ONT Salesperson Order Summary

select  sr.salesrep_id salesrep_id1
,      sc.header_id header_id1 
,      l.line_id line_id1
,      nvl(l.ordered_quantity,0) * nvl(l.unit_selling_price,0)*sc.percent/100   quota
,     decode(l.line_category_code, 'RETURN', 1, 0) 
      * nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0)*sc.percent/100   quota_rma
,      l.line_number  line_number
,      l.shipment_number  ship_number
,      l.option_number option_number
,      l.line_number||'.'|| l.shipment_number||'.'|| l.option_number  line_ship_option_num
,      nvl(l.ordered_quantity,0) ord_qty
,      nvl(l.shipped_quantity,0) shp_qty
,      nvl(l.cancelled_quantity,0) can_qty
,      nvl(l.invoiced_quantity,0)  inv_qty
,      nvl(l.unit_selling_price,0) sale_price
,      nvl(l.unit_list_price,0) list_price
,      nvl(l.ordered_quantity,0) * nvl(l.unit_selling_price,0) extended_price
,     decode(l.line_category_code, 'RETURN', 1, 0) 
     * nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0) extended_price_rma
,     nvl(l.ordered_quantity,0) * nvl(l.unit_list_price,0) extended_list_price
,      decode(l.line_category_code, 'RETURN', 1, 0) 
       * nvl(l.ordered_quantity,0)  * nvl(l.unit_list_price,0) extended_list_price_rma
,        decode(nvl(l.unit_list_price,0),0,0,
              ((nvl(l.unit_list_price,0) - nvl(l.unit_selling_price,0))/nvl(l.unit_list_price,0)) 
              * 100 ) percent_discount
,       lt.name line_type
,       l.line_category_code  line_category
,       l.salesrep_id line_salesrep_id
,       l.item_identifier_type
,       l.inventory_item_id
,       l.ordered_item_id
,       l.ordered_item
,       &rp_item_flex_all_seg item_flex 
,      NULL null_order
,      l.charge_periodicity_code, 
	ONT_OEXOESOS_XMLP_PKG.c_charge_periodicityformula(l.charge_periodicity_code) C_CHARGE_PERIODICITY, 
--ONT_OEXOESOS_XMLP_PKG.c_discount_periodicityformula(:s_sellp_periodicity, :s_listp_periodicity) c_discount_periodicity, 
ONT_OEXOESOS_XMLP_PKG.c_gl_conv_rateformula(:CURRENCY_CODE, :ORDERED_DATE, :CONVERSION_TYPE_CODE, :CONVERSION_RATE) C_GL_CONV_RATE, 
ONT_OEXOESOS_XMLP_PKG.c_priceformula(nvl ( l.ordered_quantity , 0 ) * nvl ( l.unit_selling_price , 0 ), :C_GL_CONV_RATE) C_PRICE, 
	ONT_OEXOESOS_XMLP_PKG.c_sale_priceformula(nvl ( l.unit_selling_price , 0 ), :C_GL_CONV_RATE) C_SALE_PRICE, 
	ONT_OEXOESOS_XMLP_PKG.F_PERIODICITYFORMATTRIGGER c_periodicity,
	        ONT_OEXOESOS_XMLP_PKG.Item_dspFormula(l.item_identifier_type,
			  l.inventory_item_id,
			  l.ordered_item_id,
			  l.ordered_item,
			  SI.ORGANIZATION_ID) ITEM_Dsp
from  oe_order_headers_all h, 
      oe_order_lines_all l,
      mtl_system_items_vl si, 
      oe_sales_credits sc,
      oe_sales_credit_types sct,
      ra_salesreps_all sr,
      oe_transaction_types_tl lt
where  h.header_id = :HEADER_ID
and    l.header_id = h.header_id
and    si.inventory_item_id = l.inventory_item_id   
and    nvl(si.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
and   nvl(l.org_id,0) = nvl(:p_organization_id,0)
and    sc.header_id = l.header_id
and    sct.sales_credit_type_id = sc.sales_credit_type_id
and    sct.quota_flag = 'Y'
and    sr.salesrep_id = sc.salesrep_id
and    nvl(sr.org_id,0) = nvl(:p_organization_id,0)
and    sc.salesrep_id = :salesrep_id
and    l.line_type_id=lt.transaction_type_id
and    nvl(sc.line_id,-1) = decode(nvl(sc.line_id,-1), l.line_id, l.line_id,-1)
and    lt.language = userenv('LANG')
and    not exists
      (select null
       from  oe_sales_credits sc2
       ,     oe_sales_credit_types sct2
       where sc2.header_id = l.header_id
       and   nvl(sc2.line_id,-1) != nvl(sc.line_id,-2)
       and   decode(nvl(sc.line_id,-1),l.line_id,1,2) >
             decode(nvl(sc2.line_id,-1),l.line_id,1,2)
       and   nvl(sc2.line_id,-1) = 
             decode(nvl(sc2.line_id,-1),l.line_id,l.line_id, -1)
       and   sct2.sales_credit_type_id = sc2.sales_credit_type_id
       and   sct2.quota_flag = 'Y')
&lp_line_category
&lp_line_type 
 and sr.salesrep_id=:salesrep_id 
 and sc.header_id=:header_id
order by 29 ASC,1 ASC,2 ASC,30 ASC , line_number,nvl(ship_number,0),nvl(option_number,0)