09/28/04 EGS: Created.

IN_DMD_PRO_IPVW

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT , A.DEMAND_SOURCE , A.SOURCE_BUS_UNIT , A.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.INV_ITEM_ID , A.DEMAND_LINE_NO , C.BUSINESS_UNIT , C.PRODUCTION_ID , C.MG_OUTPUT_TYPE , C.MG_OUTPUT_ITEM , C.OP_SEQUENCE , C.MG_OUTPUT_QTY_CODE , D.HASH_KEY , '020' , C.BUSINESS_UNIT %Concat X.PEG_KEY_SEPARATOR %Concat C.PRODUCTION_ID , %TrimSubstr(C.MG_OUTPUT_ITEM, 1, 18) , C.MG_OUTPUT_SCHD_QTY - C.COMPLETED_QTY , SUM(E.QTY_PEGGED) , C.MG_OUTPUT_SCHD_QTY , 'N' , SPH.NEW_END_DATE , SPH.NEW_END_TIME , %Dttm(SPH.NEW_END_DATE, SPH.NEW_END_TIME) , 'N' FROM PS_IN_DEMAND A , PS_SF_OUTPUT_LIST C , PS_SF_PRDNID_HEADR SPH , PS_SF_OUTPUT_HASH D , PS_IN_PEGGING E , PS_INSTALLATION_IN X WHERE C.BUSINESS_UNIT = SPH.BUSINESS_UNIT AND C.PRODUCTION_ID = SPH.PRODUCTION_ID AND NOT EXISTS ( SELECT 'X' FROM PS_IN_DEMAND_HASH IDH , PS_IN_PEGGING IP WHERE IDH.BUSINESS_UNIT = A.BUSINESS_UNIT AND IDH.DEMAND_SOURCE = A.DEMAND_SOURCE AND IDH.SOURCE_BUS_UNIT = A.SOURCE_BUS_UNIT AND IDH.ORDER_NO = A.ORDER_NO AND IDH.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND IDH.SCHED_LINE_NBR = A.SCHED_LINE_NBR AND IDH.INV_ITEM_ID = A.INV_ITEM_ID AND IDH.DEMAND_LINE_NO = A.DEMAND_LINE_NO AND IP.DEMAND_HASH = IDH.HASH_KEY AND IP.DMD_ORDER_TYPE IN ('030', '070', '090') AND IP.SUPPLY_HASH = D.HASH_KEY AND IP.SUP_ORDER_TYPE = '020' AND IP.PEG_STATUS = '10') AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.PRODUCTION_ID = D.PRODUCTION_ID AND C.MG_OUTPUT_TYPE = D.MG_OUTPUT_TYPE AND C.MG_OUTPUT_ITEM = D.MG_OUTPUT_ITEM AND C.OP_SEQUENCE = D.OP_SEQUENCE AND C.MG_OUTPUT_QTY_CODE = D.MG_OUTPUT_QTY_CODE AND C.MG_OUTPUT_SCHD_QTY - C.COMPLETED_QTY > 0 AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.MG_OUTPUT_ITEM AND D.HASH_KEY = E.SUPPLY_HASH AND E.SUP_ORDER_TYPE = '020' AND E.PEG_STATUS = '10' GROUP BY A.BUSINESS_UNIT, A.DEMAND_SOURCE, A.SOURCE_BUS_UNIT, A.ORDER_NO, A.ORDER_INT_LINE_NO, A.SCHED_LINE_NBR, A.INV_ITEM_ID, A.DEMAND_LINE_NO, C.BUSINESS_UNIT, C.PRODUCTION_ID, C.MG_OUTPUT_TYPE, C.MG_OUTPUT_ITEM, C.OP_SEQUENCE, C.MG_OUTPUT_QTY_CODE, D.HASH_KEY, C.MG_OUTPUT_SCHD_QTY, C.COMPLETED_QTY, SPH.NEW_END_DATE, SPH.NEW_END_TIME, X.PEG_KEY_SEPARATOR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
2 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
3 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
4 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
5 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
6 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
7 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
8 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
11 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
12 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.
13 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
14 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
15 PEG_HASH Character(28) VARCHAR2(28) NOT NULL 07/29/04 EGS: Created
16 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
17 PEG_ORDER_KEY Character(30) VARCHAR2(30) NOT NULL Used to store the various Order Number formats in 1 field
18 PEG_LINE_KEY Character(30) VARCHAR2(30) NOT NULL Used to Store the concatenation of Line, Demand, Sched, Kit, etc in 1 field.
19 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available
20 BAD_PEG_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Bad Pegged Quantity
21 QTY_ORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/21/04 EGS: Created
22 PEG_FLAG Character(1) VARCHAR2(1) NOT NULL 06/17/04 EGS: Created.
23 SCHED_DATE Date(10) DATE Schedule Date
24 SCHED_TIME Time(15) TIMESTAMP Schedule Time
25 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
26 HARD_PEG Character(1) VARCHAR2(1) NOT NULL 09/01/04 EGS: Created