Query to get the details of discounts on the orders in Order Management

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

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) -- BUG#2202575

  and ot.LANGUAGE = SYS_CONTEXT('USERENV','LANG') -- change for bug3526405

  and h.transactional_curr_code = curr.currency_code

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

ORDER BY h.transactional_curr_code,

       ot.name