FA Depreciation Summary By Codept Report
SELECT DISTINCT Al1.Book_Name,
Al2.Exp_Comp Exp_Co,
Al2.Exp_Glbl_Acct Exp_Gac,
(select ffv.Description
FROM fnd_flex_value_sets ffvs,
fnd_flex_values_VL ffv
WHERE ffvs.flex_value_set_name = 'XXON_GL_GLOBAL_ACCT'
and ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value = Al2.Exp_Glbl_Acct) Exp_Gac_Desc,
Al2.Exp_Dept,
Al2.Company_Dept CO_Dept,
Al1.Period_Mon_Yy Period,
Al1.Period_Yyyymm,
Al1.yyyyq Qtr_yyyyq,
SUM(CASE
WHEN Al2.Total_Units = 0 THEN
0
ELSE
Round((Al1.Deprn_Amt / Al2.Total_Units *
Al2.Units_Assigned),
2)
END) Deprn_Amt,
SUM(CASE
WHEN Al2.Total_Units = 0 THEN
0
ELSE
Round((Al1.Deprn_Amt_Usd / Al2.Total_Units *
Al2.Units_Assigned),
2)
END) Deprn_Amt_Usd,
Al2.Fs_Category
FROM (SELECT /*+ Parallel (8) */
Fds.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Number,
Fds.Asset_Id Asset_Id,
Fdp.Period_Name Period_Mon_Yy,
To_Char(To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM') Period_Yyyymm,
Gp.Period_Year || Gp.Quarter_num yyyyq,
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,
gl_periods Gp
WHERE 1=1
AND 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'
AND Rat.From_Currency(+) = Lgr.Currency_Code
AND Rat.To_Currency(+) = 'USD'
AND Rat.Conversion_Date(+) = Trunc(Fdp.Period_Open_Date)
AND Gp.Period_Set_Name = '4-4-5'
AND Gp.Period_Name = (SUBSTR(Fdp.Period_Name, 1, 3) || '-' || SUBSTR(Fdp.Period_Name, 7, 2))
UNION ALL
SELECT Fmds.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Number,
Fmds.Asset_Id Asset_Id,
Fdp.Period_Name Period_Mon_Yy,
To_Char(To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM') Period_Yyyymm,
Gp.Period_Year || Gp.Quarter_num yyyyq,
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,
gl_periods Gp
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
AND Gp.Period_Set_Name = '4-4-5'
AND Gp.Period_Name = Fdp.Period_Name
) 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.Serial_Number Serial_Number,
Fab.Deprn_Start_Date Deprn_Start_Date,
--Fab.Deprn_Method_Code Deprn_Method,
--Fab.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,
Xglcc.segment1 Fs_Category,
Fadb.Asset_Type Asset_Type,
Fadb.Current_Units Total_Units,
Fadh.Units_Assigned Units_Assigned,
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,
Fadh.Distribution_Id Distribution_Id,
Fadh.BOOK_TYPE_CODE test
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,
FA_CATEGORIES_B Xglcc,
--Apps.Xxon_Gl_Fs_Glcc_Attr_Plsec Xglcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
--per_users Fndu,
PER_ALL_PEOPLE_F 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 Glcc.Code_Combination_Id =Xglcc.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.Effective_Start_Date(+)) AND
Trunc(Papf.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 Xglcc.Category_Id = Fadb.Asset_Category_Id
AND Xglcc.Category_Id=Fada.Category_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_Name) OR COALESCE(:P_Book_Name,null) IS Null)
-- AND (AL1.Book_Name amp;SPARM01)
AND Al1.Deprn_Amt >0
-- AND (Al1.Deprn_Amt lt;gt; 0)
AND Al1.Period_Yyyymm=NVL((To_Char(To_Date(:P_Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM')),Al1.Period_Yyyymm)
--To_Char(To_Date(:P_Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'), 'YYYYMM')
-- AND (Al1.Period_Yyyymm amp;SPARM02)
GROUP BY Al1.Book_Name,
Al2.Exp_Comp,
Al2.Exp_Glbl_Acct,
Al2.Exp_Dept,
Al2.Company_Dept,
Al1.Period_Mon_Yy,
Al1.Period_Yyyymm,
Al1.yyyyq,
Al2.Fs_Category
ORDER BY Al1.Book_Name
No comments:
Post a Comment