09/28/04 EGS: Created.

IN_PRO_DMD_DPVW

(SQL View)
 Index Back

SELECT B.BUSINESS_UNIT , B.PRODUCTION_ID , B.MG_OUTPUT_TYPE , B.MG_OUTPUT_ITEM , B.OP_SEQUENCE , B.MG_OUTPUT_QTY_CODE , D.BUSINESS_UNIT , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , D.ORDER_INT_LINE_NO , D.SCHED_LINE_NBR , D.INV_ITEM_ID , D.DEMAND_LINE_NO , C.DESTIN_BU , D.BUSINESS_UNIT , D.HASH_KEY , BB.DMD_ORDER_TYPE , C.QTY_REQUESTED_BASE - C.QTY_BACKORDER_BASE , BB.QTY_PEGGED , SUM(E.QTY_PEGGED) - BB.QTY_PEGGED , C.QTY_REQUESTED_BASE - C.QTY_BACKORDER_BASE , C.QTY_ALLOC_BASE , BB.QTY_RECEIVED , BB.QTY_COMPLETE , 'Y' , %DatePart(C.SCHED_DTTM) , %TimePart(C.SCHED_DTTM) , C.SCHED_DTTM , BB.HARD_PEG , BB.PEG_STATUS , C.INTERUNIT_FLG , BB.DT_TIMESTAMP , C.PRIORITY_NBR FROM PS_SF_OUTPUT_HASH B , PS_IN_PEGGING BB , PS_IN_DEMAND C , PS_IN_DEMAND_HASH D , PS_IN_PEGGING E WHERE B.HASH_KEY = BB.SUPPLY_HASH AND BB.SUP_ORDER_TYPE = '020' AND D.HASH_KEY = BB.DEMAND_HASH AND BB.DMD_ORDER_TYPE IN ('030', '070', '090') AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.DEMAND_SOURCE = C.DEMAND_SOURCE AND D.SOURCE_BUS_UNIT = C.SOURCE_BUS_UNIT AND D.ORDER_NO = C.ORDER_NO AND D.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND D.SCHED_LINE_NBR = C.SCHED_LINE_NBR AND D.INV_ITEM_ID = C.INV_ITEM_ID AND D.DEMAND_LINE_NO = C.DEMAND_LINE_NO AND E.DEMAND_HASH = BB.DEMAND_HASH AND E.DMD_ORDER_TYPE IN ('030', '070', '090') AND (E.SUP_ORDER_TYPE <> '100' OR E.PEG_STATUS <> '20') AND ((E.PEG_STATUS < '30' AND BB.PEG_STATUS < '30') OR BB.PEG_STATUS = '30') GROUP BY B.BUSINESS_UNIT, B.PRODUCTION_ID, B.MG_OUTPUT_TYPE, B.MG_OUTPUT_ITEM, B.OP_SEQUENCE, B.MG_OUTPUT_QTY_CODE, D.BUSINESS_UNIT, D.ORDER_NO, D.DEMAND_SOURCE, D.SOURCE_BUS_UNIT, D.ORDER_INT_LINE_NO, D.SCHED_LINE_NBR, D.INV_ITEM_ID , D.DEMAND_LINE_NO, C.DESTIN_BU, D.HASH_KEY, BB.DMD_ORDER_TYPE, C.QTY_REQUESTED_BASE, C.QTY_ALLOC_BASE, C.QTY_BACKORDER_BASE, BB.QTY_PEGGED, BB.QTY_RECEIVED, BB.QTY_COMPLETE, C.SCHED_DTTM, BB.HARD_PEG, BB.PEG_STATUS, C.INTERUNIT_FLG, BB.DT_TIMESTAMP, C.PRIORITY_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_SUP Character(5) VARCHAR2(5) NOT NULL 07/29/04 EGS: Created
2 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
3 MG_OUTPUT_TYPE Character(2) VARCHAR2(2) NOT NULL 10/22/98 (CN#EN800-6.0) LAJ...Created Stores the output item type on a Bill of Materials. The type could represent either a co-product (primary or secondary) or a by-product (waste or recycle). 11/23/98 (CN#SF800-13) JNW added teardown xlat
CP=Primary
CS=Co-Product
RB=Recycle
TD=Teardown
WB=Waste
4 MG_OUTPUT_ITEM Character(18) VARCHAR2(18) NOT NULL 04/10/00 (Internet Architecture) SCC: Uppercase label Id. 10/22/98 (CN#EN800-6.0) LAJ...Created Stores the output item number on a Bill of Materials. This item may be either a co-product or a by-product on the BOM. The output type field determines which one it represents. There should always be at least one output item on the BOM at all times; this output item is the same as the BOMs item id.
5 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
6 MG_OUTPUT_QTY_CODE Character(3) VARCHAR2(3) NOT NULL 01/28/98 (CN#EN800-6.0) LAJ...Created Stores the quantity code for an output item on a Bill of Materials. It was created with the same values as qty_code, but by separating these two fields, it allows greater flexibility in the future.
ASY=Assembly
ORD=Order
7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
8 DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Demand Source
IN=Material Request
OM=Sales Order
PL=Planning Requisition
PO=Purchase Order
PR=Purchasing Requisition
RT=Material Return
SF=Production Request
WM=Work Order
9 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
10 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
11 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
12 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
13 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
14 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
15 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
16 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
17 PEG_HASH Character(28) VARCHAR2(28) NOT NULL 07/29/04 EGS: Created
18 PEG_ORDER_TYPE Character(4) VARCHAR2(4) NOT NULL Used by Pegging Maintenance/Inquiry
010=Purchase
020=Production
030=Transfer Demand
035=Transfer Supply
070=Stock Request
090=Sales Order
100=Requisition
110=WO Inventory
115=WO Non-Inventory
19 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available
20 QTY_PEGGED Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/16/04 EGS: Created.
21 BAD_PEG_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Bad Pegged Quantity
22 QTY_ORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/21/04 EGS: Created
23 QTY_COMPLETE Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
24 QTY_RECEIVED Number(16,4) DECIMAL(15,4) NOT NULL Received Qty
25 QTY_COMPLETE_PEG Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
26 PEG_FLAG Character(1) VARCHAR2(1) NOT NULL 06/17/04 EGS: Created.
27 SCHED_DATE Date(10) DATE Schedule Date
28 SCHED_TIME Time(15) TIMESTAMP Schedule Time
29 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
30 HARD_PEG Character(1) VARCHAR2(1) NOT NULL 09/01/04 EGS: Created
31 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged
32 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
33 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
34 PRIORITY_NBR Number(3,0) SMALLINT NOT NULL Priority