DP_DDH_SO_VW

(SQL View)
 Index Back

extract shipping data for DDH

update DDH from shipping tables 04/27/2004 - Added Ship_DTTM, SCHED_DTTM and SCHED_LINE_NBR. Loaded SHIP_DATE and SCHED_DATE using %Datepart() Meta SQL.

SELECT A.BUSINESS_UNIT , A.DATE_LAST_MAINT , A.NET_UNIT_PRICE , A.CURRENCY_CD , A.DEMAND_LINE_NO , A.DEMAND_SOURCE , %DatePart(A.SHIP_DTTM) , A.SHIP_DTTM , A.QTY_SHIPPED , A.QTY_SHIP_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 , A.SHIP_TO_CUST_ID , A.SOURCE_BUS_UNIT , A.BUSINESS_UNIT_BI , A.UNIT_MEASURE_SHIP , A.UNIT_OF_MEASURE , %Dttm(A.REQUEST_DATE, %TimeIn('00.00.00.000000')) , A.CUST_ID , IB.SOURCE_CODE , A.DT_TIMESTAMP , A.DT_TIMESTAMP , A.DT_TIMESTAMP , S.SETID , IB.FORECAST_ITEM_FLAG , IB.FORECASTER , A.IN_FULFILL_STATE FROM PS_IN_DEMAND A , PS_SET_CNTRL_REC S , PS_BU_ITEMS_INV IB WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.RECNAME = 'PROD_ITEM' AND IB.BUSINESS_UNIT = A.BUSINESS_UNIT AND IB.INV_ITEM_ID = A.INV_ITEM_ID 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 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
5 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
6 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
7 SHIP_DATE Date(10) DATE Item Shipping Date
8 SHIP_DTTM DateTime(26) TIMESTAMP Ship Date/Time
9 QTY_SHIPPED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
10 QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Ship Base
11 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
12 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
13 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
14 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
15 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
16 SCHED_DATE Date(10) DATE Schedule Date
17 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
18 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
19 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
20 BUSINESS_UNIT_BI Character(5) VARCHAR2(5) NOT NULL Billing Business Unit
21 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
22 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
23 REQ_SHIP_DTTM DateTime(26) TIMESTAMP Requested Ship
24 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
25 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
26 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
27 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
28 DP_ADD_DTTM_0 DateTime(26) TIMESTAMP DP_ADD_DTTM_0
29 SETID Character(5) VARCHAR2(5) NOT NULL SetID
30 FORECAST_ITEM_FLAG Character(1) VARCHAR2(1) NOT NULL Demand Planning Item
31 FORECASTER Character(20) VARCHAR2(20) NOT NULL Forecaster
32 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