Архив

Публикации с меткой ‘ap_suppliers’

OEBS API: Добавляем счет на отделение поставщика

Скрипт добавления(назначения) счета на отделение заказчика.
В данном примере счета копируются из одного отделения в другое.

DECLARE
  p_org_id_from number := 111; 
  p_org_id_to   number := 112; 
  --
  l_payee_rec      iby_disbursement_setup_pub.payeecontext_rec_type;
  l_pay_instr_rec  iby_fndcpt_setup_pub.pmtinstrument_rec_type;  
  l_pay_assign_rec iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
  --
  x_assign_id           NUMBER;
  x_return_status       VARCHAR2(1);
  x_msg_count           NUMBER;
  x_msg_data            VARCHAR2(4000);
  x_response            apps.iby_fndcpt_common_pub.result_rec_type;  
BEGIN
  FND_MSG_PUB.initialize;
 
  fnd_global.apps_initialize(-1, 20639, 200); --Диспетчер кредиторов - Кредиторы
  
  for i in (
            SELECT sup.vendor_type_lookup_code
                 , sup.vendor_id
                 , ss.party_site_id
                 , ss2.party_site_id  party_site_id_to
                 , ss.vendor_site_id  vendor_site_id_from
                 , ss2.vendor_site_id vendor_site_id_to
                 , sup.party_id
                 , sup.segment1
                 , sup.vendor_name
                 , epa.org_id
                 , ss.vendor_site_code
                 , piu.order_of_preference priority
                 , eba.ext_bank_account_id
                 , eba.bank_id
                 , eba.branch_id
                 , eba.country_code
                 , eba.bank_account_name
                 , eba.bank_account_num
                 , eba.currency_code
                 , eba.foreign_payment_use_flag
                 , eba.payment_factor_flag
              FROM apps.ap_suppliers sup
                 , apps.ap_supplier_sites_all   ss
                 , apps.ap_supplier_sites_all   ss2
                 , apps.iby_external_payees_all epa
                 , apps.iby_pmt_instr_uses_all  piu
                 , apps.iby_ext_bank_accounts   eba
             WHERE sup.vendor_id     = ss.vendor_id
               AND ss.vendor_site_id = epa.supplier_site_id
               and ss2.vendor_id        = sup.vendor_id  
               and ss2.vendor_site_code = ss.vendor_site_code
               and ss2.org_id           = p_org_id_to
               AND epa.ext_payee_id  = piu.ext_pmt_party_id
               AND piu.instrument_id = eba.ext_bank_account_id
               AND epa.org_id    = p_org_id_from
               and not exists (select 1 
                               from apps.iby_external_payees_all epa1
                                  , apps.iby_pmt_instr_uses_all  piu1
                                  , apps.iby_ext_bank_accounts   eba1
                               where 1=1
                                     and epa1.supplier_site_id = ss2.vendor_site_id
                                     and epa1.ext_payee_id     = piu1.ext_pmt_party_id
                                     and piu1.instrument_id    = eba1.ext_bank_account_id
                                   and eba1.ext_bank_account_id = eba.ext_bank_account_id
                              )
                        
           )
  loop
    dbms_output.put_line('sup.vendor_id='||i.vendor_id);
 
    x_response := null;
    --    
    l_payee_rec.Party_Id         := i.party_id; 
    l_payee_rec.Payment_Function := 'PAYABLES_DISB';
    l_payee_rec.Party_Site_id    := i.party_site_id_to;
    l_payee_rec.Supplier_Site_id := i.vendor_site_id_to;
    l_payee_rec.Org_Id           := p_org_id_to;
    l_payee_rec.Org_Type         := 'OPERATING_UNIT';
    
    -- Instrument Record.
    l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
    l_pay_instr_rec.Instrument_Id   := i.ext_bank_account_id;
          
    -- Assignment Record.
    l_pay_assign_rec.Instrument := l_pay_instr_rec;
    l_pay_assign_rec.Priority   := i.priority;
    l_pay_assign_rec.Start_Date := trunc(sysdate);    
    
    iby_disbursement_setup_pub.set_payee_instr_assignment(
        p_api_version        => 1.0,
        p_init_msg_list      => 'T',
        p_commit             => 'F',
        x_return_status      => x_return_status,
        x_msg_count          => x_msg_count,
        x_msg_data           => x_msg_data,
        p_payee              => l_payee_rec,
        p_assignment_attribs => l_pay_assign_rec,
        x_assign_id          => x_assign_id,
        x_response           => x_response);    
      
    dbms_output.put_line('x_return_status='||x_return_status);
    dbms_output.put_line('out_mesg.Result_Code     ='||x_response.Result_Code);
    dbms_output.put_line('out_mesg.Result_Category ='||x_response.Result_Category);
    dbms_output.put_line('out_mesg.Result_Message  ='||x_response.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 j 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 loop;
  
  commit;
end;

Доп. информацию можно прочитать в ноте How to create bank account through API and associate to multiple supplier sites. [ID 1299614.1]

OEBS API: Обновляем альтернативное имя отделения поставщика

4 Декабрь 2012 Нет комментариев

Скрипт обновления альтернативного имени отделения поставщика.
В данном примере обнуляем альтернативное наименование.

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;

OEBS API: Создание поставщика и отделения

19 Ноябрь 2012 Нет комментариев

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 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;