Thursday, January 1, 2015

API to Create External Bank

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks that then can be assigned to customers (AR) or Suppliers (AP).

DECLARE
   LC_OUTPUT          VARCHAR2 (3000);
   LC_MSG_DUMMY       VARCHAR2 (3000);
   LC_RETURN_STATUS   VARCHAR2 (3000);
   LC_MSG_DATA        VARCHAR2 (3000);

   LN_BANK_ID         NUMBER;
   LN_MSG_COUNT       NUMBER;
   LR_EXTBANK_REC     APPS.IBY_EXT_BANKACCT_PUB.EXTBANK_REC_TYPE;
   LR_RESPONSE_REC    APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;

   V_COUNTRY_CODE     VARCHAR2 (100);

   CURSOR C1
   IS
      SELECT DISTINCT BANK_COUNTRY, BANK_NUMBER, BANK_NAME
                FROM xx_customer_tmp_amm_t
               WHERE     BANK_COUNTRY IS NOT NULL
                     AND BANK_NUMBER IS NOT NULL
                     AND BANK_NAME IS NOT NULL
                     and O_BANK_UPLOAD_STATUS is null;
BEGIN
   LC_RETURN_STATUS := '';
   LN_MSG_COUNT := '';
   LC_MSG_DATA := '';


   APPS.FND_MSG_PUB.DELETE_MSG (NULL);
   APPS.FND_MSG_PUB.INITIALIZE ();

   FOR REC1 IN C1
   LOOP
      BEGIN
         SELECT LOOKUP_CODE
           INTO V_COUNTRY_CODE
           FROM FND_LOOKUP_VALUES
          WHERE     LOOKUP_TYPE = 'GHR_US_POSTAL_COUNTRY_CODE'
                AND LANGUAGE = 'US'
                AND MEANING = TRIM (REC1.BANK_COUNTRY);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('Bank_Country name is not Correct ');
      END;

      LR_EXTBANK_REC.BANK_NAME := REC1.BANK_NAME;
      LR_EXTBANK_REC.BANK_NUMBER := REC1.BANK_NUMBER;
      LR_EXTBANK_REC.COUNTRY_CODE := V_COUNTRY_CODE;
      IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK ( -- ------------------------------
         -- Input data elements
         -- ------------------------------
         P_API_VERSION     => 1.0,
         P_INIT_MSG_LIST   => FND_API.G_TRUE,
         P_EXT_BANK_REC    => LR_EXTBANK_REC,
         -- --------------------------------
         -- Output data elements
         -- --------------------------------
         X_BANK_ID         => LN_BANK_ID,
         X_RETURN_STATUS   => LC_RETURN_STATUS,
         X_MSG_COUNT       => LN_MSG_COUNT,
         X_MSG_DATA        => LC_MSG_DATA,
         X_RESPONSE        => LR_RESPONSE_REC);

      LC_OUTPUT := ' ';

      IF (LC_RETURN_STATUS <> 'S')
      THEN
         FOR I IN 1 .. LN_MSG_COUNT
         LOOP
            APPS.FND_MSG_PUB.GET (I,
                                  APPS.FND_API.G_FALSE,
                                  LC_MSG_DATA,
                                  LC_MSG_DUMMY);

            LC_OUTPUT :=
               LC_OUTPUT
               || (TO_CHAR (I) || ': ' || SUBSTR (LC_MSG_DATA, 1, 250));
         END LOOP;

         APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.OUTPUT,
                                 'Error Occured while Creating Bank: ');
      END IF;

      IF (LC_RETURN_STATUS = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

   And  Here is a Query that u can use to see uploaded banks.

SELECT BankParty.*
  FROM HZ_PARTIES BankParty, HZ_CODE_ASSIGNMENTS BankCA
 WHERE     BankParty.PARTY_TYPE = 'ORGANIZATION'
       AND NVL (BankParty.status, 'A') = 'A'
       AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
       AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
       AND NVL (BankCA.STATUS, 'A') = 'A'
       AND bankparty.created_by = -1;

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.      

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete