DimPayeeLoad
 GetVendorMods (Variable)
  Properties
Property Value
Name GetVendorMods
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
Value Type String
Description This variable contains the SQL Query that will retrieve the data from the FIP_VENDOR table to load into the DimPayee dimension.
System Variable False
Namespace User
Qualified Name User::GetVendorMods
Evaluate As Expression True
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
ID 8ee1f7be-103a-4f0b-b76a-8b8719286c2b
Raise Changed Event False
Powered by BI Documenter