SELECT fadd.asset_number asset_no
,fadd.description asset_desc
,fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_maj_seg', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'BASED_CATEGORY', 'N', 'VALUE') major_category
,fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_min_seg', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'MINOR_CATEGORY', 'N', 'VALUE') minor_category
,to_char(fa_tofes_yud_alef_pkg.purchase_date(fadd.asset_id),'DD-MON-YYYY') purchase_date
,to_char(fb.date_placed_in_service,'DD-MON-YYYY') date_placed_in_service
,fb.original_cost org_asset_cost
,NVL(fa_tofes_yud_alef_pkg.ytd_trans_amt(fadd.asset_id),0) ytd_trans_amounts
,ROUND(NVL((12/fb.life_in_months)*100,fb.basic_rate*100),2) depr_off_rate
,((ROUND(NVL((12/fb.life_in_months)*100,fb.basic_rate*100),2)/12)) * fa_tofes_yud_alef_pkg.deprn_claim_number(fadd.asset_id)
depr_claim_rate
,nvl(fa_tofes_yud_alef_pkg.ytd_deprn_amt(fadd.asset_id),0) ytd_depr_amount
,NVL(fa_tofes_yud_alef_pkg.accm_deprn_pr_amt(fadd.asset_id),0) accum_depr_prior_yr
FROM FA_ADDITIONS fadd
,&LP_FA_BOOKS fb
,FA_CATEGORIES fc
,(SELECT MAX(fth1.transaction_header_id) transaction_header_id
,fth1.asset_id
FROM fa_transaction_headers fth1
,&LP_FA_BOOKS fb1
,&LP_FA_DEPRN_PERIODS fdp1
WHERE fb1.transaction_header_id_in = fth1.transaction_header_id
AND fth1.asset_id = fb1.asset_id
AND fth1.book_type_code = fdp1.book_type_code
AND fth1.book_type_code = :P_BOOK_NAME
AND fdp1.period_counter = &p_lex_end_period_counter
AND fth1.date_effective <= fdp1.period_close_date
GROUP BY fth1.asset_id) subfth
-- Bug#12351785:Continuation of bug9237853.
-- Bug#9237853:Changes done to avoid duplicate rows.
WHERE fadd.asset_id = fb.asset_id
-- AND fadd.asset_id = fah.asset_id
-- AND fah.category_id = fc.category_id
-- AND fadd.asset_id = fai.asset_id(+)
-- AND fai.po_vendor_id = pov.vendor_id(+)
--AND fdp.book_type_code = fds.book_type_code
--AND fb.asset_id = fds.asset_id
--AND fdp.period_counter = fds.period_counter
AND fadd.asset_category_id = fc.category_id
AND fb.transaction_header_id_in = subfth.transaction_header_id
AND fb.asset_id = subfth.asset_id
--AND fb.book_type_code = fdp.book_type_code
AND fadd.asset_type = 'CAPITALIZED'
AND fb.depreciate_flag = 'YES'
AND fb.book_type_code = :P_BOOK_NAME
--AND fdp.period_counter >= &p_lex_begin_period_counter
--AND fdp.period_counter <= &p_lex_end_period_counter
AND NVL(fb.period_counter_fully_retired, &p_lex_end_period_counter) >= &p_lex_begin_period_counter
--Bug#11932660
--AND fb.date_effective BETWEEN fdp.period_open_date AND NVL(fdp.period_close_date, fb.date_effective)
--Bug#9237853
--AND fah.transaction_header_id_in =
-- (SELECT MAX(fah1.transaction_header_id_in)
-- FROM fa_asset_history fah1
-- WHERE fah1.asset_id = fadd.asset_id)
ORDER BY major_category, asset_no |