ORD_TOT_PRC_VW

(SQL View)
 Index Back

Order Header + total qty/price

Date Initials Issue Description 072001 dms F-CJORGENS-11 Add total line adjustments

SELECT o.business_unit , o.order_no , SUM(l.qty_ordered) , SUM(l.ext_list_prc_base) , %Round((SUM(l.ext_net_prc_base) + SUM(%DecMult(l.ext_net_prc_base, %DecMult(o.addtl_adjust_pct, .01))) + o.addtl_adjust_base), 4) , SUM(l.extended_list_prc) , %Round((SUM(l.extended_net_price) + SUM(%DecMult(l.extended_net_price, %DecMult(o.addtl_adjust_pct, .01))) + o.addtl_adjust_amt), 4) , SUM(l.extended_tax_amt) , SUM(l.ext_tax_amt_base) , SUM(l.vat_amt) , SUM(l.vat_amt_base) , SUM(l.vat_amt_rvc) , SUM(l.vat_amt_rvc_bse) , SUM(l.extended_net_price) , SUM(l.ext_net_prc_base) , SUM(l.extended_tax_amt) , SUM(l.ext_tax_amt_base) , %Round((SUM(%DecMult(L.EXTENDED_NET_PRICE, %DecMult(O.ADDTL_ADJUST_PCT, .01))) + O.ADDTL_ADJUST_AMT), 4) , %Round((SUM(%DecMult(L.EXT_NET_PRC_BASE, %DecMult(O.ADDTL_ADJUST_PCT, .01))) + O.ADDTL_ADJUST_BASE), 4) , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 FROM PS_ORD_HEADER o , ps_ord_tot_ln_vw l , ps_bus_unit_tbl_om bu WHERE o.business_unit = l.business_unit AND o.order_no = l.order_no AND bu.business_unit = l.business_unit AND l.product_id <> bu.freight_prod_id GROUP BY o.business_unit, o.order_no, o.addtl_adjust_pct, o.addtl_adjust_amt, o.addtl_adjust_base, o.freight_charge, o.freight_charge_bse, o.freight_tax_amt, o.freight_tax_base UNION SELECT o.business_unit , o.order_no , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , SUM(l.extended_net_price) , SUM(l.ext_net_prc_base) , SUM(l.extended_tax_amt) , SUM(l.ext_tax_amt_base) , SUM(l.vat_amt) , SUM(l.vat_amt_base) , SUM(l.vat_amt_rvc) , SUM(l.vat_amt_rvc_bse) FROM PS_ORD_HEADER o , ps_ord_tot_ln_vw l , ps_bus_unit_tbl_om bu WHERE o.business_unit = l.business_unit AND o.order_no = l.order_no AND bu.business_unit = l.business_unit AND l.product_id = bu.freight_prod_id GROUP BY o.business_unit, o.order_no UNION SELECT o.business_unit , o.order_no , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , SUM(d.freight_charge_cnv) , SUM(d.freight_charge_bse) , SUM(d.freight_tax_amt) , SUM(d.freight_tax_base) , SUM(d.vat_amt) , SUM(d.vat_amt_bse) , SUM(d.vat_amt_rvc) , SUM(d.vat_amt_rvc_bse) FROM PS_ORD_HEADER o , ps_in_delivery_ord d WHERE o.business_unit = d.source_bus_unit AND o.order_no = d.order_no AND d.demand_source = 'OM' AND d.freight_app = 'OM' GROUP BY o.business_unit, o.order_no

# 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 ORDER_TOTAL_QTY Number(20,4) DECIMAL(19,4) NOT NULL Total Order Qty
4 EXT_LIST_PRC_BASE Signed Number(29,4) DECIMAL(27,4) NOT NULL Extended List Price (base)
5 EXT_NET_PRC_BASE Signed Number(29,4) DECIMAL(27,4) NOT NULL Extended Net Price (base)
6 EXTENDED_LIST_PRC Signed Number(29,4) DECIMAL(27,4) NOT NULL Ext List Price
7 EXTENDED_NET_PRICE Signed Number(29,4) DECIMAL(27,4) NOT NULL Ext Net Amount
8 EXTENDED_TAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Extended Tax Amount
9 EXT_TAX_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Extended Tax Amt (base)
10 EXT_VAT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Extended VAT Amount
11 EXT_VAT_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Extended VAT Amount (base)
12 EXT_VAT_AMT_RVC Signed Number(28,3) DECIMAL(26,3) NOT NULL Customer's Output VAT Total
13 EXT_VAT_AMT_RV_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Cust Output VAT Total (base)
14 EXT_GROSS_NET Signed Number(29,4) DECIMAL(27,4) NOT NULL Extended Gross Net
15 EXT_GROSS_NET_BSE Signed Number(29,4) DECIMAL(27,4) NOT NULL Extended Gross Net Base
16 TAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount
17 TAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount (base)
18 ADDTL_ADJUST_AMT Signed Number(29,4) DECIMAL(27,4) NOT NULL Adjustment Amount
19 ADDTL_ADJUST_BASE Signed Number(29,4) DECIMAL(27,4) NOT NULL Adjustment Amount (base)
20 FREIGHT_CHARGE Signed Number(17,4) DECIMAL(15,4) NOT NULL Freight Charge
21 FREIGHT_CHARGE_BSE Signed Number(17,4) DECIMAL(15,4) NOT NULL Freight Charge (base)
22 FREIGHT_TAX_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Freight Tax Amount
23 FREIGHT_TAX_BASE Signed Number(17,2) DECIMAL(15,2) NOT NULL Freight Tax Amount Base
24 FREIGHT_VAT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight VAT Amount
25 FREIGHT_VAT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight VAT Amunt (Base)
26 FREIGHT_VAT_RVC Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight VAT Amount
27 FREIGHT_VAT_RV_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight VAT Amunt (Base)