FA Onsemi Asset Roll Forward Report
WITH Allprd AS
(SELECT substr(a.Period_Name,1,4)||a.period_year period_name
FROM (SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Periods,
Gp.Period_Name,
Gp.START_DATE,
Gp.Period_year
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5') a,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Start_Period,
Gp.START_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :P_PSTART) Starting,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) End_Period,
Gp.END_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :P_PEND ) Ending
WHERE a.START_DATE BETWEEN Starting.START_DATE AND Ending.END_DATE)
select
substr(book_name,1,3) Country,
book_name,
sum(COST_BEGINING) COST_BEGINING,
SUM(Cost_Addition_Usd) Cost_Addition_Usd,
sum(COST_ADJ_ADDITION_USD) COST_ADJ_ADDITION_USD,
sum(COST_ADJ_XFER_IN_USD) COST_ADJ_XFER_IN_USD,
SUM(Cost_Xfer_In_Usd) Cost_Xfer_In_Usd,
sum(COST_XFER_OUT_USD) COST_XFER_OUT_USD,
sum(COST_RETIRED_USD) COST_RETIRED_USD,
sum(COST_ENDING) COST_ENDING,
sum(BEGINNING_DEPRN) BEGINNING_DEPRN,
sum(CY_DEPRN) CY_DEPRN,
SUM(Init_Ad_Addition_Usd) Init_Ad_Addition_Usd,
SUM(Init_Ad_Xfer_In_Usd) Init_Ad_Xfer_In_Usd,
sum(AD_XFER_OUT_USD) AD_XFER_OUT_USD,
sum(AD_DISPOSAL_USD) AD_DISPOSAL_USD,
sum(LTD_DEPRN) LTD_DEPRN,
sum(COST_ENDING) - (sum(COST_BEGINING) + SUM(Cost_Addition_Usd) + sum(COST_ADJ_ADDITION_USD) + sum(COST_ADJ_XFER_IN_USD)
+ SUM(Cost_Xfer_In_Usd) + sum(COST_XFER_OUT_USD) + sum(COST_RETIRED_USD)) COST_CHECK,
sum(LTD_DEPRN) - (sum(BEGINNING_DEPRN) + sum(CY_DEPRN) + SUM(Init_Ad_Addition_Usd) + SUM(Init_Ad_Xfer_In_Usd) + sum(AD_XFER_OUT_USD)
+ sum(AD_DISPOSAL_USD)) DEPRN_CHECK
from(
/*start of Adjustment */
SELECT adj.book_name,
/*SUM(deprn.deprn_adj_usd) deprn_adj_usd,*/
sum(0) COST_BEGINING,
sum(0) Cost_Addition_Usd,
NVL(SUM(decode((decode(adj.asset_key, 'ICXFR', 'INTERCO', 'ADDITION')), 'ADDITION', adj.adj_amount_usd
)),0) + NVL(SUM(decode(adj.period_name,adj.adnp,0,decode((decode(adj.asset_key, 'ICXFR', 'INTERCO', 'ADDITION')), 'INTERCO', adj.adj_amount_usd))
),0) cost_adj_addition_usd,
SUM(decode(adj.period_name,adj.adnp,decode((decode(adj.asset_key, 'ICXFR', 'INTERCO', 'ADDITION')), 'INTERCO', adj.adj_amount_usd),0)
) cost_adj_xfer_in_usd,
SUM(0) Cost_Xfer_In_Usd,
sum(0) COST_XFER_OUT_USD,
sum(0) COST_RETIRED_USD,
sum(0) COST_ENDING,
sum(0) BEGINNING_DEPRN,
sum(0) CY_DEPRN,
sum(0) Init_Ad_Addition_Usd,
sum(0) Init_Ad_Xfer_In_Usd,
sum(0) AD_XFER_OUT_USD,
sum(0) AD_DISPOSAL_USD,
sum(0) LTD_DEPRN
FROM (SELECT Fadj.Book_Type_Code Book_Name,
Fadd.Asset_Id,
fask.segment3 asset_key,
Prd.Period_Name,
(SELECT 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)
AND Bks.asset_id = fadd.asset_id and Bks.Book_Type_Code = fadj.book_type_code) Adnp,
(
CASE
WHEN fadj.debit_credit_flag = 'DR' THEN
1
ELSE
- 1
END
) * nvl(
CASE
WHEN lgr.currency_code = 'USD' THEN
fadj.adjustment_amount
WHEN Substr(Fab.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((fadj.adjustment_amount *
Rat.Conversion_Rate),
2)
ELSE
famc.adjustment_amount
END, 0) adj_amount_usd,
0 Deprn_Adj_Usd
FROM Fa_Adjustments Fadj,
Fa_Additions_b Fadd,
fa_asset_keywords fask,
Fa_Additions_Tl Fadt,
Fa_Mc_Adjustments Famc,
Fa_Categories_b Fcb,
Fa_Deprn_Periods Prd,
fa_book_controls ctr,
Fa_Books Fab,
gl_ledgers lgr,
Gl_Daily_Rates Rat,
Allprd
WHERE Fadd.Asset_Id = Fadj.Asset_Id
AND fadd.asset_key_ccid = fask.code_combination_id (+)
AND Famc.Asset_Id(+) = Fadj.Asset_Id
AND Famc.Book_Type_Code(+) = Fadj.Book_Type_Code
AND Famc.Adjustment_Type(+) = Fadj.Adjustment_Type
AND Famc.Transaction_Header_Id(+) = Fadj.Transaction_Header_Id
AND Famc.Source_Type_Code(+) = 'ADJUSTMENT'
AND Fadj.Source_Type_Code = 'ADJUSTMENT'
AND Fadj.Adjustment_Type = 'COST'
AND Famc.Adjustment_Type(+) = 'COST'
AND famc.distribution_id(+) = Fadj.distribution_id
AND Prd.Book_Type_Code = Fadj.Book_Type_Code
AND Prd.Period_Counter = Fadj.Period_Counter_Adjusted
AND Fab.Asset_Id = Fadj.Asset_Id
AND Fab.Book_Type_Code = Fadj.Book_Type_Code
AND Fab.Transaction_Header_Id_In = Fadj.Transaction_Header_Id
AND ctr.book_type_code = fab.book_type_code
AND lgr.ledger_id = ctr.set_of_books_id
AND Fcb.Category_Id = Fadd.Asset_Category_Id
AND Fadt.Asset_Id = Fab.Asset_Id
AND lgr.ledger_id = ctr.set_of_books_id
AND Fadt.Language = 'US'
AND Rat.Conversion_Type(+) = '1001'
AND Rat.From_Currency(+) = Lgr.Currency_Code
AND Rat.To_Currency(+) = 'USD'
AND Rat.Conversion_Date(+) = Trunc(Prd.calendar_period_open_date)
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
--%IF ':PARM01' = '' %THENDO;
--AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like '%:PARM03%')
--%ELSE;
--AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like '%:PARM01%:PARM03%')
--%ENDIF;
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END
AND Prd.Period_Name = Allprd.Period_Name) Adj,
(SELECT Book_Name,
Asset_Id,
Period_Name,
0 Adj_Amount_Usd,
SUM(Deprn_Adj_Usd) Deprn_Adj_Usd
FROM (SELECT Fds.Book_Type_Code Book_Name,
Fds.Asset_Id Asset_Id,
Fdp.Period_Name,
Nvl(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,
0) Deprn_Adj_Usd
FROM Fa_Deprn_Periods Fdp,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadd,
Fa_Categories_b Fcb,
Fa_Book_Controls Ctr,
Gl_Ledgers Lgr,
Gl_Daily_Rates Rat ,
Allprd
WHERE 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 Fadd.Asset_Id = Fds.Asset_Id
AND Ctr.Book_Type_Code = Fds.Book_Type_Code
AND Fcb.Category_Id = Fadd.Asset_Category_Id
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.calendar_period_open_date)
--%IF ':PARM01' = '' %THENDO;
--AND (Fds.book_type_code LIKE '%:PARM02%' OR Fds.book_type_code like '%:PARM03%')
--%ELSE;
--AND (Fds.book_type_code LIKE '%:PARM01%:PARM02%' OR Fds.book_type_code like '%:PARM01%:PARM03%')
AND ( Fds.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fds.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
--%ENDIF;
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END
AND Fdp.Period_Name = Allprd.Period_Name
UNION ALL
SELECT Fmds.Book_Type_Code Book_Name,
Fmds.Asset_Id Asset_Id,
Fdp.Period_Name,
Nvl(Fmds.Deprn_Adjustment_Amount, 0) Deprn_Adj_Usd
FROM Fa_Deprn_Periods Fdp,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadd,
Fa_Categories_b Fcb ,
Allprd
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 Fadd.Asset_Id = Fmds.Asset_Id
AND Fcb.Category_Id = Fadd.Asset_Category_Id
--%IF ':PARM01' = '' %THENDO;
--AND (Fds.book_type_code LIKE '%:PARM02%' OR Fds.book_type_code like '%:PARM03%')
--%ELSE;
--AND (Fds.book_type_code LIKE '%:PARM01%:PARM02%' OR Fds.book_type_code like '%:PARM01%:PARM03%')
AND ( Fds.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fds.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
--%ENDIF;
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END
AND Fdp.Period_Name = Allprd.Period_Name
)
GROUP BY Book_Name, Asset_Id, Period_Name) Deprn
WHERE Adj.Book_Name = Deprn.Book_Name(+)
AND Adj.Asset_Id = Deprn.Asset_Id(+)
AND Adj.Period_Name = Deprn.Period_Name(+)
GROUP BY Adj.Book_Name
/*end of Adjustment */
/*start of Addition */
union all
SELECT Dtl.Book_Name,
sum(0) COST_BEGINING,
SUM(Decode((Decode(Dtl.Asset_Key, 'ICXFR', 'INTERCO', 'ADDITION')),
'ADDITION',
Nvl(Dtl.Original_Cost_Usd, Dtl.Original_Cost))) Cost_Addition_Usd,
sum(0) COST_ADJ_ADDITION_USD,
sum(0) COST_ADJ_XFER_IN_USD,
SUM(Decode((Decode(Dtl.Asset_Key, 'ICXFR', 'INTERCO', 'ADDITION')),
'INTERCO',
Nvl(Dtl.Original_Cost_Usd, Dtl.Original_Cost))) Cost_Xfer_In_Usd,
sum(0) COST_XFER_OUT_USD,
sum(0) COST_RETIRED_USD,
sum(0) COST_ENDING,
sum(0) BEGINNING_DEPRN,
sum(0) CY_DEPRN,
SUM((Decode(((Decode(Dtl.Asset_Key, 'ICXFR', 'INTERCO', 'ADDITION'))),
'ADDITION',
Nvl(Deprn.Init_Reserve_Usd, Deprn.Init_Reserve_Amt),
0))) Init_Ad_Addition_Usd,
SUM((Decode(((Decode(Dtl.Asset_Key, 'ICXFR', 'INTERCO', 'ADDITION'))),
'INTERCO',
Nvl(Deprn.Init_Reserve_Usd, Deprn.Init_Reserve_Amt),
0))) Init_Ad_Xfer_In_Usd,
sum(0) AD_XFER_OUT_USD,
sum(0) AD_DISPOSAL_USD,
sum(0) LTD_DEPRN
FROM (SELECT Fab.Book_Type_Code Book_Name,
Fadb.Asset_Number Asset_Number,
Fab.Asset_Id Asset_Id,
Fask.Segment3 Asset_Key,
sum(nvl((Fab.Original_Cost / Fadb.Current_Units * Fadh.Units_Assigned),0)) Original_Cost,
/*sum(nvl((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),0)) Original_Cost_Usd,*/
sum(nvl((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((Fab.Original_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Original_Cost / Fadb.Current_Units *
Fadh.Units_Assigned),
2)
END
ELSE
NULL
END),0)) Original_Cost_Usd
FROM Fa_Additions_b Fadb,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Fa_Asset_Keywords Fask,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
-- Fa_Distribution_Accounts Fada,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Calendar_Periods Fcp,
Fa_Categories_b Fcb,
(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
WHERE Fadb.Asset_Id = Fab.Asset_Id
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.Book_Type_Code=Fadh.Book_Type_Code --Added
AND Fcb.Category_Id = Fadb.Asset_Category_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_Number in('97226000X' /*'96874330','96874351'*/) ---- Remove this .
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
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 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 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
--%IF ':PARM01' = '' %THENDO;
--AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like '%:PARM03%')
--%ELSE;
--AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like --'%:PARM01%:PARM03%')
--%ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END
GROUP BY Fab.Book_Type_Code,
Fadb.Asset_Number,
Fab.Asset_Id,
Fask.Segment3) Dtl,
(WITH Allprd AS
(SELECT substr(a.Period_Name,1,4)||a.period_year period_name
FROM (SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Periods,
Gp.Period_Name,
Gp.START_DATE,
Gp.Period_year
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5') a,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Start_Period,
Gp.START_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :P_PSTART) Starting,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) End_Period,
Gp.END_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :P_PEND ) Ending
WHERE a.START_DATE BETWEEN Starting.START_DATE AND Ending.END_DATE
)
SELECT Fab.Asset_Id Asset_Id,
Fab.Book_Type_Code Book_Name,
Dpr.Period_Name Period,
Allprd.Period_Name All_Prd,
Dpr.Fiscal_Year YEAR
FROM Fa_Books Fab, Fa_Deprn_Periods Dpr
, Allprd
WHERE Fab.Book_Type_Code = Dpr.Book_Type_Code
AND Fab.Date_Effective BETWEEN Dpr.Period_Open_Date AND
Nvl(Dpr.Period_Close_Date, SYSDATE + 1)
AND Fab.Transaction_Header_Id_In =
(SELECT MIN(Bks2.Transaction_Header_Id_In)
FROM Fa_Books Bks2
WHERE Bks2.Book_Type_Code = Fab.Book_Type_Code
AND Bks2.Asset_Id = Fab.Asset_Id)
AND Allprd.Period_Name = Dpr.Period_Name
-- %IF ':PARM01' = '' %THENDO;
-- AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like '%:PARM03%')
-- %ELSE;
-- AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like '%:PARM01%:PARM03%')
-- %ENDIF
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)) Prd,
(SELECT Fds.Book_Type_Code Book_Name,
Fds.Asset_Id Asset_Id,
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*/
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), 2)
ELSE
CASE
WHEN Fds.Deprn_Source_Code = 'BOOKS' THEN
Fds.Deprn_Reserve
ELSE
0
END
END
ELSE
0
END Init_Reserve_Usd
FROM Fa_Deprn_Periods Fab,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadb,
Fa_Book_Controls Ctr,
Gl_Ledgers Lgr,
Gl_Daily_Rates Rat,
Fa_Categories_b Fcb
WHERE Fab.Book_Type_Code = Fds.Book_Type_Code
AND Fab.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 Fadb.Asset_Number in('97226000X' /*'96874330','96874351'*/) -- Remove this.
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(Fab.Period_Open_Date)
AND Fds.Deprn_Source_Code = 'BOOKS'
AND Fcb.Category_Id = Fadb.Asset_Category_Id
-- %IF ':PARM01' = '' %THENDO;
-- AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like --'%:PARM03%')
-- %ELSE;
-- AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR --Fab.book_type_code like '%:PARM01%:PARM03%')
--- %ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END ) Deprn
WHERE Dtl.Asset_Id = Prd.Asset_Id
AND Dtl.Book_Name = Prd.Book_Name
AND Dtl.Asset_Id = Deprn.Asset_Id
AND Dtl.Book_Name = Deprn.Book_Name
GROUP BY Dtl.Book_Name
/*end of Addition */
/*start of Retirement*/
union all
SELECT Book_Name,
sum(0) COST_BEGINING,
SUM(0) Cost_Addition_Usd,
sum(0) COST_ADJ_ADDITION_USD,
sum(0) COST_ADJ_XFER_IN_USD,
SUM(0) Cost_Xfer_In_Usd,
SUM(Decode(Decode(Al1.Retirement_Categ,
'Intercompany Transfers',
'INTERCO',
'RETIREMENT'),
'INTERCO',
(-1 * Nvl(Nvl(Al1.Cost_Retired_Usd, Al1.Cost_Retired), 0)))) Cost_Xfer_out_usd,
SUM(Decode(Decode(Al1.Retirement_Categ,
'Intercompany Transfers',
'INTERCO',
'RETIREMENT'),
'RETIREMENT',
(-1 * Nvl(Nvl(Al1.Cost_Retired_Usd, Al1.Cost_Retired), 0)))) Cost_Retired_Usd,
sum(0) COST_ENDING,
sum(0) BEGINNING_DEPRN,
sum(0) CY_DEPRN,
SUM(0) Init_Ad_Addition_Usd,
SUM(0) Init_Ad_Xfer_In_Usd,
SUM(Decode(Decode(Al1.Retirement_Categ,
'Intercompany Transfers',
'INTERCO',
'RETIREMENT'),
'INTERCO',
(-1 * Nvl(Nvl(Al1.Reserve_Ret_Usd_Calc, Al1.Reserve_Retired), 0)))) Ad_Xfer_Out_Usd,
SUM(Decode(Decode(Al1.Retirement_Categ,
'Intercompany Transfers',
'INTERCO',
'RETIREMENT'),
'RETIREMENT',
(-1 *
Nvl(Nvl(Al1.Reserve_Ret_Usd_Calc, Al1.Reserve_Retired), 0)))) Ad_Disposal_Usd,
sum(0) LTD_DEPRN
FROM (SELECT Far.Book_Type_Code Book_Name,
Far.Cost_Retired Cost_Retired,
((Nvl(Far.Cost_Retired, 0) - Nvl(Far.Nbv_Retired, 0))) Reserve_Retired,
/*((CASE
WHEN Substr(Far.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Famr.Cost_Retired
ELSE
0
END)) Cost_Retired_Usd,*/
((CASE
WHEN Substr(Far.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Far.Cost_Retired
ELSE
0
END)) Cost_Retired_Usd,
((CASE
WHEN Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code NOT LIKE '%FA ENP%' THEN
0
WHEN Substr(Far.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round((Far.Cost_Retired * Rat.Conversion_Rate), 2)
ELSE
Nvl(Famr.Cost_Retired, Far.Cost_Retired)
END)) Cost_Ret_Usd_Calc,
/*((CASE
WHEN Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code NOT LIKE '%FA ENP%' THEN
0
WHEN Substr(Far.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round(((Far.Cost_Retired - Far.Nbv_Retired) *
Rat.Conversion_Rate),
2)
ELSE
Nvl((Famr.Cost_Retired - Famr.Nbv_Retired),
(Far.Cost_Retired - Far.Nbv_Retired))
END)) Reserve_Ret_Usd_Calc,*/
((CASE
WHEN Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code NOT LIKE '%FA ENP%' THEN
0
WHEN Substr(Far.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN
Round(((Far.Cost_Retired - Far.Nbv_Retired) *
Rat.Conversion_Rate),
2)
ELSE
Nvl((Far.Cost_Retired - Far.Nbv_Retired),
(Far.Cost_Retired - Far.Nbv_Retired))
END)) Reserve_Ret_Usd_Calc,
CASE
WHEN Substr(Far.Retirement_Type_Code, 1, 3) = 'ICO' THEN
'Intercompany Transfers'
WHEN Falt.Meaning = 'SCRAPPED' THEN
'Retirements'
WHEN Falt.Meaning = 'EXTSALE' THEN
'Sales to Third Parties'
ELSE
'Other'
END Retirement_Categ
FROM Fa_Retirements Far,
Fa_Mc_Retirements Famr,
Fa_Transaction_Headers Thdr,
Fa_Book_Controls Fabc,
Fa_Additions_b Fadb,
-- Fa_Lookups_Tl Falt, Infosys: Changed table name to match oracle fusion
FND_LOOKUP_VALUES Falt,
Fa_Books Fab,
Gl_Daily_Rates Rat,
Gl_Ledgers Lgr,
Gl_Ledgers Lgs,
Fa_Deprn_Periods Glp,
Fa_Categories_b Fcb,
Allprd
WHERE Far.Book_Type_Code = Fabc.Book_Type_Code
AND Far.Asset_Id = Fadb.Asset_Id
AND Far.Retirement_Type_Code = Falt.Lookup_Code(+)
AND Famr.Retirement_Id(+) = Far.Retirement_Id
AND Far.Asset_Id = Fab.Asset_Id
AND Far.Book_Type_Code = Fab.Book_Type_Code
AND Falt.Language(+) = 'US'
--AND Falt.Lookup_Type(+) = 'RETIREMENT'
AND Falt.Lookup_Type(+) = 'FA_RETIREMENT_TYPE' -- Infosys Modified to match value in oracle fusion
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fab.Date_Ineffective IS NULL
AND Far.Date_Effective BETWEEN Glp.Period_Open_Date AND
Nvl(Glp.Period_Close_Date, (SYSDATE + 1))
AND Far.Book_Type_Code = Glp.Book_Type_Code
AND Far.Status = 'PROCESSED'
AND Rat.Conversion_Type = 'Corporate'
AND Rat.From_Currency =
Decode(Lgr.Currency_Code, 'USD', 'BMD', Lgr.Currency_Code)
AND Rat.To_Currency = 'USD'
AND Rat.Conversion_Date = Trunc(Far.Date_Effective)
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Thdr.Transaction_Header_Id = Far.Transaction_Header_Id_In
AND Lgs.Ledger_Category_Code(+) = 'PRIMARY'
AND Substr(Lgs.Name(+), 1, 3) =
Substr(Far.Retirement_Type_Code, 8, 3)
AND Allprd.Period_Name = Glp.Period_Name
--%IF ':PARM01' = '' %THENDO;
--AND (Far.book_type_code LIKE '%:PARM02%' OR Far.book_type_code like '%:PARM03%')
--%ELSE;
-- AND (Far.book_type_code LIKE '%:PARM01%:PARM02%' OR Far.book_type_code like -'--%:PARM01%:PARM03%')
-- %ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND Fcb.Segment1 = CASE WHEN :P_ASTCAT IS NULL THEN Fcb.Segment1 ELSE :P_ASTCAT END
AND Fcb.Segment2 = CASE WHEN :P_ASTSCAT IS NULL THEN Fcb.Segment2 ELSE :P_ASTSCAT END
) Al1
GROUP BY Book_Name
/*end of Retirement*/
/*start of Begin BAL*/
union all
SELECT a.Book_Name,
SUM(a.Current_Cost_Usd) COST_BEGINING,
SUM(0) Cost_Addition_Usd,
sum(0) COST_ADJ_ADDITION_USD,
sum(0) COST_ADJ_XFER_IN_USD,
SUM(0) Cost_Xfer_In_Usd,
sum(0) COST_XFER_OUT_USD,
sum(0) COST_RETIRED_USD,
sum(0) COST_ENDING,
SUM(a.Life_Deprn_Usd) BEGINNING_DEPRN
,
sum(0) CY_DEPRN,
SUM(0) Init_Ad_Addition_Usd,
SUM(0) Init_Ad_Xfer_In_Usd,
sum(0) AD_XFER_OUT_USD,
sum(0) AD_DISPOSAL_USD,
sum(0) LTD_DEPRN
FROM (SELECT Book_Name,
SUM(Nvl(Current_Cost_Usd, Current_Cost)) Current_Cost_Usd,
SUM(Nvl(Life_Deprn_Usd, Life_Deprn_Amt)) Life_Deprn_Usd
FROM (SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */ Fab.Book_Type_Code Book_Name,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn_Amt,
(CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END) Current_Cost_Usd,
CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned * Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdpmc,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE 1=1 AND CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code =
Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
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 1=1
AND Ck.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
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 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code =
Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt ,
Fa_Mc_Books 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,
-- %IF :P_ASTCAT = '' %THENDO;
-- %IF :P_ASTSCAT = '' %THENDO;
-- %ELSE;
Fa_Categories_b Fcb
-- %ENDIF;
-- %ELSE;
-- ,Fa_Categories_b Fcb
-- %ENDIF;
WHERE Fadb.Asset_Id = Fab.Asset_Id
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_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 Fadb.asset_number='97225005X' --Remove
AND Fadh.Book_Type_Code=Fab.Book_Type_Code --added
AND Faah.Book_Type_Code=Fab.Book_Type_Code --added
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 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 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 Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.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) >=
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
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 Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code =
Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
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 1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code =
Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE
1=1
AND Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code =
Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
--%IF :P_ASTCAT = '' %THENDO;
--%IF :P_ASTSCAT = '' %THENDO;
--%ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
--%ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%IF :P_ENTITY = '' %THENDO;
--AND (Fab.book_type_code LIKE :P_BOOKTYPE1 OR Fab.book_type_code like :P_BOOKTYPE2)
--%ELSE;
--AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like '%:PARM01%:PARM03%')
--%ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
--%IF :P_ASTCAT = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF :P_ASTSCAT = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
)
GROUP BY Book_Name
UNION ALL
SELECT Book_Name,
SUM(Nvl(Current_Cost_Usd, Current_Cost)) Current_Cost_Usd,
SUM(Nvl(Life_Deprn_Usd, Life_Deprn_Amt)) Life_Deprn_Usd
FROM (SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */ Fab.Book_Type_Code Book_Name,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
0 Life_Deprn_Amt,
CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
0 Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Mc_Books 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
--%IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
--%ELSE;
,Fa_Categories_b Fcb
-- %ENDIF;
-- %ELSE;
-- ,Fa_Categories_b Fcb
-- %ENDIF;
WHERE 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 Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
AND Fadh.Book_Type_Code=Fab.Book_Type_Code --added
AND Faah.Book_Type_Code=Fab.Book_Type_Code --added
--AND Fadb.asset_number='97225005X' --Remove
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
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 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) >=
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = 'DEC-'||to_char(to_number(substr(:P_PSTART,5,6))-1))
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
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
-- %IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%ENDIF;
-- %ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
-- %ENDIF;
--%IF ':PARM01' = '' %THENDO;
--AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like '%:PARM03%')
--%ELSE;
--AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like --'%:PARM01%:PARM03%')
--%ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
)
GROUP BY Book_Name ) a
GROUP BY a.Book_Name
/*end of Begin BAL*/
/*start of End BAL*/
union all
SELECT a.Book_Name,
SUM(0) COST_BEGINING,
SUM(0) Cost_Addition_Usd,
sum(0) COST_ADJ_ADDITION_USD,
sum(0) COST_ADJ_XFER_IN_USD,
SUM(0) Cost_Xfer_In_Usd,
sum(0) COST_XFER_OUT_USD,
sum(0) COST_RETIRED_USD,
SUM(a.Current_Cost_Usd) COST_ENDING,
SUM(0) BEGINNING_DEPRN,
sum(0) CY_DEPRN,
SUM(0) Init_Ad_Addition_Usd,
SUM(0) Init_Ad_Xfer_In_Usd,
sum(0) AD_XFER_OUT_USD,
sum(0) AD_DISPOSAL_USD,
SUM(a.Life_Deprn_Usd) LTD_DEPRN
FROM (SELECT Book_Name,
SUM(Nvl(Current_Cost_Usd, Current_Cost)) Current_Cost_Usd,
SUM(Nvl(Life_Deprn_Usd, Life_Deprn_Amt)) Life_Deprn_Usd,
SUM(nvl(Ytd_Deprn_Usd,0)) Ytd_Deprn_Usd
FROM (SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */ Fab.Book_Type_Code Book_Name,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn_Amt,
(CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned),
2)
END
ELSE
NULL
END) Current_Cost_Usd,
CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Deprn_Reserve / Faah.Units *
Fadh.Units_Assigned),
2)
END
ELSE
NULL
END Life_Deprn_Usd,
CASE
WHEN (Fabc.Book_Class <> 'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fds.Ytd_Deprn / Faah.Units *
Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Mcdt.Ytd_Deprn / Faah.Units *
Fadh.Units_Assigned),
2)
ELSE
Round((Fds.Ytd_Deprn / Faah.Units *
Fadh.Units_Assigned),
2)
END
ELSE
NULL
END Ytd_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdpmc,
(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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code =
Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code =
Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
Fa_Mc_Books 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
-- %IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
,Fa_Categories_b Fcb
-- %ENDIF;
--- %ELSE;
-- ,Fa_Categories_b Fcb
-- %ENDIF;
WHERE Fadb.Asset_Id = Fab.Asset_Id
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_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_number='97225005X' -- Remove
AND Fadh.Book_Type_Code=Fab.Book_Type_Code --added
AND Faah.Book_Type_Code=Fab.Book_Type_Code --added
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 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 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 Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.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) >=
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
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 Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code =
Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code =
Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
-- %IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
-- %ENDIF;
-- %ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
-- %IF ':PARM01' = '' %THENDO;
--AND (Fab.book_type_code LIKE ':PARM02%' OR Fab.book_type_code like ':PARM03%')
-- %ELSE;
-- AND (Fab.book_type_code LIKE ':PARM01:PARM02%' OR Fab.book_type_code like ':PARM01:PARM03%')
-- %ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END )
GROUP BY Book_Name
UNION ALL
SELECT Book_Name,
SUM(Nvl(Current_Cost_Usd, Current_Cost)) Current_Cost_Usd,
SUM(Nvl(Life_Deprn_Usd, Life_Deprn_Amt)) Life_Deprn_Usd,
SUM(0) Ytd_Deprn_Usd
FROM (SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */ Fab.Book_Type_Code Book_Name,
(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
0 Life_Deprn_Amt,
CASE
WHEN (Fabc.Book_Class <>'TAX' OR
(Fabc.Book_Class = 'TAX' AND
Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
WHEN Lgr.Currency_Code = 'USD' THEN
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned),
2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN
('612', '613', '624') THEN
Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned),
2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
0 Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Mc_Books 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
--%IF ':PARM04' = '' %THENDO;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
,Fa_Categories_b Fcb
--%ENDIF;
-- %ELSE;
-- ,Fa_Categories_b Fcb
--%ENDIF;
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 Fab.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
--AND Fadb.asset_number='97225005X' -- Remove
AND Fadh.Book_Type_Code=Fab.Book_Type_Code --added
AND Faah.Book_Type_Code=Fab.Book_Type_Code --added
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
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 = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
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 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) >=
(SELECT Period_Counter
FROM fa_deprn_periods Fadppar
WHERE Fadppar.Period_Name = (select substr(a.Period_Name,1,4)||a.period_year
from
Gl_Periods a
where
a.Period_Set_Name = '4-4-5'
and a.Period_Name = :P_PEND)
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
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
--%IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
-- %ENDIF;
-- %ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
-- %ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%IF ':PARM01' = '' %THENDO;
--AND (Fab.book_type_code LIKE '%:PARM02%' OR Fab.book_type_code like '%:PARM03%')
-- %ELSE;
--AND (Fab.book_type_code LIKE '%:PARM01%:PARM02%' OR Fab.book_type_code like --'%:PARM01%:PARM03%')
-- %ENDIF;
AND ( Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Fab.book_type_code LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
-- %IF ':PARM04' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment1 :SPARM04)
-- %ENDIF;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- AND (Fcb.Segment2 :SPARM05)
-- %ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
)
GROUP BY Book_Name) a
GROUP BY a.Book_Name
/*end of End BAL*/
/*start of Depreciation*/
union all
SELECT Book_Name,
SUM(0) Cost_Begining,
SUM(0) Cost_Addition_Usd,
SUM(0) Cost_Adj_Addition_Usd,
SUM(0) Cost_Adj_Xfer_In_Usd,
SUM(0) Cost_Xfer_In_Usd,
SUM(0) Cost_Xfer_Out_Usd,
SUM(0) Cost_Retired_Usd,
SUM(0) Cost_Ending,
SUM(0) Beginning_Deprn,
SUM(Deprn_Amt_Usd) Cy_Deprn,
SUM(0) Init_Ad_Addition_Usd,
SUM(0) Init_Ad_Xfer_In_Usd,
SUM(0) Ad_Xfer_Out_Usd,
SUM(0) Ad_Disposal_Usd,
SUM(0) Ltd_Deprn
FROM (SELECT DISTINCT Al1.Book_Name,
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,
SUM(CASE
WHEN Al2.Total_Units = 0 THEN
0
ELSE
Round((Al1.Init_Reserve_Amt / Al2.Total_Units *
Al2.Units_Assigned),
2)
END) Init_Reserve_Amt
FROM (SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */
Fds.Book_Type_Code Book_Name,
Fds.Asset_Id Asset_Id,
Fdp.Period_Name,
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 Fds.Deprn_Source_Code = 'BOOKS' THEN
Fds.Deprn_Reserve
ELSE
0
END Init_Reserve_Amt,
Fds.Deprn_Amount Deprn_Amt
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
-- %IF ':PARM04' = '' %THENDO;
---- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- ,Fa_Categories_b Fcb
-- %ENDIF;
-- %ELSE;
,Fa_Categories_b Fcb
-- %ENDIF;
WHERE 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.Calendar_Period_Open_Date)
--%IF ':PARM04' = '' %THENDO;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
--%ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
UNION ALL
SELECT /*+ FULL(Fds) PARALLEL(Fds,4) */
Fmds.Book_Type_Code Book_Name,
Fmds.Asset_Id Asset_Id,
Fdp.Period_Name,
Fmds.Deprn_Amount Deprn_Amt_Usd,
CASE
WHEN Fds.Deprn_Source_Code = 'BOOKS' THEN
Fds.Deprn_Reserve
ELSE
0
END Init_Reserve_Amt,
Fds.Deprn_Amount Deprn_Amt
FROM Fa_Deprn_Periods Fdp,
Fa_Deprn_Summary Fds,
Fa_Mc_Deprn_Summary Fmds,
Fa_Additions_b Fadb
-- %IF ':PARM04' = '' %THENDO;
-- %IF ':PARM05' = '' %THENDO;
-- %ELSE;
-- ,Fa_Categories_b Fcb
-- %ENDIF;
-- %ELSE;
,Fa_Categories_b Fcb
-- %ENDIF;
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
--%IF ':PARM04' = '' %THENDO;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
--%ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END) Al1,
(SELECT Fab.Book_Type_Code Book_Name,
Fadb.Current_Units Total_Units,
Fadh.Units_Assigned Units_Assigned,
Fab.Asset_Id Asset_Id
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Asset_Keywords Fask,
Fa_Distribution_History Fadh,
--Fa_Distribution_Accounts Fada,
Gl_Periods Glp1
--%IF ':PARM04' = '' %THENDO;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--,Fa_Categories_b Fcb
--%ENDIF;
--%ELSE;
,Fa_Categories_b Fcb
--%ENDIF;
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 Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Asset_Id = Fadh.Asset_Id
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND
Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fadh.Book_Type_Code = Fab.Book_Type_Code -- Added
AND Fab.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
--%IF ':PARM04' = '' %THENDO;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
-- AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
--%ELSE;
--AND Fcb.Category_Id = Fadb.Asset_Category_Id
--%ENDIF;
AND Fcb.Category_Id(+) = Fadb.Asset_Category_Id
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END
--%IF ':PARM04' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment1 :SPARM04)
--%ENDIF;
--%IF ':PARM05' = '' %THENDO;
--%ELSE;
--AND (Fcb.Segment2 :SPARM05)
--%ENDIF;
AND NVL(Fcb.Segment1,'XXXXX') = CASE WHEN :P_ASTCAT IS NULL THEN NVL(Fcb.Segment1,'XXXXX') ELSE :P_ASTCAT END
AND NVL(Fcb.Segment2,'XXXXX') = CASE WHEN :P_ASTSCAT IS NULL THEN NVL(Fcb.Segment2,'XXXXX') ELSE :P_ASTSCAT END) Al2,
Allprd
WHERE Al1.Book_Name = Al2.Book_Name
AND Al1.Asset_Id = Al2.Asset_Id
AND Allprd.Period_Name = Al1.Period_Name
--%IF '&PARM01' = '' %THENDO;
--AND (Al1.Book_Name LIKE '%&PARM02%' OR Al1.Book_Name like '%&PARM03%')
--%ELSE;
--AND (Al1.Book_Name LIKE '%&PARM01%&PARM02%' OR Al1.Book_Name like '%&PARM01%&PARM03%')
--%ENDIF;
AND ( Al1.Book_Name LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE1||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE1||'%' END)
OR
Al1.Book_Name LIKE (CASE WHEN :P_ENTITY IS NULL THEN '%'||:P_BOOKTYPE2||'%' ELSE '%'||:P_ENTITY||'%'||:P_BOOKTYPE2||'%' END)
)
AND (Al1.Deprn_Amt <> 0 OR Al1.Init_Reserve_Amt <> 0)
GROUP BY Al1.Book_Name)
GROUP BY Book_Name
/*end of Depreciation*/
)
group by substr(book_name,1,3),
book_name
order by substr(book_name,1,3),book_name
No comments:
Post a Comment