Receiving Interface Example

CREATE OR REPLACE PROCEDURE APPS.XX_RCV_INT
IS
   l_vendor_id   NUMBER        := NULL;
   l_uom         VARCHAR2 (20) := NULL;
   l_currency    VARCHAR2(20)  := NULL;

   CURSOR c1
   IS
      SELECT *
        FROM xxfz.xx_receiving_f
       WHERE receipt_number = 1218;--posted_flag = 'Y';

   CURSOR c2 (recpt_num IN NUMBER)
   IS
      SELECT *
        FROM xxfz.xx_rcv_lots_f
       WHERE receipt_number = recpt_num;
BEGIN
   FOR hdr IN c1
   LOOP
--
--GETTING VENDOR FROM PO
--
      SELECT vendor_id
        INTO l_vendor_id
        FROM po_headers_all
       WHERE po_header_id = hdr.po_header_id;

--
--GETTING UNIT OF MEASURE
--
      SELECT primary_unit_of_measure
        INTO l_uom
        FROM mtl_system_items_b
       WHERE inventory_item_id = hdr.item_id AND organization_id = 102;

--
--GETTING CURRENCY CODE
--
      SELECT currency_code
        INTO l_currency
        FROM po_headers_all
       WHERE po_header_id = hdr.po_header_id;
     
   
   
   

      INSERT INTO rcv_headers_interface
                  (header_interface_id,
                   GROUP_ID, processing_status_code, receipt_source_code,
                   transaction_type, last_update_date, last_updated_by,
                   last_update_login, vendor_id, expected_receipt_date,
                   validation_flag, receipt_num
                  )
           VALUES (rcv_headers_interface_s.NEXTVAL,
                   rcv_interface_groups_s.NEXTVAL, 'PENDING', 'VENDOR',
                   'NEW', SYSDATE, -1,
                   0, l_vendor_id, SYSDATE,
                   'Y', hdr.receipt_number
                  );

      FOR lines IN c2 (hdr.receipt_number)
      LOOP
         INSERT INTO rcv_transactions_interface
                     (interface_transaction_id,
                      GROUP_ID, last_update_date, last_updated_by,
                      creation_date, created_by, last_update_login,
                      transaction_type, transaction_date,
                      processing_status_code, processing_mode_code,
                      transaction_status_code, item_id, quantity,
                      unit_of_measure, auto_transact_code,
                      receipt_source_code, to_organization_code,
                      source_document_code, po_header_id,     -- document_num,
                      header_interface_id, validation_flag, currency_code
                     )
              VALUES (rcv_transactions_interface_s.NEXTVAL,
                      rcv_interface_groups_s.CURRVAL, SYSDATE, -1,
                      SYSDATE, -1, 0,
                      'RECEIVE', SYSDATE,
                      'PENDING', 'BATCH',
                      'PENDING', hdr.item_id, lines.lot_number,
                      l_uom, 'RECEIVE',
                      'VENDOR', 'FGN',
                      'PO', hdr.po_header_id,                           --740,
                      rcv_headers_interface_s.CURRVAL, 'Y', l_currency
                     );
      END LOOP;
   END LOOP;
end;
/

Query to pull Supplier Bank Account Information in R12

Query to pull active Vendors along with their associated Bank Accounts (External Bank Accounts).
Generally when vendor table is joined to iby_account_owners it returns 2 or more rows and the information does not match with the Accounts we are able to query from Supplier front end. After spending some time I came to know the issues related to this table. See below how I resolved it.

Also refer to metalink Note: 1342935.1 for more information.


  SELECT       ss.vendor_site_id,
               s.vendor_id,
               s.last_update_date,
               s.last_updated_by,
               s.vendor_name,
               s.vendor_name_alt,
               segment1                        vendor_num,
               s.creation_date,
               s.employee_id,
               s.vendor_type_lookup_code,
               s.parent_vendor_id,
               s.num_1099,
               s.type_1099,
               s.start_date_active,
               s.end_date_active,
               s.minority_group_lookup_code,
               s.payment_method_lookup_code,
               s.receipt_required_flag,
               s.enforce_ship_to_location_code,
               s.tax_verification_date,
               s.federal_reportable_flag,
               s.attribute_category,
               s.attribute3,
               s.attribute5,
               s.attribute6,
               s.attribute12,
               s.attribute13,
               s.attribute15,
               s.tax_reporting_name,
               null "site infor stars here",
               ss.vendor_site_id,
               s.tax_reporting_name,
               ss.vendor_site_id,
               ss.last_update_date,
               ss.last_updated_by,
               ss.vendor_site_code,
               ss.vendor_site_code_alt,
               ss.creation_date,
               ss.address_line1,
               ss.address_lines_alt,
               ss.address_line2,
               ss.address_line3,
               ss.address_line4,
               ss.city,
               ss.state,
               ss.zip,
               ss.area_code,
               ss.phone,
               ss.inactive_date,
               ss.payment_method_lookup_code,
               ss.attribute3,
               ss.attribute5,
               ss.attribute6,
               ss.attribute12,
               ss.attribute13,
               ss.attribute15,
               ss.email_address,
               ss.remittance_email,
               --
               null "Bank infor starts here",
               bank.bank_name,
               ext_acct.bank_account_name,
               ext_acct.bank_account_number,
               ext_acct.bank_account_type
               --
          FROM apps.ap_supplier_sites_all   ss,
               apps.ap_suppliers            s,
               apps.iby_account_owners      owner,
               apps.iby_ext_bank_accounts_v ext_acct,
               apps.iby_ext_banks_v    bank,
               apps.iby_ext_bank_branches_v branch
         WHERE s.vendor_id = ss.vendor_id
              --AND ss.pay_site_flag = 'Y'
           AND nvl(ss.inactive_date, SYSDATE) >= SYSDATE
           AND nvl(s.start_date_active, SYSDATE) <= SYSDATE
           AND nvl(s.end_date_active, SYSDATE) >= SYSDATE
           AND nvl(s.enabled_flag, 'Y') = 'Y'
           AND owner.account_owner_party_id(+) = s.party_id
           AND ext_acct.ext_bank_account_id(+) = owner.ext_bank_account_id
           AND bank.bank_party_id(+) = ext_acct.bank_party_id
           AND SYSDATE BETWEEN nvl(ext_acct.start_date, SYSDATE) AND
               nvl(ext_acct.end_date, SYSDATE)
              /*           AND (s.vendor_type_lookup_code <> 'EMPLOYEE' OR
                             s.vendor_type_lookup_code IS NULL)
              */
           AND branch.branch_party_id(+) = ext_acct.branch_party_id
           AND EXISTS
         (SELECT 'ASSIGNMENT'
                  FROM apps.iby_pmt_instr_uses_all piu
                 WHERE (instrument_type = 'BANKACCOUNT' AND
                       piu.instrument_id = ext_acct.ext_bank_account_id)
                    OR ext_acct.ext_bank_account_id IS NULL)
        --AND owner.primary_flag = 'Y'
         ORDER BY s.vendor_name;



If one is interested in retrieving Vendors associated with some bank account only an alternate approach is to simply join iby_ext_bank_accounts table with iby_pmt_instr_uses_all as shown below instead of using EXISTS clause.

piu.instrument_type = 'BANKACCOUNT' AND
piu.instrument_id = ext_acct.ext_bank_account_id
         

Access control to a function/report using RBAC in r12

Giving access of a report to a particular user using RBAC

Assign Transfer Journal Entries to GL to a specific user

Author: Faraz Anwar

Dated: 03/07/2014

Step 1) Create Function

System Administrator > Application > Function

Function Code	XXGL_APGL_TRXFR
User Function Name	XX Transfer Journal Entries to GL
Type	Form
Form	Run Reports
Application	Application Object Library
Parameters	REQUEST_GROUP_CODE="XXGL_APGL_TRXFR" REQUEST_GROUP_APPL_SHORT_NAME="SQLAP" TITLE="AP:TRANSFER_TO_GL" USE_ORG="N"

Step 2: Add a Function to Menu

System Administrator > Application > Menu

Query Menu AP_NAVIGATE_GUI12

Add a menu item “XX Transfer Journal Entries to GL” at the end of the menu as shown in the figure.

Sequence	180
Prompt	Submit AP to GL Transfer
Submenu
Function	XX Transfer Journal Entries to GL
Grant	“Uncheck” Grant

3) Create a Permission Set

System Administrator > Application > Menu

Menu	XXGL_APGL_TRXFR
User Menu Name	XX Transfer Journal Entries to GL
Menu Type	Permission Set

Seq	Prompt	Submenu	Function
1			XX Transfer Journal Entries to GL

Step 4) Create a new Request Group

System Administrator > Security > Responsibility > Request

Group	XXGL_APGL_TRXFR
Application	Payables
Code	XXGL_APGL_TRXFR

In Requests Section Add:

Type	Name	Application
Program	Transfer Journal Entries to GL	Subledger Accounting

Step 5: Create Grant

Functional Administrator > Secuirty:Grants (Tab) > Create Grant (Button)

Name	XX Transfer Journal Entries to GL
Description
Grantee Type	Specific User
Grantee	XX
Responsibility	XX AP Inquiry

Set	XX Transfer Journal Entries to GL

Click Finish

R12 Bank Conversion/Data Loading using Oracle standard API

create table xx_bank_conversion_tmp
(
row_id number,
gl_org_segment varchar2(3),
Bank_Name varchar2(300),
--Bank_country_code varchar2(10),
Branch_name varchar2(300),
--Branch_type varchar2(15),
Branch_routing_num varchar2(30),
Bank_account_name varchar2(300),
bank_Account_num varchar2(30),
--currency varchar2(3),
--multi_currency_allowed_flag varchar2(1),
--ap_use_allowed_flag varchar2(1),
--ar_use_allowed_flag varchar2(1),
process_flag varchar2(1)
); 



CREATE OR REPLACE PROCEDURE xx_bank_conversion IS
  g_init_msg_list VARCHAR2(300) := fnd_api.g_true; -- IN
  g_bank_id       NUMBER;
  g_branch_id     NUMBER;
  g_acct_id       NUMBER;
  g_log_level     integer;
  
  CURSOR c1 is select * from xx_bank_conversion_tmp where process_flag = 'Y';
BEGIN
  
/*Author: Faraz Anwar*/
/*Dated: 03/25/2014 */


/*FND: Debug Log Enabled
    Set this profile to Yes

FND: Debug Log Module
    Set this to %

FND: Debug Log Level
    Set this to Statement

 */

 /*
   LEVEL_UNEXPECTED     : Internal Level Id is 6
   LEVEL_ERROR          : Internal Level Id is 5
   LEVE_EXCEPTION      : Internal Level Id is 4
   LEVEL_EVENT          : Internal Level Id is 3
   LEVEL_PROCEDURE      : Internal Level Id is 2
   LEVEL_STATEMENT      : Internal Level Id is 1
 */
   
   
 DECLARE
    l_resp_id             NUMBER;
    l_user_id             NUMBER;
  BEGIN
  
    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name LIKE 'FANWAR';
  
    SELECT responsibility_id
      INTO l_resp_id
      FROM fnd_responsibility_vl
     WHERE responsibility_name LIKE 'AR Inquiry Reports';
  
    fnd_global.apps_initialize(l_user_id, l_resp_id, 222); --AR=222
  
    mo_global.init('AR');
    
    FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT, 'apps.plsql.xx_bank_conversion');

    FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'l_user_id '||l_user_id);
    FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'l_resp_id '||l_resp_id);
    FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'G_CURRENT_RUNTIME_LEVEL '||FND_LOG.G_CURRENT_RUNTIME_LEVEL);
    
  END INIT;


FOR r1 in c1 LOOP
  
  g_bank_id       := null;
  g_branch_id     := null;
  g_acct_id       := null;
  --
  -- Creating Bank
  --

  DECLARE
    l_country_code            VARCHAR2(300); -- IN
    l_bank_name               VARCHAR2(300); -- IN
    l_bank_number             VARCHAR2(300); -- IN
    l_alternate_bank_name     VARCHAR2(300); -- IN
    l_short_bank_name         VARCHAR2(300); -- IN
    l_description             VARCHAR2(300); -- IN
    l_tax_payer_id            VARCHAR2(300); -- IN
    l_tax_registration_number VARCHAR2(300); -- IN
    l_bank_id                 NUMBER; -- OUT
    l_acct_id                 NUMBER;
    l_return_status           VARCHAR2(300); -- OUT
    l_msg_count               NUMBER; -- OUT
    l_msg_data                VARCHAR2(300); -- OUT
  
  BEGIN
  
    l_country_code := 'US';
    l_bank_name    := r1.bank_name;
  
    DECLARE
      l_end_date DATE;
    BEGIN
      ce_bank_pub.check_bank_exist(p_country_code => l_country_code,
                                   p_bank_name    => l_bank_name,
                                   p_bank_number  => l_bank_number,
                                   x_bank_id      => l_bank_id,
                                   x_end_date     => l_end_date);
    END;
   
  
    IF l_bank_id is not null then 
         FND_LOG.STRING(fnd_log.level_exception,'apps.plsql.xx_bank_conversion.create_bank', 'Bank Exists l_bank_name ='||l_bank_name); 
         FND_LOG.STRING(FND_LOG.level_exception,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_id '||l_bank_id);
    END IF;
      
    IF l_bank_id IS NULL THEN
      ce_bank_pub.create_bank(p_init_msg_list           => g_init_msg_list, -- IN
                              p_country_code            => l_country_code, -- IN
                              p_bank_name               => l_bank_name, -- IN
                              p_bank_number             => l_bank_number, -- IN
                              p_alternate_bank_name     => l_alternate_bank_name, -- IN
                              p_short_bank_name         => l_short_bank_name, -- IN
                              p_description             => l_description, -- IN
                              p_tax_payer_id            => l_tax_payer_id, -- IN
                              p_tax_registration_number => l_tax_registration_number, -- IN
                              x_bank_id                 => l_bank_id, -- OUT
                              x_return_status           => l_return_status, -- OUT
                              x_msg_count               => l_msg_count, -- OUT
                              x_msg_data                => l_msg_data -- OUT
                              );
    
    
      IF l_return_status = 'S' then 
            g_log_level  := FND_LOG.LEVEL_EVENT;
      ELSE 
            g_log_level := FND_LOG.LEVEL_EXCEPTION;
      END IF;
      
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_id '||L_BANK_ID);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_name '||l_bank_name);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_return_status '||l_return_status);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_msg_count '||L_MSG_COUNT);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_msg_data '||L_MSG_DATA);
    END IF;
      

  
    g_bank_id := l_bank_id;
    
    END bank;
  
    --
    --  Creating Branch
    --
  
    DECLARE
      l_branch_name           VARCHAR2(300); -- IN
      l_branch_number         VARCHAR2(300); -- IN
      l_branch_type           VARCHAR2(300); -- IN
      l_alternate_branch_name VARCHAR2(300); -- IN
      l_description           VARCHAR2(300); -- IN
      l_bic                   VARCHAR2(300); -- IN
      l_eft_number            VARCHAR2(300); -- IN
      l_rfc_identifier        VARCHAR2(300); -- IN
      l_branch_id             NUMBER; -- OUT
      l_return_status         VARCHAR2(300); -- OUT
      l_msg_count             NUMBER; -- OUT
      l_msg_data              VARCHAR2(300); -- OUT
    BEGIN
      l_branch_name := r1.branch_name;
      l_branch_number  := r1.branch_routing_num;
      l_branch_type := 'ABA';
    
      DECLARE
        l_end_date DATE;
      BEGIN
      
        ce_bank_pub.check_branch_exist(p_bank_id       => g_bank_id,
                                       p_branch_name   => l_branch_name,
                                       p_branch_number => l_branch_number,
                                       x_branch_id     => l_branch_id,
                                       x_end_date      => l_end_date);
      END;
    
      IF l_branch_id is not null then 
         FND_LOG.STRING(fnd_log.level_exception,'apps.plsql.xx_bank_conversion.create_bank_branch', 'Branch Already Exists l_branch_name ='||l_branch_name); 
         FND_LOG.STRING(FND_LOG.level_exception,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_id '||L_BRANCH_ID);
      END IF;
      
      IF l_branch_id IS NULL THEN
      
        ce_bank_pub.create_bank_branch(p_init_msg_list         => g_init_msg_list, -- IN
                                       p_bank_id               => g_bank_id, -- IN
                                       p_branch_name           => l_branch_name, -- IN
                                       p_branch_number         => l_branch_number, -- IN
                                       p_branch_type           => l_branch_type, -- IN
                                       p_alternate_branch_name => l_alternate_branch_name, -- IN
                                       p_description           => l_description, -- IN
                                       p_bic                   => l_bic, -- IN
                                       p_eft_number            => l_eft_number, -- IN
                                       p_rfc_identifier        => l_rfc_identifier, -- IN
                                       x_branch_id             => l_branch_id, -- OUT
                                       x_return_status         => l_return_status, -- OUT
                                       x_msg_count             => l_msg_count, -- OUT
                                       x_msg_data              => l_msg_data -- OUT
                                       );
                                       
      IF l_return_status = 'S' then 
          g_log_level  := FND_LOG.LEVEL_EVENT;
      ELSE 
          g_log_level := FND_LOG.LEVEL_EXCEPTION;
      END IF;
      
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_id '||L_BRANCH_ID);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_name '||l_branch_name);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_msg_count '||L_MSG_COUNT);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_msg_data '||L_MSG_DATA);
      FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_return_status '||l_return_status);
      
      END IF;  
      
      g_branch_id := l_branch_id;
    
      --
      -- Creating Bank Account
      --
    END branch;
    
      DECLARE
        l_acct_rec apps.ce_bank_pub.bankacct_rec_type;
        l_return_status VARCHAR2(300); -- OUT
        l_msg_count     NUMBER; -- OUT
        l_msg_data      VARCHAR2(300); -- OUT
        l_acct_id       number;
      
      BEGIN
      
        FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.create_bank_acct', 'Calling create_bank_acct');
      
        l_acct_rec.bank_id                     := g_bank_id;
        l_acct_rec.branch_id                   := g_branch_id;
        l_acct_rec.account_owner_org_id        := 102; --OU
        l_acct_rec.account_owner_party_id      := 1460411; --OU party id, xle_entity_profiles
        l_acct_rec.account_classification      := 'INTERNAL';
        l_acct_rec.bank_account_name           := r1.bank_account_name;                      --'';
        l_acct_rec.bank_account_num            := r1.bank_account_num;                       --'222222222';
        l_acct_rec.currency                    := 'USD';
        l_acct_rec.multi_currency_allowed_flag := 'N';
        l_acct_rec.ap_use_allowed_flag         := 'Y';
        l_acct_rec.ar_use_allowed_flag         := 'Y';
        l_acct_rec.start_date                  := SYSDATE;

        l_acct_rec.asset_code_combination_id   := null;
        l_acct_rec.masked_account_num          := r1.bank_account_num;

        l_acct_rec.zero_amount_allowed         := 'N';
        l_acct_rec.multi_currency_allowed_flag := 'N';
        l_acct_rec.pooled_flag                 := 'N';
        
        l_acct_rec.ap_amount_tolerance         := 0;
        l_acct_rec.ar_amount_tolerance         := 0;
        l_acct_rec.xtr_amount_tolerance        := 0;
        l_acct_rec.pay_amount_tolerance        := 0;
        l_acct_rec.ce_amount_tolerance     := 0;
        
        l_acct_rec.ap_percent_tolerance        := 0;
        l_acct_rec.ar_percent_tolerance        := 0;
        l_acct_rec.xtr_percent_tolerance       := 0;
        l_acct_rec.pay_percent_tolerance       := 0;

        l_acct_rec.ce_percent_tolerance       := 0;

        BEGIN
          SELECT code_combination_id
            INTO l_acct_rec.asset_code_combination_id
            FROM gl_code_combinations_kfv
           WHERE concatenated_segments =
                 '865-00-1111-00-000-X-00-0-00-00000';
        EXCEPTION
          WHEN no_data_found THEN
            NULL;

        END;
      
        ce_bank_pub.create_bank_acct(p_init_msg_list => g_init_msg_list,
                                     p_acct_rec      => l_acct_rec,
                                     x_acct_id       => l_acct_id,
                                     x_return_status => l_return_status,
                                     x_msg_count     => l_msg_count,
                                     x_msg_data      => l_msg_data);
      
        
        

        g_acct_id := l_acct_id;
        
        IF l_return_status = 'S' then 
          g_log_level  := FND_LOG.LEVEL_EVENT;
        ELSE 
          g_log_level := FND_LOG.LEVEL_EXCEPTION;
        END IF;
          
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'l_acct_id '||l_acct_id);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'l_account_name '||r1.bank_account_name);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_return_status '||l_return_status);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_msg_count '||L_MSG_COUNT);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_msg_data '||L_MSG_DATA);
        
        if l_return_status = 'E' then
          CONTINUE;
        END IF;
      END bank_acct;
    
      --
      -- Assigning Bank Account Uses
      --
    
      DECLARE
        l_acct_use_rec  apps.ce_bank_pub.bankacct_use_rec_type;
        l_acct_use_id   NUMBER;
        l_return_status VARCHAR2(300); -- OUT
        l_msg_count     NUMBER; -- OUT
        l_msg_data      VARCHAR2(300); -- OUT
      BEGIN
      
        l_acct_use_rec.bank_account_id      := g_acct_id;
        l_acct_use_rec.org_type             := 'OU';
        l_acct_use_rec.org_id               := 102;
        --l_acct_use_rec.legal_entity_id      := 102;
        l_acct_use_rec.ar_use_enable_flag   := 'Y';
        l_acct_use_rec.ap_use_enable_flag   := 'Y';
        --l_acct_use_rec.org_party_id         := 1460411; --xla_entity_profiles
        l_acct_use_rec.authorized_flag      := 'N';
        l_acct_use_rec.default_account_flag := 'N';
      
        BEGIN
          SELECT code_combination_id
            INTO l_acct_use_rec.asset_code_combination_id
            FROM gl_code_combinations_kfv
           WHERE concatenated_segments =
                 '199-00-1111-00-000-X-00-0-00-00000';
        EXCEPTION
          WHEN no_data_found THEN
            NULL;
        END;
      
        l_acct_use_rec.ap_asset_ccid := l_acct_use_rec.asset_code_combination_id;
        l_acct_use_rec.ar_asset_ccid := l_acct_use_rec.asset_code_combination_id;
      
        FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'Calling create_bank_acct_use');
      
        ce_bank_pub.create_bank_acct_use(p_init_msg_list => g_init_msg_list,
                                         p_acct_use_rec  => l_acct_use_rec,
                                         x_acct_use_id   => l_acct_use_id,
                                         x_return_status => l_return_status,
                                         x_msg_count     => l_msg_count,
                                         x_msg_data      => l_msg_data);
      
        
        IF l_return_status = 'S' then 
          g_log_level  := FND_LOG.LEVEL_EVENT;
        ELSE 
          g_log_level := FND_LOG.LEVEL_EXCEPTION;
        END IF;
          
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'l_acct_use_id '||l_acct_use_id);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'l_acct_use_acct_name '||r1.bank_account_name);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_return_status '||l_return_status);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_msg_count '||L_MSG_COUNT);
        FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_msg_data '||L_MSG_DATA);
        
        IF l_return_status = 'E' then 
          CONTINUE; 
        END IF;
        
      END bank_acct_use;
      end loop;
END;


Run following SQL query to verify if any of the lines were not loaded successfully.

SELECT * FROM FND_LOG_MESSAGES WHERE MODULE LIKE 'apps.plsql.xx_bank_conversion%' 
--and module not like 'apps.plsql.xx_bank_conversion.create_bank_branch' 
--and module not like 'apps.plsql.xx_bank_conversion.create_bank' 
and log_level = 4 order by log_sequence asc;