--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,
