[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR l_deletes_cur(p_user_id asg_user.user_id%TYPE,
p_responsibility_id jtf_state_responsibilities.responsibility_id%TYPE)
IS
SELECT STATE_TRANSITION_ID, ACCESS_ID
FROM CSM_STATE_TRANSITIONS_ACC acc
WHERE acc.USER_ID = p_user_id
AND STATE_TRANSITION_ID not in
(SELECT STATE_TRANSITION_ID
FROM JTF_STATE_TRANSITIONS trn
WHERE RULE_ID IN
(SELECT sresp.RULE_ID
FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
WHERE RESPONSIBILITY_ID = p_responsibility_id
AND sresp.RULE_ID = srule.RULE_ID
AND srule.STATE_TYPE = 'TASK_STATUS' -- IN ('TASK_STATUS', 'SR_STATUS') AND APPLICATION_ID IN (513, 170)
)
);
open l_deletes_cur (p_user_id, l_responsibility_id);
FETCH l_deletes_cur INTO l_state_transition_id, l_access_id;
EXIT WHEN l_deletes_cur%NOTFOUND;
DELETE FROM CSM_STATE_TRANSITIONS_ACC
WHERE USER_ID = p_user_id
AND STATE_TRANSITION_ID = l_state_transition_id;
close l_deletes_cur;
Updates the records updated in the backend, in the ACC,
for the passed user
Also adds corresponding entries in to SDQ
Arguments:
p_user_id User_ID of the user for whom to refresh
p_access_id: The access_id to be refreshed. If null, then whole ACC table
is refreshed
*/
procedure Refresh_Acc_Upd (p_user_id asg_user.user_id%TYPE,
p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE)
IS
l_mark_dirty boolean;
l_last_update_date JTF_STATE_TRANSITIONS.LAST_UPDATE_DATE%TYPE;
l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
'SELECT trn.state_transition_id, trn.last_update_date, acc.ACCESS_ID
FROM JTF_STATE_TRANSITIONS trn,
CSM_STATE_TRANSITIONS_ACC acc
WHERE trn.state_transition_id = acc.state_transition_id
AND acc.user_id = :1 AND trn.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE';
open l_updates_cur for l_dsql
using p_user_id;
open l_updates_cur for l_dsql
using p_user_id, p_access_id;
FETCH l_updates_cur INTO l_state_transition_id, l_last_update_date, l_access_id;
EXIT WHEN l_updates_cur%NOTFOUND;
UPDATE CSM_STATE_TRANSITIONS_ACC
SET LAST_UPDATE_DATE = l_last_update_date
WHERE ACCESS_ID = l_access_id
AND USER_ID = p_user_id;
close l_updates_cur;
Inserts the records inserted in the backend to the ACC,
for the passed user
Also adds corresponding entries in to SDQ
Arguments:
p_user_id User_ID of the user for whom to refresh
p_user_name Name of the user for whom to refresh
p_access_id: The access_id to be refreshed. If null, then whole ACC table
is refreshed
*/
procedure Refresh_Acc_Ins (
p_user_id asg_user.user_id%TYPE,
p_user_name asg_user.user_name%TYPE,
p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE,
p_resp_id jtf_state_responsibilities.responsibility_id%TYPE)
IS
l_mark_dirty boolean;
l_last_update_date JTF_STATE_TRANSITIONS.LAST_UPDATE_DATE%TYPE;
l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
'SELECT trn.state_transition_id, trn.last_update_date
FROM JTF_STATE_TRANSITIONS trn
WHERE trn.RULE_ID IN
(SELECT sresp.RULE_ID
FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
WHERE sresp.RULE_ID = srule.RULE_ID
AND srule.STATE_TYPE = ''TASK_STATUS''
AND RESPONSIBILITY_ID = :1 ) '
|| ' AND trn.state_transition_id not in
(SELECT state_transition_id
FROM CSM_STATE_TRANSITIONS_ACC acc
WHERE user_id = :2)';
open l_inserts_cur for l_dsql
using l_responsibility_id, p_user_id;
open l_inserts_cur for l_dsql
using l_responsibility_id, p_user_id, p_access_id;
FETCH l_inserts_cur INTO l_state_transition_id, l_last_update_date;
EXIT WHEN l_inserts_cur%NOTFOUND;
SELECT CSM.CSM_STATE_TRANSITIONS_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
INSERT INTO CSM_STATE_TRANSITIONS_ACC(access_id,
STATE_TRANSITION_ID, USER_ID, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_access_id, l_state_transition_id, p_user_id, fnd_global.user_id,
sysdate, fnd_global.user_id, l_last_update_date,
fnd_global.user_id);
close l_inserts_cur;
backend table for deletes, updates and inserts.
Also adds corresponding entries in to SDQ
Arguments:
p_user_id: The user for whom to refresh the table. If null, then refreshes
for all the users
p_access_id: The access_id to be refreshed. If null, then whole ACC table
is refreshed
*/
procedure Refresh_Acc (p_user_id asg_user.user_id%TYPE,
p_user_name asg_user.user_name%TYPE,
p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE,
p_respid jtf_state_responsibilities.responsibility_id%TYPE)
IS
BEGIN
/*** DELETES ***/
--Delete only if refresh is not asked for a particular access_id
IF p_access_id IS NULL THEN
refresh_acc_del(p_user_id, p_user_name, p_respid);
/******* UPDATES **********/
refresh_acc_upd(p_user_id, p_access_id);
/*** INSERTS ***/
refresh_acc_ins(p_user_id, p_user_name, p_access_id, p_respid);
backend table for deletes, updates and inserts.
Also adds corresponding entries in to SDQ
Arguments:
p_user_id: The user for whom to refresh the table. If null, then refreshes
for all the users
p_access_id: The access_id to be refreshed. If null, then whole ACC table
is refreshed
*/
procedure Refresh_ACC (p_status OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
select usr.user_id, usr.user_name
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND usr.user_id=usr.owner_id
AND pubresp.pub_name ='SERVICEP';
UPDATE jtm_con_request_data
SET last_run_date = l_run_date
WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';