FA Child Assets Report
select * from (SELECT Al1.Book_Name,
Al1.Asset_Number,
Al1.Parent_Asset_Number,
Al1.Asset_Description,
Al2.Parent_Period_Retired,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Tag_Number,
Al1.Placed_In_Service,
Al2.Parent_Placed_Srvc,
Al1.Creation_Date_Addn,
Al1.Asset_Comments_Attr2,
Al1.Orig_Asset_Num_Attr3,
Al1.Orig_Service_Dt_Attr4,
Al1.Country,
Al1.State,
Al1.City,
Al1.Building,
Al1.Room,
Al1.Cost_Acct,
Al1.Reserve_Acct,
Al1.Exp_Glbl_Acct,
Al1.Exp_Dept,
Al1.Deprn_Method,
Al1.Life_Months,
Al1.Remaining_Life_Months,
Al2.Parent_Life_Mths,
Al2.Parent_Remg_Life_Mths,
(Al1.Remaining_Life_Months - Al2.Parent_Remg_Life_Mths) Remaining_Life_Diff,
Al1.Depreciate,
Al1.Held_For_Sale,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Deprn_Amount,
Al1.Ytd_Deprn_Amt,
Al1.Life_Deprn_Amount,
Al1.Net_Book_Value,
Al1.Original_Cost_Usd,
Al1.Current_Cost_Usd,
Al1.Deprn_Amt_Usd,
Al1.Ytd_Deprn_Usd,
Al1.Life_Deprn_Usd,
Al1.Net_Book_Value_Usd
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,
Fab.Date_Placed_In_Service Placed_In_Service,
Glp1.Period_Name Addn_Period_Mon_Yy,
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,
null 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.Attribute8 Orig_Cost_Attr8,
Fadb.Attribute9 Child_Extend_Life,
Fadb.Attribute10 Uop_Indicator,
Fadb.Attribute11 Eam_Asset_Number,
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,
FM.METHOD_CODE Deprn_Method,
--Fab.Life_In_Months Life_Months,
FM.LIFE_IN_MONTHS Life_Months,
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','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months),
1),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months)
1)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH')) - 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','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months),
1),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months)
1)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH')) - 1
ELSE
0
END Remaining_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,
Glc4.Segment3 Clearing_Acct,
Glc4.Segment4 Clear_Acct_Local,
--Glcc.Segment1 || '.' || Glcc.Segment4 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 = Fdp2.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,
(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,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Original_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Original_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Original_Cost / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Original_Cost_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Cost / Fadb.Current_Units * Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
CASE
WHEN Fdp.Fiscal_Year = Fdp2.Fiscal_Year AND
(Fabc.Book_Class ='TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Ytd_Deprn / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Ytd_Deprn / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Ytd_Deprn / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Ytd_Deprn_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Deprn_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Deprn_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Deprn_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Life_Deprn_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fdsc.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mdsc.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fdsc.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Deprn_Amt_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Impairmt_Resv_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round(((Fab.Cost - Fds.Deprn_Reserve -
Fds.Impairment_Reserve) / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round(((Famb.Cost - Mcdt.Deprn_Reserve -
Mcdt.Impairment_Reserve) / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round(((Fab.Cost - Fds.Deprn_Reserve -
Fds.Impairment_Reserve) / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Net_Book_Value_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Adjusted_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Adjusted_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Adjusted_Cost / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Adjusted_Cost_Usd,
CASE
WHEN Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code LIKE '%FA ENP%') THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Salvage_Value / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Salvage_Value / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Salvage_Value / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Salvage_Value_Usd,
Fab.Rate_Adjustment_Factor Rate_Adj_Factor,
CASE
WHEN Fab.Rate_Adjustment_Factor < 1 THEN
'Y'
ELSE
'N'
END Amort_Adj,
--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,
Fadb.Parent_Asset_Id Parent_Asset_Id
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Asset_Keywords Fask,
Fa_Distribution_History Fadh,
--Fa_Distribution_Accounts Fada,
fa_category_books fcbk,
Fa_Locations Fal,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Code_Combinations Glc4,
Gl_Periods Glp1,
per_users Fndu,
/*PER_ALL_PEOPLE_F PAPF,*/
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Calendar_Periods Fcp,
FA_METHODS FM,
(SELECT Asset_Id Asset_Id,
Book_Type_Code Book_Type_Code,
Ytd_Deprn Ytd_Deprn,
Deprn_Reserve Deprn_Reserve,
Impairment_Reserve Impairment_Reserve
FROM Fa_Mc_Deprn_Summary Mcds
WHERE Period_Counter =
(SELECT MAX(Period_Counter)
FROM Fa_Mc_Deprn_Summary Mmcd
WHERE Mmcd.Asset_Id = Mcds.Asset_Id
AND Mmcd.Book_Type_Code = Mcds.Book_Type_Code)) Mcdt,
(SELECT Ldep.Asset_Id Asset_Id,
Ldep.Book_Type_Code Book_Type_Code,
Ldep.Deprn_Amount Deprn_Amount
FROM 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 Mdep.Asset_Id Asset_Id,
Mdep.Book_Type_Code Book_Type_Code,
Mdep.Deprn_Amount Deprn_Amount
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y')) Mdsc,
(SELECT Mbks.Asset_Id Asset_Id,
Mbks.Book_Type_Code Book_Type_Code,
Mbks.Cost Cost,
Mbks.Original_Cost Original_Cost,
Mbks.Adjusted_Cost Adjusted_Cost,
Mbks.Salvage_Value Salvage_Value
FROM Fa_Mc_Books Mbks
WHERE Mbks.Date_Ineffective IS NULL) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(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 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 fcbk.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.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(Period_Counter)
FROM Fa_Deprn_Summary Fds2
WHERE Fadb.Asset_Id = Fds2.Asset_Id
AND Fdp.Book_Type_Code = Fds2.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 = fcbk.Asset_Cost_Account_Ccid
AND Glc3.Code_Combination_Id = fcbk.reserve_account_ccid
AND Glc4.Code_Combination_Id = fcbk.Asset_Clearing_Account_Ccid
AND Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fab.Period_Counter_Fully_Retired IS NULL
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
and Fndu.ACTIVE_FLAG='Y'
AND fcbk.category_id = fadb.asset_category_id
AND fcbk.book_type_code = fab.book_type_code
and Fab.METHOD_ID = FM.METHOD_ID
) Al1,
(SELECT Fab.Book_Type_Code Parent_Book_Name,
Fabc.Book_Class Parent_Book_Class,
Fadb.Asset_Number Parent_Asset_Number,
Fab.Asset_Id Parent_Asset_Id,
Fadt.Description Parent_Asset_Description,
Substr(Fadb.Attribute_Category_Code,
1,
(Instr(Fadb.Attribute_Category_Code, '.') - 1)) Parent_Asset_Category,
Substr(Fadb.Attribute_Category_Code,
(Instr(Fadb.Attribute_Category_Code, '.') + 1),
Length(Fadb.Attribute_Category_Code)) Parent_Asset_Subcategory,
Fab.Date_Placed_In_Service Parent_Placed_Srvc,
--Fab.Life_In_Months Parent_Life_Mths,
--null Parent_Life_Mths,
FM.Life_In_Months Parent_Life_Mths,
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','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months),
1),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months)
1)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH')) - 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','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months),
1),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH'),
--Fab.Life_In_Months)
1)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=ENGLISH')) - 1
ELSE
0
END Parent_Remg_Life_Mths,
Fadb.Attribute2 Parent_Asset_Comments,
Fadb.Attribute4 Parent_Orig_Srvc,
Fab.Depreciate_Flag Parent_Depreciate,
Frp.Period_Name Parent_Period_Retired,
CASE
WHEN Nvl(Fab.Period_Counter_Fully_Retired, 0) = 0 THEN
'N'
ELSE
'Y'
END Parent_Retired
FROM Fa_Additions_b Fadb,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Fa_Calendar_Periods Fcp,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Frp,
FA_METHODS FM
WHERE Fadb.Asset_Id = Fab.Asset_Id
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Date_Ineffective IS NULL
AND Fabc.Date_Ineffective IS NULL
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
--AND Al1.Parent_Asset_Id = Al2.Parent_Asset_Id
-- AND Al1.Book_Name = nvl(:p_book_name,Al1.Book_Name)
-- AND Al1.Parent_Asset_Id IS NOT NULL
--= 'Life greater than Parent'
--AND Al2.Parent_Period_Retired IS NULL
-- and Frp.Period_Name IS NULL
--AND Nvl(AL1.Child_Extend_Life, 'No') = 'YES'
--AND AL1.Book_Class = 'CORPORATE'
AND Fabc.Book_Class = 'CORPORATE'
--AND Al2.Parent_Period_Retired IS NOT NULL
-- and Frp.Period_Name IS NOT NULL
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Frp.Period_Counter(+) = Fab.Period_Counter_Fully_Retired
AND Frp.Book_Type_Code(+) = Fab.Book_Type_Code
and Fab.METHOD_ID = FM.METHOD_ID
) Al2
WHERE Al1.Book_Name = Al2.Parent_Book_Name
AND Al1.Parent_Asset_Id = Al2.Parent_Asset_Id
AND Al1.Book_Name = nvl(:p_book_name,Al1.Book_Name)
AND Al1.Parent_Asset_Id IS NOT NULL
AND (
(:RPT_TYPE = 'Life greater than Parent' AND AL2.Parent_Period_Retired IS NULL
--AND NVL(AL1.Child_Extend_Life, 'No') = 'YES'
AND AL1.Book_Class = 'CORPORATE')
OR
(AL2.Parent_Period_Retired IS NOT NULL)
)
-- AND AL1.Book_Class = 'CORPORATE'
--AND Al2.Parent_Period_Retired IS NOT NULL
--= 'Life greater than Parent'
--'- = 'Y'
-- AND (Al1.Remaining_Life_Months - Al2.Parent_Remg_Life_Mths) = 0
ORDER BY Al1.Book_Name,
Al1.Asset_Number
)
WHERE
(
(:RPT_TYPE <>'Life greater than Parent'
AND :Rem_amount <>'Y'
)OR(:RPT_TYPE ='Life greater than Parent'
AND :Rem_amount <>'Y')OR (:RPT_TYPE <>'Life greater than Parent'
AND :Rem_amount ='Y') OR (Remaining_Life_Months - Parent_Remg_Life_Mths) > 0)
-- AND (
-- (:RPT_TYPE <> 'Life greater than Parent' AND :Rem_amount <> 'Y')
-- OR
-- (Remaining_Life_Months - Parent_Remg_Life_Mths) > 0
-- )
-- and ((:RPT_TYPE = 'Life greater than Parent'
-- AND :Rem_amount = 'Y'
-- AND (Remaining_Life_Months - Parent_Remg_Life_Mths) > 0) OR (1=1) )
Report End : FA Child Assets Report
No comments:
Post a Comment