Monday, October 31, 2016

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 ;