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;