Tuesday 31 May 2016

Find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount

Below query will help to find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount. 


  SELECT prha.requisition_header_id,
         prha.segment1,
         gdr.conversion_rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (
                    DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                    1)),
            2)
            usd_amount,
         gdr.to_currency,
         gdr.from_currency,
         prla.rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (prla.rate, 1)),
            2)
            line_currency_amount,
         prla.currency_code line_currency,
         ROUND (
            SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
            2)
            actual_requisition_amount,
         gs.currency_code func_currency,
         TRUNC (prha.creation_date) creation_date,
         gcc.segment3,
         prha.org_id
    FROM po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_requisition_headers_all prha,
         gl_code_combinations_kfv gcc,
         gl_sets_of_books gs,
         gl_daily_rates gdr
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND NVL (prla.cancel_flag, 'N') <> 'Y'
         --         AND  prha.org_id = 9944
         AND prla.requisition_line_id = prda.requisition_line_id
         AND prha.authorization_status = 'APPROVED'
         AND prda.code_combination_id = gcc.code_combination_id
         AND gs.set_of_books_id = prda.set_of_books_id
         --         AND gs.currency_code <> prla.currency_code
         AND gdr.from_currency = gs.currency_code(+)
         AND gdr.to_currency(+) = 'USD'
         AND TRUNC (gdr.conversion_date) =
                TRUNC (NVL (prla.rate_date, prha.creation_date))
--         AND gcc.segment3 IN ('15510','15610','15620' )
--         AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
         prha.segment1,
         prla.rate,
         gdr.conversion_rate,
         TRUNC (prha.creation_date),
         gcc.segment3,
         gdr.to_currency,
         gdr.from_currency,
         gs.currency_code,
         prla.currency_code,
         prha.org_id
  HAVING SUM (
              NVL (prda.req_line_quantity, 1)
            * NVL (prla.unit_price, 1)
            * NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                   1)) > 100000

No comments:

Post a Comment