09/28/04 EGS: Created.

IN_DMD_REQ_DPVW

(SQL View)
 Index Back

SELECT B.BUSINESS_UNIT , B.DEMAND_SOURCE , B.SOURCE_BUS_UNIT , B.ORDER_NO , B.ORDER_INT_LINE_NO , B.SCHED_LINE_NBR , B.INV_ITEM_ID , B.DEMAND_LINE_NO , C.BUSINESS_UNIT , C.REQ_ID , C.LINE_NBR , C.SCHED_NBR , C.DISTRIB_LINE_NUM , D.HASH_KEY , '100' , C.QTY_OPEN_STD , BB.QTY_PEGGED , SUM(E.QTY_PEGGED) - BB.QTY_PEGGED , C.QTY_OPEN_STD , BB.QTY_COMPLETE , BB.QTY_COMPLETE , 'Y' , %DatePart(BB.SUP_DTTM) , %TimePart(BB.SUP_DTTM) , BB.SUP_DTTM , BB.HARD_PEG , BB.PEG_STATUS , BB.DT_TIMESTAMP , C.DISTRIB_LN_STATUS FROM PS_IN_DEMAND_HASH B , PS_IN_PEGGING BB , PS_REQ_LN_DISTRIB C , PS_REQ_HASH D , PS_IN_PEGGING E , PS_REQ_LINE_SHIP F WHERE B.HASH_KEY = BB.DEMAND_HASH AND BB.DMD_ORDER_TYPE IN ('030', '070', '090') AND D.HASH_KEY = BB.SUPPLY_HASH AND BB.SUP_ORDER_TYPE = '100' AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.REQ_ID = D.REQ_ID AND C.LINE_NBR = D.LINE_NBR AND C.SCHED_NBR = D.SCHED_NBR AND C.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM AND F.BUSINESS_UNIT = C.BUSINESS_UNIT AND F.REQ_ID = C.REQ_ID AND F.LINE_NBR = C.LINE_NBR AND E.SUPPLY_HASH = BB.SUPPLY_HASH AND E.SUP_ORDER_TYPE = '100' AND ((E.PEG_STATUS < '30' AND BB.PEG_STATUS < '30') OR BB.PEG_STATUS = '30') AND NOT EXISTS( SELECT 'x' FROM PS_PO_DISTRIB_STG T WHERE T.STAGE_STATUS IN ('S','E','R','B','N','P','I','V') AND T.BUSINESS_UNIT_IN = C.BUSINESS_UNIT_IN AND T.BUSINESS_UNIT = D.BUSINESS_UNIT AND T.REQ_ID = D.REQ_ID AND T.REQ_LINE_NBR = D.LINE_NBR AND T.REQ_SCHED_NBR = D.SCHED_NBR AND T.REQ_DISTRIB_NBR = D.DISTRIB_LINE_NUM) GROUP BY B.BUSINESS_UNIT, B.ORDER_NO, B.DEMAND_SOURCE, B.SOURCE_BUS_UNIT, B.ORDER_INT_LINE_NO, B.SCHED_LINE_NBR, B.INV_ITEM_ID , B.DEMAND_LINE_NO, C.BUSINESS_UNIT, C.REQ_ID, C.LINE_NBR, C.SCHED_NBR, C.DISTRIB_LINE_NUM, D.HASH_KEY, BB.SUP_ORDER_TYPE, C.QTY_OPEN_STD, BB.QTY_PEGGED, BB.QTY_COMPLETE, BB.SUP_DTTM, BB.HARD_PEG, BB.PEG_STATUS, BB.DT_TIMESTAMP, C.DISTRIB_LN_STATUS

# 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 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
11 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
12 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
13 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
14 PEG_HASH Character(28) VARCHAR2(28) NOT NULL 07/29/04 EGS: Created
15 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
16 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available
17 QTY_PEGGED Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/16/04 EGS: Created.
18 BAD_PEG_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Bad Pegged Quantity
19 QTY_ORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/21/04 EGS: Created
20 QTY_COMPLETE Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
21 QTY_COMPLETE_PEG Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
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
27 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged
28 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
29 DISTRIB_LN_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Line Status
C=Closed
M=Mass Liquidated
O=Open
P=Unavailable for Sourcing
X=Canceled