[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pa_debug.debug( ' ->Before insert into PA_DRAFT_INV_DETS_AR') ;
insert into PA_DRAFT_INV_DETS_AR (
PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
DRAFT_INVOICE_DETAIL_ID,
EXPENDITURE_ITEM_ID,
LINE_NUM,
PROJECT_ID,
DENOM_CURRENCY_CODE,
DENOM_BILL_AMOUNT,
ACCT_CURRENCY_CODE,
BILL_AMOUNT,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
ACCT_EXCHANGE_RATE,
CC_PROJECT_ID,
CC_TAX_TASK_ID,
ORG_ID,
REV_CODE_COMBINATION_ID,
DRAFT_INVOICE_NUM,
DRAFT_INVOICE_LINE_NUM,
OUTPUT_VAT_TAX_ID,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
LINE_NUM_REVERSED,
DETAIL_ID_REVERSED,
REVERSED_FLAG,
PROJACCT_CURRENCY_CODE,
PROJACCT_COST_AMOUNT,
PROJACCT_BILL_AMOUNT,
MARKUP_CALC_BASE_CODE,
IND_COMPILED_SET_ID,
RULE_PERCENTAGE,
BILL_RATE,
BILL_MARKUP_PERCENTAGE,
BASE_AMOUNT,
SCHEDULE_LINE_PERCENTAGE,
INVOICED_FLAG,
ORIG_DRAFT_INVOICE_NUM,
ORIG_DRAFT_INVOICE_LINE_NUM,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TP_JOB_ID,
PROV_PROJ_BILL_JOB_ID,
PROJECT_TP_CURRENCY_CODE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJFUNC_TP_CURRENCY_CODE,
PROJFUNC_TP_RATE_DATE,
PROJFUNC_TP_RATE_TYPE,
PROJFUNC_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_TRANSFER_PRICE,
TP_AMT_TYPE_CODE
)
select
p_purge_batch_id,
p_purge_release,
p_project_id,
DRAFT_INVOICE_DETAIL_ID,
EXPENDITURE_ITEM_ID,
LINE_NUM,
PROJECT_ID,
DENOM_CURRENCY_CODE,
DENOM_BILL_AMOUNT,
ACCT_CURRENCY_CODE,
BILL_AMOUNT,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
ACCT_EXCHANGE_RATE,
CC_PROJECT_ID,
CC_TAX_TASK_ID,
ORG_ID,
REV_CODE_COMBINATION_ID,
DRAFT_INVOICE_NUM,
DRAFT_INVOICE_LINE_NUM,
OUTPUT_VAT_TAX_ID,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
LINE_NUM_REVERSED,
DETAIL_ID_REVERSED,
REVERSED_FLAG,
PROJACCT_CURRENCY_CODE,
PROJACCT_COST_AMOUNT,
PROJACCT_BILL_AMOUNT,
MARKUP_CALC_BASE_CODE,
IND_COMPILED_SET_ID,
RULE_PERCENTAGE,
BILL_RATE,
BILL_MARKUP_PERCENTAGE,
BASE_AMOUNT,
SCHEDULE_LINE_PERCENTAGE,
INVOICED_FLAG,
ORIG_DRAFT_INVOICE_NUM,
ORIG_DRAFT_INVOICE_LINE_NUM,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TP_JOB_ID,
PROV_PROJ_BILL_JOB_ID,
PROJECT_TP_CURRENCY_CODE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJFUNC_TP_CURRENCY_CODE,
PROJFUNC_TP_RATE_DATE,
PROJFUNC_TP_RATE_TYPE,
PROJFUNC_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_TRANSFER_PRICE,
TP_AMT_TYPE_CODE
from pa_draft_invoice_details_all
where expenditure_item_id in
( select ei.expenditure_item_id
from pa_tasks t,
pa_expenditure_items_all ei
where ei.expenditure_item_date <= p_txn_to_date
and ei.task_id = t.task_id
and t.project_id = p_project_id )
and rownum <= l_commit_size;
insert into PA_DRAFT_INV_DETS_AR (
PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
DRAFT_INVOICE_DETAIL_ID,
EXPENDITURE_ITEM_ID,
LINE_NUM,
PROJECT_ID,
DENOM_CURRENCY_CODE,
DENOM_BILL_AMOUNT,
ACCT_CURRENCY_CODE,
BILL_AMOUNT,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
ACCT_EXCHANGE_RATE,
CC_PROJECT_ID,
CC_TAX_TASK_ID,
ORG_ID,
REV_CODE_COMBINATION_ID,
DRAFT_INVOICE_NUM,
DRAFT_INVOICE_LINE_NUM,
-- OUTPUT_VAT_TAX_ID,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
LINE_NUM_REVERSED,
DETAIL_ID_REVERSED,
REVERSED_FLAG,
PROJACCT_CURRENCY_CODE,
PROJACCT_COST_AMOUNT,
PROJACCT_BILL_AMOUNT,
MARKUP_CALC_BASE_CODE,
IND_COMPILED_SET_ID,
RULE_PERCENTAGE,
BILL_RATE,
BILL_MARKUP_PERCENTAGE,
BASE_AMOUNT,
SCHEDULE_LINE_PERCENTAGE,
INVOICED_FLAG,
ORIG_DRAFT_INVOICE_NUM,
ORIG_DRAFT_INVOICE_LINE_NUM,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TP_JOB_ID,
PROV_PROJ_BILL_JOB_ID,
PROJECT_TP_CURRENCY_CODE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJFUNC_TP_CURRENCY_CODE,
PROJFUNC_TP_RATE_DATE,
PROJFUNC_TP_RATE_TYPE,
PROJFUNC_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_TRANSFER_PRICE,
TP_AMT_TYPE_CODE
)
select
p_purge_batch_id,
p_purge_release,
p_project_id,
DRAFT_INVOICE_DETAIL_ID,
EXPENDITURE_ITEM_ID,
LINE_NUM,
PROJECT_ID,
DENOM_CURRENCY_CODE,
DENOM_BILL_AMOUNT,
ACCT_CURRENCY_CODE,
BILL_AMOUNT,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
ACCT_EXCHANGE_RATE,
CC_PROJECT_ID,
CC_TAX_TASK_ID,
ORG_ID,
REV_CODE_COMBINATION_ID,
DRAFT_INVOICE_NUM,
DRAFT_INVOICE_LINE_NUM,
-- OUTPUT_VAT_TAX_ID,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
LINE_NUM_REVERSED,
DETAIL_ID_REVERSED,
REVERSED_FLAG,
PROJACCT_CURRENCY_CODE,
PROJACCT_COST_AMOUNT,
PROJACCT_BILL_AMOUNT,
MARKUP_CALC_BASE_CODE,
IND_COMPILED_SET_ID,
RULE_PERCENTAGE,
BILL_RATE,
BILL_MARKUP_PERCENTAGE,
BASE_AMOUNT,
SCHEDULE_LINE_PERCENTAGE,
INVOICED_FLAG,
ORIG_DRAFT_INVOICE_NUM,
ORIG_DRAFT_INVOICE_LINE_NUM,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TP_JOB_ID,
PROV_PROJ_BILL_JOB_ID,
PROJECT_TP_CURRENCY_CODE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJFUNC_TP_CURRENCY_CODE,
PROJFUNC_TP_RATE_DATE,
PROJFUNC_TP_RATE_TYPE,
PROJFUNC_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_TRANSFER_PRICE,
TP_AMT_TYPE_CODE
from pa_draft_invoice_details_all
where expenditure_item_id in
( select ei.expenditure_item_id /* Bug#4943324 : Perf Issue : Removed the Task table */
from
pa_expenditure_items_all ei
where ei.project_id = p_project_id )
and rownum <= l_commit_size;
pa_debug.debug( ' ->After insert into PA_DRAFT_INV_DETS_AR') ;
* counter tracking the number of records that deleted from
* the mrc table.
*/
IF (l_mrc_flag = 'Y') THEN
pa_utils2.MRC_row_count := 0;
/*delete from pa_mc_draft_inv_details_all mdi
where (mdi.draft_invoice_detail_id) in
( select mdir.draft_invoice_detail_id
from pa_mc_draft_inv_dets_ar mdir
where mdir.purge_project_id = p_project_id ) ; */
/* Commented out for MRC migration to SLA delete from pa_mc_draft_inv_details_all mdi
where mdi.set_of_books_id > 0
and exists ( select 1 from pa_mc_draft_inv_dets_ar mdir
where mdir.purge_project_id = p_project_id
and mdir.set_of_books_id > 0
and mdir.draft_invoice_detail_id = mdi.draft_invoice_detail_id);
/* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
whenever any of the source project is purged. Since we have to show
appropriate message, in the case of Drilldown from Intercompany Invoice
to source Expenditure items, will not have a performance hit as we would
know upfront that the at least one of the source project has been purged.
*/
update pa_draft_invoices_all di
set di.purge_flag = 'Y'
where ( di.project_id, di.draft_invoice_num ) in
( select did.project_id, did.draft_invoice_num
from pa_draft_inv_dets_ar did
where did.cc_project_id = p_project_id
)
and rownum < l_commit_size;
pa_debug.debug( ' ->Before delete from pa_draft_invoice_details_all ') ;
delete from pa_draft_invoice_details_all did
where (did.project_id, did.draft_invoice_detail_id) in
( select did2.project_id, did2.draft_invoice_detail_id
from PA_DRAFT_INV_DETS_AR did2
where did2.purge_project_id = p_project_id
) ;
pa_debug.debug( ' ->After delete from pa_draft_invoice_details_all ') ;
* counter tracking the number of records that deleted from
* the mrc table.
*/
IF (l_mrc_flag = 'Y') THEN
pa_utils2.MRC_row_count := 0;
pa_debug.debug( ' ->Before delete from pa_draft_invoice_details_all ') ;
/*delete from pa_mc_draft_inv_details_all mdi
where (mdi.draft_invoice_detail_id ) in
( select did.draft_invoice_detail_id
from pa_tasks t,
pa_expenditure_items_all ei,
pa_draft_invoice_details_all did
where ei.expenditure_item_date <= p_txn_to_date
and ei.task_id = t.task_id
and t.project_id = p_project_id
and t.project_id = did.project_id
)
and rownum < l_commit_size; */
delete from pa_mc_draft_inv_details_all mdi where
mdi.set_of_books_id > 0
and exists
(select 1 from pa_draft_invoice_details_all did,
pa_expenditure_items_all ei
where ei.expenditure_item_id = did.expenditure_item_id
and ei.project_id = p_project_id
and ei.expenditure_item_date <= p_txn_to_date
and did.draft_invoice_detail_id = mdi.draft_invoice_detail_id)
and rownum < l_pmy_commit_size;
/* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
whenever any of the source project is purged. Since we have to show
appropriate message, in the case of Drilldown from Intercompany Invoice
to source Expenditure items, will not have a performance hit as we would
know upfront that the at least one of the source project has been purged.
*/
update pa_draft_invoices_all di
set di.purge_flag = 'Y'
where ( di.project_id, di.draft_invoice_num ) in
( select did.project_id, did.draft_invoice_num
from pa_draft_invoice_details_all did
where did.cc_project_id = p_project_id
and did.expenditure_item_id in
( select ei.expenditure_item_id
from pa_tasks t,
pa_expenditure_items_all ei
where ei.expenditure_item_date <= p_txn_to_date
and ei.task_id = t.task_id
and t.project_id = p_project_id
)
)
and rownum < l_commit_size;
/* delete from pa_draft_invoice_details_all did
where (did.expenditure_item_id ) in
( select ei.expenditure_item_id
from pa_tasks t,
pa_expenditure_items_all ei
where ei.expenditure_item_date <= p_txn_to_date
and ei.task_id = t.task_id
and t.project_id = p_project_id
)
and did.cc_project_id = p_project_id
and rownum < l_commit_size;
/*delete from pa_mc_draft_inv_details_all mdi
where (mdi.draft_invoice_detail_id ) in
( select did.draft_invoice_detail_id
from pa_tasks t,
pa_expenditure_items_all ei,
pa_draft_invoice_details_all did
where ei.task_id = t.task_id
and t.project_id = p_project_id
and t.project_id = did.project_id
)
and rownum < l_commit_size; */
delete from pa_mc_draft_inv_details_all mdi where
mdi.set_of_books_id > 0
and exists
(select 1 from pa_draft_invoice_details_all did,
pa_expenditure_items_all ei
where ei.expenditure_item_id = did.expenditure_item_id
and ei.project_id = p_project_id
and did.draft_invoice_detail_id = mdi.draft_invoice_detail_id)
and rownum < l_pmy_commit_size;
/* The new column pa_draft_invoices_all.purge_flag will be updated with 'Y'
whenever any of the source project is purged. Since we have to show
appropriate message, in the case of Drilldown from Intercompany Invoice
to source Expenditure items, will not have a performance hit as we would
know upfront that the at least one of the source project has been purged.
*/
update pa_draft_invoices_all di
set di.purge_flag = 'Y'
where ( di.project_id, di.draft_invoice_num ) in
( select did.project_id, did.draft_invoice_num
from pa_draft_invoice_details_all did
where did.cc_project_id = p_project_id
)
and rownum < l_commit_size;
/*delete from pa_draft_invoice_details_all did
where (did.expenditure_item_id ) in
( select ei.expenditure_item_id /* Bug#4943324 : Perf Issue : Removed the Task table
from pa_expenditure_items_all ei
where ei.project_id = p_project_id
)
and did.cc_project_id = p_project_id
and rownum < l_commit_size;
pa_debug.debug( ' ->After delete from pa_draft_invoice_details_all ') ;
delete from pa_draft_invoice_details_all did
where (did.expenditure_item_id ) in
( select ei.expenditure_item_id
from pa_tasks t,
pa_expenditure_items_all ei
where ei.expenditure_item_date <= p_txn_to_date
and ei.task_id = t.task_id
and t.project_id = p_project_id)
and did.cc_project_id = p_project_id
and rownum < l_commit_size;
delete from pa_draft_invoice_details_all did
where (did.expenditure_item_id ) in
( select ei.expenditure_item_id
from --pa_tasks t, /* Bug#4943324 : Perf Issue : Removed the Task table */
pa_expenditure_items_all ei
where -- ei.task_id = t.task_id and
ei.project_id = p_project_id)
and did.cc_project_id = p_project_id
and rownum < l_commit_size;
pa_debug.debug( ' ->Before insert into PA_MC_DRAFT_INV_DETS_AR') ;
/* Commented out for MRC migration to SLA insert into PA_MC_DRAFT_INV_DETS_AR
(
PURGE_BATCH_ID,
PURGE_RELEASE,
PURGE_PROJECT_ID,
SET_OF_BOOKS_ID,
DRAFT_INVOICE_DETAIL_ID,
PROJECT_ID,
INVOICED_FLAG,
ACCT_CURRENCY_CODE,
BILL_AMOUNT,
REQUEST_ID,
ACCT_RATE_TYPE,
ACCT_RATE_DATE,
ACCT_EXCHANGE_RATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
p_purge_batch_id,
p_purge_release,
p_project_id,
mc.SET_OF_BOOKS_ID,
mc.DRAFT_INVOICE_DETAIL_ID,
mc.PROJECT_ID,
mc.INVOICED_FLAG,
mc.ACCT_CURRENCY_CODE,
mc.BILL_AMOUNT,
mc.REQUEST_ID,
mc.ACCT_RATE_TYPE,
mc.ACCT_RATE_DATE,
mc.ACCT_EXCHANGE_RATE,
mc.PROGRAM_APPLICATION_ID,
mc.PROGRAM_ID,
mc.PROGRAM_UPDATE_DATE
from pa_mc_draft_inv_details_all mc,
pa_draft_inv_dets_ar ar
where ar.purge_project_id = p_project_id
and mc.draft_invoice_detail_id = ar.draft_invoice_detail_id; */
pa_debug.debug( ' ->After insert into PA_MC_DRAFT_INV_DETS_AR') ;