PO_CNT_SRCH_VW(SQL View) |
Index Back |
---|---|
Contract Search for PO'sPO002 Manufacturer Contract. This view is used in dynamic view PO_CNTRCT_DVW. |
SELECT DISTINCT DST_D.VENDOR_SETID , DST_D.VENDOR_ID , MFG_A.SETID , MFG_A.CNTRCT_ID , DST_B.VENDOR_NAME_SHORT , MFG_A.CNTRCT_STATUS , MFG_A.DESCR , MFG_A.CNTRCT_BEGIN_DT , MFG_A.CNTRCT_EXPIRE_DT , MFG_A.SETID , MFG_A.CONTROL_TYPE , MFG_A.VERSION_NBR , 1 , MFG_C.CNTRCT_ID_DST , MFG_C.PRIORITY , MFG_A.CNTRCT_PROC_OPT , MFG_A.MFG_ID , MFG_A.CNTRCT_CORP_FLG FROM PS_CNTRCT_HDR MFG_A , PS_VENDOR DST_B , PS_CNTRCT_MFG_DST MFG_C , PS_CNTRCT_HDR DST_D WHERE MFG_A.CNTRCT_PROC_OPT = 'MFG' AND MFG_A.CNTRCT_STATUS = 'A' AND MFG_A.VERSION_STATUS = 'C' AND MFG_A.CNTRCT_CF_LOCK = 'N' AND (MFG_A.CNTRCT_EXPIRE_DT IS NULL OR (MFG_A.CNTRCT_EXPIRE_DT IS NOT NULL AND MFG_A.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) AND MFG_A.SETID = MFG_C.SETID AND MFG_A.CNTRCT_ID = MFG_C.CNTRCT_ID AND MFG_A.VERSION_NBR = MFG_C.VERSION_NBR AND MFG_C.SETID = DST_D.SETID AND MFG_C.CNTRCT_ID_DST = DST_D.CNTRCT_ID AND MFG_C.RELTN_STATUS = 'A' AND DST_D.VERSION_NBR = 1 AND DST_D.VENDOR_SETID = DST_B.SETID AND DST_D.VENDOR_ID = DST_B.VENDOR_ID AND DST_D.CNTRCT_PROC_OPT = 'DST' AND DST_D.CNTRCT_STATUS = 'A' AND DST_D.VERSION_STATUS = 'C' AND DST_D.CNTRCT_CF_LOCK = 'N' AND (DST_D.CNTRCT_EXPIRE_DT IS NULL OR (DST_D.CNTRCT_EXPIRE_DT IS NOT NULL AND DST_D.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) AND NOT EXISTS( SELECT 'X' FROM %Table(CS_CNTRCT_HD_SN) CS WHERE MFG_A.SETID = CS.SETID AND MFG_A.CNTRCT_ID = CS.CNTRCT_ID) AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LINE_UOM C , PS_CNTRCT_LINE E WHERE E.SETID = MFG_A.SETID AND E.CNTRCT_ID = MFG_A.CNTRCT_ID AND E.VERSION_NBR = MFG_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 E.DESCR254_MIXED <> ' ' AND E.CATEGORY_ID <> ' ' AND E.UNIT_OF_MEASURE <> ' ' AND C.PRICE_CNTRCT_BASE <> 0 ) UNION ALL 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 , A.SETID , A.CONTROL_TYPE , A.VERSION_NBR , 2 , ' ' , 0 , A.CNTRCT_PROC_OPT , A.MFG_ID , A.CNTRCT_CORP_FLG FROM PS_CNTRCT_HDR A , PS_VENDOR B WHERE A.CNTRCT_PROC_OPT NOT IN ('MFG','DST') AND 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.CNTRCT_EXPIRE_DT IS NULL OR (A.CNTRCT_EXPIRE_DT IS NOT NULL AND A.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) 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 ( (A.ALLOW_OPEN_ITEM = 'Y') OR 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.CATEGORY_ID <> ' ' AND E.UNIT_OF_MEASURE <> ' ' AND C.VNDR_LOC <> ' ' AND (C.PRICE_CNTRCT_BASE <> 0 OR (C.PRICE_CNTRCT_BASE = 0 AND E.INV_ITEM_ID = ' ')) AND D.QTY_SCHED <> 0) OR EXISTS ( SELECT 'X' FROM PS_CNTRCT_CATEGORY C WHERE C.SETID = A.SETID AND C.CNTRCT_ID = A.CNTRCT_ID AND C.VERSION_NBR = A.VERSION_NBR AND C.CATEGORY_ID <> ' ') )) 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 | VENDOR_CNTRCT_SET | Character(5) | VARCHAR2(5) NOT NULL | Supplier Set ID |
2 | VENDOR_CNTRCT | Character(10) | VARCHAR2(10) NOT NULL | Supplier ID |
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_NMVW2 |
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 |
10 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
11 | CONTROL_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
PO002 Manufacturer Contract
BU=Business Unit CDOM=Contract Domain SHIP=Ship To X=Not Used |
12 | VERSION_NBR | Number(5,0) | INTEGER NOT NULL | Contract Version number |
13 | CNTRCT_PRIORITY | Number(3,0) | SMALLINT NOT NULL | PO002 Manufacturer Contract. Contract Priority for contract search and auto default. |
14 | CNTRCT_ID_DST | Character(25) | VARCHAR2(25) NOT NULL | Contract ID To |
15 | PRIORITY | Number(3,0) | SMALLINT NOT NULL | Priority |
16 | CNTRCT_PROC_OPT | Character(4) | VARCHAR2(4) NOT NULL |
Contract Process Option
AP=Recurring Voucher BPO=Release to Single PO Only DST=Distributor GN=General Contract GRPM=Group Multi Supplier GRPS=Group Single Supplier MFG=Manufacturer PADV=Prepaid Voucher w/ Advance PO PO=Purchase Order PPAY=Prepaid Voucher RPOV=Recurring PO Voucher SPP=Special Purpose |
17 | MFG_ID | Character(50) | VARCHAR2(50) NOT NULL | Manufacturer ID |
18 | CNTRCT_CORP_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Corporate Contract Flag
N=No Y=Yes |