API To Create Employee Qualification

Salam Alaikum,

   I share this API, that creates an employee qualification, but please note that it needs that some prerequisites for this API is needed. Like qualification type should be already created and qualification_type_id should be input to the create_qualification API. As for establishment and establishment_id to be input for the API. And lastly, the employee should be an attendant of the establishment and attendance_id should be input to the API.

The following queries might help to understand the API more clearly:

SELECT qualification_type_id, NAME
  FROM per_qualification_types_tl
 WHERE 1 = 1 AND LANGUAGE = 'US';

SELECT establishment_id,NAME
  FROM per_establishments
 WHERE 1 = 1;

SELECT attendance_id
  FROM per_establishment_attend_v
 WHERE     1 = 1
       AND person_id = 'xx'
       AND establishment_id = 'establishment_id';

   And this is the API:

DECLARE
   l_qualification_id        NUMBER;
   l_object_version_number   NUMBER;
  
   CURSOR per_edu
   IS
      SELECT xx_edu.ROWID,
             xx_edu.*
        FROM xxx_emp_edu_school_info xx_edu
       WHERE 1 = 1 AND NVL (upload_status, 'N') = 'N';

BEGIN
   FOR i_rec IN per_edu
   LOOP
      fnd_profile.put ('PER_PERSON_ID', i_rec.person_id);
      per_qualifications_api.create_qualification
          (p_validate                      => FALSE,
           p_effective_date                => SYSDATE,
           p_qualification_type_id         => i_rec.qualification_type_id,
           p_attendance_id                 => i_rec.attendance_id,
           p_business_group_id             => fnd_profile.VALUE
                                                      ('PER_BUSINESS_GROUP_ID'),
           p_person_id                     => i_rec.person_id,
           p_awarded_date                  => SYSDATE,
           p_start_date                    => SYSDATE,
           p_qua_information_category      => 'SA',
           p_qua_information1              => 'EARENED',
           p_qualification_id              => l_qualification_id,
           p_object_version_number         => l_object_version_number
          );

      UPDATE xxx_emp_edu_school_info
         SET upload_status = 'Y',
             qualification_id = l_qualification_id
       WHERE ROWID = i_rec.ROWID;
      
       commit;
   END LOOP;
END;

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

API to Create External Bank Branch

Salam Alaikum,

   I share with you guys this API, that i use to create External Banks Branches , please note that external bank should exist and bank_id should be passed to this API.

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;
   x_branch_id             NUMBER;
   p_count                 NUMBER;

   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
   FOR REC1 IN C1
   LOOP
      p_ext_bank_branch_rec.bch_object_version_number := 1.0;
      p_ext_bank_branch_rec.branch_name := rec1.BANK_BRANCH_NAME;
      p_ext_bank_branch_rec.branch_type := 'ABA';
      p_ext_bank_branch_rec.branch_number := rec1.BRANCH_NUMBER;
      p_ext_bank_branch_rec.bank_party_id := rec1.NEW_BANK_ID;


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


      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;

      IF (x_return_status = 'S')
      THEN
              --         COMMIT;
      END IF;
   END LOOP;
END;

     And  Here is a Query that you can use to see uploaded bank branches.

SELECT BranchParty.PARTY_ID Branch_Party_Id,
       BranchParty.PARTY_NAME Bank_Branch_Name,
       BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,
       BranchCA.start_date_active Start_Date,
       BranchCA.end_date_active End_Date
  FROM HZ_PARTIES BranchParty, HZ_CODE_ASSIGNMENTS BranchCA
 WHERE     1 = 1
       AND BranchParty.PARTY_TYPE = 'ORGANIZATION'
       AND BranchParty.status = 'A'
       AND BranchCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
       AND BranchCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH')
       AND BranchCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
       AND (BranchCA.STATUS = 'A' OR BranchCA.STATUS IS NULL)
       AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID
       AND BranchParty.created_by = -1;

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

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.      

Concurrent & Responsibilities query

Salam Alaikum,

   I share with you guys this query that i use to get report information, it can also give you all the reports available for a specific user and by which responsibilities. And it can be used to get all the reports attached to a certain responsibility via its request group.

SELECT a.user_concurrent_program_name,
       b.request_group_name,
       e.responsibility_name
  FROM fnd_concurrent_programs_tl a,
       fnd_request_groups b,
       fnd_request_group_units c,
       fnd_responsibility d,
       fnd_responsibility_tl e,
       fnd_user_resp_groups_direct f,
       fnd_user g,
       fnd_concurrent_programs h
 WHERE     a.concurrent_program_id = c.request_unit_id
       AND a.concurrent_program_id = h.concurrent_program_id
       AND b.request_group_id = c.request_group_id
       AND b.request_group_id = d.request_group_id
       AND d.responsibility_id = e.responsibility_id
       AND b.application_id = c.application_id
       AND d.application_id = e.application_id
       AND f.user_id = g.user_id
       AND f.responsibility_id = e.responsibility_id
       AND UPPER (a.user_concurrent_program_name) LIKE
            '%' || UPPER (:p_concurrent_program_name) || '%'
       AND g.user_name = NVL (UPPER (:p_user_name), g.user_name)
       AND e.language = NVL (UPPER (:p_language), USERENV ('LANG'))
       AND SYSDATE BETWEEN NVL (
                            f.start_date,
                            fnd_date.canonical_to_date (
                             '0001/01/01 00:00:00'))
                       AND NVL (
                            f.end_date,
                            fnd_date.canonical_to_date (
                             '4000/12/31 23:59:59'));

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

Query to get all Business Group's Mapped with thier Legal Entities

Salam Alaikum,

   I share with you guys this query that can used to get Business group information, along with their Legal entities.

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE     lep.transacting_entity_flag = 'Y'
       AND lep.party_id = hzp.party_id
       AND lep.legal_entity_id = reg.source_id
       AND reg.source_table = 'XLE_ENTITY_PROFILES'
       AND hrl.location_id = reg.location_id
       AND reg.identifying_flag = 'Y'
       AND ter.territory_code = hrl.country
       AND lep.legal_entity_id = hro.default_legal_context_id
       AND gloperatingunitseo.organization_id = hro.organization_id
       AND hroutl_bg.organization_id = hro.business_group_id
       AND hroutl_ou.organization_id = hro.organization_id
       AND glev.legal_entity_id = lep.legal_entity_id;

Note: the query might need mild modification based on the setup you have.

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