[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROWID FROM fa_worker_jobs WHERE request_id = request_id_in;
INSERT INTO FA_WORKER_JOBS
( START_RANGE
, END_RANGE
, WORKER_NUM
, STATUS
,REQUEST_ID
)
SELECT min(asset_id),
max(asset_id),
1,
'UNASSIGNED',
p_request_id
FROM fa_books
WHERE book_type_code = p_book_type_code
AND transaction_header_id_out is null;
INSERT INTO fa_worker_jobs
(start_range, end_range, worker_num, status, request_id)
SELECT MIN(asset_id), MAX(asset_id), 0, 'UNASSIGNED', p_request_id
FROM (SELECT /*+ parallel(BK) */
asset_id,
floor(rank() over(ORDER BY asset_id) / l_batch_size) unit_id
FROM fa_books bk
WHERE bk.book_type_code = p_book_type_code
AND bk.transaction_header_id_out IS NULL)
GROUP BY unit_id;
UPDATE fa_worker_jobs
SET worker_num = l_worker_num_tbl(i)
WHERE ROWID = l_rowid_tbl(i);
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
SELECT set_of_books_id
INTO h_set_of_books_id
FROM fa_book_controls
WHERE book_type_code = book;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments_mrc_v aj,
fa_deprn_periods dp
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
GROUP BY dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
aj.source_type_code
UNION ALL
SELECT dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments_mrc_v aj,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
aj.source_type_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments aj,
fa_deprn_periods dp
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND aj.code_combination_id IS NOT NULL -- suju
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
GROUP BY dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
aj.source_type_code
UNION ALL
SELECT dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments aj,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND (dp.xla_conversion_status IS NULL OR
aj.code_combination_id IS NULL)
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
aj.source_type_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments_mrc_v aj,
fa_deprn_periods dp
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
-- start of cua
AND NOT EXISTS
(SELECT 'x'
FROM fa_books_mrc_v bks
WHERE bks.book_type_code = book
AND bks.asset_id = aj.asset_id
AND bks.group_asset_id IS NOT NULL
AND bks.date_ineffective IS NOT NULL)
-- end of cua
GROUP BY dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
aj.source_type_code
UNION ALL
SELECT dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments_mrc_v aj,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
-- start of cua
AND NOT EXISTS
(SELECT 'x'
FROM fa_books_mrc_v bks
WHERE bks.book_type_code = book
AND bks.asset_id = aj.asset_id
AND bks.group_asset_id IS NOT NULL
AND bks.date_ineffective IS NOT NULL)
-- end of cua
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
aj.source_type_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments aj,
fa_deprn_periods dp
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
-- start of cua
AND NOT EXISTS
(SELECT 'x'
FROM fa_books bks
WHERE bks.book_type_code = book
AND bks.asset_id = aj.asset_id
AND bks.group_asset_id IS NOT NULL
AND bks.date_ineffective IS NOT NULL)
-- end of cua
GROUP BY dh.asset_id,
dh.code_combination_id,
aj.code_combination_id,
aj.source_type_code
UNION ALL
SELECT dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_distribution_history dh,
fa_transaction_headers th,
fa_asset_history ah,
fa_adjustments aj,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE dh.book_type_code = distribution_source_book
AND dh.asset_id BETWEEN start_range AND end_range --Anuj
AND aj.asset_id = dh.asset_id
AND aj.book_type_code = book
AND aj.distribution_id = dh.distribution_id
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NULL
AND th.transaction_header_id = aj.transaction_header_id
AND ah.asset_id = dh.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND th.transaction_header_id BETWEEN
ah.transaction_header_id_in AND
nvl(ah.transaction_header_id_out - 1,
th.transaction_header_id)
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
-- start of cua
AND NOT EXISTS
(SELECT 'x'
FROM fa_books bks
WHERE bks.book_type_code = book
AND bks.asset_id = aj.asset_id
AND bks.group_asset_id IS NOT NULL
AND bks.date_ineffective IS NOT NULL)
-- end of cua
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY dh.asset_id,
dh.code_combination_id,
lines.code_combination_id, --AJ.Code_Combination_ID,
aj.source_type_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
cb.deprn_reserve_acct,
'ADDITION',
SUM(dd.deprn_reserve),
h_request_id
FROM fa_distribution_history dh,
fa_category_books cb,
fa_asset_history ah,
fa_deprn_detail_mrc_v dd
WHERE NOT EXISTS
(SELECT asset_id
FROM fa_balances_reports_itf
WHERE asset_id = dh.asset_id
AND distribution_ccid = dh.code_combination_id
AND source_type_code = 'ADDITION'
AND request_id = h_request_id)
AND dd.book_type_code = book
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND (dd.period_counter + 1) BETWEEN period1_pc AND period2_pc
AND dd.deprn_source_code = 'B'
AND dd.asset_id = dh.asset_id
AND dd.deprn_reserve <> 0
AND dd.distribution_id = dh.distribution_id
AND dd.asset_id = ah.asset_id
AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
AND nvl(dh.date_ineffective, SYSDATE) <=
nvl(ah.date_ineffective, SYSDATE)
AND dd.book_type_code = cb.book_type_code
AND ah.category_id = cb.category_id
GROUP BY dh.asset_id,
dh.code_combination_id,
cb.deprn_reserve_acct;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
cb.deprn_reserve_acct,
'ADDITION',
SUM(nvl(dd.deprn_reserve, 0)),
h_request_id
FROM fa_distribution_history dh,
fa_category_books cb,
fa_asset_history ah,
fa_book_controls bc,
fa_deprn_detail dd
WHERE NOT EXISTS
(SELECT asset_id
FROM fa_balances_reports_itf
WHERE asset_id = dh.asset_id
AND distribution_ccid = dh.code_combination_id
AND source_type_code = 'ADDITION'
AND request_id = h_request_id)
AND dd.book_type_code = book
AND bc.book_type_code = book
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND (dd.period_counter + 1) BETWEEN period1_pc AND period2_pc
AND dd.deprn_source_code = 'B'
AND dd.asset_id = dh.asset_id
AND bc.distribution_source_book = dh.book_type_code
AND dd.deprn_reserve <> 0
AND dd.distribution_id = dh.distribution_id
AND dd.asset_id = ah.asset_id
AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
AND nvl(dh.date_ineffective, SYSDATE) <=
nvl(ah.date_ineffective, SYSDATE)
AND dd.book_type_code = cb.book_type_code
AND ah.category_id = cb.category_id
GROUP BY dh.asset_id,
dh.code_combination_id,
cb.deprn_reserve_acct;
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
SELECT set_of_books_id
INTO h_set_of_books_id
FROM fa_book_controls
WHERE book_type_code = book;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
aj.code_combination_id),
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_adjustments_mrc_v aj,
fa_books_mrc_v bk,
fa_group_asset_default gad,
fa_deprn_periods dp
WHERE bk.asset_id = aj.asset_id
AND bk.book_type_code = book
AND bk.group_asset_id = gad.group_asset_id
AND bk.book_type_code = gad.book_type_code
AND bk.date_ineffective IS NULL
AND aj.asset_id IN
(SELECT asset_id
FROM fa_books_mrc_v
WHERE group_asset_id IS NOT NULL
AND date_ineffective IS NULL)
AND aj.asset_id = bk.asset_id
AND aj.asset_id BETWEEN start_range AND end_range --anuj
AND aj.book_type_code = book
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
GROUP BY aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
aj.code_combination_id),
aj.source_type_code
UNION ALL
SELECT aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
lines.code_combination_id /*AJ.Code_Combination_ID*/),
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_adjustments_mrc_v aj,
fa_books_mrc_v bk,
fa_group_asset_default gad,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE bk.asset_id = aj.asset_id
AND bk.book_type_code = book
AND bk.group_asset_id = gad.group_asset_id
AND bk.book_type_code = gad.book_type_code
AND bk.date_ineffective IS NULL
AND aj.asset_id BETWEEN start_range AND end_range --anuj
AND aj.asset_id IN
(SELECT asset_id
FROM fa_books_mrc_v
WHERE group_asset_id IS NOT NULL
AND date_ineffective IS NULL)
AND aj.asset_id = bk.asset_id
AND aj.book_type_code = book
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NULL
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
lines.code_combination_id /*AJ.Code_Combination_ID*/),
aj.source_type_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
aj.code_combination_id),
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_adjustments aj,
fa_books bk,
fa_group_asset_default gad,
fa_deprn_periods dp
WHERE bk.asset_id = aj.asset_id
AND bk.book_type_code = book
AND aj.asset_id BETWEEN start_range AND end_range --anuj
AND bk.group_asset_id = gad.group_asset_id
AND bk.book_type_code = gad.book_type_code
AND bk.date_ineffective IS NULL
AND aj.asset_id IN
(SELECT asset_id
FROM fa_books
WHERE group_asset_id IS NOT NULL
AND date_ineffective IS NULL)
AND aj.asset_id = bk.asset_id
AND aj.book_type_code = book
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NOT NULL
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
GROUP BY aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
aj.code_combination_id),
aj.source_type_code
UNION ALL
SELECT aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
lines.code_combination_id /*AJ.Code_Combination_ID*/),
NULL,
aj.source_type_code,
SUM(decode(aj.debit_credit_flag, balance_type, 1, -1) *
aj.adjustment_amount),
h_request_id
FROM fa_adjustments aj,
fa_books bk,
fa_group_asset_default gad,
fa_deprn_periods dp
/* SLA Changes */,
xla_ae_headers headers,
xla_ae_lines lines,
xla_distribution_links links
WHERE bk.asset_id = aj.asset_id
AND bk.book_type_code = book
AND aj.asset_id BETWEEN start_range AND end_range --anuj
AND bk.group_asset_id = gad.group_asset_id
AND bk.book_type_code = gad.book_type_code
AND bk.date_ineffective IS NULL
AND aj.asset_id IN
(SELECT asset_id
FROM fa_books
WHERE group_asset_id IS NOT NULL
AND date_ineffective IS NULL)
AND aj.asset_id = bk.asset_id
AND aj.book_type_code = book
AND aj.adjustment_type IN
(report_type,
decode(report_type, 'REVAL RESERVE', 'REVAL AMORT'))
AND aj.period_counter_created BETWEEN period1_pc AND
period2_pc
AND dp.book_type_code = aj.book_type_code
AND dp.period_counter = aj.period_counter_created
AND dp.xla_conversion_status IS NULL
AND (decode(report_type, aj.adjustment_type, 1, 0) *
aj.adjustment_amount) <> 0
/* SLA Changes */
AND links.source_distribution_id_num_1 =
aj.transaction_header_id
AND links.source_distribution_id_num_2 = aj.adjustment_line_id
AND links.application_id = 140
AND links.source_distribution_type = 'TRX'
AND headers.application_id = 140
AND headers.ae_header_id = links.ae_header_id
AND headers.ledger_id = h_set_of_books_id
AND lines.ae_header_id = links.ae_header_id
AND lines.ae_line_num = links.ae_line_num
AND lines.application_id = 140
GROUP BY aj.asset_id,
-- Changed for BMA1
-- nvl(GAD.DEPRN_EXPENSE_ACCT_CCID, -2000),
gad.deprn_expense_acct_ccid,
decode(aj.adjustment_type,
'COST',
gad.asset_cost_acct_ccid,
lines.code_combination_id /* AJ.Code_Combination_ID*/),
aj.source_type_code;
SELECT argument2
INTO h_book_id
FROM fnd_concurrent_requests
WHERE request_id = h_request_id;
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'COST',
cb.asset_cost_acct,
'CIP COST',
cb.cip_cost_acct,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
dd.cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_distribution_history dh,
fa_deprn_detail_mrc_v dd,
fa_asset_history ah,
fa_category_books cb,
fa_books_mrc_v bk
WHERE dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
AND decode(report_type,
'CIP COST',
dd.deprn_source_code,
decode(begin_or_end,
'BEGIN',
dd.deprn_source_code,
'D')) = dd.deprn_source_code
AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- End bug fix 5076193
dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail_mrc_v sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND dh.distribution_id = dd.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = dd.book_type_code -- changed from book var to column
AND bk.book_type_code = cb.book_type_code
AND -- changed from book var to column
bk.asset_id = dd.asset_id
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
earliest_pc
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT /*+ USE_HASH(SUB_DD,BK) */
dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'COST',
cb.asset_cost_acct,
'CIP COST',
cb.cip_cost_acct,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
dd.cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_deprn_detail dd,
fa_distribution_history dh,
fa_asset_history ah,
fa_category_books cb,
fa_books bk,
(SELECT asset_id,
distribution_id,
MAX(period_counter) mpc
FROM fa_deprn_detail
WHERE book_type_code = book
AND period_counter <= period_pc
GROUP BY asset_id, distribution_id) sub_dd
WHERE dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND dd.asset_id BETWEEN start_range AND end_range
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
AND decode(report_type,
'CIP COST',
dd.deprn_source_code,
decode(begin_or_end,
'BEGIN',
dd.deprn_source_code,
'D')) = dd.deprn_source_code
AND dd.period_counter = sub_dd.mpc
AND dd.distribution_id = sub_dd.distribution_id
AND sub_dd.asset_id = dd.asset_id
AND ah.asset_id = dd.asset_id
--AND ah.asset_type <> 'EXPENSED' /* Commented and added below for Bug 16326387 */
AND ah.asset_type IN ('CAPITALIZED', 'CIP', 'GROUP')
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = dd.book_type_code
AND bk.book_type_code = cb.book_type_code
AND bk.asset_id = dd.asset_id
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
earliest_pc
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type,
'CIP',
cb.cip_cost_acct,
NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'COST',
cb.asset_cost_acct,
'CIP COST',
cb.cip_cost_acct,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
dd.cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_deprn_detail dd,
fa_distribution_history dh,
fa_asset_history ah,
fa_category_books cb,
fa_books bk
WHERE dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
dd.deprn_source_code
AND dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND dh.distribution_id = dd.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ah.asset_type IN ('CAPITALIZED', 'CIP')
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = dd.book_type_code -- changed from book var to column
AND bk.book_type_code = cb.book_type_code
AND -- changed from book var to column
bk.asset_id = dd.asset_id
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
earliest_pc
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type,
'CIP',
cb.cip_cost_acct,
NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'COST',
cb.asset_cost_acct,
'CIP COST',
cb.cip_cost_acct,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
dd.cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_distribution_history dh,
fa_deprn_detail_mrc_v dd,
fa_asset_history ah,
fa_category_books cb,
fa_books_mrc_v bk
WHERE dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
decode(report_type,
'CIP COST',
dd.deprn_source_code,
decode(begin_or_end,
'BEGIN',
dd.deprn_source_code,
'D')) = dd.deprn_source_code
AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- end bug fix 5076193
dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail_mrc_v sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND dh.distribution_id = dd.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = dd.book_type_code -- changed from book var to column
AND bk.book_type_code = cb.book_type_code
AND -- changed from book var to column
bk.asset_id = dd.asset_id
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
earliest_pc
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL
-- start of CUA - This is to exclude the Group Asset Members
AND bk.group_asset_id IS NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'COST',
cb.asset_cost_acct,
'CIP COST',
cb.cip_cost_acct,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
dd.cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_distribution_history dh,
fa_deprn_detail dd,
fa_asset_history ah,
fa_category_books cb,
fa_books bk
WHERE dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND
-- Bug fix 5076193 (CIP Assets dont appear in CIP Detail Report)
decode(report_type,
'CIP COST',
dd.deprn_source_code,
decode(begin_or_end,
'BEGIN',
dd.deprn_source_code,
'D')) = dd.deprn_source_code
AND
/* DECODE(Begin_or_End,
'BEGIN', DD.Deprn_Source_Code, 'D') =
DD.Deprn_Source_Code AND */
-- End bug fix 5076193
dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND dh.distribution_id = dd.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = dd.book_type_code -- changed from book var to column
AND bk.book_type_code = cb.book_type_code
AND -- changed from book var to column
bk.asset_id = dd.asset_id
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
bk.date_effective AND nvl(bk.date_ineffective, SYSDATE)
AND nvl(bk.period_counter_fully_retired, period_pc + 1) >
earliest_pc
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL
-- start of CUA - This is to exclude the Group Asset Members
AND bk.group_asset_id IS NULL;
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
--DH.Code_Combination_ID,
nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
NULL,
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
-- Commented by Prabakar
'COST',
decode(nvl(bk.group_asset_id, -2),
-2,
dd.cost,
bk.cost),
-- 'COST', DD.Cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_books_mrc_v bk,
fa_category_books cb,
fa_asset_history ah,
fa_deprn_detail_mrc_v dd,
fa_distribution_history dh,
-- Commented by Prabakar
fa_group_asset_default gad
WHERE
-- Commented by Prabakar
gad.book_type_code = bk.book_type_code
AND gad.group_asset_id = bk.group_asset_id
AND
-- This is to include only the Group Asset Members
bk.group_asset_id IS NOT NULL
AND dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
dd.deprn_source_code
AND dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail_mrc_v sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND bk.book_type_code = book
AND bk.asset_id = dd.asset_id
AND
-- Commented by Prabakar
(bk.transaction_header_id_in =
(SELECT MIN(fab.transaction_header_id_in)
FROM fa_books_groups_mrc_v bg, fa_books_mrc_v fab
WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
AND bg.book_type_code = fab.book_type_code
AND fab.transaction_header_id_in <=
bg.transaction_header_id_in
AND nvl(fab.transaction_header_id_out,
bg.transaction_header_id_in) >=
bg.transaction_header_id_in
AND bg.period_counter = period_pc + 1
AND fab.asset_id = bk.asset_id
AND fab.book_type_code = bk.book_type_code
AND bg.beginning_balance_flag IS NOT NULL))
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
--DH.Code_Combination_ID,
nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
NULL,
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
-- Commented by Prabakar
'COST',
decode(nvl(bk.group_asset_id, -2),
-2,
dd.cost,
bk.cost),
-- 'COST', DD.Cost,
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_books bk,
fa_category_books cb,
fa_asset_history ah,
fa_deprn_detail dd,
fa_distribution_history dh,
-- Commented by Prabakar
fa_group_asset_default gad
WHERE
-- Commented by Prabakar
gad.book_type_code = bk.book_type_code
AND gad.group_asset_id = bk.group_asset_id
AND
-- This is to include only the Group Asset Members
bk.group_asset_id IS NOT NULL
AND dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
dd.deprn_source_code
AND dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND bk.book_type_code = book
AND bk.asset_id = dd.asset_id
AND
-- Commented by Prabakar
(bk.transaction_header_id_in =
(SELECT MIN(fab.transaction_header_id_in)
FROM fa_books_groups bg, fa_books fab
WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
AND bg.book_type_code = fab.book_type_code
AND fab.transaction_header_id_in <=
bg.transaction_header_id_in
AND nvl(fab.transaction_header_id_out,
bg.transaction_header_id_in) >=
bg.transaction_header_id_in
AND bg.period_counter = period_pc + 1
AND fab.asset_id = bk.asset_id
AND fab.book_type_code = bk.book_type_code
AND bg.beginning_balance_flag IS NOT NULL))
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT gar.group_asset_id asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
*/
'BEGIN',
dd.deprn_reserve,
h_request_id
FROM fa_deprn_summary_mrc_v dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
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.book_type_code = gar.book_type_code
AND gad.group_asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 <= period_pc);
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT gar.group_asset_id asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),
*/
'BEGIN',
dd.deprn_reserve,
h_request_id
FROM fa_deprn_summary dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
WHERE dd.book_type_code = book
AND dd.asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 <= period_pc);
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
-- DH.Code_Combination_ID,
nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
NULL,
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
decode(nvl(bk.group_asset_id, -2),
-2,
dd.cost,
bk.cost),
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_books_mrc_v bk,
fa_category_books cb,
fa_asset_history ah,
fa_deprn_detail_mrc_v dd,
fa_distribution_history dh,
-- Commented by Prabakar
fa_group_asset_default gad
WHERE
-- Commented by Prabakar
gad.book_type_code = bk.book_type_code
AND gad.group_asset_id = bk.group_asset_id
-- This is to include only the Group Asset Members
AND bk.group_asset_id IS NOT NULL
AND dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
dd.deprn_source_code
AND dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail_mrc_v sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND bk.book_type_code = book
AND bk.asset_id = dd.asset_id
AND
-- Commented by Prabakar
(bk.transaction_header_id_in =
(SELECT MIN(fab.transaction_header_id_in)
FROM fa_books_groups_mrc_v bg, fa_books_mrc_v fab
WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
AND bg.book_type_code = fab.book_type_code
AND fab.transaction_header_id_in <=
bg.transaction_header_id_in
AND nvl(fab.transaction_header_id_out,
bg.transaction_header_id_in) >=
bg.transaction_header_id_in
AND bg.period_counter = period_pc + 1
AND fab.asset_id = bk.asset_id
AND fab.book_type_code = bk.book_type_code
AND bg.beginning_balance_flag IS NOT NULL))
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
-- DH.Code_Combination_ID,
nvl(gad.deprn_expense_acct_ccid, dh.code_combination_id),
-- Changed for BMA1
-- nvl(gad.asset_cost_acct_ccid,1127),
gad.asset_cost_acct_ccid,
NULL,
decode(report_type,
'RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
'REVAL RESERVE',
decode(dd.deprn_source_code,
'D',
begin_or_end,
'ADDITION'),
begin_or_end),
decode(report_type,
'COST',
decode(nvl(bk.group_asset_id, -2),
-2,
dd.cost,
bk.cost),
'CIP COST',
dd.cost,
'RESERVE',
dd.deprn_reserve,
'REVAL RESERVE',
dd.reval_reserve),
h_request_id
FROM fa_books bk,
fa_category_books cb,
fa_asset_history ah,
fa_deprn_detail dd,
fa_distribution_history dh,
-- Commented by Prabakar
fa_group_asset_default gad
WHERE
-- Commented by Prabakar
gad.book_type_code = bk.book_type_code
AND gad.group_asset_id = bk.group_asset_id
-- This is to include only the Group Asset Members
AND bk.group_asset_id IS NOT NULL
AND dh.book_type_code = distribution_source_book
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
dh.date_effective AND nvl(dh.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND dd.asset_id = dh.asset_id
AND dd.book_type_code = book
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter <= period_pc
AND decode(begin_or_end, 'BEGIN', dd.deprn_source_code, 'D') =
dd.deprn_source_code
AND dd.period_counter =
(SELECT MAX(sub_dd.period_counter)
FROM fa_deprn_detail sub_dd
WHERE sub_dd.book_type_code = book
AND sub_dd.distribution_id = dh.distribution_id
AND sub_dd.period_counter <= period_pc)
AND ah.asset_id = dd.asset_id
AND ((ah.asset_type <> 'EXPENSED' AND
report_type IN ('COST', 'CIP COST')) OR
(ah.asset_type IN ('CAPITALIZED', 'CIP') AND
report_type IN ('RESERVE', 'REVAL RESERVE')))
AND decode(dd.deprn_source_code, 'D', p_date, a_date) BETWEEN
ah.date_effective AND nvl(ah.date_ineffective, SYSDATE)
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND bk.book_type_code = book
AND bk.asset_id = dd.asset_id
AND
-- Commented by Prabakar
(bk.transaction_header_id_in =
(SELECT MIN(fab.transaction_header_id_in)
FROM fa_books_groups bg, fa_books fab
WHERE bg.group_asset_id = nvl(bk.group_asset_id, -2)
AND bg.book_type_code = fab.book_type_code
AND fab.transaction_header_id_in <=
bg.transaction_header_id_in
AND nvl(fab.transaction_header_id_out,
bg.transaction_header_id_in) >=
bg.transaction_header_id_in
AND bg.period_counter = period_pc + 1
AND fab.asset_id = bk.asset_id
AND fab.book_type_code = bk.book_type_code
AND bg.beginning_balance_flag IS NOT NULL))
AND decode(report_type,
'COST',
decode(ah.asset_type,
'CAPITALIZED',
cb.asset_cost_acct,
NULL),
'CIP COST',
decode(ah.asset_type, 'CIP', cb.cip_cost_acct, NULL),
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT gar.group_asset_id asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),*/
'END',
dd.deprn_reserve,
h_request_id
FROM fa_deprn_summary_mrc_v dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
WHERE dd.book_type_code = book
AND dd.asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 <= period_pc);
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT gar.group_asset_id asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
/* DECODE(Report_Type,
'RESERVE', DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
'REVAL RESERVE',
DECODE(DD.Deprn_Source_Code,
'D', Begin_or_End, 'ADDITION'),
Begin_or_End),*/
'END',
dd.deprn_reserve,
h_request_id
FROM fa_deprn_summary dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
WHERE dd.book_type_code = book
AND dd.asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 <= period_pc);
SELECT to_number(substrb(userenv('CLIENT_INFO'), 45, 10))
INTO h_set_of_books_id
FROM dual;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(dd.deprn_source_code, 'D', 'DEPRECIATION', 'ADDITION'),
SUM(decode(report_type,
'RESERVE',
dd.deprn_amount -
decode(adj.debit_credit_flag, 'DR', 1, -1) *
nvl(adj.adjustment_amount, 0),
'REVAL RESERVE',
-dd.reval_amortization)),
h_request_id
FROM fa_category_books cb,
fa_distribution_history dh,
fa_asset_history ah,
fa_deprn_detail_mrc_v dd,
fa_deprn_periods_mrc_v dp,
fa_adjustments_mrc_v adj
WHERE dh.book_type_code = distribution_source_book
AND ah.asset_id = dd.asset_id
AND ah.asset_type IN ('CAPITALIZED', 'CIP')
AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
AND nvl(dh.date_ineffective, SYSDATE) <=
nvl(ah.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND ((dd.deprn_source_code = 'B' AND
(dd.period_counter + 1) < period2_pc) OR
(dd.deprn_source_code = 'D'))
AND dd.book_type_code || '' = book
AND dd.asset_id = dh.asset_id
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter BETWEEN period1_pc AND period2_pc
AND dp.book_type_code = dd.book_type_code
AND dp.period_counter = dd.period_counter
AND decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL
AND (decode(report_type,
'RESERVE',
dd.deprn_amount,
'REVAL RESERVE',
nvl(dd.reval_amortization, 0)) <> 0 OR
decode(report_type,
'RESERVE',
dd.deprn_amount - nvl(dd.deprn_adjustment_amount, 0),
'REVAL RESERVE',
nvl(dd.reval_amortization, 0)) <> 0)
AND adj.asset_id(+) = dd.asset_id
AND adj.book_type_code(+) = dd.book_type_code
AND adj.period_counter_created(+) = dd.period_counter
AND adj.distribution_id(+) = dd.distribution_id
AND adj.source_type_code(+) = 'REVALUATION'
AND adj.adjustment_type(+) = 'EXPENSE'
AND adj.adjustment_amount(+) <> 0
GROUP BY dh.asset_id,
dh.code_combination_id,
decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
dd.deprn_source_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dh.asset_id,
dh.code_combination_id,
NULL,
decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
decode(dd.deprn_source_code, 'D', 'DEPRECIATION', 'ADDITION'),
SUM(decode(report_type,
'RESERVE',
dd.deprn_amount -
decode(adj.debit_credit_flag, 'DR', 1, -1) *
nvl(adj.adjustment_amount, 0),
'REVAL RESERVE',
-dd.reval_amortization)),
h_request_id
FROM --fa_lookups_b rt, Bug fix 11727910 fa_lookups_b is not used in this report
fa_category_books cb,
fa_distribution_history dh,
fa_asset_history ah,
fa_deprn_detail dd,
fa_deprn_periods dp,
fa_adjustments adj
WHERE dh.book_type_code = distribution_source_book
AND ah.asset_id = dd.asset_id
AND ah.asset_type IN ('CAPITALIZED', 'CIP')
AND ah.date_effective < nvl(dh.date_ineffective, SYSDATE)
AND nvl(dh.date_ineffective, SYSDATE) <=
nvl(ah.date_ineffective, SYSDATE)
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
AND cb.category_id = ah.category_id
AND cb.book_type_code = book
AND ((dd.deprn_source_code = 'B' AND
(dd.period_counter + 1) < period2_pc) OR
(dd.deprn_source_code = 'D'))
AND dd.book_type_code || '' = book
AND dd.asset_id = dh.asset_id
AND dd.distribution_id = dh.distribution_id
AND dd.period_counter BETWEEN period1_pc AND period2_pc
AND dp.book_type_code = dd.book_type_code
AND dp.period_counter = dd.period_counter
AND decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct) IS NOT NULL
AND (decode(report_type,
'RESERVE',
dd.deprn_amount,
'REVAL RESERVE',
nvl(dd.reval_amortization, 0)) <> 0 OR
decode(report_type,
'RESERVE',
dd.deprn_amount - nvl(dd.deprn_adjustment_amount, 0),
'REVAL RESERVE',
nvl(dd.reval_amortization, 0)) <> 0)
AND adj.asset_id(+) = dd.asset_id
AND adj.book_type_code(+) = dd.book_type_code
AND adj.period_counter_created(+) = dd.period_counter
AND adj.distribution_id(+) = dd.distribution_id
AND adj.source_type_code(+) = 'REVALUATION'
AND adj.adjustment_type(+) = 'EXPENSE'
AND adj.adjustment_amount(+) <> 0
GROUP BY dh.asset_id,
dh.code_combination_id,
decode(report_type,
'RESERVE',
cb.deprn_reserve_acct,
'REVAL RESERVE',
cb.reval_reserve_acct),
dd.deprn_source_code;
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dd.asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
'DEPRECIATION',
SUM(dd.deprn_amount),
h_request_id
FROM fa_deprn_summary_mrc_v dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
WHERE dd.book_type_code = book
AND dd.asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 BETWEEN period1_pc AND period2_pc
GROUP BY dd.asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
'DEPRECIATION';
INSERT INTO fa_balances_reports_itf
(asset_id,
distribution_ccid,
adjustment_ccid,
category_books_account,
source_type_code,
amount,
request_id)
SELECT dd.asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
'DEPRECIATION',
SUM(dd.deprn_amount),
h_request_id
FROM fa_deprn_summary dd,
fa_group_asset_rules gar,
fa_group_asset_default gad
WHERE dd.book_type_code = book
AND dd.asset_id = gar.group_asset_id
AND dd.asset_id BETWEEN start_range AND end_range --Anuj
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 BETWEEN period1_pc AND period2_pc
GROUP BY dd.asset_id,
gad.deprn_expense_acct_ccid,
gad.deprn_reserve_acct_ccid,
NULL,
'DEPRECIATION';
SELECT start_range, end_range
FROM fa_worker_jobs
WHERE request_id = request_id_in
AND worker_num = worker_number_in
AND status = 'IN PROCESS';
UPDATE fa_worker_jobs
SET status = 'IN PROCESS'
WHERE status = 'UNASSIGNED'
AND request_id = l_request_id
AND worker_number = l_worker_number;
SELECT period_open_date, nvl(period_close_date, SYSDATE)
INTO beg_period_open_date, beg_period_close_date
FROM fa_deprn_periods
WHERE book_type_code = book
AND period_counter = period1_pc;
SELECT period_open_date, nvl(period_close_date, SYSDATE)
INTO end_period_open_date, end_period_close_date
FROM fa_deprn_periods
WHERE book_type_code = book
AND period_counter = period2_pc;
UPDATE fa_worker_jobs
SET status = 'COMPLETED'
WHERE status = 'IN PROCESS'
AND request_id = l_request_id
AND start_range = start_asset_id
AND end_range = end_asset_id;
UPDATE fa_worker_jobs
SET status = 'FAILED'
WHERE status IN ('IN PROCESS', 'UNASSIGNED')
AND request_id = l_request_id
AND start_range = start_asset_id
AND end_range = end_asset_id;