Fixed Assets To Payables Query

 SELECT ma.book_type_code AS book,

       ma.posting_status AS queue_name,

       TRIM(TO_CHAR(ma.Payables_cost, '99999999.00')) AS cost,

       ma.invoice_number,

       b.line_number AS invoice_line_number,

       CASE WHEN b.quantity_invoiced IS NOT NULL THEN b.quantity_invoiced ELSE 1 END AS units,

       b.description,

       gcc.segment4 AS acct,

       TO_CHAR(ma.create_batch_date, 'MM-DD-YYYY') AS ma_create_date ,

       ma.invoice_id,

       ma.feeder_system_name AS source, 

       SYSDATE AS run_dt,

       'X' KEY

  FROM fa_mass_additions ma,

       ap_invoices_all a,

       ap_invoice_lines_all b,

       ap_invoice_distributions_all c,

       gl_code_combinations gcc

 WHERE a.invoice_id = b.invoice_id

   AND b.invoice_id = c.invoice_id

   AND b.line_number = c.invoice_line_number

   AND gcc.code_combination_id = c.DIST_CODE_COMBINATION_ID

   AND b.invoice_id = ma.invoice_id

   AND b.line_number = ma.invoice_line_number

   AND ma.posting_status = 'NEW'

   AND ma.fixed_assets_cost != 0

   AND ma.feeder_system_name = 'ORACLE PAYABLES'

   AND gcc.segment4 = '164100'

ORDER BY  ma.book_type_code DESC, ma.invoice_number, b.line_number

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

Active Assets Listing Report

 

WITH HIER_AREA AS 

(

   SELECT /*+ materialize */ distinct gsv.DEP29_PK1_VALUE,gsv.DEP1_PK1_VALUE,flv.description

   FROM GL_SEG_VAL_HIER_CF gsv,

   FND_TREE_NODE ftn, 

   fnd_flex_values_vl flv

   where gsv.tree_structure_code = 'GL_ACCT_FLEX' 

   and gsv.tree_code = 'xxxxxxxxxxxx' 

   and flv.FLEX_VALUE = gsv.dep29_pk1_value

        and flv.value_category = 'xxxxxxx'

   AND ((COALESCE(NULL,:P_TREE_V ) IS NULL) OR gsv.TREE_VERSION_ID IN (:P_TREE_V))

),

 

report as (

select /*+ materialize */ 

AREA_CODE_NAME

,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

, sum(COST) COST

,sum(deprn) deprn

,sum(accum_deprn) accum_deprn

,(sum(ytd_dpr) + sum(ytd_pdp)) ytd_dpr

,Revaluation_Reserve

,(sum(COST) - sum(accum_deprn))NBV

,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

,case when rem_life_in_mon <= 0 then 0 else rem_life_in_mon end rem_life_in_mon

,fuel_type

from(SELECT

ftn.dep29_pk1_value||'-'||ftn.description AREA_CODE_NAME, --- Added as per ERFA-910

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 ROWNUM<50

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 period_counter from fa_deprn_periods

where fiscal_year =  substr(sysdate,1,4)

and book_type_code = fb.book_type_Code

and period_close_date is null)

),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 

                                  AND FDP1.FISCAL_YEAR = Extract(year from sysdate)

                                  ))FDD1, XLA_AE_HEADERS XAH1, XLA_AE_LINES XLA1, GL_CODE_COMBINATIONS GCC1

      WHERE 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 1=1

         and 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 = 'xxxxxx'

and period_year = extract(year from sysdate)) RUN_DATE

,ftn.dep29_pk1_value area

,ftn.description area_name

,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

,fab.model_number fuel_type

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 1=1 

and fab.asset_id not in (select distinct fth1.asset_id from fa_transaction_headers fth1

where fth1.transaction_type_code ='xxxxxxxx'

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.distribution_id = fdd.distribution_id --new

                            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 ((COALESCE(NULL,:P_AREA ) IS NULL) OR ftn.dep29_pk1_value IN (:P_AREA)) 

AND ((COALESCE(NULL,:P_AREA_NAME) IS NULL) OR ftn.description IN (:P_AREA_NAME)) 

and fb.book_type_code = :P_BOOK_TYPE_CODE

AND ((COALESCE(NULL,:P_FACILITY ) IS NULL) OR gcc.segment2 IN (:P_FACILITY)) 

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

)

group by AREA_CODE_NAME,

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

,fuel_type

order by area,facility_id,category_major,category_minor)

select * from report

order by area,facility_id,category_major,category_minor,asset_id