CREATE OR REPLACE PACKAGE BODY cux_ar_party_location_pkg IS g_pkg_name CONSTANT VARCHAR2(30) := 'CUX_AR_PARTY_LOCATION_PKG'; -- Debug g_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N'); g_space VARCHAR2(30) := chr(38) || 'nbsp'; /*================================================== Program Name: PROCESS_REQUEST Description: History: * 1.00 2018-04-19 wang.chen Creation ==================================================*/ PROCEDURE process_request(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false, p_commit IN VARCHAR2 DEFAULT fnd_api.g_false, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2) IS l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_REQUEST'; l_savepoint_name CONSTANT VARCHAR2(30) := 'PROCESS_REQUEST'; CURSOR cur IS SELECT t.import_id, t.party_name, t.contract_name, t.contract_phone, t.address FROM cux_ar_party_loc_import t; l_location_rec hz_location_v2pub.location_rec_type; l_object_version_number NUMBER; --x_return_status VARCHAR2(200); --x_msg_count NUMBER; --x_msg_data VARCHAR2(200); l_error_message VARCHAR2(1000); BEGIN x_return_status := cux_api.start_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_init_msg_list => fnd_api.g_true); IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN RAISE fnd_api.g_exc_unexpected_error; ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN RAISE fnd_api.g_exc_error; END IF; FOR rec IN cur LOOP FOR rec1 IN (SELECT DISTINCT hl.location_id, hl.object_version_number FROM hz_locations hl, hz_party_sites hps, hz_parties hp WHERE hps.location_id = hl.location_id AND hps.party_id = hp.party_id AND hp.party_name = rec.party_name) LOOP l_location_rec.location_id := rec1.location_id; l_location_rec.address1 := rec.address; l_location_rec.address2 := rec.contract_name; l_location_rec.address3 := rec.contract_phone; hz_location_v2pub.update_location(p_location_rec => l_location_rec, p_object_version_number => rec1.object_version_number, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); IF x_msg_count > 1 AND x_return_status <> fnd_api.g_ret_sts_success THEN FOR i IN 1 .. x_msg_count LOOP l_error_message := i || ' . ' || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 255); cux_conc_utl.log_msg('Party Name: ' || rec.party_name); cux_conc_utl.log_msg('Orig Location ID: ' || rec1.location_id || ',' || l_error_message); END LOOP; END IF; END LOOP; END LOOP; -- 输出表尾 cux_api.end_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_commit => p_commit, x_msg_count => x_msg_count, x_msg_data => x_msg_data); EXCEPTION WHEN fnd_api.g_exc_error THEN x_return_status := cux_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, --p_savepoint_name => l_savepoint_name, p_exc_name => cux_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); WHEN fnd_api.g_exc_unexpected_error THEN x_return_status := cux_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, -- p_savepoint_name => l_savepoint_name, p_exc_name => cux_api.g_exc_name_unexp, x_msg_count => x_msg_count, x_msg_data => x_msg_data); WHEN OTHERS THEN x_return_status := cux_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, --- p_savepoint_name => l_savepoint_name, p_exc_name => cux_api.g_exc_name_others, x_msg_count => x_msg_count, x_msg_data => x_msg_data); END process_request; /*================================================== * FUNCTION / PROCEDURE * NAME : * main * DESCRIPTION: * 报表主程序 * HISTORY: * 1.00 2018-04-19 wang.chen Creation 2 * ================================================== */ PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS l_return_status VARCHAR2(30) := fnd_api.g_ret_sts_success; l_msg_count NUMBER; l_msg_data VARCHAR2(2000); l_api_name CONSTANT VARCHAR2(30) := 'MAIN'; l_date_from DATE; l_date_to DATE; BEGIN retcode := '0'; cux_conc_utl.log_header; process_request(p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN RAISE fnd_api.g_exc_unexpected_error; ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN RAISE fnd_api.g_exc_error; END IF; -- concurrent footer log cux_conc_utl.log_footer; EXCEPTION WHEN fnd_api.g_exc_error THEN cux_conc_utl.log_message_list; retcode := '1'; errbuf := REPLACE(l_msg_data, chr(0), ' '); WHEN fnd_api.g_exc_unexpected_error THEN cux_conc_utl.log_message_list; retcode := '2'; errbuf := REPLACE(l_msg_data, chr(0), ' '); WHEN OTHERS THEN fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name, p_procedure_name => l_api_name, p_error_text => substrb(SQLERRM, 1, 240)); cux_conc_utl.log_message_list; retcode := '2'; errbuf := SQLERRM; END main; END cux_ar_party_location_pkg;
create table cux_ar_party_loc_import (IMPORT_ID NUMBER, PARTY_NAME VARCHAR2(240), CONTRACT_NAME VARCHAR2(240), CONTRACT_PHONE VARCHAR2(240), ADDRESS VARCHAR2(240) ) select * from cux_ar_party_loc_import for update