[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_SYSTEM_ITEMS_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_SYSTEM_ITEMS_EVENT_PKG AS
2 /* $Header: csmesib.pls 120.2 2006/04/06 21:37:52 trajasek noship $ */
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_pub_item varchar2(30) := 'CSM_MTL_SYSTEM_ITEMS';
15
16 /*** Globals ***/
17 g_mtl_sys_items_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC';
18 g_mtl_sys_items_table_name CONSTANT VARCHAR2(30) := 'CS_MTL_SYSTEM_ITEMS';
19 g_mtl_sys_items_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC_S' ;
20 g_mtl_sys_items_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
21 g_mtl_sys_items_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22 g_mtl_sys_items_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
23 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SYSTEM_ITEMS');
24
25 PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
26 p_organization_id IN NUMBER,
27 p_category_set_id IN NUMBER,
28 p_category_id IN NUMBER,
29 p_last_run_date IN DATE,
30 p_changed IN VARCHAR2)
31 IS
32 l_run_date DATE;
33 l_sqlerrno VARCHAR2(20);
34 l_sqlerrmsg VARCHAR2(4000);
35 l_error_msg VARCHAR2(4000);
36 l_return_status VARCHAR2(2000);
37 l_stmt VARCHAR2(4000);
38 l_stmt1 VARCHAR2(4000);
39 l_markdirty BOOLEAN;
40
41
42 TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
43 TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
44
45 l_inventory_item_id_tbl inventory_item_id_tbl_typ;
46 l_organization_id_tbl organization_id_tbl_typ;
47 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
48 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
49
50 -- Both category and cat set are null
51 CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
52 b_changed VARCHAR2, b_last_run_date DATE)
53 IS
54 SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
55 FROM MTL_SYSTEM_ITEMS_B msi
56 WHERE msi.organization_id = b_organization_id
57 AND (b_changed = 'Y'
58 OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))
59 AND NOT EXISTS
60 ( SELECT 1
61 FROM csm_mtl_system_items_acc acc
62 WHERE user_id = b_user_id
63 AND acc.inventory_item_id = msi.inventory_item_id
64 AND acc.organization_id = msi.organization_id);
65
66 -- Category is not null and Cat set is null
67 CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
68 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
69 IS
70 SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
71 FROM mtl_system_items_b msi
72 WHERE NOT EXISTS
73 (SELECT 1
74 FROM csm_mtl_system_items_acc acc
75 WHERE user_id = b_user_id
76 AND acc.inventory_item_id = msi.inventory_item_id
77 AND acc.organization_id = msi.organization_id
78 )
79 AND msi.organization_id = b_organization_id
80 AND msi.inventory_item_id IN
81 (SELECT itemcat.inventory_item_id
82 FROM mtl_item_categories itemcat
83 WHERE itemcat.category_id = b_category_id
84 AND itemcat.organization_id = b_organization_id
85 AND (b_changed = 'Y'
86 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
87 )
88 );
89
90 -- Category is null and Cat Set is not null
91 CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
92 b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
93 IS
94 SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
95 FROM mtl_system_items_b msi
96 WHERE NOT EXISTS
97 (SELECT 1
98 FROM csm_mtl_system_items_acc acc
99 WHERE user_id = b_user_id
100 AND acc.inventory_item_id = msi.inventory_item_id
101 AND acc.organization_id = msi.organization_id
102 )
103 AND msi.organization_id = b_organization_id
104 AND msi.inventory_item_id IN
105 (SELECT itemcat.inventory_item_id
106 FROM mtl_item_categories itemcat
107 WHERE itemcat.category_set_id = b_category_set_id
108 AND itemcat.organization_id = b_organization_id
109 AND (b_changed = 'Y'
110 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
111 )
112 );
113
114 -- Both Category and Category set are not null
115 CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
116 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
117 IS
118 SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
119 FROM mtl_system_items_b msi
120 WHERE NOT EXISTS
121 (SELECT 1
122 FROM csm_mtl_system_items_acc acc
123 WHERE user_id = b_user_id
124 AND acc.inventory_item_id = msi.inventory_item_id
125 AND acc.organization_id = msi.organization_id
126 )
127 AND msi.organization_id = b_organization_id
128 AND msi.inventory_item_id IN
129 (SELECT itemcat.inventory_item_id
130 FROM mtl_item_categories itemcat
131 WHERE itemcat.category_set_id = b_category_set_id
132 AND itemcat.category_id = b_category_id
133 AND itemcat.organization_id = b_organization_id
134 AND (b_changed = 'Y'
135 OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
136 )
137 );
138
139 BEGIN
140 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
141 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
142
143 l_run_date := SYSDATE;
144
145 -- since profiles are changed get all inventory items that may already exist in
146 -- acc due to current SR's and increment counter
147 IF p_changed = 'Y' THEN
148
149 -- changed to dynamic sql to support either category or category set
150 l_stmt := 'UPDATE csm_mtl_system_items_acc';
151 l_stmt := l_stmt || ' SET counter = counter + 1';
152 l_stmt := l_stmt || ' , last_update_date = SYSDATE';
153 l_stmt := l_stmt || ' , last_updated_by = 1';
154 l_stmt := l_stmt || ' WHERE user_id = :1';
155 l_stmt := l_stmt || ' AND organization_id = :2';
156
157 IF (p_category_id IS NOT NULL) THEN
158 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
159 END IF;
160
161 IF (p_category_set_id IS NOT NULL) THEN
162 IF (l_stmt1 IS NOT NULL) THEN
163 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
164 || p_category_set_id;
165 ELSE
166 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
167 END IF;
168 END IF;
169
170 IF (l_stmt1 IS NOT NULL) THEN
171 l_stmt := l_stmt || ' AND ';
172 l_stmt := l_stmt || ' inventory_item_id IN';
173 l_stmt := l_stmt || ' (SELECT inventory_item_id';
174 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
175 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
176 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 )';
177 END IF;
178
179 IF l_stmt1 IS NOT NULL THEN
180 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id;
181 ELSE
182 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id;
183 END IF;
184
185 ELSE -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
186 -- changed to dynamic sql to support either category or category set
187 l_stmt := NULL;
188 l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
189 l_stmt := l_stmt || ' SET counter = counter - 1';
190 l_stmt := l_stmt || ' , last_update_date = SYSDATE';
191 l_stmt := l_stmt || ' , last_updated_by = 1';
192 l_stmt := l_stmt || ' WHERE user_id = :1';
193 l_stmt := l_stmt || ' AND organization_id = :2';
194
195 IF (p_category_id IS NOT NULL) THEN
196 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
197 END IF;
198
199 IF (p_category_set_id IS NOT NULL) THEN
200 IF (l_stmt1 IS NOT NULL) THEN
201 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
202 || p_category_set_id;
203 ELSE
204 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
205 END IF;
206 END IF;
207
208 IF (l_stmt1 IS NOT NULL) THEN
209 l_stmt := l_stmt || ' AND NOT EXISTS ';
210 l_stmt := l_stmt || ' (SELECT 1';
211 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
212 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
213 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 ';
214 l_stmt := l_stmt || ' AND acc.inventory_item_id = itemcat.inventory_item_id ';
215 l_stmt := l_stmt || ' UNION ';
216 l_stmt := l_stmt || ' SELECT 1';
217 l_stmt := l_stmt || ' FROM csm_incidents_all_acc sr_acc, ';
218 l_stmt := l_stmt || ' cs_incidents_all_b sr ';
219 l_stmt := l_stmt || ' WHERE sr_acc.incident_id = sr.incident_id ';
220 l_stmt := l_stmt || ' AND sr_acc.user_id = :4 ';
221 l_stmt := l_stmt || ' AND acc.inventory_item_id = sr.inventory_item_id )';
222
223 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id, p_user_id;
224
225 -- bulk collect all items eligible for delete
226 l_tab_access_id.DELETE;
227 l_tab_user_id.DELETE;
228
229 SELECT access_id, user_id
230 BULK COLLECT INTO l_tab_access_id, l_tab_user_id
231 FROM csm_mtl_system_items_acc acc
232 WHERE acc.counter = 0;
233
234 IF l_tab_access_id.COUNT > 0 THEN
235 -- do bulk makedirty
236 l_markdirty := asg_download.mark_dirty(
237 P_PUB_ITEM => g_pub_item
238 , p_accessList => l_tab_access_id
239 , p_userid_list => l_tab_user_id
240 , p_dml_type => 'D'
241 , P_TIMESTAMP => l_run_date
242 );
243
244 FORALL i IN 1..l_tab_access_id.COUNT
245 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
246 END IF;
247 ELSE
248 -- do not decrement as category/category set profile is not changed
249 l_stmt := NULL;
250 END IF;
251 END IF; -- end of if p_changed ='Y'
252
253 -- download new items if not existing
254 IF (p_category_id IS NULL AND p_category_set_id IS NULL) THEN
255 OPEN c_items(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
256 b_changed=>p_changed, b_last_run_date=>p_last_run_date);
257 LOOP
258 l_tab_access_id.DELETE;
259 l_inventory_item_id_tbl.DELETE;
260 l_organization_id_tbl.DELETE;
261 l_tab_user_id.DELETE;
262
263 FETCH c_items BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
264 l_organization_id_tbl, l_tab_user_id LIMIT 200;
265 EXIT WHEN l_tab_access_id.COUNT = 0;
266
267 IF l_tab_access_id.COUNT > 0 THEN
268 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
269 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
270 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
271
272 FORALL i IN 1..l_tab_access_id.COUNT
273 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
274 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
275 VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
276 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
277
278 /*** push to oLite using asg_download ***/
279 -- do bulk makedirty
280 l_markdirty := asg_download.mark_dirty(
281 P_PUB_ITEM => g_pub_item
282 , p_accessList => l_tab_access_id
283 , p_userid_list => l_tab_user_id
284 , p_dml_type => 'I'
285 , P_TIMESTAMP => l_run_date
286 );
287
288 END IF; -- end of l_tab_access_id.count > 0
289
290 END LOOP;
291 CLOSE c_items;
292
293 -- Category is not null and Cat set is null
294 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NULL) THEN
295 OPEN c_items_Cat(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
296 b_changed=>p_changed, b_last_run_date=>p_last_run_date,
297 b_category_id=>p_category_id);
298 LOOP
299 l_tab_access_id.DELETE;
300 l_inventory_item_id_tbl.DELETE;
301 l_organization_id_tbl.DELETE;
302 l_tab_user_id.DELETE;
303
304 FETCH c_items_Cat BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
305 l_organization_id_tbl, l_tab_user_id LIMIT 200;
306 EXIT WHEN l_tab_access_id.COUNT = 0;
307
308 IF l_tab_access_id.COUNT > 0 THEN
309 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
310 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
311 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
312
313 FORALL i IN 1..l_tab_access_id.COUNT
314 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
315 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
319 /*** push to oLite using asg_download ***/
316 VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
317 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
318
320 -- do bulk makedirty
321 l_markdirty := asg_download.mark_dirty(
322 P_PUB_ITEM => g_pub_item
323 , p_accessList => l_tab_access_id
324 , p_userid_list => l_tab_user_id
325 , p_dml_type => 'I'
326 , P_TIMESTAMP => l_run_date
327 );
328
329 END IF; -- end of l_tab_access_id.count > 0
330
331 END LOOP;
332 CLOSE c_items_Cat;
333
334 -- Category is null and Cat Set is not null
335 ELSIF (p_category_id IS NULL AND p_category_set_id IS NOT NULL) THEN
336 OPEN c_items_Cat_Set(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
337 b_changed=>p_changed, b_last_run_date=>p_last_run_date,
338 b_category_set_id=>p_category_set_id);
339 LOOP
340 l_tab_access_id.DELETE;
341 l_inventory_item_id_tbl.DELETE;
342 l_organization_id_tbl.DELETE;
343 l_tab_user_id.DELETE;
344
345 FETCH c_items_Cat_Set BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
346 l_organization_id_tbl, l_tab_user_id LIMIT 200;
347 EXIT WHEN l_tab_access_id.COUNT = 0;
348
349 IF l_tab_access_id.COUNT > 0 THEN
350 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
351 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
352 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
353
354 FORALL i IN 1..l_tab_access_id.COUNT
355 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
356 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
357 VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
358 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
359
360 /*** push to oLite using asg_download ***/
361 -- do bulk makedirty
362 l_markdirty := asg_download.mark_dirty(
363 P_PUB_ITEM => g_pub_item
364 , p_accessList => l_tab_access_id
365 , p_userid_list => l_tab_user_id
366 , p_dml_type => 'I'
367 , P_TIMESTAMP => l_run_date
368 );
369
370 END IF; -- end of l_tab_access_id.count > 0
371
372 END LOOP;
373 CLOSE c_items_Cat_Set;
374
375 -- Both Category and Category set are not null
376 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NOT NULL) THEN
377 OPEN c_items_Cat_Set_Cat(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
378 b_changed=>p_changed, b_last_run_date=>p_last_run_date,
379 b_category_id=>p_category_id, b_category_set_id=>p_category_set_id);
380 LOOP
381 l_tab_access_id.DELETE;
382 l_inventory_item_id_tbl.DELETE;
383 l_organization_id_tbl.DELETE;
384 l_tab_user_id.DELETE;
385
386 FETCH c_items_Cat_Set_Cat BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
387 l_organization_id_tbl, l_tab_user_id LIMIT 200;
388 EXIT WHEN l_tab_access_id.COUNT = 0;
389
390 IF l_tab_access_id.COUNT > 0 THEN
391 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
392 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
393 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
394
395 FORALL i IN 1..l_tab_access_id.COUNT
396 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
397 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
398 VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
399 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
400
401 /*** push to oLite using asg_download ***/
402 -- do bulk makedirty
403 l_markdirty := asg_download.mark_dirty(
404 P_PUB_ITEM => g_pub_item
405 , p_accessList => l_tab_access_id
406 , p_userid_list => l_tab_user_id
407 , p_dml_type => 'I'
408 , P_TIMESTAMP => l_run_date
409 );
410
411 END IF; -- end of l_tab_access_id.count > 0
412
413 END LOOP;
414 CLOSE c_items_Cat_Set_Cat;
415
416 END IF;
417
418 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
419 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
420 EXCEPTION
421 WHEN others THEN
422 l_sqlerrno := TO_CHAR(SQLCODE);
423 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
427 RAISE;
424 l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
425 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
426 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
428 END INSERT_MTL_SYSTEM_ITEMS;
429
430 PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
431 IS
432 l_sqlerrno VARCHAR2(20);
433 l_sqlerrmsg VARCHAR2(4000);
434 l_error_msg VARCHAR2(4000);
435 l_return_status VARCHAR2(2000);
436 l_run_date DATE;
437 l_tab_access_id asg_download.access_list;
438 l_tab_user_id asg_download.user_list;
439 l_markdirty BOOLEAN;
440 l_max_last_update_date_b DATE;
441 l_max_last_update_date_tl DATE;
442
443 CURSOR c_changed( b_last_date DATE)
444 IS
445 SELECT /*+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) */
446 acc.access_id, acc.user_id
447 FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
448 WHERE msi.inventory_item_id = acc.inventory_item_id
449 AND msi.organization_id = acc.organization_id
450 AND (msi.last_update_date >= b_last_date);
451
452 BEGIN
453 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
454 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
455
456 l_run_date := SYSDATE;
457
458 /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
459 /* , MTL_SYSTEM_ITEMS_TL which were custom created */
460 SELECT MAX(last_update_date) INTO l_max_last_update_date_b
461 FROM mtl_system_items_b;
462 IF( l_max_last_update_date_b < p_last_run_date) THEN
463 SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
464 FROM mtl_system_items_tl;
465 IF(l_max_last_update_date_tl < p_last_run_date) THEN
466 -- No updates
467 CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
468 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
469 RETURN;
470 END IF;
471 END IF;
472
473 OPEN c_changed( p_last_run_date);
474 LOOP
475 l_tab_access_id.DELETE;
476 l_tab_user_id.DELETE;
477
478 FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 200;
479 EXIT WHEN l_tab_access_id.COUNT = 0;
480
481 IF l_tab_access_id.COUNT > 0 THEN
482 CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
483 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
484
485 -- do bulk makedirty
486 l_markdirty := asg_download.mark_dirty(
487 P_PUB_ITEM => g_pub_item
488 , p_accessList => l_tab_access_id
489 , p_userid_list => l_tab_user_id
490 , p_dml_type => 'U'
491 , P_TIMESTAMP => l_run_date
492 );
493 END IF;
494 END LOOP;
495 CLOSE c_changed;
496
497 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
498 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
499 EXCEPTION
500 WHEN OTHERS THEN
501 l_sqlerrno := TO_CHAR(SQLCODE);
502 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
503 l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
504 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
505 RAISE;
506 END UPDATE_MTL_SYSTEM_ITEMS;
507
508 PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
509 p_organization_id IN NUMBER,
510 p_category_set_id IN NUMBER,
511 p_category_id IN NUMBER)
512 IS
513 l_sqlerrno VARCHAR2(20);
514 l_sqlerrmsg VARCHAR2(4000);
515 l_error_msg VARCHAR2(4000);
516 l_return_status VARCHAR2(2000);
517 l_stmt VARCHAR2(4000);
518 l_stmt1 VARCHAR2(4000);
519 l_markdirty BOOLEAN;
520 l_run_date DATE;
521 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
522 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
523
524 BEGIN
525 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
526 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
527
528 l_run_date := SYSDATE;
529
530 l_tab_access_id.DELETE;
531
532 l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
533 l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
534 l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
535 l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
536 l_stmt := l_stmt || ' WHERE USER_ID = :1';
537 l_stmt := l_stmt || ' AND organization_id = :2';
538
539 IF (p_category_id IS NOT NULL) THEN
540 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
541 END IF;
542
543 IF (p_category_set_id IS NOT NULL) THEN
544 IF (l_stmt1 IS NOT NULL) THEN
545 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
546 || p_category_set_id;
547 ELSE
551
548 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
549 END IF;
550 END IF;
552 IF (l_stmt1 IS NOT NULL) THEN
553 l_stmt := l_stmt || ' AND EXISTS (';
554 l_stmt := l_stmt || ' SELECT 1 ';
555 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
556 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
557 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 ';
558 l_stmt := l_stmt || ' AND acc.inventory_item_id = itemcat.inventory_item_id )';
559 END IF;
560
561 IF (l_stmt1 IS NOT NULL) THEN
562 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id;
563 ELSE
564 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id;
565 END IF;
566
567 -- bulk collect all items eligible for delete
568 l_tab_access_id.DELETE;
569 l_tab_user_id.DELETE;
570
571 SELECT access_id, user_id
572 BULK COLLECT INTO l_tab_access_id, l_tab_user_id
573 FROM csm_mtl_system_items_acc acc
574 WHERE acc.counter = 0;
575
576 IF l_tab_access_id.COUNT > 0 THEN
577 -- do bulk makedirty
578 l_markdirty := asg_download.mark_dirty(
579 P_PUB_ITEM => g_pub_item
580 , p_accessList => l_tab_access_id
581 , p_userid_list => l_tab_user_id
582 , p_dml_type => 'D'
583 , P_TIMESTAMP => l_run_date
584 );
585
586 FORALL i IN 1..l_tab_access_id.COUNT
587 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
588 END IF;
589
590 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
591 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
592 EXCEPTION
593 WHEN others THEN
594 l_sqlerrno := TO_CHAR(SQLCODE);
595 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
596 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
597 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
598 RAISE;
599 END DELETE_MTL_SYSTEM_ITEMS;
600
601 PROCEDURE concurrent_process_user(p_user_id IN NUMBER,
602 p_organization_id IN NUMBER,
603 p_category_set_id IN NUMBER,
604 p_category_id IN NUMBER,
605 p_last_run_date IN DATE)
606 IS
607 l_sqlerrno VARCHAR2(20);
608 l_sqlerrmsg VARCHAR2(4000);
609 l_error_msg VARCHAR2(4000);
610 l_return_status VARCHAR2(2000);
611 l_pre_cat_filter BOOLEAN; -- TRUE when category filter was active previously
612 l_post_cat_filter BOOLEAN; -- TRUE when category filter is active now
613 l_cat_filter_changed BOOLEAN; -- TRUE when category filter changed
614
615 CURSOR c_org(b_user_id NUMBER)
616 IS
617 SELECT organization_id, category_set_id, category_id
618 FROM csm_user_inventory_org
619 WHERE user_id = b_user_id
620 FOR UPDATE;
621
622 r_org c_org%ROWTYPE;
623
624 BEGIN
625 CSM_UTIL_PKG.LOG('Entering CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
626 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
627
628 /* Get old profile settings */
629 OPEN c_org( p_user_id );
630 FETCH c_org INTO r_org;
631 IF c_org%NOTFOUND THEN -- should not occur
632 CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
633 || ' - Inserting all mtl_system_items',
634 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
635 get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
636 p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
637
638 INSERT INTO csm_user_inventory_org (
639 user_id, organization_id, last_update_date, last_updated_by,
640 creation_date, created_by, category_set_id, category_id )
641 VALUES (
642 p_user_id, p_organization_id, SYSDATE, 1, SYSDATE, 1,
643 p_category_set_id, p_category_id );
644
645 ELSE
646 l_pre_cat_filter := FALSE;
647 l_post_cat_filter := FALSE;
648
649 IF (( r_org.category_set_id IS NOT NULL ) OR
650 ( r_org.category_id IS NOT NULL)) THEN
651 l_pre_cat_filter := TRUE;
652 END IF;
653
654 IF (( p_category_set_id IS NOT NULL ) OR
655 ( p_category_id IS NOT NULL)) THEN
656 l_post_cat_filter := TRUE;
657 END IF;
658
659 /*** did category filter change from active -> inactive or vice versa ***/
660 l_cat_filter_changed := FALSE;
661 IF l_pre_cat_filter <> l_post_cat_filter THEN
662 /*** yes -> set boolean ***/
663 l_cat_filter_changed := TRUE;
664 ELSE
665 /*** no -> is filter active ***/
666 IF l_post_cat_filter THEN
667 /*** yes -> did category or category set change? ***/
668 IF NVL(r_org.category_set_id, 0) <> NVL(p_category_set_id, 0)
669 OR NVL(r_org.category_id, 0) <> NVL(p_category_id, 0) THEN
670 l_cat_filter_changed := TRUE;
674
671 END IF;
672 END IF;
673 END IF;
675 IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id, -1)
676 OR l_cat_filter_changed THEN
677 CSM_UTIL_PKG.LOG('Deleting records for old profile settings for user_id: ' || TO_CHAR(p_user_id),
678 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
679
680 -- delete labor/expense items only and then re-insert for the new orgif org changes
681 IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id,-1) THEN
682 csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,
683 p_organization_id=>r_org.organization_id);
684
685 -- download new labor/expense items for the new org
686 csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
687 END IF;
688
689 delete_mtl_system_items(p_user_id=>p_user_id,
690 p_organization_id=>r_org.organization_id,
691 p_category_set_id=>r_org.category_set_id,
692 p_category_id=>r_org.category_id);
693
694 CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
695 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
696
697 insert_mtl_system_items(p_user_id=>p_user_id,
698 p_organization_id=>p_organization_id,
699 p_category_set_id=>p_category_set_id,
700 p_category_id=>p_category_id,
701 p_last_run_date=>NULL,
702 p_changed=>'Y');
703
704 CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
705 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
706
707 UPDATE csm_user_inventory_org
708 SET organization_id = p_organization_id
709 , category_set_id = p_category_set_id
710 , category_id = p_category_id
711 , last_update_date = SYSDATE
712 WHERE CURRENT OF c_org;
713
714 ELSE
715 -- profiles are the same
716 -- get any new changes
717 CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
718 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
719
720 insert_mtl_system_items(p_user_id=>p_user_id,
721 p_organization_id=>p_organization_id,
722 p_category_set_id=>p_category_set_id,
723 p_category_id=>p_category_id,
724 p_last_run_date=>p_last_run_date,
725 p_changed=>'N');
726 END IF;
727 END IF;
728 CLOSE c_org;
729
730 CSM_UTIL_PKG.LOG('Leaving CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
731 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
732 EXCEPTION
733 WHEN OTHERS THEN
734 l_sqlerrno := TO_CHAR(SQLCODE);
735 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
736 l_error_msg := ' Exception in concurrent_process_user for user_id :'
737 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
738 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);
739 RAISE;
740 END CONCURRENT_PROCESS_USER;
741
742 PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,
743 p_message OUT NOCOPY VARCHAR2)
744 IS
745 PRAGMA AUTONOMOUS_TRANSACTION;
746 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
747 l_all_omfs_user_list asg_download.user_list;
748 l_null_omfs_user_list asg_download.user_list;
749 l_user_id fnd_user.user_id%TYPE;
750 l_user_organization_id mtl_system_items.organization_id%TYPE;
751 l_user_category_set_id mtl_category_sets.category_set_id%TYPE;
752 l_user_category_id mtl_categories.category_id%TYPE;
753 l_run_date DATE;
754 l_sqlerrno VARCHAR2(20);
755 l_sqlerrmsg VARCHAR2(2000);
756
757 CURSOR l_last_run_date_csr
758 IS
759 SELECT NVL(last_run_date, TO_DATE('1','J'))
760 FROM jtm_con_request_data
761 WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
762 AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
763
764 BEGIN
765 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
766 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
767
768 l_run_date := SYSDATE;
769
770 -- get last conc program update date
771 OPEN l_last_run_date_csr;
772 FETCH l_last_run_date_csr INTO l_prog_update_date;
773 CLOSE l_last_run_date_csr;
774
775 -- do an update for existing records
776 update_mtl_system_items(p_last_run_date => l_prog_update_date);
777 COMMIT;
778
779 -- get user list of all omfs users
780 l_all_omfs_user_list := l_null_omfs_user_list;
781 l_all_omfs_user_list := csm_util_pkg.get_all_omfs_palm_user_list;
785 l_user_organization_id := csm_profile_pkg.get_organization_id(l_user_id);
782
783 FOR i IN 1..l_all_omfs_user_list.COUNT LOOP
784 l_user_id := l_all_omfs_user_list(i);
786 l_user_category_set_id := csm_profile_pkg.get_category_set_id(l_user_id);
787 l_user_category_id := csm_profile_pkg.get_category_id(l_user_id);
788
789 concurrent_process_user(p_user_id=>l_user_id,
790 p_organization_id=>l_user_organization_id,
791 p_category_set_id=>l_user_category_set_id,
792 p_category_id=>l_user_category_id,
793 p_last_run_date=>l_prog_update_date);
794
795 END LOOP;
796
797 -- update last_run_date
798 UPDATE jtm_con_request_data
799 SET last_run_date = l_run_date
800 WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
801 AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
802
803 COMMIT;
804
805 p_status := 'FINE';
806 p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
807
808 CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
809 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
810 EXCEPTION
811 WHEN others THEN
812 l_sqlerrno := TO_CHAR(SQLCODE);
813 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
814 p_status := 'ERROR';
815 p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
816 ROLLBACK;
817 csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
818 END REFRESH_MTL_SYSTEM_ITEMS_ACC;
819
820 PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
821 p_category_set_id IN NUMBER, p_category_id IN NUMBER)
822 IS
823 l_sqlerrno VARCHAR2(20);
824 l_sqlerrmsg VARCHAR2(4000);
825 l_error_msg VARCHAR2(4000);
826 l_return_status VARCHAR2(2000);
827
828 BEGIN
829 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
830 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
831
832 insert_mtl_system_items(p_user_id=>p_user_id,
833 p_organization_id=>p_organization_id,
834 p_category_set_id=>p_category_set_id,
835 p_category_id=>p_category_id,
836 p_last_run_date=>NULL,
837 p_changed=>'N'); -- new user, no profiles are changed
838
839 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
840 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
841
842 EXCEPTION
843 WHEN others THEN
844 l_sqlerrno := TO_CHAR(SQLCODE);
845 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
846 l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
847 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
848 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
849 RAISE;
850 END get_new_user_mtl_system_items;
851
852 PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
853 p_organization_id IN NUMBER,
854 p_user_id IN NUMBER,
855 p_error_msg OUT NOCOPY VARCHAR2,
856 x_return_status IN OUT NOCOPY VARCHAR2)
857 IS
858 BEGIN
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
861 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
862
863 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
864 CSM_ACC_PKG.Insert_Acc
865 ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
866 ,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
867 ,P_SEQ_NAME => g_mtl_sys_items_seq_name
868 ,P_PK1_NAME => g_mtl_sys_items_pk1_name
869 ,P_PK1_NUM_VALUE => p_inventory_item_id
870 ,P_PK2_NAME => g_mtl_sys_items_pk2_name
871 ,P_PK2_NUM_VALUE => p_organization_id
872 ,P_USER_ID => p_user_id
873 );
874
875 END IF;
876
877 p_error_msg := 'SUCCESS';
878 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
879 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
880 EXCEPTION
881 WHEN others THEN
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
884 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
885 RAISE;
886 END MTL_SYSTEM_ITEMS_ACC_I;
887
888 PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
889 p_organization_id IN NUMBER,
890 p_user_id IN NUMBER,
891 p_error_msg OUT NOCOPY VARCHAR2,
892 x_return_status IN OUT NOCOPY VARCHAR2)
893 IS
894 BEGIN
895 x_return_status := FND_API.G_RET_STS_SUCCESS;
896 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
897 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
898
899 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
900 CSM_ACC_PKG.Delete_Acc
901 ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
902 ,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
903 ,P_PK1_NAME => g_mtl_sys_items_pk1_name
904 ,P_PK1_NUM_VALUE => p_inventory_item_id
905 ,P_PK2_NAME => g_mtl_sys_items_pk2_name
906 ,P_PK2_NUM_VALUE => p_organization_id
907 ,P_USER_ID => p_user_id
908 );
909 END IF;
910
911 p_error_msg := 'SUCCESS';
912 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
913 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
914 EXCEPTION
915 WHEN others THEN
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
918 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
919 RAISE;
920 END MTL_SYSTEM_ITEMS_ACC_D;
921
922 END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;