PV_REQ_ITM_VW(SQL View) |
Index Back |
---|---|
Item Catalog Search ViewUsed as Item ID prompt table for requisitions when a requestor can only use specific catalogs. |
SELECT F.SETID ,F.REQUESTOR_ID ,C.INV_ITEM_ID ,C.SETID ,%Substring(A.TREE_NODE, 1, 18) ,E.CATEGORY_ID ,D.DESCR ,C.UNIT_MEASURE_STD FROM PSTREENODE A , PSTREELEAF B , PS_MASTER_ITEM_TBL C , PS_PURCH_ITEM_ATTR D , PS_ITM_CAT_TBL E , PS_REQUESTOR_CAT F WHERE F.SETID = A.SETID AND F.CATALOG_ID = A.TREE_NAME AND E.SETID = C.SETID AND E.CATEGORY_ID = C.CATEGORY_ID AND E.EFF_STATUS = 'A' AND %EffdtCheck(ITM_CAT_TBL F, E, %CurrentDateIn) AND C.SETID = D.SETID AND C.INV_ITEM_ID = D.INV_ITEM_ID AND D.SUBITEM_ONLY <> 'Y' AND C.ITM_STATUS_CURRENT = '1' AND A.SETID = B.SETID AND A.SETCNTRLVALUE = B.SETCNTRLVALUE AND A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT AND A.TREE_NODE_NUM = B.TREE_NODE_NUM AND ((%CurrentDateIn >= D.PO_AVAIL_DT) AND (D.PO_UNAVAIL_DT > %CurrentDateIn)) AND ((B.DYNAMIC_RANGE = 'Y' AND E.SETID = A.SETID AND E.CATEGORY_CD = A.TREE_NODE) OR (B.SETID = C.SETID AND B.RANGE_FROM <= C.INV_ITEM_ID AND C.INV_ITEM_ID <= B.RANGE_TO)) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREENODE A1 WHERE A1.SETID = A.SETID AND A1.SETCNTRLVALUE = A.SETCNTRLVALUE AND A1.TREE_NAME = A.TREE_NAME AND A1.TREE_NODE_NUM = A.TREE_NODE_NUM AND A1.EFFDT <= %CurrentDateIn) AND C.SETID = F.SETID GROUP BY F.SETID ,F.REQUESTOR_ID ,%Substring(A.TREE_NODE, 1, 18),C.INV_ITEM_ID,C.SETID,E.CATEGORY_ID ,D.DESCR ,C.UNIT_MEASURE_STD |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | SETID | Character(5) | VARCHAR2(5) NOT NULL |
SetID
Default Value: OPR_DEF_TBL_FS.SETID Prompt Table: SP_SETID_NONVW |
2 | REQUESTOR_ID | Character(30) | VARCHAR2(30) NOT NULL |
Requestor id
Default Value: REQ_HDR.REQUESTOR_ID Prompt Table: REQUESTOR_VW |
3 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: MST_ITM_VW |
4 | ITM_SETID | Character(5) | VARCHAR2(5) NOT NULL | Item SetID |
5 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL |
Category Code
Prompt Table: ITM_CAT_VW |
6 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL |
Category ID
Prompt Table: ITM_CAT_VW |
7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
8 | UNIT_MEASURE_STD | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |