ONT Cancelled Orders
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Cancelled Orders Report
Application: Order Management
Source: Cancelled Orders Report (XML)
Short Name: OEXOEOCS_XML
DB package: ONT_OEXOEOCS_XMLP_PKG
Description: Cancelled Orders Report
Application: Order Management
Source: Cancelled Orders Report (XML)
Short Name: OEXOEOCS_XML
DB package: ONT_OEXOEOCS_XMLP_PKG
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 cust, org.customer_number cust_no, h.order_number ordnum, h.transactional_curr_code currency, h.ordered_date ord_date, (nvl(l.cancelled_quantity,0)*nvl(l.unit_selling_price,0)) amount, l.line_id lineid, l.inventory_item_id iid, DECODE(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, l.shipment_number shipnum, l.option_number optionnum, (l.cancelled_quantity) cancelled_qty, (l.cancelled_quantity2) cancelled_qty2, order_quantity_uom unit1, ordered_quantity_uom2 unit2, h.salesrep_id hsalesrep_id, sr.name hsalesrep, l.salesrep_id lsalesrep_id, sr2.name lsalesrep, l.ordered_item_id, --&rp_item_flex_all_seg item_flex, l.ordered_item, l.item_identifier_type item_identifier_type, h.conversion_rate conversion_rate, h.conversion_type_code conversion_type_code, h.transactional_curr_code currency_code, l.line_category_code cat_code, NULL, ONT_OEXOEOCS_XMLP_PKG.c_currency_codeformula(h.transactional_curr_code) C_CURRENCY_CODE, ONT_OEXOEOCS_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_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)) C_AMOUNT, ONT_OEXOEOCS_XMLP_PKG.cf_unit4formula(ordered_quantity_uom2) CF_unit4, ONT_OEXOEOCS_XMLP_PKG.cf_unit3formula(order_quantity_uom) CF_unit3, 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_dsp, ONT_OEXOEOCS_XMLP_PKG.c_precisionformula(h.transactional_curr_code) C_precision, ONT_OEXOEOCS_XMLP_PKG.rp_dummy_item_p rp_dummy_item 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 WHERE 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_id,0) and nvl(sr.org_id,0) = nvl(:p_organization_id,0) and nvl(sr2.org_id,0) = nvl(:p_organization_id,0) */ 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 &lp_order_num &lp_salesrep &lp_customer_name &lp_order_date &lp_item_flex_all_seg &lp_order_category &lp_line_category --ORDER BY linenum,shipnum,optionnum ORDER BY 2 ASC,3 ASC,4 ASC,5 ASC,7 ASC,21 ASC,9 ASC,8 ASC,29 ASC,28 ASC,32 ASC , linenum , shipnum , optionnum |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Sort By |
|
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 Name (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 |
Blitz Report™