[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_rows
(x_project_id IN PA_PROJECTS_ALL.PROJECT_ID%type
,x_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
,x_funds_control_level_code IN PA_BUDGETARY_CONTROLS.FUNDS_CONTROL_LEVEL_CODE%type
,x_top_task_id IN PA_TASKS.TASK_ID%type
,x_task_id IN PA_TASKS.TASK_ID%type
,x_parent_member_id IN PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%type
,x_resource_list_member_id IN PA_RESOURCE_LIST_MEMBERS.resource_list_member_id%type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_return_status VARCHAR2(10);
insert into PA_BUDGETARY_CONTROLS ( BUDGETARY_CONTROLS_ID
,PROJECT_ID
,FUNDS_CONTROL_LEVEL_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,TOP_TASK_ID
,TASK_ID
,PARENT_MEMBER_ID
,RESOURCE_LIST_MEMBER_ID
,BUDGET_TYPE_CODE
)
values (
PA_BUDGETARY_CONTROLS_s.nextval
,x_project_id
,x_funds_control_level_code
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,x_top_task_id
,x_task_id
,x_parent_member_id
,x_resource_list_member_id
,x_budget_type_code
);
, p_procedure_name => 'insert_rows');
/* Bug 5162775 : The procedure del_bc_rec_for_reset_auto is created to delete BC records
during budgetary control reset. */
PROCEDURE del_bc_rec_for_reset_auto
(p_project_id IN NUMBER
,p_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
delete from pa_budgetary_controls
where project_id = p_project_id
and budget_type_code = p_budget_type_code;
/* Bug 5162775 : The procedure del_bc_rec_for_task_auto is created to delete
all the dangling records from budgetary controls. */
PROCEDURE del_bc_rec_for_task_auto
(p_project_id IN NUMBER
,p_budget_type_code IN PA_BUDGET_TYPES.BUDGET_TYPE_CODE%type
,p_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE from pa_budgetary_controls bc
WHERE EXISTS
( SELECT pt2.task_id
FROM pa_tasks pt2
WHERE bc.task_id=pt2.parent_task_id
AND ((p_entry_level_code = 'L') OR
(p_entry_level_code = 'M' AND exists (select 1 from pa_tasks pt1
where bc.task_id = pt1.task_id
and pt1.parent_task_id IS NOT NULL))))
AND bc.project_id = p_project_id
AND bc.budget_type_code = p_budget_type_code;
SELECT
pt1.task_id,
pt1.top_task_id
FROM pa_tasks pt1
WHERE pt1.project_id = x_project_id
AND
( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
OR
(x_entry_level_code IN ('L','M')
AND NOT EXISTS
( SELECT
pt2.task_id
FROM pa_tasks pt2
WHERE pt1.task_id=pt2.parent_task_id)
)
)
AND not exists
( SELECT 1
FROM pa_budgetary_controls bc
WHERE bc.project_id = x_project_id
AND bc.budget_type_code = x_budget_type_code
AND bc.task_id = pt1.task_id
);
SELECT br.resource_list_member_id,
DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
DECODE(br.parent_member_id,
NULL,decode(x_group_resource_type_id,
0,decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res),
decode(v_funds_control_level_res_grp,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res_grp)),
decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res)
) funds_control_level
FROM PA_RESOURCE_LIST_MEMBERS br
WHERE br.ENABLED_FLAG = 'Y'
AND br.RESOURCE_LIST_ID = x_resource_list_id
AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
and nvl(br.migration_code, 'M') = 'M'
and not exists (select 1 from pa_budgetary_controls bc
where bc.project_id = x_project_id
and bc.budget_type_code = x_budget_type_code
and bc.resource_list_member_id = br.resource_list_member_id);
SELECT br.resource_list_member_id,
DECODE(x_group_resource_type_id,0,decode(nvl(br.parent_member_id,0),0,-1) --uncategorized
,NVL(br.PARENT_MEMBER_ID,0)) parent_member_id,
DECODE(br.parent_member_id,
NULL,decode(x_group_resource_type_id,
0,decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res),
decode(v_funds_control_level_res_grp,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res_grp)),
decode(v_funds_control_level_res,'D',nvl(funds_control_level_code,'N'),v_funds_control_level_res)
) funds_control_level
FROM PA_RESOURCE_LIST_MEMBERS br
WHERE br.ENABLED_FLAG = 'Y'
AND br.RESOURCE_LIST_ID = x_resource_list_id
AND DECODE(br.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
and nvl(br.migration_code, 'M') = 'M';
SELECT
pt1.task_id,
pt1.top_task_id
FROM pa_tasks pt1
WHERE pt1.project_id = x_project_id
AND
( (x_entry_level_code IN ( 'T' ,'M') AND pt1.parent_task_id is null )
OR
(x_entry_level_code IN ('L','M')
AND NOT EXISTS
( SELECT
pt2.task_id
FROM pa_tasks pt2
WHERE pt1.task_id=pt2.parent_task_id)
)
)
AND exists
( SELECT 1
FROM pa_budgetary_controls bc
WHERE bc.project_id = x_project_id
AND bc.budget_type_code = x_budget_type_code
AND bc.task_id = pt1.task_id
);
Call del_bc_rec_for_task_auto to delete all the dangling records from budgetary controls in autonomous mode.
i.e if entry_level_code = 'L' , delete all the records where the task has some child tasks.
if entry_level_code = 'M' , delete all the records whose task is neither the top nor the lowest task.
For all newly added tasks Loop
Create budgetary control records for the task level.
For all the resources and resource groups Loop
Create budgetary control records for the resource and resource group levels.
End Loop
End Loop
End if
*/
--===================================================================================
BEGIN
PA_DEBUG.init_err_stack('PA_BUDGETARY_CONTROLS_PKG.CREATE_BC_LEVELS');
select
FUND_CONTROL_LEVEL_PROJECT,
FUND_CONTROL_LEVEL_TASK,
FUND_CONTROL_LEVEL_RES_GRP,
FUND_CONTROL_LEVEL_RES
into
v_funds_control_level_project,
v_funds_control_level_task,
v_funds_control_level_res_grp,
v_funds_control_level_res
from
pa_budgetary_control_options
where
project_id = x_project_id and budget_type_code = x_budget_type_code;
PA_FCK_UTIL.debug_msg('PA_BC_Log: Call INSERT_ROWS -- Create BC for PROJECT ');
PA_DEBUG.g_err_stage := 'PA_BC_Log:PA_BC_Log: Call INSERT_ROWS -- Create BC for PROJECT ';
insert_rows (x_project_id
,x_budget_type_code
,v_funds_control_level_project
,null
,null
,null
,null
,l_return_status
,l_msg_count
,l_msg_data
);
insert_rows( x_project_id
, x_budget_type_code
, rec_res_grps.funds_control_level
, 0
, 0
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
,l_return_status
,l_msg_count
,l_msg_data
);
insert_rows( x_project_id
, x_budget_type_code
, rec_res_grps.funds_control_level
, l_top_task_id
, task_rec.task_id
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
,l_return_status
,l_msg_count
,l_msg_data
);
insert_rows(x_project_id
,x_budget_type_code
,v_funds_control_level_task
,l_top_task_id
,task_rec.task_id
,null
,null
,l_return_status
,l_msg_count
,l_msg_data);
insert_rows( x_project_id
, x_budget_type_code
, rec_res_grps.funds_control_level
, l_top_task_id
, task_rec.task_id
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
,l_return_status
,l_msg_count
,l_msg_data
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_budgetary_controls bc,
PA_BUDGET_VERSIONS pbv
WHERE BUDGET_VERSION_ID = p_budget_version_id
and bc.project_id = pbv.project_id
and bc.budget_type_code = pbv.budget_type_code);
select bv.project_id
,bv.budget_type_code
,bv.resource_list_id
,rl.group_resource_type_id
,bem.entry_level_code
from PA_BUDGET_VERSIONS bv
,PA_BUDGET_ENTRY_METHODS bem
,PA_RESOURCE_LISTS_ALL_BG rl
where bv.budget_version_id = p_budget_version_id
and bv.budget_entry_method_code = bem.budget_entry_method_code
and bv.resource_list_id = rl.resource_list_id
--FP M changes
and nvl(rl.migration_code, 'M') = 'M' ;
select bem.entry_level_code
from pa_budget_versions bv,
pa_budget_entry_methods bem
where bv.budget_version_id = p_budget_version_id
and bv. budget_entry_method_code = bem. budget_entry_method_code;
delete all the budgetary control records.
l_calling_mode := 'R'
Else
l_calling_mode := 'REBASELINE'
end if
End if
Then pa_budgetary_controls_pkg.create_bc_levels is called with the appropriate calling mode
*/
--====================================================================================
begin
l_return_status := FND_API.G_RET_STS_SUCCESS;
-- Bug 5162775 : Call del_bc_rec_for_reset_auto to delete BC records in autonomous mode during BC reset.
del_bc_rec_for_reset_auto (p_project_id => bud_ctrl_params_rec.project_id,
p_budget_type_code => bud_ctrl_params_rec.budget_type_code);
delete from pa_budgetary_controls
where project_id = x_project_id
and budget_type_code = x_budget_type_code;
PA_FCK_UTIL.debug_msg('PA_BC_Log: delete budgetary Controls');
PA_DEBUG.g_err_stage := 'PA_BC_Log: Deleted Budgetary Control Records: '||'PROJECT ID = '||x_project_id;
select 'Y' into bc_rec_exists from SYS.DUAL
where exists
(select' Y'
from pa_budgetary_controls
where project_id = x_project_id and
budget_type_code = x_budget_type_code and
rownum=1);
/* select count(*) into bc_rec_count
from pa_budgetary_controls
where project_id = x_project_id and
budget_type_code = x_budget_type_code;
select PROJECT_ID,BUDGET_TYPE_CODE
into l_project_id, l_budget_type_code
from PA_BUDGET_VERSIONS
where BUDGET_VERSION_ID = x_budget_version_id;
select budget_status_code from PA_BUDGET_VERSIONS where budget_version_id = p_budget_version_id;