Why is the element LOV empty on the element entries form when using a Secure User

If you have run all the secure user processes and the secure user can access appropriate employees but when they try to access the LOV of the element entries form, there is nothing in the LOV.

Have you created synonyms for each secure user for the HR_LOOKUPS object. You need to  drop the synonym for each secure user. You should now be able to access the elements LOV.

The following tables are accessed by the element entries LOV (sql stmt):

• PAY_ELE_CLASSIFICATION_RULES
• PAY_ELEMENT_SETS,
• PAY_ELEMENT_TYPE_RULES
• PAY_INPUT_VALUES_F
• HR_LOOKUPS
• PAY_PAYWSMEE_ELEMENTS_LOV

Verify that the secure has grants to all these objects.

select * from dba_tab_privs where table_name =
and object_type = 'SYNONYM' ;

In this case, there was a public synonym for HR_LOOKUPS but also had individual synonyms owned by each of the secure users (not HR_SECURE_USER but the secure user names created). That means that the system was using the synonym, within their own schema not the public one.

After dropping the synonym you can now access the LOV;

SQLPLUS> drop synonym schema.synonym name

Read more

Location in Oracle HRMS

How to upload Locations in Oracle HRMS??

Here is the solution.

TABLE

CREATE TABLE DEV_LOCATION

(

  LINE_ID               NUMBER,

  EFFECTIVE_START_DATE  DATE,

  LOCATION_NAME         VARCHAR2(240 BYTE),

  DESCRIPTION           VARCHAR2(240 BYTE),

  ADDRESS_LINE1         VARCHAR2(240 BYTE),

  ADDRESS_LINE2         VARCHAR2(240 BYTE),

  ADDRESS_LINE3         VARCHAR2(240 BYTE),

  CITY                  VARCHAR2(100 BYTE),

  PROVINCE              VARCHAR2(100 BYTE),

  COUNTRY               VARCHAR2(100 BYTE),

  POSTAL_CODE           VARCHAR2(100 BYTE),

  TELEPHONE             VARCHAR2(100 BYTE),

  FAX                   VARCHAR2(20 BYTE),

  PROCESS_FLAG          CHAR(1 BYTE),

  ERROR_DESCRIPTION     CHAR(240 BYTE),

  LOCATION_ID           NUMBER,

  OBJ_VER_NUMBER        NUMBER

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_LOCATION

IS

   CURSOR c_loc

   IS

      SELECT line_id, effective_start_date, location_name, description,

             address_line1, address_line2, address_line3, city, province,country,postal_code,

             telephone, fax, process_flag, error_description, location_id,

             obj_ver_number

        FROM dev_location;

       --WHERE NVL (process_flag, 'N') <> 'Y' AND error_description IS NULL;

   lc_c_loc                  c_loc%ROWTYPE;

   l_location_id             NUMBER;

   l_object_version_number   NUMBER;

   error_desc                VARCHAR2 (240);

   lv_loc_flag               CHAR (1);

   l_city                    VARCHAR2 (20);

   l_country                 VARCHAR2 (30);

   l_type                    VARCHAR2 (20);

BEGIN

   OPEN c_loc;

   LOOP

      FETCH c_loc

       INTO lc_c_loc;

      EXIT WHEN c_loc%NOTFOUND;

    

---------------------------

--- Country Code

---------------------------

     IF lc_c_loc.country IS NOT NULL THEN

        SELECT ftv.territory_code

           INTO l_country

           FROM fnd_territories_vl ftv

          WHERE UPPER (ftv.territory_short_name) LIKE UPPER (TRIM (lc_c_loc.country));

         

      ELSE

         l_country := '';

      END IF;

      BEGIN

         hr_location_api.create_location

                          (p_validate                   => FALSE,

                           p_effective_date             => lc_c_loc.effective_start_date,

                           p_location_code              => lc_c_loc.location_name,

                           p_description                => lc_c_loc.description,

                           p_address_line_1             => lc_c_loc.address_line1,

                           p_address_line_2             => lc_c_loc.address_line2,

                           p_address_line_3             => lc_c_loc.address_line3,

                           p_postal_code                => lc_c_loc.postal_code,

                           p_town_or_city               => lc_c_loc.city,

                           p_region_1                   => lc_c_loc.province,

                           p_country                    => l_country,

                           p_telephone_number_1         => lc_c_loc.telephone,

                           p_telephone_number_3         => lc_c_loc.fax,

                           p_business_group_id          => 2217,                            

                           p_style                      => 'PK',

                           p_location_id                => l_location_id,

                           p_object_version_number      => l_object_version_number

                          );

         IF l_location_id IS NOT NULL

         THEN

            lv_loc_flag := 'Y';

            error_desc := 'No Error';

         END IF;

         IF lv_loc_flag = 'Y'

         THEN

            UPDATE dev_location

               SET error_description = error_desc,

                   process_flag = lv_loc_flag,

                   location_id = l_location_id,

                   obj_ver_number = l_object_version_number

             WHERE line_id = lc_c_loc.line_id;

         ELSE

            error_desc := error_desc || SQLERRM;

            UPDATE dev_location

               SET error_description = error_desc,

                   process_flag = 'N'

             WHERE line_id = lc_c_loc.line_id;

         END IF;

         DBMS_OUTPUT.put_line ('Location ID    : ' || l_location_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            lv_loc_flag := 'N';

            error_desc := error_desc || SQLERRM;

            UPDATE dev_location

               SET error_description = error_desc,

                   process_flag = lv_loc_flag

             WHERE line_id = lc_c_loc.line_id;

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

      lv_loc_flag := '';

      l_location_id := '';

      l_object_version_number := '';

      error_desc := '';

  

   END LOOP;

   CLOSE c_loc;

--dbms_output.put_line('Location ID        : '||l_location_id);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/

How to create jobs in Oracle HRMS

Leave comment for any query.

Read more