Reports2017-11-18T12:27:27+00:00

FA Asset Book Details

Description
Categories: Enginatics, Financials
FA asset books with asset depreciation summary and financial transaction values.
Using parameters 'Show Calendar', 'Show Alternative Ledgers', 'Show Accounting Rules', 'Show Natural Accounts' shows the setup details of book controls.

with c_alt_ledgers as (
select
gl.ledger_id,
fmbc.book_type_code,
gl.name alternative_ledger,
decode(gl.ledger_category_code,'ALC','Reporting','SECONDARY','Secondary') alternative_ledger_type,
gl.currency_code alternative_ledger_currency,
fifsv.description alt_ledger_chart_of_accounts,
fmbc.gl_posting_allowed_flag alt_ledger_allow_gl_posting,
fmbc.enabled_flag alternative_ledger_enabled
from
fa_mc_book_controls fmbc,
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
fmbc.set_of_books_id=gl.ledger_id and
gl.ledger_category_code<>'PRIMARY' and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code='GL#' and
fifsv.application_id=101 and
'&show_alt_ledgers'='Y'
),
c_additions as(
select
fab.asset_id,
fab.asset_number,
(select fat.description from fa_additions_tl fat where fat.asset_id=fab.asset_id and fat.language=userenv('lang')) asset_description,
fab.tag_number,
fab.attribute_category_code asset_category_code,
fab.serial_number,
(select fakk.concatenated_segments from fa_asset_keywords_kfv fakk where fakk.code_combination_id=fab.asset_key_ccid) asset_key,
(select fab1.asset_number from fa_additions_b fab1 where fab1.asset_id=fab.parent_asset_id)parent_asset_number,
(select fat.description from fa_additions_tl fat where fat.asset_id=fab.parent_asset_id and fat.language=userenv('lang')) parent_description ,
fab.manufacturer_name,
fab.model_number,
fl.lease_number,
fl.description lease_description,
(select asu.vendor_name from ap_suppliers asu where fl.lessor_id=asu.vendor_id) lessor,
fab.in_use_flag,
fab.inventorial,
fab.asset_type,
fab.current_units,
fab.property_type_code,
fab.owned_leased,
fab.property_1245_1250_code,
fab.new_used,
fab.commitment,
fab.investment_law,
fb.book_type_code,
fb.cost
from
fa_additions_b fab,
fa_books fb,
fa_leases fl
where
fab.asset_id=fb.asset_id and
nvl(fb.disabled_flag,'N')='N' and
fb.transaction_header_id_out is null and
fab.lease_id=fl.lease_id(+) and
('&show_additions'='Y' or '&show_dprn'='Y' or '&show_trx'='Y')
),
c_dprn as(
select
fds.book_type_code,
fds.asset_id,
fds.period_counter,
fdp.period_name deprn_period_name,
fdp.period_close_date deprn_period_close_date,
fds.deprn_amount,
fds.ytd_deprn,
fds.deprn_reserve,
fds.deprn_source_code
from
(select x.* from (select max(fds.period_counter) over (partition by fds.asset_id,fds.book_type_code) max_period_counter, fds.* from fa_deprn_summary fds) x where x.period_counter=x.max_period_counter) fds,
fa_deprn_periods fdp
where
fds.book_type_code=fdp.book_type_code(+) and
fds.period_counter+1=fdp.period_counter(+) and
'&show_dprn'='Y'
),
c_fin_trx as (
select
fb.asset_id,
fb.book_type_code,
(select fl.meaning from fa_lookups fl where fl.lookup_type='FAXOLTRX' and fl.lookup_code=fth.transaction_type_code) trx_transaction_type,
fdp.period_name trx_period_entered,
fcp.period_name trx_period_effective,
fb.cost trx_current_cost,
fth.transaction_date_entered trx_date_entered,
fdp.fiscal_year trx_fiscal_year,
fth.date_effective trx_date_effective,
ftr.transaction_type trx_reference_type,
ftr.trx_reference_id,
fb.transaction_header_id_in
from
fa_books fb,
fa_books fb0,
fa_transaction_headers fth,
fa_trx_references ftr,
fa_deprn_periods fdp,
fa_book_controls fbc,
fa_calendar_periods fcp
where
fb.transaction_header_id_in=fb0.transaction_header_id_out(+) and
(fb.cost<>fb0.cost or fb0.cost is null) and
fb.transaction_header_id_in=fth.transaction_header_id and
fth.trx_reference_id=ftr.trx_reference_id(+) and
fth.book_type_code=fdp.book_type_code and
fth.date_effective between fdp.period_open_date and nvl(fdp.period_close_date,sysdate) and
fb.book_type_code=fbc.book_type_code and
fbc.deprn_calendar=fcp.calendar_type and
fth.transaction_date_entered between fcp.start_date and fcp.end_date and
fb.book_type_code=fdp.book_type_code and
'&show_trx'='Y'
)
select --main SQL starts here
fbc.book_type_code book,
fbc.book_type_name description,
fbc.book_class class,
fbc.distribution_source_book associated_corporate_book,
(select fds.period_name from fa_deprn_periods fds where fds.period_counter=fbc.initial_period_counter and fds.book_type_code=fbc.book_type_code) initial_period_counter,
(select fds.period_name from fa_deprn_periods fds where fds.period_counter=fbc.last_mass_copy_period_counter and fds.book_type_code=fbc.book_type_code) last_mass_copy_period_counter ,
(select fds.period_name from fa_deprn_periods fds where fds.period_counter=fbc.last_period_counter and fds.book_type_code=fbc.book_type_code) last_period_counter,
&calendar_columns
(select fift.id_flex_structure_name from fnd_id_flex_structures_tl fift where gl.chart_of_accounts_id=fift.id_flex_num and fift.application_id=101 and fift.id_flex_code='GL#' and fift.language=userenv('lang')) chart_of_accounts,
&alt_ledger_columns
&accounting_rules_columns
&natural_account_columns
&asset_number
&addition_columns
&dprn_columns
&trx_columns
haou.name operating_unit,
org_id
from
fa_book_controls fbc,
gl_ledgers gl,
c_alt_ledgers,
c_additions,
c_dprn,
c_fin_trx,
hr_all_organization_units haou
where
fbc.set_of_books_id=gl.ledger_id and
fbc.book_type_code=c_alt_ledgers.book_type_code(+) and
fbc.book_type_code=c_additions.book_type_code(+) and
c_additions.book_type_code=c_dprn.book_type_code(+) and
c_additions.asset_id=c_dprn.asset_id(+) and
c_additions.book_type_code=c_fin_trx.book_type_code(+) and
c_additions.asset_id=c_fin_trx.asset_id(+) and
fbc.org_id=haou.organization_id(+) and
2=2
order by
fbc.book_type_code
&order_columns

Parameter Name SQL text Validation
Organization
haout.name=:org_name
LOV
Show Fin Transactions
c_fin_trx.trx_transaction_type,
c_fin_trx.trx_period_entered,
c_fin_trx.trx_period_effective,
c_fin_trx.trx_current_cost,
c_fin_trx.trx_date_entered,
c_fin_trx.trx_fiscal_year,
c_fin_trx.trx_date_effective,
c_fin_trx.trx_reference_type,
Show Fin Transactions
,c_additions.asset_number, c_additions.asset_description, c_fin_trx.trx_date_entered
Show Depreciation Summary
,c_additions.asset_number, c_additions.asset_description
Show Depreciation Summary
c_dprn.deprn_period_name depreciation_period,
c_dprn.deprn_period_close_date depreciation_period_close_date,
c_dprn.deprn_amount depreciation_amount,
c_dprn.ytd_deprn ytd_depreciation,
c_dprn.deprn_reserve depreciation_reserv,
c_additions.cost-c_dprn.deprn_reserve book_value,
c_dprn.deprn_source_code depreciation_source_code,
Show Depreciation Summary
c_additions.asset_number,
c_additions.asset_description description,
Show Asset Details
,c_additions.asset_number, c_additions.asset_description
Show Asset Details
c_additions.asset_number,
c_additions.asset_description description,
Show Asset Details
c_additions.tag_number,
c_additions.asset_category_code category,
c_additions.serial_number,
c_additions.asset_key,
c_additions.asset_type,
c_additions.current_units units,
c_additions.parent_asset_number,
c_additions.parent_description parent_asset_description,
c_additions.manufacturer_name manufacturer,
c_additions.model_number model,
c_additions.lease_number,
c_additions.lease_description,
c_additions.lessor,
c_additions.in_use_flag in_use,
c_additions.inventorial,
c_additions.property_type_code property_type,
c_additions.owned_leased ownership,
c_additions.property_1245_1250_code property_class,
c_additions.new_used bought,
c_additions.commitment,
c_additions.investment_law,
c_additions.cost,
Show Alternative Ledgers
c_alt_ledgers.alternative_ledger,
c_alt_ledgers.alternative_ledger_type,
c_alt_ledgers.alternative_ledger_currency,
c_alt_ledgers.alt_ledger_chart_of_accounts,
c_alt_ledgers.alt_ledger_allow_gl_posting,
c_alt_ledgers.alternative_ledger_enabled,
Show Fin Transactions
Y
LOV Oracle
Show Depreciation Summary
Y
LOV Oracle
Show Asset Details
Y
LOV Oracle
Show Natural Accounts
--retirment accounts
fbc.proceeds_of_sale_gain_acct proceeds_of_sale_gain,
fbc.proceeds_of_sale_loss_acct proceeds_of_sale_loss,
fbc.proceeds_of_sale_clearing_acct proceeds_of_sale_clearing,
fbc.cost_of_removal_gain_acct cost_of_removal_gain,
fbc.cost_of_removal_loss_acct cost_of_removal_loss,
fbc.cost_of_removal_clearing_acct cost_of_removal_clearing,
fbc.nbv_retired_gain_acct net_book_value_retired_gain,
fbc.nbv_retired_loss_acct net_book_value_retired_loss,
fbc.reval_rsv_retired_gain_acct reval_reserve_retired_gain,
fbc.reval_rsv_retired_loss_acct reval_reserve_retired_loss,
--
fbc.deferred_deprn_reserve_acct deferred_depreciation_reserve,
fbc.deferred_deprn_expense_acct deferred_depreciation_expense,
fbc.deprn_adjustment_acct depreciation_adjustment,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id=fbc.flexbuilder_defaults_ccid) account_generator_defaults,
LOV Oracle
Show Accounting Rules
trunc(fbc.capital_gain_threshold/12) capital_gain_threshold_years,
mod(fbc.capital_gain_threshold,12) capital_gain_threshold_months,
fbc.amortize_flag allow_amortized_changes,
fbc.allow_mass_changes,
fbc.sorp_enabled_flag uk_local_authority_acc,
fbc.allow_cost_sign_change_flag allow_cost_sign_changes,
fbc.prevent_prior_period_txns_flag prevent_prev_period_amort_chng,
fbc.allow_unallocated_lines_flag allow_unallocated_line_types,
fbc.import_alloc_inv_lines_as_new disable_auto_merge_of_ap_lines,
--revaluations
fbc.allow_reval_flag allow_revaluations,
fbc.reval_deprn_reserve_flag revalue_accumulated_dprn,
fbc.reval_ytd_deprn_flag revalue_ytd_depreciation,
fbc.retire_reval_reserve_flag retire_revaluation_reserve,
fbc.amortize_reval_reserve_flag amortize_revaluation_reserve,
fbc.default_reval_fully_rsvd_flag revalue_fully_reserved_assets,
fbc.default_life_extension_factor life_extension_factor,
fbc.default_max_fully_rsvd_revals maximum_revaluations,
fbc.default_life_extension_factor life_extension_ceiling,
fbc.default_period_end_reval_flag include_curr_period_dprn,
--group dprn
fbc.allow_cip_member_flag allow_cip_member_in_grp_assets,
fbc.allow_cip_dep_group_flag allow_cip_dprn_in_grp_assets,
fbc.allow_member_tracking_flag allow_member_asset_track,
fbc.allow_interco_group_flag interco_member_asset_assign,
--other flags
fbc.intercompany_posting_flag create_intercomp_bal_entries,
fbc.allow_group_deprn_flag allow_group_depreciation,
fbc.allow_deprn_adjustments allow_reserve_adjustments,
fbc.allow_cost_ceiling allow_cost_ceilings,
fbc.itc_allowed_flag allow_invest_tax_credits,
fbc.allow_deprn_exp_ceiling allow_expense_ceilings,
fbc.allow_cip_assets_flag allow_cip_assets,
fbc.allow_backdated_transfers_flag allow_backdated_transfers,
fbc.allow_mass_copy,
fbc.copy_additions_flag copy_additions,
fbc.copy_adjustments_flag copy_adjustments,
fbc.copy_retirements_flag copy_retirements,
fbc.copy_amort_adaj_exp_flag copy_amort_add_and_adj_as_exp,
LOV Oracle
Show Alternative Ledgers
Y
LOV Oracle
Show Calendar
fbc.date_ineffective inactive_on,
fbc.allow_purge_flag allow_purge,
(select fdp.period_name from fa_deprn_periods fdp where fdp.period_counter=fbc.last_purge_period_counter and fdp.book_type_code=fbc.book_type_code) purged_through,
gl.short_name ledger,
gl.description ledger_description,
fbc.gl_posting_allowed_flag allow_gl_posting,
fbc.deprn_calendar depreciation_calendar,
fbc.fiscal_year_name,
fbc.prorate_calendar,
(select fdp.period_name from fa_deprn_periods fdp where fdp.period_close_date is null and fdp.book_type_code=fbc.book_type_code) current_period,
fbc.current_fiscal_year,
decode(fbc.deprn_allocation_code,'E','Evenly','D','By Days',fbc.deprn_allocation_code) divide_depreciation,
fbc.depr_first_year_ret_flag depreciate_if_retired_first_yr,
fbc.last_deprn_run_date last_depreciation_run_date,
fbc.deprn_status last_depreciation_run_status,
fbc.deprn_request_id last_depreciation_request,
LOV Oracle
Book
fbc.book_type_code=:book_type_code
LOV

Download
 

By continuing to use the site, you agree to the use of cookies. Accept