48: IF FND_API.to_boolean(p_init_msg_list) THEN
49: FND_MSG_PUB.initialize;
50: END IF;
51:
52: JTF_Utility_PVT.debug_message(l_full_name || ': start');
53:
54: IF NOT FND_API.compatible_api_call
55: (
56: l_api_version,
64:
65: x_return_status := FND_API.g_ret_sts_success;
66:
67: -- validate
68: JTF_Utility_PVT.debug_message(l_full_name || ': validate');
69:
70: validate_postal_code
71: (
72: p_api_version => l_api_version,
99: END LOOP;
100: END IF;
101:
102: -- insert
103: JTF_Utility_PVT.debug_message(l_full_name || ': insert');
104:
105: INSERT INTO JTF_LOC_POSTAL_CODES
106: (
107: location_postal_code_id,
150: p_count => x_msg_count,
151: p_data => x_msg_data
152: );
153:
154: JTF_Utility_PVT.debug_message(l_full_name||': end');
155:
156: EXCEPTION
157:
158: WHEN FND_API.g_exc_error THEN
227: IF FND_API.to_boolean(p_init_msg_list) THEN
228: FND_MSG_PUB.initialize;
229: END IF;
230:
231: JTF_Utility_PVT.debug_message(l_full_name || ': start');
232:
233: IF NOT FND_API.compatible_api_call
234: (
235: l_api_version,
251: );
252:
253: IF p_remove_flag <> 'Y' THEN
254: -- item level
255: JTF_Utility_PVT.debug_message(l_full_name || ': validate');
256: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
257: JTF_Utility_PVT.debug_message(l_full_name || ': check items');
258: check_items
259: (
253: IF p_remove_flag <> 'Y' THEN
254: -- item level
255: JTF_Utility_PVT.debug_message(l_full_name || ': validate');
256: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
257: JTF_Utility_PVT.debug_message(l_full_name || ': check items');
258: check_items
259: (
260: p_validation_mode => JTF_PLSQL_API.g_update,
261: x_return_status => l_return_status,
270: END IF;
271:
272: -- record level
273: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
274: JTF_Utility_PVT.debug_message(l_full_name||': check record');
275: check_record
276: (
277: p_postal_code_rec => p_postal_code_rec,
278: p_complete_rec => l_postal_code_rec,
287: END IF;
288: END IF;
289:
290: -- update
291: JTF_Utility_PVT.debug_message(l_full_name||': update');
292:
293: IF p_remove_flag = 'Y' THEN
294: UPDATE JTF_LOC_POSTAL_CODES SET
295: end_date_active = SYSDATE
331: p_count => x_msg_count,
332: p_data => x_msg_data
333: );
334:
335: JTF_Utility_PVT.debug_message(l_full_name || ': end');
336:
337: EXCEPTION
338:
339: WHEN FND_API.g_exc_error THEN
404: IF FND_API.to_boolean(p_init_msg_list) THEN
405: FND_MSG_PUB.initialize;
406: END IF;
407:
408: JTF_Utility_PVT.debug_message(l_full_name || ': start');
409:
410: IF NOT FND_API.compatible_api_call
411: (
412: l_api_version,
420:
421: x_return_status := FND_API.g_ret_sts_success;
422:
423: -- delete
424: JTF_Utility_PVT.debug_message(l_full_name || ': delete');
425:
426: DELETE FROM JTF_LOC_POSTAL_CODES
427: WHERE location_postal_code_id = p_postal_code_id
428: AND object_version_number = p_object_version;
446: p_count => x_msg_count,
447: p_data => x_msg_data
448: );
449:
450: JTF_Utility_PVT.debug_message(l_full_name || ': end');
451:
452: EXCEPTION
453:
454: WHEN FND_API.g_exc_error THEN
520: FOR UPDATE OF location_postal_code_id NOWAIT;
521:
522: BEGIN
523: -- initialize
524: JTF_Utility_PVT.debug_message(l_full_name || ': start');
525:
526: IF FND_API.to_boolean(p_init_msg_list) THEN
527: FND_MSG_PUB.initialize;
528: END IF;
540:
541: x_return_status := FND_API.g_ret_sts_success;
542:
543: -- lock
544: JTF_Utility_PVT.debug_message(l_full_name || ': lock');
545:
546: OPEN c_postal_code;
547: FETCH c_postal_code INTO l_postal_code_id;
548: IF (c_postal_code%NOTFOUND) THEN
562: p_count => x_msg_count,
563: p_data => x_msg_data
564: );
565:
566: JTF_Utility_PVT.debug_message(l_full_name || ': end');
567:
568: EXCEPTION
569:
570: WHEN JTF_Utility_PVT.resource_locked THEN
566: JTF_Utility_PVT.debug_message(l_full_name || ': end');
567:
568: EXCEPTION
569:
570: WHEN JTF_Utility_PVT.resource_locked THEN
571: x_return_status := FND_API.g_ret_sts_error;
572: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
573: FND_MESSAGE.set_name('JTF', 'JTF_API_RESOURCE_LOCKED');
574: FND_MSG_PUB.add;
642:
643: BEGIN
644:
645: ----------------------- initialize --------------------
646: JTF_Utility_PVT.debug_message(l_full_name||': start');
647:
648: IF NOT FND_API.compatible_api_call
649: (
650: l_api_version,
660:
661: ---------------------- validate ------------------------
662: -- item level
663: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
664: JTF_Utility_PVT.debug_message(l_full_name||': check items');
665: check_items
666: (
667: p_validation_mode => JTF_PLSQL_API.g_create,
668: x_return_status => l_return_status,
677: END IF;
678:
679: -- record level
680: IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
681: JTF_Utility_PVT.debug_message(l_full_name||': check record');
682: check_record
683: (
684: p_postal_code_rec => p_postal_code_rec,
685: p_complete_rec => p_postal_code_rec,
700: p_count => x_msg_count,
701: p_data => x_msg_data
702: );
703:
704: JTF_Utility_PVT.debug_message(l_full_name ||': end');
705:
706: EXCEPTION
707: WHEN FND_API.g_exc_error THEN
708: x_return_status := FND_API.g_ret_sts_error;
757: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
758:
759: BEGIN
760: -- initialize
761: JTF_Utility_PVT.debug_message(l_full_name || ': start');
762:
763: x_return_status := FND_API.g_ret_sts_success;
764:
765: -- check required items
762:
763: x_return_status := FND_API.g_ret_sts_success;
764:
765: -- check required items
766: JTF_Utility_PVT.debug_message(l_full_name || ': check required items');
767: check_req_items
768: (
769: p_validation_mode => p_validation_mode,
770: p_postal_code_rec => p_postal_code_rec,
775: RETURN;
776: END IF;
777:
778: -- check foreign key items
779: JTF_Utility_PVT.debug_message(l_full_name || ': check fk items');
780: check_fk_items
781: (
782: p_postal_code_rec => p_postal_code_rec,
783: x_return_status => x_return_status
878:
879: x_return_status := FND_API.g_ret_sts_success;
880:
881: IF p_postal_code_rec.location_area_id IS NOT NULL THEN
882: l_fk_flag := JTF_Utility_PVT.check_fk_exists
883: (
884: 'JTF_LOC_AREAS_VL',
885: 'location_area_id',
886: p_postal_code_rec.location_area_id