/****** Object: StoredProcedure [dbo].[BI_seermHATBSRSWrapper_base] Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seermHATBSRSWrapper_base] @I_dAgingDate datetime = NULL, @I_cStartCustomerNumber char(15) = NULL, @I_cEndCustomerNumber char(15) = NULL, @I_cStartCustomerName char(65) = NULL, @I_cEndCustomerName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartSalesPersonID char(15) = NULL, @I_cEndSalesPersonID char(15) = NULL, @I_cStartSalesTerritory char(15) = NULL, @I_cEndSalesTerritory char(15) = NULL, @I_cStartShortName char(15) = NULL, @I_cEndShortName char(15) = NULL, @I_cStartState char(5) = NULL, @I_cEndState char(5) = NULL, @I_cStartZipCode char(11) = NULL, @I_cEndZipCode char(11) = NULL, @I_cStartPhoneNumber char(21) = NULL, @I_cEndPhoneNumber char(21) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_sIncludeBalanceTypes smallint = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceCustomer tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL, @I_tConsolidateNAActivity tinyint = NULL as declare @FUNLCURR char(12) CREATE TABLE #RMHATBAP( [APFRDCNM] [char](21) NOT NULL, [APFRDCTY] [smallint] NOT NULL, [FROMCURR] [char](15) NOT NULL, [APTODCNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [APPTOAMT] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [DATE1] [datetime] NOT NULL, [POSTED] [tinyint] NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [PPSAMDED] [numeric](19, 5) NOT NULL, [GSTDSAMT] [numeric](19, 5) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [ORAPTOAM] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP ( [APFRDCNM] ASC, [APFRDCTY] ASC, [APTODCNM] ASC, [APTODCTY] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP ( [APTODCTY] ASC, [APTODCNM] ASC, [APFRDCTY] ASC, [APFRDCNM] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBDO( [CUSTNMBR] [char](15) NOT NULL, [CHCUMNUM] [char](15) NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [RMDTYPAL] [smallint] NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [CURNCYID] [char](15) NOT NULL, [ORTRXAMT] [numeric](19, 5) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [AGNGBUKT] [smallint] NOT NULL, [CASHAMNT] [numeric](19, 5) NOT NULL, [COMDLRAM] [numeric](19, 5) NOT NULL, [SLSAMNT] [numeric](19, 5) NOT NULL, [COSTAMNT] [numeric](19, 5) NOT NULL, [FRTAMNT] [numeric](19, 5) NOT NULL, [MISCAMNT] [numeric](19, 5) NOT NULL, [TAXAMNT] [numeric](19, 5) NOT NULL, [DISAVAMT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [TRXDSCRN] [char](31) NOT NULL, [DOCABREV] [char](3) NOT NULL, [CHEKNMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [GLPOSTDT] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [POSTDATE] [datetime] NOT NULL, [DINVPDOF] [datetime] NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORCASAMT] [numeric](19, 5) NOT NULL, [ORSLSAMT] [numeric](19, 5) NOT NULL, [ORCSTAMT] [numeric](19, 5) NOT NULL, [ORDAVAMT] [numeric](19, 5) NOT NULL, [ORFRTAMT] [numeric](19, 5) NOT NULL, [ORMISCAMT] [numeric](19, 5) NOT NULL, [ORTAXAMT] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [ORORGTRX] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [Aging_Period_Amount] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO ( [RMDTYPAL] ASC, [DOCNUMBR] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO ( [CUSTNMBR] ASC, [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO ( [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBCU( [CUSTNMBR] [char](15) NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [AGNGDATE] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU ( [DSCRIPTN] ASC, [CUSTNMBR] ASC ) select @FUNLCURR = rtrim(FUNLCURR) from MC40000 If @I_cEndCustomerNumber = '' begin set @I_cEndCustomerNumber = 'zzzzzzzzzz' End If @I_cEndCustomerName = '' begin set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ' End If @I_cEndClassID = '' begin set @I_cEndClassID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesPersonID = '' begin set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesTerritory = '' begin set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ' End If @I_cEndShortName = '' begin set @I_cEndShortName = 'þþþþþþþþþþþþþþþ' End If @I_cEndState = '' begin set @I_cEndState = 'þþþþþþþþþþþþþþþ' End If @I_cEndZipCode = '' begin set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ' End If @I_cEndPhoneNumber = '' begin set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndUserDefined = '' begin set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ' End exec [rmHistoricalAgedTrialBalance] '#RMHATBCU', '#RMHATBDO', '#RMHATBAP', '', @I_dAgingDate, @I_cStartCustomerNumber, @I_cEndCustomerNumber, @I_cStartCustomerName, @I_cEndCustomerName, @I_cStartClassID, @I_cEndClassID, @I_cStartSalesPersonID, @I_cEndSalesPersonID, @I_cStartSalesTerritory, @I_cEndSalesTerritory, @I_cStartShortName, @I_cEndShortName, @I_cStartState, @I_cEndState, @I_cStartZipCode, @I_cEndZipCode, @I_cStartPhoneNumber, @I_cEndPhoneNumber, @I_cStartUserDefined, @I_cEndUserDefined, 1, 0, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_sIncludeBalanceTypes, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceCustomer, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, @I_tConsolidateNAActivity, @FUNLCURR, 0, 0, 1.0000, 0, 2, 0
Drop table BIT_RMHATB_MC
Select RMHATB.* Into BIT_RMHATB_MC FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB
--Debug temp tables
/*
Drop Table BI_TEMP_RMHATBCU
Drop Table BI_TEMP_RMHATBDO
Drop Table BI_TEMP_RMHATBAP
select * into BI_TEMP_RMHATBCU from #rmhatbcu
select * into BI_TEMP_RMHATBDO from #rmhatbdo
select * into BI_TEMP_RMHATBAP from #rmhatbap
*/
--WHILE (SELECT COUNT(*) FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB_MC WHERE ORTRXAMT<>0 GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0 BEGIN UPDATE BIT_RMHATB_MC SET AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0 FROM BIT_RMHATB_MC JOIN ( SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB_MC GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) PARTIALLY_APPLIED ON PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB_MC.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB_MC.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB_MC.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB_MC.APFRDCNM END
--Select RMHATB.* Into BIT_RMHATB_RAW FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB
--SELECT * FROM BI_RMHATB4_MC
SELECT * FROM BI_RMHATB4_NOAA_BASE
GO
/****** Object: StoredProcedure [dbo].[BI_seermHATBSRSWrapper_mc] Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seermHATBSRSWrapper_mc] @I_dAgingDate datetime = NULL, @I_cStartCustomerNumber char(15) = NULL, @I_cEndCustomerNumber char(15) = NULL, @I_cStartCustomerName char(65) = NULL, @I_cEndCustomerName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartSalesPersonID char(15) = NULL, @I_cEndSalesPersonID char(15) = NULL, @I_cStartSalesTerritory char(15) = NULL, @I_cEndSalesTerritory char(15) = NULL, @I_cStartShortName char(15) = NULL, @I_cEndShortName char(15) = NULL, @I_cStartState char(5) = NULL, @I_cEndState char(5) = NULL, @I_cStartZipCode char(11) = NULL, @I_cEndZipCode char(11) = NULL, @I_cStartPhoneNumber char(21) = NULL, @I_cEndPhoneNumber char(21) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_sIncludeBalanceTypes smallint = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceCustomer tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL, @I_tConsolidateNAActivity tinyint = NULL as declare @FUNLCURR char(12) CREATE TABLE #RMHATBAP( [APFRDCNM] [char](21) NOT NULL, [APFRDCTY] [smallint] NOT NULL, [FROMCURR] [char](15) NOT NULL, [APTODCNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [APPTOAMT] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [DATE1] [datetime] NOT NULL, [POSTED] [tinyint] NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [PPSAMDED] [numeric](19, 5) NOT NULL, [GSTDSAMT] [numeric](19, 5) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [ORAPTOAM] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP ( [APFRDCNM] ASC, [APFRDCTY] ASC, [APTODCNM] ASC, [APTODCTY] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP ( [APTODCTY] ASC, [APTODCNM] ASC, [APFRDCTY] ASC, [APFRDCNM] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBDO( [CUSTNMBR] [char](15) NOT NULL, [CHCUMNUM] [char](15) NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [RMDTYPAL] [smallint] NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [CURNCYID] [char](15) NOT NULL, [ORTRXAMT] [numeric](19, 5) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [AGNGBUKT] [smallint] NOT NULL, [CASHAMNT] [numeric](19, 5) NOT NULL, [COMDLRAM] [numeric](19, 5) NOT NULL, [SLSAMNT] [numeric](19, 5) NOT NULL, [COSTAMNT] [numeric](19, 5) NOT NULL, [FRTAMNT] [numeric](19, 5) NOT NULL, [MISCAMNT] [numeric](19, 5) NOT NULL, [TAXAMNT] [numeric](19, 5) NOT NULL, [DISAVAMT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [TRXDSCRN] [char](31) NOT NULL, [DOCABREV] [char](3) NOT NULL, [CHEKNMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [GLPOSTDT] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [POSTDATE] [datetime] NOT NULL, [DINVPDOF] [datetime] NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORCASAMT] [numeric](19, 5) NOT NULL, [ORSLSAMT] [numeric](19, 5) NOT NULL, [ORCSTAMT] [numeric](19, 5) NOT NULL, [ORDAVAMT] [numeric](19, 5) NOT NULL, [ORFRTAMT] [numeric](19, 5) NOT NULL, [ORMISCAMT] [numeric](19, 5) NOT NULL, [ORTAXAMT] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [ORORGTRX] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [Aging_Period_Amount] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO ( [RMDTYPAL] ASC, [DOCNUMBR] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO ( [CUSTNMBR] ASC, [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO ( [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBCU( [CUSTNMBR] [char](15) NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [AGNGDATE] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU ( [DSCRIPTN] ASC, [CUSTNMBR] ASC ) select @FUNLCURR = rtrim(FUNLCURR) from MC40000 If @I_cEndCustomerNumber = '' begin set @I_cEndCustomerNumber = 'zzzzzzzzzz' End If @I_cEndCustomerName = '' begin set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ' End If @I_cEndClassID = '' begin set @I_cEndClassID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesPersonID = '' begin set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesTerritory = '' begin set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ' End If @I_cEndShortName = '' begin set @I_cEndShortName = 'þþþþþþþþþþþþþþþ' End If @I_cEndState = '' begin set @I_cEndState = 'þþþþþþþþþþþþþþþ' End If @I_cEndZipCode = '' begin set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ' End If @I_cEndPhoneNumber = '' begin set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndUserDefined = '' begin set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ' End exec [rmHistoricalAgedTrialBalance] '#RMHATBCU', '#RMHATBDO', '#RMHATBAP', '', @I_dAgingDate, @I_cStartCustomerNumber, @I_cEndCustomerNumber, @I_cStartCustomerName, @I_cEndCustomerName, @I_cStartClassID, @I_cEndClassID, @I_cStartSalesPersonID, @I_cEndSalesPersonID, @I_cStartSalesTerritory, @I_cEndSalesTerritory, @I_cStartShortName, @I_cEndShortName, @I_cStartState, @I_cEndState, @I_cStartZipCode, @I_cEndZipCode, @I_cStartPhoneNumber, @I_cEndPhoneNumber, @I_cStartUserDefined, @I_cEndUserDefined, 1, 0, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_sIncludeBalanceTypes, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceCustomer, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, @I_tConsolidateNAActivity, @FUNLCURR, 0, 0, 1.0000, 0, 2, 0
Drop table BIT_RMHATB_MC
Select RMHATB.* Into BIT_RMHATB_MC FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB
--Debug temp tables
/*
Drop Table BI_TEMP_RMHATBCU
Drop Table BI_TEMP_RMHATBDO
Drop Table BI_TEMP_RMHATBAP
select * into BI_TEMP_RMHATBCU from #rmhatbcu
select * into BI_TEMP_RMHATBDO from #rmhatbdo
select * into BI_TEMP_RMHATBAP from #rmhatbap
*/
--WHILE (SELECT COUNT(*) FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB_MC WHERE ORTRXAMT<>0 GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0 BEGIN UPDATE BIT_RMHATB_MC SET AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0 FROM BIT_RMHATB_MC JOIN ( SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB_MC GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) PARTIALLY_APPLIED ON PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB_MC.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB_MC.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB_MC.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB_MC.APFRDCNM END
--Select RMHATB.* Into BIT_RMHATB_RAW FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB
--SELECT * FROM BI_RMHATB4_MC
SELECT * FROM BI_RMHATB4_NOAA_MC
GO
/****** Object: Table [dbo].[BI_TEMP_RMHATBAP] Script Date: 03/05/2019 10:31:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_RMHATBAP](
[APFRDCNM] [char](21) NOT NULL,
[APFRDCTY] [smallint] NOT NULL,
[FROMCURR] [char](15) NOT NULL,
[APTODCNM] [char](21) NOT NULL,
[APTODCTY] [smallint] NOT NULL,
[APPTOAMT] [numeric](19, 5) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[DATE1] [datetime] NOT NULL,
[POSTED] [tinyint] NOT NULL,
[DISTKNAM] [numeric](19, 5) NOT NULL,
[WROFAMNT] [numeric](19, 5) NOT NULL,
[PPSAMDED] [numeric](19, 5) NOT NULL,
[GSTDSAMT] [numeric](19, 5) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[XCHGRATE] [numeric](19, 7) NOT NULL,
[RLGANLOS] [numeric](19, 5) NOT NULL,
[ORAPTOAM] [numeric](19, 5) NOT NULL,
[ORDISTKN] [numeric](19, 5) NOT NULL,
[ORWROFAM] [numeric](19, 5) NOT NULL,
[DENXRATE] [numeric](19, 7) NOT NULL,
[MCTRXSTT] [smallint] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BI_TEMP_RMHATBCU] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_RMHATBCU](
[CUSTNMBR] [char](15) NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[AGNGDATE] [datetime] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BI_TEMP_RMHATBDO] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BI_TEMP_RMHATBDO](
[CUSTNMBR] [char](15) NOT NULL,
[CHCUMNUM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[AGNGBUKT] [smallint] NOT NULL,
[CASHAMNT] [numeric](19, 5) NOT NULL,
[COMDLRAM] [numeric](19, 5) NOT NULL,
[SLSAMNT] [numeric](19, 5) NOT NULL,
[COSTAMNT] [numeric](19, 5) NOT NULL,
[FRTAMNT] [numeric](19, 5) NOT NULL,
[MISCAMNT] [numeric](19, 5) NOT NULL,
[TAXAMNT] [numeric](19, 5) NOT NULL,
[DISAVAMT] [numeric](19, 5) NOT NULL,
[DISTKNAM] [numeric](19, 5) NOT NULL,
[WROFAMNT] [numeric](19, 5) NOT NULL,
[TRXDSCRN] [char](31) NOT NULL,
[DOCABREV] [char](3) NOT NULL,
[CHEKNMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[DUEDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[DISCDATE] [datetime] NOT NULL,
[POSTDATE] [datetime] NOT NULL,
[DINVPDOF] [datetime] NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[XCHGRATE] [numeric](19, 7) NOT NULL,
[ORCASAMT] [numeric](19, 5) NOT NULL,
[ORSLSAMT] [numeric](19, 5) NOT NULL,
[ORCSTAMT] [numeric](19, 5) NOT NULL,
[ORDAVAMT] [numeric](19, 5) NOT NULL,
[ORFRTAMT] [numeric](19, 5) NOT NULL,
[ORMISCAMT] [numeric](19, 5) NOT NULL,
[ORTAXAMT] [numeric](19, 5) NOT NULL,
[ORCTRXAM] [numeric](19, 5) NOT NULL,
[ORORGTRX] [numeric](19, 5) NOT NULL,
[ORDISTKN] [numeric](19, 5) NOT NULL,
[ORWROFAM] [numeric](19, 5) NOT NULL,
[DENXRATE] [numeric](19, 7) NOT NULL,
[MCTRXSTT] [smallint] NOT NULL,
[Aging_Period_Amount] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[BIT_RMHATB_MC] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_RMHATB_MC](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[CUSTNAME] [char](65) NOT NULL,
[BALNCTYP] [smallint] NOT NULL,
[USERDEF1] [char](21) NOT NULL,
[CNTCPRSN] [char](61) NOT NULL,
[PHONE1] [char](21) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[SALSTERR] [char](15) NOT NULL,
[PYMTRMID] [char](21) NOT NULL,
[CRLMTAMT] [numeric](19, 5) NOT NULL,
[CRLMTPER] [smallint] NOT NULL,
[CRLMTPAM] [numeric](19, 5) NOT NULL,
[CRLMTTYP] [smallint] NOT NULL,
[CUSTCLAS] [char](15) NOT NULL,
[SHRTNAME] [char](15) NOT NULL,
[ZIP] [char](11) NOT NULL,
[STATE] [char](29) NOT NULL,
[CUDSCRIPTN] [char](31) NOT NULL,
[AGNGDATE] [datetime] NOT NULL,
[CHCUMNUM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[DCURNCYID] [char](15) NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[AGNGBUKT] [smallint] NOT NULL,
[CASHAMNT] [numeric](19, 5) NOT NULL,
[COMDLRAM] [numeric](19, 5) NOT NULL,
[SLSAMNT] [numeric](19, 5) NOT NULL,
[COSTAMNT] [numeric](19, 5) NOT NULL,
[FRTAMNT] [numeric](19, 5) NOT NULL,
[MISCAMNT] [numeric](19, 5) NOT NULL,
[TAXAMNT] [numeric](19, 5) NOT NULL,
[DISAVAMT] [numeric](19, 5) NOT NULL,
[DDISTKNAM] [numeric](19, 5) NOT NULL,
[DWROFAMNT] [numeric](19, 5) NOT NULL,
[TRXDSCRN] [char](31) NOT NULL,
[DOCABREV] [char](3) NOT NULL,
[CHEKNMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[DUEDATE] [datetime] NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[DISCDATE] [datetime] NOT NULL,
[POSTDATE] [datetime] NOT NULL,
[DINVPDOF] [datetime] NOT NULL,
[DCURRNIDX] [smallint] NOT NULL,
[DXCHGRATE] [numeric](19, 7) NOT NULL,
[ORCASAMT] [numeric](19, 5) NOT NULL,
[ORSLSAMT] [numeric](19, 5) NOT NULL,
[ORCSTAMT] [numeric](19, 5) NOT NULL,
[ORDAVAMT] [numeric](19, 5) NOT NULL,
[ORFRTAMT] [numeric](19, 5) NOT NULL,
[ORMISCAMT] [numeric](19, 5) NOT NULL,
[ORTAXAMT] [numeric](19, 5) NOT NULL,
[ORCTRXAM] [numeric](19, 5) NOT NULL,
[ORORGTRX] [numeric](19, 5) NOT NULL,
[DORDISTKN] [numeric](19, 5) NOT NULL,
[DORWROFAM] [numeric](19, 5) NOT NULL,
[DDENXRATE] [numeric](19, 7) NOT NULL,
[DMCTRXSTT] [smallint] NOT NULL,
[Aging_Period_Amount] [numeric](19, 5) NOT NULL,
[APFRDCNM] [char](21) NOT NULL,
[APFRDCTY] [smallint] NOT NULL,
[FROMCURR] [char](15) NOT NULL,
[APTODCNM] [char](21) NOT NULL,
[APTODCTY] [smallint] NOT NULL,
[APPTOAMT] [numeric](19, 5) NOT NULL,
[ACURNCYID] [char](15) NOT NULL,
[DATE1] [datetime] NOT NULL,
[POSTED] [tinyint] NOT NULL,
[ADISTKNAM] [numeric](19, 5) NOT NULL,
[AWROFAMNT] [numeric](19, 5) NOT NULL,
[PPSAMDED] [numeric](19, 5) NOT NULL,
[GSTDSAMT] [numeric](19, 5) NOT NULL,
[ACURRNIDX] [smallint] NOT NULL,
[AXCHGRATE] [numeric](19, 7) NOT NULL,
[RLGANLOS] [numeric](19, 5) NOT NULL,
[ORAPTOAM] [numeric](19, 5) NOT NULL,
[AORDISTKN] [numeric](19, 5) NOT NULL,
[AORWROFAM] [numeric](19, 5) NOT NULL,
[ADENXRATE] [numeric](19, 7) NOT NULL,
[AMCTRXSTT] [smallint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[BI_RMHATB4_NOAA] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA]
AS
SELECT dbo.BIT_RMHATB_MC.APPLY_AMOUNT, dbo.BIT_RMHATB_MC.AGING_AMOUNT, dbo.BIT_RMHATB_MC.CUSTNMBR, dbo.BIT_RMHATB_MC.CUSTNAME,
dbo.BIT_RMHATB_MC.BALNCTYP, dbo.BIT_RMHATB_MC.USERDEF1, dbo.BIT_RMHATB_MC.CNTCPRSN, dbo.BIT_RMHATB_MC.PHONE1,
dbo.BIT_RMHATB_MC.SLPRSNID, dbo.BIT_RMHATB_MC.SALSTERR, dbo.BIT_RMHATB_MC.PYMTRMID, dbo.BIT_RMHATB_MC.CRLMTAMT,
dbo.BIT_RMHATB_MC.CRLMTPER, dbo.BIT_RMHATB_MC.CRLMTPAM, dbo.BIT_RMHATB_MC.CRLMTTYP, dbo.BIT_RMHATB_MC.CUSTCLAS,
dbo.BIT_RMHATB_MC.SHRTNAME, dbo.BIT_RMHATB_MC.ZIP, dbo.BIT_RMHATB_MC.STATE, dbo.BIT_RMHATB_MC.CUDSCRIPTN,
dbo.BIT_RMHATB_MC.AGNGDATE, dbo.BIT_RMHATB_MC.CHCUMNUM, dbo.BIT_RMHATB_MC.DOCNUMBR, dbo.BIT_RMHATB_MC.RMDTYPAL,
dbo.BIT_RMHATB_MC.DSCRIPTN, dbo.BIT_RMHATB_MC.DCURNCYID, dbo.BIT_RMHATB_MC.ORTRXAMT, dbo.BIT_RMHATB_MC.AGNGBUKT,
dbo.BIT_RMHATB_MC.CASHAMNT, dbo.BIT_RMHATB_MC.COMDLRAM, dbo.BIT_RMHATB_MC.SLSAMNT, dbo.BIT_RMHATB_MC.COSTAMNT,
dbo.BIT_RMHATB_MC.FRTAMNT, dbo.BIT_RMHATB_MC.MISCAMNT, dbo.BIT_RMHATB_MC.TAXAMNT, dbo.BIT_RMHATB_MC.DISAVAMT,
dbo.BIT_RMHATB_MC.DDISTKNAM, dbo.BIT_RMHATB_MC.DWROFAMNT, dbo.BIT_RMHATB_MC.TRXDSCRN, dbo.BIT_RMHATB_MC.DOCABREV,
dbo.BIT_RMHATB_MC.CHEKNMBR, dbo.BIT_RMHATB_MC.DOCDATE, dbo.BIT_RMHATB_MC.DUEDATE, dbo.BIT_RMHATB_MC.GLPOSTDT,
dbo.BIT_RMHATB_MC.DISCDATE, dbo.BIT_RMHATB_MC.POSTDATE, dbo.BIT_RMHATB_MC.DINVPDOF, dbo.BIT_RMHATB_MC.DCURRNIDX,
dbo.BIT_RMHATB_MC.DXCHGRATE, dbo.BIT_RMHATB_MC.ORCASAMT, dbo.BIT_RMHATB_MC.ORSLSAMT, dbo.BIT_RMHATB_MC.ORCSTAMT,
dbo.BIT_RMHATB_MC.ORDAVAMT, dbo.BIT_RMHATB_MC.ORFRTAMT, dbo.BIT_RMHATB_MC.ORMISCAMT, dbo.BIT_RMHATB_MC.ORTAXAMT,
dbo.BIT_RMHATB_MC.ORCTRXAM, dbo.BIT_RMHATB_MC.ORORGTRX, dbo.BIT_RMHATB_MC.DORDISTKN, dbo.BIT_RMHATB_MC.DORWROFAM,
dbo.BIT_RMHATB_MC.DDENXRATE, dbo.BIT_RMHATB_MC.DMCTRXSTT, dbo.BIT_RMHATB_MC.Aging_Period_Amount, dbo.BIT_RMHATB_MC.APFRDCNM,
dbo.BIT_RMHATB_MC.APFRDCTY, dbo.BIT_RMHATB_MC.FROMCURR, dbo.BIT_RMHATB_MC.APTODCNM, dbo.BIT_RMHATB_MC.APTODCTY,
dbo.BIT_RMHATB_MC.APPTOAMT, dbo.BIT_RMHATB_MC.ACURNCYID, dbo.BIT_RMHATB_MC.DATE1, dbo.BIT_RMHATB_MC.POSTED,
dbo.BIT_RMHATB_MC.ADISTKNAM, dbo.BIT_RMHATB_MC.AWROFAMNT, dbo.BIT_RMHATB_MC.PPSAMDED, dbo.BIT_RMHATB_MC.GSTDSAMT,
dbo.BIT_RMHATB_MC.ACURRNIDX, dbo.BIT_RMHATB_MC.AXCHGRATE, dbo.BIT_RMHATB_MC.RLGANLOS, dbo.BIT_RMHATB_MC.ORAPTOAM,
dbo.BIT_RMHATB_MC.AORDISTKN, dbo.BIT_RMHATB_MC.AORWROFAM, dbo.BIT_RMHATB_MC.ADENXRATE, dbo.BIT_RMHATB_MC.AMCTRXSTT,
dbo.RM00201.CLASSID, dbo.RM00201.CLASDSCR, CASE WHEN MC020102.CURNCYID IS NULL THEN 'TTD' ELSE MC020102.CURNCYID END AS Currency,
dbo.MC020102.XCHGRATE, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.AGING_AMOUNT / dbo.MC020102.XCHGRATE, 2)
ELSE Aging_Amount END AS Aging_Amount_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Apply_AMOUNT / dbo.MC020102.XCHGRATE,
2) ELSE Apply_Amount END AS Apply_Amount_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Curtrxam / dbo.MC020102.XCHGRATE, 2)
ELSE Curtrxam END AS Curtrxam_MC, CASE WHEN xchgrate IS NOT NULL THEN round(dbo.BIT_RMHATB_MC.Ortrxamt / dbo.MC020102.XCHGRATE, 2)
ELSE Ortrxamt END AS Ortrxamt_MC, dbo.BIT_RMHATB_MC.CURTRXAM, CAST(RTRIM(dbo.BIT_RMHATB_MC.APFRDCNM) AS varchar)
+ '-' + CAST(dbo.BIT_RMHATB_MC.APFRDCTY AS varchar) AS ApfrDocNmTy
FROM dbo.MC020102 RIGHT OUTER JOIN
dbo.BIT_RMHATB_MC ON dbo.MC020102.RMDTYPAL = dbo.BIT_RMHATB_MC.RMDTYPAL AND dbo.MC020102.CUSTNMBR = dbo.BIT_RMHATB_MC.CUSTNMBR AND
dbo.MC020102.DOCNUMBR = dbo.BIT_RMHATB_MC.DOCNUMBR LEFT OUTER JOIN
dbo.RM00201 RIGHT OUTER JOIN
dbo.RM00101 ON dbo.RM00201.CLASSID = dbo.RM00101.CUSTCLAS ON dbo.BIT_RMHATB_MC.CUSTNMBR = dbo.RM00101.CUSTNMBR
GO
/****** Object: View [dbo].[BI_RMHATB4_NOAA_BASE] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA_BASE]
AS
SELECT dbo.BI_RMHATB4_NOAA.APPLY_AMOUNT, dbo.BI_RMHATB4_NOAA.AGING_AMOUNT, dbo.BI_RMHATB4_NOAA.CUSTNMBR, dbo.BI_RMHATB4_NOAA.CUSTNAME,
dbo.BI_RMHATB4_NOAA.BALNCTYP, dbo.BI_RMHATB4_NOAA.USERDEF1, dbo.BI_RMHATB4_NOAA.CNTCPRSN, dbo.BI_RMHATB4_NOAA.PHONE1,
dbo.BI_RMHATB4_NOAA.SLPRSNID, dbo.BI_RMHATB4_NOAA.SALSTERR, dbo.BI_RMHATB4_NOAA.PYMTRMID, dbo.BI_RMHATB4_NOAA.CRLMTAMT,
dbo.BI_RMHATB4_NOAA.CRLMTPER, dbo.BI_RMHATB4_NOAA.CRLMTPAM, dbo.BI_RMHATB4_NOAA.CRLMTTYP, dbo.BI_RMHATB4_NOAA.CUSTCLAS,
dbo.BI_RMHATB4_NOAA.SHRTNAME, dbo.BI_RMHATB4_NOAA.ZIP, dbo.BI_RMHATB4_NOAA.STATE, dbo.BI_RMHATB4_NOAA.CUDSCRIPTN,
dbo.BI_RMHATB4_NOAA.AGNGDATE, dbo.BI_RMHATB4_NOAA.CHCUMNUM, dbo.BI_RMHATB4_NOAA.DOCNUMBR, dbo.BI_RMHATB4_NOAA.RMDTYPAL,
dbo.BI_RMHATB4_NOAA.DSCRIPTN, dbo.BI_RMHATB4_NOAA.DCURNCYID, dbo.BI_RMHATB4_NOAA.ORTRXAMT AS Ortrxamt_Loc,
dbo.BI_RMHATB4_NOAA.CURTRXAM AS Curtrxam_Loc, dbo.BI_RMHATB4_NOAA.AGNGBUKT, dbo.BI_RMHATB4_NOAA.CASHAMNT,
dbo.BI_RMHATB4_NOAA.COMDLRAM, dbo.BI_RMHATB4_NOAA.SLSAMNT, dbo.BI_RMHATB4_NOAA.COSTAMNT, dbo.BI_RMHATB4_NOAA.FRTAMNT,
dbo.BI_RMHATB4_NOAA.MISCAMNT, dbo.BI_RMHATB4_NOAA.TAXAMNT, dbo.BI_RMHATB4_NOAA.DISAVAMT, dbo.BI_RMHATB4_NOAA.DDISTKNAM,
dbo.BI_RMHATB4_NOAA.DWROFAMNT, dbo.BI_RMHATB4_NOAA.TRXDSCRN, dbo.BI_RMHATB4_NOAA.DOCABREV, dbo.BI_RMHATB4_NOAA.CHEKNMBR,
dbo.BI_RMHATB4_NOAA.DOCDATE, dbo.BI_RMHATB4_NOAA.DUEDATE, dbo.BI_RMHATB4_NOAA.GLPOSTDT, dbo.BI_RMHATB4_NOAA.DISCDATE,
dbo.BI_RMHATB4_NOAA.POSTDATE, dbo.BI_RMHATB4_NOAA.DINVPDOF, dbo.BI_RMHATB4_NOAA.DCURRNIDX, dbo.BI_RMHATB4_NOAA.DXCHGRATE,
dbo.BI_RMHATB4_NOAA.ORCASAMT, dbo.BI_RMHATB4_NOAA.ORSLSAMT, dbo.BI_RMHATB4_NOAA.ORCSTAMT, dbo.BI_RMHATB4_NOAA.ORDAVAMT,
dbo.BI_RMHATB4_NOAA.ORFRTAMT, dbo.BI_RMHATB4_NOAA.ORMISCAMT, dbo.BI_RMHATB4_NOAA.ORTAXAMT, dbo.BI_RMHATB4_NOAA.ORCTRXAM,
dbo.BI_RMHATB4_NOAA.ORORGTRX, dbo.BI_RMHATB4_NOAA.DORDISTKN, dbo.BI_RMHATB4_NOAA.DORWROFAM, dbo.BI_RMHATB4_NOAA.DDENXRATE,
dbo.BI_RMHATB4_NOAA.DMCTRXSTT, dbo.BI_RMHATB4_NOAA.Aging_Period_Amount, dbo.BI_RMHATB4_NOAA.APFRDCNM, dbo.BI_RMHATB4_NOAA.APFRDCTY,
dbo.BI_RMHATB4_NOAA.FROMCURR, dbo.BI_RMHATB4_NOAA.APTODCNM, dbo.BI_RMHATB4_NOAA.APTODCTY, dbo.BI_RMHATB4_NOAA.APPTOAMT,
dbo.BI_RMHATB4_NOAA.ACURNCYID, dbo.BI_RMHATB4_NOAA.DATE1, dbo.BI_RMHATB4_NOAA.POSTED, dbo.BI_RMHATB4_NOAA.ADISTKNAM,
dbo.BI_RMHATB4_NOAA.AWROFAMNT, dbo.BI_RMHATB4_NOAA.PPSAMDED, dbo.BI_RMHATB4_NOAA.GSTDSAMT, dbo.BI_RMHATB4_NOAA.ACURRNIDX,
dbo.BI_RMHATB4_NOAA.AXCHGRATE, dbo.BI_RMHATB4_NOAA.RLGANLOS, dbo.BI_RMHATB4_NOAA.ORAPTOAM, dbo.BI_RMHATB4_NOAA.AORDISTKN,
dbo.BI_RMHATB4_NOAA.AORWROFAM, dbo.BI_RMHATB4_NOAA.ADENXRATE, dbo.BI_RMHATB4_NOAA.AMCTRXSTT, dbo.BI_RMHATB4_NOAA.CLASSID,
dbo.BI_RMHATB4_NOAA.CLASDSCR, dbo.BI_RMHATB4_NOAA.Currency, dbo.BI_RMHATB4_NOAA.XCHGRATE, dbo.BI_RMHATB4_NOAA.Aging_Amount_MC,
dbo.BI_RMHATB4_NOAA.Apply_Amount_MC, CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Curtrxam END AS Curtrxam,
CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Ortrxamt END AS Ortrxamt, FirstPay.FPDocNum, FirstPay.FPCurTrxamMC,
FirstPay.FPCurtrxam, FirstPay.FPAgingAmt, FirstPay.MaxDocNmTy, dbo.BI_RMHATB4_NOAA.ApfrDocNmTy
FROM dbo.BI_RMHATB4_NOAA LEFT OUTER JOIN
(SELECT DOCNUMBR AS FPDocNum, MAX(ApfrDocNmTy) AS MaxDocNmTy, Curtrxam_MC AS FPCurTrxamMC, CURTRXAM AS FPCurtrxam,
Aging_Amount_MC AS FPAgingAmt
FROM dbo.BI_RMHATB4_NOAA AS BI_RMHATB4_NOAA_1
WHERE (APFRDCTY > 0)
GROUP BY DOCNUMBR, Curtrxam_MC, CURTRXAM, Aging_Amount_MC) AS FirstPay ON dbo.BI_RMHATB4_NOAA.DOCNUMBR = FirstPay.FPDocNum AND
dbo.BI_RMHATB4_NOAA.ApfrDocNmTy = FirstPay.MaxDocNmTy
GO
/****** Object: View [dbo].[BI_RMHATB4_NOAA_MC] Script Date: 03/05/2019 10:31:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB4_NOAA_MC]
AS
SELECT dbo.BI_RMHATB4_NOAA.APPLY_AMOUNT AS APPLY_AMOUNT_LOC, dbo.BI_RMHATB4_NOAA.AGING_AMOUNT AS AGING_AMOUNT_LOC,
dbo.BI_RMHATB4_NOAA.CUSTNMBR, dbo.BI_RMHATB4_NOAA.CUSTNAME, dbo.BI_RMHATB4_NOAA.BALNCTYP, dbo.BI_RMHATB4_NOAA.USERDEF1,
dbo.BI_RMHATB4_NOAA.CNTCPRSN, dbo.BI_RMHATB4_NOAA.PHONE1, dbo.BI_RMHATB4_NOAA.SLPRSNID, dbo.BI_RMHATB4_NOAA.SALSTERR,
dbo.BI_RMHATB4_NOAA.PYMTRMID, dbo.BI_RMHATB4_NOAA.CRLMTAMT, dbo.BI_RMHATB4_NOAA.CRLMTPER, dbo.BI_RMHATB4_NOAA.CRLMTPAM,
dbo.BI_RMHATB4_NOAA.CRLMTTYP, dbo.BI_RMHATB4_NOAA.CUSTCLAS, dbo.BI_RMHATB4_NOAA.SHRTNAME, dbo.BI_RMHATB4_NOAA.ZIP,
dbo.BI_RMHATB4_NOAA.STATE, dbo.BI_RMHATB4_NOAA.CUDSCRIPTN, dbo.BI_RMHATB4_NOAA.AGNGDATE, dbo.BI_RMHATB4_NOAA.CHCUMNUM,
dbo.BI_RMHATB4_NOAA.DOCNUMBR, dbo.BI_RMHATB4_NOAA.RMDTYPAL, dbo.BI_RMHATB4_NOAA.DSCRIPTN, dbo.BI_RMHATB4_NOAA.DCURNCYID,
dbo.BI_RMHATB4_NOAA.ORTRXAMT AS Ortrxamt_Loc, dbo.BI_RMHATB4_NOAA.CURTRXAM AS Curtrxam_Loc, dbo.BI_RMHATB4_NOAA.AGNGBUKT,
dbo.BI_RMHATB4_NOAA.CASHAMNT, dbo.BI_RMHATB4_NOAA.COMDLRAM, dbo.BI_RMHATB4_NOAA.SLSAMNT, dbo.BI_RMHATB4_NOAA.COSTAMNT,
dbo.BI_RMHATB4_NOAA.FRTAMNT, dbo.BI_RMHATB4_NOAA.MISCAMNT, dbo.BI_RMHATB4_NOAA.TAXAMNT, dbo.BI_RMHATB4_NOAA.DISAVAMT,
dbo.BI_RMHATB4_NOAA.DDISTKNAM, dbo.BI_RMHATB4_NOAA.DWROFAMNT, dbo.BI_RMHATB4_NOAA.TRXDSCRN, dbo.BI_RMHATB4_NOAA.DOCABREV,
dbo.BI_RMHATB4_NOAA.CHEKNMBR, dbo.BI_RMHATB4_NOAA.DOCDATE, dbo.BI_RMHATB4_NOAA.DUEDATE, dbo.BI_RMHATB4_NOAA.GLPOSTDT,
dbo.BI_RMHATB4_NOAA.DISCDATE, dbo.BI_RMHATB4_NOAA.POSTDATE, dbo.BI_RMHATB4_NOAA.DINVPDOF, dbo.BI_RMHATB4_NOAA.DCURRNIDX,
dbo.BI_RMHATB4_NOAA.DXCHGRATE, dbo.BI_RMHATB4_NOAA.ORCASAMT, dbo.BI_RMHATB4_NOAA.ORSLSAMT, dbo.BI_RMHATB4_NOAA.ORCSTAMT,
dbo.BI_RMHATB4_NOAA.ORDAVAMT, dbo.BI_RMHATB4_NOAA.ORFRTAMT, dbo.BI_RMHATB4_NOAA.ORMISCAMT, dbo.BI_RMHATB4_NOAA.ORTAXAMT,
dbo.BI_RMHATB4_NOAA.ORCTRXAM, dbo.BI_RMHATB4_NOAA.ORORGTRX, dbo.BI_RMHATB4_NOAA.DORDISTKN, dbo.BI_RMHATB4_NOAA.DORWROFAM,
dbo.BI_RMHATB4_NOAA.DDENXRATE, dbo.BI_RMHATB4_NOAA.DMCTRXSTT, dbo.BI_RMHATB4_NOAA.Aging_Period_Amount, dbo.BI_RMHATB4_NOAA.APFRDCNM,
dbo.BI_RMHATB4_NOAA.APFRDCTY, dbo.BI_RMHATB4_NOAA.FROMCURR, dbo.BI_RMHATB4_NOAA.APTODCNM, dbo.BI_RMHATB4_NOAA.APTODCTY,
dbo.BI_RMHATB4_NOAA.APPTOAMT, dbo.BI_RMHATB4_NOAA.ACURNCYID, dbo.BI_RMHATB4_NOAA.DATE1, dbo.BI_RMHATB4_NOAA.POSTED,
dbo.BI_RMHATB4_NOAA.ADISTKNAM, dbo.BI_RMHATB4_NOAA.AWROFAMNT, dbo.BI_RMHATB4_NOAA.PPSAMDED, dbo.BI_RMHATB4_NOAA.GSTDSAMT,
dbo.BI_RMHATB4_NOAA.ACURRNIDX, dbo.BI_RMHATB4_NOAA.AXCHGRATE, dbo.BI_RMHATB4_NOAA.RLGANLOS, dbo.BI_RMHATB4_NOAA.ORAPTOAM,
dbo.BI_RMHATB4_NOAA.AORDISTKN, dbo.BI_RMHATB4_NOAA.AORWROFAM, dbo.BI_RMHATB4_NOAA.ADENXRATE, dbo.BI_RMHATB4_NOAA.AMCTRXSTT,
dbo.BI_RMHATB4_NOAA.CLASSID, dbo.BI_RMHATB4_NOAA.CLASDSCR, dbo.BI_RMHATB4_NOAA.Currency, dbo.BI_RMHATB4_NOAA.XCHGRATE,
dbo.BI_RMHATB4_NOAA.Aging_Amount_MC AS AGING_AMOUNT, dbo.BI_RMHATB4_NOAA.Apply_Amount_MC AS APPLY_AMOUNT, CASE WHEN (apfrdcnm > '0') AND
(FPDocNum IS NULL) THEN isnull(FPAgingAmt, 0) ELSE Curtrxam_MC END AS Curtrxam, CASE WHEN (apfrdcnm > '0') AND (FPDocNum IS NULL)
THEN isnull(FPAgingAmt, 0) ELSE Ortrxamt_MC END AS Ortrxamt, FirstPay.FPDocNum, FirstPay.FPCurTrxamMC, FirstPay.FPCurtrxam, FirstPay.FPAgingAmt,
FirstPay.MaxDocNmTy, dbo.BI_RMHATB4_NOAA.ApfrDocNmTy
FROM dbo.BI_RMHATB4_NOAA LEFT OUTER JOIN
(SELECT DOCNUMBR AS FPDocNum, MAX(ApfrDocNmTy) AS MaxDocNmTy, Curtrxam_MC AS FPCurTrxamMC, CURTRXAM AS FPCurtrxam,
Aging_Amount_MC AS FPAgingAmt
FROM dbo.BI_RMHATB4_NOAA AS BI_RMHATB4_NOAA_1
WHERE (APFRDCTY > 0)
GROUP BY DOCNUMBR, Curtrxam_MC, CURTRXAM, Aging_Amount_MC) AS FirstPay ON dbo.BI_RMHATB4_NOAA.DOCNUMBR = FirstPay.FPDocNum AND
dbo.BI_RMHATB4_NOAA.ApfrDocNmTy = FirstPay.MaxDocNmTy
GO
No comments:
Post a Comment