SPB_VMS_SUP_RVW

(SQL View)
Index Back

vms supplier view

view to get a list of vms supplier for a given business unit

SELECT DISTINCT A.BUSINESS_UNIT , A.VMS_SUPPLIER_ID , B.NAME1 FROM PS_SPB_VMS_BY_BU A , PS_SPB_SUPBYBU_RVW B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VMS_SUPPLIER_ID = B.VENDOR_ID AND A.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_SPB_VMS_BY_BU B_ED WHERE A.BUSINESS_UNIT = B_ED.BUSINESS_UNIT AND B_ED.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_SP_VW

2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VENDOR_SP_VW
Set Control Field: BUSINESS_UNIT

3 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1