1 PACKAGE BODY CSM_STATE_TRANSITION_EVENT_PKG AS
2 /* $Header: csmestrb.pls 120.7 2008/02/07 06:52:55 anaraman ship $ */
3
4 l_no_status_transition_resp EXCEPTION;
5
6 procedure Refresh_Acc_Del (p_user_id asg_user.user_id%TYPE,
7 p_user_name asg_user.user_name%TYPE,
8 p_resp_id jtf_state_responsibilities.responsibility_id%TYPE)
9 IS
10
11 l_mark_dirty boolean;
12
13 l_responsibility_id jtf_state_responsibilities.responsibility_id%TYPE;
14 l_state_transition_id JTF_STATE_TRANSITIONS.state_transition_id%TYPE;
15 l_pkvalueslist asg_download.pk_list;
16 l_null_pkvalueslist asg_download.pk_list;
17 l_access_id CSM_STATE_TRANSITIONS_ACC.ACCESS_ID%TYPE;
18
19 CURSOR l_deletes_cur(p_user_id asg_user.user_id%TYPE,
20 p_responsibility_id jtf_state_responsibilities.responsibility_id%TYPE)
21 IS
22 SELECT STATE_TRANSITION_ID, ACCESS_ID
23 FROM CSM_STATE_TRANSITIONS_ACC acc
24 WHERE acc.USER_ID = p_user_id
25 AND STATE_TRANSITION_ID not in
26 (SELECT STATE_TRANSITION_ID
27 FROM JTF_STATE_TRANSITIONS trn
28 WHERE RULE_ID IN
29 (SELECT sresp.RULE_ID
30 FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
31 WHERE RESPONSIBILITY_ID = p_responsibility_id
32 AND sresp.RULE_ID = srule.RULE_ID
33 AND srule.STATE_TYPE = 'TASK_STATUS' -- IN ('TASK_STATUS', 'SR_STATUS') AND APPLICATION_ID IN (513, 170)
34 )
35 );
36
37 BEGIN
38 -- get the user responsibility id
39 l_responsibility_id := p_resp_id;
40
41 --open the cursor
42 open l_deletes_cur (p_user_id, l_responsibility_id);
43 --loop over cursor entries, and delete from acc table, as well as mark dirty the SDQ
44 LOOP
45 FETCH l_deletes_cur INTO l_state_transition_id, l_access_id;
46 EXIT WHEN l_deletes_cur%NOTFOUND;
47
48 l_pkvalueslist := l_null_pkvalueslist;
49 l_pkvalueslist(1) := l_state_transition_id;
50
51 --mark dirty the SDQ
52 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForUser('CSF_M_STATE_TRANSITIONS',
53 -- l_access_id, p_user_id, ASG_DOWNLOAD.DEL, SYSDATE, l_pkvalueslist);
54 -- Since access_id's are sequence based, we do not need to pass pk values for delete
55 l_access_id, p_user_id, ASG_DOWNLOAD.DEL, SYSDATE);
56
57 --delete from ACC
58 DELETE FROM CSM_STATE_TRANSITIONS_ACC
59 WHERE USER_ID = p_user_id
60 AND STATE_TRANSITION_ID = l_state_transition_id;
61
62 END LOOP;
63
64 --close the cursor
65 close l_deletes_cur;
66
67 END Refresh_Acc_Del;
68
69
70 /**
71 Updates the records updated in the backend, in the ACC,
72 for the passed user
73 Also adds corresponding entries in to SDQ
74
75 Arguments:
76 p_user_id User_ID of the user for whom to refresh
77 p_access_id: The access_id to be refreshed. If null, then whole ACC table
78 is refreshed
79 */
80 procedure Refresh_Acc_Upd (p_user_id asg_user.user_id%TYPE,
81 p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE)
82 IS
83
84 l_mark_dirty boolean;
85
86 l_state_transition_id JTF_STATE_TRANSITIONS.state_transition_id%TYPE;
87 l_last_update_date JTF_STATE_TRANSITIONS.LAST_UPDATE_DATE%TYPE;
88 l_access_id CSM_STATE_TRANSITIONS_ACC.ACCESS_ID%TYPE;
89 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
90 l_dsql varchar2(2048);
91 l_bind_count NUMBER :=1;
92
93 BEGIN
94
95 l_dsql :=
96 'SELECT trn.state_transition_id, trn.last_update_date, acc.ACCESS_ID
97 FROM JTF_STATE_TRANSITIONS trn,
98 CSM_STATE_TRANSITIONS_ACC acc
99 WHERE trn.state_transition_id = acc.state_transition_id
100 AND acc.user_id = :1 AND trn.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE';
101
102 IF p_access_id IS NOT NULL THEN
103 l_dsql := l_dsql || ' AND acc.STATE_TRANSITION_ID = :2';
104 l_bind_count := l_bind_count + 1;
105 END IF;
106
107 --open the cursor
108 IF (l_bind_count =1) THEN
109 open l_updates_cur for l_dsql
110 using p_user_id;
111 ELSIF (l_bind_count =2) THEN
112 open l_updates_cur for l_dsql
113 using p_user_id, p_access_id;
114 END IF;
115
116 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
117 LOOP
118 FETCH l_updates_cur INTO l_state_transition_id, l_last_update_date, l_access_id;
119 EXIT WHEN l_updates_cur%NOTFOUND;
120
121 --mark dirty the SDQ
122 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForUser('CSF_M_STATE_TRANSITIONS',
123 l_access_id, p_user_id,
124 ASG_DOWNLOAD.UPD, sysdate);
125
126 --update ACC
127 UPDATE CSM_STATE_TRANSITIONS_ACC
128 SET LAST_UPDATE_DATE = l_last_update_date
129 WHERE ACCESS_ID = l_access_id
130 AND USER_ID = p_user_id;
131
132 END LOOP;
133
134 --close the cursor
135 close l_updates_cur;
136
137 END Refresh_Acc_Upd;
138
139
140
141 /**
142 Inserts the records inserted in the backend to the ACC,
143 for the passed user
144 Also adds corresponding entries in to SDQ
145
146 Arguments:
147 p_user_id User_ID of the user for whom to refresh
148 p_user_name Name of the user for whom to refresh
149 p_access_id: The access_id to be refreshed. If null, then whole ACC table
150 is refreshed
151 */
152 procedure Refresh_Acc_Ins (
153 p_user_id asg_user.user_id%TYPE,
154 p_user_name asg_user.user_name%TYPE,
155 p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE,
156 p_resp_id jtf_state_responsibilities.responsibility_id%TYPE)
157 IS
158
159 l_mark_dirty boolean;
160
161 l_state_transition_id JTF_STATE_TRANSITIONS.state_transition_id%TYPE;
162 l_last_update_date JTF_STATE_TRANSITIONS.LAST_UPDATE_DATE%TYPE;
163 l_responsibility_id jtf_state_responsibilities.responsibility_id%TYPE;
164 l_access_id CSM_STATE_TRANSITIONS_ACC.ACCESS_ID%TYPE;
165 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
166 l_dsql varchar2(2048);
167 l_bind_count NUMBER :=2;
168 BEGIN
169 -- get the user responsibility id
170 l_responsibility_id := p_resp_id;
171
172 IF l_responsibility_id IS NULL THEN
173 RAISE l_no_status_transition_resp;
174 END IF;
175 l_dsql :=
176 'SELECT trn.state_transition_id, trn.last_update_date
177 FROM JTF_STATE_TRANSITIONS trn
178 WHERE trn.RULE_ID IN
179 (SELECT sresp.RULE_ID
180 FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
181 WHERE sresp.RULE_ID = srule.RULE_ID
182 AND srule.STATE_TYPE = ''TASK_STATUS''
183 AND RESPONSIBILITY_ID = :1 ) '
184 || ' AND trn.state_transition_id not in
185 (SELECT state_transition_id
186 FROM CSM_STATE_TRANSITIONS_ACC acc
187 WHERE user_id = :2)';
188
189 IF p_access_id IS NOT NULL THEN
190 l_dsql := l_dsql || ' AND trn.STATE_TRANSITION_ID = :3';
191 l_bind_count := l_bind_count + 1;
192 END IF;
193
194 --open the cursor
195 IF (l_bind_count =2) THEN
196 open l_inserts_cur for l_dsql
197 using l_responsibility_id, p_user_id;
198 ELSIF (l_bind_count =3) THEN
199 open l_inserts_cur for l_dsql
200 using l_responsibility_id, p_user_id, p_access_id;
201 END IF;
202 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
203 LOOP
204 FETCH l_inserts_cur INTO l_state_transition_id, l_last_update_date;
205 EXIT WHEN l_inserts_cur%NOTFOUND;
206
207 IF p_access_id IS NULL THEN
208 SELECT CSM.CSM_STATE_TRANSITIONS_ACC_S.NEXTVAL INTO l_access_id FROM DUAL;
209 ELSE
210 l_access_id := p_access_id;
211 END IF;
212
213 --mark dirty the SDQ
214 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForUser('CSF_M_STATE_TRANSITIONS',
215 l_access_id, p_user_id,
216 ASG_DOWNLOAD.INS, sysdate);
217
218 --insert into ACC
219 INSERT INTO CSM_STATE_TRANSITIONS_ACC(access_id,
220 STATE_TRANSITION_ID, USER_ID, CREATED_BY,
221 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
222 LAST_UPDATE_LOGIN)
223 VALUES (l_access_id, l_state_transition_id, p_user_id, fnd_global.user_id,
224 sysdate, fnd_global.user_id, l_last_update_date,
225 fnd_global.user_id);
226
227 END LOOP;
228
229 --close the cursor
230 close l_inserts_cur;
231
232 END Refresh_Acc_Ins;
233
234
235 /**
236 Refreshes the CSM_STATE_TRANSITIONS_ACC table by comparing with the
237 backend table for deletes, updates and inserts.
238 Also adds corresponding entries in to SDQ
239
240 Arguments:
241 p_user_id: The user for whom to refresh the table. If null, then refreshes
242 for all the users
243 p_access_id: The access_id to be refreshed. If null, then whole ACC table
244 is refreshed
245 */
246 procedure Refresh_Acc (p_user_id asg_user.user_id%TYPE,
247 p_user_name asg_user.user_name%TYPE,
248 p_access_id CSM_STATE_TRANSITIONS_ACC.state_transition_id%TYPE,
249 p_respid jtf_state_responsibilities.responsibility_id%TYPE)
250 IS
251
252 BEGIN
253
254 /*** DELETES ***/
255 --Delete only if refresh is not asked for a particular access_id
256 IF p_access_id IS NULL THEN
257 refresh_acc_del(p_user_id, p_user_name, p_respid);
258 END IF;
259 /******* UPDATES **********/
260 refresh_acc_upd(p_user_id, p_access_id);
261 /*** INSERTS ***/
262 refresh_acc_ins(p_user_id, p_user_name, p_access_id, p_respid);
263
264 END;
265
266
267 /**
268 Refreshes the CSM_STATE_TRANSITIONS_ACC table by comparing with the
269 backend table for deletes, updates and inserts.
270 Also adds corresponding entries in to SDQ
271
272 Arguments:
273 p_user_id: The user for whom to refresh the table. If null, then refreshes
274 for all the users
275 p_access_id: The access_id to be refreshed. If null, then whole ACC table
276 is refreshed
277 */
278 procedure Refresh_ACC (p_status OUT NOCOPY VARCHAR2,
279 p_message OUT NOCOPY VARCHAR2)
280 IS
281 PRAGMA AUTONOMOUS_TRANSACTION;
282 l_sqlerrno varchar2(20);
283 l_sqlerrmsg varchar2(2000);
284 l_responsibility_id jtf_state_responsibilities.responsibility_id%TYPE;
285 l_run_date DATE;
286 l_user_id NUMBER;
287
288 CURSOR l_omfs_palm_resources_csr is
289 --R12 For multiple responsibility
290 select usr.user_id, usr.user_name
291 FROM asg_user_pub_resps pubresp
292 , asg_user usr
293 WHERE usr.enabled = 'Y'
294 AND pubresp.user_name = usr.user_name
295 AND usr.user_id=usr.owner_id
296 AND pubresp.pub_name ='SERVICEP';
297
298
299 BEGIN
300 l_run_date := SYSDATE;
301
302 --if user id is passed, refresh for only that user.
303 --else refresh for all the users
304 /* IF p_user_id IS NOT NULL THEN
305 l_responsibility_id := CSM_UTIL_PKG.get_responsibility_id(p_user_id);
306
307 IF l_responsibility_id IS NULL THEN
308 RAISE l_no_status_transition_resp;
309 END IF;
310
311 refresh_acc( p_user_id => p_user_id,
312 p_user_name => CSM_UTIL_PKG.get_user_name( p_user_id),
313 p_access_id => p_access_id,
314 p_respid => l_responsibility_id);
315 ELSE
316 */
317 --refresh for all the users
318 FOR l_omfs_palm_resources_rec IN l_omfs_palm_resources_csr LOOP
319 BEGIN
320 l_user_id := l_omfs_palm_resources_rec.user_id;
321 l_responsibility_id := CSM_UTIL_PKG.get_responsibility_id(l_omfs_palm_resources_rec.user_id);
322
323 IF l_responsibility_id IS NULL THEN
324 RAISE l_no_status_transition_resp;
325 END IF;
326
327 refresh_acc( p_user_id => l_omfs_palm_resources_rec.user_id,
328 p_user_name => l_omfs_palm_resources_rec.user_name,
329 p_access_id => NULL,
330 p_respid => l_responsibility_id);
331 EXCEPTION
332 WHEN l_no_status_transition_resp THEN
333 csm_util_pkg.log('No Status Transition responsibility defined for user: ' || l_omfs_palm_resources_rec.user_name);
334 END;
335 END LOOP;
336 -- END IF;
337
338 UPDATE jtm_con_request_data
339 SET last_run_date = l_run_date
340 WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
341 AND procedure_name = 'REFRESH_ACC';
342
343 COMMIT;
344
345 p_status := 'FINE';
346 p_message := 'CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc Executed successfully';
347
348 EXCEPTION
349 WHEN l_no_status_transition_resp THEN
350 csm_util_pkg.log('No Status Transition responsibility defined for user: ' || l_user_id);
351 p_status := 'ERROR';
352 p_message := 'Error in CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc';
353
354 WHEN others THEN
355 l_sqlerrno := to_char(SQLCODE);
356 l_sqlerrmsg := substr(SQLERRM, 1,300);
357 csm_util_pkg.log('State Tansition Error:' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_STATE_TRANISITION_EVENT_PKG.Refresh_Acc');
358 p_status := 'ERROR';
359 p_message := 'Error in CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc :' ||l_sqlerrno || ':' || l_sqlerrmsg;
360 ROLLBACK;
361 -- logm('State Transition Error:' || l_sqlerrno || ':' || substr(l_sqlerrmsg,1,200));
362 -- fnd_file.put_line(fnd_file.log, 'CSM_STATE_TRANSITION_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
363
364 END Refresh_ACC;
365 END CSM_STATE_TRANSITION_EVENT_PKG;
[Home] [Help]