[Home] [Help]
PACKAGE BODY: APPS.CSM_COUNTER_EVENT_PKG
Source
1 PACKAGE BODY CSM_COUNTER_EVENT_PKG AS
2 /* $Header: csmecntb.pls 120.4 2006/07/24 12:44:41 trajasek noship $ */
3
4 --
5 -- Purpose: Encapsulate various operations on counter.
6 -- Methods willbe called by workflow engine
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- Jayan 05MAy02 Initial Revision
10 -- MelvinP 02Jul02 Fixed Ctr_Val_Make_Dirty_ForEachUser,
11 -- Ctr_Val_MDirty_U_ForEachUser
12 -- --------- ------ ------------------------------------------
13
14 /*** Globals ***/
15 g_counters_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_COUNTERS_ACC';
16 g_counters_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTERS';
17 g_counters_seq_name CONSTANT VARCHAR2(30) := 'CSM_COUNTERS_ACC_S';
18 g_counters_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_ID';
19 g_counters_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
20 CSM_ACC_PKG.t_publication_item_list('CSF_M_COUNTERS');
21
22 g_counter_val_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_VALUES_ACC';
23 g_counter_val_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_VALUES';
24 g_counter_val_seq_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_VALUES_ACC_S';
25 g_counter_val_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_VALUE_ID';
26 g_counter_val_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
27 CSM_ACC_PKG.t_publication_item_list('CSF_M_COUNTER_VALUES');
28
29
30 l_markdirty_failed EXCEPTION;
31
32 -- the below is a private proc called if the counter is deleted for all instances
33 PROCEDURE COUNTER_VALS_MAKE_DIRTY_D_GRP (p_counter_id IN NUMBER,
34 p_user_id IN NUMBER,
35 p_error_msg OUT NOCOPY VARCHAR2,
36 x_return_status IN OUT NOCOPY VARCHAR2)
37 IS
38 l_err_msg VARCHAR2(4000);
39 l_user_id NUMBER;
40
41 CURSOR l_counter_value_csr(p_counter_id cs_counters.counter_id%TYPE, p_user_id NUMBER)
42 IS
43 SELECT cval.counter_value_id
44 FROM csm_counter_values_acc acc,
45 CSI_COUNTER_READINGS cval
46 WHERE acc.user_id = p_user_id
47 AND acc.counter_value_id = cval.counter_value_id
48 AND cval.counter_id = p_counter_id;
49
50 BEGIN
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP' || ' for PK ' || to_char(p_counter_id);
53 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP', FND_LOG.LEVEL_PROCEDURE);
54
55 l_user_id := p_user_id;
56
57 FOR r_counter_value_rec in l_counter_value_csr(p_counter_id, l_user_id) LOOP
58 --Call DELETE ACC
59 CSM_ACC_PKG.Delete_Acc
60 ( P_PUBLICATION_ITEM_NAMES => g_counter_val_pubi_name
61 ,P_ACC_TABLE_NAME => g_counter_val_acc_table_name
62 ,P_PK1_NAME => g_counter_val_pk1_name
63 ,P_PK1_NUM_VALUE => r_counter_value_rec.counter_value_id
64 ,P_USER_ID => l_user_id
65 );
66
67
68 --bug 5253769 DELETE COUNTER PROPERTY READINGS property for the user
69 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL
70 (P_COUNTER_VALUE_ID => r_counter_value_rec.counter_value_id,
71 P_USER_ID => l_user_id,
72 P_ERROR_MSG => p_error_msg,
73 X_RETURN_STATUS => x_return_status
74 );
75 END LOOP;
76
77 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP' || ' for PK ' || to_char(p_counter_id);
78 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP', FND_LOG.LEVEL_PROCEDURE);
79
80 EXCEPTION
81 WHEN others THEN
82 p_error_msg := ' FAILED COUNTER_VALS_MAKE_DIRTY_D_GRP:' || to_char(p_counter_id);
83 x_return_status := FND_API.G_RET_STS_ERROR;
84 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP',FND_LOG.LEVEL_EXCEPTION);
85 -- RAISE;
86 END COUNTER_VALS_MAKE_DIRTY_D_GRP;
87
88 PROCEDURE COUNTER_MDIRTY_D(p_counter_id IN NUMBER,
89 p_error_msg OUT NOCOPY VARCHAR2,
90 x_return_status IN OUT NOCOPY VARCHAR2)
91 IS
92 l_err_msg VARCHAR2(4000);
93 l_user_id NUMBER;
94
95 CURSOR l_acc_csr(b_counter_id NUMBER)
96 IS
97 SELECT acc.user_id
98 FROM CSI_COUNTERS_B counters
99 , CS_CSI_COUNTER_GROUPS counter_groups
100 , csm_item_instances_acc acc
101 , CSI_COUNTER_ASSOCIATIONS ass
102 WHERE counters.counter_id = ass.counter_id
103 AND ass.source_object_code = 'CP'
104 AND counters.counter_type = 'REGULAR'
105 AND ass.source_object_id = acc.instance_id
106 AND counters.counter_id = b_counter_id
107 AND counter_groups.counter_group_id(+) = counters.group_id;
108
109 BEGIN
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D' || ' for PK ' || to_char(p_counter_id);
112 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
113
114 FOR r_acc_csr IN l_acc_csr(p_counter_id) LOOP
115 l_user_id := r_acc_csr.user_id;
116
117 -- delete counter for the user
118 CSM_ACC_PKG.Delete_Acc
119 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
120 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
121 ,P_PK1_NAME => g_counters_pk1_name
122 ,P_PK1_NUM_VALUE => p_counter_id
123 ,P_USER_ID => l_user_id
124 );
125
126 --bug 5253769 DELETE COUNTER property for the user
127 CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL
128 (P_COUNTER_ID => p_counter_id,
129 P_USER_ID => l_user_id,
130 P_ERROR_MSG => p_error_msg,
131 X_RETURN_STATUS => x_return_status
132 );
133
134 -- delete counter readings for the user
135 csm_counter_event_pkg.COUNTER_VALS_MAKE_DIRTY_D_GRP(p_counter_id=>p_counter_id,
136 p_user_id=>l_user_id,
137 p_error_msg=>p_error_msg,
138 x_return_status=>x_return_status);
139
140
141 --R12 DELETING Relationship for the counter & user
142 CSM_CNTR_RELATION_EVENT_PKG.COUNTER_RELATION_DEL(p_counter_id,l_user_id);
143
144
145 END LOOP;
146
147 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D' || ' for PK ' || to_char(p_counter_id);
148 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
149
150 EXCEPTION
151 WHEN others THEN
152 p_error_msg := ' FAILED COUNTER_MDIRTY_D:' || to_char(p_counter_id);
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
155 RAISE;
156 END COUNTER_MDIRTY_D;
157
158 PROCEDURE COUNTER_MDIRTY_D(p_counter_id IN NUMBER,
159 p_user_id IN NUMBER,
160 p_error_msg OUT NOCOPY VARCHAR2,
161 x_return_status IN OUT NOCOPY VARCHAR2)
162 IS
163 l_err_msg VARCHAR2(4000);
164 l_user_id NUMBER;
165
166 BEGIN
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D' || ' for PK ' || to_char(p_counter_id);
169 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
170
171 -- delete counter for the user
172 CSM_ACC_PKG.Delete_Acc
173 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
174 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
175 ,P_PK1_NAME => g_counters_pk1_name
176 ,P_PK1_NUM_VALUE => p_counter_id
177 ,P_USER_ID => p_user_id
178 );
179
180 --bug 5253769 DELETE COUNTER property for the user
181 CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_DEL
182 (P_COUNTER_ID => p_counter_id,
183 P_USER_ID => p_user_id,
184 P_ERROR_MSG => p_error_msg,
185 X_RETURN_STATUS => x_return_status
186 );
187
188 --R12 DELETING Relationship for the counter & user
189 CSM_CNTR_RELATION_EVENT_PKG.COUNTER_RELATION_DEL(p_counter_id,p_user_id);
190
191 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D' || ' for PK ' || to_char(p_counter_id);
192 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
193
194 EXCEPTION
195 WHEN others THEN
196 p_error_msg := ' FAILED COUNTER_MDIRTY_D:' || to_char(p_counter_id);
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
199 RAISE;
200 END COUNTER_MDIRTY_D;
201
202 PROCEDURE COUNTER_VALS_MAKE_DIRTY_D_GRP (p_counter_id IN NUMBER,
203 p_instance_id IN NUMBER,
204 p_user_id IN NUMBER,
205 p_error_msg OUT NOCOPY VARCHAR2,
206 x_return_status IN OUT NOCOPY VARCHAR2)
207 IS
208 l_err_msg VARCHAR2(4000);
209 l_user_id NUMBER;
210
211 CURSOR l_counter_value_csr(p_counter_id cs_counters.counter_id%TYPE, p_instance_id NUMBER,
212 p_user_id NUMBER)
213 IS
214 SELECT cval.counter_value_id
215 FROM CSI_COUNTERS_B cntrs,
216 CSI_COUNTER_READINGS cval,
217 CSI_COUNTER_ASSOCIATIONS cas,
218 csm_item_instances_acc acc
219 WHERE cntrs.counter_id = cas.counter_id
220 AND cas.source_object_code = 'CP'
221 AND cas.source_object_id = p_instance_id
222 AND cntrs.counter_id = cval.counter_id
223 AND cas.source_object_id = acc.instance_id
224 AND acc.user_id = p_user_id
225 AND cval.counter_id = p_counter_id
226 AND EXISTS
227 (SELECT 1
228 FROM csm_counter_values_acc vacc
229 WHERE vacc.counter_value_id = cval.counter_value_id
230 AND vacc.user_id = acc.user_id
231 );
232
233 BEGIN
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP' || ' for PK ' || to_char(p_counter_id);
236 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP', FND_LOG.LEVEL_PROCEDURE);
237
238 l_user_id := p_user_id;
239
240 FOR r_counter_value_rec in l_counter_value_csr(p_counter_id, p_instance_id, l_user_id) LOOP
241 --Call DELETE ACC
242 CSM_ACC_PKG.Delete_Acc
243 ( P_PUBLICATION_ITEM_NAMES => g_counter_val_pubi_name
244 ,P_ACC_TABLE_NAME => g_counter_val_acc_table_name
245 ,P_PK1_NAME => g_counter_val_pk1_name
246 ,P_PK1_NUM_VALUE => r_counter_value_rec.counter_value_id
247 ,P_USER_ID => l_user_id
248 );
249
250 --bug 5253769 DELETE COUNTER PROPERTY READINGS property for the user
251 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL
252 (P_COUNTER_VALUE_ID => r_counter_value_rec.counter_value_id,
253 P_USER_ID => l_user_id,
254 P_ERROR_MSG => p_error_msg,
255 X_RETURN_STATUS => x_return_status
256 );
257
258 END LOOP;
259
260 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP' || ' for PK ' || to_char(p_counter_id);
261 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP', FND_LOG.LEVEL_PROCEDURE);
262
263 EXCEPTION
264 WHEN others THEN
265 p_error_msg := ' FAILED COUNTER_VALS_MAKE_DIRTY_D_GRP:' || to_char(p_counter_id);
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_D_GRP',FND_LOG.LEVEL_EXCEPTION);
268 -- RAISE;
269 END COUNTER_VALS_MAKE_DIRTY_D_GRP;
270
271 PROCEDURE CTR_MAKE_DIRTY_U_FOREACHUSER(p_counter_id IN NUMBER,
272 p_error_msg OUT NOCOPY VARCHAR2,
273 x_return_status IN OUT NOCOPY VARCHAR2)
274 IS
275 l_err_msg VARCHAR2(4000);
276 l_user_id NUMBER;
277 l_publication_item_name VARCHAR2(30);
278 l_accesslist NUMBER;
279 l_resource_list NUMBER;
280 l_dmllist CHAR(1);
281 l_time_stamp DATE;
282 l_markdirty BOOLEAN;
283
284 CURSOR l_user_ids_csr (p_counter_id cs_counters.counter_id%TYPE)
285 IS
286 SELECT acc.user_id,
287 acc.access_id
288 FROM csm_counters_acc acc
289 WHERE acc.counter_id = p_counter_id;
290
291 BEGIN
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_U_FOREACHUSER' || ' for PK ' || to_char(p_counter_id);
294 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
295
296 FOR r_user_id_rec in l_user_ids_csr(p_counter_id) LOOP
297
298 l_publication_item_name := 'CSF_M_COUNTERS';
299 l_dmllist := 'U';
300 l_resource_list := r_user_id_rec.user_id;
301 l_accesslist := r_user_id_rec.access_id;
302
303 l_time_stamp := sysdate;
304 --call irst wrapper by anurag
305 l_markdirty := csm_util_pkg.MakeDirtyForUser (l_publication_item_name
306 , l_accesslist
307 , l_resource_list
308 , l_dmllist
309 , l_time_stamp);
310
311 IF l_markdirty THEN
312 --set the result. (no specific result text needed for this activity)
313 p_error_msg := 'COMPLETE CTR_MAKE_DIRTY_U_FOREACHUSER:' || to_char(l_accesslist);
314 END IF;
315
316 --bug 5253769 UPDATE COUNTER property for the user
317 CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_UPD
318 (P_COUNTER_ID => p_counter_id,
319 P_USER_ID => l_resource_list,
320 P_ERROR_MSG => p_error_msg,
321 X_RETURN_STATUS => x_return_status
322 );
323
324 END LOOP ; --end user ids cursor loop
325
326 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_U_FOREACHUSER' || ' for PK ' || to_char(p_counter_id);
327 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 if l_user_ids_csr%isopen then
332 close l_user_ids_csr;
333 end if ; -- cursor
334
335 p_error_msg := ' FAILED CTR_MAKE_DIRTY_U_FOREACHUSER:' || to_char(p_counter_id);
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_U_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
338 RAISE;
339 END CTR_MAKE_DIRTY_U_FOREACHUSER;
340
341 PROCEDURE CTR_MAKE_DIRTY_I_FOREACHUSER(p_counter_id IN NUMBER,
342 p_error_msg OUT NOCOPY VARCHAR2,
343 x_return_status IN OUT NOCOPY VARCHAR2)
344 IS
345 l_err_msg VARCHAR2(4000);
346 l_user_id NUMBER;
347 l_publication_item_name VARCHAR2(30);
348 l_accesslist NUMBER;
349 l_resource_list NUMBER;
350 l_dmllist CHAR(1);
351 l_time_stamp DATE;
352 l_markdirty BOOLEAN;
353
354 CURSOR l_user_ids_csr (p_counter_id cs_counters.counter_id%TYPE)
355 IS
356 SELECT acc.user_id
357 FROM CSI_COUNTERS_B ctr
358 , CS_CSI_COUNTER_GROUPS cgrp
359 , csm_item_instances_acc acc
360 , CSI_COUNTER_ASSOCIATIONS cas
361 WHERE ctr.counter_id = cas.counter_id
362 AND cas.source_object_code = 'CP'
363 AND ctr.counter_type = 'REGULAR'
364 AND cgrp.counter_group_id(+)= ctr.group_id
365 AND cas.source_object_id = acc.instance_id
366 AND ctr.counter_id = p_counter_id;
367
368 BEGIN
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_I_FOREACHUSER' || ' for PK ' || to_char(p_counter_id);
371 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_I_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
372
373 -- Open USER IDs loop
374 FOR r_user_id_rec IN l_user_ids_csr(p_counter_id) LOOP
375 -- Call Insert ACC
376 CSM_ACC_PKG.Insert_Acc
377 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
378 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
379 ,P_SEQ_NAME => g_counters_seq_name
380 ,P_PK1_NAME => g_counters_pk1_name
381 ,P_PK1_NUM_VALUE => p_counter_id
382 ,P_USER_ID => r_user_id_rec.user_id
383 );
384 --R12 Inserting Relationship for each user
385 CSM_CNTR_RELATION_EVENT_PKG.COUNTER_RELATION_INS(p_counter_id,r_user_id_rec.user_id);
386
387 --bug 5253769 INSERT COUNTER property for the user
388 CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS
389 (P_COUNTER_ID => p_counter_id,
390 P_USER_ID => r_user_id_rec.user_id,
391 P_ERROR_MSG => p_error_msg,
392 X_RETURN_STATUS => x_return_status
393 );
394
395 END LOOP ; --End USER IDs loop
396
397 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_I_FOREACHUSER' || ' for PK ' || to_char(p_counter_id);
398 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_I_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF l_user_ids_csr%ISOPEN then
403 CLOSE l_user_ids_csr;
404 END IF;
405
406 p_error_msg := ' FAILED CTR_MAKE_DIRTY_I_FOREACHUSER:' || to_char(p_counter_id);
407 x_return_status := FND_API.G_RET_STS_ERROR;
408 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.CTR_MAKE_DIRTY_I_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
409 RAISE;
410 END CTR_MAKE_DIRTY_I_FOREACHUSER;
411
412 PROCEDURE CTR_VAL_MAKE_DIRTY_FOREACHUSER(p_ctr_grp_log_id cs_counter_grp_log.counter_grp_log_id%type,
413 p_error_msg OUT NOCOPY VARCHAR2,
414 x_return_status IN OUT NOCOPY VARCHAR2)
415 IS
416 l_counter_value_id CSI_COUNTER_READINGS.counter_value_id%TYPE;
417 l_err_msg VARCHAR2(4000);
418 l_user_id NUMBER;
419 l_counter_id NUMBER;
420 l_instance_id NUMBER;
421 l_max_counter_readings NUMBER;
422
423 CURSOR l_user_ids_csr (p_counter_grp_log_id cs_counter_grp_log.counter_grp_log_id%TYPE)
424 IS
425 SELECT acc.user_id,
426 cval.counter_value_id,
427 ctr.counter_id,
428 acc.instance_id
429 FROM CSI_COUNTER_READINGS cval,
430 csi_counter_associations cas,
431 csi_counters_b ctr,
432 csm_counters_acc cnt_acc,
433 csm_item_instances_acc acc
434 WHERE cval.transaction_id = p_counter_grp_log_id
435 AND cas.source_object_code = 'CP'
436 AND ctr.counter_id = cas.counter_id
437 AND acc.instance_id = cas.source_object_id
438 AND cnt_acc.user_id = acc.user_id
439 AND cnt_acc.counter_id = cval.counter_id
440 AND ctr.counter_id = cnt_acc.counter_id
441 AND ctr.counter_type = 'REGULAR';
442
443
444
445 CURSOR l_max_counter_readings_csr(p_counter_id IN NUMBER, p_instance_id in NUMBER,
446 p_user_id IN NUMBER)
447 IS
448 SELECT cval.counter_value_id,
449 cval.value_timestamp
450 FROM CSI_COUNTERS_B cntrs,
451 CSI_COUNTER_READINGS cval,
452 CSI_COUNTER_ASSOCIATIONS cas,
453 csm_item_instances_acc iacc,
454 csm_counters_acc acc
455 WHERE acc.user_id = p_user_id
456 AND acc.counter_id = p_counter_id
457 AND iacc.user_id = acc.user_id
458 AND cntrs.counter_id = acc.counter_id
459 AND cntrs.counter_id = cas.counter_id
460 AND cas.source_object_code = 'CP'
461 AND cas.source_object_id = p_instance_id
462 AND cntrs.counter_id = cval.counter_id
463 AND cas.source_object_id = iacc.instance_id
464 AND cval.counter_id = p_counter_id
465 AND EXISTS
466 (SELECT 1
467 FROM csm_counter_values_acc cv_acc
468 WHERE cv_acc.user_id = acc.user_id
469 AND cv_acc.counter_value_id = cval.counter_value_id
470 )
471 ORDER BY cval.value_timestamp desc;
472
473 l_max_counter_readings_rec l_max_counter_readings_csr%ROWTYPE;
474
475 BEGIN
476 x_return_status := FND_API.G_RET_STS_SUCCESS;
477 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.CTR_VAL_MAKE_DIRTY_FOREACHUSER' || ' for cnt_grp_log_id ' || to_char(p_ctr_grp_log_id);
478 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MAKE_DIRTY_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
479
480 FOR r_user_id_rec IN l_user_ids_csr(p_ctr_grp_log_id) LOOP
481 l_user_id := r_user_id_rec.user_id;
482 l_counter_value_id := r_user_id_rec.counter_value_id;
483 l_counter_id := r_user_id_rec.counter_id;
484 l_instance_id := r_user_id_rec.instance_id;
485 l_max_counter_readings := NVL(csm_profile_pkg.get_max_readings_per_counter(l_user_id),0);
486
487 -- Call Insert ACC
488 CSM_ACC_PKG.Insert_Acc
489 ( P_PUBLICATION_ITEM_NAMES => g_counter_val_pubi_name
490 ,P_ACC_TABLE_NAME => g_counter_val_acc_table_name
491 ,P_SEQ_NAME => g_counters_seq_name
492 ,P_PK1_NAME => g_counter_val_pk1_name
493 ,P_PK1_NUM_VALUE => l_counter_value_id
494 ,P_USER_ID => l_user_id
495 );
496
497 --bug 5253769 INSERT COUNTER PROPERTY READINGS property for the user
498 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS
499 (P_COUNTER_VALUE_ID => l_counter_value_id,
500 P_USER_ID => l_user_id,
501 P_ERROR_MSG => p_error_msg,
502 X_RETURN_STATUS => x_return_status
503 );
504
505 -- purge older readings so to keep the record count at the history profile
506 OPEN l_max_counter_readings_csr(l_counter_id, l_instance_id, l_user_id);
507 LOOP
508 FETCH l_max_counter_readings_csr INTO l_max_counter_readings_rec;
509 IF l_max_counter_readings_csr%NOTFOUND THEN
510 EXIT;
511 END IF;
512
513 -- delete counter readings so as to keep history number of readings on the device
514 IF l_max_counter_readings_csr%ROWCOUNT > l_max_counter_readings THEN
515 --Call DELETE ACC
516 CSM_ACC_PKG.Delete_Acc
517 ( P_PUBLICATION_ITEM_NAMES => g_counter_val_pubi_name
518 ,P_ACC_TABLE_NAME => g_counter_val_acc_table_name
519 ,P_PK1_NAME => g_counter_val_pk1_name
520 ,P_PK1_NUM_VALUE => l_max_counter_readings_rec.counter_value_id
521 ,P_USER_ID => l_user_id
522 );
523 --bug 5253769 DELETE COUNTER PROPERTY READINGS property for the user
524 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL
525 (P_COUNTER_VALUE_ID => l_max_counter_readings_rec.counter_value_id,
526 P_USER_ID => l_user_id,
527 P_ERROR_MSG => p_error_msg,
528 X_RETURN_STATUS => x_return_status
529 );
530
531 END IF;
532 END LOOP;
533 CLOSE l_max_counter_readings_csr;
534
535 END LOOP ; --End USER IDs cursor loop
536
537 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.CTR_VAL_MAKE_DIRTY_FOREACHUSER' || ' for cnt_grp_log_id ' || to_char(p_ctr_grp_log_id);
538 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MAKE_DIRTY_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 IF l_user_ids_csr%ISOPEN THEN
543 CLOSE l_user_ids_csr;
544 END IF;
545
546 IF l_max_counter_readings_csr%ISOPEN THEN
547 CLOSE l_max_counter_readings_csr;
548 END IF;
549
550 p_error_msg := ' FAILED CTR_VAL_MAKE_DIRTY_FOREACHUSER ctr_grp_log_id:' || to_char(p_ctr_grp_log_id);
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MAKE_DIRTY_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
553 RAISE;
554 END CTR_VAL_MAKE_DIRTY_FOREACHUSER;
555
556 PROCEDURE CTR_VAL_MDIRTY_U_FOREACHUSER(p_ctr_grp_log_id cs_counter_grp_log.counter_grp_log_id%type,
557 p_error_msg OUT NOCOPY VARCHAR2,
558 x_return_status IN OUT NOCOPY VARCHAR2)
559 IS
560 l_err_msg VARCHAR2(4000);
561 l_user_id NUMBER;
562 l_counter_value_id CSI_COUNTER_READINGS.counter_value_id%TYPE;
563 l_publication_item_name varchar2(30);
564 l_accesslist asg_download.access_list;
565 l_resourcelist asg_download.user_list;
566 l_dmllist char(1);
567 l_time_stamp DATE;
568 l_counter_value_count NUMBER;
569 l_markdirty boolean;
570
571 CURSOR l_user_ids_csr (p_counter_grp_log_id cs_counter_grp_log.counter_grp_log_id%type)
572 IS
573 SELECT acc.user_id,
574 acc.access_id,
575 cv.counter_value_id
576 FROM csm_counter_values_acc acc,
577 CSI_COUNTER_READINGS cv
578 WHERE acc.counter_value_id = cv.counter_value_id
579 AND cv.transaction_id = p_counter_grp_log_id;
580
581 BEGIN
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.CTR_VAL_MDIRTY_U_FOREACHUSER' || ' for cnt_grp_log_id ' || to_char(p_ctr_grp_log_id);
584 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MDIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
585
586 l_publication_item_name := 'CSF_M_COUNTER_VALUES';
587 l_dmllist := CSM_UTIL_PKG.GetAsgDmlConstant(ASG_DOWNLOAD.UPD);
588 l_counter_value_count := 0;
589 l_time_stamp := SYSDATE;
590
591 FOR r_user_id_rec IN l_user_ids_csr(l_counter_value_id) LOOP
592 l_counter_value_count := l_counter_value_count + 1;
593 l_resourcelist(l_counter_value_count) := r_user_id_rec.user_id;
594 l_accesslist(l_counter_value_count) := r_user_id_rec.access_id;
595 END LOOP;
596
597 IF l_accesslist.count > 0 THEN
598 l_markdirty := csm_util_pkg.MakeDirtyForUser (l_publication_item_name
599 , l_accesslist
600 , l_resourcelist
601 , l_dmllist
602 , l_time_stamp);
603
604 IF NOT l_markdirty THEN
605 NULL;
606 END IF;
607 END IF;
608
609 FOR r_user_id_rec IN l_user_ids_csr(l_counter_value_id) LOOP
610 --bug 5253769 DELETE COUNTER PROPERTY READINGS property for the user
611 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD
612 (P_COUNTER_VALUE_ID => l_counter_value_id,
613 P_USER_ID => r_user_id_rec.user_id,
614 P_ERROR_MSG => p_error_msg,
615 X_RETURN_STATUS => x_return_status
616 );
617
618 END LOOP;
619
620 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.CTR_VAL_MDIRTY_U_FOREACHUSER' || ' for cnt_grp_log_id ' || to_char(p_ctr_grp_log_id);
621 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MDIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 IF l_user_ids_csr%ISOPEN then
626 CLOSE l_user_ids_csr;
627 END IF;
628
629 p_error_msg := ' FAILED CTR_VAL_MDIRTY_U_FOREACHUSER ctr_grp_log_id:' || to_char(p_ctr_grp_log_id);
630 x_return_status := FND_API.G_RET_STS_ERROR;
631 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.CTR_VAL_MDIRTY_U_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
632 RAISE;
633 END CTR_VAL_MDIRTY_U_FOREACHUSER;
634
635 PROCEDURE COUNTER_MDIRTY_I(p_counter_id IN NUMBER,
636 p_user_id IN NUMBER,
637 p_error_msg OUT NOCOPY VARCHAR2,
638 x_return_status IN OUT NOCOPY VARCHAR2)
639 IS
640 l_err_msg VARCHAR2(4000);
641 l_user_id NUMBER;
642
643 BEGIN
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_I' || ' for cnt_id ' || to_char(p_counter_id);
646 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_I', FND_LOG.LEVEL_PROCEDURE);
647
648 CSM_ACC_PKG.Insert_Acc
649 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
650 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
651 ,P_SEQ_NAME => g_counters_seq_name
652 ,P_PK1_NAME => g_counters_pk1_name
653 ,P_PK1_NUM_VALUE => p_counter_id
654 ,P_USER_ID => p_user_id
655 );
656
657 --R12 Inserting Relationship for the counter & user
658 CSM_CNTR_RELATION_EVENT_PKG.COUNTER_RELATION_INS(p_counter_id,p_user_id);
659
660 --bug 5253769 INSERT COUNTER property for the user
661 CSM_COUNTER_PROPERTY_EVENT_PKG.COUNTER_PROPERTY_INS
662 (P_COUNTER_ID => p_counter_id,
663 P_USER_ID => p_user_id,
664 P_ERROR_MSG => p_error_msg,
665 X_RETURN_STATUS => x_return_status
666 );
667
668
669 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_I' || ' for cnt_id ' || to_char(p_counter_id);
670 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_I', FND_LOG.LEVEL_PROCEDURE);
671
672 EXCEPTION
673 WHEN others THEN
674 p_error_msg := ' FAILED COUNTER_MDIRTY_I:' || to_char(p_counter_id);
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
677 RAISE;
678 END COUNTER_MDIRTY_I;
679
680 PROCEDURE COUNTER_VALS_MAKE_DIRTY_I_GRP(p_counter_id IN NUMBER,
681 p_instance_id IN NUMBER,
682 p_user_id IN NUMBER,
683 p_error_msg OUT NOCOPY VARCHAR2,
684 x_return_status IN OUT NOCOPY VARCHAR2)
685 IS
686 l_err_msg VARCHAR2(4000);
687 l_counter_value_id CSI_COUNTER_READINGS.counter_value_id%TYPE;
688
689 CURSOR l_counter_value_csr(p_counter_id cs_counters.counter_id%TYPE, p_instance_id NUMBER,
690 p_user_id NUMBER)
691 IS
692 SELECT cval.value_timestamp,
693 cval.counter_value_id
694 FROM CSI_COUNTERS_B cntrs,
695 CSI_COUNTER_READINGS cval,
696 CSI_COUNTER_ASSOCIATIONS cas,
697 csm_counters_acc cnt_acc,
698 csm_item_instances_acc acc
699 WHERE cntrs.counter_id = cas.counter_id
700 AND cas.source_object_code = 'CP'
701 AND cas.source_object_id = p_instance_id
702 AND cntrs.counter_id = cval.counter_id
703 AND cas.source_object_id = acc.instance_id
704 AND acc.user_id = p_user_id
705 AND acc.user_id = cnt_acc.user_id
706 AND cval.counter_id = p_counter_id
707 AND cval.counter_id = cnt_acc.counter_id
708 ORDER BY cval.value_timestamp desc;
709
710 r_counter_value_rec l_counter_value_csr%ROWTYPE;
711
712 BEGIN
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 l_err_msg := 'Entering CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_I_GRP' || ' for cnt_id ' || to_char(p_counter_id);
715 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_I_GRP', FND_LOG.LEVEL_PROCEDURE);
716
717 OPEN l_counter_value_csr(p_counter_id, p_instance_id, p_user_id);
718 LOOP
719 FETCH l_counter_value_csr INTO r_counter_value_rec;
720 EXIT WHEN ((l_counter_value_csr%NOTFOUND) OR (l_counter_value_csr%ROWCOUNT > csm_profile_pkg.get_max_readings_per_counter(p_user_id)));
721
722 l_counter_value_id := r_counter_value_rec.counter_value_id;
723
724 --Call Insert ACC
725 CSM_ACC_PKG.Insert_Acc
726 ( P_PUBLICATION_ITEM_NAMES => g_counter_val_pubi_name
727 ,P_ACC_TABLE_NAME => g_counter_val_acc_table_name
728 ,P_SEQ_NAME => g_counters_seq_name
729 ,P_PK1_NAME => g_counter_val_pk1_name
730 ,P_PK1_NUM_VALUE => l_counter_value_id
731 ,P_USER_ID => p_user_id
732 );
733
734 --bug 5253769 INSERT COUNTER property Readings for the user
735 CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS
736 (P_COUNTER_VALUE_ID => l_counter_value_id,
737 P_USER_ID => p_user_id,
738 P_ERROR_MSG => p_error_msg,
739 X_RETURN_STATUS => x_return_status
740 );
741
742 END LOOP;
743 IF l_counter_value_csr%ISOPEN THEN
744 CLOSE l_counter_value_csr;
745 END IF;
746
747 l_err_msg := 'Leaving CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_I_GRP' || ' for cnt_id ' || to_char(p_counter_id);
748 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_I_GRP', FND_LOG.LEVEL_PROCEDURE);
749
750 EXCEPTION
751 WHEN others THEN
752 IF l_counter_value_csr%ISOPEN THEN
753 CLOSE l_counter_value_csr;
754 END IF;
755 p_error_msg := ' FAILED COUNTER_VALS_MAKE_DIRTY_I_GRP:' || to_char(p_counter_id);
756 x_return_status := FND_API.G_RET_STS_ERROR;
757 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_COUNTER_EVENT_PKG.COUNTER_VALS_MAKE_DIRTY_I_GRP',FND_LOG.LEVEL_EXCEPTION);
758 RAISE;
759 END COUNTER_VALS_MAKE_DIRTY_I_GRP;
760
761 END CSM_COUNTER_EVENT_PKG; -- of package csm_counter_event_pkg