top of page

Tags

Archive

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


120 views0 comments

Recent Posts

See All

When transactions are entered, Dynamic Insertion in Oracle Fusion General Ledger (GL) allows an organisation to automatically insert extra segments or values in the GL chart of accounts. This feature

Oracle Fusion Segment Security The General Ledger (GL) feature enables an organisation to restrict access to data in the GL depending on certain parts of the GL chart of accounts. A segment is a subse

Oracle Fusion General Ledger (GL) Cross-Validation Rules are used to assure the integrity and accuracy of data entered into the GL module. They are used to ensure that data submitted in the GL is cons

Other Posts you may Interested