NAV - Cannot post because you did not specify which entry to apply. You must specify an entry in the Applies-to-ID field for one or more open entries.

This occurs when attempting to apply customer or vendor payments to invoices.

This is happening because you did not choose "Payment" or "Credit Memo" as the document type when entering the transaction, which would have prompted you to enter the transaction as a negative.
Most likely, your transaction has gone in as a positive with no document type, causing the system to treat it as another invoice.

You cannot apply an invoice to an invoice.

• Solution:
• Reverse the payment transaction
• Redo the transaction with the correct document type
• enter the transaction amount as negative

NAV - Export all modified objects

http://www.waldo.be/2016/03/11/export-nav-application-objects/

NAV - Update posted dates to match document dates for posted purchase invoices

Invoices and receipts were posted to the wrong dates, this fixes those dates.
However, this does not fix inventory if it was already posted to the wrong date.
The correct way to fix this is to reverse all transactions, then re-enter them on the correct date.
-------------------------------------------------------------------------------------------------------------

update [dbo].[CRONUS$Purch_ Inv_ Header] set [Posting Date] = [Document Date]



update [dbo].[CRONUS$G_L Entry] set [Posting Date] = [Document Date]



update [dbo].[CRONUS$VAT Entry] set [Posting Date] = [Document Date]



update [dbo].[CRONUS$Vendor Ledger Entry] set [Posting Date] = [Document Date]



update [dbo].[CRONUS$Value Entry] set [Posting Date] = [Document Date]



update DEST

Set DEST.[Posting Date] = SRC.[Posting Date]

From [dbo].[CRONUS$Detailed Vendor Ledg_ Entry] as DEST

INNER JOIN [dbo].[CRONUS$Vendor Ledger Entry] as SRC on DEST.[Document No_]= SRC.[Document No_]

---------------------------------------------
update [dbo].[CRONUS$Purch_ Rcpt_ Header] set [Posting Date] = [Document Date] where [Posting Date] = '2018-08-28'

update DEST
Set DEST.[Posting Date] = SRC.[Posting Date]
From [dbo].[CRONUS$Purch_ Rcpt_ Line] as DEST
INNER JOIN [dbo].[CRONUS$Purch_ Rcpt_ Header] as SRC on DEST.[Document No_]= SRC.[No_]
WHERE DEST.[Posting Date] = '2018-08-28'


select * from [CRONUS$Purch_ Rcpt_ Header]
select * from [CRONUS$Purch_ Rcpt_ Line]

update DEST
Set DEST.[Posting Date] = SRC.[Posting Date]
From [dbo].[CRONUS$G_L Entry] as DEST
INNER JOIN [dbo].[CRONUS$Purch_ Inv_ Header] as SRC on DEST.[Document No_]= SRC.[No_]
WHERE DEST.[Posting Date] = '2018-08-28'

NAV CAL - Date2DMY - How to get dateparts from dates in nav and pad with leading zeroes

cd := FORMAT(DATE2DMY(ChqDate,1));
cm := FORMAT(DATE2DMY(ChqDate,2));
cy := FORMAT(DATE2DMY(ChqDate,3));
ChqDateDD := PADSTR('',2-STRLEN(cd),'0') + cd;
ChqDateMM := PADSTR('',2-STRLEN(cm),'0') + cm;
ChqDateYYYY := cy;


On Cheque, use Max to get the nonblank value in the footer
--------------------------------------------------------------------------------
Function to return YYYYMMDD

DateToYYYYMMDD(Date : Date) : Text
cd := FORMAT(DATE2DMY(Date,1));
cm := FORMAT(DATE2DMY(Date,2));
cy := FORMAT(DATE2DMY(Date,3));
cd := PADSTR('',2-STRLEN(cd),'0') + cd;
cm := PADSTR('',2-STRLEN(cm),'0') + cm;

EXIT(cy+cm+cd);


Opposite function to convert from text to date
-----------------------------------------------------------------------
YYYYMMDD2DATE(Date : Text[20]) : Date
cd := COPYSTR(Date,9,2);
cm := COPYSTR(Date,6,2);
cy := COPYSTR(Date,1,4);
EVALUATE(icd,cd);
EVALUATE(icm,cm);
EVALUATE(icy,cy);
MyDate := DMY2DATE(icd,icm,icy);
EXIT(MyDate);

NAV - Matrix Page Notes - Items by Location

• Matrix Page Example P9231 Items by Location Matrix
• PageType: Listpart
• Page Source: Table of values on the left  (Items) 
• Create an Array for 30 dimensions, and a column for each value in your array
• OnAfterGetRecord
• Create a counter that will increment based on headers for each column from values (Locations)
• Filter and sum your ItemLedgerEntry data by Current Item and Current Location, store that value in your array[Location col no] and create a repeat loop to populate each value for each cell based on the current record value, and the current column counter value

-------------------------------------------------Example---------------------------------------

OnAfterGetRecord()

MATRIX_CurrentColumnOrdinal := 0;

IF TempMatrixLocation.FINDSET THEN

  REPEAT

    MATRIX_CurrentColumnOrdinal := MATRIX_CurrentColumnOrdinal + 1;

    MATRIX_OnAfterGetRecord(MATRIX_CurrentColumnOrdinal);

  UNTIL (TempMatrixLocation.NEXT = 0) OR (MATRIX_CurrentColumnOrdinal = MATRIX_NoOfMatrixColumns);

LOCAL MATRIX_OnAfterGetRecord(ColumnID : Integer)

TempItem.COPY(Rec);

TempItem.SETRANGE("Location Filter",MatrixRecords[ColumnID].Code);

TempItem.CALCFIELDS(Inventory);

MATRIX_CellData[ColumnID] := TempItem.Inventory;

SetVisible;

LS NAV - Enable Count by Denomination for End Of Day - Send value to cash account - Safe Management

• Store>Statement/Closing
• Tick Safe Management in use
• POS Start Amount Method: Flexible Bag
• Tender Type>Cash
• Tick Count by Denomination
• Tick Taken to Bank
• Under Bank Posting
• Bank Account Type:  GL Account
• Bank Account: Enter GL Account
• Under Declaration
• Counting Required: True
• Taken to Bank: True
• Taken to Bank by Denomination: True

LS NAV - Autopost Statements

• Create Job to run C99001460 Automatic Statement Posting
• • Retail Setup
  • Set Default Store, Enter Store Name
  • Store Setup
  • Calculation Type: Terminal or staff
  • One statement per day: True
  • Tender Type
  • Counting Required (False) (Not required)
  • Job Setup
  • Create Job to run C99001460
  • Select Stores to run against
  • Modification to Codeunit 99001460 Automatic Statement Posting to automatically post statement after job ran
  • Add Code “StatementPost.RUN(Statement);”
  • Comment/Remove code “StatementPost.RunItemPosting(Statement,FALSE);”
  • If any warnings exist on the statement pertaining to missing transactions, it should be cleared and recalculated or the statement will not post
  • Check the Trans. Sales Entry Status 99001494 table to ensure that records exist for all transactions on a statement. If any are missing, the statement will not post.
  • This is happening because all of the transactions have not been uploaded to the HO as yet. The statement has been prematurely calculated
  • You must clear and recalculate the statement.
  • The problem that happens is that when the automated statement calculate runs, and hits this specific error, it breaks the job itself preventing any other records from being processed, and putting the job and statement into error states
  • When the job runs again, the same thing happens, causing the job to permanently stick on a bad statement
  • Prior to running the job, ensure all transactions are at HO with a full trans pull
  • You must add code to auto clear and recalculate the statement if the job or the statement are in error states (or you could auto clear and recalculate ALL the statements)

NAV-Multiple Contacts per Customer/ Vendor / Bank

• From the Vendor Card
• Navigate>Contact
• Navigate>Link With Existing
• T5054

LS Retail Hospitality - LS Retail Recipes vs NAV Assemblies

• LS Retail recipes work exactly like Assemblies, however they also function like kits.
• When a recipe item is sold, it will sell each of the individual items in the recipe
• Eg. Chicken sandwich will sell bread, tomato, chicken because the sandwich is only assembled when it is sold
• Recipes are specifically triggered through the LS Retail POS interface
• Recipes cannot be used for Backoffice Sales
• Assemblies can be used in Backoffice Sales
• Assemblies can be produced to order using backoffice sales
• Assemblies will not work using the LS Retail POS interface
• Recipes and Assemblies can both be produced into finished goods
• Recipes and Assemblies both require UofM conversions

Dynamics GP - Get the posted Batch Number for Inventory Transactions

• select SOPNUMBE, SOPTYPE, BACHNUMB from sop30200

NAV - Edit R1408 Bank Acc. Recon Test to show Unpresented checks, Unverified Deposits, Bank Balance

Ticking "Print Outstanding Transactions" will give detailed breakdown of outstanding documents.

• R1408 Bank Acc. Recon. -TEST
• T1284 Outstanding Bank Transaction 
• Temp table used to store outstanding Bank Trx (Outstanding Payment Trx are also stored here, but currently is not populated correctly in code NAV2017)
• Add ExtDocNo
• OutstdBankTransac>Group1>Add Parent Group> On Doctype
• Add footer
• Add total to footer
• Add External DocNo 
• Adjust 1284.CopyFromBankAccLedgerEntry
• ExtDocNo := BankAccountLedgerEntry."External Document No."; 

NAV - Buy x get x free

https://dynamicsuser.net/nav/f/users/5580/free-item-buy-x-items-free-1-item

Setup an equivalent price that will discount the total amount based on the quantity.
Build out the entire price table for the possible quantity tiers for each free item

Eg. Buy 10 get 1 free = discount 11 so it's the same price as 10

SSRS - TextBoxExprHost

This error comes up when you have two fields with the same name in different cases (upper and lower)
Rename your textbox to something different and save.

Dynamics NAV - How to Restore LS Retail VM to Azure

1. Download the vm through multiple rar files here
2. Uncompress for the .vhdx file
3. Create Local VM, in Hyper-V
1. Select to import from vhdx file
4. In Azure, perform a site recovery against local machine to import machine to Azure

Excel VBScript - Import text file and format as text to prevent date autoformatting

        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

Excel VBScript - Find last row in a sheet

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Dynamics GP - SSRS AR Aged Trial Balance Originating Multicurrency Document Amounts and Totals

/****** Object:  StoredProcedure [dbo].[BI_seermHATBSRSWrapper_base]    Script Date: 19/03/2019 11:21: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 
 --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: 19/03/2019 11:21: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 
 --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].[BIT_RMHATB_MC]    Script Date: 19/03/2019 11:21:27 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: 19/03/2019 11:21:27 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.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: 19/03/2019 11:21:27 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 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, dbo.BI_RMHATB4_NOAA.APPLY_AMOUNT, 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
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.ApfrDocNmTy = FirstPay.MaxDocNmTy

GO
/****** Object:  View [dbo].[BI_RMHATB4_NOAA_MC]    Script Date: 19/03/2019 11:21:27 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
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.ApfrDocNmTy = FirstPay.MaxDocNmTy

GO

Dynamics GP - SSRS AP Aged Trial Balance Originating Multicurrency Document Amounts and Totals

/****** Object:  StoredProcedure [dbo].[BI_seepmHATBWrapper_mc]    Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 CREATE procedure [dbo].[BI_seepmHATBWrapper_mc]   @I_dAgingDate datetime     = NULL,   @I_cStartVendorID char(15)    = NULL,   @I_cEndVendorID char(15)    = NULL,   @I_cStartVendorName char(65)   = NULL,   @I_cEndVendorName char(65)    = NULL,   @I_cStartClassID char(15)    = NULL,   @I_cEndClassID char(15)    = NULL,   @I_cStartUserDefined char(15)   = NULL,   @I_cEndUserDefined char(15)   = NULL,   @I_cStartPaymentPriority char(3)  = NULL,   @I_cEndPaymentPriority char(3)   = NULL,   @I_cStartDocumentNumber char(21)  = NULL,   @I_cEndDocumentNumber char(21)   = NULL,   @I_tUsingDocumentDate tinyint   = NULL,   @I_dStartDate datetime     = NULL,   @I_dEndDate datetime     = NULL,   @I_tExcludeNoActivity tinyint   = NULL,   @I_tExcludeMultiCurrency tinyint  = NULL,   @I_tExcludeZeroBalanceVendors tinyint  = NULL,   @I_tExcludeFullyPaidTrxs tinyint  = NULL,   @I_tExcludeCreditBalance tinyint  = NULL,   @I_tExcludeUnpostedAppldCrDocs tinyint = NULL as  declare  @X int, @VENDORID char (15),  @CNTRLNUM char (21), @DOCNUMBR char (21),  @DOCAMNT numeric (19,5) , @MIN_DEX_ROW_ID int set @X = 0  CREATE TABLE #PMATBVEN(  [VENDORID] [char](15) NOT NULL,  [VENDNAME] [char](65) NOT NULL,  [VNDCLSID] [char](11) NOT NULL,  [USERDEF1] [char](21) NOT NULL,  [PYMNTPRI] [char](3) NOT NULL,  [KEYSOURC] [char](41) NOT NULL,  [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL )  CREATE UNIQUE NONCLUSTERED INDEX [PK#PMATBVEN] ON #PMATBVEN (  [VENDORID] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBVEN] ON #PMATBVEN (  [VENDNAME] ASC,  [VENDORID] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK3#PMATBVEN] ON #PMATBVEN (  [VNDCLSID] ASC,  [VENDORID] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK4#PMATBVEN] ON #PMATBVEN (  [USERDEF1] ASC,  [VENDORID] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK5#PMATBVEN] ON #PMATBVEN (  [PYMNTPRI] ASC,  [VENDORID] ASC )  CREATE TABLE #PMATBAPP(  [APTVCHNM] [char](21) NOT NULL,  [APTODCTY] [smallint] NOT NULL,  [VCHRNMBR] [char](21) NOT NULL,  [DOCTYPE] [smallint] NOT NULL,  [APAMAGPR_1] [numeric](19, 5) NOT NULL,  [APAMAGPR_2] [numeric](19, 5) NOT NULL,  [APAMAGPR_3] [numeric](19, 5) NOT NULL,  [APAMAGPR_4] [numeric](19, 5) NOT NULL,  [APAMAGPR_5] [numeric](19, 5) NOT NULL,  [APAMAGPR_6] [numeric](19, 5) NOT NULL,  [APAMAGPR_7] [numeric](19, 5) NOT NULL,  [APPLDAMT] [numeric](19, 5) NOT NULL,  [POSTED] [tinyint] NOT NULL,  [CURNCYID] [char](15) NOT NULL,  [CURRNIDX] [smallint] NOT NULL,  [XCHGRATE] [numeric](19, 7) NOT NULL,  [ORAPPAMT] [numeric](19, 5) NOT NULL,  [OAGPRAMT_1] [numeric](19, 5) NOT NULL,  [OAGPRAMT_2] [numeric](19, 5) NOT NULL,  [OAGPRAMT_3] [numeric](19, 5) NOT NULL,  [OAGPRAMT_4] [numeric](19, 5) NOT NULL,  [OAGPRAMT_5] [numeric](19, 5) NOT NULL,  [OAGPRAMT_6] [numeric](19, 5) NOT NULL,  [OAGPRAMT_7] [numeric](19, 5) NOT NULL,  [RLGANLOS] [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#PMATBAPP] ON #PMATBAPP (  [APTVCHNM] ASC,  [APTODCTY] ASC,  [VCHRNMBR] ASC,  [DOCTYPE] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBAPP] ON #PMATBAPP  (  [VCHRNMBR] ASC,  [DOCTYPE] ASC,  [APTVCHNM] ASC,  [APTODCTY] ASC )  CREATE TABLE #PMATBDOC(  [VENDORID] [char](15) NOT NULL,  [CNTRLNUM] [char](21) NOT NULL,  [CNTRLTYP] [smallint] NOT NULL,  [DOCNUMBR] [char](21) NOT NULL,  [DOCTYPE] [smallint] NOT NULL,  [DOCAMNT] [numeric](19, 5) NOT NULL,  [DISTKNAM] [numeric](19, 5) NOT NULL,  [DOCDATE] [datetime] NOT NULL,  [DUEDATE] [datetime] NOT NULL,  [DISCDATE] [datetime] NOT NULL,  [TRXSORCE] [char](13) NOT NULL,  [CURTRXAM] [numeric](19, 5) NOT NULL,  [EAMAGPER_1] [numeric](19, 5) NOT NULL,  [EAMAGPER_2] [numeric](19, 5) NOT NULL,  [EAMAGPER_3] [numeric](19, 5) NOT NULL,  [EAMAGPER_4] [numeric](19, 5) NOT NULL,  [EAMAGPER_5] [numeric](19, 5) NOT NULL,  [EAMAGPER_6] [numeric](19, 5) NOT NULL,  [EAMAGPER_7] [numeric](19, 5) NOT NULL,  [DISAMTAV] [numeric](19, 5) NOT NULL,  [PERIODID] [smallint] NOT NULL,  [WROFAMNT] [numeric](19, 5) NOT NULL,  [KEYSOURC] [char](41) NOT NULL,  [DINVPDOF] [datetime] NOT NULL,  [PSTGDATE] [datetime] NOT NULL,  [CURNCYID] [char](15) NOT NULL,  [CURRNIDX] [smallint] NOT NULL,  [XCHGRATE] [numeric](19, 7) NOT NULL,  [ORDOCAMT] [numeric](19, 5) NOT NULL,  [ORDISTKN] [numeric](19, 5) NOT NULL,  [ORCTRXAM] [numeric](19, 5) NOT NULL,  [OAGPRAMT_1] [numeric](19, 5) NOT NULL,  [OAGPRAMT_2] [numeric](19, 5) NOT NULL,  [OAGPRAMT_3] [numeric](19, 5) NOT NULL,  [OAGPRAMT_4] [numeric](19, 5) NOT NULL,  [OAGPRAMT_5] [numeric](19, 5) NOT NULL,  [OAGPRAMT_6] [numeric](19, 5) NOT NULL,  [OAGPRAMT_7] [numeric](19, 5) NOT NULL,  [ODISAMTAV] [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#PMATBDOC] ON #PMATBDOC (  [VENDORID] ASC,  [CURNCYID] ASC,  [CNTRLNUM] ASC,  [CNTRLTYP] ASC,  [KEYSOURC] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK2#PMATBDOC] ON #PMATBDOC  (  [CURNCYID] ASC,  [DEX_ROW_ID] ASC )  exec pmHistoricalAgedTrialBalance   '#PMATBVEN',         '#PMATBDOC',         '#PMATBAPP',         '',           @I_dAgingDate,   @I_cStartVendorID,   @I_cEndVendorID,   @I_cStartVendorName,   @I_cEndVendorName,   @I_cStartClassID,   @I_cEndClassID,   @I_cStartUserDefined,   @I_cEndUserDefined,   @I_cStartPaymentPriority,   @I_cEndPaymentPriority,   @I_cStartDocumentNumber,   @I_cEndDocumentNumber,   @I_tUsingDocumentDate,   @I_dStartDate,   @I_dEndDate,   @I_tExcludeNoActivity,   @I_tExcludeMultiCurrency,   @I_tExcludeZeroBalanceVendors,   @I_tExcludeFullyPaidTrxs,   @I_tExcludeCreditBalance,   @I_tExcludeUnpostedAppldCrDocs,   '',           0,            0,            1,            0,            2,            0           
 Drop Table BIT_PMHATB_MC
 Select HATB.* Into BIT_PMHATB_MC FROM ( select  isnull([V].[VENDORID],'') as [VENDORID],  isnull([VENDNAME],'') as [VENDNAME],  isnull([VNDCLSID],'') as [VNDCLSID],  isnull([USERDEF1],'') as [USERDEF1],  isnull([PYMNTPRI],'') as [PYMNTPRI],  isnull([V].[KEYSOURC],'') as [VEN_KEYSOURC],  isnull([APTVCHNM],'') as [APTVCHNM],  isnull([APTODCTY],0) as [APTODCTY],   isnull([VCHRNMBR],'') as [VCHRNMBR],  isnull([A].[DOCTYPE],0) as APP_DOCTYPE,  isnull([APAMAGPR_1],0) as [APAMAGPR_1],  isnull([APAMAGPR_2],0) as [APAMAGPR_2],   isnull([APAMAGPR_3],0) as [APAMAGPR_3],   isnull([APAMAGPR_4],0) as [APAMAGPR_4],   isnull([APAMAGPR_5],0) as [APAMAGPR_5],   isnull([APAMAGPR_6],0) as [APAMAGPR_6],   isnull([APAMAGPR_7],0) as [APAMAGPR_7],   isnull([APPLDAMT],0) as [APPLDAMT],   isnull([POSTED],0) as [POSTED],   isnull([ORAPPAMT],0) as [ORAPPAMT],   isnull([A].[OAGPRAMT_1],0) as [APP_OAGPRAMT_1],   isnull([A].[OAGPRAMT_2],0) as [APP_OAGPRAMT_2],   isnull([A].[OAGPRAMT_3],0) as [APP_OAGPRAMT_3],   isnull([A].[OAGPRAMT_4],0) as [APP_OAGPRAMT_4],   isnull([A].[OAGPRAMT_5],0) as [APP_OAGPRAMT_5],   isnull([A].[OAGPRAMT_6],0) as [APP_OAGPRAMT_6],   isnull([A].[OAGPRAMT_7],0) as [APP_OAGPRAMT_7],  isnull([CNTRLNUM],'') as [CNTRLNUM],  isnull([CNTRLTYP],0) as [CNTRLTYP],   isnull([DOCNUMBR],'') as [DOCNUMBR],  isnull([D].[DOCTYPE],0) as DOC_DOCTYPE,  isnull([DOCAMNT],0) as [DOCAMNT],   isnull([DISTKNAM],0) as [DISTKNAM],   isnull([DOCDATE],'1900-01-01') as [DOCDATE],  isnull([DUEDATE],'1900-01-01') as [DUEDATE],  isnull([DISCDATE],'1900-01-01') as [DISCDATE],  isnull([TRXSORCE],'') as [TRXSORCE],  isnull([CURTRXAM],0) as [CURTRXAM],   isnull([EAMAGPER_1],0) as [EAMAGPER_1],   isnull([EAMAGPER_2],0) as [EAMAGPER_2],   isnull([EAMAGPER_3],0) as [EAMAGPER_3],   isnull([EAMAGPER_4],0) as [EAMAGPER_4],   isnull([EAMAGPER_5],0) as [EAMAGPER_5],   isnull([EAMAGPER_6],0) as [EAMAGPER_6],   isnull([EAMAGPER_7],0) as [EAMAGPER_7],   isnull([DISAMTAV],0) as [DISAMTAV],   isnull([PERIODID],0) as [PERIODID],   isnull([WROFAMNT],0) as [WROFAMNT],   isnull([D].[KEYSOURC],'') as [DOC_KEYSOURC],  isnull([DINVPDOF],'1900-01-01') as [DINVPDOF],  isnull([PSTGDATE],'1900-01-01') as [PSTGDATE],  isnull([ORDOCAMT],0) as [ORDOCAMT],   isnull([ORDISTKN],0) as [ORDISTKN],   isnull([ORCTRXAM],0) as [ORCTRXAM],   isnull([D].[OAGPRAMT_1],0) as [DOC_OAGPRAMT_1],   isnull([D].[OAGPRAMT_2],0) as [DOC_OAGPRAMT_2],   isnull([D].[OAGPRAMT_3],0) as [DOC_OAGPRAMT_3],   isnull([D].[OAGPRAMT_4],0) as [DOC_OAGPRAMT_4],   isnull([D].[OAGPRAMT_5],0) as [DOC_OAGPRAMT_5],   isnull([D].[OAGPRAMT_6],0) as [DOC_OAGPRAMT_6],   isnull([D].[OAGPRAMT_7],0) as [DOC_OAGPRAMT_7],   isnull([ODISAMTAV],0) as [ODISAMTAV],   isnull([ORWROFAM],0) as [ORWROFAM],  1 as DEX_ROW_ID  from #PMATBVEN V left join #PMATBDOC D on V.VENDORID = D.VENDORID left join #PMATBAPP A on D.CNTRLNUM = A.APTVCHNM  and D.DOCTYPE = A.APTODCTY ) as HATB  update BIT_PMHATB_MC set DEX_ROW_ID = @X, @X = @X + 1   declare AMOUNT cursor for  select VENDORID, CNTRLNUM, DOCNUMBR, DOCAMNT,Min(DEX_ROW_ID)as MIN_DEX_ROW_ID from BIT_PMHATB_MC group by VENDORID, CNTRLNUM,DOCNUMBR,DOCAMNT having count(*)>1   OPEN AMOUNT   FETCH NEXT FROM AMOUNT  INTO @VENDORID, @CNTRLNUM, @DOCNUMBR, @DOCAMNT, @MIN_DEX_ROW_ID  WHILE @@FETCH_STATUS = 0   BEGIN   update BIT_PMHATB_MC   set   CURTRXAM =0.00000,   EAMAGPER_1=0.00000,   EAMAGPER_2=0.00000,   EAMAGPER_3=0.00000,   EAMAGPER_4=0.00000,  EAMAGPER_5=0.00000,   EAMAGPER_6=0.00000,   EAMAGPER_7=0.00000  where DEX_ROW_ID > @MIN_DEX_ROW_ID and   VENDORID = @VENDORID and   CNTRLNUM = @CNTRLNUM and  DOCNUMBR = @DOCNUMBR and  DOCAMNT =  @DOCAMNT   FETCH NEXT FROM AMOUNT   INTO  @VENDORID, @CNTRLNUM, @DOCNUMBR, @DOCAMNT,@MIN_DEX_ROW_ID  end  CLOSE AMOUNT  DEALLOCATE AMOUNT
 Select * from BI_PMHATB4_MC
GO
/****** Object:  Table [dbo].[BIT_PMHATB_MC]    Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_PMHATB_MC](
[VENDORID] [char](15) NOT NULL,
[VENDNAME] [char](65) NOT NULL,
[VNDCLSID] [char](11) NOT NULL,
[USERDEF1] [char](21) NOT NULL,
[PYMNTPRI] [char](3) NOT NULL,
[VEN_KEYSOURC] [char](41) NOT NULL,
[APTVCHNM] [char](21) NOT NULL,
[APTODCTY] [smallint] NOT NULL,
[VCHRNMBR] [char](21) NOT NULL,
[APP_DOCTYPE] [smallint] NOT NULL,
[APAMAGPR_1] [numeric](19, 5) NOT NULL,
[APAMAGPR_2] [numeric](19, 5) NOT NULL,
[APAMAGPR_3] [numeric](19, 5) NOT NULL,
[APAMAGPR_4] [numeric](19, 5) NOT NULL,
[APAMAGPR_5] [numeric](19, 5) NOT NULL,
[APAMAGPR_6] [numeric](19, 5) NOT NULL,
[APAMAGPR_7] [numeric](19, 5) NOT NULL,
[APPLDAMT] [numeric](19, 5) NOT NULL,
[POSTED] [tinyint] NOT NULL,
[ORAPPAMT] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_1] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_2] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_3] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_4] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_5] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_6] [numeric](19, 5) NOT NULL,
[APP_OAGPRAMT_7] [numeric](19, 5) NOT NULL,
[CNTRLNUM] [char](21) NOT NULL,
[CNTRLTYP] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOC_DOCTYPE] [smallint] NOT NULL,
[DOCAMNT] [numeric](19, 5) NOT NULL,
[DISTKNAM] [numeric](19, 5) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[DUEDATE] [datetime] NOT NULL,
[DISCDATE] [datetime] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[EAMAGPER_1] [numeric](19, 5) NOT NULL,
[EAMAGPER_2] [numeric](19, 5) NOT NULL,
[EAMAGPER_3] [numeric](19, 5) NOT NULL,
[EAMAGPER_4] [numeric](19, 5) NOT NULL,
[EAMAGPER_5] [numeric](19, 5) NOT NULL,
[EAMAGPER_6] [numeric](19, 5) NOT NULL,
[EAMAGPER_7] [numeric](19, 5) NOT NULL,
[DISAMTAV] [numeric](19, 5) NOT NULL,
[PERIODID] [smallint] NOT NULL,
[WROFAMNT] [numeric](19, 5) NOT NULL,
[DOC_KEYSOURC] [char](41) NOT NULL,
[DINVPDOF] [datetime] NOT NULL,
[PSTGDATE] [datetime] NOT NULL,
[ORDOCAMT] [numeric](19, 5) NOT NULL,
[ORDISTKN] [numeric](19, 5) NOT NULL,
[ORCTRXAM] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_1] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_2] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_3] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_4] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_5] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_6] [numeric](19, 5) NOT NULL,
[DOC_OAGPRAMT_7] [numeric](19, 5) NOT NULL,
[ODISAMTAV] [numeric](19, 5) NOT NULL,
[ORWROFAM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[BI_PMHATB2_MC]    Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PMHATB2_MC]
AS
SELECT        dbo.BIT_PMHATB_MC.VENDORID, dbo.BIT_PMHATB_MC.VENDNAME, dbo.BIT_PMHATB_MC.VNDCLSID, dbo.BIT_PMHATB_MC.USERDEF1, dbo.BIT_PMHATB_MC.PYMNTPRI,
                         dbo.BIT_PMHATB_MC.VEN_KEYSOURC, dbo.BIT_PMHATB_MC.APTVCHNM, dbo.BIT_PMHATB_MC.APTODCTY, dbo.BIT_PMHATB_MC.VCHRNMBR, dbo.BIT_PMHATB_MC.APP_DOCTYPE,
                         dbo.BIT_PMHATB_MC.APAMAGPR_1, dbo.BIT_PMHATB_MC.APAMAGPR_2, dbo.BIT_PMHATB_MC.APAMAGPR_3, dbo.BIT_PMHATB_MC.APAMAGPR_4, dbo.BIT_PMHATB_MC.APAMAGPR_5,
                         dbo.BIT_PMHATB_MC.APAMAGPR_6, dbo.BIT_PMHATB_MC.APAMAGPR_7, dbo.BIT_PMHATB_MC.APPLDAMT, dbo.BIT_PMHATB_MC.POSTED, dbo.BIT_PMHATB_MC.ORAPPAMT,
                         dbo.BIT_PMHATB_MC.APP_OAGPRAMT_1, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_2, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_3, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_4,
                         dbo.BIT_PMHATB_MC.APP_OAGPRAMT_5, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_6, dbo.BIT_PMHATB_MC.APP_OAGPRAMT_7, dbo.BIT_PMHATB_MC.CNTRLNUM, dbo.BIT_PMHATB_MC.CNTRLTYP,
                         dbo.BIT_PMHATB_MC.DOCNUMBR, dbo.BIT_PMHATB_MC.DOC_DOCTYPE, dbo.BIT_PMHATB_MC.DISTKNAM, dbo.BIT_PMHATB_MC.DOCDATE, dbo.BIT_PMHATB_MC.DUEDATE,
                         dbo.BIT_PMHATB_MC.DISCDATE, dbo.BIT_PMHATB_MC.TRXSORCE, dbo.BIT_PMHATB_MC.CURTRXAM, dbo.BIT_PMHATB_MC.EAMAGPER_1, dbo.BIT_PMHATB_MC.EAMAGPER_2,
                         dbo.BIT_PMHATB_MC.EAMAGPER_3, dbo.BIT_PMHATB_MC.EAMAGPER_4, dbo.BIT_PMHATB_MC.EAMAGPER_5, dbo.BIT_PMHATB_MC.EAMAGPER_6, dbo.BIT_PMHATB_MC.EAMAGPER_7,
                         dbo.BIT_PMHATB_MC.DISAMTAV, dbo.BIT_PMHATB_MC.PERIODID, dbo.BIT_PMHATB_MC.WROFAMNT, dbo.BIT_PMHATB_MC.DOC_KEYSOURC, dbo.BIT_PMHATB_MC.DINVPDOF,
                         dbo.BIT_PMHATB_MC.PSTGDATE, dbo.BIT_PMHATB_MC.ORDOCAMT, dbo.BIT_PMHATB_MC.ORDISTKN, dbo.BIT_PMHATB_MC.ORCTRXAM, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_1,
                         dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_2, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_3, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_4, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_5,
                         dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_6, dbo.BIT_PMHATB_MC.DOC_OAGPRAMT_7, dbo.BIT_PMHATB_MC.ODISAMTAV, dbo.BIT_PMHATB_MC.ORWROFAM, dbo.BIT_PMHATB_MC.DEX_ROW_ID,
                         dbo.BIT_PMHATB_MC.DOCAMNT, ISNULL(Exch.ExchRate, 0) AS CalcRate, P.PORDNMBR, CASE WHEN Currency = '' THEN ExchCurr ELSE Currency END AS Currency, Exch.ExchCurr, Exch.ExchRate,
                         Exch.ExchOrDocamt
FROM            dbo.BIT_PMHATB_MC LEFT OUTER JOIN
                             (SELECT        VENDORID, VCHRNMBR, DOCTYPE, DOCNUMBR, CURNCYID AS Currency, PORDNMBR
                               FROM            dbo.PM20000
                               UNION
                               SELECT        VENDORID, VCHRNMBR, DOCTYPE, DOCNUMBR, CURNCYID, PORDNMBR
                               FROM            dbo.PM30200) AS P ON dbo.BIT_PMHATB_MC.DOC_DOCTYPE = P.DOCTYPE AND dbo.BIT_PMHATB_MC.CNTRLNUM = P.VCHRNMBR AND
                         dbo.BIT_PMHATB_MC.VENDORID = P.VENDORID LEFT OUTER JOIN
                             (SELECT        DCSTATUS, DOCTYPE, VCHRNMBR, PMNTNMBR, DOCDATE, VENDORID, CURNCYID AS ExchCurr, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE AS ExchRate, EXCHDATE, TIME1, RTCLCMTD,
                                                         ORCTRXAM, OPURAMT, ORFRTAMT, OMISCAMT, ORTAXAMT, ORCASAMT, ORCHKAMT, ORCHKTTL, ORCCDAMT, ORAPPAMT, ORDISTKN, ORDATKN, ORDDLRAT, ORTDISAM, ORWROFAM,
                                                         OBKPURAMT, ORBKTFRT, ORBKTMSC, UNGANLOS, RMMCERRS, OCHGAMT, ORDOCAMT AS ExchOrDocamt, ODISAMTAV, ORGAPDISCTKN, OTOTPAY, OR1099AM, DENXRATE, MCTRXSTT,
                                                         OrigBackoutTradeDisc, DEX_ROW_ID
                               FROM            dbo.MC020103) AS Exch ON dbo.BIT_PMHATB_MC.DOC_DOCTYPE = Exch.DOCTYPE AND dbo.BIT_PMHATB_MC.CNTRLNUM = Exch.VCHRNMBR AND
                         dbo.BIT_PMHATB_MC.VENDORID = Exch.VENDORID

GO
/****** Object:  View [dbo].[BI_PMHATB4_MC]    Script Date: 08/14/2018 11:47:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_PMHATB4_MC]
AS
SELECT        VENDORID, VENDNAME, VNDCLSID, USERDEF1, PYMNTPRI, VEN_KEYSOURC, APTVCHNM, APTODCTY, VCHRNMBR, APP_DOCTYPE, APAMAGPR_1, APAMAGPR_2, APAMAGPR_3, APAMAGPR_4, APAMAGPR_5,
                         APAMAGPR_6, APAMAGPR_7, APPLDAMT, POSTED, ORAPPAMT, APP_OAGPRAMT_1, APP_OAGPRAMT_2, APP_OAGPRAMT_3, APP_OAGPRAMT_4, APP_OAGPRAMT_5, APP_OAGPRAMT_6, APP_OAGPRAMT_7,
                         CNTRLNUM, CNTRLTYP, DOCNUMBR, DOC_DOCTYPE, DISTKNAM, DOCDATE, DUEDATE, DISCDATE, TRXSORCE, CURTRXAM, EAMAGPER_1, EAMAGPER_2, EAMAGPER_3, EAMAGPER_4, EAMAGPER_5,
                         EAMAGPER_6, EAMAGPER_7, DISAMTAV, PERIODID, WROFAMNT, DOC_KEYSOURC, DINVPDOF, PSTGDATE, ORDOCAMT, ORDISTKN, ORCTRXAM, DOC_OAGPRAMT_1, DOC_OAGPRAMT_2, DOC_OAGPRAMT_3,
                         DOC_OAGPRAMT_4, DOC_OAGPRAMT_5, DOC_OAGPRAMT_6, DOC_OAGPRAMT_7, ODISAMTAV, ORWROFAM, DEX_ROW_ID, DOCAMNT, CalcRate, ROUND(DOCAMNT / CalcRate, 2) AS CalcOrdDocamt,
                         ROUND(EAMAGPER_1 / CalcRate, 2) AS CEAMAGPER_1, ROUND(EAMAGPER_2 / CalcRate, 2) AS CEAMAGPER_2, ROUND(EAMAGPER_3 / CalcRate, 2) AS CEAMAGPER_3, ROUND(EAMAGPER_4 / CalcRate, 2)
                         AS CEAMAGPER_4, ROUND(EAMAGPER_5 / CalcRate, 2) AS CEAMAGPER_5, ROUND(EAMAGPER_6 / CalcRate, 2) AS CEAMAGPER_6, ROUND(EAMAGPER_7 / CalcRate, 2) AS CEAMAGPER_7, PORDNMBR,
                         Currency
FROM            dbo.BI_PMHATB2_MC
WHERE        (Currency <> 'TTD')

GO