top of page

Tags

Archive

Misc Oracle Fusion Asset Queries which will help in EBS to Fusion Conversion/Reporting/Debugging

Anyone new to the oracle fusion Assets can take advantage of these queries to find the joins and key columns


Query1 :

SELECT

AD.ASSET_NUMBER || ' - ' || ADT.DESCRIPTION ASSET_NUMBER,

ADT.DESCRIPTION || ' [' || AD.ASSET_NUMBER || ']' ASSET_NUMBER,

TH.TRANSACTION_HEADER_ID TRANSNUM,

DECODE(TH.TRANSACTION_HEADER_ID,

DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT, 0) TO_FROM,

/* &ACCT_FLEX_ACCT_SEG GL_ACCOUNT,

&ACCT_FLEX_BAL_SEG COMP_CODE,

&ACCT_FLEX_COST_SEG COST_CENTER,

&LOC_FLEX_ALL_SEG LOCATION,*/

&D_GL_ACCOUNT D_GL_ACCOUNT1,

&D_COMP_CODE D_COMP_CODE1,

&D_COST_CENTER D_COST_CENTER1,

&D_LOCATION D_LOCATION1,

to_char(TH.TRANSACTION_DATE_ENTERED,'YYYY-MM-DD') START_DATE,

dh.assigned_to assigned_to,

pn.full_name EMPLOYEE,

ascc.code_combination_id ccid,

SUM(CADJ.ADJUSTMENT_AMOUNT*

DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)) COST,

0 DEPRN_RESERVE,

SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS,

FA_FAS430_XMLP_PKG.D_AS_COSTFormula D_RP_DATA_FOUND

,&D_COMP_CODE_DESC D_COMP_CODE_DESC

,&D_ACCOUNT_DESC D_ACCOUNT_DESC

,&D_COST_CTR_DESC D_COST_CTR_DESC,

DECODE(AK.CODE_COMBINATION_ID,NULL,NULL,&d_asset_key_segs) ASSET_KEY,

TH.LAST_UPDATE_DATE LAST_UPDATE_DATE,

TH.LAST_UPDATED_BY LAST_UPDATED_BY,

TH.CREATION_DATE CREATION_DATE,

TH.CREATED_BY CREATED_BY,

TH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN

FROM

FA_LOCATIONS LOC,

FA_ADDITIONS_B AD,

FA_ADDITIONS_TL ADT,

GL_CODE_COMBINATIONS ASCC,

PER_PERSON_NAMES_F_V PN,

FA_DISTRIBUTION_HISTORY DH,

FA_TRANSACTION_HEADERS TH,

FA_ASSET_KEYWORDS AK,

&LP_FA_ADJUSTMENTS CADJ

WHERE

TH.BOOK_TYPE_CODE = :P_BOOK AND

TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND

TH.DATE_EFFECTIVE >= :PERIOD1_POD AND

TH.DATE_EFFECTIVE <= :PERIOD1_PCD AND

nvl(TH.MASS_REFERENCE_ID, 0) =

NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0)) AND

(:P_BATCH_NAME IS NULL

OR (:P_BATCH_NAME IS NOT NULL AND TH.BATCH_NAME = :P_BATCH_NAME)

)

AND

(TH.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_IN OR

TH.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_OUT)

AND

AD.ASSET_ID = TH.ASSET_ID

AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+)

AND

AD.ASSET_ID = ADT.ASSET_ID AND

ADT.LANGUAGE = userenv('LANG')

AND

DH.BOOK_TYPE_CODE = :P_BOOK

AND

LOC.LOCATION_ID = DH.LOCATION_ID

AND

ASCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID

AND

PN.PERSON_ID(+) = DH.ASSIGNED_TO -- fusion

AND

trunc(sysdate) between pn.effective_start_date(+) and -- fusion

pn.effective_end_date(+) -- fusion

AND

CADJ.BOOK_TYPE_CODE = :P_BOOK AND

CADJ.ASSET_ID = TH.ASSET_ID AND

CADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND

CADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND

CADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND

CADJ.ADJUSTMENT_TYPE in ('COST','CIP COST')

and &D_COMP_CODE_SEC != 'Y'

and &D_ACCOUNT_SEC != 'Y'

and &D_COST_CTR_SEC != 'Y'


GROUP BY

TH.TRANSACTION_HEADER_ID,

DECODE(TH.TRANSACTION_HEADER_ID,

DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT, 0) ,

DH.DISTRIBUTION_ID,

&D_GL_ACCOUNT,

&D_COMP_CODE,

&D_COST_CENTER,

&D_LOCATION,

--End of DT Fix Changes

AD.ASSET_NUMBER,

ADT.DESCRIPTION,

to_char(TH.TRANSACTION_DATE_ENTERED,'YYYY-MM-DD'),

DH.ASSIGNED_TO,

PN.FULL_NAME,

ascc.code_combination_id,

&D_COMP_CODE_DESC,

&D_ACCOUNT_DESC ,

&D_COST_CTR_DESC,

DECODE(AK.CODE_COMBINATION_ID,NULL,NULL,&d_asset_key_segs),

TH.LAST_UPDATE_DATE,

TH.LAST_UPDATED_BY,

TH.CREATION_DATE,

TH.CREATED_BY,

TH.LAST_UPDATE_LOGIN

UNION

SELECT

--- AD.ASSET_NUMBER || ' - ' || ADT.DESCRIPTION ASSET_NUMBER,

ADT.DESCRIPTION || ' [' || AD.ASSET_NUMBER || ']' ASSET_NUMBER,

TH.TRANSACTION_HEADER_ID TRANSNUM,

DECODE(TH.TRANSACTION_HEADER_ID,

DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT, 0) TO_FROM,

/* &ACCT_FLEX_ACCT_SEG GL_ACCOUNT,

&ACCT_FLEX_BAL_SEG COMP_CODE,

&ACCT_FLEX_COST_SEG COST_CENTER,

&LOC_FLEX_ALL_SEG LOCATION,*/

&D_GL_ACCOUNT D_GL_ACCOUNT1,

&D_COMP_CODE D_COMP_CODE1,

&D_COST_CENTER D_COST_CENTER1,

&D_LOCATION D_LOCATION1,

to_char(TH.TRANSACTION_DATE_ENTERED,'YYYY-MM-DD') START_DATE,

dh.assigned_to assigned_to,

pn.full_name EMPLOYEE,

ascc.code_combination_id ccid,

0 COST,

SUM(NVL(RADJ.ADJUSTMENT_AMOUNT,0) *

DECODE(NVL(RADJ.DEBIT_CREDIT_FLAG,'CR'),'CR',1,'DR',-1)) DEPRN_RESERVE,

SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS,

FA_FAS430_XMLP_PKG.D_AS_COSTFormula D_RP_DATA_FOUND

,&D_COMP_CODE_DESC D_COMP_CODE_DESC

,&D_ACCOUNT_DESC D_ACCOUNT_DESC

,&D_COST_CTR_DESC D_COST_CTR_DESC,

DECODE(AK.CODE_COMBINATION_ID,NULL,NULL,&d_asset_key_segs),

TH.LAST_UPDATE_DATE LAST_UPDATE_DATE,

TH.LAST_UPDATED_BY LAST_UPDATED_BY,

TH.CREATION_DATE CREATION_DATE,

TH.CREATED_BY CREATED_BY,

TH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN

FROM

FA_LOCATIONS LOC,

FA_ADDITIONS_B AD,

FA_ADDITIONS_TL ADT,

GL_CODE_COMBINATIONS ASCC,

PER_PERSON_NAMES_F_V PN,

FA_DISTRIBUTION_HISTORY DH,

FA_TRANSACTION_HEADERS TH,

FA_ASSET_KEYWORDS AK,

FA_ADJUSTMENTS RADJ

WHERE

TH.BOOK_TYPE_CODE = :P_BOOK AND

TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND

TH.DATE_EFFECTIVE >= :PERIOD1_POD AND

TH.DATE_EFFECTIVE <= :PERIOD1_PCD AND

nvl(TH.MASS_REFERENCE_ID, 0) =

NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0)) AND

(:P_BATCH_NAME IS NULL

OR (:P_BATCH_NAME IS NOT NULL AND TH.BATCH_NAME = :P_BATCH_NAME)

)

AND

(TH.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_IN OR

TH.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_OUT)

AND

AD.ASSET_ID = TH.ASSET_ID

AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+)

AND

AD.ASSET_ID = ADT.ASSET_ID AND

ADT.LANGUAGE = userenv('LANG')

AND

DH.BOOK_TYPE_CODE = :P_BOOK

AND

PN.PERSON_ID(+) = DH.ASSIGNED_TO -- fusion

AND

trunc(sysdate) between pn.effective_start_date(+) and -- fusion

pn.effective_end_date(+) -- fusion

AND

LOC.LOCATION_ID = DH.LOCATION_ID

AND

ASCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID

AND

RADJ.BOOK_TYPE_CODE = :P_BOOK AND

RADJ.ASSET_ID = TH.ASSET_ID AND

RADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND

RADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND

RADJ.ADJUSTMENT_TYPE = 'RESERVE' AND

RADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID

and &D_COMP_CODE_SEC != 'Y'

and &D_ACCOUNT_SEC != 'Y'

and &D_COST_CTR_SEC != 'Y'


GROUP BY

TH.TRANSACTION_HEADER_ID,

DECODE(TH.TRANSACTION_HEADER_ID,

DH.TRANSACTION_HEADER_ID_IN,1,

DH.TRANSACTION_HEADER_ID_OUT, 0) ,

DH.DISTRIBUTION_ID,

&D_GL_ACCOUNT,

&D_COMP_CODE,

&D_COST_CENTER,

&D_LOCATION,

AD.ASSET_NUMBER,

ADT.DESCRIPTION,

to_char(TH.TRANSACTION_DATE_ENTERED,'YYYY-MM-DD'),

DH.ASSIGNED_TO,

PN.FULL_NAME,

ascc.code_combination_id,

&D_COMP_CODE_DESC,

&D_ACCOUNT_DESC ,

&D_COST_CTR_DESC,

TH.LAST_UPDATE_DATE,

TH.LAST_UPDATED_BY,

TH.CREATION_DATE,

TH.CREATED_BY,

TH.LAST_UPDATE_LOGIN,

DECODE(AK.CODE_COMBINATION_ID,NULL,NULL,&d_asset_key_segs)

ORDER BY 1,2,3,4,5,6,7,8,13, 9 , 10, 12, 11



Query2:


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,

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


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,

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, 5

1,

2,

3,

4

Query 3:



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 ('USSAMDEPP', 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 ('USAMDEPP', 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 ('USSAMDEPP', 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 ('USSAMDEPP', 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 ('USSAMDEPP', 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








234 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