Oracle API to Insert Supplier Banking Account Information

In this post , I will share you the PLSQL script which uses Oracle Standard API to insert Supplier Banking Account Information in Oracle Apps.



Supplier Bank Account Creation API

  DECLARE

x_bank_rec iby_ext_bankacct_pub.extbank_rec_type;

x_bank_id NUMBER;

x_return_status VARCHAR2 (10);

x_msg_count NUMBER;

x_msg_data VARCHAR2 (256);

x_response_rec iby_fndcpt_common_pub.result_rec_type;

x_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;

x_branch_id NUMBER;

x_acct_id NUMBER;

x_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;

p_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type;

x_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type;

x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_create_tab_type;

l_ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;

l_ext_payee_id_rec_type iby_disbursement_setup_pub.ext_payee_id_rec_type;

p_payee iby_disbursement_setup_pub.payeecontext_rec_type;

p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;

p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type;

x_assign_id NUMBER;

v_bank_party_id NUMBER;

v_branch_party_id NUMBER;

v_vendor_id NUMBER;

v_vendor_site_id NUMBER;

v_party_site_id NUMBER;

V_EXT_BANK_ACCOUNT_ID NUMBER;

v_bank_creation_flag VARCHAR2 (40);

v_error_flag VARCHAR2 (40);

v_error_message VARCHAR2 (4000);

v_party_id number;

v_TERRITORY_CODE VARCHAR2(400);

x_joint_owner_id number;

CURSOR supplier_bank

IS

SELECT ROWID, xrs.*

FROM XXAP_SUPP_BANK_ACCOUNT_STG xrs

WHERE processed_flag = 'N';


BEGIN

DBMS_OUTPUT.put_line ('Start of procedure.');

fnd_global.apps_initialize (fnd_global.user_id,

fnd_global.resp_id,

fnd_global.resp_appl_id

);

DBMS_OUTPUT.put_line ('Procedure initializad.');

FOR i IN supplier_bank

LOOP

v_bank_creation_flag := 'N';

v_error_flag := 'N';

v_error_message := NULL;

v_bank_party_id:=NULL;

V_branch_party_id:=NULL;

v_ext_bank_account_id:=NULL;

v_vendor_id:=NULL;

v_party_id:=NULL;

v_vendor_site_id:=NULL;

v_party_site_id:=NULL;

v_TERRITORY_CODE:=NULL;




 begin
  select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories

where UPPER(TERRITORY_CODE)=UPPER(i.COUNTRY);

exception when others then

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist';




end;
  


　
  BEGIN

SELECT bank_party_id

INTO v_bank_party_id

FROM ce_banks_v

WHERE UPPER(bank_name)= UPPER(i.bank_name)

and upper(HOME_COUNTRY)=UPPER(I.COUNTRY)

AND ROWNUM=1;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_error_flag := 'Y';

v_error_message := 'BANK IS NOT EXIST IN THE SYSTEM';

END;

BEGIN

SELECT branch_party_id

INTO v_branch_party_id

FROM ce_bank_branches_v

where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME)

AND UPPER(bank_name)=UPPER(I.BANK_NAME)

AND UPPER(BANK_HOME_COUNTRY)=UPPER(I.COUNTRY)

AND ROWNUM=1;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := 'Y';

v_error_message :=

v_error_message || 'BRANCH IS NOT EXIST IN THE SYSTEM';

END;

BEGIN

SELECT ext_bank_account_id

INTO v_ext_bank_account_id

FROM iby_ext_bank_accounts

WHERE bank_id = v_bank_party_id

AND branch_id = v_branch_party_id

AND bank_account_num = i.bank_account_number

AND UPPER(bank_account_name) = UPPER(i.bank_account_name);

EXCEPTION

WHEN no_data_found

THEN

v_ext_bank_account_id:=null;

when others then

v_error_flag := 'Y';

v_error_message :=

v_error_message || 'ERROR IN THE SYSTEM BANK ACCOUNT NUMBER FETCH';




  END;






  BEGIN

SELECT vendor_id

INTO v_vendor_id

FROM po_vendors

WHERE upper(vendor_name) = upper(i.vendor_name)

and segment1=I.ATTRIBUTE1;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := 'Y';

v_error_message :=

v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';

END;

BEGIN

SELECT party_id

INTO v_party_id

FROM po_vendors

WHERE upper(vendor_name) =upper(i.vendor_name)

and segment1=I.ATTRIBUTE1;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := 'Y';

v_error_message :=

v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';

END;

IF i.vendor_site_code IS NOT NULL THEN

BEGIN

SELECT vendor_site_id,party_site_id

INTO v_vendor_site_id,v_party_site_id

FROM ap_supplier_sites_all

WHERE UPPER(vendor_site_code) = UPPER(i.vendor_site_code)

AND ORG_ID=p_operating_unit

AND vendor_id = v_vendor_id;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := 'Y';

v_error_message :=

v_error_message || 'VENDOR SITE NOT EXIST IN THE SYSTEM';

END;





   
  END IF;



　

　
  IF v_error_flag = 'N' AND v_ext_bank_account_id IS NULL

THEN

x_bank_acct_rec.country_code := I.COUNTRY ;

x_bank_acct_rec.branch_id := v_branch_party_id;

x_bank_acct_rec.bank_id := v_bank_party_id;

x_bank_acct_rec.acct_owner_party_id := v_party_id;

--supplier party id

x_bank_acct_rec.iban := i.iban;

x_bank_acct_rec.currency := i.currency;

x_bank_acct_rec.bank_account_name := i.bank_account_name;

x_bank_acct_rec.bank_account_num := i.bank_account_number;

x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

iby_ext_bankacct_pub.create_ext_bank_acct

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

p_ext_bank_acct_rec => x_bank_acct_rec,

x_acct_id => x_acct_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_response => x_response_rec

);

IF x_return_status = fnd_api.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line ('External bank account created.');

DBMS_OUTPUT.put_line ('x_acct_id' || x_acct_id);

DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);

DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count);

DBMS_OUTPUT.put_line ('x_msg_data.' || x_msg_data);

ELSE



DBMS_OUTPUT.put_line
  ( 'Creation of BANK ACCCOUNT failed:'



|| x_msg_data
  );

ROLLBACK;

v_error_flag := 'Y';

v_error_message :=



v_error_message
  || 'Creation of BANK ACCCOUNT failed:'

|| x_msg_data;



　

　

　
  END IF;

l_ext_payee_rec.payee_party_site_id := v_party_site_id;

l_ext_payee_rec.payee_party_id := v_party_id;

l_ext_payee_rec.payment_function := 'PAYABLES_DISB';

IF i.vendor_site_code IS NOT NULL THEN

l_ext_payee_rec.payer_org_id := p_operating_unit;

l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';

ELSE

l_ext_payee_rec.payer_org_id := NULL;

l_ext_payee_rec.payer_org_type := NULL;

END IF;

l_ext_payee_rec.exclusive_pay_flag := 'N';

l_ext_payee_rec.default_pmt_method :=

NVL ('CHECK', i.payment_method);

l_ext_payee_rec.supplier_site_id := v_vendor_site_id;

x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

p_ext_payee_tab (0) := l_ext_payee_rec;

iby_disbursement_setup_pub.create_external_payee

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

p_ext_payee_tab => p_ext_payee_tab,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_ext_payee_id_tab => x_ext_payee_id_tab,

x_ext_payee_status_tab => x_ext_payee_status_tab

);

IF x_return_status = fnd_api.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line ('External Payee created.');

DBMS_OUTPUT.put_line ('x_return_status: ' || x_return_status

);

DBMS_OUTPUT.put_line ('x_msg_count. ' || x_msg_count);

DBMS_OUTPUT.put_line ('x_msg_data. ' || x_msg_data);

ELSE

DBMS_OUTPUT.put_line ( 'Creation of payee failed:'



|| x_msg_data
  );

v_error_message :=



v_error_message
  || 'Creation of payee failed:'

|| x_msg_data;

ROLLBACK;

v_error_flag := 'Y';



　

　

　
  END IF;

p_payee.supplier_site_id := v_vendor_site_id;

p_payee.party_id :=v_party_id;

p_payee.party_site_id := v_party_site_id;

p_payee.payment_function := 'PAYABLES_DISB';

IF i.vendor_site_code IS NOT NULL THEN

p_payee.org_id := p_operating_unit;

p_payee.org_type := 'OPERATING_UNIT';

ELSE

p_payee.org_id := NULL;

p_payee.org_type := NULL;

END IF;

l_ext_payee_id_rec_type := x_ext_payee_id_tab (0);

p_instrument.instrument_id := x_acct_id;

p_instrument.instrument_type := 'BANKACCOUNT';

p_assignment_attribs.priority := 1;

p_assignment_attribs.instrument := p_instrument;

x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

x_response_rec := NULL;

iby_disbursement_setup_pub.set_payee_instr_assignment

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

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 => p_payee,

p_assignment_attribs => p_assignment_attribs,

x_assign_id => x_assign_id,

x_response => x_response_rec

);

IF x_return_status = fnd_api.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');

DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);

DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);

DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count);

DBMS_OUTPUT.put_line ('x_msg_data.' || x_msg_data);



　
  ELSE



DBMS_OUTPUT.put_line
  ( 'Creation of Payee_Instr_Assignment failed:'



|| x_msg_data
  );

v_error_message:=

v_error_message||'Creation of Payee_Instr_Assignment failed:'

|| x_msg_data;

ROLLBACK;

v_error_flag := 'Y';



　

　

　
  END IF;

END IF;






  IF v_error_flag = 'N' AND v_ext_bank_account_id IS NOT NULL

THEN




  DBMS_OUTPUT.put_line ('CHECKING THAT VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status);




BEGIN
   x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

x_response_rec := NULL;

IBY_EXT_BANKACCT_PUB.check_bank_acct_owner

(p_api_version => 1.0,

p_init_msg_list => FND_API.G_FALSE,

p_bank_acct_id => v_ext_bank_account_id,

p_acct_owner_party_id =>v_party_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_response => x_response_rec




);
  EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('ERROR IN CHECKING VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status);




END;
  IF x_return_status = fnd_api.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line ('already owner');




  p_payee.supplier_site_id := v_vendor_site_id;

p_payee.party_id :=v_party_id;

p_payee.party_site_id := v_party_site_id;

p_payee.payment_function := 'PAYABLES_DISB';

IF i.vendor_site_code IS NOT NULL THEN

p_payee.org_id := p_operating_unit;

p_payee.org_type := 'OPERATING_UNIT';

ELSE

p_payee.org_id := NULL;

p_payee.org_type := NULL;

END IF;

p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id;

p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT';

p_assignment_attribs.priority := 1;

-- p_assignment_attribs.instrument := p_instrument;

x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

x_response_rec := NULL;




  iby_disbursement_setup_pub.set_payee_instr_assignment

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

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 => p_payee,

p_assignment_attribs => p_assignment_attribs,

x_assign_id => x_assign_id,

x_response => x_response_rec

);

IF x_return_status = fnd_api.g_ret_sts_success




  THEN

DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');

DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);

DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);

else



DBMS_OUTPUT.put_line
  ( 'Creation of Payee_Instr_Assignment failed:'



|| x_msg_data
  );

v_error_message:=

v_error_message||'Creation of Payee_Instr_Assignment failed:'

|| x_msg_data;

ROLLBACK;

v_error_flag := 'Y';

end if;




ELSE



DBMS_OUTPUT.put_line
  ( 'Creation of joint bank acct owner'

);




 x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

x_response_rec := NULL;

IBY_EXT_BANKACCT_PUB.add_joint_account_owner




(
  p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

p_bank_account_id => v_ext_bank_account_id,

p_acct_owner_party_id => v_party_id,

x_joint_acct_owner_id =>x_joint_owner_id,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data =>x_msg_data,

x_response=> x_response_rec);

IF x_return_status = fnd_api.g_ret_sts_success

THEN

DBMS_OUTPUT.put_line ('joint acct created');

p_payee.supplier_site_id := v_vendor_site_id;

p_payee.party_id :=v_party_id;

p_payee.party_site_id := v_party_site_id;

p_payee.payment_function := 'PAYABLES_DISB';

IF i.vendor_site_code IS NOT NULL THEN

p_payee.org_id := p_operating_unit;

p_payee.org_type := 'OPERATING_UNIT';

ELSE

p_payee.org_id := NULL;

p_payee.org_type := NULL;

END IF;

p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id;

p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT';

p_assignment_attribs.priority := 1;


x_msg_count := 0;

x_msg_data := NULL;

x_return_status := NULL;

x_response_rec := NULL;




  iby_disbursement_setup_pub.set_payee_instr_assignment

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

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 => p_payee,

p_assignment_attribs => p_assignment_attribs,

x_assign_id => x_assign_id,

x_response => x_response_rec

);

IF x_return_status = fnd_api.g_ret_sts_success




  THEN

DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');

DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);

DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);

else



DBMS_OUTPUT.put_line
  ( 'Creation of Payee_Instr_Assignment failed:'



|| x_msg_data
  );

v_error_message:=

v_error_message||'Creation of Payee_Instr_Assignment failed:'

|| x_msg_data;

ROLLBACK;

v_error_flag := 'Y';

end if;

else



DBMS_OUTPUT.put_line
  ( 'Creation of joint account owner failed'



|| x_msg_data
  );

v_error_message:=

v_error_message||'Creation of joint account owner failed'

|| x_msg_data;

ROLLBACK;

v_error_flag := 'Y';

end if;




end if;

end if;





　




  IF v_error_flag = 'Y'

THEN

ROLLBACK;

UPDATE XXAP_SUPP_BANK_ACCOUNT_STG

SET processed_flag = 'N',

error_description = v_error_message

WHERE ROWID = i.ROWID;

COMMIT;

ELSE

UPDATE XXAP_SUPP_BANK_ACCOUNT_STG

SET processed_flag = 'Y'

WHERE ROWID = i.ROWID;

COMMIT;

END IF;

END LOOP;

END;

To Learn OAF Online from Beginning , Please go to the OAF Tutorial