ONT Cancelled Orders Reason Detail

Description
Categories: BI Publisher
Columns: Dummy Salesrep Reason, Dummy Order Cancel Date, Dummy Order Number, Dummy Cancelled By, Hist Line Id, Last Update Date, Cust, Cust No, Ordnum, Ord Date ...
Application: Order Management
Source: Cancelled Orders Reason Detail Report (XML)
Short Name: OEXOEOCR_XML
DB package: ONT_OEXOEOCR_XMLP_PKG
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
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
Ledger Id
 
Number
Item Flex Code