[Home] [Help]
PACKAGE BODY: APPS.CSM_ITEM_INSTANCE_EVENT_PKG
Source
1 PACKAGE BODY CSM_ITEM_INSTANCE_EVENT_PKG AS
2 /* $Header: csmeibb.pls 120.11 2008/06/20 08:02:58 trajasek ship $ */
3
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_table_name1 CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18 CSM_ACC_PKG.t_publication_item_list('CSF_M_ITEM_INSTANCES');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
20 g_pub_item CONSTANT VARCHAR2(30) := 'CSF_M_ITEM_INSTANCES';
21
22 g_table_name2 CONSTANT VARCHAR2(30) := 'CSI_II_RELATIONSHIPS';
23 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_II_RELATIONSHIPS_ACC';
24 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_II_RELATIONSHIPS_ACC_S';
25 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
26 CSM_ACC_PKG.t_publication_item_list('CSF_M_II_RELATIONSHIPS');
27 g_pk1_name2 CONSTANT VARCHAR2(30) := 'RELATIONSHIP_ID';
28 g_pub_item2 CONSTANT VARCHAR2(30) := 'CSF_M_II_RELATIONSHIPS';
29
30 PROCEDURE ITEM_INSTANCE_MDIRTY_U_ECHUSER(p_instance_id IN NUMBER,
31 p_error_msg OUT NOCOPY VARCHAR2,
32 x_return_status IN OUT NOCOPY VARCHAR2)
33 IS
34 cursor l_instance_users_csr (p_instance_id csm_item_instances_acc.instance_id%TYPE) is
35 SELECT access_id, user_id
36 FROM csm_item_instances_acc
37 WHERE instance_id = p_instance_id;
38
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER ',
42 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER',FND_LOG.LEVEL_PROCEDURE);
43
44 -- get users who have access to this instance_ID
45 FOR r_instance_users_rec in l_instance_users_csr(p_instance_id) LOOP
46 CSM_ACC_PKG.Update_Acc
47 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
48 ,P_ACC_TABLE_NAME => g_acc_table_name1
49 ,P_ACCESS_ID => r_instance_users_rec.access_id
50 ,P_USER_ID => r_instance_users_rec.user_id
51 );
52 END LOOP;
53
54 p_error_msg := 'SUCCESS';
55 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER ',
56 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER',FND_LOG.LEVEL_PROCEDURE);
57
58 EXCEPTION
59 WHEN others THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 p_error_msg := 'FAILED ITEM_INSTANCE_MDIRTY_U_ECHUSER InstanceId:' || p_instance_id;
62 CSM_UTIL_PKG.LOG(p_error_msg,'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER',FND_LOG.LEVEL_EXCEPTION);
63 RAISE;
64 END ITEM_INSTANCE_MDIRTY_U_ECHUSER;
65
66 PROCEDURE II_RELATIONSHIPS_ACC_I(p_relationship_id IN NUMBER,
67 p_user_id IN NUMBER,
68 p_error_msg OUT NOCOPY VARCHAR2,
69 x_return_status IN OUT NOCOPY VARCHAR2)
70 IS
71 BEGIN
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_I ',
74 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
75
76 CSM_ACC_PKG.Insert_Acc
77 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
78 ,P_ACC_TABLE_NAME => g_acc_table_name2
79 ,P_SEQ_NAME => g_acc_sequence_name2
80 ,P_PK1_NAME => g_pk1_name2
81 ,P_PK1_NUM_VALUE => p_relationship_id
82 ,P_USER_ID => p_user_id
83 );
84
85 p_error_msg := 'SUCCESS';
86 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_I ',
87 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_I', FND_LOG.LEVEL_PROCEDURE);
88
89 EXCEPTION
90 WHEN others THEN
91 x_return_status := FND_API.G_RET_STS_ERROR;
92 p_error_msg := ' FAILED II_RELATIONSHIPS_ACC_I RELATIONSHIP_ID: ' || to_char(p_relationship_id);
93 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
94 RAISE;
95 END II_RELATIONSHIPS_ACC_I;
96
97 PROCEDURE ITEM_INSTANCES_ACC_PROCESSOR(p_instance_id IN NUMBER,
98 p_user_id IN NUMBER,
99 p_flowtype IN VARCHAR2,
100 p_error_msg OUT NOCOPY VARCHAR2,
101 x_return_status IN OUT NOCOPY VARCHAR2)
102 IS
103 l_inv_item_id NUMBER;
104 l_lst_vld_org_id NUMBER;
105 l_label VARCHAR(30);
106 l_parent_ins NUMBER;
107
108 CURSOR c_ins_label(c_instance_id NUMBER)
109 IS
110 SELECT civ.version_label
111 FROM csi_i_version_labels civ
112 WHERE (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
113 AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
114 AND civ.instance_id = c_instance_id;
115
116 CURSOR c_parent_instance(c_instance_id NUMBER)
117 IS
118 SELECT CIR.OBJECT_ID
119 FROM CSI_II_RELATIONSHIPS CIR
120 WHERE CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
121 AND CIR.SUBJECT_ID = c_instance_id;
122
123 BEGIN
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR ',
126 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
127
128 IF p_instance_id IS NOT NULL AND p_user_id IS NOT NULL THEN
129 CSM_ACC_PKG.Insert_Acc
130 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
131 ,P_ACC_TABLE_NAME => g_acc_table_name1
132 ,P_SEQ_NAME => g_acc_sequence_name1
133 ,P_PK1_NAME => g_pk1_name1
134 ,P_PK1_NUM_VALUE => p_instance_id
135 ,P_USER_ID => p_user_id
136 );
137
138 --Bug 6594511 : Insert the corresponding item into acc table
139 SELECT cii.inventory_item_id,NVL(cii.LAST_VLD_ORGANIZATION_ID,cii.inv_master_organization_id)
140 INTO l_inv_item_id,l_lst_vld_org_id
141 FROM csi_item_instances cii
142 WHERE cii.instance_id = p_instance_id;
143
144 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_I(l_inv_item_id,
145 l_lst_vld_org_id,
146 p_user_id,
147 p_error_msg,
148 x_return_status);
149
150 --12.1
151 OPEN c_ins_label(p_instance_id);
152 FETCH c_ins_label INTO l_label;
153 CLOSE c_ins_label;
154
155 OPEN c_parent_instance(p_instance_id );
156 FETCH c_parent_instance INTO l_parent_ins;
157 CLOSE c_parent_instance;
158
159 UPDATE csm_item_instances_acc
160 SET PARENT_INSTANCE_ID = l_parent_ins,
161 VERSION_LABEL = l_label
162 WHERE USER_ID = p_user_id
163 AND INSTANCE_ID = p_instance_id;
164
165 -- increment count of item instances downloaded
166 -- this is used to determine how many IB instances are downloaded at a location
167 IF p_flowtype IS NULL OR p_flowtype <> 'HISTORY' THEN
168 csm_sr_event_pkg.g_ib_count := NVL(csm_sr_event_pkg.g_ib_count,0) + 1;
169 END IF;
170 END IF;
171
172 p_error_msg := 'SUCCESS';
173 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR ',
174 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
175 EXCEPTION
176 WHEN others THEN
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 p_error_msg := ' FAILED ITEM_INSTANCES_ACC_PROCESSOR INSTANCE_ID: ' || to_char(p_instance_id);
179 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
180 RAISE;
181 END ITEM_INSTANCES_ACC_PROCESSOR;
182
183 PROCEDURE II_RELATIONSHIPS_ACC_D(p_relationship_id IN NUMBER,
184 p_user_id IN NUMBER,
185 p_error_msg OUT NOCOPY VARCHAR2,
186 x_return_status IN OUT NOCOPY VARCHAR2)
187 IS
188 BEGIN
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_D ',
191 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
192
193 CSM_ACC_PKG.Delete_Acc
194 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
195 ,P_ACC_TABLE_NAME => g_acc_table_name2
196 ,P_PK1_NAME => g_pk1_name2
197 ,P_PK1_NUM_VALUE => p_relationship_id
198 ,P_USER_ID => p_user_id
199 );
200
201 p_error_msg := 'SUCCESS';
202 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_D',
203 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_D', FND_LOG.LEVEL_PROCEDURE);
204
205 EXCEPTION
206 WHEN others THEN
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 p_error_msg := ' FAILED II_RELATIONSHIPS_ACC_D RELATIONSHIP_ID: ' || to_char(p_relationship_id);
209 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.II_RELATIONSHIPS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
210 RAISE;
211 END II_RELATIONSHIPS_ACC_D;
212
213 PROCEDURE ITEM_INSTANCES_ACC_D(p_instance_id IN NUMBER,
214 p_user_id IN NUMBER,
215 p_error_msg OUT NOCOPY VARCHAR2,
216 x_return_status IN OUT NOCOPY VARCHAR2)
217 IS
218 l_ref_exists NUMBER := 0 ;
219
220 /** Check if any other SR refers given instance and user*/
221 CURSOR l_check_instance_ref(l_instance_id csm_item_instances_acc.instance_id%TYPE,
222 l_user_id csm_item_instances_acc.user_id%TYPE)
223 IS
224 SELECT 1
225 FROM csm_item_instances_acc a,
226 cs_incidents_all_b b,
227 csm_incidents_all_acc c
228 WHERE a.instance_id = l_instance_id
229 AND a.user_id = l_user_id
230 AND a.counter = 1
231 AND a.instance_id = b.customer_product_id
232 AND b.incident_id =c.incident_id
233 AND c.user_id = l_user_id;
234
235 l_inv_item_id NUMBER;
236 l_lst_vld_org_id NUMBER;
237
238 BEGIN
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_D ',
241 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
242
243 OPEN l_check_instance_ref(p_instance_id, p_user_id) ;
244 FETCH l_check_instance_REF INTO l_REF_EXISTS ;
245 IF l_check_instance_REF%NOTFOUND THEN
246 l_ref_exists := 0 ;
247 END IF ;
248 CLOSE l_check_instance_ref ;
249
250 IF L_REF_EXISTS <> 1 THEN
251 -- delete from csm_item_instances_acc
252 CSM_ACC_PKG.Delete_Acc
253 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
254 ,P_ACC_TABLE_NAME => g_acc_table_name1
255 ,P_PK1_NAME => g_pk1_name1
256 ,P_PK1_NUM_VALUE => p_instance_id
257 ,P_USER_ID => p_user_id
258 );
259 --Bug 6594511 :Delete the corresponding item from the acc table
260 SELECT cii.inventory_item_id,NVL(cii.LAST_VLD_ORGANIZATION_ID,cii.inv_master_organization_id)
261 INTO l_inv_item_id,l_lst_vld_org_id
262 FROM csi_item_instances cii
263 WHERE cii.instance_id = p_instance_id;
264
265 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_D(l_inv_item_id,
266 l_lst_vld_org_id,
267 p_user_id,
268 p_error_msg,
269 x_return_status);
270
271 END IF ;
272
273 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_D ',
274 'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_D',FND_LOG.LEVEL_PROCEDURE);
275
276 EXCEPTION
277 WHEN others THEN
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 p_error_msg := ' FAILED ITEM_INSTANCES_ACC_D INSTANCE_ID: ' || to_char(p_instance_id);
280 CSM_UTIL_PKG.LOG(p_error_msg,'CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_D',FND_LOG.LEVEL_EXCEPTION);
281 RAISE;
282 END ITEM_INSTANCES_ACC_D;
283
284 PROCEDURE REFRESH_INSTANCES_ACC (p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
285 IS
286 PRAGMA AUTONOMOUS_TRANSACTION;
287 l_last_run_date DATE;
288 l_sqlerrno varchar2(20);
289 l_sqlerrmsg varchar2(2000);
290 l_current_run_date DATE;
291 g_pub_item1 VARCHAR2(30);
292 g_pub_item2 VARCHAR2(30);
293
294 CURSOR l_last_run_date_csr
295 IS
296 SELECT nvl(last_run_date, (sysdate - 365*50))
297 FROM jtm_con_request_data
298 WHERE package_name = 'CSM_ITEM_INSTANCE_EVENT_PKG'
299 AND procedure_name = 'REFRESH_INSTANCES_ACC';
300
301 -- get expired instances
302 --Bug 5184532
303 CURSOR l_expiredinstances_del_csr
304 IS
305 SELECT acc.access_id,
306 acc.user_ID
307 --acc.INSTANCE_ID
308 FROM csm_item_instances_acc acc
309 , csi_item_instances cii
310 , csi_instance_statuses iis
311 , asg_user asg
312 WHERE cii.instance_id = acc.instance_id
313 AND cii.location_type_code = 'INVENTORY'
314 AND cii.instance_status_id = iis.instance_status_id
315 AND asg.user_id = asg.owner_id
316 AND asg.user_id = acc.user_id
317 AND ( NOT (SYSDATE BETWEEN NVL(cii.active_start_date,SYSDATE)
318 AND NVL(cii.active_end_date,SYSDATE))
319 OR (NVL(iis.terminated_flag,'N') = 'Y')
320 );
321
322 -- get instances that are not in users subinventory
323 CURSOR l_instances_del_csr
324 IS
325 SELECT acc.access_id,
326 acc.user_ID,
327 acc.INSTANCE_ID
328 FROM csm_item_instances_acc acc
329 , csi_item_instances cii
330 , asg_user asg
331 WHERE cii.instance_id = acc.instance_id
332 AND cii.location_type_code = 'INVENTORY'
333 AND asg.user_id = asg.owner_id
334 AND asg.user_id = acc.user_id
335 AND NOT EXISTS
336 (SELECT 1
337 FROM csm_mtl_onhand_qty_acc ohqacc
338 WHERE ohqacc.user_id = acc.user_id
339 AND ohqacc.inventory_item_id = cii.inventory_item_id
340 AND ohqacc.organization_id = cii.inv_organization_id
341 AND ohqacc.subinventory_code = cii.inv_subinventory_name
342 AND ((ohqacc.LOCATOR_ID IS NULL AND cii.INV_LOCATOR_ID IS NULL)
343 OR (ohqacc.LOCATOR_ID = cii.INV_LOCATOR_ID))
344 AND ((ohqacc.LOT_NUMBER IS NULL AND cii.LOT_NUMBER IS NULL)
345 OR (ohqacc.LOT_NUMBER = cii.LOT_NUMBER))
346 AND ((ohqacc.REVISION IS NULL AND cii.INVENTORY_REVISION IS NULL)
347 OR (ohqacc.REVISION = cii.INVENTORY_REVISION))
348 )
349 ;
350 --bug 5184539
351 CURSOR l_iteminstances_upd_csr(p_last_run_date DATE)
352 IS
353 SELECT /* index (acc CSM_ITEM_INSTANCES_ACC_U1) */
354 acc.user_id,
355 --acc.instance_id,
356 acc.access_id
357 FROM csm_item_instances_acc acc,
358 csi_item_instances cii,
359 asg_user asg
360 WHERE cii.instance_id = acc.instance_id
361 AND asg.user_id = asg.owner_id
362 AND asg.user_id = acc.user_id
363 AND asg.ENABLED = 'Y'
364 AND cii.last_update_date >= p_last_run_date;
365
366 -- get all the trackable items that exists in the csm_mtl_onhand_acc table
367 CURSOR l_ins_item_instances_csr
368 IS
369 SELECT CSM_ITEM_INSTANCES_ACC_S.NEXTVAL as access_id,
370 cqa.user_id,
371 cii.instance_id,
372 cqa.user_id,
373 cii.instance_id,
374 civ.version_label,
375 CIR.OBJECT_ID
376 FROM csm_mtl_onhand_qty_acc cqa
377 , csi_item_instances cii
378 , csi_instance_statuses iis
379 , asg_user asg
380 , csi_i_version_labels civ
381 , CSI_II_RELATIONSHIPS CIR
382 WHERE cii.inventory_item_id = cqa.inventory_item_id
383 AND cii.inv_organization_id = cqa.organization_id
384 AND cii.inv_subinventory_name = cqa.subinventory_code
385 AND ((cqa.LOCATOR_ID IS NULL AND cii.INV_LOCATOR_ID IS NULL)
386 OR (cqa.LOCATOR_ID = cii.INV_LOCATOR_ID))
387 AND ((cqa.LOT_NUMBER IS NULL AND cii.LOT_NUMBER IS NULL)
388 OR (cqa.LOT_NUMBER = cii.LOT_NUMBER))
389 AND ((cqa.REVISION IS NULL AND cii.INVENTORY_REVISION IS NULL)
390 OR (cqa.REVISION = cii.INVENTORY_REVISION))
391 AND cii.location_type_code = 'INVENTORY'
392 AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
393 AND cii.INSTANCE_STATUS_ID = iis.instance_status_id
394 AND NVL(iis.terminated_flag,'N') = 'N'
395 AND asg.user_id = asg.owner_id
396 AND asg.user_id = cqa.user_id
397 AND asg.ENABLED = 'Y'
398 AND cii.instance_id = civ.instance_id(+)
399 AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
400 AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
401 AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
402 AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
403 AND NOT EXISTS
404 ( SELECT 1
405 FROM csm_item_instances_acc cia
406 WHERE cia.user_id = cqa.user_id
407 AND cia.instance_id = cii.instance_id
408 );
409
410 -- delete relationships for instances that no longer belong to the user
411 CURSOR l_itemrelationships_del1_csr
412 IS
413 SELECT /*+ index(cii CSI_II_RELATIONSHIPS_U01) */acc.access_id,
414 acc.user_id
415 --, acc.relationship_id
416 FROM csm_ii_relationships_acc acc,
417 csi_ii_relationships cii
418 WHERE cii.relationship_id = acc.relationship_id
419 AND NOT EXISTS
420 (SELECT 1
421 FROM csm_item_instances_acc ins_acc
422 WHERE ins_acc.user_id = acc.user_id
423 AND (ins_acc.instance_id = cii.object_id OR ins_acc.instance_id = cii.subject_id)
424 );
425
426 -- delete relationships that are dropped or end-dated from the backend
427 CURSOR l_itemrelationships_del2_csr
428 IS
429 SELECT /*+ index(acc csm_ii_relationships_acc_u1) */acc.access_id,
430 acc.user_id
431 --,acc.relationship_id
432 FROM csm_ii_relationships_acc acc
433 WHERE NOT EXISTS
434 (SELECT 1
435 FROM csi_ii_relationships cii
436 WHERE cii.relationship_id = acc.relationship_id
437 AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
438 );
439
440 -- update existing relationships
441 CURSOR l_itemrelationships_upd_csr(p_last_run_date DATE)
442 IS
443 SELECT /* index (acc CSM_II_RELATIONSHIPS_ACC_U1) */
444 acc.user_id,
445 acc.access_id,
446 ii.subject_id,
447 ii.object_id
448 FROM csm_ii_relationships_acc acc,
449 csi_ii_relationships ii
450 WHERE ii.relationship_id = acc.relationship_id
451 AND ii.last_update_date >= p_last_run_date;
452
453 -- get new relationships for instances that the user has access to
454 --Bug 5184522
455 CURSOR l_itemrelationships_ins_csr(b_instance_id NUMBER,b_user_id NUMBER)
456 IS
457 SELECT DISTINCT cii.relationship_id
458 FROM csi_ii_relationships cii
459 WHERE cii.relationship_type_code = 'COMPONENT-OF'
460 AND (cii.object_id = b_instance_id OR cii.subject_id = b_instance_id)
461 AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
462 AND NOT EXISTS (SELECT 1 FROM CSM_II_RELATIONSHIPS_ACC ACC
463 WHERE ACC.user_id = b_useR_id
464 AND ACC.relationship_id = cii.relationship_id);
465
466
467 TYPE instance_idTab IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
468 TYPE rel_idTab IS TABLE OF csm_ii_relationships_acc.relationship_id%TYPE INDEX BY BINARY_INTEGER;
469 TYPE ver_lab_Tab IS TABLE OF csi_i_version_labels.version_label%TYPE INDEX BY BINARY_INTEGER;
470 l_rel_id_lst rel_idTab;
471 l_user_id_lst asg_download.user_list;
472 l_acc_id_lst asg_download.access_list;
473 l_instance_id_lst instance_idTab;
474 l_inst_id_lst_bkp instance_idTab;
475 l_user_id_lst_bkp asg_download.user_list;
476 l_dummy BOOLEAN;
477 l_ver_label_lst ver_lab_Tab;
478 l_parent_inst_id_lst instance_idTab;
479 BEGIN
480 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC',
481 'CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC',FND_LOG.LEVEL_PROCEDURE);
482
483 l_current_run_date := SYSDATE;
484 g_pub_item1 := 'CSF_M_ITEM_INSTANCES';
485 g_pub_item2 := 'CSF_M_II_RELATIONSHIPS';
486
487 -- get last conc program update date
488 OPEN l_last_run_date_csr;
489 FETCH l_last_run_date_csr INTO l_last_run_date;
490 CLOSE l_last_run_date_csr;
491
492 -- delete item instances that are expired
493 IF l_acc_id_lst.COUNT > 0 THEN
494 l_acc_id_lst.DELETE;
495 END IF;
496 IF l_user_id_lst.COUNT > 0 THEN
497 l_user_id_lst.DELETE;
498 END IF;
499
500 OPEN l_expiredinstances_del_csr;
501 LOOP
502 FETCH l_expiredinstances_del_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst LIMIT 500;
503 EXIT WHEN l_acc_id_lst.COUNT = 0;
504 -- post deletes to olite
505 -- do bulk makedirty
506 l_dummy := asg_download.mark_dirty(
507 P_PUB_ITEM => g_pub_item1
508 , p_accessList => l_acc_id_lst
509 , p_userid_list => l_user_id_lst
510 , p_dml_type => 'D'
511 , P_TIMESTAMP => l_current_run_date
512 );
513
514 -- do a bulk delete
515 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
516 DELETE FROM CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
517
518 COMMIT;--IB Deletes are commited
519
520 IF l_acc_id_lst.COUNT > 0 THEN
521 l_acc_id_lst.DELETE;
522 END IF;
523 IF l_user_id_lst.COUNT > 0 THEN
524 l_user_id_lst.DELETE;
525 END IF;
526 END LOOP;
527 CLOSE l_expiredinstances_del_csr;
528
529 -- delete item instances that are not in the user's subinventory
530 /* IF l_acc_id_lst.COUNT > 0 THEN
531 l_acc_id_lst.DELETE;
532 END IF;
533 IF l_user_id_lst.COUNT > 0 THEN
534 l_user_id_lst.DELETE;
535 END IF;
536 IF l_instance_id_lst.COUNT > 0 THEN
537 l_instance_id_lst.DELETE;
538 END IF;
539
540 OPEN l_instances_del_csr;
541 FETCH l_instances_del_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_instance_id_lst;
542 CLOSE l_instances_del_csr;
543
544 -- post deletes to olite
545 IF l_acc_id_lst.COUNT > 0 THEN
546 -- do bulk makedirty
547 l_dummy := asg_download.mark_dirty(
548 P_PUB_ITEM => g_pub_item1
549 , p_accessList => l_acc_id_lst
550 , p_userid_list => l_user_id_lst
551 , p_dml_type => 'D'
552 , P_TIMESTAMP => l_current_run_date
553 );
554
555 -- do a bulk delete
556 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
557 DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
558 END IF;
559 */
560
561 -- get the updates to item instances
562
563
564 OPEN l_iteminstances_upd_csr(l_last_run_date);
565 LOOP
566 FETCH l_iteminstances_upd_csr BULK COLLECT INTO l_user_id_lst, l_acc_id_lst LIMIT 1000;
567 EXIT WHEN l_acc_id_lst.COUNT = 0 ;
568 -- post updates to olite
569 -- do bulk makedirty
570 l_dummy := asg_download.mark_dirty(
571 P_PUB_ITEM => g_pub_item1
572 , p_accessList => l_acc_id_lst
573 , p_userid_list => l_user_id_lst
574 , p_dml_type => 'U'
575 , P_TIMESTAMP => l_current_run_date
576 );
577 COMMIT;--IB Updates are commited
578 IF l_acc_id_lst.COUNT > 0 THEN
579 l_acc_id_lst.DELETE;
580 END IF;
581 IF l_user_id_lst.COUNT > 0 THEN
582 l_user_id_lst.DELETE;
583 END IF;
584
585 END LOOP;
586 CLOSE l_iteminstances_upd_csr;
587
588 -- process inserts
589 IF l_acc_id_lst.COUNT > 0 THEN
590 l_acc_id_lst.DELETE;
591 END IF;
592 IF l_user_id_lst.COUNT > 0 THEN
593 l_user_id_lst.DELETE;
594 END IF;
595 IF l_instance_id_lst.COUNT > 0 THEN
596 l_instance_id_lst.DELETE;
597 END IF;
598 --Bug 5184522
599 IF l_inst_id_lst_bkp.COUNT > 0 THEN
600 l_inst_id_lst_bkp.DELETE;
601 END IF;
602 IF l_user_id_lst_bkp.COUNT > 0 THEN
603 l_user_id_lst_bkp.DELETE;
604 END IF;
605
606 IF l_ver_label_lst.COUNT > 0 THEN
607 l_ver_label_lst.DELETE;
608 END IF;
609
610 IF l_parent_inst_id_lst.COUNT > 0 THEN
611 l_parent_inst_id_lst.DELETE;
612 END IF;
613 --Inserting instances for the user
614 OPEN l_ins_item_instances_csr;
615 FETCH l_ins_item_instances_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_instance_id_lst, l_user_id_lst_bkp, l_inst_id_lst_bkp, l_ver_label_lst, l_parent_inst_id_lst;
616 CLOSE l_ins_item_instances_csr;
617
618 IF l_acc_id_lst.COUNT > 0 THEN
619 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
620 INSERT INTO CSM_ITEM_INSTANCES_ACC (ACCESS_ID, USER_ID, INSTANCE_ID,
621 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
622 LAST_UPDATE_LOGIN,VERSION_LABEL, PARENT_INSTANCE_ID)
623 VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_instance_id_lst(i),
624 1, 1, l_current_run_date,1,l_current_run_date,
625 1,l_ver_label_lst(i), l_parent_inst_id_lst(i));
626
627 -- do bulk makedirty
628 l_dummy := asg_download.mark_dirty(
629 P_PUB_ITEM => g_pub_item1
630 , p_accessList => l_acc_id_lst
631 , p_userid_list => l_user_id_lst
632 , p_dml_type => 'I'
633 , P_TIMESTAMP => l_current_run_date
634 );
635 --Bug 5184522
636 /* FOR I IN 1..l_instance_id_lst.COUNT
637 LOOP
638 l_inst_id_lst_bkp(I) :=l_instance_id_lst(I);
639 l_user_id_lst_bkp(I) :=l_user_id_lst(I);
640 END LOOP;*/
641 END IF;
642 COMMIT;--IB Inserts are commited
643
644 -- post deletes to relationships for instances that no longer belong to the user
645 IF l_acc_id_lst.COUNT > 0 THEN
646 l_acc_id_lst.DELETE;
647 END IF;
648 IF l_user_id_lst.COUNT > 0 THEN
649 l_user_id_lst.DELETE;
650 END IF;
651 IF l_rel_id_lst.COUNT > 0 THEN
652 l_rel_id_lst.DELETE;
653 END IF;
654
655 IF l_ver_label_lst.COUNT > 0 THEN
656 l_ver_label_lst.DELETE;
657 END IF;
658
659 IF l_parent_inst_id_lst.COUNT > 0 THEN
660 l_parent_inst_id_lst.DELETE;
661 END IF;
662
663 OPEN l_itemrelationships_del1_csr;
664 FETCH l_itemrelationships_del1_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst;
665 CLOSE l_itemrelationships_del1_csr;
666
667 -- post deletes to olite
668 IF l_acc_id_lst.COUNT > 0 THEN
669 -- do bulk makedirty
670 l_dummy := asg_download.mark_dirty(
671 P_PUB_ITEM => g_pub_item2
672 , p_accessList => l_acc_id_lst
673 , p_userid_list => l_user_id_lst
674 , p_dml_type => 'D'
675 , P_TIMESTAMP => l_current_run_date
676 );
677
678 -- do a bulk delete
679 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
680 DELETE CSM_II_RELATIONSHIPS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
681
682 END IF;
683
684 COMMIT;--IB Relation Deletes are commited
685
686 -- post delete for relationships that are dropped or end-dated from the backend
687 IF l_acc_id_lst.COUNT > 0 THEN
688 l_acc_id_lst.DELETE;
689 END IF;
690 IF l_user_id_lst.COUNT > 0 THEN
691 l_user_id_lst.DELETE;
692 END IF;
693
694 OPEN l_itemrelationships_del2_csr;
695 FETCH l_itemrelationships_del2_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst;
696 CLOSE l_itemrelationships_del2_csr;
697
698 -- post deletes to olite
699 IF l_acc_id_lst.COUNT > 0 THEN
700 -- do bulk makedirty
701 l_dummy := asg_download.mark_dirty(
702 P_PUB_ITEM => g_pub_item2
703 , p_accessList => l_acc_id_lst
704 , p_userid_list => l_user_id_lst
705 , p_dml_type => 'D'
706 , P_TIMESTAMP => l_current_run_date
707 );
708
709 -- do a bulk delete
710 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
711 DELETE CSM_II_RELATIONSHIPS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
712
713 END IF;
714 COMMIT;--IB Relation Deletes are commited
715
716 -- post updated to relationships
717 IF l_acc_id_lst.COUNT > 0 THEN
718 l_acc_id_lst.DELETE;
719 END IF;
720 IF l_user_id_lst.COUNT > 0 THEN
721 l_user_id_lst.DELETE;
722 END IF;
723 IF l_instance_id_lst.COUNT > 0 THEN
724 l_instance_id_lst.DELETE;
725 END IF;
726 IF l_parent_inst_id_lst.COUNT > 0 THEN
727 l_parent_inst_id_lst.DELETE;
728 END IF;
729 OPEN l_itemrelationships_upd_csr(l_last_run_date);
730 FETCH l_itemrelationships_upd_csr BULK COLLECT INTO l_user_id_lst, l_acc_id_lst, l_instance_id_lst, l_parent_inst_id_lst;
731 CLOSE l_itemrelationships_upd_csr;
732
733 -- post updates to olite
734 IF l_acc_id_lst.COUNT > 0 THEN
735 -- do bulk makedirty for relationship change
736 l_dummy := asg_download.mark_dirty(
737 P_PUB_ITEM => g_pub_item2
738 , p_accessList => l_acc_id_lst
739 , p_userid_list => l_user_id_lst
740 , p_dml_type => 'U'
741 , P_TIMESTAMP => l_current_run_date
742 );
743
744 IF l_acc_id_lst.COUNT > 0 THEN
745 l_acc_id_lst.DELETE;
746 END IF;
747
748 --If the relationship are updated then the instance should be updated with the correct parent
749 FORALL i in l_parent_inst_id_lst.FIRST..l_parent_inst_id_lst.LAST
750 UPDATE csm_item_instances_acc
751 SET PARENT_INSTANCE_ID = l_parent_inst_id_lst(i)
752 WHERE USER_ID = l_user_id_lst(i)
753 AND INSTANCE_ID = l_instance_id_lst(i)
754 RETURNING access_id BULK COLLECT INTO l_acc_id_lst ;
755
756 -- do bulk makedirty for Instances
757 l_dummy := asg_download.mark_dirty(
758 P_PUB_ITEM => g_pub_item1
759 , p_accessList => l_acc_id_lst
760 , p_userid_list => l_user_id_lst
761 , p_dml_type => 'U'
762 , P_TIMESTAMP => l_current_run_date
763 );
764 END IF;
765
766 COMMIT;--IB Relation Updates are commited
767
768 IF l_instance_id_lst.COUNT > 0 THEN
769 l_instance_id_lst.DELETE;
770 END IF;
771 IF l_parent_inst_id_lst.COUNT > 0 THEN
772 l_parent_inst_id_lst.DELETE;
773 END IF;
774
775 -- post inserts to olite
776 --Bug 5184522
777 FOR I in 1..l_inst_id_lst_bkp.COUNT
778 LOOP
779 l_acc_id_lst.delete;
780 l_rel_id_lst.DELETE;
781 l_user_id_lst.DELETE;
782
783 OPEN l_itemrelationships_ins_csr(l_inst_id_lst_bkp(I),l_user_id_lst_bkp(I));
784 FETCH l_itemrelationships_ins_csr BULK COLLECT INTO l_rel_id_lst;
785 CLOSE l_itemrelationships_ins_csr;
786
787 IF l_rel_id_lst.COUNT >0 THEN
788
789 FOR J IN 1..l_rel_id_lst.COUNT
790 LOOP
791 SELECT CSM_II_RELATIONSHIPS_ACC_S.NEXTVAL
792 INTO l_acc_id_lst(J)
793 FROM dual;
794 l_user_id_lst(J):=l_user_id_lst_bkp(I);
795 END LOOP;
796
797 FORALL j IN 1..l_rel_id_lst.COUNT
798 INSERT INTO CSM_II_RELATIONSHIPS_ACC (ACCESS_ID, USER_ID, RELATIONSHIP_ID,
799 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
800 VALUES (l_acc_id_lst(j), l_user_id_lst_bkp(I), l_rel_id_lst(j), 1, 1, l_current_run_date,1,l_current_run_date,1);
801
802 -- do bulk makedirty
803 l_dummy := asg_download.mark_dirty(
804 P_PUB_ITEM => g_pub_item2
805 , p_accessList => l_acc_id_lst
806 , p_userid_list => l_user_id_lst
807 , p_dml_type => 'I'
808 , P_TIMESTAMP => l_current_run_date
809 );
810 END IF;
811 END LOOP;
812
813
814 -- set the program update date in jtm_con_request_data to sysdate
815 UPDATE jtm_con_request_data
816 SET last_run_date = l_current_run_date
817 WHERE product_code = 'CSM'
818 AND package_name = 'CSM_ITEM_INSTANCE_EVENT_PKG'
819 AND procedure_name = 'REFRESH_INSTANCES_ACC';
820
821 COMMIT;
822
823 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC',
824 'CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC',FND_LOG.LEVEL_PROCEDURE);
825
826 p_status := 'FINE';
827 p_message := 'CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC Executed successfully';
828
829 EXCEPTION
830 WHEN others THEN
831 l_sqlerrno := to_char(SQLCODE);
832 l_sqlerrmsg := substr(SQLERRM, 1,2000);
833 p_status := 'ERROR';
834 p_message := 'Error in CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
835 ROLLBACK;
836 csm_util_pkg.log('CSM_ITEM_INSTANCE_EVENT_PKG.REFRESH_INSTANCES_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
837 END REFRESH_INSTANCES_ACC;
838
839 PROCEDURE GET_IB_AT_LOCATION(p_instance_id IN NUMBER, p_party_site_id IN NUMBER, p_party_id IN NUMBER,
840 p_location_id IN NUMBER, p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
841 IS
842 l_sqlerrno VARCHAR2(20);
843 l_sqlerrmsg VARCHAR2(4000);
844 l_error_msg VARCHAR2(4000);
845 l_return_status VARCHAR2(2000);
846 l_parent_instance_id csi_item_instances.instance_id%TYPE;
847 l_max_ib_at_location NUMBER;
848
849 CURSOR c_ib_parent_csr(p_instance_id IN number)
850 IS
851 SELECT object_id AS instance_id
852 FROM CSI_II_RELATIONSHIPS
853 WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
854 AND SUBJECT_ID = p_instance_id
855 AND SYSDATE BETWEEN nvl(active_start_date, SYSDATE)
856 AND nvl(active_end_date, SYSDATE)
857 ;
858
859 CURSOR c_existing_ib_at_location_csr (
860 p_user_id IN NUMBER,
861 p_party_site_id NUMBER,
862 p_location_id NUMBER,
863 p_party_id NUMBER,
864 p_instance_id NUMBER,
865 p_parent_instance_id NUMBER )
866 IS
867 SELECT acc.instance_id
868 FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii
869 WHERE acc.instance_id = cii.instance_id
870 AND acc.user_id = p_user_id
871 AND owner_party_id = p_party_id
872 AND ( ( cii.location_id = p_party_site_id
873 AND cii.location_type_code = 'HZ_PARTY_SITES'
874 ) OR
875 ( cii.location_id = p_location_id
876 AND cii.location_type_code = 'HZ_LOCATIONS'
877 )
878 )
879 AND acc.instance_id NOT IN
880 (
881 SELECT acc.instance_id
882 FROM CSM_ITEM_INSTANCES_ACC acc
883 WHERE acc.user_id = p_user_id
884 AND acc.instance_id IN (p_instance_id, p_parent_instance_id)
885 UNION
886 SELECT acc.instance_id
887 FROM CSM_ITEM_INSTANCES_ACC acc
888 WHERE acc.user_id = p_user_id
889 AND acc.instance_id IN
890 (
891 SELECT subject_id
892 FROM CSI_II_RELATIONSHIPS
893 START WITH object_id = p_instance_id
894 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
895 AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE)
896 AND NVL(active_end_date, SYSDATE)
897 CONNECT BY object_id = PRIOR subject_id
898 )
899 ) ;
900
901 CURSOR c_new_ib_at_location_csr (
902 p_user_id NUMBER,
903 p_party_site_id NUMBER,
904 p_location_id NUMBER,
905 p_party_id NUMBER )
906 IS
907 SELECT cii.instance_id
908 FROM CSI_ITEM_INSTANCES cii, MTL_SYSTEM_ITEMS_B si
909 WHERE si.inventory_item_id = cii.inventory_item_id
910 AND si.organization_id = NVL( cii.inv_organization_id,
911 cii.inv_master_organization_id )
912 AND cii.instance_id NOT IN
913 ( SELECT acc.instance_id FROM CSM_ITEM_INSTANCES_ACC acc
914 WHERE acc.user_id = p_user_id
915 )
916 AND owner_party_id = p_party_id
917 AND ( ( cii.location_id = p_party_site_id
918 AND cii.location_type_code = 'HZ_PARTY_SITES'
919 ) OR
920 ( cii.location_id = p_location_id
921 AND cii.location_type_code = 'HZ_LOCATIONS'
922 )
923 )
924 -- AND si.service_item_flag = 'N'
925 AND nvl(si.enabled_flag,'Y') = 'Y'
926 AND si.serv_req_enabled_code = 'E'
927 AND si.contract_item_type_code IS NULL
928 ;
929
930 BEGIN
931 CSM_UTIL_PKG.LOG('Entering GET_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
932 ' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
933
934 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
935 l_max_ib_at_location := NVL(csm_profile_pkg.get_max_ib_at_location(p_user_id),0);
936
937 -- get parent instance_id if exists
938 OPEN c_ib_parent_csr(p_instance_id);
939 FETCH c_ib_parent_csr INTO l_parent_instance_id;
940 CLOSE c_ib_parent_csr;
941
942 -- Increment counter for existing IB's
943 FOR c_exist_ib_items IN c_existing_ib_at_location_csr (
944 p_user_id,
945 p_party_site_id,
946 p_location_id,
947 p_party_id,
948 p_instance_id,
949 l_parent_instance_id )
950 LOOP
951 CSM_ACC_PKG.Insert_Acc
952 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
953 ,P_ACC_TABLE_NAME => g_acc_table_name1
954 ,P_SEQ_NAME => g_acc_sequence_name1
955 ,P_PK1_NAME => g_pk1_name1
956 ,P_PK1_NUM_VALUE => c_exist_ib_items.instance_id
957 ,P_USER_ID => p_user_id
958 );
959
960 csm_sr_event_pkg.g_ib_count := csm_sr_event_pkg.g_ib_count + 1;
961 END LOOP;
962
963 -- Greater than check for Profile IB count was reset to a lower value
964 IF csm_sr_event_pkg.g_ib_count >= l_max_ib_at_location THEN
965 RETURN;
966 ELSE
967 /** Insert For other IB's at location */
968 FOR c_ib_items IN c_new_ib_at_location_csr (
969 p_user_id, p_party_site_id , p_location_id, p_party_id )
970 LOOP
971
972 IF csm_sr_event_pkg.g_ib_count < l_max_ib_at_location
973 THEN
974 CSM_ACC_PKG.Insert_Acc
975 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
976 ,P_ACC_TABLE_NAME => g_acc_table_name1
977 ,P_SEQ_NAME => g_acc_sequence_name1
978 ,P_PK1_NAME => g_pk1_name1
979 ,P_PK1_NUM_VALUE => c_ib_items.instance_id
980 ,P_USER_ID => p_user_id
981 );
982
983 csm_sr_event_pkg.g_ib_count := csm_sr_event_pkg.g_ib_count + 1;
984 ELSE
985 EXIT;
986 END IF;
987
988 END LOOP;
989 END IF;
990
991 END IF;
992
993 CSM_UTIL_PKG.LOG('Leaving GET_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
994 ' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
995
996 EXCEPTION
997 WHEN OTHERS THEN
998 l_sqlerrno := to_char(SQLCODE);
999 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1000 l_error_msg := ' Exception in GET_IB_AT_LOCATION for instance_id: ' || p_instance_id || ' and party_site_id:'
1001 || to_char(p_party_site_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1002 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_EXCEPTION);
1003 END GET_IB_AT_LOCATION;
1004
1005 PROCEDURE SPAWN_COUNTERS_INS (p_instance_id IN NUMBER, p_user_id IN NUMBER)
1006 IS
1007 l_sqlerrno VARCHAR2(20);
1008 l_sqlerrmsg VARCHAR2(4000);
1009 l_error_msg VARCHAR2(4000);
1010 l_return_status VARCHAR2(2000);
1011
1012 CURSOR l_counter_id_csr ( p_instance_id NUMBER, p_user_id NUMBER)
1013 IS
1014 SELECT cntrs.counter_id
1015 FROM CS_CSI_COUNTER_GROUPS cntr_grps,
1016 CSI_COUNTERS_B cntrs,
1017 CSI_COUNTER_ASSOCIATIONS cas
1018 WHERE cntrs.counter_id = cas.counter_id
1019 AND cas.source_object_code = 'CP'
1020 AND cntrs.counter_type = 'REGULAR'
1021 AND cntr_grps.counter_group_id(+) = cntrs.group_id
1022 AND cas.source_object_id = p_instance_id
1023 -- get only records for the instance belonging to the user
1024 AND EXISTS (SELECT 1
1025 FROM csm_item_instances_acc acc
1026 WHERE acc.user_id = p_user_id
1027 AND acc.instance_id = cas.source_object_id) ;
1028
1029 BEGIN
1030 CSM_UTIL_PKG.LOG('Entering SPAWN_COUNTERS_INS for instance_id: ' || p_instance_id,
1031 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_INS',FND_LOG.LEVEL_PROCEDURE);
1032
1033 FOR r_counter_id_rec in l_counter_id_csr(p_instance_id, p_user_id) LOOP
1034 --- get the counter
1035 csm_counter_event_pkg.COUNTER_MDIRTY_I(r_counter_id_rec.counter_id, p_user_id, l_error_msg, l_return_status);
1036
1037 -- get the counter readings
1038 csm_counter_event_pkg.COUNTER_VALS_MAKE_DIRTY_I_GRP(r_counter_id_rec.counter_id, p_instance_id, p_user_id, l_error_msg, l_return_status);
1039
1040 END LOOP;
1041
1042 CSM_UTIL_PKG.LOG('Leaving SPAWN_COUNTERS_INS for instance_id: ' || p_instance_id,
1043 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_INS',FND_LOG.LEVEL_PROCEDURE);
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 l_sqlerrno := to_char(SQLCODE);
1047 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1048 l_error_msg := l_error_msg || '- Exception in SPAWN_COUNTERS_INS for instance_id: ' || p_instance_id
1049 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1050 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_INS',FND_LOG.LEVEL_EXCEPTION);
1051 RAISE;
1052 END SPAWN_COUNTERS_INS;
1053
1054 PROCEDURE DELETE_IB_AT_LOCATION(p_incident_id IN NUMBER, p_instance_id IN NUMBER, p_party_site_id IN NUMBER, p_party_id IN NUMBER,
1055 p_location_id IN NUMBER, p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
1056 IS
1057 l_sqlerrno VARCHAR2(20);
1058 l_sqlerrmsg VARCHAR2(4000);
1059 l_error_msg VARCHAR2(4000);
1060 l_return_status VARCHAR2(2000);
1061 l_parent_instance_id csi_item_instances.instance_id%TYPE;
1062
1063 -- the below query prevents deletion of the configuration of the current IB item
1064 -- however it needs further validation to correctly delete IB at location.
1065 CURSOR c_ib_at_location_csr (
1066 p_user_id NUMBER,
1067 p_party_site_id NUMBER,
1068 p_location_id NUMBER,
1069 p_party_id NUMBER,
1070 p_incident_id NUMBER ) IS
1071 SELECT acc.instance_id
1072 FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii,
1073 csm_incidents_all_acc iacc, cs_incidents_all_b cia
1074 WHERE acc.instance_id = cii.instance_id
1075 AND acc.user_id = p_user_id
1076 AND acc.counter <> 1 -- do not delete if there is just 1 instance of the IB item
1077 AND acc.instance_id <> p_instance_id
1078 AND owner_party_id = p_party_id
1079 AND ( ( cii.location_id = p_party_site_id
1080 AND cii.location_type_code = 'HZ_PARTY_SITES' )
1081 OR ( cii.location_id = p_location_id
1082 AND cii.location_type_code = 'HZ_LOCATIONS') )
1083 AND iacc.user_id = acc.user_id
1084 AND iacc.incident_id <> p_incident_id
1085 AND iacc.incident_id = cia.incident_id
1086 AND cia.customer_product_id <> acc.instance_id
1087 AND NOT EXISTS
1088 (SELECT 1
1089 FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
1090 START WITH CIRo.OBJECT_ID = p_instance_id
1091 AND CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1092 AND SYSDATE BETWEEN NVL(CIRo.active_start_date, SYSDATE)
1093 AND NVL(CIRo.active_end_date, SYSDATE)
1094 CONNECT BY CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
1095 ) CIR,
1096 CSI_ITEM_INSTANCES CII
1097 WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
1098 AND cii.instance_id = acc.instance_id
1099 AND SYSDATE BETWEEN NVL ( CII.ACTIVE_START_DATE , SYSDATE )
1100 AND NVL ( CII.ACTIVE_END_DATE , SYSDATE)
1101 );
1102
1103 BEGIN
1104 CSM_UTIL_PKG.LOG('Entering DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
1105 ' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
1106
1107 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1108 FOR c_ib_items IN c_ib_at_location_csr (
1109 p_user_id, p_party_site_id, p_location_id, p_party_id, p_incident_id )
1110 LOOP
1111 CSM_ACC_PKG.Delete_Acc
1112 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1113 ,P_ACC_TABLE_NAME => g_acc_table_name1
1114 ,P_PK1_NAME => g_pk1_name1
1115 ,P_PK1_NUM_VALUE => c_ib_items.instance_id
1116 ,P_USER_ID => p_user_id
1117 );
1118 END LOOP;
1119 END IF;
1120
1121 CSM_UTIL_PKG.LOG('Leaving DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
1122 ' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 l_sqlerrno := to_char(SQLCODE);
1126 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1127 l_error_msg := ' Exception in DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id || ' and party_site_id:'
1128 || to_char(p_party_site_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1129 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_AT_LOCATION',FND_LOG.LEVEL_EXCEPTION);
1130 END DELETE_IB_AT_LOCATION;
1131
1132 PROCEDURE SPAWN_COUNTERS_DEL (p_instance_id IN NUMBER, p_user_id IN NUMBER)
1133 IS
1134 l_sqlerrno VARCHAR2(20);
1135 l_sqlerrmsg VARCHAR2(4000);
1136 l_error_msg VARCHAR2(4000);
1137 l_return_status VARCHAR2(2000);
1138
1139 CURSOR l_counter_id_csr ( p_instance_id number,
1140 p_user_id number )
1141 IS
1142 SELECT cntrs.counter_id
1143 FROM CS_CSI_COUNTER_GROUPS cntr_grps,
1144 CSI_COUNTERS_B cntrs,
1145 CSI_COUNTER_ASSOCIATIONS cas
1146 WHERE cntrs.counter_id = cas.counter_id
1147 AND cas.source_object_code = 'CP'
1148 AND cntrs.counter_type = 'REGULAR'
1149 AND cntr_grps.counter_group_id(+) = cntrs.group_id
1150 AND cas.source_object_id = p_instance_id
1151 AND EXISTS (SELECT 1
1152 FROM CSM_COUNTERS_ACC acc
1153 WHERE acc.user_id = p_user_id
1154 AND acc.counter_id = cntrs.counter_id);
1155
1156 BEGIN
1157 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_DEL ',
1158 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_DEL',FND_LOG.LEVEL_PROCEDURE);
1159
1160 -- get all the counters for the instance
1161 FOR r_counter_id_rec in l_counter_id_csr(p_instance_id, p_user_id) LOOP
1162 --- drop the counter
1163 csm_counter_event_pkg.COUNTER_MDIRTY_D(r_counter_id_rec.counter_id, p_user_id, l_error_msg, l_return_status);
1164
1165 -- drop the counter readings
1166 csm_counter_event_pkg.COUNTER_VALS_MAKE_DIRTY_D_GRP(r_counter_id_rec.counter_id, p_instance_id, p_user_id, l_error_msg, l_return_status);
1167
1168 END LOOP;
1169
1170 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_DEL ',
1171 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_DEL',FND_LOG.LEVEL_PROCEDURE);
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 l_sqlerrno := to_char(SQLCODE);
1175 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1176 l_error_msg := l_error_msg || '- Exception in SPAWN_COUNTERS_DEL for instance_id: ' || p_instance_id
1177 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1178 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.SPAWN_COUNTERS_DEL',FND_LOG.LEVEL_EXCEPTION);
1179 RAISE;
1180 END SPAWN_COUNTERS_DEL;
1181
1182 PROCEDURE DELETE_IB_NOTIN_INV (p_inv_item_id IN NUMBER, p_org_id IN NUMBER, p_user_id IN NUMBER)
1183 IS
1184 l_sqlerrno VARCHAR2(20);
1185 l_sqlerrmsg VARCHAR2(4000);
1186 l_error_msg VARCHAR2(4000);
1187 l_dummy BOOLEAN;
1188
1189 TYPE instance_idTab IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
1190 l_instance_id_lst instance_idTab;
1191 l_user_id_lst asg_download.user_list;
1192 l_acc_id_lst asg_download.access_list;
1193
1194
1195 CURSOR l_instances_del_csr
1196 IS
1197 SELECT acc.access_id,
1198 acc.user_ID,
1199 acc.INSTANCE_ID
1200 FROM csm_item_instances_acc acc
1201 , csi_item_instances cii
1202 WHERE cii.instance_id = acc.instance_id
1203 AND cii.location_type_code = 'INVENTORY'
1204 AND cii.inventory_item_id = p_inv_item_id
1205 AND cii.inv_organization_id = p_org_id
1206 AND acc.user_id = p_user_id;
1207
1208 BEGIN
1209
1210 CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV ',
1211 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_PROCEDURE);
1212
1213 -- delete item instances that are not in the user's subinventory
1214 IF l_acc_id_lst.COUNT > 0 THEN
1215 l_acc_id_lst.DELETE;
1216 END IF;
1217 IF l_user_id_lst.COUNT > 0 THEN
1218 l_user_id_lst.DELETE;
1219 END IF;
1220 IF l_instance_id_lst.COUNT > 0 THEN
1221 l_instance_id_lst.DELETE;
1222 END IF;
1223
1224 OPEN l_instances_del_csr;
1225 FETCH l_instances_del_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_instance_id_lst;
1226 CLOSE l_instances_del_csr;
1227
1228 -- post deletes to olite
1229 IF l_acc_id_lst.COUNT > 0 THEN
1230 -- do bulk makedirty
1231 l_dummy := asg_download.mark_dirty(
1232 P_PUB_ITEM => 'CSF_M_ITEM_INSTANCES'
1233 , p_accessList => l_acc_id_lst
1234 , p_userid_list => l_user_id_lst
1235 , p_dml_type => 'D'
1236 , P_TIMESTAMP => sysdate
1237 );
1238
1239 -- do a bulk delete
1240 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
1241 DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
1242 END IF;
1243
1244 CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV ',
1245 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_PROCEDURE);
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 l_sqlerrno := to_char(SQLCODE);
1250 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1251 l_error_msg := l_error_msg || '- Exception in DELETE_IB_NOTIN_INV ' || ':'
1252 || l_sqlerrno || ':' || l_sqlerrmsg;
1253 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_EXCEPTION);
1254 RAISE;
1255
1256 END DELETE_IB_NOTIN_INV;
1257
1258 END CSM_ITEM_INSTANCE_EVENT_PKG;