---FA_S102_ASSET_ADDITIONS_V4_ModV1--
SELECT DISTINCT Al2.Period_Added_Mon_Yy Period_Added,
Al1.Book_Name,
Al1.Asset_Number,
Deprn_Expense_Account_Ccid,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Asset_Description,
Al1.Tag_Number,
Al1.Parent_Asset_Number,
Al1.Asset_Key,
TO_CHAR (Al1.Placed_In_Service, 'MM/DD/YYYY') Placed_In_Service,
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.Ytd_Deprn_Amt,
Al1.Life_Deprn_Amount,
Al1.Net_Book_Value,
Al1.Original_Cost_Usd,
Al1.Current_Cost_Usd,
-- NVL(Al1.Deprn_Amt_Usd,0) - NVL(Al1.Catchup_Deprn_Amt_Usd,0)
AL1.Current_Month_Deprn_Usd Current_Month_Deprn_Usd,
Al1.Catchup_Deprn_Amt_Usd,
Al1.Deprn_Amt_Usd,
Al1.Ytd_Deprn_Usd,
Al1.Life_Deprn_Usd,
Al1.Net_Book_Value_Usd,
To_char(sysdate, 'MM/DD/YYYY HH12:MM:SS AM') timestamp
FROM (SELECT DISTINCT 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, */
(SELECT FC.SEGMENT1 FROM Fa_Categories_B FC
WHERE FC.CATEGORY_ID=FADB.ASSET_CATEGORY_ID
AND ROWNUM=1) Asset_Category,
(SELECT DISTINCT FC.SEGMENT2 FROM Fa_Categories_B FC
WHERE FC.CATEGORY_ID=FADB.ASSET_CATEGORY_ID
AND ROWNUM=1) Asset_Subcategory,
/* 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.UserName Created_By_Id,
--Fndu.Description Created_By_Name,
Fadb.Owned_Leased Owned_Leased,
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,
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,
Rets.Date_Retired Date_Retired,
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,
FCBK.Deprn_Expense_Account_Ccid,
--(Fab.Original_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Original_Cost,
Famb.Original_Cost Original_Cost,
--(Fab.Cost / Fadb.Current_Units * Fadh.Units_Assigned) Current_Cost,
faMb.Cost 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,*/
Mcdt.deprn_reserve Ytd_Deprn_Amt,
(Mcdt.Deprn_Reserve / Fadb.Current_Units * Fadh.Units_Assigned) Life_Deprn_Amount,
(Fdsc.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Deprn_Amount,
(Lmdp.Deprn_Amount / Fadb.Current_Units * Fadh.Units_Assigned) Prev_Mth_Deprn_Amt,
(Fds.Impairment_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned) Impairment_Reserve,
/*((Famb.Cost - Mcdt.Deprn_Reserve - Fds.Impairment_Reserve) /
Fadb.Current_Units * Fadh.Units_Assigned) Net_Book_Value, */
FAMB.COST - MCDT.Deprn_Reserve Net_Book_Value,
--(Fab.Adjusted_Cost / Fadb.Current_Units * Fadh.Units_Assigned) Adjusted_Cost,
Famb.Adjusted_Cost 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, */
FAB.Original_Cost 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, */
FAB.COST 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, */
Fds.Deprn_Reserve 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, */
Round((Fds.Deprn_Reserve / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2) 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((Mdsc.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Deprn_Amt_Usd, */
FDSC.DEPRN_RESERVE 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((Nvl(Fdsc.Deprn_Adjustment_Amount, 0) - Nvl(Fdsc.Bonus_Deprn_Adjustment_Amount, 0) / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Nvl(Mdsc.Deprn_Adjustment_Amount, 0) - Nvl(Mdsc.Bonus_Deprn_Adjustment_Amount, 0) / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Nvl(Fdsc.Deprn_Adjustment_Amount, 0) - Nvl(Fdsc.Bonus_Deprn_Adjustment_Amount, 0) / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Catchup_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((Lmdp.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Lmdu.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Lmdp.Deprn_Amount / Fadb.Current_Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Prev_Mth_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(((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,*/
(Fab.Cost - Fds.Deprn_Reserve) Net_Book_Value_Usd,
--Fab.Adjustment_Required_Status Adj_Required_Status,
Fab.Capitalize_Flag Capitalize,
Fab.Depreciate_Flag Depreciate,
FDSC.DEPRN_AMOUNT Current_Month_Deprn_Usd,
Fab.Unit_Of_Measure Unit_Of_Measure,
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
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_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,
DEPRN_AMOUNT DEPRN_AMOUNT
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,
Ldep.Deprn_Adjustment_Amount,
Ldep.Bonus_Deprn_Adjustment_Amount,
Ldep.DEPRN_RESERVE DEPRN_RESERVE
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 Pdep.Asset_Id Asset_Id,
Pdep.Book_Type_Code Book_Type_Code,
Pdep.Deprn_Amount Deprn_Amount
FROM Fa_Deprn_Summary Pdep
WHERE Pdep.Period_Counter =
(SELECT MAX(Period_Counter) - 1
FROM Fa_Deprn_Periods Pdrn
WHERE Pdrn.Book_Type_Code = Pdep.Book_Type_Code
AND Pdrn.Deprn_Run = 'Y')) Lmdp,
(SELECT Mdep.Asset_Id Asset_Id,
Mdep.Book_Type_Code Book_Type_Code,
Mdep.Deprn_Amount Deprn_Amount,
Mdep.Deprn_Adjustment_Amount,
Mdep.Bonus_Deprn_Adjustment_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 Pdpu.Asset_Id Asset_Id,
Pdpu.Book_Type_Code Book_Type_Code,
Pdpu.Deprn_Amount Deprn_Amount
FROM Fa_Mc_Deprn_Summary Pdpu
WHERE Pdpu.Period_Counter =
(SELECT MAX(Period_Counter) - 1
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Pdpu.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y')) Lmdu,
(SELECT Ret.Asset_Id Asset_Id,
Ret.Book_Type_Code Book_Type_Code,
MAX(Ret.Date_Retired) Date_Retired
FROM Fa_Retirements Ret
GROUP BY Ret.Asset_Id, Ret.Book_Type_Code) Rets,
(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
-- ,FMBS.DEPRN_RESERVE DEPRN_RESERVE
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
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 fm.method_id = fab.method_id
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
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 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 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 ROWNUM=1
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 fcbk.category_id = fadb.asset_category_id
AND fcbk.book_type_code = fab.book_type_code
--AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
--AND Glc3.Code_Combination_Id = Fada.Deprn_Reserve_Account_Ccid
AND Glc2.code_combination_id = fcbk.asset_cost_account_ccid
AND Glc3.code_combination_id = fcbk.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 Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Rets.Asset_Id(+) = Fab.Asset_Id
AND Rets.Book_Type_Code(+) = Fab.Book_Type_Code
AND Lmdp.Asset_Id(+) = Fab.Asset_Id
AND Lmdp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Lmdu.Asset_Id(+) = Fab.Asset_Id
AND Lmdu.Book_Type_Code(+) = Fab.Book_Type_Code
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 ( case when Fab.Book_Type_Code in (:p_book_type_code) then 1
when (COALESCE(NULL,:p_book_type_code) is NULL ) then 1
end = 1 )
) Al1,
(SELECT Bks.Asset_Id Asset_Id,
Bks.Book_Type_Code Book_Name,
Bks.Date_Effective Addition_Date,
substr(Dpr.Period_Name,1,4)||substr(Dpr.Period_name,-2) Period_Added_Mon_Yy,
Dpr.Fiscal_Year Year_Yyyy
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 to_char(to_date(Dpr.Period_name,'MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'),'MON-YY',
'NLS_DATE_LANGUAGE=ENGLISH') = nvl(:p_period,Dpr.Period_Name) */
/* AND To_char(Dpr.Period_Name,'MON-YY','NLS_DATE_LANGUAGE=ENGLISH') = nvl(:p_period,Dpr.Period_Name) */
AND Bks.Transaction_Header_Id_In =
(SELECT distinct MIN(Bks2.Transaction_Header_Id_In) over()
FROM Fa_Books Bks2
WHERE Bks2.Book_Type_Code = Bks.Book_Type_Code
AND Bks2.Asset_Id = Bks.Asset_Id)) Al2
WHERE Al2.Asset_Id = Al1.Asset_Id
AND Al2.Book_Name = Al1.Book_Name
AND ( case when Al1.Asset_Number in (:p_asset_num) then 1
when (COALESCE(NULL,:p_asset_num) is NULL ) then 1
end = 1 )
--and AL1.Asset_Number IN ('100000017','100000014','100000064')
Order By Al1.Asset_Number
No comments:
Post a Comment