Balance Adjustment procedure in Oracle HRMS Payroll

How to get Employee balance by Element using a PL/SQL Function

Please refer to the steps below.

1) TABLE

CREATE TABLE DEV_BAL_ADJ_TAB

(

  LINE_ID               NUMBER,

  EFFECTIVE_START_DATE  DATE,

  EMPLOYEE_NUMBER       VARCHAR2(150 BYTE),

  ELEM_LINK_ID          VARCHAR2(150 BYTE),

  INPUT_VAL_ID1         VARCHAR2(150 BYTE),

  ELEM_ENT_VAL1         VARCHAR2(150 BYTE),

  INPUT_VAL_ID2         VARCHAR2(150 BYTE),

  ELEM_ENT_VAL2         VARCHAR2(150 BYTE),

  ERROR_DESCRIPTION     VARCHAR2(4000 BYTE)

)

2) CREATION PROCEURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ELEMENTS_BAL_ADJ

IS

   CURSOR C_COMP

   IS

      SELECT   LINE_ID, EFFECTIVE_START_DATE, EMPLOYEE_NUMBER ASSG_ID,

               ELEM_LINK_ID, INPUT_VAL_ID1, ELEM_ENT_VAL1, INPUT_VAL_ID2,

               ELEM_ENT_VAL2

          FROM DEV_BAL_ADJ_TAB

         WHERE ERROR_DESCRIPTION IS NULL

      --WHERE ERROR_DESCRIPTION <> 'NO ERROR'

      ORDER BY 1;

/*SELECT LINE_ID, DBAT.EFFECTIVE_START_DATE,

       (SELECT DISTINCT TO_NUMBER (PAF.ASSIGNMENT_ID)

                   FROM PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAF

                  WHERE PPF.PERSON_ID = PAF.PERSON_ID

                    AND PPF.EMPLOYEE_NUMBER = DBAT.EMPLOYEE_NUMBER) ASSG_ID,

       DBAT.ELEM_LINK_ID,

       (SELECT  PIVF.INPUT_VALUE_ID

          FROM PAY_ELEMENT_TYPES_F PETF,

               PAY_INPUT_VALUES_F PIVF,

               PAY_ELEMENT_LINKS_F PELF

         WHERE PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID

           AND PETF.ELEMENT_TYPE_ID = PELF.ELEMENT_TYPE_ID

           AND PELF.ELEMENT_LINK_ID = DBAT.ELEM_LINK_ID

           AND UPPER(PIVF.NAME) = 'PAY VALUE') INPUT_VAL_ID1,

       DBAT.ELEM_ENT_VAL1, (SELECT  PIVF.INPUT_VALUE_ID

          FROM PAY_ELEMENT_TYPES_F PETF,

               PAY_INPUT_VALUES_F PIVF,

               PAY_ELEMENT_LINKS_F PELF

         WHERE PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID

           AND PETF.ELEMENT_TYPE_ID = PELF.ELEMENT_TYPE_ID

           AND PELF.ELEMENT_LINK_ID = DBAT.ELEM_LINK_ID

           AND UPPER(PIVF.NAME) = 'AMOUNT TO BE DEDUCTED') INPUT_VAL_ID2, DBAT.ELEM_ENT_VAL2

  FROM DEV_BAL_ADJ_TAB DBAT

  --WHERE    ERROR_DESCRIPTION IS NULL

  --WHERE  ELEM_LINK_ID <> '#N/A'

  ORDER BY 1;*/

   LC_C_COMP                 C_COMP%ROWTYPE;

   --L_COMP_ID                 NUMBER;

   L_EFFECTIVE_START_DATE    DATE;

   L_ASSG_ID                 NUMBER;

   L_EFFECTIVE_END_DATE      DATE;

   L_ELEMENT_ENTRY_ID        NUMBER;

   L_OBJECT_VERSION_NUMBER   NUMBER;

   L_CREATE_WARNING          BOOLEAN;

   LV_JOB_FLAG               CHAR (1);

   ERROR_DESC                VARCHAR2 (5000);

BEGIN

   OPEN C_COMP;

   LOOP

      FETCH C_COMP

       INTO LC_C_COMP;

--       SELECT TO_NUMBER(PAF.ASSIGNMENT_ID) INTO L_ASSG_ID

--       FROM PER_PEOPLE_F PPF , PER_ASSIGNMENTS_F PAF

--       WHERE PPF.PERSON_ID = PAF.PERSON_ID

--       AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE

--       AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE

--       AND PPF.EMPLOYEE_NUMBER = LC_C_COMP.EMPLOYEE_NUMBER;

      EXIT WHEN C_COMP%NOTFOUND;

      BEGIN

         --DBMS_OUTPUT.PUT_LINE('RAN');

         PAY_BALANCE_ADJUSTMENT_API.CREATE_ADJUSTMENT

                         (P_VALIDATE                   => FALSE,

                          P_EFFECTIVE_DATE             => LC_C_COMP.EFFECTIVE_START_DATE,

                          P_ASSIGNMENT_ID              => LC_C_COMP.ASSG_ID,

                          P_CONSOLIDATION_SET_ID       => 1,

                          P_ELEMENT_LINK_ID            => LC_C_COMP.ELEM_LINK_ID,

                          P_INPUT_VALUE_ID1            => LC_C_COMP.INPUT_VAL_ID1,

                          P_ENTRY_VALUE1               => LC_C_COMP.ELEM_ENT_VAL1,

                          P_INPUT_VALUE_ID2            => LC_C_COMP.INPUT_VAL_ID2,

                          P_ENTRY_VALUE2               => LC_C_COMP.ELEM_ENT_VAL2,

                          P_ELEMENT_ENTRY_ID           => L_ELEMENT_ENTRY_ID,

                          P_EFFECTIVE_START_DATE       => L_EFFECTIVE_START_DATE,

                          P_EFFECTIVE_END_DATE         => L_EFFECTIVE_END_DATE,

                          P_OBJECT_VERSION_NUMBER      => L_OBJECT_VERSION_NUMBER,

                          P_CREATE_WARNING             => L_CREATE_WARNING

                         );

         --DBMS_OUTPUT.PUT_LINE('RAN'||L_ELEMENT_ENTRY_ID);

         IF L_ELEMENT_ENTRY_ID IS NOT NULL

         THEN

            LV_JOB_FLAG := 'Y';

            ERROR_DESC := 'NO ERROR';

         --    DBMS_OUTPUT.PUT_LINE('CONDITION 1');

         END IF;

         IF LV_JOB_FLAG = 'Y'

         THEN

            UPDATE DEV_BAL_ADJ_TAB

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_COMP.LINE_ID;

--                 DBMS_OUTPUT.PUT_LINE('CONDITION 2');

         ELSE

            ERROR_DESC := SQLERRM;

--                DBMS_OUTPUT.PUT_LINE('CONDITION 3');

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            ERROR_DESC := ERROR_DESC || SQLERRM;

            UPDATE DEV_BAL_ADJ_TAB

               SET ERROR_DESCRIPTION = ERROR_DESC

             --  ELEMENT_PROCESS_FLAG = LV_JOB_FLAG

            WHERE  LINE_ID = LC_C_COMP.LINE_ID;

      -- DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 254));

      END;

      L_EFFECTIVE_START_DATE := NULL;

      L_EFFECTIVE_END_DATE := NULL;

   END LOOP;

   CLOSE C_COMP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 254));

END;

/

How to find API for Data Uploading

Leave a comment for any query.