Employee Address Procedure in Oracle HRMS

How to find correct API for Data Uploading

Kindly make required changes, if any, according to your requirement/setup.

Kindly create control file by yourself

How to create Control file for Sql Loader

Here we go!!!

1) TABLE

CREATE TABLE DEV_EMPLOYEE_ADDRESS

(

  LINE_ID            NUMBER(15),

  EMP_CODE           VARCHAR2(150 BYTE),

  EMP_NAME           VARCHAR2(150 BYTE),

  ADDRESS_LINE1      VARCHAR2(150 BYTE),

  ADDRESS_LINE2      VARCHAR2(150 BYTE),

  ADDRESS_LINE3      VARCHAR2(150 BYTE),

  CITY               VARCHAR2(150 BYTE),

  POSTAL_CODE        VARCHAR2(150 BYTE),

  COUNTRY            VARCHAR2(150 BYTE),

  EFFECTIVE_DATE     DATE,

  PRIMARY_FLAG       VARCHAR2(150 BYTE),

  PERM_PRES_FLAG     VARCHAR2(150 BYTE),

  PERSONAL_EMAIL     VARCHAR2(150 BYTE),

  PROCESS_FLAG       VARCHAR2(150 BYTE),

  ERROR_DESCRIPTION  VARCHAR2(150 BYTE),

  ADDRESS_ID         NUMBER(15),

  OBJ_VER_NUMBER     NUMBER(15)

)

2) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_EMP_ADDRESS

IS

   CURSOR C_EMP_ADD

   IS

      SELECT   LINE_ID, EMP_CODE, ADDRESS_LINE1, ADDRESS_LINE2,

               ADDRESS_LINE3, CITY, POSTAL_CODE, COUNTRY, EFFECTIVE_DATE,

               PRIMARY_FLAG, PERM_PRES_FLAG, PERSONAL_EMAIL, PROCESS_FLAG,

               ERROR_DESCRIPTION, ADDRESS_ID, OBJ_VER_NUMBER

          FROM DEV_EMPLOYEE_ADDRESS;

--         WHERE NVL (PROCESS_FLAG, 'N') <> 'Y'

--           AND ADDRESS_LINE1 IS NOT NULL

--           AND ERROR_DESCRIPTION IS NULL

--      ORDER BY PRIMARY_FLAG DESC, LINE_ID;

   LC_C_EMP_ADD              C_EMP_ADD%ROWTYPE;

   L_ADDRESS_ID              NUMBER;

   L_OBJECT_VERSION_NUMBER   NUMBER;

   ERROR_DESC                VARCHAR2 (240);

   LV_ADD_FLAG               CHAR (1);

   L_CITY                    VARCHAR2 (20);

   L_COUNTRY                 VARCHAR2 (30);

   L_TYPE                    VARCHAR2 (20);

   L_PERSON_ID               NUMBER;

   L_FLAG                    CHAR (1);

BEGIN

   OPEN C_EMP_ADD;

   LOOP

      FETCH C_EMP_ADD

       INTO LC_C_EMP_ADD;

      EXIT WHEN C_EMP_ADD%NOTFOUND;

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

--- TYPE CODE

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

      SELECT LOOKUP_CODE

        INTO L_TYPE

        FROM HR_LOOKUPS

       WHERE LOOKUP_TYPE = 'ADDRESS_TYPE'

         AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_EMP_ADD.PERM_PRES_FLAG));

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

--- PERSON ID

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

      BEGIN

         SELECT DISTINCT PAPF.PERSON_ID

                    INTO L_PERSON_ID

                    FROM PER_ALL_PEOPLE_F PAPF

                   WHERE PAPF.EMPLOYEE_NUMBER = LC_C_EMP_ADD.EMP_CODE;

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

            LV_ADD_FLAG := 'N';

            ERROR_DESC := 'NO SUCH EMPLOYEE';

         WHEN TOO_MANY_ROWS

         THEN

            LV_ADD_FLAG := 'N';

            ERROR_DESC :=

                     'SAME EMPLOYEE NUMBER IS ASSIGNED TO MULTIPLE EMPLOYEES';

      END;

      IF (UPPER (TRIM (LC_C_EMP_ADD.PRIMARY_FLAG)) = 'Y')

      THEN

         L_FLAG := 'Y';

      ELSE

         L_FLAG := 'N';

      END IF;

      BEGIN

         HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

            (P_VALIDATE                   => FALSE

                                                  -- , P_VALIDATE_COUNTY                   => FALSE

         ,

             P_PERSON_ID                  => L_PERSON_ID,

             P_DATE_FROM                  => LC_C_EMP_ADD.EFFECTIVE_DATE    ,

             P_ADDRESS_LINE1              => LC_C_EMP_ADD.ADDRESS_LINE1     ,

             P_ADDRESS_LINE2              => LC_C_EMP_ADD.ADDRESS_LINE2      ,

             P_ADDRESS_LINE3              => LC_C_EMP_ADD.ADDRESS_LINE3     ,

             P_TOWN_OR_CITY               => LC_C_EMP_ADD.CITY              ,

             P_POSTAL_CODE                => LC_C_EMP_ADD.POSTAL_CODE       ,

             P_COUNTRY                    => LC_C_EMP_ADD.COUNTRY           ,

             --P_ADD_INFORMATION13          => '@'--NVL(LC_C_EMP_ADD.PERSONAL_EMAIL,'ABC@YAHOO.COM')   ,

             P_EFFECTIVE_DATE             => LC_C_EMP_ADD.EFFECTIVE_DATE    ,

             P_PRIMARY_FLAG               => L_FLAG,

             P_ADDRESS_TYPE               => L_TYPE,

             P_STYLE                      => 'PK_LOC',

             P_ADDRESS_ID                 => L_ADDRESS_ID,

             P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER

            );

         IF L_ADDRESS_ID IS NOT NULL

         THEN

            LV_ADD_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_ADD_FLAG = 'Y'

         THEN

            UPDATE DEV_EMPLOYEE_ADDRESS

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = LV_ADD_FLAG,

                   ADDRESS_ID = L_ADDRESS_ID,

                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER

             WHERE EMP_CODE = LC_C_EMP_ADD.EMP_CODE

               AND LINE_ID = LC_C_EMP_ADD.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_EMPLOYEE_ADDRESS

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = 'N'

             WHERE EMP_CODE = LC_C_EMP_ADD.EMP_CODE

               AND LINE_ID = LC_C_EMP_ADD.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('ADDRESS ID    : ' || L_ADDRESS_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_ADD_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_EMPLOYEE_ADDRESS

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   PROCESS_FLAG = LV_ADD_FLAG

             WHERE EMP_CODE = LC_C_EMP_ADD.EMP_CODE

               AND LINE_ID = LC_C_EMP_ADD.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      DBMS_OUTPUT.PUT_LINE ('ADDRESS ID    : ' || L_ADDRESS_ID);

      LV_ADD_FLAG := '';

      L_ADDRESS_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

      COMMIT;

   END LOOP;

   CLOSE C_EMP_ADD;

END;

/

Feedback is much appreciated.