Dynamics GP - Simplified SP to Load seermHATBwrapper into a temp table

--Create Temp Table
/****** Object:  Table [dbo].[BI_HATB_Temp]    Script Date: 12/10/2014 2:47:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BI_HATB_Temp](
[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].[BI_HATB_Temp] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AMCTRXSTT] [smallint] NOT NULL

GO

SET ANSI_PADDING OFF
GO

--Create Custom Stored Procedure to clear table and load new data into it
/****** Object:  StoredProcedure [dbo].[BI_Hatb_load]    Script Date: 12/10/2014 3:00:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[BI_Hatb_load]
@I_dAgingDate1 datetime     = NULL
AS
BEGIN
set nocount on
truncate table  bi_hatb_temp
insert into bi_hatb_temp
([APPLY_AMOUNT]
      ,[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]
      ,[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]
 )


exec seermHATBSRSWrapper @I_dAgingDate=@I_dAgingDate1,@I_cStartCustomerNumber=N'',@I_cEndCustomerNumber=N'',@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',@I_cStartClassID=N'',@I_cEndClassID=N'',@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',@I_cStartShortName=N'',@I_cEndShortName=N'',@I_cStartState=N'',@I_cEndState=N'',@I_cStartZipCode=N'',@I_cEndZipCode=N'',@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',@I_tUsingDocumentDate=0,@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate='2014-12-09 00:00:00',@I_sIncludeBalanceTypes=0,@I_tExcludeNoActivity=1,@I_tExcludeMultiCurrency=1,@I_tExcludeZeroBalanceCustomer=1,@I_tExcludeFullyPaidTrxs=1,@I_tExcludeCreditBalance=0,@I_tExcludeUnpostedAppldCrDocs=1,@I_tConsolidateNAActivity=0

select * from BI_HATB_TEMP
END


GO



--Execute BI_HATB_TEMP with one parameter, aging date

exec bi_hatb_load @I_dAgingDate1='2014-12-31 00:00:00'