[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
put_line('Last Updated By = ' || g_last_updated_by);
put_line('Last Update Login = ' || g_last_update_login);
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
p_worker_id,
p_num_workers,
p_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
TRUE);
SELECT /*+ rowid(xh) leading(xh) cardinality(10) no_merge */
xl.report_header_id,
xl.report_line_id,
xl.org_id,
xl.set_of_books_id,
gs.chart_of_accounts_id,
xl.flex_concatenated,
xl.code_combination_id,
xl.amount,
xl.project_id,
xl.task_id,
xl.award_id,
xl.expenditure_organization_id
FROM ap_expense_report_lines_all xl,
ap_expense_report_headers_all xh,
gl_sets_of_books gs
WHERE xh.rowid BETWEEN l_start_rowid AND l_end_rowid
--Derive chart of accounts ID from set of books ID
AND gs.set_of_books_id = xl.set_of_books_id
AND xl.report_header_id = xh.report_header_id
AND xh.vouchno <> 0
AND xl.code_combination_id is not null
AND (xl.itemization_parent_id is null
OR xl.itemization_parent_id <> -1)) xl
ON (rd.report_header_id = xl.report_header_id
and rd.report_line_id = xl.report_line_id)
WHEN MATCHED THEN UPDATE SET
rd.last_updated_by = l_bug_number,
rd.last_update_date = sysdate,
rd.amount = xl.amount,
rd.project_id = xl.project_id,
rd.task_id = xl.task_id,
rd.award_id = xl.award_id,
rd.expenditure_organization_id = xl.expenditure_organization_id,
rd.cost_center = nvl(ap_web_acctg_pkg.getcostcenter(
xl.code_combination_id, xl.chart_of_accounts_id), xl.flex_concatenated)
WHEN NOT MATCHED THEN INSERT (
rd.report_distribution_id,
rd.report_line_id,
rd.report_header_id,
rd.org_id,
rd.sequence_num,
rd.last_updated_by,
rd.last_update_date,
rd.created_by,
rd.creation_date,
rd.amount,
rd.project_id,
rd.task_id,
rd.award_id,
rd.expenditure_organization_id,
rd.code_combination_id,
rd.cost_center)
VALUES (
ap_exp_report_dists_s.nextval, -- use sequence
xl.report_line_id,
xl.report_header_id,
xl.org_id,
to_number(0), -- sequence_num=0 for 1-1 relationship
l_bug_number, -- last_updated_by
sysdate, -- last_update_date
l_bug_number, -- created_by
sysdate, -- creation_date
xl.amount,
xl.project_id,
xl.task_id,
xl.award_id,
xl.expenditure_organization_id,
xl.code_combination_id,
nvl(ap_web_acctg_pkg.getcostcenter(
xl.code_combination_id, xl.chart_of_accounts_id), xl.flex_concatenated));
UPDATE AP_EXPENSE_REPORT_LINES_ALL XL
SET XL.flex_concatenated = null,
XL.code_combination_id = null,
XL.project_id = null,
XL.project_number = null,
XL.project_name = null,
XL.task_id = null,
XL.task_number = null,
XL.task_name = null,
XL.award_id = null,
XL.award_number = null,
XL.expenditure_organization_id = null
WHERE XL.code_combination_id IS NOT NULL
AND (XL.itemization_parent_id IS NULL
OR XL.itemization_parent_id <> -1)
AND EXISTS (select /*+ unnest rowid(xh) */ null
from AP_EXPENSE_REPORT_HEADERS_ALL xh
where xh.rowid between l_start_rowid and l_end_rowid
and xh.vouchno <> 0
and xh.report_header_id = xl.report_header_id);
ad_parallel_updates_pkg.processed_rowid_range(
l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
FALSE);