EM_MG_NOAGO_VW

(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 , A.NAME1 , D.DESCR , Z.EM_AGREEMENT_ID , Z.AGREEMENT_NAME 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 F , PS_SET_CNTRL_REC G , PS_EM_MG_TBL Z 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 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 E.BUSINESS_UNIT = F.SETCNTRLVALUE AND F.REC_GROUP_ID = 'EM_01' AND F.RECNAME = 'EM_AGREEMENT' 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) AND Z.SETID = E.BUSINESS_UNIT AND EXISTS ( SELECT 'X' FROM PS_EM_MG_PEND_UPD Y WHERE Y.SETID = Z.SETID AND Y.EM_AGREEMENT_ID = Z.EM_AGREEMENT_ID AND Y.EM_MG_PROC_OPT = 'C' AND ((Y.VENDOR_SETID = A.SETID AND Y.VENDOR_ID = A.VENDOR_ID AND Y.VNDR_LOC = C.VNDR_LOC) OR (Y.VENDOR_SETID = ' ' AND Y.VENDOR_ID = ' ' AND Y.VNDR_LOC = ' ' AND Y.EM_PROCESS_ALL = 'Y')))

  • Related Language Record: EM_MG_NOAGR_LG

    • # 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 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 EM_AGREEMENT_ID Character(10) VARCHAR2(10) NOT NULL Agreement Id
    8 AGREEMENT_NAME Character(30) VARCHAR2(30) NOT NULL Master Agreement Name