EM_MG_NOAGR_LG

(SQL View)
 Index Back

View of Suppliers w/o Agreemen

View of Suppliers not linked to a Master Agreement

SELECT E.BUSINESS_UNIT , A.SETID , A.VENDOR_ID , C.VNDR_LOC , AL.LANGUAGE_CD , AL.NAME1 , LL.DESCR FROM PS_VENDOR A , PS_EM_VENDOR_REG B , PS_VENDOR_LOC C , PS_VNDR_LOC_SCROL D , PS_EM_BUY_REG_TBL E , PS_SET_CNTRL_REC G , PS_VENDOR_LANG AL , PS_VNDRLOCSCROL_LG LL WHERE A.SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.SETID = AL.SETID AND A.VENDOR_ID = AL.VENDOR_ID AND LL.SETID = C.SETID AND LL.VENDOR_ID = C.VENDOR_ID AND LL.VNDR_LOC = C.VNDR_LOC AND LL.LANGUAGE_CD = AL.LANGUAGE_CD AND C.SETID = D.SETID AND C.VENDOR_ID = D.VENDOR_ID AND C.VNDR_LOC = D.VNDR_LOC AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_VENDOR_LOC CC WHERE CC.SETID = C.SETID AND CC.VENDOR_ID = C.VENDOR_ID AND CC.VNDR_LOC = C.VNDR_LOC AND CC.EFFDT <= %CurrentDateIn) AND E.BUSINESS_UNIT = G.SETCNTRLVALUE AND G.REC_GROUP_ID = 'FS_38' AND G.RECNAME = 'VENDOR' AND G.SETID = A.SETID AND EXISTS ( SELECT 'X' FROM PS_SET_CNTRL_REC F WHERE E.BUSINESS_UNIT = F.SETCNTRLVALUE AND F.REC_GROUP_ID = 'EM_01' AND F.SETID = E.BUSINESS_UNIT) AND NOT EXISTS ( SELECT 'X' FROM PS_EM_AGREEMENT H WHERE H.SETID = E.BUSINESS_UNIT AND H.VENDOR_SETID = A.SETID AND H.VENDOR_ID = A.VENDOR_ID AND H.VNDR_LOC = C.VNDR_LOC)

  • Related Language Record for EM_MG_NOAGO_VW EM_MG_NOAGR_V2 EM_MG_NOAGR_VC EM_MG_NOAGR_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
    3 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    4 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description