APPS-ORACLE.RU

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

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]

Описание нескольких таблиц из схемы IBY (Payments):

IBY.IBY_EXTERNAL_PAYEES_ALL
Платежные атрибуты поставщика (получателя)

IBY.IBY_EXTERNAL_PAYERS_ALL
Платежные атрибуты заказчика (плательщика)

IBY.IBY_ACCOUNT_OWNERS
Владельцы банковского счета

IBY.IBY_PMT_INSTR_USES_ALL
Назначения платежных средств.
Содержит связь между платежными средствами (payment instruments) и плательщиком/получателем (Payer/Payee). Платежным средством может быть кредитная карта или банковсвкий счет.

IBY.IBY_EXT_BANK_ACCOUNTS
Внешние банковские счета

Запрос для определения типа расчетного счета:

(поставщик или заказчик)

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 ('','')