IN_SO_NCPS_VW

(SQL View)
 Index Back

IN Peg MSR Search View

06/21/04 EGS: Created 05/30/07 EGS: Do not get closed requisitions.

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 , A.BUSINESS_UNIT , A.DESTIN_BU , '090' , A.QTY_REQUESTED_BASE - A.QTY_BACKORDER_BASE , SUM(QTY_PEGGED) , A.QTY_ALLOC_BASE , SUM(IP.QTY_RECEIVED) , SUM(IP.QTY_COMPLETE) , A.QTY_REQUESTED_BASE - A.QTY_BACKORDER_BASE , %DatePart(A.SCHED_DTTM) , %TimePart(A.SCHED_DTTM) , A.SCHED_DTTM , A.INTERUNIT_FLG , A.CUST_ID , A.SHIP_TO_CUST_ID , OH.ORDER_STATUS , OL.CNTRCT_ID , OL.CUSTOMER_PO , B.HASH_KEY , A.PEG_STATUS , A.IN_FULFILL_STATE , OS.TRFT_DISPOSITION , OS.TRFT_SRC_TYPE , A.PARENT_PROD_ID FROM PS_IN_DEMAND A , PS_IN_DEMAND_HASH B , PS_ORD_HEADER OH , PS_ORD_LINE OL , PS_ORD_SCHEDULE OS , PS_IN_PEGGING IP WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.DEMAND_SOURCE = B.DEMAND_SOURCE AND A.SOURCE_BUS_UNIT = B.SOURCE_BUS_UNIT AND A.ORDER_NO = B.ORDER_NO AND A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND A.SCHED_LINE_NBR = B.SCHED_LINE_NBR AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DEMAND_LINE_NO = B.DEMAND_LINE_NO AND A.DEMAND_SOURCE = 'OM' AND OH.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND OH.ORDER_NO = A.ORDER_NO AND OL.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND OL.ORDER_NO = A.ORDER_NO AND OL.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND OS.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND OS.ORDER_NO = A.ORDER_NO AND OS.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND OS.SCHED_LINE_NBR = A.SCHED_LINE_NBR AND A.PEG_STATUS < '40' AND IP.DEMAND_HASH = B.HASH_KEY AND IP.DMD_ORDER_TYPE = '090' AND IP.PEG_STATUS < '30' AND (IP.SUP_ORDER_TYPE <> '100' OR IP.PEG_STATUS <> '20') 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, A.DESTIN_BU, A.QTY_REQUESTED_BASE, A.QTY_ALLOC_BASE, A.QTY_BACKORDER_BASE, A.SCHED_DTTM, A.INTERUNIT_FLG, A.CUST_ID, A.SHIP_TO_CUST_ID, OH.ORDER_STATUS, OL.CNTRCT_ID, OL.CUSTOMER_PO, B.HASH_KEY, A.PEG_STATUS, A.IN_FULFILL_STATE, OS.TRFT_DISPOSITION, OS.TRFT_SRC_TYPE, A.PARENT_PROD_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business 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_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
10 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
11 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
12 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available
13 QTY_PEGGED Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/16/04 EGS: Created.
14 QTY_COMPLETE Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
15 QTY_RECEIVED Number(16,4) DECIMAL(15,4) NOT NULL Received Qty
16 QTY_COMPLETE_PEG Number(16,4) DECIMAL(15,4) NOT NULL Quantity Complete
17 QTY_ORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL 06/21/04 EGS: Created
18 SCHED_DATE Date(10) DATE Schedule Date
19 SCHED_TIME Time(15) TIMESTAMP Schedule Time
20 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
21 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
22 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
23 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
24 ORDER_STATUS Character(3) VARCHAR2(3) NOT NULL Order Status
C=Closed
O=Open
P=Pending
X=Canceled
25 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
26 CUSTOMER_PO Character(25) VARCHAR2(25) NOT NULL Customer PO
27 PEG_HASH Character(28) VARCHAR2(28) NOT NULL 07/29/04 EGS: Created
28 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged
29 IN_FULFILL_STATE Character(2) VARCHAR2(2) NOT NULL Inventory Fulfillment State
10=Pending
20=Unfulfilled
30=Releasable
40=Released
50=Confirmed
60=Shipped
70=Depleted
90=Canceled
30 TRFT_DISPOSITION Character(1) VARCHAR2(1) NOT NULL Supply Source disposition
1=Direct Ship
2=Direct Receipt
31 TRFT_SRC_TYPE Character(1) VARCHAR2(1) NOT NULL Alternate Source Type
0=All Sources
1=Inventory Business Unit
2=Distribution Network
3=New Requisition
4=New Purchase Order
5=Existing Requisition
6=Existing Purchase Order
7=Existing Interunit Transfer
8=Existing Production Order
9=New Production Order
32 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id