| Value |
 Expression: "DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'
SELECT
CAST('N' + RIGHT('000000000' + CAST(NonEmpl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK
,CAST(NonEmpl.PERSON_ID AS VARCHAR(9)) as PayeeId
,0 as PayeeSequence
,'N' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS
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('N' + RIGHT('000000000' + CAST(NonEmpl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK
,CAST(NonEmpl.PERSON_ID AS VARCHAR(9)) as PayeeId
,0 as PayeeSequence
,'N' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAST(ISNULL(Addr.ZIP, 'N/A') AS VARCHAR(11)) as ZipCode
,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country
FROM NON_EMPLOYEE_A NonEmpl
LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc
ON (
PayeeDesc.TABLE_NAME = 'PAYEETYPE'
AND PayeeDesc.CODE = 'N'
AND PayeeDesc.STATUS = 'A'
)
LEFT OUTER JOIN NON_EMPLOYEE_A_ADDRESS_INFO Addr
ON (
NonEmpl.ISN_NON_EMPLOYEE_A = Addr.ISN_NON_EMPLOYEE_A
AND Addr.cnxarraycolumn = 0
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDesc
ON (
StateDesc.TABLE_NAME = 'STATE'
AND StateDesc.CODE = Addr.STATE_CODE
AND StateDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
ON (
CountryDesc.TABLE_NAME = 'COUNTRY-ST'
AND CountryDesc.CODE = Addr.COUNTRY_CODE
AND CountryDesc.STATUS = 'A'
)
WHERE NonEmpl.PERSON_ID IN
(
/* Select changed records from NonEmpl File */
SELECT PERSON_ID
FROM NON_EMPLOYEE_A
WHERE (
NON_EMPLOYEE_A.RowUpdatedOn >= @IncrementalBeginDtTm
AND NON_EMPLOYEE_A.RowUpdatedOn <= @IncrementalEndDtTm
AND NON_EMPLOYEE_A.PERSON_ID > 0
)
) 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('N' + RIGHT('000000000' + CAST(NonEmpl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK
,CAST(NonEmpl.PERSON_ID AS VARCHAR(9)) as PayeeId
,0 as PayeeSequence
,'N' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', '
+ ISNULL(NonEmpl.FIRSTNAME, ' ') + ' '
+ ISNULL(NonEmpl.MIDDLE, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS |
Properties
