1 PACKAGE BODY CSM_SERIAL_NUMBERS_EVENT_PKG AS
2 /* $Header: csmeslnb.pls 120.6.12020000.2 2013/04/09 11:07:41 saradhak ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_pub_item varchar2(30) := 'CSM_MTL_SERIAL_NUMBERS';
15
16 PROCEDURE insert_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
17 IS
18 TYPE inventory_items_tbl_typ IS TABLE OF mtl_serial_numbers.inventory_item_id%TYPE INDEX BY binary_integer;
19 TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
20 TYPE organizations_tbl_typ IS TABLE OF mtl_serial_numbers.current_organization_id%TYPE INDEX BY binary_integer;
21 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
22
23 l_inventory_items_tbl inventory_items_tbl_typ;
24 l_serial_numbers_tbl serial_numbers_tbl_typ;
25 l_organizations_tbl organizations_tbl_typ;
26 l_access_id_tbl access_id_tbl_typ;
27 l_run_date DATE;
28 l_markdirty boolean;
29 l_sqlerrno varchar2(20);
30 l_sqlerrmsg varchar2(2000);
31
32 -- update counter of records that may already exist in acc table
33 CURSOR l_upd_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
34 IS
35 SELECT acc.access_id
36 FROM csm_mtl_serial_numbers_acc acc
37 WHERE acc.user_id = p_userid
38 --AND acc.current_organization_id = p_organization_id
39 AND (acc.inventory_item_id, acc.serial_number, acc.current_organization_id) IN (
40 SELECT inventory_item_id, serial_number, current_organization_id
41 FROM mtl_serial_numbers
42 WHERE creation_date >= NVL(p_lastrundate, creation_date)
43 AND current_status =3
44 AND (current_subinventory_code, current_organization_id) IN (
45 SELECT subinventory_code
46 ,organization_id
47 FROM csp_inv_loc_assignments
48 WHERE resource_id = p_resourceid
49 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
50 AND nvl( effective_date_end , SYSDATE ))
51 );
52
53 -- get all new serial numbers that do not exist in acc table
54 CURSOR l_ins_mtl_serial_numbers_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
55 IS--select serial numbers in status 3 for both ib non ib items
56 SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id
57 FROM mtl_serial_numbers
58 WHERE current_status =3 -- resides in stores + issued out of subinv
59 AND ( current_subinventory_code, current_organization_id ) IN (
60 SELECT subinventory_code
61 ,organization_id
62 FROM csp_inv_loc_assignments
63 WHERE resource_id = p_resourceid
64 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
65 AND nvl( effective_date_end , SYSDATE ))
66 AND ( inventory_item_id, serial_number, current_organization_id ) NOT IN (
67 SELECT inventory_item_id, serial_number, current_organization_id
68 FROM csm_mtl_serial_numbers_acc
69 WHERE user_id = p_userid );
70
71 CURSOR l_ins_mtl_ser_num_fornonib_csr(p_organization_id in number, p_resourceid IN number, p_userid in number, p_lastrundate IN date)
72 IS--select serial numbers in status 1,4 for non ib items only
73 SELECT csm_mtl_serial_numbers_acc_s.nextval,
74 ser.inventory_item_id,
75 ser.serial_number,
76 ser.current_organization_id
77 FROM mtl_serial_numbers ser
78 WHERE ser.current_status IN (1,4) --Not assigned and out of subinv
79 AND EXISTS (
80 SELECT 'x'
81 FROM csm_mtl_onhand_qty_acc ohqacc,
82 mtl_system_items sys
83 WHERE ohqacc.inventory_item_id = ser.inventory_item_id
84 AND ohqacc.organization_id = ser.current_organization_id
85 AND ohqacc.user_id = p_userid
86 AND ohqacc.inventory_item_id = sys.inventory_item_id
87 AND ohqacc.ORGANIZATION_ID = sys.ORGANIZATION_ID
88 AND NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
89 )
90 AND NOT EXISTS (
91 SELECT 'x'
92 FROM csm_mtl_serial_numbers_acc sacc
93 WHERE user_id = p_userid
94 AND sacc.inventory_item_id = ser.inventory_item_id
95 AND sacc.serial_number = ser.serial_number
96 AND sacc.current_organization_id = ser.current_organization_id);
97
98 BEGIN
99 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
100 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
101
102 IF l_access_id_tbl.count > 0 THEN
103 l_access_id_tbl.DELETE;
104 END IF;
105
106 l_run_date := SYSDATE;
107
108 -- update counter of serial numbers that already exist for user in acc table
109 OPEN l_upd_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
110 FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
111 CLOSE l_upd_mtl_serial_numbers_csr;
112
113 IF l_access_id_tbl.count > 0 THEN
114 FORALL i IN 1..l_access_id_tbl.count
115 UPDATE csm_mtl_serial_numbers_acc
116 SET counter = counter + 1
117 ,last_update_date = SYSDATE
118 ,last_updated_by = fnd_global.user_id
119 WHERE access_id = l_access_id_tbl(i);
120
121 l_access_id_tbl.delete;
122 END IF;
123
124 -- BULK collect all new inserted serial numbers with STatus 3
125 OPEN l_ins_mtl_serial_numbers_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
126 FETCH l_ins_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;
127 CLOSE l_ins_mtl_serial_numbers_csr;
128
129 -- bulk insert into acc tables
130 IF l_access_id_tbl.count > 0 THEN
131 FORALL i IN 1..l_access_id_tbl.count
132 INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
133 counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
134 VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
135 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
136
137 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
138 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
139
140 -- make dirty calls
141 FOR i IN 1..l_access_id_tbl.count LOOP
142 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
143 l_access_id_tbl(i),
144 p_resource_id,
145 asg_download.ins,
146 l_run_date);
147 END LOOP;
148 END IF;
149
150
151 IF CSM_UTIL_PKG.is_html5_user(p_user_id) THEN --non ib serial numbers not reqd
152 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS for html5 user',
153 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
154 RETURN;
155 END IF;
156
157 ---Insert for non ib items
158 IF l_access_id_tbl.count > 0 THEN
159 l_access_id_tbl.delete;
160 l_inventory_items_tbl.delete;
161 l_serial_numbers_tbl.delete;
162 l_organizations_tbl.delete;
163 END IF;
164
165 -- BULK collect all new inserted serial numbers with STatus 1 and 4 for Non ib items
166 OPEN l_ins_mtl_ser_num_fornonib_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
167 FETCH l_ins_mtl_ser_num_fornonib_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_serial_numbers_tbl, l_organizations_tbl;
168 CLOSE l_ins_mtl_ser_num_fornonib_csr;
169
170 -- bulk insert into acc tables
171 IF l_access_id_tbl.count > 0 THEN
172 FORALL i IN 1..l_access_id_tbl.count
173 INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
174 counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
175 VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
176 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
177
178 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
179 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
180
181 -- make dirty calls
182 FOR i IN 1..l_access_id_tbl.count LOOP
183 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
184 l_access_id_tbl(i),
185 p_resource_id,
186 asg_download.ins,
187 l_run_date);
188 END LOOP;
189 END IF;
190
191 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
192 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
193
194 EXCEPTION
195 WHEN others THEN
196 l_sqlerrno := to_char(SQLCODE);
197 l_sqlerrmsg := substr(SQLERRM, 1,2000);
198 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
199 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
200
201 END INSERT_MTL_SERIAL_NUMBERS;
202
203 PROCEDURE update_mtl_serial_numbers(p_organization_id IN number, p_last_run_date IN date, p_resource_id IN number, p_user_id IN number)
204 IS
205 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
206 l_access_id_tbl access_id_tbl_typ;
207 l_run_date DATE;
208 l_markdirty boolean;
209 l_sqlerrno varchar2(20);
210 l_sqlerrmsg varchar2(2000);
211
212 -- upd serial numbers whose locations have changed within the org to locations user has access to
213 CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
214 IS
215 SELECT access_id
216 FROM csm_mtl_serial_numbers_acc acc
217 , mtl_serial_numbers msn
218 WHERE msn.inventory_item_id = acc.inventory_item_id
219 AND msn.serial_number = acc.serial_number
220 AND msn.current_organization_id = acc.current_organization_id
221 AND msn.last_update_date >= p_lastrundate
222 AND acc.user_id = p_userid;
223 /*AND ( msn.current_subinventory_code, msn.current_organization_id ) IN (
224 SELECT subinventory_code
225 , organization_id
226 FROM csp_inv_loc_assignments
227 WHERE resource_id = p_resourceid
228 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
229 AND nvl( effective_date_end , SYSDATE ));*/
230
231 -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
232 CURSOR l_del_mtl_serial_numbers_csr(p_resourceid IN number, p_userid IN number)
233 IS
234 SELECT access_id
235 FROM csm_mtl_serial_numbers_acc acc
236 , mtl_serial_numbers msn
237 WHERE msn.inventory_item_id = acc.inventory_item_id
238 AND msn.serial_number = acc.serial_number
239 AND msn.current_organization_id = acc.current_organization_id
240 AND acc.user_id = p_userid
241 AND msn.current_status =3
242 AND ( msn.current_subinventory_code, msn.current_organization_id ) NOT IN (
243 SELECT subinventory_code
244 , organization_id
245 FROM csp_inv_loc_assignments
246 WHERE resource_id = p_resourceid
247 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
248 AND nvl( effective_date_end , SYSDATE ))
249 UNION
250 SELECT access_id
251 FROM csm_mtl_serial_numbers_acc acc
252 WHERE acc.user_id = p_user_id
253 AND NOT EXISTS
254 (SELECT 1
255 FROM mtl_serial_numbers msn
256 WHERE msn.serial_number = acc.serial_number
257 AND msn.inventory_item_id = acc.inventory_item_id
258 AND msn.current_organization_id = acc.current_organization_id
259 AND msn.CURRENT_STATUS =3
260 )
261 AND NOT EXISTS
262 ( SELECT 1
263 FROM mtl_serial_numbers ser,
264 csm_mtl_onhand_qty_acc ohqacc,
265 mtl_system_items sys
266 WHERE ser.current_status in (1,4)
267 AND ohqacc.user_id = acc.user_id
268 AND ser.serial_number = acc.serial_number
269 AND ser.inventory_item_id =acc.inventory_item_id
270 AND ser.inventory_item_id =sys.inventory_item_id
271 AND ser.CURRENT_ORGANIZATION_ID = sys.ORGANIZATION_ID
272 AND NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
273 AND ohqacc.inventory_item_id = ser.inventory_item_id
274 AND ohqacc.organization_id = ser.current_organization_id
275
276 );
277
278
279 CURSOR l_del_mtl_serial_numbers_csr5(p_resourceid IN number, p_userid IN number) --htm5 users only status=3 i.e no non-ib items
280 IS
281 SELECT access_id
282 FROM csm_mtl_serial_numbers_acc acc , mtl_serial_numbers msn
283 WHERE msn.inventory_item_id = acc.inventory_item_id
284 AND msn.serial_number = acc.serial_number
285 AND msn.current_organization_id = acc.current_organization_id
286 AND acc.user_id = p_userid
287 AND msn.current_status =3
288 AND ( msn.current_subinventory_code, msn.current_organization_id ) NOT IN (
289 SELECT subinventory_code, organization_id
290 FROM csp_inv_loc_assignments WHERE resource_id = p_resourceid
291 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )AND nvl( effective_date_end , SYSDATE ))
292 UNION
293 SELECT access_id
294 FROM csm_mtl_serial_numbers_acc acc
295 WHERE acc.user_id = p_user_id
296 AND NOT EXISTS
297 (SELECT 1 FROM mtl_serial_numbers msn
298 WHERE msn.serial_number = acc.serial_number
299 AND msn.inventory_item_id = acc.inventory_item_id
300 AND msn.current_organization_id = acc.current_organization_id
301 AND msn.CURRENT_STATUS =3 );
302
303
304
305 CURSOR l_delete_serial_number_acc(p_userid IN number)
306 IS
307 SELECT access_id
308 FROM csm_mtl_serial_numbers_acc
309 WHERE user_id = p_userid
310 AND counter = 0;
311
312 BEGIN
313 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
314 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
315
316 l_run_date := SYSDATE;
317
318 IF l_access_id_tbl.count > 0 THEN
319 l_access_id_tbl.DELETE;
320 END IF;
321
322 -- bulk collect all updated serial_numbers
323 OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
324 FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
325 CLOSE l_upd_mtl_serial_numbers_csr;
326
327 IF l_access_id_tbl.count > 0 THEN
328 CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked update on csm_mtl_serial_numbers for resource ' || p_resource_id ,
329 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
330
331 -- make dirty calls
332 FOR i IN 1..l_access_id_tbl.count LOOP
333 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
334 l_access_id_tbl(i),
335 p_resource_id,
336 asg_download.upd,
337 l_run_date);
338 END LOOP;
339
340 l_access_id_tbl.DELETE;
341 END IF;
342
343
344 IF CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) THEN
345 -- bulk collect all serial numbers to be deleted
346 OPEN l_del_mtl_serial_numbers_csr5(p_resource_id, p_user_id);
347 FETCH l_del_mtl_serial_numbers_csr5 BULK COLLECT INTO l_access_id_tbl;
348 CLOSE l_del_mtl_serial_numbers_csr5;
349 ELSE
350 -- bulk collect all serial numbers to be deleted
351 OPEN l_del_mtl_serial_numbers_csr(p_resource_id, p_user_id);
352 FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
353 CLOSE l_del_mtl_serial_numbers_csr;
354 END IF;
355
356 -- update counter for records to be deleted
357 IF l_access_id_tbl.count > 0 THEN
358 FORALL i IN 1..l_access_id_tbl.count
359 UPDATE csm_mtl_serial_numbers_acc
360 SET counter = counter - 1
361 ,last_update_date = SYSDATE
362 ,last_updated_by = fnd_global.user_id
363 WHERE access_id = l_access_id_tbl(i);
364
365 l_access_id_tbl.delete;
366 END IF;
367
368 -- delete all access_id's that have counter = 0
369 OPEN l_delete_serial_number_acc(p_user_id);
370 FETCH l_delete_serial_number_acc BULK COLLECT INTO l_access_id_tbl;
371 CLOSE l_delete_serial_number_acc;
372
373 IF l_access_id_tbl.count > 0 THEN
374 CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records marked delete on csm_mtl_serial_numbers for resource ' || p_resource_id ,
375 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
376
377 -- make dirty calls
378 FOR i IN 1..l_access_id_tbl.count LOOP
379 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
380 l_access_id_tbl(i),
381 p_resource_id,
382 asg_download.del,
383 l_run_date);
384 END LOOP;
385
386 FORALL i IN 1..l_access_id_tbl.count
387 DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);
388
389 l_access_id_tbl.DELETE;
390 END IF;
391
392 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
393 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
394
395 EXCEPTION
396 WHEN others THEN
397 l_sqlerrno := to_char(SQLCODE);
398 l_sqlerrmsg := substr(SQLERRM, 1,2000);
399 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
400 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
401
402 END UPDATE_MTL_SERIAL_NUMBERS;
403
404 PROCEDURE refresh_mtl_serial_numbers_acc(p_status OUT NOCOPY VARCHAR2,
405 p_message OUT NOCOPY VARCHAR2)
406 IS
407 PRAGMA AUTONOMOUS_TRANSACTION;
408 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
409 l_all_omfs_palm_resource_list asg_download.user_list;
410 l_valid_omfs_resource_list asg_download.user_list;
411 l_null_palm_omfs_resource_list asg_download.user_list;
412 l_user_palm_organization_id mtl_system_items.organization_id%TYPE;
413 l_usr_list_for_serial asg_download.user_list;
414 l_rsrc_list_for_serial asg_download.user_list;
415 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
416 l_run_date DATE;
417 l_user_id fnd_user.user_id%TYPE;
418 l_sqlerrno varchar2(20);
419 l_sqlerrmsg varchar2(2000);
420
421 CURSOR l_last_run_date_csr
422 IS
423 SELECT nvl(last_run_date, (sysdate - 365*50))
424 FROM jtm_con_request_data
425 WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
426 AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
427
428 CURSOR l_user_id_csr (p_resourceid IN number)
429 IS
430 SELECT user_id
431 FROM asg_user
432 WHERE resource_id = p_resourceid;
433
434 CURSOR l_usr_rsrc_list_serial
435 IS
436 SELECT
437 au.resource_id,
438 au.user_id
439 FROM asg_user_pub_resps aupr,
440 asg_user au
441 WHERE aupr.pub_name = 'SERVICEP'
442 AND au.user_name = aupr.user_name
443 AND au.owner_id = au.user_id
444 AND au.enabled = 'Y';
445
446 BEGIN
447 l_run_date := SYSDATE;
448
449 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
450 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
451
452 -- R12 Serial Number implementation
453 IF l_usr_list_for_serial.COUNT > 0 THEN
454 l_usr_list_for_serial.DELETE;
455 END IF;
456
457 IF l_rsrc_list_for_serial.COUNT > 0 THEN
458 l_rsrc_list_for_serial.DELETE;
459 END IF;
460
461 OPEN l_last_run_date_csr;
462 FETCH l_last_run_date_csr INTO l_last_run_date;
463 CLOSE l_last_run_date_csr;
464
465 OPEN l_usr_rsrc_list_serial;
466 FETCH l_usr_rsrc_list_serial BULK COLLECT INTO l_rsrc_list_for_serial,l_usr_list_for_serial;
467 CLOSE l_usr_rsrc_list_serial;
468 IF l_usr_list_for_serial.COUNT > 0 THEN
469 FOR l_count in 1..l_usr_list_for_serial.COUNT LOOP
470 l_user_palm_organization_id:=csm_profile_pkg.get_organization_id(l_usr_list_for_serial(l_count));
471 --updating/deleting serial numbers already present in the ACC
472 update_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
473 COMMIT;
474 --inserting new serial numbers
475 insert_mtl_serial_numbers(l_user_palm_organization_id,l_last_run_date,l_rsrc_list_for_serial(l_count),l_usr_list_for_serial(l_count));
476 COMMIT;
477 END LOOP;
478 END IF;
479
480 -- update last_run_date
481 UPDATE jtm_con_request_data
482 SET last_run_date = l_run_date
483 WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
484 AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
485
486 COMMIT;
487
488 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
489 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
490
491 p_status := 'FINE';
492 p_message := 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC executed successfully';
493
494 EXCEPTION
495 WHEN others THEN
496 l_sqlerrno := to_char(SQLCODE);
497 l_sqlerrmsg := substr(SQLERRM, 1,2000);
498 ROLLBACK;
499 p_status := 'ERROR';
500 p_message := 'Error in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
501 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
502 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
503
504 END REFRESH_MTL_SERIAL_NUMBERS_ACC;
505
506 PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
507 , p_user_id IN number
508 , p_resource_id IN number)
509 IS
510 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
511 l_access_id_tbl access_id_tbl_typ;
512 l_mark_dirty boolean;
513 l_run_date date;
514 l_sqlerrno varchar2(20);
515 l_sqlerrmsg varchar2(2000);
516
517 -- make delete dirty calls for serial numbers with counter=1; do not delete from acc
518 -- since delete will be done based on csp_inv_loc_assignment data
519 CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
520 p_userid IN number)
521 IS
522 SELECT access_id
523 FROM csm_mtl_serial_numbers_acc
524 WHERE user_id = p_userid
525 AND current_organization_id = p_organizationid
526 AND counter = 0;
527
528 BEGIN
529 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
530 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
531
532 l_run_date := SYSDATE;
533
534
535 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
536 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
537
538 EXCEPTION
539 WHEN others THEN
540 l_sqlerrno := to_char(SQLCODE);
541 l_sqlerrmsg := substr(SQLERRM, 1,2000);
542 --logm('Exception ' || l_sqlerrmsg);
543 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
544 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
545
546 END DELETE_OLD_ORG_SERIAL_NUMBERS;
547
548
549 PROCEDURE GET_NEW_ORG_SERIAL_NUMBERS(p_organization_id IN number
550 , p_user_id IN number
551 , p_resource_id IN number)
552 IS
553 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
554 l_access_id_tbl access_id_tbl_typ;
555 l_mark_dirty boolean;
556 l_run_date date;
557 l_sqlerrno varchar2(20);
558 l_sqlerrmsg varchar2(2000);
559
560 -- make insert dirty calls for serial numbers with counter=1;
561 -- since data with counter>1 would already be existing on the palm
562 CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
563 p_userid IN number)
564 IS
565 SELECT access_id
566 FROM csm_mtl_serial_numbers_acc
567 WHERE user_id = p_userid
568 AND current_organization_id = p_organizationid
569 AND counter = 1;
570
571 BEGIN
572 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
573 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
574
575 l_run_date := SYSDATE;
576
577
578 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
579 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
580
581 EXCEPTION
582 WHEN others THEN
583 l_sqlerrno := to_char(SQLCODE);
584 l_sqlerrmsg := substr(SQLERRM, 1,2000);
585 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
586 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
587
588
589 END GET_NEW_ORG_SERIAL_NUMBERS;
590
591 -- currently not being used as serial numbers are not downloaded
592 PROCEDURE INV_LOC_ASS_MSN_MAKE_DIRTY_I(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
593 IS
594 l_sqlerrno VARCHAR2(20);
595 l_sqlerrmsg VARCHAR2(4000);
596 l_error_msg VARCHAR2(4000);
597 l_return_status VARCHAR2(2000);
598
599 BEGIN
600 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
601 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_PROCEDURE);
602
603 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
604 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_PROCEDURE);
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_sqlerrno := to_char(SQLCODE);
609 l_sqlerrmsg := substr(SQLERRM, 1,2000);
610 l_error_msg := ' Exception in INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id:'
611 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
612 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_EXCEPTION);
613 RAISE;
614 END INV_LOC_ASS_MSN_MAKE_DIRTY_I;
615
616 -- currently not being used as serial numbers are not downloaded
617 PROCEDURE INV_LOC_ASS_MSN_MAKE_DIRTY_D(p_csp_inv_loc_assignment_id IN NUMBER,
618 p_user_id IN NUMBER)
619 IS
620 l_sqlerrno VARCHAR2(20);
621 l_sqlerrmsg VARCHAR2(4000);
622 l_error_msg VARCHAR2(4000);
623 l_return_status VARCHAR2(2000);
624
625 BEGIN
626 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
627 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_PROCEDURE);
628
629 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
630 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_PROCEDURE);
631 EXCEPTION
632 WHEN OTHERS THEN
633 l_sqlerrno := to_char(SQLCODE);
634 l_sqlerrmsg := substr(SQLERRM, 1,2000);
635 l_error_msg := ' Exception in INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id:'
636 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
637 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_EXCEPTION);
638 RAISE;
639 END INV_LOC_ASS_MSN_MAKE_DIRTY_D;
640
641 PROCEDURE RECEIVED_MTL_SERIAL_NUM(p_org_id IN NUMBER,p_subinv_code IN VARCHAR2, p_item_id IN NUMBER,p_serial_number IN VARCHAR2,p_user_id IN NUMBER)
642 IS
643 CURSOR received_srl_item
644 IS
645 SELECT csm_mtl_serial_numbers_acc_s.nextval, inventory_item_id, serial_number, current_organization_id,au.user_id
646 FROM mtl_serial_numbers,asg_user au
647 WHERE current_status =3 -- html5 supports only 3
648 AND current_organization_id=p_org_id
649 AND current_subinventory_code = p_subinv_code
650 AND inventory_item_id = p_item_id
651 AND serial_number = p_serial_number
652 AND au.user_id = p_user_id
653 AND ( current_subinventory_code, current_organization_id )
654 IN ( SELECT subinventory_code ,organization_id
655 FROM csp_inv_loc_assignments
656 WHERE resource_id = au.resource_id
657 AND resource_type='RS_EMPLOYEE'
658 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE ) AND nvl( effective_date_end , SYSDATE ))
659 AND ( inventory_item_id, serial_number, current_organization_id ) NOT IN (
660 SELECT inventory_item_id, serial_number, current_organization_id
661 FROM csm_mtl_serial_numbers_acc
662 WHERE user_id = au.user_id );
663
664 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
665 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
666 l_tab_item_id ASG_DOWNLOAD.ACCESS_LIST;
667 l_tab_org_id ASG_DOWNLOAD.ACCESS_LIST;
668 l_markdirty BOOLEAN;
669
670 TYPE serial_numbers_tbl_typ IS TABLE OF mtl_serial_numbers.serial_number%TYPE INDEX BY binary_integer;
671 l_tab_srl serial_numbers_tbl_typ;
672
673 BEGIN
674
675 CSM_UTIL_PKG.LOG('Entered RECEIVED_MTL_SERIAL_NUM', 'CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM',FND_LOG.LEVEL_STATEMENT);
676
677 OPEN received_srl_item;
678 FETCH received_srl_item BULK COLLECT INTO l_tab_access_id,l_tab_item_id,l_tab_srl,l_tab_org_id,l_tab_user_id;
679 CLOSE received_srl_item;
680
681 IF l_tab_access_id.COUNT > 0 THEN
682 FORALL i IN 1..l_tab_access_id.count
683 INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
684 counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
685 VALUES (l_tab_access_id(i), l_tab_user_id(i), l_tab_srl(i), l_tab_item_id(i), l_tab_org_id(i),
686 1, fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
687
688 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_serial_numbers_acc ',
689 'CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM',FND_LOG.LEVEL_STATEMENT);
690
691 l_markdirty := asg_download.mark_dirty(
692 P_PUB_ITEM => g_pub_item
693 , p_accessList => l_tab_access_id
694 , p_userid_list => l_tab_user_id
695 , p_dml_type => 'I'
696 , P_TIMESTAMP => sysdate
697 );
698 END IF;
699 CSM_UTIL_PKG.LOG('Leaving RECEIVED_MTL_SERIAL_NUM ', 'CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM',FND_LOG.LEVEL_STATEMENT);
700 END RECEIVED_MTL_SERIAL_NUM;
701
702 END CSM_SERIAL_NUMBERS_EVENT_PKG;
[Home] [Help]