ONT Salesperson Order Summary

Description
Categories: BI Publisher
Application: Order Management
Source: Salesperson Order Summary Report (XML)
Short Name: OEXOESOS_XML
DB package: ONT_OEXOESOS_XMLP_PKG
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)
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Order Number (From)
 
Number
Order Number (To)
 
Number
Order Date (From)
 
Date
Order Date (To)
 
Date
Order Type
 
LOV Oracle
Line Type
 
LOV Oracle
Agreement
 
LOV Oracle
Salesperson (From)
 
LOV Oracle
Salesperson (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Order Category
 
LOV Oracle
Line Category
 
LOV Oracle
Item Display
 
LOV Oracle
Mixed Precision
 
Number
Show Open Orders Only
 
LOV Oracle
Use Ledger Currency
 
LOV Oracle
Ledger Id
 
Number
Item Flex Code