select order_data.cust_account_ID
,order_data.ERP_CUST_NUMBER
,order_data.org_id
,order_data.order_number
,order_data.inventory_item_id
,order_data.ordered_item
,order_data.description
,order_data.DEPARTMENT
,order_data.order_type_id
,order_data.transactional_curr_code
,order_data.ordered_quantity
,order_data.unit_selling_price
,order_data.line_category_code
,order_data.flow_status_code
,order_data.shipped_quantity
,order_data.shipped_flag
,order_data.commission
,order_data.cur_date --当前的时间用与匹配等待发运的汇率
,order_data.creation_date --订单创建日期用与匹配增量表订单的汇率
,order_data.transaction_date --发运确认日期用与匹配出货订单的汇率
,order_data.line_id --订单行ID
,order_data.line_number
,order_data.sys_hold --'Y' 为存在系统暂挂 'N'为正常订单
,order_data.hold_date
,case when order_data.transactional_curr_code='USD' then 1 else CRate_WaitOrder.Conversion_Rate end cur_rate --当前汇率
,case when order_data.transactional_curr_code='USD' then 1 else CRate_Order.Conversion_Rate end order_rate --订单创建日的汇率
,case when order_data.transactional_curr_code='USD' then 1 else CRate_Shipped.Conversion_Rate end shipped_rate --发运的汇率
,order_data.ordered_date
,case when order_data.transactional_curr_code='USD' then 1 else CRate_Ordered.Conversion_Rate end Ordered_rate --订单签订日的汇率
from
(
select oh.SOLD_TO_ORG_ID cust_account_ID, hca.account_number ERP_CUST_NUMBER,oh.org_id,oh.order_number
,ool.inventory_item_id ,ool.ordered_item,mtl_dep.DEPARTMENT,mtl_dep.description
,oh.order_type_id
,oh.transactional_curr_code
, case when ool.line_category_code='RETURN' then -ool.ordered_quantity else ool.ordered_quantity end ordered_quantity
,ool.unit_selling_price
,ool.line_category_code --ORDER 订单 RETURN 退货
,ool.flow_status_code
,ool.shipped_quantity
,case when ods.transaction_date is not null or oh.order_type_id in ( 1068,1128,1108,1109) then 'Y' else 'N' end shipped_flag --从创库判断是否发运
,case when oh.order_type_id in ( 1068,1128,1108,1109) then 1 else 0 end commission --1 为佣金订单 0 非佣金订单
,to_char(sysdate,'yyyy-mm-dd') cur_date
,to_char( ool.creation_date ,'yyyy-mm-dd') creation_date
,to_char( (case when oh.order_type_id in ( 1068,1128,1108,1109) then oh.ordered_date else ods.transaction_date end),'yyyy-mm-dd') --佣金订单取订单签订日
transaction_date
,ool.line_id
,ool.line_number || '.'|| ool.shipment_number line_number
,case when sys_hold.line_id is null then 'N' else 'Y' end sys_hold --如果为空，则不是系统暂挂，否则为系统暂挂
,sys_hold.hold_date --暂挂日期
,to_char( oh.ordered_date ,'yyyy-mm-dd') ordered_date --订单签订单日期
from OE_ORDER_LINES_ALL ool
,OE_ORDER_HEADERS_ALL oh
,HZ_CUST_ACCOUNTS hca,
(--关联查询物料对应的业务类型
select mc.INVENTORY_ITEM_ID,ms.segment1 item_code,ms.description,mc.SEGMENT1,mc.SEGMENT2 ,scv.DEPARTMENT
from MTL_ITEM_CATEGORIES_V mc ,
mtl_system_items_b ms,
( --关联有效的厂牌、产品线与之对应的业务类型
select scv.ITEM_CATEGORY_SEGMENT1,scv.ITEM_CATEGORY_SEGMENT2,scv.DEPARTMENT
from SECOM_COMPARISON_V scv where status_Code='VALID'
)scv
where mc.INVENTORY_ITEM_ID=ms.inventory_item_id(+)
and mc.ORGANIZATION_ID=ms.organization_id(+)
and mc.SEGMENT1=scv.ITEM_CATEGORY_SEGMENT1(+)
and mc.SEGMENT2=scv.ITEM_CATEGORY_SEGMENT2(+)
and ms.organization_id=84
and mc.CATEGORY_SET_ID=1
and scv.DEPARTMENT is not null --业务类型实际不会为空
) mtl_dep,
(--判断订单是否已发运
SELECT trx_source_line_id,min(mmt.transaction_date) transaction_date
FROM mtl_material_transactions mmt
WHERE mmt.transaction_type_id IN (15,33,37)
group by trx_source_line_id
) ods ,
(--取非信用暂挂且没有释放的数据
select line_id,hold_id,hold_name,creation_date hold_date
from Secom_Hold_Order_V
where 1 = case when hold_id=1 then 0 else 1 end --需把信用暂挂的排除掉
and 1 = case when released_flag='Y' then 0 else 1 end --而且是没有释放的
) sys_hold
where ool.header_id=oh.header_id(+)
and oh.SOLD_TO_ORG_ID=hca.cust_account_ID(+)
and ool.inventory_item_id=mtl_dep.INVENTORY_ITEM_ID(+)
and ool.line_id=ods.trx_source_line_id(+)
and ool.line_id=sys_hold.line_id(+)
and hca.account_number is not null
and mtl_dep.INVENTORY_ITEM_ID is not null
--and 1 = case when oh.order_type_id in( '1067','1006','1026','1005','1004','1008','1015','1012','1027','1011','1010','1014','1019','1018','1017','1021')
-- then 0 else 1 end
--and oh.SOLD_TO_ORG_ID in (select cust_account_id from secom_tmp_customer)
) order_data,
(--所有币别转换成美金的汇率(等待发运订单)
select to_char( gdr.conversion_date,'yyyy-mm-dd') conversion_date, gdr.from_currency,gdr.to_currency ,gdr.conversion_rate,gdr.inverse_conversion_rate
from GL_DAILY_RATES_V gdr
where gdr.user_conversion_type='Corporate'
and gdr.to_currency='USD'
) CRate_WaitOrder,
(--所有币别转换成美金的汇率(订单创建日的汇率)
select to_char( gdr.conversion_date,'yyyy-mm-dd') conversion_date, gdr.from_currency,gdr.to_currency ,gdr.conversion_rate,gdr.inverse_conversion_rate
from GL_DAILY_RATES_V gdr
where gdr.user_conversion_type='Corporate'
and gdr.to_currency='USD'
) CRate_Order,
(--所有币别转换成美金的汇率(发运)
select to_char( gdr.conversion_date,'yyyy-mm-dd') conversion_date, gdr.from_currency,gdr.to_currency ,gdr.conversion_rate,gdr.inverse_conversion_rate
from GL_DAILY_RATES_V gdr
where gdr.user_conversion_type='Corporate'
and gdr.to_currency='USD'
) CRate_Shipped,
(--所有币别转换成美金的汇率(订单签订增量用到)
select to_char( gdr.conversion_date,'yyyy-mm-dd') conversion_date, gdr.from_currency,gdr.to_currency ,gdr.conversion_rate,gdr.inverse_conversion_rate
from GL_DAILY_RATES_V gdr
where gdr.user_conversion_type='Corporate'
and gdr.to_currency='USD'
) CRate_Ordered
where order_data.cur_date=CRate_WaitOrder.conversion_date(+)
and order_data.transactional_curr_code=CRate_WaitOrder.from_currency(+)
and order_data.creation_date=CRate_Order.conversion_date(+)
and order_data.transactional_curr_code=CRate_Order.from_currency(+)
and order_data.transaction_date=CRate_Shipped.conversion_date(+)
and order_data.transactional_curr_code=CRate_Shipped.from_currency(+)
and order_data.ordered_date=CRate_Ordered.conversion_date(+)
and order_data.transactional_curr_code=CRate_Ordered.from_currency(+)
and order_data.erp_cust_number=86318
