DP_DDH_INV_VW

(SQL View)
Index Back

extract INV data for DDH

used for IN,PL,PR,RT demand sources - selected from SHIP_INF_INV

SELECT A.BUSINESS_UNIT , A.DATE_LAST_MAINT , A.NET_UNIT_PRICE , A.TRANSFER_COST , A.CURRENCY_CD , A.DEMAND_LINE_NO , A.DEMAND_SOURCE , A.INTERUNIT_FLG , %DatePart(A.SHIP_DTTM) , A.SHIP_DTTM , A.QTY_REQUESTED , A.QTY_REQUESTED_BASE , A.QTY_BACKORDER , A.QTY_BACKORDER_BASE , A.INV_ITEM_ID , A.ORDER_INT_LINE_NO , A.ORDER_NO , A.PRODUCT_ID , A.SCHED_LINE_NBR , %DatePart(A.SCHED_DTTM) , A.SCHED_DTTM , (CASE A.LOCATION WHEN ' ' THEN A.SHIP_TO_CUST_ID ELSE A.LOCATION END) , A.SOURCE_BUS_UNIT , A.BUSINESS_UNIT_BI , A.UNIT_MEASURE_SHIP , A.UNIT_OF_MEASURE , A.DT_TIMESTAMP , %Dttm(A.DATE_LAST_MAINT, %TimeIn('00.00.00.000000')) , A.DT_TIMESTAMP , S.SETID , IB.FORECAST_ITEM_FLAG , IB.FORECASTER , A.IN_FULFILL_STATE , IB.SOURCE_CODE FROM PS_IN_DEMAND A , PS_SET_CNTRL_REC S , PS_BU_ITEMS_INV IB WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT AND IB.BUSINESS_UNIT = A.BUSINESS_UNIT AND IB.INV_ITEM_ID = A.INV_ITEM_ID AND S.RECNAME = 'PROD_ITEM' AND IB.FORECAST_ITEM_FLAG = 'Y' AND A.IN_FULFILL_STATE IN ('60', '70')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DATE_LAST_MAINT Date(10) DATE Date Last Maintained
3 NET_UNIT_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price
4 TRANSFER_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Transfer Price
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
6 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
7 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
8 INTERUNIT_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit
N=No
Y=Yes
9 SHIP_DATE Date(10) DATE Item Shipping Date
10 SHIP_DTTM DateTime(26) TIMESTAMP Ship Date/Time
11 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
12 QTY_REQUESTED_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested Base
13 QTY_BACKORDER Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Qty
14 QTY_BACKORDER_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Backorder Base Qty
15 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
16 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
17 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
18 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
19 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
20 SCHED_DATE Date(10) DATE Schedule Date
21 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
22 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
23 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
24 BUSINESS_UNIT_BI Character(5) VARCHAR2(5) NOT NULL Billing Business Unit
25 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
26 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
27 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
28 DP_UPDDTTM_0 DateTime(26) TIMESTAMP date/time of last update to level 0 record used by messaging system to select records changed in the FDM system
29 DP_ADD_DTTM_0 DateTime(26) TIMESTAMP DP_ADD_DTTM_0
30 SETID Character(5) VARCHAR2(5) NOT NULL SetID
31 FORECAST_ITEM_FLAG Character(1) VARCHAR2(1) NOT NULL Demand Planning Item
32 FORECASTER Character(20) VARCHAR2(20) NOT NULL Forecaster
33 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
34 SOURCE_CODE Character(2) VARCHAR2(2) NOT NULL 8/5/97 lee - inactivated source code 4 - service
1=Make
2=Buy
3=Expense
4=Service
5=Floor Stock
6=Planning