--This Query selects the list of Assets,which satisfies the input selection parameters--
SELECT FADD.asset_id asset_id
,FADD.asset_number asset_number
,FADD.description asset_name
,FB.date_placed_in_service date_placed_in_service
,FADD.&lc_state_reg_date state_registration_filing_date
,FB.prorate_date prorate_date
,NVL(decode(FM.rate_source_rule, 'FLAT', (12*FFR.&lc_years + FFR.&lc_months)
,FB.life_in_months),0)
useful_life_months
,fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_select', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'ALL', 'N', 'DESCRIPTION') asset_category
,FB.deprn_method_code depreciation_method
,NVL(FB.adjusted_recoverable_cost,0) recoverable_cost
,NVL(FDS.deprn_amount,0) monthly_amount_of_depreciation
,FDP.period_name last_depreciation_period
,FDS.deprn_reserve accumulated_depreciation
FROM fa_additions FADD
,fa_books FB
,fa_methods FM
,fa_deprn_summary FDS
,fa_deprn_periods FDP
,fa_categories FC
,fa_flat_rates FFR
WHERE FADD.asset_number = NVL(:P_ASSET_NUMBER,FADD.asset_number)
AND FB.asset_id = FADD.asset_id
AND FB.deprn_method_code = FM.method_code
AND NVL(FB.life_in_months,-1) = NVL(FM.life_in_months,-1)
AND FDS.asset_id = FADD.asset_id
AND FDS.book_type_code = FDP.book_type_code
AND FDS.period_counter = FDP.period_counter
AND FDP.book_type_code = FB.book_type_code
AND FDP.period_counter =(SELECT MAX(FDS1.period_counter)
FROM fa_deprn_summary FDS1
WHERE FDS1.asset_id = FADD.asset_id
AND FDS1.book_type_code = FB.book_type_code)
AND ((FM.rate_source_rule = 'FLAT'
AND FFR.basic_rate = FB.basic_rate
AND FFR.adjusted_rate = FB.adjusted_rate)
OR
(NVL(FM.life_in_months,-1) = NVL(FB.life_in_months,-1)
AND FM.rate_source_rule <> 'FLAT'))
and FM.method_id=FFR.method_id(+)
AND FB.date_ineffective is null
AND FB.book_type_code = :P_BOOK_NAME
AND FADD.asset_category_id = FC.category_id
AND FC.category_id = NVL(:P_ASSET_CATEGORY,FC.category_id) |
