ONT Cancelled Orders

➜ Back to Library
Description
Categories: BI Publisher, Sales
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
Item Flex Code 
 
Ledger Id 
 
 Number
Mixed Precision 
 
 Number
Show in Ledger Currency 
 
 LOV Oracle
Line Category 
 
 LOV Oracle
Order Category 
 
 LOV Oracle
Item Display 
 
 LOV Oracle
Item 
 
 LOV Oracle
Order Date (To) 
 
 Date
Order Date (From) 
 
 Date
Salesperson Name (To) 
 
 LOV Oracle
Salesperson (From) 
 
 LOV Oracle
Order Number (To) 
 
 Number
Order Number (From) 
 
 Number
Customer Name (To) 
 
 LOV Oracle
Customer Name (From) 
 
 LOV Oracle
Sort By 
 
 LOV Oracle
Download
 
Related Blitz Reports
ONT Cancelled Orders Reason Detail
ONT Order Discount Detail
ONT Credit Order Detail
ONT Credit Order Discrepancy
ONT Credit Order Summary
ONT Outstanding Holds
PO Unordered Receipts
ONT Internal Order and Purchasing Requisition Discrepancy
ONT Lines on Credit Check Hold