| Value |
 Expression: "DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'
SELECT
CAST('C' + Cust.CUSTOMER_ID as varchar(14)) as PayeeAK ,Cust.CUSTOMER_ID AS PayeeId
,0 as PayeeSequence
,'C' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeName
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,CAST(ISNULL(AddrBA.STATE,ISNULL(AddrAD.STATE,ISNULL(Addr0.STATE,'N/A'))) AS VARCHAR(3)) AS StateCode
,CAST(ISNULL(StateDescBA.DESCRIPTION,ISNULL(StateDescAD.DESCRIPTION,ISNULL(StateDesc0.DESCRIPTION,'N/A'))) AS VARCHAR(30)) AS State
,CAST(ISNULL(AddrBA.ZIP,ISNULL(AddrAD.ZIP,ISNULL(Addr0.ZIP,'N/A'))) AS VARCHAR(11)) AS ZipCode
,CAST(CASE
WHEN StateDes
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('C' + Cust.CUSTOMER_ID as varchar(14)) as PayeeAK ,Cust.CUSTOMER_ID AS PayeeId
,0 as PayeeSequence
,'C' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeName
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,CAST(ISNULL(AddrBA.STATE,ISNULL(AddrAD.STATE,ISNULL(Addr0.STATE,'N/A'))) AS VARCHAR(3)) AS StateCode
,CAST(ISNULL(StateDescBA.DESCRIPTION,ISNULL(StateDescAD.DESCRIPTION,ISNULL(StateDesc0.DESCRIPTION,'N/A'))) AS VARCHAR(30)) AS State
,CAST(ISNULL(AddrBA.ZIP,ISNULL(AddrAD.ZIP,ISNULL(Addr0.ZIP,'N/A'))) AS VARCHAR(11)) AS ZipCode
,CAST(CASE
WHEN StateDescBA.DESCRIPTION IS NOT NULL THEN CountryDesc.DESCRIPTION
WHEN StateDescBA.DESCRIPTION IS NULL AND StateDescAD.DESCRIPTION IS NOT NULL THEN CountryDesc.DESCRIPTION
WHEN StateDescBA.DESCRIPTION IS NULL AND StateDescAD.DESCRIPTION IS NULL AND StateDesc0.DESCRIPTION IS NOT NULL
THEN CountryDesc.DESCRIPTION
ELSE 'N/A' END
AS VARCHAR(30)) AS Country
FROM AR_CUSTOMER_A Cust
LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc
ON (
PayeeDesc.TABLE_NAME = 'PAYEETYPE'
AND PayeeDesc.CODE = 'C'
AND PayeeDesc.STATUS = 'A'
)
-- Get Billing Address
LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS AddrBA
ON (
Cust.ISN_AR_CUSTOMER_A = AddrBA.ISN_AR_CUSTOMER_A
AND AddrBA.TYPE = 'BA'
)
-- Get Physical Address
LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS AddrAD
ON (
Cust.ISN_AR_CUSTOMER_A = AddrAD.ISN_AR_CUSTOMER_A
AND AddrAD.TYPE = 'AD'
)
-- Get Billing Address
LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS Addr0
ON (
Cust.ISN_AR_CUSTOMER_A = Addr0.ISN_AR_CUSTOMER_A
AND Addr0.cnxarraycolumn = 0
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDescBA
ON (
StateDescBA.TABLE_NAME = 'STATE'
AND StateDescBA.CODE = AddrBA.STATE
AND StateDescBA.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDescAD
ON (
StateDescAD.TABLE_NAME = 'STATE'
AND StateDescAD.CODE = AddrAD.STATE
AND StateDescAD.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDesc0
ON (
StateDesc0.TABLE_NAME = 'STATE'
AND StateDesc0.CODE = Addr0.STATE
AND StateDesc0.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
ON (
CountryDesc.TABLE_NAME = 'COUNTRY-ST'
AND CountryDesc.CODE = 'US'
AND CountryDesc.STATUS = 'A'
)
WHERE Cust.CUSTOMER_ID IN
(
/* Select changed records from Cust File */
SELECT CUSTOMER_ID
FROM AR_CUSTOMER_A
WHERE (
AR_CUSTOMER_A.RowUpdatedOn >= @IncrementalBeginDtTm
AND AR_CUSTOMER_A.RowUpdatedOn <= @IncrementalEndDtTm
AND AR_CUSTOMER_A.CUSTOMER_TYPE <> 'ST'
)
) ORDER BY PayeeAK, PayeeName
|
| Expression |
"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'
SELECT
CAST('C' + Cust.CUSTOMER_ID as varchar(14)) as PayeeAK ,Cust.CUSTOMER_ID AS PayeeId
,0 as PayeeSequence
,'C' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeName
,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,CAST(ISNULL(AddrBA.STATE,ISNULL(AddrAD.STATE,ISNULL(Addr0.STATE,'N/A'))) AS VARCHAR(3)) AS StateCode
,CAST(ISNULL(StateDescBA.DESCRIPTION,ISNULL(StateDescAD.DESCRIPTION,ISNULL(StateDesc0.DESCRIPTION,'N/A'))) AS VARCHAR(30)) AS State
,CAST(ISNULL(AddrBA.ZIP,ISNULL(AddrAD.ZIP,ISNULL(Addr0.ZIP,'N/A'))) AS VARCHAR(11)) AS ZipCode
,CAST(CASE
WHEN StateDes |
Properties
