Value |
Expression: "DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */
DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT
CAST('V' + Vendor.VA_VENDOR_CODE + (RIGHT('0000' + CAST(Vendor.VA_VENDOR_SEQ_NBR AS VARCHAR(4)),4)) AS VARCHAR(14)) as PayeeAK
,Vendor.VA_VENDOR_CODE AS PayeeId
,Vendor.VA_VENDOR_SEQ_NBR AS PayeeSequence
,'V' AS PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') AS PayeeType
,CAST(ISNULL(Vendor.VA_MAILING_ADDRESS_NAME, 'N/A') AS VARCHAR(50)) AS PayeeName
,CAST(ISNULL(VendorIsn.VENDOR_NAME,'N/A') AS VARCHAR(50)) AS PayeeNameConsolidated
,CAST(
CASE
WHEN Minority.MINORITY_CODE = NoMinority.FIELD_VALUE THEN 'No'
ELSE 'Yes'
END AS VARCHAR(10))
AS IsMinority
,ISNULL(CAST(VENDOR.VA_STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAS
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('V' + Vendor.VA_VENDOR_CODE + (RIGHT('0000' + CAST(Vendor.VA_VENDOR_SEQ_NBR AS VARCHAR(4)),4)) AS VARCHAR(14)) as PayeeAK
,Vendor.VA_VENDOR_CODE AS PayeeId
,Vendor.VA_VENDOR_SEQ_NBR AS PayeeSequence
,'V' AS PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') AS PayeeType
,CAST(ISNULL(Vendor.VA_MAILING_ADDRESS_NAME, 'N/A') AS VARCHAR(50)) AS PayeeName
,CAST(ISNULL(VendorIsn.VENDOR_NAME,'N/A') AS VARCHAR(50)) AS PayeeNameConsolidated
,CAST(
CASE
WHEN Minority.MINORITY_CODE = NoMinority.FIELD_VALUE THEN 'No'
ELSE 'Yes'
END AS VARCHAR(10))
AS IsMinority
,ISNULL(CAST(VENDOR.VA_STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAST(ISNULL(VENDOR.VA_ZIP, 'N/A') AS VARCHAR(11)) as ZipCode
,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country
FROM FIP_VENDOR Vendor
LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc
ON (
PayeeDesc.TABLE_NAME = 'PAYEETYPE'
AND PayeeDesc.CODE = 'V'
AND PayeeDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE StateDesc
ON (
StateDesc.TABLE_NAME = 'STATE'
AND StateDesc.CODE = VENDOR.VA_STATE
AND StateDesc.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc
ON (
CountryDesc.TABLE_NAME = 'COUNTRY-ST'
AND CountryDesc.CODE = VENDOR.VA_COUNTRY
AND CountryDesc.STATUS = 'A'
)
/* Find the No Minority code from the MNRTY-CAT table to check against */
LEFT OUTER JOIN UTL_CODE_TABLE NoMinorityCode
ON (
NoMinorityCode.TABLE_NAME = 'MNRTY-CAT'
AND NoMinorityCode.CODE = '7'
AND NoMinorityCode.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC NoMinority
ON (
NoMinority.ISN_UTL_CODE_TABLE = NoMinorityCode.ISN_UTL_CODE_TABLE
and NoMinority.cnxarraycolumn = 0
)
/* Get the Vendor Header to get the ISN to check the Minority Code */
LEFT OUTER JOIN FIP_VENDOR VendorIsn
ON (
VendorIsn.VENDOR_CODE = Vendor.VA_VENDOR_CODE
)
LEFT OUTER JOIN FIP_VENDOR_MINORITY_CODE Minority
ON (
Minority.ISN_FIP_VENDOR = VendorIsn.ISN_FIP_VENDOR
and Minority.cnxarraycolumn = 0
)
WHERE Vendor.VA_VENDOR_CODE IN
(
/* Select changed records from Empl File */
SELECT VA_VENDOR_CODE
FROM FIP_VENDOR
WHERE (
FIP_VENDOR.RowUpdatedOn >= @IncrementalBeginDtTm
AND FIP_VENDOR.RowUpdatedOn <= @IncrementalEndDtTm
AND FIP_VENDOR.VA_VENDOR_CODE <> ' '
)
UNION
SELECT VENDOR_CODE
FROM FIP_VENDOR
WHERE (
FIP_VENDOR.RowUpdatedOn >= @IncrementalBeginDtTm
AND FIP_VENDOR.RowUpdatedOn <= @IncrementalEndDtTm
AND FIP_VENDOR.VENDOR_CODE <> ' '
)
) 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('V' + Vendor.VA_VENDOR_CODE + (RIGHT('0000' + CAST(Vendor.VA_VENDOR_SEQ_NBR AS VARCHAR(4)),4)) AS VARCHAR(14)) as PayeeAK
,Vendor.VA_VENDOR_CODE AS PayeeId
,Vendor.VA_VENDOR_SEQ_NBR AS PayeeSequence
,'V' AS PayeeTypeCode
,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') AS PayeeType
,CAST(ISNULL(Vendor.VA_MAILING_ADDRESS_NAME, 'N/A') AS VARCHAR(50)) AS PayeeName
,CAST(ISNULL(VendorIsn.VENDOR_NAME,'N/A') AS VARCHAR(50)) AS PayeeNameConsolidated
,CAST(
CASE
WHEN Minority.MINORITY_CODE = NoMinority.FIELD_VALUE THEN 'No'
ELSE 'Yes'
END AS VARCHAR(10))
AS IsMinority
,ISNULL(CAST(VENDOR.VA_STATE AS VARCHAR(3)), 'N/A') AS StateCode
,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State
,CAS |