ONT Cancelled Orders

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
Ask a question
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