--declare variables used to call the wrapper stored procedure
DECLARE
@I_dAgingDate DATETIME = '1/1/2027',
@I_cStartCustomerNumber CHAR(15) = 'a',
@I_cEndCustomerNumber CHAR(15) = 'c',
@I_cStartCustomerName CHAR(65) = '',
@I_cEndCustomerName CHAR(65) = '',
@I_cStartClassID CHAR(15) = '',
@I_cEndClassID CHAR(15) = '',
@I_cStartSalesPersonID CHAR(15) = '',
@I_cEndSalesPersonID CHAR(15) = '',
@I_cStartSalesTerritory CHAR(15) = '',
@I_cEndSalesTerritory CHAR(15) = '',
@I_cStartShortName CHAR(15) = '',
@I_cEndShortName CHAR(15) = '',
@I_cStartState CHAR(5) = '',
@I_cEndState CHAR(5) = '',
@I_cStartZipCode CHAR(11) = '',
@I_cEndZipCode CHAR(11) = '',
@I_cStartPhoneNumber CHAR(21) = '',
@I_cEndPhoneNumber CHAR(21) = '',
@I_cStartUserDefined CHAR(15) = '',
@I_cEndUserDefined CHAR(15) = '',
@I_tUsingDocumentDate TINYINT = '',
@I_dStartDate DATETIME = '1/1/1900',
@I_dEndDate DATETIME = '1/1/2027',
@I_sIncludeBalanceTypes SMALLINT = '1',
@I_tExcludeNoActivity TINYINT = '1',
@I_tExcludeMultiCurrency TINYINT = '1',
@I_tExcludeZeroBalanceCustomer TINYINT = '1',
@I_tExcludeFullyPaidTrxs TINYINT = '1',
@I_tExcludeCreditBalance TINYINT = '1',
@I_tExcludeUnpostedAppldCrDocs TINYINT = '1',
@I_tConsolidateNAActivity TINYINT = '1'
--declare a table to hold the results of the wrapper stored procedure
--we need it in a table so that we can make changes to it later
declare @out table (
[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
)
--call the wrapper stored procedure, put the results into our table
INSERT INTO @out
EXEC seermHATBSRSWrapper
@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 ,
@I_tUsingDocumentDate ,
@I_dStartDate ,
@I_dEndDate ,
@I_sIncludeBalanceTypes ,
@I_tExcludeNoActivity ,
@I_tExcludeMultiCurrency ,
@I_tExcludeZeroBalanceCustomer,
@I_tExcludeFullyPaidTrxs ,
@I_tExcludeCreditBalance,
@I_tExcludeUnpostedAppldCrDocs ,
@I_tConsolidateNAActivity
--select statement that demonstrates adding the CSPORNMBR field from the RM20101 and RM30101
--Using '*' in production code is not best practice, but we're opting for readability here.
--remove the '*' and include the specific fields that you'd like in the output
SELECT rt.CSPORNBR,o.*
FROM @out o
LEFT JOIN (
SELECT docnumbr, rmdtypal, cspornbr FROM dbo.RM20101
UNION all
SELECT docnumbr, rmdtypal, cspornbr FROM dbo.RM30101
) rt ON rt.DOCNUMBR = o.DOCNUMBR AND rt.RMDTYPAL = o.RMDTYPAL