[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.3.12020000.2 2013/04/09 10:59:05 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_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 g_download_inv_flag VARCHAR2(10) := NULL;
25
26 PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
27 p_organization_id IN NUMBER,
28 p_category_set_id IN NUMBER,
29 p_category_id IN NUMBER,
30 p_last_run_date IN DATE,
31 p_changed IN VARCHAR2)
32 IS
33 l_run_date DATE;
34 l_sqlerrno VARCHAR2(20);
35 l_sqlerrmsg VARCHAR2(4000);
36 l_error_msg VARCHAR2(4000);
37 l_return_status VARCHAR2(2000);
38 l_stmt VARCHAR2(4000);
39 l_stmt1 VARCHAR2(4000);
40 l_markdirty BOOLEAN;
41
42
43 TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
44 TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
45
46 l_inventory_item_id_tbl inventory_item_id_tbl_typ;
47 l_organization_id_tbl organization_id_tbl_typ;
48 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
49 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
50
51 -- Both category and cat set are null
52 CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
53 b_changed VARCHAR2, b_last_run_date DATE)
54 IS
55 SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
56 FROM MTL_SYSTEM_ITEMS_B msi
57 WHERE msi.organization_id = b_organization_id
58 AND NOT EXISTS
59 ( SELECT 1
60 FROM csm_mtl_system_items_acc acc
61 WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
62 AND acc.inventory_item_id = msi.inventory_item_id
63 AND acc.organization_id = msi.organization_id);
64
65 -- Category is not null and Cat set is null
66 CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
67 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
68 IS
69 SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
70 itemcat.organization_id, b_user_id
71 FROM mtl_item_categories itemcat
72 WHERE itemcat.category_id = b_category_id
73 AND itemcat.organization_id = b_organization_id
74 AND NOT EXISTS
75 (SELECT 1
76 FROM csm_mtl_system_items_acc acc
77 WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
78 AND acc.inventory_item_id = itemcat.inventory_item_id
79 AND acc.organization_id = itemcat.organization_id
80 );
81
82 -- Category is null and Cat Set is not null
83 CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
84 b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
85 IS
86 SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
87 itemcat.organization_id, b_user_id
88 FROM mtl_item_categories itemcat
89 WHERE itemcat.category_set_id = b_category_set_id
90 AND itemcat.organization_id = b_organization_id
91 AND NOT EXISTS
92 (SELECT 1
93 FROM csm_mtl_system_items_acc acc
94 WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
95 AND acc.inventory_item_id = itemcat.inventory_item_id
96 AND acc.organization_id = itemcat.organization_id
97 );
98
99 -- Both Category and Category set are not null
100 CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
101 b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
102 IS
103 SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
104 itemcat.organization_id, b_user_id
105 FROM mtl_item_categories itemcat
106 WHERE itemcat.category_set_id = b_category_set_id
107 AND itemcat.category_id = b_category_id
108 AND itemcat.organization_id = b_organization_id
109 AND NOT EXISTS
110 (SELECT 1
111 FROM csm_mtl_system_items_acc acc
112 WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
113 AND acc.inventory_item_id = itemcat.inventory_item_id
114 AND acc.organization_id = itemcat.organization_id
115 );
116
117 CURSOR c_check_org(b_org_id NUMBER, b_user_id NUMBER)
118 Is
119 SELECT 'Y' FROM CSM_USER_INVENTORY_ORG
120 WHERE ORGANIZATION_ID = b_org_id
121 AND CATEGORY_SET_ID IS NULL
122 AND CATEGORY_ID IS NULL
123 AND TYPE = 'C'
124 AND USER_ID NOT IN(b_user_id);
125
126 CURSOR c_get_all_items_acc (b_organization_id NUMBER, b_USER_ID NUMBER )
127 IS
128 SELECT ACCESS_ID,b_USER_ID
129 FROM csm_mtl_system_items_acc acc
130 WHERE acc.organization_id = b_organization_id
131 AND acc.USER_ID = -1
132 AND NOT EXISTS (SELECT 1 FROM
133 csm_mtl_system_items_acc acci
134 WHERE acci.organization_id = b_organization_id
135 AND acci.USER_ID = b_USER_ID);
136
137 ---items to download for insert
138 CURSOR c_all_items_for_org (b_organization_id NUMBER)
139 IS
140 SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id, organization_id
141 FROM MTL_SYSTEM_ITEMS_B msi
142 WHERE msi.organization_id = b_organization_id;
143
144
145 --csm_mtl_system_items_acc_s.NEXTVAL,
146
147 l_check_org VARCHAR2(10) := 'N';
148 l_profile_value VARCHAR2(10);
149 l_number NUMBER := -1;
150 BEGIN
151 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
152 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
153
154 l_run_date := SYSDATE;
155 --GEt the value for CSM: Download Organization Level Inventory
156 l_profile_value := NVL(g_download_inv_flag,'N');
157
158 IF l_profile_value = 'Y' THEN
159
160 IF p_changed = 'Y' OR p_changed = 'W' THEN --W New user
161
162 OPEN c_check_org(p_organization_id, p_user_id);
163 FETCH c_check_org into l_check_org;
164 CLOSE c_check_org;
165
166 --Check if org details are already inserted for some user.
167 IF l_check_org = 'Y' THEN
168
169 OPEN c_get_all_items_acc(p_organization_id,p_user_id);
170 LOOP
171 l_tab_access_id.DELETE;
172 l_tab_user_id.DELETE;
173 FETCH c_get_all_items_acc BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
174 EXIT WHEN l_tab_access_id.COUNT =0;
175
176 l_markdirty := asg_download.mark_dirty(
177 P_PUB_ITEM => g_pub_item
178 , p_accessList => l_tab_access_id
179 , p_userid_list => l_tab_user_id
180 , p_dml_type => 'I'
181 , P_TIMESTAMP => l_run_date
182 );
183
184 END LOOP;
185 CLOSE c_get_all_items_acc;
186 ELSE--IF Org Items are not present then insert it freshly.
187
188 --Do a direct into into the ACC table for user -1
189 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
190 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
191 SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,
192 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
193 FROM MTL_SYSTEM_ITEMS_B b
194 WHERE b.ORGANIZATION_ID = p_organization_id
195 AND b.enabled_flag = 'Y'
196 AND SYSDATE BETWEEN nvl(b.start_date_active, SYSDATE)
197 AND nvl(b.end_date_active, SYSDATE);
198
199 OPEN c_get_all_items_acc(p_organization_id,p_user_id);
200 LOOP
201 l_tab_access_id.DELETE;
202 l_tab_user_id.DELETE;
203 FETCH c_get_all_items_acc BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
204 EXIT WHEN l_tab_access_id.COUNT =0;
205 l_markdirty := asg_download.mark_dirty(
206 P_PUB_ITEM => g_pub_item
207 , p_accessList => l_tab_access_id
208 , p_userid_list => l_tab_user_id
209 , p_dml_type => 'I'
210 , P_TIMESTAMP => l_run_date
211 );
212
213 END LOOP;
214 CLOSE c_get_all_items_acc;
215 END IF;--if items for org exist check
216
217 UPDATE CSM_USER_INVENTORY_ORG
218 SET TYPE = 'C'
219 WHERE CATEGORY_SET_ID IS NULL
220 AND CATEGORY_ID IS NULL
221 AND USER_ID = p_user_id;
222 COMMIT;
223 END IF;--IF org has changed for the user.
224 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS after common processing',
225 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
226
227 RETURN;
228 END IF;--Profile to process only org
229
230 --------------------------------------------------------------------------------
231 --The following code will not be executed if the profile
232 --CSM: Download Organization Level Inventory is set to Y
233 --------------------------------------------------------------------------------
234 -- since profiles are changed get all inventory items that may already exist in
235 -- acc due to current SR's and increment counter
236 IF p_changed = 'Y' THEN
237
238 -- changed to dynamic sql to support either category or category set
239 l_stmt := 'UPDATE csm_mtl_system_items_acc';
240 l_stmt := l_stmt || ' SET counter = counter + 1';
241 l_stmt := l_stmt || ' , last_update_date = SYSDATE';
242 l_stmt := l_stmt || ' , last_updated_by = 1';
243 l_stmt := l_stmt || ' WHERE user_id = :1';
244 l_stmt := l_stmt || ' AND organization_id = :2';
245
246 IF (p_category_id IS NOT NULL) THEN
247 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
248 END IF;
249
250 IF (p_category_set_id IS NOT NULL) THEN
251 IF (l_stmt1 IS NOT NULL) THEN
252 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
253 || p_category_set_id;
254 ELSE
255 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
256 END IF;
257 END IF;
258
259 IF (l_stmt1 IS NOT NULL) THEN
260 l_stmt := l_stmt || ' AND ';
261 l_stmt := l_stmt || ' inventory_item_id IN';
262 l_stmt := l_stmt || ' (SELECT inventory_item_id';
263 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
264 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
265 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 )';
266 END IF;
267
268 IF l_stmt1 IS NOT NULL THEN
269 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id;
270 ELSE
271 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id;
272 END IF;
273
274 ELSE -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
275 -- changed to dynamic sql to support either category or category set
276 l_stmt := NULL;
277 l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
278 l_stmt := l_stmt || ' SET counter = counter - 1';
279 l_stmt := l_stmt || ' , last_update_date = SYSDATE';
280 l_stmt := l_stmt || ' , last_updated_by = 1';
281 l_stmt := l_stmt || ' WHERE user_id = :1';
282 l_stmt := l_stmt || ' AND organization_id = :2';
283
284 IF (p_category_id IS NOT NULL) THEN
285 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
286 END IF;
287
288 IF (p_category_set_id IS NOT NULL) THEN
289 IF (l_stmt1 IS NOT NULL) THEN
290 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
291 || p_category_set_id;
292 ELSE
293 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
294 END IF;
295 END IF;
296
297 IF (l_stmt1 IS NOT NULL) THEN
298 l_stmt := l_stmt || ' AND NOT EXISTS ';
299 l_stmt := l_stmt || ' (SELECT 1';
300 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
301 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
302 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 ';
303 l_stmt := l_stmt || ' AND acc.inventory_item_id = itemcat.inventory_item_id) ';
304 l_stmt := l_stmt || ' AND NOT EXISTS ';
305 l_stmt := l_stmt || ' (SELECT 1';
306 l_stmt := l_stmt || ' FROM csm_incidents_all_acc sr_acc, ';
307 l_stmt := l_stmt || ' cs_incidents_all_b sr ';
308 l_stmt := l_stmt || ' WHERE sr_acc.incident_id = sr.incident_id ';
309 l_stmt := l_stmt || ' AND sr_acc.user_id = :4 ';
310 l_stmt := l_stmt || ' AND acc.inventory_item_id = sr.inventory_item_id )';
311
312 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id, p_user_id;
313
314 -- bulk collect all items eligible for delete
315 l_tab_access_id.DELETE;
316 l_tab_user_id.DELETE;
317
318 SELECT access_id, user_id
319 BULK COLLECT INTO l_tab_access_id, l_tab_user_id
320 FROM csm_mtl_system_items_acc acc
321 WHERE acc.counter = 0;
322
323 IF l_tab_access_id.COUNT > 0 THEN
324 -- do bulk makedirty
325 l_markdirty := asg_download.mark_dirty(
326 P_PUB_ITEM => g_pub_item
327 , p_accessList => l_tab_access_id
328 , p_userid_list => l_tab_user_id
329 , p_dml_type => 'D'
330 , P_TIMESTAMP => l_run_date
331 );
332
333 FORALL i IN 1..l_tab_access_id.COUNT
334 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
335 END IF;
336 ELSE
337 -- do not decrement as category/category set profile is not changed
338 l_stmt := NULL;
339 END IF;
340 END IF; -- end of if p_changed ='Y'
341
342 IF l_profile_value='Y' THEN
343 l_number:=-1;
344 ELSE
345 l_number:=p_user_id;
346 END IF;
347
348 -- download new items if not existing
349 IF (p_category_id IS NULL AND p_category_set_id IS NULL) THEN
350 OPEN c_items(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
351 b_changed=>l_profile_value, b_last_run_date=>p_last_run_date);
352 LOOP
353 l_tab_access_id.DELETE;
354 l_inventory_item_id_tbl.DELETE;
355 l_organization_id_tbl.DELETE;
356 l_tab_user_id.DELETE;
357
358 FETCH c_items BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
359 l_organization_id_tbl, l_tab_user_id LIMIT 1000;
360 EXIT WHEN l_tab_access_id.COUNT = 0;
361
362 IF l_tab_access_id.COUNT > 0 THEN
363 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
364 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
365 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
366
367 FORALL i IN 1..l_tab_access_id.COUNT
368 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
369 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
370 VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
371 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
372
373 /*** push to oLite using asg_download ***/
374 -- do bulk makedirty
375 l_markdirty := asg_download.mark_dirty(
376 P_PUB_ITEM => g_pub_item
377 , p_accessList => l_tab_access_id
378 , p_userid_list => l_tab_user_id
379 , p_dml_type => 'I'
380 , P_TIMESTAMP => l_run_date
381 );
382 END IF; -- end of l_tab_access_id.count > 0
383 COMMIT;
384
385 END LOOP;
386 CLOSE c_items;
387
388 -- Category is not null and Cat set is null
389 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NULL) THEN
390 OPEN c_items_Cat(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
391 b_changed=>l_profile_value, b_last_run_date=>p_last_run_date,
392 b_category_id=>p_category_id);
393 LOOP
394 l_tab_access_id.DELETE;
395 l_inventory_item_id_tbl.DELETE;
396 l_organization_id_tbl.DELETE;
397 l_tab_user_id.DELETE;
398
399 FETCH c_items_Cat BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
400 l_organization_id_tbl, l_tab_user_id LIMIT 1000;
401 EXIT WHEN l_tab_access_id.COUNT = 0;
402
403 IF l_tab_access_id.COUNT > 0 THEN
404 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
405 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
406 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
407
408 FORALL i IN 1..l_tab_access_id.COUNT
409 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
410 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
411 VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
412 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
413
414 /*** push to oLite using asg_download ***/
415 -- do bulk makedirty
416 l_markdirty := asg_download.mark_dirty(
417 P_PUB_ITEM => g_pub_item
418 , p_accessList => l_tab_access_id
419 , p_userid_list => l_tab_user_id
420 , p_dml_type => 'I'
421 , P_TIMESTAMP => l_run_date
422 );
423
424 END IF; -- end of l_tab_access_id.count > 0
425 COMMIT;
426 END LOOP;
427 CLOSE c_items_Cat;
428
429 -- Category is null and Cat Set is not null
430 ELSIF (p_category_id IS NULL AND p_category_set_id IS NOT NULL) THEN
431 OPEN c_items_Cat_Set(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
432 b_changed=>l_profile_value, b_last_run_date=>p_last_run_date,
433 b_category_set_id=>p_category_set_id);
434 LOOP
435 l_tab_access_id.DELETE;
436 l_inventory_item_id_tbl.DELETE;
437 l_organization_id_tbl.DELETE;
438 l_tab_user_id.DELETE;
439
440 FETCH c_items_Cat_Set BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
441 l_organization_id_tbl, l_tab_user_id LIMIT 1000;
442 EXIT WHEN l_tab_access_id.COUNT = 0;
443
444 IF l_tab_access_id.COUNT > 0 THEN
445 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
446 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
447 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
448
449 FORALL i IN 1..l_tab_access_id.COUNT
450 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
451 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
452 VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
453 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
454
455 /*** push to oLite using asg_download ***/
456 -- do bulk makedirty
457 l_markdirty := asg_download.mark_dirty(
458 P_PUB_ITEM => g_pub_item
459 , p_accessList => l_tab_access_id
460 , p_userid_list => l_tab_user_id
461 , p_dml_type => 'I'
462 , P_TIMESTAMP => l_run_date
463 );
464
465 END IF; -- end of l_tab_access_id.count > 0
466 COMMIT;
467 END LOOP;
468 CLOSE c_items_Cat_Set;
469
470 -- Both Category and Category set are not null
471 ELSIF (p_category_id IS NOT NULL AND p_category_set_id IS NOT NULL) THEN
472 OPEN c_items_Cat_Set_Cat(b_user_id=>p_user_id, b_organization_id=>p_organization_id,
473 b_changed=>l_profile_value, b_last_run_date=>p_last_run_date,
474 b_category_id=>p_category_id, b_category_set_id=>p_category_set_id);
475 LOOP
476 l_tab_access_id.DELETE;
477 l_inventory_item_id_tbl.DELETE;
478 l_organization_id_tbl.DELETE;
479 l_tab_user_id.DELETE;
480
481 FETCH c_items_Cat_Set_Cat BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
482 l_organization_id_tbl, l_tab_user_id LIMIT 1000;
483 EXIT WHEN l_tab_access_id.COUNT = 0;
484
485 IF l_tab_access_id.COUNT > 0 THEN
486 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
487 || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
488 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
489
490 FORALL i IN 1..l_tab_access_id.COUNT
491 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
492 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
493 VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
494 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
495
496 /*** push to oLite using asg_download ***/
497 -- do bulk makedirty
498 l_markdirty := asg_download.mark_dirty(
499 P_PUB_ITEM => g_pub_item
500 , p_accessList => l_tab_access_id
501 , p_userid_list => l_tab_user_id
502 , p_dml_type => 'I'
503 , P_TIMESTAMP => l_run_date
504 );
505
506 END IF; -- end of l_tab_access_id.count > 0
507 COMMIT;
508 END LOOP;
509 CLOSE c_items_Cat_Set_Cat;
510
511 END IF;
512 UPDATE CSM_USER_INVENTORY_ORG
513 SET TYPE = 'I'
514 WHERE USER_ID = p_user_id;
515 COMMIT;
516
517 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
518 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
519 EXCEPTION
520 WHEN others THEN
521 l_sqlerrno := TO_CHAR(SQLCODE);
522 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
523 l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
524 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
525 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
526 RAISE;
527 END INSERT_MTL_SYSTEM_ITEMS;
528
529 PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
530 IS
531 l_sqlerrno VARCHAR2(20);
532 l_sqlerrmsg VARCHAR2(4000);
533 l_error_msg VARCHAR2(4000);
534 l_return_status VARCHAR2(2000);
535 l_run_date DATE;
536 l_tab_access_id asg_download.access_list;
537 l_tab_user_id asg_download.user_list;
538 l_markdirty BOOLEAN;
539 l_max_last_update_date_b DATE;
540 l_max_last_update_date_tl DATE;
541 l_profile_value VARCHAR2(10);
542 l_transaction_flag VARCHAR2(4) := 'N';
543
544 CURSOR c_changed( b_last_date DATE)
545 IS
546 SELECT acc.access_id, acc.user_id
547 FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi,asg_user au
548 WHERE msi.inventory_item_id = acc.inventory_item_id
549 AND msi.organization_id = acc.organization_id
550 AND acc.USER_ID > 0
551 AND au.user_id=acc.user_id
552 AND nvl(au.MULTI_PLATFORM,'N')='N'
553 AND msi.last_update_date >= b_last_date;
554
555 ---check if user org can be separated.
556 CURSOR c_changed_org_based( b_last_date DATE)
557 IS
558 SELECT acc.access_id, uorg.user_id
559 FROM csm_mtl_system_items_acc acc,
560 csm_user_inventory_org uorg
561 WHERE acc.organization_id = uorg.organization_id
562 AND uorg.category_set_id IS NULL
563 AND uorg.category_id IS NULL
564 AND acc.USER_ID = -1
565 AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
566 WHERE msi.inventory_item_id = acc.inventory_item_id
567 AND msi.organization_id = acc.organization_id
568 AND (msi.last_update_date >= b_last_date))
569 UNION
570 SELECT acc.access_id, uorg.user_id
571 FROM csm_mtl_system_items_acc acc,
572 csm_user_inventory_org uorg
573 WHERE acc.organization_id = uorg.organization_id
574 AND uorg.category_set_id IS NULL
575 AND uorg.category_id IS NULL
576 AND acc.USER_ID = -1
577 AND EXISTS ( SELECT 1 FROM mtl_system_items_tl tl
578 WHERE tl.inventory_item_id = acc.inventory_item_id
579 AND tl.organization_id = acc.organization_id
580 AND (tl.last_update_date >= b_last_date));
581
582
583 ---New items inserted for orgs in user org table
584 CURSOR c_New_items_for_org
585 IS
586 SELECT acc.access_id, uorg.user_id
587 FROM csm_mtl_system_items_acc acc,
588 csm_user_inventory_org uorg
589 WHERE acc.organization_id = uorg.organization_id
590 AND uorg.category_set_id IS NULL
591 AND uorg.category_id IS NULL
592 AND acc.USER_ID = -2;
593
594 ---delete items that are disabled or end dated
595
596 CURSOR c_del_org_based( b_last_date DATE)
597 IS
598 SELECT acc.access_id, uorg.user_id
599 FROM csm_mtl_system_items_acc acc,
600 csm_user_inventory_org uorg
601 WHERE acc.organization_id = uorg.organization_id
602 AND uorg.category_set_id IS NULL
603 AND uorg.category_id IS NULL
604 AND acc.USER_ID = -1
605 AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
606 WHERE msi.inventory_item_id = acc.inventory_item_id
607 AND msi.organization_id = acc.organization_id
608 AND (msi.enabled_flag = 'N' OR msi.end_date_active < b_last_date));
609
610 BEGIN
611 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
612 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
613
614 l_run_date := SYSDATE;
615
616 /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
617 /* , MTL_SYSTEM_ITEMS_TL which were custom created */
618 SELECT MAX(last_update_date) INTO l_max_last_update_date_b
619 FROM mtl_system_items_b;
620 IF( l_max_last_update_date_b < p_last_run_date) THEN
621 SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
622 FROM mtl_system_items_tl;
623 IF(l_max_last_update_date_tl < p_last_run_date) THEN
624 -- No updates
625 CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
626 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
627 RETURN;
628 END IF;
629 END IF;
630
631 --GEt the value for CSM: Download Organization Level Inventory
632
633 l_profile_value := NVL(g_download_inv_flag,'N');
634
635 IF l_profile_value = 'Y' THEN
636
637 --DELETE
638 OPEN c_del_org_based( l_run_date);
639 LOOP
640 l_tab_access_id.DELETE;
641 l_tab_user_id.DELETE;
642
643 FETCH c_del_org_based BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
644 EXIT WHEN l_tab_access_id.COUNT = 0;
645
646 CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
647 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
648 l_transaction_flag :='Y'; --this flag is to save db access
649 -- do bulk makedirty
650 l_markdirty := asg_download.mark_dirty(
651 P_PUB_ITEM => g_pub_item
652 , p_accessList => l_tab_access_id
653 , p_userid_list => l_tab_user_id
654 , p_dml_type => 'D'
655 , P_TIMESTAMP => l_run_date
656 );
657
658 COMMIT;
659 END LOOP;
660 CLOSE c_del_org_based;
661
662 IF l_transaction_flag ='Y' THEN
663 l_transaction_flag := 'N';
664 --DELETE the items from the acc table
665 DELETE FROM csm_mtl_system_items_acc acc
666 WHERE USER_ID = -1
667 AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
668 WHERE msi.inventory_item_id = acc.inventory_item_id
669 AND msi.organization_id = acc.organization_id
670 AND msi.enabled_flag = 'N' OR msi.end_date_active < l_run_date);
671 COMMIT;
672 END IF;
673
674 --UPDATE
675 OPEN c_changed_org_based( p_last_run_date);
676 LOOP
677 l_tab_access_id.DELETE;
678 l_tab_user_id.DELETE;
679
680 FETCH c_changed_org_based BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
681 EXIT WHEN l_tab_access_id.COUNT = 0;
682
683 IF l_tab_access_id.COUNT > 0 THEN
684 CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
685 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
686
687 -- do bulk makedirty
688 l_markdirty := asg_download.mark_dirty(
689 P_PUB_ITEM => g_pub_item
690 , p_accessList => l_tab_access_id
691 , p_userid_list => l_tab_user_id
692 , p_dml_type => 'U'
693 , P_TIMESTAMP => l_run_date
694 );
695 END IF;
696 COMMIT;
697 END LOOP;
698 CLOSE c_changed_org_based;
699 --Insert newly created Items for allthe user in csm_user_inventory_org
700
701 --Do a direct into into the ACC table for user -2
702 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
703 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
704 SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,
705 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
706 FROM MTL_SYSTEM_ITEMS_B msi
707 WHERE msi.enabled_flag = 'Y'
708 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
709 AND msi.ORGANIZATION_ID
710 IN(SELECT DISTINCT uorg.ORGANIZATION_ID
711 FROM CSM_USER_INVENTORY_ORG uorg
712 WHERE uorg.category_set_id IS NULL
713 AND uorg.category_id IS NULL)
714 AND NOT EXISTS
715 ( SELECT 1
716 FROM csm_mtl_system_items_acc acc
717 WHERE acc.user_id = -1
718 AND acc.inventory_item_id = msi.inventory_item_id
719 AND acc.organization_id = msi.organization_id);
720
721 COMMIT;
722 l_transaction_flag := 'N';
723 --Mark Dirty all the inserts
724 OPEN c_New_items_for_org;
725 LOOP
726 l_tab_access_id.DELETE;
727 l_tab_user_id.DELETE;
728 FETCH c_New_items_for_org BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
729 EXIT WHEN l_tab_access_id.COUNT = 0;
730
731 IF l_tab_access_id.COUNT > 0 THEN
732 CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for Inerting csm_mtl_system_items',
733 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
734 l_transaction_flag := 'Y';
735 -- do bulk makedirty
736 l_markdirty := asg_download.mark_dirty(
737 P_PUB_ITEM => g_pub_item
738 , p_accessList => l_tab_access_id
739 , p_userid_list => l_tab_user_id
740 , p_dml_type => 'I'
741 , P_TIMESTAMP => l_run_date
742 );
743 END IF;
744 COMMIT;
745 END LOOP;
746 CLOSE c_New_items_for_org;
747
748 IF l_transaction_flag = 'Y' THEN
749 --Update the user_id to -1;
750 UPDATE csm_mtl_system_items_acc
751 SET USER_ID = -1
752 WHERE USER_ID = -2;
753 COMMIT;
754 END IF;
755
756 ELSE
757 OPEN c_changed( p_last_run_date);
758 LOOP
759 l_tab_access_id.DELETE;
760 l_tab_user_id.DELETE;
761
762 FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
763 EXIT WHEN l_tab_access_id.COUNT = 0;
764
765 IF l_tab_access_id.COUNT > 0 THEN
766 CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
767 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
768
769 -- do bulk makedirty
770 l_markdirty := asg_download.mark_dirty(
771 P_PUB_ITEM => g_pub_item
772 , p_accessList => l_tab_access_id
773 , p_userid_list => l_tab_user_id
774 , p_dml_type => 'U'
775 , P_TIMESTAMP => l_run_date
776 );
777 END IF;
778 COMMIT;
779 END LOOP;
780 CLOSE c_changed;
781
782 END IF;
783
784 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
785 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 l_sqlerrno := TO_CHAR(SQLCODE);
790 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
791 l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
792 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
793 RAISE;
794 END UPDATE_MTL_SYSTEM_ITEMS;
795
796 PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
797 p_organization_id IN NUMBER,
798 p_category_set_id IN NUMBER,
799 p_category_id IN NUMBER)
800 IS
801 l_sqlerrno VARCHAR2(20);
802 l_sqlerrmsg VARCHAR2(4000);
803 l_error_msg VARCHAR2(4000);
804 l_return_status VARCHAR2(2000);
805 l_stmt VARCHAR2(4000);
806 l_stmt1 VARCHAR2(4000);
807 l_markdirty BOOLEAN;
808 l_run_date DATE;
809 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
810 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
811
812 CURSOR c_Delete_items(b_org_id NUMBER, b_user_id NUMBER)
813 IS
814 SELECT b_user_id, ACCESS_ID
815 FROM csm_mtl_system_items_acc
816 WHERE USER_ID =-1
817 AND ORGANIZATION_ID = b_org_id;
818
819 CURSOR c_get_org_count(b_org_id NUMBER, b_user_id NUMBER)
820 IS
821 SELECT count(*)
822 FROM csm_user_inventory_org
823 WHERE USER_ID <> b_user_id
824 AND ORGANIZATION_ID = b_org_id;
825
826 l_profile_value VARCHAR2(100);
827 l_org_count NUMBER;
828 BEGIN
829 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
830 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
831
832 l_run_date := SYSDATE;
833 l_tab_access_id.DELETE;
834
835 --GEt the value for CSM: Download Organization Level Inventory
836 l_profile_value := g_download_inv_flag;
837
838 --If profile is set for org level
839 IF l_profile_value = 'Y' THEN
840 OPEN c_Delete_items(p_organization_id, p_user_id);
841 LOOP
842 FETCH c_Delete_items BULK COLLECT INTO l_tab_user_id, l_tab_access_id LIMIT 1000;
843 EXIT WHEN l_tab_access_id.COUNT =0;
844
845 l_markdirty := asg_download.mark_dirty(
846 P_PUB_ITEM => g_pub_item
847 , p_accessList => l_tab_access_id
848 , p_userid_list => l_tab_user_id
849 , p_dml_type => 'D'
850 , P_TIMESTAMP => l_run_date
851 );
852
853 l_tab_access_id.DELETE;
854 l_tab_user_id.DELETE;
855 END LOOP;
856 COMMIT;
857
858 --IF the user is the last user in the org then delete the items from the org...
859 OPEN c_get_org_count(p_organization_id, p_user_id);
860 FETCH c_get_org_count INTO l_org_count;
861 CLOSE c_get_org_count;
862 IF l_org_count = 0 THEN
863 DELETE FROM csm_mtl_system_items_acc WHERE USER_ID = -1
864 AND organization_id =p_organization_id;
865 END IF;
866 ELSE
867
868 l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
869 l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
870 l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
871 l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
872 l_stmt := l_stmt || ' WHERE USER_ID = :1';
873 l_stmt := l_stmt || ' AND organization_id = :2';
874
875 IF (p_category_id IS NOT NULL) THEN
876 l_stmt1 := ' itemcat.category_id = ' || p_category_id;
877 END IF;
878
879 IF (p_category_set_id IS NOT NULL) THEN
880 IF (l_stmt1 IS NOT NULL) THEN
881 l_stmt1 := l_stmt1 || ' AND itemcat.category_set_id = '
882 || p_category_set_id;
883 ELSE
884 l_stmt1 := ' itemcat.category_set_id = ' || p_category_set_id;
885 END IF;
886 END IF;
887
888 IF (l_stmt1 IS NOT NULL) THEN
889 l_stmt := l_stmt || ' AND EXISTS (';
890 l_stmt := l_stmt || ' SELECT 1 ';
891 l_stmt := l_stmt || ' FROM mtl_item_categories itemcat';
892 l_stmt := l_stmt || ' WHERE ' || l_stmt1;
893 l_stmt := l_stmt || ' AND itemcat.organization_id = :3 ';
894 l_stmt := l_stmt || ' AND acc.inventory_item_id = itemcat.inventory_item_id )';
895 END IF;
896
897 IF (l_stmt1 IS NOT NULL) THEN
898 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id, p_organization_id;
899 ELSE
900 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id;
901 END IF;
902
903 -- bulk collect all items eligible for delete
904 l_tab_access_id.DELETE;
905 l_tab_user_id.DELETE;
906
907 SELECT access_id, user_id
908 BULK COLLECT INTO l_tab_access_id, l_tab_user_id
909 FROM csm_mtl_system_items_acc acc
910 WHERE acc.counter = 0;
911
912 IF l_tab_access_id.COUNT > 0 THEN
913 -- do bulk makedirty
914 l_markdirty := asg_download.mark_dirty(
915 P_PUB_ITEM => g_pub_item
916 , p_accessList => l_tab_access_id
917 , p_userid_list => l_tab_user_id
918 , p_dml_type => 'D'
919 , P_TIMESTAMP => l_run_date
920 );
921
922 FORALL i IN 1..l_tab_access_id.COUNT
923 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
924 END IF;
925 END IF;
926
927 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
928 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
929 EXCEPTION
930 WHEN others THEN
931 l_sqlerrno := TO_CHAR(SQLCODE);
932 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
933 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
934 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
935 RAISE;
936 END DELETE_MTL_SYSTEM_ITEMS;
937
938 PROCEDURE concurrent_process_user(p_user_id IN NUMBER,
939 p_organization_id IN NUMBER,
940 p_category_set_id IN NUMBER,
941 p_category_id IN NUMBER,
942 p_last_run_date IN DATE)
943 IS
944 l_sqlerrno VARCHAR2(20);
945 l_sqlerrmsg VARCHAR2(4000);
946 l_error_msg VARCHAR2(4000);
947 l_return_status VARCHAR2(2000);
948 l_pre_cat_filter BOOLEAN; -- TRUE when category filter was active previously
949 l_post_cat_filter BOOLEAN; -- TRUE when category filter is active now
950 l_cat_filter_changed BOOLEAN; -- TRUE when category filter changed
951
952 CURSOR c_org(b_user_id NUMBER)
953 IS
954 SELECT organization_id, category_set_id, category_id
955 FROM csm_user_inventory_org
956 WHERE user_id = b_user_id
957 FOR UPDATE;
958
959 r_org c_org%ROWTYPE;
960 l_profile_value VARCHAR2(10);
961 BEGIN
962 CSM_UTIL_PKG.LOG('Entering CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
963 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
964
965 /* Get old profile settings */
966 g_download_inv_flag := NULL;
967 g_download_inv_flag := CSM_PROFILE_PKG.Get_download_inventory_flag
968 (p_user_id =>p_user_id);
969
970 OPEN c_org( p_user_id );
971 FETCH c_org INTO r_org;
972 IF c_org%NOTFOUND THEN -- should not occur
973 CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
974 || ' - Inserting all mtl_system_items',
975 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
976 INSERT INTO csm_user_inventory_org (
977 user_id, organization_id, last_update_date, last_updated_by,
978 creation_date, created_by, category_set_id, category_id )
979 VALUES (
980 p_user_id, p_organization_id, SYSDATE, 1, SYSDATE, 1,
981 p_category_set_id, p_category_id );
982
983 get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
984 p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
985
986
987 ELSE
988 l_pre_cat_filter := FALSE;
989 l_post_cat_filter := FALSE;
990
991 IF (( r_org.category_set_id IS NOT NULL ) OR
992 ( r_org.category_id IS NOT NULL)) THEN
993 l_pre_cat_filter := TRUE;
994 END IF;
995
996 IF (( p_category_set_id IS NOT NULL ) OR
997 ( p_category_id IS NOT NULL)) THEN
998 l_post_cat_filter := TRUE;
999 END IF;
1000
1001 /*** did category filter change from active -> inactive or vice versa ***/
1002 l_cat_filter_changed := FALSE;
1003 IF l_pre_cat_filter <> l_post_cat_filter THEN
1004 /*** yes -> set boolean ***/
1005 l_cat_filter_changed := TRUE;
1006 ELSE
1007 /*** no -> is filter active ***/
1008 IF l_post_cat_filter THEN
1009 /*** yes -> did category or category set change? ***/
1010 IF NVL(r_org.category_set_id, 0) <> NVL(p_category_set_id, 0)
1011 OR NVL(r_org.category_id, 0) <> NVL(p_category_id, 0) THEN
1012 l_cat_filter_changed := TRUE;
1013 END IF;
1014 END IF;
1015 END IF;
1016
1017 IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id, -1)
1018 OR l_cat_filter_changed THEN
1019 CSM_UTIL_PKG.LOG('Deleting records for old profile settings for user_id: ' || TO_CHAR(p_user_id),
1020 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1021
1022 -- delete labor/expense items only and then re-insert for the new orgif org changes
1023 IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id,-1) THEN
1024 csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,
1025 p_organization_id=>r_org.organization_id);
1026
1027 -- download new labor/expense items for the new org
1028 csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
1029 END IF;
1030
1031 delete_mtl_system_items(p_user_id=>p_user_id,
1032 p_organization_id=>r_org.organization_id,
1033 p_category_set_id=>r_org.category_set_id,
1034 p_category_id=>r_org.category_id);
1035
1036 CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
1037 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1038
1039 insert_mtl_system_items(p_user_id=>p_user_id,
1040 p_organization_id=>p_organization_id,
1041 p_category_set_id=>p_category_set_id,
1042 p_category_id=>p_category_id,
1043 p_last_run_date=>NULL,
1044 p_changed=>'Y');
1045
1046 CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
1047 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1048
1049 UPDATE csm_user_inventory_org
1050 SET organization_id = p_organization_id
1051 , category_set_id = p_category_set_id
1052 , category_id = p_category_id
1053 , last_update_date = SYSDATE
1054 WHERE CURRENT OF c_org;
1055
1056 ELSE
1057 -- profiles are the same
1058 -- get any new changes
1059 CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
1060 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1061
1062 insert_mtl_system_items(p_user_id=>p_user_id,
1063 p_organization_id=>p_organization_id,
1064 p_category_set_id=>p_category_set_id,
1065 p_category_id=>p_category_id,
1066 p_last_run_date=>p_last_run_date,
1067 p_changed=>'N');
1068 END IF;
1069 END IF;
1070 CLOSE c_org;
1071
1072 CSM_UTIL_PKG.LOG('Leaving CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
1073 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1074 EXCEPTION
1075 WHEN OTHERS THEN
1076 l_sqlerrno := TO_CHAR(SQLCODE);
1077 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1078 l_error_msg := ' Exception in concurrent_process_user for user_id :'
1079 || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1080 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);
1081 RAISE;
1082 END CONCURRENT_PROCESS_USER;
1083
1084 /*--NO LONGER USING THIS API */
1085 PROCEDURE process_htm5_ordered_items
1086 IS
1087
1088 CURSOR c_htm5_mtl_items
1089 IS
1090 SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id,org_id ,user_id
1091 FROM ( select c.inventory_item_id,d.destination_organization_id org_id ,a.user_id
1092 from csm_req_lines_acc a, csp_req_line_details b ,
1093 oe_order_lines_all c,csp_requirement_headers d
1094 ,csp_requirement_lines e ,asg_user au
1095 where a.requirement_line_id=b.requirement_line_id
1096 and b.source_type= 'IO' and b.source_id=c.line_id
1097 and au.user_id=a.user_id and au.multi_platform='Y'
1098 and d.requirement_header_id=e.requirement_header_id
1099 and b.requirement_line_id=e.requirement_line_id
1100 UNION
1101 select c.inventory_item_id,c.organization_id as org_id ,a.user_id
1102 from csm_req_lines_acc a, csp_req_line_details b , mtl_reservations c,asg_user au
1103 where a.requirement_line_id=b.requirement_line_id
1104 and b.source_type= 'RES' and b.source_id=c.reservation_id
1105 and au.user_id=a.user_id and au.multi_platform='Y') d
1106 where not exists(select 1 from csm_mtl_system_items_acc acc
1107 where acc.inventory_item_id=d.inventory_item_id
1108 and acc.organization_id=d.org_id
1109 and acc.user_id=d.user_id);
1110
1111
1112 TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1113 TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1114 l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1115 l_organization_id_tbl organization_id_tbl_typ;
1116 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1117 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
1118 l_markdirty BOOLEAN;
1119
1120 BEGIN
1121
1122 OPEN c_htm5_mtl_items;
1123 LOOP
1124 l_tab_access_id.DELETE;
1125 l_inventory_item_id_tbl.DELETE;
1126 l_organization_id_tbl.DELETE;
1127 l_tab_user_id.DELETE;
1128
1129 FETCH c_htm5_mtl_items BULK COLLECT INTO l_tab_access_id,l_inventory_item_id_tbl,
1130 l_organization_id_tbl, l_tab_user_id LIMIT 1000;
1131 EXIT WHEN l_tab_access_id.COUNT = 0;
1132
1133 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc of requirement lines' ,
1134 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_ordered_items',FND_LOG.LEVEL_EVENT);
1135
1136 FORALL i IN 1..l_tab_access_id.COUNT
1137 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
1138 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1139 VALUES (l_tab_access_id(i), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
1140 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
1141
1142 /*** push to oLite using asg_download ***/
1143 -- do bulk makedirty
1144 l_markdirty := asg_download.mark_dirty(
1145 P_PUB_ITEM => g_pub_item
1146 , p_accessList => l_tab_access_id
1147 , p_userid_list => l_tab_user_id
1148 , p_dml_type => 'I'
1149 , P_TIMESTAMP => sysdate
1150 );
1151
1152 COMMIT;
1153 END LOOP;
1154 CLOSE c_htm5_mtl_items;
1155 END process_htm5_ordered_items;
1156
1157 PROCEDURE process_htm5_user_items(p_run_date DATE)
1158 IS
1159 CURSOR c_get_del_items(b_ret_acty_date DATE) IS
1160 select a.access_id,au.user_id
1161 from csm_mtl_system_items_acc a,asg_user au
1162 where au.user_id=a.user_id
1163 and NVL(au.MULTI_PLATFORM,'N')='Y'
1164 and not exists (select 1
1165 from csf_debrief_lines b
1166 where transaction_type_id in (select transaction_type_id
1167 from cs_transaction_types_b
1168 where line_order_category_code='RETURN')
1169 and b.last_update_date >= b_ret_acty_date
1170 and a.inventory_item_id=b.inventory_item_id
1171 and a.organization_id=nvl(b.receiving_inventory_org_id,b.issuing_inventory_org_id)) --return debrief-ed items
1172 and not exists(select 1
1173 from CSM_REQ_LINE_DETAILS_ACC req_acc
1174 where req_acc.user_id=au.user_id
1175 and req_acc.item_id=a.inventory_item_id
1176 and req_acc.org_id =a.organization_id) --htm5 user ordered/reserved item
1177 and not exists(select 1
1178 from csm_item_instances_acc acc ,csi_item_instances ii
1179 where acc.instance_id=ii.instance_id
1180 and acc.user_id=au.user_id
1181 and ii.inventory_item_id=a.inventory_item_id
1182 and a.organization_id=NVL(ii.inv_organization_id,ii.LAST_VLD_ORGANIZATION_ID)) --sr instance and its children/parent
1183 and not exists(select 1
1184 from csm_incidents_all_acc inc_a,cs_incidents_all_b inc
1185 where inc_a.incident_id=inc.incident_id
1186 and inc_a.user_id=au.user_id
1187 and NVL(inc.customer_product_id,0)=0
1188 and inc.inventory_item_id=a.inventory_item_id
1189 and inc.org_id=a.organization_id); --sr item
1190
1191 CURSOR c_get_ins_items(b_ret_acty_date DATE,
1192 b_max_mtl_items NUMBER) IS
1193 select csm_mtl_system_items_acc_s.NEXTVAL,item_id,msi.org_id,au.user_id
1194 from (select item_id,org_id from
1195 (select inventory_item_id as item_id,
1196 nvl(receiving_inventory_org_id,issuing_inventory_org_id) as org_id,
1197 sum(quantity) tot from csf_debrief_lines b
1198 where transaction_type_id in (select transaction_type_id
1199 from cs_transaction_types_b
1200 where line_order_category_code='RETURN')
1201 and inventory_item_id is not null
1202 and nvl(receiving_inventory_org_id,issuing_inventory_org_id) is not null
1203 and b.lasT_update_date > b_ret_acty_date
1204 group by inventory_item_id ,nvl(receiving_inventory_org_id,issuing_inventory_org_id)
1205 order by tot desc)
1206 where rownum <= b_max_mtl_items) msi,
1207 asg_user au
1208 where not exists(select 1 from csm_mtl_system_items_acc
1209 WHERE msi.item_id = inventory_item_id
1210 AND msi.org_id = organization_id
1211 AND user_id =au.user_id);
1212
1213 CURSOR c_get_upd_items IS
1214 SELECT acc.access_id, acc.user_id
1215 FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
1216 WHERE msi.inventory_item_id = acc.inventory_item_id
1217 AND msi.organization_id = acc.organization_id
1218 AND EXISTS(SELECT 1 FROM ASG_USER au WHERE AU.USER_ID=acc.USER_ID and NVL(MULTI_PLATFORM,'N')='Y')
1219 AND acc.CREATION_DATE <=p_run_date
1220 AND msi.last_update_date > p_run_date;
1221
1222 TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1223 TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1224 l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1225 l_organization_id_tbl organization_id_tbl_typ;
1226 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1227 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
1228 l_markdirty BOOLEAN;
1229 l_respId NUMBER;
1230 l_ret_acty_date DATE;
1231 l_max_mtl_items NUMBER;
1232 BEGIN
1233
1234 SELECT RESPONSIBILITY_ID INTO l_respId FROM FND_RESPONSIBILITY WHERE RESPONSIBILITY_KEY='OMFS_PALM';
1235 l_ret_acty_date := sysdate - to_number(nvl(fnd_profile.value_specific('CSM_MULTI_RET_ACTY_DURATION',null,l_respId,null),90));
1236 l_max_mtl_items := to_number(nvl(fnd_profile.value_specific('CSM_MULTI_MAX_MTL_ITEMS',null,l_respId,null),500));
1237
1238
1239 --DELETE --based on date in profile --COUNT will be retained with old value till user gets recreated or effectively gets corrected in passing days
1240 --This query might take longer and we can make it run twice a day since only date profile is handled
1241 --one drawback but not an issue( as client has more data)-> a requirement's mtl item that's downloaded may not get deleted same day if removed after this prg is run(in second half of day)
1242 IF (SYSDATE-p_run_date) >= 0.5 THEN
1243 OPEN c_get_del_items(l_ret_acty_date);
1244 LOOP
1245 l_tab_access_id.DELETE;
1246 l_tab_user_id.DELETE;
1247 FETCH c_get_del_items BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
1248 EXIT WHEN l_tab_access_id.COUNT =0;
1249
1250 CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from csm_mtl_system_items_acc','CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
1251
1252 l_markdirty := asg_download.mark_dirty(
1253 P_PUB_ITEM => g_pub_item
1254 , p_accessList => l_tab_access_id
1255 , p_userid_list => l_tab_user_id
1256 , p_dml_type => 'D'
1257 , P_TIMESTAMP => sysdate
1258 );
1259
1260 FORALL i IN 1..l_tab_access_id.COUNT
1261 DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
1262 COMMIT;
1263 END LOOP;
1264 CLOSE c_get_del_items;
1265 END IF;
1266
1267
1268 --UPDATE
1269 OPEN c_get_upd_items;
1270 LOOP
1271 l_tab_access_id.DELETE;
1272 l_tab_user_id.DELETE;
1273 FETCH c_get_upd_items BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
1274 EXIT WHEN l_tab_access_id.COUNT =0;
1275 CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records from csm_mtl_system_items_acc' ,'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
1276 l_markdirty := asg_download.mark_dirty(
1277 P_PUB_ITEM => g_pub_item
1278 , p_accessList => l_tab_access_id
1279 , p_userid_list => l_tab_user_id
1280 , p_dml_type => 'U'
1281 , P_TIMESTAMP => sysdate
1282 );
1283
1284 END LOOP;
1285 CLOSE c_get_upd_items;
1286
1287 COMMIT;
1288
1289 --INSERT --based on date and count in profile for specific resps
1290
1291 OPEN c_get_ins_items(l_ret_acty_date, l_max_mtl_items);
1292 LOOP
1293 l_tab_access_id.DELETE;
1294 l_inventory_item_id_tbl.DELETE;
1295 l_organization_id_tbl.DELETE;
1296 l_tab_user_id.DELETE;
1297
1298 FETCH c_get_ins_items BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
1299 l_organization_id_tbl, l_tab_user_id LIMIT 2000;
1300 EXIT WHEN l_tab_access_id.COUNT = 0;
1301
1302 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc ' ,
1303 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
1304
1305 FORALL i IN 1..l_tab_access_id.COUNT
1306 INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
1307 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1308 VALUES (l_tab_access_id(i), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
1309 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
1310
1311 /*** push to oLite using asg_download ***/
1312 -- do bulk makedirty
1313 l_markdirty := asg_download.mark_dirty(
1314 P_PUB_ITEM => g_pub_item
1315 , p_accessList => l_tab_access_id
1316 , p_userid_list => l_tab_user_id
1317 , p_dml_type => 'I'
1318 , P_TIMESTAMP => sysdate
1319 );
1320
1321 COMMIT;
1322 END LOOP;
1323 CLOSE c_get_ins_items;
1324
1325 --process_htm5_ordered_items; --will be done synchronously
1326
1327 END process_htm5_user_items;
1328
1329 PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,
1330 p_message OUT NOCOPY VARCHAR2)
1331 IS
1332 PRAGMA AUTONOMOUS_TRANSACTION;
1333 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
1334 l_all_omfs_user_list asg_download.user_list;
1335 l_null_omfs_user_list asg_download.user_list;
1336 l_user_id fnd_user.user_id%TYPE;
1337 l_user_organization_id mtl_system_items.organization_id%TYPE;
1338 l_user_category_set_id mtl_category_sets.category_set_id%TYPE;
1339 l_user_category_id mtl_categories.category_id%TYPE;
1340 l_run_date DATE;
1341 l_sqlerrno VARCHAR2(20);
1342 l_sqlerrmsg VARCHAR2(2000);
1343 l_num_non_multi_users NUMBER;
1344
1345 CURSOR l_last_run_date_csr
1346 IS
1347 SELECT NVL(last_run_date, TO_DATE('1','J'))
1348 FROM jtm_con_request_data
1349 WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1350 AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1351
1352 BEGIN
1353 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
1354 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1355
1356 l_run_date := SYSDATE;
1357
1358 -- get last conc program update date
1359 OPEN l_last_run_date_csr;
1360 FETCH l_last_run_date_csr INTO l_prog_update_date;
1361 CLOSE l_last_run_date_csr;
1362
1363 process_htm5_user_items(l_prog_update_date);
1364
1365 COMMIT;
1366
1367 -- Check if there are any non multi platform users to process
1368 select count(*) into l_num_non_multi_users
1369 from asg_user
1370 where enabled = 'Y'
1371 and nvl(multi_platform,'N') = 'N';
1372
1373 IF(l_num_non_multi_users > 0) THEN
1374 -- do an update for existing records
1375 update_mtl_system_items(p_last_run_date => l_prog_update_date);
1376
1377 COMMIT;
1378
1379 -- get user list of all omfs users
1380 l_all_omfs_user_list := l_null_omfs_user_list;
1381 l_all_omfs_user_list := csm_util_pkg.get_all_omfs_palm_user_list;
1382
1383 FOR i IN 1..l_all_omfs_user_list.COUNT LOOP
1384 l_user_id := l_all_omfs_user_list(i);
1385
1386 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_user_id) THEN
1387
1388 l_user_organization_id := csm_profile_pkg.get_organization_id(l_user_id);
1389 l_user_category_set_id := csm_profile_pkg.get_category_set_id(l_user_id);
1390 l_user_category_id := csm_profile_pkg.get_category_id(l_user_id);
1391
1392 concurrent_process_user(p_user_id=>l_user_id,
1393 p_organization_id=>l_user_organization_id,
1394 p_category_set_id=>l_user_category_set_id,
1395 p_category_id=>l_user_category_id,
1396 p_last_run_date=>l_prog_update_date);
1397
1398 END IF;
1399
1400 END LOOP;
1401
1402 END IF;
1403
1404 -- update last_run_date
1405 UPDATE jtm_con_request_data
1406 SET last_run_date = l_run_date
1407 WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1408 AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1409
1410 COMMIT;
1411
1412 p_status := 'FINE';
1413 p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
1414
1415 CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
1416 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1417 EXCEPTION
1418 WHEN others THEN
1419 l_sqlerrno := TO_CHAR(SQLCODE);
1420 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1421 p_status := 'ERROR';
1422 p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
1423 ROLLBACK;
1424 csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1425 END REFRESH_MTL_SYSTEM_ITEMS_ACC;
1426
1427 PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
1428 p_category_set_id IN NUMBER, p_category_id IN NUMBER)
1429 IS
1430 l_sqlerrno VARCHAR2(20);
1431 l_sqlerrmsg VARCHAR2(4000);
1432 l_error_msg VARCHAR2(4000);
1433 l_return_status VARCHAR2(2000);
1434 l_profile_value VARCHAR2(10);
1435 l_changed varchar2(10);
1436 BEGIN
1437 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1438 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1439 --The flag can be null only when is called from csm_user_event_pkg during
1440 --User creation
1441 IF g_download_inv_flag IS NULL THEN
1442 --GEt the value for CSM: Download Organization Level Inventory
1443 g_download_inv_flag := CSM_PROFILE_PKG.Get_download_inventory_flag
1444 (p_user_id=>p_user_id);
1445 END IF;
1446
1447 IF g_download_inv_flag = 'Y' THEN
1448 l_changed := 'W';
1449 ELSE
1450 l_changed := 'N';
1451 END IF;
1452
1453 insert_mtl_system_items(p_user_id=>p_user_id,
1454 p_organization_id=>p_organization_id,
1455 p_category_set_id=>p_category_set_id,
1456 p_category_id=>p_category_id,
1457 p_last_run_date=>NULL,
1458 p_changed=>l_changed); -- new user, no profiles are changed
1459
1460 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1461 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1462
1463 EXCEPTION
1464 WHEN others THEN
1465 l_sqlerrno := TO_CHAR(SQLCODE);
1466 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1467 l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
1468 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1469 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
1470 RAISE;
1471 END get_new_user_mtl_system_items;
1472
1473 PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
1474 p_organization_id IN NUMBER,
1475 p_user_id IN NUMBER,
1476 p_error_msg OUT NOCOPY VARCHAR2,
1477 x_return_status IN OUT NOCOPY VARCHAR2)
1478 IS
1479 BEGIN
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1482 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1483
1484 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1485 CSM_ACC_PKG.Insert_Acc
1486 ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
1487 ,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
1488 ,P_SEQ_NAME => g_mtl_sys_items_seq_name
1489 ,P_PK1_NAME => g_mtl_sys_items_pk1_name
1490 ,P_PK1_NUM_VALUE => p_inventory_item_id
1491 ,P_PK2_NAME => g_mtl_sys_items_pk2_name
1492 ,P_PK2_NUM_VALUE => p_organization_id
1493 ,P_USER_ID => p_user_id
1494 );
1495
1496 END IF;
1497
1498 p_error_msg := 'SUCCESS';
1499 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1500 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1501 EXCEPTION
1502 WHEN others THEN
1503 x_return_status := FND_API.G_RET_STS_ERROR;
1504 p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1505 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
1506 RAISE;
1507 END MTL_SYSTEM_ITEMS_ACC_I;
1508
1509 PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
1510 p_organization_id IN NUMBER,
1511 p_user_id IN NUMBER,
1512 p_error_msg OUT NOCOPY VARCHAR2,
1513 x_return_status IN OUT NOCOPY VARCHAR2)
1514 IS
1515 BEGIN
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
1518 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1519
1520 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1521 CSM_ACC_PKG.Delete_Acc
1522 ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
1523 ,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
1524 ,P_PK1_NAME => g_mtl_sys_items_pk1_name
1525 ,P_PK1_NUM_VALUE => p_inventory_item_id
1526 ,P_PK2_NAME => g_mtl_sys_items_pk2_name
1527 ,P_PK2_NUM_VALUE => p_organization_id
1528 ,P_USER_ID => p_user_id
1529 );
1530 END IF;
1531
1532 p_error_msg := 'SUCCESS';
1533 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
1534 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1535 EXCEPTION
1536 WHEN others THEN
1537 x_return_status := FND_API.G_RET_STS_ERROR;
1538 p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1539 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1540 RAISE;
1541 END MTL_SYSTEM_ITEMS_ACC_D;
1542
1543 END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;