Friday 5 January 2018

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


5 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Good blog, Thanks for sharing this informative article. It would be helpful to all if you write a full article.
Oracle Fusion HCM Online Training"

Anonymous said...

Good blog, Thanks for sharing this informative article. It would be helpful to all if you write a full article.
Oracle Fusion HCM Online Training"

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

Goutham Raj said...

Good Article, Alots of information provided, Thanks for sharing this information.
Spark Scala Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *