ORD_TOT_SHTO_VW(SQL View) |
Index Back |
|---|---|
Order tots by shipto cust |
| SELECT b.business_unit , b.order_no , b.product_id , c.ship_to_cust_id , b.qty_ordered , SUM(c.qty_scheduled) , %Round((SUM(%DecMult(c.qty_scheduled, c.list_price)) + %DecMult((b.qty_ordered - SUM(c.qty_scheduled)), b.list_price)), 4) , %Round((SUM(%DecMult(c.qty_scheduled, c.net_unit_price)) + %DecMult((b.qty_ordered - SUM(c.qty_scheduled)), b.price)), 4) , %Round((SUM(%DecMult(c.qty_scheduled, c.list_price_base)) + %DecMult((b.qty_ordered - SUM(c.qty_scheduled)), b.list_price_base)), 4) , %Round((SUM(%DecMult(c.qty_scheduled, c.net_unit_price_bse)) + %DecMult((b.qty_ordered - SUM(c.qty_scheduled)), b.price_base)), 4) FROM PS_ORD_SCHEDULE c , ps_ord_line b WHERE b.business_unit = c.business_unit AND b.order_no = c.order_no AND b.order_int_line_no = c.order_int_line_no AND b.ord_line_status <> 'X' AND c.ord_sch_status <> 'X' GROUP BY b.business_unit, b.order_no, b.product_id, c.ship_to_cust_id, b.qty_ordered, b.list_price, b.price, b.list_price_base, b.price_base |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | 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 |
| 3 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 4 | SHIP_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Ship To Customer |
| 5 | QTY_ORDERED | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Order Qty |
| 6 | TOTAL_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Total Quantity |
| 7 | EXTENDED_LIST_PRC | Signed Number(29,4) | DECIMAL(27,4) NOT NULL | Ext List Price |
| 8 | EXTENDED_NET_PRICE | Signed Number(29,4) | DECIMAL(27,4) NOT NULL | Ext Net Amount |
| 9 | EXT_LIST_PRC_BASE | Signed Number(29,4) | DECIMAL(27,4) NOT NULL | Extended List Price (base) |
| 10 | EXT_NET_PRC_BASE | Signed Number(29,4) | DECIMAL(27,4) NOT NULL | Extended Net Price (base) |