/******************************************************************************** * * Program Name: sp_apcvenmast_from_employee.sql * * Program Description: Stored Procedure to create Lawson APCVENMAST records * from Lawson EMPLOYEE Records * Assumptions: Works with Lawson 8.0.3.8 APPS * * Usage: exec sp_apcvenmast_from_employee [employee number] * * Author: John Eisenschmidt * * Date: 2004-08-06 - Created * 2004-08-26 - Vendor group is now EMPL [JE] * 2004-08-30 - Added grant for sp to webuser [JE] * 2004-09-02 - Changed Vendor Group and Vendor Class [JE] * 2004-10-22 - Changed back Vendor Group and Class [JE] * 2004-10-22 - Added back the emp number in user field [JE] * 2004-10-27 - Added subquery for ACH information [JE] * 2004-11-24 - Wrapped ISNULL() around direct deposit info * for people who still get paper checks [JE] * 2004-12-23 - Fixed a bug where people who had no direct * deposit info in payroll would not convert. * Since EMACHDEPST.EBANKID is an INT, I had * to CAST it back to VARCHAR in the subqry [JE] * ********************************************************************************/ if exists ( select name from sysobjects where name = 'sp_apcvenmast_from_employee' ) drop procedure sp_apcvenmast_from_employee; go create procedure sp_apcvenmast_from_employee (@employee int) as insert into apcvenmast select 'FELD' AS VENDOR_GROUP, -- 'EMPL', --VENDOR_GROUP -- ltrim(rtrim(employee)), --VENDOR '' AS VENDOR, -- '' AS OLD_VENDOR, ltrim(rtrim(employee)) AS OLD_VENDOR, '' AS LOCATION_CODE, '' AS LOC_TYPE, 'EMP' AS VEN_CLASS, ltrim(rtrim(last_name)) + ', ' + ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(middle_init)) AS VENDOR_VNAME, ltrim(rtrim(last_name)) + ', ' + ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(middle_init)) AS VENDOR_SNAME, ''AS VENDOR_CONTACT, addr1 AS ADDR1, addr2 AS ADDR2, addr3 AS ADDR3, -- THE LAST ADDRESS LINE GETS STAMPED WITH "EMPLOYEE (COMPANY HOME DEPT) 'EMPLOYEE (' + ltrim(rtrim(convert(varchar,hm_dist_co))) + '-' + ltrim(rtrim(convert(varchar,hm_acct_unit))) + ')' AS ADDR4, city AS CITY_ADDR5, state AS STATE_PROV, zip AS POSTAL_CODE, '' AS COUNTY, '' AS COUNTRY, '' AS REGION, '' AS COUNTRY_CODE, '' AS NORM_REMIT, '' AS NORM_PURCH, '' AS REMIT_TO_CODE, '' AS PURCH_FR_LOC, '' AS PAY_VENDOR, 'A' AS VENDOR_STATUS, 1 AS VEN_PRIORITY, '' AS PHONE_PREFIX, '' AS PHONE_NUM, '' AS PHONE_EXT, '' AS FAX_PREFIX, '' AS FAX_NUM, '' AS FAX_EXT, '' AS TELEX_NUM, 'IMMED' AS TERM_CODE, '' AS INV_CURRENCY, '' AS BAL_CURRENCY, 'Y' AS CURR_RECALC, 'N' AS SEP_CHK_FLAG, '' AS TAX_ID, '' AS TAX_CODE, '' AS HLD_CODE, '' AS DIST_CODE, '' AS ACCR_CODE, '' AS BANK_INST_CODE, '' AS CASH_CODE, ISNULL(( SELECT CAST(EBANK_ID AS VARCHAR) FROM EMACHDEPST WHERE EMPLOYEE.EMPLOYEE=EMACHDEPST.EMPLOYEE AND NET_PERCENT='100.000' AND END_DATE='1753-01-01 00:00:00.000' ),'') AS BANK_ENTITY, --BANK_ENTITY | must match cashbook ISNULL(( SELECT EBNK_ACCT_NBR FROM EMACHDEPST WHERE EMPLOYEE.EMPLOYEE=EMACHDEPST.EMPLOYEE AND NET_PERCENT='100.000' AND END_DATE='1753-01-01 00:00:00.000' ),'') AS VBANK_ACCT_NO, --VBANK_ACCT_NO | bank account number 0 AS RIBKEY, ISNULL(( SELECT ACCOUNT_TYPE FROM EMACHDEPST WHERE EMPLOYEE.EMPLOYEE=EMACHDEPST.EMPLOYEE AND NET_PERCENT='100.000' AND END_DATE='1753-01-01 00:00:00.000' ),'') AS VBANK_ACCT_TP, --VBANK_ACCT_TP | C for checking, S for savings '' AS INCOME_CODE, 'N' AS INCOME_WH_FLG, '' AS EDI_NBR, '' AS ACH_PRENOT, 0 AS MAX_INV_AMT, getdate() AS ORIGIN_DATE, -- '' AS USER_NAME_01, ltrim(rtrim(employee)) AS USER_NAME_01, '' AS USER_NAME_02, '' AS USER_NAME_03, '' AS USER_NAME_04, '' AS USER_NAME_05, '' AS USER_NAME_06, ltrim(rtrim(last_name)) + ', ' + ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(middle_init)) AS LEGAL_NAME, '' AS ACTIVITY, '' AS ACCT_CATEGORY, '' AS DISCOUNT_CODE, 0 AS PRIME_RATE, '' AS INVOICE_GROUP, '' AS ERS_CAPABLE, '' AS INVC_REF_TYPE, '' AS EDI_AUTO_REL, '' AS AUTH_CODE, '' AS CHARGE_CODE, '' AS PMT_CAT_CODE, '' AS NORM_EXP_CODE, '' AS PMT_FORM, '' AS SWIFT_ID, '' AS PROC_GRP, '' AS MATCH_TABLE, '' AS HANDLING_CODE, '' AS DISC_CALC_DATE, '' AS ENCLOSURE, '' AS CREATE_POD_FL, '' AS REQ_MATCH_REF, 0 AS POOL_OPTION, '' AS HOLD_INSP_FLAG, 0 AS WRITE_OFF_AMT, '' AS VEN_CLAIM_TYPE, '' AS CLAIM_HOLD_CD, '' AS CB_HOLD_CODE, 0 AS CB_MINIMUM_AMT, '' AS REPLACE_GOODS, '' AS SHIP_OR_HOLD, '' AS ERS_HANDLING, email_address AS E_MAIL_ADDRESS, '' AS URL_ADDR, '' AS VEND_ACCT, '' AS LANGUAGE_CODE, '' AS TAX_USAGE_CD, '' AS VAT_REG_CTRY, '' AS VAT_REG_NBR, '' AS VALIDATE_PO, '' AS REQUIRE_PO, '' AS POV_BUYER_CODE, 0 AS POV_OPEN_PO_LM, 0 AS POV_OPEN_PO_AM, 0 AS POV_LEADTIME, '' AS POV_EDI_NUMBER, '' AS POV_FAX_PREFIX, '' AS POV_FAX_EXT, '' AS POV_FAX_NUMBER, 0 AS POV_MAX_ORD_AM, '' AS POV_FREIGHT_TR, '' AS POV_SHIP_VIA, '' AS POV_FOB_CODE, '' AS POV_NORM_DEL, 0 AS POV_OSHIP_TPCT, 0 AS POV_USHIP_TPCT, '' AS POV_HIN_NBR, '' AS POV_ZOHSHIP_FL, '' AS POV_ZUHSHIP_FL, '' AS POV_CAN_ALL_BO, '' AS POV_RMA_REQ, '' AS POV_RMA_DOC_RQ, '' AS POV_OV_SHP_VIA, '' AS POV_REQ_LOCLVL, '' AS POV_REQ_CONLVL, '' AS POV_ISSUE_MTHD, '' AS POV_REV_ISS_MT, '' AS POV_REV_EDI_NB, '' AS POV_REV_EDI_TR, '' AS POV_REV_INCL, '' AS POV_VEN_CONT, '' AS POV_PHONE_PREF, '' AS POV_PHONE_NUM, '' AS POV_PHONE_EXT, '' AS POV_VEND_ACCT, '' AS POV_PO_CODE, 0 AS POV_UPD_PO_CST from employee where employee = @employee; go -- give the webuser the ability to call this sp grant execute on sp_apcvenmast_from_employee to webuser;