| Value |
 Expression: "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
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
)
)
|
| Expression |
"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 |
Properties
