FA Asset Adjustment Life Method Report
--FA Asset Adjustment Life Methods DM--
SELECT Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Category,
Al1.Period_Mon_Yy Period,
to_char(Al1.Last_Update_Date,'mm/dd/yyyy hh:mi:ss AM') Last_Update_Date,
Al1.Last_Updt_Name,
Al1.Asset_Description,
Al1.Addn_Period_Mon_Yy Addn_Period,
Al1.Deprn_Method,
Al1.Prev_Method,
Al1.Life_Months,
Al1.Prev_Life,
Al1.Depr_Flag,
Al1.Prev_Depr_Flag,
Al1.Amort_Adj,
Al1.Prev_Amort_Adj,
to_char(Al1.Placed_Svc,'mm/dd/yyyy hh:mi:ss AM') Placed_Svc,
to_char(Al1.Prev_Placed_Svc,'mm/dd/yyyy hh:mi:ss AM') Prev_Placed_Svc,
Al1.Asset_Id,
to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM') timestamp
FROM (SELECT Fadj.Book_Type_Code Book_Name,
Fadd.Attribute_Category_Code Asset_Category,
Fadd.Asset_Number Asset_Number,
Prd.Period_Name Period_Mon_Yy,
Fadj.Last_Update_Date Last_Update_Date,
Fadj.Transaction_Type_Code Adjustment_Type,
Usr.User_id Last_Updt_Id,
Usr.username Last_Updt_Name,
Fadd.Creation_Date Addn_Creation_Date,
Glp.Period_Name Addn_Period_Mon_Yy,
Fadt.Description Asset_Description,
--Fab.Deprn_Method_Code Deprn_Method,
--(select method_code from fa_methods where method_id =Fab.method_id) Deprn_Method,
fm.method_code Deprn_Method,
--Fab.Life_In_Months Life_Months,
--null Life_Months,
fm.Life_In_Months Life_Months,
Fab.Depreciate_Flag Depr_Flag,
CASE
WHEN Fab.Rate_Adjustment_Factor = 1 THEN
'No'
ELSE
'Yes'
END Amort_Adj,
Fab.Date_Placed_In_Service Placed_Svc,
--Fabp.Deprn_Method_Code Prev_Method,
(select method_code from fa_methods where method_id =Fabp.method_id) Prev_Method,
--Fabp.Life_In_Months Prev_Life,
--null Prev_Life,
(select Life_In_Months from fa_methods where method_id =Fabp.method_id) Prev_Life,
Fabp.Depreciate_Flag Prev_Depr_Flag,
CASE
WHEN Fabp.Rate_Adjustment_Factor = 1 THEN
'No'
ELSE
'Yes'
END Prev_Amort_Adj,
Fabp.Date_Placed_In_Service Prev_Placed_Svc,
Fadj.Asset_Id Asset_Id,
Fadj.Transaction_Header_Id Transaction_Header_Id
FROM Fa_Transaction_Headers Fadj,
Fa_Additions_b Fadd,
Fa_Additions_Tl Fadt,
per_users Usr,
Fa_Deprn_Periods Prd,
Fa_Books Fab,
Fa_Books Fabp,
Gl_Periods Glp,
fa_methods fm
WHERE Fadd.Asset_Id = Fadj.Asset_Id
AND Usr.Username = Fadj.Last_Updated_By
AND Fadj.Transaction_Type_Code = 'ADJUSTMENT'
AND Prd.Book_Type_Code = Fadj.Book_Type_Code
AND Fadj.Date_Effective BETWEEN Prd.Period_Open_Date AND
Nvl(Prd.Period_Close_Date, SYSDATE)
AND Fab.Asset_Id = Fadj.Asset_Id
AND Fab.Book_Type_Code = Fadj.Book_Type_Code
AND Fab.Transaction_Header_Id_In = Fadj.Transaction_Header_Id
AND Fabp.Asset_Id = Fab.Asset_Id
AND Fabp.Transaction_Header_Id_Out = Fab.Transaction_Header_Id_In
AND Fabp.Book_Type_Code = Fab.Book_Type_Code
and fab.method_id = fm.method_id
AND Glp.Period_Set_Name = '4-4-5'
AND Trunc(Fadd.Creation_Date) BETWEEN Glp.Start_Date AND Glp.End_Date
AND Fadt.Asset_Id = Fab.Asset_Id
AND Fadt.Language = 'US'
AND Prd.Period_Name = nvl(:p_period_name,Prd.Period_Name)
AND Fadj.Book_Type_Code = nvl(:p_Book_Type_Code,Fadj.Book_Type_Code)
-- AND (Fabp.Deprn_Method_Code = Fab.Deprn_Method_Code OR
AND (Fabp.Method_id = Fab.Method_id OR
-- Fabp.Life_In_Months = Fab.Life_In_Months OR
Fabp.Depreciate_Flag = Fab.Depreciate_Flag OR
Fabp.Date_Placed_In_Service = Fab.Date_Placed_In_Service OR
(Fabp.Rate_Adjustment_Factor = 1 AND
Fab.Rate_Adjustment_Factor = 1))) Al1
------FA Asset Adjustments ReportFA Asset Adjustments Report
SELECT Al1.Period_Mon_Yy Period,
Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Description,
Al1.Asset_category,
Al1.Asset_subcategory,
Al1.Source_Type,
Al1.Adjustment_Type,
SUM(CASE
WHEN Al1.Debit_Credit_Flag = 'DR' THEN
Al1.Adj_Amount
ELSE
Al1.Adj_Amount * -1
END) Adj_Amount,
SUM(CASE
WHEN Al1.Debit_Credit_Flag = 'DR' THEN
Al1.Adj_Amount_Usd
ELSE
Al1.Adj_Amount_Usd * -1
END) Adj_Amount_Usd,
to_char(Al1.Addn_Creation_Date,'mm/dd/yyyy hh:mi:ss AM') Addn_Creation_Date,
Al1.Addn_Period_Mon_Yy ,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Orig_Cost_Usd,
Al1.Curr_Cost_Usd,
to_char(Al1.Last_Update_Date,'mm/dd/yyyy hh:mi:ss AM') Last_Update_Date,
Al1.Last_Updt_Name ,
to_char(sysdate ,'mm/dd/yyyy hh:mi:ss AM') timestamp
FROM
(
SELECT Fadj.Book_Type_Code Book_Name,
Fcb.segment1 Asset_category,
Fcb.segment2 Asset_subcategory,
Fadd.Asset_Number Asset_Number,
Prd.Period_Name Period_Mon_Yy,
Fadj.Last_Update_Date Last_Update_Date,
Fadj.Source_Type_Code Source_Type,
Fadj.Adjustment_Type Adjustment_Type,
Fadj.Debit_Credit_Flag Debit_Credit_Flag,
Fadj.Adjustment_Amount Adj_Amount_Usd,
Famc.Adjustment_Amount Adj_Amount,
ppnf.display_name Last_Updt_Name,
Fab.Original_Cost Orig_Cost_Usd,
Fab.Cost Curr_Cost_Usd,
Famb.Original_Cost Original_Cost,
Famb.Cost Current_Cost,
Fadd.Creation_Date Addn_Creation_Date,
Prd.Period_Name Addn_Period_Mon_Yy,
Fadt.Description Asset_Description
FROM Fa_Adjustments Fadj,
Fa_Additions_b Fadd,
Fa_Additions_Tl Fadt,
Fa_Mc_Adjustments Famc,
Fa_Deprn_Periods Prd,
Fa_Books Fab,
Gl_Periods Glp,
Fa_Categories_B Fcb,
(SELECT Mbks.Asset_Id Asset_Id,
Mbks.Book_Type_Code Book_Type_Code,
Mbks.Transaction_Header_Id_In,
Mbks.Cost Cost,
Mbks.Original_Cost Original_Cost
FROM Fa_Mc_Books Mbks) Famb,
per_all_people_f ppf,
PER_PERSON_NAMES_F ppnf
WHERE Fadd.Asset_Id = Fadj.Asset_Id
AND Famc.Asset_Id(+) = Fadj.Asset_Id
AND Famc.Book_Type_Code(+) = Fadj.Book_Type_Code
AND Famc.Adjustment_Type(+) = Fadj.Adjustment_Type
AND Famc.Transaction_Header_Id(+) = Fadj.Transaction_Header_Id
AND Famc.Source_Type_Code(+) = 'ADJUSTMENT'
--AND Usr.User_Id = Fadj.Last_Updated_By
AND Fadj.Source_Type_Code = 'ADJUSTMENT'
AND Prd.Book_Type_Code = Fadj.Book_Type_Code
AND Prd.Period_Counter = Fadj.Period_Counter_Adjusted
AND Fab.Asset_Id = Fadj.Asset_Id
AND Fab.Book_Type_Code = Fadj.Book_Type_Code
AND Fab.Transaction_Header_Id_In = Fadj.Transaction_Header_Id
AND Glp.Period_Set_Name = '4-4-5'
AND Trunc(Fadd.Creation_Date) BETWEEN Glp.Start_Date AND
Glp.End_Date
AND Fadt.Asset_Id = Fab.Asset_Id
AND Fadt.Language = 'US'
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Fadj.Adjustment_Type = 'COST'
AND Fadd.asset_category_id = fcb.category_id
AND ppf.person_number = Fadj.Last_Updated_By
AND ppf.person_id = ppnf.person_id
AND ppnf.name_type= 'GLOBAL'
AND (Prd.Period_Name) = nvl(:P_PERIOD_NAME,Prd.Period_Name)
AND (Fadj.Book_Type_Code) = nvl(:P_BOOK_TYPE_CODE,Fadj.Book_Type_Code)
)Al1
group by
Al1.Period_Mon_Yy ,
Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Description,
Al1.Asset_category,
Al1.Asset_subcategory,
Al1.Source_Type,
Al1.Adjustment_Type,
to_char(Al1.Addn_Creation_Date,'mm/dd/yyyy hh:mi:ss AM') ,
Al1.Addn_Period_Mon_Yy ,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Orig_Cost_Usd,
Al1.Curr_Cost_Usd,
to_char(Al1.Last_Update_Date,'mm/dd/yyyy hh:mi:ss AM') ,
Al1.Last_Updt_Name ,
to_char(sysdate ,'mm/dd/yyyy hh:mi:ss AM')
-------FA Asset Depreciation Comparison Current Last ReportFA Asset Depreciation Comparison Current Last Report
select * from (SELECT DISTINCT Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Description,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Exp_Glbl_Acct,
Al1.Reserve_Acct,
Al1.Exp_Dept,
Al1.Placed_In_Service,
Al1.Deprn_Method,
Al1.Life_Months,
-- Al1.Remaining_Life_Months,
Al1.Remaining_Life_Months,
Al1.Original_Cost Original_Cost_Usd,
Al1.Current_Cost Current_Cost_Usd,
Al1.Ytd_Deprn_Amt Ytd_Deprn_Amt_Usd,
Al1.Life_Deprn_Amount Life_Deprn_Amt_Usd,
Al1.Net_Book_Value Net_Book_Value_Usd,
Al1.Deprn_Amount Deprn_Amt_Usd,
Al1.Prev_Mth_Deprn_Amt Prev_Mth_Deprn_Amt_Usd,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Ytd_Deprn_Amt,
Al1.Life_Deprn_Amount Life_Deprn_Amt,
Al1.Net_Book_Value,
Al1.Deprn_Amount Deprn_Amt,
Al1.Prev_Mth_Deprn_Amt,
Al1.Date_Retired,
CASE
WHEN Al1.Prev_Mth_Deprn_Amt <> 0 THEN
Round(((Al1.Deprn_Amount - Al1.Prev_Mth_Deprn_Amt) /
Al1.Prev_Mth_Deprn_Amt * 100),
1)
ELSE
NULL
END Difference
FROM (SELECT Fab.Book_Type_Code Book_Name,
Fabc.Book_Class Book_Class,
Fadb.Asset_Number Asset_Number,
Fadt.Description Asset_Description,
Substr(Fadb.Attribute_Category_Code,
1,
(Instr(Fadb.Attribute_Category_Code, '.') - 1)) Asset_Category,
Substr(Fadb.Attribute_Category_Code,
(Instr(Fadb.Attribute_Category_Code, '.') + 1),
Length(Fadb.Attribute_Category_Code)) Asset_Subcategory,
Fadb.Attribute_Category_Code Category_Subcategory,
Fadb.Tag_Number Tag_Number,
Fask.Segment1 Asset_Key,
Fadb2.Asset_Number Parent_Asset_Number,
to_char(fab.Date_Placed_In_Service,'MM/DD/YYYY') Placed_In_Service, --Added by samba on 20250425
Glp1.Period_Name Addn_Period,
Adnp.Addn_Fa_Period Addn_Fa_Period,
Fadb.Creation_Date Creation_Date_Addn,
Fadb.Last_Update_Date Last_Updt_Addn,
Fab.Last_Update_Date Last_Updt_Books,
Fndu.UserName Created_By_Id,
--Fndu.Description Created_By_Name,
Fadb.Attribute2 Asset_Comments_Attr2,
Fadb.Attribute3 Orig_Asset_Num_Attr3,
Fadb.Attribute4 Orig_Service_Dt_Attr4,
Fadb.Attribute5 Impairment_Amt_Attr5,
Fadb.Attribute6 Custom_Declaration_Attr6,
Fadb.Attribute7 Held_For_Sale,
Fadb.Serial_Number Serial_Number,
Fadb.Manufacturer_Name Manufacturer_Name,
Fadb.Model_Number Model_Number,
Fadb.Owned_Leased Owned_Leased,
Fadb.New_Used New_Used,
Fal.Segment1 Country,
Fal.Segment2 State,
Fal.Segment3 City,
Fal.Segment4 Building,
Fal.Segment5 Room,
Fab.Deprn_Start_Date Deprn_Start_Date,
--Fab.Deprn_Method_Code Deprn_Method,
(select method_code from fa_methods fm where fm.method_id = fab.METHOD_ID) Deprn_Method,
--Fab.Life_In_Months Life_Months,
(SELECT life_in_months from fa_methods fm where fm.METHOD_ID = Fab.METHOD_ID ) Life_Months, --Added by samba on 20250425
/* CASE
WHEN (Months_Between(CASE
WHEN To_Char(Fcp.Start_Date, 'YYYY') < 2000 THEN
Add_Months(Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
)
END,
To_Date(Fdp.Period_Name, 'MON-YY')) - 1) > 0 THEN
Months_Between(CASE
WHEN To_Char(Fcp.Start_Date, 'YYYY') < 2000 THEN
Add_Months(Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
)
END,
To_Date(Fdp.Period_Name, 'MON-YY')) - 1
ELSE
0
END Remaining_Life_Months, */
(SELECT life_in_months from fa_methods fm
where fm.METHOD_ID = fab.METHOD_ID) - (fdp.period_counter - fdp2.period_counter) Remaining_Life_Months, --Added by samba on 20250425
--Fab.Retirement_Pending_Flag Retirement_Pending_Flag,
CASE
WHEN Nvl(Fab.Period_Counter_Fully_Retired, 0) = 0 THEN
'N'
ELSE
'Y'
END Retired_y_n,
to_char(Rets.Date_Retired,'MM/DD/YYYY') Date_Retired, --Added by samba on 20250425
Glcc.Segment1 Exp_Comp,
Glcc.Segment3 Exp_Glbl_Acct,
Glcc.Segment4 Exp_Lcl_Acct,
Glcc.Segment5 Exp_Dept,
Glcc.Segment7 Exp_Intco,
Glcc.Segment8 Exp_Project,
Glcc.Segment6 Exp_Bu,
Glcc.Segment9 Exp_Future,
Glc2.Segment3 Cost_Acct,
Glc2.Segment4 Cost_Acct_Local,
Glc3.Segment3 Reserve_Acct,
Glc3.Segment4 Reserve_Acct_Local,
Glcc.Segment1 || '.' || Glcc.Segment5 Company_Dept,
(Fab.Original_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Original_Cost,
(Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned) Current_Cost,
CASE
WHEN Fdp.Fiscal_Year = Fdp.Fiscal_Year THEN
(Fds.Ytd_Deprn / Fadb.Current_Units * Fadh.Units_Assigned)
ELSE
0
END Ytd_Deprn_Amt,
(Fds.Deprn_Reserve / Fadb.Current_Units * Fadh.Units_Assigned) Life_Deprn_Amount,
(Fdsc.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Deprn_Amount,
(Lmdp.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Prev_Mth_Deprn_Amt,
(Fds.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned) Impairment_Reserve,
((Fab.Cost - Fds.Deprn_Reserve - Fds.Impairment_Reserve) /
Fadb.Current_Units * Fadh.Units_Assigned) Net_Book_Value,
(Fab.Adjusted_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Adjusted_Cost,
(Fab.Salvage_Value / Fadb.Current_Units * Fadh.Units_Assigned) Salvage_Value,
Fab.Percent_Salvage_Value Percent_Salvage,
(Fab.Recoverable_Cost / Fadb.Current_Units *
Fadh.Units_Assigned) Recoverable_Cost,
(Fab.Adjusted_Recoverable_Cost / Fadb.Current_Units *
Fadh.Units_Assigned) Adj_Recoverable_Cost,
Fab.Rate_Adjustment_Factor Rate_Adj_Factor,
--Fab.Prorate_Convention_Code Prorate_Conv,
Fab.Prorate_Date Prorate_Date,
--Fab.Cost_Change_Flag Cost_Change_Flag,
--Fab.Adjustment_Required_Status Adj_Required_Status,
Fab.Capitalize_Flag Capitalize,
Fab.Depreciate_Flag Depreciate,
Fab.Unit_Of_Measure Unit_Of_Measure,
Fab.Allowed_Deprn_Limit Allowed_Deprn_Limit_Pct,
Fab.Allowed_Deprn_Limit_Amount Allowed_Deprn_Limit_Amt,
Fadb.Asset_Type Asset_Type,
Fadb.Current_Units Total_Units,
Fadh.Units_Assigned Units_Assigned,
/*Papf.Full_Name Assigned_To_Name,
Papf.Employee_Number Assigned_To_Emp_No,*/
Fab.Asset_Id Asset_Id,
Fadh.Code_Combination_Id Code_Combination_Id,
To_Char(Fab.Date_Placed_In_Service, 'MON-YYYY') Depn_Start_Month,
Fabc.Last_Period_Counter Last_Period_Counter,
Fadb.Asset_Category_Id Asset_Category_Id,
Fadh.Distribution_Id Distribution_Id,
Fab.Transaction_Header_Id_In Transaction_Hdr_Id_In,
Trunc(SYSDATE) Trunc_Sysdate
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Book_Controls Fabc,
fa_category_books fcbk,
Fa_Asset_Keywords Fask,
Fa_Distribution_History Fadh,
--Fa_Distribution_Accounts Fada,
Fa_Distribution_HISTORY Fada,
Fa_Locations Fal,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
per_users Fndu,
/* Per_All_People_f Papf,*/
--FA_MC_DEPRN_SUMMARY Fds,
--FA_MC_BOOKS FAB,
Fa_Deprn_Summary Fds,
Fa_Books Fab,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Calendar_Periods Fcp,
(SELECT Ldep.Asset_Id Asset_Id,
Ldep.Book_Type_Code Book_Type_Code,
Ldep.Deprn_Amount Deprn_Amount
FROM --FA_MC_DEPRN_SUMMARY Ldep,
Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y')) Fdsc,
(SELECT Pdep.Asset_Id Asset_Id,
Pdep.Book_Type_Code Book_Type_Code,
Pdep.Deprn_Amount Deprn_Amount
FROM --FA_MC_DEPRN_SUMMARY Pdep,
Fa_Deprn_Summary Pdep
WHERE Pdep.Period_Counter =
(SELECT MAX(Pdrn.Period_Counter) - 1
FROM Fa_Deprn_Periods Pdrn
WHERE Pdrn.Book_Type_Code = Pdep.Book_Type_Code
AND Pdrn.Deprn_Run = 'Y')) Lmdp,
(SELECT Ret.Asset_Id Asset_Id,
Ret.Book_Type_Code Book_Type_Code,
MAX(Ret.Date_Retired) Date_Retired
FROM Fa_Retirements Ret
WHERE Ret.Status = 'DELETED'
GROUP BY Ret.Asset_Id, Ret.Book_Type_Code) Rets,
(SELECT Bks.Asset_Id Asset_Id,
Bks.Book_Type_Code Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
FROM Fa_Books Bks,
Fa_Deprn_Periods Dpr
WHERE Bks.Book_Type_Code = Dpr.Book_Type_Code
AND Bks.Date_Effective BETWEEN Dpr.Period_Open_Date AND
Nvl(Dpr.Period_Close_Date, SYSDATE + 1)
AND Bks.Transaction_Header_Id_In =
(SELECT MIN(Bks2.Transaction_Header_Id_In)
FROM Fa_Books Bks2
WHERE Bks2.Book_Type_Code = Bks.Book_Type_Code
AND Bks2.Asset_Id = Bks.Asset_Id)) Adnp
WHERE 1=1
AND :p_report_type='NOMRC'
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fadh.Location_Id = Fal.Location_Id
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id
-- AND Fada.Code_Combination_Id = Glcc.Code_Combination_Id
--
AND fcbk.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id
AND fcbk.book_type_code = fab.book_type_code
--
AND fcbk.category_id = fadb.asset_category_id
--AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
--AND Glc3.Code_Combination_Id = Fada.Deprn_Reserve_Account_Ccid
AND Glc2.code_combination_id = fcbk.asset_cost_account_ccid
AND Glc3.code_combination_id = fcbk.reserve_account_ccid
--
--
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND
Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fadb.Created_By = Fndu.Username
/*AND Fadh.Assigned_To = Papf.Person_Id(+)*/
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fds.Period_Counter =
(SELECT MAX(Fds3.Period_Counter)
FROM --FA_MC_DEPRN_SUMMARY Fds2,
Fa_Deprn_Summary Fds3
WHERE Fadb.Asset_Id = Fds3.Asset_Id
AND Fdp.Book_Type_Code = Fds3.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
/*AND Trunc(SYSDATE) BETWEEN Trunc(Papf.Effective_Start_Date(+)) AND
Trunc(Papf.Effective_End_Date(+))*/
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fab.Date_Ineffective IS NULL
AND Fabc.Date_Ineffective IS NULL
AND Fadh.Date_Ineffective IS NULL
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.Distribution_Id = Fadh.Distribution_Id
-- AND Glc2.Code_Combination_Id = Fada.Code_Combination_Id
-- AND Glc3.Code_Combination_Id = Fada.Code_Combination_Id
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Rets.Asset_Id(+) = Fab.Asset_Id
AND Rets.Book_Type_Code(+) = Fab.Book_Type_Code
AND Lmdp.Asset_Id(+) = Fab.Asset_Id
AND Lmdp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code) Al1
WHERE 1=1 --Al1.Book_Name = nvl(:p_book_name,Al1.Book_Name)
AND ( case when Al1.Book_Name in (:p_book_name) then 1
when (COALESCE(NULL,:p_book_name) is NULL ) then 1
end = 1 )
AND (Al1.Date_Retired IS NOT NULL AND Nvl(Al1.Deprn_Amount, 0) = 0 AND
Nvl(Al1.Prev_Mth_Deprn_Amt, 0) = 0)
UNION
SELECT DISTINCT Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Description,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Exp_Glbl_Acct,
Al1.Reserve_Acct,
Al1.Exp_Dept,
Al1.Placed_In_Service,
Al1.Deprn_Method,
Al1.Life_Months,
-- Al1.Remaining_Life_Months,
null Remaining_Life_Months,
Al1.Original_Cost Original_Cost_Usd,
Al1.Current_Cost Current_Cost_Usd,
Al1.Ytd_Deprn_Amt Ytd_Deprn_Amt_Usd,
Al1.Life_Deprn_Amount Life_Deprn_Amt_Usd,
Al1.Net_Book_Value Net_Book_Value_Usd,
Al1.Deprn_Amount Deprn_Amt_Usd,
Al1.Prev_Mth_Deprn_Amt Prev_Mth_Deprn_Amt_Usd,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Ytd_Deprn_Amt,
Al1.Life_Deprn_Amount Life_Deprn_Amt,
Al1.Net_Book_Value,
Al1.Deprn_Amount Deprn_Amt,
Al1.Prev_Mth_Deprn_Amt,
Al1.Date_Retired,
CASE
WHEN Al1.Prev_Mth_Deprn_Amt <> 0 THEN
Round(((Al1.Deprn_Amount - Al1.Prev_Mth_Deprn_Amt) /
Al1.Prev_Mth_Deprn_Amt * 100),
1)
ELSE
NULL
END Difference
FROM (SELECT Fab.Book_Type_Code Book_Name,
Fabc.Book_Class Book_Class,
Fadb.Asset_Number Asset_Number,
Fadt.Description Asset_Description,
Substr(Fadb.Attribute_Category_Code,
1,
(Instr(Fadb.Attribute_Category_Code, '.') - 1)) Asset_Category,
Substr(Fadb.Attribute_Category_Code,
(Instr(Fadb.Attribute_Category_Code, '.') + 1),
Length(Fadb.Attribute_Category_Code)) Asset_Subcategory,
Fadb.Attribute_Category_Code Category_Subcategory,
Fadb.Tag_Number Tag_Number,
Fask.Segment1 Asset_Key,
Fadb2.Asset_Number Parent_Asset_Number,
to_char(fab.Date_Placed_In_Service,'MM/DD/YYYY') Placed_In_Service, --Added by samba on 20250425
Glp1.Period_Name Addn_Period,
Adnp.Addn_Fa_Period Addn_Fa_Period,
Fadb.Creation_Date Creation_Date_Addn,
Fadb.Last_Update_Date Last_Updt_Addn,
Fab.Last_Update_Date Last_Updt_Books,
Fndu.UserName Created_By_Id,
--Fndu.Description Created_By_Name,
Fadb.Attribute2 Asset_Comments_Attr2,
Fadb.Attribute3 Orig_Asset_Num_Attr3,
Fadb.Attribute4 Orig_Service_Dt_Attr4,
Fadb.Attribute5 Impairment_Amt_Attr5,
Fadb.Attribute6 Custom_Declaration_Attr6,
Fadb.Attribute7 Held_For_Sale,
Fadb.Serial_Number Serial_Number,
Fadb.Manufacturer_Name Manufacturer_Name,
Fadb.Model_Number Model_Number,
Fadb.Owned_Leased Owned_Leased,
Fadb.New_Used New_Used,
Fal.Segment1 Country,
Fal.Segment2 State,
Fal.Segment3 City,
Fal.Segment4 Building,
Fal.Segment5 Room,
Fab.Deprn_Start_Date Deprn_Start_Date,
--Fab.Deprn_Method_Code Deprn_Method,
(select method_code from fa_methods fm where fm.method_id = fab.METHOD_ID) Deprn_Method,
--Fab.Life_In_Months Life_Months,
(SELECT life_in_months from fa_methods fm where fm.METHOD_ID = Fab.METHOD_ID ) Life_Months, --Added by samba on 20250425
/* CASE
WHEN (Months_Between(CASE
WHEN To_Char(Fcp.Start_Date, 'YYYY') < 2000 THEN
Add_Months(Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
)
END,
To_Date(Fdp.Period_Name, 'MON-YY')) - 1) > 0 THEN
Months_Between(CASE
WHEN To_Char(Fcp.Start_Date, 'YYYY') < 2000 THEN
Add_Months(Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY'),0--,Fab.Life_In_Months
)
END,
To_Date(Fdp.Period_Name, 'MON-YY')) - 1
ELSE
0
END Remaining_Life_Months, */
(SELECT life_in_months from fa_methods fm
where fm.METHOD_ID = fab.METHOD_ID) - (fdp.period_counter - fdp2.period_counter) Remaining_Life_Months, --Added by samba on 20250425
--Fab.Retirement_Pending_Flag Retirement_Pending_Flag,
CASE
WHEN Nvl(Fab.Period_Counter_Fully_Retired, 0) = 0 THEN
'N'
ELSE
'Y'
END Retired_y_n,
to_char(Rets.Date_Retired,'MM/DD/YYYY') Date_Retired, --Added by samba on 20250425
Glcc.Segment1 Exp_Comp,
Glcc.Segment3 Exp_Glbl_Acct,
Glcc.Segment4 Exp_Lcl_Acct,
Glcc.Segment5 Exp_Dept,
Glcc.Segment7 Exp_Intco,
Glcc.Segment8 Exp_Project,
Glcc.Segment6 Exp_Bu,
Glcc.Segment9 Exp_Future,
Glc2.Segment3 Cost_Acct,
Glc2.Segment4 Cost_Acct_Local,
Glc3.Segment3 Reserve_Acct,
Glc3.Segment4 Reserve_Acct_Local,
Glcc.Segment1 || '.' || Glcc.Segment5 Company_Dept,
(Fab.Original_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Original_Cost,
(Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned) Current_Cost,
CASE
WHEN Fdp.Fiscal_Year = Fdp.Fiscal_Year THEN
(Fds.Ytd_Deprn / Fadb.Current_Units * Fadh.Units_Assigned)
ELSE
0
END Ytd_Deprn_Amt,
(Fds.Deprn_Reserve / Fadb.Current_Units * Fadh.Units_Assigned) Life_Deprn_Amount,
(Fdsc.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Deprn_Amount,
(Lmdp.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Prev_Mth_Deprn_Amt,
(Fds.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned) Impairment_Reserve,
((Fab.Cost - Fds.Deprn_Reserve - Fds.Impairment_Reserve) /
Fadb.Current_Units * Fadh.Units_Assigned) Net_Book_Value,
(Fab.Adjusted_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Adjusted_Cost,
(Fab.Salvage_Value / Fadb.Current_Units * Fadh.Units_Assigned) Salvage_Value,
Fab.Percent_Salvage_Value Percent_Salvage,
(Fab.Recoverable_Cost / Fadb.Current_Units *
Fadh.Units_Assigned) Recoverable_Cost,
(Fab.Adjusted_Recoverable_Cost / Fadb.Current_Units *
Fadh.Units_Assigned) Adj_Recoverable_Cost,
Fab.Rate_Adjustment_Factor Rate_Adj_Factor,
--Fab.Prorate_Convention_Code Prorate_Conv,
Fab.Prorate_Date Prorate_Date,
--Fab.Cost_Change_Flag Cost_Change_Flag,
--Fab.Adjustment_Required_Status Adj_Required_Status,
Fab.Capitalize_Flag Capitalize,
Fab.Depreciate_Flag Depreciate,
Fab.Unit_Of_Measure Unit_Of_Measure,
Fab.Allowed_Deprn_Limit Allowed_Deprn_Limit_Pct,
Fab.Allowed_Deprn_Limit_Amount Allowed_Deprn_Limit_Amt,
Fadb.Asset_Type Asset_Type,
Fadb.Current_Units Total_Units,
Fadh.Units_Assigned Units_Assigned,
/*Papf.Full_Name Assigned_To_Name,
Papf.Employee_Number Assigned_To_Emp_No,*/
Fab.Asset_Id Asset_Id,
Fadh.Code_Combination_Id Code_Combination_Id,
To_Char(Fab.Date_Placed_In_Service, 'MON-YYYY') Depn_Start_Month,
Fabc.Last_Period_Counter Last_Period_Counter,
Fadb.Asset_Category_Id Asset_Category_Id,
Fadh.Distribution_Id Distribution_Id,
Fab.Transaction_Header_Id_In Transaction_Hdr_Id_In,
Trunc(SYSDATE) Trunc_Sysdate
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Book_Controls Fabc,
fa_category_books fcbk,
Fa_Asset_Keywords Fask,
Fa_Distribution_History Fadh,
--Fa_Distribution_Accounts Fada,
Fa_Distribution_HISTORY Fada,
Fa_Locations Fal,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
per_users Fndu,
/* Per_All_People_f Papf,*/
--FA_MC_DEPRN_SUMMARY Fds,
--FA_MC_BOOKS FAB,
FA_MC_DEPRN_SUMMARY Fds,
Fa_Books Fab,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Calendar_Periods Fcp,
(SELECT Ldep.Asset_Id Asset_Id,
Ldep.Book_Type_Code Book_Type_Code,
Ldep.Deprn_Amount Deprn_Amount
FROM FA_MC_DEPRN_SUMMARY Ldep
WHERE Ldep.Period_Counter =
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y')) Fdsc,
(SELECT Pdep.Asset_Id Asset_Id,
Pdep.Book_Type_Code Book_Type_Code,
Pdep.Deprn_Amount Deprn_Amount
FROM --FA_MC_DEPRN_SUMMARY Pdep,
FA_MC_DEPRN_SUMMARY Pdep
WHERE Pdep.Period_Counter =
(SELECT MAX(Pdrn.Period_Counter) - 1
FROM Fa_Deprn_Periods Pdrn
WHERE Pdrn.Book_Type_Code = Pdep.Book_Type_Code
AND Pdrn.Deprn_Run = 'Y')) Lmdp,
(SELECT Ret.Asset_Id Asset_Id,
Ret.Book_Type_Code Book_Type_Code,
MAX(Ret.Date_Retired) Date_Retired
FROM Fa_Retirements Ret
WHERE Ret.Status = 'DELETED'
GROUP BY Ret.Asset_Id, Ret.Book_Type_Code) Rets,
(SELECT Bks.Asset_Id Asset_Id,
Bks.Book_Type_Code Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
FROM Fa_Books Bks,
Fa_Deprn_Periods Dpr
WHERE Bks.Book_Type_Code = Dpr.Book_Type_Code
AND Bks.Date_Effective BETWEEN Dpr.Period_Open_Date AND
Nvl(Dpr.Period_Close_Date, SYSDATE + 1)
AND Bks.Transaction_Header_Id_In =
(SELECT MIN(Bks2.Transaction_Header_Id_In)
FROM Fa_Books Bks2
WHERE Bks2.Book_Type_Code = Bks.Book_Type_Code
AND Bks2.Asset_Id = Bks.Asset_Id)) Adnp
WHERE 1=1
AND :p_report_type='MRC'
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fadh.Location_Id = Fal.Location_Id
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id
AND fcbk.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id
AND fcbk.book_type_code = fab.book_type_code
--
AND fcbk.category_id = fadb.asset_category_id
--AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
--AND Glc3.Code_Combination_Id = Fada.Deprn_Reserve_Account_Ccid
AND Glc2.code_combination_id = fcbk.asset_cost_account_ccid
AND Glc3.code_combination_id = fcbk.reserve_account_ccid
--
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND
Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fadb.Created_By = Fndu.Username
/*AND Fadh.Assigned_To = Papf.Person_Id(+)*/
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fds.Period_Counter =
(SELECT MAX(Fds3.Period_Counter)
FROM --FA_MC_DEPRN_SUMMARY Fds2,
FA_MC_DEPRN_SUMMARY Fds3
WHERE Fadb.Asset_Id = Fds3.Asset_Id
AND Fdp.Book_Type_Code = Fds3.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
/*AND Trunc(SYSDATE) BETWEEN Trunc(Papf.Effective_Start_Date(+)) AND
Trunc(Papf.Effective_End_Date(+))*/
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fab.Date_Ineffective IS NULL
AND Fabc.Date_Ineffective IS NULL
AND Fadh.Date_Ineffective IS NULL
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.Distribution_Id = Fadh.Distribution_Id
-- AND Glc2.Code_Combination_Id = Fada.Code_Combination_Id
-- AND Glc3.Code_Combination_Id = Fada.Code_Combination_Id
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Rets.Asset_Id(+) = Fab.Asset_Id
AND Rets.Book_Type_Code(+) = Fab.Book_Type_Code
AND Lmdp.Asset_Id(+) = Fab.Asset_Id
AND Lmdp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code) Al1
WHERE 1=1 --Al1.Book_Name = nvl(:p_book_name,Al1.Book_Name)
AND ( case when Al1.Book_Name in (:p_book_name) then 1
when (COALESCE(NULL,:p_book_name) is NULL ) then 1
end = 1 )
AND (Al1.Date_Retired IS NOT NULL AND Nvl(Al1.Deprn_Amount, 0) = 0 AND
Nvl(Al1.Prev_Mth_Deprn_Amt, 0) = 0)
)
ORDER BY 1,2
No comments:
Post a Comment