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.



0 comments: