ONT Salesperson Order Summary

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Salesperson Order Summary Report
Application: Order Management
Source: Salesperson Order Summary Report (XML)
Short Name: OEXOESOS_XML
DB package: ONT_OEXOESOS_XMLP_PKG
Run ONT Salesperson Order Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit 
 
 LOV
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
Download
Related Blitz Reports
Yanmar - Discount on order lines check GL Account Analysis GL Account Analysis (Distributions) AR Customers and Sites RK: AR Transactions and Lines Avalara VAT LinesData Avalara VAT listing ONT Credit Order Detail ONT DIFOT
Blitz Report™

Blitz Report™ provides multiple benefits: