PO_CNTRCT_SRCH4

(SQL View)
 Index Back

Contract Search for PO's


SELECT DISTINCT A.VENDOR_SETID , A.VENDOR_ID , A.SETID , A.CNTRCT_ID , B.VENDOR_NAME_SHORT , A.CNTRCT_STATUS , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT FROM PS_CNTRCT_HDR A , PS_VENDOR B WHERE A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.CNTRCT_STATUS = 'A' AND A.VERSION_STATUS = 'C' AND A.CNTRCT_CF_LOCK = 'N' AND ( (A.CNTRCT_TYPE = 'PO' OR A.CNTRCT_TYPE = 'GN') AND A.ALLOW_OPEN_ITEM <> 'Y' AND NOT EXISTS( SELECT 'X' FROM %Table(CS_CNTRCT_HD_SN) CS WHERE A.SETID = CS.SETID AND A.CNTRCT_ID = CS.CNTRCT_ID) AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LINE_UOM C , PS_CNTRCT_LN_SHIP D , PS_CNTRCT_LINE E WHERE E.SETID = A.SETID AND E.CNTRCT_ID = A.CNTRCT_ID AND E.VERSION_NBR = A.VERSION_NBR AND C.SETID = E.SETID AND C.CNTRCT_ID = E.CNTRCT_ID AND C.VERSION_NBR = E.VERSION_NBR AND C.CNTRCT_LINE_NBR = E.CNTRCT_LINE_NBR AND D.SETID = E.SETID AND D.CNTRCT_ID = E.CNTRCT_ID AND D.VERSION_NBR = E.VERSION_NBR AND D.CNTRCT_LINE_NBR = E.CNTRCT_LINE_NBR AND E.DESCR254_MIXED <> ' ' AND E.CNTRCT_LN_TYPE = 'R' AND E.CNTR_LN_TYPE_ID <> ' ' AND E.CATEGORY_ID <> ' ' AND E.UNIT_OF_MEASURE <> ' ' AND C.VNDR_LOC <> ' ' AND C.PRICE_CNTRCT_BASE <> 0 AND D.QTY_SCHED <> 0) OR EXISTS ( SELECT 'X' FROM PS_CNTRCT_CATEGORY F , PS_ITM_CAT_TBL G WHERE F.CATEGORY_ID <> ' ' AND F.CNTRCT_ID = A.CNTRCT_ID AND F.VERSION_NBR = A.VERSION_NBR AND F.SETID = A.SETID AND A.VERSION_STATUS = 'C' AND G.SETID = F.SETID AND G.CATEGORY_ID = F.CATEGORY_ID AND G.CURRENCY_CD = A.CURRENCY_CD AND G.CATEGORY_CD IN( SELECT DISTINCT CATEGORY_CD FROM PS_SPB_ROLE_TYPE H WHERE H.SETID = F.SETID) ) ) AND NOT EXISTS ( SELECT 'X' FROM PS_CNTRCT_BPO_XREF F WHERE A.SETID = F.SETID AND A.CNTRCT_ID = F.CNTRCT_ID) AND NOT EXISTS ( SELECT 'X' FROM PS_CNTRCT_RPO_XREF X WHERE A.SETID = X.SETID AND A.CNTRCT_ID = X.CNTRCT_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
5 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name

Prompt Table: VENDOR_PO_NM_VW

6 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled

Default Value: O

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
9 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date