Fixed Assets Query in Oracle Fusion
Hi Friends, We are going to discuss about the Fixed Assets Query in Oracle Fusion. This fixed asset query helps to extract the fixed assets details in Oracle Fusion. We are sharing the complete SQL Query which helps to fetch all Fixed assets details in Oracle Fusion. This is one of the important SQL query in Fixed assets. We just need to pass the required parameters in this fixed asset sql query to run in oracle fusion to fetch the data. We can use this fixed asset query to develop the Custom BIP report of Fixed Asset register in Oracle Fusion. We are also sharing the important fixed assets tables related to this query. Please find below the complete detail about Fixed Assets Query in Oracle Fusion.
 |
| Fixed Assets Query in Oracle Fusion |
8 Important Table in Fixed Assets Query in Oracle Fusion.
1.fa_books
2.fa_methods
3.fa_additions_b
4.fa_additions_tl
5.fa_deprn_summary
6.fa_deprn_periods
7.fa_categories_b
8.fa_deprn_detai
Detail Level of Fixed Assets Query in Oracle Fusion
SELECT distinct fds.deprn_source_code,
fab.asset_number,
fb.book_type_code,
fcb.segment1 MAJOR_CATEGORY,
fcb.segment2 MINOR_Category,
fab.current_units,
fat.description,
fb.date_placed_in_service,
round((SELECT MAX(gdr.conversion_rate)
FROM gl_daily_rates gdr, gl_daily_conversion_types gdc
WHERE gdr.from_currency = 'USD'
AND gdr.to_currency = 'INR'
AND gdr.conversion_type = gdc.conversion_type
and gdc.user_conversion_type = 'Corporate'
AND TRUNC (gdr.conversion_date) = trunc(fb.date_placed_in_service)),5)
forex_rate,
fb.cost,
(fb.cost - fb.salvage_value) recoverable_cost,
fds.adjusted_cost depreciable_basis,
fds.deprn_amount,
(select INVOICE_NUMBER from fa_asset_invoices fai where fai.asset_id = fab.asset_id
and INVOICE_TRANSACTION_ID_IN = (select min(INVOICE_TRANSACTION_ID_IN) from fa_asset_invoices fai2
where fai2.asset_id = fai.asset_id) and rownum=1 ) invoice_number,
(select VENDOR_NAME from fa_asset_invoices fai where fai.asset_id = fab.asset_id
and INVOICE_TRANSACTION_ID_IN = (select min(INVOICE_TRANSACTION_ID_IN) from fa_asset_invoices fai2
where fai2.asset_id = fai.asset_id) and rownum=1) supplier_name,
(SELECT date_retired
FROM fa_retirements fr, fa_adjustments fa
WHERE fa.asset_id = fab.asset_id
AND fr.transaction_header_id_in = fa.transaction_header_id
AND fa.book_type_code = fr.book_type_code
AND fa.asset_id = fr.asset_id
AND source_type_code = 'RETIREMENT'
AND adjustment_type = 'COST'
AND fr.status <> 'DELETED'
AND fdp.book_type_code = fa.book_type_code
AND fa.period_counter_created = fdp.period_counter
and ADJUSTMENT_LINE_ID = (select max(ADJUSTMENT_LINE_ID) from fa_adjustments fa1 where
fa1.asset_id = fa.asset_id AND fa1.source_type_code = 'RETIREMENT'
AND fa1.adjustment_type = 'COST')) retirement_date,
fds.ytd_deprn,
fds.deprn_reserve,
(fb.cost - fds.deprn_reserve - fdd.impairment_reserve) net_book_value,
fab.asset_type,
fab.serial_number,
fab.tag_number,
(TO_CHAR(fb.date_placed_in_service, 'dd')
||'-'
||DECODE(TO_CHAR(fb.date_placed_in_service, 'mm'), '01', 'Jan', '02', 'Feb', '03', 'Mar', '04', 'Apr', '05', 'May', '06', 'Jun', '07', 'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct', '11', 'Nov', 'Dec')
|| '-'
|| TO_CHAR(fb.date_placed_in_service, 'yyyy')) DPIS,
fb.salvage_type,
fb.salvage_value,
fb.prorate_date,
fb.depreciate_flag,
fdp.period_name,
fdp.period_counter,
fdd.impairment_reserve,
fdd.ytd_impairment,
fm.LIFE_IN_MONTHS,
to_number( (case
when fb.date_placed_in_service < --'2018-04-01'
(decode(:P_CURR,'INR','20'||substr(:p_period_name,-5,2)||'-04-01',
(case when fdp.period_num<'7' then '20'||(substr(:p_period_name,-5,2)-1)||'-10-01'
else '20'||substr(:p_period_name,-5,2)||'-10-01' END))
)
then '0'
else to_char(nvl((select ((SELECT (NVL (SUM (fa.adjustment_amount), 0))
FROM fa_adjustments fa
WHERE fdp.book_type_code = fa.book_type_code
AND adjustment_type = 'COST'
AND source_type_code IN( 'ADDITION' ,'ADJUSTMENT')
AND FA.DEBIT_CREDIT_FLAG='DR'
AND fa.asset_id =fab.ASSET_ID)-
(SELECT (NVL (SUM (fa.adjustment_amount), 0))
FROM fa_adjustments fa
WHERE fdp.book_type_code = fa.book_type_code
AND adjustment_type = 'COST'
AND FA.DEBIT_CREDIT_FLAG='CR'
AND source_type_code IN( 'ADDITION' ,'ADJUSTMENT')
AND fa.asset_id =fab.ASSET_ID)) from dual),0))
end)) YTD_ADDITIONS,
nvl(( SELECT (NVL((ytd_deprn),0))
FROM fa_deprn_summary fds,
fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
-- AND fds.period_counter = (SELECT MAX (period_counter)
-- FROM fa_deprn_summary
-- WHERE asset_id = fds.asset_id)
AND fdp.book_type_code = :p_book_type_code
AND fds.asset_id =fab.ASSET_ID
AND fdp.period_counter = (select period_counter from fa_deprn_periods fdp1
where fdp1.period_name = :p_period_name
and fdp1.book_type_code=:p_book_type_code)
and fds.deprn_source_code = 'DEPRN'
AND FDP.fiscal_year= ('20'||substr(:p_period_name,-5,2))),0) YTD_DEP
FROM fa_books fb,
fa_methods fm,
fa_additions_b fab,
fa_additions_tl fat,
fa_deprn_summary fds,
fa_deprn_periods fdp,
fa_categories_b fcb,
fa_deprn_detail fdd
WHERE 1 = 1
AND fb.date_ineffective IS NULL
and fb.method_id = fm.method_id
AND fcb.category_id = fab.asset_category_id
AND fdp.period_counter = fds.period_counter
AND fdp.book_type_code = fds.book_type_code
AND fds.asset_id = fb.asset_id
AND fds.book_type_code = fb.book_type_code
AND fb.asset_id = fab.asset_id
AND fdp.period_name = :p_period_name
AND fb.book_type_code = :p_book_type_code
AND fat.asset_id = fab.asset_id
AND fat.language = 'US'
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 fab.ASSET_TYPE != 'GROUP'
 |
| Fixed Assets Query in Oracle Fusion |
0 comments:
Post a Comment