使用API更新供应商名称及曾用名

原文地址 更新供应商名称 EBS R12 (12.1.3)

 

更新供应商名称或替代供应商名称不能直接使用

pos_vendor_pub_pkg.update_vendor
(
p_vendor_rec => l_vendor_rec,
x_return_status => l_return_status, --返回的状态
x_msg_count => l_msg_count, --返回的错误数量
x_msg_data => l_msg_data); --返回的错误信息

此API,需要使用另外一个API

hz_party_v2pub.update_organization (p_init_msg_list => fnd_api.g_false
,p_organization_rec => l_vendor 
,p_party_object_version_number => v_object
,x_profile_id => v_profile_id
,x_return_status => l_vendor_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data 
);

where l_vendor.organization_name represents vendor name 
and l_vendor.organization_name_phonetic represents vendor alternate name

SELECT pv.party_id
,hp.object_version_number 
FROM po_vendors pv
,hz_parties hp
WHERE upper(pv.vendor_name) = upper(p_vendor_name)
AND pv.end_date_active IS NULL
AND pv.party_id = hp.party_id;

This query will give mandatory fields for this api

 代码

PROCEDURE update_vendor_name(p_vendor_id       NUMBER,
                             p_vendor_name     VARCHAR2,
                             p_vendor_alt_name VARCHAR2,
                             errbuf            OUT VARCHAR2,
                             retcode           OUT VARCHAR2) IS
    l_organization_rec      hz_party_v2pub.organization_rec_type;
    x_profile_id            NUMBER;
    l_party_id              NUMBER;
    l_object_version_number NUMBER;
    l_msg_count             NUMBER;
    l_msg_data              VARCHAR2(4000);
    l_return_status         VARCHAR2(10);
BEGIN
    retcode := '0';
    errbuf  := 'Success';
    SELECT aps.party_id,
           hzp.object_version_number
      INTO l_party_id,
           l_object_version_number
      FROM ap_suppliers aps,
           hz_parties   hzp
     WHERE vendor_id = p_vendor_id
       AND aps.party_id = hzp.party_id;

    l_organization_rec.party_rec.party_id         := l_party_id;
    l_organization_rec.organization_name          := p_vendor_name;
    l_organization_rec.organization_name_phonetic := p_vendor_alt_name;

    hz_party_v2pub.update_organization(p_init_msg_list               => fnd_api.g_true,
                                       p_organization_rec            => l_organization_rec,
                                       p_party_object_version_number => l_object_version_number,
                                       x_profile_id                  => x_profile_id,
                                       x_return_status               => l_return_status,
                                       x_msg_count                   => l_msg_count,
                                       x_msg_data                    => errbuf);
    IF l_return_status <> g_ret_sts_success THEN
        FOR i IN 1 .. l_msg_count LOOP
            errbuf := errbuf || substr(fnd_msg_pub.get(p_encoded => 'F'),
                                       1,
                                       255);
        
        END LOOP;
        retcode := 'CUXPOS-0005';
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END update_vendor_name;