| 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
,Payable.FISCAL_YEAR as FiscalYear
,Payable.PAYABLE_NBR as PayableNumber
,ISNULL(Payable.ORIGIN, 'N/A') as OriginCode
,ISNULL(OrigDesc.DESCRIPTION, 'N/A') as Origin
,ISNULL(Payable.REF_TYPE, 'N/A') as ReferenceTypeCode
,CASE
WHEN Payable.ORIGIN = 'DSBR' THEN ISNULL(DsbrDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'PUR' THEN ISNULL(RefTypesDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'MEMO' THEN ISNULL(CrTypesDesc.DESCRIPTION,'N/A')
ELSE ISNULL(RefTypesMiscDesc.DESCRIPTION,'N/A')
END AS ReferenceType
,CAST(CASE
WHEN REF_NBR IS NULL THEN 'N/A'
ELSE LEFT(REF_NBR,4) + '-' + SUBSTRING(REF_NBR
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
,Payable.FISCAL_YEAR as FiscalYear
,Payable.PAYABLE_NBR as PayableNumber
,ISNULL(Payable.ORIGIN, 'N/A') as OriginCode
,ISNULL(OrigDesc.DESCRIPTION, 'N/A') as Origin
,ISNULL(Payable.REF_TYPE, 'N/A') as ReferenceTypeCode
,CASE
WHEN Payable.ORIGIN = 'DSBR' THEN ISNULL(DsbrDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'PUR' THEN ISNULL(RefTypesDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'MEMO' THEN ISNULL(CrTypesDesc.DESCRIPTION,'N/A')
ELSE ISNULL(RefTypesMiscDesc.DESCRIPTION,'N/A')
END AS ReferenceType
,CAST(CASE
WHEN REF_NBR IS NULL THEN 'N/A'
ELSE LEFT(REF_NBR,4) + '-' + SUBSTRING(REF_NBR,5,15)
END AS VARCHAR(20))
AS ReferenceNumber ,ISNULL(Payment.PAYMENT_TYPE, 'N/A') as PaymentTypeCode
,CASE
WHEN Payment.PAYMENT_TYPE = 'CHK' THEN 'Check'
WHEN Payment.PAYMENT_TYPE = 'EFT' THEN 'Electronic Fund Transfer'
WHEN Payment.PAYMENT_TYPE = 'CCR' THEN 'Refund to Credit Card'
ELSE 'N/A'
END AS PaymentType
,ISNULL(Payment.CHECK_EFT_CCR_NBR,'N/A') AS PaymentTypeNumber
,CAST(ISNULL(Payable.PAY_STATUS, 'N/A') AS VARCHAR(3)) AS PayableStatusCode
,CAST(ISNULL(StatusDesc.STATUS_DESC, 'N/A') AS VARCHAR(30)) as PayableStatus
,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 '2098-01-01'
WHEN Payable.PAYMENT_DATE = ' ' THEN '2098-01-01'
WHEN ISDATE(Payable.PAYMENT_DATE) = 1 THEN Payable.PAYMENT_DATE
ELSE '2098-12-31'
END as DateTime) AS PaymentDate
,ISNULL(Payable.PAYMENT_XREF, 'N/A') as PaymentXrefNbr
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 UTL_CODE_TABLE OrigDesc
ON (
OrigDesc.TABLE_NAME = 'ORIGINS'
AND OrigDesc.CODE = Payable.ORIGIN
AND OrigDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE DsbrDesc
ON (
DsbrDesc.TABLE_NAME = 'DISBTYPES'
AND DsbrDesc.CODE = Payable.REF_TYPE
AND DsbrDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE RefTypesDesc
ON (
RefTypesDesc.TABLE_NAME = 'REF-TYPES'
AND RefTypesDesc.CODE = Payable.REF_TYPE
AND RefTypesDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE CrTypesDesc
ON (
CrTypesDesc.TABLE_NAME = 'CR-REASONS'
AND CrTypesDesc.CODE = Payable.REF_TYPE
AND CrTypesDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE RefTypesMiscDesc
ON (
RefTypesMiscDesc.TABLE_NAME = 'REFTY-MISC'
AND RefTypesMiscDesc.CODE = Payable.REF_TYPE
AND RefTypesMiscDesc.STATUS = 'A'
)
LEFT OUTER JOIN FIP_TABLES StatusDesc
ON (
StatusDesc.STATUS_CODE = Payable.PAY_STATUS
)
WHERE (CAST(Payable.FISCAL_YEAR + Payable.PAYABLE_NBR as varchar(12))) IN
(
/* Select changed records from Student 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
,Payable.FISCAL_YEAR as FiscalYear
,Payable.PAYABLE_NBR as PayableNumber
,ISNULL(Payable.ORIGIN, 'N/A') as OriginCode
,ISNULL(OrigDesc.DESCRIPTION, 'N/A') as Origin
,ISNULL(Payable.REF_TYPE, 'N/A') as ReferenceTypeCode
,CASE
WHEN Payable.ORIGIN = 'DSBR' THEN ISNULL(DsbrDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'PUR' THEN ISNULL(RefTypesDesc.DESCRIPTION,'N/A')
WHEN Payable.ORIGIN = 'MEMO' THEN ISNULL(CrTypesDesc.DESCRIPTION,'N/A')
ELSE ISNULL(RefTypesMiscDesc.DESCRIPTION,'N/A')
END AS ReferenceType
,CAST(CASE
WHEN REF_NBR IS NULL THEN 'N/A'
ELSE LEFT(REF_NBR,4) + '-' + SUBSTRING(REF_NBR |
Properties
