Oracle FA Cloud BI Query
SELECT * FROM
(SELECT DISTINCT
fad.asset_number,
fat.description,
TO_CHAR (fb.date_placed_in_service, 'MM/DD/YYYY')
date_placed_in_service,
NVL (fb.cost, '') cost,
NVL (
(SELECT SUM(fdd1.deprn_amount)
FROM fa_deprn_detail fdd1, fa_deprn_periods fdp1
WHERE fdd1.asset_id = fb.asset_id
AND fdd1.asset_id = fad.asset_id
AND fdd1.book_type_code = fdp1.book_type_code
AND fdd1.period_counter = fdp1.period_counter
AND fdp1.period_name = 'Apr-19'
AND fdd1.book_type_code =
NVL ('US 1FINS', fdd1.book_type_code)
AND fdp1.fiscal_year =
(SELECT DISTINCT fiscal_year
FROM fa_deprn_periods fdpp
WHERE fdpp.period_name = 'Apr-19')),
0
)
deprn_amount,
NVL (
(SELECT SUM(fdd1.deprn_adjustment_amount)
FROM fa_deprn_detail fdd1, fa_deprn_periods fdp1
WHERE fdd1.asset_id = fb.asset_id
AND fdd1.asset_id = fad.asset_id
AND fdd1.book_type_code = fdp1.book_type_code
AND fdd1.period_counter = fdp1.period_counter
AND fdp1.period_name = 'Apr-19'
AND fdd1.book_type_code =
NVL ('US FIN', fdd1.book_type_code)
AND fdp1.fiscal_year =
(SELECT DISTINCT fiscal_year
FROM fa_deprn_periods fdpp
WHERE fdpp.period_name = 'Apr-19')),
0
)
deprn_adjustment_amount,
NVL (
(SELECT SUM(fdd1.ytd_deprn)
FROM fa_deprn_detail fdd1, fa_deprn_periods fdp1
WHERE fdd1.asset_id = fb.asset_id
AND fdd1.asset_id = fad.asset_id
AND fdd1.book_type_code = fdp1.book_type_code
AND fdd1.period_counter = fdp1.period_counter
AND fdp1.period_name = 'Apr-19'
--AND FDPL.PERIOD_NUMBER <= (SELECT PERIOD_NUMBER FROM FDPL WHERE PERIOD_NAME = :P_PERIOD_NAME
-- )
AND fdd1.book_type_code =
NVL ('US 1FINS', fdd1.book_type_code)
AND fdp1.fiscal_year =
(SELECT DISTINCT fiscal_year
FROM fa_deprn_periods fdpp
WHERE fdpp.period_name = 'Feb-19')),
0
)
ytd_deprn,
NVL (
(SELECT SUM(fdd1.deprn_reserve)
FROM fa_deprn_detail fdd1, fa_deprn_periods fdp1
WHERE fdd1.asset_id = fb.asset_id
AND fdd1.asset_id = fad.asset_id
AND fdd1.book_type_code = fdp1.book_type_code
AND fdd1.period_counter = fdp1.period_counter
AND fdp1.period_name = 'Apr-19'
AND fdd1.book_type_code =
NVL ('US 1FINS', fdd1.book_type_code)
AND fdp1.fiscal_year =
(SELECT DISTINCT fiscal_year
FROM fa_deprn_periods fdpp
WHERE fdpp.period_name = 'Apr-19')),
0
)
deprn_reserve,
(SELECT distinct glcc.segment1
FROM fa_deprn_detail fdd1,
fa_distribution_history fdh,
gl_code_combinations glcc
WHERE fdd1.asset_id = fb.asset_id
AND fdd1.asset_id = fad.asset_id
AND fdd1.book_type_code =
NVL ('US 1FINS', fdd1.book_type_code)
AND fdd1.distribution_id = fdh.distribution_id
AND fdh.code_combination_id = glcc.code_combination_id
AND ROWNUM<=1 )
company,
:p_period_name period_name,
ROUND (fam.life_in_months / 12, 2) life_in_years,
fam.method_code depreciation_method,
fam.name depreciation_name,
fb.book_type_code,
fct.prorate_convention_code ,
fct.description prorate_convention_desc,
TO_CHAR (fb.prorate_date, 'MM/DD/YYYY') prorate_date,
fad.attribute1 tax_ref,
fad.attribute2 pship_attrib,
fad.attribute3 fas_source
FROM fa_additions_b fad,
fa_additions_tl fat,
fa_books fb,
fa_convention_types fct,
fa_methods fam
WHERE fad.asset_id = fb.asset_id
AND fad.asset_id = fat.asset_id
AND fct.convention_type_id = fb.convention_type_id
AND fb.method_id = fam.method_id
--and fat.language = 'us'
AND fb.date_ineffective IS NULL
AND fb.book_type_code = NVL(:p_book_name,fb.book_type_code)
)
ORDER BY asset_number
