Скрипт обновления альтернативного имени отделения поставщика.
В данном примере обнуляем альтернативное наименование.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
x_return_status varchar2(1);
x_msg_count number;
x_msg_data varchar2(4000);
--
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
--
l_success_count NUMBER := 0;
l_failure_count NUMBER := 0;
l_all_count NUMBER := 0;
BEGIN
fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы
l_success_count := 0;
l_failure_count := 0;
l_all_count := 0;
-- цикл по отделениям
for s in (select t.vendor_site_id from ap_supplier_sites_all t
where 1=1
and t.vendor_site_code like '%XX%'
and vendor_site_code_alt is not null
)
loop
l_all_count := l_all_count + 1;
-- обнуляем альт.имя
l_vendor_site_rec.VENDOR_SITE_CODE_ALT := chr(0);
--
ap_vendor_pub_pkg.update_vendor_site(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => s.vendor_site_id);
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_site_id='||s.vendor_site_id
||'"; '|| 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_site_id='||s.vendor_site_id
||'"; '|| x_msg_data );
END LOOP;
end if;
else
l_success_count := l_success_count + 1;
end if;
end loop; -- s
dbms_output.put_line('+--------------------------------------+');
dbms_output.put_line('UPDATE INFORMATION');
dbms_output.put_line(' All count : '||l_all_count);
dbms_output.put_line(' Update success : '||l_success_count);
dbms_output.put_line(' Update failure : '||l_failure_count);
dbms_output.put_line('+--------------------------------------+');
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;
Пример скрипта для создания расчетного счета на отделении поставщика:
DECLARE
l_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;
out_mesg apps.iby_fndcpt_common_pub.result_rec_type;
l_acct NUMBER;
l_bank_id NUMBER := 0; --!!! установить значение
l_branch_id NUMBER := 0; --!!!
l_acct_owner_party_id NUMBER := 0; --!!!
l_supplier_site_id NUMBER := 0; --!!!
l_party_site_id NUMBER := 0; --!!!
--
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
BEGIN
FND_MSG_PUB.initialize;
fnd_global.apps_initialize(1, 20639, 200); --Диспетчер кредиторов - Кредиторы
l_bank_acct_rec.bank_id := l_bank_id;
l_bank_acct_rec.branch_id := l_branch_id;
l_bank_acct_rec.country_code := 'RU';
l_bank_acct_rec.bank_account_name := '3321615678101831567890';
l_bank_acct_rec.bank_account_num := 3321615678101831567890;
l_bank_acct_rec.acct_owner_party_id := l_acct_owner_party_id;
l_bank_acct_rec.currency := 'RUB';
l_bank_acct_rec.object_version_number := 1.0;
l_bank_acct_rec.start_date := sysdate;
l_bank_acct_rec.foreign_payment_use_flag := 'Y';
l_bank_acct_rec.payment_factor_flag := 'N';
apps.iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version => 1.0,
p_init_msg_list => 'F',
p_ext_bank_acct_rec => l_bank_acct_rec,
p_association_level => 'SS',
p_supplier_site_id => l_supplier_site_id,
p_party_site_id => l_party_site_id,
p_org_id => 82,
p_org_type => 'OPERATING_UNIT',
x_acct_id => l_acct,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => out_mesg);
COMMIT;
dbms_output.put_line('x_return_status='||x_return_status);
dbms_output.put_line('out_mesg.Result_Code ='||out_mesg.Result_Code);
dbms_output.put_line('out_mesg.Result_Category ='||out_mesg.Result_Category);
dbms_output.put_line('out_mesg.Result_Message ='||out_mesg.Result_Message);
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(chr(10)||'x_msg_count='||x_msg_count);
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;
end;
OEBS Пример использования API для создания поставщика и отделения поставщика:
declare
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_vendor_id number;
x_party_id number;
x_vendor_site_id number;
x_party_site_id number;
x_location_id number;
l_party_site_id number;
--
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
begin
l_vendor_rec.SEGMENT1 := '1234575';
l_vendor_rec.VENDOR_NAME := 'MYTESTSUPPLIER2';
l_vendor_rec.SUMMARY_FLAG := 'N';
l_vendor_rec.ENABLED_FLAG := 'Y';
AP_VENDOR_PUB_PKG.Create_Vendor(p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id);
if (x_return_status <> 'S') then
dbms_output.put_line('ERROR in supplier creation');
dbms_output.put_line('--------------------------------------');
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;
else
l_vendor_site_rec.vendor_id := x_vendor_id;
l_vendor_site_rec.VENDOR_SITE_CODE := 'MYHOME';
l_vendor_site_rec.org_id := 82;
l_vendor_site_rec.COUNTRY := 'RU';
l_vendor_site_rec.ADDRESS_LINE1 := 'Lenina';
l_vendor_site_rec.PURCHASING_SITE_FLAG := 'Y';
l_vendor_site_rec.PAY_SITE_FLAG := 'Y';
AP_VENDOR_PUB_PKG.Create_Vendor_Site(p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id);
if (x_return_status <> 'S') then
dbms_output.put_line('ERROR in supplier site creation');
dbms_output.put_line('--------------------------------------');
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;
else
dbms_output.put_line('Supplier Site Created!');
end if;
end if;
commit;
end;
ОЕБС Пример вызова параллельной программы для сторнирования журналов
-- -------------------------------------------------------------------------------
-- Сторнирование журнала
PROCEDURE reverse_journals(x_return_status OUT VARCHAR2
,x_mess OUT VARCHAR2)
IS
TYPE t_integers IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
l_request_ids t_integers;
l_result BOOLEAN := TRUE;
l_access_set_id NUMBER;
BEGIN
write_log('START REVERSE_JOURNALS');
x_return_status := 'E';
l_access_set_id := fnd_profile.value('GL_ACCESS_SET_ID');
FOR c_je IN (SELECT jh.je_header_id
,jh.rowid AS row_id
,jh.name AS journal_name
,b.name AS batch_name
FROM gl.gl_je_headers jh, gl_je_batches b
WHERE 1 = 1
AND jh.je_batch_id = b.je_batch_id
--
AND jh.ledger_id = G_LEDGER_ID
AND jh.je_source = G_JE_SOURCE_NAME
AND jh.je_category = G_JE_CATEGORY_NAME
AND jh.period_name = P_PERIOD_NAME
AND (b.name LIKE g_bath_name_in || '%' OR
b.name LIKE g_bath_name_out || '%')
AND (jh.name LIKE g_jh_name_in || '%' OR
jh.name LIKE g_jh_name_out || '%')
AND ( --Неотсторнированные журналы
(jh.accrual_rev_status IS NULL AND
-- сторно на сторно на сторно ...
jh.reversed_je_header_id IS NULL) OR
(jh.accrual_rev_status IS NULL AND
jh.reversed_je_header_id IS NOT NULL AND
MOD((SELECT COUNT(*)
FROM gl_je_headers jh0
START WITH jh0.je_header_id = jh.je_header_id
CONNECT BY jh0.je_header_id = PRIOR
jh0.reversed_je_header_id)
,2) != 0))
ORDER BY jh.je_header_id)
LOOP
write_log(' Сторнирование журнала "' || c_je.journal_name ||
'" из пакета "' || c_je.batch_name || '"');
UPDATE gl_je_headers
SET accrual_rev_flag = 'Y'
,accrual_rev_period_name = P_PERIOD_NAME
,accrual_rev_change_sign_flag = 'Y'
,accrual_rev_effective_date = SYSDATE
WHERE ROWID = c_je.row_id;
--
l_request_ids(l_request_ids.COUNT + 1) :=
fnd_request.submit_request(application => 'SQLGL'
,program => 'GLPREV'
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => l_access_set_id
,argument2 => c_je.je_header_id);
COMMIT;
IF l_request_ids(l_request_ids.COUNT) = 0 THEN
write_log('Ошибка при отправке параллельного запроса Сторнирование журналов.');
l_request_ids.DELETE(l_request_ids.COUNT);
l_result := FALSE;
--exit;
END IF;
END LOOP;
--
WHILE l_request_ids.COUNT > 0
LOOP
-- описание функции http://apps-oracle.ru/fnd_request_exec/#WAIT_CONCURRENT
if NOT (wait_concur(l_request_ids(l_request_ids.FIRST),x_mess)) then
write_log(x_mess);
l_result := FALSE;
exit;
end if;
l_request_ids.DELETE(l_request_ids.FIRST);
END LOOP;
write_log('END REVERSE_JOURNALS' || chr(10));
--
IF l_result THEN
x_return_status := 'S';
x_mess := 'Сторнирование журналов завершено успешно.';
RETURN;
ELSE
x_return_status := 'E';
x_mess := 'Сторнирование журналов завершено с ошибками. '||x_mess;
RETURN;
END IF;
--
END reverse_journals;
Пример вызова параллельной программы для импорта журналов
-- -----------------------------------------------------
-- Импорт журналов
-- -----------------------------------------------------
PROCEDURE import_journals(x_return_status OUT VARCHAR2
,x_mess OUT VARCHAR2
)
IS
l_flag_exist NUMBER;
l_request_id NUMBER;
BEGIN
x_return_status := 'E';
write_log('START IMPORT_JOURNALS');
-- проверка, что есть вставленные строки
SELECT COUNT(1) INTO l_flag_exist FROM gl_interface t
WHERE group_id = fnd_global.conc_request_id;
IF (l_flag_exist != 0) THEN
write_log('Запуск программы "Программа - импорт журналов"');
l_request_id := apps.fnd_request.submit_request(
'SQLGL'
,'GLLEZLSRS'
,NULL
,SYSDATE
,FALSE
,fnd_profile.value('GL_ACCESS_SET_ID') --Data Access Set ID
,G_JE_SOURCE_NAME --Source
,G_LEDGER_ID --Ledger
,fnd_global.conc_request_id --Group ID
,'N' --Post Errors to Suspense
,'N' --Create Summary Journals
,'W' --Import Descriptive Flexfields
);
COMMIT;
IF l_request_id = 0 THEN
x_mess := 'При отправке запроса произошла ошибка. Номер запроса:' || l_request_id;
write_log(x_mess);
RETURN;
END IF;
-- описание функции http://apps-oracle.ru/fnd_request_exec/#WAIT_CONCURRENT
if NOT (wait_concur(l_request_id,x_mess)) then
write_log(x_mess);
RETURN;
end if;
END IF;
write_log('END IMPORT_JOURNALS' || chr(10));
x_return_status := 'S';
END;
OEBS API Запуск параллельной программы
declare
l_request_id NUMBER;
--
p_app_name VARCHAR2(100);
p_request_name VARCHAR2(100);
begin
l_request_id:=apps.fnd_request.submit_request
(application => p_app_name -- код приложения
,program => p_request_name -- код параллельной программы
,description => NULL -- описание
,start_time => NULL -- когда запустить программу
,sub_request => FALSE -- признак дочерней программы
,argument1 => 1 -- параметр параллельной программы
-- ...
--,argument100 =>
);
COMMIT;
if l_request_id = 0 then
--write_log('При отправке запроса произошла ошибка');
raise fnd_api.g_exc_error;
end if;
end;
Если,
start_time = NULL, то запуск происходит сразу.
start_time = fnd_date.date_to_canonical(sysdate + (1/26/60)*5) — запуск через 5 минут
Читать дальше про “OEBS API Параллельные программы — отправка, ожидание, скрытый режим” »
Для добавления функций в избранное по полномочиям — API:FND_SIGNON.UPDATE_NAVIGATOR
Читать дальше про “OEBS Меню полномочий — Избранное (Top Ten List)” »
Пример использования API для обновления корреспондентского счёта банка. (корр. счёт)
declare
p_brunch_number varchar2(30) := '!NUM';
p_ksnp varchar2(40) := '!CORR_ACCOUNT';
--
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec hz_contact_point_v2pub.edi_rec_type;
l_obj_vers NUMBER;
l_party_id number;
--
x_return_status varchar2(1);
x_msg_count number;
x_msg_data varchar2(4000);
begin
select party_id into l_party_id
from ar.hz_organization_profiles hop
where 1=1
and sysdate between hop.effective_start_date and nvl(hop.effective_end_date, sysdate+1)
and hop.bank_or_branch_number = p_brunch_number;
dbms_output.put_line('l_party_id='||l_party_id);
SELECT contact_point_id, object_version_number
into l_contact_point_rec.contact_point_id, l_obj_vers
FROM hz_contact_points
WHERE contact_point_type = 'EDI'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_party_id;
dbms_output.put_line('l_obj_vers='||l_obj_vers);
dbms_output.put_line('contact_point_id='||l_contact_point_rec.contact_point_id);
l_edi_rec.edi_ece_tp_location_code := p_ksnp;
hz_bank_pub.update_edi_contact_point (fnd_api.g_true,
l_contact_point_rec,
l_edi_rec,
l_obj_vers,
x_return_status,
x_msg_count,
x_msg_data
);
dbms_output.put_line('--');
dbms_output.put_line('Return status = '||x_return_status);
if x_return_status != fnd_api.g_ret_sts_success then
if x_msg_count = 1 then
dbms_output.put_line ('Error:' || x_msg_data);
else
FOR i IN 1 .. x_msg_count LOOP
dbms_output.put_line('Error:' || fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false) );
END LOOP;
end if;
end if;
-- commit;
exception
when others then
dbms_output.put_line('SQLERRM:'||SQLERRM||chr(10));
dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||chr(10)||dbms_utility.format_error_backtrace);
raise;
end;
Пример использования API для обновления БИК банка.
declare
p_brunch_number varchar2(30) := '!NUM';
p_bic varchar2(9) := '!NEW_BIC';
--
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_eft_rec hz_contact_point_v2pub.eft_rec_type;
l_eft_vers number;
l_party_id number;
--
x_return_status varchar2(1);
x_msg_count number;
x_msg_data varchar2(4000);
begin
select party_id into l_party_id
from ar.hz_organization_profiles hop
where 1=1
and sysdate between hop.effective_start_date and nvl(hop.effective_end_date, sysdate+1)
and hop.bank_or_branch_number = p_brunch_number;
dbms_output.put_line('l_party_id='||l_party_id);
SELECT contact_point_id, object_version_number
into l_contact_point_rec.contact_point_id, l_eft_vers
FROM hz_contact_points
WHERE contact_point_type = 'EFT'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_party_id;
dbms_output.put_line('l_eft_vers='||l_eft_vers);
dbms_output.put_line('contact_point_id='||l_contact_point_rec.contact_point_id);
l_eft_rec.eft_swift_code := p_bic;
hz_bank_pub.update_eft_contact_point ( fnd_api.g_true,
l_contact_point_rec,
l_eft_rec,
l_eft_vers,
x_return_status,
x_msg_count,
x_msg_data
);
dbms_output.put_line('--');
dbms_output.put_line('Return status = '||x_return_status);
if x_return_status != fnd_api.g_ret_sts_success then
if x_msg_count = 1 then
dbms_output.put_line ('Error:' || x_msg_data);
else
FOR i IN 1 .. x_msg_count LOOP
dbms_output.put_line('Error:'
||fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false));
END LOOP;
end if;
end if;
-- commit;
exception
when others then
dbms_output.put_line('SQLERRM:'||SQLERRM||chr(10));
dbms_output.put_line('FORMAT_ERROR_BACKTRACE:'||chr(10)
||dbms_utility.format_error_backtrace);
raise;
end;
Последние комментарии