FA Inquiry And Retired Asset Report
select * from (SELECT 'union1' union_test,
Fab.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Num,
Fadt.Description Asset_Desc,
Fcb.Segment1 Asset_Category,
Fcb.Segment2 Asset_Subcategory,
Fadb.Tag_Number Tag_Num,
Fadb2.Asset_Number Parent_Asset_Num,
Fask.Segment1 Asset_Key1,
Fask.Segment2 Asset_Key2,
Fask.Segment3 Asset_Key3,
To_date(Date_Placed_In_Service, 'YYYY-MM-DD') Placed_In_Service,
to_char(Fadb.Creation_Date ,'DD-MM-YYYY HH:MI:SS')Creation_Date_Addn,
Fadb.Owned_Leased,
Fadb.Attribute2 Asset_Comments_Attr2,
Fadb.Attribute3 Orig_Asset_Num_Attr3,
Fadb.Attribute4 Orig_Service_Dt_Attr4,
Fadb.Attribute6 Custom_Declaration_Attr6,
Fadb.Attribute8 Orig_Cost_Attr8,
Fadb.Model_Number Model_Number,
Fadb.Serial_Number Serial_Number,
Fadb.Manufacturer_Name Manufacturer_Name,
Fal.Segment1 Country,
Fal.Segment2 State,
Fal.Segment3 City,
Fal.Segment4 Building,
Fal.Segment5 Room,
Glcc.Segment1 Exp_Co,
Glcc.Segment2 Exp_Loc,
Glcc.Segment3 Exp_Gac,
Glc2.Segment3 Cost_Gac,
Glc3.Segment3 Reserve_Gac,
Glcc.Segment5 Exp_Dept,
Glcc.Segment6 Exp_Bu,
Glcc.Segment8 Exp_proj,
-- 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 ROUND(fm.life_in_months - (MONTHS_BETWEEN (
(
SELECT
TO_DATE(TO_CHAR(fdd1.calendar_period_close_date,'MM/DD/YYYY'),'MM/DD/YYYY')
FROM
fa_deprn_periods fdd1
WHERE
fdd1.period_counter = fdp.period_counter
AND fdd1.Book_Type_Code = fdp.Book_Type_Code),TO_DATE(TO_CHAR(fab.date_placed_in_service,'MM/DD/YYYY'),'MM/DD/YYYY'))),0) < 0 THEN 0
ELSE ROUND(fm.life_in_months - (MONTHS_BETWEEN (
(
SELECT
TO_DATE(TO_CHAR(fdd1.calendar_period_close_date,'MM/DD/YYYY'),'MM/DD/YYYY')
FROM
fa_deprn_periods fdd1
WHERE
fdd1.period_counter = fdp.period_counter
AND fdd1.Book_Type_Code = fdp.Book_Type_Code),TO_DATE(TO_CHAR(fab.date_placed_in_service,'MM/DD/YYYY'),'MM/DD/YYYY'))),0) END Remaining_Life_Months,
Fab.Depreciate_Flag Depreciate,
Fadb.Attribute7 Held_For_Sale,
Fadb.Attribute11 Eam_Asset_Num,
(Fab.Original_Cost / Faah.Units * Fadh.Units_Assigned) Original_Cost_Usd,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost_Usd,
(Fdsc.Deprn_Amount / Faah.Units * Fadh.Units_Assigned) Deprn_Amt_Usd,
CASE
WHEN Fdp2.Fiscal_Year =
(SELECT Fdpc.Fiscal_Year
FROM Fa_Deprn_Periods Fdpc
WHERE Fdpc.Book_Type_Code = Fdp2.Book_Type_Code
AND Fdpc.Period_Name =:P_Period_name) THEN
(Fds.Ytd_Deprn / Faah.Units * Fadh.Units_Assigned)
ELSE
0
END Ytd_Deprn_Amt_Usd,
(Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn_Amt_Usd,
((Fab.Cost - Fds.Deprn_Reserve - Fds.Impairment_Reserve) /
Faah.Units * Fadh.Units_Assigned) Net_Book_Value_Usd,
Fabc.Book_Class,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Famb.Original_Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Original_Cost,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Current_Cost,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Mdsc.Deprn_Amount / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Deprn_Amt,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR (Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Fdpmc.Fiscal_Year =
(SELECT Fdpc.Fiscal_Year
FROM Fa_Deprn_Periods Fdpc
WHERE Fdpc.Book_Type_Code = Fdp2.Book_Type_Code
AND Fdpc.Period_Name =:P_Period_name ) THEN
Round((Mcdt.Ytd_Deprn / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
0
END
ELSE
NULL
END Ytd_Deprn,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Mcdt.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Life_Deprn,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round(((Famb.Cost - Mcdt.Deprn_Reserve - Mcdt.Impairment_Reserve) /
Faah.Units * Fadh.Units_Assigned),
2)
ELSE
NULL
END Net_Book_Value,
CASE
WHEN Fab.Rate_Adjustment_Factor < 1 THEN
'Y'
ELSE
'N'
END Amort_Adj,
Fadb.Attribute10 Uop_Indicator,
Fdp3.Period_Name Period_Retired,
Glc2.Segment4 Cost_Lac,
Glc3.Segment4 Reserve_Lac,
Glcc.Segment4 Exp_Lac,
null Assigned_To_Name,
--Xpv.Full_Name Assigned_To_Name,
Xpv.PERSON_NUMBER Assigned_To_Emp_Num,
Fadh.Units_Assigned,
FADB.ATTRIBUTE5 Impairment_Amt_Attr5,
FADB.ATTRIBUTE7 Child_Extend_Life,
to_char(sysdate ,'mm/dd/yyyy hh:mi:ss') timestamp,
Fdp2.period_name,
Fab.Asset_Id,
Fdp2.Book_Type_Code
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Deprn_Periods Fdpmc,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
FA_METHODS FM,
PER_ALL_PEOPLE_F xpv,
-- per_users Fndu,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period_name
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a,Fa_Mc_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Mcds.Asset_Id
AND b.Book_Type_Code = Mcds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck,
Fa_Mc_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period_name
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND a.Period_Counter =
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period_name
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
(SELECT Ldep.Asset_Id,
Ldep.Book_Type_Code,
Ldep.Deprn_Amount,
Ldep.Ytd_Deprn,
Ldep.Period_Counter
FROM Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y'
AND Ldrn.Period_Name =:P_Period_name)) Fdsc,
(SELECT Mdep.Asset_Id,
Mdep.Book_Type_Code,
Mdep.Deprn_Amount,
Mdep.Ytd_Deprn,
Mdep.Period_Counter
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y'
AND Mdrn.Period_Name =:P_Period_name)) Mdsc,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) 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,
Bks.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 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 Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.BOOK_TYPE_CODE=Fadh.Book_Type_Code
AND Faah.Asset_Id = Fadb.Asset_Id
and Fab.BOOK_TYPE_CODE=Faah.Book_Type_Code
AND Fadh.Assigned_To = Xpv.Person_Id(+)
AND Trunc(SYSDATE) BETWEEN Xpv.Effective_Start_Date(+) AND Xpv.Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id kk
AND Fadh.code_combination_id = 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 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 Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fask.Segment1 = nvl(:P_Asset_key,Fask.Segment1)
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 Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fdpmc.Book_Type_Code(+) = Mcdt.Book_Type_Code
AND Fdpmc.Period_Counter(+) = Mcdt.Period_Counter
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
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 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 Fdsc.Period_Counter(+) = Fds.Period_Counter
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
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 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 Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck, Fa_Deprn_Summary Fdk
WHERE Ck.Period_Name = :P_Period_name
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND Nvl(Fab.Period_Counter_Fully_Reserved, 0) <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a
JOIN Fa_Deprn_Summary b ON a.Period_Counter = b.Period_Counter
WHERE a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS (
SELECT 1
FROM Fa_Deprn_Periods Ck
JOIN Fa_Deprn_Summary Fdk ON Ck.Period_Counter = Fdk.Period_Counter
WHERE Ck.Period_Name = :P_Period_name
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND b.Asset_Id = Fdk.Asset_Id
)
AND NVL(Fab.Period_Counter_Fully_Reserved, 0) < (
SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
AND a.Period_Counter < (
SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
END = Fds.Period_Counter
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
AND Fab.METHOD_ID=FM.METHOD_ID
AND (Fadb.Asset_Number IN (:P_Asset_number) OR COALESCE(:P_Asset_number,null) IS Null)
and Fcb.Segment1 = nvl(:P_Asset_category,Fcb.Segment1)
and Fcb.Segment2 = nvl(:P_ASSET_SUBCATEGORY,Fcb.Segment2)
and Fal.Segment1 = nvl(:P_Country,Fal.Segment1)
and Fal.Segment2 = nvl(:P_State,Fal.Segment2)
and Fal.Segment3 = nvl(:P_City,Fal.Segment3)
and Fal.Segment4 = nvl(:P_Bilding,Fal.Segment4)
and Fal.Segment5 = nvl(:P_Room,Fal.Segment5)
and Glcc.Segment5 = nvl(:P_DEPARTMENT,Glcc.Segment5)
UNION ALL
SELECT 'union2' union_test,
Fab.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Num,
Fadt.Description Asset_Desc,
Fcb.Segment1 Asset_Category,
Fcb.Segment2 Asset_Subcategory,
Fadb.Tag_Number Tag_Num,
Fadb2.Asset_Number Parent_Asset_Num,
Fask.Segment1 Asset_Key1,
Fask.Segment2 Asset_Key2,
Fask.Segment3 Asset_Key3,
To_date(Date_Placed_In_Service, 'YYYY-MM-DD') Placed_In_Service,
to_char(Fadb.Creation_Date ,'DD-MM-YYYY HH:MI:SS')Creation_Date_Addn,
Fadb.Owned_Leased,
Fadb.Attribute2 Asset_Comments_Attr2,
Fadb.Attribute3 Orig_Asset_Num_Attr3,
Fadb.Attribute4 Orig_Service_Dt_Attr4,
Fadb.Attribute6 Custom_Declaration_Attr6,
Fadb.Attribute8 Orig_Cost_Attr8,
Fadb.Model_Number Model_Number,
Fadb.Serial_Number Serial_Number,
Fadb.Manufacturer_Name Manufacturer_Name,
Fal.Segment1 Country,
Fal.Segment2 State,
Fal.Segment3 City,
Fal.Segment4 Building,
Fal.Segment5 Room,
Glcc.Segment1 Exp_Co,
Glcc.Segment2 Exp_Loc,
Glc2.Segment3 Cost_Gac,
Glc3.Segment3 Reserve_Gac,
Glcc.Segment3 Exp_Gac,
Glcc.Segment5 Exp_Dept,
Glcc.Segment6 Exp_Bu,
Glcc.Segment8 Exp_proj,
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-YYYY','NLS_DATE_LANGUAGE=ENGLISH'),
Fm.Life_In_Months),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'), Fm.Life_In_Months)
END,
To_Date(:P_Period_name, 'MON-YYYY','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-YYYY','NLS_DATE_LANGUAGE=ENGLISH'),
Fm.Life_In_Months),
- (12 * 100))
ELSE
Add_Months(To_Date(Fcp.Period_Name, 'MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'), Fm.Life_In_Months)
END,
To_Date(:P_Period_name, 'MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')) - 1
ELSE
0
END Remaining_Life_Months,
Fab.Depreciate_Flag Depreciate,
Fadb.Attribute7 Held_For_Sale,
Fadb.Attribute11 Eam_Asset_Num,
(Fab.Original_Cost / Faah.Units * Fadh.Units_Assigned) Original_Cost_Usd,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost_Usd,
NULL Deprn_Amount_Usd,
NULL Ytd_Deprn_Usd,
NULL Life_Deprn_Amt_Usd,
((Fab.Cost) / Faah.Units * Fadh.Units_Assigned) Net_Book_Value_Usd,
Fabc.Book_Class,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Famb.Original_Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Original_Cost,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Current_Cost,
NULL Deprn_Amt,
NULL Ytd_Deprn,
NULL Life_Deprn,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
Round(((Famb.Cost) / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
NULL
END Net_Book_Value,
CASE
WHEN Fab.Rate_Adjustment_Factor < 1 THEN
'Y'
ELSE
'N'
END Amort_Adj,
Fadb.Attribute10 Uop_Indicator,
Fdp3.Period_Name Period_Retired,
Glc2.Segment4 Cost_Lac,
Glc3.Segment4 Reserve_Lac,
Glcc.Segment4 Exp_Lac,
null Assigned_To_Name,
Xpv.PERSON_NUMBER Assigned_To_Emp_Num,
Fadh.Units_Assigned,
FADB.ATTRIBUTE5 Impairment_Amt_Attr5,
FADB.ATTRIBUTE7 Child_Extend_Life,
to_char(sysdate ,'mm/dd/yyyy hh:mi:ss') timestamp,
Fdp2.period_name,
Fab.Asset_Id,
Fdp2.Book_Type_Code
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
Gl_Periods Glp1,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
FA_METHODS FM,
PER_ALL_PEOPLE_F xpv,
--per_users Fndu,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) 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,
Bks.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 Fab.Depreciate_Flag = 'NO'
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Summary Fds
WHERE Fds.Asset_Id = Fab.Asset_Id
AND Fds.Book_Type_Code = Fab.Book_Type_Code
AND Deprn_Source_Code = 'DEPRN')
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 Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.BOOK_TYPE_CODE=Fadh.Book_Type_Code
AND Faah.Asset_Id = Fadb.Asset_Id
and Fab.BOOK_TYPE_CODE=Faah.Book_Type_Code
AND Fadh.Assigned_To = Xpv.Person_Id(+)
AND Trunc(SYSDATE) BETWEEN Xpv.Effective_Start_Date(+) AND Xpv.Effective_End_Date(+)
AND Fadh.code_combination_id = 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 Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fask.Segment1 = nvl(:P_Asset_key,Fask.Segment1)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fab.Book_Type_Code
AND Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE 1=1
and Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE 1=1
and Fadppar.Period_Name = :P_Period_name
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
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 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 (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
AND Fab.METHOD_ID=FM.METHOD_ID
AND (Fadb.Asset_Number IN (:P_Asset_number) OR COALESCE(:P_Asset_number,null) IS Null)
and Fcb.Segment1 = nvl(:P_Asset_category,Fcb.Segment1)
and Fcb.Segment2 = nvl(:P_ASSET_SUBCATEGORY,Fcb.Segment2)
and Fal.Segment1 = nvl(:P_Country,Fal.Segment1)
and Fal.Segment2 = nvl(:P_State,Fal.Segment2)
and Fal.Segment3 = nvl(:P_City,Fal.Segment3)
and Fal.Segment4 = nvl(:P_Bilding,Fal.Segment4)
and Fal.Segment5 = nvl(:P_Room,Fal.Segment5)
and Glcc.Segment5 = nvl(:P_DEPARTMENT,Glcc.Segment5)
ORDER BY 1, 3
) where 1=1
No comments:
Post a Comment