Dynamics NAV - Item quantities with Large Decimals > 5

Sometimes item stock will go into large fractions.
To resolve this, do a Departments/Financial Management/Inventory/Costing/Phys. Inventory Journal
Create a Batch
Calculate for your specific items that are giving trouble
Edit the Qty. (Phys Inventory) and set it to the closest higher amount to adjust the stock in to remove the small rounding.

You have to round transactions in the Warehouse entry 7312 to 5 dp to get the sums to calculate correctly.



select * into [CRONUS$Warehouse Entry_BAK] from [CRONUS$Warehouse Entry]

update [CRONUS$Warehouse Entry] set Quantity = round(Quantity,5), [Qty_ (Base)] = round([Qty_ (Base)],5)
where
[Qty_ (Base)] - round([Qty_ (Base)],5) <> 0
and [Item No_] in (
SELECT        [CRONUS$Warehouse Entry].[Item No_]
FROM            [CRONUS$Warehouse Entry] INNER JOIN
                         [CRONUS$Item] ON [CRONUS$Warehouse Entry].[Item No_] = [CRONUS$Item].No_
GROUP BY [CRONUS$Warehouse Entry].[Item No_], [CRONUS$Item].Blocked
HAVING        (SUM([CRONUS$Warehouse Entry].Quantity) - ROUND(SUM([CRONUS$Warehouse Entry].Quantity), 2) <> 0) AND ([CRONUS$Item].Blocked = 0)

)

LS NAV Views - Transaction per Day / Store

SELECT        COUNT([Transaction No_]) AS TrxCount, Date, [Store No_], [POS Terminal No_]
FROM            [Transaction Header]
GROUP BY Date, [Store No_], [POS Terminal No_]
HAVING        (Date > GETDATE() - 5)
ORDER BY Date

LS NAV - Modify Picking Intructions

R214 - Pick Instruction

Dynamics NAV - Modify Customer Statement

R116 - Statement
T47 - Aging Band Buffer

• Remove bank info
• Customer balance aging on footer
• Add Invoice numbers
• The posting date should be invoice date 

Add default report options in request page CAL

StartDate := 010105D;

EndDate := TODAY;

IncludeAgingBand := TRUE;

DateChoice := DateChoice::"Posting Date";

Add additional aging bucket and labels

T47 - Add column for additional buckets

Add UseAgingLabels Variable to request page
Add new rows in aging bucket header, show/hide based on UseAgingLabels value

CurrAgingDate := AgingDate[6];

AgingLabel[6] := FORMAT(CurrAgingDate - AgingDate[5]) + ' days';
AgingLabel[5] := FORMAT(CurrAgingDate - AgingDate[4]) + ' days';
AgingLabel[4] := FORMAT(CurrAgingDate - AgingDate[3]) + ' days';
AgingLabel[3] := FORMAT(CurrAgingDate - AgingDate[2]) + ' days';
AgingLabel[2] := FORMAT(CurrAgingDate - AgingDate[1]) + ' days';
AgingLabel[1] := 'Over ' + FORMAT(AgingDate[6] - AgingDate[1]);

Dynamics NAV - Modify Sales Quote

R204 - Sales Quote

Field calculations used

Enter all calculations under Integer group under Sales Line. Do not use the actual Sales Line group.
Use max for all values in the report.

Unit Price NoVat, no Discount = "Sales Line".Amount / "Sales Line".Quantity
Gross Total (UnitPriceNoVat, no discount x qty) = "Sales Line".Amount
NetAmount (line total no vat no discount):= "Sales Line".Amount-"Sales Line"."Line Discount Amount"-VATAmount;

Dynamics NAV - Extended pack

https://systemcenter.dk/wp-content/uploads/2018/03/Microsoft-Dynamics-NAV-2018-Product-Overview-and-Capability-Guide.pdf

• Extended Pack gives you
• Intercompany Postings, Responsibility Centers, Cost Accounting
• Service Order, Service Price, Service Item, Service Contract, Planning & Dispatching (Not related to Project-Jobs, which is in standard)
• Manufacturing Objects
• Warehousing Objects
• Warehouse Receipt, Shipment
• Put Away
• Internal Pick and Put away
• Bins
• Additional Custom Objects (You already get from the Standard, these are in addition to those)
• Additional 10 Tables, Codeunits
• Additional 100 Pages, XML Ports

LS NAV - Sales Types - Suspend to large format Pick Ticket or Quote

• From the POS, it may be necessary to suspend a transaction to print a pick ticket or quote.
• Pick Ticket
• After the order has been picked, the suspend is retrieved, and cashed out
• Quote
• If a customer returns to accept the quote, the suspend is retrieved and cashed out

To achieve this, Sales Types are used

• Pick Ticket using Sales Order
• When the transaction is suspended, the data will be temporarily stored as a sales order to allow you to use the pick ticket print from sales orders
• It is possible to modify the Sales Order and this will be reflected when the order is recovered from suspension on the pos
• When the transaction is cashed out and completed, it is removed from the sales orders
• This setup will suppress the regular suspend slip print
• Go to Report Selections>Set 214 as Pick instruction
• Navigate to Departments/LS Retail/Point Of Sale/Design/Functionality/Sales Types
• Create a Sales Type of PICK
• Set Suspend Type to Sales Order
• Tick Request>Description to allow user to capture a name (This will not print by default on 214, it must be added, stored in T36 SalesHeader.POS Comment)
• Set print options to Sales Report ID, Set report id to 214
• In POS, find the suspend button or make one
• Right click>Button properties
• Command = Suspend
• Parameter = PICK (This triggers the creation of the sales order, and print of 214)
• Quote using Sales Quote
• When the transaction is suspended, the data will be temporarily stored as a sales quote to allow you to use the quote print from sales quotes
• It is possible to modify the Sales Quote and this will be reflected when the Quote is recovered from suspension on the pos
• When the transaction is cashed out and completed, it is removed from the sales quotes
• This setup will suppress the regular suspend slip print
• Navigate to Departments/LS Retail/Point Of Sale/Design/Functionality/Sales Types
• Create a Sales Type of QUOTE
• Set Suspend Type to Sales Quote
• *****NOTE: YOU CANNOT RETRIEVE QUOTES FROM SUSPEND USING GETORDER*****
• *****Create all transactions as orders, and just display the sales type in the datatable*****
• Tick Request>Description to allow user to capture a name (This will not print by default on 204, it must be added, stored in T36 SalesHeader.POS Comment)
• Set print options to Sales Report ID, Set report id to 204
• In POS, find the suspend button or make one
• Right click>Button properties
• Command = Suspend
• Parameter = QUOTE (This triggers the creation of the sales order, and print of 204)
• To Retrieve the Orders, you cannot use the regular SUSPEND command, you must use the GETORDER command instead, and update the datatable and menu buttons to make the display list functional (copy the retrieve and cancel from the suspend menu)
• To Retrieve the Quote
• Add Parameter "QUOTE" in the GETORDER button
• However, this will only allow you to retrieve quotes from the lookup
• You can create separate buttons to retrieve quote, and retrieve order, but they will use the same datatable lookup
• A complete fix is to add additional commands to GETQUOTE and DataTables for the Quotes
• C99001570 POS Transaction>GetOrderPressed
• Create a new command for GETQUOTEPRESSED
• 
  

SQL - Database is too large. Size does not change after deleting data.

https://technet.microsoft.com/library/ms189035(v=sql.120).aspx

You have to run shrinkdatabase to free up all unused space in the db

Use Master
Alter Database TEST
SET SINGLE_USER With ROLLBACK IMMEDIATE

DBCC SHRINKDATABASE (UserDB, 10);

GO
Use Master
Alter Database TEST
SET MULTI_USER

LS NAV - Data Director - How to temporarily stop jobs from sending to a terminal that is down for an extended period of time

• Go to Departments/LS Retail/BackOffice/Store Management/Distribution/Distribution Locations
• Untick "Active for Replication" for the terminals required

Keep in mind, if you do not send updates to these terminals, when you eventually do turn them on, they will need preloads of all data to sync up with the HO.

If you allow the jobs to just build up, when you turn the machine on, it will receive all of the updates and work fine.

However, if you leave a million jobs to pile up in DD, this will also lead to problems.

Use your discretion.

LS NAV - Modify Large Invoice - Detailed Receipt

This is the large invoice format that can print from the LS Retail POS

To enable Detail Receipt Printing:

• Departments/LS Retail/Point Of Sale/POS/Profiles/Functionality Profile
• Under Windows Printing Fasttab
• Set a value for each report object that should be used instead of the OPOS report
• Sales Slip = Receipt
• POS will send print to the default windows printer

99001470 - Detailed Receipt

• Adjust Layout
• Calc sums in cal
• Use max in page footer to display total

Dynamics NAV - Modify AR Trial Balance - Customer Detail/Summary Trial Balance

R120 - Aged Accounts Receivable
R105 - Customer Summary Aging
R109 - Customer - Summary Aging Simp. (Shows aging in LCY Only)
R129 - Customer - Trial Balance
R104 - Customer - Detail Trial Bal.

• Update layout add 2 columns
•       Increase dimension count by 2 for
•       Headertext, GrandTotalCustLedgEntry,AgedCustLedgEntry, TotalCustLedgEntry
• PeriodStartDate,PeriodEndDate,

Dynamics NAV - Modify AP Trial Balance - Vendor Detail/Summary Trial Balance

R304 - Vendor Detail Trial Balance
R305 - Vendor Summary Aging
R322 - Aged Accounts Payable

• Vendor Summary Aging
• Starting Date is the date to age from (should always be today)
• Add additional aging buckets, default is 3, increase to 5
•  Increase by 2 Columns, Increase PeriodStartDate from 6 to 8
•  VendBalanceDue dim inc from 5 to 7
•  VendBalanceDueLCY dim inc from 5 to 7
•  InVendBalanceDueLCY dim inc from 5 to 7
•  InVendBalanceDueLCY2 dim inc from 5 to 7
• Adjust Layout, add new columns
• Adjust total formulas
• Aged Accounts Payable
• Update Layout. Add 2 more aging columns.
•       All dim increased by 2
•       PeriodStartDate,PeriodEndDate,HeaderText,GrandTotalVLERemaingAmtLCY
•       AgedVendorLedgEntry,TotalVendorLedgentry

Dynamics NAV - How to Automatically restart a NAS instance using Batch file or SQL

Create a new batch file with these lines
Replace YourServerInstanceName with the specific instance you want to restart (It should be your NAS instance from the NAV Admin tool)
-----------------------------------------------

NET STOP "MicrosoftDynamicsNavServer$YourServerInstanceName"
NET START "MicrosoftDynamicsNavServer$YourServerInstanceName"

------------------------------------------------

Schedule this batch file to run daily with admin priveleges to ensure you always have a clean NAS running.
ensure that the scheduling does NOT interfere with any jobs like adjust costs, otherwise it will kill the job halfway.


---------------------------------------------
This can also be done using SQL

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO
xp_cmdshell 'NET STOP MicrosoftDynamicsNavServer$YourServerInstanceName'

xp_cmdshell 'NET START MicrosoftDynamicsNavServer$YourServerInstanceName'

Dynamics NAV - Modify Check Notes - Stub/Check

R1401 - Check (Stub/Check)

Other Check formats (10401 SSC, 10411 SCS) must be obtained from a non-W1 version

• Stub is body, Check is footer
• Stub will bleed to next page if too long
• Add Vendor id, Vendor Phone

Before printing, you have to create the custom page size on the printer, and select it when printing checks.

• To print a check go to Departments/Financial Management/Cash Management/Payment Journals
• Date-Date
• Doctype - payment
• Account Type - Vendor
• Vendor - Choose vendor id
• Currency Code - Must match the bank you will choose
• Payment method code - Check
• Amount - enter amount
• Bal Account type - Bank
• Bal Account - Bank account
• Applies to - choose application
• Bank payment type - Computer check
• Print Check
• Enter bank Account
• To reprint, Void all checks, then Print Checks again

Dynamics NAV - Modify Purchase Order Notes

Dev Environment > Tools> Options> Use report Builder = Yes
Tools>License Information>Change>Dev key

Add page headers - move fields into header
Add page footers - move fields into footer
Add Company address
Add ship to address
Add vendor phone numbers
Add Expected receipt date
Add currency
Add User printed
Add buyer
Add authorization signature
Add line numbers

• Purchase Order Print - Report 405 Order
• P50 - Purchase Order
• Add Expected Receipt Date
• T38 - Purchase Header
• 
  

Dynamics NAV - Core Landed Costs functionality

• You can do the following
• Setup Charge Items that represent freight, insurance, or other charges
• From a Purchase Order, or a Purchase Invoice you can apportion this cost across existing lines in the current PO based on Weight, Volume or Value
• Weight and volume are on the item card, and item uofm setup
• You can also apportion the cost across Posted Purchase Receipt Lines for different Po's and Vendors (Item Charge Assignment>Get Receipt Lines)
• Automatically create value entries, and update the cost of the received items retroactively
• It updates the direct cost of the item
• It updates any COGS entries on the item
• Apply multiple item charge lines to a single line (Eg. Freight and Insurance)
• Use this method only when you are paying one vendor for ALL of the costs

• You Cannot do the following
• Assign Charge items across multiple Purchase Order Lines
• You must post a Purchase Receipt first, then you can assign item charges across multiple receipts from different vendors
• Store specific Tariff or duty percentages per item or item type
• Automatically calculate item charge values based on a standard duty % for an item type
• Automatically calculate compounded charge item values 
• Eg. Apply Item Charge 1 for Extra Charges
• Then Apply Item charge 2 for 10% Duties that include the value of the extra charges
• Print any Customs documents
• Combine Multiple PO's into a single shipment
• Track Containers
• Assign PO lines across Containers
• Assign charge items to General Ledger lines or Fixed Asset lines

Dynamics NAV - cannot be opened by this version of the microsoft dynamics nav development environment

The database has already been converted in a newer version.

Download and install the latest version of NAV (Latest patch or hotfix)
Extract the files, Run the setup

Dynamics GP - Cash Receipts Tables

RM10201 - Unposted AR Cash Receipts
SOP10103 - UnPosted Sales Cash Receipts
rm20101 - Includes work Cash Receipts
rm30101 - Includes historical cash receipts

Dynamics GP - View - Posted and Unposted AR, SOP and Cash Receipts

--Combines Tables SOP10100 -unposted sop,SOP30200-posted sop, sop10102, sop10103-posted and unposted cash receipts, RM10201-unposted AR, RM20101-posted ar, RM30201-historical ar

/****** Object:  View [dbo].[BI_AR_Apply_Detail]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*Minor changes have been made to include additional information*/
CREATE VIEW [dbo].[BI_AR_Apply_Detail]
AS
SELECT     T.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, CM.SHRTNAME AS Short_Name, T.DOCDATE AS Document_Date, T.GLPOSTDT AS GL_Posting_Date,
                  CASE T .RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, T.BACHNUMB AS Payment_Batch, T.docTypeNum AS Document_Type_and_Number, T.DOCNUMBR AS Document_Number,
                  T.ORTRXAMT AS Original_Trx_Amount, T.CURTRXAM AS Current_Trx_Amount, T.amountApplied AS Total_Applied_Amount, A.APPTOAMT AS Amount_Applied, A.APTODCTY AS Applied_to_Doc_Type, A.debitType AS Applied_to_Doc_Type_Name,
                  A.APTODCNM AS Applied_to_Doc_Number, A.APTODCDT AS Applied_to_Document_Date, A.ApplyToGLPostDate AS Applied_to_GL_Posting_Date, A.DISTKNAM AS Discount, A.WROFAMNT AS Writeoff, A.DATE1 AS Apply_Date, A.GLPOSTDT AS Apply_GL_Posting_Date,
                  D.ORTRXAMT AS Applied_To_Doc_Total, D.DINVPDOF AS Applied_To_Date_Paid_Off, D.CURTRXAM AS Applied_To_Doc_Unapplied_Amount, D.CSPORNBR AS Customer_PO_Number, D.SLPRSNID
FROM        (SELECT     CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
                                     CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN
                                      'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied
                   FROM        dbo.RM20101
                   WHERE     (RMDTYPAL > 6) AND (VOIDSTTS = 0)
                   UNION
                   SELECT     CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
                                     CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN
                                      'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied
                   FROM        dbo.RM30101
                   WHERE     (RMDTYPAL > 6) AND (VOIDSTTS = 0)) AS T INNER JOIN
                  dbo.RM00101 AS CM ON T.CUSTNMBR = CM.CUSTNMBR INNER JOIN
                      (SELECT     tO1.CUSTNMBR, tO2.APTODCTY, tO2.APTODCNM, tO2.APFRDCTY, tO2.APFRDCNM,
                                         CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END AS debitType, tO2.APPTOAMT,
                                         tO2.ApplyToGLPostDate, tO2.APTODCDT, tO2.DISTKNAM, tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT
                       FROM        dbo.RM20201 AS tO2 INNER JOIN
                                         dbo.RM20101 AS tO1 ON tO2.APTODCTY = tO1.RMDTYPAL AND tO2.APTODCNM = tO1.DOCNUMBR
                       UNION
                       SELECT     tH1.CUSTNMBR, tH2.APTODCTY, tH2.APTODCNM, tH2.APFRDCTY, tH2.APFRDCNM,
                                         CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END AS debitType, tH2.APPTOAMT,
                                         tH2.ApplyToGLPostDate, tH2.APTODCDT, tH2.DISTKNAM, tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
                       FROM        dbo.RM30201 AS tH2 INNER JOIN
                                         dbo.RM30101 AS tH1 ON tH2.APTODCTY = tH1.RMDTYPAL AND tH2.APTODCNM = tH1.DOCNUMBR) AS A ON A.APFRDCTY = T.RMDTYPAL AND A.APFRDCNM = T.DOCNUMBR INNER JOIN
                      (SELECT     RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM, CSPORNBR, SLPRSNID
                       FROM        dbo.RM20101 AS RM20101_1
                       UNION
                       SELECT     RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, 0 AS CURTRXAM, CSPORNBR, SLPRSNID
                       FROM        dbo.RM30101 AS RM30101_1) AS D ON A.APTODCTY = D.RMDTYPAL AND A.APTODCNM = D.DOCNUMBR

GO
/****** Object:  View [dbo].[BI_AR_Pmt_SR]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Pmt_SR]
AS
SELECT DISTINCT Document_Number, MAX(SLPRSNID) AS PmtSR
FROM        dbo.BI_AR_Apply_Detail
GROUP BY Document_Number

GO
/****** Object:  View [dbo].[BI_AR_CashRcts_SOP_Unposted]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_CashRcts_SOP_Unposted]
AS
SELECT        dbo.SOP10103.SOPTYPE, dbo.SOP10103.SOPNUMBE, dbo.SOP10103.SEQNUMBR, dbo.SOP10103.PYMTTYPE, dbo.SOP10103.DOCNUMBR, dbo.SOP10103.RMDTYPAL, dbo.SOP10103.CHEKBKID,
                         dbo.SOP10103.CHEKNMBR, dbo.SOP10103.CARDNAME, dbo.SOP10103.RCTNCCRD, dbo.SOP10103.AUTHCODE, dbo.SOP10103.AMNTPAID, dbo.SOP10103.OAMTPAID, dbo.SOP10103.AMNTREMA,
                         dbo.SOP10103.OAMNTREM, dbo.SOP10103.DOCDATE, dbo.SOP10103.EXPNDATE, dbo.SOP10103.CURNCYID, dbo.SOP10103.CURRNIDX, dbo.SOP10103.TRXSORCE, dbo.SOP10103.DEPSTATS,
                         dbo.SOP10103.DELETE1, dbo.SOP10103.GLPOSTDT, dbo.SOP10103.CASHINDEX, dbo.SOP10103.DEPINDEX, dbo.SOP10103.EFTFLAG, dbo.SOP10103.DEX_ROW_ID, dbo.SOP10100.CUSTNMBR,
                         dbo.SOP10100.BACHNUMB, dbo.SOP10100.SLPRSNID, dbo.SOP10100.VOIDSTTS
FROM            dbo.SOP10103 INNER JOIN
                         dbo.SOP10100 ON dbo.SOP10103.SOPNUMBE = dbo.SOP10100.SOPNUMBE AND dbo.SOP10103.SOPTYPE = dbo.SOP10100.SOPTYPE

GO
/****** Object:  View [dbo].[BI_AR_Unposted]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Unposted]
AS
SELECT        CUSTNMBR, DOCDATE, GLPOSTDT, RM_Doc_Type, BACHNUMB, docTypeNum, DOCNUMBR, ORTRXAMT, Curtrxam, amtAppl, RMDTYPAL, mrkdnamt, Factor, ORTRXAMT + mrkdnamt AS GrossAmt, SLPRSNID,
                         TAXAMNT, CURNCYID, voidstts, CHEKBKID AS TrxCheckbook
FROM            (SELECT        CUSTNMBR, DOCDATE, GLPOSTDT,
                                                    CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
                                                    CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' +
                                                     CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT,
                                                    ORTRXAMT AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS mrkdnamt, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
                                                    '' AS SLPRSNID, 0 AS TAXAMNT, CURNCYID, 0 AS voidstts, CHEKBKID
                          FROM            dbo.RM10201
                          UNION
                          SELECT        CUSTNMBR, DOCDATE, GLPOSTDT,
                                                   CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
                                                   CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' END AS docTypeNum, DOCNUMBR, DOCAMNT,
                                                   DOCAMNT AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS mrkdnamt, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
                                                   SLPRSNID, TAXAMNT, CURNCYID, 0 AS voidstts, '' AS Chekbkid
                          FROM            dbo.RM10301
                          UNION
                          SELECT        CUSTNMBR, DOCDATE, GLPOSTDT,
                                                   CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type, BACHNUMB,
                                                   CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE PYMTTYPE WHEN 5 THEN 'Payment - Check ' +
                                                    CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 4 THEN 'Payment - Cash' WHEN 6 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, AMNTPAID,
                                                   AMNTPAID AS Curtrxam, 0 AS amtAppl, RMDTYPAL, 0 AS Expr1, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor, SLPRSNID,
                                                   0 AS Taxamnt, CURNCYID, VOIDSTTS, CHEKBKID
                          FROM            dbo.BI_AR_CashRcts_SOP_Unposted
                          UNION
                          SELECT        CUSTNMBR, DOCDATE, GLPOSTDT, CASE SOPTYPE WHEN 3 THEN 'Sale/Invoice' WHEN 4 THEN 'Return' END AS stype, BACHNUMB,
                                                   CASE SOPTYPE WHEN 3 THEN 'Sale/Invoice' WHEN 4 THEN 'Return' END AS Expr1, SOPNUMBE, DOCAMNT, DOCAMNT AS Curtrxam, 0 AS amtAppl, SOPTYPE, MRKDNAMT,
                                                   CASE SOPTYPE WHEN 3 THEN 1 WHEN 4 THEN - 1 END AS factor, SLPRSNID, TAXAMNT, CURNCYID, VOIDSTTS, '' AS Chekbkid
                          FROM            dbo.SOP10100
                          WHERE        (SOPTYPE > 2)) AS UnpostedAr

GO
/****** Object:  View [dbo].[BI_AR_Posted]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_Posted]
AS
SELECT        T.CUSTNMBR AS Customer_ID, T.DOCDATE AS Document_Date, T.GLPOSTDT AS GL_Posting_Date,
                         CASE T .RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type,
                         T.BACHNUMB AS Payment_Batch, T.docTypeNum AS Document_Type_and_Number, T.DOCNUMBR AS Document_Number, T.ORTRXAMT AS Original_Trx_Amount, T.CURTRXAM AS Current_Trx_Amount,
                         T.amountApplied AS Total_Applied_Amount, T.RMDTYPAL, SOPSR.MRKDNAMT, CASE RMDTYPAL WHEN 1 THEN 1 WHEN 3 THEN 1 WHEN 7 THEN - 1 WHEN 8 THEN - 1 WHEN 9 THEN 1 END AS Factor,
                         T.ORTRXAMT + SOPSR.MRKDNAMT AS GrossAmt, SOPSR.SLPRSNID, SOPSR.TAXAMNT, SOPSR.CURNCYID, T.VOIDSTTS, T.checkbkid AS TrxCheckbook
FROM            (SELECT        CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
                                                    CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' +
                                                     CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT,
                                                    CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied, VOIDSTTS, MSCSCHID AS checkbkid
                          FROM            dbo.RM20101
                          WHERE        (VOIDSTTS = 0)
                          UNION
                          SELECT        CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
                                                   CASE RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' +
                                                    CASE CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum, DOCNUMBR, ORTRXAMT,
                                                   CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM AS amountApplied, VOIDSTTS, MSCSCHID AS checkbkid
                          FROM            dbo.RM30101
                          WHERE        (VOIDSTTS = 0)) AS T LEFT OUTER JOIN
                             (SELECT        SOPNUMBE, SOPTYPE, MRKDNAMT, SLPRSNID, TAXAMNT, CURNCYID
                               FROM            dbo.SOP30200
                               WHERE        (SOPTYPE IN (3, 4))) AS SOPSR ON T.DOCNUMBR = SOPSR.SOPNUMBE

GO
/****** Object:  View [dbo].[BI_SOP_Comments]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_SOP_Comments]
AS
SELECT        dbo.SOP10106.SOPTYPE, dbo.SOP10106.SOPNUMBE, dbo.SOP10106.CMMTTEXT AS Comments, SOPCMTID.COMMNTID AS CommentID
FROM            dbo.SOP10106 INNER JOIN
                             (SELECT        SOPTYPE, SOPNUMBE, COMMNTID
                               FROM            dbo.SOP30200) AS SOPCMTID ON dbo.SOP10106.SOPNUMBE = SOPCMTID.SOPNUMBE AND dbo.SOP10106.SOPTYPE = SOPCMTID.SOPTYPE
WHERE        (dbo.SOP10106.SOPTYPE >= 3)


GO
/****** Object:  View [dbo].[BI_AR_ALL]    Script Date: 22/05/2018 09:45:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_ALL]
AS
SELECT        AllAR.Status, AllAR.Customer_ID, AllAR.Document_Date, AllAR.GL_Posting_Date, AllAR.RM_Doc_Type, AllAR.Payment_Batch, AllAR.Document_Type_and_Number, AllAR.Document_Number,
                         AllAR.Original_Trx_Amount * AllAR.Factor AS Original_Trx_Amount, AllAR.Current_Trx_Amount, AllAR.Total_Applied_Amount, AllAR.RMDTYPAL, ISNULL(AllAR.MRKDNAMT, 0) * AllAR.Factor AS Markdown,
                         AllAR.Factor, ISNULL(AllAR.GrossAmt, 0) * AllAR.Factor AS GrossAmt, dbo.BI_SOP_Comments.CommentID, dbo.BI_SOP_Comments.Comments, AllAR.SLPRSNID AS TrxSr, dbo.BI_AR_Pmt_SR.PmtSR,
                         dbo.RM00101.SLPRSNID AS CusSrID, CASE WHEN (pmtsr IS NULL OR
                         pmtsr = '') THEN CASE WHEN allar.slprsnid IS NULL THEN rm00101.slprsnid ELSE allar.slprsnid END ELSE pmtsr END AS FinalSR, ISNULL(AllAR.TAXAMNT, 0) * AllAR.Factor AS Taxamnt,
                         dbo.RM00101.CUSTCLAS AS CustomerClass, dbo.RM00101.CUSTNAME AS CustomerName, ISNULL(AllAR.CURNCYID, 'TTD') AS Currency, (AllAR.Original_Trx_Amount - ISNULL(AllAR.TAXAMNT, 0))
                         * AllAR.Factor AS OriginalAmtNoVAT, CASE WHEN (voidstts = 0 OR
                         voidstts IS NULL) THEN 'Normal' ELSE 'Voided' END AS [Void Status], AllAR.TrxCheckbook, dbo.RM00101.CHEKBKID AS CusCheckbook
FROM            (SELECT        'Posted' AS Status, Customer_ID, Document_Date, GL_Posting_Date, RM_Doc_Type, Payment_Batch, Document_Type_and_Number, Document_Number, Original_Trx_Amount, Current_Trx_Amount,
                                                    Total_Applied_Amount, RMDTYPAL, MRKDNAMT, Factor, GrossAmt, SLPRSNID, TAXAMNT, CURNCYID, VOIDSTTS, TrxCheckbook
                          FROM            dbo.BI_AR_Posted
                          UNION
                          SELECT        'Unposted' AS Status, CUSTNMBR, DOCDATE, GLPOSTDT, RM_Doc_Type, BACHNUMB, docTypeNum, DOCNUMBR, ORTRXAMT, Curtrxam, amtAppl, RMDTYPAL, mrkdnamt, Factor, GrossAmt,
                                                   SLPRSNID, TAXAMNT, CURNCYID, voidstts, TrxCheckbook
                          FROM            dbo.BI_AR_Unposted) AS AllAR LEFT OUTER JOIN
                         dbo.RM00101 ON AllAR.Customer_ID = dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
                         dbo.BI_AR_Pmt_SR ON AllAR.Document_Number = dbo.BI_AR_Pmt_SR.Document_Number LEFT OUTER JOIN
                         dbo.BI_SOP_Comments ON AllAR.Document_Number = dbo.BI_SOP_Comments.SOPNUMBE

GO

Ls NAV - Mix and Match Benefit with Extra Print does not print

The item you are using for the extra print needs to allow "print if 0 price"

Dynamics GP - SSRS - Historical Aged Trial Balance Report does not Match GP - Customers are missing

This is happening because the default filters used when you leave them blank in SSRS are "þþþþþþþþþþþþþþþ" in the seeRMHATBssrsWrapper stored procedure.

These default filters are filtering the data incorrectly.
The stored procedure should be updated, and  "þþþþþþþþþþþþþþþ" replaced with "zzzzzzzzzzzzzzz"

Dynamics GP - seeRMHATBSRSWrapper - Output SSRS Historical AR TB to Table - Fixed Customer Filter and MC, Fixed sums, No AA

--Calculates HATB in Base currency and MC. Use each view accordingly.
/****** Object:  StoredProcedure [dbo].[BI_seermHATBSRSWrapper_base]    Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE procedure [dbo].[BI_seermHATBSRSWrapper_base]   @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 = 'zzzzzzzzzz'  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_MC
 Select RMHATB.* Into BIT_RMHATB_MC 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 

--Debug temp tables
/*
Drop Table BI_TEMP_RMHATBCU
Drop Table BI_TEMP_RMHATBDO
Drop Table BI_TEMP_RMHATBAP

 select * into BI_TEMP_RMHATBCU from #rmhatbcu
 select * into BI_TEMP_RMHATBDO from #rmhatbdo
 select * into BI_TEMP_RMHATBAP from #rmhatbap
 */

 --WHILE  (SELECT COUNT(*) FROM   (SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB_MC  WHERE ORTRXAMT<>0 GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0  BEGIN UPDATE BIT_RMHATB_MC SET  AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0  FROM BIT_RMHATB_MC  JOIN  ( SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB_MC  GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT  HAVING COUNT(*)>1 ) PARTIALLY_APPLIED  ON  PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB_MC.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB_MC.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB_MC.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB_MC.APFRDCNM  END 
--Select RMHATB.* Into BIT_RMHATB_RAW 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 
--SELECT * FROM BI_RMHATB4_MC 
SELECT * FROM BI_RMHATB4_NOAA_BASE







GO
/****** Object:  StoredProcedure [dbo].[BI_seermHATBSRSWrapper_mc]    Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO




 CREATE procedure [dbo].[BI_seermHATBSRSWrapper_mc]   @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 = 'zzzzzzzzzz'  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_MC
 Select RMHATB.* Into BIT_RMHATB_MC 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 

--Debug temp tables
/*
Drop Table BI_TEMP_RMHATBCU
Drop Table BI_TEMP_RMHATBDO
Drop Table BI_TEMP_RMHATBAP

 select * into BI_TEMP_RMHATBCU from #rmhatbcu
 select * into BI_TEMP_RMHATBDO from #rmhatbdo
 select * into BI_TEMP_RMHATBAP from #rmhatbap
*/

 --WHILE  (SELECT COUNT(*) FROM   (SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB_MC  WHERE ORTRXAMT<>0 GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0  BEGIN UPDATE BIT_RMHATB_MC SET  AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0  FROM BIT_RMHATB_MC  JOIN  ( SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB_MC  GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT  HAVING COUNT(*)>1 ) PARTIALLY_APPLIED  ON  PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB_MC.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB_MC.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB_MC.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB_MC.APFRDCNM  END 
--Select RMHATB.* Into BIT_RMHATB_RAW 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 
--SELECT * FROM BI_RMHATB4_MC 
SELECT * FROM BI_RMHATB4_NOAA_MC






GO
/****** Object:  Table [dbo].[BI_TEMP_RMHATBAP]    Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_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
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BI_TEMP_RMHATBCU]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_RMHATBCU](
[CUSTNMBR] [char](15) NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[AGNGDATE] [datetime] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BI_TEMP_RMHATBDO]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_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
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BIT_RMHATB_MC]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_RMHATB_MC](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CUSTNAME] [char](65) NOT NULL,
[BALNCTYP] [smallint] NOT NULL,
[USERDEF1] [char](21) NOT NULL,
[CNTCPRSN] [char](61) NOT NULL,
[PHONE1] [char](21) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[SALSTERR] [char](15) NOT NULL,
[PYMTRMID] [char](21) NOT NULL,
[CRLMTAMT] [numeric](19, 5) NOT NULL,
[CRLMTPER] [smallint] NOT NULL,
[CRLMTPAM] [numeric](19, 5) NOT NULL,
[CRLMTTYP] [smallint] NOT NULL,
[CUSTCLAS] [char](15) NOT NULL,
[SHRTNAME] [char](15) NOT NULL,
[ZIP] [char](11) NOT NULL,
[STATE] [char](29) NOT NULL,
[CUDSCRIPTN] [char](31) NOT NULL,
[AGNGDATE] [datetime] NOT NULL,
[CHCUMNUM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[DCURNCYID] [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,
[DDISTKNAM] [numeric](19, 5) NOT NULL,
[DWROFAMNT] [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,
[DCURRNIDX] [smallint] NOT NULL,
[DXCHGRATE] [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,
[DORDISTKN] [numeric](19, 5) NOT NULL,
[DORWROFAM] [numeric](19, 5) NOT NULL,
[DDENXRATE] [numeric](19, 7) NOT NULL,
[DMCTRXSTT] [smallint] NOT NULL,
[Aging_Period_Amount] [numeric](19, 5) NOT NULL,
[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,
[ACURNCYID] [char](15) NOT NULL,
[DATE1] [datetime] NOT NULL,
[POSTED] [tinyint] NOT NULL,
[ADISTKNAM] [numeric](19, 5) NOT NULL,
[AWROFAMNT] [numeric](19, 5) NOT NULL,
[PPSAMDED] [numeric](19, 5) NOT NULL,
[GSTDSAMT] [numeric](19, 5) NOT NULL,
[ACURRNIDX] [smallint] NOT NULL,
[AXCHGRATE] [numeric](19, 7) NOT NULL,
[RLGANLOS] [numeric](19, 5) NOT NULL,
[ORAPTOAM] [numeric](19, 5) NOT NULL,
[AORDISTKN] [numeric](19, 5) NOT NULL,
[AORWROFAM] [numeric](19, 5) NOT NULL,
[ADENXRATE] [numeric](19, 7) NOT NULL,
[AMCTRXSTT] [smallint] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[BI_RMHATB4_NOAA]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA]
AS
SELECT     dbo.BIT_RMHATB_MC.APPLY_AMOUNT, dbo.BIT_RMHATB_MC.AGING_AMOUNT, dbo.BIT_RMHATB_MC.CUSTNMBR, dbo.BIT_RMHATB_MC.CUSTNAME,
                      dbo.BIT_RMHATB_MC.BALNCTYP, dbo.BIT_RMHATB_MC.USERDEF1, dbo.BIT_RMHATB_MC.CNTCPRSN, dbo.BIT_RMHATB_MC.PHONE1,
                      dbo.BIT_RMHATB_MC.SLPRSNID, dbo.BIT_RMHATB_MC.SALSTERR, dbo.BIT_RMHATB_MC.PYMTRMID, dbo.BIT_RMHATB_MC.CRLMTAMT,
                      dbo.BIT_RMHATB_MC.CRLMTPER, dbo.BIT_RMHATB_MC.CRLMTPAM, dbo.BIT_RMHATB_MC.CRLMTTYP, dbo.BIT_RMHATB_MC.CUSTCLAS,
                      dbo.BIT_RMHATB_MC.SHRTNAME, dbo.BIT_RMHATB_MC.ZIP, dbo.BIT_RMHATB_MC.STATE, dbo.BIT_RMHATB_MC.CUDSCRIPTN,
                      dbo.BIT_RMHATB_MC.AGNGDATE, dbo.BIT_RMHATB_MC.CHCUMNUM, dbo.BIT_RMHATB_MC.DOCNUMBR, dbo.BIT_RMHATB_MC.RMDTYPAL,
                      dbo.BIT_RMHATB_MC.DSCRIPTN, dbo.BIT_RMHATB_MC.DCURNCYID, dbo.BIT_RMHATB_MC.ORTRXAMT, dbo.BIT_RMHATB_MC.AGNGBUKT,
                      dbo.BIT_RMHATB_MC.CASHAMNT, dbo.BIT_RMHATB_MC.COMDLRAM, dbo.BIT_RMHATB_MC.SLSAMNT, dbo.BIT_RMHATB_MC.COSTAMNT,
                      dbo.BIT_RMHATB_MC.FRTAMNT, dbo.BIT_RMHATB_MC.MISCAMNT, dbo.BIT_RMHATB_MC.TAXAMNT, dbo.BIT_RMHATB_MC.DISAVAMT,
                      dbo.BIT_RMHATB_MC.DDISTKNAM, dbo.BIT_RMHATB_MC.DWROFAMNT, dbo.BIT_RMHATB_MC.TRXDSCRN, dbo.BIT_RMHATB_MC.DOCABREV,
                      dbo.BIT_RMHATB_MC.CHEKNMBR, dbo.BIT_RMHATB_MC.DOCDATE, dbo.BIT_RMHATB_MC.DUEDATE, dbo.BIT_RMHATB_MC.GLPOSTDT,
                      dbo.BIT_RMHATB_MC.DISCDATE, dbo.BIT_RMHATB_MC.POSTDATE, dbo.BIT_RMHATB_MC.DINVPDOF, dbo.BIT_RMHATB_MC.DCURRNIDX,
                      dbo.BIT_RMHATB_MC.DXCHGRATE, dbo.BIT_RMHATB_MC.ORCASAMT, dbo.BIT_RMHATB_MC.ORSLSAMT, dbo.BIT_RMHATB_MC.ORCSTAMT,
                      dbo.BIT_RMHATB_MC.ORDAVAMT, dbo.BIT_RMHATB_MC.ORFRTAMT, dbo.BIT_RMHATB_MC.ORMISCAMT, dbo.BIT_RMHATB_MC.ORTAXAMT,
                      dbo.BIT_RMHATB_MC.ORCTRXAM, dbo.BIT_RMHATB_MC.ORORGTRX, dbo.BIT_RMHATB_MC.DORDISTKN, dbo.BIT_RMHATB_MC.DORWROFAM,
                      dbo.BIT_RMHATB_MC.DDENXRATE, dbo.BIT_RMHATB_MC.DMCTRXSTT, dbo.BIT_RMHATB_MC.Aging_Period_Amount, dbo.BIT_RMHATB_MC.APFRDCNM,
                      dbo.BIT_RMHATB_MC.APFRDCTY, dbo.BIT_RMHATB_MC.FROMCURR, dbo.BIT_RMHATB_MC.APTODCNM, dbo.BIT_RMHATB_MC.APTODCTY,
                      dbo.BIT_RMHATB_MC.APPTOAMT, dbo.BIT_RMHATB_MC.ACURNCYID, dbo.BIT_RMHATB_MC.DATE1, dbo.BIT_RMHATB_MC.POSTED,
                      dbo.BIT_RMHATB_MC.ADISTKNAM, dbo.BIT_RMHATB_MC.AWROFAMNT, dbo.BIT_RMHATB_MC.PPSAMDED, dbo.BIT_RMHATB_MC.GSTDSAMT,
                      dbo.BIT_RMHATB_MC.ACURRNIDX, dbo.BIT_RMHATB_MC.AXCHGRATE, dbo.BIT_RMHATB_MC.RLGANLOS, dbo.BIT_RMHATB_MC.ORAPTOAM,
                      dbo.BIT_RMHATB_MC.AORDISTKN, dbo.BIT_RMHATB_MC.AORWROFAM, dbo.BIT_RMHATB_MC.ADENXRATE, dbo.BIT_RMHATB_MC.AMCTRXSTT,
                      dbo.RM00201.CLASSID, dbo.RM00201.CLASDSCR, CASE WHEN MC020102.CURNCYID IS NULL THEN 'TTD' ELSE MC020102.CURNCYID END AS Currency,
                      dbo.MC020102.XCHGRATE, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.AGING_AMOUNT / dbo.MC020102.XCHGRATE, 2)
                      ELSE Aging_Amount END AS Aging_Amount_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Apply_AMOUNT / dbo.MC020102.XCHGRATE,
                      2) ELSE Apply_Amount END AS Apply_Amount_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Curtrxam / dbo.MC020102.XCHGRATE, 2)
                      ELSE Curtrxam END AS Curtrxam_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Ortrxamt / dbo.MC020102.XCHGRATE, 2)
                      ELSE Ortrxamt END AS Ortrxamt_MC, dbo.BIT_RMHATB_MC.CURTRXAM, CAST(RTRIM(dbo.BIT_RMHATB_MC.APFRDCNM) AS varchar)
                      + '-' + CAST(dbo.BIT_RMHATB_MC.APFRDCTY AS varchar) AS ApfrDocNmTy
FROM         dbo.MC020102 RIGHT OUTER JOIN
                      dbo.BIT_RMHATB_MC ON dbo.MC020102.RMDTYPAL = dbo.BIT_RMHATB_MC.RMDTYPAL AND dbo.MC020102.CUSTNMBR = dbo.BIT_RMHATB_MC.CUSTNMBR AND
                      dbo.MC020102.DOCNUMBR = dbo.BIT_RMHATB_MC.DOCNUMBR LEFT OUTER JOIN
                      dbo.RM00201 RIGHT OUTER JOIN
                      dbo.RM00101 ON dbo.RM00201.CLASSID = dbo.RM00101.CUSTCLAS ON dbo.BIT_RMHATB_MC.CUSTNMBR = dbo.RM00101.CUSTNMBR

GO
/****** Object:  View [dbo].[BI_RMHATB4_NOAA_BASE]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA_BASE]
AS
SELECT     dbo.BI_RMHATB4_NOAA.APPLY_AMOUNT, dbo.BI_RMHATB4_NOAA.AGING_AMOUNT, dbo.BI_RMHATB4_NOAA.CUSTNMBR, dbo.BI_RMHATB4_NOAA.CUSTNAME,
                      dbo.BI_RMHATB4_NOAA.BALNCTYP, dbo.BI_RMHATB4_NOAA.USERDEF1, dbo.BI_RMHATB4_NOAA.CNTCPRSN, dbo.BI_RMHATB4_NOAA.PHONE1,
                      dbo.BI_RMHATB4_NOAA.SLPRSNID, dbo.BI_RMHATB4_NOAA.SALSTERR, dbo.BI_RMHATB4_NOAA.PYMTRMID, dbo.BI_RMHATB4_NOAA.CRLMTAMT,
                      dbo.BI_RMHATB4_NOAA.CRLMTPER, dbo.BI_RMHATB4_NOAA.CRLMTPAM, dbo.BI_RMHATB4_NOAA.CRLMTTYP, dbo.BI_RMHATB4_NOAA.CUSTCLAS,
                      dbo.BI_RMHATB4_NOAA.SHRTNAME, dbo.BI_RMHATB4_NOAA.ZIP, dbo.BI_RMHATB4_NOAA.STATE, dbo.BI_RMHATB4_NOAA.CUDSCRIPTN,
                      dbo.BI_RMHATB4_NOAA.AGNGDATE, dbo.BI_RMHATB4_NOAA.CHCUMNUM, dbo.BI_RMHATB4_NOAA.DOCNUMBR, dbo.BI_RMHATB4_NOAA.RMDTYPAL,
                      dbo.BI_RMHATB4_NOAA.DSCRIPTN, dbo.BI_RMHATB4_NOAA.DCURNCYID, dbo.BI_RMHATB4_NOAA.ORTRXAMT AS Ortrxamt_Loc,
                      dbo.BI_RMHATB4_NOAA.CURTRXAM AS Curtrxam_Loc, dbo.BI_RMHATB4_NOAA.AGNGBUKT, dbo.BI_RMHATB4_NOAA.CASHAMNT,
                      dbo.BI_RMHATB4_NOAA.COMDLRAM, dbo.BI_RMHATB4_NOAA.SLSAMNT, dbo.BI_RMHATB4_NOAA.COSTAMNT, dbo.BI_RMHATB4_NOAA.FRTAMNT,
                      dbo.BI_RMHATB4_NOAA.MISCAMNT, dbo.BI_RMHATB4_NOAA.TAXAMNT, dbo.BI_RMHATB4_NOAA.DISAVAMT, dbo.BI_RMHATB4_NOAA.DDISTKNAM,
                      dbo.BI_RMHATB4_NOAA.DWROFAMNT, dbo.BI_RMHATB4_NOAA.TRXDSCRN, dbo.BI_RMHATB4_NOAA.DOCABREV, dbo.BI_RMHATB4_NOAA.CHEKNMBR,
                      dbo.BI_RMHATB4_NOAA.DOCDATE, dbo.BI_RMHATB4_NOAA.DUEDATE, dbo.BI_RMHATB4_NOAA.GLPOSTDT, dbo.BI_RMHATB4_NOAA.DISCDATE,
                      dbo.BI_RMHATB4_NOAA.POSTDATE, dbo.BI_RMHATB4_NOAA.DINVPDOF, dbo.BI_RMHATB4_NOAA.DCURRNIDX, dbo.BI_RMHATB4_NOAA.DXCHGRATE,
                      dbo.BI_RMHATB4_NOAA.ORCASAMT, dbo.BI_RMHATB4_NOAA.ORSLSAMT, dbo.BI_RMHATB4_NOAA.ORCSTAMT, dbo.BI_RMHATB4_NOAA.ORDAVAMT,
                      dbo.BI_RMHATB4_NOAA.ORFRTAMT, dbo.BI_RMHATB4_NOAA.ORMISCAMT, dbo.BI_RMHATB4_NOAA.ORTAXAMT, dbo.BI_RMHATB4_NOAA.ORCTRXAM,
                      dbo.BI_RMHATB4_NOAA.ORORGTRX, dbo.BI_RMHATB4_NOAA.DORDISTKN, dbo.BI_RMHATB4_NOAA.DORWROFAM, dbo.BI_RMHATB4_NOAA.DDENXRATE,
                      dbo.BI_RMHATB4_NOAA.DMCTRXSTT, dbo.BI_RMHATB4_NOAA.Aging_Period_Amount, dbo.BI_RMHATB4_NOAA.APFRDCNM, dbo.BI_RMHATB4_NOAA.APFRDCTY,
                      dbo.BI_RMHATB4_NOAA.FROMCURR, dbo.BI_RMHATB4_NOAA.APTODCNM, dbo.BI_RMHATB4_NOAA.APTODCTY, dbo.BI_RMHATB4_NOAA.APPTOAMT,
                      dbo.BI_RMHATB4_NOAA.ACURNCYID, dbo.BI_RMHATB4_NOAA.DATE1, dbo.BI_RMHATB4_NOAA.POSTED, dbo.BI_RMHATB4_NOAA.ADISTKNAM,
                      dbo.BI_RMHATB4_NOAA.AWROFAMNT, dbo.BI_RMHATB4_NOAA.PPSAMDED, dbo.BI_RMHATB4_NOAA.GSTDSAMT, dbo.BI_RMHATB4_NOAA.ACURRNIDX,
                      dbo.BI_RMHATB4_NOAA.AXCHGRATE, dbo.BI_RMHATB4_NOAA.RLGANLOS, dbo.BI_RMHATB4_NOAA.ORAPTOAM, dbo.BI_RMHATB4_NOAA.AORDISTKN,
                      dbo.BI_RMHATB4_NOAA.AORWROFAM, dbo.BI_RMHATB4_NOAA.ADENXRATE, dbo.BI_RMHATB4_NOAA.AMCTRXSTT, dbo.BI_RMHATB4_NOAA.CLASSID,
                      dbo.BI_RMHATB4_NOAA.CLASDSCR, dbo.BI_RMHATB4_NOAA.Currency, dbo.BI_RMHATB4_NOAA.XCHGRATE, dbo.BI_RMHATB4_NOAA.Aging_Amount_MC,
                      dbo.BI_RMHATB4_NOAA.Apply_Amount_MC, CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Curtrxam END AS Curtrxam,
                      CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Ortrxamt END AS Ortrxamt, FirstPay.FPDocNum, FirstPay.FPCurTrxamMC,
                      FirstPay.FPCurtrxam, FirstPay.FPAgingAmt, FirstPay.MaxDocNmTy, dbo.BI_RMHATB4_NOAA.ApfrDocNmTy
FROM         dbo.BI_RMHATB4_NOAA LEFT OUTER JOIN
                          (SELECT     DOCNUMBR AS FPDocNum, MAX(ApfrDocNmTy) AS MaxDocNmTy, Curtrxam_MC AS FPCurTrxamMC, CURTRXAM AS FPCurtrxam,
                                                   Aging_Amount_MC AS FPAgingAmt
                            FROM          dbo.BI_RMHATB4_NOAA AS BI_RMHATB4_NOAA_1
                            WHERE      (APFRDCTY > 0)
                            GROUP BY DOCNUMBR, Curtrxam_MC, CURTRXAM, Aging_Amount_MC) AS FirstPay ON dbo.BI_RMHATB4_NOAA.DOCNUMBR = FirstPay.FPDocNum AND
                      dbo.BI_RMHATB4_NOAA.ApfrDocNmTy = FirstPay.MaxDocNmTy

GO
/****** Object:  View [dbo].[BI_RMHATB4_NOAA_MC]    Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA_MC]
AS
SELECT     dbo.BI_RMHATB4_NOAA.APPLY_AMOUNT AS APPLY_AMOUNT_LOC, dbo.BI_RMHATB4_NOAA.AGING_AMOUNT AS AGING_AMOUNT_LOC,
                      dbo.BI_RMHATB4_NOAA.CUSTNMBR, dbo.BI_RMHATB4_NOAA.CUSTNAME, dbo.BI_RMHATB4_NOAA.BALNCTYP, dbo.BI_RMHATB4_NOAA.USERDEF1,
                      dbo.BI_RMHATB4_NOAA.CNTCPRSN, dbo.BI_RMHATB4_NOAA.PHONE1, dbo.BI_RMHATB4_NOAA.SLPRSNID, dbo.BI_RMHATB4_NOAA.SALSTERR,
                      dbo.BI_RMHATB4_NOAA.PYMTRMID, dbo.BI_RMHATB4_NOAA.CRLMTAMT, dbo.BI_RMHATB4_NOAA.CRLMTPER, dbo.BI_RMHATB4_NOAA.CRLMTPAM,
                      dbo.BI_RMHATB4_NOAA.CRLMTTYP, dbo.BI_RMHATB4_NOAA.CUSTCLAS, dbo.BI_RMHATB4_NOAA.SHRTNAME, dbo.BI_RMHATB4_NOAA.ZIP,
                      dbo.BI_RMHATB4_NOAA.STATE, dbo.BI_RMHATB4_NOAA.CUDSCRIPTN, dbo.BI_RMHATB4_NOAA.AGNGDATE, dbo.BI_RMHATB4_NOAA.CHCUMNUM,
                      dbo.BI_RMHATB4_NOAA.DOCNUMBR, dbo.BI_RMHATB4_NOAA.RMDTYPAL, dbo.BI_RMHATB4_NOAA.DSCRIPTN, dbo.BI_RMHATB4_NOAA.DCURNCYID,
                      dbo.BI_RMHATB4_NOAA.ORTRXAMT AS Ortrxamt_Loc, dbo.BI_RMHATB4_NOAA.CURTRXAM AS Curtrxam_Loc, dbo.BI_RMHATB4_NOAA.AGNGBUKT,
                      dbo.BI_RMHATB4_NOAA.CASHAMNT, dbo.BI_RMHATB4_NOAA.COMDLRAM, dbo.BI_RMHATB4_NOAA.SLSAMNT, dbo.BI_RMHATB4_NOAA.COSTAMNT,
                      dbo.BI_RMHATB4_NOAA.FRTAMNT, dbo.BI_RMHATB4_NOAA.MISCAMNT, dbo.BI_RMHATB4_NOAA.TAXAMNT, dbo.BI_RMHATB4_NOAA.DISAVAMT,
                      dbo.BI_RMHATB4_NOAA.DDISTKNAM, dbo.BI_RMHATB4_NOAA.DWROFAMNT, dbo.BI_RMHATB4_NOAA.TRXDSCRN, dbo.BI_RMHATB4_NOAA.DOCABREV,
                      dbo.BI_RMHATB4_NOAA.CHEKNMBR, dbo.BI_RMHATB4_NOAA.DOCDATE, dbo.BI_RMHATB4_NOAA.DUEDATE, dbo.BI_RMHATB4_NOAA.GLPOSTDT,
                      dbo.BI_RMHATB4_NOAA.DISCDATE, dbo.BI_RMHATB4_NOAA.POSTDATE, dbo.BI_RMHATB4_NOAA.DINVPDOF, dbo.BI_RMHATB4_NOAA.DCURRNIDX,
                      dbo.BI_RMHATB4_NOAA.DXCHGRATE, dbo.BI_RMHATB4_NOAA.ORCASAMT, dbo.BI_RMHATB4_NOAA.ORSLSAMT, dbo.BI_RMHATB4_NOAA.ORCSTAMT,
                      dbo.BI_RMHATB4_NOAA.ORDAVAMT, dbo.BI_RMHATB4_NOAA.ORFRTAMT, dbo.BI_RMHATB4_NOAA.ORMISCAMT, dbo.BI_RMHATB4_NOAA.ORTAXAMT,
                      dbo.BI_RMHATB4_NOAA.ORCTRXAM, dbo.BI_RMHATB4_NOAA.ORORGTRX, dbo.BI_RMHATB4_NOAA.DORDISTKN, dbo.BI_RMHATB4_NOAA.DORWROFAM,
                      dbo.BI_RMHATB4_NOAA.DDENXRATE, dbo.BI_RMHATB4_NOAA.DMCTRXSTT, dbo.BI_RMHATB4_NOAA.Aging_Period_Amount, dbo.BI_RMHATB4_NOAA.APFRDCNM,
                      dbo.BI_RMHATB4_NOAA.APFRDCTY, dbo.BI_RMHATB4_NOAA.FROMCURR, dbo.BI_RMHATB4_NOAA.APTODCNM, dbo.BI_RMHATB4_NOAA.APTODCTY,
                      dbo.BI_RMHATB4_NOAA.APPTOAMT, dbo.BI_RMHATB4_NOAA.ACURNCYID, dbo.BI_RMHATB4_NOAA.DATE1, dbo.BI_RMHATB4_NOAA.POSTED,
                      dbo.BI_RMHATB4_NOAA.ADISTKNAM, dbo.BI_RMHATB4_NOAA.AWROFAMNT, dbo.BI_RMHATB4_NOAA.PPSAMDED, dbo.BI_RMHATB4_NOAA.GSTDSAMT,
                      dbo.BI_RMHATB4_NOAA.ACURRNIDX, dbo.BI_RMHATB4_NOAA.AXCHGRATE, dbo.BI_RMHATB4_NOAA.RLGANLOS, dbo.BI_RMHATB4_NOAA.ORAPTOAM,
                      dbo.BI_RMHATB4_NOAA.AORDISTKN, dbo.BI_RMHATB4_NOAA.AORWROFAM, dbo.BI_RMHATB4_NOAA.ADENXRATE, dbo.BI_RMHATB4_NOAA.AMCTRXSTT,
                      dbo.BI_RMHATB4_NOAA.CLASSID, dbo.BI_RMHATB4_NOAA.CLASDSCR, dbo.BI_RMHATB4_NOAA.Currency, dbo.BI_RMHATB4_NOAA.XCHGRATE,
                      dbo.BI_RMHATB4_NOAA.Aging_Amount_MC AS AGING_AMOUNT, dbo.BI_RMHATB4_NOAA.Apply_Amount_MC AS APPLY_AMOUNT, CASE WHEN (apfrdcnm > '0') AND
                      (FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Curtrxam_MC END AS Curtrxam, CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL)
                      THEN isnull(FPAgingAmt, 0) ELSE Ortrxamt_MC END AS Ortrxamt, FirstPay.FPDocNum, FirstPay.FPCurTrxamMC, FirstPay.FPCurtrxam, FirstPay.FPAgingAmt,
                      FirstPay.MaxDocNmTy, dbo.BI_RMHATB4_NOAA.ApfrDocNmTy
FROM         dbo.BI_RMHATB4_NOAA LEFT OUTER JOIN
                          (SELECT     DOCNUMBR AS FPDocNum, MAX(ApfrDocNmTy) AS MaxDocNmTy, Curtrxam_MC AS FPCurTrxamMC, CURTRXAM AS FPCurtrxam,
                                                   Aging_Amount_MC AS FPAgingAmt
                            FROM          dbo.BI_RMHATB4_NOAA AS BI_RMHATB4_NOAA_1
                            WHERE      (APFRDCTY > 0)
                            GROUP BY DOCNUMBR, Curtrxam_MC, CURTRXAM, Aging_Amount_MC) AS FirstPay ON dbo.BI_RMHATB4_NOAA.DOCNUMBR = FirstPay.FPDocNum AND
                      dbo.BI_RMHATB4_NOAA.ApfrDocNmTy = FirstPay.MaxDocNmTy

GO