BUSINESS_UNITPrompt Table: BUS_UNIT_SP_VW
VENDOR_IDPrompt Table:
VENDOR_SP_VW
Set Control Field: BUSINESS_UNIT
SPB_VMSBUSR_DVW(SQL View) |
Index Back |
|---|---|
vms supplier viewview to get a list of vms supplier for a given business unit |
| SELECT A.BUSINESS_UNIT , A.VENDOR_ID , A.NAME1 FROM PS_SPB_SUPBYBU_DVW A WHERE EXISTS ( SELECT V.BUSINESS_UNIT FROM PS_SPB_VMS_BY_BU V WHERE V.BUSINESS_UNIT = A.BUSINESS_UNIT AND V.VMS_SUPPLIER_ID = A.VENDOR_ID AND V.EFFDT = ( SELECT MAX(V_ED.EFFDT) FROM PS_SPB_VMS_BY_BU V_ED WHERE V.BUSINESS_UNIT = V_ED.BUSINESS_UNIT AND V_ED.EFFDT <= %CurrentDateIn) AND V.EFF_STATUS = 'A') OR EXISTS ( SELECT S.BUSINESS_UNIT FROM PS_SPB_VMS_BY_SRVC S WHERE S.BUSINESS_UNIT = A.BUSINESS_UNIT AND S.VMS_SUPPLIER_ID = A.VENDOR_ID AND S.EFFDT = ( SELECT MAX(S_ED.EFFDT) FROM PS_SPB_VMS_BY_SRVC S_ED WHERE S.BUSINESS_UNIT = S_ED.BUSINESS_UNIT AND S_ED.EFFDT <= %CurrentDateIn) AND S.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
|
| 3 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |