FA Location ReportFA Location Report
select flx.value room_code,
trim(xtl.description) room_Desc,
to_char(flx.creation_date,'yyyy-mm-dd') Creation_Date,
to_char(flx.last_update_date,'yyyy-mm-dd') Last_Updt_Date,
to_char(flx.start_date_active,'yyyy-mm-dd') Start_Date,
to_char(flx.end_date_active,'yyyy-mm-dd') End_Date,
flx.enabled_flag Enabled_Flag
FROM fnd_vs_values_b flx,
fnd_flex_values_tl xtl,
fnd_flex_value_Sets fvs
WHERE fvs.flex_value_set_id = flx.value_set_id
AND xtl.flex_value_id = flx.value_id
and xtl.language = 'US'
and fvs.flex_value_set_name = 'XXON_FA_LOCATION_ROOM_GRID'
and :p_rpt_type ='ROOM'
and flx.enabled_flag = decode(:p_enabled_flag,'N','N','Y','Y', flx.enabled_flag )
*****select flx.value Building_Code,
trim(xtl.description) Building_Desc,
to_char(flx.creation_date,'yyyy-mm-dd') Creation_Date,
to_char(flx.last_update_date,'yyyy-mm-dd') Last_Updt_Date,
to_char(flx.start_date_active,'yyyy-mm-dd') Start_Date,
to_char(flx.end_date_active,'yyyy-mm-dd') End_Date,
flx.enabled_flag Enabled_Flag
FROM fnd_vs_values_b flx,
fnd_flex_values_tl xtl,
fnd_flex_value_Sets fvs
WHERE fvs.flex_value_set_id = flx.value_set_id
AND xtl.flex_value_id = flx.value_id
and xtl.language = 'US'
and fvs.flex_value_set_name = 'XXON_FA_LOCATION_BUILDING'
and :p_rpt_type ='BUILDING'
and flx.enabled_flag = decode(:p_enabled_flag,'N','N','Y','Y', flx.enabled_flag )
*******select flx.value Building_Code,
trim(xtl.description) Building_Desc,
to_char(flx.creation_date,'yyyy-mm-dd') Creation_Date,
to_char(flx.last_update_date,'yyyy-mm-dd') Last_Updt_Date,
to_char(flx.start_date_active,'yyyy-mm-dd') Start_Date,
to_char(flx.end_date_active,'yyyy-mm-dd') End_Date,
flx.enabled_flag Enabled_Flag
FROM fnd_vs_values_b flx,
fnd_flex_values_tl xtl,
fnd_flex_value_Sets fvs
WHERE fvs.flex_value_set_id = flx.value_set_id
AND xtl.flex_value_id = flx.value_id
and xtl.language = 'US'
and fvs.flex_value_set_name = 'XXON_FA_LOCATION_BUILDING'
and :p_rpt_type ='BUILDING'
and flx.enabled_flag = decode(:p_enabled_flag,'N','N','Y','Y', flx.enabled_flag )
*********select flx.value State_Code,
trim(xtl.description) State_Desc,
to_char(flx.creation_date,'yyyy-mm-dd') Creation_Date,
to_char(flx.last_update_date,'yyyy-mm-dd') Last_Updt_Date,
to_char(flx.start_date_active,'yyyy-mm-dd') Start_Date,
to_char(flx.end_date_active,'yyyy-mm-dd') End_Date,
flx.enabled_flag Enabled_Flag
FROM fnd_vs_values_b flx,
fnd_flex_values_tl xtl,
fnd_flex_value_Sets fvs
WHERE fvs.flex_value_set_id = flx.value_set_id
AND xtl.flex_value_id = flx.value_id
and xtl.language = 'US'
and fvs.flex_value_set_name = 'XXON_FA_LOCATION_STATE'
and :p_rpt_type ='STATE'
and flx.enabled_flag = decode(:p_enabled_flag,'N','N','Y','Y', flx.enabled_flag )
************select flx.value Country_Code,
trim(xtl.description) Country_Desc,
to_char(flx.creation_date,'yyyy-mm-dd') Creation_Date,
to_char(flx.last_update_date,'yyyy-mm-dd') Last_Updt_Date,
to_char(flx.start_date_active,'yyyy-mm-dd') Start_Date,
to_char(flx.end_date_active,'yyyy-mm-dd') End_Date,
flx.enabled_flag Enabled_Flag
FROM fnd_vs_values_b flx,
fnd_flex_values_tl xtl,
fnd_flex_value_Sets fvs
WHERE fvs.flex_value_set_id = flx.value_set_id
AND xtl.flex_value_id = flx.value_id
and xtl.language = 'US'
and fvs.flex_value_set_name = 'XXON_FA_LOCATION_COUNTRY'
and :p_rpt_type ='COUNTRY'
and flx.enabled_flag = decode(:p_enabled_flag,'N','N','Y','Y', flx.enabled_flag )
------FA Non Depreciating Asset with Non Zero NBV ReportFA Non Depreciating Asset with Non Zero NBV Report
---FA_S120_NON_DEPR_ASSET_WITH_NONZERO_NBV_V2_ModV1--
SELECT Al1.Book_Name,
Al1.Asset_Number,
Al1.Asset_Description,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Tag_Number,
Al1.Parent_Asset_Number,
Al1.Asset_Key,
Al1.Placed_In_Service,
Al1.Creation_Date_Addn,
Al1.Owned_Leased,
Al1.Asset_Comments_Attr2,
Al1.Orig_Asset_Num_Attr3,
Al1.Orig_Service_Dt_Attr4,
Al1.Custom_Declaration_Attr6,
Al1.Model_Number,
Al1.Serial_Number,
Al1.Manufacturer_Name,
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,
Al1.Depreciate,
Al1.Original_Cost,
Al1.Current_Cost,
Al1.Deprn_Amount,
Al1.Ytd_Deprn_Amt,
Al1.Life_Deprn_Amount,
Al1.Net_Book_Value,
Al1.Salvage_Value,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Original_Cost / Al2.Conversion_Rate
ELSE
Nvl(Al1.Original_Cost_Usd, Al1.Original_Cost)
END Original_Cost_Usd_Calc,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Current_Cost / Al2.Conversion_Rate
ELSE
Nvl(Al1.Current_Cost_Usd, Al1.Current_Cost)
END Curr_Cost_Usd_Calc,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Deprn_Amount / Al2.Conversion_Rate
ELSE
Nvl(Al1.Deprn_Amt_Usd, Al1.Deprn_Amount)
END Deprn_Usd_Calc,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Ytd_Deprn_Amt / Al2.Conversion_Rate
ELSE
Nvl(Al1.Ytd_Deprn_Usd, Al1.Ytd_Deprn_Amt)
END Ytd_Deprn_Usd_Calc,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Life_Deprn_Amount / Al2.Conversion_Rate
ELSE
Nvl(Al1.Life_Deprn_Usd, Al1.Life_Deprn_Amount)
END Life_Deprn_Usd_Calc,
CASE
WHEN Al1.Book_Name LIKE '6%' THEN
Al1.Net_Book_Value / Al2.Conversion_Rate
ELSE
Nvl(Al1.Net_Book_Value_Usd, Al1.Net_Book_Value)
END Nbv_Usd_Calc,
Al1.Salvage_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,
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,
fm.Method_Code Deprn_Method,
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=AMERICAN'),
fm.Life_In_Months),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'),
fm.Life_In_Months)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN')) - 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=AMERICAN'),
fm.Life_In_Months),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'),
fm.Life_In_Months)
END,
To_Date(Fdp.Period_Name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN')) - 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.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,
Glc4.Segment3 Clearing_Acct,
Glc4.Segment4 Clear_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 = 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,
--Fnd_User Fndu,
per_users Fndu,
fa_methods fm,
--Xxon_Employee_View Papf,
(
select ppf.person_id,
ppf.person_number Employee_Number,
ppn.full_name
from per_all_people_f ppf,
per_person_names_f ppn
where ppf.person_id = ppn.person_id
and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))
and trunc(sysdate) between trunc(nvl(ppn.effective_start_date,trunc(sysdate))) and trunc(nvl(ppn.effective_end_date,trunc(sysdate)))
and nvl(ppn.name_type,'GLOBAL') = 'GLOBAL'
) Papf,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Calendar_Periods Fcp,
(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 fm.method_id = fab.method_id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.Book_Type_Code = Fadh.Book_Type_Code
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 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.Person_Effective_Start_Date(+)) AND
Trunc(Papf.Person_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.Asset_Cost_Account_Ccid
--AND Glc3.Code_Combination_Id = Fada.Deprn_Reserve_Account_Ccid
--AND Glc4.Code_Combination_Id = Fada.Asset_Clearing_Account_Ccid
AND fcbk.category_id = fadb.asset_category_id
AND fcbk.book_type_code = fab.book_type_code
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) Al1,
(SELECT Glp.Period_Name Period_Mon_Yy,
Rat.From_Currency From_Curr,
Rat.To_Currency To_Curr,
Decode(Rat.Conversion_Type,
'Corporate',
'Corporate',
'1001',
'EOP',
'1002',
'AVG',
Rat.Conversion_Type) Conversion_Type,
Rat.Conversion_Type Conv_Type_Orig,
Rat.Conversion_Rate Conversion_Rate,
Rat.Conversion_Date Conversion_Date,
To_Number(To_Char(Rat.Conversion_Date, 'DD')) Conversion_Day
FROM Gl_Periods Glp, Gl_Daily_Rates Rat
WHERE Rat.Conversion_Date BETWEEN Glp.Start_Date AND Glp.End_Date
AND Glp.Period_Set_Name = '4-4-5'
AND Glp.Period_Type = 'Month') Al2
WHERE Al1.Trunc_Sysdate = Al2.Conversion_Date(+)
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.Depreciate = 'NO'
AND Al1.Asset_Category <> 'LAND'
AND (NOT Al1.Net_Book_Value BETWEEN - 1 AND 1)
AND Al2.Conversion_Type(+) = 'Corporate'
AND Al2.From_Curr(+) = 'USD'
AND Al2.To_Curr(+) = 'JPY'
ORDER BY Al1.Book_Name,
Al1.Asset_Number
No comments:
Post a Comment