[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT ADJUSTMENT_CCID, CATEGORY_BOOKS_ACCOUNT, ROWID
FROM FA_BALANCES_REPORT_GT;
SELECT DISTINCT
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.Asset_Number,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid, /* StatReq */
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule, /* StatReq */
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
NVL(SUM (DECODE (BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RECLASS', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'REVALUATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TRANSFER', NVL(BAL.Amount,0), NULL)),
NVL(SUM (DECODE (BAL.Source_Type_Code,
'END', NVL(BAL.Amount,0), NULL)), 0) /*Bug# 9293000 */
FROM
FA_ASSET_HISTORY AH, /* StatReq */
fa_category_books cb,
FA_METHODS M, /* StatReq */
FA_BOOKS B, /* StatReq */
FA_BALANCES_REPORT_GT BAL,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS DHCC,
GL_CODE_COMBINATIONS AJCC,
FA_ADDITIONS AD1,
FA_LOOKUPS LU
WHERE AD.Asset_ID = BAL.Asset_ID
AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
AND B.ASSET_ID = AD.ASSET_ID
AND B.Book_Type_Code = c_book
AND B.Date_Ineffective is NULL
AND B.Transaction_Header_Id_Out is NULL
AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9)
AND M.METHOD_CODE = B.DEPRN_METHOD_CODE
AND AH.ASSET_ID = AD.ASSET_ID
/* Commented for bug #5015612. It was cousing No data found in Rxi: Asset Cost Balance Reprort.
-- Added for bug#4860955
and ah.transaction_header_id_in = ( select min(ah1.transaction_header_id_in)
from fa_asset_history ah1
where ah1.asset_id=ah.asset_id
and ah1.category_id=ah.category_id
and ah1.asset_type='CIP') */
and ah.category_id = cb.category_id
and cb.book_type_code = c_book
and decode(ah.asset_type,'CIP',cb.cip_cost_acct, cb.asset_cost_acct) = BAL.Category_Books_Account
-- Added for bug#4860955
and nvl(ah.date_effective,c_to_date) <= c_to_date
-- Added for bug #5015612
--bug 6668625
-- Bug#9384471 Modified for displaying correct category after reclass
and ah.transaction_header_id_in = ( select max(ah1.transaction_header_id_in)
from fa_asset_history ah1,
fa_category_books cb1
where ah1.asset_id=ah.asset_id
-- and ah1.category_id=ah.category_id Bug#9384471
and ah1.asset_type=decode(report_type,'CIP COST','CIP',
'COST','CAPITALIZED',ah1.asset_type)
and cb1.book_type_code=c_book
and cb1.category_id=ah1.category_id
and decode(report_type,'CIP COST',cb1.cip_cost_acct,cb1.asset_cost_acct)
= decode(report_type,'CIP COST',cb.cip_cost_acct,cb.asset_cost_acct)
and nvl(ah1.date_effective,c_to_date) <= c_to_date )
-- Commented for bug#4860955
-- AND c_to_date between ah.date_effective and nvl(ah.date_ineffective, c_to_date)
AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99) -- Added for Drill Down Report
AND LU.Lookup_Type (+) = 'ASSET TYPE'
AND LU.Lookup_Code (+) = AD.Asset_Type
GROUP BY
ad.asset_id,
dhcc.code_combination_id,
BAL.Category_Books_Account,
BAL.Cost_Account,
AD.ASSET_NUMBER,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id,
b.date_placed_in_service,
m.method_code,
b.life_in_months,
m.stl_method_flag,
m.rate_source_rule;
SELECT DISTINCT
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.Asset_Number,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid, /* StatReq */
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule, /* StatReq */
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
NVL(SUM (DECODE (BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RECLASS', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'REVALUATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TRANSFER', NVL(BAL.Amount,0), NULL)),
NVL(SUM (DECODE (BAL.Source_Type_Code,
'END', NVL(BAL.Amount,0), NULL)), 0) /*Bug# 9293000 */
FROM
FA_ASSET_HISTORY AH, /* StatReq */
fa_category_books cb,
FA_METHODS M, /* StatReq */
FA_MC_BOOKS B, /* StatReq */
FA_BALANCES_REPORT_GT BAL,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS DHCC,
GL_CODE_COMBINATIONS AJCC,
FA_ADDITIONS AD1,
FA_LOOKUPS LU
WHERE AD.Asset_ID = BAL.Asset_ID
AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
AND B.ASSET_ID = AD.ASSET_ID
AND B.Book_Type_Code = c_book
AND B.Date_Ineffective is NULL
AND B.Transaction_Header_Id_Out is NULL
AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9)
AND M.METHOD_CODE = B.DEPRN_METHOD_CODE
AND AH.ASSET_ID = AD.ASSET_ID
/* Commented for bug #5015612. It was cousing No data found in Rxi: Asset Cost Balance Reprort.
-- Added for bug#4860955
and ah.transaction_header_id_in = ( select min(ah1.transaction_header_id_in)
from fa_asset_history ah1
where ah1.asset_id=ah.asset_id
and ah1.category_id=ah.category_id
and ah1.asset_type='CIP') */
and ah.category_id = cb.category_id
and cb.book_type_code = c_book
and decode(ah.asset_type,'CIP',cb.cip_cost_acct, cb.asset_cost_acct) = BAL.Category_Books_Account
-- Added for bug#4860955
and nvl(ah.date_effective,c_to_date) <= c_to_date
-- Added for bug #5015612
--bug 6668625
and ah.transaction_header_id_in = ( select max(ah1.transaction_header_id_in)
from fa_asset_history ah1,
fa_category_books cb1
where ah1.asset_id=ah.asset_id
-- and ah1.category_id=ah.category_id Bug#9384471
and ah1.asset_type=decode(report_type,'CIP COST','CIP',
'COST','CAPITALIZED',ah1.asset_type)
and cb1.book_type_code=c_book
and cb1.category_id=ah1.category_id
and decode(report_type,'CIP COST',cb1.cip_cost_acct,cb1.asset_cost_acct)
= decode(report_type,'CIP COST',cb.cip_cost_acct,cb.asset_cost_acct)
and nvl(ah1.date_effective,c_to_date) <= c_to_date )
-- Commented for bug#4860955
-- AND c_to_date between ah.date_effective and nvl(ah.date_ineffective, c_to_date)
AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99) -- Added for Drill Down Report
AND LU.Lookup_Type (+) = 'ASSET TYPE'
AND LU.Lookup_Code (+) = AD.Asset_Type
AND B.set_of_books_id = h_sob_id
GROUP BY
ad.asset_id,
dhcc.code_combination_id,
BAL.Category_Books_Account,
BAL.Cost_Account,
AD.ASSET_NUMBER,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id,
b.date_placed_in_service,
m.method_code,
b.life_in_months,
m.stl_method_flag,
m.rate_source_rule;
SELECT DISTINCT
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.Asset_Number,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule, /* StatReq */
ad1.asset_number,
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
SUM (DECODE (BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'ADDITION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'ADJUSTMENT', NVL(BAL.Amount,0), NULL)), /*9293000 */
SUM (DECODE (BAL.Source_Type_Code,
'DEPRECIATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RECLASS', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RETIREMENT', -NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'REVALUATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TAX', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TRANSFER', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'END', NVL(BAL.Amount,0), NULL)) /*Bug# 9293000 */
FROM
FA_ASSET_HISTORY AH, /* StatReq */
FA_METHODS M, /* StatReq */
FA_BOOKS B, /* StatReq */
FA_BALANCES_REPORT_GT BAL,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS DHCC,
GL_CODE_COMBINATIONS AJCC,
FA_ADDITIONS AD1, -- This is only used to get Group Asset Number
FA_LOOKUPS LU,
FA_BOOKS BKG
WHERE AD.Asset_ID = BAL.Asset_ID
AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
AND B.Book_Type_Code = c_book /* StatReq */
AND B.ASSET_ID = AD.ASSET_ID /* StatReq */
AND B.Date_Ineffective is NULL /* StatReq */
AND B.Transaction_Header_Id_Out is NULL /* StatReq */
AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9) /* StatReq */
AND M.METHOD_CODE = B.DEPRN_METHOD_CODE /* StatReq */
AND AH.ASSET_ID = AD.ASSET_ID /* StatReq */
AND AH.DATE_EFFECTIVE <= c_to_date /* StatReq */
AND NVL(AH.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date /* StatReq */
AND LU.Lookup_Type (+) = 'ASSET TYPE'
AND LU.Lookup_Code (+) = AD.Asset_Type
AND BKG.Book_Type_Code = c_book /* Bug 16424827 */
AND BKG.ASSET_ID = AD.ASSET_ID
AND BKG.DATE_EFFECTIVE <= c_to_date
AND NVL(BKG.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date
AND AD1.Asset_ID(+) = BKG.GROUP_ASSET_ID
GROUP BY
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.ASSET_NUMBER,
AD.TAG_NUMBER,
AD.DESCRIPTION, ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule, /* StatReq */
ad1.asset_number;
SELECT DISTINCT
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.Asset_Number,
AD.tag_number,
AD.description,
ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule, /* StatReq */
ad1.asset_number,
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0), /* StatReq */
SUM (DECODE (BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'ADDITION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'ADJUSTMENT', NVL(BAL.Amount,0), NULL)), /*9293000 */
SUM (DECODE (BAL.Source_Type_Code,
'DEPRECIATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RECLASS', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RETIREMENT', -NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'REVALUATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TAX', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TRANSFER', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'END', NVL(BAL.Amount,0), NULL))/*Bug# 9293000 */
FROM
FA_ASSET_HISTORY AH, /* StatReq */
FA_METHODS M, /* StatReq */
FA_MC_BOOKS B, /* StatReq */
FA_BALANCES_REPORT_GT BAL,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS DHCC,
GL_CODE_COMBINATIONS AJCC,
FA_ADDITIONS AD1, -- This is only used to get Group Asset Number
FA_LOOKUPS LU,
FA_MC_BOOKS BKG
WHERE AD.Asset_ID = BAL.Asset_ID
AND DHCC.Code_Combination_ID = BAL.Distribution_CCID
AND AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
AND B.Book_Type_Code = c_book /* StatReq */
AND B.ASSET_ID = AD.ASSET_ID /* StatReq */
AND B.Date_Ineffective is NULL /* StatReq */
AND B.Transaction_Header_Id_Out is NULL /* StatReq */
AND nvl(M.LIFE_IN_MONTHS, -9) = nvl(B.LIFE_IN_MONTHS, -9) /* StatReq */
AND M.METHOD_CODE = B.DEPRN_METHOD_CODE /* StatReq */
AND AH.ASSET_ID = AD.ASSET_ID /* StatReq */
AND AH.DATE_EFFECTIVE <= c_to_date /* StatReq */
AND NVL(AH.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date /* StatReq */
AND AD1.Asset_ID(+) = nvl(BAL.Group_Asset_ID,-99)
AND LU.Lookup_Type (+) = 'ASSET TYPE'
AND LU.Lookup_Code (+) = AD.Asset_Type
AND B.set_of_books_id = h_sob_id
AND BKG.Book_Type_Code = c_book /* Bug 16424827 */
AND BKG.ASSET_ID = AD.ASSET_ID
AND BKG.DATE_EFFECTIVE <= c_to_date
AND NVL(BKG.DATE_INEFFECTIVE, SYSDATE + 1) > c_to_date
AND AD1.Asset_ID(+) = BKG.GROUP_ASSET_ID
AND BKG.set_of_books_id = h_sob_id
GROUP BY
ad.asset_id, /* StatReq */
DHCC.CODE_COMBINATION_ID,
BAL.Category_Books_Account,
BAL.Cost_Account, /* StatReq */
AD.ASSET_NUMBER,
AD.TAG_NUMBER,
AD.DESCRIPTION, ad.serial_number, ad.inventorial, ad.asset_key_ccid,
ah.category_id, /* StatReq */
b.date_placed_in_service, /* StatReq */
m.method_code, /* StatReq */
b.life_in_months, /* StatReq */
m.stl_method_flag, /* StatReq */
m.rate_source_rule; /* StatReq */
SELECT P1.Period_Counter,
P1.Period_Open_Date,
P1.Period_Close_Date,
P2.Period_Counter,
NVL(P2.Period_Close_Date, SYSDATE),
BC.Distribution_Source_Book,
BC.Accounting_Flex_Structure
FROM FA_DEPRN_PERIODS P1,
FA_DEPRN_PERIODS P2,
FA_BOOK_CONTROLS BC
WHERE BC.Book_Type_Code = Book
AND P1.Book_Type_Code = Book AND
P1.Period_Name = Start_Period_Name
AND P2.Book_Type_Code = Book AND
P2.Period_Name = End_Period_Name;
SELECT DISTINCT BAL.Group_Asset_ID Group_Asset_ID
FROM FA_BALANCES_REPORT_GT BAL
WHERE BAL.Asset_ID = BAL.Group_Asset_ID;
SELECT
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0) /* StatReq */
FROM FA_BALANCES_REPORT_GT BAL
WHERE BAL.Group_Asset_id = p_group_asset_id
AND BAL.Group_Asset_id <> BAL.Asset_id;
SELECT
NVL(SUM(DECODE(BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Cost_Begin_Balance, 0), NULL)), 0),
NVL(SUM (DECODE (BAL.Source_Type_Code,
'BEGIN', NVL(BAL.Amount,0), NULL)), 0),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADDITION', 'CIP ADDITION'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE, 'CIP COST', 'ADDITION'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','ADJUSTMENT','CIP ADJUSTMENT'),
NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'RECLASS', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
DECODE(REPORT_TYPE,'COST','RETIREMENT','CIP RETIREMENT'),
-NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'REVALUATION', NVL(BAL.Amount,0), NULL)),
SUM (DECODE (BAL.Source_Type_Code,
'TRANSFER', NVL(BAL.Amount,0), NULL)),
NVL(SUM (DECODE (BAL.Source_Type_Code,
'END', NVL(BAL.Amount,0), NULL)), 0)
FROM FA_BALANCES_REPORT_GT BAL
WHERE BAL.Group_Asset_id = p_group_asset_id
AND BAL.Group_Asset_id <> BAL.Asset_id;
select nvl(sorp_enabled_flag,'N')
into h_sorp_enabled_flag
from fa_book_controls
where book_type_code = Book;
select 'P'
into h_mrcsobtype
from fa_book_controls
where book_type_code = book
and set_of_books_id = h_sob_id;
h_mesg_name := 'FA_SHARED_DELETE_FAILED';
select
category_flex_structure,
asset_key_flex_structure
into
h_cat_flex_struct,
h_key_flex_struct
from
fa_system_controls;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Member Track
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount,
Cost_Begin_Balance)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
+
nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
,
DD.COST
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_BOOKS BK, -- Added for Member Track
FA_ADJUSTMENTS AJ
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period1_Pc - 1 AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period1_Pc - 1) AND
AJ.Asset_ID(+) = DD.Asset_ID AND
AJ.Book_type_code(+) = Book AND
AJ.Distribution_ID(+) = DD.Distribution_ID AND
AJ.Adjustment_Type(+) = Report_Type AND
AJ.Period_Counter_Created(+) BETWEEN
(DD.Period_Counter+1) AND (Period1_PC-1)
AND (( DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9407401 */
AND (( 0 = (select count(1)
from fa_deprn_detail dd1
where dd1.asset_id = DD.Asset_ID
AND dd1.Deprn_Source_Code = 'T'
AND dd1.book_type_code = Book
AND dd1.Period_Counter = DD.Period_Counter
AND dd1.distribution_id > dd.distribution_id) AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D')
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
AND ((Period1_Pod BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period1_Pod) AND
DD.Deprn_Source_Code = 'D') OR
(DD.Deprn_Run_Date BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, DD.Deprn_Run_Date) AND
DD.Deprn_Source_Code = 'B') OR
(Period1_Pod BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period1_Pod) AND
-- DD.Deprn_Source_Code = 'T' AND
nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D'))) /*Bug# 9293000 */
AND CB.Category_ID = AH.Category_ID
AND CB.Book_Type_Code = Book
AND DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
null)), -- Added for second decode for drill down report
'CIP COST',
DECODE (AH.Asset_Type,
'CIP', CB.CIP_Cost_Acct,
null),
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND BK.Book_Type_Code = CB.book_type_code
AND BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', Period1_pod,
nvl(Period1_pcd,Period1_pod)) BETWEEN /*Bug# 9293000 */
BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate) AND
NVL(BK.Period_Counter_Fully_Retired, Period1_PC)
> period1_pc - 1
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
DH.LOCATION_ID,-- bug 16587270 null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve),
DD.COST
UNION ALL-- Added to get assets added with reserve when multiple periods intervall bug 3756517
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve),
DD.COST
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_BOOKS BK,
FA_DEPRN_PERIODS DP_BROW
WHERE DH.Book_Type_Code = distribution_source_book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = book AND
DD.Distribution_ID = DH.Distribution_ID AND
dd.deprn_reserve <> 0 AND
dd.deprn_source_code = 'B' AND
DD.Period_Counter between period1_pc-1 and period2_pc
AND AH.Asset_ID = DH.Asset_ID AND
( (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')))
AND
DP_BROW.book_type_code = dd.book_type_code and
DP_BROW.period_counter = dd.period_counter +1
and NVL(DP_BROW.period_close_date,SYSDATE) between ah.date_effective and nvl(ah.date_ineffective,sysdate)
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = book
AND DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND BK.Book_Type_Code = CB.book_type_code
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND BK.Asset_ID = DD.Asset_ID AND
ah.date_effective between BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate);
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Member Track
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount,
Cost_Begin_Balance)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
+
nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
,
DD.COST
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_MC_DEPRN_DETAIL DD,
FA_MC_BOOKS BK, -- Added for Member Track
FA_MC_ADJUSTMENTS AJ
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period1_Pc - 1 AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_MC_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period1_Pc - 1 AND
SUB_DD.set_of_books_id = h_sob_id) AND
AJ.Asset_ID(+) = DD.Asset_ID AND
AJ.Book_type_code(+) = Book AND
AJ.Distribution_ID(+) = DD.Distribution_ID AND
AJ.Adjustment_Type(+) = Report_Type AND
AJ.set_of_books_id(+) = h_sob_id AND
AJ.Period_Counter_Created(+) BETWEEN
(DD.Period_Counter+1) AND (Period1_Pc - 1)
AND (( DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND (( 0 = (select count(1)
from fa_mc_deprn_detail dd1
where dd1.asset_id = DD.Asset_ID
AND dd1.Deprn_Source_Code = 'T'
AND dd1.book_type_code = Book
AND dd1.Period_Counter = DD.Period_Counter
AND dd1.set_of_books_id = h_sob_id
AND dd1.distribution_id > dd.distribution_id) AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D')
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
AND ((Period1_Pod BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period1_Pod) AND
DD.Deprn_Source_Code = 'D') OR
(DD.Deprn_Run_Date BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, DD.Deprn_Run_Date) AND
DD.Deprn_Source_Code = 'B') OR
(Period1_Pod BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period1_Pod) AND
-- DD.Deprn_Source_Code = 'T' AND
nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D'))) /*Bug# 9293000 */
AND CB.Category_ID = AH.Category_ID
AND CB.Book_Type_Code = Book
AND DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
null)), -- Added for second decode for drill down report
'CIP COST',
DECODE (AH.Asset_Type,
'CIP', CB.CIP_Cost_Acct,
null),
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND BK.Book_Type_Code = CB.book_type_code
AND BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', Period1_pod,
nvl(Period1_pcd,Period1_pod)) BETWEEN /*Bug# 9293000 */
BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate) AND
NVL(BK.Period_Counter_Fully_Retired, Period1_PC)
> period1_pc - 1
AND DD.set_of_books_id = h_sob_id
AND BK.set_of_books_id = h_sob_id
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
DH.LOCATION_ID,-- bug 16587270 null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve),
DD.COST
UNION ALL-- Added to get assets added with reserve when multiple periods intervall bug 3756517
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID),
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'BEGIN', 'T', 'BEGIN', 'ADDITION'), -- Added 'T'
'BEGIN'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve),
DD.COST
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_MC_DEPRN_DETAIL DD,
FA_MC_BOOKS BK,
FA_MC_DEPRN_PERIODS DP_BROW
WHERE DH.Book_Type_Code = distribution_source_book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = book AND
DD.Distribution_ID = DH.Distribution_ID AND
dd.deprn_reserve <> 0 AND
dd.deprn_source_code = 'B' AND
DD.Period_Counter between period1_pc-1 and period2_pc
AND AH.Asset_ID = DH.Asset_ID AND
( (AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')))
AND
DP_BROW.book_type_code = dd.book_type_code and
DP_BROW.period_counter = dd.period_counter +1
and NVL(DP_BROW.period_close_date,SYSDATE) between ah.date_effective and nvl(ah.date_ineffective,sysdate)
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = book
AND DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND BK.Book_Type_Code = CB.book_type_code
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND BK.Asset_ID = DD.Asset_ID AND
ah.date_effective between BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate)
AND DD.set_of_books_id = h_sob_id
AND BK.set_of_books_id = h_sob_id
AND DP_BROW.set_of_books_id = h_sob_id;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'END'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
+
nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_BOOKS BK,
FA_ADJUSTMENTS AJ
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= period2_pc AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= period2_pc) AND
AJ.Asset_ID(+) = DD.Asset_ID AND
AJ.Book_type_code(+) = Book AND
AJ.Distribution_ID(+) = DD.Distribution_ID AND
AJ.Adjustment_Type(+) = Report_Type AND
AJ.Period_Counter_Created(+) BETWEEN
(DD.Period_Counter+1) AND Period2_PC
-- Added for bug#4860955
and Period2_pcd between dh.date_effective and nvl(dh.date_ineffective,Period2_pcd)
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
AND ((Period2_Pcd BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period2_Pcd) AND
DD.Deprn_Source_Code = 'D') OR
(DD.deprn_run_date between ah.date_effective AND
NVL(ah.date_ineffective, DD.Deprn_run_date) AND
DD.deprn_source_code = 'B') OR
(Period2_Pcd BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period2_Pcd) AND
DD.Deprn_Source_Code = decode(substr(report_type,-4),'COST', DD.Deprn_Source_Code, 'T') AND
nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D') ))
AND DD.deprn_source_code in ('D', 'T')
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
null)),
'CIP COST',
DECODE (AH.Asset_Type,
'CIP', CB.CIP_Cost_Acct,
null),
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND BK.Book_Type_Code = CB.book_type_code AND
BK.Asset_ID = DD.Asset_ID AND
Period2_pcd BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate) AND
NVL(BK.Period_Counter_Fully_Retired, Period2_PC+1)
> period1_pc - 1
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
DH.LOCATION_ID,-- bug 16587270 null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'END'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve) ;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'END'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
+
nvl( SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) * AJ.Adjustment_Amount),0)
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_MC_DEPRN_DETAIL DD,
FA_MC_BOOKS BK,
FA_MC_ADJUSTMENTS AJ
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND DD.Asset_ID = AH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= period2_pc AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_MC_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Asset_ID = DH.Asset_ID AND
SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= period2_pc AND
SUB_DD.set_of_books_id = h_sob_id) AND
AJ.Asset_ID(+) = DD.Asset_ID AND
AJ.Book_type_code(+) = Book AND
AJ.Distribution_ID(+) = DD.Distribution_ID AND
AJ.Adjustment_Type(+) = Report_Type AND
AJ.set_of_books_id(+) = h_sob_id AND
AJ.Period_Counter_Created(+) BETWEEN
(DD.Period_Counter+1) AND Period2_PC
-- Added for bug#4860955
and Period2_pcd between dh.date_effective and nvl(dh.date_ineffective,Period2_pcd)
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D'))
AND ((Period2_Pcd BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period2_Pcd) AND
DD.Deprn_Source_Code = 'D') OR
(DD.deprn_run_date between ah.date_effective AND
NVL(ah.date_ineffective, DD.Deprn_run_date) AND
DD.deprn_source_code = 'B') OR
(Period2_Pcd BETWEEN AH.Date_Effective AND
NVL(AH.Date_Ineffective, Period2_Pcd) AND
DD.Deprn_Source_Code = decode(substr(report_type,-4),'COST', DD.Deprn_Source_Code, 'T') AND
nvl(Report_Style,'S')= decode(substr(report_type,-4),'COST', nvl(Report_Style,'S'),'D') ))
AND DD.deprn_source_code in ('D', 'T')
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
DECODE(Report_Style,'D',DECODE(AH.Asset_Type,'GROUP',CB.Asset_Cost_Acct,null),
null)),
'CIP COST',
DECODE (AH.Asset_Type,
'CIP', CB.CIP_Cost_Acct,
null),
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND BK.Book_Type_Code = CB.book_type_code AND
BK.Asset_ID = DD.Asset_ID AND
Period2_pcd BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, sysdate) AND
NVL(BK.Period_Counter_Fully_Retired, Period2_PC+1)
> period1_pc - 1
AND DD.set_of_books_id = h_sob_id
AND BK.set_of_books_id = h_sob_id
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
DH.LOCATION_ID,-- bug 16587270 null,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'REVAL RESERVE', DECODE(DD.Deprn_Source_Code,
'D', 'END', 'T', 'END', 'ADDITION'), -- Added 'T'
'END'),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve);
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id),
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct), -- Bug 8616752
CB.Asset_Cost_Acct,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH1,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ,
FA_BOOKS BK
/* SLA changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
,fa_book_controls bc
WHERE BC.BOOK_TYPE_CODE = Book
AND DH.Book_Type_Code = Distribution_Source_Book
AND AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type in
(Report_Type, DECODE(Report_Type,
'REVAL RESERVE', 'REVAL AMORT')) AND
AJ.Period_Counter_Created BETWEEN
Period1_PC AND Period2_PC
AND TH.Transaction_Header_ID = AJ.Transaction_Header_ID
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D')) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND AJ.Adjustment_Amount <> 0
AND AH1.ASSET_ID = AJ.ASSET_ID /* StatReq */
AND AH1.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE /* StatReq */
AND NVL(AH1.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE /* StatReq */
AND CB.Category_ID = AH1.Category_ID AND /* StatReq */
CB.Book_Type_Code = Book /* StatReq */
AND BK.Book_Type_Code = Book -- Added for Drill Down Report
AND BK.Asset_ID = DH.Asset_ID
-- AND BK.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE
-- AND NVL(BK.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE
AND BK.DATE_EFFECTIVE <= nvl(DH.DATE_INEFFECTIVE, sysdate)
AND NVL(BK.DATE_INEFFECTIVE, SYSDATE + 1) > nvl(DH.DATE_INEFFECTIVE, sysdate)
AND (AJ.track_member_flag is null OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
/* SLA Changes */
and links.Source_distribution_id_num_1(+) = aj.transaction_header_id
and links.Source_distribution_id_num_2(+) = aj.adjustment_line_id
and links.application_id(+) = 140
and links.source_distribution_type(+) = 'TRX'
and headers.ae_header_id(+) = links.ae_header_id
and headers.application_id(+) = 140
and lines.ae_header_id(+) = links.ae_header_id
and lines.ae_line_num(+) = links.ae_line_num
and lines.application_id(+) = 140
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
AJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct), -- Bug 8616752
CB.Asset_Cost_Acct,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH1,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_MC_ADJUSTMENTS AJ,
FA_MC_BOOKS BK
/* SLA changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
,fa_mc_book_controls bc
WHERE BC.BOOK_TYPE_CODE = Book
AND DH.Book_Type_Code = Distribution_Source_Book
AND AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type in
(Report_Type, DECODE(Report_Type,
'REVAL RESERVE', 'REVAL AMORT')) AND
AJ.Period_Counter_Created BETWEEN
Period1_PC AND Period2_PC
AND TH.Transaction_Header_ID = AJ.Transaction_Header_ID
AND AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type in ('CAPITALIZED', 'GROUP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE')) OR
(AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D')) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND AJ.Adjustment_Amount <> 0
AND AH1.ASSET_ID = AJ.ASSET_ID /* StatReq */
AND AH1.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE /* StatReq */
AND NVL(AH1.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE /* StatReq */
AND CB.Category_ID = AH1.Category_ID AND /* StatReq */
CB.Book_Type_Code = Book /* StatReq */
AND BK.Book_Type_Code = Book -- Added for Drill Down Report
AND BK.Asset_ID = DH.Asset_ID
-- AND BK.DATE_EFFECTIVE <= DH.DATE_EFFECTIVE
-- AND NVL(BK.DATE_INEFFECTIVE, SYSDATE) > DH.DATE_EFFECTIVE
AND BK.DATE_EFFECTIVE <= nvl(DH.DATE_INEFFECTIVE, sysdate)
AND NVL(BK.DATE_INEFFECTIVE, SYSDATE + 1) > nvl(DH.DATE_INEFFECTIVE, sysdate)
AND AJ.set_of_books_id = h_sob_id
AND BK.set_of_books_id = h_sob_id
AND (AJ.track_member_flag is null OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
/* SLA Changes */
and links.Source_distribution_id_num_1(+) = aj.transaction_header_id
and links.Source_distribution_id_num_2(+) = aj.adjustment_line_id
and links.application_id(+) = 140
and links.source_distribution_type(+) = 'TRX'
and headers.ae_header_id(+) = links.ae_header_id
and headers.application_id(+) = 140
and lines.ae_header_id(+) = links.ae_header_id
and lines.ae_line_num(+) = links.ae_line_num
and lines.application_id(+) = 140
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
decode(nvl(Report_Style,'S'),'D',null,lines.code_combination_id), --AJ.Code_Combination_ID,
DECODE (Report_Type,
'COST', CB.Asset_Cost_Acct,
'CIP COST', CB.CIP_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
AJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(DD.Deprn_Source_Code,
'D', 'DEPRECIATION', 'T', 'DEPRECIATION', 'ADDITION'),
SUM (DECODE (Report_Type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', -DD.Reval_Amortization))
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_BOOKS BK -- Added for Drill Down Report
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND AH.Asset_ID = DH.Asset_ID
AND ((AH.Asset_Type in ('CAPITALIZED', 'GROUP')) OR
AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D') -- Added for Drill Down Report
AND AH.Date_Effective <= nvl(DP.Period_Close_Date,sysdate) AND
NVL (AH.Date_Ineffective, SYSDATE) >= nvl(DP.Period_Open_Date,sysdate)
AND AH.Date_Effective < nvl(DH.Date_Ineffective,sysdate) AND
NVL (AH.Date_Ineffective, SYSDATE) > nvl(DH.Date_Effective,sysdate)
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND ((DD.Deprn_Source_Code = 'B'
AND DD.Period_Counter < Period2_PC) OR
(DD.Deprn_Source_Code = 'D') OR
(DD.Deprn_Source_Code = 'T' and nvl(Report_Style,'S') = 'D')) AND
DD.Book_Type_Code = Book AND
DD.Asset_ID = DH.Asset_ID AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter between
Period1_PC and Period2_PC
AND DP.Book_Type_Code = DD.Book_Type_Code AND
DP.Period_Counter = DD.Period_Counter
AND DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND DECODE (Report_Type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
AND BK.Book_Type_Code = Book -- Added for Drill Down Report
AND BK.Asset_ID = DH.Asset_ID
AND nvl(DP.Period_Close_Date,sysdate) BETWEEN BK.Date_Effective and nvl(BK.Date_Ineffective, Sysdate)
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DD.Deprn_Source_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Group_Asset_ID, -- Added for Drill Down Report
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Cost_Account,
Source_Type_Code,
Amount)
SELECT DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
null,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DECODE(DD.Deprn_Source_Code,
'D', 'DEPRECIATION', 'T', 'DEPRECIATION', 'ADDITION'),
SUM (DECODE (Report_Type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', -DD.Reval_Amortization))
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_MC_DEPRN_DETAIL DD,
FA_MC_DEPRN_PERIODS DP,
FA_MC_BOOKS BK -- Added for Drill Down Report
WHERE DH.Book_Type_Code = Distribution_Source_Book
AND AH.Asset_ID = DH.Asset_ID
AND ((AH.Asset_Type in ('CAPITALIZED', 'GROUP')) OR
AH.Asset_Type <> 'EXPENSED' AND nvl(Report_Style,'S') = 'D') -- Added for Drill Down Report
AND AH.Date_Effective <= nvl(DP.Period_Close_Date,sysdate) AND
NVL (AH.Date_Ineffective, SYSDATE) >= nvl(DP.Period_Open_Date,sysdate)
AND AH.Date_Effective < nvl(DH.Date_Ineffective,sysdate) AND
NVL (AH.Date_Ineffective, SYSDATE) > nvl(DH.Date_Effective,sysdate)
AND CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND ((DD.Deprn_Source_Code = 'B'
AND DD.Period_Counter < Period2_PC) OR
(DD.Deprn_Source_Code = 'D') OR
(DD.Deprn_Source_Code = 'T' and nvl(Report_Style,'S') = 'D')) AND
DD.Book_Type_Code = Book AND
DD.Asset_ID = DH.Asset_ID AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter between
Period1_PC and Period2_PC
AND DP.Book_Type_Code = DD.Book_Type_Code AND
DP.Period_Counter = DD.Period_Counter
AND DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND ((DD.Deprn_Source_Code <> 'T' AND Report_Type = 'RESERVE' ) OR Report_Type <> 'RESERVE' OR nvl(Report_Style,'S') = 'D') /*Bug# 9284473 */
AND DECODE (Report_Type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
AND BK.Book_Type_Code = Book -- Added for Drill Down Report
AND BK.Asset_ID = DH.Asset_ID
AND nvl(DP.Period_Close_Date,sysdate) BETWEEN BK.Date_Effective and nvl(BK.Date_Ineffective, Sysdate)
AND DD.set_of_books_id = h_sob_id
AND DP.set_of_books_id = h_sob_id
AND BK.set_of_books_id = h_sob_id
GROUP BY
DH.Asset_ID,
DECODE(AH.Asset_Type,'GROUP',DH.Asset_ID,BK.Group_Asset_ID), -- Added for Drill Down Report
DH.Code_Combination_ID,
DECODE (Report_Type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
CB.Asset_Cost_Acct,
DD.Deprn_Source_Code;
h_mesg_name := 'FA_POST_SQL_UPDATE_TABLE';
update fa_balances_report_gt
set category_books_account = acct_all_segs(gl_account_seg)
where rowid = h_br_rowid;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert /*+ noappend */ into fa_balances_rep_itf
(request_id, company, cost_center, account,
cost_account,
inventorial, asset_key, asset_number, tag_number, description,
category, deprn_method,
account_description, date_placed_in_service, book_type_code,
life_in_months, stl_depreciation_rate,
concat_vendor_name, concat_invoice_number,
concat_invoice_description, concat_location,
concat_retirement_type, cost_begin_balance,
begin_balance, additions,adjustments, /*9293000 */
retirements, revaluations, reclasses, transfers, depreciation,
amortization, end_balance, out_of_balance_flag, serial_number,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
group_asset_number ) values (
Request_Id, acct_all_segs(gl_balancing_seg),
acct_all_segs(fa_cost_ctr_seg), h_account,
h_cost_account,
h_inventorial, h_shortconcat_key,
-- 'BAL','CC','ACCT',
h_asset, h_tag_number, h_description,
h_concat_cat, h_method_code,
h_short_account_description, h_date_placed_in_service, book,
h_life_in_months, h_stl_depreciation_rate,
h_short_vendor_name, h_short_invoice_number, h_short_invoice_descr, -- bug 5944006
h_short_location,
h_short_retirement_type, h_cost_begin_balance, -- bug 5944006
h_begin, h_addition,h_adjustment, h_retirement,
h_revaluation, h_reclass, h_transfer, h_depreciation,
h_depreciation, nvl(h_end,0), h_out_of_bal_flag, h_serial_number,
User_Id, sysdate, user_id, sysdate, h_Login_id,
h_group_asset);
select distinct asset_number
into h_group_asset
from fa_additions
where asset_id=group_asset.group_asset_id;
Update fa_balances_rep_itf
set cost_begin_balance = h_cost_begin_balance
where request_id = Request_Id
and asset_number = h_group_asset;
mesg := 'Error selecting cost balances';
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert /*+ noappend */ into fa_balances_rep_itf
(request_id, company, cost_center, account,
cost_account, /* StatReq */
asset_key, asset_number, tag_number, description,
inventorial, serial_number,
category, deprn_method, /* StatReq */
account_description, date_placed_in_service, book_type_code, /* StatReq */
life_in_months, stl_depreciation_rate, /* StatReq */
concat_vendor_name, concat_invoice_number, /* StatReq */
concat_invoice_description, concat_location, /* StatReq */
concat_retirement_type, cost_begin_balance, /* StatReq */
begin_balance, additions, adjustments,
retirements, revaluations, reclasses, capitalizations, transfers,
end_balance, out_of_balance_flag, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, group_asset_number
-- , segment1, segment2, segment3, segment4, segment5,
-- segment6, segment7, segment8, segment9, segment10,
-- segment11, segment12, segment13, segment14, segment15,
-- segment16, segment17, segment18, segment19, segment20,
-- segment21, segment22, segment23, segment24, segment25,
-- segment26, segment27, segment28, segment29, segment30
) values (
Request_Id, acct_all_segs(gl_balancing_seg),
acct_all_segs(fa_cost_ctr_seg), h_account,
h_cost_account,
h_shortconcat_key,
-- 'BAL','CC','ACCT',
h_asset, h_tag_number, h_description,
h_inventorial, h_serial_number,
h_concat_cat, h_method_code,
h_short_account_description, h_date_placed_in_service, book,
h_life_in_months, h_stl_depreciation_rate,
h_short_vendor_name, h_short_invoice_number, h_short_invoice_descr, -- bug 5944006
h_short_location,
h_short_retirement_type, h_cost_begin_balance, -- bug 5944006
h_begin, h_addition, h_adjustment, h_retirement,
h_revaluation, h_reclass, h_capitalization, h_transfer,
h_end, h_out_of_bal_flag, User_Id, sysdate, User_Id,
sysdate, h_Login_Id, h_group_Asset);
select distinct asset_number
into h_group_asset
from fa_additions
where asset_id=group_asset.group_asset_id;
Update fa_balances_rep_itf
set cost_begin_balance = h_cost_begin_balance,
begin_balance = h_begin,
additions = h_addition,
capitalizations = h_capitalization,
adjustments = h_adjustment,
reclasses = h_reclass,
retirements = h_retirement,
revaluations = h_revaluation,
transfers = h_transfer,
end_balance = h_end
where request_id = Request_Id
and asset_number = h_group_asset;
if h_mesg_name in ('FA_SHARED_DETELE_FAILED','FA_SHARED_INSERT_FAILED') then
fnd_message.set_token('TABLE',h_table_token,FALSE);