Script for updating the vendor short name sequence after vendor data insertion to the peoplesoft FSCM finanicals database,

Script for updating the vendor short name sequence after vendor data insertion to the peoplesoft FSCM finanicals database,

CREATE TABLE SYSADM.scb_vnd_tmp_1 AS
SELECT setid,vendor_id,vndr_name_shrt_usr,
RANK() OVER (PARTITION BY vndr_name_shrt_usr ORDER BY vendor_id) AS VNDR_NAME_SEQ_NUM
FROM PS_VENDOR WHERE  SETID = 'SCMGS' AND VNDR_NAME_SEQ_NUM = 999;

UPDATE PS_VENDOR V SET
V.VENDOR_NAME_SHORT = ( SELECT DISTINCT vndr_name_shrt_usr||'-'||LPAD(TO_CHAR(VNDR_NAME_SEQ_NUM),3,'0') FROM SYSADM.scb_vnd_tmp T WHERE T.VENDOR_ID = V.VENDOR_ID )
,V.VNDR_NAME_SEQ_NUM = ( SELECT DISTINCT VNDR_NAME_SEQ_NUM FROM SYSADM.scb_vnd_tmp T WHERE T.VENDOR_ID = V.VENDOR_ID )
WHERE V.SETID = 'SHARE' AND V.VNDR_NAME_SEQ_NUM = 999;