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;