| Value |
 Expression: "DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'
SELECT
CAST('S' + Student.STUDENT_ID as varchar(14)) as PayeeAK
,Student.STUDENT_ID as PayeeId
,0 as PayeeSequence
,'S' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(StdntAddr.STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAST(ISNULL(StdntAddr.ZIP_CD, 'N/A') AS VARCHAR(11)) as Zi
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('S' + Student.STUDENT_ID as varchar(14)) as PayeeAK
,Student.STUDENT_ID as PayeeId
,0 as PayeeSequence
,'S' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(StdntAddr.STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAST(ISNULL(StdntAddr.ZIP_CD, 'N/A') AS VARCHAR(11)) as ZipCode
,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country
FROM ST_STDNT_A Student
LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc
ON (
PayeeDesc.TABLE_NAME = 'PAYEETYPE'
AND PayeeDesc.CODE = 'S'
AND PayeeDesc.STATUS = 'A'
)
LEFT OUTER JOIN ST_ADDRESSES_A StdntAddr
ON (
Student.STUDENT_ID = StdntAddr.STUDENT_ID
AND StdntAddr.ADDR_TY = '01'
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDesc
ON (
StateDesc.TABLE_NAME = 'STATE'
AND StateDesc.CODE = StdntAddr.STATE
AND StateDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
ON (
CountryDesc.TABLE_NAME = 'COUNTRY-ST'
AND CountryDesc.CODE = StdntAddr.CNTRY
AND CountryDesc.STATUS = 'A'
)
WHERE Student.STUDENT_ID IN
(
/* Select changed records from Student File */
SELECT STUDENT_ID
FROM ST_STDNT_A
WHERE (
ST_STDNT_A.RowUpdatedOn >= @IncrementalBeginDtTm
AND ST_STDNT_A.RowUpdatedOn <= @IncrementalEndDtTm
)
UNION
/* Select changed records from Student Address File */
SELECT STUDENT_ID
FROM ST_ADDRESSES_A
WHERE (
ST_ADDRESSES_A.ADDR_TY = '01'
AND ST_ADDRESSES_A.RowUpdatedOn >= @IncrementalBeginDtTm
AND ST_ADDRESSES_A.RowUpdatedOn <= @IncrementalEndDtTm
)
) 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('S' + Student.STUDENT_ID as varchar(14)) as PayeeAK
,Student.STUDENT_ID as PayeeId
,0 as PayeeSequence
,'S' as PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeName
, CAST(ISNULL(Student.LST_NM, 'N/A') + ', '
+ ISNULL(Student.FRST_NM, ' ') + ' '
+ ISNULL(Student.MDL_NM, ' ')
AS varchar(50)) AS PayeeNameConsolidated
,CAST('Unknown' AS VARCHAR(10)) AS IsMinority
,ISNULL(CAST(StdntAddr.STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAST(ISNULL(StdntAddr.ZIP_CD, 'N/A') AS VARCHAR(11)) as Zi |
Properties
