[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
,SOB.currency_code
,CUR.precision
INTO lc_book
,lc_book_class
,ln_accounting_flex_structure
,lc_distribution_source_book
,lc_currency_code
,ln_precision
FROM fa_book_controls BC
,gl_ledgers SOB
,fnd_currencies CUR
WHERE BC.book_type_code = P_BOOK
AND SOB.ledger_id = BC.set_of_books_id
AND SOB.currency_code = CUR.currency_code;
SELECT FDP.period_name
,FDP.period_counter
,FDP.period_open_date
,NVL(FDP.period_close_date, SYSDATE)
,DECODE(FDP.period_close_date, NULL, 'NO', 'YES')
,FDP.fiscal_year
INTO lc_period_name
,ln_period_PC
,ld_period_POD
,ld_period_PCD
,lc_period_closed
,ln_period_FY
FROM fa_deprn_periods FDP
WHERE FDP.book_type_code = P_BOOK
AND FDP.period_name = P_PERIOD1;
FUNCTION c_do_insertformula(Book IN VARCHAR2, Period1 IN VARCHAR2) RETURN NUMBER IS
BEGIN
DECLARE
lc_book VARCHAR2(15);
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 BOOKS.group_asset_id IS NULL
AND 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 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 BOOKS.group_asset_id IS NULL
AND 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 BOOKS.group_asset_id IS NULL;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'5)INSERT INTO ');
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
,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
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
AND NVL(GAR.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 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
,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;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'7)INSERT INTO ');
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
,GAR.life_in_months life
,SGR.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
,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
AND GAD.book_type_code = SGR.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
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;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'8)INSERT INTO ');
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
,GAR.life_in_months life
,SGR.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
,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;