FA Depreciation Query
dhcc.segment2 business_unit,
dhcc.segment3 account,
dhcc.segment4 dept,
adt.asset_number,
adt.tag_number,
LTRIM (RTRIM (cat.segment1))
|| '-'
|| LTRIM (RTRIM (cat.segment2))
|| '-'
|| LTRIM (RTRIM (cat.segment3))
category,
bks.date_placed_in_service acquistion_date,
bks.original_cost,
adt.description,
dh.location_id,
adt.context subject_to_property_tax,
adt.attribute1 property_tax_code,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned
/ ah.units),
2)
COST,
ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
2)
deprn,
ROUND (SUM (NVL (dn.deprn_reserve, 0) * dh.units_assigned / ah.units),
2)
deprn_reserve,
ROUND (SUM (NVL (dn.ytd_deprn, 0) * dh.units_assigned / ah.units), 2)
YTD_DEP,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired,
'', (bks.cost - dn.deprn_reserve),
0)
* dh.units_assigned
/ ah.units),
2)
c_nbv
FROM fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp
WHERE fp.book_type_code = 'PIUDCL_NAG_FAB' AND dn.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.period_counter =
(SELECT dp.period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = 'PIUDCL_NAG_FAB'
AND dp.period_counter =
(SELECT MAX (dpz.period_counter)
FROM fa_deprn_summary dsz,
fa_deprn_periods dpz
WHERE dpz.book_type_code = 'PIUDCL_NAG_FAB'
AND dpz.period_counter <=
fp.period_counter
AND dsz.book_type_code = 'PIUDCL_NAG_FAB'
AND dsz.period_counter =
dpz.period_counter
AND dsz.asset_id = dn.asset_id))
AND bks.book_type_code = 'PIUDCL_NAG_FAB'
AND bks.asset_id = dn.asset_id
AND NVL (bks.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND bks.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND NVL (bks.period_counter_fully_retired, fp.period_counter) IN
(SELECT dpy.period_counter
FROM fa_deprn_periods dpy
WHERE dpy.book_type_code = 'PIUDCL_NAG_FAB'
AND dpy.fiscal_year = fp.fiscal_year)
AND adt.asset_id = dn.asset_id
AND adt.ASSET_CATEGORY_ID = cat.category_id
AND adt.asset_id = dh.asset_id
AND dh.book_type_code = 'PIUDCL_NAG_FAB'
AND NVL (dh.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dh.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dhcc.code_combination_id(+) = dh.code_combination_id
AND ah.asset_id = adt.asset_id
AND NVL (ah.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND ah.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
GROUP BY fp.period_name,
dhcc.segment2,
dhcc.segment4,
dhcc.segment3,
adt.asset_number,
adt.tag_number,
LTRIM (RTRIM (cat.segment1))
|| '-'
|| LTRIM (RTRIM (cat.segment2))
|| '-'
|| LTRIM (RTRIM (cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost,
dh.location_id,
adt.context,
adt.attribute1;
No comments:
Post a Comment