CREATE OR REPLACE PROCEDURE APPS.RESPONSIBILITY_END_DATE (
retcode OUT VARCHAR2,
errbuf OUT VARCHAR2,
p_responsibilty IN VARCHAR2,
action_type IN VARCHAR2,
p_end_date varchar2
)
IS
CURSOR c1
IS
SELECT DISTINCT u.user_id user_id, u.user_name usr_name,
wur.role_orig_system_id responsibility_id,
resp.responsibility_name,
(SELECT application_id
FROM fnd_application
WHERE application_short_name =
/* Val between 1st and 2nd separator */
REPLACE
(SUBSTR (wura.role_name,
INSTR (wura.role_name,
'|',
1,
1
)
+ 1,
( INSTR (wura.role_name,
'|',
1,
2
)
- INSTR (wura.role_name,
'|',
1,
1
)
- 1
)
),
'%col',
':'
)) responsibility_application_id,
(SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_key =
/* Val after 3rd separator */
REPLACE
(SUBSTR (wura.role_name,
INSTR (wura.role_name,
'|',
1,
3
)
+ 1
),
'%col',
':'
)) security_group_id,
wura.start_date, wura.end_date end_date,
wura.created_by created_by,
wura.creation_date creation_date,
wura.last_updated_by last_updated_by,
wura.last_update_date last_update_date,
wura.last_update_login last_update_login,
wura.assignment_reason description
FROM fnd_user u,
wf_user_role_assignments wura,
wf_all_user_roles wur,
fnd_responsibility_vl resp
WHERE u.user_name = wura.user_name
AND wura.relationship_id = -1
AND wur.role_orig_system = 'FND_RESP'
AND NOT wura.role_name LIKE 'FND_RESP|%|ANY'
AND wura.role_name = wur.role_name
AND wura.user_name = wur.user_name
AND wur.role_orig_system_id = resp.responsibility_id
AND UPPER (SUBSTR (resp.responsibility_name, 1, 3)) =p_responsibilty
AND wura.end_date IS NULL;
CURSOR c2
IS
SELECT DISTINCT u.user_id user_id, u.user_name usr_name,
wur.role_orig_system_id responsibility_id,
resp.responsibility_name,
(SELECT application_id
FROM fnd_application
WHERE application_short_name =
/* Val between 1st and 2nd separator */
REPLACE
(SUBSTR (wura.role_name,
INSTR (wura.role_name,
'|',
1,
1
)
+ 1,
( INSTR (wura.role_name,
'|',
1,
2
)
- INSTR (wura.role_name,
'|',
1,
1
)
- 1
)
),
'%col',
':'
)) responsibility_application_id,
(SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_key =
/* Val after 3rd separator */
REPLACE
(SUBSTR (wura.role_name,
INSTR (wura.role_name,
'|',
1,
3
)
+ 1
),
'%col',
':'
)) security_group_id,
wura.start_date, wura.end_date end_date,
wura.created_by created_by,
wura.creation_date creation_date,
wura.last_updated_by last_updated_by,
wura.last_update_date last_update_date,
wura.last_update_login last_update_login,
wura.assignment_reason description
FROM fnd_user u,
wf_user_role_assignments wura,
wf_all_user_roles wur,
fnd_responsibility_vl resp
WHERE u.user_name = wura.user_name
AND wura.relationship_id = -1
AND wur.role_orig_system = 'FND_RESP'
AND NOT wura.role_name LIKE 'FND_RESP|%|ANY'
AND wura.role_name = wur.role_name
AND wura.user_name = wur.user_name
--AND u.user_name = p_user_name
AND wur.role_orig_system_id = resp.responsibility_id
AND UPPER (SUBSTR (resp.responsibility_name, 1, 3)) = p_responsibilty
AND TRUNC(wura.end_date) =to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS');
num_total_rows NUMBER;
l_action VARCHAR2 (10);
resp_end_date varchar2(15);
BEGIN
num_total_rows := 0;
l_action := UPPER (action_type);
-- fnd_file.put_line (fnd_file.LOG, 'User Name --> ' || p_user_name);
DBMS_OUTPUT.put_line ('Responsibility End Date Before Conversion.....' || p_end_date);
resp_end_date := to_char(to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-RRRR');
-- resp_end_date := FND_DATE.CANONICAL_TO_DATE (p_end_date);
DBMS_OUTPUT.put_line ('Responsibility End Date.....' || p_end_date);
fnd_file.put_line (fnd_file.LOG, 'Responsibility End Date.....' || p_end_date);
IF (l_action = 'DISABLE')
THEN
DBMS_OUTPUT.put_line ('Action Type.....' || l_action);
fnd_file.put_line (fnd_file.LOG, 'Action Type.....' || l_action);
FOR i IN c1
LOOP
DBMS_OUTPUT.put_line ('Processing Started.....');
fnd_file.put_line (fnd_file.LOG, 'Processing Started.....');
DBMS_OUTPUT.put_line ( 'Processing Responsibility ==> '
|| i.responsibility_name
);
fnd_file.put_line (fnd_file.LOG,
'Processing Responsibility ==> '
|| i.responsibility_name
);
l_action := UPPER (action_type);
IF (fnd_user_resp_groups_api.assignment_exists
(i.user_id,
i.responsibility_id,
i.responsibility_application_id,
i.security_group_id
)
)
THEN
fnd_user_resp_groups_api.update_assignment
(user_id => i.user_id,
responsibility_id => i.responsibility_id,
responsibility_application_id => i.responsibility_application_id,
security_group_id => i.security_group_id,
start_date => i.start_date,
end_date => to_date(p_end_date,'RRRR/MM/DD HH24:MI:SS'),
description => NULL
);
END IF;
COMMIT;
num_total_rows := c1%ROWCOUNT;
DBMS_OUTPUT.put_line ('Processing Completed Successfully.....');
fnd_file.put_line (fnd_file.LOG,
'Processing Completed Successfully.....'
);
END LOOP;
IF num_total_rows > 0
THEN
--DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ( 'Total Rows Updated = '
|| TO_CHAR (num_total_rows)
);
fnd_file.put_line (fnd_file.LOG, 'Rows Updated:' || num_total_rows);
ELSE
fnd_file.put_line (fnd_file.LOG,
'There is no records available to update'
);
DBMS_OUTPUT.put_line ('There is no records available to update');
END IF;
END IF;
IF (l_action = 'ENABLE')
THEN
DBMS_OUTPUT.put_line ('Action Type.....' || l_action);
fnd_file.put_line (fnd_file.LOG, 'Action Type.....' || l_action);
FOR j IN c2
LOOP
DBMS_OUTPUT.put_line ('Processing Started.....');
fnd_file.put_line (fnd_file.LOG, 'Processing Started.....');
DBMS_OUTPUT.put_line ( 'Processing Responsibility ==> '
|| j.responsibility_name
);
fnd_file.put_line (fnd_file.LOG,
'Processing Responsibility ==> '
|| j.responsibility_name
);
IF (fnd_user_resp_groups_api.assignment_exists
(j.user_id,
j.responsibility_id,
j.responsibility_application_id,
j.security_group_id
)
)
THEN
fnd_user_resp_groups_api.update_assignment
(user_id => j.user_id,
responsibility_id => j.responsibility_id,
responsibility_application_id => j.responsibility_application_id,
security_group_id => j.security_group_id,
start_date => j.start_date,
end_date => NULL,
description => NULL
);
END IF;
COMMIT;
num_total_rows := c2%ROWCOUNT;
DBMS_OUTPUT.put_line ('Processing Completed Successfully.....');
fnd_file.put_line (fnd_file.LOG,
'Processing Completed Successfully.....'
);
END LOOP;
IF num_total_rows > 0
THEN
--DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ( 'Total Rows Updated = '
|| TO_CHAR (num_total_rows)
);
fnd_file.put_line (fnd_file.LOG, 'Rows Updated:' || num_total_rows);
ELSE
fnd_file.put_line (fnd_file.LOG,
'There is no records available to update'
);
DBMS_OUTPUT.put_line ('There is no records available to update');
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error Code is --> '
|| SQLCODE
|| ' And Error Msg is --> '
|| SQLERRM
);
END;
/
Here i have not
specified any Username to end date the responsibility. If u want to end
date user-wise responsibility, You have to pass the User name as the
parameter in the procedure and you have to specify the username as a
parameter in Cursor also.
So it will end date the responsibility.................
No comments:
Post a Comment