Bank API in Oracle apps

===================================================================
CE_BANK_PUB.CREATE_BANK API to Create an Internal Bank in R12===================================================================
DECLARE
   p_init_msg_list             VARCHAR2 (200);
   p_country_code              VARCHAR2 (200);
   p_bank_name                 VARCHAR2 (200);
   p_bank_number               VARCHAR2 (200);
   p_alternate_bank_name       VARCHAR2 (200);
   p_short_bank_name           VARCHAR2 (200);
   p_description               VARCHAR2 (200);
   p_tax_payer_id              VARCHAR2 (200);
   p_tax_registration_number   VARCHAR2 (200);
   x_bank_id                   NUMBER;
   x_return_status             VARCHAR2 (200);
   x_msg_count                 NUMBER;
   x_msg_data                  VARCHAR2 (200);
   p_count                     NUMBER;
BEGIN
   p_init_msg_list       := fnd_api.g_true;
   p_country_code        := 'US';
   p_bank_name           := 'Test Bank';
   p_bank_number         := '14258';
   p_alternate_bank_name := 'Alternate Test Bank';
   p_short_bank_name     := 'IDFC';
   p_description         := 'Test Bank Creation API';
   CE_BANK_PUB.CREATE_BANK
              (p_init_msg_list            => p_init_msg_list,
               p_country_code             => p_country_code,
               p_bank_name                => p_bank_name,
               p_bank_number              => p_bank_number,
               p_alternate_bank_name      => p_alternate_bank_name,
               p_short_bank_name          => p_short_bank_name,
               p_description              => p_description,
               x_bank_id                  => x_bank_id,
               x_return_status            => x_return_status,
               x_msg_count                => x_msg_count,
               x_msg_data                 => x_msg_data
              );

   DBMS_OUTPUT.put_line ('BANK_ID/PARTY_ID = ' || x_bank_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);
   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;
===================================================================
API to Update an Internal Bank in R12 (CE_BANK_PUB.UPDATE_BANK)====================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_id                 NUMBER;
   p_bank_name               VARCHAR2 (200);
   p_bank_number             VARCHAR2 (200);
   p_alternate_bank_name     VARCHAR2 (200);
   p_short_bank_name         VARCHAR2 (200);
   p_description             VARCHAR2 (200);
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
   p_count                   NUMBER;
BEGIN
   p_init_msg_list         := fnd_api.g_true;
   p_bank_id               := 1147;
   p_bank_name             := 'test bank2';
   p_bank_number           := 3933;
   p_alternate_bank_name   := 'Alternate Test Bank2';
   p_short_bank_name       := 'IDF';                    
   p_description           := 'Test Bank2 Description';
   p_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK
           (p_init_msg_list              => p_init_msg_list,
            p_bank_id                    => p_bank_id,
            p_bank_name                  => p_bank_name,
            p_bank_number                => p_bank_number,
            p_alternate_bank_name        => p_alternate_bank_name,
            p_short_bank_name            => p_short_bank_name,
            p_description                => p_description,
            p_object_version_number      => p_object_version_number,
            x_return_status              => x_return_status,
            x_msg_count                  => x_msg_count,
            x_msg_data                   => x_msg_data
           );
   DBMS_OUTPUT.put_line (   'P_OBJECT_VERSION_NUMBER = '
                         || p_object_version_number
                        );
   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);
   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;
================================================================================
API to Check Existence of an Internal Bank R12 (CE_BANK_PUB.CHECK_BANK_EXIST)================================================================================
DECLARE
   p_country_code   VARCHAR2 (200);
   p_bank_name      VARCHAR2 (200);
   p_bank_number    VARCHAR2 (200);
   x_bank_id        NUMBER;
   x_end_date       DATE;
BEGIN
   p_country_code := 'US';
   p_bank_name    := 'Test Bank';
   p_bank_number  := 14258;
   ce_bank_pub.check_bank_exist (p_country_code      => p_country_code,
                                 p_bank_name         => p_bank_name,
                                 p_bank_number       => p_bank_number,
                                 x_bank_id           => x_bank_id,
                                 x_end_date          => x_end_date
                                );
   DBMS_OUTPUT.put_line (   'BANK_ID/PARTY_ID = '
                         || NVL (TO_CHAR (x_bank_id), 'NOT EXISTS')
                        );
   DBMS_OUTPUT.put_line (   'END_DATE = '
                         || NVL (TO_CHAR (x_end_date), 'NOT EXISTS')
                        );
END;
================================================================================
API to End Date an Internal Bank in R12 - CE_BANK_PUB.SET_BANK_END_DATE================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_id                 NUMBER;
   p_end_date                DATE;
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
BEGIN
   p_init_msg_list         := fnd_api.g_true;
   p_bank_id               := 119147;
   p_end_date              := SYSDATE + 360;
   p_object_version_number := 1;
   CE_BANK_PUB.SET_BANK_END_DATE
             (p_init_msg_list              => p_init_msg_list,
              p_bank_id                    => p_bank_id,
              p_end_date                   => p_end_date,
              p_object_version_number      => p_object_version_number,
              x_return_status              => x_return_status,
              x_msg_count                  => x_msg_count,
              x_msg_data                   => x_msg_data
              );
   DBMS_OUTPUT.put_line (   'P_OBJECT_VERSION_NUMBER = '
                         || p_object_version_number
                        );
   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);
END;
======================
BANK BRANCH APIS======================
================================================================================
API to Create an Internal Bank Branch R12 (CE_BANK_PUB.CREATE_BANK_BRANCH)================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_id                 NUMBER;
   p_branch_name             VARCHAR2 (200);
   p_branch_number           VARCHAR2 (200);
   p_branch_type             VARCHAR2 (200);
   p_alternate_branch_name   VARCHAR2 (200);
   p_description             VARCHAR2 (200);
   p_rfc_identifier          VARCHAR2 (200);
   x_branch_id               NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
   p_count                   NUMBER;
BEGIN
   p_init_msg_list := fnd_api.g_true;
   p_bank_id       := 219143; -- PARTY_ID of the BANK
   p_branch_name   := 'Test Branch';
   p_alternate_branch_name := 'Alternate Branch Name';
   p_description   := 'TEST Branch description';
 
   -- branch number/Routing Transit Number is subjected to
   -- the check digit validation
   p_branch_number := 111000025;
 
   -- Validated against AR_LOOKUPS with lookup_type = BANK_BRANCH_TYPE
   p_branch_type   := 'ABA';
 
   -- Validated against CE_LOOKUPS with lookup_type = RFC_IDENTIFIER
   p_rfc_identifier := 'AFC';

 
   CE_BANK_PUB.CREATE_BANK_BRANCH
            (p_init_msg_list              => p_init_msg_list,
             p_bank_id                    => p_bank_id,
             p_branch_name                => p_branch_name,
             p_branch_number              => p_branch_number,
             p_branch_type                => p_branch_type,
             p_alternate_branch_name      => p_alternate_branch_name,
             p_description                => p_description,     
             p_rfc_identifier             => p_rfc_identifier,
             x_branch_id                  => x_branch_id,
             x_return_status              => x_return_status,
             x_msg_count                  => x_msg_count,
             x_msg_data                   => x_msg_data
            );
   DBMS_OUTPUT.put_line ('BRANCH_ID/PARTY_ID = ' || x_branch_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);
   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;

================================================================================
API to Update an Internal Bank Branch R12 (CE_BANK_PUB.UPDATE_BANK_BRANCH)================================================================================

DECLARE
   p_init_msg_list               VARCHAR2 (200);
   p_branch_id                   NUMBER;
   p_branch_name                 VARCHAR2 (200);
   p_branch_number               VARCHAR2 (200);
   p_branch_type                 VARCHAR2 (200);
   p_alternate_branch_name       VARCHAR2 (200);
   p_description                 VARCHAR2 (200); 
   p_country_validate            VARCHAR2 (200);
   p_bch_object_version_number   NUMBER;
   p_typ_object_version_number   NUMBER;
   p_rfc_object_version_number   NUMBER;
   p_eft_object_version_number   NUMBER;
   x_return_status               VARCHAR2 (200);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (200);
   p_count                       NUMBER;
BEGIN
   p_init_msg_list := fnd_api.g_true;
   p_branch_id := 219154;
   p_branch_number := 111000025;
   p_branch_type := 'ABA';
   p_branch_name := 'Test Branch';
   p_alternate_branch_name := 'updated Alternate Branch Name';
   p_description := 'TEST Branch description';
   p_country_validate := NULL;
   p_bch_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK_BRANCH
        (p_init_msg_list                  => p_init_msg_list,
         p_branch_id                      => p_branch_id,
         p_branch_name                    => p_branch_name,
         p_branch_number                  => p_branch_number,
         p_branch_type                    => p_branch_type,
         p_alternate_branch_name          => p_alternate_branch_name,
         p_description                    => p_description,
         p_country_validate               => p_country_validate,
         p_bch_object_version_number      => p_bch_object_version_number,
         p_typ_object_version_number      => p_typ_object_version_number,
         p_rfc_object_version_number      => p_rfc_object_version_number,
         p_eft_object_version_number      => p_eft_object_version_number,
         x_return_status                  => x_return_status,
         x_msg_count                      => x_msg_count,
         x_msg_data                       => x_msg_data
        );
   DBMS_OUTPUT.put_line (   'P_BCH_OBJECT_VERSION_NUMBER = '
                         || p_bch_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_TYP_OBJECT_VERSION_NUMBER = '
                         || p_typ_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_RFC_OBJECT_VERSION_NUMBER = '
                         || p_rfc_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_EFT_OBJECT_VERSION_NUMBER = '
                         || p_eft_object_version_number
                        );
   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);
   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;

================================================================================
CE_BANK_PUB.CHECK_BRANCH_EXISTS - API to check existence of an Internal Bank Branch in R12================================================================================

DECLARE
   p_bank_id         NUMBER;
   p_branch_name     VARCHAR2 (200);
   p_branch_number   VARCHAR2 (200);
   x_branch_id       NUMBER;
   x_end_date        DATE;
BEGIN
   p_bank_id        :=  1245;
   p_branch_name    := 'Test Branch';
   p_branch_number  := '1452789';
   CE_BANK_PUB.CHECK_BRANCH_EXIST
                  (p_bank_id            => p_bank_id,
                   p_branch_name        => p_branch_name,
                   p_branch_number      => p_branch_number,
                   x_branch_id          => x_branch_id,
                   x_end_date           => x_end_date
                  );
   DBMS_OUTPUT.put_line ('X_BRANCH_ID = ' || x_branch_id);
   DBMS_OUTPUT.put_line ('X_END_DATE = ' || x_end_date);
END;

=================================================================================
API to End Date an Internal Bank Branch in R12 - CE_BANK_PUB.SET_BANK_BRANCH_END_DATE=================================================================================

DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_branch_id               NUMBER;
   p_end_date                DATE;
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
BEGIN
   p_init_msg_list         := fnd_api.g_true;
   p_branch_id := 72141; -- CE_BANK_BRANCHES_V.BRANCH_PARTY_ID
   p_end_date              := SYSDATE + 360;
   p_object_version_number := 1;
   CE_BANK_PUB.SET_BANK_BRANCH_END_DATE
             (p_init_msg_list              => p_init_msg_list,
              p_branch_id                  => p_branch_id,
              p_end_date                   => p_end_date,
              p_object_version_number      => p_object_version_number,
              x_return_status              => x_return_status,
              x_msg_count                  => x_msg_count,
              x_msg_data                   => x_msg_data
              );
   DBMS_OUTPUT.put_line (   'P_OBJECT_VERSION_NUMBER = '
                         || p_object_version_number
                        );
   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);
END;
====================
BANK ACCOUNT APIS ====================
=================================================================================
API to Create an Internal Bank Account in R12 CE_BANK_PUB.CREATE_BANK_ACCT================================================================================
DECLARE
   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (200);
   p_count           NUMBER;
 
BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION 
   p_acct_rec.account_owner_party_id := 2041;
   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'USD';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;
 
   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      => p_init_msg_list,
                   p_acct_rec           => p_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
                  );
                        
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_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);
   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;

==========================================================================================
API to update an Internal Bank Account in Oracle Apps R12 (CE_BANK_PUB.UPDATE_BANK_ACCT)==========================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_acct_rec                apps.ce_bank_pub.bankacct_rec_type;
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
   p_count                   NUMBER;
BEGIN
   p_init_msg_list := NULL;
   p_acct_rec.bank_account_id := 41001;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;
   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 18256889;
   p_acct_rec.currency := 'USD';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;
   p_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK_ACCT
           (p_init_msg_list              => p_init_msg_list,
            p_acct_rec                   => p_acct_rec,
            p_object_version_number      => p_object_version_number,
            x_return_status              => x_return_status,
            x_msg_count                  => x_msg_count,
            x_msg_data                   => x_msg_data
           );
   DBMS_OUTPUT.put_line (   'P_OBJECT_VERSION_NUMBER = '
                         || p_object_version_number
                        );
   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);
   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;