select
x.customer_name,
x.customer_number,
x.order_number,
x.order_date,
x.created_by,
x.salesperson,
x.order_currency,
x.currency report_currency,
x.line_number,
x.category,
x.item,
&category_columns
x.cancelled_qty,
x.uom,
x.amount,
x.secondary_cancelled_quantity,
x.secondary_uom,
x.cancelled_date,
x.cancelled_by,
x.cancelled_reason,
x.cancelled_comments,
x.line_salesperson,
x.conversion_type,
x.conversion_rate,
x.line_num,
x.shipment_num,
x.option_num
from
(
select distinct
l.line_id,
decode(upper(:p_order_by), 'SALESREP', sr.name, null) dummy_salesrep,
decode(upper(:p_order_by), 'ORDER_DATE', h.ordered_date, null) dummy_order_date,
decode(upper(:p_order_by), 'ORDER_NUMBER', h.order_number, null) dummy_order_number,
org.name customer_name,
org.customer_number customer_number,
h.order_number order_number,
trunc(h.ordered_date) order_date,
xxen_util.user_name(h.created_by) created_by,
sr.name salesperson,
h.transactional_curr_code order_currency,
ont_oexoeocs_xmlp_pkg.c_currency_codeformula(h.transactional_curr_code) currency,
decode(l.item_type_code,'SERVICE',l.line_number ||'.'|| l.shipment_number||'.'|| l.option_number ||'.'||l.component_number || decode(l.service_number,null,null,'.'||l.service_number), 'INCLUDED',l.line_number ||'.'|| l.shipment_number||'.'|| l.option_number ||'.'||l.component_number ||decode(l.service_number,null,null,'.'||l.service_number), l.line_number || '.' || l.shipment_number || decode(l.option_number,null,null,'.'||l.option_number)) line_number,
l.line_number line_num,
l.shipment_number shipment_num,
l.option_number option_num,
l.line_category_code category,
ont_oexoeocs_xmlp_pkg.Item_dspFormula (l.item_identifier_type , l.inventory_item_id, l.ordered_item_id,l.ordered_item,si.organization_id,si.inventory_item_id) item,
l.item_identifier_type item_identifier_type,
l.cancelled_quantity cancelled_qty,
l.order_quantity_uom uom,
ont_oexoeocs_xmlp_pkg.c_amountformula(( nvl ( l.cancelled_quantity , 0 ) * nvl ( l.unit_selling_price , 0 ) ), ont_oexoeocs_xmlp_pkg.c_gl_conv_rateformula(h.transactional_curr_code, h.ordered_date, h.conversion_type_code, h.conversion_rate), ont_oexoeocs_xmlp_pkg.c_precisionformula(h.transactional_curr_code)) amount,
l.cancelled_quantity2 secondary_cancelled_quantity,
l.ordered_quantity_uom2 secondary_uom,
sr2.name line_salesperson,
h.conversion_type_code conversion_type,
ont_oexoeocs_xmlp_pkg.c_gl_conv_rateformula(h.transactional_curr_code, h.ordered_date, h.conversion_type_code, h.conversion_rate) conversion_rate,
oolh.hist_creation_date cancelled_date,
xxen_util.user_name(oolh.hist_created_by) cancelled_by,
nvl(xxen_util.meaning(ore.reason_code,'CANCEL_CODE',660),ore.reason_code) cancelled_reason,
ore.comments cancelled_comments,
l.line_id order_line_id,
si.organization_id,
si.inventory_item_id
from
oe_order_lines_all l,
oe_order_headers_all h,
mtl_system_items_vl si,
ra_salesreps_all sr,
ra_salesreps_all sr2,
oe_sold_to_orgs_v org,
(select distinct
oolh.header_id,
oolh.line_id,
last_value(oolh.hist_creation_date) over (partition by oolh.line_id order by oolh.hist_creation_date) hist_creation_date,
last_value(oolh.hist_created_by) over (partition by oolh.line_id order by oolh.hist_creation_date) hist_created_by,
last_value(oolh.reason_id) over (partition by oolh.line_id order by oolh.hist_creation_date) reason_id
from
oe_order_lines_history oolh
where
nvl(oolh.audit_flag(+), 'Y') = 'Y'
and oolh.hist_type_code(+) = 'CANCELLATION'
) oolh,
oe_reasons ore
where
nvl(:p_item_seg,'?') = nvl(:p_item_seg,'?')
and l.header_id = h.header_id
and l.cancelled_quantity > 0
and h.salesrep_id=sr.salesrep_id(+)
and l.salesrep_id=sr2.salesrep_id(+)
and si.organization_id+0 = oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id())
and l.inventory_item_id=si.inventory_item_id
and nvl(h.org_id,0) = nvl(:p_organization_id1,0)
and nvl(sr.org_id,0) = nvl(:p_organization_id1,0)
and nvl(sr2.org_id,0) = nvl(:p_organization_id1,0)
and org.organization_id = l.sold_to_org_id
and oolh.header_id(+) = l.header_id
and oolh.line_id(+) = l.line_id
and ore.reason_id(+) = oolh.reason_id
&lp_order_num &lp_salesrep &lp_customer_name &lp_order_date &lp_item &lp_order_category &lp_line_category
) x
order by
x.dummy_salesrep asc,
x.dummy_order_date asc,
x.dummy_order_number asc,
x.customer_name asc,
x.order_number asc,
x.salesperson asc,
x.order_date asc,
x.order_currency asc,
x.conversion_type asc,
x.currency asc,
x.line_num,
x.shipment_num,
x.option_num |