ONT Returns by Reason

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Returns by Reason Report (XML)
Short Name: OEXOEORR_XML
DB package: ONT_OEXOEORR_XMLP_PKG
select  lu.meaning   reason,
        h.order_number order_number,
        h.ordered_date  order_date,
        h.transactional_curr_code  currency_code,
        OTYPE.name credit_order_type,
        l.line_id line_id,
        l.line_number line_number,
        l.line_number || decode (l.shipment_number, null, null, '.' || l.shipment_number) || 
        decode (l.option_number, null, null, '.' || l.option_number) line_shipment_option_number,
        l.item_identifier_type,
        L.INVENTORY_ITEM_ID,
        L.ORDERED_ITEM_ID,
        L.ORDERED_ITEM,
        &RP_ITEM_FLEX_ALL_SEG ITEM_FLEX,
       &RP_ITEM_FLEX_CODE item_code,
        nvl(l.ordered_quantity,0)        authorized_qty,
        nvl(l.fulfilled_quantity,0)    fulfilled_qty,
        nvl(l.unit_selling_price,0)  unit_selling_price,
        LTYPE.name line_type,
        l.item_revision, 
   ONT_OEXOEORR_XMLP_PKG.Item_dspFormula (l.item_identifier_type , l.inventory_item_id, l.ordered_item_id,l.ordered_item,SI.ORGANIZATION_ID, l.inventory_item_id) Item_dsp_1,
	ONT_OEXOEORR_XMLP_PKG.c_valueformula(nvl ( l.unit_selling_price , 0 ), nvl ( l.fulfilled_quantity , 0 )) c_value
from oe_order_headers_all h,
         oe_order_lines_all l,
         OE_TRANSACTION_TYPES_TL OTYPE,
         OE_TRANSACTION_TYPES_TL  LTYPE,
         ar_lookups lu ,
       mtl_system_items_vl si
where   h.header_id = l.header_id
and     l.line_category_code = 'RETURN'
and     l.return_reason_code = lu.lookup_code (+)
and     lu.lookup_type (+) = 'CREDIT_MEMO_REASON'
and     l.item_type_code(+) != 'INCLUDED' 
and     h.booked_flag = 'Y' 
and     l.booked_flag = 'Y' 
and     nvl(h.cancelled_flag, 'N') = 'N' 
and     l.inventory_item_id = si.inventory_item_id 
and    nvl(si.organization_id,0) = nvl(:c_master_org,0)
and    h.order_type_id = OTYPE.TRANSACTION_TYPE_ID (+) 
and    otype.language(+) = userenv('LANG')    
and    l.line_type_id = LTYPE.TRANSACTION_TYPE_ID (+)
and    ltype.language(+) = userenv('LANG')     
and    nvl(l.org_id,0) = nvl(h.org_id,0)
and    nvl(h.org_id,0) = nvl(:p_org_id, 0)
&lp_item_flex_all_seg
&lp_return_reason
&lp_order_date
&lp_credit_order_type
&lp_credit_order_line_type
order by 
lu.meaning
&RP_ITEM_ORDER_BY
Parameter NameSQL textValidation
Item Flex Code
 
Ledger Id
 
Number
Mixed Precision
 
Number
Item Display
 
LOV Oracle
Item
 
LOV Oracle
Credit Order Line Type
 
LOV Oracle
Credit Order Type
 
LOV Oracle
Credit Order Date (To)
 
Date
Credit Order Date (From)
 
Date
Return Reason
 
LOV Oracle