[Home] [Help]
PACKAGE BODY: APPS.CSL_CSI_ITEM_INSTANCES_ACC_PKG
Source
1 PACKAGE BODY CSL_CSI_ITEM_INSTANCES_ACC_PKG AS
2 /* $Header: csliiacb.pls 120.0 2005/05/25 11:05:37 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_INSTANCES_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CSI_ITEM_INSTANCES');
8 g_table_name CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
10 g_debug_level NUMBER; -- debug level
11 g_resource_id_list dbms_sql.Number_Table; -- list of resource to which an item instance should be replicated
12
13 -- ER 3168446
14 g_ib_count NUMBER := 0;
15 g_parent_instance_id NUMBER;
16
17 /*** Function that checks if item instance record should be replicated.
18 Returns TRUE if it should ***/
19 FUNCTION Replicate_Record
20 ( p_instance_id NUMBER
21 , p_resource_id NUMBER
22 )
23 RETURN BOOLEAN
24 IS
25 CURSOR c_item_instance (b_instance_id NUMBER) IS
26 SELECT null
27 FROM CSI_ITEM_INSTANCES
28 WHERE instance_id = b_instance_id;
29 r_item_instance c_item_instance%ROWTYPE;
30 BEGIN
31 /*** get debug level ***/
32 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
33
34 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
35 jtm_message_log_pkg.Log_Msg
36 ( p_instance_id
37 , g_table_name
38 , 'Entering Replicate_Record'
39 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
40 END IF;
41
42 /*** is resource a mobile user? ***/
43 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
44 /*** No -> exit ***/
45 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
46 jtm_message_log_pkg.Log_Msg
47 ( p_instance_id
48 , g_table_name
49 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
50 'Resource_id ' || p_resource_id || ' is not a mobile user.'
51 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
52 END IF;
53
54 RETURN FALSE;
55 END IF;
56
57 /*** check if instance record exists ***/
58 OPEN c_item_instance( p_instance_id );
59 FETCH c_item_instance INTO r_item_instance;
60 IF c_item_instance%NOTFOUND THEN
61 /*** could not find item instance record -> exit ***/
62 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
63 jtm_message_log_pkg.Log_Msg
64 ( p_instance_id
65 , g_table_name
66 , 'Replicate_Record error: Could not find instance_id ' || p_instance_id
67 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
68 END IF;
69
70 CLOSE c_item_instance;
71 RETURN FALSE;
72 END IF;
73 CLOSE c_item_instance;
74
75 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
76 jtm_message_log_pkg.Log_Msg
77 ( p_instance_id
78 , g_table_name
79 , 'Replicate_Record returned TRUE'
80 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
81 END IF;
82
83 /** Record matched criteria -> return true ***/
84 RETURN TRUE;
85 END Replicate_Record;
86
87
88 /*** Private procedure that replicates extended item attributes
89 for a given item instance***/
90 PROCEDURE CON_ITEM_ATTR( p_status OUT NOCOPY VARCHAR2,
91 p_message OUT NOCOPY VARCHAR2)
92 IS
93
94 /*** get the last run date of the concurent program ***/
95 CURSOR c_LastRundate IS
96 select LAST_RUN_DATE
97 from JTM_CON_REQUEST_DATA
98 where package_name = 'CSL_CSI_ITEM_INSTANCES_ACC_PKG'
99 AND procedure_name = 'CON_ITEM_ATTR';
100 r_LastRundate c_LastRundate%ROWTYPE;
101
102 CURSOR c_insert_diffs(b_last_run_date date) IS
103 SELECT ATTRIBUTE_VALUE_ID, RESOURCE_ID
104 FROM CSI_IEA_VALUES civ, CSI_I_EXTENDED_ATTRIBS ciea, csl_csi_item_instances_acc acc
105 WHERE civ.attribute_id = ciea.attribute_id
106 AND civ.instance_id = acc.instance_id
107 AND NVL(ciea.active_start_date, sysdate) <= sysdate
108 AND NVL(ciea.active_end_date, sysdate) >= sysdate
109 AND civ.ATTRIBUTE_VALUE_ID NOT IN (SELECT ATTRIBUTE_VALUE_ID from CSL_CSI_ITEM_ATTR_ACC)
110 AND civ.last_update_date > b_last_run_date;
111
112 r_insert_diffs c_insert_diffs%ROWTYPE;
113
114 CURSOR c_updates(b_last_run_date date) IS
115 SELECT acc.ACCESS_ID, acc.RESOURCE_ID
116 FROM CSI_IEA_VALUES civ, CSI_I_EXTENDED_ATTRIBS ciea,
117 csl_csi_item_instances_acc csiacc, CSL_CSI_ITEM_ATTR_ACC acc
118 WHERE civ.attribute_id = ciea.attribute_id
119 AND civ.instance_id = csiacc.instance_id
120 AND civ.ATTRIBUTE_VALUE_ID = acc.ATTRIBUTE_VALUE_ID
121 AND NVL(ciea.active_start_date, sysdate) <= sysdate
122 AND NVL(ciea.active_end_date, sysdate) >= sysdate
123 AND civ.last_update_date > b_last_run_date;
124 r_updates c_updates%ROWTYPE;
125
126 /*
127 CURSOR c_end_dates(b_last_run_date date) IS
128 SELECT ATTRIBUTE_VALUE_ID, RESOURCE_ID
129 FROM CSI_IEA_VALUES civ, CSI_I_EXTENDED_ATTRIBS ciea, csl_csi_item_instances_acc acc
130 WHERE civ.attribute_id = ciea.attribute_id
131 AND civ.instance_id = acc.instance_id
132 AND NVL(ciea.active_start_date, sysdate) <= sysdate
133 AND NVL(ciea.active_end_date, sysdate) < sysdate
134 AND civ.ATTRIBUTE_VALUE_ID IN (SELECT ATTRIBUTE_VALUE_ID from CSL_CSI_ITEM_ATTR_ACC)
135 AND civ.last_update_date > b_last_run_date;
136 r_end_dates c_end_dates%ROWTYPE;
137
138 CURSOR c_deletes(b_last_run_date date) IS
139 SELECT ATTRIBUTE_VALUE_ID, RESOURCE_ID
140 from CSL_CSI_ITEM_ATTR_ACC
141 where ATTRIBUTE_VALUE_ID NOT IN (SELECT ATTRIBUTE_VALUE_ID from CSI_IEA_VALUES)
142 */
143
144 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
145 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CSI_ITEM_ATTR');
146 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR_ACC';
147 l_pk1_name CONSTANT VARCHAR2(30) := 'ATTRIBUTE_VALUE_ID';
148 l_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR';
149
150 l_current_run_date date;
151 BEGIN
152
153 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
154 jtm_message_log_pkg.Log_Msg
155 ( 0
156 , l_table_name
157 , 'Entering Insert_Item_Attr'
158 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
159 END IF;
160
161 /*** First retrieve last run date of the conccurent program ***/
162 OPEN c_LastRundate;
163 FETCH c_LastRundate INTO r_LastRundate;
164 CLOSE c_LastRundate;
165
166 l_current_run_date := SYSDATE;
167
168 --INSERT
169 FOR r_insert_diffs in c_insert_diffs(r_LastRundate.LAST_RUN_DATE)
170 LOOP
171
172 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
173 jtm_message_log_pkg.Log_Msg
174 ( 0
175 , l_table_name
176 , 'Inserting ACC record for resource_id = ' || r_insert_diffs.resource_id
177 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
178 END IF;
179
180 JTM_HOOK_UTIL_PKG.Insert_Acc
181 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
182 ,P_ACC_TABLE_NAME => l_acc_table_name
183 ,P_RESOURCE_ID => r_insert_diffs.resource_id
184 ,P_PK1_NAME => l_pk1_name
185 ,P_PK1_NUM_VALUE => r_insert_diffs.attribute_value_id
186 );
187
188 END LOOP;
189
190 --UPDATE
191 FOR r_updates in c_updates(r_LastRundate.LAST_RUN_DATE)
192 LOOP
193
194 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
195 jtm_message_log_pkg.Log_Msg
196 ( 0
197 , l_table_name
198 , 'Updating ACC record for resource_id = ' || r_updates.resource_id
199 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
200 END IF;
201
202 JTM_HOOK_UTIL_PKG.Update_Acc
203 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
204 ,P_ACC_TABLE_NAME => l_acc_table_name
205 ,P_RESOURCE_ID => r_updates.resource_id
206 ,P_ACCESS_ID => r_updates.access_id
207 );
208
209 END LOOP;
210
211 /*
212 --DELETE
213 FOR r_end_dates in c_end_dates(r_LastRundate.LAST_RUN_DATE)
214 LOOP
215
216 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
217 jtm_message_log_pkg.Log_Msg
218 ( 0
219 , l_table_name
220 , 'Updating ACC record for resource_id = ' || r_end_dates.resource_id
221 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
222 END IF;
223
224 JTM_HOOK_UTIL_PKG.Delete_Acc
225 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
226 ,P_ACC_TABLE_NAME => l_acc_table_name
227 ,P_RESOURCE_ID => r_end_dates.resource_id
228 ,P_PK1_NAME => l_pk1_name
229 ,P_PK1_NUM_VALUE => r_end_dates.attribute_value_id
230 );
231
232 END LOOP;
233 */
234
235 /*Update the last run date*/
236 UPDATE jtm_con_request_data SET last_run_date = l_current_run_date
237 WHERE package_name = 'CSL_CSI_ITEM_INSTANCES_ACC_PKG'
238 AND procedure_name = 'CON_ITEM_ATTR';
239
240 COMMIT;
241
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( 0
246 , l_table_name
247 , 'Leaving Con_item_Attr'
248 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
249 END IF;
250
251 p_status := 'FINE';
252 p_message := 'CSL_CSI_ITEM_INSTANCES_ACC_PKG.CON_ITEM_ATTR Executed successfully';
253
254 EXCEPTION
255
256 WHEN OTHERS THEN
257 p_status := 'ERROR';
258 p_message := 'Error in CSL_CSI_ITEM_INSTANCES_ACC_PKG.CON_ITEM_ATTR: ' || substr(SQLERRM, 1, 2000);
259 jtm_message_log_pkg.Log_Msg
260 (0,
261 'CSL_CSI_ITEM_ATTR_ACC',
262 'Exception occured in CON_ITEM_ATTR ',
263 JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
264
265 END;
266
267
268 --Bug 3724152
269 /*** Private procedure that replicates extended item attributes
270 for a given item instance***/
271 PROCEDURE Insert_Item_Attr
272 ( p_instance_id IN NUMBER
273 ,p_resource_id IN NUMBER
274 ,p_flow_type IN NUMBER )
275 IS
276 CURSOR c_item_attr( b_instance_id NUMBER ) IS
277 SELECT ATTRIBUTE_VALUE_ID
278 FROM CSI_IEA_VALUES civ, CSI_I_EXTENDED_ATTRIBS ciea
279 WHERE civ.attribute_id = ciea.attribute_id
280 AND NVL(ciea.active_start_date, sysdate) <= sysdate
281 AND NVL(ciea.active_end_date, sysdate) >= sysdate
282 AND civ.instance_id = b_instance_id;
283
284 r_item_attr c_item_attr%ROWTYPE;
285
286 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
287 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CSI_ITEM_ATTR');
288 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR_ACC';
289 l_pk1_name CONSTANT VARCHAR2(30) := 'ATTRIBUTE_VALUE_ID';
290 l_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR';
291
292 BEGIN
293
294 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
295 jtm_message_log_pkg.Log_Msg
296 ( p_instance_id
297 , g_table_name
298 , 'Entering Insert_Item_Attr'
299 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
300 END IF;
301
302 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
303 jtm_message_log_pkg.Log_Msg
304 ( p_instance_id
305 , g_table_name
306 , 'Inserting ACC record for resource_id = ' || p_resource_id
307 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
308 END IF;
309
310 FOR r_item_attr in c_item_attr(p_instance_id)
311 LOOP
312
313 JTM_HOOK_UTIL_PKG.Insert_Acc
314 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
315 ,P_ACC_TABLE_NAME => l_acc_table_name
316 ,P_RESOURCE_ID => p_resource_id
317 ,P_PK1_NAME => l_pk1_name
318 ,P_PK1_NUM_VALUE => r_item_attr.attribute_value_id
319 );
320
321 END LOOP;
322
323
324 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
325 jtm_message_log_pkg.Log_Msg
326 ( p_instance_id
327 , g_table_name
328 , 'Leaving Insert_ACC_Record'
329 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
330 END IF;
331
332 END;
333
334 --Bug 3724152
335 /*** Private procedure that deletes extended item attributes
336 for a given item instance***/
337 PROCEDURE Delete_Item_Attr
338 ( p_instance_id IN NUMBER
339 ,p_resource_id IN NUMBER
340 ,p_flow_type IN NUMBER )
341 IS
342 CURSOR c_item_attr( b_instance_id NUMBER ) IS
343 SELECT ATTRIBUTE_VALUE_ID
344 FROM CSI_IEA_VALUES
345 WHERE instance_id = b_instance_id;
346
347 r_item_attr c_item_attr%ROWTYPE;
348
349 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
350 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CSI_ITEM_ATTR');
351 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR_ACC';
352 l_pk1_name CONSTANT VARCHAR2(30) := 'ATTRIBUTE_VALUE_ID';
353 l_table_name CONSTANT VARCHAR2(30) := 'CSL_CSI_ITEM_ATTR';
354
355 BEGIN
356
357 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
358 jtm_message_log_pkg.Log_Msg
359 ( p_instance_id
360 , g_table_name
361 , 'Entering Delete_Item_Attr'
362 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
363 END IF;
364
365 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
366 jtm_message_log_pkg.Log_Msg
367 ( p_instance_id
368 , g_table_name
369 , 'Deleting ACC record for resource_id = ' || p_resource_id
370 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
371 END IF;
372
373 FOR r_item_attr in c_item_attr(p_instance_id)
374 LOOP
375
376 JTM_HOOK_UTIL_PKG.Delete_Acc
377 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
378 ,P_ACC_TABLE_NAME => l_acc_table_name
379 ,P_RESOURCE_ID => p_resource_id
380 ,P_PK1_NAME => l_pk1_name
381 ,P_PK1_NUM_VALUE => r_item_attr.attribute_value_id
382 );
383
384 END LOOP;
385
386 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
387 jtm_message_log_pkg.Log_Msg
388 ( p_instance_id
389 , g_table_name
390 , 'Leaving Delete_Item_Attr'
391 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
392 END IF;
393
394 END;
395
396 /*** Private procedure that replicates given item instance related data
397 for resource ***/
398 PROCEDURE Insert_ACC_Record
399 ( p_instance_id IN NUMBER
400 ,p_resource_id IN NUMBER
401 ,p_flow_type IN NUMBER )
402 IS
403 CURSOR c_ii( b_instance_id NUMBER ) IS
404 SELECT inventory_item_id
405 , inv_organization_id
406 , LOCATION_ID
407 , location_type_code
408 , INV_MASTER_ORGANIZATION_ID
409 FROM CSI_ITEM_INSTANCES
410 WHERE instance_id = b_instance_id;
411 r_ii c_ii%ROWTYPE;
412 l_org_id NUMBER;
413 l_return BOOLEAN;
414 BEGIN
415 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
416 jtm_message_log_pkg.Log_Msg
417 ( p_instance_id
418 , g_table_name
419 , 'Entering Insert_ACC_Record'
420 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
421 END IF;
422
423 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
424 jtm_message_log_pkg.Log_Msg
425 ( p_instance_id
426 , g_table_name
427 , 'Inserting ACC record for resource_id = ' || p_resource_id
428 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
429 END IF;
430
431 JTM_HOOK_UTIL_PKG.Insert_Acc
432 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
433 ,P_ACC_TABLE_NAME => g_acc_table_name
434 ,P_RESOURCE_ID => p_resource_id
435 ,P_PK1_NAME => g_pk1_name
436 ,P_PK1_NUM_VALUE => p_instance_id
437 );
438
439 -- ER 3168446
440 g_ib_count := g_ib_count + 1;
441
442 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
443 jtm_message_log_pkg.Log_Msg
444 ( p_instance_id
445 , g_table_name
446 , 'Calling Non-critical dependent hooks'
447 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
448 END IF;
449 -- NOTES/COUNTERS ( do not replicate notes/counters for history instances )
450 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
451 l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
452 ( P_SOURCE_OBJ_ID => p_instance_id
453 , P_SOURCE_OBJ_CODE => 'CP'
454 , P_RESOURCE_ID => p_resource_id );
455
456 -- COUNTERS
457 l_return := CSL_CS_COUNTERS_ACC_PKG.POST_INSERT_PARENT(
458 P_ITEM_INSTANCE_ID => p_instance_id
459 , P_RESOURCE_ID => p_resource_id );
460 END IF;--p_flow_type
461
462 -- SYSTEM ITEM
463 OPEN c_ii( p_instance_id );
464 FETCH c_ii INTO r_ii;
465 IF c_ii%FOUND THEN
466 -- l_org_id := NVL( r_ii.INV_ORGANIZATION_ID, TO_NUMBER(FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')));
467 l_org_id := r_ii.INV_MASTER_ORGANIZATION_ID;
468 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child(
469 p_inventory_item_id => r_ii.INVENTORY_ITEM_ID
470 , p_organization_id => l_org_id
471 , p_resource_id => p_resource_id );
472
473 IF r_ii.location_type_code = 'HZ_PARTY_SITES' THEN
474 CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE(
475 p_party_site_id => r_ii.LOCATION_ID
476 ,p_resource_id => p_resource_id);
477
478 ELSIF r_ii.location_type_code = 'HZ_LOCATIONS' THEN
479 CSL_HZ_LOCATIONS_ACC_PKG.INSERT_LOCATION(
480 p_location_id => r_ii.LOCATION_ID
481 ,p_resource_id => p_resource_id);
482 END IF;
483 END IF;
484 CLOSE c_ii;
485
486 --Bug 3724152
487 Insert_Item_Attr
488 ( p_instance_id => p_instance_id
489 ,p_resource_id => p_resource_id
490 ,p_flow_type => p_flow_type);
491
492 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
493 jtm_message_log_pkg.Log_Msg
494 ( p_instance_id
495 , g_table_name
496 , 'Leaving Insert_ACC_Record'
497 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
498 END IF;
499 END Insert_ACC_Record;
500
501 /*** Private procedure that re-sends given item instance to mobile ***/
502 PROCEDURE Update_ACC_Record
503 ( p_instance_id IN NUMBER
504 ,p_resource_id IN NUMBER
505 ,p_acc_id IN NUMBER
506 )
507 IS
508 BEGIN
509 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
510 jtm_message_log_pkg.Log_Msg
511 ( p_instance_id
512 , g_table_name
513 , 'Entering Update_ACC_Record'
514 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
515 END IF;
516
517 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
518 jtm_message_log_pkg.Log_Msg
519 ( p_instance_id
520 , g_table_name
521 , 'Updating ACC record for resource_id = ' || p_resource_id
522 || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
523 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
524 END IF;
525
526 JTM_HOOK_UTIL_PKG.Update_Acc
527 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
528 ,P_ACC_TABLE_NAME => g_acc_table_name
529 ,P_RESOURCE_ID => p_resource_id
530 ,P_ACCESS_ID => p_acc_id
531 );
532
533 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
534 jtm_message_log_pkg.Log_Msg
535 ( p_instance_id
536 , g_table_name
537 , 'Leaving Update_ACC_Record'
538 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
539 END IF;
540 END Update_ACC_Record;
541
542
543 /*** Private procedure that deletes item instance for resource from
544 acc table ***/
545 PROCEDURE Delete_ACC_Record
546 ( p_instance_id IN NUMBER
547 ,p_resource_id IN NUMBER
548 ,p_flow_type IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
549 IS
550 CURSOR c_ii( b_instance_id NUMBER ) IS
551 SELECT inventory_item_id
552 , inv_organization_id
553 , LOCATION_ID
554 , location_type_code
555 , INV_MASTER_ORGANIZATION_ID
556 FROM CSI_ITEM_INSTANCES
557 WHERE instance_id = b_instance_id;
558 r_ii c_ii%ROWTYPE;
559 l_org_id NUMBER;
560 l_return BOOLEAN;
561 BEGIN
562 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
563 jtm_message_log_pkg.Log_Msg
564 ( p_instance_id
565 , g_table_name
566 , 'Entering Delete_ACC_Record'
567 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
568 END IF;
569
570 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
571 jtm_message_log_pkg.Log_Msg
572 ( p_instance_id
573 , g_table_name
574 , 'Deleting ACC record for resource_id = ' || p_resource_id
575 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
576 END IF;
577
578 /*** Delete item instance ACC record ***/
579 JTM_HOOK_UTIL_PKG.Delete_Acc
580 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
581 ,P_ACC_TABLE_NAME => g_acc_table_name
582 ,P_PK1_NAME => g_pk1_name
583 ,P_PK1_NUM_VALUE => p_instance_id
584 ,P_RESOURCE_ID => p_resource_id
585 );
586
587
588 -- NOTES/COUNTERS ( notes/counters for history instances are not replicated so don't delete )
589 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
590 -- NOTES
591 CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN
592 ( P_SOURCE_OBJ_ID => p_instance_id
593 , P_SOURCE_OBJ_CODE => 'CP'
594 , P_RESOURCE_ID => p_resource_id );
595 -- COUNTERS
596 l_return := CSL_CS_COUNTERS_ACC_PKG.PRE_DELETE_PARENT
597 ( P_ITEM_INSTANCE_ID => p_instance_id
598 , P_RESOURCE_ID => p_resource_id );
599 END IF;
600
601 -- SYSTEM ITEM
602 OPEN c_ii( p_instance_id );
603 FETCH c_ii INTO r_ii;
604 IF c_ii%FOUND THEN
605 -- l_org_id := NVL( r_ii.INV_ORGANIZATION_ID, TO_NUMBER(FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')));
606 l_org_id := r_ii.INV_MASTER_ORGANIZATION_ID;
607 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child
608 ( p_inventory_item_id => r_ii.INVENTORY_ITEM_ID
609 , p_organization_id => l_org_id
610 , p_resource_id => p_resource_id
611 );
612 IF r_ii.location_type_code = 'HZ_PARTY_SITES' THEN
613 CSL_HZ_PARTY_SITES_ACC_PKG.DELETE_PARTY_SITE
614 ( p_party_site_id => r_ii.LOCATION_ID
615 ,p_resource_id => p_resource_id);
616 ELSIF r_ii.location_type_code = 'HZ_LOCATIONS' THEN
617 CSL_HZ_LOCATIONS_ACC_PKG.DELETE_LOCATION
618 ( p_location_id => r_ii.LOCATION_ID
619 ,p_resource_id => p_resource_id);
620 END IF;
621 END IF;
622 CLOSE c_ii;
623
624 --Bug 3724152
625 Delete_Item_Attr
626 ( p_instance_id => p_instance_id
627 ,p_resource_id => p_resource_id
628 ,p_flow_type => p_flow_type);
629
630 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
631 jtm_message_log_pkg.Log_Msg
632 ( p_instance_id
633 , g_table_name
634 , 'Leaving Delete_ACC_Record'
635 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
636 END IF;
637 END Delete_ACC_Record;
638
639
640 /** Procedure Insert Child for Child instances */
641 PROCEDURE Insert_Childs
642 ( p_instance_id IN NUMBER
643 , p_resource_id IN NUMBER
644 )
645 IS
646 CURSOR c_child_instance ( b_instance_id NUMBER ) IS
647 SELECT subject_id
648 FROM CSI_II_RELATIONSHIPS
649 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
650 START WITH object_id = b_instance_id
651 CONNECT BY PRIOR subject_id = object_id;
652 r_child_instance c_child_instance%ROWTYPE;
653
654 l_profile_value VARCHAR2(240);
655 BEGIN
656 /*** get debug level ***/
657 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
658
659 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
660 jtm_message_log_pkg.Log_Msg
661 ( p_instance_id
662 , g_table_name
663 , 'Entering Insert_Childs'
664 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
665 END IF;
666
667 l_profile_value := NVL(fnd_profile.value_specific('CSL_REPLICATE_CP_CHILDS'), 'N' );
668 -- IF Replicate Childs profile set to Y THEN
669 IF l_profile_value = 'Y' THEN
670 -- LOOP through the child list
671 FOR r_child_instance IN c_child_instance( p_instance_id ) LOOP
672 -- Insert IB (child_instance_id)
673 Insert_ACC_Record
674 ( r_child_instance.subject_id
675 , p_resource_id
676 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
677 );
678
679 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
680 jtm_message_log_pkg.Log_Msg
681 ( p_instance_id
682 , g_table_name
683 , 'Child Instance inserted into ACC - INSTANCE_ID: ' || r_child_instance.subject_id
684 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
685 END IF;
686 END LOOP; -- Next child IB
687 END IF;
688
689 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
690 jtm_message_log_pkg.Log_Msg
691 ( p_instance_id
692 , g_table_name
693 , 'Leaving Insert_Childs'
694 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
695 END IF;
696
697 END Insert_Childs;
698
699
700 /** Insert Parent Instances for this Record */
701 PROCEDURE Insert_Parents
702 ( p_instance_id IN NUMBER
703 , p_resource_id IN NUMBER
704 )
705 IS
706 CURSOR c_parent_instance ( b_instance_id NUMBER ) IS
707 SELECT object_id
708 FROM CSI_II_RELATIONSHIPS
709 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
710 AND SUBJECT_ID = b_instance_id;
711 r_parent_instance c_parent_instance%ROWTYPE;
712
713 l_profile_value VARCHAR2(240);
714 BEGIN
715 /*** get debug level ***/
716 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
717
718 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
719 jtm_message_log_pkg.Log_Msg
720 ( p_instance_id
721 , g_table_name
722 , 'Entering Insert_Parents'
723 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
724 END IF;
725
726 l_profile_value := NVL(fnd_profile.value_specific('CSL_REPLICATE_CP_PARENTS'), 'N' );
727
728 -- If Replicate Parents profile set to Y THEN
729 IF l_profile_value = 'Y' THEN
730 -- LOOP through the parent list
731 FOR r_parent_instance IN c_parent_instance( p_instance_id ) LOOP
732 -- Insert IB (parent_instance_id)
733 Insert_ACC_Record
734 ( r_parent_instance.object_id
735 , p_resource_id
736 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
737 );
738
739 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
740 jtm_message_log_pkg.Log_Msg
741 ( p_instance_id
742 , g_table_name
743 , 'Parent Instance inserted into ACC - INSTANCE_ID: ' || r_parent_instance.object_id
744 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
745 END IF;
746 END LOOP; -- Next child IB
747 END IF;
748
749 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
750 jtm_message_log_pkg.Log_Msg
751 ( p_instance_id
752 , g_table_name
753 , 'Leaving Insert_Parents'
754 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
755 END IF;
756
757 END Insert_Parents;
758
759 /* ER 3168446
760 ** Insert IB Item, Parent and Child records */
761 PROCEDURE insert_ib_parent_child ( p_instance_id IN NUMBER
762 , p_resource_id IN NUMBER
763 , p_flow_type IN NUMBER) IS
764 BEGIN
765 IF Replicate_Record ( p_instance_id , p_resource_id) THEN
766
767 /*Do not replicate parent for history*/
768 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
769 Insert_Parents ( p_instance_id ,p_resource_id );
770 END IF; -- p_flow_type
771
772 Insert_ACC_Record ( p_instance_id ,p_resource_id ,p_flow_type);
773
774 /*Do not replicate parent/childs for history*/
775 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
776 Insert_Childs ( p_instance_id ,p_resource_id);
777 END IF; --p_flow_type
778 END IF;
779 END insert_ib_parent_child;
780
781
782 /***
783 Public function that gets called when an item instance needs to be inserted
784 into ACC table.
785 Returns TRUE when record already was or has been inserted into ACC table.
786 p_flow_type - DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
787 ***/
788
789 FUNCTION Pre_Insert_Child
790 ( p_instance_id IN NUMBER
791 ,p_resource_id IN NUMBER
792 ,p_flow_type IN NUMBER
793 , p_party_site_id IN NUMBER ) -- ER 3168446 INSTALL_SITE_USE_ID From SR
794
795 RETURN BOOLEAN IS
796
797 /** ER 3168446 - View IB at Location Fix */
798
799 CURSOR c_ib_party ( b_party_site_id NUMBER ) IS
800 SELECT party_id, location_id FROM hz_party_sites
801 WHERE party_site_id = b_party_site_id;
802
803
804 CURSOR c_existing_ib_at_location (
805 b_resource_id NUMBER,
806 b_party_site_id NUMBER,
807 b_location_id NUMBER,
808 b_party_id NUMBER,
809 b_instance_id NUMBER,
810 b_parent_instance_id NUMBER ) IS
811 SELECT acc.instance_id
812 FROM CSL_CSI_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii
813 WHERE acc.instance_id = cii.instance_id
814 AND acc.resource_id = b_resource_id
815 AND owner_party_id = b_party_id
816 AND ( ( cii.location_id = b_party_site_id
817 AND cii.location_type_code = 'HZ_PARTY_SITES'
818 ) OR
819 ( cii.location_id = b_location_id
820 AND cii.location_type_code = 'HZ_LOCATIONS'
821 )
822 )
823 AND acc.instance_id NOT IN
824 (
825 SELECT acc.instance_id FROM CSL_CSI_ITEM_INSTANCES_ACC acc
826 WHERE acc.resource_id = b_resource_id AND
827 acc.instance_id IN (b_instance_id, b_parent_instance_id)
828 UNION
829 SELECT acc.instance_id FROM CSL_CSI_ITEM_INSTANCES_ACC acc
830 WHERE acc.resource_id = b_resource_id AND
831 acc.instance_id IN
832 (
833 SELECT subject_id FROM CSI_II_RELATIONSHIPS
834 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
835 START WITH object_id = b_instance_id
836 CONNECT BY PRIOR subject_id = object_id
837 )
838 ) ;
839
840
841 CURSOR c_new_ib_at_location (
842 b_resource_id NUMBER,
843 b_party_site_id NUMBER,
844 b_location_id NUMBER,
845 b_party_id NUMBER ) IS
846 SELECT cii.instance_id
847 FROM CSI_ITEM_INSTANCES cii, MTL_SYSTEM_ITEMS si
848 WHERE si.organization_id = NVL( cii.inv_organization_id,
849 cii.inv_master_organization_id )
850 AND si.inventory_item_id = cii.inventory_item_id
851 AND cii.instance_id NOT IN
852 ( SELECT acc.instance_id FROM CSL_CSI_ITEM_INSTANCES_ACC acc
853 WHERE acc.resource_id = b_resource_id
854 )
855 AND owner_party_id = b_party_id
856 AND ( ( cii.location_id = b_party_site_id
857 AND cii.location_type_code = 'HZ_PARTY_SITES'
858 ) OR
859 ( cii.location_id = b_location_id
860 AND cii.location_type_code = 'HZ_LOCATIONS'
861 )
862 )
863 AND si.service_item_flag = 'N' AND nvl(si.enabled_flag,'Y') = 'Y'
864 AND si.serv_req_enabled_code = 'E';
865
866 l_party_id NUMBER;
867 l_location_id NUMBER;
868
869 BEGIN
870 /*** get debug level ***/
871 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
872
873 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
874 jtm_message_log_pkg.Log_Msg
875 ( p_instance_id
876 , g_table_name
877 , 'Entering Pre_Insert_Child procedure'
878 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
879 END IF;
880
881 /* ER 3168446 View IB at Location Fix
882 ** Get the Party of this party Site */
883 OPEN c_ib_party (p_party_site_id);
884 FETCH c_ib_party INTO l_party_id, l_location_id;
885 CLOSE c_ib_party;
886
887 /** Insert for SR IB */
888 g_ib_count := 0;
889 insert_ib_parent_child (p_instance_id, p_resource_id, p_flow_type);
890
891 -- Increment counter for existing IB's
892 FOR c_exist_ib_items IN c_existing_ib_at_location (
893 p_resource_id,
894 p_party_site_id,
895 l_location_id,
896 l_party_id,
897 p_instance_id,
898 g_parent_instance_id )
899 LOOP
900 Insert_ACC_Record ( p_instance_id ,p_resource_id ,p_flow_type);
901 END LOOP;
902
903 -- Greater than check for Profile IB count was reset to a lower value
904 IF g_ib_count >= NVL(FND_PROFILE.VALUE (
905 'CSL_IBITEM_COUNT_AT_LOCATION'), 0) THEN
906 RETURN TRUE;
907 ELSE
908
909 /** Insert For other IB's at location */
910 FOR c_ib_items IN c_new_ib_at_location (
911 p_resource_id, p_party_site_id , l_location_id, l_party_id )
912 LOOP
913
914 IF g_ib_count < NVL(FND_PROFILE.VALUE (
915 'CSL_IBITEM_COUNT_AT_LOCATION'), 0) THEN
916 Insert_ACC_Record ( c_ib_items.instance_id,
917 p_resource_id, p_flow_type);
918 ELSE
919 EXIT;
920 END IF;
921
922 END LOOP;
923 END IF;
924
925 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
926 jtm_message_log_pkg.Log_Msg
927 ( p_instance_id
928 , g_table_name
929 , 'Leaving Pre_Insert_Child procedure'
930 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
931 END IF;
932
933 -- ER 3168446
934 g_ib_count := 0;
935
936 /*** always return success ***/
937 RETURN TRUE;
938 EXCEPTION
939 WHEN OTHERS THEN
940 g_ib_count := 0;
941 IF c_ib_party%ISOPEN THEN
942 CLOSE c_ib_party;
943 END IF;
944 IF c_existing_ib_at_location%ISOPEN THEN
945 CLOSE c_existing_ib_at_location;
946 END IF;
947 IF c_new_ib_at_location%ISOPEN THEN
948 CLOSE c_new_ib_at_location;
949 END IF;
950 END Pre_Insert_Child;
951
952 /**/
953 PROCEDURE Delete_Childs
954 ( p_instance_id IN NUMBER
955 , p_resource_id IN NUMBER
956 )
957 IS
958 CURSOR c_child_instance ( b_instance_id NUMBER ) IS
959 SELECT subject_id
960 FROM CSI_II_RELATIONSHIPS
961 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
962 START WITH object_id = b_instance_id
963 CONNECT BY PRIOR subject_id = object_id;
964 r_child_instance c_child_instance%ROWTYPE;
965
966 l_profile_value VARCHAR2(240);
967 BEGIN
968 /*** get debug level ***/
969 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
970
971 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
972 jtm_message_log_pkg.Log_Msg
973 ( p_instance_id
974 , g_table_name
975 , 'Entering Delete_Childs'
976 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
977 END IF;
978
979 l_profile_value := NVL(fnd_profile.value_specific('CSL_REPLICATE_CP_CHILDS'), 'N' );
980 -- IF Replicate childs profile set to Y THEN
981 IF l_profile_value = 'Y' THEN
982 -- LOOP through the child list
983 FOR r_child_instance IN c_child_instance( p_instance_id ) LOOP
984 -- Delete IB (child_instance_id)
985 Delete_ACC_Record
986 ( r_child_instance.subject_id
987 , p_resource_id
988 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
989 );
990
991 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
992 jtm_message_log_pkg.Log_Msg
993 ( p_instance_id
994 , g_table_name
995 , 'Child removed from ACC - INSTANCE_ID: ' || r_child_instance.subject_id
996 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
997 END IF;
998 END LOOP; -- Next child IB
999 END IF;
1000
1001 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1002 jtm_message_log_pkg.Log_Msg
1003 ( p_instance_id
1004 , g_table_name
1005 , 'Leaving Delete_Childs'
1006 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1007 END IF;
1008
1009 END Delete_Childs;
1010
1011 PROCEDURE Delete_Parents
1012 ( p_instance_id IN NUMBER
1013 , p_resource_id IN NUMBER
1014 )
1015 IS
1016 CURSOR c_parent_instance ( b_instance_id NUMBER ) IS
1017 SELECT object_id
1018 FROM CSI_II_RELATIONSHIPS
1019 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1020 AND SUBJECT_ID = b_instance_id;
1021 r_parent_instance c_parent_instance%ROWTYPE;
1022
1023 l_profile_value VARCHAR2(240);
1024 BEGIN
1025 /*** get debug level ***/
1026 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1027
1028 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1029 jtm_message_log_pkg.Log_Msg
1030 ( p_instance_id
1031 , g_table_name
1032 , 'Entering Delete_Parents'
1033 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1034 END IF;
1035
1036 l_profile_value := NVL(fnd_profile.value_specific('CSL_REPLICATE_CP_PARENTS'), 'N' );
1037
1038 -- If Replicate Parents profile set to Y THEN
1039 IF l_profile_value = 'Y' THEN
1040 -- LOOP through the parent list
1041 FOR r_parent_instance IN c_parent_instance( p_instance_id ) LOOP
1042 -- Delete IB (child_instance_id)
1043 Delete_ACC_Record
1044 ( r_parent_instance.object_id
1045 , p_resource_id
1046 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
1047 );
1048
1049 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1050 jtm_message_log_pkg.Log_Msg
1051 ( p_instance_id
1052 , g_table_name
1053 , 'Parent removed from ACC - INSTANCE_ID: ' || r_parent_instance.object_id
1054 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1055 END IF;
1056 END LOOP; -- Next child IB
1057 END IF;
1058
1059 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1060 jtm_message_log_pkg.Log_Msg
1061 ( p_instance_id
1062 , g_table_name
1063 , 'Leaving Delete_Parents'
1064 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1065 END IF;
1066
1067 END Delete_Parents;
1068
1069
1070 /***
1071 Public procedure that gets called when an item instance needs to be
1072 deleted from ACC table.
1073 p_flow_type - DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
1074 ***/
1075 PROCEDURE Post_Delete_Child
1076 ( p_instance_id IN NUMBER
1077 ,p_resource_id IN NUMBER
1078 ,p_flow_type IN NUMBER
1079 ,p_party_site_id IN NUMBER )
1080 IS
1081
1082 /** ER 3168446 - View IB at location Fix */
1083
1084 CURSOR c_ib_party ( b_party_site_id NUMBER ) IS
1085 SELECT party_id, location_id FROM hz_party_sites
1086 WHERE party_site_id = b_party_site_id;
1087
1088
1089 CURSOR c_ib_at_location (
1090 b_resource_id NUMBER,
1091 b_party_site_id NUMBER,
1092 b_location_id NUMBER,
1093 b_party_id NUMBER ) IS
1094 SELECT acc.instance_id
1095 FROM CSL_CSI_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii
1096 WHERE acc.instance_id = cii.instance_id
1097 AND acc.resource_id = b_resource_id
1098 AND owner_party_id = b_party_id
1099 AND ( ( cii.location_id = b_party_site_id
1100 AND cii.location_type_code = 'HZ_PARTY_SITES' )
1101 OR ( cii.location_id = b_location_id
1102 AND cii.location_type_code = 'HZ_LOCATIONS') );
1103
1104 l_party_id NUMBER;
1105 l_location_id NUMBER;
1106
1107 l_acc_id NUMBER;
1108 BEGIN
1109 /*** get debug level ***/
1110 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1111
1112 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1113 jtm_message_log_pkg.Log_Msg
1114 ( p_instance_id
1115 , g_table_name
1116 , 'Entering Post_Delete_Child'
1117 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1118 END IF;
1119
1120 /** ER 3168446 - View IB at Location Fix
1121 Delete the Instance associated with this SR and other Instances also
1122 */
1123
1124 /** Get the Party of this party Site */
1125 OPEN c_ib_party (p_party_site_id);
1126 FETCH c_ib_party INTO l_party_id, l_location_id;
1127 CLOSE c_ib_party;
1128
1129
1130 FOR c_ib_items IN c_ib_at_location (
1131 p_resource_id, p_party_site_id, l_location_id, l_party_id )
1132 LOOP
1133
1134 delete_acc_record ( p_instance_id, p_resource_id, p_flow_type);
1135
1136 END LOOP;
1137
1138 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1139 jtm_message_log_pkg.Log_Msg
1140 ( p_instance_id
1141 , g_table_name
1142 , 'Leaving Post_Delete_Child'
1143 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1144 END IF;
1145 END Post_Delete_Child;
1146
1147 /*Procedure that gets called from mtl_onhand_quantity acc package*/
1148 PROCEDURE Pre_Insert_Item
1149 ( p_inventory_item_id IN NUMBER
1150 , p_organization_id IN NUMBER
1151 , p_subinventory_code IN VARCHAR2
1152 , p_resource_id IN NUMBER
1153 )
1154 IS
1155 CURSOR c_item_instance( b_inventory_item_id NUMBER
1156 , b_organization_id NUMBER
1157 , b_subinventory_code VARCHAR2)
1158 IS
1159 SELECT instance_id,
1160 INV_ORGANIZATION_ID,
1161 INVENTORY_ITEM_ID,
1162 INV_MASTER_ORGANIZATION_ID
1163 FROM csi_item_instances
1164 WHERE inventory_item_id = b_inventory_item_id
1165 AND inv_organization_id = b_organization_id
1166 AND inv_subinventory_name = b_subinventory_code;
1167
1168 l_org_id NUMBER;
1169
1170 BEGIN
1171 /*** get debug level ***/
1172 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1173 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1174 jtm_message_log_pkg.Log_Msg
1175 ( p_resource_id
1176 , g_table_name
1177 , 'Entering Pre_Insert_Item'
1178 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1179 END IF;
1180
1181 FOR r_item_instance IN c_item_instance ( p_inventory_item_id, p_organization_id, p_subinventory_code ) LOOP
1182 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1183 jtm_message_log_pkg.Log_Msg
1184 ( r_item_instance.instance_id
1185 , g_table_name
1186 , 'Inserting ACC record for resource_id = ' || p_resource_id
1187 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1188 END IF;
1189
1190 JTM_HOOK_UTIL_PKG.Insert_Acc
1191 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
1192 ,P_ACC_TABLE_NAME => g_acc_table_name
1193 ,P_RESOURCE_ID => p_resource_id
1194 ,P_PK1_NAME => g_pk1_name
1195 ,P_PK1_NUM_VALUE => r_item_instance.instance_id
1196 );
1197
1198 -- Add SYSTEM ITEMs
1199 --l_org_id := NVL( r_item_instance.INV_ORGANIZATION_ID, TO_NUMBER(FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')));
1200 l_org_id := r_item_instance.INV_MASTER_ORGANIZATION_ID;
1201 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child( p_inventory_item_id => r_item_instance.INVENTORY_ITEM_ID
1202 , p_organization_id => l_org_id
1203 , p_resource_id => p_resource_id
1204 );
1205
1206 END LOOP;
1207
1208 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1209 jtm_message_log_pkg.Log_Msg
1210 ( p_resource_id
1211 , g_table_name
1212 , 'Leaving Pre_Insert_Item'
1213 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1214 END IF;
1215 END Pre_Insert_Item;
1216
1217 /*Procedure that gets called from mtl_onhand_quantity acc package*/
1218 PROCEDURE Post_Delete_Item
1219 ( p_inventory_item_id IN NUMBER
1220 , p_organization_id IN NUMBER
1221 , p_subinventory_code IN VARCHAR2
1222 , p_resource_id IN NUMBER
1223 )
1224 IS
1225 CURSOR c_item_instance( b_inventory_item_id NUMBER
1226 , b_organization_id NUMBER
1227 , b_subinventory_code VARCHAR2)
1228 IS
1229 SELECT instance_id,
1230 INV_ORGANIZATION_ID,
1231 INVENTORY_ITEM_ID,
1232 INV_MASTER_ORGANIZATION_ID
1233 FROM csi_item_instances
1234 WHERE inventory_item_id = b_inventory_item_id
1235 AND inv_organization_id = b_organization_id
1236 AND inv_subinventory_name = b_subinventory_code;
1237
1238 l_org_id NUMBER;
1239
1240 BEGIN
1241 /*** get debug level ***/
1242 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1244 jtm_message_log_pkg.Log_Msg
1245 ( p_resource_id
1246 , g_table_name
1247 , 'Entering Post_Delete_Item'
1248 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1249 END IF;
1250
1251 FOR r_item_instance IN c_item_instance ( p_inventory_item_id, p_organization_id, p_subinventory_code ) LOOP
1252 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1253 jtm_message_log_pkg.Log_Msg
1254 ( r_item_instance.instance_id
1255 , g_table_name
1256 , 'Deleting ACC record for resource_id = ' || p_resource_id
1257 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1258 END IF;
1259
1260 /*** Delete item instance ACC record ***/
1261 JTM_HOOK_UTIL_PKG.Delete_Acc
1262 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
1263 ,P_ACC_TABLE_NAME => g_acc_table_name
1264 ,P_PK1_NAME => g_pk1_name
1265 ,P_PK1_NUM_VALUE => r_item_instance.instance_id
1266 ,P_RESOURCE_ID => p_resource_id
1267 );
1268
1269 -- Delete SYSTEM ITEMs
1270 --l_org_id := NVL( r_item_instance.INV_ORGANIZATION_ID, TO_NUMBER(FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')));
1271 l_org_id := r_item_instance.INV_MASTER_ORGANIZATION_ID;
1272 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child( p_inventory_item_id => r_item_instance.INVENTORY_ITEM_ID
1273 , p_organization_id => l_org_id
1274 , p_resource_id => p_resource_id
1275 );
1276
1277 END LOOP;
1278
1279 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1280 jtm_message_log_pkg.Log_Msg
1281 ( p_resource_id
1282 , g_table_name
1283 , 'Leaving Post_Delete_Item'
1284 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1285 END IF;
1286 END Post_Delete_Item;
1287
1288
1289 /*** Called before item instance Insert ***/
1290 PROCEDURE PRE_INSERT_ITEM_INSTANCE
1291 ( x_return_status OUT NOCOPY varchar2
1292 )
1293 IS
1294 BEGIN
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296 END PRE_INSERT_ITEM_INSTANCE;
1297
1298 /*** Called after item instance Insert ***/
1299 PROCEDURE POST_INSERT_ITEM_INSTANCE ( p_api_version IN NUMBER
1300 , P_Init_Msg_List IN VARCHAR2
1301 , P_Commit IN VARCHAR2
1302 , p_validation_level IN NUMBER
1303 , p_instance_id IN NUMBER
1304 , X_Return_Status OUT NOCOPY VARCHAR2
1305 , X_Msg_Count OUT NOCOPY NUMBER
1306 , X_Msg_Data OUT NOCOPY VARCHAR2)
1307 IS
1308 l_dummy BOOLEAN;
1309
1310 CURSOR c_is_parent( b_instance_id NUMBER ) IS
1311 SELECT cia.resource_id
1312 FROM CSL_CSI_ITEM_INSTANCES_ACC cia
1313 , CSI_II_RELATIONSHIPS cir
1314 WHERE cir.relationship_type_code = 'COMPONENT-OF'
1315 AND cir.subject_id = cia.instance_id
1316 AND cir.object_id = b_instance_id;
1317
1318 CURSOR c_is_child( b_instance_id NUMBER ) IS
1319 SELECT cia.resource_id
1320 FROM CSL_CSI_ITEM_INSTANCES_ACC cia
1321 , CSI_II_RELATIONSHIPS cir
1322 WHERE cir.relationship_type_code = 'COMPONENT-OF'
1323 AND cir.object_id = cia.instance_id
1324 AND cir.subject_id = b_instance_id;
1325
1326
1327 BEGIN
1328 /*** get debug level ***/
1329 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1330
1331 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1332 jtm_message_log_pkg.Log_Msg
1333 ( p_instance_id
1334 , g_table_name
1335 , 'Entering POST_INSERT hook'
1336 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1337 END IF;
1338 /*Check if this item is a parent or child of an existing instance*/
1339
1340 /*** Is this a parent ? ***/
1341 FOR r_is_parent IN c_is_parent( p_instance_id ) LOOP
1342 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1343 jtm_message_log_pkg.Log_Msg
1344 ( p_instance_id
1345 , g_table_name
1346 , 'Instance is parent for resource: '||r_is_parent.resource_id
1347 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1348 END IF;
1349 Insert_ACC_Record( p_instance_id => p_instance_id
1350 , p_resource_id => r_is_parent.resource_id
1351 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
1352 );
1353 END LOOP;
1354
1355 /*Is this a child ?*/
1356 FOR r_is_child IN c_is_child( p_instance_id ) LOOP
1357 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1358 jtm_message_log_pkg.Log_Msg
1359 ( p_instance_id
1360 , g_table_name
1361 , 'Instance is child for resource: '||r_is_child.resource_id
1362 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1363 END IF;
1364
1365 Insert_ACC_Record( p_instance_id => p_instance_id
1366 , p_resource_id => r_is_child.resource_id
1367 , p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
1368 );
1369 END LOOP;
1370
1371 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1372 jtm_message_log_pkg.Log_Msg
1373 ( p_instance_id
1374 , g_table_name
1375 , 'Leaving POST_INSERT hook'
1376 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1377 END IF;
1378
1379 x_return_status := FND_API.G_RET_STS_SUCCESS;
1380 RETURN;
1381
1382 EXCEPTION WHEN OTHERS THEN
1383 /*** hook failed -> log error ***/
1384 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1385 jtm_message_log_pkg.Log_Msg
1386 ( p_instance_id
1387 , g_table_name
1388 , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
1389 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1390 END IF;
1391 fnd_msg_pub.Add_Exc_Msg('CSL_CSI_ITEM_INSTANCES_ACC_PKG','POST_INSERT_ITEM_INSTANCE',sqlerrm);
1392 -- x_return_status := FND_API.G_RET_STS_ERROR;
1393 x_return_status := FND_API.G_RET_STS_SUCCESS;
1394 END POST_INSERT_ITEM_INSTANCE;
1395
1396 /* Called before item instance Update */
1397 PROCEDURE PRE_UPDATE_ITEM_INSTANCE
1398 ( x_return_status OUT NOCOPY varchar2
1399 )
1400 IS
1401 BEGIN
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403 END PRE_UPDATE_ITEM_INSTANCE;
1404
1405 /* Called after item instance Update */
1406 PROCEDURE POST_UPDATE_ITEM_INSTANCE
1407 ( x_return_status OUT NOCOPY varchar2
1408 )
1409 IS
1410 BEGIN
1411 x_return_status := FND_API.G_RET_STS_SUCCESS;
1412 END POST_UPDATE_ITEM_INSTANCE;
1413
1414 /* Called before item instance Delete */
1415 PROCEDURE PRE_DELETE_ITEM_INSTANCE
1416 ( x_return_status OUT NOCOPY varchar2
1417 )
1418 IS
1419 BEGIN
1420 x_return_status := FND_API.G_RET_STS_SUCCESS;
1421 END PRE_DELETE_ITEM_INSTANCE;
1422
1423 /* Called after item instance Delete */
1424 PROCEDURE POST_DELETE_ITEM_INSTANCE
1425 ( x_return_status OUT NOCOPY varchar2
1426 )
1427 IS
1428 BEGIN
1429 x_return_status := FND_API.G_RET_STS_SUCCESS;
1430 END POST_DELETE_ITEM_INSTANCE;
1431
1432 PROCEDURE CONC_ITEM_INSTANCES( p_last_run_date IN DATE)
1433 IS
1434
1435 CURSOR c_changed(b_last_run_date DATE) IS
1436 SELECT ACCESS_ID , resource_id
1437 FROM csl_csi_item_instances_acc
1438 WHERE (instance_id in
1439 (SELECT instance_id
1440 FROM csi_item_instances
1441 WHERE last_update_date >= b_last_run_date));
1442
1443 l_org_id NUMBER;
1444 l_dummy BOOLEAN;
1445
1446 TYPE access_idTab IS TABLE OF CSL_CSI_ITEM_INSTANCES_ACC.access_id%TYPE INDEX BY BINARY_INTEGER;
1447 TYPE inst_idTab IS TABLE OF CSL_CSI_ITEM_INSTANCES_ACC.instance_id%TYPE INDEX BY BINARY_INTEGER;
1448 TYPE resource_idTab IS TABLE OF CSL_CSI_ITEM_INSTANCES_ACC.resource_id%TYPE INDEX BY BINARY_INTEGER;
1449 TYPE inv_org_idTab IS TABLE OF CSI_ITEM_INSTANCES.INV_ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
1450 TYPE inv_itm_idTab IS TABLE OF CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE INDEX BY BINARY_INTEGER;
1451 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1452 l_tab_resource_id ASG_DOWNLOAD.USER_LIST;
1453
1454 acc_id access_idTab;
1455 inst_id inst_idTab;
1456 res_id resource_idTab;
1457 inv_org_id inv_org_idTab;
1458 inv_itm_id inv_itm_idTab;
1459
1460 BEGIN
1461 /*** get debug level ***/
1462 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1463
1464 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1465 jtm_message_log_pkg.Log_Msg
1466 ( 0
1467 , g_table_name
1468 , 'Entering CONC_ITEM_INSTANCES hook'
1469 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1470 END IF;
1471
1472 --UPDATE
1473 /*Fetch all changed item instances that are in the acc table*/
1474 OPEN c_changed( p_last_run_date );
1475 FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_resource_id;
1476
1477 IF (l_tab_access_id.COUNT > 0) THEN
1478 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1479 jtm_message_log_pkg.Log_Msg
1480 ( 0
1481 , g_table_name
1482 , 'Update ACC record for all resources, count = ' || l_tab_access_id.COUNT
1483 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1484 END IF;
1485
1486 /*** push to oLite using asg_download ***/
1487 l_dummy := asg_download.markdirty(
1488 P_PUB_ITEM => g_publication_item_name(1)
1489 , P_ACCESSLIST => l_tab_access_id
1490 , P_RESOURCELIST => l_tab_resource_id
1491 , P_DML_TYPE => 'U'
1492 , P_TIMESTAMP => SYSDATE
1493 );
1494 END IF;
1495 CLOSE c_changed;
1496
1497 -- INSERT
1498 SELECT cii.INSTANCE_ID
1499 , cii.INV_ORGANIZATION_ID
1500 , cii.INVENTORY_ITEM_ID
1501 , cqa.RESOURCE_ID
1502 BULK COLLECT INTO inst_id, inv_org_id, inv_itm_id, res_id
1503 FROM csi_item_instances cii
1504 , csl_mtl_onhand_qty_acc cqa
1505 , csi_instance_statuses iis
1506 WHERE cii.inventory_item_id = cqa.inventory_item_id
1507 AND cii.inv_organization_id = cqa.organization_id
1508 AND cii.inv_subinventory_name = cqa.subinventory_code
1509 AND ((cqa.LOT_NUMBER IS NULL AND cii.LOT_NUMBER IS NULL)
1510 OR (cqa.LOT_NUMBER = cii.LOT_NUMBER))
1511 AND ((cqa.LOCATOR_ID IS NULL AND cii.INV_LOCATOR_ID IS NULL)
1512 OR (cqa.LOCATOR_ID = cii.INV_LOCATOR_ID))
1513 AND ((cqa.REVISION IS NULL AND cii.INVENTORY_REVISION IS NULL)
1514 OR (cqa.REVISION = cii.INVENTORY_REVISION))
1515 AND cii.location_type_code = 'INVENTORY'
1516 AND cii.INSTANCE_STATUS_ID = iis.instance_status_id
1517 AND NVL(iis.terminated_flag,'N') = 'N'
1518 AND NOT EXISTS
1519 ( SELECT null
1520 FROM csl_csi_item_instances_acc cia
1521 WHERE cii.instance_id = cia.instance_id
1522 AND cqa.resource_id = cia.resource_id
1523 );
1524
1525 IF (inst_id.COUNT > 0) THEN
1526 FOR i IN inst_id.FIRST..inst_id.LAST LOOP
1527 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1528 jtm_message_log_pkg.Log_Msg
1529 ( inst_id(i)
1530 , g_table_name
1531 , 'Inserting ACC record for resource_id = ' || res_id(i)
1532 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1533 END IF;
1534
1535 JTM_HOOK_UTIL_PKG.Insert_Acc
1536 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
1537 ,P_ACC_TABLE_NAME => g_acc_table_name
1538 ,P_RESOURCE_ID => res_id(i)
1539 ,P_PK1_NAME => g_pk1_name
1540 ,P_PK1_NUM_VALUE => inst_id(i)
1541 );
1542
1543 -- Add SYSTEM ITEMs
1544 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child( p_inventory_item_id => inv_itm_id(i)
1545 , p_organization_id => inv_org_id(i)
1546 , p_resource_id => res_id(i)
1547 );
1548 END LOOP;
1549 END IF;
1550
1551 -- DELETE
1552 acc_id.DELETE;
1553 inst_id.DELETE;
1554 inv_org_id.DELETE;
1555 inv_itm_id.DELETE;
1556 res_id.DELETE;
1557
1558 SELECT ii.INSTANCE_ID
1559 , ii.INV_ORGANIZATION_ID
1560 , ii.INVENTORY_ITEM_ID
1561 , iiac.RESOURCE_ID
1562 BULK COLLECT INTO inst_id, inv_org_id, inv_itm_id, res_id
1563 FROM CSL_CSI_ITEM_INSTANCES_ACC iiac
1564 , CSI_ITEM_INSTANCES ii
1565 , CSI_INSTANCE_STATUSES iis
1566 WHERE ii.INV_ORGANIZATION_ID IS NOT NULL
1567 AND ii.INV_SUBINVENTORY_NAME IS NOT NULL
1568 AND ii.INVENTORY_ITEM_ID IS NOT NULL
1569 AND ii.INSTANCE_ID = iiac.INSTANCE_ID
1570 AND ii.INSTANCE_STATUS_ID = iis.INSTANCE_STATUS_ID
1571 AND NVL(iis.TERMINATED_FLAG,'N') = 'Y'
1572 AND NOT EXISTS
1573 ( SELECT null
1574 FROM CS_INCIDENTS_ALL_B inc
1575 , CSL_CS_INCIDENTS_ALL_ACC inac
1576 WHERE inc.incident_id = inac.incident_id
1577 AND inc.customer_product_id = iiac.instance_id
1578 AND inac.resource_id = iiac.resource_id
1579 );
1580
1581 IF (inst_id.COUNT > 0) THEN
1582 FOR i IN inst_id.FIRST..inst_id.LAST LOOP
1583 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1584 jtm_message_log_pkg.Log_Msg
1585 ( inst_id(i)
1586 , g_table_name
1587 , 'Deleting ACC record for resource_id = ' || res_id(i)
1588 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1589 END IF;
1590
1591 -- Delete item instance ACC record
1592 JTM_HOOK_UTIL_PKG.Delete_Acc
1593 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
1594 ,P_ACC_TABLE_NAME => g_acc_table_name
1595 ,P_PK1_NAME => g_pk1_name
1596 ,P_PK1_NUM_VALUE => inst_id(i)
1597 ,P_RESOURCE_ID => res_id(i)
1598 );
1599
1600 -- Delete SYSTEM ITEMs
1601 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child( p_inventory_item_id => inv_itm_id(i)
1602 , p_organization_id => inv_org_id(i)
1603 , p_resource_id => res_id(i)
1604 );
1605 END LOOP;
1606 END IF;
1607
1608
1609 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1610 jtm_message_log_pkg.Log_Msg
1611 ( 0
1612 , g_table_name
1613 , 'Leaving CONC_ITEM_INSTANCES hook'
1614 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1615 END IF;
1616
1617 END CONC_ITEM_INSTANCES;
1618
1619 END CSL_CSI_ITEM_INSTANCES_ACC_PKG;