OM_DTWQ_VW

(SQL View)
 Index Back

Inventory Shipment Planning

Total working quantity by schedule. The primary purpose of this view is to get the total working quantity by schedule for kits. The SQL does not account for drop ship products or items sourced from a PO or req, but since kits don't currently support either of those, it doesn't matter.

SELECT D.BUSINESS_UNIT , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , D.ORDER_INT_LINE_NO , D.SCHED_LINE_NBR , WQ.SETID_INV_ITEM , SUM(CASE D.IN_FULFILL_STATE WHEN '10' THEN CASE WHEN (S.ORD_SCH_STATUS = 'O' AND (S.TRFT_DISPOSITION = '2' OR S.TRFT_SRC_TYPE < '3' OR S.TRFT_SRC_TYPE > '6')) THEN (D.QTY_REQUESTED - D.QTY_BACKORDER) ELSE 0 END WHEN '20' THEN(D.QTY_REQUESTED - D.QTY_BACKORDER) WHEN '30' THEN CASE WHEN (I.SOFT_ALLOC_FLG = 'Y' OR ((I.SOFT_ALLOC_FLG = ' ' OR I.SOFT_ALLOC_FLG IS NULL) AND B.SOFT_ALLOC_FLG = 'Y')) THEN D.QTY_ALLOCATED WHEN WQ.QTY_PROMISED > 0 THEN WQ.QTY_PROMISED WHEN D.QTY_PICK_OVRIDE > 0 AND D.IN_ZERO_PICKED_FLG = 'N' THEN D.QTY_PICK_OVRIDE ELSE (D.QTY_REQUESTED - D.QTY_BACKORDER) END WHEN '40' THEN CASE WHEN S.CUST_PICKUP = 'Y' THEN (D.QTY_REQUESTED - D.QTY_BACKORDER) WHEN (I.SOFT_ALLOC_FLG = 'Y' OR ((I.SOFT_ALLOC_FLG = ' ' OR I.SOFT_ALLOC_FLG IS NULL) AND B.SOFT_ALLOC_FLG = 'Y')) THEN D.QTY_ALLOCATED WHEN WQ.QTY_PROMISED > 0 THEN WQ.QTY_PROMISED WHEN D.QTY_PICK_OVRIDE > 0 AND D.IN_ZERO_PICKED_FLG = 'N' THEN D.QTY_PICK_OVRIDE ELSE (D.QTY_REQUESTED - D.QTY_BACKORDER) END WHEN '50' THEN CASE WHEN ((D.QTY_REQUESTED - D.QTY_BACKORDER) = 0 AND (D.QTY_PICKED - D.QTY_BACKORDER) >= 0) THEN (D.QTY_PICKED - D.QTY_BACKORDER) WHEN (D.QTY_BACKORDER > 0 AND D.LAST_QTY_SHIP > 0 AND D.LAST_QTY_SHIP < D.QTY_PICKED) THEN D.LAST_QTY_SHIP ELSE D.QTY_PICKED END ELSE D.QTY_SHIPPED END) , WQ.UNIT_MEASURE_ORD , WQ.UNIT_MEASURE_SHIP , S.ORD_SCH_STATUS , S.TRFT_DISPOSITION , S.TRFT_SRC_TYPE , S.CUST_PICKUP FROM PS_IN_DEMAND D LEFT OUTER JOIN PS_DF_SETUP_IT_INV I ON I.BUSINESS_UNIT = D.BUSINESS_UNIT AND I.INV_ITEM_ID = D.INV_ITEM_ID JOIN PS_DF_SETUP_INV B ON B.BUSINESS_UNIT = D.BUSINESS_UNIT JOIN PS_ORD_SCHEDULE S ON S.SHIP_FROM_BU = D.BUSINESS_UNIT AND D.DEMAND_SOURCE = 'OM' AND S.BUSINESS_UNIT = D.SOURCE_BUS_UNIT AND S.ORDER_NO = D.ORDER_NO AND S.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO AND S.SCHED_LINE_NBR = D.SCHED_LINE_NBR AND S.DEMAND_SENT = 'Y' JOIN PS_OM_DQTY_VW WQ ON WQ.BUSINESS_UNIT = D.BUSINESS_UNIT AND WQ.DEMAND_SOURCE = D.DEMAND_SOURCE AND WQ.SOURCE_BUS_UNIT = D.SOURCE_BUS_UNIT AND WQ.ORDER_NO = D.ORDER_NO AND WQ.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO AND WQ.SCHED_LINE_NBR = D.SCHED_LINE_NBR AND WQ.INV_ITEM_ID = D.INV_ITEM_ID AND WQ.DEMAND_LINE_NO = D.DEMAND_LINE_NO WHERE D.IN_FULFILL_STATE BETWEEN '10' AND '70' AND D.IN_PROCESS_DATE IS NOT NULL AND D.APPROVED_FLAG = 'Y' AND D.RESERVE_STATUS < '99' GROUP BY D.BUSINESS_UNIT, D.DEMAND_SOURCE, D.SOURCE_BUS_UNIT, D.ORDER_NO, D.ORDER_INT_LINE_NO, D.SCHED_LINE_NBR, WQ.SETID_INV_ITEM, WQ.UNIT_MEASURE_ORD, WQ.UNIT_MEASURE_SHIP, S.ORD_SCH_STATUS, S.TRFT_DISPOSITION, S.TRFT_SRC_TYPE, S.CUST_PICKUP

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BUIN_NONVW

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

Prompt Table: SP_BU_FS_NONVW

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 SETID_INV_ITEM Character(5) VARCHAR2(5) NOT NULL Inv Items SETID
8 DELIV_WORK_QTY Number(16,4) DECIMAL(15,4) NOT NULL Delivery Working Quantity
9 DELIV_UOM_QTY Character(3) VARCHAR2(3) NOT NULL Delivery Order Qty UOM
10 DELIV_SHIP_UOM_QTY Character(3) VARCHAR2(3) NOT NULL Delivery Ship Qty UOM
11 ORD_SCH_STATUS Character(1) VARCHAR2(1) NOT NULL Schedule Status
C=Closed
O=Open
P=Pending
X=Canceled
12 TRFT_DISPOSITION Character(1) VARCHAR2(1) NOT NULL Supply Source disposition
1=Direct Ship
2=Direct Receipt
13 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
14 CUST_PICKUP Character(1) VARCHAR2(1) NOT NULL Counter sale customer pickup flag
N=No
Y=Yes