PO_ITMSUBUC_VW

(SQL View)
 Index Back

Procurement Line Grp PO

Procurement Line Group PO Header view for use on spend inquiry.

SELECT DISTINCT E.OPRID ,E.RUN_CNTL_ID ,B.ITM_SETID ,B.ORIG_INV_ITEM_ID ,B.DESCR254_MIXED2 ,D.CATEGORY_CD ,A.BUSINESS_UNIT ,A.BUYER_ID ,A.VENDOR_SETID ,A.VENDOR_ID ,E.FROM_DATE ,E.TO_DATE FROM PS_PO_HDR A ,PS_PO_LINE B , PS_MASTER_ITEM_TBL C , PS_ITM_CAT_TBL D , PS_RUN_POITMSUBU E WHERE %Join(COMMON_KEYS, PO_HDR A, PO_LINE B) AND C.SETID = B.ITM_SETID AND C.INV_ITEM_ID = B.ORIG_INV_ITEM_ID AND C.CATEGORY_ID = D.CATEGORY_ID AND B.ITM_SETID = D.SETID AND B.ORIG_INV_ITEM_ID <> ' ' AND D.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ITM_CAT_TBL F WHERE D.SETID = F.SETID AND D.CATEGORY_TYPE = F.CATEGORY_TYPE AND D.CATEGORY_CD = F.CATEGORY_CD AND D.CATEGORY_ID = F.CATEGORY_ID AND F.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
3 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
4 ORIG_INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Used to identify the original item ordered, in case an item substitution has taken place.
5 DESCR254_MIXED2 Character(254) VARCHAR2(254) NOT NULL Long Description
6 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_PM_NONVW

8 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer

Prompt Table: BUYER_ACTIVE_VW

9 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

10 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VENDOR_VW
Set Control Field: VENDOR_SETID

11 FROM_DATE Date(10) DATE From Date
12 TO_DATE Date(10) DATE To Date