PO_CAPA_POGP_VW

(SQL View)
 Index Back

Sum of PO/by group code

cel 06/21/2003 Sum of purchaseorders by group code ... that are within predefined capacity date ranges for each group. Copy of PO_CAPX_POGP_VW. .. except... This view only does the PO's that are in first capacity range and before start of schedule. Used to combine to the proposed qty version of the apply which is only looking at start of schedule -- forward. But capacity has to look at entire range. (AND D.DUE_DT < A.SCHEDULE_START_DT ) has this line added.

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.DUE_DT < A.SCHEDULE_START_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