ORACLE TECH CENTER: 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