IN_DEL_FRTBY_VW

(SQL View)
 Index Back

Delivery Totals

Weight, volume, quantity, and price totals for deliveries

SELECT D.BUSINESS_UNIT , D.DELIVERY_ID , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , D.BUSINESS_UNIT , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , M.SETID , SUM(%Round(%DecMult(%DecMult((CASE T.DELIV_WORK_QTY WHEN 0 THEN 0 ELSE %DecDiv(B.DELIV_WORK_QTY, T.DELIV_WORK_QTY) END), B.QTY_SCHEDULED), B.NET_UNIT_PRICE), 4)) , B.CURRENCY_CD , SUM(%Round(%DecMult(%DecMult((CASE T.DELIV_WORK_QTY WHEN 0 THEN 0 ELSE %DecDiv(B.DELIV_WORK_QTY, T.DELIV_WORK_QTY) END), B.QTY_SCHEDULED), B.NET_UNIT_PRICE_BSE), 4)) , B.CURRENCY_CD_BASE , %Round(SUM(B.DELIV_WORK_QTY), 4) , %Round(SUM(B.DELIV_WK_QTY_SHIP), 4) , SUM(%Round(%DecMult(%DecMult(D.DELIV_UNIT_VOL,%Sql(IN_DEL_WORK_PACK_QTY_CASE, B,D)), D.DELIV_UNIT_VOL_CNV), 4)) , SUM(%Round(%DecMult(%DecMult(D.DELIV_UNIT_WT,%Sql(IN_DEL_WORK_PACK_QTY_CASE, B,D)), D.DELIV_UNIT_WT_CNV), 4)) , SUM(%Round(%DecMult(%DecMult(D.DELIV_UNIT_VOL,%Sql(IN_DEL_PACK_QTY_CASE,B,D)), D.DELIV_UNIT_VOL_CNV), 4)) , SUM(%Round(%DecMult(%DecMult(D.DELIV_UNIT_WT,%Sql(IN_DEL_PACK_QTY_CASE,B,D)), D.DELIV_UNIT_WT_CNV), 4)) , B.DELIV_UOM_QTY , B.DELIV_SHIP_UOM_QTY , D.DELIV_UOM_SHIP_VOL , D.DELIV_UOM_SHIP_WT , D.DELIV_UOM_VOL , D.DELIV_UOM_WT , COUNT(*) , MIN(D.IN_FULFILL_STATE) , MAX(D.IN_FULFILL_STATE) , MAX(B.CUST_PICKUP) FROM PS_IN_DEMAND D , PS_IN_DELIVERY Y ,PS_SET_CNTRL_REC SR , PS_IN_DEL_BKTWQ_VW B , PS_MASTER_ITEM_TBL M , PS_IN_DEL_TOTWQ_VW T WHERE D.IN_FULFILL_STATE BETWEEN '10' AND '60' AND D.DELIVERY_ID > ' ' AND D.IN_PROCESS_DATE IS NOT NULL AND D.BUSINESS_UNIT = Y.BUSINESS_UNIT AND D.DELIVERY_ID = Y.DELIVERY_ID AND SR.SETCNTRLVALUE = D.BUSINESS_UNIT AND SR.RECNAME = 'MASTER_ITEM_TBL' AND %Join(COMMON_KEYS, IN_DEMAND D, IN_DEL_BKTWQ_VW B) AND B.DELIVERY_ID = D.DELIVERY_ID AND M.SETID = SR.SETID AND M.INV_ITEM_ID = D.INV_ITEM_ID AND M.INVENTORY_ITEM = 'Y' AND B.SETID_INV_ITEM = M.SETID AND T.SETID_INV_ITEM = B.SETID_INV_ITEM AND %Join(COMMON_KEYS, IN_DEL_TOTWQ_VW T, IN_DEMAND D) GROUP BY D.BUSINESS_UNIT , D.DELIVERY_ID , D.DEMAND_SOURCE , D.SOURCE_BUS_UNIT , D.ORDER_NO , M.SETID, B.CURRENCY_CD, B.CURRENCY_CD_BASE , B.DELIV_UOM_QTY , B.DELIV_SHIP_UOM_QTY , D.DELIV_UOM_SHIP_VOL, D.DELIV_UOM_SHIP_WT, D.DELIV_UOM_VOL , D.DELIV_UOM_WT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DELIVERY_ID Character(10) VARCHAR2(10) NOT NULL Delivery ID
3 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
4 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
5 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
6 SEL_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Select Business Unit
7 SEL_DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Select Demand Source
8 SEL_SOURCE_BU Character(5) VARCHAR2(5) NOT NULL Select Source Business Unit
9 SEL_ORDER_NO Character(10) VARCHAR2(10) NOT NULL Select Order
10 SETID_INV_ITEM Character(5) VARCHAR2(5) NOT NULL Inv Items SETID
11 EXTENDED_NET_PRICE Signed Number(29,4) DECIMAL(27,4) NOT NULL Ext Net Amount
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 EXT_NET_PRC_BASE Signed Number(29,4) DECIMAL(27,4) NOT NULL Extended Net Price (base)
14 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
15 ORDERING_QTY Signed Number(18,4) DECIMAL(16,4) NOT NULL Ordering Quantity
16 SHIPPING_QTY Number(17,4) DECIMAL(16,4) NOT NULL Shipping Quantity
17 SHIPPING_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Shipping Volume
18 SHIPPING_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Shipping Weight
19 PACKING_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Packing Volume
20 PACKING_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Packing Weight
21 DELIV_UOM_QTY Character(3) VARCHAR2(3) NOT NULL Delivery Order Qty UOM
22 DELIV_SHIP_UOM_QTY Character(3) VARCHAR2(3) NOT NULL Delivery Ship Qty UOM
23 DELIV_UOM_SHIP_VOL Character(3) VARCHAR2(3) NOT NULL Ship UOM for volume conversion
24 DELIV_UOM_SHIP_WT Character(3) VARCHAR2(3) NOT NULL Ship UOM for weight conversion
25 DELIV_UOM_VOL Character(3) VARCHAR2(3) NOT NULL Delivery Volume UOM
26 DELIV_UOM_WT Character(3) VARCHAR2(3) NOT NULL Delivery Weight UOM
27 NBR_DEMAND_LINES Number(4,0) SMALLINT NOT NULL Number of Demand Lines
28 MIN_FULFILL_STATE Character(2) VARCHAR2(2) NOT NULL Inventory Fulfillment State
29 MAX_FULFILL_STATE Character(2) VARCHAR2(2) NOT NULL Inventory Fulfillment State
30 CUST_PICKUP Character(1) VARCHAR2(1) NOT NULL Counter sale customer pickup flag
N=No
Y=Yes