ONT Agreement Activity
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Agreement Activity Report
Application: Order Management
Source: Agreement Activity Report (XML)
Short Name: OEXOEASR_XML
DB package: ONT_OEXOEASR_XMLP_PKG
Description: Agreement Activity Report
Application: Order Management
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 | |
---|---|---|---|
Operating Unit | LOV | ||
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) | Char | ||
Order Number (High) | Char |