SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_AA_Dtl]
AS
SELECT DISTINCT
RTRIM(CASE WHEN dbo.AAG20000.DOCNUMBR IS NULL THEN sop30300.sopnumbe ELSE AAG20000.DOCNUMBR END) AS SOPNUMBE, dbo.SOP30300.ITEMNMBR,
dbo.SOP30300.ITEMDESC, dbo.AAG00401.aaTrxDimCodeDescr, dbo.AAG00401.aaTrxDimCode, dbo.AAG00401.aaTrxDimCodeDescr2,
dbo.AAG20000.DOCNUMBR
FROM dbo.AAG20003 INNER JOIN
dbo.AAG20001 ON dbo.AAG20003.aaSubLedgerDistID = dbo.AAG20001.aaSubLedgerDistID AND
dbo.AAG20003.aaSubLedgerHdrID = dbo.AAG20001.aaSubLedgerHdrID INNER JOIN
dbo.AAG00401 ON dbo.AAG20003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG20003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
dbo.AAG20000 ON dbo.AAG20001.aaSubLedgerHdrID = dbo.AAG20000.aaSubLedgerHdrID FULL OUTER JOIN
dbo.SOP30300 ON dbo.AAG20000.DOCNUMBR = dbo.SOP30300.SOPNUMBE
GO
/****** Object: Table [dbo].[BIT_RMHATB] Script Date: 1/26/2017 1:01:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIT_RMHATB](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AMCTRXSTT] [smallint] NOT NULL
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[BI_RMHATB2] Script Date: 1/26/2017 1:03:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB2]
AS
SELECT dbo.BIT_RMHATB.*, dbo.BI_SOP_AA_Dtl.ITEMNMBR, dbo.BI_SOP_AA_Dtl.ITEMDESC, dbo.BI_SOP_AA_Dtl.aaTrxDimCodeDescr,
dbo.BI_SOP_AA_Dtl.aaTrxDimCode
FROM dbo.BIT_RMHATB LEFT OUTER JOIN
dbo.BI_SOP_AA_Dtl ON dbo.BIT_RMHATB.DOCNUMBR = dbo.BI_SOP_AA_Dtl.SOPNUMBE
GO
/****** Object: View [dbo].[BI_RMHATB3] Script Date: 7/18/2017 7:41:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB3]
AS
select * from(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY docnumbr ORDER BY docnumbr DESC) AS rn
FROM BI_RMHATB2) as A
GO
-----------------------------------------------------------------------
CREATE VIEW [dbo].[BI_RMHATB4]
AS
SELECT APPLY_AMOUNT AS OrigAppAmt, AGING_AMOUNT AS OrigAgingAmt, CURTRXAM AS OrigCurTrxamt, APPLY_AMOUNT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN AGING_AMOUNT ELSE (CASE WHEN rmdtypal > 1 THEN AGING_AMOUNT ELSE 0 END) END AS AGING_AMOUNT, CUSTNMBR, CUSTNAME,
BALNCTYP, USERDEF1, CNTCPRSN, PHONE1, SLPRSNID, SALSTERR, PYMTRMID, CRLMTAMT, CRLMTPER, CRLMTPAM, CRLMTTYP, CUSTCLAS, SHRTNAME, ZIP,
STATE, CUDSCRIPTN, AGNGDATE, CHCUMNUM, DOCNUMBR, RMDTYPAL, DSCRIPTN, DCURNCYID, ORTRXAMT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN CURTRXAM ELSE (CASE WHEN rmdtypal > 1 THEN CURTRXAM ELSE 0 END) END AS CURTRXAM, AGNGBUKT, CASHAMNT, COMDLRAM,
SLSAMNT, COSTAMNT, FRTAMNT, MISCAMNT, TAXAMNT, DISAVAMT, DDISTKNAM, DWROFAMNT, TRXDSCRN, DOCABREV, CHEKNMBR, DOCDATE, DUEDATE,
GLPOSTDT, DISCDATE, POSTDATE, DINVPDOF, DCURRNIDX, DXCHGRATE, ORCASAMT, ORSLSAMT, ORCSTAMT, ORDAVAMT, ORFRTAMT, ORMISCAMT, ORTAXAMT,
ORCTRXAM, ORORGTRX, DORDISTKN, DORWROFAM, DDENXRATE, DMCTRXSTT, Aging_Period_Amount, APFRDCNM, APFRDCTY, FROMCURR, APTODCNM,
APTODCTY, APPTOAMT, ACURNCYID, DATE1, POSTED, ADISTKNAM, AWROFAMNT, PPSAMDED, GSTDSAMT, ACURRNIDX, AXCHGRATE, RLGANLOS, ORAPTOAM,
AORDISTKN, AORWROFAM, ADENXRATE, AMCTRXSTT, ITEMNMBR, ITEMDESC, aaTrxDimCodeDescr, aaTrxDimCode, rn
FROM dbo.BI_RMHATB3
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_RMHATB3]
AS
select * from(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY docnumbr ORDER BY docnumbr DESC) AS rn
FROM BI_RMHATB2) as A
GO
-----------------------------------------------------------------------
CREATE VIEW [dbo].[BI_RMHATB4]
AS
SELECT APPLY_AMOUNT AS OrigAppAmt, AGING_AMOUNT AS OrigAgingAmt, CURTRXAM AS OrigCurTrxamt, APPLY_AMOUNT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN AGING_AMOUNT ELSE (CASE WHEN rmdtypal > 1 THEN AGING_AMOUNT ELSE 0 END) END AS AGING_AMOUNT, CUSTNMBR, CUSTNAME,
BALNCTYP, USERDEF1, CNTCPRSN, PHONE1, SLPRSNID, SALSTERR, PYMTRMID, CRLMTAMT, CRLMTPER, CRLMTPAM, CRLMTTYP, CUSTCLAS, SHRTNAME, ZIP,
STATE, CUDSCRIPTN, AGNGDATE, CHCUMNUM, DOCNUMBR, RMDTYPAL, DSCRIPTN, DCURNCYID, ORTRXAMT, CASE WHEN rn = 1 AND
rmdtypal = 1 THEN CURTRXAM ELSE (CASE WHEN rmdtypal > 1 THEN CURTRXAM ELSE 0 END) END AS CURTRXAM, AGNGBUKT, CASHAMNT, COMDLRAM,
SLSAMNT, COSTAMNT, FRTAMNT, MISCAMNT, TAXAMNT, DISAVAMT, DDISTKNAM, DWROFAMNT, TRXDSCRN, DOCABREV, CHEKNMBR, DOCDATE, DUEDATE,
GLPOSTDT, DISCDATE, POSTDATE, DINVPDOF, DCURRNIDX, DXCHGRATE, ORCASAMT, ORSLSAMT, ORCSTAMT, ORDAVAMT, ORFRTAMT, ORMISCAMT, ORTAXAMT,
ORCTRXAM, ORORGTRX, DORDISTKN, DORWROFAM, DDENXRATE, DMCTRXSTT, Aging_Period_Amount, APFRDCNM, APFRDCTY, FROMCURR, APTODCNM,
APTODCTY, APPTOAMT, ACURNCYID, DATE1, POSTED, ADISTKNAM, AWROFAMNT, PPSAMDED, GSTDSAMT, ACURRNIDX, AXCHGRATE, RLGANLOS, ORAPTOAM,
AORDISTKN, AORWROFAM, ADENXRATE, AMCTRXSTT, ITEMNMBR, ITEMDESC, aaTrxDimCodeDescr, aaTrxDimCode, rn
FROM dbo.BI_RMHATB3
GO
/****** Object: StoredProcedure [dbo].[BI_seermHATBSRSWrapper] Script Date: 1/26/2017 1:01:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[BI_seermHATBSRSWrapper] @I_dAgingDate datetime = NULL, @I_cStartCustomerNumber char(15) = NULL, @I_cEndCustomerNumber char(15) = NULL, @I_cStartCustomerName char(65) = NULL, @I_cEndCustomerName char(65) = NULL, @I_cStartClassID char(15) = NULL, @I_cEndClassID char(15) = NULL, @I_cStartSalesPersonID char(15) = NULL, @I_cEndSalesPersonID char(15) = NULL, @I_cStartSalesTerritory char(15) = NULL, @I_cEndSalesTerritory char(15) = NULL, @I_cStartShortName char(15) = NULL, @I_cEndShortName char(15) = NULL, @I_cStartState char(5) = NULL, @I_cEndState char(5) = NULL, @I_cStartZipCode char(11) = NULL, @I_cEndZipCode char(11) = NULL, @I_cStartPhoneNumber char(21) = NULL, @I_cEndPhoneNumber char(21) = NULL, @I_cStartUserDefined char(15) = NULL, @I_cEndUserDefined char(15) = NULL, @I_tUsingDocumentDate tinyint = NULL, @I_dStartDate datetime = NULL, @I_dEndDate datetime = NULL, @I_sIncludeBalanceTypes smallint = NULL, @I_tExcludeNoActivity tinyint = NULL, @I_tExcludeMultiCurrency tinyint = NULL, @I_tExcludeZeroBalanceCustomer tinyint = NULL, @I_tExcludeFullyPaidTrxs tinyint = NULL, @I_tExcludeCreditBalance tinyint = NULL, @I_tExcludeUnpostedAppldCrDocs tinyint = NULL, @I_tConsolidateNAActivity tinyint = NULL as declare @FUNLCURR char(12) CREATE TABLE #RMHATBAP( [APFRDCNM] [char](21) NOT NULL, [APFRDCTY] [smallint] NOT NULL, [FROMCURR] [char](15) NOT NULL, [APTODCNM] [char](21) NOT NULL, [APTODCTY] [smallint] NOT NULL, [APPTOAMT] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [DATE1] [datetime] NOT NULL, [POSTED] [tinyint] NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [PPSAMDED] [numeric](19, 5) NOT NULL, [GSTDSAMT] [numeric](19, 5) NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [RLGANLOS] [numeric](19, 5) NOT NULL, [ORAPTOAM] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP ( [APFRDCNM] ASC, [APFRDCTY] ASC, [APTODCNM] ASC, [APTODCTY] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP ( [APTODCTY] ASC, [APTODCNM] ASC, [APFRDCTY] ASC, [APFRDCNM] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBDO( [CUSTNMBR] [char](15) NOT NULL, [CHCUMNUM] [char](15) NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [RMDTYPAL] [smallint] NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [CURNCYID] [char](15) NOT NULL, [ORTRXAMT] [numeric](19, 5) NOT NULL, [CURTRXAM] [numeric](19, 5) NOT NULL, [AGNGBUKT] [smallint] NOT NULL, [CASHAMNT] [numeric](19, 5) NOT NULL, [COMDLRAM] [numeric](19, 5) NOT NULL, [SLSAMNT] [numeric](19, 5) NOT NULL, [COSTAMNT] [numeric](19, 5) NOT NULL, [FRTAMNT] [numeric](19, 5) NOT NULL, [MISCAMNT] [numeric](19, 5) NOT NULL, [TAXAMNT] [numeric](19, 5) NOT NULL, [DISAVAMT] [numeric](19, 5) NOT NULL, [DISTKNAM] [numeric](19, 5) NOT NULL, [WROFAMNT] [numeric](19, 5) NOT NULL, [TRXDSCRN] [char](31) NOT NULL, [DOCABREV] [char](3) NOT NULL, [CHEKNMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [DUEDATE] [datetime] NOT NULL, [GLPOSTDT] [datetime] NOT NULL, [DISCDATE] [datetime] NOT NULL, [POSTDATE] [datetime] NOT NULL, [DINVPDOF] [datetime] NOT NULL, [CURRNIDX] [smallint] NOT NULL, [XCHGRATE] [numeric](19, 7) NOT NULL, [ORCASAMT] [numeric](19, 5) NOT NULL, [ORSLSAMT] [numeric](19, 5) NOT NULL, [ORCSTAMT] [numeric](19, 5) NOT NULL, [ORDAVAMT] [numeric](19, 5) NOT NULL, [ORFRTAMT] [numeric](19, 5) NOT NULL, [ORMISCAMT] [numeric](19, 5) NOT NULL, [ORTAXAMT] [numeric](19, 5) NOT NULL, [ORCTRXAM] [numeric](19, 5) NOT NULL, [ORORGTRX] [numeric](19, 5) NOT NULL, [ORDISTKN] [numeric](19, 5) NOT NULL, [ORWROFAM] [numeric](19, 5) NOT NULL, [DENXRATE] [numeric](19, 7) NOT NULL, [MCTRXSTT] [smallint] NOT NULL, [Aging_Period_Amount] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO ( [RMDTYPAL] ASC, [DOCNUMBR] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO ( [CUSTNMBR] ASC, [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO ( [CURNCYID] ASC, [DSCRIPTN] ASC, [DEX_ROW_ID] ASC ) CREATE TABLE #RMHATBCU( [CUSTNMBR] [char](15) NOT NULL, [DSCRIPTN] [char](31) NOT NULL, [AGNGDATE] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL ) CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU ( [DSCRIPTN] ASC, [CUSTNMBR] ASC ) select @FUNLCURR = rtrim(FUNLCURR) from MC40000 If @I_cEndCustomerNumber = '' begin set @I_cEndCustomerNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndCustomerName = '' begin set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ' End If @I_cEndClassID = '' begin set @I_cEndClassID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesPersonID = '' begin set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ' End If @I_cEndSalesTerritory = '' begin set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ' End If @I_cEndShortName = '' begin set @I_cEndShortName = 'þþþþþþþþþþþþþþþ' End If @I_cEndState = '' begin set @I_cEndState = 'þþþþþþþþþþþþþþþ' End If @I_cEndZipCode = '' begin set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ' End If @I_cEndPhoneNumber = '' begin set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ' End If @I_cEndUserDefined = '' begin set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ' End exec [rmHistoricalAgedTrialBalance] '#RMHATBCU', '#RMHATBDO', '#RMHATBAP', '', @I_dAgingDate, @I_cStartCustomerNumber, @I_cEndCustomerNumber, @I_cStartCustomerName, @I_cEndCustomerName, @I_cStartClassID, @I_cEndClassID, @I_cStartSalesPersonID, @I_cEndSalesPersonID, @I_cStartSalesTerritory, @I_cEndSalesTerritory, @I_cStartShortName, @I_cEndShortName, @I_cStartState, @I_cEndState, @I_cStartZipCode, @I_cEndZipCode, @I_cStartPhoneNumber, @I_cEndPhoneNumber, @I_cStartUserDefined, @I_cEndUserDefined, 1, 0, @I_tUsingDocumentDate, @I_dStartDate, @I_dEndDate, @I_sIncludeBalanceTypes, @I_tExcludeNoActivity, @I_tExcludeMultiCurrency, @I_tExcludeZeroBalanceCustomer, @I_tExcludeFullyPaidTrxs, @I_tExcludeCreditBalance, @I_tExcludeUnpostedAppldCrDocs, @I_tConsolidateNAActivity, @FUNLCURR, 0, 0, 1.0000, 0, 2, 0
Drop table BIT_RMHATB
Select RMHATB.* Into BIT_RMHATB FROM ( select case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT, case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT, isnull([CU].[CUSTNMBR], '') as [CUSTNMBR], isnull([C].[CUSTNAME], '') as [CUSTNAME], isnull([C].[BALNCTYP],0) as [BALNCTYP], isnull([C].[USERDEF1], '') as [USERDEF1], isnull([C].[CNTCPRSN],'') as [CNTCPRSN], isnull([C].[PHONE1], '') as [PHONE1], isnull([C].[SLPRSNID], '') as [SLPRSNID], isnull([C].[SALSTERR], '') as [SALSTERR], isnull([C].[PYMTRMID], '') as [PYMTRMID], isnull([C].[CRLMTAMT], 0) as [CRLMTAMT], isnull([C].[CRLMTPER], 0) as [CRLMTPER], isnull([C].[CRLMTPAM], 0) as [CRLMTPAM], isnull([C].[CRLMTTYP], 0) as [CRLMTTYP], isnull([C].[CUSTCLAS], '') as [CUSTCLAS], isnull([C].[SHRTNAME], '') as [SHRTNAME], isnull([C].[ZIP], '') as [ZIP], isnull([C].[STATE], '') as [STATE], isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN], isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE], isnull([D].[CHCUMNUM], '') as [CHCUMNUM], isnull([D].[DOCNUMBR], '') as [DOCNUMBR], isnull([D].[RMDTYPAL], 0) as [RMDTYPAL], isnull([D].[DSCRIPTN], '') as [DSCRIPTN], isnull([D].[CURNCYID], '') as [DCURNCYID], isnull([D].[ORTRXAMT], 0) as [ORTRXAMT], isnull([D].[CURTRXAM], 0) as [CURTRXAM], isnull([D].[AGNGBUKT], 0) as [AGNGBUKT], isnull([D].[CASHAMNT], 0) as [CASHAMNT], isnull([D].[COMDLRAM], 0) as [COMDLRAM], isnull([D].[SLSAMNT], 0) as [SLSAMNT], isnull([D].[COSTAMNT], 0) as [COSTAMNT], isnull([D].[FRTAMNT], 0) as [FRTAMNT], isnull([D].[MISCAMNT], 0) as [MISCAMNT], isnull([D].[TAXAMNT], 0) as [TAXAMNT], isnull([D].[DISAVAMT], 0) as [DISAVAMT], isnull([D].[DISTKNAM], 0) as [DDISTKNAM], isnull([D].[WROFAMNT], 0) as [DWROFAMNT], isnull([D].[TRXDSCRN], '') as [TRXDSCRN], isnull([D].[DOCABREV], '') as [DOCABREV], isnull([D].[CHEKNMBR], '') as [CHEKNMBR], isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE], isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE], isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT], isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE], isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE], isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF], isnull([D].[CURRNIDX], 0) as [DCURRNIDX], isnull([D].[XCHGRATE], 0) as [DXCHGRATE], isnull([D].[ORCASAMT], 0) as [ORCASAMT], isnull([D].[ORSLSAMT], 0) as [ORSLSAMT], isnull([D].[ORCSTAMT], 0) as [ORCSTAMT], isnull([D].[ORDAVAMT], 0) as [ORDAVAMT], isnull([D].[ORFRTAMT], 0) as [ORFRTAMT], isnull([D].[ORMISCAMT], 0) as [ORMISCAMT], isnull([D].[ORTAXAMT], 0) as [ORTAXAMT], isnull([D].[ORCTRXAM], 0) as [ORCTRXAM], isnull([D].[ORORGTRX], 0) as [ORORGTRX], isnull([D].[ORDISTKN], 0) as [DORDISTKN], isnull([D].[ORWROFAM], 0) as [DORWROFAM], isnull([D].[DENXRATE], 0) as [DDENXRATE], isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT], isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount], isnull([A].[APFRDCNM],'') as [APFRDCNM], isnull([A].[APFRDCTY], 0) as [APFRDCTY], isnull([A].[FROMCURR], '') as [FROMCURR], isnull([A].[APTODCNM], '') as [APTODCNM], isnull([A].[APTODCTY], 0) as [APTODCTY], isnull([A].[APPTOAMT], 0) as [APPTOAMT], isnull([A].[CURNCYID], '') as [ACURNCYID], isnull([A].[DATE1], '1900-01-01') as [DATE1], isnull([A].[POSTED], 0) as [POSTED], isnull([A].[DISTKNAM], 0) as [ADISTKNAM], isnull([A].[WROFAMNT], 0) as [AWROFAMNT], isnull([A].[PPSAMDED], 0) as [PPSAMDED], isnull([A].[GSTDSAMT], 0) as [GSTDSAMT], isnull([A].[CURRNIDX], 0) as [ACURRNIDX], isnull([A].[XCHGRATE], 0) as [AXCHGRATE], isnull([A].[RLGANLOS], 0) as [RLGANLOS], isnull([A].[ORAPTOAM], 0) as [ORAPTOAM], isnull([A].[ORDISTKN], 0) as [AORDISTKN], isnull([A].[ORWROFAM], 0) as [AORWROFAM], isnull([A].[DENXRATE], 0) as [ADENXRATE], isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT] FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB WHILE (SELECT COUNT(*) FROM (SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB WHERE ORTRXAMT<>0 GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0 BEGIN UPDATE BIT_RMHATB SET AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0 FROM BIT_RMHATB JOIN ( SELECT CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB GROUP BY CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) PARTIALLY_APPLIED ON PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB.APFRDCNM END
SELECT * FROM BI_RMHATB4
GO
No comments:
Post a Comment