Wednesday, August 25, 2010

R12 Supplier Bank/Branch Creation Code

Your Ad Here



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;
Your Ad Here

3 comments:

  1. Thank you Amit. It is a useful example.
    Helmut

    ReplyDelete
  2. Hi,

    I am working on supplier bank conversion and have a question using this API's.

    These APIs are working fine and it is creating bank and branch in the tables IBY_EXT_BANKS_V and IBY_EXT_BANK_BRANCHES_V but where do I give the address information for the bank in the API, because the record type does not have parameters to pass address information.

    I am kind of stuck, can someone please help me?? It's urgent.

    Thanks,

    ReplyDelete
  3. Hi Amit,

    I have got a requirement as follows:

    Need to create Bank accounts for the existing suppliers.
    Need to pass 4 parameters Org_id, vendor_id , vendor_site_id and Lot_id(Sequence) as parameters in anonymous block .
    No need to load data into staging table as they will load.
    No Need of Conc prg also.

    We need to pass the parameters and these parameters contain the data in the staging table and need to create bank accounts for the suppliers.

    But unfortunately they are not loading data to these parameters. we need to populate them and update in the staging table.

    Please help me in this as it is little urgent.

    Thanks,
    Ushha.

    ReplyDelete