FA Depreciation For Selected Periods Report
SELECT DISTINCT Al1.Book_Name,
Al1.Asset_Number,
Al2.Asset_Description,
Al2.Asset_Category,
Al2.Asset_Subcategory,
TO_CHAR(Al2.Placed_In_Service,'DD-MM-YYYY') Placed_In_Service,
--Al2.Creation_Date_Addn,
TO_CHAR(Al2.Creation_Date_Addn,'DD-MM-YYYY') Creation_Date_Addn,
Al2.Exp_Glbl_Acct,
Al2.Exp_Dept,
Al1.Period_Yyyymm,
Al2.Deprn_Method,
Al2.Life_Months,
Al1.Deprn_Amt,
Al1.Deprn_Amt_Usd,
Al1.Init_Reserve_Amt,
Al1.Init_Reserve_Usd
FROM (SELECT Fds.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Number,
Fds.Asset_Id Asset_Id,
Fdp.Period_Name Period_Name,
To_Char(To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM') Period_Yyyymm,
Fdp.Fiscal_Year Year_Yyyy,
Fdp.Period_Num Month_Mm,
Fds.Deprn_Run_Date Deprn_Run_Date,
Fds.Deprn_Source_Code Deprn_Source,
Fds.Deprn_Amount Deprn_Amt,
Fds.Ytd_Deprn Ytd_Deprn_Amt,
Fds.Deprn_Reserve Life_Deprn_Amt,
CASE
WHEN Fds.Deprn_Source_Code = 'BOOKS' THEN
Fds.Deprn_Reserve
ELSE
0
END Init_Reserve_Amt,
CASE
WHEN Fds.Deprn_Source_Code = 'BOOKS' THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Deprn_Reserve
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Deprn_Reserve * Rat.Conversion_Rate), 2)
ELSE
CASE
WHEN Fmds.Deprn_Source_Code = 'BOOKS' THEN
Fmds.Deprn_Reserve
ELSE
0
END
END
ELSE
0
END Init_Reserve_Usd,
Fds.Adjusted_Cost Deprn_Basis_Amt,
Fds.Deprn_Adjustment_Amount Deprn_Adj_Amt,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Deprn_Amount
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Deprn_Amount * Rat.Conversion_Rate), 2)
ELSE
Fmds.Deprn_Amount
END Deprn_Amt_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Ytd_Deprn
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Ytd_Deprn * Rat.Conversion_Rate), 2)
ELSE
Fmds.Ytd_Deprn
END Ytd_Deprn_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Deprn_Reserve
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Deprn_Reserve * Rat.Conversion_Rate), 2)
ELSE
Fmds.Deprn_Reserve
END Life_Deprn_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Adjusted_Cost
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Adjusted_Cost * Rat.Conversion_Rate), 2)
ELSE
Fmds.Adjusted_Cost
END Deprn_Basis_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Deprn_Adjustment_Amount
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Deprn_Adjustment_Amount * Rat.Conversion_Rate),
2)
ELSE
Fmds.Deprn_Adjustment_Amount
END Deprn_Adj_Usd,
Fds.Period_Counter Period_Counter,
Fds.Impairment_Amount Impairment_Amt,
Fds.Ytd_Impairment Impairment_Ytd,
Fds.Impairment_Reserve Impairment_Reserve,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Impairment_Amount
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Impairment_Amount * Rat.Conversion_Rate), 2)
ELSE
Fmds.Impairment_Amount
END Impair_Amt_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Ytd_Impairment
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Ytd_Impairment * Rat.Conversion_Rate), 2)
ELSE
Fmds.Ytd_Impairment
END Impair_Ytd_Usd,
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Fds.Impairment_Reserve
WHEN Substr(Fds.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Fds.Impairment_Reserve * Rat.Conversion_Rate), 2)
ELSE
Fmds.Impairment_Reserve
END Impair_Reserve_Usd
FROM Fa_Deprn_Periods Fdp,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadb,
Fa_Book_Controls Ctr,
Gl_Ledgers Lgr,
Gl_Daily_Rates Rat
WHERE Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fdp.Period_Counter = Fds.Period_Counter
AND Fmds.Asset_Id(+) = Fds.Asset_Id
AND Fmds.Book_Type_Code(+) = Fds.Book_Type_Code
AND Fmds.Period_Counter(+) = Fds.Period_Counter
AND Fadb.Asset_Id = Fds.Asset_Id
AND Ctr.Book_Type_Code = Fds.Book_Type_Code
AND Lgr.Ledger_Id = Ctr.Set_Of_Books_Id
AND Rat.Conversion_Type(+) = '1001' /*EOP rates*/
AND Rat.From_Currency(+) = Lgr.Currency_Code
AND Rat.To_Currency(+) = 'USD'
AND Rat.Conversion_Date(+) = Trunc(Fdp.Period_Open_Date)
UNION ALL
SELECT Fmds.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Number,
Fmds.Asset_Id Asset_Id,
Fdp.Period_Name Period_Name,
To_Char(To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM') Period_Yyyymm,
Fdp.Fiscal_Year Year_Yyyy,
Fdp.Period_Num Month_Mm,
Fmds.Deprn_Run_Date Deprn_Run_Date,
Fmds.Deprn_Source_Code Deprn_Source,
Fds.Deprn_Amount Deprn_Amt,
Fds.Ytd_Deprn Ytd_Deprn_Amt,
Fds.Deprn_Reserve Life_Deprn_Amt,
CASE
WHEN Fds.Deprn_Source_Code = 'BOOKS' THEN
Fds.Deprn_Reserve
ELSE
0
END Init_Reserve_Amt,
CASE
WHEN Fmds.Deprn_Source_Code = 'BOOKS' THEN
Fmds.Deprn_Reserve
ELSE
0
END Init_Reserve_Usd,
Fds.Adjusted_Cost Deprn_Basis_Amt,
Fds.Deprn_Adjustment_Amount Deprn_Adj_Amt,
Fmds.Deprn_Amount Deprn_Amt_Usd,
Fmds.Ytd_Deprn Ytd_Deprn_Usd,
Fmds.Deprn_Reserve Life_Deprn_Usd,
Fmds.Adjusted_Cost Deprn_Basis_Usd,
Fmds.Deprn_Adjustment_Amount Deprn_Adj_Usd,
Fmds.Period_Counter Period_Counter,
Fds.Impairment_Amount Impairment_Amt,
Fds.Ytd_Impairment Impairment_Ytd,
Fds.Impairment_Reserve Impairment_Reserve,
Fmds.Impairment_Amount Impair_Amt_Usd,
Fmds.Ytd_Impairment Impair_Ytd_Usd,
Fmds.Impairment_Reserve Impair_Reserve_Usd
FROM Fa_Deprn_Periods Fdp,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadb
WHERE Fdp.Book_Type_Code = Fmds.Book_Type_Code
AND Fdp.Period_Counter = Fmds.Period_Counter
AND Fds.Asset_Id(+) = Fmds.Asset_Id
AND Fds.Book_Type_Code(+) = Fmds.Book_Type_Code
AND Fds.Period_Counter(+) = Fmds.Period_Counter
AND Fds.Asset_Id IS NULL
AND Fadb.Asset_Id = Fmds.Asset_Id) Al1,
(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,
Fab.Date_Placed_In_Service Placed_In_Service,
Glp1.Period_Name Addn_Period_Mon_Yy,
Fadb.Creation_Date Creation_Date_Addn,
Fadb.Last_Update_Date Last_Updt_Addn,
Fab.Last_Update_Date Last_Updt_Books,
-- Fndu.User_Name 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.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,
FM.Method_Code Deprn_Method,
FM.Life_In_Months Life_Months,
-- 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,
Glcc.Segment1 Exp_Comp,
Glcc.Segment3 Exp_Glbl_Acct,
Glcc.Segment4 Exp_Lcl_Acct,
Glcc.Segment5 Exp_Dept,
Glcc.Segment6 Exp_Bu,
Glcc.Segment7 Exp_Intco,
Glcc.Segment8 Exp_Project,
--Glcc.Segment7 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.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,
(Fab.Original_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Original_Cost,
(Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned) Current_Cost
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Fa_Asset_Keywords Fask,
Fa_Distribution_History Fadh,
fa_category_books Fada,
Fa_Locations Fal,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
FA_METHODS FM
-- Fnd_User Fndu,
-- Apps.Xxon_Employee_View Papf
WHERE 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 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.User_Id
-- AND Fadh.Assigned_To = Papf.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN
-- Trunc(Papf.Person_Effective_Start_Date(+)) AND
-- Trunc(Papf.Person_Effective_End_Date(+))
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.category_id = fadb.asset_category_id
--AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND Fab.Book_Type_Code=Fadh.Book_Type_Code
AND Fab.METHOD_ID=FM.METHOD_ID
-- and Fab.Book_Type_Code='KOR FA CORP'
-- and Fadb.asset_number='KL04053000'
) Al2
WHERE Al1.Book_Name = Al2.Book_Name
AND Al1.Asset_Id = Al2.Asset_Id
AND (Al1.Book_Name IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Al1.Period_Name=nvl(:P_Perios_Name,Al1.Period_Name)
--AND Al1.Asset_Number='50000000141BT'
-- %IF 'amp;PARM01' = '' %THENDO;
-- %ELSE;
-- AND (Al1.Period_Name amp;SPARM01)
-- %ENDIF;
-- %IF 'amp;PARM02' = '' %THENDO;
-- %ELSE;
-- AND (Al1.Book_Name amp;SPARM02)
-- %ENDIF;
AND (Al1.Deprn_Amt>0 OR Al1.Init_Reserve_Amt>0)
ORDER BY Al1.Book_Name,
Al1.Asset_Number,
Al2.Asset_Description
----FA Depreciation Methods ReportFA Depreciation Methods Report
SELECT Mtd.Method_Code,
Mtd.Name Method_Description,
Mtd.Life_In_Months,
Trunc(Mtd.Life_In_Months / 12) Life_Years,
MOD(Mtd.Life_In_Months, 12) Life_Months,
Mtd.Rate_Source_Rule Rate_Source,
Mtd.Deprn_Basis_Rule Deprn_Basis,
Mtd.Prorate_Periods_Per_Year Periods_Per_Year,
Frt.Year Table_Year,
Frt.Period_Placed_In_Service Table_Month,
Frt.Rate Table_Rate,
to_char(Mtd.Creation_Date ,'mm/dd/yyyy hh:mi:ss AM') Method_Creation_Date,
to_char(Mtd.Last_Update_Date ,'mm/dd/yyyy hh:mi:ss AM') Method_Last_Updt_Date,
to_char(Frt.Creation_Date ,'mm/dd/yyyy hh:mi:ss AM') Rate_Creation_Date,
to_char(Frt.Last_Update_Date ,'mm/dd/yyyy hh:mi:ss AM') Rate_Last_Updt_Date
FROM Fa_Methods Mtd
, Fa_Rates Frt
WHERE Frt.Method_Id(+) = Mtd.Method_Id
AND Mtd.Method_Code = nvl(:P_METHOD_CODE,Mtd.Method_Code)
AND Mtd.Life_In_Months = nvl(:P_LIFE_IN_MONTHS,Mtd.Life_In_Months)
No comments:
Post a Comment