SPM_BU_SNW_VW3

(SQL View)
 Index Back

Supplier Info by BU

SELECT A.BUSINESS_UNIT, B.VENDOR_ID, B.NAME1, A.NOTIFY_BY, A.PERSON_ID, A.SPM_SOURCETYPE, A.SUBMITAL_LIMIT FROM PS_SPM_BU_SUPPLIER A, PS_VENDOR_SPRO_VW B WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SPM_BU_SUPPLIER A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.VENDOR_ID = A_ED.VENDOR_ID AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND A.VENDOR_ID = B.VENDOR_ID AND A.EFF_STATUS = 'A'

SELECT A.BUSINESS_UNIT , B.VENDOR_ID , B.NAME1 , A.NOTIFY_BY , A.PERSON_ID , A.SPM_SOURCETYPE , A.SUBMITAL_LIMIT ,B.SPB_SUPPLIER_TYPE FROM PS_SPM_BU_SUPPLIER A , PS_VENDOR_SPRO_VW B , PS_SET_CNTRL_GROUP S WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_SPM_BU_SUPPLIER A_ED WHERE A.BUSINESS_UNIT = A_ED.BUSINESS_UNIT AND A.VENDOR_ID = A_ED.VENDOR_ID AND A_ED.EFFDT <= %CurrentDateIn) AND A.VENDOR_ID = B.VENDOR_ID AND A.BUSINESS_UNIT = S.SETCNTRLVALUE AND S.REC_GROUP_ID = 'FS_38' AND B.SETID = S.SETID

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

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

Prompt Table: SP_BU_SP_NONVW

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
4 NOTIFY_BY Character(4) VARCHAR2(4) NOT NULL Notification Method
EM=Email and Worklist
WL=WorkList Only

Default Value: EM

5 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID

Prompt Table: SPM_SPLR_CNT_VW
Set Control Field: BUSINESS_UNIT

6 SPM_SOURCETYPE Character(4) VARCHAR2(4) NOT NULL Supplier Network Source Type
0=Internal
1=Primary
2=Secondary
3=Tertiary
7 SUBMITAL_LIMIT Number(2,0) SMALLINT NOT NULL Submittal Limit

Default Value: 5

8 SPB_SUPPLIER_TYPE Character(1) VARCHAR2(1) NOT NULL Supplier Type
B=Both
D=Deliverable
R=Resource