Sunday, 15 May 2016

Query to find the PO Requisition with specific amount and Conversion Type in Oracle Apps



SELECT *
  FROM (  SELECT SUM (unit_price * quantity * gdr.CONVERSION_RATE) req_cost,
                 b.requisition_header_id,
                 a.segment1,
                 a.org_id, b.DESTINATION_TYPE_CODE, gdr.conversion_type
            FROM po_requisition_headers_all a,
                 po_requisition_lines_all b,
                 org_organization_definitions ood,
                 gl_sets_of_books gsob,
                 gl_daily_rates gdr
           WHERE     a.requisition_header_id = b.requisition_header_id
                 AND a.org_id = ood.organization_id
                 AND ood.set_of_books_id = gsob.set_of_books_id
                 AND gsob.currency_code = gdr.from_currency
--                 AND gdr.conversion_type = 'Corporate'
                 AND gdr.to_currency = 'USD'
                 AND gdr.conversion_date = TRUNC (SYSDATE)
                 AND NVL (b.cancel_flag, 'N') = 'N'
                 AND a.approved_date > SYSDATE - 365
        GROUP BY b.requisition_header_id, a.segment1, a.org_id, b.DESTINATION_TYPE_CODE, gdr.conversion_type)
 WHERE req_cost > 100000

No comments:

Post a Comment