Category: FA – Fixed Assets

Oracle EBS R12 Fixed Assets Tutorial pdf,  Fixed Assets Pending Invoices,   Fixed Assets Accounting setup, Fixed Assets Reconciliation , Fixed Assets Period Closure

SQL Query to drill down FA Assets to GL

The below given query provides the drill down from the FA Assets mapping to GL with XLA Tables

SELECT
FTH.ASSET_ID, FAB.ASSET_NUMBER,
FTH.TRANSACTION_TYPE_CODE,
FTH.DATE_EFFECTIVE,
FAB.ASSET_TYPE,
GJH.JE_HEADER_ID,
GJH.PERIOD_NAME,
XE.EVENT_ID,
GJH.NAME HEADER_NAME,
GCC.SEGMENT1 BC,
GCC.SEGMENT3 DEPT,
GCC.SEGMENT4 ACCT
FROM
GL_JE_HEADERS GJH,
GL.GL_JE_LINES GJL,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_PERIODS GLP,
GL_IMPORT_REFERENCES IMP,
XLA_AE_LINES  XAL ,
XLA.XLA_AE_HEADERS XAH,
XLA.XLA_EVENTS XE,
XLA_TRANSACTION_ENTITIES XTE,
FA_TRANSACTION_HEADERS  FTH,
FA_ADDITIONS_B FAB
WHERE 1=1
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE                    = 'Assets'
and GJH.JE_CATEGORY                  = 'Addition'
AND GJL.JE_HEADER_ID                 = IMP.JE_HEADER_ID
AND GJL.JE_LINE_NUM                  = IMP.JE_LINE_NUM
AND IMP.GL_SL_LINK_ID                = GJH.JE_HEADER_ID
AND IMP.GL_SL_LINK_TABLE             = XAL.GL_SL_LINK_TABLE
AND XAL.APPLICATION_ID               = XAH.APPLICATION_ID
AND XAL.AE_HEADER_ID                 = XAH.AE_HEADER_ID
AND XAH.APPLICATION_ID               = XE.APPLICATION_ID
AND XAH.EVENT_ID                     = XE.EVENT_ID
AND XE.APPLICATION_ID                = XTE.APPLICATION_ID
AND XTE.APPLICATION_ID               = 20004
AND XE.ENTITY_ID                     = XTE.ENTITY_ID
--AND XTE.ENTITY_CODE                  IN ( 'TRANSACTIONS', 'ADJUSTMENTS')
AND XAL.CODE_COMBINATION_ID          = GCC.CODE_COMBINATION_ID
AND XTE.ENTITY_ID                    = XE.ENTITY_ID
AND XTE.SOURCE_ID_INT_1              = FTH.TRANSACTION_HEADER_ID
AND FTH.ASSET_ID                     = FAB.ASSET_ID
--AND FAB.ASSET_NUMBER                 = '1474840'

Oracle EBS R12 SQL to fetch fixed asset invoices

The blog provides the EBS R12 SQL Query to fetch fixed (FA) asset invoices data. The EBS R12 SQL provides the data  extract related to the  invoice, vendor, project related details of assets
 
SELECT DISTINCT
'I' RECORD_IDENTIFIER,
B.ASSET,
FAI.INVOICE_NUMBER APBILL,
FAI.AP_DISTRIBUTION_LINE_NUMBER INVOICE_LINE,
(SELECT  PV.VENDOR_NAME FROM APPS.PO_VENDORS PV WHERE PV.VENDOR_ID  = FAI.PO_VENDOR_ID ) SUPPLIER,
(SELECT PPA.NAME FROM APPS.PA_PROJECTS_ALL PPA WHERE  PPA.PROJECT_ID=FAI.PROJECT_ID )PROJECT,
FAI.FIXED_ASSETS_COST INVOICE_LINE_AMOUNT,
B.ASSET_ID
FROM
APPS.FA_ASSET_INVOICES FAI,
(SELECT
FAB.ASSET_TYPE ASSET_CLASS,
FAB.ASSET_NUMBER ASSET,
FAB.ASSET_NUMBER ASSET_NAME,
FAT.DESCRIPTION ASSET_DESCRITPION,
PAPF.FULL_NAME EMPLOYEE,
FLK.CONCATENATED_SEGMENTS LOCATION,
FB.BOOK_TYPE_CODE BOOK,
FB.ORIGINAL_COST ASSET_COST,
GSOB.CURRENCY_CODE CURRENCY_CODE,
FDS.DEPRN_RESERVE ACCUMULATED_DEPRECIATION,
FB.DATE_PLACED_IN_SERVICE,
FDS.DEPRN_RUN_DATE LAST_POSTING_DATE,
FAB.TAG_NUMBER ASSET_TAG,
FAB.SERIAL_NUMBER SERIAL_NUMBER,
GCCK.CONCATENATED_SEGMENTS EXPENSE_ACCOUNT,
FAB.ASSET_ID ASSET_ID
FROM
APPS.FA_ADDITIONS_B FAB,
APPS.FA_ADDITIONS_TL FAT,
APPS.FA_BOOKS FB,
APPS.FA_DISTRIBUTION_HISTORY FDH,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FA_LOCATIONS_KFV FLK,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.FA_DEPRN_SUMMARY FDS,
APPS.GL_SETS_OF_BOOKS  GSOB
WHERE
FAB.ASSET_ID=FAT.ASSET_ID
AND FAB.ASSET_ID=FDH.ASSET_ID
AND FAB.ASSET_ID=FB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID=FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE='DEPRN'
AND FDS.PERIOD_COUNTER =(SELECT MAX(PERIOD_COUNTER) FROM APPS.FA_DEPRN_SUMMARY FDSS WHERE FDSS.DEPRN_SOURCE_CODE='DEPRN' AND FDSS.ASSET_ID=FB.ASSET_ID AND FDSS.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.TRANSACTION_HEADER_ID_IN=(SELECT MAX(TRANSACTION_HEADER_ID_IN) FROM APPS.FA_BOOKS FB1 WHERE FB1.ASSET_ID=FB.ASSET_ID AND FB1.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FDH.LOCATION_ID=FLK.LOCATION_ID
AND GCCK.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GCCK.CHART_OF_ACCOUNTS_ID=GSOB.CHART_OF_ACCOUNTS_ID
AND FDH.ASSIGNED_TO = PAPF.PERSON_ID (+)
AND FAT.LANGUAGE = USERENV('LANG')
AND FB.BOOK_TYPE_CODE <>'ACE TAX') B
WHERE
B.ASSET_ID=FAI.ASSET_ID
and fai.DATE_INEFFECTIVE IS NULL
ORDER BY B.ASSET_ID ;

 


EBS R12 SQL to get fixed asset depreciation details

The blog provides the EBS R12 SQL to get Fixed Asset information and related depreciation details
SELECT
'A' RECORD_IDENTIFIER,
FAB.ASSET_TYPE ASSET_CLASS,
FAB.ASSET_NUMBER ASSET,
FAB.ASSET_NUMBER ASSET_NAME,
FAT.DESCRIPTION ASSET_DESCRITPION,
PAPF.FULL_NAME EMPLOYEE,
FLK.CONCATENATED_SEGMENTS LOCATION,
FB.BOOK_TYPE_CODE BOOK,
FB.ORIGINAL_COST ASSET_COST,
GSOB.CURRENCY_CODE CURRENCY_CODE,
FDS.DEPRN_RESERVE ACCUMULATED_DEPRECIATION,
FB.DATE_PLACED_IN_SERVICE,
FDS.DEPRN_RUN_DATE LAST_POSTING_DATE,
FAB.TAG_NUMBER ASSET_TAG,
FAB.SERIAL_NUMBER SERIAL_NUMBER,
GCCK.CONCATENATED_SEGMENTS EXPENSE_ACCOUNT,
FAB.ASSET_ID ASSET_ID
FROM
APPS.FA_ADDITIONS_B FAB,
APPS.FA_ADDITIONS_TL FAT,
APPS.FA_BOOKS FB,
APPS.FA_DISTRIBUTION_HISTORY FDH,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FA_LOCATIONS_KFV FLK,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.FA_DEPRN_SUMMARY FDS,
APPS.GL_SETS_OF_BOOKS  GSOB
WHERE
FAB.ASSET_ID=FAT.ASSET_ID
AND FAB.ASSET_ID=FDH.ASSET_ID
AND FAB.ASSET_ID=FB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID=FB.ASSET_ID
AND FDS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDS.DEPRN_SOURCE_CODE='DEPRN'
AND FDS.PERIOD_COUNTER =(SELECT MAX(PERIOD_COUNTER) FROM APPS.FA_DEPRN_SUMMARY FDSS WHERE FDSS.DEPRN_SOURCE_CODE='DEPRN' AND FDSS.ASSET_ID=FB.ASSET_ID AND FDSS.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.TRANSACTION_HEADER_ID_IN=(SELECT MAX(TRANSACTION_HEADER_ID_IN) FROM APPS.FA_BOOKS FB1 WHERE FB1.ASSET_ID=FB.ASSET_ID AND FB1.BOOK_TYPE_CODE=FB.BOOK_TYPE_CODE)
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FDH.LOCATION_ID=FLK.LOCATION_ID
AND GCCK.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GCCK.CHART_OF_ACCOUNTS_ID=GSOB.CHART_OF_ACCOUNTS_ID
AND FDH.ASSIGNED_TO = PAPF.PERSON_ID (+)
AND FAT.LANGUAGE = USERENV('LANG')
AND FB.BOOK_TYPE_CODE <>'ACE TAX'
ORDER BY ASSET_ID ;