Protected by Copyscape Web Copyright Protection Software

Search This Blog

Saturday, June 8, 2013

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