Oracle Apps Fixed Assets Table

Below are some important tables frequently used in query's in Oracle Apps for Fixed Assets module.

 

FA_ADDITIONS_B           Descriptive information about assets 

 

FA_ADDITIONS_TL          Translated Descriptive information about assets

 

FA_ADJUSTMENTS           Adjustment information on assets user by 

                         General Ledger to create journal entry lines 

                         in the general ledger  

 

FA_ASSET_HISTORY         Historical information about asset  

                         reclassification and unit adjustments 

 

FA_ASSET_INVOICES        Accounts payable and purchasing information 

                         for each asset  

 

FA_BOOKS                  Financial information of each asset  

 

FA_BOOK_CONTROLS          Control information that affects all assets 

                          in a depreciation book  

 

FA_CALENDAR_PERIODS       Detailed calendar information  

 

FA_CATEGORIES_B           Default financial information for asset 

                          categories 

 

FA_CATEGORY_BOOKS         Default financial information for an asset 

                          category and depreciation book combination 

 

 

FA_DEPRN_DETAIL           Depreciation amounts charged to the 

                          depreciation expense account in each 

                          distribution line


FA_DEPRN_PERIODS          Information about each depreciation period

FA_DISTRIBUTION_ACCOUNTS  Table to store account ccids for all 

                          distributions for a book


FA_DISTRIBUTION_HISTORY   Employee, location, and Accounting Flexfield 

                          values assigned to each asset

FA_RETIREMENTS            Information about asset retirements and 

                          reinstatements

Oracle Apps GL Period to Date(PTD) and Year to Date(YTD) query

Below query will give YTD and PTD balances for specific Account posted in a specific Period.

SELECT   gb.LEDGER_ID,
         glcc.SEGMENT1,
         glcc.segment2,
         glcc.SEGMENT3,
         glcc.segment4,
         glcc.segment5,
         glcc.segment6
         FFV.DESCRIPTION,
         gb.PERIOD_NAME,
         NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0) PTD,
         (NVL (gb.BEGIN_BALANCE_DR, 0) - NVL (gb.BEGIN_BALANCE_CR, 0))
         + (NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0)) YTD
    FROM  gl_balances gb,
          gl_ledgers gl,
          gl_code_combinations_kfv glcc,
          APPS.FND_FLEX_VALUES_VL FFV,
          gl_ledgers gl
   WHERE  gb.code_combination_id = glcc.code_combination_id
      AND gb.LEDGER_ID = gl.ledger_id
      AND GLCC.SEGMENT3 = FFV.FLEX_VALUE
      AND gl.name = :ledger_name
      AND gb.period_name = :PERIOD_NAME
ORDER BY  gb.LEDGER_ID,
          glcc.SEGMENT1,
          glcc.segment2,
          glcc.SEGMENT3,
          glcc.segment4,
          glcc.segment5,
          glcc.segment6

Oracle Apps, General Ledger, YTD query, PTD query

Oracle apps GL Trial Balance Detail Report query

Trial Balance in Oracle Apps will give you the Credits, Debits and Net balances for a specific account in a given period. Below query does the same.
Same query in Oracle Apps can be used to find out the Journals posted in a specific Account, Company for a given period.

select
gjh.name Journal_name,
gjh.je_category category_name,
gjh.je_source source_name,
gjb.name batch_name,
GCC.SEGMENT1,   -- comapny
GCC.SEGMENT2,  --cost center
GCC.SEGMENT3,  -- account
GCC.SEGMENT4,  ---Project
GCC.SEGMENT5, --Product
GCC.SEGMENT6, --Others
SUM(NVL(GJL.ACCOUNTED_DR,0))ACCOUNTED_DR,
SUM(NVL(GJL.ACCOUNTED_CR,0))ACCOUNTED_CR,
SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))BALANCE
from gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_code_combinations gcc,
GL_JE_BATCHES GJB
where gjl.je_header_id = gjh.je_header_id
AND gjh.je_batch_id=gjb.je_batch_id
and gjl.code_combination_id=gcc.code_combination_id
and gjh.ledger_id=gl.ledger_id
and gjh.status='P'        
--Actual balances
AND gjh.actual_flag='A' 
AND gjh.period_name='AUG-14'
AND GL.name='US Vision Operation'  --Ledger
GROUP BY
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
gjh.je_category ,
gjh.je_source ,
gjb.name,
gjh.name ;