Create Bank Branch Oracle EBS (Calling API)

DECLARE

   CURSOR c1

   IS

      SELECT bank_name, branch_name, branch_type, ID

        FROM ce_bank_branch_tmp;

   v_return_status   VARCHAR2 (50);

   v_branch_id       NUMBER;

   v_bank_id         NUMBER;

   v_msg_count       NUMBER;

   v_msg_data        VARCHAR2 (200);

BEGIN

   FOR r1 IN c1

   LOOP

      BEGIN

         SELECT hp.party_id

           INTO v_bank_id

           FROM ar.hz_parties hp, ar.hz_party_usg_assignments hpua

          WHERE hp.party_name = r1.bank_name

            AND hp.party_id = hpua.party_id

            AND hpua.party_usage_code = 'BANK';

      EXCEPTION

         WHEN OTHERS

         THEN

            v_bank_id := NULL;

      END;

      ce_bank_pub.create_bank_branch

                 (p_bank_id            => v_bank_id,

                  p_branch_name        => r1.branch_name,

                  p_branch_type        => r1.branch_type,

                  x_branch_id          => v_branch_id,

                  x_return_status      => v_return_status,

                  x_msg_count          => v_msg_count,

                  x_msg_data           => v_msg_data

                                     );

      DBMS_OUTPUT.put_line (   r1.branch_name

                            || ' has branch id '

                            || v_branch_id

                            || ' status '

                            || v_return_status

                            || ' error '

                            || v_msg_data

                           );

      UPDATE ce_bank_branch_tmp

         SET flag = v_return_status,

             note = v_msg_data

       WHERE ID = r1.ID;

   END LOOP;

END;

ce_bank_branch_tmp is staging table which stored bank branch data before upload to oracle bank branch table.