1 PACKAGE BODY CSM_SERIAL_NUMBERS_EVENT_PKG AS
2 /* $Header: csmeslnb.pls 120.6 2008/02/06 13:50:00 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_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 ---Insert for non ib items
151 IF l_access_id_tbl.count > 0 THEN
152 l_access_id_tbl.delete;
153 l_inventory_items_tbl.delete;
154 l_serial_numbers_tbl.delete;
155 l_organizations_tbl.delete;
156 END IF;
157
158 -- BULK collect all new inserted serial numbers with STatus 1 and 4 for Non ib items
159 OPEN l_ins_mtl_ser_num_fornonib_csr(p_organization_id, p_resource_id, p_user_id, p_last_run_date);
160 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;
161 CLOSE l_ins_mtl_ser_num_fornonib_csr;
162
163 -- bulk insert into acc tables
164 IF l_access_id_tbl.count > 0 THEN
165 FORALL i IN 1..l_access_id_tbl.count
166 INSERT INTO csm_mtl_serial_numbers_acc(access_id, user_id, serial_number, inventory_item_id, current_organization_id,
167 counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
168 VALUES (l_access_id_tbl(i), p_user_id, l_serial_numbers_tbl(i), l_inventory_items_tbl(i), l_organizations_tbl(i),
169 1, fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
170
171 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_serial_numbers_acc for resource ' || p_resource_id ,
172 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
173
174 -- make dirty calls
175 FOR i IN 1..l_access_id_tbl.count LOOP
176 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
177 l_access_id_tbl(i),
178 p_resource_id,
179 asg_download.ins,
180 l_run_date);
181 END LOOP;
182 END IF;
183
184 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS ',
185 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
186
187 EXCEPTION
188 WHEN others THEN
189 l_sqlerrno := to_char(SQLCODE);
190 l_sqlerrmsg := substr(SQLERRM, 1,2000);
191 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
192 'CSM_SERIAL_NUMBERS_EVENT_PKG.INSERT_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
193
194 END INSERT_MTL_SERIAL_NUMBERS;
195
196 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)
197 IS
198 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY binary_integer;
199 l_access_id_tbl access_id_tbl_typ;
200 l_run_date DATE;
201 l_markdirty boolean;
202 l_sqlerrno varchar2(20);
203 l_sqlerrmsg varchar2(2000);
204
205 -- upd serial numbers whose locations have changed within the org to locations user has access to
206 CURSOR l_upd_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
207 IS
208 SELECT access_id
209 FROM csm_mtl_serial_numbers_acc acc
210 , mtl_serial_numbers msn
211 WHERE msn.inventory_item_id = acc.inventory_item_id
212 AND msn.serial_number = acc.serial_number
213 AND msn.current_organization_id = acc.current_organization_id
214 AND msn.last_update_date >= p_lastrundate
215 AND acc.user_id = p_userid;
216 /*AND ( msn.current_subinventory_code, msn.current_organization_id ) IN (
217 SELECT subinventory_code
218 , organization_id
219 FROM csp_inv_loc_assignments
220 WHERE resource_id = p_resourceid
221 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
222 AND nvl( effective_date_end , SYSDATE ));*/
223
224 -- decrement counter of serial numbers whose locations have changed within the org or that reside in a diff org
225 CURSOR l_del_mtl_serial_numbers_csr(p_lastrundate IN date, p_resourceid IN number, p_userid IN number)
226 IS
227 SELECT access_id
228 FROM csm_mtl_serial_numbers_acc acc
229 , mtl_serial_numbers msn
230 WHERE msn.inventory_item_id = acc.inventory_item_id
231 AND msn.serial_number = acc.serial_number
232 AND msn.current_organization_id = acc.current_organization_id
233 AND acc.user_id = p_userid
234 AND msn.current_status =3
235 AND ( msn.current_subinventory_code, msn.current_organization_id ) NOT IN (
236 SELECT subinventory_code
237 , organization_id
238 FROM csp_inv_loc_assignments
239 WHERE resource_id = p_resourceid
240 AND SYSDATE BETWEEN nvl( effective_date_start, SYSDATE )
241 AND nvl( effective_date_end , SYSDATE ))
242 UNION
243 SELECT access_id
244 FROM csm_mtl_serial_numbers_acc acc
245 WHERE acc.user_id = p_user_id
246 AND NOT EXISTS
247 (SELECT 1
248 FROM mtl_serial_numbers msn
249 WHERE msn.serial_number = acc.serial_number
250 AND msn.inventory_item_id = acc.inventory_item_id
251 AND msn.current_organization_id = acc.current_organization_id
252 AND msn.CURRENT_STATUS =3
253 )
254 AND NOT EXISTS
255 ( SELECT 1
256 FROM mtl_serial_numbers ser,
257 csm_mtl_onhand_qty_acc ohqacc,
258 mtl_system_items sys
259 WHERE ser.current_status in (1,4)
260 AND ohqacc.user_id = acc.user_id
261 AND ser.serial_number = acc.serial_number
262 AND ser.inventory_item_id =acc.inventory_item_id
263 AND ser.inventory_item_id =sys.inventory_item_id
264 AND ser.CURRENT_ORGANIZATION_ID = sys.ORGANIZATION_ID
265 AND NVL(sys.COMMS_NL_TRACKABLE_FLAG,'N') ='N'
266 AND ohqacc.inventory_item_id = ser.inventory_item_id
267 AND ohqacc.organization_id = ser.current_organization_id
268
269 );
270
271
272 CURSOR l_delete_serial_number_acc(p_userid IN number)
273 IS
274 SELECT access_id
275 FROM csm_mtl_serial_numbers_acc
276 WHERE user_id = p_userid
277 AND counter = 0;
278
279 BEGIN
280 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
281 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
282
283 l_run_date := SYSDATE;
284
285 IF l_access_id_tbl.count > 0 THEN
286 l_access_id_tbl.DELETE;
287 END IF;
288
289 -- bulk collect all updated serial_numbers
290 OPEN l_upd_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
291 FETCH l_upd_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
292 CLOSE l_upd_mtl_serial_numbers_csr;
293
294 IF l_access_id_tbl.count > 0 THEN
295 CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for updating csm_mtl_serial_numbers for resource ' || p_resource_id ,
296 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
297
298 -- make dirty calls
299 FOR i IN 1..l_access_id_tbl.count LOOP
300 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
301 l_access_id_tbl(i),
302 p_resource_id,
303 asg_download.upd,
304 l_run_date);
305 END LOOP;
306
307 l_access_id_tbl.DELETE;
308 END IF;
309
310 -- bulk collect all serial numbers to be deleted
311 OPEN l_del_mtl_serial_numbers_csr(p_last_run_date, p_resource_id, p_user_id);
312 FETCH l_del_mtl_serial_numbers_csr BULK COLLECT INTO l_access_id_tbl;
313 CLOSE l_del_mtl_serial_numbers_csr;
314
315 -- update counter for records to be deleted
316 IF l_access_id_tbl.count > 0 THEN
317 FORALL i IN 1..l_access_id_tbl.count
318 UPDATE csm_mtl_serial_numbers_acc
319 SET counter = counter - 1
320 ,last_update_date = SYSDATE
321 ,last_updated_by = fnd_global.user_id
322 WHERE access_id = l_access_id_tbl(i);
323
324 l_access_id_tbl.delete;
325 END IF;
326
327 -- delete all access_id's that have counter = 0
328 OPEN l_delete_serial_number_acc(p_user_id);
329 FETCH l_delete_serial_number_acc BULK COLLECT INTO l_access_id_tbl;
330 CLOSE l_delete_serial_number_acc;
331
332 IF l_access_id_tbl.count > 0 THEN
333 CSM_UTIL_PKG.LOG(l_access_id_tbl.count || ' records sent to olite for deleting csm_mtl_serial_numbers for resource ' || p_resource_id ,
334 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_STATEMENT);
335
336 -- make dirty calls
337 FOR i IN 1..l_access_id_tbl.count LOOP
338 l_markdirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
339 l_access_id_tbl(i),
340 p_resource_id,
341 asg_download.del,
342 l_run_date);
343 END LOOP;
344
345 FORALL i IN 1..l_access_id_tbl.count
346 DELETE csm_mtl_serial_numbers_acc WHERE ACCESS_ID = l_access_id_tbl(i);
347
348 l_access_id_tbl.DELETE;
349 END IF;
350
351 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS ',
352 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
353
354 EXCEPTION
355 WHEN others THEN
356 l_sqlerrno := to_char(SQLCODE);
357 l_sqlerrmsg := substr(SQLERRM, 1,2000);
358 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
359 'CSM_SERIAL_NUMBERS_EVENT_PKG.UPDATE_MTL_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
360
361 END UPDATE_MTL_SERIAL_NUMBERS;
362
363 PROCEDURE refresh_mtl_serial_numbers_acc
364 IS
365 PRAGMA AUTONOMOUS_TRANSACTION;
366 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
367 l_all_omfs_palm_resource_list asg_download.user_list;
368 l_valid_omfs_resource_list asg_download.user_list;
369 l_null_palm_omfs_resource_list asg_download.user_list;
370 l_user_palm_organization_id mtl_system_items.organization_id%TYPE;
371 l_usr_list_for_serial asg_download.user_list;
372 l_rsrc_list_for_serial asg_download.user_list;
373 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
374 l_run_date DATE;
375 l_user_id fnd_user.user_id%TYPE;
376 l_sqlerrno varchar2(20);
377 l_sqlerrmsg varchar2(2000);
378
379 CURSOR l_last_run_date_csr
380 IS
381 SELECT nvl(last_run_date, (sysdate - 365*50))
382 FROM jtm_con_request_data
383 WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
384 AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
385
386 CURSOR l_user_id_csr (p_resourceid IN number)
387 IS
388 SELECT user_id
389 FROM asg_user
390 WHERE resource_id = p_resourceid;
391
392 CURSOR l_usr_rsrc_list_serial
393 IS
394 SELECT
395 au.resource_id,
396 au.user_id
397 FROM asg_user_pub_resps aupr,
398 asg_user au
399 WHERE aupr.pub_name = 'SERVICEP'
400 AND au.user_name = aupr.user_name
401 AND au.owner_id = au.user_id
402 AND au.enabled = 'Y';
403
404 BEGIN
405 l_run_date := SYSDATE;
406
407 CSM_UTIL_PKG.LOG('Entering CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
408 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
409
410 -- R12 Serial Number implementation
411 IF l_usr_list_for_serial.COUNT > 0 THEN
412 l_usr_list_for_serial.DELETE;
413 END IF;
414
415 IF l_rsrc_list_for_serial.COUNT > 0 THEN
416 l_rsrc_list_for_serial.DELETE;
417 END IF;
418
419 OPEN l_last_run_date_csr;
420 FETCH l_last_run_date_csr INTO l_last_run_date;
421 CLOSE l_last_run_date_csr;
422
423 OPEN l_usr_rsrc_list_serial;
424 FETCH l_usr_rsrc_list_serial BULK COLLECT INTO l_rsrc_list_for_serial,l_usr_list_for_serial;
425 CLOSE l_usr_rsrc_list_serial;
426 IF l_usr_list_for_serial.COUNT > 0 THEN
427 FOR l_count in 1..l_usr_list_for_serial.COUNT LOOP
428 l_user_palm_organization_id:=csm_profile_pkg.get_organization_id(l_usr_list_for_serial(l_count));
429 --updating/deleting serial numbers already present in the ACC
430 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));
431 --inserting new serial numbers
432 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));
433
434 END LOOP;
435 END IF;
436
437
438 -- update last_run_date
439 UPDATE jtm_con_request_data
440 SET last_run_date = l_run_date
441 WHERE package_name = 'CSM_SERIAL_NUMBERS_EVENT_PKG'
442 AND procedure_name = 'REFRESH_MTL_SERIAL_NUMBERS_ACC';
443
444 COMMIT;
445
446 CSM_UTIL_PKG.LOG('Leaving CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc ',
447 'CSM_SERIAL_NUMBERS_EVENT_PKG.refresh_mtl_serial_numbers_acc',FND_LOG.LEVEL_PROCEDURE);
448
449
450 EXCEPTION
451 WHEN others THEN
452 l_sqlerrno := to_char(SQLCODE);
453 l_sqlerrmsg := substr(SQLERRM, 1,2000);
454 ROLLBACK;
455 CSM_UTIL_PKG.LOG('Exception in CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
456 'CSM_SERIAL_NUMBERS_EVENT_PKG.REFRESH_MTL_SERIAL_NUMBERS_ACC',FND_LOG.LEVEL_EXCEPTION);
457
458 END REFRESH_MTL_SERIAL_NUMBERS_ACC;
459
460 PROCEDURE DELETE_OLD_ORG_SERIAL_NUMBERS(p_organization_id IN number
461 , p_user_id IN number
462 , p_resource_id IN number)
463 IS
464 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
465 l_access_id_tbl access_id_tbl_typ;
466 l_mark_dirty boolean;
467 l_run_date date;
468 l_sqlerrno varchar2(20);
469 l_sqlerrmsg varchar2(2000);
470
471 -- make delete dirty calls for serial numbers with counter=1; do not delete from acc
472 -- since delete will be done based on csp_inv_loc_assignment data
473 CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
474 p_userid IN number)
475 IS
476 SELECT access_id
477 FROM csm_mtl_serial_numbers_acc
478 WHERE user_id = p_userid
479 AND current_organization_id = p_organizationid
480 AND counter = 0;
481
482 BEGIN
483 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
484 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
485
486 l_run_date := SYSDATE;
487
488
489 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS ',
490 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
491
492 EXCEPTION
493 WHEN others THEN
494 l_sqlerrno := to_char(SQLCODE);
495 l_sqlerrmsg := substr(SQLERRM, 1,2000);
496 --logm('Exception ' || l_sqlerrmsg);
497 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
498 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.DELETE_OLD_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
499
500 END DELETE_OLD_ORG_SERIAL_NUMBERS;
501
502
503 PROCEDURE GET_NEW_ORG_SERIAL_NUMBERS(p_organization_id IN number
504 , p_user_id IN number
505 , p_resource_id IN number)
506 IS
507 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_serial_numbers_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
508 l_access_id_tbl access_id_tbl_typ;
509 l_mark_dirty boolean;
510 l_run_date date;
511 l_sqlerrno varchar2(20);
512 l_sqlerrmsg varchar2(2000);
513
514 -- make insert dirty calls for serial numbers with counter=1;
515 -- since data with counter>1 would already be existing on the palm
516 CURSOR l_del_mtl_serial_numbers_csr(p_organizationid IN number,
517 p_userid IN number)
518 IS
519 SELECT access_id
520 FROM csm_mtl_serial_numbers_acc
521 WHERE user_id = p_userid
522 AND current_organization_id = p_organizationid
523 AND counter = 1;
524
525 BEGIN
526 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
527 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
528
529 l_run_date := SYSDATE;
530
531
532 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS ',
533 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_PROCEDURE);
534
535 EXCEPTION
536 WHEN others THEN
537 l_sqlerrno := to_char(SQLCODE);
538 l_sqlerrmsg := substr(SQLERRM, 1,2000);
539 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
540 'CSM_MTL_SERIAL_NUMBERS_EVENT_PKG.GET_NEW_ORG_SERIAL_NUMBERS',FND_LOG.LEVEL_EXCEPTION);
541
542
543 END GET_NEW_ORG_SERIAL_NUMBERS;
544
545 -- currently not being used as serial numbers are not downloaded
546 PROCEDURE INV_LOC_ASS_MSN_MAKE_DIRTY_I(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
547 IS
548 l_sqlerrno VARCHAR2(20);
549 l_sqlerrmsg VARCHAR2(4000);
550 l_error_msg VARCHAR2(4000);
551 l_return_status VARCHAR2(2000);
552
553 BEGIN
554 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
555 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_PROCEDURE);
556
557 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
558 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_PROCEDURE);
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 l_sqlerrno := to_char(SQLCODE);
563 l_sqlerrmsg := substr(SQLERRM, 1,2000);
564 l_error_msg := ' Exception in INV_LOC_ASS_MSN_MAKE_DIRTY_I for csp_inv_loc_assignment_id:'
565 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
566 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I',FND_LOG.LEVEL_EXCEPTION);
567 RAISE;
568 END INV_LOC_ASS_MSN_MAKE_DIRTY_I;
569
570 -- currently not being used as serial numbers are not downloaded
571 PROCEDURE INV_LOC_ASS_MSN_MAKE_DIRTY_D(p_csp_inv_loc_assignment_id IN NUMBER,
572 p_user_id IN NUMBER)
573 IS
574 l_sqlerrno VARCHAR2(20);
575 l_sqlerrmsg VARCHAR2(4000);
576 l_error_msg VARCHAR2(4000);
577 l_return_status VARCHAR2(2000);
578
579 BEGIN
580 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
581 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_PROCEDURE);
582
583 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
584 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_PROCEDURE);
585 EXCEPTION
586 WHEN OTHERS THEN
587 l_sqlerrno := to_char(SQLCODE);
588 l_sqlerrmsg := substr(SQLERRM, 1,2000);
589 l_error_msg := ' Exception in INV_LOC_ASS_MSN_MAKE_DIRTY_D for csp_inv_loc_assignment_id:'
590 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
591 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D',FND_LOG.LEVEL_EXCEPTION);
592 RAISE;
593 END INV_LOC_ASS_MSN_MAKE_DIRTY_D;
594
595 END CSM_SERIAL_NUMBERS_EVENT_PKG;
[Home] [Help]