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
浙公网安备 33010602011771号