Пример использования API для перевода контрагента (таблица HZ_PAPTIES) с типом «organization» в статус “Неактивно”:
declare
p_party_id number := 2661479911;
--
l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
l_object_version_number number;
--
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
begin
fnd_global.apps_initialize(-1, 20639, 200); --Диспетчер кредиторов - Кредиторы
l_party_rec.party_id := p_party_id;
l_party_rec.status := 'I';
l_organization_rec.party_rec := l_party_rec;
select hp.object_version_number into l_object_version_number
from hz_parties hp where hp.party_id = l_party_rec.party_id;
HZ_PARTY_V2PUB.update_organization(p_init_msg_list => fnd_api.g_false,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
dbms_output.put_line(x_return_status);
if (x_return_status <> 'S') then
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);
dbms_output.put_line('ERROR update for party_id='||l_party_rec.party_id||':');
if x_msg_count = 1 then
dbms_output.put_line(x_msg_data);
else
FOR i IN 1 .. x_msg_count LOOP
dbms_output.put_line(fnd_msg_pub.get(fnd_msg_pub.g_next,
fnd_api.g_false));
END LOOP;
end if;
end if;
commit;
end;
Скрипт обновления имени и альтернативного имени поставщика.
Убираем лишние пробелы в конце и начале имени.
declare
l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
x_profile_id NUMBER;
--
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;
--
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
begin
fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы
for i in (SELECT vendor_id,
vendor_name,
vendor_name_alt,
party_id,
(select hp.object_version_number
from hz_parties hp where party_id = pv.party_id
) as object_version_number
FROM po_vendors pv
WHERE 1=1
and (vendor_name like '% ' or vendor_name like ' %'
or vendor_name_alt like '% '
or vendor_name_alt like ' %')
)
loop
l_all_count := l_all_count + 1;
--dbms_output.put_line('vendor_id='||i.vendor_id);
l_party_rec.party_id := i.party_id;
l_organization_rec.organization_name := trim(i.vendor_name);
l_organization_rec.organization_name_phonetic := trim(i.vendor_name);
l_organization_rec.party_rec := l_party_rec;
HZ_PARTY_V2PUB.update_organization(p_init_msg_list => FND_API.G_TRUE,
p_organization_rec => l_organization_rec,
p_party_object_version_number => i.object_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
--dbms_output.put_line(x_return_status);
if (x_return_status <> 'S') then
l_failure_count := l_failure_count + 1;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
p_count => x_msg_count,
p_data => x_msg_data);
if x_msg_count = 1 then
dbms_output.put_line('ERROR vendor_id='||i.vendor_id
||'; vendor_name="'||i.vendor_name||'"; '|| x_msg_data );
else
FOR j IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
dbms_output.put_line('ERROR vendor_id='||i.vendor_id
||'; vendor_name="'||i.vendor_name||'"; '|| x_msg_data );
END LOOP;
end if;
else
l_success_count := l_success_count + 1;
end if;
end loop;
dbms_output.put_line('+--------------------------------------+');
dbms_output.put_line('Обновление поставщиков');
dbms_output.put_line(' Общее количество : '||l_all_count);
dbms_output.put_line(' Обновлено успешно : '||l_success_count);
dbms_output.put_line(' Не удалось обновить : '||l_failure_count);
dbms_output.put_line('+--------------------------------------+');
COMMIT;
end;
Последние комментарии