Query to get Lease assets – Oracle EBS to Fusion

Introduction:

This blog has the SQL query that can be used to pull the lease assets details per asset book for EBS to Cloud data migration.

SQL Query: 

SELECT  fb.book_type_code asset_book

               ,LEASE.lease_number

                 ,LEASE.description lease_description

                 ,FASB_LEASE_TYPE

                 ,fcb.segment1 ||’.’||fcb.segment2    asset_category

                  ,LESSOR

                  ,LESSOR_SITE

                  , fair_value

                  , PRESENT_VALUE

                  ,FLS.START_DATE    LEASE_START_DATE

                  ,fls.end_date lease_end_date

                  

                FROM

                                 apps.fa_additions_b fab,

                                 apps.fa_additions_tl fat,

                                 apps.fa_books fb,

                                 apps.fa_categories_b fcb,

                                 apps.fa_asset_keywords fak,

                             apps.FA_LEASES_V lease ,

                                 APPS.FA_LEASE_payments FLS

                                 

    WHERE 1=1

    AND fab.asset_id = fat.asset_id

    AND fab.asset_id = fb.asset_id

    AND fb.date_ineffective IS NULL

     AND fb.transaction_header_id_out IS NULL

     AND fat.LANGUAGE = userenv(‘LANG’)

     AND fb.book_type_code =   p_source_book — ‘US_CORP_US_GAAP’

     AND fab.asset_category_id = fcb.category_id

     AND fab.asset_key_ccid = fak.code_combination_id

     AND fab.lease_id = lease.lease_id (+)

    AND fab.lease_id IS NOT NULL

     AND LEASE.PAYMENT_SCHEDULE_ID = FLS.PAYMENT_SCHEDULE_ID

     AND  NVL(FLS.END_DATE, SYSDATE+2)>= SYSDATE

     AND  fls.PAYMENT_LINE_NUMBER = (SELECT MAX(PAYMENT_LINE_NUMBER) FROM     APPS.FA_LEASE_payments fls1

      WHERE fls1.PAYMENT_SCHEDULE_ID = fls.PAYMENT_SCHEDULE_ID );