PO_CAPX_POGP_VW

(SQL View)
 Index Back

Sum of PO/by group code

cel 05/01/2002 Sum of purchaseorders by group code ... that are within predefined capacity date ranges for each group.

SELECT DISTINCT A.OPRID , A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.ITM_SETID , A.SCHED_GROUP_CD , A.UNIT_MEASURE_STD , A.FROM_DT , A.TO_DT , %subrec(PO_CAP_FLEX_SBR,A) , A.QTY_MAX , 0 , SUM(%Round(%DecMult(D.QTY_PO,J.CONVERSION_RATE) ,4)) FROM PS_PO_CAP_SUM_VW A , PS_ITM_VND_BU_ATTR C , PS_PO_BUY_POALL_VW D , PS_INV_ITEM_UOM J WHERE A.INCLUDE_PO <> 'N' AND A.INV_ITEM_ID = ' ' AND A.ITM_SETID = C.SETID AND A.SCHED_GROUP_CD = C.SCHED_GROUP_CD AND A.VENDOR_ID = C.VENDOR_ID AND A.VENDOR_SETID = C.VENDOR_SETID AND A.VNDR_LOC = C.VNDR_LOC AND D.VENDOR_SETID = C.VENDOR_SETID AND D.VENDOR_ID = C.VENDOR_ID AND D.VNDR_LOC = C.VNDR_LOC AND D.INV_ITEM_ID = C.INV_ITEM_ID AND D.ITM_SETID = C.SETID AND D.BUSINESS_UNIT_IN = C.BUSINESS_UNIT_IN AND D.DUE_DT >= A.FROM_DT AND D.DUE_DT <= A.TO_DT AND D.ITM_SETID=J.SETID AND D.INV_ITEM_ID=J.INV_ITEM_ID AND D.UNIT_OF_MEASURE=J.UNIT_OF_MEASURE AND J.CONVERSION_RATE<>0.0000 AND D.INV_ITEM_ID NOT IN ( SELECT X.INV_ITEM_ID FROM PS_ITM_VNDR_UOM X WHERE D.ITM_SETID=X.SETID AND D.INV_ITEM_ID=X.INV_ITEM_ID AND D.VENDOR_ID=X.VENDOR_ID AND D.VNDR_LOC = X.VNDR_LOC AND D.VENDOR_SETID=X.VENDOR_SETID AND D.UNIT_OF_MEASURE=X.UNIT_OF_MEASURE AND X.CONVERSION_RATE<>0) GROUP BY A.OPRID, A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.ITM_SETID ,A.SCHED_GROUP_CD, A.BUSINESS_UNIT_IN , A.UNIT_MEASURE_STD, 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 SCHED_GROUP_CD Character(6) VARCHAR2(6) NOT NULL 04/01/02 SCC CN#WV900-1.0: Supplier Collaboration - New Field
7 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
8 FROM_DT Date(10) DATE From Date
9 TO_DT Date(10) DATE To Date
10 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
11 DATETIME_COLLAB DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0. Datetime of when the line was approved by the supplier.
12 DATETIME_DISPATCH DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0
13 DATETIME_CREATE DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0.
14 SCHEDULE_START_DT Date(10) DATE 02/2001 cel WV-802-1.0
15 SCHEDULE_END_DT Date(10) DATE 02/2001 cel WV-802-1.0
16 UNIT_MEASURE_VNDR Character(3) VARCHAR2(3) NOT NULL Vendor Unit of Measure
17 DATETIME_APPLY DateTime(26) TIMESTAMP 08/27/01 SCC CN#WV840-1.0: Supplier Collaboration - New Field
18 INCLUDE_PO Character(1) VARCHAR2(1) NOT NULL Include PO Data
N=No
Y=Yes
19 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
20 BUYER_ID_SEL Character(30) VARCHAR2(30) NOT NULL Buyer
21 QTY_MAX Number(16,4) DECIMAL(15,4) NOT NULL Maximum Quantity
22 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
23 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity