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)

Supplier With Bank Details and Payment Details

SQL  Query
-----------------------------------
select asp.party_id,
       asp.vendor_id,
       ass.pay_site_flag,
       ass.vendor_site_id,
       hrl_ship.location_code,
       hp_supp.party_id,
       asp.VENDOR_NAME Supplier_name,
       asp.VENDOR_TYPE_LOOKUP_CODE type,
       asp.ONE_TIME_FLAG,
       asp.individual_1099,
       DECODE (
            UPPER (asp.vendor_type_lookup_code),
            'EMPLOYEE',
            papf.national_identifier,
            DECODE (asp.organization_type_lookup_code,
                    'INDIVIDUAL', asp.individual_1099,
                    'FOREIGN INDIVIDUAL', asp.individual_1099,
                    hp_supp.jgzz_fiscal_code)
         ) Taxpayer_ID,
         asp.VAT_REGISTRATION_NUM ,
         hp_supp.DUNS_Number_c  ,
         asp.start_date_active,
         asp.end_date_active,
         asp.hold_flag,
         asp.last_update_date,
         ass.address_line1,
         ass.address_line2,
         ass.address_line3 ,
         ass.city,
         ass.state,
         ass.zip
         , ass.country,
         ass.purchasing_site_flag ,
         ass.RFQ_ONLY_SITE_FLAG ,
         ass.PAY_SITE_FLAG
         , ass.Vendor_Site_Code
         , ass.ATTRIBUTE1 Supplier_Qualification_Status
         , ass.ATTRIBUTE15  Invoice_Approver --, ass.SHIP_TO_LOCATION_CODE
         , hrl_ship.location_code ship_to_location_code
         , hrl_bill.location_code bill_to_location_code
         , ass.inactive_date
         , ass.last_update_date site_last_update_date
         , ass.terms_id
         , att.name terms_name
         , ass.invoice_currency_code
         , ass.payment_currency_code
         , ass.hold_all_payments_flag
         , ass.hold_future_payments_flag
         , ass.vat_registration_num
         , hou.name OPERATING_UNIT_NAME
         , ass.email_address
         , ass.primary_pay_site_flag
         , ass.DUNS_NUMBER
         , ass.PAY_GROUP_LOOKUP_CODE
         , person.person_first_name first_name
         , person.person_last_name  last_name
         , pty_rel.email_address
         , pty_rel.primary_phone_number
         , apsc.creation_date contact_creation_date
         , apsc.last_update_date contact_last_update_date
          --hrl_ship.*
-- person.person_first_name ,
-- person.person_last_name ,pty_rel.primary_phone_number ,
-- pty_rel.email_address
-- , apsc.email_address , ass.*
,ass.duns_number site_duns_number
from ap_suppliers asp
   , ap_supplier_sites_all ass
   , ap_supplier_contacts apsc
   , hz_parties person
   , hz_parties pty_rel
   , hz_parties hp_supp
   , per_all_people_f papf
   , hr_locations hrl_ship
   , hr_locations hrl_bill  
   , ap_terms_tl att
   , hr_operating_units hou
   --, AND pv.party_id = hp.party_id
where 1 = 1
--and asp.vendor_id = 46006 -- 5001 -- 
and ass.vendor_id = asp.vendor_id
and asp.party_id = hp_supp.party_id
AND apsc.org_party_site_id(+) = ass.party_site_id
--and ass.org_id = 222 -- 123
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND asp.employee_id = papf.person_id(+)
and hrl_ship.location_id(+) = ass.ship_to_location_id
and hrl_bill.location_id(+) = ass.bill_to_location_id
and att.term_id(+) = ass.terms_id
and ass.org_id = hou.organization_id
and TRUNC(asp.creation_date) between :P_FROM_CREATION_DATE AND :P_TO_CREATION_DATE
and TRUNC(asp.last_update_date) between NVL(:P_FROM_UPDATE_DATE,TRUNC(asp.last_update_date)) AND NVL(:P_TO_UPDATE_DATE,TRUNC(asp.last_update_date))
and asp.vendor_name = nvl(:p_supplier_name,asp.vendor_name)

Formula Column1(CF_BANK_DATA)
-----------------------------
function CF_BANK_DATAFormula return Number is
begin
 
 
     SELECT   eb.bank_name                 ,
              eb.bank_number ,
                  ebb.bank_branch_name         ,
              ebb.branch_number            ,
              eba.bank_account_num         ,
              eba.bank_account_name
       INTO :CP_BANK_NAME,
            :CP_BANK_NUMBER,
            :CP_BANK_BRANCH_NAME,
            :CP_BRANCH_NUMBER,
            :CP_BANK_ACCOUNT_NUM  ,
            :CP_bank_account_name
   FROM ap.ap_suppliers av         ,
        ap.ap_supplier_sites_all assa     ,
        apps.iby_ext_bank_accounts eba  ,
        apps.iby_account_owners ao      ,
        apps.iby_ext_banks_v eb         ,
        apps.iby_ext_bank_branches_v ebb,
            hr_operating_units hou
  WHERE av.vendor_id           = assa.vendor_id
AND ao.account_owner_party_id = av.party_id
AND eba.ext_bank_account_id   = ao.ext_bank_account_id
AND eb.bank_party_id          = ebb.bank_party_id
AND eba.branch_id             = ebb.branch_party_id
AND eba.bank_id               = eb.bank_party_id
AND assa.org_id                 =hou.organization_id
AND av.vendor_id               = :vendor_id
AND assa.vendor_site_id    = :vendor_site_id;

    RETURN(NULL);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(100,'No bank records for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Formula Column2(CF_PAYMENT_METHOD_CODE)
--------------------------------
function CF_PAYMENT_METHOD_CODEFormula return Char is
lv_payment_method_code   VARCHAR2(30);
begin
 
 
  SELECT ieppm.payment_method_code
   INTO lv_payment_method_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id;
 

    RETURN(lv_payment_method_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(101,'No payment_method_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

formula Column3(CF_FORMAT_CODE)
-----------------------
function CF_FORMAT_CODEFormula return Char is
lv_format_code   VARCHAR2(30);
begin
 
 
  SELECT ifv.format_code
   INTO lv_format_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA,
        IBY_FORMATS_VL IFV
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id
    AND ifv.format_code(+) = iepa.payment_format_code;
 

    RETURN(lv_format_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(102,'No format_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Bank Vochur Report

select api.invoice_num
,       to_char(api.invoice_date, 'DD-MON-YYYY')   "INV_DATE"
,      ac.bank_account_name
,      aip.amount * (nvl(aip.EXCHANGE_RATE,1)) LINE_AMOUNT
,      ac.check_number
,      to_char(ac.check_date, 'DD-MON-YYYY')   "CHECK_DATE"
,      ac.amount  AMOUNT_USD
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE     
,      ac.amount * (nvl(ac.EXCHANGE_RATE,1))  PAY_AMOUNT    
,      ac.vendor_name
,      ac.vendor_site_code
,      ac.DOC_SEQUENCE_VALUE
,      ac.org_id
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME             
from   ap_invoices_all api
,      ap_invoice_payments_all  aip
,      ap_checks_all ac
,      gl_code_combinations gcc
,      ap_bank_accounts_all aba
,      fnd_flex_values_vl  ff
,       fnd_user fu
,       per_all_people_f papf       
where  api.invoice_id=aip.invoice_id
and    api.org_id=aip.org_id
and    gcc.segment4=ff.flex_value
and    ff.flex_value_set_id=1009939
and    gcc.code_combination_id=aba.asset_code_combination_id
and    aip.check_id=ac.check_id
and    api.vendor_id<>7
and    ac.bank_account_id=aba.bank_account_id
and    ac.org_id=aba.org_id
and    ac.bank_account_id=nvl(:P_BANK,ac.bank_account_id)
and    ac.check_number=nvl(:P_CHECK_NUM,ac.check_number)
and    ac.org_id=nvl(:P_ORG_ID,ac.org_id)
and    aip.org_id=ac.org_id
and    ac.CREATED_BY=fu.USER_ID           
and    fu.EMPLOYEE_ID=papf.PERSON_ID       
group by api.invoice_num
,      api.invoice_date
,      ac.bank_account_name
,      ac.check_number
,      ac.check_date
,      ac.amount
,       aip.EXCHANGE_RATE
,      ac.DOC_SEQUENCE_VALUE
,      ac.vendor_name
,      ac.vendor_site_code
,      ac.org_id
,      aip.amount
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE      
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME            
union
select api.invoice_num
,      to_char(api.invoice_date, 'DD-MON-YYYY')  "INV_DATE"
,      ac.bank_account_name
,      aip.amount * (nvl(aip.EXCHANGE_RATE,1)) LINE_AMOUNT
,      ac.check_number
,        to_char(ac.check_date, 'DD-MON-YYYY')   "CHECK_DATE"
,      ac.amount
,      ac.CURRENCY_CODE     
,       ac.EXCHANGE_RATE     
,      ac.amount * (nvl(ac.EXCHANGE_RATE,1))  Base_Amount    
,      ac.vendor_name
,      pap.title||' '||pap.first_name||pap.last_name||'(EMP CODE '||pap.employee_number ||')'  "Supplier Site Code"
,      ac.DOC_SEQUENCE_VALUE
,      ac.org_id
,      ff.description
,      aba.bank_account_num
,      papf.FULL_NAME           
from   ap_invoices_all api
,      ap_invoice_payments_all  aip
,      ap_checks_all ac
,      per_all_people_f pap
,      ap_bank_accounts_all aba
,      gl_code_combinations gcc
,      fnd_flex_values_vl  ff
,       fnd_user fu              
,       per_all_people_f papf      
where  api.invoice_id=aip.invoice_id
and    api.org_id=aip.org_id
and    api.vendor_id=7
and    ac.bank_account_id=aba.bank_account_id
and    ac.org_id=aba.org_id
and    gcc.segment4=ff.flex_value
and    ff.flex_value_set_id=1009939
and    gcc.code_combination_id=aba.asset_code_combination_id
and    ac.bank_account_id=nvl(:P_BANK,ac.bank_account_id)
and    ac.check_number=nvl(:P_CHECK_NUM,ac.check_number)
and    ac.org_id=nvl(:P_ORG_ID,ac.org_id)
and    aip.check_id=ac.check_id
and    aip.org_id=ac.org_id
and    ac.vendor_site_code=pap.employee_number
and    ac.CREATED_BY=fu.USER_ID         
and    fu.EMPLOYEE_ID=papf.PERSON_ID     
group by api.invoice_num
,      api.invoice_date
,      ac.bank_account_name
,      ac.check_number
,      ac.check_date
,      ac.amount
,       aip.EXCHANGE_RATE
,      ac.DOC_SEQUENCE_VALUE
,      ac.vendor_name
,      pap.title||' '||pap.first_name||pap.last_name||'(EMP CODE '||pap.employee_number
,      ac.org_id
,      aip.amount
,      ac.CURRENCY_CODE     
,      ff.description
,      aba.bank_account_num
,       papf.FULL_NAME