SO Shipment订单已做发运出货，查看发运状况

--generate data for 1 month so shipment

           select
                   org_id,
                   customer_id,
                   transactional_curr_code,
                   nvl(sum(((ordered_quantity-shipped_quantity)*unit_selling_price*tax_code)), 0) as amt
           from (
                   select
                           ooha.org_id,
                           oola.sold_to_org_id as customer_id,
                           transactional_curr_code,
                           ooha.header_id,
                           oola.line_id,
                           nvl(oola.ordered_quantity, 0) as ordered_quantity,  -- 下訂單時輸入的數量
                           nvl(unit_selling_price, 0) as unit_selling_price,          -- 銷售單位（不含稅）
                           nvl(tax_value, 0) as tax_value,
                           decode(tax_code, null, 1, (replace(replace(nvl(tax_code, 1), 'QY'), '%')+100)/100) format_tax_code,
                           (case when substr(replace(tax_code,' ','|'),3,1) ='|' then
                                   decode(tax_code, null, 1, (to_number(rtrim(substr(tax_code, instr(replace(tax_code,' ','|'),'|'), length(tax_code)),'%'))+100)/100)   
                              else decode(tax_code, null, 1, (to_number(substr(tax_code, 1, length(tax_code)-1))+100)/100)                
                           end) as tax_code,
                           ---##################################
                           -- 最終以出貨表的出貨量 wdv.shipped_quantity 為準，而不是以下訂單時的   訂單數量
                           (select nvl(sum(kol.kol_mtl_uom_conversion_func(wdv.requested_quantity_uom, oola.order_quantity_uom, wdv.shipped_quantity)), 0)
                              from apps.wsh_deliverables_v wdv
                             where wdv.source_header_id = ooha.header_id
                               and wdv.inv_interfaced_flag = 'Y'
                               and wdv.source_line_id = oola.line_id) as shipped_quantity 
                           ---##################################      
                    from  apps.oe_order_headers_all ooha,
                          apps.oe_order_lines_all oola
                   where  ooha.header_id = oola.header_id
                    and  schedule_ship_date is not null   -- 已出貨的物料的 “計劃出貨日期” 是不為空的
                     and  trunc(schedule_ship_date) < add_months(sysdate, 1)
                     --modified as at 2010/09/02--
                     and  oola.cancelled_flag = 'N'   -- 物料行是否因其它原因作了“取消”， cancelled_flag 是否取消
                     -- started -- added by pan 2011-04-14
                    and  line_type_id in (select transaction_type_id from ont.oe_transaction_types_all where order_category_code <> 'RETURN')   -- 行類型 <> RETURN 不是RMA退貨類型
           ) 
           group by org_id, customer_id, transactional_curr_code
           order by org_id, customer_id, transactional_curr_code;

TAX_CODE只有4種類型（目前4種，以後是否有增改，另作討論）：

以下SQL只是用來處理一個稅率問題而已（都是修改了N手的產品，先把功能實現，性能以後說，方法N百種，供參與）:

1. decode(tax_code, null, 1, (replace(replace(nvl(tax_code, 1), 'QY'), '%')+100)/100) format_tax_code,
2.  (case when substr(replace(tax_code,' ','|'),3,1) ='|' then 
               decode(tax_code, null, 1, (to_number(rtrim(substr(tax_code, instr(replace(tax_code,' ','|'),'|'), length(tax_code)),'%'))+100)/100)    
         else decode(tax_code, null, 1, (to_number(substr(tax_code, 1, length(tax_code)-1))+100)/100)                 
      end) as tax_code,