DP_DDH_BI_VW

(SQL View)
 Index Back

extract Billing data for DDH

select transactional data from BI tables to bundle into a message and publish it to demand planning for the demand planning data history

SELECT A.BUSINESS_UNIT , A.INVOICE , A.LINE_SEQ_NUM , A.UNIT_OF_MEASURE , A.ORDER_INT_LINE_NO , A.ORDER_NO , A.BUSINESS_UNIT_OM , A.SHIP_FROM_BU , A.PRODUCT_ID , B.BILL_TO_CUST_ID , B.INVOICE_DT , A.SOLD_TO_CUST_ID , A.SHIP_TO_CUST_ID , B.SALES_PERSON , C.DEMAND_DATE , C.INV_ITEM_ID , A.BASE_CURRENCY , %DatePart(C.SCHED_DTTM) , C.SCHED_DTTM , C.QTY_SHIPPED , C.QTY_SHIP_BASE , C.SOURCE_BUS_UNIT , C.NET_UNIT_PRICE , C.DEMAND_LINE_NO , C.DEMAND_SOURCE , C.SCHED_LINE_NBR , C.UNIT_MEASURE_SHIP , A.NET_EXTENDED_BSE , A.SHIP_DATE , C.SHIP_DTTM , A.LAST_UPDATE_DTTM , B.LAST_UPDATE_DTTM , S.SETID , IB.FORECAST_ITEM_FLAG , IB.FORECASTER , C.IN_FULFILL_STATE , IB.SOURCE_CODE FROM PS_BI_LINE A , PS_BI_HDR B , PS_IN_DEMAND C , PS_SET_CNTRL_REC S , PS_BU_ITEMS_INV IB WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INVOICE = A.INVOICE AND C.BUSINESS_UNIT = A.SHIP_FROM_BU AND C.ORDER_NO = A.ORDER_NO AND C.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND IB.BUSINESS_UNIT = A.SHIP_FROM_BU AND IB.INV_ITEM_ID = C.INV_ITEM_ID AND IB.FORECAST_ITEM_FLAG = 'Y' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.RECNAME = 'CUSTOMER' AND C.IN_FULFILL_STATE >= '60'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
3 LINE_SEQ_NUM Number(5,0) INTEGER NOT NULL Sequence
4 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
5 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
6 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
7 BUSINESS_UNIT_OM Character(5) VARCHAR2(5) NOT NULL Order Management Business Unit
8 SHIP_FROM_BU Character(5) VARCHAR2(5) NOT NULL Ship from INV BU
9 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
10 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
11 INVOICE_DT Date(10) DATE Invoice Date
12 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
13 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
14 SALES_PERSON Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer.
15 DEMAND_DATE Date(10) DATE Demand Date
16 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
18 SCHED_DATE Date(10) DATE Schedule Date
19 SCHED_DTTM DateTime(26) TIMESTAMP 08/16/01 JNW (CN#PL900-1.0): Added
20 QTY_SHIPPED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
21 QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Ship Base
22 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
23 NET_UNIT_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price
24 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
25 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
26 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
27 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
28 NET_EXTENDED_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Extended Amount
29 SHIP_DATE Date(10) DATE Item Shipping Date
30 SHIP_DTTM DateTime(26) TIMESTAMP Ship Date/Time
31 DP_UPDDTTM_1 DateTime(26) TIMESTAMP date/time of last update to level 1 record used by messaging system to select records changed in the FDM system
32 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
33 SETID Character(5) VARCHAR2(5) NOT NULL SetID
34 FORECAST_ITEM_FLAG Character(1) VARCHAR2(1) NOT NULL Demand Planning Item
35 FORECASTER Character(20) VARCHAR2(20) NOT NULL Forecaster
36 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
37 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