1 PACKAGE BODY CSL_CS_COUNTER_VALS_ACC_PKG AS
2 /* $Header: cslcvacb.pls 115.10 2002/11/08 14:02:58 asiegers ship $ */
3
4 /*** Globals ***/
5 g_counters_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTERS_ACC';
6 g_counters_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_ID';
7
8 g_ctr_vals_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTER_VALUES_ACC';
9 g_ctr_vals_pubi_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
10 JTM_HOOK_UTIL_PKG.t_publication_item_list('CS_COUNTER_VALUES');
11 g_ctr_vals_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_VALUES';
12 g_ctr_vals_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_VALUE_ID';
13
14 g_ctr_prop_vals_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTER_PROP_VALS_ACC';
15 g_ctr_prop_vals_pubi_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
16 JTM_HOOK_UTIL_PKG.t_publication_item_list('CS_COUNTER_PROP_VALS');
17 g_ctr_prop_vals_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_PROP_VALUES';
18 g_ctr_prop_vals_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_PROP_VALUE_ID';
19
20 g_debug_level NUMBER; -- debug level
21
22
23 /***
24 PRIVATE Function to return the parent (counter_id)
25 ***/
26 FUNCTION Get_Parent_Counter_Id(p_counter_value_id IN NUMBER)
27 RETURN NUMBER
28 IS
29 CURSOR c_parent(b_counter_value_id NUMBER) IS
30 SELECT COUNTER_ID
31 FROM CS_COUNTER_VALUES
32 WHERE COUNTER_VALUE_ID = b_counter_value_id;
33 l_parent_id NUMBER;
34 BEGIN
35 OPEN c_parent(p_counter_value_id);
36 FETCH c_parent INTO l_parent_id;
37 IF c_parent%NOTFOUND THEN
38 CLOSE c_parent;
39 RETURN NULL;
40 END IF;
41 CLOSE c_parent;
42 RETURN l_parent_id;
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN NULL;
46 END Get_Parent_Counter_Id;
47
48 /***
49 PRIVATE Function to return the parent (item_instance)
50 ***/
51 FUNCTION Get_Parent_Counter_Value_Id(p_counter_prop_val_id IN NUMBER)
52 RETURN NUMBER
53 IS
54 CURSOR c_parent(b_counter_prop_val_id NUMBER) IS
55 SELECT COUNTER_VALUE_ID
56 FROM CS_COUNTER_PROP_VALUES
57 WHERE COUNTER_PROP_VALUE_ID = b_counter_prop_val_id;
58 l_parent_id NUMBER;
59 BEGIN
60 OPEN c_parent(p_counter_prop_val_id);
61 FETCH c_parent INTO l_parent_id;
62 IF c_parent%NOTFOUND THEN
63 CLOSE c_parent;
64 RETURN NULL;
65 END IF;
66 CLOSE c_parent;
67 RETURN l_parent_id;
68 EXCEPTION
69 WHEN OTHERS THEN
70 RETURN NULL;
71 END Get_Parent_Counter_Value_Id;
72
73 /***
74 *** APIs directly access JTM_CS_COUNTER_PROP_VALS_ACC
75 ***/
76 PROCEDURE INSERT_CTR_PROP_VAL_ACC_RECORD ( p_counter_prop_value_id IN NUMBER,
77 p_resource_id IN NUMBER
78 )
79 IS
80 BEGIN
81 -- add debug info later.
82 JTM_HOOK_UTIL_PKG.Insert_Acc
83 ( P_PUBLICATION_ITEM_NAMES => g_ctr_prop_vals_pubi_name
84 ,P_ACC_TABLE_NAME => g_ctr_prop_vals_acc_table_name
85 ,P_PK1_NAME => g_ctr_prop_vals_pk1_name
86 ,P_PK1_NUM_VALUE => p_counter_prop_value_id
87 ,P_RESOURCE_ID => p_resource_id
88 );
89 END INSERT_CTR_PROP_VAL_ACC_RECORD;
90
91 PROCEDURE DELETE_CTR_PROP_VAL_ACC_RECORD ( p_counter_prop_value_id IN NUMBER,
92 p_resource_id IN NUMBER
93 )
94 IS
95 BEGIN
96 -- add debug info later.
97 JTM_HOOK_UTIL_PKG.Delete_Acc
98 ( P_PUBLICATION_ITEM_NAMES => g_ctr_prop_vals_pubi_name
99 ,P_ACC_TABLE_NAME => g_ctr_prop_vals_acc_table_name
100 ,P_PK1_NAME => g_ctr_prop_vals_pk1_name
101 ,P_PK1_NUM_VALUE => p_counter_prop_value_id
102 ,P_RESOURCE_ID => p_resource_id
103 );
104 END DELETE_CTR_PROP_VAL_ACC_RECORD;
105
106 PROCEDURE UPDATE_CTR_PROP_VAL_ACC_RECORD
107 ( p_resource_id IN NUMBER
108 ,p_acc_id IN NUMBER
109 )
110 IS
111 BEGIN
112 JTM_HOOK_UTIL_PKG.UPDATE_Acc
113 ( P_PUBLICATION_ITEM_NAMES => g_ctr_prop_vals_pubi_name
114 ,P_ACC_TABLE_NAME => g_ctr_prop_vals_acc_table_name
115 ,P_RESOURCE_ID => p_resource_id
116 ,P_ACCESS_ID => p_acc_id
117 );
118 END UPDATE_CTR_PROP_VAL_ACC_RECORD;
119
120 /***************************************
121 *** Private procedures that directly access the JTM_CS_COUNTER_VALUES_ACC tables
122 ***************************************/
123 -- add debug info later.
124
125 PROCEDURE INSERT_CTR_VALUE_ACC_Record
126 ( p_counter_value_id IN NUMBER
127 ,p_resource_id IN NUMBER
128 )
129 IS
130 CURSOR c_counter_prop_value (b_counter_value_id NUMBER) IS
131 SELECT COUNTER_PROP_VALUE_ID
132 FROM CS_COUNTER_PROP_VALUES
133 WHERE COUNTER_VALUE_ID = b_counter_value_id;
134 BEGIN
135 JTM_HOOK_UTIL_PKG.Insert_Acc
136 ( P_PUBLICATION_ITEM_NAMES => g_ctr_vals_pubi_name
137 ,P_ACC_TABLE_NAME => g_ctr_vals_acc_table_name
138 ,P_PK1_NAME => g_ctr_vals_pk1_name
139 ,P_PK1_NUM_VALUE => p_counter_value_id
140 ,P_RESOURCE_ID => p_resource_id
141 );
142 FOR r_counter_prop_value_id IN c_counter_prop_value( p_counter_value_id ) LOOP
143 -- add debug info later
144 INSERT_CTR_PROP_VAL_ACC_RECORD( r_counter_prop_value_id.COUNTER_PROP_VALUE_ID, p_resource_id );
145 END LOOP;
146 END INSERT_CTR_VALUE_ACC_Record;
147
148 PROCEDURE Update_CTR_VALUE_ACC_Record
149 ( p_resource_id IN NUMBER
150 ,p_acc_id IN NUMBER
151 )
152 IS
153 BEGIN
154 JTM_HOOK_UTIL_PKG.UPDATE_Acc
155 ( P_PUBLICATION_ITEM_NAMES => g_ctr_vals_pubi_name
156 ,P_ACC_TABLE_NAME => g_ctr_vals_acc_table_name
157 ,P_RESOURCE_ID => p_resource_id
158 ,P_ACCESS_ID => p_acc_id
159 );
160 END Update_CTR_VALUE_ACC_Record;
161
162 PROCEDURE DELETE_CTR_VALUE_ACC_Record
163 ( p_counter_value_id IN NUMBER
164 ,p_resource_id IN NUMBER
165 )
166 IS
167 CURSOR c_counter_prop_value (b_counter_value_id NUMBER) IS
168 SELECT COUNTER_PROP_VALUE_ID
169 FROM CS_COUNTER_PROP_VALUES
170 WHERE COUNTER_VALUE_ID = b_counter_value_id;
171 BEGIN
172 JTM_HOOK_UTIL_PKG.Delete_Acc
173 ( P_PUBLICATION_ITEM_NAMES => g_ctr_vals_pubi_name
174 ,P_ACC_TABLE_NAME => g_ctr_vals_acc_table_name
175 ,P_PK1_NAME => g_ctr_vals_pk1_name
176 ,P_PK1_NUM_VALUE => p_counter_value_id
177 ,P_RESOURCE_ID => p_resource_id
178 );
179 FOR r_counter_prop_value_id IN c_counter_prop_value( p_counter_value_id ) LOOP
180 -- add debug info later
181 DELETE_CTR_PROP_VAL_ACC_RECORD( r_counter_prop_value_id.COUNTER_PROP_VALUE_ID, p_resource_id );
182 END LOOP;
183 END DELETE_CTR_VALUE_ACC_Record;
184
185 /*******************************************************
186 *** PUBLIC APIS accessed by JTM_CS_COUNTERS_ACC_PKG
187 *******************************************************/
188
189 FUNCTION POST_INSERT_PARENT
190 ( p_counter_id IN NUMBER
191 ,p_resource_id IN NUMBER
192 )
193 RETURN BOOLEAN
194 IS
195 CURSOR c_counter_values (b_counter_id NUMBER) IS
196 SELECT COUNTER_VALUE_ID
197 FROM CS_COUNTER_VALUES
198 WHERE COUNTER_ID = b_counter_id;
199 BEGIN
200 FOR r_counter_value_id IN c_counter_values(p_counter_id)
201 LOOP
202 INSERT_CTR_VALUE_ACC_RECORD(r_counter_value_id.COUNTER_VALUE_ID, p_resource_id);
203 END LOOP;
204 RETURN TRUE;
205 EXCEPTION
206 WHEN OTHERS THEN
207 RETURN FALSE;
208 END Post_Insert_Parent;
209
210
211 FUNCTION PRE_DELETE_PARENT
212 ( p_counter_id IN NUMBER
213 ,p_resource_id IN NUMBER
214 )
215 RETURN BOOLEAN
216 IS
217 CURSOR c_counter_values (b_counter_id NUMBER) IS
218 SELECT COUNTER_VALUE_ID
219 FROM CS_COUNTER_VALUES
220 WHERE COUNTER_ID = b_counter_id;
221 BEGIN
222 FOR r_counter_value_id IN c_counter_values(p_counter_id)
223 LOOP
224 DELETE_CTR_VALUE_ACC_RECORD(r_counter_value_id.COUNTER_VALUE_ID, p_resource_id);
225 END LOOP;
226 RETURN TRUE;
227 EXCEPTION
228 WHEN OTHERS THEN
229 RETURN FALSE;
230 END PRE_DELETE_PARENT;
231
232 /************************************************
233 *** Public APIs accessed by VUHK package
234 ************************************************/
235
236 /***
237 Function that checks if counter value record should be replicated.
238 Returns TRUE if it should
239 ***/
240 FUNCTION Replicate_Record
241 ( p_counter_value_id NUMBER
242 )
243 RETURN BOOLEAN
244 IS
245 CURSOR c_counter_value (b_counter_value_id NUMBER) IS
246 SELECT *
247 FROM CS_COUNTER_VALUES
248 WHERE COUNTER_VALUE_ID = b_counter_value_id;
249 CURSOR c_counter_acc (b_counter_id NUMBER) IS
250 SELECT *
251 FROM JTM_CS_COUNTERS_ACC
252 WHERE COUNTER_ID = b_counter_id;
253 r_counter_value c_counter_value%ROWTYPE;
254 r_counter_acc c_counter_acc%ROWTYPE;
255 l_counter_id CS_COUNTER_VALUES.COUNTER_ID%TYPE;
256 BEGIN
257 /*** get debug level ***/
258 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
259
260 /*** Check if the p_counter_value_id is valid ***/
261 OPEN c_counter_value( p_counter_value_id );
262 FETCH c_counter_value INTO r_counter_value;
263 IF c_counter_value%NOTFOUND THEN
264 /*** could not find counter value record -> exit ***/
265 CLOSE c_counter_value;
266 RETURN FALSE;
267 END IF;
268
269 /*** Check if the counter_id is a not null ***/
270 l_counter_id := r_counter_value.counter_id;
271 IF l_counter_id IS NULL THEN
272 /*** null counter_id -> exit ***/
273 CLOSE c_counter_value;
274 RETURN FALSE;
275 END IF;
276 CLOSE c_counter_value;
277
278 /*** Check if the counter id is a valid counter_id in the acc table
279 JTM_CS_COUNTERS_ACC ***/
280 OPEN c_counter_acc( l_counter_id );
281 FETCH c_counter_acc INTO r_counter_acc;
282 IF c_counter_acc%NOTFOUND THEN
283 /*** no -> don't replicate ***/
284 RETURN FALSE;
285 END IF;
286
287 RETURN TRUE;
288 END Replicate_Record;
289
290 /********************************************************
291 The following procedures are for VUHK of CS_COUNTER_VALUES.
292 *******************************************/
293
294 /* Called before counter value Insert.
295 DO NOTHING
296 */
297 PROCEDURE PRE_INSERT_COUNTER_VALUE
298 ( x_return_status OUT NOCOPY varchar2
299 )
300 IS
301 BEGIN
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 END PRE_INSERT_COUNTER_VALUE ;
304
305 /* Called after counter value Insert.
306 Check if counter is mobilized. If so, do the insert.
307 */
308 PROCEDURE POST_INSERT_COUNTER_VALUE ( P_Api_Version_Number IN NUMBER
309 , P_Init_Msg_List IN VARCHAR2
310 , P_Commit IN VARCHAR2
311 , p_validation_level IN NUMBER
312 , p_COUNTER_GRP_LOG_ID IN NUMBER
313 , X_Return_Status OUT NOCOPY VARCHAR2
314 , X_Msg_Count OUT NOCOPY NUMBER
315 , X_Msg_Data OUT NOCOPY VARCHAR2 )
316 IS
317 l_tab_resource_id dbms_sql.Number_Table;
318 l_tab_access_id dbms_sql.Number_Table;
319 l_counter_id NUMBER;
320
321 CURSOR c_ctr_vals( b_grp_log_id NUMBER ) IS
322 SELECT *
323 FROM CS_COUNTER_VALUES
324 WHERE COUNTER_GRP_LOG_ID = b_grp_log_id;
325
326 BEGIN
327 x_return_status := FND_API.G_RET_STS_SUCCESS;
328
329 /*Fetch all counter value records for this grp log id*/
330 FOR r_ctr_vals IN c_ctr_vals( p_COUNTER_GRP_LOG_ID ) LOOP
331
332 l_counter_id := Get_Parent_Counter_Id(r_ctr_vals.counter_value_id);
333
334 IF l_counter_id IS NOT NULL
335 THEN
336 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
337 ( p_acc_table_name => g_counters_acc_table_name
338 ,p_pk1_name => g_counters_pk1_name
339 ,p_pk1_num_value => l_counter_id
340 ,l_tab_resource_id => l_tab_resource_id
341 ,l_tab_access_id => l_tab_access_id
342 );
343
344 IF l_tab_resource_id.COUNT > 0 THEN
345 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
346 INSERT_CTR_VALUE_ACC_RECORD
347 (r_ctr_vals.counter_value_id
348 ,l_tab_resource_id(i)
349 );
350 END LOOP; --l_tab_resource_id.FIRST
351 END IF; --l_tab_resource_id.COUNT
352 END IF; -- l_counter_id not null
353 END LOOP; --c_ctr_vals
354 END POST_INSERT_COUNTER_VALUE ;
355
356 /* Called before counter value Update
357 * DO NOTHING
358 */
359 PROCEDURE PRE_UPDATE_COUNTER_VALUE ( x_return_status OUT NOCOPY varchar2)
360 IS
361 BEGIN
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363 END PRE_UPDATE_COUNTER_VALUE ;
364
365 /* Called after counter value Update
366 * Mark dirty
367 */
368 PROCEDURE POST_UPDATE_COUNTER_VALUE ( P_Api_Version_Number IN NUMBER
369 , P_Init_Msg_List IN VARCHAR2
370 , P_Commit IN VARCHAR2
371 , p_validation_level IN NUMBER
372 , p_COUNTER_GRP_LOG_ID IN NUMBER
373 , p_object_version_number IN NUMBER
374 , X_Return_Status OUT NOCOPY VARCHAR2
375 , X_Msg_Count OUT NOCOPY NUMBER
376 , X_Msg_Data OUT NOCOPY VARCHAR2 )
377 IS
378 l_tab_resource_id dbms_sql.Number_Table;
379 l_tab_access_id dbms_sql.Number_Table;
380 CURSOR c_ctr_vals( b_grp_log_id NUMBER ) IS
381 SELECT *
382 FROM CS_COUNTER_VALUES
383 WHERE COUNTER_GRP_LOG_ID = b_grp_log_id;
384
385 BEGIN
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 FOR r_ctr_vals IN c_ctr_vals( p_COUNTER_GRP_LOG_ID ) LOOP
389
390 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
391 ( p_acc_table_name => g_ctr_vals_acc_table_name
392 ,p_pk1_name => g_ctr_vals_pk1_name
393 ,p_pk1_num_value => r_ctr_vals.counter_value_id
394 ,l_tab_resource_id => l_tab_resource_id
395 ,l_tab_access_id => l_tab_access_id
396 );
397
398 IF l_tab_resource_id.COUNT > 0 THEN
399 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
400 UPDATE_CTR_VALUE_ACC_RECORD
401 (l_tab_resource_id(i)
402 ,l_tab_access_id(i)
403 );
404 END LOOP;
405 END IF;
406 END LOOP;
407 END POST_UPDATE_COUNTER_VALUE;
408
409 /* Called before counter value Update
410 * DO NOTHING
411 */
412 PROCEDURE PRE_DELETE_COUNTER_VALUE ( x_return_status OUT NOCOPY varchar2)
413 IS
414 BEGIN
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416 END PRE_DELETE_COUNTER_VALUE ;
417
418 /* Called after counter value Update
419 * Mark dirty
420 */
421 PROCEDURE POST_DELETE_COUNTER_VALUE (
422 p_counter_value_id in NUMBER
423 ,x_return_status OUT NOCOPY varchar2)
424 IS
425 l_tab_resource_id dbms_sql.Number_Table;
426 l_tab_access_id dbms_sql.Number_Table;
427 BEGIN
428 x_return_status := FND_API.G_RET_STS_SUCCESS;
429
430 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
431 ( p_acc_table_name => g_ctr_vals_acc_table_name
432 ,p_pk1_name => g_ctr_vals_pk1_name
433 ,p_pk1_num_value => p_counter_value_id
434 ,l_tab_resource_id => l_tab_resource_id
435 ,l_tab_access_id => l_tab_access_id
436 );
437
438
439 IF l_tab_resource_id.COUNT > 0 THEN
440 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
441 DELETE_CTR_VALUE_ACC_RECORD
442 (p_counter_value_id
443 ,l_tab_resource_id(i)
444 );
445 END LOOP;
446 END IF;
447 END POST_DELETE_COUNTER_VALUE;
448
449
450 /****************************************************************
451 The following procedures are for VUHK of CS_COUNTER_PROP_VALUES.
452 ****************************************************************/
453
454 /* Called before counter prop value Insert.
455 DO NOTHING
456 */
457 PROCEDURE PRE_INSERT_COUNTER_PROP_VAL
458 ( x_return_status OUT NOCOPY varchar2
459 )
460 IS
461 BEGIN
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463 END PRE_INSERT_COUNTER_PROP_VAL ;
464
465 /* Called after counter Prop value Insert.
466 Check if counter is mobilized. If so, do the insert.
467 */
468 PROCEDURE POST_INSERT_COUNTER_PROP_VAL ( P_Api_Version_Number IN NUMBER
469 , P_Init_Msg_List IN VARCHAR2
470 , P_Commit IN VARCHAR2
471 , p_validation_level IN NUMBER
472 , p_COUNTER_GRP_LOG_ID IN NUMBER
473 , X_Return_Status OUT NOCOPY VARCHAR2
474 , X_Msg_Count OUT NOCOPY NUMBER
475 , X_Msg_Data OUT NOCOPY VARCHAR2 )
476 IS
477 l_tab_resource_id dbms_sql.Number_Table;
478 l_tab_access_id dbms_sql.Number_Table;
479
480 CURSOR c_ctr_prop_val ( b_ctr_grp_log_id NUMBER ) IS
481 SELECT CPV.COUNTER_PROP_VALUE_ID
482 , CPV.COUNTER_VALUE_ID
483 FROM CS_COUNTER_PROP_VALUES CPV
484 , CS_COUNTER_VALUES CCS
485 WHERE CCS.COUNTER_VALUE_ID = CPV.COUNTER_VALUE_ID
486 AND CCS.COUNTER_GRP_LOG_ID = b_ctr_grp_log_id;
487 BEGIN
488 x_return_status := FND_API.G_RET_STS_SUCCESS;
489
490 FOR r_ctr_prop_vals IN c_ctr_prop_val( p_COUNTER_GRP_LOG_ID ) LOOP
491 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
492 ( p_acc_table_name => g_counters_acc_table_name
493 ,p_pk1_name => g_counters_pk1_name
494 ,p_pk1_num_value => r_ctr_prop_vals.COUNTER_VALUE_ID
495 ,l_tab_resource_id => l_tab_resource_id
496 ,l_tab_access_id => l_tab_access_id
497 );
498
499 IF l_tab_resource_id.COUNT > 0 THEN
500 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
501 INSERT_CTR_PROP_VAL_ACC_RECORD
502 (r_ctr_prop_vals.COUNTER_PROP_VALUE_ID
503 ,l_tab_resource_id(i)
504 );
505 END LOOP; --l_tab_resource_id.FIRST
506 END IF; -- l_tab_resource_id.COUNT
507 END LOOP; -- FOR r_ctr_prop_vals
508 END POST_INSERT_COUNTER_PROP_VAL ;
509
510 /* Called before counter prop value Update
511 * DO NOTHING
512 */
513 PROCEDURE PRE_UPDATE_COUNTER_PROP_VAL ( x_return_status OUT NOCOPY varchar2)
514 IS
515 BEGIN
516 x_return_status := FND_API.G_RET_STS_SUCCESS;
517 END PRE_UPDATE_COUNTER_PROP_VAL ;
518
519 /* Called after counter value Update
520 * Mark dirty
521 */
522 PROCEDURE POST_UPDATE_COUNTER_PROP_VAL ( P_Api_Version_Number IN NUMBER
523 , P_Init_Msg_List IN VARCHAR2
524 , P_Commit IN VARCHAR2
525 , p_validation_level IN NUMBER
526 , p_COUNTER_GRP_LOG_ID IN NUMBER
527 , p_object_version_number IN NUMBER
528 , X_Return_Status OUT NOCOPY VARCHAR2
529 , X_Msg_Count OUT NOCOPY NUMBER
530 , X_Msg_Data OUT NOCOPY VARCHAR2 )
531 IS
532 l_tab_resource_id dbms_sql.Number_Table;
533 l_tab_access_id dbms_sql.Number_Table;
534
535 CURSOR c_ctr_prop_val ( b_ctr_grp_log_id NUMBER ) IS
536 SELECT CPV.COUNTER_PROP_VALUE_ID
537 , CPV.COUNTER_VALUE_ID
538 FROM CS_COUNTER_PROP_VALUES CPV
539 , CS_COUNTER_VALUES CCS
540 WHERE CCS.COUNTER_VALUE_ID = CPV.COUNTER_VALUE_ID
541 AND CCS.COUNTER_GRP_LOG_ID = b_ctr_grp_log_id;
542
543
544 BEGIN
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 FOR r_ctr_prop_vals IN c_ctr_prop_val( p_COUNTER_GRP_LOG_ID ) LOOP
548 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
549 ( p_acc_table_name => g_ctr_prop_vals_acc_table_name
550 ,p_pk1_name => g_ctr_prop_vals_pk1_name
551 ,p_pk1_num_value => r_ctr_prop_vals.COUNTER_PROP_VALUE_ID
552 ,l_tab_resource_id => l_tab_resource_id
553 ,l_tab_access_id => l_tab_access_id
554 );
555
556 IF l_tab_resource_id.COUNT > 0 THEN
557 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
558 UPDATE_CTR_PROP_VAL_ACC_RECORD
559 (l_tab_resource_id(i)
560 ,l_tab_access_id(i)
561 );
562 END LOOP;
563 END IF;
564 END LOOP;
565 END POST_UPDATE_COUNTER_PROP_VAL;
566
567 /* Called before counter value Update
568 * DO NOTHING
569 */
570 PROCEDURE PRE_DELETE_COUNTER_PROP_VAL ( x_return_status OUT NOCOPY varchar2)
571 IS
572 BEGIN
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574 END PRE_DELETE_COUNTER_PROP_VAL ;
575
576 /* Called after counter prop value delete
577 * Mark dirty
578 */
579 PROCEDURE POST_DELETE_COUNTER_PROP_VAL (
580 p_counter_prop_val_id in NUMBER
581 ,x_return_status OUT NOCOPY varchar2)
582 IS
583 l_tab_resource_id dbms_sql.Number_Table;
584 l_tab_access_id dbms_sql.Number_Table;
585 BEGIN
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
589 ( p_acc_table_name => g_ctr_vals_acc_table_name
590 ,p_pk1_name => g_ctr_vals_pk1_name
591 ,p_pk1_num_value => p_counter_prop_val_id
592 ,l_tab_resource_id => l_tab_resource_id
593 ,l_tab_access_id => l_tab_access_id
594 );
595
596 IF l_tab_resource_id.COUNT > 0 THEN
597 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
598 DELETE_CTR_VALUE_ACC_RECORD
599 (p_counter_prop_val_id
600 ,l_tab_resource_id(i)
601 );
602 END LOOP;
603 END IF;
604 END POST_DELETE_COUNTER_PROP_VAL;
605
606 END CSL_CS_COUNTER_VALS_ACC_PKG;
[Home] [Help]