PO_CAPX_POIT_VW

(SQL View)
 Index Back

Sum of PO/by item

cel 05/01/2002 Sum of purchase orders by item ... that are within predefined capacity date ranges for each item

SELECT DISTINCT A.OPRID , A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.ITM_SETID , A.INV_ITEM_ID , A.BUSINESS_UNIT_IN , A.SCHED_FLAG , A.UNIT_MEASURE_STD , A.CATEGORY_CD , A.PO_SCHED_PRIORITY , A.FROM_DT , A.TO_DT , %subrec(PO_CAP_FLEX_SBR,A) , A.QTY_MAX , 0 , SUM(%Round(%DecMult(I.QTY_PO,J.CONVERSION_RATE) ,4)) FROM PS_PO_CAP_SUM_VW A , PS_PO_BUY_POALL_VW I ,PS_INV_ITEM_UOM J WHERE A.INCLUDE_PO <> 'N' AND A.SCHED_GROUP_CD = ' ' AND I.VENDOR_SETID = A.VENDOR_SETID AND I.VENDOR_ID = A.VENDOR_ID AND I.VNDR_LOC = A.VNDR_LOC AND I.INV_ITEM_ID = A.INV_ITEM_ID AND I.ITM_SETID = A.ITM_SETID AND I.BUSINESS_UNIT_IN = A.BUSINESS_UNIT_IN AND I.DUE_DT >= A.FROM_DT AND I.DUE_DT <= A.TO_DT AND I.ITM_SETID=J.SETID AND I.INV_ITEM_ID=J.INV_ITEM_ID AND I.UNIT_OF_MEASURE=J.UNIT_OF_MEASURE AND J.CONVERSION_RATE<>0.0000 AND I.INV_ITEM_ID NOT IN ( SELECT D.INV_ITEM_ID FROM PS_ITM_VNDR_UOM D WHERE I.ITM_SETID=D.SETID AND I.INV_ITEM_ID=D.INV_ITEM_ID AND I.VENDOR_ID=D.VENDOR_ID AND I.VNDR_LOC = D.VNDR_LOC AND I.VENDOR_SETID=D.VENDOR_SETID AND I.UNIT_OF_MEASURE=D.UNIT_OF_MEASURE AND D.CONVERSION_RATE<>0) GROUP BY A.OPRID, A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.ITM_SETID , A.INV_ITEM_ID , A.BUSINESS_UNIT_IN , A.SCHED_FLAG , A.UNIT_MEASURE_STD , A.CATEGORY_CD , A.PO_SCHED_PRIORITY , A.FROM_DT , A.TO_DT, %subrec(PO_CAP_FLEX_SBR,A) , A.QTY_MAX

# 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 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
3 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
4 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
5 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
7 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
8 SCHED_FLAG Character(1) VARCHAR2(1) NOT NULL Schedule Flag
9 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
10 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
11 PO_SCHED_PRIORITY Character(2) VARCHAR2(2) NOT NULL Schedule Priority
10=High
20=Medium
30=Low
12 FROM_DT Date(10) DATE From Date
13 TO_DT Date(10) DATE To Date
14 COLLAB_SCH_STATUS Character(2) VARCHAR2(2) NOT NULL 02/2001 cel WV-802-1.0 - Status of the supplier schedule
01=New
10=Current
20=Dispatched
30=Supplier Approved
40=Previous
50=History
15 DATETIME_COLLAB DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0. Datetime of when the line was approved by the supplier.
16 DATETIME_DISPATCH DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0
17 DATETIME_CREATE DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0.
18 SCHEDULE_START_DT Date(10) DATE 02/2001 cel WV-802-1.0
19 SCHEDULE_END_DT Date(10) DATE 02/2001 cel WV-802-1.0
20 UNIT_MEASURE_VNDR Character(3) VARCHAR2(3) NOT NULL Vendor Unit of Measure
21 DATETIME_APPLY DateTime(26) TIMESTAMP 08/27/01 SCC CN#WV840-1.0: Supplier Collaboration - New Field
22 INCLUDE_PO Character(1) VARCHAR2(1) NOT NULL Include PO Data
N=No
Y=Yes
23 DAYOFTHEWEEK Character(2) VARCHAR2(2) NOT NULL Day of the Week
1=Sunday
2=Monday
3=Tuesday
4=Wednesday
5=Thursday
6=Friday
7=Saturday
24 BUYER_ID_SEL Character(30) VARCHAR2(30) NOT NULL Buyer
25 QTY_MAX Number(16,4) DECIMAL(15,4) NOT NULL Maximum Quantity
26 CURRENT_QTY_VND Number(16,4) DECIMAL(15,4) NOT NULL 02/2001 cel WV-802-1.0. Holds the build/snapshot current requested qty on the Supplier and Schedulers Schedule Workbench. (in vendor's default uom
27 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity