Friday, 7 August 2015

Ssset information and their depriciation details query in oracle apps

/* Formatted on 8/7/2015 12:34:59 PM (QP5 v5.240.12305.39446) */
  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;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete