with
q_assets as
(
select
ad.asset_number,
ad2.description,
ad.tag_number,
ad.serial_number,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', :p_cat_flex_id, NULL, cat.category_id, 'ALL', 'Y', 'VALUE') asset_category,
cat.description category_desc,
(select fakk.concatenated_segments from fa_asset_keywords_kfv fakk where fakk.code_combination_id=ad.asset_key_ccid) asset_key,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', :p_cat_flex_id, NULL, cat.category_id, '1', 'Y', 'VALUE') asset_category_segment1,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', :p_cat_flex_id, NULL, cat.category_id, '2', 'Y', 'VALUE') asset_category_segment2,
&fa_dff_cols
ad.manufacturer_name,
ad.model_number,
lookups_pt.meaning property_type,
lookups_12.meaning property_class,
lookups_nu.meaning new_used,
lookups_iu.meaning in_use_flag,
lookups_ol.meaning owned_leased,
lookups_at.meaning asset_type,
(select max(fth.transaction_type_code) keep (dense_rank last order by fth.transaction_header_id) from fa_transaction_headers fth where fth.asset_id=ad.asset_id and fth.book_type_code=bk.book_type_code) asset_status,
p_ad.asset_number parent_asset_number,
p_ad2.description parent_asset_desc,
ad.current_units total_units,
ad.asset_id,
bk.book_type_code
from
fa_asset_history ah,
fa_books bk,
fa_categories cat,
fa_lookups lookups_at,
fa_lookups lookups_nu,
fa_lookups lookups_ol,
fa_lookups lookups_iu,
fa_lookups lookups_pt,
fa_lookups lookups_12,
fa_additions_tl ad2,
fa_additions_b ad,
fa_additions_tl p_ad2,
fa_additions_b p_ad
where
1=1 and
ad.asset_id = ad2.asset_id and
ad2.language = userenv('LANG') and
ah.asset_id = ad.asset_id and
ah.date_effective <= sysdate and
nvl(ah.date_ineffective, sysdate+1) > sysdate and
ah.category_id = cat.category_id and
bk.asset_id = ad.asset_id and
bk.book_type_code = :p_book and
bk.date_ineffective is null and
--
p_ad.asset_id (+) = ad.parent_asset_id and
p_ad2.asset_id (+) = p_ad.asset_id and
p_ad2.language (+) = userenv('LANG') and
--
lookups_at.lookup_code = ad.asset_type and
lookups_at.lookup_type = 'ASSET TYPE' and
lookups_nu.lookup_code = ad.new_used and
lookups_nu.lookup_type = 'NEWUSE' and
lookups_ol.lookup_code = ad.owned_leased and
lookups_ol.lookup_type = 'OWNLEASE' and
lookups_iu.lookup_code = ad.in_use_flag and
lookups_iu.lookup_type = 'YESNO' and
lookups_pt.lookup_code(+) = ad.property_type_code and
lookups_pt.lookup_type(+) = 'PROPERTY TYPE' and
lookups_12.lookup_code(+) = ad.property_1245_1250_code and
lookups_12.lookup_type(+) = '1245/1250 PROPERTY'
),
q_books as
(
select
books.asset_id,
books.book_type_code book,
bc.book_class,
trunc(books.date_placed_in_service) date_placed_in_service,
books.prorate_convention_code prorate_convention,
trunc(books.prorate_date) prorate_date,
(cat.number_per_fiscal_year - cap.period_num + 1) months_deprn_in_first_yr,
lu_df.meaning depreciate,
lu_dwa.meaning depreciate_when_placed_in_serv,
trunc(books.deprn_start_date) deprn_start_date,
books.deprn_method_code deprn_method,
to_char(trunc(books.life_in_months/12) + (mod(books.life_in_months,12)/100), '90D00') life_in_yr_mo,
(books.basic_rate * 100) basic_rate_pct,
(books.adjusted_rate * 100) adjusted_rate_pct,
books.production_capacity capacity,
decode(dp_ds.fiscal_year, bc.current_fiscal_year, ds.ytd_production, null) ytd_production,
ds.ltd_production ltd_production,
books.unit_of_measure unit_of_measure,
lu_ly.meaning depreciate_in_last_year,
pd2.period_name period_reserved,
pd1.period_name period_retired,
(itc.itc_amount_rate * 100) itc_rate_pct,
books.itc_amount itc_amount,
books.itc_basis itc_basis,
(itc.basis_reduction_rate * 100) basis_reduction_rate_pct,
(itc.basis_reduction_rate * books.original_cost) basis_reduction,
nvl(ds.reval_reserve,0) revaluation_reserve,
books.ceiling_name ceiling_name,
ceil.ceiling_type ceiling_type,
books.bonus_rule bonus_rule,
books.salvage_value salvage_value,
books.rate_adjustment_factor rate_adjustment_factor,
books.original_cost original_cost,
books.cost cost,
books.adjusted_recoverable_cost recoverable_cost,
books.adjusted_cost depreciable_basis,
(books.cost - ds.deprn_reserve) net_book_value,
ds.deprn_reserve deprn_reserve,
decode(dp_ds.fiscal_year, bc.current_fiscal_year, ds.ytd_deprn, 0) ytd_deprn
from
fa_books books,
fa_book_controls bc,
fa_deprn_periods pd1,
fa_deprn_periods pd2,
fa_ceiling_types ceil,
fa_deprn_summary ds,
fa_deprn_periods dp_ds,
fa_methods meth,
fa_itc_rates itc,
fa_convention_types cot,
fa_calendar_periods cap,
fa_calendar_types cat,
fa_lookups lu_df,
fa_lookups lu_ly,
fa_lookups lu_dwa
where
:p_incl_book = 'Y' and
books.book_type_code = bc.book_type_code and
books.date_ineffective is null and
bc.book_class in ('CORPORATE', 'TAX') and
pd1.book_type_code(+) = books.book_type_code and
pd2.book_type_code(+) = books.book_type_code and
pd1.period_counter(+) = nvl(books.period_counter_fully_retired,0) and
pd2.period_counter(+) = nvl(books.period_counter_fully_reserved,0) and
ceil.ceiling_name(+) = books.ceiling_name and
ds.asset_id = books.asset_id and
ds.book_type_code = books.book_type_code and
ds.period_counter =
(select
max(ds1.period_counter)
from
fa_deprn_summary ds1
where
ds1.asset_id=ds.asset_id and
ds1.book_type_code= ds.book_type_code) and
dp_ds.period_counter = decode(bc.initial_period_counter, ds.period_counter, ds.period_counter + 1, ds.period_counter) and
dp_ds.book_type_code = books.book_type_code and
meth.method_code = books.deprn_method_code and
nvl(meth.life_in_months, -1) = nvl(books.life_in_months, -1) and
itc.itc_amount_id(+) = books.itc_amount_id and
cot.prorate_convention_code = books.prorate_convention_code and
cap.calendar_type = bc.prorate_calendar and
books.prorate_date between cap.start_date and cap.end_date and
cap.calendar_type = cat.calendar_type and
lu_df.lookup_code = books.depreciate_flag and
lu_df.lookup_type = 'YESNO' and
lu_ly.lookup_code = meth.depreciate_lastyear_flag and
lu_ly.lookup_type = 'YESNO' and
lu_dwa.lookup_code = cot.depr_when_acquired_flag and
lu_dwa.lookup_type = 'YESNO'
),
d_distributions as
(
select
dh.asset_id asset_id,
bc.book_type_code,
dh.book_type_code book,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE') location,
emp.employee_number employee_number,
emp.full_name employee_name,
dh.units_assigned assigned_units
from
fa_locations loc,
gl_code_combinations cc,
per_all_people_f emp,
fa_distribution_history dh,
fa_book_controls bc
where
dh.code_combination_id = cc.code_combination_id and
dh.assigned_to is not null and
dh.assigned_to = emp.person_id and
dh.date_effective between emp.effective_start_date and emp.effective_end_date and
dh.location_id = loc.location_id and
dh.date_ineffective is null and
dh.book_type_code = bc.distribution_source_book
union all
select
dh.asset_id asset_id,
bc.book_type_code,
dh.book_type_code book,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE')location,
null,
null,
dh.units_assigned distribution_units
from
fa_locations loc,
gl_code_combinations cc,
fa_distribution_history dh,
fa_book_controls bc
where
dh.code_combination_id = cc.code_combination_id and
dh.assigned_to is null and
dh.location_id = loc.location_id and
dh.date_ineffective is null and
dh.book_type_code = bc.distribution_source_book
union all
select
dh.asset_id asset_id,
bc.book_type_code,
dh.book_type_code book,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, null, cc.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', :p_loc_flex_id, null, loc.location_id, 'ALL', 'Y', 'VALUE') location,
null,
null,
dh.units_assigned distribution_units
from
fa_locations loc,
gl_code_combinations cc,
fa_distribution_history dh,
fa_book_controls bc
where
dh.code_combination_id = cc.code_combination_id and
dh.assigned_to is not null and
dh.location_id = loc.location_id and
dh.date_ineffective is null and
dh.book_type_code = bc.distribution_source_book and
not exists
(select
employee_id
from
fa_employees
where
employee_id = dh.assigned_to)
),
q_invoices as
(
select
ai.asset_id asset_id,
ai.invoice_number,
ai.invoice_line_number||' - '||ai.ap_distribution_line_number invoice_line_number,
ai.description invoice_descripton,
po.segment1 vendor_number,
po.vendor_name,
ai.po_number,
(select aia.invoice_date from ap_invoices_all aia where aia.invoice_id=ai.invoice_id) invoice_date
from
fa_asset_invoices ai,
po_vendors po
where
ai.po_vendor_id = po.vendor_id(+) and
ai.date_ineffective is null
)
--
-- Main Query Starts Here
--
select
x.*
from
(
--
-- Q1 Asset Books
--
select /*+ push_pred(books) */
assets.asset_number,
assets.description,
assets.tag_number,
assets.serial_number,
assets.asset_category,
assets.category_desc,
assets.asset_key,
assets.asset_key||' - '||assets.description asset_key_description,
assets.asset_category_segment1,
assets.asset_category_segment2,
&fa_dff_cols_branch
assets.manufacturer_name,
assets.model_number,
assets.property_type,
assets.property_class,
assets.new_used,
assets.in_use_flag,
assets.owned_leased,
assets.asset_type,
assets.asset_status,
assets.parent_asset_number,
assets.parent_asset_desc,
assets.total_units,
--
'Book' record_type,
-- Books
books.book,
books.book_class,
books.date_placed_in_service,
books.prorate_convention,
books.prorate_date,
books.months_deprn_in_first_yr,
books.depreciate,
books.depreciate_when_placed_in_serv,
books.deprn_start_date,
books.deprn_method,
books.life_in_yr_mo,
books.basic_rate_pct,
books.adjusted_rate_pct,
books.capacity,
books.ytd_production,
books.ltd_production,
books.unit_of_measure,
books.depreciate_in_last_year,
books.period_reserved,
books.period_retired,
books.itc_rate_pct,
books.itc_amount,
books.itc_basis,
books.basis_reduction_rate_pct,
books.basis_reduction,
books.revaluation_reserve,
books.ceiling_name,
books.ceiling_type,
books.bonus_rule,
books.salvage_value,
books.rate_adjustment_factor,
books.original_cost,
books.cost,
books.recoverable_cost,
books.depreciable_basis,
books.net_book_value,
books.deprn_reserve,
books.ytd_deprn,
-- Distributions
null gl_account,
null location,
null employee_number,
null employee_name,
to_number(null) assigned_units,
-- Invoices
null invoice_number,
null invoice_date,
null invoice_line_number,
null invoice_descripton,
null vendor_number,
null vendor_name,
null po_number
from
q_assets assets,
q_books books
where
(:p_incl_book = 'Y' or (:p_incl_book = 'N' and :p_incl_dist = 'N' and :p_incl_inv = 'N')) and
assets.asset_id = books.asset_id (+)
union all
--
-- Q2 Asset Distributions
--
select /*+ push_pred(dist) */
assets.asset_number,
assets.description,
assets.tag_number,
assets.serial_number,
assets.asset_category,
assets.category_desc,
assets.asset_key,
assets.asset_key||' - '||assets.description asset_key_description,
assets.asset_category_segment1,
assets.asset_category_segment2,
&fa_dff_cols_branch
assets.manufacturer_name,
assets.model_number,
assets.property_type,
assets.property_class,
assets.new_used,
assets.in_use_flag,
assets.owned_leased,
assets.asset_type,
assets.asset_status,
assets.parent_asset_number,
assets.parent_asset_desc,
assets.total_units,
--
'Distribution' record_type,
-- Books
dist.book,
null book_class,
to_date(null) date_placed_in_service,
null prorate_convention,
to_date(null) prorate_date,
to_number(null) months_deprn_in_first_yr,
null depreciate,
null depreciate_when_placed_in_serv,
to_date(null) deprn_start_date,
null deprn_method,
null life_in_yr_mo,
to_number(null) basic_rate_pct,
to_number(null) adjusted_rate_pct,
to_number(null) capacity,
to_number(null) ytd_production,
to_number(null) ltd_production,
null unit_of_measure,
null depreciate_in_last_year,
null period_reserved,
null period_retired,
to_number(null) itc_rate_pct,
to_number(null) itc_amount,
to_number(null) itc_basis,
to_number(null) basis_reduction_rate_pct,
to_number(null) basis_reduction,
to_number(null) revaluation_reserve,
null ceiling_name,
null ceiling_type,
null bonus_rule,
to_number(null) salvage_value,
to_number(null) rate_adjustment_factor,
to_number(null) original_cost,
to_number(null) cost,
to_number(null) recoverable_cost,
to_number(null) depreciable_basis,
to_number(null) net_book_value,
to_number(null) deprn_reserve,
to_number(null) ytd_deprn,
-- Distributions
dist.gl_account,
dist.location,
dist.employee_number,
dist.employee_name,
dist.assigned_units,
-- Invoices
null invoice_number,
null invoice_date,
null invoice_line_number,
null invoice_descripton,
null vendor_number,
null vendor_name,
null po_number
from
q_assets assets,
d_distributions dist
where
:p_incl_dist = 'Y' and
assets.asset_id = dist.asset_id and
assets.book_type_code = dist.book_type_code
union all
--
-- Q3 Asset Invoices
--
select /*+ push_pred(inv) */
assets.asset_number,
assets.description,
assets.tag_number,
assets.serial_number,
assets.asset_category,
assets.category_desc,
assets.asset_key,
assets.asset_key||' - '||assets.description asset_key_description,
assets.asset_category_segment1,
assets.asset_category_segment2,
&fa_dff_cols_branch
assets.manufacturer_name,
assets.model_number,
assets.property_type,
assets.property_class,
assets.new_used,
assets.in_use_flag,
assets.owned_leased,
assets.asset_type,
assets.asset_status,
assets.parent_asset_number,
assets.parent_asset_desc,
assets.total_units,
--
'Invoice' record_type,
-- Books
null book,
null book_class,
to_date(null) date_placed_in_service,
null prorate_convention,
to_date(null) prorate_date,
to_number(null) months_deprn_in_first_yr,
null depreciate,
null depreciate_when_placed_in_serv,
to_date(null) deprn_start_date,
null deprn_method,
null life_in_yr_mo,
to_number(null) basic_rate_pct,
to_number(null) adjusted_rate_pct,
to_number(null) capacity,
to_number(null) ytd_production,
to_number(null) ltd_production,
null unit_of_measure,
null depreciate_in_last_year,
null period_reserved,
null period_retired,
to_number(null) itc_rate_pct,
to_number(null) itc_amount,
to_number(null) itc_basis,
to_number(null) basis_reduction_rate_pct,
to_number(null) basis_reduction,
to_number(null) revaluation_reserve,
null ceiling_name,
null ceiling_type,
null bonus_rule,
to_number(null) salvage_value,
to_number(null) rate_adjustment_factor,
to_number(null) original_cost,
to_number(null) cost,
to_number(null) recoverable_cost,
to_number(null) depreciable_basis,
to_number(null) net_book_value,
to_number(null) deprn_reserve,
to_number(null) ytd_deprn,
-- Distributions
null gl_account,
null location,
null employee_number,
null employee_name,
to_number(null) assigned_units,
-- Invoice
inv.invoice_number,
inv.invoice_date,
inv.invoice_line_number,
inv.invoice_descripton,
inv.vendor_number,
inv.vendor_name,
inv.po_number
from
q_assets assets,
q_invoices inv
where
:p_incl_inv = 'Y' and
assets.asset_id = inv.asset_id
) x
order by
x.asset_number,
decode(x.record_type,'Book',1,'Distribution',2,3),
x.book_class,
x.book,
x.gl_account,
x.location,
x.employee_name,
x.invoice_number,
x.invoice_line_number |