How to get Employee balance by balance Name in Oracle HRMS Payroll

How can i get Employee Balance by Element

Please find PL-SQL Function definition below.

Please note that you may change the balance dimension according to your requirement.
Currently, Fiscal year to date balance dimension is specified in the function.

CREATE OR REPLACE FUNCTION CUST_GET_BALANCE (

   P_ASSG_ID           NUMBER,

   P_PAY_EARNED_DATE   DATE,

   P_BALANCE_NAME      VARCHAR2

)

   RETURN NUMBER

IS

   X_PAY_NUM    NUMBER;

   P_PAY_Y_SD   DATE;

BEGIN

   SELECT SUM (NVL (PBVV.VALUE, 0))

     INTO X_PAY_NUM

     FROM PAY_BALANCE_VALUES_V PBVV

    WHERE PBVV.BALANCE_NAME = P_BALANCE_NAME

      AND PBVV.DATABASE_ITEM_SUFFIX = '_ASG_FYTD'

      AND PBVV.ASSIGNMENT_ID = P_ASSG_ID

      AND PBVV.EFFECTIVE_DATE = P_PAY_EARNED_DATE;

   RETURN X_PAY_NUM;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN 0;

END;

/

Leave a comment for any query.