EnterpriseDW

Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.

N/A

If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.

DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM'SELECT CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12)) as PayableAK ,CAST(CASE WHEN Payable.PAYEE_ADDR_SEQ_NBR IS NULL THEN LEFT(Payable.PAYEE_TYPE,1) + Payable.PAYEE_ID ELSE LEFT(Payable.PAYEE_TYPE,1) + Payable.PAYEE_ID + (RIGHT('0000' + CAST(ISNULL(Payable.PAYEE_ADDR_SEQ_NBR,' ') AS VARCHAR(4)),4)) END AS VARCHAR(14)) AS PayeeAK ,CAST(PAY_AMOUNT AS NUMERIC(11,2)) AS PayAmount ,CAST(CASE WHEN Payable.PAY_DATE IS NULL THEN '1900-01-01' WHEN Payable.PAY_DATE = ' ' THEN '1900-01-01' WHEN ISDATE(Payable.PAY_DATE) = 1 THEN Payable.PAY_DATE ELSE '1900-01-01' END AS DATETIME) AS PayableDate ,CAST(CASE WHEN Payable.PAYMENT_DATE IS NULL THEN '1900-01-01' WHEN Payable.PAYMENT_DATE = ' ' THEN '1900-01-01' WHEN ISDATE(Payable.PAYMENT_DATE) = 1 THEN Payable.PAYMENT_DATE ELSE '1900-01-01' END AS DATETIME) AS PaymentDate ,CAST(CASE WHEN Payable.ORIGIN = 'PUR' and Va.VENDOR_ADDRESS = ' ' THEN '-1' WHEN Payable.ORIGIN = 'PUR' and Va.VENDOR_ADDRESS <> ' ' THEN LEFT(REVERSE(SUBSTRING(REVERSE(Va.VENDOR_ADDRESS),1,CHARINDEX(' ',REVERSE(Va.VENDOR_ADDRESS)) -1)),5) WHEN Payable.ORIGIN <> 'PUR' AND Payment.PAYEE_ZIP IS NOT NULL THEN LEFT(Payment.PAYEE_ZIP,5) WHEN Payable.ORIGIN <> 'PUR' AND Payment.PAYEE_ZIP IS NULL THEN CASE WHEN CHARINDEX(' ',REVERSE(PaymentAddr.PAYEE_ADDRESS)) < 2 THEN RIGHT(PaymentAddr.PAYEE_ADDRESS,5) ELSE LEFT(REVERSE(SUBSTRING(REVERSE(PaymentAddr.PAYEE_ADDRESS),1,CHARINDEX(' ',REVERSE(PaymentAddr.PAYEE_ADDRESS)) -1)),5) END ELSE '-1' END AS VARCHAR(5)) AS ZipCodeAK FROM AP_PAYABLE Payable LEFT OUTER JOIN AP_PAYMENT Payment ON ( LEFT(Payable.PAYMENT_XREF,4) = Payment.FISCAL_YEAR and RIGHT(Payable.PAYMENT_XREF,8) = Payment.PAYMENT_NBR ) LEFT OUTER JOIN FIP_PO_HDR Po ON ( LEFT(Payable.REF_NBR,4) = Po.FISCAL_YEAR AND SUBSTRING(Payable.REF_NBR,5,8) = Po.REF_NBR ) LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS Va ON ( Po.ISN_FIP_PO_HDR = Va.ISN_FIP_PO_HDR AND Va.cnxarraycolumn = 5 ) OUTER APPLY (SELECT TOP 1 ISN_AP_PAYMENT , PAYEE_ADDRESS , PAYEE_ADDRESS_COUNT , cnxarraycolumn FROM AP_PAYMENT_PAYEE_ADDRESS PaymentAddr1 WHERE PaymentAddr1.ISN_AP_PAYMENT = Payment.ISN_AP_PAYMENT and PAYEE_ADDRESS <> ' ' ORDER by cnxarraycolumn DESC ) as PaymentAddr WHERE (CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12))) IN ( /* Select changed records from AP Payable File */ SELECT CAST(FISCAL_YEAR + PAYABLE_NBR as varchar(12)) FROM AP_PAYABLE WHERE ( AP_PAYABLE.RowUpdatedOn >= @IncrementalBeginDtTm AND AP_PAYABLE.RowUpdatedOn <= @IncrementalEndDtTm ) )

"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'SELECT CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12)) as PayableAK ,CAST(CASE WHEN Payable.PAYEE_ADDR_SEQ_NBR IS NULL THEN LEFT(Payable.PAYEE_TYPE,1) + Payable.PAYEE_ID ELSE LEFT(Payable.PAYEE_TYPE,1) + Payable.PAYEE_ID + (RIGHT('0000' + CAST(ISNULL(Payable.PAYEE_ADDR_SEQ_NBR,' ') AS VARCHAR(4)),4)) END AS VARCHAR(14)) AS PayeeAK ,CAST(PAY_AMOUNT AS NUMERIC(11,2)) AS PayAmount ,CAST(CASE WHEN Payable.PAY_DATE IS NULL THEN '1900-01-01' WHEN Payable.PAY_DATE = ' ' THEN '1900-01-01' WHEN ISDATE(Payable.PAY_DATE) = 1 THEN Payable.PAY_DATE ELSE '1900-01-01' END AS DATETIME) AS PayableDate ,CAST(CASE WHEN Payable.PAYMENT_DATE IS NULL

This is the SQL statement to extract all the Payable Data from the Source System.

Jan 1 1900 12:00AM

The IncrementalBeginDtTm setting will contain the start date and time for which updates will be extracted. This is a configuration setting from SSIS_Configurations that are set in the Control Package.

Mar 4 2016 10:33AM

The IncrementalEndDtTm setting will contain the End Date and Time for which updates will be extracted. These values will be compared against the RowUpdatedOn column on the tables in the DWStaging database to determine which rows to extract and load into the Enterprise Data Warehouse.

Y

This SSIS_Configuration Setting will determine if a Full Load will be done or not.