8/18/2019 Import External Bank Accounts r12 Oracle Apps

1/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html

19th August 2012

Below post will explain the step involved in importing an external bank account in Oracle Apps R12.

STEP 1: CREATE PARTY for BANK in TCA

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank 

SCRIPT:

Test Instance: R12.1.1 

Script:

set serveroutput on;

DECLARE

 

v_error_reason VARCHAR2 (2000); 

v_msg_data VARCHAR2 

(1000);

  v_msg_count NUMBER;

  v_return_status VARCHAR2 (100); 

v_extbank_rec_type iby_ext_bankacct_pub.extbank_rec_type ;

  x_response iby_fndcpt_common_pub.result_rec_type; 

x_bank_id NUMBER;

 

BEGIN

  v_error_reason := NULL;

  v_return_status := NULL;

  v_msg_count := NULL;

  v_msg_data := NULL;

  v_extbank_rec_type .object_version_number := 1.0;

  v_extbank_rec_type .bank_name := 'TEST SHARE';

  v_extbank_rec_type .bank_number := '14589';

IMPORT EXTERNAL BANK ACCOUNTS R12ORACLE APPS

[https://www.blogger.com/blogger.g?

blogID=955765254185387334]IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334https://www.blogger.com/blogger.g?blogID=955765254185387334http://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.htmlhttp://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.htmlhttp://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.htmlhttp://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.htmlhttps://www.blogger.com/blogger.g?blogID=955765254185387334http://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.htmlhttp://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.html

8/18/2019 Import External Bank Accounts r12 Oracle Apps

2/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 2

  v_extbank_rec_type .institution_type := 'BANK';

  v_extbank_rec_type .country_code := 

'US';

  v_extbank_rec_type .description := 'Create via API';

 

iby_ext_bankacct_pub .create_ext_bank

  (p_api_version => 1.0,

  p_init_msg_list => 

fnd_api.g_true,

  p_ext_bank_rec => v_extbank_rec_type ,

  x_bank_id => x_bank_id,

  x_return_status => v_return_status,

  x_msg_count => v_msg_count,

  x_msg_data => v_msg_data,

  x_response => x_response

  );

 

DBMS_OUTPUT.put_line ('v_return_status = '||v_return_status );

  DBMS_OUTPUT.put_line ('v_msg_count = '||v_msg_count);

  DBMS_OUTPUT.put_line ('v_msg_data = '||v_msg_data);

  DBMS_OUTPUT.put_line ('x_bank_id = '||x_bank_id);

  DBMS_OUTPUT.put_line ('x_response.Result_Code = ' || x_response.result_code);

  DBMS_OUTPUT.put_line (  'x_response.Result_Category = '

  || x_response.result_category

  );

  DBMS_OUTPUT.put_line (  'x_response.Result_Message = '

  || x_response.result_message

  );

  IF v_return_status  fnd_api.g_ret_sts_success

  THEN

  IF v_msg_count >= 1

  THEN

  FOR i IN 1 .. v_msg_count

  LOOP

  IF v_error_reason IS NULL

  THEN

  v_error_reason :=

  SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),

  1,

8/18/2019 Import External Bank Accounts r12 Oracle Apps

3/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 3

  255

  );

  ELSE

  v_error_reason :=

  v_error_reason

  || ' ,'

  || SUBSTR (fnd_msg_pub.get (p_encoded =>fnd_api.g_false),

  1,

  255

  );

  END IF;

  DBMS_OUTPUT.put_line ('BANK API ERROR-' || v_error_reason);

  END LOOP;

  END 

IF;

  END IF;

END;

STEP2: CREATE PARTY for BANK BRANCH in TCA

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

Test Instance: R12.1.1

Script:

SET SERVEROUTPUT ON;

DECLARE

  p_api_version NUMBER  := 1.0;

  p_init_msg_list VARCHAR2 (1)  := 'F'; 

x_return_status VARCHAR2 (2000);

  x_msg_count NUMBER (5);

  x_msg_data VARCHAR2 (2000);

  x_response iby_fndcpt_common_pub.result_rec_type;

  p_ext_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type ;

  v_bank_id NUMBER := 208787; -- EXISTING BANK PARTY ID 

  x_branch_id NUMBER;

  p_count NUMBER;

8/18/2019 Import External Bank Accounts r12 Oracle Apps

4/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 4

BEGIN

  DBMS_OUTPUT.put_line ('BEFORE BANK BRANCH API');

  p_ext_bank_branch_rec .bch_object_version_number := 1.0;

  p_ext_bank_branch_rec .branch_name := 'TEST BANK BRANCH';

  p_ext_bank_branch_rec .branch_type := 'ABA';

  p_ext_bank_branch_rec .bank_party_id := v_bank_id; 

IBY_EXT_BANKACCT_PUB .CREATE_EXT_BANK_BRANCH

  (p_api_version => p_api_version,

  p_init_msg_list => p_init_msg_list,

  p_ext_bank_branch_rec => p_ext_bank_branch_rec ,

  x_branch_id => x_branch_id,

  x_return_status => x_return_status,

  x_msg_count => x_msg_count,

  x_msg_data => 

x_msg_data,

  x_response => x_response

  );

  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);

  DBMS_OUTPUT.put_line ('x_branch_id = ' || x_branch_id);

  DBMS_OUTPUT.put_line ('x_response.Result_Code = ' 

|| x_response.result_code);

  DBMS_OUTPUT.put_line (  'x_response.Result_Category = '

  || x_response.result_category

  );

  DBMS_OUTPUT.put_line (  'x_response.Result_Message = '

  || x_response.result_message

  );

  IF 

x_msg_count = 

1

  THEN

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

  ELSIF x_msg_count > 1

  THEN

  LOOP

  p_count := p_count + 1;

  x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next,fnd_api.g_false);

8/18/2019 Import External Bank Accounts r12 Oracle Apps

5/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 5

  IF x_msg_data IS NULL

  THEN

  EXIT;

  END IF;

  DBMS_OUTPUT.put_line ('Message' 

|| p_count || ' ---' 

|| x_msg_data);

  END LOOP;

  END IF;

END;

STEP3: CREATE ADDRESS for BANK BRANCH as LOCATION in TCA

API Involved: HZ_LOCATION_V2PUB.CREATE_LOCATION

Below wrapper script will help you create a valid Location in the table HZ_LOCATIONS.

Test 

Instance: R12.1.3

API: HZ_LOCATION_V2PUB .CREATE_LOCATION

Note: Value for created_by_module must be a value defined in lookup type HZ_CREATED_BY_MODULES in the table FND_LOOKUP_VALUES

SCRIPT:

SET SERVEROUTPUT ON;

DECLARE

 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE ;

 x_location_id NUMBER;

 x_return_status VARCHAR2(2000);

 x_msg_count NUMBER;

 x_msg_data VARCHAR2(2000);

BEGIN

 p_location_rec.country := 

'US';

 p_location_rec.address1 := 'Shareoracleapps' ;

 p_location_rec.city := 'san Mateo';

 p_location_rec.postal_code := '94401';

 p_location_rec.state := 'CA';

 p_location_rec.created_by_module := 'BO_API';

 DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.create_location');

8/18/2019 Import External Bank Accounts r12 Oracle Apps

6/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 6

 HZ_LOCATION_V2PUB.CREATE_LOCATION

  (

  p_init_msg_list => FND_API.G_TRUE,

  p_location_rec => p_location_rec,

  x_location_id => x_location_id,

  x_return_status => x_return_status ,

  x_msg_count => x_msg_count,

  x_msg_data => 

x_msg_data);

 IF x_return_status = fnd_api.g_ret_sts_success THEN

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');

  DBMS_OUTPUT.PUT_LINE('Output information ....');

  DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_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 Location failed:'||x_msg_data);

  ROLLBACK;

  FOR i IN 1 .. x_msg_count

  LOOP

  x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

  dbms_output.put_line( i|| ') '|| x_msg_data);

  END LOOP;

END 

IF;

DBMS_OUTPUT.PUT_LINE('Completion of API');

END;

/

STEP4: CREATE PARTY SITE for BANK BRANCH with LOCATION created in above step

API Involved: HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

DESCRIPTION:This roune is used to create a Party Site for a party. Party Site relates an exisng party from the HZ_PARTIES table with an address locaon from the

HZ_LOCATIONS table.The API creates a record in the HZ_PARTY_SITES table. You can create mulple party sites with mulple locaons and mark one of those party sites a

idenfying for that party. The idenfying party site address components are denormalized into the

HZ_PARTIES table. If orig_system is passed in, the API also creates a record in the HZ_ORIG_SYS_REFERENCES table to store the mapping between the source system

reference and the TCA primary key.

API:  HZ_PARTY_SITE_V2PUB .CREATE_PARTY_SITE

8/18/2019 Import External Bank Accounts r12 Oracle Apps

7/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 7

BASE TABLES AFFECTED 

: 

HZ_PARTY_SITES

TEST INSTANCE : 

R12.1.3

NOTES:

Enter the values for Party Id and Locaon Id as valid values from HZ_PARTIES and HZ_LOCATIONS respecvely.

SELECT party_id FROM hz_parties;

SELECT 

location_id FROM 

hz_locations;

SCRIPT:

SET SERVEROUTPUT ON;

DECLARE

p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE ;

x_party_site_id NUMBER;

x_party_site_number VARCHAR2(2000);

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

BEGIN

-- Setting the Context --

mo_global.init('AR');

fnd_global.apps_initialize ( user_id => 1318

  ,resp_id => 

50559

  ,resp_appl_id => 222);

mo_global.set_policy_context ('S',204);

fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters

p_party_site_rec .party_id := 530682;

p_party_site_rec .location_id := 28215;

p_party_site_rec .identifying_address_flag := 'Y';

p_party_site_rec .created_by_module := 

'BO_API';

DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');

HZ_PARTY_SITE_V2PUB .CREATE_PARTY_SITE

  (

  p_init_msg_list => FND_API.G_TRUE,

  p_party_site_rec => p_party_site_rec ,

  x_party_site_id => x_party_site_id,

8/18/2019 Import External Bank Accounts r12 Oracle Apps

8/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 8

  x_party_site_number => x_party_site_number ,

  x_return_status => x_return_status,

  x_msg_count => x_msg_count,

  x_msg_data => x_msg_data

  );

IF x_return_status = fnd_api.g_ret_sts_success THEN

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');

  DBMS_OUTPUT.PUT_LINE('Output information ....');

  DBMS_OUTPUT.PUT_LINE('Party Site Id = '||x_party_site_id );

  DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number );

ELSE

  DBMS_OUTPUT.put_line ('Creation of Party Site failed:'||x_msg_data);

  ROLLBACK;

  FOR i IN 1 .. x_msg_count

  LOOP

  x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');

  dbms_output.put_line( i|| ') '|| x_msg_data);

  END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('Completion of API');

END;

/

STEP 5: CREATE BANK ACCOUNT in IBY using BANK_ID, BRANCH_ID created in STEP1&2

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank_acct 

Test Instance: R12.1.1

Script:

SET SERVEROUTPUT ON;

DECLARE

  p_api_version NUMBER  := 1.0;

  p_init_msg_list VARCHAR2(1)  := 'F';

  x_return_status VARCHAR2(2000);

  x_msg_count NUMBER(5);

  x_msg_data VARCHAR2(2000);

  x_response iby_fndcpt_common_pub.result_rec_type ;

  p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type ;

8/18/2019 Import External Bank Accounts r12 Oracle Apps

9/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 9

  v_supplier_party_id NUMBER := 55816;  -- EXISTING SUPPLIERS/CUSTOMER

PARTY_ID 

  v_bank_id NUMBER := 208587; -- EXISTING BANK PARTY ID 

  v_bank_branch_id NUMBER := 278411; -- EXISTING BRANCH PARTY ID 

  x_acct_id NUMBER;

  p_count NUMBER;

BEGIN

  p_ext_bank_acct_rec .object_version_number := 1.0;

  p_ext_bank_acct_rec .acct_owner_party_id := v_supplier_party_id ;

  p_ext_bank_acct_rec .bank_account_name := 'XXTEST BANK ACCNT';

  p_ext_bank_acct_rec .bank_account_num := 14278596531;

  p_ext_bank_acct_rec .alternate_acct_name := 'XXTEST BANK ACCNT ALT';

  p_ext_bank_acct_rec .bank_id := v_bank_id;

  p_ext_bank_acct_rec .branch_id := v_bank_branch_id ;

  p_ext_bank_acct_rec .start_date := SYSDATE;

  p_ext_bank_acct_rec .country_code := 'US';

  p_ext_bank_acct_rec .currency := 'USD';

  p_ext_bank_acct_rec .foreign_payment_use_flag := 'Y';

  p_ext_bank_acct_rec .payment_factor_flag := 'N';

  IBY_EXT_BANKACCT_PUB .CREATE_EXT_BANK_ACCT

  (p_api_version => p_api_version,

  p_init_msg_list => p_init_msg_list,

  p_ext_bank_acct_rec => p_ext_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

  );

 

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);

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

  DBMS_OUTPUT.put_line ('x_response.Result_Code = ' || x_response.result_code);

  DBMS_OUTPUT.put_line (  'x_response.Result_Category = '

  || x_response.result_category

8/18/2019 Import External Bank Accounts r12 Oracle Apps

10/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 10

  );

  DBMS_OUTPUT.put_line (  'x_response.Result_Message = '

  || x_response.result_message

  );

  IF x_msg_count = 1

  THEN

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

  ELSIF x_msg_count > 1

  THEN

  LOOP

  p_count := p_count + 1;

  x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next,fnd_api.g_false);

  IF 

x_msg_data IS 

NULL

  THEN

  EXIT;

  END IF;

  DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);

  END LOOP;

  END IF;

END;

8/18/2019 Import External Bank Accounts r12 Oracle Apps

11/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

http://oracleappsviews.blogspot.in/2012/08/import-external-bank-accounts-r12.html 1

  [http://4.bp.blogspot.com/-

qnqZNY9n4tE/TfiWfsZmK1I/AAAAAAAAADU/dUpj7mAgMh8/s1600/STEPS+to+Create+External+Bank+Account.JPG]

Posted 19th August 2012 by Krishnareddy

 1   View comments

Team search   4 June 2015 at 04:11

Hi Krishna,

I don't mind somebody copying the articles from my website www.shareoracleapps.com. But, i expect themto give a credit with a back link. Hopes nothing wrong in that. Anyhow, thank you for visiting my blog.

http://www.shareoracleapps.com/2011/05/import-external-bank-accounts-r12.html

Regards,TeamSearch

Reply

http://oracleappsviews.blogspot.com/2012/08/import-external-bank-accounts-r12.html?showComment=1433416308853#c8781585035762281452http://www.blogger.com/profile/18006386434186479521http://4.bp.blogspot.com/-qnqZNY9n4tE/TfiWfsZmK1I/AAAAAAAAADU/dUpj7mAgMh8/s1600/STEPS+to+Create+External+Bank+Account.JPG

8/18/2019 Import External Bank Accounts r12 Oracle Apps

12/12

22/03/2016 IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

Sign out

  Notify me

Enter your comment...

Comment as:  Unknown (Goo

Publish 

Preview

http://oracleappsviews.blogspot.com/logout?d=https://www.blogger.com/logout-redirect.g?blogID%3D955765254185387334%26postID%3D6607447616932234034