OM_DEL_TOTWQ_VW(SQL View) |
Index Back |
|---|---|
Inventory Shipment PlanningTotal 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 , ' ' , M.SETID , SUM(%Sql(IN_WORKING_QTY_CASE, WQ,I,B,S,M)) , SUM(%Sql(IN_SHIP_WORKING_QTY_CASE, WQ,I,B,S,M)) , WQ.UNIT_MEASURE_ORD , WQ.UNIT_MEASURE_SHIP , WQ.USE_STD_UOM_FLAG 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_MASTER_ITEM_TBL M ON M.INV_ITEM_ID = D.INV_ITEM_ID JOIN PS_DF_SETUP_INV B ON B.BUSINESS_UNIT = D.BUSINESS_UNIT LEFT OUTER 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 JOIN PS_OM_DEL_DQTY_VW WQ ON %Join(COMMON_KEYS, OM_DEL_DQTY_VW WQ, IN_DEMAND D) AND WQ.SETID_INV_ITEM = M.SETID WHERE D.IN_FULFILL_STATE BETWEEN '10' AND '70' AND D.APPROVED_FLAG = 'Y' AND D.RESERVE_STATUS < '99' AND M.INVENTORY_ITEM = 'Y' GROUP BY D.BUSINESS_UNIT , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , D.ORDER_INT_LINE_NO , D.SCHED_LINE_NBR , M.SETID , WQ.UNIT_MEASURE_ORD , WQ.UNIT_MEASURE_SHIP , WQ.USE_STD_UOM_FLAG |
| # | 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 | DELIVERY_ID | Character(10) | VARCHAR2(10) NOT NULL | Delivery ID |
| 8 | SETID_INV_ITEM | Character(5) | VARCHAR2(5) NOT NULL | Inv Items SETID |
| 9 | DELIV_WORK_QTY | Number(16,4) | DECIMAL(15,4) NOT NULL | Delivery Working Quantity |
| 10 | DELIV_WK_QTY_SHIP | Number(16,4) | DECIMAL(15,4) NOT NULL | Delivery Shipped Work Qty |
| 11 | DELIV_UOM_QTY | Character(3) | VARCHAR2(3) NOT NULL | Delivery Order Qty UOM |
| 12 | DELIV_SHIP_UOM_QTY | Character(3) | VARCHAR2(3) NOT NULL | Delivery Ship Qty UOM |
| 13 | USE_STD_UOM_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Use Standard UOM |