Oracle EBS-SQL (OM-1):查询订单发货明细.sql
select
mtrh.request_number 发货单号,
mmt.transaction_date 发货时间,
oola.creation_date 下单时间,
nvl(fu.description, fu.user_name) 下单人员,
ott.name 订单类型,
qp.name 价目表,
srp.name 销售人员,
a.acra1 销售大区,
a.acra2 办事处,
cust_acct.account_number 客户编码,
hp.party_name 客户名称,
ship_su.location 发货地址,
ship_loc.address1 客户地址,
ooha.order_number 销售订单号,
oola.line_number 订单行号,
oola.ordered_item 产品型号,
msib.segment1 物料编码,
msib.description 物料描述,
msib.primary_unit_of_measure 单位,
ooha.transactional_curr_code 订单币种,
oola.unit_selling_price 价格,
mmt.transaction_quantity 发货数量,
round(mmt.transaction_quantity * oola.unit_selling_price, 2) 发货金额,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) 汇率,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) * oola.unit_selling_price "价格CNY",
round(nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) =
trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) *
mmt.transaction_quantity * oola.unit_selling_price, 2) "发货金额CNY",
rt.name 付款条件,
mtrl.date_required 交付通知时间,
wdd.shipping_instructions 发运说明
from wsh.wsh_delivery_details wdd,
inv.mtl_txn_request_lines mtrl,
inv.mtl_txn_request_headers mtrh,
ont.oe_order_lines_all oola,
ont.oe_order_headers_all ooha,
ont.oe_transaction_types_tl ott,
jtf.jtf_rs_salesreps srp,
ar.hz_parties hp,
ar.hz_party_sites hps,
ar.hz_cust_accounts cust_acct,
ar.hz_cust_site_uses_all ship_su,
ar.hz_locations ship_loc,
ar.hz_cust_acct_sites_all ship_cas,
inv.mtl_system_items_b msib,
inv.mtl_material_transactions mmt,
applsys.fnd_user fu,
qp.qp_list_headers_tl qp,
ar.ra_terms_tl rt,
(select ffv1.flex_value acra1,
ffv2.flex_value acra2,
hc.site_use_id
from ar.ra_territories rt,
apps.fnd_flex_values_vl ffv1,
apps.fnd_flex_values_vl ffv2,
ar.hz_cust_site_uses_all hc
where ffv1.flex_value_set_id = 1014164
and ffv2.flex_value_set_id = 1014165
and rt.segment1 = ffv1.flex_value(+)
and rt.segment2 = ffv2.flex_value(+)
and hc.site_use_code = 'BILL_TO'
and rt.territory_id = hc.territory_id) a
where mtrl.header_id = mtrh.header_id
and ooha.sold_to_org_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = hp.party_id(+)
and ooha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = hps.party_site_id(+)
and ship_loc.location_id(+) = hps.location_id
and mtrl.line_id = wdd.move_order_line_id
and wdd.source_line_id = oola.line_id
and wdd.source_header_id = oola.header_id
and oola.inventory_item_id = msib.inventory_item_id(+)
and oola.ship_from_org_id = msib.organization_id(+)
and ooha.order_type_id = ott.transaction_type_id
and oola.header_id = ooha.header_id
and ooha.salesrep_id = srp.salesrep_id(+)
and ooha.org_id = srp.org_id(+)
and mmt.move_order_line_id = mtrl.line_id
and wdd.organization_id = mmt.organization_id
and wdd.inventory_item_id = mmt.inventory_item_id
and wdd.inventory_item_id = msib.inventory_item_id
and mmt.transaction_type_id = 52 --33
and mmt.transaction_quantity > 0
and ott.language = userenv('LANG')
and wdd.source_code = 'OE'
and wdd.released_status in ('C', 'Y')
and oola.org_id = x
and msib.organization_id = y
and a.site_use_id(+) = ooha.invoice_to_org_id
and fu.user_id = ooha.created_by
and qp.list_header_id = ooha.price_list_id
and qp.language = userenv('LANG')
and rt.term_id = oola.payment_term_id
and rt.language = 'ZHS'
union all ----增加退货部分
select '' 发货单号,
mmt.transaction_date 发货时间,
oola.creation_date 下单时间,
nvl(fu.description, fu.user_name) 下单人员,
ott.name 订单类型,
qp.name 价目表,
srp.name 销售人员,
a.acra1 销售大区,
a.acra2 办事处,
cust_acct.account_number 客户编码,
hp.party_name 客户名称,
ship_su.location 发货地址,
ship_loc.address1 客户地址,
ooha.order_number 销售订单号,
oola.line_number 订单行号,
oola.ordered_item 产品型号,
msib.segment1 物料编码,
msib.description 物料描述,
msib.primary_unit_of_measure 单位,
ooha.transactional_curr_code 订单币种,
oola.unit_selling_price 价格,
-round(mmt.transaction_quantity, 4) 发货数量,
-round(mmt.transaction_quantity * oola.unit_selling_price, 2) 发货金额,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) 汇率,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) * oola.unit_selling_price "价格CNY",
-round(nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) =
trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) *
mmt.transaction_quantity * oola.unit_selling_price, 2) "发货金额CNY",
rt.name 付款条件,
mmt.transaction_date 交付通知时间,
' ' 发运说明
from ont.oe_order_lines_all oola,
ont.oe_order_headers_all ooha,
ont.oe_transaction_types_tl ott,
jtf.jtf_rs_salesreps srp,
ar.hz_parties hp,
ar.hz_party_sites hps,
ar.hz_cust_accounts cust_acct,
ar.hz_cust_site_uses_all ship_su,
ar.hz_locations ship_loc,
ar.hz_cust_acct_sites_all ship_cas,
inv.mtl_system_items_b msib,
inv.mtl_material_transactions mmt,
po.rcv_transactions rt,
applsys.fnd_user fu,
qp.qp_list_headers_tl qp,
ar.ra_terms_tl rt,
(select ffv1.flex_value acra1,
ffv2.flex_value acra2,
hc.site_use_id
from ar.ra_territories rt,
apps.fnd_flex_values_vl ffv1,
apps.fnd_flex_values_vl ffv2,
ar.hz_cust_site_uses_all hc
where ffv1.flex_value_set_id = 1014164
and ffv2.flex_value_set_id = 1014165
and rt.segment1 = ffv1.flex_value(+)
and rt.segment2 = ffv2.flex_value(+)
and hc.site_use_code = 'BILL_TO'
and rt.territory_id = hc.territory_id) a
where ooha.sold_to_org_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = hp.party_id(+)
and ooha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = hps.party_site_id(+)
and ship_loc.location_id(+) = hps.location_id
and oola.inventory_item_id = msib.inventory_item_id(+)
and oola.ship_from_org_id = msib.organization_id(+)
and oola.line_category_code = 'RETURN'
and ooha.order_type_id = ott.transaction_type_id
and oola.header_id = ooha.header_id
and ooha.salesrep_id = srp.salesrep_id(+)
and ooha.org_id = srp.org_id(+)
and mmt.transaction_type_id = 15
and mmt.rcv_transaction_id = rt.transaction_id
and mmt.organization_id = rt.organization_id
and rt.oe_order_header_id = ooha.header_id
and rt.oe_order_line_id = oola.line_id
and ott.language = userenv('LANG')
and msib.organization_id = y
and a.site_use_id(+) = ooha.invoice_to_org_id
and fu.user_id = ooha.created_by
and qp.list_header_id = ooha.price_list_id
and qp.language = userenv('LANG')
and rt.term_id = oola.payment_term_id
and rt.language = 'ZHS'
union all ----直接开票订单
select to_char('') 发货单号,
oola.fulfillment_date 发货时间,
oola.creation_date 下单时间,
nvl(fu.description, fu.user_name) 下单人员,
ott.name 订单类型,
qp.name 价目表,
srp.name 销售人员,
a.acra1 销售大区,
a.acra2 办事处,
cust_acct.account_number 客户编码,
hp.party_name 客户名称,
ship_su.location 发货地址,
ship_loc.address1 客户地址,
ooha.order_number 销售订单号,
oola.line_number 订单行号,
oola.ordered_item 产品型号,
msib.segment1 物料编码,
msib.description 物料描述,
msib.primary_unit_of_measure 单位,
ooha.transactional_curr_code 订单币种,
oola.unit_selling_price 价格,
round(oola.fulfilled_quantity, 2) 发货数量,
round(oola.fulfilled_quantity * oola.unit_selling_price, 2) 发货金额,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) 汇率,
nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) = trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) * oola.unit_selling_price "价格CNY",
round(nvl((select gdr.conversion_rate
from gl.gl_daily_rates gdr
where gdr.conversion_type = 'Corporate'
and gdr.from_currency = ooha.transactional_curr_code
and trunc(gdr.conversion_date) =
trunc(ooha.creation_date)
and gdr.from_currency <> 'CNY'), 1) *
oola.fulfilled_quantity * oola.unit_selling_price, 2) "发货金额CNY",
rt.name 付款条件,
oola.fulfillment_date 交付通知时间,
' ' 发运说明
from ont.oe_order_lines_all oola,
ont.oe_order_headers_all ooha,
ont.oe_transaction_types_tl ott,
jtf.jtf_rs_salesreps srp,
ar.hz_parties hp,
ar.hz_party_sites hps,
ar.hz_cust_accounts cust_acct,
ar.hz_cust_site_uses_all ship_su,
ar.hz_locations ship_loc,
ar.hz_cust_acct_sites_all ship_cas,
inv.mtl_system_items_b msib,
applsys.fnd_user fu,
qp.qp_list_headers_tl qp,
ar.ra_terms_tl rt,
( select ffv1.flex_value acra1,
ffv2.flex_value acra2,
hc.site_use_id
from ar.ra_territories rt,
apps.fnd_flex_values_vl ffv1,
apps.fnd_flex_values_vl ffv2,
ar.hz_cust_site_uses_all hc
where ffv1.flex_value_set_id = 1014164
and ffv2.flex_value_set_id = 1014165
and rt.segment1 = ffv1.flex_value(+)
and rt.segment2 = ffv2.flex_value(+)
and hc.site_use_code = 'BILL_TO'
and rt.territory_id = hc.territory_id) a
where ooha.sold_to_org_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = hp.party_id(+)
and ooha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = hps.party_site_id(+)
and ship_loc.location_id(+) = hps.location_id
and oola.inventory_item_id = msib.inventory_item_id(+)
and oola.ship_from_org_id = msib.organization_id(+)
and ooha.order_type_id = ott.transaction_type_id
and oola.header_id = ooha.header_id
and ooha.salesrep_id = srp.salesrep_id(+)
and ooha.org_id = srp.org_id(+)
and oola.flow_status_code = 'CLOSED'
and oola.inventory_item_id = msib.inventory_item_id
and oola.ship_from_org_id = msib.organization_id
and ott.language = userenv('LANG')
and oola.line_category_code <> 'RETURN'
and oola.org_id = x
and msib.organization_id = y
and a.site_use_id(+) = ooha.invoice_to_org_id
and fu.user_id = ooha.created_by
and qp.list_header_id = ooha.price_list_id
and qp.language = userenv('LANG')
and rt.term_id = oola.payment_term_id
and rt.language = 'ZHS'
and not exists (select 'X'
from wsh.wsh_delivery_details wdd
where wdd.source_header_id = oola.header_id
and wdd.source_line_id = oola.line_id)
