FA Tofes Yud Alef Report 1342 (Israel)

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