Friday, 7 August 2015

invoice,vendor, project related details of assets query in oracle apps

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

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