[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.12020000.2 2013/04/09 10:56:26 saradhak ship $ */
3
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_table_name1 CONSTANT VARCHAR2(30) := '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 l_curr_user_id asg_download.user_list;
339
340 l_dummy BOOLEAN;
341
342 l_org_count NUMBER;
343 l_su_user_id NUMBER;
344 l_su_organization_id NUMBER;
345
346 CURSOR l_last_run_date_csr
347 IS
348 SELECT nvl(last_run_date, (sysdate - 365*50))
349 FROM jtm_con_request_data
350 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
351 AND procedure_name = 'REFRESH_ACC';
352
353 CURSOR c_max_last_upd_date_b
354 IS
355 SELECT MAX(last_update_date) FROM mtl_system_items_b;
356
357 CURSOR c_max_last_upd_date_tl
358 IS
359 SELECT MAX(last_update_date) FROM mtl_system_items_tl;
360
361 -- insert
362 CURSOR l_systemitems_ins_csr
363 IS
364 SELECT CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
365 au.user_id,
366 msi.inventory_item_id,
367 msi.organization_id
368 FROM asg_user au,
369 csm_user_inventory_org user_org,
370 mtl_system_items_b msi,
371 CS_BILLING_TYPE_CATEGORIES cbtc
372 WHERE user_org.user_id = au.user_id
373 AND au.user_id = au.owner_id
374 AND msi.organization_id = user_org.organization_id
375 AND msi.enabled_flag = 'Y'
376 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
377 AND msi.material_billable_flag = cbtc.billing_type
378 AND cbtc.billing_category IN ('L', 'E')
379 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
380 AND NOT EXISTS (SELECT 1 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 -- insert single user
386 CURSOR l_systemitems_ins_su_csr (p_user_id NUMBER,
387 p_user_organization_id NUMBER)
388 IS
389 SELECT msi.inventory_item_id,
390 msi.organization_id
391 FROM mtl_system_items_b msi,
392 CS_BILLING_TYPE_CATEGORIES cbtc
393 WHERE msi.organization_id = p_user_organization_id
394 AND msi.enabled_flag = 'Y'
395 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
396 AND msi.material_billable_flag = cbtc.billing_type
397 AND cbtc.billing_category IN ('L', 'E')
398 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
399 AND NOT EXISTS (SELECT 1 FROM csm_system_items_acc acc
400 WHERE acc.user_id = p_user_id
401 AND acc.inventory_item_id = msi.inventory_item_id
402 AND acc.organization_id = p_user_organization_id);
403
404 -- update
405 CURSOR l_systemitems_upd_b_csr(p_last_run_date DATE)
406 IS
407 SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
408 acc.user_id,
409 msi.inventory_item_id,
410 msi.organization_id
411 FROM csm_system_items_acc acc,
412 mtl_system_items_b msi
413 WHERE acc.inventory_item_id = msi.inventory_item_id
414 AND acc.organization_id = msi.organization_id
415 AND msi.last_update_date >= p_last_run_date
416 ;
417
418 CURSOR l_systemitems_upd_tl_csr(p_last_run_date DATE)
419 IS
420 SELECT /*+ INDEX (acc CSM_SYSTEM_ITEMS_ACC_U1) */ acc.access_id,
421 acc.user_id,
422 msi_tl.inventory_item_id,
423 msi_tl.organization_id
424 FROM csm_system_items_acc acc,
425 asg_user au,
426 mtl_system_items_tl msi_tl
427 WHERE acc.user_id = au.user_id
428 AND acc.inventory_item_id = msi_tl.inventory_item_id
429 AND acc.organization_id = msi_tl.organization_id
430 AND au.LANGUAGE = msi_tl.LANGUAGE
431 AND msi_tl.last_update_date >= p_last_run_date;
432
433 -- delete
434 CURSOR l_systemitems_del_csr
435 IS
436 SELECT /*+ index (acc csm_system_items_acc_u1)*/ acc.access_id,
437 acc.user_id,
438 acc.inventory_item_id,
439 acc.organization_id
440 FROM csm_system_items_acc acc
441 WHERE NOT EXISTS
442 (SELECT 1
443 FROM mtl_system_items_b msi,
444 CS_BILLING_TYPE_CATEGORIES cbtc
445 WHERE msi.inventory_item_id = acc.inventory_item_id
446 AND msi.organization_id = acc.organization_id
447 AND msi.material_billable_flag = cbtc.billing_type
448 AND cbtc.billing_category IN ('L', 'E')
449 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
450 AND msi.enabled_flag = 'Y'
451 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
452 );
453
454 BEGIN
455 -- set the run date
456 l_current_run_date := SYSDATE;
457
458 -- get last conc program update date
459 OPEN l_last_run_date_csr;
460 FETCH l_last_run_date_csr INTO l_last_run_date;
461 CLOSE l_last_run_date_csr;
462
463 -- process deletes
464 OPEN l_systemitems_del_csr;
465 LOOP
466 -- initialise the tables
467 IF l_acc_id.COUNT > 0 THEN
468 l_acc_id.DELETE;
469 END IF;
470 IF l_user_id.COUNT > 0 THEN
471 l_user_id.DELETE;
472 END IF;
473 IF l_inv_id.COUNT > 0 THEN
474 l_inv_id.DELETE;
475 END IF;
476 IF l_org_id.COUNT > 0 THEN
477 l_org_id.DELETE;
478 END IF;
479
480 FETCH l_systemitems_del_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id LIMIT 1000;
481 EXIT WHEN l_acc_id.COUNT = 0;
482 -- post deletes to olite
483 IF l_acc_id.COUNT > 0 THEN
484 -- do bulk makedirty
485 l_dummy := asg_download.mark_dirty(
486 P_PUB_ITEM => g_pub_item_name1
487 , p_accessList => l_acc_id
488 , p_userid_list => l_user_id
489 , p_dml_type => 'D'
490 , P_TIMESTAMP => l_current_run_date
491 );
492
493 -- do a bulk delete
494 FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
495 DELETE CSM_SYSTEM_ITEMS_ACC WHERE ACCESS_ID = l_acc_id(i);
496 END IF;
497 END LOOP;
498 CLOSE l_systemitems_del_csr;
499
500
501 -- process updates
502
503 -- initialise the tables
504 IF l_acc_id.COUNT > 0 THEN
505 l_acc_id.DELETE;
506 END IF;
507 IF l_user_id.COUNT > 0 THEN
508 l_user_id.DELETE;
509 END IF;
510 IF l_inv_id.COUNT > 0 THEN
511 l_inv_id.DELETE;
512 END IF;
513 IF l_org_id.COUNT > 0 THEN
514 l_org_id.DELETE;
515 END IF;
516
517 /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
518 /* , MTL_SYSTEM_ITEMS_TL which were custom created */
519
520 -- get max last_upd_date from msi
521 OPEN c_max_last_upd_date_b;
522 FETCH c_max_last_upd_date_b INTO l_max_last_update_date_b;
523 CLOSE c_max_last_upd_date_b;
524
525 IF( l_max_last_update_date_b < l_last_run_date) THEN
526 -- get max last_upd_date from msi_tl
527 OPEN c_max_last_upd_date_tl;
528 FETCH c_max_last_upd_date_tl INTO l_max_last_update_date_tl;
529 CLOSE c_max_last_upd_date_tl;
530
531 IF l_max_last_update_date_tl < l_last_run_date THEN
532 -- no updates
533 p_status := 'FINE';
534 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS Executed successfully - No updates';
535 csm_util_pkg.log('No updates for csm_system_items_event_new_pkg', 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS ');
536
537 -- set the program update date in jtm_con_request_data to sysdate
538 UPDATE jtm_con_request_data
539 SET last_run_date = l_current_run_date
540 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
541 AND procedure_name = 'REFRESH_ACC';
542
543 COMMIT;
544
545 RETURN;
546 ELSE
547 -- open tl cursor
548 OPEN l_systemitems_upd_tl_csr(l_last_run_date);
549 FETCH l_systemitems_upd_tl_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id;
550 CLOSE l_systemitems_upd_tl_csr;
551 END IF;
552
553 ELSE
554 l_max_last_update_date_tl := l_max_last_update_date_b;
555 -- open the main b cursor
556 OPEN l_systemitems_upd_b_csr(l_last_run_date);
557 FETCH l_systemitems_upd_b_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id;
558 CLOSE l_systemitems_upd_b_csr;
559 END IF;
560
561 -- post updates to olite
562 IF l_acc_id.COUNT > 0 THEN
563 -- do bulk makedirty
564 l_dummy := asg_download.mark_dirty(
565 P_PUB_ITEM => g_pub_item_name1
566 , p_accessList => l_acc_id
567 , p_userid_list => l_user_id
568 , p_dml_type => 'U'
569 , P_TIMESTAMP => l_current_run_date
570 );
571 END IF;
572
573 -- process inserts
574
575 -- Check for single organization_id configuration
576 SELECT count(distinct organization_id) into l_org_count
577 FROM csm_user_inventory_org;
578
579 -- Process separately when all user have same organization_id
580 IF (l_org_count = 1) THEN
581
582 -- Choose one user to process
583 SELECT user_id, organization_id into l_su_user_id, l_su_organization_id
584 FROM csm_user_inventory_org
585 WHERE rownum=1;
586
587 -- Get all the users list
588 IF l_user_id.COUNT > 0 THEN
589 l_user_id.DELETE;
590 END IF;
591 l_user_id := csm_util_pkg.get_all_omfs_palm_user_list();
592
593 -- Get all the new items
594 OPEN l_systemitems_ins_su_csr (l_su_user_id,l_su_organization_id);
595 LOOP
596
597 IF l_inv_id.COUNT > 0 THEN
598 l_inv_id.DELETE;
599 END IF;
600 IF l_org_id.COUNT > 0 THEN
601 l_org_id.DELETE;
602 END IF;
603
604 FETCH l_systemitems_ins_su_csr BULK COLLECT INTO l_inv_id, l_org_id LIMIT 1000;
605 EXIT WHEN l_inv_id.COUNT=0;
606
607 IF l_inv_id.COUNT > 0 THEN
608
609 FOR i IN l_user_id.FIRST..l_user_id.LAST LOOP
610
611 IF l_acc_id.COUNT > 0 THEN
612 l_acc_id.DELETE;
613 END IF;
614 IF l_curr_user_id.COUNT > 0 THEN
615 l_curr_user_id.DELETE;
616 END IF;
617
618 -- One change compared to other insert is that we are not
619 -- checking if the record exists before inserting into the _acc table
620 FORALL j in l_inv_id.FIRST..l_inv_id.LAST
621 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
622 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
623 VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, l_user_id(i),
624 l_inv_id(j), l_org_id(j), 1, 1, l_current_run_date, 1,
625 l_current_run_date, 1)
626 RETURNING access_id, user_id BULK COLLECT INTO l_acc_id, l_curr_user_id;
627
628 -- do bulk makedirty
629 l_dummy := asg_download.mark_dirty(
630 P_PUB_ITEM => g_pub_item_name1
631 , p_accessList => l_acc_id
632 , p_userid_list => l_curr_user_id
633 , p_dml_type => 'I'
634 , P_TIMESTAMP => l_current_run_date
635 );
636
637 END LOOP; -- End Loop for i IN _user_id.FIRST...
638
639 END IF;
640
641 END LOOP;
642 CLOSE l_systemitems_ins_su_csr;
643
644 ELSE
645 OPEN l_systemitems_ins_csr;
646 LOOP
647 -- initialise the tables
648 IF l_acc_id.COUNT > 0 THEN
649 l_acc_id.DELETE;
650 END IF;
651 IF l_user_id.COUNT > 0 THEN
652 l_user_id.DELETE;
653 END IF;
654 IF l_inv_id.COUNT > 0 THEN
655 l_inv_id.DELETE;
656 END IF;
657 IF l_org_id.COUNT > 0 THEN
658 l_org_id.DELETE;
659 END IF;
660
661 FETCH l_systemitems_ins_csr BULK COLLECT INTO l_acc_id, l_user_id, l_inv_id, l_org_id LIMIT 1000;
662 EXIT WHEN l_acc_id.COUNT = 0;
663
664 IF l_acc_id.COUNT > 0 THEN
665 FORALL i IN l_acc_id.FIRST..l_acc_id.LAST
666 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
667 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
668 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);
669
670 -- do bulk makedirty
671 l_dummy := asg_download.mark_dirty(
672 P_PUB_ITEM => g_pub_item_name1
673 , p_accessList => l_acc_id
674 , p_userid_list => l_user_id
675 , p_dml_type => 'I'
676 , P_TIMESTAMP => l_current_run_date
677 );
678
679 END IF;
680 END LOOP;
681 CLOSE l_systemitems_ins_csr;
682 END IF;
683 -- set the program update date in jtm_con_request_data to sysdate
684 UPDATE jtm_con_request_data
685 SET last_run_date = l_current_run_date
686 WHERE package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
687 AND procedure_name = 'REFRESH_ACC';
688
689 COMMIT;
690
691 p_status := 'FINE';
692 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS Executed successfully';
693
694 EXCEPTION
695 WHEN others THEN
696 l_sqlerrno := to_char(SQLCODE);
697 l_sqlerrmsg := substr(SQLERRM, 1,2000);
698 p_status := 'ERROR';
699 p_message := 'Error in CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg;
700 ROLLBACK;
701 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_SYSTEM_ITEMS ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
702 END Refresh_Acc;
703
704 /* concurrent program that refreshes inventory */
705 PROCEDURE Refresh_mtl_onhand_quantity(p_status OUT NOCOPY VARCHAR2,
706 p_message OUT NOCOPY VARCHAR2)
707 IS
708 PRAGMA AUTONOMOUS_TRANSACTION;
709 g_pub_item VARCHAR2(30) := 'CSF_M_INVENTORY';
710 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
711 l_sqlerrno varchar2(20);
712 l_sqlerrmsg varchar2(2000);
713
714 CURSOR l_onhand_acc_seq IS
715 SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL
716 FROM dual;
717
718 -- post deletes to onhand
719 CURSOR l_onhand_delete_csr IS
720 SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) */ ohqacc.user_id
721 , ohqacc.inventory_item_id
722 , ohqacc.organization_id
723 , ohqacc.ACCESS_ID
724 FROM csm_mtl_onhand_qty_acc ohqacc
725 WHERE NOT EXISTS
726 (
727 SELECT 1
728 FROM mtl_onhand_quantities_detail ohqmv
729 WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
730 AND ohqacc.organization_id = ohqmv.organization_id
731 AND ohqacc.subinventory_code = ohqmv.subinventory_code
732 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
733 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
734 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
735 )
736 OR NOT EXISTS
737 (
738 SELECT 1
739 FROM csm_inv_loc_ass_acc acc,
740 csp_inv_loc_assignments cila
741 WHERE acc.user_id = ohqacc.user_id
742 AND acc.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
743 AND cila.organization_id = ohqacc.organization_id
744 AND cila.subinventory_code = ohqacc.subinventory_code
745 );
746
747 -- get the updates to onhands for all mobile users
748 CURSOR l_onhand_update_csr IS
749 SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) index(ohqmv MTL_ONHAND_QUANTITIES_N4)*/ DISTINCT ohqacc.user_id
750 , ohqmv.INVENTORY_ITEM_ID
751 , ohqmv.ORGANIZATION_ID
752 , ohqmv.SUBINVENTORY_CODE
753 , ohqmv.LOCATOR_ID
754 , ohqmv.REVISION
755 , ohqmv.LOT_NUMBER
756 , ohqacc.quantity
757 , SUM(ohqmv.transaction_quantity) tot_qty
758 FROM csm_mtl_onhand_qty_acc ohqacc,
759 mtl_onhand_quantities_detail ohqmv
760 WHERE ohqacc.inventory_item_id = ohqmv.inventory_item_id
761 AND ohqacc.organization_id = ohqmv.organization_id
762 AND ohqacc.subinventory_code = ohqmv.subinventory_code
763 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
764 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
765 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
766 HAVING SUM(ohqmv.transaction_quantity) <> NVL(ohqacc.quantity,0)
767 GROUP BY ohqacc.user_id, ohqmv.inventory_item_id, ohqmv.organization_id, ohqmv.subinventory_code,
768 ohqmv.locator_id, ohqmv.revision, ohqmv.lot_number, ohqacc.quantity
769 ;
770
771 -- get the onhand details for mobile subinventories that are not present
772 -- for some or all the mobile users
773 CURSOR l_onhand_insert_csr IS
774 SELECT distinct /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) index (ohqmv MTL_ONHAND_QUANTITIES_N5) */ au.user_id
775 , ohqmv.INVENTORY_ITEM_ID
776 , ohqmv.ORGANIZATION_ID
777 , ohqmv.SUBINVENTORY_CODE
778 , ohqmv.LOCATOR_ID
779 , ohqmv.REVISION
780 , ohqmv.LOT_NUMBER
781 , (SELECT SUM (ohqmv2.transaction_quantity)
782 FROM mtl_onhand_quantities_detail ohqmv2 WHERE
783 ohqmv.ORGANIZATION_ID=ohqmv2.ORGANIZATION_ID AND
784 ohqmv.SUBINVENTORY_CODE=ohqmv2.SUBINVENTORY_CODE AND
785 ohqmv.INVENTORY_ITEM_ID=ohqmv2.INVENTORY_ITEM_ID AND
786 nvl(ohqmv.LOCATOR_ID,-9999)=nvl(ohqmv2.LOCATOR_ID,-9999) AND
787 nvl(ohqmv.REVISION,-9999)=nvl(ohqmv2.REVISION,-9999) AND
788 nvl(ohqmv.LOT_NUMBER,-9999)=nvl(ohqmv2.LOT_NUMBER,-9999))
789 FROM asg_user au,
790 asg_user_pub_resps aupr,
791 mtl_onhand_quantities_detail ohqmv,
792 mtl_system_items_b msi,
793 CS_BILLING_TYPE_CATEGORIES cbtc
794 WHERE au.user_name = aupr.user_name
795 AND aupr.pub_name = 'SERVICEP'
796 AND au.user_id = au.owner_id
797 AND msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
798 AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
799 AND msi.mtl_transactions_enabled_flag = 'Y'
800 AND msi.material_billable_flag = cbtc.billing_type
801 AND cbtc.billing_category = 'M'
802 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
803 AND EXISTS
804 ( SELECT 1
805 FROM csm_inv_loc_ass_acc ilaacc1,
806 csp_inv_loc_assignments ila1
807 -- csp_sec_inventories csi --R12Not requirec as we are now downloading both the items in good and bad subinv
808 WHERE ilaacc1.user_id = au.user_id
809 AND ilaacc1.csp_inv_loc_assignment_id = ila1.csp_inv_loc_assignment_id
810 AND ila1.subinventory_code = ohqmv.subinventory_code
811 AND ila1.organization_id = ohqmv.organization_id
812 AND SYSDATE BETWEEN NVL(ila1.effective_date_start, SYSDATE) AND NVL(ila1.effective_date_end, SYSDATE)
813 -- AND csi.secondary_inventory_name = ila1.subinventory_code
814 -- AND csi.organization_id = ila1.organization_id
815 -- AND csi.condition_type IN('G','B') --R12-4681995
816 )
817 AND NOT EXISTS
818 (SELECT /*index (ohqacc CSM_MTL_ONHAND_QTY_ACC_U2)*/ 1
819 FROM csm_mtl_onhand_qty_acc ohqacc
820 WHERE ohqacc.user_id = au.user_id
821 AND (ohqacc.inventory_item_id = ohqmv.inventory_item_id )
822 AND (ohqacc.organization_id = ohqmv.organization_id )
823 AND (ohqacc.subinventory_code = ohqmv.subinventory_code )
824 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
825 AND ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
826 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
827 );
828
829
830 TYPE inv_idTab IS TABLE OF csm_mtl_onhand_qty_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
831 TYPE org_idTab IS TABLE OF csm_mtl_onhand_qty_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
832 TYPE sub_codeTab IS TABLE OF csm_mtl_onhand_qty_acc.subinventory_code%TYPE INDEX BY BINARY_INTEGER;
833 TYPE rvsionTab IS TABLE OF csm_mtl_onhand_qty_acc.revision%TYPE INDEX BY BINARY_INTEGER;
834 TYPE loc_idTab IS TABLE OF csm_mtl_onhand_qty_acc.locator_id%TYPE INDEX BY BINARY_INTEGER;
835 TYPE lot_numTab IS TABLE OF csm_mtl_onhand_qty_acc.lot_number%TYPE INDEX BY BINARY_INTEGER;
836 TYPE tran_qtyTab IS TABLE OF mtl_onhand_quantities_detail.transaction_quantity%TYPE INDEX BY BINARY_INTEGER;
837 TYPE user_idTab IS TABLE OF asg_user.user_id%TYPE INDEX BY BINARY_INTEGER;
838 TYPE access_idTab IS TABLE OF csm_mtl_onhand_qty_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
839
840 inv_id inv_idTab;
841 org_id org_idTab;
842 sub_code sub_codeTab;
843 rvsion rvsionTab;
844 loc_id loc_idTab;
845 lot_num lot_numTab;
846 qty tran_qtyTab;
847 dummy_qty tran_qtyTab;
848 user_id_lst asg_download.user_list;
849 acc_id_lst asg_download.access_list;
850
851 l_dummy BOOLEAN;
852
853 l_current_run_date DATE;
854
855 l_pk_tab access_idTab;
856 l_dml_tab user_idTab;
857 l_cnt NUMBER :=0;
858
859 BEGIN
860 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ',
861 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_PROCEDURE);
862
863 l_current_run_date := SYSDATE;
864
865 --*** Push deleted records to client ***
866
867 OPEN l_onhand_delete_csr;
868 LOOP
869 IF acc_id_lst.COUNT > 0 THEN
870 acc_id_lst.DELETE;
871 END IF;
872 IF user_id_lst.COUNT > 0 THEN
873 user_id_lst.DELETE;
874 END IF;
875 IF inv_id.COUNT > 0 THEN
876 inv_id.DELETE;
877 END IF;
878 IF org_id.COUNT > 0 THEN
879 org_id.DELETE;
880 END IF;
881 IF sub_code.COUNT > 0 THEN
882 sub_code.DELETE;
883 END IF;
884 IF rvsion.COUNT > 0 THEN
885 rvsion.DELETE;
886 END IF;
887 IF loc_id.COUNT > 0 THEN
888 loc_id.DELETE;
889 END IF;
890 IF lot_num.COUNT > 0 THEN
891 lot_num.DELETE;
892 END IF;
893 IF qty.COUNT > 0 THEN
894 qty.DELETE;
895 END IF;
896 IF dummy_qty.COUNT > 0 THEN
897 dummy_qty.DELETE;
898 END IF;
899
900 FETCH l_onhand_delete_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, acc_id_lst LIMIT 100;
901 EXIT WHEN acc_id_lst.COUNT = 0;
902
903 IF acc_id_lst.COUNT > 0 THEN
904 CSM_UTIL_PKG.LOG('Pushing ' || acc_id_lst.COUNT || 'deleted records',
905 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
906
907 -- do bulk makedirty
908 l_dummy := asg_download.mark_dirty(
909 P_PUB_ITEM => g_pub_item
910 , p_accessList => acc_id_lst
911 , p_userid_list => user_id_lst
912 , p_dml_type => 'D'
913 , P_TIMESTAMP => l_current_run_date
914 );
915
916 -- FORALL i IN acc_id_lst.FIRST..acc_id_lst.LAST
917 -- DELETE CSM_MTL_ONHAND_QTY_ACC WHERE ACCESS_ID = acc_id_lst(i);
918
919 FOR i IN 1..acc_id_lst.COUNT LOOP
920 DELETE CSM_MTL_ONHAND_QTY_ACC WHERE ACCESS_ID = acc_id_lst(i);
921 CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV(inv_id(i),org_id(i),user_id_lst(i));
922 END LOOP;
923
924 END IF; -- end of deletes
925 END LOOP;
926 CLOSE l_onhand_delete_csr;
927
928
929 --*** Push updated records to client ***
930
931 OPEN l_onhand_update_csr;
932 LOOP
933 IF acc_id_lst.COUNT > 0 THEN
934 acc_id_lst.DELETE;
935 END IF;
936 IF user_id_lst.COUNT > 0 THEN
937 user_id_lst.DELETE;
938 END IF;
939 IF inv_id.COUNT > 0 THEN
940 inv_id.DELETE;
941 END IF;
942 IF org_id.COUNT > 0 THEN
943 org_id.DELETE;
944 END IF;
945 IF sub_code.COUNT > 0 THEN
946 sub_code.DELETE;
947 END IF;
948 IF rvsion.COUNT > 0 THEN
949 rvsion.DELETE;
950 END IF;
951 IF loc_id.COUNT > 0 THEN
952 loc_id.DELETE;
953 END IF;
954 IF lot_num.COUNT > 0 THEN
955 lot_num.DELETE;
956 END IF;
957 IF qty.COUNT > 0 THEN
958 qty.DELETE;
959 END IF;
960 IF dummy_qty.COUNT > 0 THEN
961 dummy_qty.DELETE;
962 END IF;
963
964 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;
965 EXIT WHEN user_id_lst.COUNT = 0;
966
967 IF user_id_lst.COUNT > 0 THEN
968 --*** push to oLite using asg_download ***
969 CSM_UTIL_PKG.LOG('Pushing ' || user_id_lst.COUNT || 'updated records',
970 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
971
972 FORALL i IN user_id_lst.FIRST..user_id_lst.LAST
973 UPDATE CSM_MTL_ONHAND_QTY_ACC
974 SET LAST_UPDATE_DATE = l_current_run_date,
975 QUANTITY = qty(i)
976 WHERE user_id = user_id_lst(i)
977 AND inventory_item_id = inv_id(i)
978 AND organization_id = org_id(i)
979 AND subinventory_code = sub_code(i)
980 AND (REVISION IS NULL OR revision = rvsion(i))
981 AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
982 AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
983 RETURNING access_id BULK COLLECT INTO acc_id_lst;
984
985 -- do bulk makedirty
986 l_dummy := asg_download.mark_dirty(
987 P_PUB_ITEM => g_pub_item
988 , p_accessList => acc_id_lst
989 , p_userid_list => user_id_lst
990 , p_dml_type => 'U'
991 , P_TIMESTAMP => l_current_run_date
992 );
993
994 --To notify user
995 FOR i in 1..acc_id_lst.COUNT
996 LOOP
997 l_cnt := l_cnt +1;
998 l_pk_tab(l_cnt) := acc_id_lst(i);
999 l_dml_tab(acc_id_lst(i)) := 2;
1000 END LOOP;
1001
1002 END IF; -- end of updates
1003 END LOOP;
1004 CLOSE l_onhand_update_csr;
1005
1006 --*** Push inserted records to client ***
1007
1008 OPEN l_onhand_insert_csr;
1009 LOOP
1010 IF acc_id_lst.COUNT > 0 THEN
1011 acc_id_lst.DELETE;
1012 END IF;
1013 IF user_id_lst.COUNT > 0 THEN
1014 user_id_lst.DELETE;
1015 END IF;
1016 IF inv_id.COUNT > 0 THEN
1017 inv_id.DELETE;
1018 END IF;
1019 IF org_id.COUNT > 0 THEN
1020 org_id.DELETE;
1021 END IF;
1022 IF sub_code.COUNT > 0 THEN
1023 sub_code.DELETE;
1024 END IF;
1025 IF rvsion.COUNT > 0 THEN
1026 rvsion.DELETE;
1027 END IF;
1028 IF loc_id.COUNT > 0 THEN
1029 loc_id.DELETE;
1030 END IF;
1031 IF lot_num.COUNT > 0 THEN
1032 lot_num.DELETE;
1033 END IF;
1034 IF qty.COUNT > 0 THEN
1035 qty.DELETE;
1036 END IF;
1037 IF dummy_qty.COUNT > 0 THEN
1038 dummy_qty.DELETE;
1039 END IF;
1040
1041 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;
1042 EXIT WHEN inv_id.COUNT = 0;
1043
1044 -- check if there are any items to be downloaded
1045 IF inv_id.COUNT > 0 THEN
1046 --*** push to oLite using asg_download ***
1047 CSM_UTIL_PKG.LOG('Pushing ' || inv_id.COUNT || 'inserted records',
1048 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_STATEMENT);
1049
1050 FOR i IN inv_id.FIRST..inv_id.LAST LOOP
1051 SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL INTO acc_id_lst(i) FROM dual;
1052 END LOOP;
1053
1054 FORALL i IN inv_id.FIRST..inv_id.LAST
1055 INSERT INTO CSM_MTL_ONHAND_QTY_ACC (ACCESS_ID, user_id, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1056 SUBINVENTORY_CODE,LOCATOR_ID,REVISION,LOT_NUMBER, LAST_UPDATE_DATE,LAST_UPDATED_BY,
1057 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),
1058 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));
1059
1060 -- do bulk makedirty
1061 l_dummy := asg_download.mark_dirty(
1062 P_PUB_ITEM => g_pub_item
1063 , p_accessList => acc_id_lst
1064 , p_userid_list => user_id_lst
1065 , p_dml_type => 'I'
1066 , P_TIMESTAMP => l_current_run_date
1067 );
1068
1069 --To notify user
1070 FOR i in 1..acc_id_lst.COUNT
1071 LOOP
1072 IF(NOT l_dml_tab.EXISTS(acc_id_lst(i))
1073 AND NOT CSM_UTIL_PKG.is_new_mmu_user(CSM_UTIL_PKG.get_user_name(user_id_lst(i)))) THEN -- this condition for new mmu user
1074 l_cnt := l_cnt +1;
1075 l_pk_tab(l_cnt) := acc_id_lst(i);
1076 l_dml_tab(acc_id_lst(i)) := 1;
1077 END IF;
1078 END LOOP;
1079
1080 END IF;
1081 END LOOP;
1082 CLOSE l_onhand_insert_csr;
1083
1084 -- set the program update date in jtm_con_request_data to sysdate
1085 UPDATE jtm_con_request_data
1086 SET last_run_date = l_current_run_date
1087 WHERE product_code = 'CSM'
1088 AND package_name = 'CSM_SYSTEM_ITEM_EVENT_PKG'
1089 AND procedure_name = 'REFRESH_MTL_ONHAND_QUANTITY';
1090
1091 -- Notify User of inventory update
1092 FOR i IN 1..l_pk_tab.COUNT
1093 LOOP
1094 IF (l_dml_tab(l_pk_tab(i))=2) THEN
1095 CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT('CSF_M_INVENTORY',to_char(l_pk_tab(i)),'UPDATE');
1096 ELSE
1097 CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT('CSF_M_INVENTORY',to_char(l_pk_tab(i)),'NEW');
1098 END IF;
1099 END LOOP;
1100
1101
1102 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ',
1103 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY',FND_LOG.LEVEL_PROCEDURE);
1104
1105 p_status := 'FINE';
1106 p_message := 'CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY Executed successfully';
1107
1108 COMMIT;
1109
1110 EXCEPTION
1111 WHEN others THEN
1112 l_sqlerrno := to_char(SQLCODE);
1113 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1114 p_status := 'ERROR';
1115 p_message := 'Error in CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY: ' || l_sqlerrno || ':' || l_sqlerrmsg;
1116 ROLLBACK;
1117 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.REFRESH_MTL_ONHAND_QUANTITY ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1118
1119 END Refresh_mtl_onhand_quantity;
1120
1121 PROCEDURE get_new_user_system_items(p_user_id IN NUMBER)
1122 IS
1123 g_pub_item VARCHAR2(30);
1124 l_current_run_date DATE;
1125 l_sqlerrno varchar2(20);
1126 l_sqlerrmsg varchar2(2000);
1127
1128 -- Insert cursor
1129 CURSOR l_systemitems_ins_csr(p_organization_id IN NUMBER, p_user_id IN NUMBER)
1130 IS
1131 SELECT /*+ INDEX (msi MTL_SYSTEM_ITEMS_B_U1) */ CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL,
1132 au.user_id,
1133 msi.inventory_item_id,
1134 msi.organization_id
1135 FROM asg_user au,
1136 asg_user_pub_resps aupr,
1137 mtl_system_items_b msi,
1138 CS_BILLING_TYPE_CATEGORIES cbtc
1139 WHERE au.user_id = p_user_id
1140 AND au.user_name = aupr.user_name
1141 AND aupr.pub_name = 'SERVICEP'
1142 AND msi.organization_id = p_organization_id
1143 AND msi.enabled_flag = 'Y'
1144 AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
1145 AND msi.material_billable_flag = cbtc.billing_type
1146 AND cbtc.billing_category IN ('L', 'E')
1147 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
1148 AND NOT EXISTS
1149 (SELECT 1
1150 FROM csm_system_items_acc acc
1151 WHERE acc.user_id = au.user_id
1152 AND acc.inventory_item_id = msi.inventory_item_id
1153 AND acc.organization_id = msi.organization_id
1154 );
1155
1156 -- Insert cursor based on already created user of same organization
1157 CURSOR l_systemitems_ins_acc_csr (p_old_user_id NUMBER, p_organization_id NUMBER, p_new_user_id NUMBER)
1158 IS
1159 SELECT inventory_item_id
1160 FROM CSM_SYSTEM_ITEMS_ACC
1161 WHERE user_id = p_old_user_id
1162 AND organization_id = p_organization_id
1163 AND NOT EXISTS
1164 (SELECT 1
1165 FROM csm_system_items_acc acc
1166 WHERE acc.user_id = p_new_user_id
1167 AND acc.organization_id = p_organization_id
1168 );
1169
1170 -- Cursor to check already created user with same organization_id
1171 CURSOR l_prev_user_csr (p_user_id NUMBER, p_organization_id NUMBER)
1172 IS
1173 SELECT user_id
1174 FROM csm_user_inventory_org
1175 WHERE organization_id = p_organization_id
1176 AND user_id <> p_user_id
1177 AND rownum = 1;
1178
1179 TYPE inv_idTab IS TABLE OF csm_system_items_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1180 TYPE org_idTab IS TABLE OF csm_system_items_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
1181
1182 l_inv_id inv_idTab;
1183 l_org_id org_idTab;
1184 l_user_id_lst asg_download.user_list;
1185 l_acc_id_lst asg_download.access_list;
1186 l_user_id NUMBER;
1187
1188 l_dummy BOOLEAN;
1189 l_organization_id mtl_system_items.organization_id%TYPE;
1190 l_count NUMBER;
1191
1192 BEGIN
1193 CSM_UTIL_PKG.LOG('Entering CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ',
1194 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1195
1196 g_pub_item := 'CSF_M_SYSTEM_ITEMS';
1197 l_current_run_date := SYSDATE;
1198 l_organization_id := csm_profile_pkg.get_organization_id(p_user_id);
1199
1200
1201 -- Check for already created user with same organization_id
1202 OPEN l_prev_user_csr(p_user_id, l_organization_id);
1203 FETCH l_prev_user_csr INTO l_user_id;
1204 CLOSE l_prev_user_csr;
1205
1206 IF l_user_id IS NOT NULL THEN
1207 CSM_UTIL_PKG.LOG(' Found MFS user created with organization_id: ' || l_organization_id,
1208 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1209
1210 IF l_acc_id_lst.COUNT > 0 THEN
1211 l_acc_id_lst.DELETE;
1212 END IF;
1213 IF l_user_id_lst.COUNT > 0 THEN
1214 l_user_id_lst.DELETE;
1215 END IF;
1216
1217 OPEN l_systemitems_ins_acc_csr (l_user_id, l_organization_id, p_user_id);
1218 LOOP
1219 IF l_inv_id.COUNT > 0 THEN
1220 l_inv_id.DELETE;
1221 END IF;
1222 IF l_org_id.COUNT > 0 THEN
1223 l_org_id.DELETE;
1224 END IF;
1225
1226 FETCH l_systemitems_ins_acc_csr BULK COLLECT INTO l_inv_id LIMIT 100;
1227 EXIT WHEN l_inv_id.COUNT = 0;
1228
1229 IF l_inv_id.COUNT > 0 THEN
1230 FORALL i IN l_inv_id.FIRST..l_inv_id.LAST
1231
1232 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1233 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1234 VALUES (CSM_SYSTEM_ITEMS_ACC_S.NEXTVAL, p_user_id , l_inv_id(i), l_organization_id, 1, 1, l_current_run_date,1,l_current_run_date,1);
1235 -- RETURNING access_id, user_id BULK COLLECT INTO l_acc_id_lst, l_user_id_lst;
1236
1237 -- -- do bulk makedirty
1238 -- l_dummy := asg_download.mark_dirty(
1239 -- P_PUB_ITEM => g_pub_item
1240 -- , p_accessList => l_acc_id_lst
1241 -- , p_userid_list => l_user_id_lst
1242 -- , p_dml_type => 'I'
1243 -- , P_TIMESTAMP => l_current_run_date
1244 -- );
1245 END IF;
1246 END LOOP;
1247
1248 CLOSE l_systemitems_ins_acc_csr;
1249 ELSE
1250 -- process inserts
1251 CSM_UTIL_PKG.LOG(' First user with organization_id: ' || l_organization_id,
1252 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1253
1254 OPEN l_systemitems_ins_csr (p_organization_id=>l_organization_id, p_user_id=>p_user_id);
1255 LOOP
1256 IF l_acc_id_lst.COUNT > 0 THEN
1257 l_acc_id_lst.DELETE;
1258 END IF;
1259 IF l_user_id_lst.COUNT > 0 THEN
1260 l_user_id_lst.DELETE;
1261 END IF;
1262 IF l_inv_id.COUNT > 0 THEN
1263 l_inv_id.DELETE;
1264 END IF;
1265 IF l_org_id.COUNT > 0 THEN
1266 l_org_id.DELETE;
1267 END IF;
1268
1269 FETCH l_systemitems_ins_csr BULK COLLECT INTO l_acc_id_lst, l_user_id_lst, l_inv_id, l_org_id LIMIT 100;
1270 EXIT WHEN l_acc_id_lst.COUNT = 0;
1271
1272 IF l_acc_id_lst.COUNT > 0 THEN
1273 FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
1274 INSERT INTO CSM_SYSTEM_ITEMS_ACC (ACCESS_ID, USER_ID, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1275 COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1276 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);
1277
1278 -- -- do bulk makedirty
1279 -- l_dummy := asg_download.mark_dirty(
1280 -- P_PUB_ITEM => g_pub_item
1281 -- , p_accessList => l_acc_id_lst
1282 -- , p_userid_list => l_user_id_lst
1283 -- , p_dml_type => 'I'
1284 -- , P_TIMESTAMP => l_current_run_date
1285 -- );
1286 END IF;
1287 END LOOP;
1288 CLOSE l_systemitems_ins_csr;
1289 END IF;
1290
1291 CSM_UTIL_PKG.LOG('Leaving CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ',
1292 'CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items',FND_LOG.LEVEL_PROCEDURE);
1293
1294 EXCEPTION
1295 WHEN others THEN
1296 l_sqlerrno := to_char(SQLCODE);
1297 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1298 csm_util_pkg.log('CSM_SYSTEM_ITEM_EVENT_PKG.get_new_user_system_items ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, FND_LOG.LEVEL_EXCEPTION);
1299 RAISE;
1300 END get_new_user_system_items;
1301
1302 PROCEDURE SYSTEM_ITEM_MDIRTY_I(p_inventory_item_id IN NUMBER,
1303 p_organization_id IN NUMBER,
1304 p_user_id IN NUMBER)
1305 IS
1306 l_sqlerrno VARCHAR2(20);
1307 l_sqlerrmsg VARCHAR2(4000);
1308 l_error_msg VARCHAR2(4000);
1309 l_return_status VARCHAR2(2000);
1310
1311 BEGIN
1312 CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id,
1313 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
1314
1315 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1316 -- insert into csm_system_items_acc
1317 CSM_ACC_PKG.Insert_Acc
1318 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1319 ,P_ACC_TABLE_NAME => g_acc_table_name1
1320 ,P_SEQ_NAME => g_acc_sequence_name1
1321 ,P_PK1_NAME => g_pk1_name1
1322 ,P_PK1_NUM_VALUE => p_inventory_item_id
1323 ,P_PK2_NAME => g_pk2_name1
1324 ,P_PK2_NUM_VALUE => p_organization_id
1325 ,P_USER_ID => p_user_id
1326 );
1327 END IF;
1328
1329 CSM_UTIL_PKG.LOG('Leaving SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id,
1330 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 l_sqlerrno := to_char(SQLCODE);
1334 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1335 l_error_msg := ' Exception in SYSTEM_ITEM_MDIRTY_I for inventory_item_id: ' || p_inventory_item_id
1336 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1337 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
1338 RAISE;
1339 END SYSTEM_ITEM_MDIRTY_I;
1340
1341 PROCEDURE SYSTEM_ITEM_MDIRTY_D(p_inventory_item_id IN NUMBER,
1342 p_organization_id IN NUMBER,
1343 p_user_id IN NUMBER)
1344 IS
1345 l_sqlerrno VARCHAR2(20);
1346 l_sqlerrmsg VARCHAR2(4000);
1347 l_error_msg VARCHAR2(4000);
1348 l_return_status VARCHAR2(2000);
1349
1350 BEGIN
1351 CSM_UTIL_PKG.LOG('Entering SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id,
1352 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
1353
1354 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1355 -- delete from csm_system_items_acc
1356 CSM_ACC_PKG.Delete_Acc
1357 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
1358 ,P_ACC_TABLE_NAME => g_acc_table_name1
1359 ,P_PK1_NAME => g_pk1_name1
1360 ,P_PK1_NUM_VALUE => p_inventory_item_id
1361 ,P_PK2_NAME => g_pk2_name1
1362 ,P_PK2_NUM_VALUE => p_organization_id
1363 ,P_USER_ID => p_user_id
1364 );
1365 END IF;
1366
1367 CSM_UTIL_PKG.LOG('Leaving SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id,
1368 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 l_sqlerrno := to_char(SQLCODE);
1372 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1373 l_error_msg := ' Exception in SYSTEM_ITEM_MDIRTY_D for inventory_item_id: ' || p_inventory_item_id
1374 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1375 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SYSTEM_ITEM_EVENT_PKG.SYSTEM_ITEM_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
1376 RAISE;
1377 END SYSTEM_ITEM_MDIRTY_D;
1378
1379 PROCEDURE RECEIVED_MTL_ONHAND(p_org_id IN NUMBER,p_subinv_code IN VARCHAR2,p_item_id IN NUMBER,p_user_id IN NUMBER)
1380 IS
1381 -- get the updates to onhands for all mobile users
1382 CURSOR l_onhand_update_csr IS
1383 SELECT /*+ index(ohqacc CSM_MTL_ONHAND_QTY_ACC_U2) index(ohqmv MTL_ONHAND_QUANTITIES_N4)*/ DISTINCT ohqacc.user_id
1384 , ohqmv.INVENTORY_ITEM_ID
1385 , ohqmv.ORGANIZATION_ID
1386 , ohqmv.SUBINVENTORY_CODE
1387 , ohqmv.LOCATOR_ID
1388 , ohqmv.REVISION
1389 , ohqmv.LOT_NUMBER
1390 , ohqacc.quantity
1391 , SUM(ohqmv.transaction_quantity) tot_qty
1392 FROM csm_mtl_onhand_qty_acc ohqacc,
1393 mtl_onhand_quantities_detail ohqmv
1394 WHERE ohqmv.inventory_item_id=p_item_id
1395 AND ohqmv.organization_id=p_org_id
1396 AND nvl(ohqmv.subinventory_code,'-999') = nvl(p_subinv_code,'-999')
1397 AND ohqacc.user_id=p_user_id
1398 AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
1399 AND ohqacc.organization_id = ohqmv.organization_id
1400 AND ohqacc.subinventory_code = ohqmv.subinventory_code
1401 AND nvl(ohqacc.LOCATOR_ID,-9999)=nvl(ohqmv.LOCATOR_ID,-9999)
1402 AND nvl(ohqacc.REVISION,'-9999')=nvl(ohqmv.REVISION,'-9999')
1403 AND nvl(ohqacc.LOT_NUMBER,'-9999')=nvl(ohqmv.LOT_NUMBER,'-9999')
1404 HAVING SUM(ohqmv.transaction_quantity) <> NVL(ohqacc.quantity,0)
1405 GROUP BY ohqacc.user_id, ohqmv.inventory_item_id, ohqmv.organization_id, ohqmv.subinventory_code,
1406 ohqmv.locator_id, ohqmv.revision, ohqmv.lot_number, ohqacc.quantity;
1407
1408 CURSOR l_onhand_insert_csr IS
1409 SELECT distinct /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) index (ohqmv MTL_ONHAND_QUANTITIES_N5) */ au.user_id
1410 , ohqmv.INVENTORY_ITEM_ID
1411 , ohqmv.ORGANIZATION_ID
1412 , ohqmv.SUBINVENTORY_CODE
1413 , ohqmv.LOCATOR_ID
1414 , ohqmv.REVISION
1415 , ohqmv.LOT_NUMBER
1416 , (SELECT SUM (ohqmv2.transaction_quantity)
1417 FROM mtl_onhand_quantities_detail ohqmv2 WHERE
1418 ohqmv.ORGANIZATION_ID=ohqmv2.ORGANIZATION_ID AND
1419 ohqmv.SUBINVENTORY_CODE=ohqmv2.SUBINVENTORY_CODE AND
1420 ohqmv.INVENTORY_ITEM_ID=ohqmv2.INVENTORY_ITEM_ID AND
1421 nvl(ohqmv.LOCATOR_ID,-9999)=nvl(ohqmv2.LOCATOR_ID,-9999) AND
1422 nvl(ohqmv.REVISION,'-9999')=nvl(ohqmv2.REVISION,'-9999') AND
1423 nvl(ohqmv.LOT_NUMBER,'-9999')=nvl(ohqmv2.LOT_NUMBER,'-9999'))
1424 FROM asg_user au,
1425 mtl_onhand_quantities_detail ohqmv
1426 WHERE ohqmv.inventory_item_id=p_item_id
1427 AND ohqmv.organization_id=p_org_id
1428 AND nvl(ohqmv.subinventory_code,'-999') = nvl(p_subinv_code,'-999')
1429 AND au.user_id = au.owner_id
1430 AND au.user_id=p_user_id
1431 AND EXISTS ( SELECT 1 FROM csp_inv_loc_assignments ila
1432 WHERE ila.resource_id = au.resource_id
1433 AND ila.resource_type='RS_EMPLOYEE'
1434 AND ila.subinventory_code = ohqmv.subinventory_code
1435 AND ila.organization_id = ohqmv.organization_id
1436 AND SYSDATE BETWEEN NVL(ila.effective_date_start, SYSDATE) AND NVL(ila.effective_date_end, SYSDATE) )
1437 AND NOT EXISTS
1438 (SELECT /*index (ohqacc CSM_MTL_ONHAND_QTY_ACC_U2)*/ 1
1439 FROM csm_mtl_onhand_qty_acc ohqacc
1440 WHERE ohqacc.user_id = au.user_id
1441 AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
1442 AND ohqacc.organization_id = ohqmv.organization_id
1443 AND ohqacc.subinventory_code = ohqmv.subinventory_code
1444 AND nvl(ohqacc.locator_id,-999) = nvl(ohqmv.locator_id,-999)
1445 AND nvl(ohqacc.lot_number,'-999') = nvl(ohqmv.lot_number,'-999')
1446 AND nvl(ohqacc.revision,'-999') = nvl(ohqmv.revision,'-999')
1447 );
1448
1449 CURSOR c_find_instances
1450 IS
1451 SELECT cii.instance_id,
1452 cqa.user_id
1453 FROM csm_mtl_onhand_qty_acc cqa
1454 , csi_item_instances cii
1455 , csi_instance_statuses iis
1456 , asg_user asg
1457 WHERE cqa.inventory_item_id=p_item_id
1458 AND cqa.organization_id=p_org_id
1459 AND nvl(cqa.subinventory_code,'-999') = nvl(p_subinv_code,'-999')
1460 AND cqa.user_id = p_user_id
1461 AND cii.inventory_item_id = cqa.inventory_item_id
1462 AND cii.inv_organization_id = cqa.organization_id
1463 AND cii.inv_subinventory_name = cqa.subinventory_code
1464 AND nvl(cii.inv_locator_id,-999) = nvl(cqa.locator_id,-999)
1465 AND nvl(cii.lot_number,'-999') = nvl(cqa.lot_number,'-999')
1466 AND nvl(cii.inventory_revision,'-999') = nvl(cqa.revision,'-999')
1467 AND cii.location_type_code = 'INVENTORY'
1468 AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
1469 AND cii.INSTANCE_STATUS_ID = iis.instance_status_id
1470 AND NVL(iis.terminated_flag,'N') = 'N'
1471 AND asg.user_id = asg.owner_id AND asg.user_id = cqa.user_id AND asg.ENABLED = 'Y'
1472 AND NOT EXISTS
1473 ( SELECT 1
1474 FROM csm_item_instances_acc cia
1475 WHERE cia.user_id = cqa.user_id
1476 AND cia.instance_id = cii.instance_id
1477 );
1478
1479
1480 TYPE inv_idTab IS TABLE OF csm_mtl_onhand_qty_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1481 TYPE org_idTab IS TABLE OF csm_mtl_onhand_qty_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
1482 TYPE sub_codeTab IS TABLE OF csm_mtl_onhand_qty_acc.subinventory_code%TYPE INDEX BY BINARY_INTEGER;
1483 TYPE rvsionTab IS TABLE OF csm_mtl_onhand_qty_acc.revision%TYPE INDEX BY BINARY_INTEGER;
1484 TYPE loc_idTab IS TABLE OF csm_mtl_onhand_qty_acc.locator_id%TYPE INDEX BY BINARY_INTEGER;
1485 TYPE lot_numTab IS TABLE OF csm_mtl_onhand_qty_acc.lot_number%TYPE INDEX BY BINARY_INTEGER;
1486 TYPE tran_qtyTab IS TABLE OF mtl_onhand_quantities_detail.transaction_quantity%TYPE INDEX BY BINARY_INTEGER;
1487 TYPE user_idTab IS TABLE OF asg_user.user_id%TYPE INDEX BY BINARY_INTEGER;
1488 TYPE access_idTab IS TABLE OF csm_mtl_onhand_qty_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
1489
1490 inv_id inv_idTab;
1491 org_id org_idTab;
1492 sub_code sub_codeTab;
1493 rvsion rvsionTab;
1494 loc_id loc_idTab;
1495 lot_num lot_numTab;
1496 qty tran_qtyTab;
1497 dummy_qty tran_qtyTab;
1498 user_id_lst asg_download.user_list;
1499 acc_id_lst asg_download.access_list;
1500
1501 l_dummy BOOLEAN;
1502 l_return_status VARCHAR2(100);
1503 l_err_msg VARCHAR2(4000);
1504
1505 BEGIN
1506 CSM_UTIL_PKG.LOG('Entered RECEIVED_MTL_ONHAND', 'CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND',FND_LOG.LEVEL_STATEMENT);
1507
1508 OPEN l_onhand_update_csr;
1509 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;
1510 CLOSE l_onhand_update_csr;
1511
1512 IF user_id_lst.COUNT > 0 THEN
1513 --*** push to oLite using asg_download ***
1514 CSM_UTIL_PKG.LOG('Pushing ' || user_id_lst.COUNT || ' updates',
1515 'CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND',FND_LOG.LEVEL_STATEMENT);
1516
1517 FORALL i IN 1..user_id_lst.COUNT
1518 UPDATE CSM_MTL_ONHAND_QTY_ACC
1519 SET LAST_UPDATE_DATE = sysdate, QUANTITY = qty(i)
1520 WHERE user_id = user_id_lst(i)
1521 AND inventory_item_id = inv_id(i)
1522 AND organization_id = org_id(i)
1523 AND subinventory_code = sub_code(i)
1524 AND (REVISION IS NULL OR revision = rvsion(i))
1525 AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
1526 AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
1527 RETURNING access_id BULK COLLECT INTO acc_id_lst;
1528
1529 -- do bulk makedirty
1530 l_dummy := asg_download.mark_dirty(
1531 P_PUB_ITEM => 'CSF_M_INVENTORY'
1532 , p_accessList => acc_id_lst
1533 , p_userid_list => user_id_lst
1534 , p_dml_type => 'U'
1535 , P_TIMESTAMP => sysdate
1536 );
1537
1538 END IF; -- end of updates
1539
1540
1541
1542 --*** Push inserted records to client ***
1543
1544 acc_id_lst.DELETE;
1545 user_id_lst.DELETE;
1546 inv_id.DELETE;
1547 org_id.DELETE;
1548 sub_code.DELETE;
1549 rvsion.DELETE;
1550 loc_id.DELETE;
1551 lot_num.DELETE;
1552 qty.DELETE;
1553 dummy_qty.DELETE;
1554
1555 OPEN l_onhand_insert_csr;
1556 FETCH l_onhand_insert_csr BULK COLLECT INTO user_id_lst, inv_id, org_id, sub_code, loc_id, rvsion, lot_num, qty ;
1557 CLOSE l_onhand_insert_csr;
1558
1559 -- check if there are any items to be downloaded
1560 IF inv_id.COUNT > 0 THEN
1561 --*** push to oLite using asg_download ***
1562 CSM_UTIL_PKG.LOG('Pushing ' || inv_id.COUNT || ' inserts',
1563 'CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND',FND_LOG.LEVEL_STATEMENT);
1564
1565 FOR i IN 1..inv_id.COUNT LOOP
1566 SELECT csm_mtl_onhand_qty_acc_s.NEXTVAL INTO acc_id_lst(i) FROM dual;
1567 END LOOP;
1568
1569 FORALL i IN inv_id.FIRST..inv_id.LAST
1570 INSERT INTO CSM_MTL_ONHAND_QTY_ACC (ACCESS_ID, user_id, INVENTORY_ITEM_ID,ORGANIZATION_ID,
1571 SUBINVENTORY_CODE,LOCATOR_ID,REVISION,LOT_NUMBER, LAST_UPDATE_DATE,LAST_UPDATED_BY,
1572 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),
1573 loc_id(i), rvsion(i), lot_num(i), sysdate,1,sysdate,1, 1, qty(i), acc_id_lst(i));
1574
1575 -- do bulk makedirty
1576 l_dummy := asg_download.mark_dirty(
1577 P_PUB_ITEM => 'CSF_M_INVENTORY'
1578 , p_accessList => acc_id_lst
1579 , p_userid_list => user_id_lst
1580 , p_dml_type => 'I'
1581 , P_TIMESTAMP => sysdate
1582 );
1583 END IF;
1584
1585 CSM_UTIL_PKG.LOG('Find and add instances if the received item is IB trackable', 'CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND',FND_LOG.LEVEL_STATEMENT);
1586 FOR rec IN c_find_instances
1587 LOOP
1588 CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCES_ACC_PROCESSOR(rec.instance_id,rec.USER_ID,NULL,l_return_status,l_err_msg);
1589 END LOOP;
1590
1591 CSM_UTIL_PKG.LOG('Leaving RECEIVED_MTL_ONHAND', 'CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND',FND_LOG.LEVEL_STATEMENT);
1592 END RECEIVED_MTL_ONHAND;
1593
1594 END CSM_SYSTEM_ITEM_EVENT_PKG;
1595