[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DO_INSERT:=do_insertformula();
select set_of_books_id
into h_set_of_books_id
from fa_book_controls
where book_type_code = p_book;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
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, p_Balance_Type, 1, -1) *
AJ.Adjustment_Amount),
'FA',
1
FROM
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
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type in
(p_Report_Type, DECODE(p_Report_Type,
'REVAL RESERVE', 'REVAL AMORT')) AND
AJ.Period_Counter_Created BETWEEN
p_Period1_PC AND p_Period2_PC AND
TH.Transaction_Header_ID = AJ.Transaction_Header_ID AND
AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
p_Report_Type IN ('COST', 'OP EXPENSE')) OR
(AH.Asset_Type = 'CAPITALIZED' AND
p_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 (p_report_type, 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 IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
null,
DECODE(TH.Transaction_Type_Code,
'FULL RETIREMENT','RETIREMENT',
'PARTIAL RETIRE','RETIREMENT',
'REINSTATEMENT','RETIREMENT',
'REVALUATION',DECODE(TH.Transaction_sub_Type,
'IMPLEMENTATION','ADDITION',
TH.Transaction_Type_Code),
TH.Transaction_Type_Code),
SUM (DECODE (AJ.Dr_Cr_Flag, p_Balance_Type, 1, -1) *
AJ.Amount),
'IAC',
2
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_TRANSACTION_HEADERS TH,
IGI_IAC_ADJUSTMENTS AJ
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type = p_Report_Type AND
AJ.Period_Counter BETWEEN
p_Period1_PC AND p_Period2_PC AND
AJ.transfer_to_gl_flag <> 'N' AND
TH.adjustment_ID = AJ.adjustment_ID
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
TH.Transaction_Type_Code,
TH.Transaction_sub_Type;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
null,
DECODE(TH.Transaction_Type_Code,
'FULL RETIREMENT','RETIREMENT',
'PARTIAL RETIRE','RETIREMENT',
'REINSTATEMENT','RETIREMENT',
'REVALUATION',DECODE(TH.Transaction_sub_Type,
'IMPLEMENTATION','DEPRECIATION',
TH.Transaction_Type_Code),
TH.Transaction_Type_Code),
SUM (DECODE (AJ.Dr_Cr_Flag, p_Balance_Type, 1, -1) *
AJ.Amount),
'IAC',
2
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_TRANSACTION_HEADERS TH,
IGI_IAC_ADJUSTMENTS AJ
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type = p_Report_Type AND
AJ.Period_Counter BETWEEN
p_Period1_PC AND p_Period2_PC AND
AJ.transfer_to_gl_flag <> 'N' AND
TH.adjustment_ID = AJ.adjustment_ID
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
TH.Transaction_Type_Code,
TH.Transaction_sub_type;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Report_ccid,
null,
TH.Transaction_Type_Code,
SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
l_data_source,
l_display_order
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_TRANSACTION_HEADERS TH,
IGI_IAC_ADJUSTMENTS AJ
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type = l_account_type AND
AJ.Period_Counter BETWEEN
p_Period1_PC AND p_Period2_PC AND
AJ.transfer_to_gl_flag <> 'N' AND
TH.adjustment_ID = AJ.adjustment_ID AND
TH.Transaction_type_code NOT IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT') AND
AJ.adjustment_offset_type = p_report_type
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.report_ccid,
TH.Transaction_Type_Code;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
SUB_AJ.Code_Combination_ID,
null,
TH.Transaction_Type_Code,
SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
l_data_source,
l_display_order
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_TRANSACTION_HEADERS TH,
IGI_IAC_ADJUSTMENTS AJ,
IGI_IAC_ADJUSTMENTS SUB_AJ
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type = l_account_type AND
AJ.Period_Counter BETWEEN
p_Period1_PC AND p_Period2_PC AND
AJ.transfer_to_gl_flag <> 'N' AND
TH.adjustment_ID = AJ.adjustment_ID AND
TH.Transaction_type_code NOT IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT') AND
AJ.adjustment_id = SUB_AJ.adjustment_id AND
AJ.distribution_id = SUB_AJ.distribution_id AND
SUB_AJ.adjustment_type = p_report_type AND
SUB_AJ.rowid = (select min(x_aj.rowid)
FROM igi_iac_adjustments x_aj
WHERE x_aj.Book_Type_Code = p_book
AND x_aj.adjustment_id = sub_aj.adjustment_id
AND x_aj.distribution_id = sub_aj.distribution_id
AND x_aj.asset_id = sub_AJ.Asset_ID
AND x_aj.adjustment_type = p_report_type
AND x_aj.amount = AJ.amount
AND x_aj.adjustment_type <> AJ.adjustment_type)
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
SUB_AJ.Code_Combination_ID,
TH.Transaction_Type_Code;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
null,
DECODE(TH.Transaction_Type_Code,'FULL RETIREMENT','RETIREMENT',
'PARTIAL RETIRE','RETIREMENT',
'REINSTATEMENT','RETIREMENT',
TH.Transaction_Type_Code),
SUM (DECODE (AJ.Dr_Cr_Flag, l_Balance_Type, 1, -1) * AJ.Amount),
l_data_source,
l_display_order
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_TRANSACTION_HEADERS TH,
IGI_IAC_ADJUSTMENTS AJ
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AJ.Asset_ID = DH.Asset_ID AND
AJ.Book_Type_Code = p_Book AND
AJ.Distribution_ID = DH.Distribution_ID AND
AJ.Adjustment_Type = l_account_type AND
AJ.Period_Counter BETWEEN
p_Period1_PC AND p_Period2_PC AND
AJ.transfer_to_gl_flag <> 'N' AND
TH.adjustment_ID = AJ.adjustment_ID
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
AJ.Code_Combination_ID,
TH.Transaction_Type_Code;
/* UPDATE igi_iac_balances_report
SET source_type_code = 'RETIREMENT'
WHERE source_type_code = 'REINSTATEMENT'
AND data_source <> 'General Fund'; */
/* UPDATE igi_iac_balances_report BR
SET adjustment_ccid = (SELECT adjustment_ccid
FROM igi_iac_balances_report SUB_BR
WHERE SUB_BR.asset_id = BR.asset_id AND
SUB_BR.distribution_ccid = BR.distribution_ccid AND
SUB_BR.display_order = DECODE(p_report_type,
'REVAL RESERVE',4,
'OP EXPENSE',3) AND
SUB_BR.source_type_code NOT IN ('BEGIN','END'))
WHERE BR.data_source IN ('Cost','Backlog','General Fund');
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_id,
Distribution_CCID,
Adjustment_CCID,
Category_books_account,
Source_type_code,
Amount,
Data_source,
Display_order)
SELECT
dh.asset_id,
dh.code_combination_id,
null,
CB.Deprn_Reserve_Acct,
'ADDITION',
sum(DD.DEPRN_RESERVE),
'FA',
1
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD
WHERE
NOT EXISTS (SELECT ASSET_ID
FROM IGI_IAC_BALANCES_REPORT
WHERE ASSET_ID = DH.ASSET_ID
AND DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
AND SOURCE_TYPE_CODE = 'ADDITION'
AND DATA_SOURCE = 'FA')
AND DD.BOOK_TYPE_CODE = p_book
AND (DD.PERIOD_COUNTER+1) BETWEEN
p_period1_pc AND p_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 IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_source,
Display_order)
SELECT /*+ index(dd FA_DEPRN_DETAIL_U1) */
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (p_Report_Type,
'COST', CB.Asset_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DECODE(p_Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', p_Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', p_Begin_or_End, 'ADDITION'),
p_Begin_or_End),
DECODE (p_Report_Type,
'COST', DD.Cost,
'OP EXPENSE', 0,
'RESERVE', DD.Deprn_Reserve,
'REVAL RESERVE', DD.Reval_Reserve),
'FA',
1
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 = p_Distribution_Source_Book AND
DECODE(DD.Deprn_Source_Code, 'D', P_Period_Date,
p_Additions_Date) BETWEEN
DH.Date_Effective AND
NVL(DH.Date_Ineffective, SYSDATE) AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = p_Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= p_Period_PC AND
DECODE(p_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 = p_Book AND
SUB_DD.Distribution_ID = DH.Distribution_ID AND
DH.Distribution_ID = DD.Distribution_ID AND
SUB_DD.Period_Counter <= p_Period_PC) AND
AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
p_Report_Type IN ('COST', 'CIP COST')) OR
(AH.Asset_Type = 'CAPITALIZED' AND
p_Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
DECODE(DD.Deprn_Source_Code, 'D', P_Period_Date,
p_Additions_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 AND
BK.Book_Type_Code = CB.book_type_code AND
BK.Asset_ID = DD.Asset_ID AND
DECODE(DD.Deprn_Source_Code, 'D', P_period_Date,
p_Additions_Date) BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
> p_Earliest_PC AND
DECODE (p_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 IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (p_Report_Type,
'COST', CB.Asset_Cost_Acct,
'RESERVE', CB.Deprn_Reserve_Acct,
'BL RESERVE', ICB.Backlog_Deprn_Rsv_ccid,
'REVAL RESERVE', ICB.Reval_Rsv_ccid,
'OP EXPENSE',ICB.Operating_Expense_ccid),
p_Begin_or_End,
DECODE (p_Report_Type,
'COST', DD.adjustment_Cost,
'OP EXPENSE', DD.Operating_Acct_Net,
'RESERVE', DD.Deprn_Reserve,
'BL RESERVE', DD.Deprn_Reserve_Backlog,
'REVAL RESERVE', DD.Reval_Reserve_Net),
'IAC',
2
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_DET_BALANCES DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
IGI_IAC_CATEGORY_BOOKS ICB,
FA_BOOKS BK,
IGI_IAC_TRANSACTION_HEADERS ITH
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = p_Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= p_Period_PC AND
DD.adjustment_id =
(SELECT MAX (SUB_TH.adjustment_id)
FROM IGI_IAC_TRANSACTION_HEADERS SUB_TH
WHERE SUB_TH.Book_Type_Code = p_Book AND
SUB_TH.Asset_ID = DH.Asset_ID AND
SUB_TH.Period_Counter <= p_Period_PC AND
SUB_TH.adjustment_status NOT IN ('PREVIEW','OBSOLETE')) AND
ITH.adjustment_id = DD.adjustment_id AND
ITH.asset_id = DD.asset_id AND
ITH.book_type_code = DD.book_type_code AND
ITH.category_id = AH.category_id AND
nvl(DD.Active_Flag,'Y') <> 'N' AND
AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
p_Report_Type IN ('COST', 'OP EXPENSE')) OR
(AH.Asset_Type = 'CAPITALIZED' AND
p_Report_Type IN ('RESERVE', 'REVAL RESERVE', 'BL RESERVE'))) AND
CB.Category_ID = AH.Category_ID AND
ICB.Category_ID = AH.Category_ID AND
p_period_date BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE) AND
CB.Book_Type_Code = DD.book_type_code AND
ICB.Book_Type_Code = DD.book_type_code AND
BK.Book_Type_Code = CB.book_type_code AND
BK.Asset_ID = DD.Asset_ID AND
p_period_Date BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
> p_Earliest_PC AND
DECODE (p_Report_Type,
'COST', DECODE (AH.Asset_Type,
'CAPITALIZED', CB.Asset_Cost_Acct,
null),
'RESERVE', CB.Deprn_Reserve_Acct,
'BL RESERVE', ICB.Backlog_deprn_Rsv_ccid,
'REVAL RESERVE', ICB.Reval_Rsv_ccid,
'OP EXPENSE', ICB.Operating_Expense_ccid) is not null;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_source,
Display_order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (p_Report_Type,
'REVAL RESERVE', ICB.Reval_Rsv_ccid, /* Here ccid should be replaced by account */
'OP EXPENSE',ICB.Operating_Expense_ccid), /* Here ccid should be replaced by account */
p_Begin_or_End,
DECODE (p_Report_Type,
'OP EXPENSE',DECODE(l_account_type,
'COST',DD.Operating_Acct_Cost,
'BACKLOG',DD.Operating_Acct_Backlog,
'NET',DD.Operating_Acct_Net) ,
'REVAL RESERVE', DECODE(l_account_type,
'COST',DD.Reval_Reserve_Cost,
'BACKLOG',DD.Reval_Reserve_Backlog,
'GENERAL FUND',DD.Reval_Reserve_Gen_Fund,
'NET',DD.Reval_Reserve_Net) ),
DECODE (p_Report_Type,
'OP EXPENSE',DECODE(l_account_type,
'COST','Cost',
'BACKLOG','Backlog',
'NET','Net') ,
'REVAL RESERVE', DECODE(l_account_type,
'COST','Cost',
'BACKLOG','Backlog',
'GENERAL FUND','General Fund',
'NET','Net') ),
DECODE (p_Report_Type,
'OP EXPENSE',DECODE(l_account_type,
'COST',1,
'BACKLOG',2,
'NET',3) ,
'REVAL RESERVE', DECODE(l_account_type,
'COST',1,
'BACKLOG',3,
'GENERAL FUND',2,
'NET',4) )
FROM
FA_DISTRIBUTION_HISTORY DH,
IGI_IAC_DET_BALANCES DD,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB,
IGI_IAC_CATEGORY_BOOKS ICB,
FA_BOOKS BK,
IGI_IAC_TRANSACTION_HEADERS ITH
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
DD.Asset_ID = DH.Asset_ID AND
DD.Book_Type_Code = p_Book AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter <= p_Period_PC AND
DD.adjustment_id =
(SELECT MAX (SUB_TH.adjustment_id)
FROM IGI_IAC_TRANSACTION_HEADERS SUB_TH
WHERE SUB_TH.Book_Type_Code = p_Book AND
SUB_TH.Asset_ID = DH.Asset_ID AND
SUB_TH.Period_Counter <= p_Period_PC AND
SUB_TH.adjustment_status NOT IN ('PREVIEW','OBSOLETE')) AND
ITH.adjustment_id = DD.adjustment_id AND
ITH.asset_id = DD.asset_id AND
ITH.book_type_code = DD.book_type_code AND
ITH.category_id = AH.category_id AND
nvl(DD.Active_Flag,'Y') <> 'N' AND
AH.Asset_ID = DH.Asset_ID AND
((AH.Asset_Type <> 'EXPENSED' AND
p_Report_Type IN ('COST', 'OP EXPENSE')) OR
(AH.Asset_Type = 'CAPITALIZED' AND
p_Report_Type IN ('RESERVE', 'REVAL RESERVE'))) AND
CB.Category_ID = AH.Category_ID AND
ICB.Category_ID = AH.Category_ID AND
p_Period_date BETWEEN
AH.Date_Effective AND
NVL(AH.Date_Ineffective, SYSDATE) AND
CB.Book_Type_Code = DD.book_type_code AND
ICB.Book_Type_Code = DD.book_type_code AND
BK.Book_Type_Code = CB.book_type_code AND
BK.Asset_ID = DD.Asset_ID AND
p_Period_Date BETWEEN
BK.Date_Effective AND
NVL(BK.Date_Ineffective, SYSDATE) AND
NVL(BK.Period_Counter_Fully_Retired, p_Period_PC+1)
> p_Earliest_PC AND
DECODE (p_Report_Type,
'REVAL RESERVE', ICB.Reval_Rsv_ccid,
'OP EXPENSE', ICB.Operating_Expense_ccid) is not null;
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_source,
Display_order)
SELECT
db.asset_id,
dh.code_combination_id,
NULL,
icb.operating_expense_ccid,
p_Begin_or_End,
DECODE(l_account_type,
'COST',DB.Operating_Acct_Cost,
'BACKLOG',DB.Operating_Acct_Backlog,
'NET',DB.Operating_Acct_Net) ,
DECODE(l_account_type,
'COST','Cost',
'BACKLOG','Backlog',
'NET','Net'),
DECODE(l_account_type,
'COST',1,
'BACKLOG',2,
'NET',3)
FROM
igi_iac_det_balances db,
fa_distribution_history dh,
fa_asset_history ah,
igi_iac_category_books icb
WHERE
dh.book_type_code = p_book AND
nvl(dh.date_ineffective, p_earliest_date-1) > p_earliest_date AND
dh.asset_id = ah.asset_id AND
nvl(dh.date_ineffective, SYSDATE) > ah.date_effective AND
nvl(dh.date_ineffective, SYSDATE) <= nvl(ah.date_ineffective, SYSDATE) AND
icb.book_type_code = p_book AND
icb.category_id = ah.category_id AND
db.distribution_id = dh.distribution_id AND
db.adjustment_id = (SELECT max(idb.adjustment_id)
FROM igi_iac_det_balances idb
WHERE idb.book_type_code = p_book AND
idb.asset_id = dh.asset_id AND
idb.distribution_id = db.distribution_id AND
idb.period_counter <= p_period_pc);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_Order)
SELECT
DH.Asset_ID,
DH.Code_Combination_ID,
null,
DECODE (p_report_type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DECODE(DD.Deprn_Source_Code,
'D', 'DEPRECIATION', 'ADDITION'),
SUM (DECODE (p_report_type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', -DD.Reval_Amortization)),
'FA',
1
FROM
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP
WHERE
DH.Book_Type_Code = p_Distribution_Source_Book AND
AH.Asset_ID = DH.Asset_ID AND
AH.Asset_Type = 'CAPITALIZED' 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 = p_Book AND
((DD.Deprn_Source_Code = 'B'
AND (DD.Period_Counter+1) < p_Period2_PC) OR
(DD.Deprn_Source_Code = 'D')) AND
DD.Book_Type_Code||'' = p_Book AND
DD.Asset_ID = DH.Asset_ID AND
DD.Distribution_ID = DH.Distribution_ID AND
DD.Period_Counter between
p_Period1_PC and p_Period2_PC AND
DP.Book_Type_Code = DD.Book_Type_Code AND
DP.Period_Counter = DD.Period_Counter AND
DECODE (p_report_type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct) is not null AND
DECODE (p_report_type,
'RESERVE', DD.Deprn_Amount,
'REVAL RESERVE', NVL(DD.Reval_Amortization,0)) <> 0
GROUP BY
DH.Asset_ID,
DH.Code_Combination_ID,
DECODE (p_report_type,
'RESERVE', CB.Deprn_Reserve_Acct,
'REVAL RESERVE', CB.Reval_Reserve_Acct),
DD.Deprn_Source_Code;
SELECT *
FROM igi_iac_transaction_headers
WHERE book_type_code = cp_book
AND period_counter BETWEEN p_period1_pc AND p_period2_pc
AND transaction_type_code IN ('PARTIAL RETIRE', 'FULL RETIREMENT', 'REINSTATEMENT');
SELECT max(adjustment_id)
FROM igi_iac_transaction_headers
WHERE book_type_code = cp_book
AND asset_id = cp_asset_id
AND adjustment_id < cp_adjustment_id
AND adjustment_status NOT IN ('PREVIEW','OBSOLETE');
SELECT curr_adj.distribution_id distribution_id,
(curr_adj.reval_reserve_cost - prev_adj.reval_reserve_cost) reval_reserve_cost,
(curr_adj.reval_reserve_backlog - prev_adj.reval_reserve_backlog) reval_reserve_backlog,
(curr_adj.general_fund_acc - prev_adj.general_fund_acc) general_fund,
(curr_adj.Operating_acct_cost - prev_adj.Operating_acct_cost) operating_acct_cost,
(curr_adj.Operating_acct_backlog - prev_adj.Operating_acct_backlog) operating_acct_backlog
FROM igi_iac_det_balances curr_adj,
igi_iac_det_balances prev_adj
WHERE curr_adj.book_type_code = cp_book
AND curr_adj.asset_id = cp_asset_id
AND prev_adj.book_type_code = cp_book
AND prev_adj.asset_id = cp_asset_id
AND curr_adj.adjustment_id = cp_curr_adj_id
AND prev_adj.adjustment_id = cp_prev_adj_id
AND curr_adj.distribution_id = prev_adj.distribution_id;
SELECT adj.distribution_id,
adj.reval_reserve_cost reval_reserve_cost,
adj.reval_reserve_backlog reval_reserve_backlog,
adj.general_fund_acc general_fund,
adj.operating_acct_cost operating_acct_cost,
adj.operating_acct_backlog operating_acct_backlog
FROM igi_iac_det_balances adj
WHERE book_type_code = cp_book
AND asset_id = cp_asset_id
AND adjustment_id = cp_curr_adj_id
AND NOT EXISTS (SELECT 'X'
FROM igi_iac_det_balances sub_adj
WHERE sub_adj.book_type_code = cp_book
AND sub_adj.asset_id = cp_asset_id
AND sub_adj.adjustment_id = cp_prev_adj_id
AND sub_adj.distribution_id = adj.distribution_id);
SELECT adj.distribution_id,
(adj.reval_reserve_cost * -1) reval_reserve_cost,
(adj.reval_reserve_backlog * -1) reval_reserve_backlog,
(adj.general_fund_acc * -1) general_fund,
(adj.operating_acct_cost * -1) operating_acct_cost,
(adj.operating_acct_backlog * -1) operating_acct_backlog
FROM igi_iac_det_balances adj
WHERE book_type_code = cp_book
AND asset_id = cp_asset_id
AND adjustment_id = cp_prev_adj_id
AND NOT EXISTS (SELECT 'X'
FROM igi_iac_det_balances sub_adj
WHERE sub_adj.book_type_code = cp_book
AND sub_adj.asset_id = cp_asset_id
AND sub_adj.adjustment_id = cp_curr_adj_id
AND sub_adj.distribution_id = adj.distribution_id);
SELECT ah.category_id
FROM fa_asset_history ah,
fa_transaction_headers th
WHERE ah.asset_id = cp_asset_id
AND th.transaction_header_id = cp_transaction_id
AND th.book_type_code = cp_book
AND th.asset_id = cp_asset_id
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
NVL (ah.transaction_header_id_out - 1,
th.transaction_header_id);
SELECT DECODE(cp_report_type,'REVAL RESERVE', reval_rsv_ccid,
'OP EXPENSE', operating_expense_ccid) adjustment_ccid
FROM igi_iac_category_books
WHERE book_type_code = cp_book
AND category_id = cp_category_id;
SELECT code_combination_id
FROM fa_distribution_history
WHERE book_type_code = cp_book
AND asset_id = cp_asset_id
AND distribution_id = cp_distribution_id;
Debug_Print('Inserting Reval Reserve records');
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.general_fund,
'General Fund',
2);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_backlog,
'Backlog',
3);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_backlog,
'Backlog',
2);
Debug_Print('Inserting Reval Reserve records');
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.general_fund,
'General Fund',
2);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_backlog,
'Backlog',
3);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_backlog,
'Backlog',
2);
Debug_Print('Inserting Reval Reserve records');
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.general_fund,
'General Fund',
2);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.reval_reserve_backlog,
'Backlog',
3);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_cost,
'Cost',
1);
INSERT INTO IGI_IAC_BALANCES_REPORT
(Asset_ID,
Distribution_CCID,
Adjustment_CCID,
Category_Books_Account,
Source_Type_Code,
Amount,
Data_Source,
Display_order)
VALUES
(l_transaction.asset_id,
l_distribution_ccid,
l_account.adjustment_ccid,
NULL,
'RETIREMENT',
l_dist.operating_acct_backlog,
'Backlog',
2);
PROCEDURE Insert_info (
p_book varchar2,
p_start_period_name varchar2,
p_end_period_name varchar2,
p_report_type varchar2) IS
l_Period1_PC number(15);
SELECT rowid,ibr.*
FROM igi_iac_balances_report ibr;
Debug_Print('Inside Insert_Info');
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
l_Period1_PC,
l_Period1_POD,
l_Period1_PCD,
l_Period2_PC,
l_Period2_PCD,
l_Distribution_Source_Book
FROM
FA_DEPRN_PERIODS P1,
FA_DEPRN_PERIODS P2,
FA_BOOK_CONTROLS BC
WHERE
BC.Book_Type_Code = p_Book AND
P1.Book_Type_Code = p_Book AND
P1.Period_Name = p_Start_Period_Name AND
P2.Book_Type_Code = p_Book AND
P2.Period_Name = p_End_Period_Name;
Debug_Print('Before processing for Delete');
DELETE FROM igi_iac_balances_report;
DELETE FROM igi_iac_balances_report
WHERE amount = 0;
SELECT SOB.Chart_of_Accounts_ID
INTO l_chart_of_accounts_id
FROM fa_book_controls BC,
gl_sets_of_books SOB
WHERE BC.Book_Type_Code = p_book AND
SOB.Set_Of_Books_ID = BC.Set_Of_Books_ID;
SELECT application_id
INTO l_appl_id FROM fnd_application
WHERE application_short_name = 'SQLGL';
UPDATE igi_iac_balances_report
SET company = l_company,
cost_center = l_cost_ctr,
account = l_account
WHERE rowid = l_balance.rowid;
Debug_Print('Error in Insert Info :'||sqlerrm);
END Insert_Info;
FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
BEGIN
BEGIN
IF (P_REPORT_TYPE = 'BL RESERVE') THEN
INSERT_INFO(P_BOOK
,P_PERIOD1
,P_PERIOD2
,P_REPORT_TYPE);
END DO_INSERTFORMULA;
SELECT
CP.USER_CONCURRENT_PROGRAM_NAME
INTO L_REPORT_NAME
FROM
FND_CONCURRENT_PROGRAMS_TL CP,
FND_CONCURRENT_REQUESTS CR
WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
AND CP.LANGUAGE = USERENV('LANG')
AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;