Element Link Creation Procedure in Oracle Payroll

How to find correct API for Data Uploading

Please note that number of segments of General Ledger can be different for each company, i have provided 8 segments(Costing, Balancing) for now, kindly change table definition and procedure accordingly.

Kindly create control file using table structure.

How to create Control file for Sql Loader

1) TABLE



CREATE TABLE DEV_ELEMENTS_LINK
(
  LINE_ID                       NUMBER,
  EFFECTIVE_DATE                DATE,
  ELEMENT_TYPE_ID               NUMBER,
  ELEMENT_TYPE_NAME             VARCHAR2(240 BYTE),
  DESCRIPTION                   VARCHAR2(240 BYTE),
  QUALIFYING_LENGTH_OF_SERVICE  VARCHAR2(240 BYTE),
  QUALIFYING_UNITS              VARCHAR2(240 BYTE),
  STANDARD_LINK_FLAG            VARCHAR2(240 BYTE),
  ORG_NAME                      VARCHAR2(240 BYTE),
  PEOPLE_GROUP_NAME             VARCHAR2(240 BYTE),
  SALARY_BASES                  VARCHAR2(240 BYTE),
  LOCATION_NAME                 VARCHAR2(240 BYTE),
  PAYROLL_ID                    NUMBER,
  PAYROLL_NAME                  VARCHAR2(240 BYTE),
  GRADE_ID                      NUMBER,
  GRADE_NAME                    VARCHAR2(240 BYTE),
  EMPLOYMENT_CATEGORY           VARCHAR2(240 BYTE),
  COSTABLE_TYPE                 VARCHAR2(240 BYTE),
  TRANSFER_GL_FLAG              VARCHAR2(240 BYTE),
  COSTING_ALLOC_FLEXFIELD_ID    NUMBER,
  ELEMENT_LINK_ID_OUT           NUMBER,
  COMMENT_ID                    NUMBER,
  OBJ_VER_NUMBER                NUMBER,
  EFFECTIVE_START_DATE          DATE,
  EFFECTIVE_END_DATE            DATE,
  P_COST_SEGMENT1               VARCHAR2(240 BYTE),
  P_COST_SEGMENT2               VARCHAR2(240 BYTE),
  P_COST_SEGMENT3               VARCHAR2(240 BYTE),
  P_COST_SEGMENT4               VARCHAR2(240 BYTE),
  P_COST_SEGMENT5               VARCHAR2(240 BYTE),
  P_COST_SEGMENT6               VARCHAR2(240 BYTE),
  P_COST_SEGMENT7               VARCHAR2(240 BYTE),
  P_COST_SEGMENT8               VARCHAR2(240 BYTE),
  COST_CONCAT_SEGMENT           VARCHAR2(240 BYTE),
  BALANCE_FLEXFIELD_ID          NUMBER,
  P_BAL_SEGMENT1                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT2                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT3                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT4                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT5                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT6                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT7                VARCHAR2(240 BYTE),
  P_BAL_SEGMENT8                VARCHAR2(240 BYTE),
  BALANCE_CONCAT_SEGMENT        VARCHAR2(240 BYTE),
  BUSINESS_GROUP_ID             NUMBER,
  ERROR_DESCRIPTION             VARCHAR2(240 BYTE),
  PROCESS_FLAG                  VARCHAR2(240 BYTE)
)


2) PROCEDURE


CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ELEMENTS_LINK
IS
   CURSOR C_COMP
   IS
      SELECT   DISTINCT LINE_ID, EFFECTIVE_DATE, ELEMENT_TYPE_ID, ELEMENT_TYPE_NAME,
               DESCRIPTION, QUALIFYING_LENGTH_OF_SERVICE, STANDARD_LINK_FLAG,
               PAYROLL_ID, PAYROLL_NAME, GRADE_ID, GRADE_NAME,
               EMPLOYMENT_CATEGORY, COSTABLE_TYPE, TRANSFER_GL_FLAG,
               COSTING_ALLOC_FLEXFIELD_ID, ELEMENT_LINK_ID_OUT, COMMENT_ID,
               OBJ_VER_NUMBER, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,
               PROCESS_FLAG, ERROR_DESCRIPTION, BALANCE_FLEXFIELD_ID,
               P_COST_SEGMENT1, P_COST_SEGMENT2, P_COST_SEGMENT3,
               P_COST_SEGMENT4, P_COST_SEGMENT5,P_COST_SEGMENT6,P_COST_SEGMENT7,
               P_COST_SEGMENT8, P_BAL_SEGMENT1,P_BAL_SEGMENT2, P_BAL_SEGMENT3,
               P_BAL_SEGMENT4,P_BAL_SEGMENT5,P_BAL_SEGMENT6, P_BAL_SEGMENT7, P_BAL_SEGMENT8,
               QUALIFYING_UNITS, BUSINESS_GROUP_ID,
               COST_CONCAT_SEGMENT, BALANCE_CONCAT_SEGMENT, ORG_NAME,
               LOCATION_NAME, PEOPLE_GROUP_NAME, SALARY_BASES
          FROM DEV_ELEMENTS_LINK         
           ORDER BY LINE_ID;

   LC_C_COMP                 C_COMP%ROWTYPE;
   L_COMP_ID                 NUMBER;
   L_OBJECT_VERSION_NUMBER   NUMBER;
   ERROR_DESC                VARCHAR2 (2000);
   LV_COMP_FLAG              CHAR (1);
   L_ELEMENT_TYPE_ID         NUMBER;
   L_PAYROLL_ID              NUMBER;
   L_GRADE_ID                NUMBER;
   L_COST_KEYFLEX            NUMBER;
   L_LEVEL_HIGH_ID           NUMBER;
   L_COMPT_ID                NUMBER;
   L_JOB_ID                  NUMBER;
   L_ELEMENT_LINK_ID         NUMBER;
   L_COMMENT_ID              NUMBER;
   L_COUNT                   NUMBER;
   L_EFFECTIVE_START_DATE    DATE;
   L_EFFECTIVE_END_DATE      DATE;
   L_ORG_ID                  NUMBER;
   L_LOC_ID                  NUMBER;
   L_BASIS_ID                NUMBER;
   L_PPL_GROUP_ID            NUMBER;
   L_EMP_CAT                 VARCHAR2 (2000);  
  



BEGIN
   OPEN C_COMP;

   LOOP
      FETCH C_COMP
       INTO LC_C_COMP;

      --EXIT WHEN C_COMP%NOTFOUND;

      ---------------------------QUERY FOR ELEMENT_TYPE_ID------------------
      IF LC_C_COMP.ELEMENT_TYPE_NAME IS NOT NULL
      THEN
         BEGIN
            SELECT   PETF.ELEMENT_TYPE_ID
                INTO L_ELEMENT_TYPE_ID
                FROM PAY_ELEMENT_TYPES_F PETF
               WHERE UPPER(PETF.ELEMENT_NAME)= UPPER(LC_C_COMP.ELEMENT_TYPE_NAME)
            ORDER BY PETF.ELEMENT_NAME;
         EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                     ERROR_DESC
                  || 'ELEMENT TYPE ERROR'
                  || SUBSTR (SQLERRM, 1, 100);
               L_ELEMENT_TYPE_ID := NULL;
         END;
      END IF;

      --------------------------QUERY FOR PAYROLL_ID--------------------------
      IF LC_C_COMP.PAYROLL_NAME IS NOT NULL
      THEN
         BEGIN
            SELECT   PAYROLL_ID
                INTO L_PAYROLL_ID
                FROM PAY_PAYROLLS_F
               WHERE UPPER(PAYROLL_NAME) = UPPER(LC_C_COMP.PAYROLL_NAME)
            ORDER BY PAYROLL_NAME;
         EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                  ERROR_DESC || 'PAYROLLNAME ERROR'
                  || SUBSTR (SQLERRM, 1, 100);
               L_PAYROLL_ID := NULL;
         END;
      END IF;

      ----------------------------QUERY FOR GRADE_ID---------------------------
      IF LC_C_COMP.GRADE_NAME IS NOT NULL
      THEN
         BEGIN
            SELECT   PG.GRADE_ID
                INTO L_GRADE_ID
                FROM PER_GRADES PG
               WHERE UPPER(PG.NAME) = UPPER(LC_C_COMP.GRADE_NAME)
            ORDER BY PG.NAME;
         EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                  ERROR_DESC || 'GRADENAME ERROR' || SUBSTR (SQLERRM, 1, 100);
               L_GRADE_ID := '';
         END;
      END IF;
     
      BEGIN
      SELECT LOOKUP_CODE
              INTO L_EMP_CAT
              FROM FND_LOOKUP_VALUES_VL
             WHERE LOOKUP_TYPE = 'EMP_CAT'
             AND UPPER (MEANING) = UPPER (TRIM (LC_C_COMP.EMPLOYMENT_CATEGORY));
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ERROR_DESC := ERROR_DESC || 'EMP CATEGORY LOOKUP ERROR';
         END;
      
     
      ----------------------------ORGANIZATION_NAME---------------------
     IF LC_C_COMP.ORG_NAME IS NOT NULL
      THEN
         BEGIN
     
     
      SELECT ORGANIZATION_ID, LOCATION_ID
    INTO L_ORG_ID, L_LOC_ID
    FROM HR_ALL_ORGANIZATION_UNITS
    WHERE UPPER(NAME) LIKE UPPER(LC_C_COMP.ORG_NAME);
    EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                  ERROR_DESC || 'ORGNAME ERROR' || SUBSTR (SQLERRM, 1, 100);
               L_ORG_ID := '';
         END;
      END IF;
     
   
   
    ------------------------SALARY BASIS------------------------------------

   IF LC_C_COMP.SALARY_BASES IS NOT NULL
      THEN
         BEGIN
         
    SELECT PAY_BASIS_ID
        INTO L_BASIS_ID   
        FROM PER_PAY_BASES
        WHERE UPPER(NAME) LIKE UPPER(LC_C_COMP.SALARY_BASES);
  
      EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                  ERROR_DESC || 'SALARY_BASIS_NAME ERROR' || SUBSTR (SQLERRM, 1, 100);
               L_BASIS_ID := '';
         END;
   END IF;
     
   
    -----------------------PEOPLE GROUP NAME---------------------------------
     IF LC_C_COMP.PEOPLE_GROUP_NAME IS NOT NULL
      THEN
         BEGIN
         
 
      SELECT PEOPLE_GROUP_ID
        INTO L_PPL_GROUP_ID
        FROM PAY_PEOPLE_GROUPS
        WHERE UPPER(GROUP_NAME) LIKE UPPER(LC_C_COMP.PEOPLE_GROUP_NAME);
   
        EXCEPTION
            WHEN OTHERS
            THEN
               ERROR_DESC :=
                  ERROR_DESC || 'PEOPLE_GROUP_NAME ERROR' || SUBSTR (SQLERRM, 1, 100);
               L_PPL_GROUP_ID := '';
         END;
     END IF;
   

          --L_COUNT := 0;

   

      --      IF L_COUNT = 0 THEN
      BEGIN
         PAY_ELEMENT_LINK_API.CREATE_ELEMENT_LINK
            (P_VALIDATE                          => FALSE,
             P_EFFECTIVE_DATE                    => LC_C_COMP.EFFECTIVE_DATE,
             P_ELEMENT_TYPE_ID                   => L_ELEMENT_TYPE_ID,
             P_BUSINESS_GROUP_ID                 => 0,
             P_COSTABLE_TYPE                     => LC_C_COMP.COSTABLE_TYPE,
             P_PAYROLL_ID                        => L_PAYROLL_ID,
             P_GRADE_ID                          => L_GRADE_ID,
             P_ORGANIZATION_ID                   => L_ORG_ID,
             P_PEOPLE_GROUP_ID                   => L_PPL_GROUP_ID,
             P_PAY_BASIS_ID                      => L_BASIS_ID,
             P_LOCATION_ID                       => L_LOC_ID,   
             P_STANDARD_LINK_FLAG                => LC_C_COMP.STANDARD_LINK_FLAG,
             P_TRANSFER_TO_GL_FLAG               => LC_C_COMP.TRANSFER_GL_FLAG
         ,
             P_EMPLOYMENT_CATEGORY               => L_EMP_CAT,--LC_C_COMP.EMPLOYMENT_CATEGORY,
             P_QUALIFYING_LENGTH_OF_SERVICE      => LC_C_COMP.QUALIFYING_LENGTH_OF_SERVICE,
             P_QUALIFYING_UNITS                  => LC_C_COMP.QUALIFYING_UNITS,
             P_COST_SEGMENT1                     => LC_C_COMP.P_COST_SEGMENT1,
             P_COST_SEGMENT2                     => LC_C_COMP.P_COST_SEGMENT2,
             P_COST_SEGMENT3                     => LC_C_COMP.P_COST_SEGMENT3,
             P_COST_SEGMENT4                     => LC_C_COMP.P_COST_SEGMENT4,
             P_COST_SEGMENT5                     => LC_C_COMP.P_COST_SEGMENT5,
             P_COST_SEGMENT6                     => LC_C_COMP.P_COST_SEGMENT6,
             P_COST_SEGMENT7                     => LC_C_COMP.P_COST_SEGMENT7,
             P_COST_SEGMENT8                     => LC_C_COMP.P_COST_SEGMENT8,
            
            
             P_BALANCE_SEGMENT1                  => LC_C_COMP.P_BAL_SEGMENT1,
             P_BALANCE_SEGMENT2                  => LC_C_COMP.P_BAL_SEGMENT2,
             P_BALANCE_SEGMENT3                  => LC_C_COMP.P_BAL_SEGMENT3,
             P_BALANCE_SEGMENT4                  => LC_C_COMP.P_BAL_SEGMENT4,
             P_BALANCE_SEGMENT5                  => LC_C_COMP.P_BAL_SEGMENT5,
             P_BALANCE_SEGMENT6                  => LC_C_COMP.P_BAL_SEGMENT6,
             P_BALANCE_SEGMENT7                  => LC_C_COMP.P_BAL_SEGMENT7,
             P_BALANCE_SEGMENT8                  => LC_C_COMP.P_BAL_SEGMENT8,
             P_COST_CONCAT_SEGMENTS              => LC_C_COMP.COST_CONCAT_SEGMENT,
             P_BALANCE_CONCAT_SEGMENTS           => LC_C_COMP.BALANCE_CONCAT_SEGMENT,
             P_ELEMENT_LINK_ID                   => L_ELEMENT_LINK_ID,
             P_COMMENT_ID                        => L_COMMENT_ID,
             P_OBJECT_VERSION_NUMBER             => L_OBJECT_VERSION_NUMBER,
             P_EFFECTIVE_START_DATE              => L_EFFECTIVE_START_DATE,
             P_EFFECTIVE_END_DATE                => L_EFFECTIVE_END_DATE
            );
            DBMS_OUTPUT.PUT_LINE('UPLOADED');

         IF L_ELEMENT_LINK_ID IS NOT NULL
         THEN
            LV_COMP_FLAG := 'Y';
            ERROR_DESC := 'NO ERROR';
         END IF;

         IF LV_COMP_FLAG = 'Y'
         THEN
            UPDATE DEV_ELEMENTS_LINK
               SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
                   --PROCESS_FLAG = LV_COMP_FLAG,
                   --ELEMENT_LINK_ID_OUT = L_ELEMENT_LINK_ID,
                   --COMMENT_ID = L_COMMENT_ID,
                   EFFECTIVE_START_DATE = L_EFFECTIVE_START_DATE,
                   EFFECTIVE_END_DATE = L_EFFECTIVE_END_DATE,
                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER
             WHERE LINE_ID = LC_C_COMP.LINE_ID;

         ELSE
            LV_COMP_FLAG := 'N';
            ERROR_DESC := SUBSTR (SQLERRM, 1, 254);

            UPDATE DEV_ELEMENTS_LINK
               SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
                   PROCESS_FLAG = LV_COMP_FLAG
            WHERE  LINE_ID = LC_C_COMP.LINE_ID;
         END IF;

      EXCEPTION
         WHEN OTHERS
         THEN
            LV_COMP_FLAG := 'N';
            ERROR_DESC := SUBSTR (SQLERRM, 1, 254);

            UPDATE DEV_ELEMENTS_LINK
               SET ERROR_DESCRIPTION = SUBSTR (ERROR_DESC, 1, 200),
                   PROCESS_FLAG = LV_COMP_FLAG
            WHERE  LINE_ID = LC_C_COMP.LINE_ID;


      END;

      L_COMP_ID := 0;
      L_LEVEL_HIGH_ID := 0;
      L_COMPT_ID := 0;
      L_JOB_ID := 0;
      L_COST_KEYFLEX := 0;
      L_COUNT := 0;
      LV_COMP_FLAG := NULL;
      ERROR_DESC := NULL;
      L_ELEMENT_TYPE_ID := NULL;
      L_PAYROLL_ID := NULL;
      L_GRADE_ID := NULL;
      L_ELEMENT_LINK_ID := NULL;
      L_COMMENT_ID := NULL;
      L_OBJECT_VERSION_NUMBER := NULL;
      L_EFFECTIVE_START_DATE := NULL;
      L_EFFECTIVE_END_DATE := NULL;
      L_ORG_ID := NULL;
      L_LOC_ID  := NULL;
      L_BASIS_ID  := NULL;
      L_PPL_GROUP_ID  := NULL;
      L_JOB_ID := NULL; 
        
     
   END LOOP;

   CLOSE C_COMP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 254));
END;
/

Leave a comment for any query.





0 comments: