Applying Bank details to suppliers sites through IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment API

CREATE OR REPLACE procedure APPS.XX_SUPP_SITE_BANKS_UPDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) as
 --======================================================================================
--  Program Name:  XX_SUPP_SITE_BANKS_UPDATE
--  Developed By:  Adithya.V
--  Description :  This Procedure will be used to Assigning the bank detials to suppliersites.
-- Created date 31-Dec-2012
-- Last updated 03-Jan-2013
--======================================================================================
p_in_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;
p_assignment_attribs  IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
l_assg_attr  IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
--l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;
l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type;
v_assg_id number;
v_response  IBY_FNDCPT_COMMON_PUB.Result_rec_type;


cursor c1 is SELECT aps.vendor_id,ibyextbankaccts.EXT_BANK_ACCOUNT_ID,aps.party_id,assa.PARTY_SITE_ID,
(SELECT  a.vendor_site_id from ap_supplier_sites_all a where a.org_id=<org_id> and a.vendor_id=assa.vendor_id
and a.party_site_id = assa.party_site_id) vendor_site_id,
aps.vendor_name,assa.vendor_site_code, ibyextbankaccts.bank_account_name,
       ibyextbankaccts.bank_account_number,
       ibyextbankaccts.bank_name, ibyextbankaccts.bank_number,
       ibyextbankaccts.bank_branch_name,
       ibyextbankaccts.branch_number,ibyextbankaccts.currency_code
  FROM iby_pmt_instr_uses_all paymentinstrumentuseseo,
       iby_external_bank_accounts_v ibyextbankaccts,
       ap_suppliers aps,
       ap_supplier_sites_all assa,iby_external_payees_all extpayeeseo
 WHERE paymentinstrumentuseseo.instrument_id = ibyextbankaccts.bank_account_id
   AND paymentinstrumentuseseo.instrument_type ='BANKACCOUNT'
   AND paymentinstrumentuseseo.instrument_id = ibyextbankaccts.ext_bank_account_id
and aps.vendor_id = assa.vendor_id
and assa.vendor_site_id(+) = extpayeeseo.supplier_site_id
AND extpayeeseo.ext_payee_id = paymentinstrumentuseseo.ext_pmt_party_id
and assa.org_id =<org_id>;




l_account_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_result_rec_type apps.iby_fndcpt_common_pub.result_rec_type;



BEGIN

FOR C_REC in c1 loop

l_assg_attr.Instrument.Instrument_Type := 'BANKACCOUNT';
l_assg_attr.Instrument.Instrument_Id := C_REC.EXT_BANK_ACCOUNT_ID; --<bank account id>
l_payee_rec.Party_Id := C_REC.party_id; --<account owner party id>- which is party_id of the supplier
l_payee_rec.Payment_Function := 'PAYABLES_DISB';
--p_assignment_attribs.Start_Date :=to_date('01-JAN-09','DD-MON-YY');

 l_payee_rec.Party_Site_id:= C_REC.party_site_id ;--<party site ID>
 l_payee_rec.Supplier_Site_id:= C_REC.vendor_site_id; --<Supplier site ID>
 l_payee_rec.Org_Id:= <Org_id>;
l_payee_rec.Org_Type:=  'OPERATING_UNIT';--<Org Type>


IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
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_assg_attr,
x_assign_id => v_assg_id,
x_response => v_response
);
                 
 if (x_return_status <> 'S') then
                    FND_FILE.PUT_LINE(FND_FILE.LOG,'Encountered ERROR in supplier site bank creation!!!');
                    FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE||' '||SQLERRM);
                    FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------');
                    FND_FILE.PUT_LINE(FND_FILE.LOG,x_msg_data);

                    IF x_msg_count > 1 THEN
                        FOR i IN 1..x_msg_count LOOP
                             FND_FILE.PUT_LINE(FND_FILE.LOG,substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                        END LOOP;
                    END IF;
            else
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SUPPLIER SITE BANK UPDATED SUCCESSFULLY AND BANK ID IS '||C_REC.EXT_BANK_ACCOUNT_ID);
           
             commit;
               
             END IF;


 END LOOP;

end;
/