How to get Employee balance by element in Oracle HRMS Payroll

How can i get Employee Balance by Balance Name

Please find the function definition below.

You may change the input names and balance dimension according to your requirement.

Dimension 'ASG_FYTD' is used in this function.

CREATE OR REPLACE FUNCTION APPS.CUST_GET_BALANCE_BY_ELEMENT (

   P_ASSG_ID           NUMBER,

   P_PAY_EARNED_DATE   DATE,

   P_ELEMENT_NAME      VARCHAR2

)

   RETURN NUMBER

IS

   X_PAY_NUM        NUMBER;

   P_PAY_Y_SD       DATE;

   P_BALANCE_NAME   VARCHAR2 (200);

BEGIN

   SELECT PBT.BALANCE_NAME

     INTO P_BALANCE_NAME

     FROM PAY_BALANCE_TYPES PBT,

          PAY_INPUT_VALUES_F PIVF,

          PAY_ELEMENT_TYPES_F PETF

    WHERE PBT.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID

      AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID

      AND UPPER (PIVF.NAME) IN

             ('ENTITLED SALARY',

              'ENTITLED LUMPSUM',

              'ENTITLED STIPEND',

              'AMOUNT TO BE PAID',

              'CALCULATED AMOUNT'

             )

      AND PETF.ELEMENT_NAME = P_ELEMENT_NAME;

   SELECT NVL (SUM (NVL (PBVV.VALUE, 0)), 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;

/

Feedback is much appreciated.