Fixed Asset ERP Data to Blackline Tool

 Select  '1' as KEY,

        market,

       segment4,

       segment1,

       segment2,

       Period_End_Date,

       sum(period_activity)+ sum(begin_balance) BALANCE

from 

(

with bal as (select /*+ materialize */ * FROM  (SELECT 

FTH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'COST' Type,

fab.asset_id,

FDD.cost,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

fa_distribution_history fadh, 

fa_transaction_headers fth,

gl_code_combinations gcc, 

fa_additions_b fab,

FA_DEPRN_PERIODS FDP,

FA_DEPRN_DETAIL FDD

WHERE 1=1

and fth.asset_id = fadh.asset_id

and fth.book_type_code = fadh.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fth.asset_id

and fadh.transaction_header_id_in = fth.transaction_header_id

and fadh.book_type_code = fth.book_type_code

AND FDD.ASSET_ID = FTH.ASSET_ID

AND FDD.DISTRIBUTION_ID = FADH.DISTRIBUTION_ID

AND FDD.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDD.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

)

AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

UNION ALL

SELECT 

FADH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'DEP_RES' Type,

fab.asset_id,

fds.deprn_reserve COST,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

FA_DEPRN_DETAIL fds, 

fa_distribution_history fadh, 

gl_code_combinations gcc, 

fa_additions_b fab, 

FA_DEPRN_PERIODS FDP

WHERE 1=1

and fadh.asset_id = fds.asset_id

and fadh.DISTRIBUTION_ID = fds.DISTRIBUTION_ID

and fadh.book_type_code = fds.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fadh.asset_id

AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDP.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDS.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDS.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

) 

AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE))

,CURRENT_UNITS AS (select /*+ materialize */ * FROM  (SELECT FAH.ASSET_ID , FAH.UNITS , FAH.BOOK_TYPE_CODE , GCC1.SEGMENT4 COST_SEGMENT4, GCC2.SEGMENT4 RESERVE_SEGMENT4

FROM 

FA_ASSET_HISTORY FAH

,FA_CATEGORY_BOOKS FCB

,GL_CODE_COMBINATIONS GCC1

,GL_CODE_COMBINATIONS GCC2

,(SELECT MAX(FTH.TRANSACTION_HEADER_ID) TRANSACTION_HEADER_ID, FTH.ASSET_ID , FTH.BOOK_TYPE_CODE

FROM  FA_TRANSACTION_HEADERS FTH

,XLA_EVENTS XE

,FA_ASSET_HISTORY FAH1

WHERE 1=1

AND FTH.EVENT_ID = XE.EVENT_ID

AND XE.EVENT_DATE  <= LAST_DAY(fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY'))

AND FTH.TRANSACTION_HEADER_ID = FAH1.TRANSACTION_HEADER_ID_IN

AND FTH.BOOK_TYPE_CODE = FAH1.BOOK_TYPE_CODE

AND FTH.ASSET_ID = FAH1.ASSET_ID

GROUP BY FTH.ASSET_ID , FTH.BOOK_TYPE_CODE) ASSET_HISTORY

WHERE 1=1

AND ASSET_HISTORY.ASSET_ID = FAH.ASSET_ID

AND ASSET_HISTORY.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN

AND ASSET_HISTORY.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE

AND FAH.CATEGORY_ID = FCB.CATEGORY_ID

AND FAH.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE

AND FCB.ASSET_COST_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID

AND FCB.RESERVE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID))

SELECT rf.ancestor_pk1_value           

                        || Chr(9)            market,

                         (CASE WHEN Type = 'DEP_RES' THEN CU.RESERVE_SEGMENT4

ELSE CU.COST_SEGMENT4 END)

                        || Chr(9)            segment4,

                        bal.segment1

                        || Chr(9)            segment1,

                        bal.segment2

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)            segment2,

To_char(Last_day(fnd_date.String_to_date(gp.period_name,

                 'Mon-YY')),

'MM/DD/YYYY'

|| Chr(9)

|| Chr(9)

|| Chr(9))           Period_End_Date,

(CASE WHEN Type = 'DEP_RES' THEN To_char((round(bal.COST,2))*(-1),'fm999999999990.00')

ELSE  To_char(round(bal.cost,2),'fm999999999990.00') END) period_activity ,

TO_CHAR(nvl(null,0) ,'fm999999999990.00') begin_balance

from 

bal,

CURRENT_UNITS CU,

gl_ledgers GLG,

(SELECT DISTINCT tree_code,ancestor_pk1_value,distance,pk1_value FROM gl_seg_val_hier_rf) rf,

gl_periods GP,

fnd_lookup_values  flv,

fa_book_controls fbc

where 1=1

and GLG.ledger_category_code = 'PRIMARY'

AND gp.period_num = :P_PERIOD_NUM

AND gp.period_year = :P_PERIOD_YEAR

AND GLG.name = nvl(:P_LEDGER,GLG.name)

AND fnd_date.String_to_date(bal.period_name,'Mon-YY') <= fnd_date.String_to_date(gp.period_name,'Mon-YY')

AND fbc.book_type_code = bal.book_type_code

and fnd_date.String_to_date(bal.period_name,'Mon-YY') <=NVL(flv.end_Date_active,SYSDATE+1)

AND rf.pk1_value = bal.segment1

AND rf.tree_code = flv.description

AND flv.lookup_type = 'xxxxxxxxxxxx'

AND rf.distance = flv.tag

and flv.language='US'

and flv.lookup_code = 'xxxxxxxxxxxx' -- RICE ID for the given object

AND fbc.book_class = 'CORPORATE'

AND GLG.LEDGER_ID = fbc.set_of_books_id

AND CU.ASSET_ID = BAL.ASSET_ID

AND CU.BOOK_TYPE_CODE = BAL.BOOK_TYPE_CODE

)

group by 

market,

       segment4,

       segment1,

       segment2,

       Period_End_Date

order by 

market,

       segment4,

       segment1,

       segment2

Oracle Fixed Assets Integration Report

 

WITH hier_area AS (SELECT /*+ materialize */ DISTINCT dep29_pk1_value, dep1_pk1_value

                     FROM gl_seg_val_hier_cf WHERE tree_structure_code = 'GL_ACCT_FLEX'

                      AND tree_code = 'xxxxxxxxx'

                      AND tree_version_id = :P_TREE_V

                  ), 

report AS (

    SELECT 

        open_period,

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        description,

        conversion_id,

        parent_id,

        unit_number,

        vin,

        category_major,

        category_minor,

        quantity,

        SUM(cost)                        cost,

        SUM(accum_deprn)                 accum_deprn,

        ( SUM(cost) - SUM(accum_deprn) ) nbv,

        SUM(deprn)                       deprn,

        ( SUM(ytd_dpr) + SUM(ytd_pdp) )  ytd_dpr,

        revaluation_reserve,

        in_service_date,

        life,

        CASE

            WHEN rem_life_in_mon <= 0 THEN 0

            ELSE rem_life_in_mon

        END rem_life_in_mon,

        convention,

        dept,

        product,

        project,

        from_curr,

        wm_ownership,

        acquired_asset,

        fa_lcode,

        address,

        city,

        county,

        state,

        country,

        postal_code,

        concat_expns_acc,

        goodwill_ownership,

        run_date,

        area,

        book_type_code,

        'X' AS transaction_type_code

    FROM

        (

            SELECT /*+ materialize */

                gcc.segment1                                                                     comp_code,

                gcc.segment2                                                                     facility_id,

                gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) facility_desc,

                fab.asset_number                                                                 asset_id,

                fab.attribute1                                                                   conversion_id,

                fab.description,

                fcb.segment1                                                                     category_major,

                fcb.segment2                                                                     category_minor,

                fl.segment2                                                                      fa_lcode,

                hl.address_line_1                                                                address,

                hl.postal_code,

                hl.town_or_city                                                                  city,

                hl.region_1                                                                      county,

                hl.region_2                                                                      state,

                hl.country,

                fct.prorate_convention_code                                                      convention,

                fm.life_in_months                                                                life,

                to_char(fb.date_placed_in_service, 'MM/DD/YYYY', 'nls_date_language=American')   in_service_date,

                nvl(fdh.units_assigned, 0)                                                       quantity,

                gcc.segment3                                                                     dept,

                gcc.segment5                                                                     product,

                gcc.segment6                                                                     project,

                sob.currency_code                                                                from_curr,

                nvl(fdd.cost, 0)                                                                 cost,

                nvl(fdd.deprn_reserve, 0)                                                        accum_deprn,

                nvl(fdd.ytd_deprn, 0)                                                            ytd_dpr1,

                nvl((SELECT SUM(nvl(xl.accounted_cr, - 1 * xl.accounted_dr)) ytd_deprn

                       FROM fa_deprn_detail      fdd1,

                            xla_ae_headers       xh,

                            xla_ae_lines         xl,

                            gl_code_combinations gcc1

                      WHERE fdd1.asset_id = fab.asset_id

                        AND fdd1.book_type_code = fb.book_type_code

                        AND fdd1.distribution_id = fdd.distribution_id

                        AND fdd1.event_id = xh.event_id

                        AND xh.ae_header_id = xl.ae_header_id

and xl.application_id = 140

                        AND xl.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xl.code_combination_id

                        AND gcc1.segment2 = gcc.segment2

                        AND fdd1.period_counter IN(SELECT MAX(fdpi.period_counter)

                                                     FROM fa_deprn_detail fdpi

                                                    WHERE fdpi.book_type_code = fb.book_type_code

                                                      AND fdpi.asset_id = fab.asset_id

                                                      AND fdpi.distribution_id = fdd.distribution_id)

                    ),

                  0)                                                                             deprn,

                nvl((

                    SELECT 

                        SUM(nvl(xla1.accounted_cr, - 1 * xla1.accounted_dr))

                    FROM

                        ( SELECT DISTINCT fdd1.event_id

                            FROM fa_deprn_detail fdd1

                           WHERE fdd1.asset_id = fab.asset_id

                             AND fdd1.book_type_code = fb.book_type_code

                             AND fdd1.deprn_source_code = 'D'

                             AND fdd1.period_counter IN( SELECT period_counter

                                                           FROM fa_deprn_periods fdp1

                                                          WHERE fdp1.book_type_code = fb.book_type_code -- fdd1.book_type_code commented by amruta

                                                            AND fdp1.fiscal_year = EXTRACT(YEAR FROM sysdate) )

                        )                    fdd1,

                        xla_ae_headers       xah1,

                        xla_ae_lines         xla1,

                        gl_code_combinations gcc1

                    WHERE 1=1

and fdd1.event_id = xah1.event_id

                        AND xah1.ae_header_id = xla1.ae_header_id

and xla1.application_id = 140

                        AND xla1.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xla1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND EXTRACT(YEAR FROM xla1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                ),

                    0)                                                                          ytd_dpr,

                nvl((

                    SELECT SUM(nvl(xl1.accounted_dr, - 1 * xl1.accounted_cr))

                    FROM fa_transaction_headers ftv1,

                        xla_ae_headers         xh1,

                        xla_ae_lines           xl1,

                        gl_code_combinations   gcc1,

                        fa_additions_vl        fab11

                    WHERE ftv1.asset_id = fab.asset_id

                        AND ftv1.book_type_code = fb.book_type_code

                        AND ftv1.event_id = xh1.event_id

                        AND xl1.ae_header_id = xh1.ae_header_id

                        AND xl1.code_combination_id = gcc1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND ftv1.asset_id = fab11.asset_id

                        AND EXTRACT(YEAR FROM xl1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                        AND xl1.accounting_class_code IN('DEPRECIATION_EXPENSE', 'UNPLANNED_DEPRECIATION_EXPENSE')

                ),

                    0) AS ytd_pdp,

                decode(fds.reval_reserve, 0, '-', fds.reval_reserve)                             revaluation_reserve,

                fab.manufacturer_name                                                            unit_number,

                (CASE

                     WHEN (SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) > 0 THEN fab.asset_number

                     WHEN (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id) > 0 

                           THEN (SELECT asset_number FROM fa_additions_vl WHERE asset_id = fab.parent_asset_id)

                     WHEN ((SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) = 0 AND

                            (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id ) = 0 

                          ) THEN NULL

                     ELSE NULL

                 END)                                                                            parent_id,

                fab.serial_number                                                                vin,

                fab.attribute2                                                                   wm_ownership,

                fab.attribute4                                                                   goodwill_ownership,

                fab.attribute5                                                                   acquired_asset,

                (

                    SELECT

                        to_char(sysdate, 'dd')

                        || '-'

                        || upper(period_name)

                    FROM

                        gl_periods

                    WHERE

                            period_num = to_char(sysdate, 'mm')

                        AND period_set_name = 'xxxxxxxxx'

                        AND period_year = EXTRACT(YEAR FROM sysdate)

                )                                                                                run_date,

                ftn.dep29_pk1_value                                                              area,

                fb.book_type_code,

                ftv.transaction_type_code,

                (

                    SELECT

                        substr(period_name, 1, 3)

                        || '-'

                        || substr(period_name, 7, 8)

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                )                                                                                open_period,

                gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 

               ||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8      concat_expns_acc,

                ( ( fm.life_in_months ) - ( ( ( ( (

                    SELECT

                        fiscal_year

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) - EXTRACT(YEAR FROM fb.date_placed_in_service) ) * 12 ) + (

                    SELECT

                        period_num

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) ) - EXTRACT(MONTH FROM fb.date_placed_in_service) ) )                          rem_life_in_mon

            FROM

                fa_transaction_headers  ftv,

                fa_additions_vl         fab,

                fa_books                fb,

                fa_retirements          fr,

                fa_deprn_summary        fds,

                fa_deprn_detail         fdd,

                fa_distribution_history fdh,

                gl_code_combinations    gcc,

                hier_area               ftn,

                fa_deprn_periods        fdp,

                fa_methods              fm,

                fa_locations            fl,

                hr_locations_all        hl,

                fa_categories_b         fcb,

                fa_convention_types     fct,

                fa_book_controls        fbc,

                gl_sets_of_books        sob

            WHERE fab.asset_id NOT IN (SELECT DISTINCT fth1.asset_id

                                         FROM fa_transaction_headers fth1

                                        WHERE fth1.transaction_type_code = 'FULL RETIREMENT'

                                          AND fth1.date_effective = (SELECT MAX(date_effective) FROM fa_transaction_headers

                                                                      WHERE asset_id = fab.asset_id

                                                                        AND book_type_code = ftv.book_type_code)

                                          AND fth1.book_type_code = fb.book_type_code

                                      )

                AND fab.asset_category_id = fcb.category_id

                AND fab.asset_id = fb.asset_id

                AND fb.date_ineffective IS NULL

                AND fb.convention_type_id = fct.convention_type_id

                AND fab.asset_id = fr.asset_id (+)

                AND fr.retirement_id (+) = fb.retirement_id

                AND fbc.book_type_code = fb.book_type_code

                AND fbc.set_of_books_id = sob.set_of_books_id

                AND fds.deprn_source_code = 'DEPRN'

                AND fds.asset_id = fb.asset_id

                AND fds.book_type_code = fb.book_type_code

                AND fdp.period_counter = fds.period_counter

                AND fdp.book_type_code = fds.book_type_code

                AND fdd.asset_id = fb.asset_id

                AND fdd.asset_id = fds.asset_id

                AND fdd.book_type_code = fb.book_type_code

                AND fdd.period_counter = fds.period_counter

                AND fdd.period_counter = fdp.period_counter

                AND fdd.period_counter IN ( SELECT MAX(fdpi.period_counter) FROM fa_deprn_detail fdpi

                                             WHERE fdpi.book_type_code = fb.book_type_code

                                               AND fdpi.asset_id = fb.asset_id)

                AND fb.method_id = fm.method_id

                AND fdd.distribution_id = fdh.distribution_id

                AND fdh.asset_id = fab.asset_id

                AND fdh.book_type_code = fb.book_type_code

                AND fdh.code_combination_id = gcc.code_combination_id

                AND fdh.date_ineffective IS NULL

                AND ftn.dep1_pk1_value = gcc.segment1

                AND fbc.book_class = 'CORPORATE' -- p_book_class

                AND fb.date_effective IN (

                    SELECT

                        MAX(fb1.date_effective)

                    FROM

                        fa_books fb1

                    WHERE

                            fb1.asset_id = fab.asset_id

                        AND fb1.book_type_code = fb.book_type_code

                )

                AND fl.location_id = fdh.location_id

                AND fl.segment2 = hl.internal_location_code

                AND fdh.transaction_header_id_in = ftv.transaction_header_id

                AND fb.asset_id = ftv.asset_id

                AND fb.book_type_code = ftv.book_type_code

                 -- and ftv.event_id = xh.event_id

        )

    GROUP BY

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        conversion_id,

        description,

        category_major,

        category_minor,

        fa_lcode,

        address,

        postal_code,

        city,

        county,

        state,

        country,

        convention,

        life,

        in_service_date,

        quantity,

        dept,

        product,

        project,

        from_curr,

        revaluation_reserve,

        unit_number,

        parent_id,

        vin,

        wm_ownership,

        goodwill_ownership,

        acquired_asset,

        run_date,

        area,

        book_type_code,

        transaction_type_code,

        open_period,

        concat_expns_acc,

        rem_life_in_mon

    ORDER BY

        comp_code,

        facility_id,

        asset_id

)

SELECT

    *

FROM

    report

ORDER BY

    comp_code,

    facility_id,

    asset_id