Oracle Cloud FA to GL BI Query
SELECT
DISTINCT ad.asset_number asset_number,
to_char(rsv.date_placed_in_service, 'MM/DD/RRRR') date_placed_in_service,
ad.attribute1 tax_ref,
ad.attribute2 pship_attrib,
ah.book_type_code book_type_code,
ad.attribute3 fas_source,
books.prorate_date prorate_date,
cat.segment1 major,
cat.segment2 minor,
rsv.method_code depreciation_method,
books.salvage_value,
cot.prorate_convention_code prorate_convention_code,
adtl.description,
cot.description prorate_convention_desc,
round(rsv.life / 12, 2) life_in_years,
rsv.life life_in_months,
rsv.rate adj_rate,
ds.bonus_rate bonus_rate,
SUM(decode(transaction_type, 'B', NULL, cost)) cost,
SUM(rsv.deprn_amount) deprn_amount,
SUM(ds.deprn_adjustment_amount) deprn_adjustment_amount,
SUM(rsv.ytd_deprn) ytd_deprn,
:p_period1 period_name,
SUM(rsv.deprn_reserve) deprn_reserve,
SUM(decode(transaction_type, 'B', NULL, nvl(percent, 0))) percent,
--decode(rsv.transaction_type, 'T','TRANSFER','R','RECLASS','U', 'UNITADJUSTMENT','REINSTATEMENT','TRANSFER OUT')
transaction_type t_type,
fa_fas400_xmlp_pkg.d_lifeformula(rsv.life, rsv.rate, ds.bonus_rate, '', rsv.use_life_in_periods_flag) d_life,
fa_fas400_xmlp_pkg.currency_code_p currency,
rsv.units units,
--Bug 23495842: RSV.PERIOD_END_NBV PERIOD_END_NBV,
SUM(rsv.period_end_nbv) nab,
(
SELECT DISTINCT
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
FROM
gl_code_combinations gcc
WHERE
gcc.code_combination_id = rsv.reserve_account_ccid
AND ROWNUM <= 1
) asset_res_acc,
(
SELECT DISTINCT
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
FROM
gl_code_combinations gcc,
xla_ae_lines xal,
xla_ae_headers xah,
fa_transaction_headers fth
WHERE
gcc.code_combination_id = xal.code_combination_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = fth.event_id
AND gcc.account_type = 'A'
AND xal.accounting_class_code = 'COST'
AND fth.asset_id = ad.asset_id
AND ROWNUM <= 1
) asset_acc,
(
SELECT DISTINCT
gcc.segment1
FROM
gl_code_combinations gcc
WHERE
gcc.code_combination_id = deprn_expense_account_ccid
AND ROWNUM <= 1
) company,
(
SELECT
nvl(fvvl.description, ' ')
FROM
fa_deprn_detail fdd1,
fa_distribution_history fdh,
gl_code_combinations glcc,
fnd_vs_values_vl fvvl,
fnd_vs_value_sets fvvs
WHERE
fdd1.asset_id = ad.asset_id
AND fdd1.book_type_code = :p_book
AND fdd1.distribution_id = fdh.distribution_id
AND fdh.code_combination_id = glcc.code_combination_id
AND fvvl.value_set_id = fvvs.value_set_id
AND fvvs.value_set_code = 'XXSAMDEPP'
AND fvvl.value = glcc.segment1
AND ROWNUM <= 1
) company_desc,
(
SELECT DISTINCT
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
FROM
gl_code_combinations gcc
WHERE
gcc.code_combination_id = deprn_expense_account_ccid
AND ROWNUM <= 1
) conct_dep_acc
FROM
fa_rsv_ledger_rep_gt rsv,
fa_additions_b ad,
fa_additions_tl adtl,
fa_asset_history ah, -- AH_Category
gl_code_combinations cc,
gl_code_combinations ccra,
fa_categories_b fcat,
fa_asset_keywords ak,
fa_deprn_summary ds,
fa_deprn_periods fdp,
fa_categories_vl cat,
fa_convention_types cot,
(
SELECT DISTINCT
to_char(date_placed_in_service, 'MM/DD/RRRR'),
to_char(prorate_date, 'MM/DD/RRRR') prorate_date,
asset_id,
book_type_code,
salvage_value,
convention_type_id,
date_ineffective,
date_effective
FROM
fa_books
) books
WHERE
rsv.asset_id = ad.asset_id
AND ah.category_id = cat.category_id
AND fdp.book_type_code = ah.book_type_code
AND fdp.period_name = :p_period1
AND books.asset_id = ad.asset_id
AND books.book_type_code = ah.book_type_code
AND rsv.deprn_expense_account_ccid = cc.code_combination_id
AND rsv.reserve_account_ccid = ccra.code_combination_id
AND ds.period_counter (+) = rsv.period_counter
AND ds.book_type_code (+) = :p_book
AND ds.asset_id (+) = rsv.asset_id
AND cot.convention_type_id = books.convention_type_id
AND ah.asset_id = ad.asset_id
AND ah.book_type_code = :p_book
AND nvl(fdp.period_close_date, sysdate) BETWEEN ah.date_effective AND nvl(ah.date_ineffective, sysdate)
AND nvl(fdp.period_close_date, sysdate) BETWEEN books.date_effective AND nvl(books.date_ineffective, sysdate)
AND fcat.category_id = ah.category_id
-- AH_Category: END
AND ad.asset_id = adtl.asset_id
AND adtl.language = userenv('LANG')
AND ad.asset_key_ccid = ak.code_combination_id (+)
GROUP BY
ad.asset_number,
adtl.description,
date_placed_in_service,
method_code,
rsv.life,
ad.asset_id,
adtl.description,
rsv.rate,
ah.book_type_code,
cat.segment1,
cat.segment2,
round(rsv.life / 12, 2),
ds.bonus_rate,
transaction_type,
rsv.reserve_account_ccid,
rsv.deprn_expense_account_ccid,
rsv.units,
fa_fas400_xmlp_pkg.currency_code_p,
--Bug 23495842: PERIOD_END_NBV,
fa_fas400_xmlp_pkg.d_lifeformula(rsv.life, rsv.rate, ds.bonus_rate, '', rsv.use_life_in_periods_flag),
ad.attribute1,
ad.attribute2,
ad.attribute3,
books.prorate_date,
books.salvage_value,
cot.prorate_convention_code,
cot.description
ORDER BY
1,
2,
3,
4
