FA Mass Change Preview - draft

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