PO_ITMSUBSN_VW

(SQL View)
 Index Back

Procurement Line Grp PO

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

SELECT DISTINCT D.OPRID ,D.RUN_CNTL_ID ,B.ITM_SETID ,B.INV_ITEM_ID ,B.DESCR254_MIXED ,A.BUSINESS_UNIT ,A.VENDOR_SETID ,A.VENDOR_ID ,A.BUYER_ID ,MIN(A.PO_DT) ,MAX(A.PO_DT) ,COUNT(*) ,SUM(C.MERCHANDISE_AMT) FROM PS_PO_HDR A , PS_PO_LINE B , PS_PO_LINE_SHIP C , PS_RUN_POITMSUBS D WHERE %Join(COMMON_KEYS, PO_HDR A, PO_LINE B) AND %Join(COMMON_KEYS, PO_LINE B, PO_LINE_SHIP C) AND B.ORIG_INV_ITEM_ID = ' ' AND B.INV_ITEM_ID <> ' ' AND D.FROM_DATE <= A.PO_DT AND D.TO_DATE >= A.PO_DT GROUP BY B.ITM_SETID, B.INV_ITEM_ID, B.DESCR254_MIXED,A.BUSINESS_UNIT ,A.VENDOR_SETID ,A.VENDOR_ID ,A.BUYER_ID, D.OPRID, D.RUN_CNTL_ID,D.FROM_DATE, D.TO_DATE

# 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 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VENDOR_PO_VW

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

Prompt Table: %EDIT_BUYER

10 FROM_DATE Date(10) DATE From Date
11 TO_DATE Date(10) DATE To Date
12 NBR_ROWS_RETURNED Number(5,0) INTEGER NOT NULL Number of Rows Returned
13 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label