Employee Contacts Creation in Oracle HRMS

How to find correct API for Data Uploading

Hi. I have posted the table structure and procedure below. I hope you could create a control file from table structure by yourself.

1) TABLE

CREATE TABLE DEV_EMPLOYEE_CONTACT_INFO

(

  LINE_ID                      NUMBER,

  EMP_CODE                     VARCHAR2(150 BYTE),

  EFFECTIVE_DATE               DATE,

  TITLE                        VARCHAR2(150 BYTE),

  FIRST_NAME                   VARCHAR2(150 BYTE),

  LAST_NAME                    VARCHAR2(150 BYTE),

  GENDER                       VARCHAR2(150 BYTE),

  DATE_OF_BIRTH                DATE,

  NIC                          VARCHAR2(150 BYTE),

  RELATIONSHIP                 VARCHAR2(150 BYTE),

  PHONE_TYPE                   VARCHAR2(150 BYTE),

  PHONE_NUMBER                 VARCHAR2(150 BYTE),

  ADDRESS_TYPE                 VARCHAR2(150 BYTE),

  ADDRESS_LINE1                VARCHAR2(150 BYTE),

  ADDRESS_LINE2                VARCHAR2(150 BYTE),

  ADDRESS_LINE3                VARCHAR2(150 BYTE),

  ADD_PROCESS_FLAG             VARCHAR2(150 BYTE),

  ADD_ERROR_DESCRIPTION        VARCHAR2(150 BYTE),

  CITY                         VARCHAR2(150 BYTE),

  COUNTRY                      VARCHAR2(150 BYTE),

  CONTACT_RELATIONSHIP_ID      NUMBER,

  CONT_REL_PERSON_ID           NUMBER,

  CTR_OBJECT_VERSION_NUMBER    VARCHAR2(150 BYTE),

  CONT_REL_PER_START_DATE      DATE,

  CONT_REL_PER_END_DATE        DATE,

  CONT_REL_PER_OBJ_VER_NUMBER  VARCHAR2(150 BYTE),

  CONT_REL_FULL_NAME           VARCHAR2(150 BYTE),

  CONT_REL_COMMENT_ID          NUMBER,

  CONT_REL_NAME_COMB_WARNING   VARCHAR2(150 BYTE),

  CONT_REL_ORIG_HIRE_WARNING   VARCHAR2(150 BYTE),

  CONT_PH_OBJ_VER_NUMBER       VARCHAR2(150 BYTE),

  CONT_PHONE_ID                NUMBER,

  CONT_PROCESS_FLAG            VARCHAR2(150 BYTE),

  CONT_ERROR_DESCRIPTION       VARCHAR2(150 BYTE),

  PH_PROCESS_FLAG              VARCHAR2(150 BYTE),

  PH_ERROR_DESCRIPTION         VARCHAR2(150 BYTE)

)

2) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_CONTACT

IS

   CURSOR C_CONTACT

   IS

      SELECT LINE_ID, EMP_CODE, EFFECTIVE_DATE,

             TITLE, FIRST_NAME, LAST_NAME, GENDER, NIC, DATE_OF_BIRTH,

             RELATIONSHIP, PHONE_TYPE,

             PHONE_NUMBER, ADDRESS_TYPE, ADDRESS_LINE1, ADDRESS_LINE2,

             ADDRESS_LINE3, CITY, COUNTRY, CONTACT_RELATIONSHIP_ID,

             CTR_OBJECT_VERSION_NUMBER, CONT_REL_PERSON_ID,

             CONT_REL_PER_OBJ_VER_NUMBER, CONT_REL_PER_START_DATE,

             CONT_REL_PER_END_DATE, CONT_REL_FULL_NAME, CONT_REL_COMMENT_ID,

             CONT_REL_NAME_COMB_WARNING, CONT_REL_ORIG_HIRE_WARNING,

             CONT_PH_OBJ_VER_NUMBER, CONT_PHONE_ID, CONT_PROCESS_FLAG,

             CONT_ERROR_DESCRIPTION, PH_PROCESS_FLAG, PH_ERROR_DESCRIPTION

        FROM DEV_EMPLOYEE_CONTACT_INFO

       WHERE NVL (CONT_PROCESS_FLAG, 'N') = 'N' ;

   LC_C_CONTACT                  C_CONTACT%ROWTYPE;

   L_CONTACT_ID                  NUMBER;

   L_PERSON_ID                   NUMBER;

   L_CONTACT_TYPE                VARCHAR2 (240);

   L_SEX_TYPE                    VARCHAR2 (240);

   L_TITLE                       VARCHAR2 (240);

   L_EFFECTIVE_START_DATE        DATE;

   L_EFFECTIVE_END_DATE          DATE;

   L_OBJECT_VERSION_NUMBER       NUMBER;

   ERROR_DESC                    VARCHAR2 (240);

   LV_CONTACT_FLAG               CHAR (1);

   L_SEQ                         VARCHAR2 (20);

   L_PERSONAL_FLAG               CHAR (1);

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

   L_CONTACT_RELATIONSHIP_ID     NUMBER;

   L_CTR_OBJECT_VERSION_NUMBER   NUMBER;

   L_PER_PERSON_ID               NUMBER;

   L_PER_OBJECT_VERSION_NUMBER   NUMBER;

   L_PER_EFFECTIVE_START_DATE    DATE;

   L_PER_EFFECTIVE_END_DATE      DATE;

   L_FULL_NAME                   VARCHAR2 (20);

   L_PER_COMMENT_ID              NUMBER;

   L_NAME_COMBINATION_WARNING    BOOLEAN;

   L_ORIG_HIRE_WARNING           BOOLEAN;

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

BEGIN

   OPEN C_CONTACT;

   LOOP

      FETCH C_CONTACT

       INTO LC_C_CONTACT;

      EXIT WHEN C_CONTACT%NOTFOUND;

      BEGIN

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

-- GET PERSON ID

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

         SELECT PAPF.PERSON_ID

           INTO L_PERSON_ID

           FROM PER_ALL_PEOPLE_F PAPF

          WHERE PAPF.EMPLOYEE_NUMBER = LC_C_CONTACT.EMP_CODE

            AND TO_CHAR (PAPF.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712';

          

            DBMS_OUTPUT.PUT_LINE ('PERSON ID    : ' || L_PERSON_ID);

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

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

-- GET RELATIONSHIP LOOKUP CODE

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

         SELECT LOOKUP_CODE

           INTO L_CONTACT_TYPE

           FROM HR_LOOKUPS

          WHERE LOOKUP_TYPE = 'CONTACT'

            AND UPPER (MEANING) = UPPER (TRIM (LC_C_CONTACT.RELATIONSHIP));

          

            DBMS_OUTPUT.PUT_LINE ('CONTACT TYPE    : ' || L_CONTACT_TYPE);

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

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

-- GET SEX  LOOKUP CODE

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

         SELECT LOOKUP_CODE

           INTO L_SEX_TYPE

           FROM HR_LOOKUPS

          WHERE LOOKUP_TYPE = 'SEX'

            AND UPPER (MEANING) LIKE UPPER (TRIM (LC_C_CONTACT.GENDER));

          

            DBMS_OUTPUT.PUT_LINE ('SEX    : ' || L_SEX_TYPE);

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

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

-- GET TITLE LOOKUP CODE

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

         SELECT LOOKUP_CODE

           INTO L_TITLE

           FROM HR_LOOKUPS

          WHERE LOOKUP_TYPE = 'TITLE'

            AND UPPER (MEANING) = UPPER (TRIM (LC_C_CONTACT.TITLE));

          

            DBMS_OUTPUT.PUT_LINE ('TITLE    : ' || L_TITLE);

        IF LC_C_CONTACT.RELATIONSHIP IN ('PARENT','CHILD','SPOUSE') THEN

        L_PERSONAL_FLAG := 'Y' ;

        ELSE

        L_PERSONAL_FLAG := 'N' ;

        END IF;

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

         HR_CONTACT_REL_API.CREATE_CONTACT

                  (P_VALIDATE                       => FALSE,

                   P_START_DATE                     => LC_C_CONTACT.EFFECTIVE_DATE,

                   P_BUSINESS_GROUP_ID              => 0,

                   P_PERSON_ID                      => L_PERSON_ID,

                   P_CONTACT_TYPE                   => L_CONTACT_TYPE,

                   P_TITLE                          => L_TITLE,

                   P_FIRST_NAME                     => LC_C_CONTACT.FIRST_NAME,

                   P_LAST_NAME                      => LC_C_CONTACT.LAST_NAME,

                   P_SEX                            => L_SEX_TYPE,

                   P_NATIONAL_IDENTIFIER            => LC_C_CONTACT.NIC,

                   P_PERSONAL_FLAG                  => L_PERSONAL_FLAG,

                   P_DATE_OF_BIRTH                  => LC_C_CONTACT.DATE_OF_BIRTH,

                   P_CONTACT_RELATIONSHIP_ID        => L_CONTACT_RELATIONSHIP_ID,

                   P_CTR_OBJECT_VERSION_NUMBER      => L_CTR_OBJECT_VERSION_NUMBER,

                   P_PER_PERSON_ID                  => L_PER_PERSON_ID,

                   P_PER_OBJECT_VERSION_NUMBER      => L_PER_OBJECT_VERSION_NUMBER,

                   P_PER_EFFECTIVE_START_DATE       => L_PER_EFFECTIVE_START_DATE,

                   P_PER_EFFECTIVE_END_DATE         => L_PER_EFFECTIVE_END_DATE,

                   P_FULL_NAME                      => L_FULL_NAME,

                   P_PER_COMMENT_ID                 => L_PER_COMMENT_ID,

                   P_NAME_COMBINATION_WARNING       => L_NAME_COMBINATION_WARNING,

                   P_ORIG_HIRE_WARNING              => L_ORIG_HIRE_WARNING

                  );

         IF L_CONTACT_RELATIONSHIP_ID IS NOT NULL

         THEN

            LV_CONTACT_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_CONTACT_FLAG = 'Y'

         THEN

            UPDATE DEV_EMPLOYEE_CONTACT_INFO

               SET CONT_ERROR_DESCRIPTION = ERROR_DESC,

                   CONT_PROCESS_FLAG = LV_CONTACT_FLAG,

                   CONTACT_RELATIONSHIP_ID = L_CONTACT_RELATIONSHIP_ID,

                   CONT_REL_PERSON_ID = L_PER_PERSON_ID,

                   CTR_OBJECT_VERSION_NUMBER = L_CTR_OBJECT_VERSION_NUMBER

             WHERE LINE_ID = LC_C_CONTACT.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_EMPLOYEE_CONTACT_INFO

               SET CONT_ERROR_DESCRIPTION = ERROR_DESC,

                   CONT_PROCESS_FLAG = 'N'

             WHERE LINE_ID = LC_C_CONTACT.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('CONTRACT ID    : ' || L_CONTACT_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_CONTACT_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_EMPLOYEE_CONTACT_INFO

               SET CONT_ERROR_DESCRIPTION = ERROR_DESC,

                   CONT_PROCESS_FLAG = LV_CONTACT_FLAG

             WHERE LINE_ID = LC_C_CONTACT.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_CONTACT_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_PER_EFFECTIVE_START_DATE := '';

      L_PER_EFFECTIVE_END_DATE := '';

      ERROR_DESC := '';

      LV_CONTACT_FLAG := '';

     

   --COMMIT;

  

   END LOOP;

   CLOSE C_CONTACT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

Leave a comment for any query.