PV_REQ_ITM_VW

(SQL View)
Index Back

Item Catalog Search View

Used 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