FA Query in Oracle applications

Requirement

-----------------

fa_deprn_periods table if we give period name we derive data from before financial year data

ex: if we enter Jan-12 we derive aprl-11 to dec-11 or sep-12 derive aprl-12 to aug-12 data.

Solution

--------------

select(

SELECT MIN(f2.calendar_period_open_date)

  FROM fa_deprn_periods f2

  WHERE f2.book_type_code LIKE 'DUBAI%'--='JAPAN CORP BOOK'

  AND f2.fiscal_year=f1.fiscal_year)  

  from_date,add_months(f1.calendar_period_close_date,-1) to_date,f1.fiscal_year from fa_deprn_periods f1

WHERE f1.book_type_code LIKE 'DUBAI%'

and upper(f1.period_name) ='&P_PERIOD_NAME'--MAR-12' or :P_PERIOD_NAME