Скрипт обновления альтернативного имени отделения поставщика.
В данном примере обнуляем альтернативное наименование.
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_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;
Запрос для определения типа расчетного счета:
(поставщик или заказчик)
SELECT account.bank_account_num
,account.ext_bank_account_id -- Link to Bank and Branch Information
,acc_instr.instrument_id
,case when ext_payer.ext_payer_id is not null then 'CUSTOMER'
when ext_iep.ext_payee_id is not null then 'SUPPLIER'
end type_account
,case when ext_payer.ext_payer_id is not null then
(select ac.CUSTOMER_ID || ' - ' || ac.CUSTOMER_NAME
from ar_customers ac where ac.CUSTOMER_ID = ext_payer.cust_account_id)
when ext_iep.ext_payee_id is not null then
(select ap.vendor_id || ' - ' || ap.vendor_name
from ap_suppliers ap where ap.party_id = ext_iep.payee_party_id)
end owner_account_name
FROM iby_ext_bank_accounts account
,iby_pmt_instr_uses_all acc_instr
,iby_external_payers_all ext_payer
,iby_external_payees_all ext_iep
WHERE 1 = 1
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id(+)
AND acc_instr.ext_pmt_party_id = ext_iep.ext_payee_id(+)
-- AND account.bank_account_num in ('','')
Для получения списка расчетных счетов поставщиков используем следующий запрос
SELECT party_supp.party_name supplier_name
,aps.segment1 supplier_number
,ass.vendor_site_code supplier_site
,ieb.bank_account_num bank_account_num
,ieb.bank_account_name bank_account_name
,party_bank.party_name bank_name
,branch_prof.bank_or_branch_number bank_number
,party_branch.party_name branch_name
,branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
,ap_suppliers aps
,hz_party_sites site_supp
,ap_supplier_sites_all ass
,iby_external_payees_all iep
,iby_pmt_instr_uses_all ipi
,iby_ext_bank_accounts ieb
,hz_parties party_bank
,hz_parties party_branch
,hz_organization_profiles bank_prof
,hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.branch_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name, ass.vendor_site_code;
Последние комментарии