Monday, 24 September 2018

Period and Daily Exchange Rates Query - FUSION

SELECT gper.set_of_books_id
      ,gl_set_of_books.name
       || gper.period_name
       || gper.to_currency_code set_of_books_name
      ,gper.period_name
      ,gper.to_currency_code to_currency
      ,lk.meaning balance_type
      ,gper.avg_rate period_average_rate
      ,gper.eop_rate period_end_rate
      ,per.period_number
  FROM gl_lookups lk
      ,gl_translation_rates gper
      ,gl_sets_of_books gl_set_of_books
      ,(SELECT GP.period_name,
              MAX (GP.period_num) AS period_number
         FROM gl_periods GP
        GROUP BY GP.period_name) per
 WHERE gper.set_of_books_id = gl_set_of_books.set_of_books_id
   AND lk.lookup_type = 'TRANSLATION_BAL_TYPE'
   AND lk.lookup_code = gper.actual_flag
   AND gper.period_name = per.period_name (+)
ORDER BY gl_set_of_books.name

1 comment: