How to upload data in Special Information type in Oracle HRMS

Table Definition:

CREATE  TABLE DEV_SIT

(

  LINE_ID                     NUMBER,

  EMPLOYEE_NUM                VARCHAR2(240 BYTE),

  SIT_NAME                    VARCHAR2(240 BYTE),

  EFFECTIVE_DATE              DATE,

  DATE_FROM                   DATE,

  DATE_TO                     DATE,

  ATTRIBUTE_CATEGORY          VARCHAR2(240 BYTE),

  ATTRIBUTE1                  VARCHAR2(240 BYTE),

  ATTRIBUTE2                  VARCHAR2(240 BYTE),

  SEGMENT1                    VARCHAR2(240 BYTE),

  SEGMENT2                    VARCHAR2(240 BYTE),

  SEGMENT3                    VARCHAR2(240 BYTE),

  CONCAT_SEGMENTS             VARCHAR2(240 BYTE),

  ERROR_DESCRIPTION           VARCHAR2(240 BYTE),

  P_ANALYSIS_CRITERIA_ID      NUMBER,

  P_PERSON_ANALYSIS_ID        NUMBER,

  P_PEA_OBJECT_VERSION_NUMBER NUMBER

)

Procedure:

Note: You can increase number of segments and DFF segments. You need to update procedure according to your requirement.

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_SIT

IS

   CURSOR C_SIT

   IS     

           SELECT LINE_ID, EMPLOYEE_NUM, SIT_NAME, EFFECTIVE_DATE, DATE_FROM,

               DATE_TO, ATTRIBUTE_CATEGORY ,ATTRIBUTE1, ATTRIBUTE2, SEGMENT1, SEGMENT2, SEGMENT3,

               CONCAT_SEGMENTS

          FROM DEV_SIT;

   LC_C_SIT                C_SIT%ROWTYPE;

   L_ID_FLEX_NUM             NUMBER;

   L_PERSON_ID               NUMBER;

   L_NAME                    VARCHAR2 (240);

   ERROR_DESC                VARCHAR2 (240);

   LV_SIT_FLAG                 CHAR (1);

   L_OBJECT_VERSION_NUMBER     NUMBER;

   L_ANALYSIS_CRITERIA_ID      NUMBER;

   L_PERSON_ANALYSIS_ID        NUMBER;

   L_PEA_OBJECT_VERSION_NUMBER NUMBER;

  

BEGIN

   

   

   OPEN C_SIT;

   LOOP

      FETCH C_SIT

       INTO LC_C_SIT;

      EXIT WHEN C_SIT%NOTFOUND;

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

-- SIT NAME

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

    SELECT FI.ID_FLEX_NUM

      INTO L_ID_FLEX_NUM

      FROM FND_ID_FLEX_STRUCTURES_VL FI

     WHERE (UPPER (FI.ID_FLEX_STRUCTURE_CODE) = UPPER (LC_C_SIT.SIT_NAME))

       AND (APPLICATION_ID = 800)

       AND (ID_FLEX_CODE = 'PEA');

      

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

-- PERSON ID

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

     

        SELECT PPF.PERSON_ID

          INTO L_PERSON_ID

          FROM PER_PEOPLE_F PPF

         WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE

         AND PPF.EMPLOYEE_NUMBER = LC_C_SIT.EMPLOYEE_NUM;

       

       

      BEGIN

         HR_SIT_API.CREATE_SIT

                        (P_VALIDATE                   => FALSE,

                         P_PERSON_ID                  => L_PERSON_ID,

                         P_BUSINESS_GROUP_ID          => 2217,--FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),

                         P_ID_FLEX_NUM                => L_ID_FLEX_NUM,

                         P_DATE_FROM                  => LC_C_SIT.DATE_FROM,

                         P_DATE_TO                    => LC_C_SIT.DATE_TO,

                         P_EFFECTIVE_DATE             => LC_C_SIT.EFFECTIVE_DATE,

                         P_SEGMENT1                   => LC_C_SIT.SEGMENT1,

                         P_SEGMENT2                   => LC_C_SIT.SEGMENT2,

                         P_SEGMENT3                   => LC_C_SIT.SEGMENT3,

                         --,P_CONCAT_SEGMENTS           =>

                         P_ATTRIBUTE_CATEGORY         => LC_C_SIT.ATTRIBUTE_CATEGORY,

                         P_ATTRIBUTE1                 => LC_C_SIT.ATTRIBUTE1,

                         P_ATTRIBUTE2                 => LC_C_SIT.ATTRIBUTE2,                        

                         P_ANALYSIS_CRITERIA_ID       => L_ANALYSIS_CRITERIA_ID,

                         P_PERSON_ANALYSIS_ID         => L_PERSON_ANALYSIS_ID,

                         P_PEA_OBJECT_VERSION_NUMBER  => L_PEA_OBJECT_VERSION_NUMBER

                        

                        );

         IF L_PERSON_ANALYSIS_ID IS NOT NULL

         THEN

            LV_SIT_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         END IF;

         IF LV_SIT_FLAG = 'Y'

         THEN

            UPDATE DEV_SIT

               SET ERROR_DESCRIPTION = ERROR_DESC,

                   P_ANALYSIS_CRITERIA_ID = L_ANALYSIS_CRITERIA_ID,

                   P_PEA_OBJECT_VERSION_NUMBER = L_PEA_OBJECT_VERSION_NUMBER,

                   P_PERSON_ANALYSIS_ID = L_PERSON_ANALYSIS_ID

             WHERE LINE_ID = LC_C_SIT.LINE_ID;

         ELSE

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_SIT

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_SIT.LINE_ID;

         END IF;

        

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_SIT_FLAG := 'N';

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_SIT

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_SIT.LINE_ID;

            DBMS_OUTPUT.PUT_LINE (SQLERRM);

      END;

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

      LV_SIT_FLAG := '';

      L_OBJECT_VERSION_NUMBER  :=   '';

      L_ANALYSIS_CRITERIA_ID   :=   '';

      L_PERSON_ANALYSIS_ID     :=  '';

      L_PEA_OBJECT_VERSION_NUMBER := '';

  

--   COMMIT;

   END LOOP;

   CLOSE C_SIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

Kindly Provide feedback.  Ask questions for any query.