[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or '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;
/* Select count(*)
Into bonus_count
From FA_Books
Where book_type_code = book;
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)
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,
''
FROM
--FA_DEPRN_DETAIL_MRC_V DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD_BONUS
WHERE
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 <= 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 = DH.ASSET_ID 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
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
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,
ucd,
CB.BONUS_DEPRN_EXPENSE_ACCT
FROM
--FA_DEPRN_DETAIL_MRC_V DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD
WHERE
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 <= 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 -- chngd
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < ucd AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= ucd AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE AND -- chngd
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
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)
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,
''
FROM
--FA_DEPRN_DETAIL DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BOOKS,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD_BONUS
WHERE
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 <= 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 = DH.ASSET_ID 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
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
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,
ucd,
CB.BONUS_DEPRN_EXPENSE_ACCT
FROM
--FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BOOKS,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD
WHERE
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 <= 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 -- chngd
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < ucd AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= ucd AND
AH.ASSET_TYPE = 'CAPITALIZED'
AND
DD.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE AND -- chngd
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
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)
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,
''
FROM
--FA_DEPRN_DETAIL_MRC_V DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS_MRC_V BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD_BONUS
WHERE
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 <= 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 = DH.ASSET_ID 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
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)
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,
''
FROM
--FA_DEPRN_DETAIL DD_BONUS,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH_RT,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BOOKS,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID order by Period_counter desc nulls last)
as max_period_counter
FROM FA_DEPRN_DETAIL DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD_BONUS
WHERE
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 <= 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 = DH.ASSET_ID 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
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)
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,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL_MRC_V DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD
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
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,
(
SELECT COST,
PERIOD_COUNTER,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
YTD_DEPRN,
BONUS_YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
ASSET_ID,
BOOK_TYPE_CODE,
DISTRIBUTION_ID
FROM FA_DEPRN_DETAIL DD_BONUS1
WHERE (DISTRIBUTION_ID, BOOK_TYPE_CODE, PERIOD_COUNTER)
IN
(
SELECT distinct DD_BONUS2.DISTRIBUTION_ID,
DD_BONUS2.BOOK_TYPE_CODE,
first_value(PERIOD_COUNTER)
over (partition by DISTRIBUTION_ID, BOOK_TYPE_CODE, ASSET_ID
order by Period_counter desc nulls last) as max_period_counter
FROM FA_DEPRN_DETAIL DD_BONUS2
WHERE DD_BONUS2.BOOK_TYPE_CODE = book
AND DD_BONUS2.PERIOD_COUNTER <= upc
)
) DD
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
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;