[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_rec(x_project_id NUMBER
,x_funds_control_code VARCHAR2
,x_award_id NUMBER
,x_task_id NUMBER
,x_parent_member_id NUMBER
,x_resource_list_member_id NUMBER) IS
-- Bug 5162777 : This procedure is made autonomous.
PRAGMA AUTONOMOUS_TRANSACTION;
insert into gms_budgetary_controls ( BUDGETARY_CONTROLS_ID
,PROJECT_ID
,FUNDS_CONTROL_LEVEL_CODE
,AWARD_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,TASK_ID
,PARENT_MEMBER_ID
,RESOURCE_LIST_MEMBER_ID
)
values ( gms_budgetary_controls_s.nextval
,x_project_id
,x_funds_control_code
,x_award_id
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,x_task_id
,x_parent_member_id
,x_resource_list_member_id
);
/* Bug 5162777 : The procedure del_bc_rec_for_reset_auto is created to delete BC records in autonomous mode
during budgetary control reset. */
PROCEDURE del_bc_rec_for_reset_auto
(p_project_id IN NUMBER
,p_award_id IN NUMBER )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
delete from gms_budgetary_controls
where project_id = p_project_id
and award_id = p_award_id;
/* Bug 5162777 : 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_award_id IN NUMBER
,p_entry_level_code IN PA_BUDGET_ENTRY_METHODS.ENTRY_LEVEL_CODE%type)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE from gms_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.award_id = p_award_id;
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 gms_budgetary_controls bc
WHERE bc.project_id = x_project_id
AND bc.award_id = x_award_id
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,x_funds_control_code_res,
x_funds_control_code_resgrp),
x_funds_control_code_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 gms_budgetary_controls bc
where bc.project_id = x_project_id
and bc.award_id = x_award_id
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,x_funds_control_code_res,
x_funds_control_code_resgrp),
x_funds_control_code_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 gms_budgetary_controls bc
WHERE bc.project_id = x_project_id
and bc.award_id = x_award_id
AND bc.task_id = pt1.task_id
);
Delete all the dangling records from budgetary controls
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
*/
--===================================================================================
select fund_control_level_award
, fund_control_level_task
, fund_control_level_res_grp
, fund_control_level_res
into x_funds_control_code_awd
, x_funds_control_code_tsk
, x_funds_control_code_resgrp
, x_funds_control_code_res
from gms_awards
where award_id = x_award_id;
insert_rec(x_project_id, x_funds_control_code_awd, x_award_id, null, null, null);
insert_rec( x_project_id
, rec_res_grps.funds_control_level
, x_award_id
, 0
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
);
insert_rec( x_project_id
, rec_res_grps.funds_control_level
, x_award_id
, task_rec.task_id
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
);
-- Bug 5162777 : Delete all the dangling records from budgetary controls in autonomous mode.
del_bc_rec_for_task_auto (p_project_id => x_project_id,
p_award_id => x_award_id,
p_entry_level_code => x_entry_level_code);
insert_rec(x_project_id, x_funds_control_code_tsk
, x_award_id, task_rec.task_id, null, null);
insert_rec( x_project_id
, rec_res_grps.funds_control_level
, x_award_id
, task_rec.task_id
, rec_res_grps.parent_member_id
, rec_res_grps.resource_list_member_id
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gms_budgetary_controls bc
where bc.project_id = p_project_id
and bc.award_id = p_award_id);
Call del_bc_rec_for_reset_auto to delete all the budgetary control records.
l_calling_mode := 'BASELINE'
Else
l_calling_mode := 'REBASELINE'
end if
End if
Then gms_budg_cont_setup.create_records is called with the appropriate calling mode
*/
--====================================================================================
IF L_DEBUG = 'Y' THEN
gms_error_pkg.gms_debug('GMS_BUDG_CONT_SETUP.bud_ctrl_create - Setting up default budgetary_control', 'C');
-- Bug 5162777 : 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 => p_project_id,
p_award_id => p_award_id);