8 0 0 0 #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "RepairMain" || #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "Supply-Oth" || #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "Supply-Pkg" ? "LAURIE DULLER" : #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[Buyer]} VENDORGROUPID == "RepairMain" || VENDORGROUPID == "Supply-Oth" || VENDORGROUPID == "Supply-Pkg" ? "LAURIE DULLER" : Buyer #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "RepairMain" || #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "Supply-Oth" || #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]} == "Supply-Pkg" ? "Expense" : "" VENDORGROUPID == "RepairMain" || VENDORGROUPID == "Supply-Oth" || VENDORGROUPID == "Supply-Pkg" ? "Expense" : ""

SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees select * from (SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees) [refTable] where [refTable].[EmployeeName] = ? 0 0 0 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="EmployeeName" dataType="DT_WSTR" length="401" precision="0" scale="0" codePage="0"/><referenceColumn name="PERSONNELNUMBER" dataType="DT_WSTR" length="200" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[Buyer]}; 1252 false EmployeeName

PERSONNELNUMBER

SELECT RTRIM(VendorID) AS VendorID FROM dbo.ExpenseVendorExclusion select * from (SELECT RTRIM(VendorID) AS VendorID FROM dbo.ExpenseVendorExclusion) [refTable] where [refTable].[VendorID] = ? 0 0 1 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="VendorID" dataType="DT_WSTR" length="200" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[LegacyVendorID]}; 1252 false VendorID

select * from [dbo].[ExpenseVendors] select * from (select * from [dbo].[ExpenseVendors]) [refTable] where [refTable].[LegacyVendorID] = ? 0 0 1 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="VENDORACCOUNTNUMBER" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORORGANIZATIONNAME" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORSEARCHNAME" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORKNOWNASNAME" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORPARTYTYPE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORPARTYNUMBER" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="MAINCONTACTPERSONNELNUMBER" dataType="DT_WSTR" length="200" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYPHONENUMBER" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYPHONENUMBERDESCRIPTION" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYPHONENUMBEREXTENSION" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYPHONENUMBERPURPOSE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYFAXNUMBER" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYFAXNUMBERDESCRIPTION" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYFAXNUMBEREXTENSION" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="PRIMARYFAXNUMBERPURPOSE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORGROUPID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="BUYERGROUPID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="ISW9RECEIVED" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="CURRENCYCODE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="TAX1099FEDERALTAXID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="TAX1099IDTYPE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="TAX1099DOINGBUSINESSASNAME" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="TAX1099NAMETOUSE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="DEFAULTLEDGERDIMENSIONDISPLAYVALUE" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="LANGUAGEID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="LegacyVendorID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[LegacyVendorID]}; 1252 false LegacyVendorID

SELECT CONCAT([GroupCode],[AreaCode]) AS Groupcode ,[VENDORGROUPID] FROM [ERPDISDB].[dbo].[ExpenseVendorGroups] select * from (SELECT CONCAT([GroupCode],[AreaCode]) AS Groupcode ,[VENDORGROUPID] FROM [ERPDISDB].[dbo].[ExpenseVendorGroups]) [refTable] where [refTable].[Groupcode] = ? 0 0 0 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="Groupcode" dataType="DT_WSTR" length="500" precision="0" scale="0" codePage="0"/><referenceColumn name="VENDORGROUPID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Expense Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[VENDORGROUPID]}; 1252 false Groupcode VENDORGROUPID

0 UPDATE dbo.ExpenseVendors SET [VENDORORGANIZATIONNAME] = ?, [VENDORSEARCHNAME] = ?, [VENDORKNOWNASNAME] = ?, [VENDORPARTYTYPE] = ?, [MAINCONTACTPERSONNELNUMBER] = ?, [PRIMARYPHONENUMBER] = ?, [PRIMARYPHONENUMBERDESCRIPTION] = ?, [PRIMARYPHONENUMBEREXTENSION] = ?, [PRIMARYPHONENUMBERPURPOSE] = ?, [PRIMARYFAXNUMBER] = ?, [PRIMARYFAXNUMBERDESCRIPTION] = ?, [PRIMARYFAXNUMBEREXTENSION] = ?, [PRIMARYFAXNUMBERPURPOSE] = ?, [VENDORGROUPID] = ?, [BUYERGROUPID] = ?, [ISW9RECEIVED] = ?, [CURRENCYCODE] = ?, [TAX1099FEDERALTAXID] = ?, [TAX1099IDTYPE] = ?, [TAX1099DOINGBUSINESSASNAME] = ?, [TAX1099NAMETOUSE] = ?, [DEFAULTLEDGERDIMENSIONDISPLAYVALUE] = ?, [LANGUAGEID] = ?, [DEFAULTPAYMENTTERMSNAME] = ?, [CASHDISCOUNTCODE] = ?, [TAX1099BOXID] = ?, [ONHOLDSTATUS] = ?, ISREPORTINGTAX1099 = ?, ISW9CHECKINGENABLED = ?, ISFOREIGNENTITY = ?, PERSONFIRSTNAME = ?, PERSONLASTNAME = ?, TAX1099TYPE = ? WHERE [LegacyVendorID] = ? 1252 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65

0 [dbo].[ExpenseVendors] 1252 false 3 false false TABLOCK,CHECK_CONSTRAINTS 2147483647

0 SELECT CONVERT (NVARCHAR(200),1000000+ ROW_NUMBER() OVER (ORDER BY Vendors.[vm_add_date])) AS VENDORACCOUNTNUMBER, CONVERT(NVARCHAR(200),CASE WHEN LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END) IN ('EMP','PEM') THEN '' ELSE Vendors.vm_ap_name END) AS VENDORORGANIZATIONNAME, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_ap_addr1 LIKE 'DBA%' OR vendors.vm_ap_addr1 LIKE 'D/B/A%' THEN Vendors.vm_ap_addr1 ELSE Vendors.vm_ap_name END) AS VENDORSEARCHNAME, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_ap_addr1 LIKE 'DBA%' OR vendors.vm_ap_addr1 LIKE 'D/B/A%' THEN Vendors.vm_ap_addr1 ELSE '' END) AS VENDORKNOWNASNAME, CONVERT(NVARCHAR(200),CASE WHEN LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END) IN ('EMP','PEM') THEN'Person' ELSE 'Organization' END) AS VENDORPARTYTYPE, CONVERT(NVARCHAR(200),CASE WHEN LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END) IN ('EMP','PEM') THEN CASE WHEN CHARINDEX(',',Vendors.vm_ap_name) = 0 THEN '' ELSE SUBSTRING(Vendors.vm_ap_name,CHARINDEX(',',Vendors.vm_ap_name)+2,LEN(Vendors.vm_ap_name)-CHARINDEX(',',Vendors.vm_ap_name)) END ELSE '' END) AS PERSONFIRSTNAME, CONVERT(NVARCHAR(200),CASE WHEN LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END) IN ('EMP','PEM') THEN CASE WHEN CHARINDEX(',',Vendors.vm_ap_name) = 0 THEN vm_ap_name ELSE LEFT(Vendors.vm_ap_name,CHARINDEX(',',Vendors.vm_ap_name)-1) END ELSE '' END) AS PERSONLASTNAME, --CASE WHEN Vendors.vm_addr1 IS NOT NULL AND Vendors.vm_addr2 IS NOT NULL THEN 'Business' ELSE 'Primary' END AS ADDRESSLOCATIONROLES, --Vendors.vm_city AS ADDRESSCITY, --Vendors.vm_state AS ADDRESSCOUNTRYREGIONID, --Vendors.vm_state AS ADDRESSCOUNTRYREGIONISOCODE, --Vendors.vm_zip AS ADDRESSZIPCODE, CONVERT(NVARCHAR(200),Vendors.vm_ap_telephone) AS PRIMARYPHONENUMBER, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_ap_telephone IS NULL THEN '' WHEN LEFT(vendors.vm_ap_telephone,1) IN ('.',CHAR(45),'') THEN '' ELSE 'Business Phone' END) AS PRIMARYPHONENUMBERDESCRIPTION, CONVERT(NVARCHAR(200),'') AS PRIMARYPHONENUMBEREXTENSION, CONVERT(NVARCHAR(200),'') AS PRIMARYPHONENUMBERPURPOSE, CONVERT(NVARCHAR(200),Vendors.vm_ap_fax) AS PRIMARYFAXNUMBER, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_ap_fax IS NULL THEN '' WHEN Vendors.vm_ap_fax IN ('.',CHAR(45),'') THEN '' ELSE 'Fax' END) AS PRIMARYFAXNUMBERDESCRIPTION, CONVERT(NVARCHAR(200),'') AS PRIMARYFAXNUMBEREXTENSION, CONVERT(NVARCHAR(200),'') AS PRIMARYFAXNUMBERPURPOSE, CONVERT(NVARCHAR(200), CONCAT(LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END), CASE WHEN LEFT(Vendors.vm_ID,PATINDEX('%[0-9]%',Vendors.vm_id)-CASE WHEN PATINDEX('%[0-9]%',Vendors.vm_id) > 1 THEN 1 ELSE 0 END) IN ('BAN','LEG','EMP','MAN','TXG','PEM') THEN Vendors.vm_area_resp ELSE '' END)) AS VENDORGROUPID, CONVERT(NVARCHAR(200),/*'Supplies'*/'') AS BUYERGROUPID , --Vendors.vm_pref_via AS DEFAULTDELIVERYMODEID, --CASE WHEN terms.vm_payTerms IS NOT NULL THEN terms.D365Terms ELSE ''/*Vendors.vm_pay_terms*/ END AS DEFAULTPAYMENTTERMSNAME, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('I','N','') THEN 'No' ELSE 'Yes' END) AS ISW9RECEIVED, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_flags_09 = 'D' THEN 'USD' WHEN ISNULL(Vendors.vm_flags_09,'') = '' THEN 'USD'-- (default) WHEN Vendors.vm_flags_09 = 'C' THEN 'CAD' WHEN Vendors.vm_flags_09 = 'B' THEN 'GBP' --(British Pond) WHEN Vendors.vm_flags_09 = 'E' THEN 'EUR' --(Euro) WHEN Vendors.vm_flags_09 = 'Y' THEN 'JPY' --(Japanese Yen) ELSE '' END) AS CURRENCYCODE, CONVERT(NVARCHAR(200),CASE vendors.vm_flags_08 WHEN '1' THEN 'MISC-01' WHEN '2' THEN 'MISC-02' WHEN '4' THEN 'INT-1' WHEN '6' THEN 'MISC-06' WHEN '7' THEN 'NEC-01' ELSE '' END) AS TAX1099BOXID, CONVERT(NVARCHAR(200),CASE vendors.vm_flags_08 WHEN '1' THEN 'F1099MISC' WHEN '2' THEN 'F1099MISC' WHEN '4' THEN 'F1099INT' WHEN '6' THEN 'F1099MISC' WHEN '7' THEN 'F1099NEC' ELSE '' END) AS TAX1099TYPE, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_tax_id LIKE '[A-Z]%' THEN '' ELSE vendors.vm_tax_id END ) AS TAX1099FEDERALTAXID, CONVERT(NVARCHAR(200),CASE WHEN SUBSTRING(Vendors.vm_tax_id,3,1) = '-' THEN 'EIN' WHEN SUBSTRING(Vendors.vm_tax_id,4,1) = '-' THEN 'SSN' ELSE '' END) AS TAX1099IDTYPE, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('C','I','M','N') THEN 'No' ELSE 'Yes' END) AS ISREPORTINGTAX1099, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('I') THEN 'No' ELSE 'Yes' END) AS ISW9CHECKINGENABLED, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('F') THEN 'Yes' ELSE 'No' END) AS ISFOREIGNENTITY, CONVERT(NVARCHAR(200),vm_ap_person) AS TAX1099DOINGBUSINESSASNAME, CONVERT(NVARCHAR(200),'DBA') AS TAX1099NAMETOUSE, CONVERT(NVARCHAR(200),CASE WHEN vendors.vm_hold_code = 'Y' THEN '3' ELSE '0' END) AS ONHOLDSTATUS, --Vendors.vm_hold_code AS HoldCode, --Vendors.vm_hold_reason AS HoldReason, --Vendors.vm_area_resp AS AreaResp, CONVERT(NVARCHAR(200),'') AS DEFAULTPAYMENTTERMSNAME, CONVERT(NVARCHAR(200),'') AS CASHDISCOUNTCODE, --Vendors.vm_due_days AS CASHDISCOUNTCODE, --Vendors.vm_balance AS Balance, CONVERT(NVARCHAR(200),'-01-----') AS DEFAULTLEDGERDIMENSIONDISPLAYVALUE, CONVERT(NVARCHAR(200),'en-US ') AS LANGUAGEID, CONVERT(NVARCHAR(200),RTRIM(Vendors.vm_id)) AS LegacyVendorID, CONVERT(NVARCHAR(200),vm_buyer_locn+vm_buyer) AS Buyer FROM dbo.vendors vendors --LEFT JOIN #Terms terms --ON terms.vm_payTerms = vendors.vm_pay_terms --LEFT JOIN dbo.gtext gttext --ON gttext.gt_ID = vendors.vm_id --AND gttext.gt_from = '1099DA' --LEFT JOIN dbo.tables tables --ON tables.tab_code = vendors.vm_state --AND tables.tab_type = 'STATE' WHERE Vendors.vm_type in ('A', 'M') ORDER BY vendors.vm_id 1252 false 2

SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees select * from (SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees) [refTable] where [refTable].[EmployeeName] = ? 0 0 0 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="EmployeeName" dataType="DT_WSTR" length="401" precision="0" scale="0" codePage="0"/><referenceColumn name="PERSONNELNUMBER" dataType="DT_WSTR" length="200" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Inventory Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[Buyer]}; 1252 false EmployeeName

PERSONNELNUMBER

SELECT RTRIM(LegacyVendorID) AS LegacyVendorID FROM dbo.InventoryVendors select * from (SELECT RTRIM(LegacyVendorID) AS LegacyVendorID FROM dbo.InventoryVendors) [refTable] where [refTable].[LegacyVendorID] = ? 0 0 1 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="LegacyVendorID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Inventory Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[LegacyVendorID]}; 1252 false LegacyVendorID

SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees select * from (SELECT CONCAT(RTRIM(FIRSTNAME),' ',RTRIM(LASTNAME)) AS EmployeeName, PERSONNELNUMBER FROM dbo.Employees) [refTable] where [refTable].[EmployeeName] = ? 0 0 0 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="EmployeeName" dataType="DT_WSTR" length="401" precision="0" scale="0" codePage="0"/><referenceColumn name="PERSONNELNUMBER" dataType="DT_WSTR" length="200" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Inventory Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[LMCEXPEDITORPERSONNELNUMBER]}; 1252 false EmployeeName PERSONNELNUMBER

SELECT RTRIM([LegacyVendorID]) AS [LegacyVendorID] ,[DEFAULTPAYMENTTERMSNAME] ,[CASHDISCOUNTCOUNT] FROM [ERPDISDB].[dbo].[VendorPayTerms] select * from (SELECT RTRIM([LegacyVendorID]) AS [LegacyVendorID] ,[DEFAULTPAYMENTTERMSNAME] ,[CASHDISCOUNTCOUNT] FROM [ERPDISDB].[dbo].[VendorPayTerms]) [refTable] where [refTable].[LegacyVendorID] = ? 0 0 0 0 25 25 <referenceMetadata><referenceColumns><referenceColumn name="LegacyVendorID" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="DEFAULTPAYMENTTERMSNAME" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="CASHDISCOUNTCOUNT" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata> #{Package\Inventory Vendors DFT\OLE DB Source.Outputs[OLE DB Source Output].Columns[LegacyVendorID]}; 1252 false LegacyVendorID

DEFAULTPAYMENTTERMSNAME CASHDISCOUNTCOUNT

0 UPDATE dbo.InventoryVendors SET [VENDORORGANIZATIONNAME] = ?, [VENDORSEARCHNAME] = ?, [VENDORKNOWNASNAME] = ?, [VENDORPARTYTYPE] = ?, [MAINCONTACTPERSONNELNUMBER] = ?, [PRIMARYPHONENUMBER] = ?, [PRIMARYPHONENUMBERDESCRIPTION] = ?, [PRIMARYPHONENUMBEREXTENSION] = ?, [PRIMARYPHONENUMBERPURPOSE] = ?, [PRIMARYFAXNUMBER] = ?, [PRIMARYFAXNUMBERDESCRIPTION] = ?, [PRIMARYFAXNUMBEREXTENSION] = ?, [PRIMARYFAXNUMBERPURPOSE] = ?, [VENDORGROUPID] = ?, [BUYERGROUPID] = ?, [ISW9RECEIVED] = ?, [CURRENCYCODE] = ?, [TAX1099FEDERALTAXID] = ?, [TAX1099IDTYPE] = ?, [TAX1099DOINGBUSINESSASNAME] = ?, [TAX1099NAMETOUSE] = ?, [DEFAULTLEDGERDIMENSIONDISPLAYVALUE] = ?, [LANGUAGEID] = ?, [DEFAULTPAYMENTTERMSNAME] = ?, [CASHDISCOUNTCODE] = ?, TAX1099BOXID = ?, ONHOLDSTATUS = ?, ISREPORTINGTAX1099 = ?, ISW9CHECKINGENABLED = ?, ISFOREIGNENTITY = ?, TAX1099TYPE = ?, LMCEXPEDITORPERSONNELNUMBER = ? WHERE [LegacyVendorID] = ? 1252 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65

0 [dbo].[InventoryVendors] 1252 false 3 false false TABLOCK,CHECK_CONSTRAINTS 2147483647

0 SELECT CONVERT (NVARCHAR(200),RTRIM(Vendors.vm_id)) AS VENDORACCOUNTNUMBER, CONVERT(NVARCHAR(200),Vendors.vm_name) AS VENDORORGANIZATIONNAME, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_addr1 LIKE 'DBA%' OR vendors.vm_addr1 LIKE 'D/B/A%' THEN Vendors.vm_addr1 ELSE Vendors.vm_name END) AS VENDORSEARCHNAME, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_addr1 LIKE 'DBA%' OR vendors.vm_addr1 LIKE 'D/B/A%' THEN Vendors.vm_addr1 ELSE '' END) AS VENDORKNOWNASNAME, CONVERT(NVARCHAR(200),'Organization') AS VENDORPARTYTYPE, CAST(CONCAT('PTY',RIGHT(CONVERT(CHAR(10),ROW_NUMBER() OVER (ORDER BY vendors.vm_add_date) + 1000000000 + ?),9)) AS NVARCHAR(200)) AS PARTYNUMBER, --CASE WHEN Vendors.vm_addr1 IS NOT NULL AND Vendors.vm_addr2 IS NOT NULL THEN 'Business' ELSE 'Primary' END AS ADDRESSLOCATIONROLES, --Vendors.vm_city AS ADDRESSCITY, --Vendors.vm_state AS ADDRESSCOUNTRYREGIONID, --Vendors.vm_state AS ADDRESSCOUNTRYREGIONISOCODE, --Vendors.vm_zip AS ADDRESSZIPCODE, CONVERT(NVARCHAR(200),Vendors.vm_telephone) AS PRIMARYPHONENUMBER, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_telephone IS NULL THEN '' WHEN LEFT(vendors.vm_telephone,1) IN ('.',CHAR(45),'') THEN '' ELSE 'Business Phone' END) AS PRIMARYPHONENUMBERDESCRIPTION, CONVERT(NVARCHAR(200),'') AS PRIMARYPHONENUMBEREXTENSION, CONVERT(NVARCHAR(200),'') AS PRIMARYPHONENUMBERPURPOSE, CONVERT(NVARCHAR(200),Vendors.vm_fax_no) AS PRIMARYFAXNUMBER, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_fax_no IS NULL THEN '' WHEN Vendors.vm_fax_no IN ('.',CHAR(45),'') THEN '' ELSE 'Fax' END) AS PRIMARYFAXNUMBERDESCRIPTION, CONVERT(NVARCHAR(200),'') AS PRIMARYFAXNUMBEREXTENSION, CONVERT(NVARCHAR(200),'') AS PRIMARYFAXNUMBERPURPOSE, CONVERT(NVARCHAR(200),'Inventory') AS VENDORGROUPID, CONVERT(NVARCHAR(200),CASE WHEN vendors.[vm_buyer] in ('5', '14', '15', 'B', 'L', 'T', 'U') THEN 'Apparel' WHEN [vendors].[vm_buyer] in ('12', '17', '19', 'J', 'K', 'M', 'Q', 'R', 'Y') THEN 'Drag' WHEN [vendors].[vm_buyer] in ('7', '11', 'E', 'G', 'N', 'O', 'Z') THEN 'Offroad' WHEN [vendors].[vm_buyer] in ('3', '21', '22', 'H', 'S', 'X') THEN 'PU' WHEN [vendors].[vm_buyer] in ('2', '9', '10', 'A', 'I', 'P', 'W') THEN 'Street' ELSE '' END) AS BUYERGROUPID , --Vendors.vm_pref_via AS DEFAULTDELIVERYMODEID, --CASE WHEN terms.vm_payTerms IS NOT NULL THEN terms.D365Terms ELSE ''/*Vendors.vm_pay_terms*/ END AS DEFAULTPAYMENTTERMSNAME, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('I','N','') THEN 'No' ELSE 'Yes' END) AS ISW9RECEIVED, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_flags_09 = 'D' THEN 'USD' WHEN ISNULL(Vendors.vm_flags_09,'') = '' THEN 'USD'-- (default) WHEN Vendors.vm_flags_09 = 'C' THEN 'CAD' WHEN Vendors.vm_flags_09 = 'B' THEN 'GBP' --(British Pond) WHEN Vendors.vm_flags_09 = 'E' THEN 'EUR' --(Euro) WHEN Vendors.vm_flags_09 = 'Y' THEN 'JPY' --(Japanese Yen) ELSE '' END) AS CURRENCYCODE, CONVERT(NVARCHAR(200),CASE vendors.vm_flags_08 WHEN '1' THEN 'MISC-01' WHEN '2' THEN 'MISC-02' WHEN '4' THEN 'INT-1' WHEN '6' THEN 'MISC-06' WHEN '7' THEN 'NEC-01' ELSE '' END) AS TAX1099BOXID, CONVERT(NVARCHAR(200),CASE vendors.vm_flags_08 WHEN '1' THEN 'F1099MISC' WHEN '2' THEN 'F1099MISC' WHEN '4' THEN 'F1099INT' WHEN '6' THEN 'F1099MISC' WHEN '7' THEN 'F1099NEC' ELSE '' END) AS TAX1099TYPE, CONVERT(NVARCHAR(200),CASE WHEN Vendors.vm_tax_id LIKE '[A-Z]%' THEN '' ELSE vendors.vm_tax_id END ) AS TAX1099FEDERALTAXID, CONVERT(NVARCHAR(200),CASE WHEN SUBSTRING(Vendors.vm_tax_id,3,1) = '-' THEN 'EIN' WHEN SUBSTRING(Vendors.vm_tax_id,4,1) = '-' THEN 'SSN' ELSE '' END) AS TAX1099IDTYPE, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('C','I','M','N') THEN 'No' ELSE 'Yes' END) AS ISREPORTINGTAX1099, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('I') THEN 'No' ELSE 'Yes' END) AS ISW9CHECKINGENABLED, CONVERT(NVARCHAR(200),CASE WHEN vm_flags_08 IN ('F') THEN 'Yes' ELSE 'No' END) AS ISFOREIGNENTITY, CONVERT(NVARCHAR(200),vm_ap_person) AS TAX1099DOINGBUSINESSASNAME, CONVERT(NVARCHAR(200),'DBA') AS TAX1099NAMETOUSE, CONVERT(NVARCHAR(200),CASE WHEN vendors.vm_hold_code = 'Y' THEN '3' ELSE '0' END) AS ONHOLDSTATUS, --Vendors.vm_hold_code AS HoldCode, --Vendors.vm_hold_reason AS HoldReason, --Vendors.vm_area_resp AS AreaResp, --Vendors.vm_disc_days AS DEFAULTPAYMENTTERMSNAME, --Vendors.vm_disc_rate AS CASHDISCOUNTCODE, --Vendors.vm_due_days AS CASHDISCOUNTCODE, --Vendors.vm_balance AS Balance, CONVERT(NVARCHAR(200),'-01-----') AS DEFAULTLEDGERDIMENSIONDISPLAYVALUE, CONVERT(NVARCHAR(200),'en-US ') AS LANGUAGEID, CONVERT(NVARCHAR(250),UPPER(CASE vendors.vm_flags_03 WHEN '3' THEN 'Cindy Reidle' WHEN '4' THEN 'Jennifer King' ELSE '' END)) AS LMCEXPEDITORPERSONNELNUMBER, CONVERT(NVARCHAR(200),RTRIM(Vendors.vm_id)) AS LegacyVendorID, CONVERT(NVARCHAR(200),RTRIM(LEFT(tables.tab_desc,CHARINDEX(' ',tables.tab_desc)))) AS Buyer FROM dbo.vendors vendors --LEFT JOIN #Terms terms --ON terms.vm_payTerms = vendors.vm_pay_terms --LEFT JOIN dbo.gtext gttext --ON gttext.gt_ID = vendors.vm_id --AND gttext.gt_from = '1099DA' LEFT JOIN dbo.tables tables ON tables.tab_code = vendors.vm_buyer_locn+vendors.vm_buyer AND tables.tab_type = 'BUYER' WHERE Vendors.vm_type in ('P', 'F') AND Vendors.vm_active_code in ('A', 'S') AND NOT EXISTS (SELECT * FROM dbo.tables t2 WHERE t2.tab_type IN ('D365VEXCL') AND t2.tab_code = vendors.vm_id) Order by vendors.vm_add_date 1252 false 2 "0:Input",{872AF962-3981-41F1-91B3-E1F35D45B5D5};

0 DataResults$ 0 false false 2147483647

0 [dbo].[ExpenseVendors] 1252 false 0

0 [dbo].[InventoryVendors] 1252 false 0

#{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORACCOUNTNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORORGANIZATIONNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORSEARCHNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORKNOWNASNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORPARTYTYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBERDESCRIPTION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBEREXTENSION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBERPURPOSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBERDESCRIPTION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBEREXTENSION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBERPURPOSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORGROUPID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[BUYERGROUPID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISW9RECEIVED]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[CURRENCYCODE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099FEDERALTAXID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099DOINGBUSINESSASNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099NAMETOUSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[DEFAULTLEDGERDIMENSIONDISPLAYVALUE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LANGUAGEID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LegacyVendorID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[DEFAULTPAYMENTTERMSNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORPARTYNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[MAINCONTACTPERSONNELNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[CASHDISCOUNTCODE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099BOXID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ONHOLDSTATUS]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISREPORTINGTAX1099]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISW9CHECKINGENABLED]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISFOREIGNENTITY]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PERSONFIRSTNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PERSONLASTNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099IDTYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099TYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LMCEXPEDITORPERSONNELNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORACCOUNTNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORORGANIZATIONNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORSEARCHNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORKNOWNASNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORPARTYTYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBERDESCRIPTION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBEREXTENSION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYPHONENUMBERPURPOSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBERDESCRIPTION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBEREXTENSION]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PRIMARYFAXNUMBERPURPOSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORGROUPID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[BUYERGROUPID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISW9RECEIVED]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[CURRENCYCODE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099FEDERALTAXID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099DOINGBUSINESSASNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099NAMETOUSE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[DEFAULTLEDGERDIMENSIONDISPLAYVALUE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LANGUAGEID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LegacyVendorID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[DEFAULTPAYMENTTERMSNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[VENDORPARTYNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[MAINCONTACTPERSONNELNUMBER]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[CASHDISCOUNTCODE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099BOXID]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ONHOLDSTATUS]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISREPORTINGTAX1099]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISW9CHECKINGENABLED]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[ISFOREIGNENTITY]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PERSONFIRSTNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[PERSONLASTNAME]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099IDTYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[TAX1099TYPE]} #{Package\Load Excel Extract\Union All.Outputs[Union All Output 1].Columns[LMCEXPEDITORPERSONNELNUMBER]}

0 ExpenseExcl$ 0

0 [dbo].[ExpenseVendorExclusion] 1252 false 3 false false TABLOCK,CHECK_CONSTRAINTS 2147483647

0 ExpenseVendGrp$ 0

0 [dbo].[ExpenseVendorGroups] 1252 false 3 false false TABLOCK,CHECK_CONSTRAINTS 2147483647

0 Sheet1$ 0

0 [dbo].[VendorPayTerms] 1252 false 3 false false TABLOCK,CHECK_CONSTRAINTS 2147483647

ExpenseVendorCount OverwriteParamsSQLProp false UsedTableName [dbo].[ExpenseVendors] TableInfoObjectType 0 DataSourceViewID OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID DataSourceViewID OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID DataSourceViewID TableInfoObjectType Table OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID DataSourceViewID TableInfoObjectType Table OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID DataSourceViewID OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID OverwriteParamsSQLProp false UsedTableName TableInfoObjectType 0 DataSourceViewID DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table DataSourceViewID TableInfoObjectType Table ]]>