SELECT
AD.ASSET_NUMBER ASSET_NUMBER,
AD.ASSET_ID ASSET_ID,
&CAT_FLEX_ALL_SEG ASSET_CATEGORY,
AD.DESCRIPTION DESCR,
MCH.FROM_METHOD_CODE OLD_METHOD,
MCH.FROM_LIFE_IN_MONTHS OLD_LIFE,
MCH.FROM_BASIC_RATE*100 OLD_BASIC_RATE,
MCH.FROM_ADJUSTED_RATE*100 OLD_ADJ_RATE,
MCH.FROM_PRODUCTION_CAPACITY OLD_CAPACITY,
MCH.FROM_UOM OLD_UOM,
MCH.FROM_CONVENTION OLD_CONVENTION,
MCH.TO_METHOD_CODE NEW_METHOD,
MCH.TO_LIFE_IN_MONTHS NEW_LIFE,
MCH.TO_BASIC_RATE*100 NEW_BASIC_RATE,
MCH.TO_ADJUSTED_RATE*100 NEW_ADJ_RATE,
MCH.TO_PRODUCTION_CAPACITY NEW_CAPACITY,
MCH.TO_UOM NEW_UOM,
MCH.TO_CONVENTION NEW_CONVENTION,
DECODE(DP.PERIOD_CLOSE_DATE, NULL, 1, 0) ADDED_THIS_PERIOD,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, ca.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY,
FA_FAS860_XMLP_PKG.d_old_lifeformula(MCH.FROM_LIFE_IN_MONTHS) D_OLD_LIFE,
FA_FAS860_XMLP_PKG.d_new_lifeformula(MCH.TO_LIFE_IN_MONTHS) D_NEW_LIFE,
FA_FAS860_XMLP_PKG.change_legalformula(MCH.TO_UOM, DECODE ( DP.PERIOD_CLOSE_DATE , NULL , 1 , 0 ), AD.ASSET_ID, :C_BOOK) CHANGE_LEGAL,
FA_FAS860_XMLP_PKG.message_dispformula(:CHANGE_LEGAL, :MESSAGE_DISP) MESSAGE_DISP,
FA_FAS860_XMLP_PKG.NEW_UOM_REAL_p NEW_UOM_REAL
From
FA_MASS_CHANGES MCH,
FA_BOOK_CONTROLS BC,
FA_ADDITIONS AD,
FA_BOOKS BK,
FA_CATEGORIES_B CA,
FA_CONVENTIONS CO,
FA_CALENDAR_PERIODS CP,
FA_METHODS MTH,
FA_TRANSACTION_HEADERS TH,
FA_DEPRN_PERIODS DP
WHERE
MCH.Mass_Change_ID = :P_MASS_CHANGE_ID1 AND
--MCH.Mass_Change_ID = :P_MASS_CHANGE_ID AND
MCH.Book_Type_Code = :C_BOOK AND
MCH.STATUS = 'PREVIEW' AND
BC.Book_Type_Code = MCH.Book_Type_Code AND
BC.Allow_Mass_Changes = 'YES' AND
AD.Asset_Number >= nvl(MCH.From_Asset_Number,
AD.Asset_Number) AND
AD.Asset_Number <= nvl(MCH.To_Asset_Number,
AD.Asset_Number) AND
AD.asset_type = nvl(MCH.asset_type, AD.asset_type) AND
AD.Asset_Type != 'CIP' AND
AD.Asset_Category_ID = nvl(MCH.Category_ID,
AD.Asset_Category_ID) AND
BK.Book_Type_Code = BC.Book_Type_Code AND
BK.Asset_Id = AD.Asset_Id AND
BK.Transaction_Header_ID_Out IS NULL AND
BK.Period_Counter_Fully_Retired IS NULL AND
BK.Date_Placed_In_Service >= nvl(MCH.From_Date_Placed_In_Service,
BK.Date_Placed_In_Service) AND
BK.Date_Placed_In_Service <= nvl(MCH.To_Date_Placed_In_Service,
BK.Date_Placed_In_Service) AND
BK.Deprn_Method_Code = nvl(MCH.From_Method_Code,
BK.Deprn_Method_Code) AND
nvl(BK.Life_In_Months, -1) = nvl(MCH.From_Life_In_Months,
nvl(BK.Life_In_Months, -1)) AND
nvl(BK.Basic_Rate, -1) = nvl(MCH.From_Basic_Rate,
nvl(BK.Basic_Rate, -1)) AND
nvl(BK.Adjusted_Rate, -1) = nvl(MCH.From_Adjusted_Rate,
nvl(BK.Adjusted_Rate, -1)) AND
nvl(BK.Unit_Of_Measure, -1) =
nvl(MCH.From_Uom, nvl(BK.Unit_Of_Measure, -1)) AND
BK.Prorate_Convention_Code = nvl(MCH.From_Convention,
BK.Prorate_Convention_Code) AND
nvl(BK.Production_Capacity, -1) =
nvl(MCH.From_Production_Capacity,
nvl(BK.Production_Capacity, -1)) AND
nvl(BK.Period_Counter_Fully_Reserved, -1)
= decode(MCH.Change_Fully_Rsvd_Assets, 'YES',
nvl(BK.Period_Counter_Fully_Reserved, -1),
-1) AND
NVL(BK.Disabled_flag, 'N') = 'N' AND
CA.Category_ID = AD.Asset_Category_ID AND
CO.Prorate_Convention_Code =
decode(MCH.From_Convention,
NULL, BK.Prorate_Convention_Code,
MCH.To_Convention) AND
CO.Start_Date <= BK.Date_Placed_In_Service AND
CO.End_Date >= BK.Date_Placed_In_Service AND
CP.Calendar_Type = BC.Prorate_Calendar AND
CP.Start_Date <= CO.Prorate_Date AND
CP.End_Date >= CO.Prorate_Date AND
MTH.Method_Code = decode(MCH.From_Method_Code,
NULL, BK.Deprn_Method_Code, MCH.To_Method_Code) AND
nvl(MTH.Life_In_Months, -1) = nvl(decode(MCH.From_Method_Code,
NULL, BK.Life_In_Months,
MCH.To_Life_In_Months), -1) AND
TH.Asset_ID = AD.Asset_ID AND
TH.Book_type_code = BK.Book_Type_Code AND
TH.Transaction_Type_Code in( 'ADDITION','GROUP ADDITION') AND
DP.Book_Type_Code = TH.Book_Type_Code AND
DP.Period_Open_Date <= TH.Date_Effective AND
NVL(DP.Period_Close_Date, SYSDATE) >
TH.Date_Effective AND
( nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'N'
OR
( fnd_profile.value('CRL-FA ENABLED') = 'Y'
and not exists (select 1 from fa_asset_hierarchy ash
where ash.asset_id = ad.asset_id and
ash.parent_hierarchy_id is not null) )
) ORDER BY AD.Asset_Number |
