The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cr.concurrent_program_id
INTO l_conc_program_id
FROM FND_CONCURRENT_REQUESTS cr
WHERE cr.program_application_id = 140
AND cr.request_id = P_CONC_REQUEST_ID;
SELECT cp.user_concurrent_program_name
INTO l_report_name
FROM FND_CONCURRENT_PROGRAMS_VL cp
WHERE
cp.concurrent_program_id= l_conc_program_id
and cp.application_id = 140;
SELECT period_counter,
period_open_date,
nvl(period_close_date, sysdate),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_FY
FROM FA_DEPRN_PERIODS_MRC_V
WHERE book_type_code = P_BOOK
AND period_name = P_PERIOD1;
SELECT period_counter,
period_open_date,
nvl(period_close_date, sysdate),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_FY
FROM FA_DEPRN_PERIODS
WHERE book_type_code = P_BOOK
AND period_name = P_PERIOD1;
SELECT period_counter,
period_open_date,
nvl(period_close_date, sysdate),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_FY
FROM FA_DEPRN_PERIODS_MRC_V
WHERE book_type_code = P_BOOK
AND period_name = P_PERIOD2;
SELECT period_counter,
period_open_date,
nvl(period_close_date, sysdate),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_FY
FROM FA_DEPRN_PERIODS
WHERE book_type_code = P_BOOK
AND period_name = P_PERIOD2;
function DO_INSERTFormula return Number is
begin
BEGIN
IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
Insert_Info (P_BOOK, P_PERIOD1,
P_PERIOD2, P_REPORT_TYPE, P_ADJ_MODE);
select mrc_sob_type_code, currency_code
into p_mrcsobtype, lp_currency_code
from gl_sets_of_books
where set_of_books_id = p_ca_set_of_books_id_1;
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
select set_of_books_id
into h_set_of_books_id
from fa_book_controls
where book_type_code = book;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS_MRC_V AJ
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
AJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
AJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS_MRC_V AJ
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
-- start of cua
and not exists ( select 'x' from fa_books_mrc_v bks
where bks.book_type_code = Book
and bks.asset_id = aj.asset_id
and bks.group_asset_id is not null
and bks.date_ineffective is not null )
-- end of cua
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
AJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
TH.Transaction_Header_ID BETWEEN
AH.Transaction_Header_ID_In AND
NVL (AH.Transaction_Header_ID_Out - 1,
TH.Transaction_Header_ID)
AND
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
-- start of cua
and not exists ( select 'x' from fa_books bks
where bks.book_type_code = Book
and bks.asset_id = aj.asset_id
and bks.group_asset_id is not null
and bks.date_ineffective is not null )
-- end of cua
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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,
DH.Code_Combination_ID,
lines.code_combination_id, --AJ.Code_Combination_ID,
AJ.Source_Type_Code;
insert into FA_BALANCES_REPORT_GT
(Asset_id,
Distribution_CCID,
Adjustment_CCID,
Category_books_account,
Source_type_code,
amount)
SELECT
dh.asset_id,
dh.code_combination_id,
null,
CB.Deprn_Reserve_Acct,
'ADDITION',
sum(DD.DEPRN_RESERVE)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL_MRC_V DD
WHERE NOT EXISTS (SELECT ASSET_ID
FROM FA_BALANCES_REPORT_GT
WHERE ASSET_ID = DH.ASSET_ID
AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
AND SOURCE_TYPE_CODE = 'ADDITION')
AND DD.BOOK_TYPE_CODE = BOOK
AND (DD.PERIOD_COUNTER+1) BETWEEN
PERIOD1_PC AND PERIOD2_PC
AND DD.DEPRN_SOURCE_CODE = 'B'
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DEPRN_RESERVE <> 0
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DH.ASSET_ID = AH.ASSET_ID
AND AH.DATE_EFFECTIVE <
NVL(DH.DATE_INEFFECTIVE, SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE,SYSDATE) <=
NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND AH.CATEGORY_ID = CB.CATEGORY_ID
GROUP BY
Dh.ASSET_ID,
DH.CODE_COMBINATION_ID,
CB.DEPRN_RESERVE_ACCT;
insert into FA_BALANCES_REPORT_GT
(Asset_id,
Distribution_CCID,
Adjustment_CCID,
Category_books_account,
Source_type_code,
amount)
SELECT
dh.asset_id,
dh.code_combination_id,
null,
CB.Deprn_Reserve_Acct,
'ADDITION',
sum(DD.DEPRN_RESERVE)
FROM FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD
WHERE NOT EXISTS (SELECT ASSET_ID
FROM FA_BALANCES_REPORT_GT
WHERE ASSET_ID = DH.ASSET_ID
AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
AND SOURCE_TYPE_CODE = 'ADDITION')
AND DD.BOOK_TYPE_CODE = BOOK
AND (DD.PERIOD_COUNTER+1) BETWEEN
PERIOD1_PC AND PERIOD2_PC
AND DD.DEPRN_SOURCE_CODE = 'B'
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DEPRN_RESERVE <> 0
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DH.ASSET_ID = AH.ASSET_ID
AND AH.DATE_EFFECTIVE <
NVL(DH.DATE_INEFFECTIVE, SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE,SYSDATE) <=
NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND AH.CATEGORY_ID = CB.CATEGORY_ID
GROUP BY
Dh.ASSET_ID,
DH.CODE_COMBINATION_ID,
CB.DEPRN_RESERVE_ACCT;
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
select set_of_books_id
into h_set_of_books_id
from fa_book_controls
where book_type_code = book;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
AJ.Asset_ID,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
GAD.DEPRN_EXPENSE_ACCT_CCID,
decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_ADJUSTMENTS_MRC_V AJ,
fa_books_mrc_v bk,
fa_group_asset_default gad
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
bk.asset_id = aj.asset_id
and bk.book_type_code = book
and bk.group_asset_id = gad.group_asset_id
and bk.book_type_code = gad.book_type_code
and bk.date_ineffective is null
and aj.asset_id in (select asset_id from fa_books_mrc_v
where group_asset_id is not null
and date_ineffective is null)
and
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
AND
AJ.Asset_ID = BK.Asset_ID AND
AJ.Book_Type_Code = Book 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
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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
AJ.Asset_ID,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
GAD.DEPRN_EXPENSE_ACCT_CCID,
decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
aJ.Source_Type_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
AJ.Asset_ID,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
GAD.DEPRN_EXPENSE_ACCT_CCID,
decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /*AJ.Code_Combination_ID*/ ),
null,
AJ.Source_Type_Code,
SUM (DECODE (AJ.Debit_Credit_Flag, Balance_Type, 1, -1) *
AJ.Adjustment_Amount)
FROM
FA_LOOKUPS RT,
FA_ADJUSTMENTS AJ,
fa_books bk,
fa_group_asset_default gad
/* SLA Changes */
,xla_ae_headers headers
,xla_ae_lines lines
,xla_distribution_links links
WHERE
bk.asset_id = aj.asset_id
and bk.book_type_code = book
and bk.group_asset_id = gad.group_asset_id
and bk.book_type_code = gad.book_type_code
and bk.date_ineffective is null
and aj.asset_id in (select asset_id from fa_books
where group_asset_id is not null
and date_ineffective is null)
and
RT.Lookup_Type = 'REPORT TYPE' AND
RT.Lookup_Code = Report_Type
AND
AJ.Asset_ID = BK.Asset_ID AND
AJ.Book_Type_Code = Book 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
(DECODE (RT.Lookup_Code, AJ.Adjustment_Type, 1, 0) *
AJ.Adjustment_Amount) <> 0
/* 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.application_id = 140
and headers.ae_header_id = links.ae_header_id
and headers.ledger_id = h_set_of_books_id
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
AJ.Asset_ID,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
GAD.DEPRN_EXPENSE_ACCT_CCID,
decode(aj.adjustment_type,'COST',GAD.ASSET_COST_ACCT_CCID,lines.code_combination_id /* AJ.Code_Combination_ID*/ ),
aJ.Source_Type_Code;
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.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),
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL_MRC_V DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS_MRC_V BK
WHERE
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D')) =
DD.Deprn_Source_Code AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- End bug fix 5076193
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
AND
BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
> Earliest_PC
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.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),
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BK
WHERE
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D')) =
DD.Deprn_Source_Code AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- End bug fix 5076193
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
AND
AH.Asset_ID = DH.Asset_ID AND
AH.Asset_Type <> 'EXPENSED'
AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
AND
BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
> Earliest_PC
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.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),
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BK
WHERE
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
AND
AH.Asset_ID = DH.Asset_ID AND
AH.Asset_Type in ( 'CAPITALIZED' ,'CIP') AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
AND
BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
> Earliest_PC
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.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),
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL_MRC_V DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS_MRC_V BK
WHERE
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D')) =
DD.Deprn_Source_Code AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- end bug fix 5076193
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
AND
BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
> Earliest_PC
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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
-- start of CUA - This is to exclude the Group Asset Members
and bk.GROUP_ASSET_ID IS NULL;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.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),
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BK
WHERE
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
DECODE(Report_Type, 'CIP COST', DD.Deprn_Source_Code,
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D')) =
DD.Deprn_Source_Code AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- End bug fix 5076193
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = DD.book_type_code -- changed from book var to column
AND
BK.Book_Type_Code = CB.book_type_code AND -- changed from book var to column
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, Period_PC+1)
> Earliest_PC
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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
-- start of CUA - This is to exclude the Group Asset Members
and bk.GROUP_ASSET_ID IS NULL;
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
--DH.Code_Combination_ID,
nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
null,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
-- Commented by Prabakar
'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
-- 'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_BOOKS_MRC_V BK,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL_MRC_V DD,
FA_DISTRIBUTION_HISTORY DH,
-- Commented by Prabakar
fa_GROUP_ASSET_DEFAULT GAD
WHERE
-- Commented by Prabakar
GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
and
-- This is to include only the Group Asset Members
bk.GROUP_ASSET_ID IS not NULL AND
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
BK.Book_Type_Code = Book AND
BK.Asset_ID = DD.Asset_ID AND
-- Commented by Prabakar
( bk.transaction_header_id_in
= ( select min(fab.transaction_header_id_in) from fa_books_groups_mrc_v bg, fa_books_mrc_v fab
where bg.group_asset_id = nvl(bk.group_asset_id,-2)
and bg.book_type_code = fab.book_type_code
and fab.transaction_header_id_in <= bg.transaction_header_id_in
and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
and bg.period_counter = Period_pc + 1
and fab.asset_id = bk.asset_id
and fab.book_type_code = bk.book_type_code
and bg.BEGINNING_BALANCE_FLAG is not null )
)
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
--DH.Code_Combination_ID,
nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
null,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
-- Commented by Prabakar
'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
-- 'COST', DD.Cost,
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_BOOKS BK,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DISTRIBUTION_HISTORY DH,
-- Commented by Prabakar
fa_GROUP_ASSET_DEFAULT GAD
WHERE
-- Commented by Prabakar
GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
and
-- This is to include only the Group Asset Members
bk.GROUP_ASSET_ID IS not NULL AND
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
BK.Book_Type_Code = Book AND
BK.Asset_ID = DD.Asset_ID AND
-- Commented by Prabakar
( bk.transaction_header_id_in
= ( select min(fab.transaction_header_id_in) from fa_books_groups bg, fa_books fab
where bg.group_asset_id = nvl(bk.group_asset_id,-2)
and bg.book_type_code = fab.book_type_code
and fab.transaction_header_id_in <= bg.transaction_header_id_in
and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
and bg.period_counter = Period_pc + 1
and fab.asset_id = bk.asset_id
and fab.book_type_code = bk.book_type_code
and bg.BEGINNING_BALANCE_FLAG is not null )
)
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
*/
'BEGIN',
DD.DEPRN_RESERVE
FROM
FA_DEPRN_SUMMARY_MRC_V DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER =
(SELECT max (DD_SUB.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
WHERE DD_SUB.BOOK_TYPE_CODE = book
AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
*/
'BEGIN',
DD.DEPRN_RESERVE
FROM
FA_DEPRN_SUMMARY DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER =
(SELECT max (DD_SUB.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL DD_SUB
WHERE DD_SUB.BOOK_TYPE_CODE = book
AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
-- DH.Code_Combination_ID,
nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
null,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_BOOKS_MRC_V BK,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL_MRC_V DD,
FA_DISTRIBUTION_HISTORY DH,
-- Commented by Prabakar
fa_GROUP_ASSET_DEFAULT GAD
WHERE
-- Commented by Prabakar
GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
-- This is to include only the Group Asset Members
and bk.GROUP_ASSET_ID IS not NULL AND
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL_MRC_V SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
BK.Book_Type_Code = Book AND
BK.Asset_ID = DD.Asset_ID AND
-- Commented by Prabakar
( bk.transaction_header_id_in
= ( select min(fab.transaction_header_id_in) from fa_books_groups_mrc_v bg, fa_books_mrc_v fab
where bg.group_asset_id = nvl(bk.group_asset_id,-2)
and bg.book_type_code = fab.book_type_code
and fab.transaction_header_id_in <= bg.transaction_header_id_in
and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
and bg.period_counter = Period_pc + 1
and fab.asset_id = bk.asset_id
and fab.book_type_code = bk.book_type_code
and bg.BEGINNING_BALANCE_FLAG is not null )
)
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
-- DH.Code_Combination_ID,
nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, DH.Code_Combination_ID),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
null,
DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
DECODE (Report_Type,
'COST', decode(nvl(bk.group_asset_id,-2),-2,DD.Cost,bk.cost),
'CIP COST', DD.Cost,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve)
FROM
FA_BOOKS BK,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DISTRIBUTION_HISTORY DH,
-- Commented by Prabakar
fa_GROUP_ASSET_DEFAULT GAD
WHERE
-- Commented by Prabakar
GAD.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = BK.GROUP_ASSET_ID
-- This is to include only the Group Asset Members
and bk.GROUP_ASSET_ID IS not NULL AND
DH.Book_Type_Code = Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE)
AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= Period_PC AND
DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND
DD.Period_Counter =
(SELECT MAX (SUB_DD.Period_Counter)
FROM FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.Book_Type_Code = Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
SUB_DD.Period_Counter <= Period_PC)
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','CIP') AND
Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Date,
A_Date) BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
BK.Book_Type_Code = Book AND
BK.Asset_ID = DD.Asset_ID AND
-- Commented by Prabakar
( bk.transaction_header_id_in
= ( select min(fab.transaction_header_id_in) from fa_books_groups bg, fa_books fab
where bg.group_asset_id = nvl(bk.group_asset_id,-2)
and bg.book_type_code = fab.book_type_code
and fab.transaction_header_id_in <= bg.transaction_header_id_in
and nvl(fab.transaction_header_id_out,bg.transaction_header_id_in) >= bg.transaction_header_id_in
and bg.period_counter = Period_pc + 1
and fab.asset_id = bk.asset_id
and fab.book_type_code = bk.book_type_code
and bg.BEGINNING_BALANCE_FLAG is not null )
)
AND
DECODE (Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
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;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),*/
'END',
DD.DEPRN_RESERVE
FROM
FA_DEPRN_SUMMARY_MRC_V DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER =
(SELECT max (DD_SUB.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL_MRC_V DD_SUB
WHERE DD_SUB.BOOK_TYPE_CODE = book
AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),*/
'END',
DD.DEPRN_RESERVE
FROM
FA_DEPRN_SUMMARY DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER =
(SELECT max (DD_SUB.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL DD_SUB
WHERE DD_SUB.BOOK_TYPE_CODE = book
AND DD_SUB.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD_SUB.PERIOD_COUNTER <= PERIOD_PC);
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DECODE(DD.Deprn_Source_Code,
'D', 'DEPRECIATION', 'ADDITION'),
SUM (DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount - decode(ADJ.debit_credit_flag,'DR',1,-1)
* nvl(ADJ.adjustment_amount,0),
'REVAL RESERVE', -DD.Reval_Amortization))
FROM
FA_LOOKUPS_B RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL_MRC_V DD,
FA_DEPRN_PERIODS_MRC_V DP,
FA_ADJUSTMENTS_MRC_V ADJ
WHERE
DH.Book_Type_Code = Distribution_Source_Book
AND
AH.Asset_ID = DH.Asset_ID AND
AH.Asset_Type in ( 'CAPITALIZED' ,'CIP') AND
AH.Date_Effective <
nvl(DH.date_ineffective, sysdate) AND
nvl(DH.date_ineffective, sysdate) <=
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
((DD.Deprn_Source_Code = 'B'
AND (DD.Period_Counter+1) < Period2_PC) OR
(DD.Deprn_Source_Code = '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
RT.Lookup_Type = 'REPORT TYPE' AND
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND
(DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0 OR
DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount - nvl(DD.deprn_adjustment_amount,0),
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0)
AND ADJ.asset_id(+) = DD.asset_id AND
ADJ.book_type_code(+) = DD.book_type_code AND
ADJ.period_counter_created(+) = DD.period_counter AND
ADJ.distribution_id(+) = DD.distribution_id AND
ADJ.source_type_code(+) = 'REVALUATION' AND
ADJ.adjustment_type(+) = 'EXPENSE' AND
ADJ.adjustment_amount(+) <> 0
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DD.Deprn_Source_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DECODE(DD.Deprn_Source_Code,
'D', 'DEPRECIATION', 'ADDITION'),
SUM (DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount - decode(ADJ.debit_credit_flag,'DR',1,-1)
* nvl(ADJ.adjustment_amount,0),
'REVAL RESERVE', -DD.Reval_Amortization))
FROM
FA_LOOKUPS_B RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_ADJUSTMENTS ADJ
WHERE
DH.Book_Type_Code = Distribution_Source_Book
AND
AH.Asset_ID = DH.Asset_ID AND
AH.Asset_Type in ( 'CAPITALIZED','CIP') AND
AH.Date_Effective <
nvl(DH.date_ineffective, sysdate) AND
nvl(DH.date_ineffective, sysdate) <=
NVL(AH.Date_Ineffective, SYSDATE)
AND
CB.Category_ID = AH.Category_ID AND
CB.Book_Type_Code = Book
AND
((DD.Deprn_Source_Code = 'B'
AND (DD.Period_Counter+1) < Period2_PC) OR
(DD.Deprn_Source_Code = '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
RT.Lookup_Type = 'REPORT TYPE' AND
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null
AND
(DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0 OR
DECODE (RT.Lookup_Code,
'RESERVE', DD.Deprn_Amount - nvl(DD.deprn_adjustment_amount,0),
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0)
AND ADJ.asset_id(+) = DD.asset_id AND
ADJ.book_type_code(+) = DD.book_type_code AND
ADJ.period_counter_created(+) = DD.period_counter AND
ADJ.distribution_id(+) = DD.distribution_id AND
ADJ.source_type_code(+) = 'REVALUATION' AND
ADJ.adjustment_type(+) = 'EXPENSE' AND
ADJ.adjustment_amount(+) <> 0
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
DECODE (RT.Lookup_Code,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DD.Deprn_Source_Code;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DD.Asset_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID,
null,
'DEPRECIATION',
SUM ( DD.Deprn_Amount)
FROM
FA_DEPRN_SUMMARY_MRC_V DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER between
Period1_PC and Period2_PC
GROUP BY
DD.Asset_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
'DEPRECIATION' ;
INSERT INTO FA_BALANCES_REPORT_GT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount)
SELECT
DD.Asset_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID,
null,
'DEPRECIATION',
SUM ( DD.Deprn_Amount)
FROM
FA_DEPRN_SUMMARY DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAR.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND GAD.BOOK_TYPE_CODE = GAR.BOOK_TYPE_CODE
AND GAD.GROUP_ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.PERIOD_COUNTER between
Period1_PC and Period2_PC
GROUP BY
DD.Asset_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID ,
GAD.DEPRN_RESERVE_ACCT_CCID ,
null,
'DEPRECIATION' ;
procedure Insert_Info
(Book in varchar2,
Start_Period_Name in varchar2,
End_Period_Name in varchar2,
Report_Type in varchar2,
Adj_Mode in varchar2)
is
--Added during DT Fix
PRAGMA AUTONOMOUS_TRANSACTION;
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
SELECT
P1.Period_Counter,
P1.Period_Open_Date,
NVL(P1.Period_Close_Date, SYSDATE),
P2.Period_Counter,
NVL(P2.Period_Close_Date, SYSDATE),
BC.Distribution_Source_Book
INTO
Period1_PC,
Period1_POD,
Period1_PCD,
Period2_PC,
Period2_PCD,
Distribution_Source_Book
FROM
FA_DEPRN_PERIODS_MRC_V P1,
FA_DEPRN_PERIODS_MRC_V P2,
FA_BOOK_CONTROLS_MRC_V 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
P1.Period_Counter,
P1.Period_Open_Date,
NVL(P1.Period_Close_Date, SYSDATE),
P2.Period_Counter,
NVL(P2.Period_Close_Date, SYSDATE),
BC.Distribution_Source_Book
INTO
Period1_PC,
Period1_POD,
Period1_PCD,
Period2_PC,
Period2_PCD,
Distribution_Source_Book
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;
/* DELETE FROM FA_BALANCES_REPORT_GT; */
tables. So Inserts and Deletes wont work on FA_LOOKUPS, and instead must be
performed on both tables. Changes made by cbachand, 5/25/99
DELETE FROM FA_LOOKUPS
WHERE LOOKUP_TYPE = 'REPORT TYPE';
INSERT INTO FA_LOOKUPS
(lookup_type,
lookup_code,
last_updated_by,
last_update_date,
meaning,
enabled_flag)
VALUES
('REPORT TYPE',
Report_Type,
1,
SYSDATE,
Report_Type,
'Y'); */
DELETE FROM FA_LOOKUPS_B
WHERE LOOKUP_TYPE = 'REPORT TYPE'
AND LOOKUP_CODE = Report_Type;
DELETE FROM FA_LOOKUPS_TL
WHERE LOOKUP_TYPE = 'REPORT TYPE'
AND LOOKUP_CODE = Report_Type;
INSERT INTO FA_LOOKUPS_B
(LOOKUP_TYPE,
LOOKUP_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ENABLED_FLAG)
VALUES
('REPORT TYPE',
Report_Type,
1,
SYSDATE,
'Y');
INSERT INTO FA_LOOKUPS_TL
(LOOKUP_TYPE,
LOOKUP_CODE,
MEANING,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG)
SELECT
'REPORT TYPE',
Report_Type,
Report_Type,
SYSDATE,
1,
L.LANGUAGE_CODE,
userenv('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM FA_LOOKUPS_TL T
WHERE T.LOOKUP_TYPE = 'REPORT TYPE'
AND T.LOOKUP_CODE = Report_Type
AND T.LANGUAGE = L.LANGUAGE_CODE);
end Insert_Info;