APPS.CSM_STATE_TRANSITION_EVENT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11

CURSOR l_deletes_cur IS
SELECT ACCESS_ID,USER_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_resp_id
                                                   AND sresp.RULE_ID = srule.RULE_ID
                                                   AND srule.STATE_TYPE = 'TASK_STATUS'));

Line: 23

CURSOR l_updates_cur IS
SELECT acc.ACCESS_ID,acc.USER_ID
       FROM JTF_STATE_TRANSITIONS trn,
            CSM_STATE_TRANSITIONS_ACC acc
       WHERE trn.state_transition_id = acc.state_transition_id
       AND acc.user_id = p_user_id AND trn.LAST_UPDATE_DATE > p_lrd;

Line: 32

   l_acc_tab.DELETE; l_user_tab.DELETE;

Line: 33

   OPEN  l_deletes_cur;

Line: 34

   FETCH l_deletes_cur BULK COLLECT INTO l_acc_tab,l_user_tab;

Line: 35

   CLOSE l_deletes_cur;

Line: 39

		 DELETE FROM CSM_STATE_TRANSITIONS_ACC WHERE ACCESS_ID=l_acc_tab(I);

Line: 49

   l_acc_tab.DELETE; l_user_tab.DELETE;

Line: 50

   OPEN  l_updates_cur;

Line: 51

   FETCH l_updates_cur BULK COLLECT INTO l_acc_tab,l_user_tab;

Line: 52

   CLOSE l_updates_cur;

Line: 74

CURSOR l_inserts_cur IS
SELECT CSM_STATE_TRANSITIONS_ACC_S.NEXTVAL,p_user_id,trn.state_transition_id
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 = p_resp_id )
AND  not exists (SELECT 1 FROM CSM_STATE_TRANSITIONS_ACC acc
				WHERE user_id = p_user_id
				 AND trn.state_transition_id=acc.state_transition_id);

Line: 88

   OPEN  l_inserts_cur;

Line: 89

   FETCH l_inserts_cur BULK COLLECT INTO l_acc_tab,l_user_tab,l_str_tab;

Line: 90

   CLOSE l_inserts_cur;

Line: 97

     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_acc_tab(I), l_str_tab(I), l_user_tab(I), fnd_global.user_id,
       sysdate, fnd_global.user_id, sysdate,fnd_global.user_id);

Line: 121

   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';

Line: 131

SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';

Line: 142

 FOR rec IN (SELECT USER_ID,RESPONSIBILITY_ID FROM ASG_USER WHERE ENABLED='Y')
 LOOP
	Refresh_Acc_upd_Del(rec.USER_ID,rec.RESPONSIBILITY_ID,l_run_date);

Line: 149

  UPDATE jtm_con_request_data
     SET last_run_date = sysdate
   WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
     AND procedure_name = 'REFRESH_ACC';