R12 Supplier Bank/Branch Creation Code

DECLARE

x_result_rec_type iby_fndcpt_common_pub.result_rec_type;

v_error_reason VARCHAR2 (2000) := NULL;

v_msg_data VARCHAR2 (1000) := NULL;

v_msg_count NUMBER := NULL;

v_return_status VARCHAR2 (100) := NULL;

v_extbank_rec_type iby_ext_bankacct_pub.extbank_rec_type;

v_extbankbranch_rec_type iby_ext_bankacct_pub.extbankbranch_rec_type;

v_bank_id NUMBER;

x_bank_id NUMBER;

v_branch_id NUMBER;

x_branch_id NUMBER;

CURSOR c

IS

SELECT * FROM XXAP_BANK_BRANCH_STG;

BEGIN

FOR i IN c

LOOP

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 := i.bank_name;

v_extbank_rec_type.bank_number := i.bank_num;

v_extbank_rec_type.institution_type := i.institution_type;

v_extbank_rec_type.country_code := NVL(i.country, 'US');

v_extbank_rec_type.description := i.description;

DBMS_OUTPUT.put_line ('BEFORE BANK 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_result_rec_type

);

DBMS_OUTPUT.put_line ('AFTER BANK API');

DBMS_OUTPUT.put_line (v_return_status);

DBMS_OUTPUT.put_line (v_msg_count);

DBMS_OUTPUT.put_line (v_msg_data);

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;

ROLLBACK;

ELSE

v_bank_id := x_bank_id;

DBMS_OUTPUT.put_line ('BANK ID-' || v_bank_id);

COMMIT;

END IF;

v_error_reason := NULL;

v_return_status := NULL;

v_msg_count := NULL;

v_msg_data := NULL;

v_extbankbranch_rec_type.bank_party_id := v_bank_id;

v_extbankbranch_rec_type.branch_name := i.bank_branch_name;

v_extbankbranch_rec_type.branch_type := i.bank_branch_type;

v_extbankbranch_rec_type.attribute14 := i.bank_branch_id;

v_extbankbranch_rec_type.attribute15 := 'DOMESTIC';

DBMS_OUTPUT.put_line ('BEFORE BRANCH API');

iby_ext_bankacct_pub.create_ext_bank_branch

(p_api_version => 1.0,

p_init_msg_list => fnd_api.g_true,

p_ext_bank_branch_rec => v_extbankbranch_rec_type,

x_branch_id => x_branch_id,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

x_response => x_result_rec_type

);

DBMS_OUTPUT.put_line ('AFTER BRANCH API');

DBMS_OUTPUT.put_line (v_return_status);

DBMS_OUTPUT.put_line (v_msg_count);

DBMS_OUTPUT.put_line (v_msg_data);

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;

END LOOP;

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

END IF;

ROLLBACK;

ELSE

v_branch_id := x_branch_id;

DBMS_OUTPUT.put_line ('BRANCH ID-' || v_branch_id);

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('ERROR INSIDE LOOP-' || SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line (SQLERRM || '-' || SQLCODE);

END;

/