[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM dual
WHERE EXISTS
(SELECT 'X'
FROM PA_BUDGET_ACCT_LINES PBA,
GL_PERIOD_STATUSES GLS
WHERE GLS.application_id = PA_Period_Process_Pkg.Application_ID -- 101
AND GLS.set_of_books_id = c_set_of_books_id
AND GLS.period_name = PBA.gl_period_name
AND GLS.closing_status not in ('O' , 'F' )
AND PBA.curr_ver_available_amount <> PBA.prev_ver_available_amount
AND PBA.budget_version_id = c_budget_version_id) ;
PROCEDURE Update_bc_packets_fail(p_bud_ver_id IN NUMBER,
p_status_code IN VARCHAR2);
PROCEDURE Update_bc_packets_pass(p_bud_ver_id IN NUMBER);
PROCEDURE DELETE_DRAFT_BC_PACKETS(p_draft_bud_ver_id IN NUMBER);
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT imp.set_of_books_id, sob.currency_code, SOB.PERIOD_SET_NAME
INTO l_set_of_books_id, l_currency_code, l_period_set_name
FROM PA_IMPLEMENTATIONS_all IMP, GL_SETS_OF_BOOKS SOB
where IMP.set_of_books_id = SOB.set_of_books_id
AND imp.org_id = nvl(g_org_id, imp.org_id);
select user_je_category_name
into l_category_name
from gl_je_categories
where je_category_name = 'Budget' ;
select user_je_source_name
into l_source_name
from gl_je_sources
where je_source_name = 'Project Accounting' ;
delete from IGC_CC_INTERFACE
where cc_header_id = p_budget_version_id
and document_type = 'PA';
log_message('updated pa_budget_acct_lines table with accounted_amount ');
UPDATE PA_BUDGET_ACCT_LINES PBA
SET PBA.accounted_amount = nvl(PBA.curr_ver_available_amount,0) - nvl(PBA.prev_ver_available_amount,0)
WHERE PBA.budget_version_id = p_budget_version_id; */
UPDATE PA_BUDGET_ACCT_LINES PBA
SET PBA.accounted_amount = nvl(PBA.curr_ver_budget_amount,0) - nvl(PBA.prev_ver_budget_amount,0)
WHERE PBA.budget_version_id = p_budget_version_id;
log_message('rows updated .........'||to_char(sql%rowcount)) ;
select segment1
into l_project_num
from pa_projects_all p,
pa_budget_versions bv
where p.project_id = bv.project_id
and bv.budget_version_id = p_budget_version_id;
log_message('inserting into table IGC_CC_INTERFACE ..... ');
Insert INTO IGC_CC_INTERFACE (
CC_HEADER_ID ,
DOCUMENT_TYPE ,
CODE_COMBINATION_ID ,
PERIOD_SET_NAME ,
PERIOD_NAME ,
BATCH_LINE_NUM ,
CC_TRANSACTION_DATE ,
CC_FUNC_CR_AMT , --Bug 6633262
CC_FUNC_DR_AMT ,
JE_SOURCE_NAME ,
JE_CATEGORY_NAME ,
ACTUAL_FLAG ,
BUDGET_DEST_FLAG ,
SET_OF_BOOKS_ID ,
ENCUMBRANCE_TYPE_ID ,
CURRENCY_CODE ,
REFERENCE_1 ,
REFERENCE_2 ,
REFERENCE_3 ,
REFERENCE_4 ,
REFERENCE_5 ,
creation_date ,
created_by ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CC_ACCT_LINE_ID
)
SELECT
p_budget_version_id,
'PA',
PBA.code_combination_id,
l_period_set_name,
PBA.gl_period_name,
to_number(rownum),
PBA.start_date ,
--Bug 6524116 Changed 0 to NULL
decode(GL.account_type, -- CC_FUNC_CR_AMT column
'A',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
1, (PBA.accounted_amount * l_chk_res_unres_multi ),
-1, NULL),
'E',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
1, (PBA.accounted_amount * l_chk_res_unres_multi ),
-1, NULL),
'L',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
1, NULL ,
-1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
'R',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
1, NULL ,
-1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
'O',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
1, NULL ,
-1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
NULL ),
decode(GL.account_type, -- CC_FUNC_DR_AMT column
'A',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
-1,abs(PBA.accounted_amount * l_chk_res_unres_multi ),
1, NULL),
'E',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
-1,abs(PBA.accounted_amount * l_chk_res_unres_multi ),
1, NULL),
'L',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
-1, NULL ,
1,PBA.accounted_amount * l_chk_res_unres_multi ),
'R',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
-1, NULL ,
1,PBA.accounted_amount * l_chk_res_unres_multi ),
'O',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
-1, NULL ,
1,PBA.accounted_amount * l_chk_res_unres_multi ),
NULL ),
-- l_source_name,
-- l_category_name,
'Project Accounting',
'Budget',
'E' ,
'C' ,
l_set_of_books_id,
p_enc_type_id ,
l_currency_code ,
'PA',
p_budget_version_id,
PBA.budget_acct_line_id,
l_project_num, --Bug 6524116
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
pba.budget_acct_line_id
FROM
PA_BUDGET_ACCT_LINES PBA,
GL_PERIOD_STATUSES GP,
GL_CODE_COMBINATIONS GL
WHERE PBA.accounted_amount <> 0
AND PBA.budget_version_id = p_budget_version_id
AND GL.code_combination_id = PBA.code_combination_id
AND GP.set_of_books_id = l_set_of_books_id
AND GP.period_name = PBA.gl_period_name
AND GP.application_id = PA_Period_Process_Pkg.Application_ID; -- 101
log_message('rows inserted into IGC Table .. '||sql%rowcount);
UPDATE PA_BUDGET_ACCT_LINES PBA
SET (PBA.funds_check_status_code,
PBA.funds_check_result_code) = (SELECT ICC.status_code,
ICC.cbc_result_code
FROM IGC_CC_INTERFACE ICC
WHERE ICC.DOCUMENT_TYPE = 'PA'
AND ICC.CC_HEADER_ID = p_budget_version_id
AND ICC.reference_1 = 'PA'
AND ICC.reference_2 = PBA.budget_version_id
AND ICC.reference_3 = PBA.budget_acct_line_id)
WHERE ((PBA.budget_version_id,
PBA.budget_acct_line_id) IN
(SELECT ICC.reference_2,
ICC.reference_3
FROM IGC_CC_INTERFACE ICC
WHERE ICC.cc_header_id = p_budget_version_id
AND ICC.DOCUMENT_TYPE = 'PA'
AND ICC.reference_1 = 'PA')) ;
log_message('rows updated into PA_BUDGET_ACCT_LINES Table .. '||sql%rowcount);
SELECT decode(p_calling_mode,'CHECK',
(decode(substr(l_return_status,1,1),'S','P',
'A','P',
'F','F',
'T','F' )),
'RESERVE',
(decode(substr(l_return_status,1,1),'S','A',
'A','A',
'F','F',
'T','F' )),
'UNRESERVE',
(decode(substr(l_return_status,1,1),'S','A',
'A','A',
'F','F',
'T','F' )),
'YEAR_END_ROLLOVER',
(decode(substr(l_return_status,1,1),'S','A',
'A','A',
'F','F',
'T','F' )),
'UNRESERVE_YEAR_END_ROLLOVER',
(decode(substr(l_return_status,1,1),'S','A',
'A','A',
'F','F',
'T','F' )),
'F')
INTO l_funds_chk_rsrv_status
FROM dual ;
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)
or
(p_bdgt_ctrl_type is null and pbco.external_budget_code is null))
and a.project_id = p_project_id;
PA_SWEEPER.UPDATE_ACT_ENC_BALANCE(
x_return_status => x_return_status,
x_error_message_code => x_error_message_code,
p_project_id => p_project_id);
log_message('Create_txn_lines_in_bc_balance:'|| 'Delete draft budget version- '||p_draft_budget_version_id);
Delete pa_bc_balances
where budget_version_id = p_draft_budget_version_id;
log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records deleted from pa_bc_balances');
Delete pa_bc_packets
where budget_version_id = p_draft_budget_version_id;
log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records deleted from pa_bc_packets');
log_message('Create_txn_lines_in_bc_balance: Open cursor c_delbal to delete old verisons of budget');
l_BalRowIdTab.Delete;
log_message('Create_txn_lines_in_bc_balance: No record to delete, exit');
log_message('Create_txn_lines_in_bc_balance:'||l_BalRowIdTab.count||' records being deleted');
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 = g_cost_prev_bvid;
log_message('Create_txn_lines_in_bc_balance:Insert Close 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_draft_budget_version_id,
l_date,
l_login_id,
l_login_id,
l_date,
l_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 = g_cost_prev_bvid
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';
log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records inserted');
log_message('Create_txn_lines_in_bc_balance: Inserted closed period balances');
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)
values(
p_project_id,
t_task_id(i),
t_top_task_id(i),
t_rlmi(i),
'BGT',
p_set_of_books_id,
p_budget_version_id,
l_date,
l_login_id,
l_login_id,
l_date,
l_login_id,
t_period(i),
t_start_date(i),
t_end_date(i),
t_parent_rlmi(i),
t_burden_cost(i),
0,
0);
select pa.task_id,
pt.top_task_id,
pa.resource_list_member_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_base_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 to_number(set_of_books_id) into l_sob_id from pa_implementations_all
where org_id = g_org_id;
log_message('Establish_bc_balances: Before inserting BGT lines');
l_TaskTab.Delete;
l_TTaskTab.Delete;
l_RlmiTab.Delete;
l_PeriodTab.Delete;
l_StDateTab.Delete;
l_EdDateTab.Delete;
l_ParMemTab.Delete;
l_BurdCostTab.Delete;
log_message('Establish_bc_balances: Before Insert, no. of rec = '|| l_TaskTab.count);
SELECT sum(decode(nvl(PBL.burdened_cost,0),
0,nvl(PBL.raw_cost,0),
PBL.burdened_cost))
FROM PA_BUDGET_LINES PBL,
PA_BUDGET_VERSIONS PBV,
PA_RESOURCE_ASSIGNMENTS PRA
WHERE PBV.project_id = p_project_id
AND PBV.budget_version_id = PRA.budget_version_id
AND PRA.resource_assignment_id = PBL.resource_assignment_id
AND PBV.budget_version_id = c_budget_version_id;
Select PBV.Budget_type_code,
PBCO.Balance_type,
PBCO.External_budget_code,
PBCO.Encumbrance_Type_Id,
PBCO.Bdgt_cntrl_flag,
PBCO.gl_budget_version_id,
PBT.budget_amount_code,
BEM.entry_level_code
From PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
PA_BUDGET_VERSIONS PBV,
PA_BUDGET_TYPES PBT,
PA_BUDGET_ENTRY_METHODS BEM
WHERE PBCO.Project_Id = p_project_id
AND PBV.Budget_version_id = p_Budget_version_id
AND PBV.Budget_Type_Code = PBCO.Budget_Type_Code
AND PBT.Budget_type_code = PBV.Budget_type_code
AND PBT.Budget_type_code = PBV.Budget_type_code
AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code;
select bc_event_id, g_org_id
from pa_budget_lines
where budget_version_id = P_Budget_version_id
and bc_event_id is not null
union
select bc_rev_event_id, g_org_id
from pa_budget_lines
where budget_version_id = P_prev_budget_version_id
and bc_rev_event_id is not null;
SELECT nvl(template_flag,'N'), org_id
INTO l_template_flag , g_org_id
FROM pa_projects_all
WHERE project_id = p_project_id;
select 'CC'
into l_cc_budget_type_code
from pa_budgetary_control_options cc
where cc.project_id = p_project_id
and cc.external_budget_code = 'CC';
Select 'Y'
into g_cost_rebaseline_flag
from pa_budget_versions pbv
where pbv.project_id = p_project_id
and pbv.budget_version_id <> p_budget_version_id -- not the current budget
and pbv.budget_status_code = 'B'
and pbv.budget_type_code = l_budget_type_code
and rownum =1;
Select pbv.budget_version_id
into l_draft_budget_version_id
from pa_budget_versions pbv
where pbv.project_id = p_project_id
and pbv.budget_status_code = 'S' -- Changed from 'W' to 'S' (UT code fix)
and pbv.budget_type_code = l_budget_type_code;
log_message('Calling Delete draft bc pkt');
DELETE_DRAFT_BC_PACKETS(p_draft_bud_ver_id => l_draft_budget_version_id);
log_message(' After Calling Delete draft bc pkt');
Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
p_calling_mode => p_calling_mode);
Select decode(g_budget_amount_code,'C','Cost_Budget','R','Revenue_Budget')
into l_calling_module
from dual;
Select budget_type_code, encumbrance_type_id
into l_cc_budget_type_code, l_cc_encumbrance_type_id
from pa_budgetary_control_options
where project_id = p_project_id
and external_budget_code = 'CC' ;
Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
p_calling_mode => p_calling_mode);
select budget_version_id
into g_cc_current_bvid
from pa_budget_versions
where project_id = p_project_id
and budget_type_code = l_cc_budget_type_code
and budget_status_code = 'B'
and current_flag ='Y';
Select pbv.budget_version_id, budget_entry_method_code
into l_cc_budget_version_id, l_cc_budget_entry_level_code
from pa_budget_versions pbv
where pbv.project_id = p_project_id
and pbv.budget_type_code = l_cc_budget_type_code
and pbv.budget_status_code = 'B'
and pbv.current_flag = 'Y';
select bc_event_id, g_org_id
bulk collect into rejected_event_id_tab, ledger_id_tab
from pa_budget_lines bl
where budget_version_id = P_Budget_version_id
and bc_event_id is not null;
Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
Update_bc_packets_pass(p_bud_ver_id =>l_cc_budget_version_id);
PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
p_calling_mode => p_calling_mode);
Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
p_status_code=> 'R');
Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
p_status_code => 'R');
Update_bc_packets_fail(p_bud_ver_id =>l_cc_budget_version_id,
p_status_code=>'R');
Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
p_status_code => 'T');
Update_bc_packets_fail(p_bud_ver_id =>l_cc_budget_version_id,
p_status_code=>'T');
select length(l_sqlerrm) into l_dummy from dual;
SELECT budget_type_code,
encumbrance_type_id,
external_budget_code,
balance_type,
bdgt_cntrl_flag
FROM PA_BUDGETARY_CONTROL_OPTIONS
WHERE project_id = p_project_id
AND ( ( p_calling_mode = 'STANDARD' AND
( nvl(external_budget_code,'-1') IN ('GL','-1') ) )
OR
( p_calling_mode = 'COMMITMENT' AND
( nvl(external_budget_code,'-1') = 'CC') )
OR
( p_calling_mode = 'BUDGET' )
)
AND ( ( p_calling_mode = 'BUDGET' AND
( nvl(budget_type_code,'-1') = p_budget_type_code ) )
OR
( p_calling_mode IN ('COMMITMENT','STANDARD') )
)
AND (
( p_calling_mode = 'STANDARD'
AND ( ( nvl(balance_type,'-1') = 'E' AND
nvl(external_budget_code,'-1') = 'GL' )
OR ( nvl(balance_type,'-1') = '-1' AND
nvl(external_budget_code,'-1') = '-1' ))
)
OR
( p_calling_mode = 'COMMITMENT' AND
nvl(balance_type,'-1') = 'E'
)
OR
( p_calling_mode = 'BUDGET' ) );
SELECT budget_version_id
FROM PA_BUDGET_VERSIONS
WHERE project_id = p_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'B'
AND version_number = (Select MAX(version_number)
FROM PA_BUDGET_VERSIONS
WHERE project_id = p_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'B' );
SELECT decode(nvl(l_external_budget_code,'X'),'GL','G','CC','C','N')
into x_bdgt_intg_flag from dual ;
SELECT decode(l_external_budget_code,'GL','G','CC','C','N') into
x_bdgt_intg_flag from dual ;
UPDATE PA_BUDGET_ACCT_LINES
SET curr_ver_available_amount = curr_ver_available_amount - p_amount
WHERE budget_version_id = p_budget_version_id
AND gl_period_name = p_gl_period_name
AND code_combination_id = p_ccid ;
log_message('Updated '||to_char(sql%rowcount));
UPDATE PA_BUDGET_ACCT_LINES
SET curr_ver_available_amount = (curr_ver_budget_amount - prev_ver_budget_amount
+ prev_ver_available_amount)
WHERE budget_version_id = p_budget_version_id ;
log_message('Updated '||to_char(sql%rowcount));
SELECT 'X'
FROM DUAL
WHERE EXISTS
( SELECT 'x'
FROM PA_BUDGETARY_CONTROL_OPTIONS PBA
WHERE PBA.project_id = p_project_id
AND ( ( p_mode ='A' )
OR
( p_mode <> 'A' AND
PBA.external_budget_code = decode(p_mode,'S','GL','C','CC','-1'))));
log_message('Inserting records in PA_BUDGETARY_CONTROL_OPTIONS table');
insert into PA_BUDGETARY_CONTROL_OPTIONS
(
PROJECT_TYPE,
PROJECT_ID,
BALANCE_TYPE,
EXTERNAL_BUDGET_CODE,
GL_BUDGET_VERSION_ID,
ENCUMBRANCE_TYPE_ID,
BDGT_CNTRL_FLAG,
AMOUNT_TYPE,
BOUNDARY_CODE,
FUND_CONTROL_LEVEL_PROJECT,
FUND_CONTROL_LEVEL_TASK,
FUND_CONTROL_LEVEL_RES_GRP,
FUND_CONTROL_LEVEL_RES,
BUDGET_TYPE_CODE,
PROJECT_TYPE_ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
select
PROJECT_TYPE,
p_to_project_id,
BALANCE_TYPE,
EXTERNAL_BUDGET_CODE,
GL_BUDGET_VERSION_ID,
ENCUMBRANCE_TYPE_ID,
BDGT_CNTRL_FLAG,
AMOUNT_TYPE,
BOUNDARY_CODE,
FUND_CONTROL_LEVEL_PROJECT,
FUND_CONTROL_LEVEL_TASK,
FUND_CONTROL_LEVEL_RES_GRP,
FUND_CONTROL_LEVEL_RES,
BUDGET_TYPE_CODE,
PROJECT_TYPE_ORG_ID ,
SYSDATE ,
-1 ,
SYSDATE ,
-1 ,
-1
from PA_BUDGETARY_CONTROL_OPTIONS
where PROJECT_ID = p_from_project_id;
PROCEDURE Delete_draft_bc_packets(p_draft_bud_ver_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
g_procedure_name := 'Delete_draft_bc_packets';
log_message('Inside Delete_draft_bc_packets');
log_message( 'Before delete');
Delete from pa_bc_packets where budget_version_id = p_draft_bud_ver_id;
log_message( 'After delete');
PROCEDURE Update_bc_packets_pass(p_bud_ver_id IN NUMBER)
IS
BEGIN
log_message('Inside Update_bc_packets_pass');
g_procedure_name := 'Update_bc_packets_pass';
log_message( 'Before bcpkt update');
Update pa_bc_packets
set status_code = 'A'
where budget_version_id = p_bud_ver_id;
log_message(SQL%ROWCOUNT||' records updated');
END Update_bc_packets_pass;
PROCEDURE Update_bc_packets_fail(p_bud_ver_id IN NUMBER, p_status_code IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
log_message('Inside Update_bc_packets_fail');
g_procedure_name := 'Update_bc_packets_fail';
log_message( 'Before bcpkt update');
Update pa_bc_packets
set status_code = decode(status_code,'R',status_code,p_status_code)
where budget_version_id = p_bud_ver_id;
log_message(SQL%ROWCOUNT||' records updated');
Delete pa_bc_balances
where budget_version_id = p_bud_ver_id;
log_message(SQL%ROWCOUNT||' records deleted');
END Update_bc_packets_fail;
Select budget_version_id
into l_prev_budget_version_id
from pa_budget_versions
where project_id = p_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'Y';
Select MAX(budget_version_id)
into l_prev_budget_version_id
from pa_budget_versions
where project_id = p_project_id
and budget_type_code = p_budget_type_code
and budget_status_code = 'B'
and current_flag = 'N';
Select distinct encoded_msg
from xla_accounting_errors
where event_id in
(select evt.event_id
from xla_events evt,
psa_bc_xla_events_gt tmp
where evt.event_id = tmp.event_id
and evt.process_status_code in ('E','U'));
select result_code from psa_bc_xla_events_gt
where result_code in ('FAIL','XLA_ERROR','FATAL','XLA_NO_JOURNAL');
Select PBCO.Balance_type,
PBT.budget_amount_code,
PBV.project_id,
PBV.Budget_type_code
into g_balance_type,
g_budget_amount_code,
l_project_id,
l_budget_type_code
from PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
PA_BUDGET_VERSIONS PBV,
PA_BUDGET_TYPES PBT
where PBV.Budget_version_id = p_budget_version_id
and PBCO.Budget_Type_Code = PBV.Budget_Type_Code
and PBCO.project_id = PBV.project_id
and PBT.Budget_type_code = PBV.Budget_type_code;
select length(g_msg_data) into l_dummy from dual;
Select decode(p_mode,'Force','F', -- 'Year End'
'Check_Baseline','C', -- 'Check funds'
'Reserve_Baseline','R') -- 'Baseline'
into l_bc_mode
from dual;
select length(x.encoded_msg) into l_dummy from dual;
select -1 into l_dummy from dual where exists
(select evt.event_id
from xla_events evt,
psa_bc_xla_events_gt tmp
where evt.event_id = tmp.event_id
and evt.process_status_code = 'U');
Select pbv.budget_version_id
into g_draft_bvid
from pa_budget_versions pbv
where pbv.project_id = g_project_id
and pbv.budget_status_code = 'W'
and pbv.budget_type_code = l_budget_type_code;
Select decode(NVL(ppt.burden_cost_flag, 'N'),'Y',
NVL(ppt.burden_amt_display_method,'S'),'N')
into l_burden_method
from pa_project_types ppt,
pa_projects_all pp
where ppt.project_type = pp.project_type
and pp.project_id = X_project_id;
Select 'Y' into l_exists
from dual
where exists
(select 1
from pa_cost_distribution_lines_all cdl
where project_id = X_project_id
and burden_sum_source_run_id = -9999
and line_type = 'R'); -- Added for Bug 5864881
Update pa_budget_acct_lines
set funds_check_status_code = 'R',
funds_check_result_code = decode(substr(nvl(funds_check_result_code,'P'),1,1),'P',
decode(p_failure_status,
'FAIL',decode(g_processing_mode,
'CHECK_FUNDS','F150','F155'),
'XLA_ERROR','F172',
'XLA_NO_JOURNAL','F172',
'FATAL','F172',
'F172'),funds_check_result_code
)
where budget_version_id = p_draft_version_id
and (funds_check_status_code = 'A' or
nvl(funds_check_result_code,'P') like 'P%'
);
log_message(' In Fail_draft_acct_summary: Updated '||sql%rowcount||' records');