IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS
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';
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,
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;
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);
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');
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
routine is used to create a Party Site for a party. Party Site relates
an existing party from the HZ_PARTIES table with an address location
from the HZ_LOCATIONS table.The API creates a record in the
HZ_PARTY_SITES table. You can create multiple party sites with multiple
locations and mark one of those party sites as identifying for that
party. The identifying 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
BASE TABLES AFFECTED : HZ_PARTY_SITES
TEST INSTANCE : R12.1.3
NOTES:
Enter the values for Party Id and Location Id as valid values from HZ_PARTIES and HZ_LOCATIONS respectively.
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,
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;
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
);
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;
No comments:
Post a Comment