PO_CAPA_PLGP_VW

(SQL View)
 Index Back

Sum of PL/by group code

cel 06/21/2003 Sum of planned orders by group code ... that are within predefined capacity date ranges for each group. Copy of PO_CAPX_PLGP_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(D.QTY_PO) FROM PS_PO_CAP_SUM_VW A , PS_ITM_VND_BU_ATTR C , PS_PO_BUY_PLAN_VW D WHERE 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 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 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
7 SCHED_GROUP_CD Character(6) VARCHAR2(6) NOT NULL 04/01/02 SCC CN#WV900-1.0: Supplier Collaboration - New Field
8 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
9 FROM_DT Date(10) DATE From Date
10 TO_DT Date(10) DATE To Date
11 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
12 DATETIME_COLLAB DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0. Datetime of when the line was approved by the supplier.
13 DATETIME_DISPATCH DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0
14 DATETIME_CREATE DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0.
15 SCHEDULE_START_DT Date(10) DATE 02/2001 cel WV-802-1.0
16 SCHEDULE_END_DT Date(10) DATE 02/2001 cel WV-802-1.0
17 UNIT_MEASURE_VNDR Character(3) VARCHAR2(3) NOT NULL Vendor Unit of Measure
18 DATETIME_APPLY DateTime(26) TIMESTAMP 08/27/01 SCC CN#WV840-1.0: Supplier Collaboration - New Field
19 INCLUDE_PO Character(1) VARCHAR2(1) NOT NULL Include PO Data
N=No
Y=Yes
20 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
21 BUYER_ID_SEL Character(30) VARCHAR2(30) NOT NULL Buyer
22 QTY_MAX Number(16,4) DECIMAL(15,4) NOT NULL Maximum Quantity
23 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
24 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity