[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select end_date from pa_bc_balances
where project_id = p_project_id
for update nowait;
PROCEDURE INSERT_BGT_BALANCES(
p_project_id in number,
p_budget_version_id in number,
p_set_of_books_id in number,
p_bdgt_intg_flag in varchar2,
--p_fc_reqd in varchar2, --R12 Funds Management Uptake
x_return_status out NOCOPY varchar2,
x_error_message_code out NOCOPY varchar2) is
l_start_date date;
select pa.project_id,
pa.task_id,
pt.top_task_id,
pa.resource_list_member_id,
pbv.budget_version_id,
pb.PERIOD_NAME,
pb.START_DATE,
pb.END_DATE,
rm.PARENT_MEMBER_ID,
pb.burdened_cost
from
pa_budget_lines pb,
pa_resource_assignments pa,
pa_tasks pt,
pa_resource_list_members rm,
pa_budget_versions pbv
where pbv.budget_version_id = p_budget_version_id
and pa.resource_assignment_id = pb.resource_assignment_id
and pa.task_id = pt.task_id (+)
and pa.budget_version_id = pbv.budget_version_id
and rm.resource_list_member_id = pa.resource_list_member_id;
select pb.project_id,
pb.task_id,
pb.top_task_id,
pb.resource_list_member_id,
pb.balance_type,
pb.set_of_books_id,
pb.PERIOD_NAME,
pb.START_DATE,
pb.END_DATE,
pb.PARENT_MEMBER_ID,
pb.actual_period_to_date,
pb.encumb_period_to_date
from pa_bc_balances pb
where pb.budget_version_id = p_base_version_id
and pb.balance_type <> 'BGT';
select a.rowid
from pa_bc_balances a, pa_budgetary_control_options pbco, pa_budget_versions pbv
where pbv.budget_version_id <> p_bdgt_ver
and a.project_id = pbco.project_id
and a.project_id = pbv.project_id
and a.budget_version_id = pbv.budget_version_id
and pbco.bdgt_cntrl_flag = 'Y'
and pbco.budget_type_code = pbv.budget_type_code
and ((p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code = 'GL')
or
(p_bdgt_ctrl_type = 'CC' and pbco.external_budget_code = 'CC')
or
(p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code is null))
and a.project_id = p_project_id;
select rowid
from pa_bc_balances
where budget_version_id = p_draft_bdgt_ver;
PA_DEBUG.set_err_stack('Insert BGT Balances');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Insert BGT Balances');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Open cursor to delete draft budget version- '||p_budget_version_id);
l_BalRowIdTab.Delete;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Delete draft budget versions = ' || l_BalRowIdTab.count);
delete from pa_bc_balances
where rowid = l_BalRowIdTab(j);
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Baselined budget exists, delete old versions');
PA_DEBUG.set_err_stack('Delete');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Open cursor to delete old budget versions');
l_BalRowIdTab.Delete;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Delete old budget versions = ' || l_BalRowIdTab.count);
delete from pa_bc_balances
where rowid = l_BalRowIdTab(j);
select min(start_date), max(end_date)
into l_start_date, l_end_date
from pa_bc_balances
where project_id = p_project_id
and budget_version_id = l_base_version_id;
PA_DEBUG.set_err_stack('Insert Close Period Bal');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Insert closed period balances, TabCount = '||l_tab_count);
insert into pa_bc_balances(
PROJECT_ID,
TASK_ID,
TOP_TASK_ID,
RESOURCE_LIST_MEMBER_ID,
BALANCE_TYPE,
SET_OF_BOOKS_ID,
BUDGET_VERSION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
START_DATE,
END_DATE,
PARENT_MEMBER_ID,
ACTUAL_PERIOD_TO_DATE,
ENCUMB_PERIOD_TO_DATE)
select
bal.PROJECT_ID,
bal.TASK_ID,
bal.TOP_TASK_ID,
bal.RESOURCE_LIST_MEMBER_ID,
bal.BALANCE_TYPE,
bal.SET_OF_BOOKS_ID,
p_budget_version_id,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
bal.PERIOD_NAME,
bal.START_DATE,
bal.END_DATE,
bal.PARENT_MEMBER_ID,
bal.ACTUAL_PERIOD_TO_DATE,
bal.ENCUMB_PERIOD_TO_DATE
from pa_bc_balances bal
where budget_version_id = l_base_version_id
and trunc(start_date) = trunc(l_tab_periods(i).start_date)
and trunc(end_date) = trunc(l_tab_periods(i).end_date)
and l_tab_periods(i).closing_status = 'C'
and project_id = p_project_id
and balance_type <> 'BGT';
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Inserted closed period balances');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before inserting BGT lines');
PA_DEBUG.set_err_stack('Insert BGT lines');
l_ProjTab.Delete;
l_TaskTab.Delete;
l_TTaskTab.Delete;
l_RlmiTab.Delete;
l_BdgtVerTab.Delete;
l_PeriodTab.Delete;
l_StDateTab.Delete;
l_EdDateTab.Delete;
l_ParMemTab.Delete;
l_BurdCostTab.Delete;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before Insert, no. of rec = '|| l_ProjTab.count);
insert into pa_bc_balances(
PROJECT_ID,
TASK_ID,
TOP_TASK_ID,
RESOURCE_LIST_MEMBER_ID,
BALANCE_TYPE,
SET_OF_BOOKS_ID,
BUDGET_VERSION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
START_DATE,
END_DATE,
PARENT_MEMBER_ID,
BUDGET_PERIOD_TO_DATE,
ACTUAL_PERIOD_TO_DATE,
ENCUMB_PERIOD_TO_DATE)
select
l_ProjTab(i),
l_TaskTab(i),
l_TTaskTab(i),
l_RlmiTab(i),
'BGT',
p_set_of_books_id,
l_BdgtVerTab(i),
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_PeriodTab(i),
l_StDateTab(i),
l_EdDateTab(i),
l_ParMemTab(i),
l_BurdCostTab(i),
0,
0
from dual;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After inserting BGT lines');
PA_DEBUG.set_err_stack('Inserted BGT lines');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Insert BGT Balances');
,p_procedure_name => 'INSERT_BGT_BALANCES' --Bug 5064900
,p_error_text => PA_DEBUG.G_Err_Stack );
END INSERT_BGT_BALANCES;
select
draft_array.budget_entry_method_code,
baselined_array.budget_entry_method_code,
draft_array.resource_list_id,
baselined_array.resource_list_id,
draft_array.burdened_cost,
baselined_array.burdened_cost
from
(select
pbv_b.budget_entry_method_code budget_entry_method_code,
pbv_b.resource_list_id resource_list_id,
pbl_b.burdened_cost burdened_cost,
pra_b.resource_list_member_id resource_list_member_id,
pra_b.project_id project_id,
pra_b.task_id task_id,
pbl_b.start_date start_date
from pa_budget_versions pbv_b,
pa_budgetary_control_options pbco_b,
pa_budget_lines pbl_b,
pa_resource_assignments pra_b
where pbv_b.project_id = p_project_id
and pbv_b.budget_status_code = 'B'
and pbv_b.current_flag ='Y'
and pbco_b.bdgt_cntrl_flag = 'Y'
and pbco_b.budget_type_code = pbv_b.budget_type_code
and pbco_b.project_id = pbv_b.project_id
and ((p_bdgt_ctrl_type = 'GL' and pbco_b.external_budget_code = 'GL')
or
(p_bdgt_ctrl_type = 'CC' and pbco_b.external_budget_code = 'CC')
or
(p_bdgt_ctrl_type = 'GL' and pbco_b.external_budget_code is null))
and pbv_b.budget_version_id = pra_b.budget_version_id
and pra_b.resource_assignment_id = pbl_b.resource_assignment_id) baselined_array,
(select
pbv_d.budget_entry_method_code budget_entry_method_code,
pbv_d.resource_list_id resource_list_id,
pbl_d.burdened_cost burdened_cost,
pra_d.resource_list_member_id resource_list_member_id,
pra_d.project_id project_id,
pra_d.task_id task_id,
pbl_d.start_date start_date
from pa_budget_versions pbv_d,
pa_budgetary_control_options pbco_d,
pa_budget_lines pbl_d,
pa_resource_assignments pra_d
where pbv_d.project_id = p_project_id
and pbv_d.budget_status_code in ('W','S')
and pbco_d.bdgt_cntrl_flag = 'Y'
and pbco_d.budget_type_code = pbv_d.budget_type_code
and pbco_d.project_id = pbv_d.project_id
and ((p_bdgt_ctrl_type = 'GL' and pbco_d.external_budget_code = 'GL')
or
(p_bdgt_ctrl_type = 'CC' and pbco_d.external_budget_code = 'CC')
or
(p_bdgt_ctrl_type = 'GL' and pbco_d.external_budget_code is null))
and pbv_d.budget_version_id = pra_d.budget_version_id
and pra_d.resource_assignment_id = pbl_d.resource_assignment_id) draft_array
where baselined_array.project_id = draft_array.project_id(+)
and baselined_array.resource_list_member_id = draft_array.resource_list_member_id (+)
and baselined_array.task_id = draft_array.task_id(+)
and baselined_array.start_date = draft_array.start_date(+);
PROCEDURE INSERT_VI_ER_BTC_TXNS(
p_packet_id IN NUMBER,
p_sob_id IN NUMBER,
p_project_id IN NUMBER,
p_budget_version_id IN NUMBER,
p_bdgt_intg_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2) IS
l_ExpProjTab PA_PLSQL_DATATYPES.IdTabTyp;
select cdl.project_id,
cdl.task_id,
ei.EXPENDITURE_TYPE,
trunc(ei.EXPENDITURE_ITEM_DATE) expenditure_item_date,
-- nvl(ei.override_to_organization_id,exp.incurred_by_organization_id) organization_id, -- 7531681
nvl(ei.override_to_organization_id,( select exp.incurred_by_organization_id from pa_expenditures_all exp
where exp.expenditure_id = ei.expenditure_id)) organization_id, -- 7531681
gl.PERIOD_NAME,
gl.PERIOD_YEAR,
gl.PERIOD_NUM,
cdl.expenditure_item_id,
cdl.line_num,
/** Commented out for burdening enhancements
--decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
--decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
--decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
--decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
**/
decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,0) entered_dr,
decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,0) entered_cr,
decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,0) accounted_dr,
decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,0) accounted_cr,
cdl.gl_date,
cdl.pa_date,
cdl.dr_code_combination_id,
cdl.org_id,
cdl.budget_ccid
--PA.M
, ei.po_line_id
,'EXP'
,cdl.expenditure_item_id
,cdl.line_num
--PA.M selecting -99 for parent bc pkt for CWK BTC EIs
,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
from pa_expenditure_items_all ei,
-- pa_expenditures_all exp, -- 7531681
pa_cost_distribution_lines_all cdl,
gl_period_STATUSES gl,
pa_tasks pt
where cdl.project_id = p_project_id
and pt.task_id = ei.task_id
-- and ei.expenditure_id = exp.expenditure_id -- 7531681
--and trunc(gl.end_date) = trunc(cdl.gl_date)
--commented above since AP gl_date = transaction date rather end date of period
and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
and gl.application_id = 101
and gl.set_of_books_id = p_sob_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ('O','F')
--and nvl(cdl.amount,0) <> 0 -- filter burden transactions
and ei.expenditure_item_id = cdl.expenditure_item_id
and cdl.line_type = 'R'
and cdl.reversed_flag is null
and cdl.line_num_reversed is null
--and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
--PA.M: Added ST OT where PO Line ID is populated
and (((ei.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
) --'ER'
or
(ei.system_linkage_function in ('ST', 'OT')
and ei.po_line_id is not null)
)
or (ei.system_linkage_function = 'BTC'
/* 7531681 start */
and exists (select null
from pa_expenditure_items_all ei1,
pa_cost_distribution_lines_all cdl1
where ( cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and cdl1.project_id = p_project_id
and ei1.expenditure_item_id = cdl1.expenditure_item_id
)
and ((ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
UNION ALL
select null from pa_expenditure_items_all ei1,
pa_aud_cost_dist_lines aud
WHERE
( aud.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and aud.expenditure_item_id = ei1.expenditure_item_id
)
and ( (ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
)));
/* 7531681 and exists (select null
from pa_expenditure_items_all ei1,
pa_cost_distribution_lines_all cdl1,
pa_aud_cost_dist_lines aud
--PA.M added outer join to cdl1 and aud plus changed the or to and
-- cos if there are no records in aud,
-- then this will not pick up the BTC txns
where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
)
--or
and
( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
and aud.expenditure_item_id(+) = ei1.expenditure_item_id
)
and cdl1.expenditure_item_id = ei1.expenditure_item_id
--PA.M: Added ST OT where PO Line ID is populated
and ( (ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
)
)
); 7531681 */
and exists (select null
from pa_bc_commitments_all bc,
pa_cost_distribution_lines_all cdl1
where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and bc.document_header_id = to_number(cdl1.system_reference2)
and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
select cdl.project_id,
cdl.task_id,
ei.EXPENDITURE_TYPE,
trunc(ei.EXPENDITURE_ITEM_DATE) expenditure_item_date,
-- nvl(ei.override_to_organization_id,exp.incurred_by_organization_id) organization_id, -- 7531681
nvl(ei.override_to_organization_id,( select exp.incurred_by_organization_id from pa_expenditures_all exp
where exp.expenditure_id = ei.expenditure_id)) organization_id, -- 7531681
gl.PERIOD_NAME,
gl.PERIOD_YEAR,
gl.PERIOD_NUM,
cdl.expenditure_item_id,
cdl.line_num,
/** Commented out for Burdening enhancements
--decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
--decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
--decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
--decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
**/
decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,0) entered_dr,
decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
,0) entered_cr,
decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,0) accounted_dr,
decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
,0) accounted_cr,
cdl.gl_date,
cdl.pa_date,
cdl.dr_code_combination_id,
cdl.org_id,
cdl.budget_ccid
--PA.M
,ei.po_line_id
,'EXP'
,cdl.expenditure_item_id
,cdl.line_num
--PA.M selecting -99 for parent bc pkt for CWK BTC EIs
,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
from pa_expenditure_items_all ei,
-- pa_expenditures_all exp, -- 7531681
pa_cost_distribution_lines_all cdl,
gl_period_STATUSES gl,
pa_tasks pt
where cdl.project_id = p_project_id
and pt.task_id = ei.task_id
-- and ei.expenditure_id = exp.expenditure_id -- 7531681
--and trunc(gl.end_date) = trunc(cdl.gl_date)
--commented above since AP gl_date = transaction date rather end date of period
and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
and gl.application_id = 101
and gl.set_of_books_id = p_sob_id
and gl.adjustment_period_flag = 'N'
--and gl.closing_status = decode(p_bdgt_intg_flag, 'Y', 'O', gl.closing_status)
--and nvl(cdl.amount,0) <> 0 -- filter burden transactions
and ei.expenditure_item_id = cdl.expenditure_item_id
and cdl.line_type = 'R'
and cdl.reversed_flag is null
and cdl.line_num_reversed is null
--and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
--PA.M: Added ST OT where PO Line ID is populated
and (( (ei.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)--'ER'
or
(ei.system_linkage_function in ('ST', 'OT')
and ei.po_line_id is not null)
)
or (ei.system_linkage_function = 'BTC'
/* 7531681 start */
and exists (select null
from pa_expenditure_items_all ei1,
pa_cost_distribution_lines_all cdl1
where ( cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and cdl1.project_id = p_project_id
and ei1.expenditure_item_id = cdl1.expenditure_item_id
)
and (( ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
UNION ALL
select null from pa_expenditure_items_all ei1,
pa_aud_cost_dist_lines aud
WHERE
( aud.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and aud.expenditure_item_id = ei1.expenditure_item_id
)
and ( (ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
)
)
);
/* 7531681 and exists (select null
from pa_expenditure_items_all ei1,
pa_cost_distribution_lines_all cdl1,
pa_aud_cost_dist_lines aud
--PA.M added outer join to cdl1 and aud plus changed the or to and
-- cos if there are no records in aud,
-- then this will not pick up the BTC txns
where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
)
-- or
and
( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
and aud.expenditure_item_id(+) = ei1.expenditure_item_id
)
and cdl1.expenditure_item_id = ei1.expenditure_item_id
--PA.M: Added ST OT where PO Line ID is populated
and ( (ei1.system_linkage_function = 'VI' and transaction_source in
('AP INVOICE',
'INTERCOMPANY_AP_INVOICES',
'INTERPROJECT_AP_INVOICES',
'AP NRTAX',
'PO RECEIPT',
'PO RECEIPT NRTAX',
'AP VARIANCE',
'AP DISCOUNTS',
'PO RECEIPT NRTAX PRICE ADJ',
'PO RECEIPT PRICE ADJ',
'AP ERV',
'AP SELF ASSESSED TAX')
)
or
(ei1.system_linkage_function in ('ST', 'OT')
and ei1.po_line_id is not null)
)
)
)
); 7531681 */
and exists (select null
from pa_bc_commitments_all bc,
pa_cost_distribution_lines_all cdl1
where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
and bc.document_header_id = to_number(cdl1.system_reference2)
and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
PA_DEBUG.set_err_stack('Insert VIERBTC Txns');
l_ExpProjTab.Delete;
l_ExpTaskTab.Delete;
l_ExpExpTypTab.Delete;
l_ExpEiDateTab.Delete;
l_ExpExpOrgTab.Delete;
l_ExpPeriodTab.Delete;
l_ExpPdYearTab.Delete;
l_ExpPdNumTab.Delete;
l_ExpDocDistTab.Delete;
l_ExpDocHdrTab.Delete;
l_ExpEntDrTab.Delete;
l_ExpEntCrTab.Delete;
l_ExpAcctDrTab.Delete;
l_ExpAcctCrTab.Delete;
l_ExpGlDateTab.Delete;
l_ExpPaDateTab.Delete;
l_ExpTxnCCIDTab.Delete;
l_ExpOrgIdTab.Delete;
l_ExpBdgtCCIDTab.Delete;
l_ExpPoLineIdTab.Delete;
l_ExpReference1Tab.delete;
l_ExpReference2Tab.delete;
l_ExpReference3Tab.delete;
l_ExpParBcPktIdTab.delete;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before inserting VIERBTC');
insert into pa_bc_packets (
PACKET_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ENTERED_DR,
ENTERED_CR,
accounted_dr,
accounted_cr,
BUDGET_VERSION_ID,
bc_packet_id,
funds_process_mode,
parent_bc_packet_id,
gl_date,
pa_date,
txn_ccid,
result_code,
balance_posted_flag,
org_id,
burden_cost_flag,
old_budget_ccid
--PA.M
,document_line_id
,reference1
,reference2
,reference3 )
select
p_packet_id,
l_ExpProjTab(m),
l_ExpTaskTab(m),
l_ExpExpTypTab(m),
l_ExpEiDateTab(m),
'A',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
p_sob_id,
'Project Accounting',
'Expenditures',
'EXP',
l_ExpExpOrgTab(m),
l_ExpPeriodTab(m),
l_ExpPdYearTab(m),
l_ExpPdNumTab(m),
l_ExpDocHdrTab(m),
l_ExpDocDistTab(m),
l_ExpEntDrTab(m),
l_ExpEntCrTab(m),
l_ExpAcctDrTab(m),
l_ExpAcctCrTab(m),
p_budget_version_id,
pa_bc_packets_s.nextval,
'B',
--PA.M insert parent bc pkt as -99 for CWK BTC EIs
--null,
l_ExpParBcPktIdTab(m),
l_ExpGlDateTab(m),
l_ExpPaDateTab(m),
l_ExpTxnCCIDTab(m),
'P',
'N',
l_ExpOrgIdTab(m),
'N',
l_ExpBdgtCCIDTab(m)
--PA.M
,l_ExpPoLineIdTab(m)
,l_ExpReference1Tab(m)
,l_ExpReference2Tab(m)
,l_ExpReference3Tab(m)
from dual;
PA_DEBUG.set_err_stack('Inserted VIERBTC Txns');
,p_procedure_name => 'INSERT_VI_ER_BTC_TXNS' -- Bug 5064900
,p_error_text => PA_DEBUG.G_Err_Stack );
END INSERT_VI_ER_BTC_TXNS;
PROCEDURE INSERT_COMMITMENT_TXNS(
p_packet_id IN NUMBER,
p_sob_id IN NUMBER,
p_project_id IN NUMBER,
p_budget_version_id IN NUMBER,
p_bdgt_ctrl_type IN VARCHAR2,
p_bdgt_intg_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message_code OUT NOCOPY VARCHAR2) IS
l_ProjTab PA_PLSQL_DATATYPES.IdTabTyp;
select bc.project_id,
bc.task_id,
bc.EXPENDITURE_TYPE,
trunc(bc.EXPENDITURE_ITEM_DATE) expenditure_item_date,
bc.set_of_books_id,
bc.je_category_name,
bc.je_source_name,
bc.document_type,
bc.expenditure_organization_id,
bc.PERIOD_NAME,
bc.PERIOD_YEAR,
bc.PERIOD_NUM,
bc.document_header_id,
bc.document_distribution_id,
bc.entered_dr,
bc.entered_cr,
bc.accounted_dr,
bc.accounted_cr,
bc.bc_packet_id,
bc.parent_bc_packet_id,
bc.gl_date,
bc.pa_date,
bc.txn_ccid,
bc.org_id,
bc.burden_cost_flag,
bc.bc_commitment_id,
bc.budget_ccid
--PA.M
,bc.document_line_id
,bc.summary_record_flag
,bc.reference1
,bc.reference2
,bc.reference3
-- R12 Funds Management Uptake
,bc.burden_method_code
from pa_bc_commitments_all bc,
pa_tasks pt
--,pa_budget_versions pbv,
--pa_budget_types pbt
where bc.project_id = p_project_id
and pt.task_id = bc.task_id;
PA_DEBUG.set_err_stack('Insert Commitment Txns');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Insert Commitment Txns');
l_ProjTab.Delete;
l_TaskTab.Delete;
l_ExpTypTab.Delete;
l_EiDateTab.Delete;
l_SobTab.Delete;
l_CatNameTab.Delete;
l_SrcNameTab.Delete;
l_DocTypTab.Delete;
l_ExpOrgTab.Delete;
l_PeriodTab.Delete;
l_PdYearTab.Delete;
l_PdNumTab.Delete;
l_DocHdrTab.Delete;
l_DocDistTab.Delete;
l_EntDrTab.Delete;
l_EntCrTab.Delete;
l_AcctDrTab.Delete;
l_AcctCrTab.Delete;
l_BcPktTab.Delete;
l_ParBCPktTab.Delete;
l_GlDateTab.Delete;
l_PaDateTab.Delete;
l_TxnCCIDTab.Delete;
l_ParResTab.Delete;
l_OrgIdTab.Delete;
l_BurCstFlagTab.Delete;
l_BcCommIdTab.Delete;
l_BdgtCCIDTab.Delete;
l_DocLineIdTab.Delete;
l_SummRecFlagTab.Delete;
l_pktReference1Tab.Delete;
l_pktReference2Tab.Delete;
l_pktReference3Tab.Delete;
l_BurMethodcodeTab.delete;
-- Insert Contract Commitments
IF (p_bdgt_intg_flag = 'Y') THEN
PA_DEBUG.set_err_stack('Insert CC, linked'); --Bug 3912094
insert into pa_bc_packets (
PACKET_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ENTERED_DR,
ENTERED_CR,
accounted_dr,
accounted_cr,
BUDGET_VERSION_ID,
bc_packet_id,
funds_process_mode,
parent_bc_packet_id,
gl_date,
pa_date,
txn_ccid,
result_code,
balance_posted_flag,
org_id,
burden_cost_flag,
bc_commitment_id,
old_budget_ccid
--PA.M
,document_line_id
,summary_record_flag
,reference1
,reference2
,reference3
-- R12 Funds Management Uptake
,burden_method_code
)
select
p_packet_id,
l_ProjTab(i),
l_TaskTab(i),
l_ExpTypTab(i),
l_EiDateTab(i),
'E',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_SobTab(i),
l_CatNameTab(i),
l_SrcNameTab(i),
l_DocTypTab(i),
l_ExpOrgTab(i),
l_PeriodTab(i),
l_PdYearTab(i),
l_PdNumTab(i),
l_DocHdrTab(i),
l_DocDistTab(i),
l_EntDrTab(i),
l_EntCrTab(i),
l_AcctDrTab(i),
l_AcctCrTab(i),
p_budget_version_id,
l_BcPktTab(i),
'B',
l_ParBCPktTab(i),
l_GlDateTab(i),
l_PaDateTab(i),
l_TxnCCIDTab(i),
'P',
'N',
l_OrgIdTab(i),
l_BurCstFlagTab(i),
l_BcCommIdTab(i),
l_BdgtCCIDTab(i)
--Pa.M
,l_DocLineIdTab(i)
,l_SummRecFlagTab(i)
,l_pktReference1Tab(i)
,l_pktReference2Tab(i)
,l_pktReference3Tab(i)
-- R12 Funds Management Uptake
,l_BurMethodcodeTab(i)
from gl_period_statuses gl
where l_DocTypTab(i) in ('CC_C_CO', 'CC_P_CO')
--and l_GlDateTab(i) = gl.end_date
and trunc(l_GlDateTab(i)) between trunc(gl.start_date) and trunc(gl.end_date)
and gl.application_id = 101
and gl.set_of_books_id = p_sob_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ( 'O', 'F');
PA_DEBUG.set_err_stack('Insert CC, no link'); --Bug 3912094
insert into pa_bc_packets (
PACKET_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ENTERED_DR,
ENTERED_CR,
accounted_dr,
accounted_cr,
BUDGET_VERSION_ID,
bc_packet_id,
funds_process_mode,
parent_bc_packet_id,
gl_date,
pa_date,
txn_ccid,
result_code,
balance_posted_flag,
org_id,
burden_cost_flag,
bc_commitment_id,
old_budget_ccid
--PA.M
,document_line_id
,summary_record_flag
,reference1
,reference2
,reference3
-- R12 Funds Management Uptake
,burden_method_code
)
select
p_packet_id,
l_ProjTab(j),
l_TaskTab(j),
l_ExpTypTab(j),
l_EiDateTab(j),
'E',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_SobTab(j),
l_CatNameTab(j),
l_SrcNameTab(j),
l_DocTypTab(j),
l_ExpOrgTab(j),
l_PeriodTab(j),
l_PdYearTab(j),
l_PdNumTab(j),
l_DocHdrTab(j),
l_DocDistTab(j),
l_EntDrTab(j),
l_EntCrTab(j),
l_AcctDrTab(j),
l_AcctCrTab(j),
p_budget_version_id,
l_BcPktTab(j),
'B',
l_ParBCPktTab(j),
l_GlDateTab(j),
l_PaDateTab(j),
l_TxnCCIDTab(j),
'P',
'N',
l_OrgIdTab(j),
l_BurCstFlagTab(j),
l_BcCommIdTab(j),
l_BdgtCCIDTab(j)
--Pa.M
,l_DocLineIdTab(j)
, l_SummRecFlagTab(j)
,l_pktReference1Tab(j)
,l_pktReference2Tab(j)
,l_pktReference3Tab(j)
-- R12 Funds Management Uptake
,l_BurMethodcodeTab(j)
from dual
where l_DocTypTab(j) in ('CC_C_CO', 'CC_P_CO');
PA_DEBUG.set_err_stack('Insert Std, linked');
insert into pa_bc_packets (
PACKET_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ENTERED_DR,
ENTERED_CR,
accounted_dr,
accounted_cr,
BUDGET_VERSION_ID,
bc_packet_id,
funds_process_mode,
parent_bc_packet_id,
gl_date,
pa_date,
txn_ccid,
result_code,
balance_posted_flag,
org_id,
burden_cost_flag,
bc_commitment_id,
old_budget_ccid
--PA.M
,document_line_id
,summary_record_flag
,reference1
,reference2
,reference3
-- R12 Funds Management Uptake
,burden_method_code
)
select
p_packet_id,
l_ProjTab(k),
l_TaskTab(k),
l_ExpTypTab(k),
l_EiDateTab(k),
'E',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_SobTab(k),
l_CatNameTab(k),
l_SrcNameTab(k),
l_DocTypTab(k),
l_ExpOrgTab(k),
l_PeriodTab(k),
l_PdYearTab(k),
l_PdNumTab(k),
l_DocHdrTab(k),
l_DocDistTab(k),
l_EntDrTab(k),
l_EntCrTab(k),
l_AcctDrTab(k),
l_AcctCrTab(k),
p_budget_version_id,
l_BcPktTab(k),
'B',
l_ParBCPktTab(k),
l_GlDateTab(k),
l_PaDateTab(k),
l_TxnCCIDTab(k),
'P',
'N',
l_OrgIdTab(k),
l_BurCstFlagTab(k),
l_BcCommIdTab(k),
l_BdgtCCIDTab(k)
--Pa.M
,l_DocLineIdTab(k)
, l_SummRecFlagTab(k)
,l_pktReference1Tab(k)
,l_pktReference2Tab(k)
,l_pktReference3Tab(k)
-- R12 Funds Management Uptake
,l_BurMethodcodeTab(k)
from gl_period_statuses gl
where l_DocTypTab(k) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY')
--and l_GlDateTab(k) = gl.end_date
and trunc(l_GlDateTab(k)) between trunc(gl.start_date) and trunc(gl.end_date)
and gl.application_id = 101
and gl.set_of_books_id = p_sob_id
and gl.adjustment_period_flag = 'N'
and gl.closing_status in ( 'O', 'F');
PA_DEBUG.set_err_stack('Insert Std, no link');
insert into pa_bc_packets (
PACKET_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
ACTUAL_FLAG,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
JE_CATEGORY_NAME,
JE_SOURCE_NAME,
DOCUMENT_TYPE,
EXPENDITURE_ORGANIZATION_ID,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID,
DOCUMENT_DISTRIBUTION_ID,
ENTERED_DR,
ENTERED_CR,
accounted_dr,
accounted_cr,
BUDGET_VERSION_ID,
bc_packet_id,
funds_process_mode,
parent_bc_packet_id,
gl_date,
pa_date,
txn_ccid,
result_code,
balance_posted_flag,
org_id,
burden_cost_flag,
bc_commitment_id,
old_budget_ccid
--PA.M
,document_line_id
,summary_record_flag
,reference1
,reference2
,reference3
-- R12 Funds Management Uptake
,burden_method_code
)
select
p_packet_id,
l_ProjTab(l),
l_TaskTab(l),
l_ExpTypTab(l),
l_EiDateTab(l),
'E',
'P',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_SobTab(l),
l_CatNameTab(l),
l_SrcNameTab(l),
l_DocTypTab(l),
l_ExpOrgTab(l),
l_PeriodTab(l),
l_PdYearTab(l),
l_PdNumTab(l),
l_DocHdrTab(l),
l_DocDistTab(l),
l_EntDrTab(l),
l_EntCrTab(l),
l_AcctDrTab(l),
l_AcctCrTab(l),
p_budget_version_id,
l_BcPktTab(l),
'B',
l_ParBCPktTab(l),
l_GlDateTab(l),
l_PaDateTab(l),
l_TxnCCIDTab(l),
'P',
'N',
l_OrgIdTab(l),
l_BurCstFlagTab(l),
l_BcCommIdTab(l),
l_BdgtCCIDTab(l)
--Pa.M
, l_DocLineIdTab(l)
, l_SummRecFlagTab(l)
,l_pktReference1Tab(l)
,l_pktReference2Tab(l)
,l_pktReference3Tab(l)
-- R12 Funds Management Uptake
,l_BurMethodcodeTab(l)
from dual
where l_DocTypTab(l) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY');
PA_DEBUG.set_err_stack('Inserted Commitments');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Insert Commitment Txns');
,p_procedure_name => 'INSERT_COMMITMENT_TXNS' -- Bug 5064900
,p_error_text => PA_DEBUG.G_Err_Stack );
END INSERT_COMMITMENT_TXNS;
SELECT org_id INTO l_org_id FROM pa_projects_all WHERE project_id = p_project_id;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before selecting SOB');
select to_number(set_of_books_id) into l_sob_id from pa_implementations_all --Bug 6524116
where org_id = l_org_id;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After selecting SOB = '|| l_sob_id);
pa_sweeper.update_act_enc_balance(
x_return_status => x_return_status,
x_error_message_code => x_error_message_code
--PA.M
,P_Project_Id => P_project_id);
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert BGT Balances');
PA_DEBUG.set_err_stack('M:Insert BGT Balances');
INSERT_BGT_BALANCES(
p_project_id => p_project_id,
p_budget_version_id => p_budget_version_id,
p_set_of_books_id => l_sob_id,
p_bdgt_intg_flag => p_bdgt_intg_flag,
--p_fc_reqd => l_FcReqd, --R12 Funds Management Uptake : obsolete variable
x_return_status => x_return_status,
x_error_message_code => x_error_message_code );
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert BGT Balances, RetSts = ' || x_return_status);
select gl_bc_packets_s.nextval into l_packet_id from dual;
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert Commitment Txns');
INSERT_COMMITMENT_TXNS(
p_packet_id => l_packet_id,
p_sob_id => l_sob_id,
p_project_id => p_project_id,
p_budget_version_id => p_budget_version_id,
p_bdgt_ctrl_type => p_bdgt_ctrl_type,
p_bdgt_intg_flag => p_bdgt_intg_flag,
x_return_status => x_return_status,
x_error_message_code => x_error_message_code);
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert Commitment Txns, RetSts = ' ||x_return_status);
PA_DEBUG.set_err_stack('M:Insert VIERBTC');
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert VI ER BTC');
INSERT_VI_ER_BTC_TXNS(
p_packet_id => l_packet_id,
p_sob_id => l_sob_id,
p_project_id => p_project_id,
p_budget_version_id => p_budget_version_id,
p_bdgt_intg_flag => p_bdgt_intg_flag,
x_return_status => x_return_status,
x_error_message_code => x_error_message_code);
pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert VI ER BTC, RetSts = ' ||x_return_status);
-- R12 Funds Management Uptake : Deleted call to PA_FUNDS_CONTROL_PKG.upd_bdgt_encum_bal
-- as it will be fired during tieback .
-- R12 Funds Management Uptake : Added below update for updating pa_bc_packets with
-- uncommited newly created budget version id.
If l_return_status not in ('F','T') then
UPDATE pa_bc_packets
SET budget_version_id = p_baselined_budget_version_id
WHERE packet_id = l_packet_id
AND budget_version_id = p_budget_version_id;
-- R12 Funds Management Uptake : Update pa_bc_balances (draft version) with the latest budget version ..
UPDATE pa_bc_balances
SET budget_version_id = p_baselined_budget_version_id
WHERE budget_version_id = p_budget_version_id;