How to get entry value of an element when retro pay has already been run in Oracle HRMS Payroll

Let me explain, why i am creating this post.

Usually we call ENTRY_VALUE of an element like this in our formula

ELEMENT_NAME_INPUT_NAME_ENTRY_VALUE

What if Retropay has already been run and it has created retro entries in element entries window. In that case, if we would try to call ENTRY_VALUE of an element for which retro entry exists, it will sum up entry values of Standard and Retro Entries and pass it to the formula.

Solution:

I would create a global temporary table first, which would be purged automatically after processing of all the elements in Oracle Payroll Run or QuickPay.

Here is the table definition.

CREATE GLOBAL TEMPORARY TABLE CUST_ASSG_RESULTS_PTD (

  ASSIGNMENT_ID          NUMBER,

  ASSIGNMENT_ACTION_ID   NUMBER,

  ELEMENT_TYPE_ID        NUMBER,

  ELEMENT_PROC_PRIORITY  NUMBER,

  INPUT_NAME             VARCHAR2(200),

  INPUT_VALUE            NUMBER

) ON COMMIT DELETE ROWS;

Let us now create a function which will insert the relevant values into table while processing of the first formula in processing priority.

Function definition is below.

CREATE OR REPLACE FUNCTION APPS.CUST_SET_ASSG_PTD_DATA(

                          P_ASSG_ID              NUMBER,

                          ASSIGNMENT_ACTION_ID   NUMBER,

                          P_ELE_TYPE_ID          NUMBER,

                          P_PROC_PRIORITY        NUMBER,

                          P_INPUT_NAME           VARCHAR,

                          P_INPUT_VALUE          NUMBER

                        )

RETURN NUMBER

IS

BEGIN

        INSERT INTO CUST_ASSG_RESULTS_PTD

        (

         ASSIGNMENT_ID

        ,ASSIGNMENT_ACTION_ID

        ,ELEMENT_TYPE_ID

        ,ELEMENT_PROC_PRIORITY

        ,INPUT_NAME,

        INPUT_VALUE

        )

        VALUES

        (

         NVL(P_ASSG_ID,0)

        ,NVL(P_ASSG_ACTION_ID,0)

        ,NVL(P_ELE_TYPE_ID,0)

        ,NVL(P_PROC_PRIORITY,0)

        ,P_INPUT_NAME

        ,NVL(P_INPUT_VALUE,0)

        );

RETURN 1;

END;

After data has been inserted into dummy table, we need to call it to be passed to other formulas. So, another function would be created for this purpose.

CREATE OR REPLACE FUNCTION APPS.CUST_GET_ENTRY_VALUE (

   P_ASSG_ID           NUMBER,

   ASSG_ACTION_ID      VARCHAR2,

   P_ELEMENT_TYPE_ID   VARCHAR2,

   P_INPUT_NAME        VARCHAR2

)

   RETURN NUMBER

IS

   X_RESULT   NUMBER;

BEGIN

   SELECT INPUT_VALUE

     INTO X_RESULT

     FROM CUST_ASSG_RESULTS_PTD

    WHERE ASSIGNMENT_ID = P_ASSG_ID

      AND ASSIGNMENT_ACTION_ID = ASSG_ACTION_ID

      AND ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID

      AND INPUT_NAME = P_INPUT_NAME;

   RETURN NVL (X_RESULT, 0);

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN 0;

END;

/

Integrate this function with Oracle Applications and call it in relevant formulas.

How to register a function in Oracle HRMS Payroll

I hope this post is helpful for you.

For any query, leave comment. Kindly provide feedback for my impovement.