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