WITH hier_area AS (SELECT /*+ materialize */ DISTINCT dep29_pk1_value, dep1_pk1_value
FROM gl_seg_val_hier_cf WHERE tree_structure_code = 'GL_ACCT_FLEX'
AND tree_code = 'xxxxxxxxx'
AND tree_version_id = :P_TREE_V
),
report AS (
SELECT
open_period,
comp_code,
facility_id,
facility_desc,
asset_id,
description,
conversion_id,
parent_id,
unit_number,
vin,
category_major,
category_minor,
quantity,
SUM(cost) cost,
SUM(accum_deprn) accum_deprn,
( SUM(cost) - SUM(accum_deprn) ) nbv,
SUM(deprn) deprn,
( SUM(ytd_dpr) + SUM(ytd_pdp) ) ytd_dpr,
revaluation_reserve,
in_service_date,
life,
CASE
WHEN rem_life_in_mon <= 0 THEN 0
ELSE rem_life_in_mon
END rem_life_in_mon,
convention,
dept,
product,
project,
from_curr,
wm_ownership,
acquired_asset,
fa_lcode,
address,
city,
county,
state,
country,
postal_code,
concat_expns_acc,
goodwill_ownership,
run_date,
area,
book_type_code,
'X' AS transaction_type_code
FROM
(
SELECT /*+ materialize */
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 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 MAX(fdpi.period_counter)
FROM fa_deprn_detail fdpi
WHERE fdpi.book_type_code = fb.book_type_code
AND fdpi.asset_id = fab.asset_id
AND fdpi.distribution_id = fdd.distribution_id)
),
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 -- fdd1.book_type_code commented by amruta
AND fdp1.fiscal_year = EXTRACT(YEAR FROM sysdate) )
) fdd1,
xla_ae_headers xah1,
xla_ae_lines xla1,
gl_code_combinations gcc1
WHERE 1=1
and 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 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 = 'xxxxxxxxx'
AND period_year = EXTRACT(YEAR FROM sysdate)
) run_date,
ftn.dep29_pk1_value area,
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
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 fab.asset_id NOT IN (SELECT DISTINCT fth1.asset_id
FROM fa_transaction_headers fth1
WHERE fth1.transaction_type_code = 'FULL RETIREMENT'
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.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 fbc.book_class = 'CORPORATE' -- p_book_class
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
-- and ftv.event_id = xh.event_id
)
GROUP BY
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,
revaluation_reserve,
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,
rem_life_in_mon
ORDER BY
comp_code,
facility_id,
asset_id
)
SELECT
*
FROM
report
ORDER BY
comp_code,
facility_id,
asset_id
No comments:
Post a Comment