[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bc.book_type_code,
bc.book_class,
bc.accounting_flex_structure,
bc.distribution_source_book,
bc.fiscal_year_name,
sob.currency_code
INTO l_book,
l_book_class,
l_accounting_flex_Structure,
l_distribution_source_book,
l_fiscal_year_name,
l_currency_code
FROM FA_BOOK_CONTROLS_MRC_V 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 bc.book_type_code,
bc.book_class,
bc.accounting_flex_structure,
bc.distribution_source_book,
bc.fiscal_year_name,
sob.currency_code
INTO l_book,
l_book_class,
l_accounting_flex_Structure,
l_distribution_source_book,
l_fiscal_year_name,
l_currency_code
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 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),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
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),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
l_period_FY
FROM FA_DEPRN_PERIODS
WHERE book_type_code = P_BOOK
AND period_name = P_PERIOD1;
function C_DO_INSERTFormula return Number is
begin
declare
l_book varchar2(15);
C_Insertion_Message := l_errbuf;
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;
Function C_INSERTION_MESSAGE_p return varchar2 is
Begin
return C_INSERTION_MESSAGE;
DELETE FROM FA_RESERVE_LEDGER;
operation := 'Committing Delete';
operation := 'Rolling Back Delete';
select to_number(substrb(userenv('CLIENT_INFO'),45,10))
into h_set_of_books_id from dual;
operation := 'Selecting Book and Period information';
SELECT
BC.DISTRIBUTION_SOURCE_BOOK dbk,
nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
DP.PERIOD_COUNTER upc,
min (DP_FY.PERIOD_OPEN_DATE) tod,
min (DP_FY.PERIOD_COUNTER) tpc
INTO
dist_book,
ucd,
upc,
tod,
tpc
FROM
FA_DEPRN_PERIODS_MRC_V DP,
FA_DEPRN_PERIODS_MRC_V DP_FY,
FA_BOOK_CONTROLS_MRC_V BC
WHERE
DP.BOOK_TYPE_CODE = book AND
DP.PERIOD_NAME = period AND
DP_FY.BOOK_TYPE_CODE = book AND
DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
AND BC.BOOK_TYPE_CODE = book
GROUP BY
BC.DISTRIBUTION_SOURCE_BOOK,
DP.PERIOD_CLOSE_DATE,
DP.PERIOD_COUNTER;
SELECT
BC.DISTRIBUTION_SOURCE_BOOK dbk,
nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
DP.PERIOD_COUNTER upc,
min (DP_FY.PERIOD_OPEN_DATE) tod,
min (DP_FY.PERIOD_COUNTER) tpc
INTO
dist_book,
ucd,
upc,
tod,
tpc
FROM
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP_FY,
FA_BOOK_CONTROLS BC
WHERE
DP.BOOK_TYPE_CODE = book AND
DP.PERIOD_NAME = period AND
DP_FY.BOOK_TYPE_CODE = book AND
DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
AND BC.BOOK_TYPE_CODE = book
GROUP BY
BC.DISTRIBUTION_SOURCE_BOOK,
DP.PERIOD_CLOSE_DATE,
DP.PERIOD_COUNTER;
operation := 'Inserting into FA_RESERVE_LEDGER_GT';
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE,
RESERVE_ACCT)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
DD_BONUS.COST COST,
decode (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT,
decode (sign (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD_BONUS.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd),
''
FROM
FA_DEPRN_DETAIL_MRC_V DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD_BONUS.BOOK_TYPE_CODE = book AND
DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD_BONUS.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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod
UNION ALL
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.BONUS_DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
0 COST,
decode (DD.PERIOD_COUNTER, upc, DD.BONUS_DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
0 PERCENT,
'B' T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd),
CB.BONUS_DEPRN_EXPENSE_ACCT
FROM
FA_DEPRN_DETAIL_MRC_V DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd) AND
BOOKS.BONUS_RULE IS NOT NULL
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod
;
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE,
RESERVE_ACCT)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
DD_BONUS.COST COST,
decode (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT,
decode (sign (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD_BONUS.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd),
''
FROM
FA_DEPRN_DETAIL DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD_BONUS.BOOK_TYPE_CODE = book AND
DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD_BONUS.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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod
UNION ALL
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.BONUS_DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
0 COST,
decode (DD.PERIOD_COUNTER, upc, DD.BONUS_DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
0 PERCENT,
'B' T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd),
CB.BONUS_DEPRN_EXPENSE_ACCT
FROM
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd) AND
BOOKS.BONUS_RULE IS NOT NULL
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod
;
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
DD.COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd)
FROM
FA_DEPRN_DETAIL_MRC_V DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
-- start cua - exclude the group Assets
books.group_asset_id is null
AND -- end cua
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod AND
-- start cua - exclude the group Assets
books.group_asset_id is null;
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
DD.COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd)
FROM
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
-- start cua - exclude the group Assets
books.group_asset_id is null
AND -- end cua
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_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 = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod AND
-- start cua - exclude the group Assets
books.group_asset_id is null;
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID CH_CCID,
GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT,
GAR.DEPRN_START_DATE START_DATE,
GAR.DEPRN_METHOD_CODE METHOD,
GAR.LIFE_IN_MONTHS LIFE,
GAR.ADJUSTED_RATE RATE,
GAR.PRODUCTION_CAPACITY CAPACITY,
DD.ADJUSTED_COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
/* round (decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd) */
100 PERCENT,
'G' T_TYPE,
DD.PERIOD_COUNTER,
UCD
FROM
FA_DEPRN_SUMMARY_MRC_V DD,
FA_GROUP_ASSET_RULES GAR,
FA_GROUP_ASSET_DEFAULT GAD,
FA_DEPRN_PERIODS_MRC_V DP
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAD.SUPER_GROUP_ID is null -- MPOWELL
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 <= upc
)
AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE -- mwoodwar
AND nvl(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE; -- mwoodwar
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID CH_CCID,
GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT,
GAR.DEPRN_START_DATE START_DATE,
GAR.DEPRN_METHOD_CODE METHOD,
GAR.LIFE_IN_MONTHS LIFE,
GAR.ADJUSTED_RATE RATE,
GAR.PRODUCTION_CAPACITY CAPACITY,
DD.ADJUSTED_COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
/* round (decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd) */
100 PERCENT,
'G' T_TYPE,
DD.PERIOD_COUNTER,
UCD
FROM
FA_DEPRN_SUMMARY DD,
FA_GROUP_ASSET_RULES GAR,
FA_GROUP_ASSET_DEFAULT GAD,
FA_DEPRN_PERIODS DP
WHERE
DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND GAD.SUPER_GROUP_ID is null -- MPOWELL
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 <= upc
)
AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE -- mwoodwar
AND nvl(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE; -- mwoodwar
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID DH_CCID,
GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT,
GAR.DEPRN_START_DATE START_DATE,
SGR.DEPRN_METHOD_CODE METHOD, -- MPOWELL
GAR.LIFE_IN_MONTHS LIFE,
SGR.ADJUSTED_RATE RATE, -- MPOWELL
GAR.PRODUCTION_CAPACITY CAPACITY,
DD.ADJUSTED_COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
100 PERCENT,
'G' T_TYPE,
DD.PERIOD_COUNTER,
UCD
FROM FA_DEPRN_SUMMARY_MRC_V DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD,
fa_SUPER_GROUP_RULES SGR,
FA_DEPRN_PERIODS_MRC_V DP
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.SUPER_GROUP_ID = SGR.SUPER_GROUP_ID -- MPOWELL
AND GAD.BOOK_TYPE_CODE = SGR.BOOK_TYPE_CODE -- MPOWELL
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 <= upc)
AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
AND nvl(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE
AND SGR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
AND nvl(SGR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE;
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
GAR.GROUP_ASSET_ID ASSET_ID,
GAD.DEPRN_EXPENSE_ACCT_CCID DH_CCID,
GAD.DEPRN_RESERVE_ACCT_CCID RSV_ACCOUNT,
GAR.DEPRN_START_DATE START_DATE,
SGR.DEPRN_METHOD_CODE METHOD, -- MPOWELL
GAR.LIFE_IN_MONTHS LIFE,
SGR.ADJUSTED_RATE RATE, -- MPOWELL
GAR.PRODUCTION_CAPACITY CAPACITY,
DD.ADJUSTED_COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
100 PERCENT,
'G' T_TYPE,
DD.PERIOD_COUNTER,
UCD
FROM FA_DEPRN_SUMMARY DD,
fa_GROUP_ASSET_RULES GAR,
fa_GROUP_ASSET_DEFAULT GAD,
fa_SUPER_GROUP_RULES SGR,
FA_DEPRN_PERIODS DP
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.SUPER_GROUP_ID = SGR.SUPER_GROUP_ID -- MPOWELL
AND GAD.BOOK_TYPE_CODE = SGR.BOOK_TYPE_CODE -- MPOWELL
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 <= upc)
AND DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
AND DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND GAR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
AND nvl(GAR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE
AND SGR.DATE_EFFECTIVE <= DP.CALENDAR_PERIOD_CLOSE_DATE
AND nvl(SGR.DATE_INEFFECTIVE, (DP.CALENDAR_PERIOD_CLOSE_DATE + 1))
> DP.CALENDAR_PERIOD_CLOSE_DATE;