1 PACKAGE BODY CSL_CS_COUNTERS_ACC_PKG AS
2 /* $Header: cslctacb.pls 115.10 2002/11/08 14:03:34 asiegers ship $ */
3
4 /*** Globals ***/
5 g_item_insts_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_INSTANCES_ACC';
6 g_item_insts_table_name CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
7 g_item_insts_pk1_name CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
8
9 g_ctr_grps_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTER_GROUPS_ACC';
10 g_ctr_grps_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_GROUPS';
11 g_ctr_grps_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_GROUP_ID';
12 g_ctr_grps_pubi_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
13 JTM_HOOK_UTIL_PKG.t_publication_item_list('CS_COUNTER_GROUPS');
14
15 g_counters_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTERS_ACC';
16 g_counters_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTERS';
17 g_counters_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_ID';
18 g_counters_pubi_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
19 JTM_HOOK_UTIL_PKG.t_publication_item_list('CS_COUNTERS');
20
21 g_ctr_props_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CS_COUNTER_PROPS_ACC';
22 g_ctr_props_table_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_PROPERTIES';
23 g_ctr_props_pk1_name CONSTANT VARCHAR2(30) := 'COUNTER_PROPERTY_ID';
24 g_ctr_props_pubi_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
25 JTM_HOOK_UTIL_PKG.t_publication_item_list('CS_COUNTER_PROPS');
26
27 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
28 g_debug_level NUMBER; -- debug level
29
30 /************* PRIVATE FUNCTIONS / PROCEDURES *********/
31
32 /***
33 PRIVATE Function to return the parent (item_instance)
34 ***/
35 FUNCTION Get_Parent_Item_Instance_Id(p_counter_group_id IN NUMBER)
36 RETURN NUMBER
37 IS
38 CURSOR c_parent(b_counter_group_id NUMBER) IS
39 SELECT SOURCE_OBJECT_ID
40 FROM CS_COUNTER_GROUPS
41 WHERE SOURCE_OBJECT_CODE = 'CP' AND COUNTER_GROUP_ID = b_counter_group_id;
42 l_parent_id NUMBER;
43 BEGIN
44 OPEN c_parent(p_counter_group_id);
45 FETCH c_parent INTO l_parent_id;
46 IF c_parent%NOTFOUND THEN
47 CLOSE c_parent;
48 RETURN NULL;
49 END IF;
50 CLOSE c_parent;
51 RETURN l_parent_id;
52 EXCEPTION
53 WHEN OTHERS THEN
54 jtm_message_log_pkg.Log_Msg
55 ( p_counter_group_id
56 , g_ctr_grps_table_name
57 , 'Exception happened in Get_Parent_Item_Instance_Id for ' || p_counter_group_id
58 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
59 RETURN NULL;
60 END Get_Parent_Item_Instance_Id;
61
62 FUNCTION Get_Parent_Ctr_Grp_Id(p_counter_id IN NUMBER)
63 RETURN NUMBER
64 IS
65 CURSOR c_parent(b_counter_id NUMBER) IS
66 SELECT COUNTER_GROUP_ID
67 FROM CS_COUNTERS
68 WHERE COUNTER_ID = b_counter_id;
69 l_parent_id NUMBER;
70 BEGIN
71 OPEN c_parent(p_counter_id);
72 FETCH c_parent INTO l_parent_id;
73 IF c_parent%NOTFOUND THEN
74 CLOSE c_parent;
75 RETURN NULL;
76 END IF;
77 CLOSE c_parent;
78 RETURN l_parent_id;
79 EXCEPTION
80 WHEN OTHERS THEN
81 jtm_message_log_pkg.Log_Msg
82 ( p_counter_id
83 , g_ctr_grps_table_name
84 , 'Exception happened in Get_Parent_Ctr_Grp_Id for ' || p_counter_id
85 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
86 RETURN NULL;
87 END Get_Parent_Ctr_Grp_Id;
88
89 FUNCTION Get_Parent_Counter_Id(p_ctr_prop_id IN NUMBER)
90 RETURN NUMBER
91 IS
92 CURSOR c_parent(b_ctr_prop_id NUMBER) IS
93 SELECT COUNTER_ID
94 FROM CS_COUNTER_PROPERTIES
95 WHERE COUNTER_PROPERTY_ID = b_ctr_prop_id;
96 l_parent_id NUMBER;
97 BEGIN
98 OPEN c_parent(p_ctr_prop_id);
99 FETCH c_parent INTO l_parent_id;
100 IF c_parent%NOTFOUND THEN
101 CLOSE c_parent;
102 RETURN NULL;
103 END IF;
104 CLOSE c_parent;
105 RETURN l_parent_id;
106 EXCEPTION
107 WHEN OTHERS THEN
108 jtm_message_log_pkg.Log_Msg
109 ( p_ctr_prop_id
110 , g_ctr_grps_table_name
111 , 'Exception happened in Get_Parent_Counter_Id for ' || p_ctr_prop_id
112 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
113 RETURN NULL;
114 END Get_Parent_Counter_Id;
115
116 /***************************************
117 Procedures for CS_COUNTER_PROPERTIES hooks
118 ***************************************/
119
120 /* private procedure that replicates given counter value related data for resource
121 * reading is always controlled by counter_value not counter_prop
122 */
123 PROCEDURE INSERT_COUNTER_PROP_ACC_RECORD ( p_counter_property_id IN NUMBER,
124 p_resource_id IN NUMBER
125 )
126 IS
127 BEGIN
128 -- add debug info later.
129 JTM_HOOK_UTIL_PKG.Insert_Acc
130 ( P_PUBLICATION_ITEM_NAMES => g_ctr_props_pubi_name
131 ,P_ACC_TABLE_NAME => g_ctr_props_acc_table_name
132 ,P_PK1_NAME => g_ctr_props_pk1_name
133 ,P_PK1_NUM_VALUE => p_counter_property_id
134 ,P_RESOURCE_ID => p_resource_id
135 );
136 END INSERT_COUNTER_PROP_ACC_RECORD;
137
138 PROCEDURE UPDATE_COUNTER_PROP_ACC_RECORD (
139 p_resource_id IN NUMBER,
140 p_acc_id IN NUMBER
141 )
142 IS
143 BEGIN
144 -- add debug info later.
145 JTM_HOOK_UTIL_PKG.UPDATE_Acc
146 ( P_PUBLICATION_ITEM_NAMES => g_ctr_props_pubi_name
147 ,P_ACC_TABLE_NAME => g_ctr_props_acc_table_name
148 ,P_RESOURCE_ID => p_resource_id
149 ,P_ACCESS_ID => p_acc_id
150 );
151 END UPDATE_COUNTER_PROP_ACC_RECORD;
152
153 PROCEDURE DELETE_COUNTER_PROP_ACC_RECORD ( p_counter_property_id IN NUMBER,
154 p_resource_id IN NUMBER
155 )
156 IS
157 BEGIN
158 -- add debug info later.
159 JTM_HOOK_UTIL_PKG.DELETE_Acc
160 ( P_PUBLICATION_ITEM_NAMES => g_ctr_props_pubi_name
161 ,P_ACC_TABLE_NAME => g_ctr_props_acc_table_name
162 ,P_PK1_NAME => g_ctr_props_pk1_name
163 ,P_PK1_NUM_VALUE => p_counter_property_id
164 ,P_RESOURCE_ID => p_resource_id
165 );
166 END DELETE_COUNTER_PROP_ACC_RECORD;
167
168 /***************************************
169 Procedures for CS_COUNTERS hooks
170 ***************************************/
171 /* private procedure that replicates given counters related data for resource */
172 PROCEDURE INSERT_COUNTER_ACC_RECORD ( p_counter_id IN NUMBER,
173 p_resource_id IN NUMBER
174 )
175 IS
176 CURSOR c_counter_property (b_counter_id NUMBER) IS
177 SELECT COUNTER_PROPERTY_ID
178 FROM CS_COUNTER_PROPERTIES
179 WHERE COUNTER_ID = b_counter_id;
180 r_counter_property_id c_counter_property%ROWTYPE;
181 l_ins_ctr_val_success BOOLEAN := TRUE;
182 BEGIN
183 -- add debug info later.
184 JTM_HOOK_UTIL_PKG.Insert_Acc
185 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
186 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
187 ,P_PK1_NAME => g_counters_pk1_name
188 ,P_PK1_NUM_VALUE => p_counter_id
189 ,P_RESOURCE_ID => p_resource_id
190 );
191
192 -- add debug info later
193 l_ins_ctr_val_success := CSL_CS_COUNTER_VALS_ACC_PKG.POST_INSERT_PARENT( p_counter_id, p_resource_id );
194 IF NOT l_ins_ctr_val_success THEN
195 RETURN;
196 END IF;
197
198 FOR r_counter_property_id IN c_counter_property( p_counter_id ) LOOP
199 -- add debug info later
200 INSERT_COUNTER_PROP_ACC_RECORD( r_counter_property_id.COUNTER_PROPERTY_ID, p_resource_id );
201 END LOOP;
202 END INSERT_COUNTER_ACC_RECORD;
203
204 PROCEDURE UPDATE_COUNTER_ACC_RECORD (
205 p_resource_id IN NUMBER,
206 p_acc_id IN NUMBER
207 )
208 IS
209 BEGIN
210 -- add debug info later.
211 JTM_HOOK_UTIL_PKG.Update_Acc
212 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
213 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
214 ,P_RESOURCE_ID => p_resource_id
215 ,P_ACCESS_ID => p_acc_id
216 );
217
218 END UPDATE_COUNTER_ACC_RECORD;
219
220 PROCEDURE DELETE_COUNTER_ACC_RECORD ( p_counter_id IN NUMBER,
221 p_resource_id IN NUMBER
222 )
223 IS
224 CURSOR c_counter_property (b_counter_id NUMBER) IS
225 SELECT COUNTER_PROPERTY_ID
226 FROM CS_COUNTER_PROPERTIES
227 WHERE COUNTER_ID = b_counter_id;
228 r_counter_property_id c_counter_property%ROWTYPE;
229 l_del_ctr_val_success BOOLEAN := TRUE;
230 BEGIN
231 -- add debug info later.
232 JTM_HOOK_UTIL_PKG.Delete_Acc
233 ( P_PUBLICATION_ITEM_NAMES => g_counters_pubi_name
234 ,P_ACC_TABLE_NAME => g_counters_acc_table_name
235 ,P_PK1_NAME => g_counters_pk1_name
236 ,P_PK1_NUM_VALUE => p_counter_id
237 ,P_RESOURCE_ID => p_resource_id
238 );
239
240 -- add debug info later
241 l_del_ctr_val_success := CSL_CS_COUNTER_VALS_ACC_PKG.PRE_DELETE_PARENT( p_counter_id, p_resource_id );
242 IF NOT l_del_ctr_val_success THEN
243 RETURN;
244 END IF;
245 FOR r_counter_property_id IN c_counter_property( p_counter_id ) LOOP
246 -- add debug info later
247 DELETE_COUNTER_PROP_ACC_RECORD( r_counter_property_id.COUNTER_PROPERTY_ID, p_resource_id );
248 END LOOP;
249 END DELETE_COUNTER_ACC_RECORD;
250
251 /***************************************
252 Procedures for CS_COUNTER_GROUPS hooks
253 ***************************************/
254 /* private procedure that replicates given counter group related data for resource */
255 PROCEDURE INSERT_CTR_GRP_ACC_RECORD ( p_counter_group_id IN NUMBER,
256 p_resource_id IN NUMBER
257 )
258 IS
259 CURSOR c_counter (b_counter_group_id NUMBER) IS
260 SELECT COUNTER_ID
261 FROM CS_COUNTERS
262 WHERE COUNTER_GROUP_ID = b_counter_group_id;
263 BEGIN
264 -- add debug info later.
265 JTM_HOOK_UTIL_PKG.Insert_Acc
266 ( P_PUBLICATION_ITEM_NAMES => g_ctr_grps_pubi_name
267 ,P_ACC_TABLE_NAME => g_ctr_grps_acc_table_name
268 ,P_PK1_NAME => g_ctr_grps_pk1_name
269 ,P_PK1_NUM_VALUE => p_counter_group_id
270 ,P_RESOURCE_ID => p_resource_id
271 );
272
273 FOR r_counter_id IN c_counter( p_counter_group_id ) LOOP
274 -- add debug info later
275 INSERT_COUNTER_ACC_RECORD( r_counter_id.COUNTER_ID, p_resource_id );
276 END LOOP;
277 END INSERT_CTR_GRP_ACC_RECORD;
278
279 /* UPDATE_CTR_GRP_ACC_RECORD is not recurisive hierarchically */
280 PROCEDURE UPDATE_CTR_GRP_ACC_RECORD (
281 p_resource_id IN NUMBER,
282 p_acc_id IN NUMBER
283 )
284 IS
285 BEGIN
286 -- add debug info later.
287 JTM_HOOK_UTIL_PKG.Update_Acc
288 ( P_PUBLICATION_ITEM_NAMES => g_ctr_grps_pubi_name
289 ,P_ACC_TABLE_NAME => g_ctr_grps_acc_table_name
290 ,P_RESOURCE_ID => p_resource_id
291 ,P_ACCESS_ID => p_acc_id
292 );
293 END UPDATE_CTR_GRP_ACC_RECORD;
294
295 /* recursive delete */
296 PROCEDURE DELETE_CTR_GRP_ACC_RECORD ( p_counter_group_id IN NUMBER,
297 p_resource_id IN NUMBER
298 )
299 IS
300 CURSOR c_counter (b_counter_group_id NUMBER) IS
301 SELECT COUNTER_ID
302 FROM CS_COUNTERS
303 WHERE COUNTER_GROUP_ID = b_counter_group_id;
304 BEGIN
305 -- add debug info later.
306 JTM_HOOK_UTIL_PKG.Delete_Acc
307 ( P_PUBLICATION_ITEM_NAMES => g_ctr_grps_pubi_name
308 ,P_ACC_TABLE_NAME => g_ctr_grps_acc_table_name
309 ,P_PK1_NAME => g_ctr_grps_pk1_name
310 ,P_PK1_NUM_VALUE => p_counter_group_id
311 ,P_RESOURCE_ID => p_resource_id
312 );
313
314 FOR r_counter_id IN c_counter( p_counter_group_id ) LOOP
315 -- add debug info later
316 DELETE_COUNTER_ACC_RECORD( r_counter_id.COUNTER_ID, p_resource_id );
317 END LOOP;
318 END DELETE_CTR_GRP_ACC_RECORD;
319
320
321 /***
322 Function that checks if a counter group record should be
323 replicated. Returns TRUE if it should be replicated
324 It checks if this is a valid counter_group_id.
325 Then check if counter_group_id is associated with a customer_product_id replicatable.
326 ***/
327 FUNCTION Replicate_Record
328 ( p_counter_group_id NUMBER
329 )
330 RETURN BOOLEAN
331 IS
332 CURSOR c_counter_group ( b_counter_group_id NUMBER) IS
333 SELECT null
334 FROM CS_COUNTER_GROUPS
335 WHERE COUNTER_GROUP_ID = b_counter_group_id;
336 r_counter_group c_counter_group%ROWTYPE;
337 BEGIN
338 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
339 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
340 jtm_message_log_pkg.Log_Msg
341 ( p_counter_group_id
342 , g_ctr_grps_table_name
343 , 'Entering Replicate_Record'
344 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
345 END IF;
346 /* check if this is a valid counter_group_id. */
347 OPEN c_counter_group(p_counter_group_id);
348 FETCH c_counter_group INTO r_counter_group;
349 IF c_counter_group%NOTFOUND THEN
350 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
351 jtm_message_log_pkg.Log_Msg
352 ( p_counter_group_id
353 , g_ctr_grps_table_name
354 , 'Replicate_Record error: Could not find counter_group_id ' || p_counter_group_id
355 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
356 END IF;
357 CLOSE c_counter_group;
358 RETURN FALSE;
359 END IF;
360 CLOSE c_counter_group;
361
362 RETURN TRUE;
363 EXCEPTION
364 WHEN OTHERS THEN
365 jtm_message_log_pkg.Log_Msg
366 ( p_counter_group_id
367 , g_ctr_grps_table_name
368 , 'Exception happened in for counter_group_id ' || p_counter_group_id
369 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
370 RETURN FALSE;
371 END REPLICATE_RECORD;
372
373
374 /***
375 Public procedure called after a parent record is inserted.
376 The parent of counter group is CSI_ITEM_INSTANCE record.
377 Called by CSL_CSI_ITEM_INSTANCE_ACC_PKG
378 ***/
379 FUNCTION Post_Insert_Parent
380 ( p_item_instance_id IN NUMBER
381 ,p_resource_id IN NUMBER
382 )
383 RETURN BOOLEAN
384 IS
385 CURSOR c_counter_groups (b_item_instance_id NUMBER) IS
386 SELECT COUNTER_GROUP_ID
387 FROM CS_COUNTER_GROUPS
388 WHERE SOURCE_OBJECT_CODE = 'CP' AND SOURCE_OBJECT_ID = b_item_instance_id;
389 BEGIN
390 FOR r_counter_group_id IN c_counter_groups(p_item_instance_id)
391 LOOP
392 INSERT_CTR_GRP_ACC_RECORD(r_counter_group_id.COUNTER_GROUP_ID, p_resource_id);
393 END LOOP;
394 RETURN TRUE;
395 EXCEPTION
396 WHEN OTHERS THEN
397 RETURN FALSE;
398 END Post_Insert_Parent;
399
400 /***
401 Public procedure called after a parent record is updated
402 Check the new parent_id if it is in the ACC table.
403 Called by CSL_CSI_ITEM_INSTANCE_ACC_PKG
404 ***/
405 FUNCTION Post_Update_Parent
406 ( p_item_instance_id IN NUMBER
407 ,p_resource_id IN NUMBER
408 )
409 RETURN BOOLEAN
410 IS
411 BEGIN
412 RETURN TRUE;
413 END Post_Update_Parent;
414
415 /***
416 Public procedure that gets called before a parent record is deleted.
417 Called by CSL_CSI_ITEM_INSTANCE_ACC_PKG
418 ***/
419 FUNCTION Pre_Delete_Parent
420 ( p_item_instance_id IN NUMBER
421 ,p_resource_id IN NUMBER
422 )
423 RETURN BOOLEAN
424 IS
425 CURSOR c_counter_groups (b_item_instance_id NUMBER) IS
426 SELECT COUNTER_GROUP_ID
427 FROM CS_COUNTER_GROUPS
428 WHERE SOURCE_OBJECT_CODE = 'CP' AND SOURCE_OBJECT_ID = b_item_instance_id;
429 BEGIN
430 FOR r_counter_group_id IN c_counter_groups(p_item_instance_id) LOOP
431 DELETE_CTR_GRP_ACC_RECORD(r_counter_group_id.COUNTER_GROUP_ID, p_resource_id);
432 END LOOP;
433 RETURN TRUE;
434 END Pre_Delete_Parent;
435
436
437 /***
438 *** The following Functions Called by VUHK of CS_COUNTERS_PUB
439 ***/
440
441 /* Called before counter group Insert */
442 PROCEDURE PRE_INSERT_COUNTER_GROUP ( x_return_status out NOCOPY varchar2)
443 IS
444 BEGIN
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446 END;
447
448 /* Called after counter group Insert */
449 /* Check if the counter group is associated with a replicable item instance */
450 PROCEDURE POST_INSERT_COUNTER_GROUP ( p_api_version IN NUMBER
451 , P_Init_Msg_List IN VARCHAR2
452 , P_Commit IN VARCHAR2
453 , X_Return_Status OUT NOCOPY VARCHAR2
454 , X_Msg_Count OUT NOCOPY NUMBER
455 , X_Msg_Data OUT NOCOPY VARCHAR2
456 , p_source_object_cd IN VARCHAR2
457 , p_source_object_id IN NUMBER
458 , x_ctr_grp_id IN NUMBER
459 , x_object_version_number OUT NOCOPY NUMBER)
460 IS
461 l_tab_resource_id dbms_sql.Number_Table;
462 l_tab_access_id dbms_sql.Number_Table;
463 l_item_instance_id NUMBER;
464 BEGIN
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466 l_item_instance_id := Get_Parent_Item_Instance_Id(x_ctr_grp_id);
467
468 IF l_item_instance_id IS NULL
469 THEN
470 RETURN;
471 END IF;
472
473 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
474 ( p_acc_table_name => g_item_insts_acc_table_name
475 ,p_pk1_name => g_item_insts_pk1_name
476 ,p_pk1_num_value => l_item_instance_id
477 ,l_tab_resource_id => l_tab_resource_id
478 ,l_tab_access_id => l_tab_access_id
479 );
480
481 IF l_tab_resource_id.COUNT > 0 THEN
482 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
483 INSERT_COUNTER_PROP_ACC_RECORD
484 (x_ctr_grp_id
485 ,l_tab_resource_id(i)
486 );
487 END LOOP;
488 END IF;
489 END POST_INSERT_COUNTER_GROUP;
490
491 /* Called before counter group Update */
492 PROCEDURE PRE_UPDATE_COUNTER_GROUP ( x_return_status out NOCOPY varchar2)
493 IS
494 BEGIN
495 x_return_status := FND_API.G_RET_STS_SUCCESS;
496 END PRE_UPDATE_COUNTER_GROUP;
497
498 /* Called after counter group Update */
499 PROCEDURE POST_UPDATE_COUNTER_GROUP( P_Api_Version IN NUMBER
500 , P_Init_Msg_List IN VARCHAR2
501 , P_Commit IN VARCHAR2
502 , X_Return_Status OUT NOCOPY VARCHAR2
503 , X_Msg_Count OUT NOCOPY NUMBER
504 , X_Msg_Data OUT NOCOPY VARCHAR2
505 , p_ctr_grp_id IN NUMBER
506 , p_object_version_number IN NUMBER
507 , p_cascade_upd_to_instances IN VARCHAR2
508 , x_object_version_number OUT NOCOPY NUMBER )
509 IS
510 l_tab_resource_id dbms_sql.Number_Table;
511 l_tab_access_id dbms_sql.Number_Table;
512 BEGIN
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514
515 IF p_ctr_grp_id IS NULL
516 THEN
517 RETURN;
518 END IF;
519
520 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
521 ( p_acc_table_name => g_ctr_grps_acc_table_name
522 ,p_pk1_name => g_ctr_grps_pk1_name
523 ,p_pk1_num_value => p_ctr_grp_id
524 ,l_tab_resource_id =>l_tab_resource_id
525 ,l_tab_access_id => l_tab_access_id
526 );
527 IF l_tab_resource_id.COUNT > 0 THEN
528 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
529 UPDATE_COUNTER_PROP_ACC_RECORD
530 (
531 l_tab_resource_id(i)
532 ,l_tab_access_id(i)
533 );
534 END LOOP;
535 END IF;
536 END POST_UPDATE_COUNTER_GROUP;
537
538 /* Called before counter group Delete */
539 PROCEDURE PRE_DELETE_COUNTER_GROUP ( x_return_status out NOCOPY varchar2)
540 IS
541 BEGIN
542 x_return_status := FND_API.G_RET_STS_SUCCESS;
543 END PRE_DELETE_COUNTER_GROUP;
544
545 /* Called after counter group Delete */
546 PROCEDURE POST_DELETE_COUNTER_GROUP (
547 p_counter_group_id IN NUMBER
548 ,x_return_status out NOCOPY varchar2)
549 IS
550 l_tab_resource_id dbms_sql.Number_Table;
551 l_tab_access_id dbms_sql.Number_Table;
552 BEGIN
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554
555 IF p_counter_group_id IS NULL
556 THEN
557 RETURN;
558 END IF;
559
560 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
561 ( p_acc_table_name => g_ctr_grps_acc_table_name
562 ,p_pk1_name => g_ctr_grps_pk1_name
563 ,p_pk1_num_value => p_counter_group_id
564 ,l_tab_resource_id => l_tab_resource_id
565 ,l_tab_access_id => l_tab_access_id
566 );
567 IF l_tab_resource_id.COUNT > 0 THEN
568 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
569 DELETE_CTR_GRP_ACC_RECORD
570 ( p_counter_group_id
571 ,l_tab_access_id(i)
572 );
573 END LOOP;
574 END IF;
575 END POST_DELETE_COUNTER_GROUP;
576
577 /*** counters ************/
578 /* Called before counter Insert */
579 PROCEDURE PRE_INSERT_COUNTER ( x_return_status out NOCOPY varchar2)
580 IS
581 BEGIN
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583 END PRE_INSERT_COUNTER;
584
585 /* Called after counter Insert */
586 PROCEDURE POST_INSERT_COUNTER ( p_api_version IN NUMBER
587 , P_Init_Msg_List IN VARCHAR2
588 , P_Commit IN VARCHAR2
589 , X_Return_Status OUT NOCOPY VARCHAR2
590 , X_Msg_Count OUT NOCOPY NUMBER
591 , X_Msg_Data OUT NOCOPY VARCHAR2
592 , x_ctr_id IN NUMBER
593 , x_object_version_number OUT NOCOPY NUMBER)
594 IS
595 l_tab_resource_id dbms_sql.Number_Table;
596 l_tab_access_id dbms_sql.Number_Table;
597 l_counter_group_id NUMBER;
598 BEGIN
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600 l_counter_group_id := Get_Parent_Ctr_Grp_Id(x_ctr_id);
601
602 IF l_counter_group_id IS NULL
603 THEN
604 RETURN;
605 END IF;
606 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
607 ( p_acc_table_name => g_ctr_grps_acc_table_name
608 ,p_pk1_name => g_ctr_grps_pk1_name
609 ,p_pk1_num_value => l_counter_group_id
610 ,l_tab_resource_id => l_tab_resource_id
611 ,l_tab_access_id => l_tab_access_id
612 );
613 IF l_tab_resource_id.COUNT > 0 THEN
614 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
615 INSERT_COUNTER_ACC_RECORD
616 (x_ctr_id
617 ,l_tab_resource_id(i)
618 );
619 END LOOP;
620 END IF;
621 END POST_INSERT_COUNTER;
622
623 /* Called before counter Update */
624 PROCEDURE PRE_UPDATE_COUNTER ( x_return_status out NOCOPY varchar2)
625 IS
626 BEGIN
627 x_return_status := FND_API.G_RET_STS_SUCCESS;
628 END PRE_UPDATE_COUNTER;
629
630 /* Called after counter Update */
631 PROCEDURE POST_UPDATE_COUNTER ( P_Api_Version IN NUMBER
632 , P_Init_Msg_List IN VARCHAR2
633 , P_Commit IN VARCHAR2
634 , X_Return_Status OUT NOCOPY VARCHAR2
635 , X_Msg_Count OUT NOCOPY NUMBER
636 , X_Msg_Data OUT NOCOPY VARCHAR2
637 , p_ctr_id IN NUMBER
638 , p_object_version_number IN NUMBER
639 , p_cascade_upd_to_instances IN VARCHAR2
640 , x_object_version_number OUT NOCOPY NUMBER )
641 IS
642 l_tab_resource_id dbms_sql.Number_Table;
643 l_tab_access_id dbms_sql.Number_Table;
644 BEGIN
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
648 ( p_acc_table_name => g_counters_acc_table_name
649 ,p_pk1_name => g_counters_pk1_name
650 ,p_pk1_num_value => p_ctr_id
651 ,l_tab_resource_id => l_tab_resource_id
652 ,l_tab_access_id => l_tab_access_id
653 );
654 IF l_tab_resource_id.COUNT > 0 THEN
655 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
656 UPDATE_COUNTER_ACC_RECORD
657 (l_tab_resource_id(i)
658 ,l_tab_access_id(i)
659 );
660 END LOOP;
661 END IF;
662 END POST_UPDATE_COUNTER;
663
664 /* Called before counter Delete */
665 PROCEDURE PRE_DELETE_COUNTER ( P_Api_Version IN NUMBER
666 , P_Init_Msg_List IN VARCHAR2
667 , P_Commit IN VARCHAR2
668 , X_Return_Status OUT NOCOPY VARCHAR2
669 , X_Msg_Count OUT NOCOPY NUMBER
670 , X_Msg_Data OUT NOCOPY VARCHAR2
671 , p_ctr_id IN NUMBER )
672 IS
673 BEGIN
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675 END PRE_DELETE_COUNTER;
676
677 /* Called after counter Delete */
678 PROCEDURE POST_DELETE_COUNTER (
679 p_counter_id IN NUMBER
680 ,x_return_status out NOCOPY varchar2)
681 IS
682 l_tab_resource_id dbms_sql.Number_Table;
683 l_tab_access_id dbms_sql.Number_Table;
684 BEGIN
685 x_return_status := FND_API.G_RET_STS_SUCCESS;
686
687 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
688 ( p_acc_table_name => g_counters_acc_table_name
689 ,p_pk1_name => g_counters_pk1_name
690 ,p_pk1_num_value => p_counter_id
691 ,l_tab_resource_id => l_tab_resource_id
692 ,l_tab_access_id => l_tab_access_id
693 );
694 IF l_tab_resource_id.COUNT > 0 THEN
695 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
696 DELETE_COUNTER_ACC_RECORD
697 (p_counter_id
698 ,l_tab_access_id(i)
699 );
700 END LOOP;
701 END IF;
702 END POST_DELETE_COUNTER;
703
704
705 /* Called before counter property Insert */
706 PROCEDURE PRE_INSERT_COUNTER_PROPERTY ( x_return_status out NOCOPY varchar2)
707 IS
708 BEGIN
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710 END PRE_INSERT_COUNTER_PROPERTY;
711
712 /* Called after counter property Insert */
713 PROCEDURE POST_INSERT_COUNTER_PROPERTY ( P_Api_Version IN NUMBER
714 , P_Init_Msg_List IN VARCHAR2
715 , P_Commit IN VARCHAR2
716 , X_Return_Status OUT NOCOPY VARCHAR2
717 , X_Msg_Count OUT NOCOPY NUMBER
718 , X_Msg_Data OUT NOCOPY VARCHAR2
719 , x_ctr_prop_id IN NUMBER
720 , x_object_version_number OUT NOCOPY NUMBER )
721 IS
722 l_tab_resource_id dbms_sql.Number_Table;
723 l_tab_access_id dbms_sql.Number_Table;
724 l_counter_id NUMBER;
725 BEGIN
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727
728 l_counter_id := Get_Parent_Counter_Id(x_ctr_prop_id);
729
730 IF l_counter_id IS NULL
731 THEN
732 RETURN;
733 END IF;
734
735 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
736 ( p_acc_table_name => g_counters_acc_table_name
737 ,p_pk1_name => g_counters_pk1_name
738 ,p_pk1_num_value => l_counter_id
739 ,l_tab_resource_id => l_tab_resource_id
740 ,l_tab_access_id => l_tab_access_id
741 );
742 IF l_tab_resource_id.COUNT > 0 THEN
743 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
744 INSERT_COUNTER_PROP_ACC_RECORD
745 (x_ctr_prop_id
746 ,l_tab_resource_id(i)
747 );
748 END LOOP;
749 END IF;
750 END POST_INSERT_COUNTER_PROPERTY;
751
752 /* Called before counter property Update */
753 PROCEDURE PRE_UPDATE_COUNTER_PROPERTY ( x_return_status out NOCOPY varchar2)
754 IS
755 BEGIN
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757 END PRE_UPDATE_COUNTER_PROPERTY;
758
759 /* Called after counter property Update */
760 PROCEDURE POST_UPDATE_COUNTER_PROPERTY ( P_Api_Version IN NUMBER
761 , P_Init_Msg_List IN VARCHAR2
762 , P_Commit IN VARCHAR2
763 , X_Return_Status OUT NOCOPY VARCHAR2
764 , X_Msg_Count OUT NOCOPY NUMBER
765 , X_Msg_Data OUT NOCOPY VARCHAR2
766 , p_ctr_prop_id IN NUMBER
767 , p_object_version_number IN NUMBER
768 , p_cascade_upd_to_instances IN VARCHAR2
769 , x_object_version_number OUT NOCOPY NUMBER )
770 IS
771 l_tab_resource_id dbms_sql.Number_Table;
772 l_tab_access_id dbms_sql.Number_Table;
773 BEGIN
774 x_return_status := FND_API.G_RET_STS_SUCCESS;
775
776 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
777 ( p_acc_table_name => g_ctr_props_acc_table_name
778 ,p_pk1_name => g_ctr_props_pk1_name
779 ,p_pk1_num_value => p_ctr_prop_id
780 ,l_tab_resource_id => l_tab_resource_id
781 ,l_tab_access_id => l_tab_access_id
782 );
783 IF l_tab_resource_id.COUNT > 0 THEN
784 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
785 UPDATE_COUNTER_PROP_ACC_RECORD
786 (l_tab_resource_id(i)
787 ,l_tab_access_id(i)
788 );
789 END LOOP;
790 END IF;
791 END POST_UPDATE_COUNTER_PROPERTY;
792
793 /* Called before counter property Delete */
794 PROCEDURE PRE_DELETE_COUNTER_PROPERTY ( P_Api_Version IN NUMBER
795 , P_Init_Msg_List IN VARCHAR2
796 , P_Commit IN VARCHAR2
797 , X_Return_Status OUT NOCOPY VARCHAR2
798 , X_Msg_Count OUT NOCOPY NUMBER
799 , X_Msg_Data OUT NOCOPY VARCHAR2
800 , p_ctr_prop_id IN NUMBER )
801 IS
802 BEGIN
803 x_return_status := FND_API.G_RET_STS_SUCCESS;
804 END PRE_DELETE_COUNTER_PROPERTY;
805
806 /* Called after counter property Delete */
807 PROCEDURE POST_DELETE_COUNTER_PROPERTY (
808 p_counter_prop_id IN NUMBER
809 ,x_return_status out NOCOPY varchar2)
810 IS
811 l_tab_resource_id dbms_sql.Number_Table;
812 l_tab_access_id dbms_sql.Number_Table;
813 BEGIN
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
817 ( p_acc_table_name => g_ctr_props_acc_table_name
818 ,p_pk1_name => g_ctr_props_pk1_name
819 ,p_pk1_num_value => p_counter_prop_id
820 ,l_tab_resource_id => l_tab_resource_id
821 ,l_tab_access_id => l_tab_access_id
822 );
823 IF l_tab_resource_id.COUNT > 0 THEN
824 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
825 DELETE_COUNTER_PROP_ACC_RECORD
826 (p_counter_prop_id
827 ,l_tab_access_id(i)
828 );
829 END LOOP;
830 END IF;
831 END POST_DELETE_COUNTER_PROPERTY;
832
833 END CSL_CS_COUNTERS_ACC_PKG;
[Home] [Help]