APPS-ORACLE.RU

Пример скрипта для создания расчетного счета на отделении поставщика:

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;

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

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

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 ac.CUSTOMER_id
   , cust.party_name customer_name
   , cust_acct.account_number
   , cust_acct.cust_account_id
   , cust_uses.site_use_code
   , cust_loc.address1
   , cust_loc.address2
   , cust_loc.address3
   , cust_loc.address4
   , cust_loc.city
   , cust_loc.postal_code
   , bank.party_name                   bank_name
   , bank_prof.home_country
   , branch.party_name                 branch_name
   , branch_prof.bank_or_branch_number branch_number
   , account.bank_account_num
   , account.bank_account_name
   , acc_instr.order_of_preference
   , cust_uses.primary_flag
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_external_payers_all  ext_payer
   , iby_pmt_instr_uses_all   acc_instr
   , hz_parties               cust
   , hz_cust_accounts         cust_acct
   , hz_cust_acct_sites_all   cust_site
   , hz_cust_site_uses_all    cust_uses
   , hz_party_sites           party_site
   , hz_locations             cust_loc
   , ar_customers             ac
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND cust_acct.cust_account_id        = cust_site.cust_account_id
AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id
and cust_uses.status                 = 'A'
AND party_site.party_id              = cust.party_id
AND party_site.party_site_id         = cust_site.party_site_id
AND party_site.location_id           = cust_loc.location_id
AND cust.party_id                    = cust_acct.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id       = ext_payer.ext_payer_id
AND ext_payer.cust_account_id        = cust_acct.cust_account_id
AND cust_uses.site_use_id            = ext_payer.acct_site_use_id
and ext_payer.cust_account_id        = ac.CUSTOMER_id

Для получения списка расчетных счетов поставщиков используем следующий запрос

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;