[Home] [Help]
PACKAGE BODY: APPS.CSM_SYSTEM_ITEM_EVENT_PKG
Source
1 PACKAGE BODY CSM_SYSTEM_ITEM_EVENT_PKG AS
2 /* $Header: csmemsib.pls 120.10 2008/02/06 12:44:13 anaraman ship $ */
3
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_SYSTEM_ITEMS_B';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_SYSTEM_ITEMS_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18 CSM_ACC_PKG.t_publication_item_list('CSF_M_SYSTEM_ITEMS');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
20 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
21 g_pub_item CONSTANT VARCHAR2(30) := 'CSF_M_SYSTEM_ITEMS';
22
23 g_table_name2 CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
24 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC';
25 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC_S';
26 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
27 CSM_ACC_PKG.t_publication_item_list('CSF_M_ITEM_INSTANCES');
28 g_pk1_name2 CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
29 g_pub_item2 CONSTANT VARCHAR2(30) := 'CSF_M_ITEM_INSTANCES';
30
31 -- below procedure is called from csm_mtl_system_items where there is an org change
32 PROCEDURE delete_system_items(p_user_id IN NUMBER,
33 p_organization_id IN NUMBER)
34 IS
35 l_sqlerrno VARCHAR2(20);
36 l_sqlerrmsg VARCHAR2(4000);
37 l_error_msg VARCHAR2(4000);
38 l_return_status VARCHAR2(2000);
39 l_stmt VARCHAR2(4000);
40 l_markdirty BOOLEAN;
41 l_run_date DATE;
42 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
43 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
44
45 BEGIN
46 CSM_UTIL_PKG.LOG('Entering DELETE_SYSTEM_ITEMS ',
47 'CSM_SYSTEM_ITEM_EVENT_PKG.DELETE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
48
49 l_run_date := SYSDATE;
50
51 l_tab_access_id.DELETE;
52
53 l_stmt := 'UPDATE csm_system_items_acc acc';
54 l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
55 l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
56 l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
57 l_stmt := l_stmt || ' WHERE USER_ID = :1';
58 l_stmt := l_stmt || ' AND organization_id = :2';
59
60 EXECUTE IMMEDIATE l_stmt USING p_user_id, p_organization_id;
61
62 -- bulk collect all items eligible for delete
63 l_tab_access_id.DELETE;
64 l_tab_user_id.DELETE;
65
66 SELECT access_id, user_id
67 BULK COLLECT INTO l_tab_access_id, l_tab_user_id
68 FROM csm_system_items_acc acc
69 WHERE acc.counter = 0;
70
71 IF l_tab_access_id.COUNT > 0 THEN
72 -- do bulk makedirty
73 l_markdirty := asg_download.mark_dirty(
74 P_PUB_ITEM => g_pub_item
75 , p_accessList => l_tab_access_id
76 , p_userid_list => l_tab_user_id
77 , p_dml_type => 'D'
78 , P_TIMESTAMP => l_run_date
79 );
80
81 FORALL i IN 1..l_tab_access_id.COUNT
82 DELETE FROM csm_system_items_acc WHERE access_id = l_tab_access_id(i);
83 END IF;
84
85 CSM_UTIL_PKG.LOG('Leaving DELETE_SYSTEM_ITEMS ',
86 'CSM_SYSTEM_ITEM_EVENT_PKG.DELETE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
87 EXCEPTION
88 WHEN others THEN
89 l_sqlerrno := TO_CHAR(SQLCODE);
90 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
91 CSM_UTIL_PKG.LOG('Exception in delete_system_items: ' || l_sqlerrno || ':' || l_sqlerrmsg,
92 'csm_system_item_event_pkg.delete_system_items',FND_LOG.LEVEL_EXCEPTION);
93 RAISE;
94 END delete_system_items;
95
96 /*
97 PROCEDURE PURGE_SYSTEM_ITEMS( p_itemtype in varchar2,
98 p_itemkey in varchar2,
99 p_actid in number,
100 p_funcmode in varchar2,
101 x_result OUT nocopy VARCHAR2 )
102 IS
103 g_pub_item VARCHAR2(30);
104 l_current_run_date DATE;
105 l_sqlerrno varchar2(20);
106 l_sqlerrmsg varchar2(2000);
107
108 CURSOR l_old_system_items_csr(p_old_organization_id IN number, p_user_id IN number)
109 IS
110 --SELECT /*+ index (acc CSM_SYSTEM_ITEMS_ACC_U2) acc.user_id,
111 acc.access_id
112 FROM csm_system_items_acc acc
113 WHERE acc.user_id = p_user_id
114 AND acc.organization_id = p_old_organization_id;
115
116 l_dummy BOOLEAN;
117 l_user_id_lst asg_download.user_list;
118 l_acc_id_lst asg_download.access_list;
119 l_old_organization_id mtl_system_items.organization_id%TYPE;
120 l_organization_id mtl_system_items.organization_id%TYPE;
121 l_userid NUMBER;
122
123 BEGIN
124 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.PURGE_SYSTEM_ITEMS ',
125 'CSM_SYSTEM_ITEM_EVENT_PKG.PURGE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
126
127 g_pub_item := 'CSF_M_SYSTEM_ITEMS';
128 l_current_run_date := SYSDATE;
129
130 IF (p_funcmode = 'RUN') THEN
131
132 l_userid := wf_engine.GetActivityAttrNumber( p_itemtype,
133 p_itemkey,
134 p_actid,
135 'USER_ID'
136 );
137
138 l_old_organization_id := wf_engine.GetItemAttrText( p_itemtype,
139 p_itemkey,
140 'OLD_PROFILE_OPTION_VALUE');
141
142 l_organization_id := wf_engine.GetItemAttrText( p_itemtype,
143 p_itemkey,
144 'PROFILE_OPTION_VALUE');
145
146 IF l_old_organization_id = l_organization_id THEN
147 x_result := 'Org is same - no system items purged';
148 ELSE
149
150 -- process deletes
151 OPEN l_old_system_items_csr(l_old_organization_id, l_userid);
152 FETCH l_old_system_items_csr BULK COLLECT INTO l_user_id_lst, l_acc_id_lst;
153 CLOSE l_old_system_items_csr;
154
155 -- post deletes to olite
156 IF l_acc_id_lst.COUNT > 0 THEN
157 -- do bulk makedirty
158 l_dummy := asg_download.mark_dirty(
159 P_PUB_ITEM => g_pub_item
160 , p_accessList => l_acc_id_lst
161 , p_userid_list => l_user_id_lst
162 , p_dml_type => 'D'
163 , P_TIMESTAMP => l_current_run_date
164 );
165
166 -- do a bulk delete
167 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
168 DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
169 END IF;
170
171 x_result := 'System Item purge complete';
172 END IF;
173
174 END IF;
175
176 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.PURGE_SYSTEM_ITEMS ',
177 'CSM_SYSTEM_ITEM_EVENT_PKG.PURGE_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 --logm('ErrorLog' || substr(SQLERRM, 1, 250));
182 x_result := ' FAILED PURGE_SYSTEM_ITEMS old_OrganizationId: ' || to_char(l_old_organization_id);
183 CSM_UTIL_PKG.LOG(x_result,'CSM_SYSTEM_ITEM_EVENT_PKG.PURGE_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
184 wf_core.context('CSM_SYSTEM_ITEM_EVENT_PKG', 'PURGE_SYSTEM_ITEMS', p_itemtype, p_itemkey, to_char(p_actid),
185 'Organization ID: ' || to_char(l_old_organization_id), p_funcmode);
186 RAISE;
187
188 END PURGE_SYSTEM_ITEMS;
189
190 PROCEDURE GET_NEW_SYSTEM_ITEMS( p_itemtype in varchar2,
191 p_itemkey in varchar2,
192 p_actid in number,
193 p_funcmode in varchar2,
194 x_result OUT nocopy VARCHAR2 )
195 IS
196 g_pub_item VARCHAR2(30);
197 l_current_run_date DATE;
198 l_sqlerrno varchar2(20);
199 l_sqlerrmsg varchar2(2000);
200
201 CURSOR l_systemitems_ins_csr(p_new_organization_id IN number, p_user_id IN number)
202 IS
203 --SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
204 au.user_id,
205 msi.inventory_item_id,
206 msi.organization_id
207 FROM asg_user au,
208 asg_user_pub_resps aupr,
209 mtl_system_items_b msi,
210 CS_BILLING_TYPE_CATEGORIES cbtc
211 WHERE au.user_id = p_user_id
212 AND au.user_name = aupr.user_name
213 AND aupr.pub_name = 'SERVICEP'
214 AND msi.organization_id = p_new_organization_id
215 AND msi.enabled_flag = 'Y'
216 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
217 AND msi.material_billable_flag = cbtc.billing_type
218 AND cbtc.billing_category IN ('L', 'E')
219 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
220 AND NOT EXISTS
221 (SELECT 1
222 FROM csm_system_items_acc acc
223 WHERE acc.user_id = au.user_id
224 AND acc.inventory_item_id = msi.inventory_item_id
225 AND acc.organization_id = msi.organization_id
226 );
227
228 TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
229 TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
230
231 l_inv_id inv_idTab;
232 l_org_id org_idTab;
233 l_user_id_lst asg_download.user_list;
234 l_acc_id_lst asg_download.access_list;
235
236 l_dummy BOOLEAN;
237 l_old_organization_id mtl_system_items.organization_id%TYPE;
238 l_organization_id mtl_system_items.organization_id%TYPE;
239 l_user_id NUMBER;
240
241 BEGIN
242 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.GET_NEW_SYSTEM_ITEMS ',
243 'CSM_SYSTEM_ITEM_EVENT_PKG.GET_NEW_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
244
245 g_pub_item := 'CSF_M_SYSTEM_ITEMS';
246 l_current_run_date := SYSDATE;
247
248 IF (p_funcmode = 'RUN') THEN
249 l_user_id := wf_engine.GetActivityAttrNumber( p_itemtype,
250 p_itemkey,
251 p_actid,
252 'USER_ID'
253 );
254
255 l_old_organization_id := wf_engine.GetItemAttrText( p_itemtype,
256 p_itemkey,
257 'OLD_PROFILE_OPTION_VALUE');
258
259 l_organization_id := wf_engine.GetItemAttrText( p_itemtype,
260 p_itemkey,
261 'PROFILE_OPTION_VALUE');
262
263
264 IF l_old_organization_id = l_organization_id THEN
265 x_result := 'Org is same - no system items purged';
266 ELSE
267
268 IF l_acc_id_lst.COUNT > 0 THEN
269 l_acc_id_lst.DELETE;
270 END IF;
271 IF l_user_id_lst.COUNT > 0 THEN
272 l_user_id_lst.DELETE;
273 END IF;
274 IF l_inv_id.COUNT > 0 THEN
275 l_inv_id.DELETE;
276 END IF;
277 IF l_org_id.COUNT > 0 THEN
278 l_org_id.DELETE;
279 END IF;
280
281 -- process inserts
282 OPEN l_systemitems_ins_csr (l_organization_id, l_user_id);
283 FETCH l_systemitems_ins_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_inv_id, l_org_id;
284 CLOSE l_systemitems_ins_csr;
285
286 IF l_acc_id_lst.COUNT > 0 THEN
287 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
288 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
289 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
290 VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
291
292 -- do bulk makedirty
293 l_dummy := asg_download.mark_dirty(
294 P_PUB_ITEM => g_pub_item
295 , p_accessList => l_acc_id_lst
296 , p_userid_list => l_user_id_lst
297 , p_dml_type => 'I'
298 , P_TIMESTAMP => l_current_run_date
299 );
300 END IF;
301
302 x_result := 'Get New System Items complete';
303 END IF;
304
305 END IF;
306
307 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.GET_NEW_SYSTEM_ITEMS ',
308 'CSM_SYSTEM_ITEM_EVENT_PKG.GET_NEW_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 x_result := ' FAILED GET_NEW_SYSTEM_ITEMS OrganizationId: ' || to_char(l_organization_id);
313 CSM_UTIL_PKG.LOG(x_result, 'CSM_SYSTEM_ITEM_EVENT_PKG.GET_NEW_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
314 wf_core.context('CSM_SYSTEM_ITEM_EVENT_PKG', 'GET_NEW_SYSTEM_ITEMS', p_itemtype, p_itemkey, to_char(p_actid),
315 'Organization ID: ' || to_char(l_organization_id), p_funcmode);
316 RAISE;
317
318 END GET_NEW_SYSTEM_ITEMS;
319 */
320 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
321 p_message OUT NOCOPY VARCHAR2) AS
322 PRAGMA AUTONOMOUS_TRANSACTION;
323 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
324 l_sqlerrno varchar2(20);
325 l_sqlerrmsg varchar2(2000);
326 l_current_run_date DATE;
327 l_max_last_update_date_b DATE;
328 l_max_last_update_date_tl DATE;
329 g_pub_item_name1 VARCHAR2(30) := 'CSF_M_SYSTEM_ITEMS';
330
331 TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
332 TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
333
334 l_inv_id inv_idTab;
335 l_org_id org_idTab;
336 l_user_id asg_download.user_list;
337 l_acc_id asg_download.access_list;
338
339 l_dummy BOOLEAN;
340
341 CURSOR l_last_run_date_csr
342 IS
343 SELECT nvl(last_run_date, (sysdate - 365*50))
344 FROM jtm_con_request_data
345 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
346 AND procedure_name = 'REFRESH_ACC';
347
348 CURSOR c_max_last_upd_date_b
349 IS
350 SELECT MAX(last_update_date) FROM mtl_system_items_b;
351
352 CURSOR c_max_last_upd_date_tl
353 IS
354 SELECT MAX(last_update_date) FROM mtl_system_items_tl;
355
356 -- insert
357 CURSOR l_systemitems_ins_csr
358 IS
359 SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
360 au.user_id,
361 msi.inventory_item_id,
362 msi.organization_id
363 FROM asg_user au,
364 asg_user_pub_resps aupr,
365 csm_user_inventory_org user_org,
366 mtl_system_items_b msi,
367 CS_BILLING_TYPE_CATEGORIES cbtc
368 WHERE au.user_name = aupr.user_name
369 AND aupr.pub_name = 'SERVICEP'
370 AND user_org.user_id = au.user_id
371 AND au.user_id = au.owner_id
372 AND msi.organization_id = user_org.organization_id
373 AND msi.enabled_flag = 'Y'
374 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
375 AND msi.material_billable_flag = cbtc.billing_type
376 AND cbtc.billing_category IN ('L', 'E')
377 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
378 AND NOT EXISTS
379 (SELECT 1
380 FROM csm_system_items_acc acc
381 WHERE acc.user_id = au.user_id
382 AND acc.inventory_item_id = msi.inventory_item_id
383 AND acc.organization_id = msi.organization_id
384 );
385
386 -- update
387 CURSOR l_systemitems_upd_b_csr(p_last_run_date DATE)
388 IS
389 SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) (msi MTL_SYSTEM_ITEMS_B_U1) */ acc.access_id,
390 acc.user_id,
391 msi.inventory_item_id,
392 msi.organization_id
393 FROM csm_system_items_acc acc,
394 mtl_system_items_b msi
395 WHERE acc.inventory_item_id = msi.inventory_item_id
396 AND acc.organization_id = msi.organization_id
397 AND msi.last_update_date >= p_last_run_date
398 ;
399
400 CURSOR l_systemitems_upd_tl_csr(p_last_run_date DATE)
401 IS
402 SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) (msi_tl MTL_SYSTEM_ITEMS_TL_U1) */ acc.access_id,
403 acc.user_id,
404 msi_tl.inventory_item_id,
405 msi_tl.organization_id
406 FROM csm_system_items_acc acc,
407 asg_user au,
408 mtl_system_items_tl msi_tl
409 WHERE acc.user_id = au.user_id
410 AND acc.inventory_item_id = msi_tl.inventory_item_id
411 AND acc.organization_id = msi_tl.organization_id
412 AND au.LANGUAGE = msi_tl.LANGUAGE
413 AND msi_tl.last_update_date >= p_last_run_date;
414
415 -- delete
416 CURSOR l_systemitems_del_csr
417 IS
418 SELECT /*+ index (acc csm_system_items_acc_u1)*/ acc.access_id,
419 acc.user_id,
420 acc.inventory_item_id,
421 acc.organization_id
422 FROM csm_system_items_acc acc
423 WHERE NOT EXISTS
424 (SELECT 1
425 FROM mtl_system_items_b msi,
426 CS_BILLING_TYPE_CATEGORIES cbtc
427 WHERE msi.inventory_item_id = acc.inventory_item_id
428 AND msi.organization_id = acc.organization_id
429 AND msi.material_billable_flag = cbtc.billing_type
430 AND cbtc.billing_category IN ('L', 'E')
431 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
432 AND msi.enabled_flag = 'Y'
433 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
434 );
435
436 BEGIN
437 -- set the run date
438 l_current_run_date := SYSDATE;
439
440 -- get last conc program update date
441 OPEN l_last_run_date_csr;
442 FETCH l_last_run_date_csr INTO l_last_run_date;
443 CLOSE l_last_run_date_csr;
444
445 -- process deletes
446 OPEN l_systemitems_del_csr;
447 LOOP
448 -- initialise the tables
449 IF l_acc_id.COUNT > 0 THEN
450 l_acc_id.DELETE;
451 END IF;
452 IF l_user_id.COUNT > 0 THEN
453 l_user_id.DELETE;
454 END IF;
455 IF l_inv_id.COUNT > 0 THEN
456 l_inv_id.DELETE;
457 END IF;
458 IF l_org_id.COUNT > 0 THEN
459 l_org_id.DELETE;
460 END IF;
461
462 FETCH l_systemitems_del_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id LIMIT 100;
463 EXIT WHEN l_acc_id.COUNT = 0;
464 -- post deletes to olite
465 IF l_acc_id.COUNT > 0 THEN
466 -- do bulk makedirty
467 l_dummy := asg_download.mark_dirty(
468 P_PUB_ITEM => g_pub_item_name1
469 , p_accessList => l_acc_id
470 , p_userid_list => l_user_id
471 , p_dml_type => 'D'
472 , P_TIMESTAMP => l_current_run_date
473 );
474
475 -- do a bulk delete
476 FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
477 DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id(i);
478 END IF;
479 END LOOP;
480 CLOSE l_systemitems_del_csr;
481
482
483 -- process updates
484
485 -- initialise the tables
486 IF l_acc_id.COUNT > 0 THEN
487 l_acc_id.DELETE;
488 END IF;
489 IF l_user_id.COUNT > 0 THEN
490 l_user_id.DELETE;
491 END IF;
492 IF l_inv_id.COUNT > 0 THEN
493 l_inv_id.DELETE;
494 END IF;
495 IF l_org_id.COUNT > 0 THEN
496 l_org_id.DELETE;
497 END IF;
498
499 /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
500 /* , MTL_SYSTEM_ITEMS_TL which were custom created */
501
502 -- get max last_upd_date from msi
503 OPEN c_max_last_upd_date_b;
504 FETCH c_max_last_upd_date_b INTO l_max_last_update_date_b;
505 CLOSE c_max_last_upd_date_b;
506
507 IF( l_max_last_update_date_b < l_last_run_date) THEN
508 -- get max last_upd_date from msi_tl
509 OPEN c_max_last_upd_date_tl;
510 FETCH c_max_last_upd_date_tl INTO l_max_last_update_date_tl;
511 CLOSE c_max_last_upd_date_tl;
512
513 IF l_max_last_update_date_tl < l_last_run_date THEN
514 -- no updates
515 p_status := 'FINE';
516 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS Executed successfully - No updates';
517 csm_util_pkg.log('No updates for csm_system_items_event_new_pkg', 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS ');
518
519 -- set the program update date in jtm_con_request_data to sysdate
520 UPDATE jtm_con_request_data
521 SET last_run_date = l_current_run_date
522 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
523 AND procedure_name = 'REFRESH_ACC';
524
525 COMMIT;
526
527 RETURN;
528 ELSE
529 -- open tl cursor
530 OPEN l_systemitems_upd_tl_csr(l_last_run_date);
531 FETCH l_systemitems_upd_tl_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id;
532 CLOSE l_systemitems_upd_tl_csr;
533 END IF;
534
535 ELSE
536 l_max_last_update_date_tl := l_max_last_update_date_b;
537 -- open the main b cursor
538 OPEN l_systemitems_upd_b_csr(l_last_run_date);
539 FETCH l_systemitems_upd_b_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id;
540 CLOSE l_systemitems_upd_b_csr;
541 END IF;
542
543 -- post updates to olite
544 IF l_acc_id.COUNT > 0 THEN
545 -- do bulk makedirty
546 l_dummy := asg_download.mark_dirty(
547 P_PUB_ITEM => g_pub_item_name1
548 , p_accessList => l_acc_id
549 , p_userid_list => l_user_id
550 , p_dml_type => 'U'
551 , P_TIMESTAMP => l_current_run_date
552 );
553 END IF;
554
555 -- process inserts
556 OPEN l_systemitems_ins_csr;
557 LOOP
558 -- initialise the tables
559 IF l_acc_id.COUNT > 0 THEN
560 l_acc_id.DELETE;
561 END IF;
562 IF l_user_id.COUNT > 0 THEN
563 l_user_id.DELETE;
564 END IF;
565 IF l_inv_id.COUNT > 0 THEN
566 l_inv_id.DELETE;
567 END IF;
568 IF l_org_id.COUNT > 0 THEN
569 l_org_id.DELETE;
570 END IF;
571
572 FETCH l_systemitems_ins_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id LIMIT 500;
573 EXIT WHEN l_acc_id.COUNT = 0;
574
575 IF l_acc_id.COUNT > 0 THEN
576 FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
577 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
578 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
579 VALUES (l_acc_id(i), l_user_id(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
580
581 -- do bulk makedirty
582 l_dummy := asg_download.mark_dirty(
583 P_PUB_ITEM => g_pub_item_name1
584 , p_accessList => l_acc_id
585 , p_userid_list => l_user_id
586 , p_dml_type => 'I'
587 , P_TIMESTAMP => l_current_run_date
588 );
589
590 END IF;
591 END LOOP;
592 CLOSE l_systemitems_ins_csr;
593
594 -- set the program update date in jtm_con_request_data to sysdate
595 UPDATE jtm_con_request_data
596 SET last_run_date = l_current_run_date
597 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
598 AND procedure_name = 'REFRESH_ACC';
599
600 COMMIT;
601
602 p_status := 'FINE';
603 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS Executed successfully';
604
605 EXCEPTION
606 WHEN others THEN
607 l_sqlerrno := to_char(SQLCODE);
608 l_sqlerrmsg := substr(SQLERRM, 1,2000);
609 p_status := 'ERROR';
610 p_message := 'Error in CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg;
611 ROLLBACK;
612 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
613 END Refresh_Acc;
614
615 /* concurrent program that refreshes inventory */
616 PROCEDURE Refresh_mtl_onhand_quantity(p_status OUT NOCOPY VARCHAR2,
617 p_message OUT NOCOPY VARCHAR2)
618 IS
619 PRAGMA AUTONOMOUS_TRANSACTION;
620 g_pub_item VARCHAR2(30) := 'CSF_M_INVENTORY';
621 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
622 l_sqlerrno varchar2(20);
623 l_sqlerrmsg varchar2(2000);
624
625 CURSOR l_onhand_acc_seq IS
626 SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL
627 FROM dual;
628
629 -- post deletes to onhand
630 CURSOR l_onhand_delete_csr IS
631 SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) */ ohqacc.user_id
632 , ohqacc.inventory_item_id
633 , ohqacc.organization_id
634 , ohqacc.ACCESS_ID
635 FROM csm_mtl_onhand_qty_acc ohqacc
636 WHERE NOT EXISTS
637 (
638 SELECT 1
639 FROM mtl_onhand_quantities_detail ohqmv
640 WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
641 AND ohqacc.organization_id = ohqmv.organization_id
642 AND ohqacc.subinventory_code = ohqmv.subinventory_code
643 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
644 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
645 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
646 )
647 OR NOT EXISTS
648 (
649 SELECT 1
650 FROM csm_inv_loc_ass_acc acc,
651 csp_inv_loc_assignments cila
652 WHERE acc.user_id = ohqacc.user_id
653 AND acc.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
654 AND cila.organization_id = ohqacc.organization_id
655 AND cila.subinventory_code = ohqacc.subinventory_code
656 );
657
658 -- get the updates to onhands for all mobile users
659 CURSOR l_onhand_update_csr IS
660 SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) index(ohqmv MTL_ONHAND_QUANTITIES_N4)*/ DISTINCT ohqacc.user_id
661 , ohqmv.INVENTORY_ITEM_ID
662 , ohqmv.ORGANIZATION_ID
663 , ohqmv.SUBINVENTORY_CODE
664 , ohqmv.LOCATOR_ID
665 , ohqmv.REVISION
666 , ohqmv.LOT_NUMBER
667 , ohqacc.quantity
668 , SUM(ohqmv.transaction_quantity) tot_qty
669 FROM csm_mtl_onhand_qty_acc ohqacc,
670 mtl_onhand_quantities_detail ohqmv
671 WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
672 AND ohqacc.organization_id = ohqmv.organization_id
673 AND ohqacc.subinventory_code = ohqmv.subinventory_code
674 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
675 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
676 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
677 HAVING SUM(ohqmv.transaction_quantity) <> NVL(ohqacc.quantity,0)
678 GROUP BY ohqacc.user_id, ohqmv.inventory_item_id, ohqmv.organization_id, ohqmv.subinventory_code,
679 ohqmv.locator_id, ohqmv.revision, ohqmv.lot_number, ohqacc.quantity
680 ;
681
682 -- get the onhand details for mobile subinventories that are not present
683 -- for some or all the mobile users
684 CURSOR l_onhand_insert_csr IS
685 SELECT distinct /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) index (ohqmv MTL_ONHAND_QUANTITIES_N5) */ au.user_id
686 , ohqmv.INVENTORY_ITEM_ID
687 , ohqmv.ORGANIZATION_ID
688 , ohqmv.SUBINVENTORY_CODE
689 , ohqmv.LOCATOR_ID
690 , ohqmv.REVISION
691 , ohqmv.LOT_NUMBER
692 , (SELECT SUM (ohqmv2.transaction_quantity)
693 FROM mtl_onhand_quantities_detail ohqmv2 WHERE
694 ohqmv.ORGANIZATION_ID=ohqmv2.ORGANIZATION_ID AND
695 ohqmv.SUBINVENTORY_CODE=ohqmv2.SUBINVENTORY_CODE AND
696 ohqmv.INVENTORY_ITEM_ID=ohqmv2.INVENTORY_ITEM_ID AND
697 nvl(ohqmv.LOCATOR_ID,-9999)=nvl(ohqmv2.LOCATOR_ID,-9999) AND
698 nvl(ohqmv.REVISION,-9999)=nvl(ohqmv2.REVISION,-9999) AND
699 nvl(ohqmv.LOT_NUMBER,-9999)=nvl(ohqmv2.LOT_NUMBER,-9999))
700 FROM asg_user au,
701 asg_user_pub_resps aupr,
702 mtl_onhand_quantities_detail ohqmv,
703 mtl_system_items_b msi,
704 CS_BILLING_TYPE_CATEGORIES cbtc
705 WHERE au.user_name = aupr.user_name
706 AND aupr.pub_name = 'SERVICEP'
707 AND au.user_id = au.owner_id
708 AND msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
709 AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
710 AND msi.mtl_transactions_enabled_flag = 'Y'
711 AND msi.material_billable_flag = cbtc.billing_type
712 AND cbtc.billing_category = 'M'
713 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
714 AND EXISTS
715 ( SELECT 1
716 FROM csm_inv_loc_ass_acc ilaacc1,
717 csp_inv_loc_assignments ila1
718 -- csp_sec_inventories csi --R12Not requirec as we are now downloading both the items in good and bad subinv
719 WHERE ilaacc1.user_id = au.user_id
720 AND ilaacc1.csp_inv_loc_assignment_id = ila1.csp_inv_loc_assignment_id
721 AND ila1.subinventory_code = ohqmv.subinventory_code
722 AND ila1.organization_id = ohqmv.organization_id
723 AND SYSDATE BETWEEN NVL(ila1.effective_date_start, SYSDATE) AND NVL(ila1.effective_date_end, SYSDATE)
724 -- AND csi.secondary_inventory_name = ila1.subinventory_code
725 -- AND csi.organization_id = ila1.organization_id
726 -- AND csi.condition_type IN('G','B') --R12-4681995
727 )
728 AND NOT EXISTS
729 (SELECT /*index (ohqacc CSM_MTL_ONHAND_QTY_ACC_U2)*/ 1
730 FROM csm_mtl_onhand_qty_acc ohqacc
731 WHERE ohqacc.user_id = au.user_id
732 AND (ohqacc.inventory_item_id = ohqmv.inventory_item_id )
733 AND (ohqacc.organization_id = ohqmv.organization_id )
734 AND (ohqacc.subinventory_code = ohqmv.subinventory_code )
735 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
736 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
737 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
738 );
739
740
741 TYPE inv_idTab IS TABLE OF csm_mtl_onhand_qty_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
742 TYPE org_idTab IS TABLE OF csm_mtl_onhand_qty_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
743 TYPE sub_codeTab IS TABLE OF csm_mtl_onhand_qty_acc.subinventory_code%TYPE INDEX BY BINARY_INTEGER;
744 TYPE rvsionTab IS TABLE OF csm_mtl_onhand_qty_acc.revision%TYPE INDEX BY BINARY_INTEGER;
745 TYPE loc_idTab IS TABLE OF csm_mtl_onhand_qty_acc.locator_id%TYPE INDEX BY BINARY_INTEGER;
746 TYPE lot_numTab IS TABLE OF csm_mtl_onhand_qty_acc.lot_number%TYPE INDEX BY BINARY_INTEGER;
747 TYPE tran_qtyTab IS TABLE OF mtl_onhand_quantities_detail.transaction_quantity%TYPE INDEX BY BINARY_INTEGER;
748 TYPE user_idTab IS TABLE OF asg_user.user_id%TYPE INDEX BY BINARY_INTEGER;
749 TYPE access_idTab IS TABLE OF csm_mtl_onhand_qty_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
750
751 inv_id inv_idTab;
752 org_id org_idTab;
753 sub_code sub_codeTab;
754 rvsion rvsionTab;
755 loc_id loc_idTab;
756 lot_num lot_numTab;
757 qty tran_qtyTab;
758 dummy_qty tran_qtyTab;
759 user_id_lst asg_download.user_list;
760 acc_id_lst asg_download.access_list;
761
762 l_dummy BOOLEAN;
763
764 l_current_run_date DATE;
765
766 BEGIN
767 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ',
768 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_PROCEDURE);
769
770 l_current_run_date := SYSDATE;
771
772 --*** Push deleted records to client ***
773
774 OPEN l_onhand_delete_csr;
775 LOOP
776 IF acc_id_lst.COUNT > 0 THEN
777 acc_id_lst.DELETE;
778 END IF;
779 IF user_id_lst.COUNT > 0 THEN
780 user_id_lst.DELETE;
781 END IF;
782 IF inv_id.COUNT > 0 THEN
783 inv_id.DELETE;
784 END IF;
785 IF org_id.COUNT > 0 THEN
786 org_id.DELETE;
787 END IF;
788 IF sub_code.COUNT > 0 THEN
789 sub_code.DELETE;
790 END IF;
791 IF rvsion.COUNT > 0 THEN
792 rvsion.DELETE;
793 END IF;
794 IF loc_id.COUNT > 0 THEN
795 loc_id.DELETE;
796 END IF;
797 IF lot_num.COUNT > 0 THEN
798 lot_num.DELETE;
799 END IF;
800 IF qty.COUNT > 0 THEN
801 qty.DELETE;
802 END IF;
803 IF dummy_qty.COUNT > 0 THEN
804 dummy_qty.DELETE;
805 END IF;
806
807 FETCH l_onhand_delete_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, acc_id_lst LIMIT 100;
808 EXIT WHEN acc_id_lst.COUNT = 0;
809
810 IF acc_id_lst.COUNT > 0 THEN
811 CSM_UTIL_PKG.LOG('Pushing ' || acc_id_lst.COUNT || 'deleted records',
812 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
813
814 -- do bulk makedirty
815 l_dummy := asg_download.mark_dirty(
816 P_PUB_ITEM => g_pub_item
817 , p_accessList => acc_id_lst
818 , p_userid_list => user_id_lst
819 , p_dml_type => 'D'
820 , P_TIMESTAMP => l_current_run_date
821 );
822
823 -- FORALL i IN acc_id_lst.FIRST..acc_id_lst.LAST
824 -- DELETE CSM_MTL_ONHAND_QTY_ACC WHERE ACCESS_ID = acc_id_lst(i);
825
826 FOR i IN 1..acc_id_lst.COUNT LOOP
827 DELETE CSM_MTL_ONHAND_QTY_ACC WHERE ACCESS_ID = acc_id_lst(i);
828 CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV(inv_id(i),org_id(i),user_id_lst(i));
829 END LOOP;
830
831 END IF; -- end of deletes
832 END LOOP;
833 CLOSE l_onhand_delete_csr;
834
835
836 --*** Push updated records to client ***
837
838 OPEN l_onhand_update_csr;
839 LOOP
840 IF acc_id_lst.COUNT > 0 THEN
841 acc_id_lst.DELETE;
842 END IF;
843 IF user_id_lst.COUNT > 0 THEN
844 user_id_lst.DELETE;
845 END IF;
846 IF inv_id.COUNT > 0 THEN
847 inv_id.DELETE;
848 END IF;
849 IF org_id.COUNT > 0 THEN
850 org_id.DELETE;
851 END IF;
852 IF sub_code.COUNT > 0 THEN
853 sub_code.DELETE;
854 END IF;
855 IF rvsion.COUNT > 0 THEN
856 rvsion.DELETE;
857 END IF;
858 IF loc_id.COUNT > 0 THEN
859 loc_id.DELETE;
860 END IF;
861 IF lot_num.COUNT > 0 THEN
862 lot_num.DELETE;
863 END IF;
864 IF qty.COUNT > 0 THEN
865 qty.DELETE;
866 END IF;
867 IF dummy_qty.COUNT > 0 THEN
868 dummy_qty.DELETE;
869 END IF;
870
871 FETCH l_onhand_update_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, sub_code, loc_id, rvsion, lot_num, dummy_qty, qty LIMIT 100;
872 EXIT WHEN user_id_lst.COUNT = 0;
873
874 IF user_id_lst.COUNT > 0 THEN
875 --*** push to oLite using asg_download ***
876 CSM_UTIL_PKG.LOG('Pushing ' || user_id_lst.COUNT || 'updated records',
877 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
878
879 FORALL i IN user_id_lst.FIRST..user_id_lst.LAST
880 UPDATE CSM_MTL_ONHAND_QTY_ACC
881 SET LAST_UPDATE_DATE = l_current_run_date,
882 QUANTITY = qty(i)
883 WHERE user_id = user_id_lst(i)
884 AND inventory_item_id = inv_id(i)
885 AND organization_id = org_id(i)
886 AND subinventory_code = sub_code(i)
887 AND (REVISION IS NULL OR revision = rvsion(i))
888 AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
889 AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
890 RETURNING access_id BULK COLLECT INTO acc_id_lst;
891
892 -- do bulk makedirty
893 l_dummy := asg_download.mark_dirty(
894 P_PUB_ITEM => g_pub_item
895 , p_accessList => acc_id_lst
896 , p_userid_list => user_id_lst
897 , p_dml_type => 'U'
898 , P_TIMESTAMP => l_current_run_date
899 );
900
901 END IF; -- end of updates
902 END LOOP;
903 CLOSE l_onhand_update_csr;
904
905 --*** Push inserted records to client ***
906
907 OPEN l_onhand_insert_csr;
908 LOOP
909 IF acc_id_lst.COUNT > 0 THEN
910 acc_id_lst.DELETE;
911 END IF;
912 IF user_id_lst.COUNT > 0 THEN
913 user_id_lst.DELETE;
914 END IF;
915 IF inv_id.COUNT > 0 THEN
916 inv_id.DELETE;
917 END IF;
918 IF org_id.COUNT > 0 THEN
919 org_id.DELETE;
920 END IF;
921 IF sub_code.COUNT > 0 THEN
922 sub_code.DELETE;
923 END IF;
924 IF rvsion.COUNT > 0 THEN
925 rvsion.DELETE;
926 END IF;
927 IF loc_id.COUNT > 0 THEN
928 loc_id.DELETE;
929 END IF;
930 IF lot_num.COUNT > 0 THEN
931 lot_num.DELETE;
932 END IF;
933 IF qty.COUNT > 0 THEN
934 qty.DELETE;
935 END IF;
936 IF dummy_qty.COUNT > 0 THEN
937 dummy_qty.DELETE;
938 END IF;
939
940 FETCH l_onhand_insert_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, sub_code, loc_id, rvsion, lot_num, qty LIMIT 500;
941 EXIT WHEN inv_id.COUNT = 0;
942
943 -- check if there are any items to be downloaded
944 IF inv_id.COUNT > 0 THEN
945 --*** push to oLite using asg_download ***
946 CSM_UTIL_PKG.LOG('Pushing ' || inv_id.COUNT || 'inserted records',
947 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
948
949 FOR i IN inv_id.FIRST..inv_id.LAST LOOP
950 SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL INTO acc_id_lst(i) FROM dual;
951 END LOOP;
952
953 FORALL i IN inv_id.FIRST..inv_id.LAST
954 INSERT INTO CSM_MTL_ONHAND_QTY_ACC (ACCESS_ID, user_id, INVENTORY_ITEM_ID,ORGANIZATION_ID,
955 SUBINVENTORY_CODE,LOCATOR_ID,REVISION,LOT_NUMBER, LAST_UPDATE_DATE,LAST_UPDATED_BY,
956 CREATION_DATE,CREATED_BY, LAST_UPDATE_LOGIN, QUANTITY, GEN_PK) VALUES (acc_id_lst(i), user_id_lst(i), inv_id(i), org_id(i), sub_code(i),
957 loc_id(i), rvsion(i), lot_num(i), l_current_run_date,1,l_current_run_date,1, 1, qty(i), acc_id_lst(i));
958
959 -- do bulk makedirty
960 l_dummy := asg_download.mark_dirty(
961 P_PUB_ITEM => g_pub_item
962 , p_accessList => acc_id_lst
963 , p_userid_list => user_id_lst
964 , p_dml_type => 'I'
965 , P_TIMESTAMP => l_current_run_date
966 );
967
968 END IF;
969 END LOOP;
970 CLOSE l_onhand_insert_csr;
971
972 -- set the program update date in jtm_con_request_data to sysdate
973 UPDATE jtm_con_request_data
974 SET last_run_date = l_current_run_date
975 WHERE product_code = 'CSM'
976 AND package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
977 AND procedure_name = 'REFRESH_MTL_ONHAND_QUANTITY';
978
979 COMMIT;
980
981 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ',
982 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_PROCEDURE);
983
984 p_status := 'FINE';
985 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY Executed successfully';
986
987 EXCEPTION
988 WHEN others THEN
989 l_sqlerrno := to_char(SQLCODE);
990 l_sqlerrmsg := substr(SQLERRM, 1,2000);
991 p_status := 'ERROR';
992 p_message := 'Error in CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY: ' || l_sqlerrno || ':' || l_sqlerrmsg;
993 ROLLBACK;
994 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
995
996 END Refresh_mtl_onhand_quantity;
997
998 PROCEDURE get_new_user_system_items(p_user_id IN NUMBER)
999 IS
1000 g_pub_item VARCHAR2(30);
1001 l_current_run_date DATE;
1002 l_sqlerrno varchar2(20);
1003 l_sqlerrmsg varchar2(2000);
1004
1005 CURSOR l_systemitems_ins_csr(p_organization_id IN NUMBER, p_user_id IN NUMBER)
1006 IS
1007 SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
1008 au.user_id,
1009 msi.inventory_item_id,
1010 msi.organization_id
1011 FROM asg_user au,
1012 asg_user_pub_resps aupr,
1013 mtl_system_items_b msi,
1014 CS_BILLING_TYPE_CATEGORIES cbtc
1015 WHERE au.user_id = p_user_id
1016 AND au.user_name = aupr.user_name
1017 AND aupr.pub_name = 'SERVICEP'
1018 AND msi.organization_id = p_organization_id
1019 AND msi.enabled_flag = 'Y'
1020 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
1021 AND msi.material_billable_flag = cbtc.billing_type
1022 AND cbtc.billing_category IN ('L', 'E')
1023 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
1024 AND NOT EXISTS
1025 (SELECT 1
1026 FROM csm_system_items_acc acc
1027 WHERE acc.user_id = au.user_id
1028 AND acc.inventory_item_id = msi.inventory_item_id
1029 AND acc.organization_id = msi.organization_id
1030 );
1031
1032 TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1033 TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
1034
1035 l_inv_id inv_idTab;
1036 l_org_id org_idTab;
1037 l_user_id_lst asg_download.user_list;
1038 l_acc_id_lst asg_download.access_list;
1039
1040 l_dummy BOOLEAN;
1041 l_organization_id mtl_system_items.organization_id%TYPE;
1042
1043 BEGIN
1044 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ',
1045 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1046
1047 g_pub_item := 'CSF_M_SYSTEM_ITEMS';
1048 l_current_run_date := SYSDATE;
1049 l_organization_id := csm_profile_pkg.get_organization_id(p_user_id);
1050
1051 -- process inserts
1052 OPEN l_systemitems_ins_csr (p_organization_id=>l_organization_id, p_user_id=>p_user_id);
1053 LOOP
1054 l_acc_id_lst.DELETE;
1055 l_user_id_lst.DELETE;
1056 l_inv_id.DELETE;
1057 l_org_id.DELETE;
1058
1059 FETCH l_systemitems_ins_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_inv_id, l_org_id LIMIT 100;
1060 EXIT WHEN l_acc_id_lst.COUNT = 0;
1061
1062 IF l_acc_id_lst.COUNT > 0 THEN
1063 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
1064 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1065 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1066 VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_inv_id(i), l_org_id(i), 1, 1, l_current_run_date,1,l_current_run_date,1);
1067
1068 -- do bulk makedirty
1069 l_dummy := asg_download.mark_dirty(
1070 P_PUB_ITEM => g_pub_item
1071 , p_accessList => l_acc_id_lst
1072 , p_userid_list => l_user_id_lst
1073 , p_dml_type => 'I'
1074 , P_TIMESTAMP => l_current_run_date
1075 );
1076 END IF;
1077 END LOOP;
1078 CLOSE l_systemitems_ins_csr;
1079
1080 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ',
1081 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1082
1083 EXCEPTION
1084 WHEN others THEN
1085 l_sqlerrno := to_char(SQLCODE);
1086 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1087 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, FND_LOG.LEVEL_EXCEPTION);
1088 RAISE;
1089 END get_new_user_system_items;
1090
1091 PROCEDURE SYSTEM_ITEM_MDIRTY_I(p_inventory_item_id IN NUMBER,
1092 p_organization_id IN NUMBER,
1093 p_user_id IN NUMBER)
1094 IS
1095 l_sqlerrno VARCHAR2(20);
1096 l_sqlerrmsg VARCHAR2(4000);
1097 l_error_msg VARCHAR2(4000);
1098 l_return_status VARCHAR2(2000);
1099
1100 BEGIN
1101 CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id,
1102 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
1103
1104 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1105 -- insert into csm_system_items_acc
1106 CSM_ACC_PKG.Insert_Acc
1107 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1108 ,P_ACC_TABLE_NAME => g_acc_table_name1
1109 ,P_SEQ_NAME => g_acc_sequence_name1
1110 ,P_PK1_NAME => g_pk1_name1
1111 ,P_PK1_NUM_VALUE => p_inventory_item_id
1112 ,P_PK2_NAME => g_pk2_name1
1113 ,P_PK2_NUM_VALUE => p_organization_id
1114 ,P_USER_ID => p_user_id
1115 );
1116 END IF;
1117
1118 CSM_UTIL_PKG.LOG('Leaving SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id,
1119 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 l_sqlerrno := to_char(SQLCODE);
1123 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1124 l_error_msg := ' Exception in SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id
1125 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1126 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
1127 RAISE;
1128 END SYSTEM_ITEM_MDIRTY_I;
1129
1130 PROCEDURE SYSTEM_ITEM_MDIRTY_D(p_inventory_item_id IN NUMBER,
1131 p_organization_id IN NUMBER,
1132 p_user_id IN NUMBER)
1133 IS
1134 l_sqlerrno VARCHAR2(20);
1135 l_sqlerrmsg VARCHAR2(4000);
1136 l_error_msg VARCHAR2(4000);
1137 l_return_status VARCHAR2(2000);
1138
1139 BEGIN
1140 CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id,
1141 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
1142
1143 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1144 -- delete from csm_system_items_acc
1145 CSM_ACC_PKG.Delete_Acc
1146 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1147 ,P_ACC_TABLE_NAME => g_acc_table_name1
1148 ,P_PK1_NAME => g_pk1_name1
1149 ,P_PK1_NUM_VALUE => p_inventory_item_id
1150 ,P_PK2_NAME => g_pk2_name1
1151 ,P_PK2_NUM_VALUE => p_organization_id
1152 ,P_USER_ID => p_user_id
1153 );
1154 END IF;
1155
1156 CSM_UTIL_PKG.LOG('Leaving SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id,
1157 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 l_sqlerrno := to_char(SQLCODE);
1161 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1162 l_error_msg := ' Exception in SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id
1163 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1164 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
1165 RAISE;
1166 END SYSTEM_ITEM_MDIRTY_D;
1167
1168 END CSM_SYSTEM_ITEM_EVENT_PKG;
1169
1170
1171 -- End of DDL Script for Package Body APPS.CSM_SYSTEM_ITEM_EVENT_PKG