[Home] [Help]
PACKAGE BODY: APPS.CSM_CTR_PROP_READ_EVENT_PKG
Source
1 PACKAGE BODY CSM_CTR_PROP_READ_EVENT_PKG AS
2 /* $Header: csmecprb.pls 120.2 2006/07/26 09:29:07 trajasek noship $ */
3
4 --
5 -- Purpose: USed to downlaod Counter properties for each counter
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -----------------------------------------------------------
9
10 /*** Globals ***/
11 g_count_prp_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROP_VALUES_ACC';
12 g_count_prp_table_name CONSTANT VARCHAR2(30) := 'CSI_CTR_PROPERTY_READINGS';
13 g_count_prp_seq_name CONSTANT VARCHAR2(30) := 'CSM_COUNTER_PROP_VALUES_ACC_S';
14 g_count_prp_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_PROP_VALUE_ID';
15 g_count_prp_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
16 CSM_ACC_PKG.t_publication_item_list('CSM_COUNTER_PROP_VALUES');
17
18
19 PROCEDURE CTR_PROPERTY_READ_INS(p_counter_value_id NUMBER,
20 p_user_id NUMBER,
21 p_error_msg OUT NOCOPY VARCHAR2,
22 x_return_status IN OUT NOCOPY VARCHAR2)
23 IS
24 --Cursor to insert counter property
25 --this happens if a counter is inserted
26 CURSOR c_prop_value_ins(c_counter_value_id NUMBER,c_user_id NUMBER)
27 IS
28 SELECT
29 B.COUNTER_PROP_VALUE_ID
30 FROM
31 CSI_CTR_PROPERTY_READINGS B,
32 csm_counter_values_acc VACC
33 WHERE B.COUNTER_VALUE_ID = VACC.COUNTER_VALUE_ID
34 AND VACC.USER_ID = c_user_id
35 AND VACC.COUNTER_VALUE_ID = c_counter_value_id
36 AND NOT EXISTS( SELECT 1
37 FROM CSM_COUNTER_PROP_VALUES_ACC PVACC
38 WHERE PVACC.COUNTER_PROP_VALUE_ID = B.COUNTER_PROP_VALUE_ID
39 AND PVACC.USER_ID = c_user_id);
40
41 --variable declarations
42 l_err_msg VARCHAR2(4000);
43
44 BEGIN
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46 l_err_msg := 'Entering CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS' || ' for PK ' || to_char(p_counter_value_id);
47 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS', FND_LOG.LEVEL_PROCEDURE);
48
49 -- Open USER IDs loop
50 FOR r_cntr_prp_val_rec IN c_prop_value_ins(p_counter_value_id,p_user_id) LOOP
51 -- Call Insert ACC
52 CSM_ACC_PKG.Insert_Acc
53 ( P_PUBLICATION_ITEM_NAMES => g_count_prp_pubi_name
54 ,P_ACC_TABLE_NAME => g_count_prp_acc_table_name
55 ,P_SEQ_NAME => g_count_prp_seq_name
56 ,P_PK1_NAME => g_count_prp_pk1_name
57 ,P_PK1_NUM_VALUE => r_cntr_prp_val_rec.counter_prop_value_id
58 ,P_USER_ID => p_user_id
59 );
60 END LOOP ; --End USER IDs loop
61
62 l_err_msg := 'Leaving CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS' || ' for Counter ' || to_char(p_counter_value_id);
63 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS', FND_LOG.LEVEL_PROCEDURE);
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF c_prop_value_ins%ISOPEN then
68 CLOSE c_prop_value_ins;
69 END IF;
70
71 p_error_msg := ' FAILED CTR_PROPERTY_READ_INS FOR COUNTER VALUE:' || to_char(p_counter_value_id);
72 x_return_status := FND_API.G_RET_STS_ERROR;
73 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_INS',FND_LOG.LEVEL_EXCEPTION);
74 RAISE;
75
76 END CTR_PROPERTY_READ_INS;
77
78
79 PROCEDURE CTR_PROPERTY_READ_UPD( p_counter_value_id IN NUMBER,
80 p_user_id IN NUMBER,
81 p_error_msg OUT NOCOPY VARCHAR2,
82 x_return_status IN OUT NOCOPY VARCHAR2)
83 IS
84 --Cursors to update counter property
85 --this is called if a counter is updated
86 CURSOR c_prop_value_upd(c_counter_value_id NUMBER,c_user_id NUMBER)
87 IS
88 SELECT
89 VACC.ACCESS_ID ,
90 VACC.USER_ID
91 FROM
92 CSI_CTR_PROPERTY_READINGS B,
93 CSM_COUNTER_PROP_VALUES_ACC VACC
94 WHERE B.COUNTER_PROP_VALUE_ID = VACC.COUNTER_PROP_VALUE_ID
95 AND VACC.USER_ID = c_user_id
96 AND B.COUNTER_PROP_VALUE_ID = c_counter_value_id;
97
98 --variable declarations
99 l_err_msg VARCHAR2(4000);
100 l_markdirty BOOLEAN;
101 l_pub_item_name VARCHAR2(240) := 'CSM_COUNTER_PROP_VALUES';
102
103 BEGIN
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105 l_err_msg := 'Entering CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD' || ' for PK(COUNTER VALUE ID) ' || to_char(p_counter_value_id);
106 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD', FND_LOG.LEVEL_PROCEDURE);
107
108 -- Open USER IDs loop
109 FOR r_cntr_prp_val_rec IN c_prop_value_upd(p_counter_value_id,p_user_id) LOOP
110 -- Call Insert ACC
111 l_markdirty := csm_util_pkg.MakeDirtyForUser ( l_pub_item_name
112 , r_cntr_prp_val_rec.access_id
113 , r_cntr_prp_val_rec.user_id
114 , 'U'
115 , sysdate);
116 END LOOP ; --End USER IDs loop
117
118 l_err_msg := 'Leaving CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD' || ' for Counter value ' || to_char(p_counter_value_id);
119 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD', FND_LOG.LEVEL_PROCEDURE);
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 IF c_prop_value_upd%ISOPEN then
124 CLOSE c_prop_value_upd;
125 END IF;
126
127 p_error_msg := ' FAILED CTR_PROPERTY_READ_UPD FOR COUNTER VALUE ID : ' || to_char(p_counter_value_id);
128 x_return_status := FND_API.G_RET_STS_ERROR;
129 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_UPD',FND_LOG.LEVEL_EXCEPTION);
130 RAISE;
131
132
133 END CTR_PROPERTY_READ_UPD;
134
135 PROCEDURE CTR_PROPERTY_READ_DEL( p_counter_value_id IN NUMBER,
136 p_user_id IN NUMBER,
137 p_error_msg OUT NOCOPY VARCHAR2,
138 x_return_status IN OUT NOCOPY VARCHAR2)
139 IS
140 --Cursor to delete counter property
141 --this is called if a counter is Deleted for a user
142 CURSOR c_prop_value_del(c_counter_value_id NUMBER,c_user_id NUMBER)
143 IS
144 SELECT
145 VACC.ACCESS_ID ,
146 B.COUNTER_PROP_VALUE_ID
147 FROM
148 CSI_CTR_PROPERTY_READINGS B,
149 CSM_COUNTER_PROP_VALUES_ACC VACC
150 WHERE B.COUNTER_PROP_VALUE_ID = VACC.COUNTER_PROP_VALUE_ID
151 AND VACC.USER_ID = c_user_id
152 AND B.COUNTER_VALUE_ID = c_counter_value_id;
153
154 --variable declarations
155 l_err_msg VARCHAR2(4000);
156
157 BEGIN
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159 l_err_msg := 'Entering CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL' || ' for PK(COUNTER VALUE ID ' || to_char(p_counter_value_id);
160 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL', FND_LOG.LEVEL_PROCEDURE);
161
162 -- Open counter property id loop
163 FOR r_cntr_prp_val_rec IN c_prop_value_del(p_counter_value_id,p_user_id) LOOP
164 -- Call Delete ACC
165 CSM_ACC_PKG.Delete_Acc
166 ( P_PUBLICATION_ITEM_NAMES => g_count_prp_pubi_name
167 ,P_ACC_TABLE_NAME => g_count_prp_acc_table_name
168 ,P_PK1_NAME => g_count_prp_pk1_name
169 ,P_PK1_NUM_VALUE => r_cntr_prp_val_rec.counter_prop_value_id
170 ,P_USER_ID => p_user_id
171 );
172 END LOOP ; --End USER IDs loop
173
174 l_err_msg := 'Leaving CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL' || ' for Counter value' || to_char(p_counter_value_id);
175 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL', FND_LOG.LEVEL_PROCEDURE);
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF c_prop_value_del%ISOPEN then
180 CLOSE c_prop_value_del;
181 END IF;
182
183 p_error_msg := ' FAILED CTR_PROPERTY_READ_DEL FOR COUNTER VALUE ID : ' || to_char(p_counter_value_id);
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_CTR_PROP_READ_EVENT_PKG.CTR_PROPERTY_READ_DEL',FND_LOG.LEVEL_EXCEPTION);
186 RAISE;
187
188 END CTR_PROPERTY_READ_DEL;
189
190
191 PROCEDURE Refresh_acc(p_status OUT NOCOPY VARCHAR2,
192 p_message OUT NOCOPY VARCHAR2)
193 IS
194 PRAGMA AUTONOMOUS_TRANSACTION;
195 --variable declarations
196 TYPE counter_prop_val_tbl_typ IS TABLE OF CSI_CTR_PROPERTY_READINGS.counter_prop_value_id%TYPE INDEX BY BINARY_INTEGER;
197
198 l_sqlerrno VARCHAR2(20);
199 l_sqlerrmsg varchar2(2000);
200 l_mark_dirty boolean;
201 l_pub_item varchar2(30) := 'CSM_COUNTER_PROP_VALUES';
202 l_access_list asg_download.access_list;
203 l_user_list asg_download.user_list;
204 l_all_omfs_resource_list asg_download.user_list;
205 l_null_omfs_resource_list asg_download.user_list;
206 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
207 l_counter_prop_value_id_tbl counter_prop_val_tbl_typ;
208
209 --Cursor Declarations
210 --Insert Cursor
211 CURSOR csr_ctr_prop_reading_ins (c_last_run_date DATE)
212 IS
213 SELECT CSM_COUNTER_PROP_VALUES_ACC_S.NEXTVAL,
214 prd.counter_prop_value_id,
215 val_acc.user_id
216 FROM CSI_CTR_PROPERTY_READINGS prd,
217 csm_counter_values_acc val_acc
218 where prd.counter_value_id = val_acc.counter_value_id
219 AND val_acc.creation_date >= c_last_run_date
220 AND NOT EXISTS
221 (SELECT 1 FROM CSM_COUNTER_PROP_VALUES_ACC prop_acc
222 WHERE prop_acc.counter_prop_value_id =prd.counter_prop_value_id
223 AND prop_acc.user_id = val_acc.user_id );
224
225 --update Not supported for property reading
226
227 --Delete cursor
228 CURSOR csr_ctr_prop_reading_del
229 IS
230 SELECT prop_acc.access_id,
231 prop_acc.user_id
232 FROM CSI_CTR_PROPERTY_READINGS prd,
233 CSM_COUNTER_PROP_VALUES_ACC prop_acc
234 where prd.counter_prop_value_id = prop_acc.counter_prop_value_id
235 AND NOT EXISTS
236 (SELECT 1 FROM csm_counter_values_acc val_acc
237 WHERE prd.counter_value_id = val_acc.counter_value_id
238 AND prop_acc.user_id = val_acc.user_id);
239
240
241 --Cursor to get last run date
242 CURSOR l_last_run_date_csr
243 IS
244 SELECT nvl(last_run_date, (sysdate - 365*50))
245 FROM jtm_con_request_data
246 WHERE package_name = 'CSM_CTR_PROP_READ_EVENT_PKG'
247 AND procedure_name = 'REFRESH_ACC';
248
249 BEGIN
250
251 CSM_UTIL_PKG.LOG('Entering CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC ',
252 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
253
254 -- get last conc program update date
255 OPEN l_last_run_date_csr;
256 FETCH l_last_run_date_csr INTO l_prog_update_date;
257 CLOSE l_last_run_date_csr;
258
259 IF l_access_list.count > 0 THEN
260 l_access_list.delete;
261 END IF;
262
263 -- get resource list of all omfs users
264 l_all_omfs_resource_list := l_null_omfs_resource_list;
265 l_all_omfs_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
266
267 CSM_UTIL_PKG.LOG('Entering delete ', 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);
268
269 -- process all deletes
270 OPEN csr_ctr_prop_reading_del;
271 FETCH csr_ctr_prop_reading_del BULK COLLECT INTO l_access_list,l_user_list;
272 CLOSE csr_ctr_prop_reading_del;
273
274 --mark dirty for delete
275 IF l_access_list.count > 0 THEN
276 FOR i IN 1..l_access_list.count LOOP
277 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
278 l_access_list(i),
279 l_user_list(i),
280 asg_download.del,
281 sysdate);
282
283 END LOOP;
284
285 -- bulk delete from acc table
286 FORALL i IN 1..l_access_list.count
287 DELETE FROM CSM_COUNTER_PROP_VALUES_ACC WHERE access_id = l_access_list(i);
288
289 l_access_list.delete;
290 END IF; -- end of process deletes
291
292
293 CSM_UTIL_PKG.LOG('Leaving DELETE and entering inserts', 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
294
295 --process all inserts
296 OPEN csr_ctr_prop_reading_ins (l_prog_update_date);
297 FETCH csr_ctr_prop_reading_ins BULK COLLECT INTO l_access_list, l_counter_prop_value_id_tbl,l_user_list;
298 CLOSE csr_ctr_prop_reading_ins;
299
300 IF l_access_list.count > 0 THEN
301 FOR i IN 1..l_access_list.count LOOP
302 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
303 l_access_list(i),
304 l_user_list(i),
305 asg_download.ins,
306 sysdate);
307
308 END LOOP;
309
310 FORALL i IN 1..l_access_list.count
311 INSERT INTO CSM_COUNTER_PROP_VALUES_ACC
312 (access_id,
313 counter_prop_value_id,
314 user_id,
315 counter,
316 created_by,
317 creation_date,
318 last_updated_by,
319 last_update_date,
320 last_update_login)
321 VALUES (l_access_list(i),
322 l_counter_prop_value_id_tbl(i),
323 l_user_list(i),
324 1,
325 fnd_global.user_id,
326 sysdate,
327 fnd_global.user_id,
328 sysdate,
329 fnd_global.login_id);
330
331
332 l_counter_prop_value_id_tbl.delete;
333 l_user_list.delete;
334 l_access_list.delete;
335
336 END IF; -- end of process inserts
337
338 -- update last_run_date
339 UPDATE jtm_con_request_data
340 SET last_run_date = sysdate
341 WHERE package_name = 'CSM_CTR_PROP_READ_EVENT_PKG'
342 AND procedure_name= 'REFRESH_ACC';
343
344 COMMIT;
345
346 p_status := 'FINE';
347 p_message := 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC Executed successfully';
348
349 CSM_UTIL_PKG.LOG('Leaving CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC ',
350 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
351
352 EXCEPTION
353 WHEN others THEN
354 l_sqlerrno := to_char(SQLCODE);
355 l_sqlerrmsg := substr(SQLERRM, 1,2000);
356 p_status := 'Error';
357 p_message := 'Error in CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC :' || l_sqlerrno || ':' || l_sqlerrmsg;
358 ROLLBACK;
359 CSM_UTIL_PKG.LOG('Exception in CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
360 'CSM_CTR_PROP_READ_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
361 END REFRESH_ACC;
362
363
364 END CSM_CTR_PROP_READ_EVENT_PKG; -- Package spec of CSM_CTR_PROP_READ_EVENT_PKG