PV_PERF_V1_VW(SQL View) |
Index Back |
|---|
| SELECT A.SETID , A.INV_ITEM_ID , B.VENDOR_SETID , B.VENDOR_ID , A.CATEGORY_ID , A.UNIT_MEASURE_STD , F.DESCR , F.DESCR254_MIXED , B.ITM_ID_VNDR , B.VNDR_CATALOG_ID , C.TREE_NAME , F.PRICE_LIST , B.ITM_VNDR_PRIORITY , F.CURRENCY_CD , V.NAME1 , B.ITMV_PRIORITY_LOC , P.PV_MIN_PRIORITY , A.INVENTORY_ITEM FROM PS_MASTER_ITEM_TBL A LEFT OUTER JOIN ( PS_ITM_VENDOR B INNER JOIN PS_VENDOR V ON (B.VENDOR_SETID = V.SETID AND B.VENDOR_ID = V.VENDOR_ID AND V.VENDOR_STATUS = 'A' AND V.VNDR_STATUS_PO = 'Y' AND B.ITM_STATUS = 'A') INNER JOIN PS_PV_PREF_VNDR_VW P ON (B.SETID = P.SETID AND B.INV_ITEM_ID = P.INV_ITEM_ID)) ON ( A.INV_ITEM_ID = B.INV_ITEM_ID AND A.SETID = B.SETID ) , PSTREENODE C , PS_PURCH_ITEM_ATTR F , PS_ITM_CAT_TBL J , PSTREEDEFN Z WHERE A.ITM_STATUS_CURRENT = '1' AND A.SETID = C.SETID AND A.SETID = F.SETID AND A.INV_ITEM_ID = F.INV_ITEM_ID AND F.SUBITEM_ONLY <> 'Y' AND ((%CurrentDateIn >= F.PO_AVAIL_DT) AND (F.PO_UNAVAIL_DT > %CurrentDateIn)) AND A.SETID = J.SETID AND A.CATEGORY_ID = J.CATEGORY_ID AND J.EFF_STATUS = 'A' AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ITM_CAT_TBL WHERE SETID = J.SETID AND CATEGORY_ID = J.CATEGORY_ID AND CATEGORY_CD = J.CATEGORY_CD AND EFFDT <= %CurrentDateIn) AND J.CATEGORY_CD = C.TREE_NODE AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PSTREENODE WHERE SETID = C.SETID AND EFFDT <= %CurrentDateIn AND TREE_NAME = C.TREE_NAME) AND C.TREE_NAME = Z.TREE_NAME AND C.EFFDT = Z.EFFDT AND C.SETID = Z.SETID AND Z.TREE_STRCT_ID = 'ITEMS' AND Z.EFF_STATUS = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_MASTER_ITEM_TBL ITM WHERE ITM.SETID = A.SETID AND ITM.INV_ITEM_ID = A.INV_ITEM_ID AND A.ITM_STAT_DT_FUTURE <= %CurrentDateIn AND A.ITM_STATUS_FUTURE <> '1') |
| # | 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 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 6 | UNIT_MEASURE_STD | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |
| 9 | ITM_ID_VNDR | Character(50) | VARCHAR2(50) NOT NULL | Vendor Item ID |
| 10 | VNDR_CATALOG_ID | Character(20) | VARCHAR2(20) NOT NULL | Vendor's Catalog Number |
| 11 | TREE_NAME | Character(18) | VARCHAR2(18) NOT NULL | Tree Name |
| 12 | PRICE_LIST | Number(16,5) | DECIMAL(15,5) NOT NULL | Standard Price |
| 13 | ITM_VNDR_PRIORITY | Number(3,0) | SMALLINT NOT NULL | Item Vendor Priority |
| 14 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |
| 15 | VNDR_NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Supplier Name |
| 16 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 17 | PV_MIN_PRIORITY | Number(3,0) | SMALLINT NOT NULL | Minimum Item/Supplier Priority |
| 18 | INVENTORY_ITEM | Character(1) | VARCHAR2(1) NOT NULL |
Inventory Item
N=No Y=Yes |