SELECT LastPO.LastPONo AS PONo, LastPO.LastVersion, dbo.[CRONUS$Purchase Header Archive].[Currency Code],
dbo.[CRONUS$Purchase Header Archive].[Buy-from Vendor No_], dbo.[CRONUS$Purchase Header Archive].[Pay-to Vendor No_],
dbo.[CRONUS$Purchase Header Archive].[Pay-to Name], dbo.[CRONUS$Purchase Header Archive].[Order Date],
dbo.[CRONUS$Purchase Header Archive].[Document Date], dbo.[CRONUS$Purchase Header Archive].[Archived By],
dbo.[CRONUS$Purchase Header Archive].[No_ Printed], dbo.[CRONUS$Purchase Line Archive].No_,
dbo.[CRONUS$Purchase Line Archive].Description, dbo.[CRONUS$Purchase Line Archive].[Unit of Measure],
dbo.[CRONUS$Purchase Header Archive].[Receiving No_], dbo.[CRONUS$Purchase Line Archive].Quantity,
dbo.[CRONUS$Purchase Line Archive].[Direct Unit Cost], dbo.[CRONUS$Purchase Line Archive].[VAT _],
dbo.[CRONUS$Purchase Line Archive].Amount, dbo.[CRONUS$Purchase Line Archive].[Amount Including VAT]
FROM dbo.[CRONUS$Purchase Header Archive] INNER JOIN
(SELECT [Document Type], No_ AS LastPONo, MAX([Version No_]) AS LastVersion
FROM dbo.[CRONUS$Purchase Header Archive] AS [CRONUS$Purchase Header Archive_1]
GROUP BY [Document Type], No_
HAVING ([Document Type] = 1)) AS LastPO ON dbo.[CRONUS$Purchase Header Archive].No_ = LastPO.LastPONo AND
dbo.[CRONUS$Purchase Header Archive].[Version No_] = LastPO.LastVersion INNER JOIN
dbo.[CRONUS$Purchase Line Archive] ON
dbo.[CRONUS$Purchase Header Archive].[Document Type] = dbo.[CRONUS$Purchase Line Archive].[Document Type] AND
dbo.[CRONUS$Purchase Header Archive].No_ = dbo.[CRONUS$Purchase Line Archive].[Document No_] AND
dbo.[CRONUS$Purchase Header Archive].[Version No_] = dbo.[CRONUS$Purchase Line Archive].[Version No_]
WHERE (dbo.[CRONUS$Purchase Header Archive].[Order Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
(dbo.[CRONUS$Purchase Header Archive].[Order Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
Thursday, July 20, 2017
Dynamics NAV-LS Retail - Retail Sales - POS Manual Discounts View
SELECT dbo.[CRONUS$Trans_ Sales Entry].[Store No_], dbo.[CRONUS$Trans_ Sales Entry].[POS Terminal No_],
dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_], dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_],
dbo.[CRONUS$Trans_ Sales Entry].[Item No_], dbo.CRONUS$Item.Description, dbo.[CRONUS$Trans_ Sales Entry].Price,
dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS Quantity, dbo.[CRONUS$Trans_ Sales Entry].[Periodic Discount],
dbo.[CRONUS$Trans_ Sales Entry].[Total Discount], dbo.[CRONUS$Trans_ Sales Entry].[Line Discount],
dbo.[CRONUS$Trans_ Infocode Entry].Infocode, dbo.[CRONUS$Trans_ Infocode Entry].Information,
dbo.[CRONUS$Trans_ Infocode Entry].Amount, dbo.[CRONUS$Trans_ Sales Entry].[Net Amount] * - 1 AS [Net Amount],
dbo.[CRONUS$Trans_ Sales Entry].[VAT Amount], dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount],
dbo.[CRONUS$Trans_ Sales Entry].Date, dbo.[CRONUS$Trans_ Sales Entry].[Staff ID],
dbo.CRONUS$Staff.[First Name] + N' ' + dbo.CRONUS$Staff.[Last Name] AS StaffName, dbo.[CRONUS$Trans_ Sales Entry].[Deal Line],
dbo.[CRONUS$Trans_ Sales Entry].Price * dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS OrigNetAmt,
dbo.[CRONUS$Member Contact].Name,
dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] AS ManualDiscounts
FROM dbo.[CRONUS$Member Contact] INNER JOIN
dbo.[CRONUS$Member Sales Entry] ON
dbo.[CRONUS$Member Contact].[Account No_] = dbo.[CRONUS$Member Sales Entry].[Member Account No_] RIGHT OUTER JOIN
dbo.[CRONUS$Trans_ Sales Entry] INNER JOIN
dbo.CRONUS$Item ON dbo.[CRONUS$Trans_ Sales Entry].[Item No_] = dbo.CRONUS$Item.No_ INNER JOIN
dbo.CRONUS$Staff ON dbo.[CRONUS$Trans_ Sales Entry].[Staff ID] = dbo.CRONUS$Staff.ID ON
dbo.[CRONUS$Member Sales Entry].[Item No_] = dbo.[CRONUS$Trans_ Sales Entry].[Item No_] AND
dbo.[CRONUS$Member Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Sales Entry].[Line No_] AND
dbo.[CRONUS$Member Sales Entry].[Document No_] = dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_] LEFT OUTER JOIN
dbo.[CRONUS$Trans_ Infocode Entry] ON
dbo.[CRONUS$Trans_ Sales Entry].[Store No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Store No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[POS Terminal No_] = dbo.[CRONUS$Trans_ Infocode Entry].[POS Terminal No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Transaction No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Line No_]
WHERE (dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount] <> 0) AND (dbo.[CRONUS$Trans_ Sales Entry].Date >= CONVERT(DATETIME,
'2016-10-01 00:00:00', 102)) AND (dbo.[CRONUS$Trans_ Sales Entry].Date <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102)) AND
(dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] <> 0)
dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_], dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_],
dbo.[CRONUS$Trans_ Sales Entry].[Item No_], dbo.CRONUS$Item.Description, dbo.[CRONUS$Trans_ Sales Entry].Price,
dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS Quantity, dbo.[CRONUS$Trans_ Sales Entry].[Periodic Discount],
dbo.[CRONUS$Trans_ Sales Entry].[Total Discount], dbo.[CRONUS$Trans_ Sales Entry].[Line Discount],
dbo.[CRONUS$Trans_ Infocode Entry].Infocode, dbo.[CRONUS$Trans_ Infocode Entry].Information,
dbo.[CRONUS$Trans_ Infocode Entry].Amount, dbo.[CRONUS$Trans_ Sales Entry].[Net Amount] * - 1 AS [Net Amount],
dbo.[CRONUS$Trans_ Sales Entry].[VAT Amount], dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount],
dbo.[CRONUS$Trans_ Sales Entry].Date, dbo.[CRONUS$Trans_ Sales Entry].[Staff ID],
dbo.CRONUS$Staff.[First Name] + N' ' + dbo.CRONUS$Staff.[Last Name] AS StaffName, dbo.[CRONUS$Trans_ Sales Entry].[Deal Line],
dbo.[CRONUS$Trans_ Sales Entry].Price * dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS OrigNetAmt,
dbo.[CRONUS$Member Contact].Name,
dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] AS ManualDiscounts
FROM dbo.[CRONUS$Member Contact] INNER JOIN
dbo.[CRONUS$Member Sales Entry] ON
dbo.[CRONUS$Member Contact].[Account No_] = dbo.[CRONUS$Member Sales Entry].[Member Account No_] RIGHT OUTER JOIN
dbo.[CRONUS$Trans_ Sales Entry] INNER JOIN
dbo.CRONUS$Item ON dbo.[CRONUS$Trans_ Sales Entry].[Item No_] = dbo.CRONUS$Item.No_ INNER JOIN
dbo.CRONUS$Staff ON dbo.[CRONUS$Trans_ Sales Entry].[Staff ID] = dbo.CRONUS$Staff.ID ON
dbo.[CRONUS$Member Sales Entry].[Item No_] = dbo.[CRONUS$Trans_ Sales Entry].[Item No_] AND
dbo.[CRONUS$Member Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Sales Entry].[Line No_] AND
dbo.[CRONUS$Member Sales Entry].[Document No_] = dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_] LEFT OUTER JOIN
dbo.[CRONUS$Trans_ Infocode Entry] ON
dbo.[CRONUS$Trans_ Sales Entry].[Store No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Store No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[POS Terminal No_] = dbo.[CRONUS$Trans_ Infocode Entry].[POS Terminal No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Transaction No_] AND
dbo.[CRONUS$Trans_ Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Line No_]
WHERE (dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount] <> 0) AND (dbo.[CRONUS$Trans_ Sales Entry].Date >= CONVERT(DATETIME,
'2016-10-01 00:00:00', 102)) AND (dbo.[CRONUS$Trans_ Sales Entry].Date <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102)) AND
(dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] <> 0)
Dynamics NAV-LS Retail - Retail Sales - Voids View
SELECT dbo.[CRONUS$POS Voided Transaction].[Receipt No_], dbo.[CRONUS$POS Voided Transaction].[Transaction Type],
CASE WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 0 THEN 'Logoff' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] =
1 THEN 'Logon' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 2 THEN 'Sales' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 3 THEN 'Payment' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 4 THEN 'Remove Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 5 THEN 'Float Entry' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 6 THEN 'Change Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 7 THEN 'Tender Declaration' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 8 THEN 'Voided' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 9 THEN 'Open Drawer' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 10 THEN 'Neg Adj' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 11 THEN 'Phys Inv' END AS TrxDesc, dbo.[CRONUS$POS Voided Transaction].[Sales Staff], dbo.[CRONUS$POS Voided Transaction].[Store No_],
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_], dbo.[CRONUS$POS Voided Transaction].[Staff ID],
dbo.[CRONUS$POS Voided Transaction].[Trans_ Date], dbo.[CRONUS$POS Voided Transaction].[Trans_ Time],
dbo.[CRONUS$POS Voided Transaction].[Customer No_], dbo.[CRONUS$POS Voided Trans_ Line].[Line No_],
dbo.[CRONUS$POS Voided Trans_ Line].Number, dbo.[CRONUS$POS Voided Trans_ Line].[Currency Code],
dbo.[CRONUS$POS Voided Trans_ Line].Description, dbo.[CRONUS$POS Voided Trans_ Line].Price,
dbo.[CRONUS$POS Voided Trans_ Line].Quantity, dbo.[CRONUS$POS Voided Trans_ Line].[Net Price],
dbo.[CRONUS$POS Voided Trans_ Line].[VAT _], dbo.[CRONUS$POS Voided Trans_ Line].[Discount Amount],
dbo.[CRONUS$POS Voided Trans_ Line].[Net Amount], dbo.[CRONUS$POS Voided Trans_ Line].[VAT Amount],
dbo.[CRONUS$POS Voided Trans_ Line].Amount, dbo.[CRONUS$POS Voided Infocode Entry].Infocode,
dbo.[CRONUS$POS Voided Infocode Entry].Information, dbo.[CRONUS$POS Voided Infocode Entry].[Info_ Amt_]
FROM dbo.[CRONUS$POS Voided Transaction] LEFT OUTER JOIN
dbo.[CRONUS$POS Voided Infocode Entry] ON
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Infocode Entry].[POS Terminal No_] AND
dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Store No_] AND
dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Receipt No_] LEFT OUTER JOIN
dbo.[CRONUS$POS Voided Trans_ Line] ON
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Trans_ Line].[POS Terminal No_] AND
dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Store No_] AND
dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Receipt No_]
WHERE (dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
(dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
CASE WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 0 THEN 'Logoff' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] =
1 THEN 'Logon' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 2 THEN 'Sales' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 3 THEN 'Payment' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 4 THEN 'Remove Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 5 THEN 'Float Entry' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 6 THEN 'Change Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 7 THEN 'Tender Declaration' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 8 THEN 'Voided' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 9 THEN 'Open Drawer' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 10 THEN 'Neg Adj' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
= 11 THEN 'Phys Inv' END AS TrxDesc, dbo.[CRONUS$POS Voided Transaction].[Sales Staff], dbo.[CRONUS$POS Voided Transaction].[Store No_],
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_], dbo.[CRONUS$POS Voided Transaction].[Staff ID],
dbo.[CRONUS$POS Voided Transaction].[Trans_ Date], dbo.[CRONUS$POS Voided Transaction].[Trans_ Time],
dbo.[CRONUS$POS Voided Transaction].[Customer No_], dbo.[CRONUS$POS Voided Trans_ Line].[Line No_],
dbo.[CRONUS$POS Voided Trans_ Line].Number, dbo.[CRONUS$POS Voided Trans_ Line].[Currency Code],
dbo.[CRONUS$POS Voided Trans_ Line].Description, dbo.[CRONUS$POS Voided Trans_ Line].Price,
dbo.[CRONUS$POS Voided Trans_ Line].Quantity, dbo.[CRONUS$POS Voided Trans_ Line].[Net Price],
dbo.[CRONUS$POS Voided Trans_ Line].[VAT _], dbo.[CRONUS$POS Voided Trans_ Line].[Discount Amount],
dbo.[CRONUS$POS Voided Trans_ Line].[Net Amount], dbo.[CRONUS$POS Voided Trans_ Line].[VAT Amount],
dbo.[CRONUS$POS Voided Trans_ Line].Amount, dbo.[CRONUS$POS Voided Infocode Entry].Infocode,
dbo.[CRONUS$POS Voided Infocode Entry].Information, dbo.[CRONUS$POS Voided Infocode Entry].[Info_ Amt_]
FROM dbo.[CRONUS$POS Voided Transaction] LEFT OUTER JOIN
dbo.[CRONUS$POS Voided Infocode Entry] ON
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Infocode Entry].[POS Terminal No_] AND
dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Store No_] AND
dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Receipt No_] LEFT OUTER JOIN
dbo.[CRONUS$POS Voided Trans_ Line] ON
dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Trans_ Line].[POS Terminal No_] AND
dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Store No_] AND
dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Receipt No_]
WHERE (dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
(dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
Dynamics NAV-LS Retail - Price Change History View
----------------------------------------------------------------------------------------------------------------
--Shows all sales price records
--Change the Date Filters to the period you want
----------------------------------------------------------------------------------------------------------------
SELECT [CRONUS$Sales Price].[Item No_], CRONUS$Item.Description, [CRONUS$Sales Price].[Starting Date],
[CRONUS$Sales Price].[Ending Date], [CRONUS$Sales Price].[Unit Price Including VAT], [CRONUS$Sales Price].[Sales Type],
[CRONUS$Sales Price].[Sales Code], [CRONUS$Sales Price].[Currency Code], [CRONUS$Sales Price].[Variant Code],
[CRONUS$Sales Price].[Unit of Measure Code], [CRONUS$Sales Price].[Minimum Quantity], [CRONUS$Sales Price].[Unit Price],
[CRONUS$Sales Price].[Price Includes VAT], [CRONUS$Sales Price].[Allow Invoice Disc_],
[CRONUS$Sales Price].[VAT Bus_ Posting Gr_ (Price)], [CRONUS$Sales Price].[Allow Line Disc_], [CRONUS$Sales Price].[Markup _],
[CRONUS$Sales Price].[Profit _], [CRONUS$Sales Price].[Profit (LCY)]
FROM [CRONUS$Sales Price] INNER JOIN
CRONUS$Item ON [CRONUS$Sales Price].[Item No_] = CRONUS$Item.No_
WHERE ([CRONUS$Sales Price].[Starting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
[CRONUS$Sales Price].[Starting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
ORDER BY [CRONUS$Sales Price].[Item No_], [CRONUS$Sales Price].[Starting Date], [CRONUS$Sales Price].[Ending Date]
--Shows all sales price records
--Change the Date Filters to the period you want
----------------------------------------------------------------------------------------------------------------
SELECT [CRONUS$Sales Price].[Item No_], CRONUS$Item.Description, [CRONUS$Sales Price].[Starting Date],
[CRONUS$Sales Price].[Ending Date], [CRONUS$Sales Price].[Unit Price Including VAT], [CRONUS$Sales Price].[Sales Type],
[CRONUS$Sales Price].[Sales Code], [CRONUS$Sales Price].[Currency Code], [CRONUS$Sales Price].[Variant Code],
[CRONUS$Sales Price].[Unit of Measure Code], [CRONUS$Sales Price].[Minimum Quantity], [CRONUS$Sales Price].[Unit Price],
[CRONUS$Sales Price].[Price Includes VAT], [CRONUS$Sales Price].[Allow Invoice Disc_],
[CRONUS$Sales Price].[VAT Bus_ Posting Gr_ (Price)], [CRONUS$Sales Price].[Allow Line Disc_], [CRONUS$Sales Price].[Markup _],
[CRONUS$Sales Price].[Profit _], [CRONUS$Sales Price].[Profit (LCY)]
FROM [CRONUS$Sales Price] INNER JOIN
CRONUS$Item ON [CRONUS$Sales Price].[Item No_] = CRONUS$Item.No_
WHERE ([CRONUS$Sales Price].[Starting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
[CRONUS$Sales Price].[Starting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
ORDER BY [CRONUS$Sales Price].[Item No_], [CRONUS$Sales Price].[Starting Date], [CRONUS$Sales Price].[Ending Date]
Tuesday, July 18, 2017
SSRS - Add default dates to date parameters
- Current Month
- From:
- =DateAdd("d",(Day(Today())*-1)+1,Today())
- To:
- =dateadd("d",-1,dateadd("m",1,DateAdd("d",(Day(Today())*-1)+1,Today())))
Dynamics GP - Word Forms - How to add terms and conditions to last page
http://dynamicsgpblogster.blogspot.com/2010/09/how-to-add-terms-and-conditions-page-to.html
- Select line outside of table after total (not in page footer)
- Insert new 1x1 table
- Paste terms and conditions into here
- Select Table
- Go to Paragraph>Line and Page Breaks
- Tick Keep Lines Together
- Tick Page Breaks Before
Dynamics GP - seeRMHATBSRSWrapper - Output SSRS Historical AR TB to Table - including Analytical Accounting
/****** Object: View [dbo].[BI_SOP_AA_Dtl] Script Date: 1/26/2017 1:03:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_AA_Dtl]
AS
SELECT DISTINCT
RTRIM(CASE WHEN dbo.AAG20000.DOCNUMBR IS NULL THEN sop30300.sopnumbe ELSE AAG20000.DOCNUMBR END) AS SOPNUMBE, dbo.SOP30300.ITEMNMBR,
dbo.SOP30300.ITEMDESC, dbo.AAG00401.aaTrxDimCodeDescr, dbo.AAG00401.aaTrxDimCode, dbo.AAG00401.aaTrxDimCodeDescr2,
dbo.AAG20000.DOCNUMBR
FROM dbo.AAG20003 INNER JOIN
dbo.AAG20001 ON dbo.AAG20003.aaSubLedgerDistID = dbo.AAG20001.aaSubLedgerDistID AND
dbo.AAG20003.aaSubLedgerHdrID = dbo.AAG20001.aaSubLedgerHdrID INNER JOIN
dbo.AAG00401 ON dbo.AAG20003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG20003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
dbo.AAG20000 ON dbo.AAG20001.aaSubLedgerHdrID = dbo.AAG20000.aaSubLedgerHdrID FULL OUTER JOIN
dbo.SOP30300 ON dbo.AAG20000.DOCNUMBR = dbo.SOP30300.SOPNUMBE
GO
/****** Object: Table [dbo].[BIT_RMHATB] Script Date: 1/26/2017 1:01:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_RMHATB](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AMCTRXSTT] [smallint] NOT NULL
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[BI_RMHATB2] Script Date: 1/26/2017 1:03:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB2]
AS
SELECT dbo.BIT_RMHATB.*, dbo.BI_SOP_AA_Dtl.ITEMNMBR, dbo.BI_SOP_AA_Dtl.ITEMDESC, dbo.BI_SOP_AA_Dtl.aaTrxDimCodeDescr,
dbo.BI_SOP_AA_Dtl.aaTrxDimCode
FROM dbo.BIT_RMHATB LEFT OUTER JOIN
dbo.BI_SOP_AA_Dtl ON dbo.BIT_RMHATB.DOCNUMBR = dbo.BI_SOP_AA_Dtl.SOPNUMBE
GO
/****** Object: StoredProcedure [dbo].[BI_seermHATBSRSWrapper] Script Date: 1/26/2017 1:01:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seermHATBSRSWrapper] @I_dAgingDate datetime = NULL, @I_cStartCustomerNumber char(15) = NULL, @I_cEndCustomerNumber char(15) = NULL, @I_cStartCustomerName char(65) = NULL, @I_cEndCustomerName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartSalesPersonID char(15) = NULL, @I_cEndSalesPersonID char(15) = NULL, @I_cStartSalesTerritory char(15) = NULL, @I_cEndSalesTerritory char(15) = NULL, @I_cStartShortName char(15) = NULL, @I_cEndShortName char(15) = NULL, @I_cStartState char(5) = NULL, @I_cEndState char(5) = NULL, @I_cStartZipCode char(11) = NULL, @I_cEndZipCode char(11) = NULL, @I_cStartPhoneNumber char(21) = NULL, @I_cEndPhoneNumber char(21) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_sIncludeBalanceTypes smallint = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceCustomer tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL, @I_tConsolidateNAActivity tinyint = NULL as declare @FUNLCURR char(12) CREATE TABLE #RMHATBAP( [APFRDCNM] [char](21) NOT NULL, [APFRDCTY] [smallint] NOT NULL, [FROMCURR] [char](15) NOT NULL, [APTODCNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [APPTOAMT] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [DATE1] [datetime] NOT NULL, [POSTED] [tinyint] NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [PPSAMDED] [numeric](19, 5) NOT NULL, [GSTDSAMT] [numeric](19, 5) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [ORAPTOAM] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP ( [APFRDCNM] ASC, [APFRDCTY] ASC, [APTODCNM] ASC, [APTODCTY] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP ( [APTODCTY] ASC, [APTODCNM] ASC, [APFRDCTY] ASC, [APFRDCNM] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBDO( [CUSTNMBR] [char](15) NOT NULL, [CHCUMNUM] [char](15) NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [RMDTYPAL] [smallint] NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [CURNCYID] [char](15) NOT NULL, [ORTRXAMT] [numeric](19, 5) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [AGNGBUKT] [smallint] NOT NULL, [CASHAMNT] [numeric](19, 5) NOT NULL, [COMDLRAM] [numeric](19, 5) NOT NULL, [SLSAMNT] [numeric](19, 5) NOT NULL, [COSTAMNT] [numeric](19, 5) NOT NULL, [FRTAMNT] [numeric](19, 5) NOT NULL, [MISCAMNT] [numeric](19, 5) NOT NULL, [TAXAMNT] [numeric](19, 5) NOT NULL, [DISAVAMT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [TRXDSCRN] [char](31) NOT NULL, [DOCABREV] [char](3) NOT NULL, [CHEKNMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [GLPOSTDT] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [POSTDATE] [datetime] NOT NULL, [DINVPDOF] [datetime] NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORCASAMT] [numeric](19, 5) NOT NULL, [ORSLSAMT] [numeric](19, 5) NOT NULL, [ORCSTAMT] [numeric](19, 5) NOT NULL, [ORDAVAMT] [numeric](19, 5) NOT NULL, [ORFRTAMT] [numeric](19, 5) NOT NULL, [ORMISCAMT] [numeric](19, 5) NOT NULL, [ORTAXAMT] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [ORORGTRX] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [Aging_Period_Amount] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO ( [RMDTYPAL] ASC, [DOCNUMBR] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO ( [CUSTNMBR] ASC, [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO ( [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBCU( [CUSTNMBR] [char](15) NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [AGNGDATE] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU ( [DSCRIPTN] ASC, [CUSTNMBR] ASC ) select @FUNLCURR = rtrim(FUNLCURR) from MC40000 If @I_cEndCustomerNumber = '' begin set @I_cEndCustomerNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndCustomerName = '' begin set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ' End If @I_cEndClassID = '' begin set @I_cEndClassID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesPersonID = '' begin set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesTerritory = '' begin set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ' End If @I_cEndShortName = '' begin set @I_cEndShortName = 'þþþþþþþþþþþþþþþ' End If @I_cEndState = '' begin set @I_cEndState = 'þþþþþþþþþþþþþþþ' End If @I_cEndZipCode = '' begin set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ' End If @I_cEndPhoneNumber = '' begin set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndUserDefined = '' begin set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ' End exec [rmHistoricalAgedTrialBalance] '#RMHATBCU', '#RMHATBDO', '#RMHATBAP', '', @I_dAgingDate, @I_cStartCustomerNumber, @I_cEndCustomerNumber, @I_cStartCustomerName, @I_cEndCustomerName, @I_cStartClassID, @I_cEndClassID, @I_cStartSalesPersonID, @I_cEndSalesPersonID, @I_cStartSalesTerritory, @I_cEndSalesTerritory, @I_cStartShortName, @I_cEndShortName, @I_cStartState, @I_cEndState, @I_cStartZipCode, @I_cEndZipCode, @I_cStartPhoneNumber, @I_cEndPhoneNumber, @I_cStartUserDefined, @I_cEndUserDefined, 1, 0, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_sIncludeBalanceTypes, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceCustomer, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, @I_tConsolidateNAActivity, @FUNLCURR, 0, 0, 1.0000, 0, 2, 0
Drop table BIT_RMHATB
Select RMHATB.* Into BIT_RMHATB FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB WHILE (SELECT COUNT(*) FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB WHERE ORTRXAMT<>0 GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0 BEGIN UPDATE BIT_RMHATB SET AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0 FROM BIT_RMHATB JOIN ( SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) PARTIALLY_APPLIED ON PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB.APFRDCNM END
SELECT * FROM BI_RMHATB4
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_AA_Dtl]
AS
SELECT DISTINCT
RTRIM(CASE WHEN dbo.AAG20000.DOCNUMBR IS NULL THEN sop30300.sopnumbe ELSE AAG20000.DOCNUMBR END) AS SOPNUMBE, dbo.SOP30300.ITEMNMBR,
dbo.SOP30300.ITEMDESC, dbo.AAG00401.aaTrxDimCodeDescr, dbo.AAG00401.aaTrxDimCode, dbo.AAG00401.aaTrxDimCodeDescr2,
dbo.AAG20000.DOCNUMBR
FROM dbo.AAG20003 INNER JOIN
dbo.AAG20001 ON dbo.AAG20003.aaSubLedgerDistID = dbo.AAG20001.aaSubLedgerDistID AND
dbo.AAG20003.aaSubLedgerHdrID = dbo.AAG20001.aaSubLedgerHdrID INNER JOIN
dbo.AAG00401 ON dbo.AAG20003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG20003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
dbo.AAG20000 ON dbo.AAG20001.aaSubLedgerHdrID = dbo.AAG20000.aaSubLedgerHdrID FULL OUTER JOIN
dbo.SOP30300 ON dbo.AAG20000.DOCNUMBR = dbo.SOP30300.SOPNUMBE
GO
/****** Object: Table [dbo].[BIT_RMHATB] Script Date: 1/26/2017 1:01:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_RMHATB](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AMCTRXSTT] [smallint] NOT NULL
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[BI_RMHATB2] Script Date: 1/26/2017 1:03:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB2]
AS
SELECT dbo.BIT_RMHATB.*, dbo.BI_SOP_AA_Dtl.ITEMNMBR, dbo.BI_SOP_AA_Dtl.ITEMDESC, dbo.BI_SOP_AA_Dtl.aaTrxDimCodeDescr,
dbo.BI_SOP_AA_Dtl.aaTrxDimCode
FROM dbo.BIT_RMHATB LEFT OUTER JOIN
dbo.BI_SOP_AA_Dtl ON dbo.BIT_RMHATB.DOCNUMBR = dbo.BI_SOP_AA_Dtl.SOPNUMBE
GO
/****** Object: View [dbo].[BI_RMHATB3] Script Date: 7/18/2017 7:41:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB3]
AS
select * from(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY docnumbr ORDER BY docnumbr DESC) AS rn
FROM BI_RMHATB2) as A
GO
-----------------------------------------------------------------------
CREATE VIEW [dbo].[BI_RMHATB4]
AS
SELECT APPLY_AMOUNT AS OrigAppAmt, AGING_AMOUNT AS OrigAgingAmt, CURTRXAM AS OrigCurTrxamt, APPLY_AMOUNT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN AGING_AMOUNT ELSE (CASE WHEN rmdtypal > 1 THEN AGING_AMOUNT ELSE 0 END) END AS AGING_AMOUNT, CUSTNMBR, CUSTNAME,
BALNCTYP, USERDEF1, CNTCPRSN, PHONE1, SLPRSNID, SALSTERR, PYMTRMID, CRLMTAMT, CRLMTPER, CRLMTPAM, CRLMTTYP, CUSTCLAS, SHRTNAME, ZIP,
STATE, CUDSCRIPTN, AGNGDATE, CHCUMNUM, DOCNUMBR, RMDTYPAL, DSCRIPTN, DCURNCYID, ORTRXAMT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN CURTRXAM ELSE (CASE WHEN rmdtypal > 1 THEN CURTRXAM ELSE 0 END) END AS CURTRXAM, AGNGBUKT, CASHAMNT, COMDLRAM,
SLSAMNT, COSTAMNT, FRTAMNT, MISCAMNT, TAXAMNT, DISAVAMT, DDISTKNAM, DWROFAMNT, TRXDSCRN, DOCABREV, CHEKNMBR, DOCDATE, DUEDATE,
GLPOSTDT, DISCDATE, POSTDATE, DINVPDOF, DCURRNIDX, DXCHGRATE, ORCASAMT, ORSLSAMT, ORCSTAMT, ORDAVAMT, ORFRTAMT, ORMISCAMT, ORTAXAMT,
ORCTRXAM, ORORGTRX, DORDISTKN, DORWROFAM, DDENXRATE, DMCTRXSTT, Aging_Period_Amount, APFRDCNM, APFRDCTY, FROMCURR, APTODCNM,
APTODCTY, APPTOAMT, ACURNCYID, DATE1, POSTED, ADISTKNAM, AWROFAMNT, PPSAMDED, GSTDSAMT, ACURRNIDX, AXCHGRATE, RLGANLOS, ORAPTOAM,
AORDISTKN, AORWROFAM, ADENXRATE, AMCTRXSTT, ITEMNMBR, ITEMDESC, aaTrxDimCodeDescr, aaTrxDimCode, rn
FROM dbo.BI_RMHATB3
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB3]
AS
select * from(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY docnumbr ORDER BY docnumbr DESC) AS rn
FROM BI_RMHATB2) as A
GO
-----------------------------------------------------------------------
CREATE VIEW [dbo].[BI_RMHATB4]
AS
SELECT APPLY_AMOUNT AS OrigAppAmt, AGING_AMOUNT AS OrigAgingAmt, CURTRXAM AS OrigCurTrxamt, APPLY_AMOUNT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN AGING_AMOUNT ELSE (CASE WHEN rmdtypal > 1 THEN AGING_AMOUNT ELSE 0 END) END AS AGING_AMOUNT, CUSTNMBR, CUSTNAME,
BALNCTYP, USERDEF1, CNTCPRSN, PHONE1, SLPRSNID, SALSTERR, PYMTRMID, CRLMTAMT, CRLMTPER, CRLMTPAM, CRLMTTYP, CUSTCLAS, SHRTNAME, ZIP,
STATE, CUDSCRIPTN, AGNGDATE, CHCUMNUM, DOCNUMBR, RMDTYPAL, DSCRIPTN, DCURNCYID, ORTRXAMT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN CURTRXAM ELSE (CASE WHEN rmdtypal > 1 THEN CURTRXAM ELSE 0 END) END AS CURTRXAM, AGNGBUKT, CASHAMNT, COMDLRAM,
SLSAMNT, COSTAMNT, FRTAMNT, MISCAMNT, TAXAMNT, DISAVAMT, DDISTKNAM, DWROFAMNT, TRXDSCRN, DOCABREV, CHEKNMBR, DOCDATE, DUEDATE,
GLPOSTDT, DISCDATE, POSTDATE, DINVPDOF, DCURRNIDX, DXCHGRATE, ORCASAMT, ORSLSAMT, ORCSTAMT, ORDAVAMT, ORFRTAMT, ORMISCAMT, ORTAXAMT,
ORCTRXAM, ORORGTRX, DORDISTKN, DORWROFAM, DDENXRATE, DMCTRXSTT, Aging_Period_Amount, APFRDCNM, APFRDCTY, FROMCURR, APTODCNM,
APTODCTY, APPTOAMT, ACURNCYID, DATE1, POSTED, ADISTKNAM, AWROFAMNT, PPSAMDED, GSTDSAMT, ACURRNIDX, AXCHGRATE, RLGANLOS, ORAPTOAM,
AORDISTKN, AORWROFAM, ADENXRATE, AMCTRXSTT, ITEMNMBR, ITEMDESC, aaTrxDimCodeDescr, aaTrxDimCode, rn
FROM dbo.BI_RMHATB3
GO
/****** Object: StoredProcedure [dbo].[BI_seermHATBSRSWrapper] Script Date: 1/26/2017 1:01:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seermHATBSRSWrapper] @I_dAgingDate datetime = NULL, @I_cStartCustomerNumber char(15) = NULL, @I_cEndCustomerNumber char(15) = NULL, @I_cStartCustomerName char(65) = NULL, @I_cEndCustomerName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartSalesPersonID char(15) = NULL, @I_cEndSalesPersonID char(15) = NULL, @I_cStartSalesTerritory char(15) = NULL, @I_cEndSalesTerritory char(15) = NULL, @I_cStartShortName char(15) = NULL, @I_cEndShortName char(15) = NULL, @I_cStartState char(5) = NULL, @I_cEndState char(5) = NULL, @I_cStartZipCode char(11) = NULL, @I_cEndZipCode char(11) = NULL, @I_cStartPhoneNumber char(21) = NULL, @I_cEndPhoneNumber char(21) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_sIncludeBalanceTypes smallint = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceCustomer tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL, @I_tConsolidateNAActivity tinyint = NULL as declare @FUNLCURR char(12) CREATE TABLE #RMHATBAP( [APFRDCNM] [char](21) NOT NULL, [APFRDCTY] [smallint] NOT NULL, [FROMCURR] [char](15) NOT NULL, [APTODCNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [APPTOAMT] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [DATE1] [datetime] NOT NULL, [POSTED] [tinyint] NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [PPSAMDED] [numeric](19, 5) NOT NULL, [GSTDSAMT] [numeric](19, 5) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [ORAPTOAM] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP ( [APFRDCNM] ASC, [APFRDCTY] ASC, [APTODCNM] ASC, [APTODCTY] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP ( [APTODCTY] ASC, [APTODCNM] ASC, [APFRDCTY] ASC, [APFRDCNM] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBDO( [CUSTNMBR] [char](15) NOT NULL, [CHCUMNUM] [char](15) NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [RMDTYPAL] [smallint] NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [CURNCYID] [char](15) NOT NULL, [ORTRXAMT] [numeric](19, 5) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [AGNGBUKT] [smallint] NOT NULL, [CASHAMNT] [numeric](19, 5) NOT NULL, [COMDLRAM] [numeric](19, 5) NOT NULL, [SLSAMNT] [numeric](19, 5) NOT NULL, [COSTAMNT] [numeric](19, 5) NOT NULL, [FRTAMNT] [numeric](19, 5) NOT NULL, [MISCAMNT] [numeric](19, 5) NOT NULL, [TAXAMNT] [numeric](19, 5) NOT NULL, [DISAVAMT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [TRXDSCRN] [char](31) NOT NULL, [DOCABREV] [char](3) NOT NULL, [CHEKNMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [GLPOSTDT] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [POSTDATE] [datetime] NOT NULL, [DINVPDOF] [datetime] NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORCASAMT] [numeric](19, 5) NOT NULL, [ORSLSAMT] [numeric](19, 5) NOT NULL, [ORCSTAMT] [numeric](19, 5) NOT NULL, [ORDAVAMT] [numeric](19, 5) NOT NULL, [ORFRTAMT] [numeric](19, 5) NOT NULL, [ORMISCAMT] [numeric](19, 5) NOT NULL, [ORTAXAMT] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [ORORGTRX] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [Aging_Period_Amount] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO ( [RMDTYPAL] ASC, [DOCNUMBR] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO ( [CUSTNMBR] ASC, [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO ( [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBCU( [CUSTNMBR] [char](15) NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [AGNGDATE] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU ( [DSCRIPTN] ASC, [CUSTNMBR] ASC ) select @FUNLCURR = rtrim(FUNLCURR) from MC40000 If @I_cEndCustomerNumber = '' begin set @I_cEndCustomerNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndCustomerName = '' begin set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ' End If @I_cEndClassID = '' begin set @I_cEndClassID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesPersonID = '' begin set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesTerritory = '' begin set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ' End If @I_cEndShortName = '' begin set @I_cEndShortName = 'þþþþþþþþþþþþþþþ' End If @I_cEndState = '' begin set @I_cEndState = 'þþþþþþþþþþþþþþþ' End If @I_cEndZipCode = '' begin set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ' End If @I_cEndPhoneNumber = '' begin set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndUserDefined = '' begin set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ' End exec [rmHistoricalAgedTrialBalance] '#RMHATBCU', '#RMHATBDO', '#RMHATBAP', '', @I_dAgingDate, @I_cStartCustomerNumber, @I_cEndCustomerNumber, @I_cStartCustomerName, @I_cEndCustomerName, @I_cStartClassID, @I_cEndClassID, @I_cStartSalesPersonID, @I_cEndSalesPersonID, @I_cStartSalesTerritory, @I_cEndSalesTerritory, @I_cStartShortName, @I_cEndShortName, @I_cStartState, @I_cEndState, @I_cStartZipCode, @I_cEndZipCode, @I_cStartPhoneNumber, @I_cEndPhoneNumber, @I_cStartUserDefined, @I_cEndUserDefined, 1, 0, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_sIncludeBalanceTypes, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceCustomer, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, @I_tConsolidateNAActivity, @FUNLCURR, 0, 0, 1.0000, 0, 2, 0
Drop table BIT_RMHATB
Select RMHATB.* Into BIT_RMHATB FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB WHILE (SELECT COUNT(*) FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB WHERE ORTRXAMT<>0 GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0 BEGIN UPDATE BIT_RMHATB SET AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0 FROM BIT_RMHATB JOIN ( SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) PARTIALLY_APPLIED ON PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB.APFRDCNM END
SELECT * FROM BI_RMHATB4
GO
Subscribe to:
Posts (Atom)