SETIDINV_ITEM_IDVENDOR_SETIDVENDOR_IDCONTRACT_SETIDBUSINESS_UNIT_REQN=No
Y=Yes
PV_SRCH_IVCNTVW(SQL View) |
Index Back |
|---|---|
Item Vendor Contracts |
| SELECT IV.SETID , IV.INV_ITEM_ID ,A.VENDOR_SETID , A.VENDOR_ID ,A.SETID ,B.BUSINESS_UNIT ,A.SETID %Concat '|' %Concat B.BUSINESS_UNIT ,'Y' FROM PS_ITM_VENDOR IV , PS_CNT_SRCH_VW_O1 A , PS_PV_CNT_CTRL_VW B WHERE A.CNTRCT_AUTO_DFLT = 'Y' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR A.CNTRCT_EXPIRE_DT >= %CurrentDateIn) AND A.SETID=B.SETID AND A.CNTRCT_ID=B.CNTRCT_ID AND A.VERSION_NBR=B.VERSION_NBR AND A.VENDOR_SETID = IV.VENDOR_SETID AND A.VENDOR_ID = IV.VENDOR_ID AND IV.ITM_STATUS = 'A' UNION SELECT IV.SETID , IV.INV_ITEM_ID ,A.VENDOR_SETID , A.VENDOR_ID ,A.SETID ,B.BUSINESS_UNIT ,A.SETID %Concat '|' %Concat B.BUSINESS_UNIT ,'Y' FROM PS_ITM_VENDOR IV , PS_CNT_SRCH_VW_L1 A , PS_PV_CNT_CTRL_VW B , PS_MASTER_ITEM_TBL M WHERE A.CNTRCT_AUTO_DFLT = 'Y' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR A.CNTRCT_EXPIRE_DT >= %CurrentDateIn) AND A.SETID=B.SETID AND A.CNTRCT_ID=B.CNTRCT_ID AND A.VERSION_NBR=B.VERSION_NBR AND A.VENDOR_SETID = IV.VENDOR_SETID AND A.VENDOR_ID = IV.VENDOR_ID AND IV.ITM_STATUS = 'A' AND IV.SETID = M.SETID AND IV.INV_ITEM_ID = M.INV_ITEM_ID AND M.ITM_STATUS_CURRENT = '1' AND A.ITM_SETID=IV.SETID AND A.INV_ITEM_ID=IV.INV_ITEM_ID AND A.CATEGORY_SETID=M.SETID AND A.CATEGORY_ID=M.CATEGORY_ID UNION SELECT IV.SETID , IV.INV_ITEM_ID ,A.VENDOR_SETID , A.VENDOR_ID ,A.SETID ,B.BUSINESS_UNIT ,A.SETID %Concat '|' %Concat B.BUSINESS_UNIT ,'Y' FROM PS_ITM_VENDOR IV , PS_CNT_SRCH_VW_L2 A , PS_PV_CNT_CTRL_VW B , PS_MASTER_ITEM_TBL M WHERE A.CNTRCT_AUTO_DFLT = 'Y' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR A.CNTRCT_EXPIRE_DT >= %CurrentDateIn) AND A.SETID=B.SETID AND A.CNTRCT_ID=B.CNTRCT_ID AND A.VERSION_NBR=B.VERSION_NBR AND A.VENDOR_SETID = IV.VENDOR_SETID AND A.VENDOR_ID = IV.VENDOR_ID AND IV.ITM_STATUS = 'A' AND IV.SETID = M.SETID AND IV.INV_ITEM_ID = M.INV_ITEM_ID AND M.ITM_STATUS_CURRENT = '1' AND A.ITM_SETID=IV.SETID AND A.INV_ITEM_ID=IV.INV_ITEM_ID AND A.CATEGORY_SETID=M.SETID AND A.CATEGORY_ID=M.CATEGORY_ID UNION SELECT IV.SETID , IV.INV_ITEM_ID ,A.VENDOR_SETID , A.VENDOR_ID ,A.SETID ,B.BUSINESS_UNIT ,A.SETID %Concat '|' %Concat B.BUSINESS_UNIT ,'Y' FROM PS_ITM_VENDOR IV , PS_CNT_SRCH_VW_C1 A , PS_PV_CNT_CTRL_VW B , PS_MASTER_ITEM_TBL M WHERE A.CNTRCT_AUTO_DFLT = 'Y' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR A.CNTRCT_EXPIRE_DT >= %CurrentDateIn) AND A.SETID=B.SETID AND A.CNTRCT_ID=B.CNTRCT_ID AND A.VERSION_NBR=B.VERSION_NBR AND A.VENDOR_SETID = IV.VENDOR_SETID AND A.VENDOR_ID = IV.VENDOR_ID AND IV.ITM_STATUS = 'A' AND IV.SETID = M.SETID AND IV.INV_ITEM_ID = M.INV_ITEM_ID AND M.ITM_STATUS_CURRENT = '1' AND A.CATEGORY_SETID=M.SETID AND A.CATEGORY_ID=M.CATEGORY_ID AND A.INV_ITEM_ID NOT IN ( SELECT CE.INV_ITEM_ID FROM PS_CNTRCT_CAT_EXC CE WHERE CE.SETID = A.SETID AND CE.CNTRCT_ID = A.CNTRCT_ID AND CE.VERSION_NBR = A.VERSION_NBR AND CE.CAT_LINE_NBR = A.CAT_LINE_NBR) UNION SELECT IV.SETID , IV.INV_ITEM_ID ,A.VENDOR_SETID , A.VENDOR_ID ,A.SETID ,B.BUSINESS_UNIT ,A.SETID %Concat '|' %Concat B.BUSINESS_UNIT ,'Y' FROM PS_ITM_VENDOR IV , PS_CNT_SRCH_VW_MFG A , PS_PV_CNT_CTRL_VW B , PS_MASTER_ITEM_TBL M WHERE A.CNTRCT_AUTO_DFLT = 'Y' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR A.CNTRCT_EXPIRE_DT >= %CurrentDateIn) AND A.SETID=B.SETID AND A.CNTRCT_ID=B.CNTRCT_ID AND A.VERSION_NBR=B.VERSION_NBR AND A.VENDOR_SETID = IV.VENDOR_SETID AND A.VENDOR_ID = IV.VENDOR_ID AND IV.ITM_STATUS = 'A' AND IV.SETID = M.SETID AND IV.INV_ITEM_ID = M.INV_ITEM_ID AND M.ITM_STATUS_CURRENT = '1' AND A.ITM_SETID=IV.SETID AND A.INV_ITEM_ID=IV.INV_ITEM_ID AND A.CATEGORY_SETID=M.SETID AND A.CATEGORY_ID=M.CATEGORY_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 3 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 4 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 5 | CONTRACT_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Contract Set ID |
| 6 | BUSINESS_UNIT_REQ |
Character(5) | VARCHAR2(5) NOT NULL | Requesting Business Unit |
| 7 | KEYVALUE | Character(30) | VARCHAR2(30) NOT NULL | Key Value |
| 8 | PV_CONTRACT_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Contract Item
N=No Y=Yes |