Скрипт обновления имени и альтернативного имени поставщика.
Убираем лишние пробелы в конце и начале имени.
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;
Последние комментарии