FA Depreciation Projection

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Ledger, Book, Company, Company Desc, Account, Account Desc, Department, Department Desc, Asset Number, Period ...
Based on Oracle's 'Depreciation Projection Report' FASPRJ

Uses custom DB package call XXEN_FASPRJ to launch Oracle standard Depreciation Projection concurrent FAPROJ. The data generation is explained in note:
How Does FA Depreciation Projections Handle Table FA_PROJ_INTERIM_XXX or FA_PROJ_INTERIM_REP ? (Doc ID 1607626.1)
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1607626.1 ... 
Based on Oracle's 'Depreciation Projection Report' FASPRJ

Uses custom DB package call XXEN_FASPRJ to launch Oracle standard Depreciation Projection concurrent FAPROJ. The data generation is explained in note:
How Does FA Depreciation Projections Handle Table FA_PROJ_INTERIM_XXX or FA_PROJ_INTERIM_REP ? (Doc ID 1607626.1)
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1607626.1
   more
select distinct
gl.name ledger,
fpiv.book_type_code book,
&segment_columns
&asset_number
fpiv.period_name period,
sum(fpiv.depreciation) over (partition by fpiv.request_id, fpiv.book_type_code, &segment_columns2 &asset_number fpiv.period_name, fpiv.fiscal_year) depreciation,
fpiv.fiscal_year,
fpiv.period_index,
&segment_columns3
fpiv.request_id
from
gl_ledgers gl,
fa_book_controls fbc,
fa_proj_interim_v fpiv,
fa_additions fa,
gl_code_combinations gcc
where
1=1 and
gl.ledger_id=fbc.set_of_books_id and
fbc.book_type_code=fpiv.book_type_code and
fpiv.asset_id=fa.asset_id and
fpiv.code_combination_id=gcc.code_combination_id
order by
fpiv.request_id,
gl.name,
fpiv.book_type_code,
&segment_columns2
&asset_number
fpiv.fiscal_year,
fpiv.period_index
Parameter Name SQL text Validation
Show Asset Number
fa.asset_number,
LOV
Show Cost Center
select
'gcc.'||lower(fifs.application_column_name)||' "'||fifst.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(gcc.'||lower(fifs.application_column_name)||', '''||fifs.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
:dummy=:dummy and
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
LOV
Run Depreciation Projection
fpiv.request_id in (select fcr.request_id from fnd_concurrent_requests fcr where fcr.description='FA Projections '||fnd_global.conc_request_id)
LOV
Currency
 
LOV
Chart of Accounts
select
'gcc.'||lower(fifs.application_column_name)||' "'||fifst.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(gcc.'||lower(fifs.application_column_name)||', '''||fifs.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' desc",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type in ('GL_BALANCING','GL_ACCOUNT') and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
LOV
Calendar
 
LOV
Starting Period
 
LOV
Number of Periods
 
Number
Book1
 
LOV
Book2
 
LOV
Book3
 
LOV
Book4
 
LOV
Previous Request Id
fpiv.request_id=:request_id
LOV
Chart of Accounts
select
'gcc.'||lower(fifs.application_column_name)||','
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type in ('GL_BALANCING','GL_ACCOUNT') and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
Chart of Accounts
select
'gcc.'||lower(fifst.application_column_name)||'||'' - '''||'||xxen_util.segment_description(gcc.'||lower(fifst.application_column_name)||', '''||fifst.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type in ('GL_BALANCING','GL_ACCOUNT') and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
order by
fifs.segment_num
Show Cost Center
select
'gcc.'||lower(fifs.application_column_name)||','
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
:dummy=:dummy and
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')
Show Cost Center
select
'gcc.'||lower(fifst.application_column_name)||'||'' - '''||'||xxen_util.segment_description(gcc.'||lower(fifst.application_column_name)||', '''||fifst.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifst.form_left_prompt||' with desc",' text
from
fnd_id_flex_segments fifs,
fnd_id_flex_segments_tl fifst
where
:dummy=:dummy and
(
fifs.application_id,
fifs.id_flex_code,
fifs.id_flex_num,
fifs.application_column_name
) in
(
select
fsav.application_id,
fsav.id_flex_code,
fsav.id_flex_num,
fsav.application_column_name
from
fnd_segment_attribute_values fsav
where
fsav.segment_attribute_type='FA_COST_CTR' and
fsav.attribute_value='Y'
) and
fifs.application_id=101 and
fifs.id_flex_code='GL#' and
fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) and
fifs.application_id=fifst.application_id and
fifs.id_flex_code=fifst.id_flex_code and
fifs.id_flex_num=fifst.id_flex_num and
fifs.application_column_name=fifst.application_column_name and
fifst.language=userenv('lang')