ONT Cancelled Orders

➜ Back to Library
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
Run ONT Cancelled Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
 
Related Blitz Reports
ONT Order Discount Detail ONT Cancelled Orders Reason Detail ONT Order Headers and Lines ONT Credit Order Detail GL Account Analysis (Distributions) GL Account Analysis ONT DIFOT ONT Outstanding Holds ONT Credit Order Discrepancy
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™