--定义接口表字段
create table CUX_INV_VENDOR_8MIMPORT
(
group_id NUMBER not null,
vendor_name VARCHAR2(360),
vendor_type VARCHAR2(30),
tax_reference VARCHAR2(30),
vendor_site_code VARCHAR2(80),
city VARCHAR2(80),
address VARCHAR2(360),
person_name VARCHAR2(240),
phone_number VARCHAR2(240),
email_address VARCHAR2(240),
bank_name VARCHAR2(240),
bank_branch_name VARCHAR2(240),
bank_account_num VARCHAR2(240),
bank_account_name VARCHAR2(240),
status_code VARCHAR2(1) default 'N' not null,
return_msg VARCHAR2(2400),
creation_date DATE,
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240),
attribute6 VARCHAR2(240),
attribute7 VARCHAR2(240),
attribute8 VARCHAR2(240),
attribute9 VARCHAR2(240),
attribute10 VARCHAR2(240),
attribute11 VARCHAR2(240),
attribute12 VARCHAR2(240),
attribute13 VARCHAR2(240),
attribute14 VARCHAR2(240),
attribute15 VARCHAR2(240)
)
CREATE OR REPLACE PACKAGE cux_inv_vendor_8mimport_pkg IS
/*===============================================
/*==================================================
* PROCEDURE NAME
* main
* DESCRIPTION:
*
* HISTORY:
* 1.00 2018-01-24 jin.he Creation
* ==================================================*/
PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT VARCHAR2);
END cux_inv_vendor_8mimport_pkg;
/
CREATE OR REPLACE PACKAGE BODY cux_inv_vendor_8mimport_pkg IS
/*===============================================
================================================*/
--GLOBAL VARIABLES
g_pkg_name CONSTANT VARCHAR2(30) := 'CUX_INV_VENDOR_8MIMPORT_PKG';
g_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED')
,'N');
------------------------------------------------
--LOG
------------------------------------------------
PROCEDURE log(p_msg VARCHAR2) IS
BEGIN
-- IF g_debug = 'Y' THEN
cux_conc_utl.log_msg(p_msg);
-- END IF;
END log;
------------------------------------------------
--update_status
------------------------------------------------
PROCEDURE update_status(p_group_id IN NUMBER
,p_status_code IN VARCHAR2
,p_return_msg IN VARCHAR2) IS
--PRAGMA AUTONOMOUS_TRANSACTION;
l_count NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_count
FROM cux_inv_vendor_8mimport t
WHERE t.return_msg LIKE ('%' || p_return_msg || '%')
AND t.group_id = p_group_id;
IF l_count = 0 THEN
UPDATE cux_inv_vendor_8mimport
SET status_code = p_status_code
,return_msg = substr(return_msg || ',' || p_return_msg
,2)
WHERE group_id = p_group_id;
--COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
log(dbms_utility.format_error_stack || ' ' || dbms_utility.format_error_backtrace);
END update_status;
FUNCTION get_category_id(p_structure_id NUMBER
,p_categ_name VARCHAR2) RETURN NUMBER IS
v_cate_id NUMBER;
BEGIN
SELECT mck.category_id
INTO v_cate_id
FROM mtl_categories_kfv mck
WHERE mck.concatenated_segments = p_categ_name
AND mck.structure_id = p_structure_id;
RETURN v_cate_id;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END get_category_id;
------------------------------------------------
--GET_LOOKUP_MEANING
------------------------------------------------
FUNCTION get_lookup_meaning(p_lookup_type IN VARCHAR2
,p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
l_meaning VARCHAR2(240);
BEGIN
SELECT flv.meaning
INTO l_meaning
FROM fnd_lookup_values_vl flv
WHERE flv.lookup_type = p_lookup_type
AND flv.lookup_code = p_lookup_code
AND flv.enabled_flag = 'Y';
RETURN l_meaning;
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
WHEN OTHERS THEN
fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
,p_procedure_name => 'GET_LOOKUP_MEANING'
,p_error_text => p_lookup_code || substrb(SQLERRM
,1
,240));
RAISE fnd_api.g_exc_error;
END get_lookup_meaning;
PROCEDURE create_vendor(p_group_id IN NUMBER
,p_vendor_name IN VARCHAR2
,p_vendor_type IN VARCHAR2
,p_tax_reference IN VARCHAR2
,x_vendor_id OUT NUMBER
,x_party_id OUT NUMBER) IS
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
l_vendor_type_lookup_code VARCHAR2(30);
BEGIN
BEGIN
SELECT pov.vendor_id
,pov.party_id
INTO l_vendor_id
,l_party_id
FROM po_vendors pov
WHERE pov.vendor_name = p_vendor_name;
EXCEPTION
WHEN OTHERS THEN
l_vendor_id := NULL;
l_party_id := NULL;
END;
BEGIN
SELECT flv.lookup_code
INTO l_vendor_type_lookup_code
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'VENDOR TYPE'
AND flv.meaning = p_vendor_type
AND flv.language = 'ZHS'
AND flv.enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
l_vendor_type_lookup_code := NULL;
END;
IF l_vendor_id IS NULL THEN