SPM_BU_SNW_VW

(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.EFF_STATUS = 'A' AND A.BUSINESS_UNIT = S.SETCNTRLVALUE AND S.REC_GROUP_ID = 'FS_38' AND B.SETID = S.SETID

  • Related Language Record: SPM_BU_SNW_LVW

    • # 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