Bank and Bank Branch Conversion

Sample Data File
------------------
ICICI BANK|ICICI BEGUMPET|US|03-SEP-2013|
ICICI BANK|ICICI KPHB|USN|03-SEP-2013|
SBI BANK|SBI BEGUMPET|US|03-SEP-2013|
SBI BANK|SBI KPHB|USX|03-SEP-2013|
SBH BANK|ICICI BEGUMPET|US|03-SEP-2013|
ICICI BANK|ICICI SRNAGAR|US|03-SEP-2013|

Control File
------------------------
LOAD DATA
INFILE "&1"
APPEND INTO TABLE XXI_BANK_BRANCH_STG_SK
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(BANK_NAME,
BANK_BRANCH_NAME ,
COUNTRY,
START_DATE,
CREATED_BY    CONSTANT"-1",
CREATION_DATE    "SYSDATE",
LAST_UPDATED_BY    CONSTANT "-1",
LAST_UPDATE_DATE    "SYSDATE",
PROCESS_FLAG    CONSTANT"1")

Package Code
------------------------

CREATE OR REPLACE PACKAGE I_CE_BANK_BRANCH_PKG_SK IS
PROCEDURE MESSAGE(P_MESSAGE IN VARCHAR2);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY I_CE_BANK_BRANCH_PKG_SK IS

PROCEDURE MESSAGE(P_MESSAGE IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,P_MESSAGE);
END;

PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
               RETCODE OUT NUMBER) IS
              
ln_bank_id              NUMBER;
lv_return_status        VARCHAR2(1);
ln_msg_count            NUMBER;
lv_msg_data             VARCHAR2(4000);
lr_response             IBY_FNDCPT_COMMON_PUB.Result_rec_type;  
lr_ext_bank_rec         IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
lr_ext_bank_branch_rec  IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
ln_branch_id            NUMBER;
              
BEGIN

        FOR rec_bank IN (SELECT DISTINCT bank_name,country
                           FROM XXI_BANK_BRANCH_STG_SK
                          WHERE process_flag =1)
        LOOP
       
       
        lr_ext_bank_rec.bank_name := rec_bank.bank_name;
        lr_ext_bank_rec.institution_type := 'BANK';
        lr_ext_bank_rec.country_code := rec_bank.country;
        lr_ext_bank_rec.object_version_number := '1.0';
       
       
         IBY_EXT_BANKACCT_PUB.create_ext_bank (
                    '1.0',
                    FND_API.G_TRUE,
                    lr_ext_bank_rec,
                    ln_bank_id  ,
                    lv_return_status ,
                    ln_msg_count,
                    lv_msg_data ,
                    lr_response ); 
           MESSAGE('                       ');
           MESSAGE('lv_return_status bank:-'||lv_return_status);  
            
           IF lv_return_status = 'S' THEN
              COMMIT;
              MESSAGE('Bank id:-'||ln_bank_id);

            FOR rec_branch IN (SELECT bank_branch_name,
                                      country,
                                      start_date
                                 FROM XXI_BANK_BRANCH_STG_SK
                                WHERE process_flag =1
                                  AND bank_name = rec_bank.bank_name)
              LOOP
            
               
                lv_return_status := NULL;
                ln_msg_count     := NULL;
                lv_msg_data      := NULL;
                lr_response      := NULL;
               
               
                lr_ext_bank_branch_rec.bank_party_id := ln_bank_id;
                lr_ext_bank_branch_rec.branch_name   := rec_branch.bank_branch_name;
                lr_ext_bank_branch_rec.branch_type   := 'SWIFT';

               
                IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
                '1.0',
                 FND_API.G_TRUE,
                 lr_ext_bank_branch_rec,
                 ln_branch_id,
                 lv_return_status,
                 ln_msg_count,
                 lv_msg_data,
                 lr_response
                    );
                   
                   
                MESSAGE('lv_return_status bank branch:-'||lv_return_status);  
            
               IF lv_return_status = 'S' THEN
                    COMMIT;
                      MESSAGE('Bank Branch id:-'||ln_branch_id);
             
                        ELSE
          
                            MESSAGE('ln_msg_count branch:-'||ln_msg_count);
                            MESSAGE('lv_msg_data  branch:-'||lv_msg_data);          
                                 
                        END IF;
        
               END LOOP;      
            
           ELSE
              MESSAGE('Error Message Count For Bank:-'||ln_msg_count);
              MESSAGE('Error Msg Data For Bank:-'||lv_msg_data);

              END IF;
                   
       
        END LOOP;
EXCEPTION
        WHEN OTHERS THEN
             MESSAGE('Err msg:-'||SQLERRM);
             ROLLBACK;
             RETCODE := 2;              
END;
END;

Check Base Tables
---------------------------
SELECT * FROM CE_BANKS_V WHERE BANK_NAME IN('ICICI BANK','SBI BANK','SBH BANK')

SELECT * FROM CE_BANK_BRANCHES_V WHERE BANK_PARTY_ID IN(424995,425001,425005,410933)