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
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;
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;
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,
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,
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
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);
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:
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)
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:
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
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,
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,
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
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);
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:
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
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
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
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;
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:
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
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
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:
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
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:
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);
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
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
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);
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;
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;