ONT Cancelled Orders Reason Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Cancelled Orders Reason Detail Report
Application: Order Management
Source: Cancelled Orders Reason Detail Report (XML)
Short Name: OEXOEOCR_XML
DB package: ONT_OEXOEOCR_XMLP_PKG
Description: Cancelled Orders Reason Detail Report
Application: Order Management
Source: Cancelled Orders Reason Detail Report (XML)
Short Name: OEXOEOCR_XML
DB package: ONT_OEXOEOCR_XMLP_PKG
Run
ONT Cancelled Orders Reason Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT decode(upper(:p_order_by) , 'SALESREP', sr.name, 'CANCEL_REASON',lu.meaning, NULL) dummy_salesrep_reason, decode(upper(:p_order_by), 'ORDER_DATE', h.ordered_date, 'CANCEL_DATE', to_date(lh.hist_creation_date,'DD-MON-YYYY'), NULL) dummy_order_cancel_date, decode(upper(:p_order_by) , 'ORDER_NUMBER', h.order_number, NULL) dummy_order_number, decode(upper(:p_order_by) , 'CANCEL_BY', fnd_user.user_name, NULL) dummy_cancelled_by, lh.line_id hist_line_id, lh.last_update_date, org.name cust, org.customer_number cust_no, h.order_number ordnum, h.ordered_date ord_date, nvl(lh.cancelled_quantity,0)*nvl(lh.unit_selling_price,0) amount, lh.line_id lineid, SI.description Internal_Item_Desc, lh.inventory_item_id iid, 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)) linenum, lh.shipment_number shipnum, lh.option_number optionnum, lh.ordered_quantity ordered_qty, lh.cancelled_quantity cancelled_qty, lh.cancelled_quantity2 cancelled_qty2, lh.order_quantity_uom unit1, lh.ordered_quantity_uom2 unit2, lu.meaning cancel_reason , h.salesrep_id hsalesrep_id, lh.salesrep_id lsalesrep_id, sr2.name lsalesrep, sr.name salesrep, lh.hist_creation_date candate, fnd_user.user_name username, lh.item_identifier_type, lh.ordered_item_id, lh.ordered_item, --&rp_item_flex_all_seg item_flex, h.conversion_rate conversion_rate, h.conversion_type_code conversion_type_code, h.transactional_curr_code currency_code, lh.line_category_code cat_code, NULL, ONT_OEXOEOCR_XMLP_PKG.c_currency_codeformula(h.transactional_curr_code) C_CURRENCY_CODE, ONT_OEXOEOCR_XMLP_PKG.c_gl_conv_rateformula(h.transactional_curr_code, h.ordered_date, h.conversion_type_code, h.conversion_rate) C_GL_CONV_RATE, --ONT_OEXOEOCR_XMLP_PKG.c_amountformula(:Calc_amount, :C_GL_CONV_RATE, :C_precision) C_AMOUNT, ONT_OEXOEOCR_XMLP_PKG.c_amountformula(ONT_OEXOEOCR_XMLP_PKG.calc_amountformula(lh.line_id, lh.cancelled_quantity), ONT_OEXOEOCR_XMLP_PKG.c_gl_conv_rateformula(h.transactional_curr_code, h.ordered_date, h.conversion_type_code, h.conversion_rate), ONT_OEXOEOCR_XMLP_PKG.c_precisionformula(h.transactional_curr_code)) C_AMOUNT, ONT_OEXOEOCR_XMLP_PKG.cf_unit1formula(lh.order_quantity_uom) CF_unit1, ONT_OEXOEOCR_XMLP_PKG.cf_unit2formula(lh.ordered_quantity_uom2) CF_unit2, --&item_dsp item_dsp, ONT_OEXOEOCR_XMLP_PKG.c_cancelled_qtyformula(lh.line_id, lh.cancelled_quantity) C_cancelled_qty, ONT_OEXOEOCR_XMLP_PKG.calc_amountformula(lh.line_id, lh.cancelled_quantity) Calc_amount, ONT_OEXOEOCR_XMLP_PKG.item_dspFormula(lh.item_identifier_type,lh.inventory_item_id,SI.ORGANIZATION_ID,SI.INVENTORY_ITEM_ID,lh.ordered_item_id,lh.ordered_item) item_dsp, ONT_OEXOEOCR_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision FROM oe_order_lines_history lh, oe_order_lines_all l, oe_order_headers_all h, oe_reasons r, mtl_system_items_vl SI, ra_salesreps sr, ra_salesreps sr2, oe_lookups lu, oe_sold_to_orgs_v org, fnd_user WHERE lh.header_id = h.header_id and l.header_id = h.header_id and l.line_id = lh.line_id AND lh.hist_type_code='CANCELLATION' AND h.salesrep_id=sr.salesrep_id(+) AND lh.salesrep_id=sr2.salesrep_id(+) AND r.reason_id=lh.reason_id AND r.reason_code = lu.lookup_code AND lu.lookup_type = 'CANCEL_CODE' AND nvl(SI.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0) AND lh.inventory_item_id=SI.inventory_item_id AND org.organization_id=lh.sold_to_org_id AND lh.hist_created_by=fnd_user.user_id and nvl(h.org_id,0)= nvl(:p_organization_id,0) &lp_order_num &lp_salesrep &lp_customer_name &lp_order_date &lp_item_flex_all_seg &lp_cancelled_by &lp_cancel_date &lp_cancel_reason &lp_order_category &lp_line_category --ORDER BY linenum,shipnum,optionnum,candate ORDER BY 1 ASC,2 ASC,4 ASC,3 ASC,7 ASC,9 ASC,27 ASC,35 ASC,34 ASC,38 ASC , linenum , shipnum , optionnum , candate |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Sort By |
|
LOV Oracle | |
Cancel Reason |
|
LOV Oracle | |
Cancel Date (From) |
|
Date | |
Cancel Date (To) |
|
Date | |
Cancelled By (From) |
|
LOV Oracle | |
Cancelled By (To) |
|
LOV Oracle | |
Customer Name (From) |
|
LOV Oracle | |
Customer Name (To) |
|
LOV Oracle | |
Order Number (From) |
|
Number | |
Order Number (To) |
|
Number | |
Salesperson (From) |
|
LOV Oracle | |
Salesperson (To) |
|
LOV Oracle | |
Order Date (From) |
|
Date | |
Order Date (To) |
|
Date | |
Item |
|
LOV Oracle | |
Item Display |
|
LOV Oracle | |
Order Category |
|
LOV Oracle | |
Line Category |
|
LOV Oracle | |
Show in Ledger Currency |
|
LOV Oracle | |
Mixed Precision |
|
Number |