Tuesday, August 14, 2018

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

No comments:

Post a Comment