Important SQL Queries related to Oracle Fixed Asset

Important SQL Queries related to Oracle Fixed Asset

 

Developed for version 11.5.10

 

--Below sql to extract asset information and their depriciation 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 ;

--Below sql to extract 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 ;

Combined SQL-

SELECT
FAB.ASSET_TYPE ASSET_CLASS,
FAB.ASSET_NUMBER  ASSET,
PV.VENDOR_NAME VENDOR,
FAI.INVOICE_NUMBER APBILL,
FAB.ASSET_NUMBER ASSET_NAME,
FAT.DESCRIPTION ASSET_DESCRITPION,
NULL EMPLOYEE,
'NOT AVAILABLE' DEPARTMENT,
FLK.CONCATENATED_SEGMENTS LOCATION,
'NOT AVAILABLE' CLASS,
NULL PROJECT,
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,
FAB.ASSET_ID ASSET_ID,
FB.BOOK_TYPE_CODE BOOK,
FAI.AP_DISTRIBUTION_LINE_NUMBER INVOICE_LINE,
FAI.FIXED_ASSETS_COST INVOICE_LINE_AMOUNT
FROM
APPS.FA_ADDITIONS_B FAB,
APPS.FA_ADDITIONS_TL FAT,
APPS.FA_BOOKS FB,
APPS.FA_DISTRIBUTION_HISTORY FDH,
APPS.FA_LOCATIONS_KFV FLK,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.FA_DEPRN_SUMMARY FDS,
APPS.GL_SETS_OF_BOOKS  GSOB,
APPS.FA_ASSET_INVOICES FAI LEFT OUTER JOIN APPS.PO_VENDORS PV ON FAI.PO_VENDOR_ID =PV.VENDOR_ID
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 FAT.LANGUAGE = USERENV('LANG')
And Fb.Book_Type_Code <>'ACE TAX'
And Fab.Asset_Id=Fai.Asset_Id --AND FAB.ASSET_ID=37875
and fai.DATE_INEFFECTIVE IS NULL
ORDER BY FAB.ASSET_ID,FB.BOOK_TYPE_CODE ;