FA Depreciation Projection

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
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(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(gcc.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
:dummy=:dummy and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.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
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.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)
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
Ledger
select
'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",'||chr(10)||
'xxen_util.segment_description(gcc.'||lower(fifsv.application_column_name)||', '''||fifsv.application_column_name||''', gcc.chart_of_accounts_id)'||' "'||fifsv.form_left_prompt||' desc",' text
from
fnd_id_flex_segments_vl fifsv
where
(
fifsv.application_id,
fifsv.id_flex_code,
fifsv.id_flex_num,
fifsv.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
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.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)
order by
fifsv.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
Show Cost Center
select
'gcc.'||lower(fifs.application_column_name)||',' text
from
fnd_id_flex_segments_vl fifsv
where
:dummy=:dummy and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.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
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.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)
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_vl fifsv
where
:dummy=:dummy and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.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
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.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)
Ledger
select
'gcc.'||lower(fifs.application_column_name)||',' text
from
fnd_id_flex_segments_vl fifsv
where
(
fifsv.application_id,
fifsv.id_flex_code,
fifsv.id_flex_num,
fifsv.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
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.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)
order by
fifsv.segment_num
Ledger
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_vl fifsv
where
(
fifsv.application_id,
fifsv.id_flex_code,
fifsv.id_flex_num,
fifsv.application_column_name
) in
(