ONT Agreement Activity
Description
Categories: BI Publisher
Columns: Currency2, Conversion Rate, Conversion Type Code, Agreement Name, Start Date, End Date, Salesperson, Customer Name, Customer Number, Order Number ...
Columns: Currency2, Conversion Rate, Conversion Type Code, Agreement Name, Start Date, End Date, Salesperson, Customer Name, Customer Number, Order Number ...
Application: Order Management
Source: Agreement Activity Report (XML)
Short Name: OEXOEASR_XML
DB package: ONT_OEXOEASR_XMLP_PKG
Source: Agreement Activity Report (XML)
Short Name: OEXOEASR_XML
DB package: ONT_OEXOEASR_XMLP_PKG
SELECT h.transactional_curr_code Currency2, h.conversion_rate conversion_rate, h.conversion_type_code conversion_type_code, a.name agreement_name, a.start_date_active start_date , a.end_date_active end_date, sr.name salesperson, c.party_name customer_name, acct.account_number customer_number, h.order_number order_number, h.ordered_date order_date, a.purchase_order_num purchase_order_num, sum((nvl(l.ordered_quantity,0) -nvl(l.cancelled_quantity,0)) * nvl(l.unit_selling_price,0)) qty_ordered, sum(nvl(l.shipped_quantity,0)*nvl(l.unit_selling_price,0)) qty_shipped, SUM(nvl(trxl.quantity_invoiced,0)*nvl(trxl.unit_selling_price,0)) qty_invoiced, ONT_OEXOEASR_XMLP_PKG.C_CONVERT_AMOUNTFormula(:P_USE_FUNCTIONAL_CURRENCY,sum((nvl(l.ordered_quantity,0) -nvl(l.cancelled_quantity,0)) * nvl(l.unit_selling_price,0)) , SUM(nvl(trxl.quantity_invoiced,0)*nvl(trxl.unit_selling_price,0)),sum(nvl(l.shipped_quantity,0)*nvl(l.unit_selling_price,0)) , h.transactional_curr_code,:rp_functional_currency, h.conversion_rate,:P_SOB_ID,h.ordered_date,h.conversion_type_code) C_CONVERT_AMOUNT, ONT_OEXOEASR_XMLP_PKG.c_use_currencyformula(h.transactional_curr_code) C_USE_CURRENCY, ONT_OEXOEASR_XMLP_PKG.c_data_not_foundformula(h.transactional_curr_code) C_DATA_NOT_FOUND, ONT_OEXOEASR_XMLP_PKG.c_qty_ordered_p c_qty_ordered, ONT_OEXOEASR_XMLP_PKG.c_qty_shipped_p c_qty_shipped, ONT_OEXOEASR_XMLP_PKG.c_qty_shipped_dsp_p c_qty_shipped_dsp, ONT_OEXOEASR_XMLP_PKG.c_qty_ordered_dsp_p c_qty_ordered_dsp, ONT_OEXOEASR_XMLP_PKG.c_qty_invoiced_dsp_p c_qty_invoiced_dsp, ONT_OEXOEASR_XMLP_PKG.c_qty_invoiced_p c_qty_invoiced --added FROM OE_AGREEMENTS a, OE_ORDER_HEADERS h, OE_ORDER_LINES_ALL l, RA_CUSTOMER_TRX_LINES_ALL trxl, QP_LOOKUPS lu, HZ_PARTIES c, HZ_CUST_ACCOUNTS acct, JTF_RS_SALESREPS sr WHERE a.agreement_type_code = lu.lookup_code(+) AND lu.lookup_type(+)='QP_AGREEMENT_TYPE' AND a.agreement_id = l.AGREEMENT_ID AND h.header_id = l.header_id AND l.line_category_code = 'ORDER' AND to_char(l.line_id) = trxl.interface_line_attribute6(+) AND trxl.interface_line_context(+) = :p_interface_line_context AND c.party_id = acct.party_id AND acct.cust_account_id = h.sold_to_org_id AND a.salesrep_id =sr.salesrep_id(+) &lp_agreement_range &lp_customer_range &lp_agreement_type &lp_purchase_num &lp_order_number_range &lp_salesperson GROUP BY h.transactional_curr_code, h.conversion_rate, h.conversion_type_code, a.name, a.start_date_active , a.end_date_active, sr.name, c.party_name, acct.account_number, h.order_number, h.ordered_date, a.purchase_order_num ORDER BY h.transactional_curr_code,a.name,c.party_name,h.order_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Agreement Name (Low) |
|
LOV Oracle | |
Agreement Name (High) |
|
LOV Oracle | |
Customer (Low) |
|
LOV Oracle | |
Customer (High) |
|
LOV Oracle | |
Agreement Type |
|
LOV Oracle | |
Purchase Order Number |
|
LOV Oracle | |
Sales Person |
|
LOV Oracle | |
Use Ledger Currency |
|
LOV Oracle | |
Order Number (Low) |
|
||
Order Number (High) |
|
||
Invoice Line Context |
|