[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Start_range ,
End_range
FROM FA_WORKER_JOBS
WHERE request_id = request_id_in;
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;
Select 1
Into bonus_count
From dual
where exists (select 1 from FA_Books
Where book_type_code = book
and bonus_rule is not null
and transaction_header_id_out is null);
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
SELECT /*+ ORDERED
Index(DD_BONUS FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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 (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
DECODE (DH.TRANSACTION_HEADER_ID_OUT, 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 ,
ucd ,
'',
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL_MRC_V DD_BONUS,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL_MRC_V
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) dd1,
FA_DEPRN_DETAIL_MRC_V DD_BONUS,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS_MRC_V BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND DH.BOOK_TYPE_CODE = dist_book
AND DH.DATE_EFFECTIVE <= ucd
AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
UNION ALL
SELECT /*+ ORDERED
Index(DD FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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 ,
ucd ,
CB.BONUS_DEPRN_EXPENSE_ACCT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL_MRC_V DD,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL_MRC_V
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) dd1,
FA_DEPRN_DETAIL_MRC_V DD,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS_MRC_V BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND BOOKS.BONUS_RULE IS NOT NULL
AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD.ASSET_ID --7721457
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND BOOKS.BONUS_RULE = BR.BONUS_RULE
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN BR.START_YEAR AND
NVL(BR.END_YEAR, DP.FISCAL_YEAR)
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
SELECT /*+ ORDERED
Index(DD_BONUS FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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 (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
DECODE (DH.TRANSACTION_HEADER_ID_OUT, 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 ,
ucd ,
'' ,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL DD_BONUS,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) DD1 ,
FA_DEPRN_DETAIL DD_BONUS ,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB ,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_BONUS.DISTRIBUTION_ID = dd1.DISTRIBUTION_ID --7721457
AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND DH.BOOK_TYPE_CODE = dist_book
AND DH.DATE_EFFECTIVE <= ucd
AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
UNION ALL
SELECT /*+ ORDERED
Index(DD FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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 ,
ucd ,
CB.BONUS_DEPRN_EXPENSE_ACCT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL DD,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) DD1,
FA_DEPRN_DETAIL DD ,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB ,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND BOOKS.BONUS_RULE IS NOT NULL
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD.ASSET_ID --7721457
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND DD.DISTRIBUTION_ID = dd1.DISTRIBUTION_ID --7721457
AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND BOOKS.BONUS_RULE = BR.BONUS_RULE
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN BR.START_YEAR AND
NVL(BR.END_YEAR, DP.FISCAL_YEAR)
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_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 (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
DECODE (DH.TRANSACTION_HEADER_ID_OUT, 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 ,
ucd ,
'',
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL_MRC_V DD_BONUS,
( SELECT
DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL_MRC_V
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) dd1,
FA_DEPRN_DETAIL_MRC_V DD_BONUS,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS_MRC_V BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND -- BOOKS.BOOK_TYPE_CODE CHNGD
DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND --chngd
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_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 (DH.TRANSACTION_HEADER_ID_OUT, NULL, DH.UNITS_ASSIGNED / AH.UNITS * 100) PERCENT ,
DECODE (DH.TRANSACTION_HEADER_ID_OUT, NULL,
DECODE (TH_RT.TRANSACTION_TYPE_CODE, 'FULL RETIREMENT', 'F', DECODE (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
DECODE(TH_DIST_END.TRANSACTION_TYPE_CODE,'TRANSFER', 'T', 'TRANSFER OUT', 'P', 'RECLASS', 'R')) T_TYPE,
DD_BONUS.PERIOD_COUNTER ,
ucd ,
'',
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL DD_BONUS,
( SELECT
DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) DD1,
FA_DEPRN_DETAIL DD_BONUS ,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS BOOKS ,
FA_TRANSACTION_HEADERS TH_RT,
FA_TRANSACTION_HEADERS TH_DIST_END,
FA_CATEGORY_BOOKS CB ,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE BOOKS.BOOK_TYPE_CODE = book
AND BOOKS.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.ASSET_ID
AND NVL(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc
AND BOOKS.DATE_EFFECTIVE <= ucd
AND NVL(BOOKS.DATE_INEFFECTIVE,sysdate+1) > ucd
AND CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD_BONUS.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD_BONUS.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD_BONUS.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD_BONUS.ASSET_ID --7721457
AND AH.DATE_EFFECTIVE < ucd
AND NVL(AH.DATE_INEFFECTIVE,sysdate) >= ucd
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND DD_BONUS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND DD_BONUS.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD_BONUS.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD_BONUS.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
AND TH_RT.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND TH_DIST_END.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE --bug#10259151
AND TH_DIST_END.ASSET_ID = TH_RT.ASSET_ID
AND TH_DIST_END.TRANSACTION_HEADER_ID = DECODE(DH.TRANSACTION_HEADER_ID_OUT, NULL,DH.TRANSACTION_HEADER_ID_IN,DH.TRANSACTION_HEADER_ID_OUT)
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
SELECT /*+ ORDERED
Index(DD FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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),
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL_MRC_V DD,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL_MRC_V
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) dd1,
FA_DEPRN_DETAIL_MRC_V DD ,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS_MRC_V BOOKS ,
FA_TRANSACTION_HEADERS TH ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB ,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE books.group_asset_id IS NULL
AND CB.BOOK_TYPE_CODE = book
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AH.ASSET_ID = DD.ASSET_ID --7721457
AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
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 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 = DD.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.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 = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
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 -- start cua - exclude the group Assets
AND 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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
SELECT
/*+ ORDERED
Index(DD FA_DEPRN_DETAIL_U1)
index(DH FA_DISTRIBUTION_HISTORY_U1)
Index(AH FA_ASSET_HISTORY_N2)
*/
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),
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
CB.ASSET_COST_ACCT
FROM
--FA_DEPRN_DETAIL DD,
( SELECT /*+ Index (FA_DEPRN_DETAIL FA_DEPRN_DETAIL_N1 ) */ DISTRIBUTION_ID ,
MAX(PERIOD_COUNTER) PERIOD_COUNTER
FROM FA_DEPRN_DETAIL
WHERE BOOK_TYPE_CODE = book
AND PERIOD_COUNTER <= upc
AND ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
GROUP BY DISTRIBUTION_ID
) DD1,
FA_DEPRN_DETAIL DD ,
FA_DISTRIBUTION_HISTORY DH ,
FA_ASSET_HISTORY AH ,
FA_BOOKS BOOKS ,
FA_TRANSACTION_HEADERS TH ,
FA_TRANSACTION_HEADERS TH_RT,
FA_CATEGORY_BOOKS CB ,
FA_ADDITIONS AD,
FA_BONUS_RATES BR,
FA_DEPRN_PERIODS DP
WHERE books.group_asset_id IS NULL
AND CB.BOOK_TYPE_CODE = book
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND DD.DISTRIBUTION_ID = DD1.DISTRIBUTION_ID --7721457
AND DD.PERIOD_COUNTER = DD1.PERIOD_COUNTER --7721457
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND AH.ASSET_ID = DD.ASSET_ID --7721457
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 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 = DD.ASSET_ID --7721457
AND BOOKS.ASSET_ID = AD.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 BOOKS.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DP.FISCAL_YEAR BETWEEN DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(BOOKS.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
AND DH.BOOK_TYPE_CODE = dist_book
AND DH.DATE_EFFECTIVE <= ucd
AND NVL(DH.DATE_INEFFECTIVE, sysdate) > tod
AND 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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
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,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
GAD.ASSET_COST_ACCT
FROM FA_DEPRN_SUMMARY_MRC_V DD ,
FA_GROUP_ASSET_RULES GAR ,
FA_GROUP_ASSET_DEFAULT GAD,
FA_DEPRN_PERIODS_MRC_V DP,
FA_ADDITIONS AD,
FA_BONUS_RATES BR
WHERE DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD.ASSET_ID = AD.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.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
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,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
GAD.ASSET_COST_ACCT
FROM FA_DEPRN_SUMMARY DD ,
FA_GROUP_ASSET_RULES GAR ,
FA_GROUP_ASSET_DEFAULT GAD,
FA_DEPRN_PERIODS DP,
FA_ADDITIONS AD,
FA_BONUS_RATES BR
WHERE DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.ASSET_ID = AD.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.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
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,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
GAD.ASSET_COST_ACCT
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,
FA_ADDITIONS AD,
FA_BONUS_RATES BR
WHERE DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.ASSET_ID = AD.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.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
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 ,
ASSET_NUMBER ,
BONUS_RATE ,
ASSET_COST_ACCT
)
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,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ,
BR.BONUS_RATE ,
GAD.ASSET_COST_ACCT
FROM FA_DEPRN_SUMMARY DD ,
fa_GROUP_ASSET_RULES GAR ,
fa_GROUP_ASSET_DEFAULT GAD,
fa_SUPER_GROUP_RULES SGR ,
FA_DEPRN_PERIODS DP,
FA_ADDITIONS AD,
FA_BONUS_RATES BR
WHERE DD.BOOK_TYPE_CODE = book
AND DD.ASSET_ID BETWEEN REC1.START_RANGE AND REC1.END_RANGE
AND DD.ASSET_ID = GAR.GROUP_ASSET_ID
AND DD.ASSET_ID = AD.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.BONUS_RULE = BR.BONUS_RULE (+)
AND DP.FISCAL_YEAR BETWEEN DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
BR.START_YEAR)
AND DECODE(GAR.BONUS_RULE,
null, DP.FISCAL_YEAR,
NVL(BR.END_YEAR, DP.FISCAL_YEAR))
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;