CREATE OR REPLACE PROCEDURE xxhr_migrate_approval_groups_p (
p_application_name VARCHAR2,
p_approval_group_name VARCHAR2)
AS
l_approval_group_id NUMBER := NULL;
l_object_version_number NUMBER := NULL;
l_start_date DATE := NULL;
l_end_date DATE := NULL;
l_application_id NUMBER := NULL;
l_voting_regime VARCHAR2 (100) := NULL;
l_obj_object_version_number NUMBER := NULL;
l_obj_start_date DATE := NULL;
l_obj_end_date DATE := NULL;
l_role_name VARCHAR2
(500) := NULL;
l_orig_system VARCHAR2 (500) := NULL;
l_user_name VARCHAR2
(500) := NULL;
l_parameter_name VARCHAR2 (50) := 'wf_roles_name';
l_parameter VARCHAR2
(50) := NULL;
l_grp_item_obj_version_no NUMBER := NULL;
l_approval_group_item_id NUMBER := NULL;
l_grp_item_start_date DATE := NULL;
l_grp_item_end_date DATE := NULL;
l_process VARCHAR2
(1) := 'N';
l_err_msg VARCHAR2
(1000) := NULL;
CURSOR fetch_appr_grp
IS
SELECT *
FROM ame_approval_groups a
WHERE
name LIKE '%' || p_approval_group_name || '%'
AND TRUNC (SYSDATE) BETWEEN start_date AND end_date
AND is_static = 'Y' -- Only Static Approval Groups
AND EXISTS
(SELECT
'X'
FROM
ame_approval_group_config
WHERE approval_group_id = a.approval_group_id
AND TRUNC
(SYSDATE)
BETWEEN
start_date
AND end_date);
CURSOR fetch_grp_members (cl_approval_group_id NUMBER)
IS
SELECT orig_system, orig_system_id, order_number
FROM ame_approval_group_members
WHERE approval_group_id = cl_approval_group_id;
BEGIN
fnd_global.apps_initialize (user_id => 21196, --BIJOY
resp_id => 50637, -- XXHR HRMS Manager ( IT )
resp_appl_id =>
800 --
Human Resources
);
SELECT application_id
INTO l_application_id
FROM ame_calling_apps
WHERE
application_name = p_application_name
AND SYSDATE BETWEEN start_date AND end_date;
FOR i IN fetch_appr_grp
LOOP
l_process := 'N';
l_approval_group_id := NULL;
l_object_version_number := NULL;
l_start_date := NULL;
l_end_date := NULL;
l_voting_regime := NULL;
l_obj_object_version_number := NULL;
l_obj_start_date := NULL;
l_obj_end_date := NULL;
l_role_name := NULL;
l_grp_item_obj_version_no := NULL;
l_approval_group_item_id := NULL;
l_grp_item_start_date := NULL;
l_grp_item_end_date := NULL;
l_err_msg := NULL;
BEGIN
ame_approver_group_api.create_ame_approver_group
(
p_validate => FALSE,
p_name =>
i.name,
p_description => i.description,
p_is_static => i.is_static,
p_query_string => NULL,
p_approval_group_id => l_approval_group_id,
p_start_date => l_start_date,
p_end_date => l_end_date,
p_object_version_number => l_object_version_number);
l_process := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_err_msg :=
i.approval_group_id
|| '-->'
|| 'Approval Group Excep:
'
|| SQLERRM;
l_process := 'N';
END;
IF l_process = 'Y'
THEN
SELECT voting_regime
INTO l_voting_regime
FROM ame_approval_group_config
WHERE
approval_group_id = i.approval_group_id
AND TRUNC (SYSDATE) BETWEEN start_date AND end_date;
BEGIN
ame_approver_group_api.create_approver_group_config
(
p_validate => FALSE,
p_approval_group_id => l_approval_group_id,
p_application_id => l_application_id,
p_voting_regime => l_voting_regime,
p_order_number => NULL,
p_object_version_number => l_obj_object_version_number,
p_start_date => l_obj_start_date,
p_end_date => l_obj_end_date);
l_process := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_err_msg :=
l_err_msg
|| '--> '
|| i.approval_group_id
|| '-->'
|| 'Approval Config: '
|| SQLERRM;
l_process := 'N';
END;
END IF;
IF l_process = 'Y'
THEN
FOR j IN fetch_grp_members (i.approval_group_id)
LOOP
l_orig_system := j.orig_system;
IF l_orig_system = 'PQH_ROLE'
THEN
--Fetch Role Name from Source
SELECT role_name
INTO l_role_name
FROM pqh_roles
WHERE role_id = j.orig_system_id AND enable_flag = 'Y';
--Fetch Role from Destination
SELECT l_orig_system || ':' || role_id
INTO l_parameter
FROM pqh_roles
WHERE role_name = l_role_name;
ELSIF l_orig_system = 'FND_USR'
THEN
--Fetch Role Name from Source
SELECT user_name
INTO l_user_name
FROM fnd_user fu
WHERE
user_id = j.orig_system_id
AND TRUNC
(SYSDATE)
BETWEEN
start_date
AND NVL (
end_date,
hr_general.end_of_time);
--Fetch Role from Destination
SELECT l_orig_system || ':' || user_id
INTO l_parameter
FROM fnd_user
WHERE
user_name = l_user_name
AND TRUNC
(SYSDATE)
BETWEEN
start_date
AND NVL (
end_date,
hr_general.end_of_time);
END IF;
BEGIN
ame_approver_group_api.create_approver_group_item
(
p_validate => FALSE,
p_approval_group_id => l_approval_group_id,
p_parameter_name => l_parameter_name,
p_parameter => l_parameter,
p_order_number => j.order_number,
p_approval_group_item_id => l_approval_group_item_id,
p_object_version_number => l_grp_item_obj_version_no,
p_start_date => l_grp_item_start_date,
p_end_date => l_grp_item_end_date);
l_process := 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_err_msg :=
l_err_msg
|| '--> '
|| i.approval_group_id
|| '-->'
|| 'Approval Group Items: '
|| SQLERRM;
l_process := 'N';
END;
END LOOP;
END IF;
IF l_process = 'Y'
THEN
DBMS_OUTPUT.put_line (
l_approval_group_id
|| '-->'
|| i.name
|| ' has been Created
Successfully !!!');
COMMIT;
ELSE
DBMS_OUTPUT.put_line (l_err_msg);
ROLLBACK;
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
ROLLBACK;
END;
/
No comments:
Post a Comment