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