Friday, 18 November 2016

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

/* Formatted on 11/18/2016 3:53:44 PM (QP5 v5.114.809.3010) */
  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

1 comment:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete