PO_ITMSUBUD_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 ,B.CATEGORY_ID ,A.BUSINESS_UNIT ,A.PO_ID ,A.PO_DT ,A.VENDOR_SETID ,A.VENDOR_ID ,A.BUYER_ID ,B.LINE_NBR ,B.INV_ITEM_ID ,B.DESCR254_MIXED ,B.UNIT_OF_MEASURE ,C.PRICE_PO ,C.DUE_DT ,C.SHIPTO_ID ,SUM(C.QTY_PO) ,SUM(C.MERCHANDISE_AMT) FROM PS_PO_HDR A , PS_PO_LINE B , PS_PO_LINE_SHIP C , PS_ITM_CAT_TBL D , PS_RUN_POITMSUBU E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PO_ID = C.PO_ID AND B.LINE_NBR = C.LINE_NBR AND B.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) GROUP BY B.ITM_SETID ,B.ORIG_INV_ITEM_ID ,B.DESCR254_MIXED2 ,D.CATEGORY_CD ,B.CATEGORY_ID ,A.PO_DT ,A.BUSINESS_UNIT ,A.PO_ID ,A.VENDOR_SETID ,A.VENDOR_ID ,A.BUYER_ID ,B.LINE_NBR ,B.INV_ITEM_ID ,B.DESCR254_MIXED ,B.UNIT_OF_MEASURE ,C.PRICE_PO ,C.DUE_DT ,C.SHIPTO_ID, E.OPRID,E.RUN_CNTL_ID

# 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 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
10 PO_DT Date(10) DATE NOT NULL Date

Default Value: %date

11 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
12 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VENDOR_PO_VW

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

Prompt Table: %EDIT_BUYER

14 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

Default Value: 1

15 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
16 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
17 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: %EDIT_UOM

18 PRICE_PO Signed Number(17,5) DECIMAL(15,5) NOT NULL Purchase Order Price
19 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
20 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
21 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity
22 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label