Get Employee Balance in Oracle HRMS

Contents:- Get All or one Employee Balance in Oracle HRMS

 1.   First Create one Custom Table to Store all Employee Balance.

Create Table store_employee_balance(employee_number varchar2(10),assignment_id number,balance_amount number,effective_date date,err_msg varchar2(1500));

 2.   Create a Procedure With Following Contents to get all or one employee balance of your business group and store into your created custom table.

CREATE OR REPLACE PROCEDURE GET_ALL_EMPLOYEE_BALANCE (

   P_BUSINESS_GROUP_ID      NUMBER,

   P_ASSIGNMENT_ID       IN NUMBER,

   P_EFFECTIVE_DATE         DATE,

   P_BALANCE_NAME           VARCHAR2,

   P_DIMENSION_NAME         VARCHAR2)

AS

   l_balance_amount       NUMBER;

   l_defined_balance_id   pay_defined_balances.defined_balance_id%TYPE;

   l_error                VARCHAR2 (1000);

   P_ASS_ID               NUMBER := 0;

   P_EMP_NO               VARCHAR2 (10) := NULL;

   CURSOR csr_defined_bal (

      c_balance_name        IN VARCHAR2,

      c_dimension_name      IN VARCHAR2,

      c_business_group_id   IN NUMBER)

   IS

      SELECT db.defined_balance_id

        FROM apps.pay_balance_types pbt,

             apps.pay_defined_balances db,

             apps.pay_balance_dimensions bd

       WHERE     UPPER (pbt.balance_name) = UPPER (c_balance_name)

             AND pbt.balance_type_id = db.balance_type_id

             AND bd.balance_dimension_id = db.balance_dimension_id

             AND UPPER (bd.dimension_name) = UPPER (c_dimension_name)

             AND pbt.business_group_id = c_business_group_id

             AND db.business_group_id = pbt.business_group_id;

   CURSOR EMPC

   IS

      SELECT PAPF.EMPLOYEE_NUMBER, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID

        FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_ASSIGNMENTS_F PAAF

       WHERE     PAPF.PERSON_ID = PAAF.PERSON_ID

             AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'

             AND PAAF.PRIMARY_FLAG = 'Y'

             AND PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)

             AND PAAF.ASSIGNMENT_TYPE = 'E'

             AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                                     AND PAPF.EFFECTIVE_END_DATE

             AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE

                                     AND PAAF.EFFECTIVE_END_DATE

             AND PAPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID

             AND PAAF.PAYROLL_ID = 44;

BEGIN

   OPEN csr_defined_bal (c_balance_name        => p_balance_name,

                         c_dimension_name      => p_dimension_name,

                         c_business_group_id   => p_business_group_id);

   FETCH csr_defined_bal INTO l_defined_balance_id;

   IF csr_defined_bal%NOTFOUND

   THEN

      CLOSE csr_defined_bal;

      l_balance_amount := 0;

   ELSE

      CLOSE csr_defined_bal;

      IF (P_ASSIGNMENT_ID IS NULL)

      THEN

         apps.pay_balance_pkg.set_context ('tax_unit_id',

                                           p_business_group_id);

         BEGIN

            FOR I IN EMPC

            LOOP

               --            P_ASS_ID := I.ASSIGNMENT_ID;

               BEGIN

                  l_balance_amount :=

                     apps.pay_balance_pkg.get_value (

                        l_defined_balance_id,

                        I.ASSIGNMENT_ID,

                        NVL (p_effective_date, TRUNC (SYSDATE)),

                        NULL);

                  INSERT INTO store_employee_balance

                       VALUES (i.employee_number,

                               i.assignment_id,

                               l_balance_amount,

                               NVL (p_effective_date, TRUNC (SYSDATE)),

                               NULL);

               EXCEPTION

                  WHEN OTHERS

                  THEN

                     l_error := SUBSTR (SQLERRM, 1, 999);

                     INSERT INTO STORE_EMPLOYEE_BALANCE

                          VALUES (

                                    NULL,

                                    NULL,

                                    NULL,

                                    NULL,

                                       'ERROR IN GETTING EMPLOYEE BALANCE FOR EMPLOYEE '

                                    || I.EMPLOYEE_NUMBER

                                    || ' = '

                                    || L_ERROR);

                                    commit;

               END;

            END LOOP;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_error := SUBSTR (SQLERRM, 1, 999);

               INSERT INTO STORE_EMPLOYEE_BALANCE

                    VALUES (NULL,

                            NULL,

                            NULL,

                            NULL,

                            'ERROR IN GETTING EMPLOYEE DATA = ' || L_ERROR);

         END;

      ELSE

         apps.pay_balance_pkg.set_context ('tax_unit_id',

                                           p_business_group_id);

         l_balance_amount :=

            apps.pay_balance_pkg.get_value (

               l_defined_balance_id,

               P_ASSIGNMENT_ID,

               NVL (p_effective_date, TRUNC (SYSDATE)),

               NULL);

         SELECT PAPF.EMPLOYEE_NUMBER

           INTO P_EMP_NO

           FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_ASSIGNMENTS_F PAAF

          WHERE     PAPF.PERSON_ID = PAAF.PERSON_ID

                AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'

                AND PAAF.ASSIGNMENT_STATUS_TYPE_ID IN (1, 2)

                AND PAAF.ASSIGNMENT_TYPE = 'E'

                AND PAAF.ASSIGNMENT_ID = P_ASSIGNMENT_ID

                AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE

                                        AND PAPF.EFFECTIVE_END_DATE

                AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE

                                        AND PAAF.EFFECTIVE_END_DATE;

         INSERT INTO store_employee_balance

              VALUES (P_EMP_NO,

                      P_assignment_id,

                      l_balance_amount,

                      NVL (p_effective_date, TRUNC (SYSDATE)),

                      NULL);

                      commit;

      END IF;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      l_error := SUBSTR (SQLERRM, 1, 999);

      INSERT INTO STORE_EMPLOYEE_BALANCE

           VALUES (NULL,

                   NULL,

                   NULL,

                   NULL,

                   'ERROR = ' || L_ERROR);

END;

/

 3.   Execute your created procedure to store one or all employee balance into your custom table.

Execute GET_ALL_EMPLOYEE_BALANCE(business_group_id,assignment_id,effective_date,balance_name,dimension_name);

Notes: If you want to get balance for all employee then pass NULL into assignment_id.