[Home] [Help]
PACKAGE BODY: APPS.CSM_COUNTER_READING_EVENT_PKG
Source
1 PACKAGE BODY CSM_COUNTER_READING_EVENT_PKG AS
2 /* $Header: csmecrdb.pls 120.0 2006/06/30 12:41:57 trajasek noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14
15
16 PROCEDURE Refresh_acc(p_status OUT NOCOPY VARCHAR2,
17 p_message OUT NOCOPY VARCHAR2)
18 IS
19 PRAGMA AUTONOMOUS_TRANSACTION;
20 --variable declarations
21 TYPE counter_value_tbl_typ IS TABLE OF CSI_COUNTER_READINGS.counter_value_id%TYPE INDEX BY BINARY_INTEGER;
22 TYPE counter_tbl_typ IS TABLE OF CSI_COUNTER_READINGS.counter_id%TYPE INDEX BY BINARY_INTEGER;
23
24 l_sqlerrno VARCHAR2(20);
25 l_sqlerrmsg varchar2(2000);
26 l_mark_dirty boolean;
27 l_pub_item varchar2(30) := 'CSF_M_COUNTER_VALUES';
28 l_access_list asg_download.access_list;
29 l_user_list asg_download.user_list;
30 l_all_omfs_resource_list asg_download.user_list;
31 l_null_omfs_resource_list asg_download.user_list;
32 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
33 l_counter_value_id_tbl counter_value_tbl_typ;
34 l_counter_id_tbl counter_tbl_typ;
35
36 --Cursor Declarations
37 --Insert Cursor
38 CURSOR csr_ctr_reading_ins
39 IS
40 SELECT CSM_COUNTER_VALUES_ACC_S.NEXTVAL,
41 cnt_rd.counter_value_id,
42 cnt_acc.counter_id,
43 cnt_acc.user_id
44 FROM CSI_COUNTER_READINGS cnt_rd,
45 csm_counters_acc cnt_acc
46 where cnt_rd.counter_id=cnt_acc.counter_id
47 AND NOT EXISTS
48 (SELECT 1 FROM csm_counter_values_acc val_acc
49 WHERE VAL_acc.counter_value_id =cnt_rd.counter_value_id);
50
51
52 --update cursor
53 CURSOR csr_ctr_reading_upd(p_lastrundate IN date)
54 IS
55 SELECT val_acc.access_id,
56 val_acc.user_id
57 FROM CSI_COUNTER_READINGS cnt_rd,
58 csm_counter_values_acc val_acc
59 where cnt_rd.counter_id = val_acc.counter_id
60 AND cnt_rd.last_update_date >= p_lastrundate;
61
62 --Delete cursor
63 CURSOR csr_ctr_reading_del
64 IS
65 SELECT val_acc.access_id,
66 val_acc.user_id
67 FROM CSI_COUNTER_READINGS cnt_rd,
68 csm_counter_values_acc val_acc
69 where cnt_rd.counter_id = val_acc.counter_id
70 AND NOT EXISTS
71 (SELECT 1 FROM csm_counters_acc cnt_acc
72 WHERE cnt_acc.counter_id =val_acc.counter_id
73 AND cnt_acc.user_id =val_acc.user_id);
74
75
76 --Cursor to get last run date
77 CURSOR l_last_run_date_csr
78 IS
79 SELECT nvl(last_run_date, (sysdate - 365*50))
80 FROM jtm_con_request_data
81 WHERE package_name = 'CSM_COUNTER_READING_EVENT_PKG'
82 AND procedure_name = 'REFRESH_ACC';
83
84 BEGIN
85
86 CSM_UTIL_PKG.LOG('Entering CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC ',
87 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
88
89 -- get last conc program update date
90 OPEN l_last_run_date_csr;
91 FETCH l_last_run_date_csr INTO l_prog_update_date;
92 CLOSE l_last_run_date_csr;
93
94 IF l_access_list.count > 0 THEN
95 l_access_list.delete;
96 END IF;
97
98 -- get resource list of all omfs users
99 l_all_omfs_resource_list := l_null_omfs_resource_list;
100 l_all_omfs_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
101
102 CSM_UTIL_PKG.LOG('Entering delete ', 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);
103
104 -- process all deletes
105 OPEN csr_ctr_reading_del;
106 FETCH csr_ctr_reading_del BULK COLLECT INTO l_access_list,l_user_list;
107 CLOSE csr_ctr_reading_del;
108
109 --mark dirty for delete
110 IF l_access_list.count > 0 THEN
111 FOR i IN 1..l_access_list.count LOOP
112 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
113 l_access_list(i),
114 l_user_list(i),
115 asg_download.del,
116 sysdate);
117
118 END LOOP;
119
120 -- bulk delete from acc table
121 FORALL i IN 1..l_access_list.count
122 DELETE FROM csm_counter_values_acc WHERE access_id = l_access_list(i);
123
124 l_access_list.delete;
125 END IF; -- end of process deletes
126
127 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
128
129
130 -- process all updates
131 OPEN csr_ctr_reading_upd(l_prog_update_date);
132 FETCH csr_ctr_reading_upd BULK COLLECT INTO l_access_list,l_user_list;
133 CLOSE csr_ctr_reading_upd;
134
135 IF l_access_list.count > 0 THEN
136 FOR i IN 1..l_access_list.count LOOP
137 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
138 l_access_list(i),
139 l_user_list(i),
140 asg_download.upd,
141 sysdate);
142
143 END LOOP;
144
145 l_access_list.delete;
146 END IF; -- end of process updates
147
148 IF l_counter_value_id_tbl.count > 0 THEN
149 l_counter_value_id_tbl.delete;
150 END IF;
151
152 IF l_counter_id_tbl.count > 0 THEN
153 l_counter_id_tbl.delete;
154 END IF;
155
156 IF l_user_list.count > 0 THEN
157 l_user_list.delete;
158 END IF;
159
160 IF l_access_list.count > 0 THEN
161 l_access_list.delete;
162 END IF;
163
164 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
165
166 --process all inserts
167 OPEN csr_ctr_reading_ins;
168 FETCH csr_ctr_reading_ins BULK COLLECT INTO l_access_list, l_counter_value_id_tbl, l_counter_id_tbl, l_user_list;
169 CLOSE csr_ctr_reading_ins;
170
171 IF l_access_list.count > 0 THEN
172 FOR i IN 1..l_access_list.count LOOP
173 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
174 l_access_list(i),
175 l_user_list(i),
176 asg_download.ins,
177 sysdate);
178
179 END LOOP;
180
181 FORALL i IN 1..l_access_list.count
182 INSERT INTO csm_counter_values_acc
183 (access_id,
184 counter_value_id,
185 counter_id,
186 user_id,
187 counter,
188 created_by,
189 creation_date,
190 last_updated_by,
191 last_update_date,
192 last_update_login)
193 VALUES (l_access_list(i),
194 l_counter_value_id_tbl(i),
195 l_counter_id_tbl(i),
196 l_user_list(i),
197 1,
198 fnd_global.user_id,
199 sysdate,
200 fnd_global.user_id,
201 sysdate,
202 fnd_global.login_id);
203
204
205 l_counter_value_id_tbl.delete;
206 l_counter_id_tbl.delete;
207 l_user_list.delete;
208 l_access_list.delete;
209
210 END IF; -- end of process inserts
211
212 -- update last_run_date
213 UPDATE jtm_con_request_data
214 SET last_run_date = sysdate
215 WHERE package_name = 'CSM_COUNTER_READING_EVENT_PKG'
216 AND procedure_name= 'REFRESH_ACC';
217
218 COMMIT;
219
220 p_status := 'FINE';
221 p_message := 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC Executed successfully';
222
223 CSM_UTIL_PKG.LOG('Leaving CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC ',
224 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
225
226 EXCEPTION
227 WHEN others THEN
228 l_sqlerrno := to_char(SQLCODE);
229 l_sqlerrmsg := substr(SQLERRM, 1,2000);
230 p_status := 'Error';
231 p_message := 'Error in CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC :' || l_sqlerrno || ':' || l_sqlerrmsg;
232 ROLLBACK;
233 CSM_UTIL_PKG.LOG('Exception in CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
234 'CSM_COUNTER_READING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
235 END REFRESH_ACC;
236
237 PROCEDURE COUNTER_VALUE_ACC_INS(p_counter_value_id IN NUMBER,
238 p_counter_id IN NUMBER,
239 p_error_msg OUT NOCOPY VARCHAR2,
240 x_return_status IN OUT NOCOPY VARCHAR2)
241 IS
242 --variable declarations
243 TYPE counter_value_tbl_typ IS TABLE OF CSI_COUNTER_READINGS.counter_value_id%TYPE INDEX BY BINARY_INTEGER;
244 TYPE counter_tbl_typ IS TABLE OF CSI_COUNTER_READINGS.counter_id%TYPE INDEX BY BINARY_INTEGER;
245
246 l_sqlerrno VARCHAR2(20);
247 l_sqlerrmsg varchar2(2000);
248 l_mark_dirty boolean;
249 l_pub_item varchar2(30) := 'CSF_M_COUNTER_VALUES';
250 l_access_list asg_download.access_list;
251 l_user_list asg_download.user_list;
252 l_counter_value_id_tbl counter_value_tbl_typ;
253 l_counter_id_tbl counter_tbl_typ;
254
255 --Cursor Declarations
256 --Insert Cursor
257 CURSOR csr_ctr_reading_ins(c_counter_value_id NUMBER,c_counter_id Number)
258 IS
259 SELECT CSM_COUNTER_VALUES_ACC_S.NEXTVAL,
260 cnt_rd.counter_value_id,
261 cnt_acc.counter_id,
262 cnt_acc.user_id
263 FROM CSI_COUNTER_READINGS cnt_rd,
264 csm_counters_acc cnt_acc
265 where cnt_rd.counter_id=cnt_acc.counter_id
266 AND cnt_rd.counter_value_id = c_counter_value_id
267 AND cnt_acc.counter_id = c_counter_id
268 AND NOT EXISTS
269 (SELECT 1 FROM csm_counter_values_acc val_acc
270 WHERE val_acc.counter_value_id =cnt_rd.counter_value_id
271 AND val_acc.user_id =cnt_acc.user_id);
272
273 BEGIN
274
275 CSM_UTIL_PKG.LOG('Entering CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS ',
276 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS', FND_LOG.LEVEL_PROCEDURE);
277
278 IF l_access_list.count > 0 THEN
279 l_access_list.delete;
280 l_counter_value_id_tbl.delete;
281 l_counter_id_tbl.delete;
282 l_user_list.delete;
283 END IF;
284
285
286 CSM_UTIL_PKG.LOG('Entering Insert ', 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS',FND_LOG.LEVEL_STATEMENT);
287
288 --process all inserts
289 OPEN csr_ctr_reading_ins(p_counter_value_id,p_counter_id);
290 FETCH csr_ctr_reading_ins BULK COLLECT INTO l_access_list, l_counter_value_id_tbl, l_counter_id_tbl, l_user_list;
291 CLOSE csr_ctr_reading_ins;
292
293 IF l_access_list.count > 0 THEN
294 FOR i IN 1..l_access_list.count LOOP
295 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
296 l_access_list(i),
297 l_user_list(i),
298 asg_download.ins,
299 sysdate);
300
301 END LOOP;
302
303 FORALL i IN 1..l_access_list.count
304 INSERT INTO csm_counter_values_acc
305 (access_id,
306 counter_value_id,
307 counter_id,
308 user_id,
309 counter,
310 created_by,
311 creation_date,
312 last_updated_by,
313 last_update_date,
314 last_update_login)
315 VALUES (l_access_list(i),
316 l_counter_value_id_tbl(i),
317 l_counter_id_tbl(i),
318 l_user_list(i),
319 1,
320 fnd_global.user_id,
321 sysdate,
322 fnd_global.user_id,
323 sysdate,
324 fnd_global.login_id);
325
326
327 l_counter_value_id_tbl.delete;
328 l_counter_id_tbl.delete;
329 l_user_list.delete;
330 l_access_list.delete;
331
332 END IF; -- end of process insert
333
334 CSM_UTIL_PKG.LOG('Leaving CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS ',
335 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS',FND_LOG.LEVEL_PROCEDURE);
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337
338
339 EXCEPTION
340 WHEN others THEN
341 l_sqlerrno := to_char(SQLCODE);
342 l_sqlerrmsg := substr(SQLERRM, 1,2000);
343 p_error_msg := 'Error in CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS :' || l_sqlerrno || ':' || l_sqlerrmsg;
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 RAISE;
346 CSM_UTIL_PKG.LOG('Exception in CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
347 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_INS',FND_LOG.LEVEL_EXCEPTION);
348 END COUNTER_VALUE_ACC_INS;
349
350
351
352
353
354 PROCEDURE COUNTER_VALUE_ACC_UPD(p_counter_value_id IN NUMBER,
355 p_counter_id IN NUMBER,
356 p_error_msg OUT NOCOPY VARCHAR2,
357 x_return_status IN OUT NOCOPY VARCHAR2)
358 IS
359 --variable declarations
360
361 l_sqlerrno VARCHAR2(20);
362 l_sqlerrmsg varchar2(2000);
363 l_mark_dirty boolean;
364 l_pub_item varchar2(30) := 'CSF_M_COUNTER_VALUES';
365 l_access_list asg_download.access_list;
366 l_user_list asg_download.user_list;
367
368 --Cursor Declarations
369 --Update Cursor
370 CURSOR csr_ctr_reading_upd(c_counter_value_id NUMBER)
371 IS
372 SELECT val_acc.access_id,
373 val_acc.user_id
374 FROM csm_counter_values_acc val_acc
375 WHERE val_acc.counter_value_id =c_counter_value_id;
376
377 BEGIN
378
379 CSM_UTIL_PKG.LOG('Entering CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD ',
380 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD', FND_LOG.LEVEL_PROCEDURE);
381
382 IF l_access_list.count > 0 THEN
383 l_access_list.delete;
384 l_user_list.delete;
385 END IF;
386
387
388 CSM_UTIL_PKG.LOG('Entering Update Counter Value id ', 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD',FND_LOG.LEVEL_STATEMENT);
389
390 --process all inserts
391 OPEN csr_ctr_reading_upd(p_counter_value_id);
392 FETCH csr_ctr_reading_upd BULK COLLECT INTO l_access_list,l_user_list;
393 CLOSE csr_ctr_reading_upd;
394
395 IF l_access_list.count > 0 THEN
396 FOR i IN 1..l_access_list.count LOOP
397 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
398 l_access_list(i),
399 l_user_list(i),
400 asg_download.ins,
401 sysdate);
402
403 END LOOP;
404 l_user_list.delete;
405 l_access_list.delete;
406
407 END IF; -- end of process insert
408
409 CSM_UTIL_PKG.LOG('Leaving CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD ',
410 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD',FND_LOG.LEVEL_PROCEDURE);
411 x_return_status := FND_API.G_RET_STS_SUCCESS;
412
413
414 EXCEPTION
415 WHEN others THEN
416 l_sqlerrno := to_char(SQLCODE);
417 l_sqlerrmsg := substr(SQLERRM, 1,2000);
418 p_error_msg := 'Error in CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD :' || l_sqlerrno || ':' || l_sqlerrmsg;
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 RAISE;
421 CSM_UTIL_PKG.LOG('Exception in CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD: ' || l_sqlerrno || ':' || l_sqlerrmsg,
422 'CSM_COUNTER_READING_EVENT_PKG.COUNTER_VALUE_ACC_UPD',FND_LOG.LEVEL_EXCEPTION);
423 END COUNTER_VALUE_ACC_UPD;
424
425 END CSM_COUNTER_READING_EVENT_PKG;