ONT Order Discount Summary
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Order Discount Summary Report
Application: Order Management
Source: Order Discount Summary Report (XML)
Short Name: OEXPRPRS_XML
DB package: ONT_OEXPRPRS_XMLP_PKG
Description: Order Discount Summary Report
Application: Order Management
Source: Order Discount Summary Report (XML)
Short Name: OEXPRPRS_XML
DB package: ONT_OEXPRPRS_XMLP_PKG
Run
ONT Order Discount Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT h.transactional_curr_code Currency2, ot.name Order_Type, DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL) CUSTOMER_ID, DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL) CUSTOMER_NAME_2, h.order_number Order_Number, PARTY.PARTY_NAME CUSTOMER_NAME_1, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, h.ordered_date Order_Date, ag.name Agreement, sr.name Sales_Person, sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))) Order_List, sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))) Order_Amount , curr.precision c_pre, l.charge_periodicity_code --added ,ONT_OEXPRPRS_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(h.transactional_curr_code) C_DATA_NOT_FOUND, ONT_OEXPRPRS_XMLP_PKG.CF_1FORMULA(l.charge_periodicity_code) C_CHARGE_PERIODICITY, ONT_OEXPRPRS_XMLP_PKG.C_DISCOUNT_PRICEFormula(sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))), sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))) ) C_DISCOUNT_PRICE, ONT_OEXPRPRS_XMLP_PKG.C_ORDER_AMOUNTFormula(sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN',-1*nvl(l.unit_selling_price,0),nvl(l.unit_selling_price,0))),curr.precision ) C_ORDER_AMOUNT, ONT_OEXPRPRS_XMLP_PKG.C_ORDER_LISTFORMULA(sum(nvl(l.ordered_quantity,0)* DECODE(L.LINE_CATEGORY_CODE,'RETURN', -1*nvl(l.unit_list_price,0),nvl(l.unit_list_price,0))),curr.precision) C_ORDER_LIST FROM oe_order_headers_all h, oe_order_lines_all l, OE_TRANSACTION_TYPES_TL OT, ra_salesreps sr, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, oe_agreements ag , fnd_currencies curr WHERE OT.TRANSACTION_TYPE_ID = h.order_type_id AND h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND h.salesrep_id=sr.salesrep_id (+) AND h.agreement_id = ag.agreement_id(+) AND l.header_id=h.header_id AND l.service_reference_line_id is null AND NVL(h.cancelled_flag, 'N') = 'N' and nvl(h.org_id,0) = nvl(:p_organization_id,0) and nvl(l.org_id,0) = nvl(:p_organization_id,0) and nvl(sr.org_id,0) = nvl(:p_organization_id,0) and ot.LANGUAGE = SYS_CONTEXT('USERENV','LANG') and h.transactional_curr_code = curr.currency_code &lp_customer_name &lp_customer_number &lp_salesrep &lp_agreement &lp_order_type &lp_open_orders_only &lp_order_num &lp_order_date &lp_order_category &lp_line_category GROUP BY h.transactional_curr_code, ot.name, DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID,NULL), DECODE(SUBSTR(UPPER(:P_ORDER_BY),1,1),'C',PARTY.PARTY_NAME,NULL), h.order_number, PARTY.PARTY_NAME, CUST_ACCT.ACCOUNT_NUMBER, h.ordered_date, ag.name, sr.name, curr.precision, l.charge_periodicity_code &lp_having ORDER BY h.transactional_curr_code, ot.name &lp_sort_by |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Sort By |
|
LOV Oracle | |
Open Orders Only |
|
LOV Oracle | |
Customer Name (From) |
|
LOV Oracle | |
Customer Name (To) |
|
LOV Oracle | |
Customer Number (From) |
|
LOV Oracle | |
Customer Number (To) |
|
LOV Oracle | |
Sales Person (From) |
|
LOV Oracle | |
Salesperson (To) |
|
LOV Oracle | |
Agreement |
|
LOV Oracle | |
Order Type (From) |
|
LOV Oracle | |
Order Type (To) |
|
LOV Oracle | |
Order Number (From) |
|
Number | |
Order Number (To) |
|
Number | |
Order Date (From) |
|
Date | |
Order Date (To) |
|
Date | |
Order Amount (From) |
|
Number | |
Order Amount (To) |
|
Number | |
Order List (From) |
|
Number | |
Order List (To) |
|
Number | |
Order Category |
|
LOV Oracle | |
Line Category |
|
LOV Oracle |