How to show text format output in Excel in Oracle Applications

While defining any concurrent program, the default output format is ‘Text’ as shown below, so the program will open the report output in browser.

If we want to take the report output in Excel format, though the output format is 'Text', we need to do the following steps.

Go to System Administrator à  Profile à System

Query for ‘Viewer%’

Go to Install à Viewer Options

Click on Text -> Add one more line with Text and give Mime Type as ‘application/vnd.ms-excel’ and description as ‘Microsoft Excel’ and check “Allow Native Client Encoding” check box and save it. This will allow us to take the Text format output either in ‘Browser’ or ‘Excel’.

For testing, run any report and view the output, Tools -> Copy File, it will give the Viewer Options LOV as shown below:

Note: The above illustration is for the programs which we write in PL\SQL Program and report output format is in Text in concurrent program definition.

Kindly provide your feedback.

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

Value Sets in Oracle Apps

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.

Writing a Skip Rule Formula in Oracle HRMS Payroll

Sometimes it is a requirement that a formula is processed when a certain condition is met. For instance, your company would pay an allowance every quarter. So to achieve this goal, there a functionality available of writing Skip Rule Formula.

Here is the element window and where we specify the Skip Rule.

Navigation: Global HRMS Manager -> Total Compensation -> Basic-> Element Description

Now Let's write the Skip Rule Formula.

Navigation: Global HRMS Manager -> Total Compensation -> Basic-> Write Formulas

Choose Edit and write your formula as follows:

Default for PAY_PROC_PERIOD_NUMBER is 0

skip_flag = 'y'

IF

PAY_PROC_PERIOD_NUMBER=1 OR

PAY_PROC_PERIOD_NUMBER=4 OR

PAY_PROC_PERIOD_NUMBER=7 OR

PAY_PROC_PERIOD_NUMBER=10 THEN

(

skip_flag='n'

)

Return skip_flag

Verify the formula, close the Edit window, and save your work.

Now navigate back to Element screen.

Navigation: Global HRMS Manager -> Total Compensation -> Basic-> Element Description

Now select the skip rule formula field as shown in Screenshot 1.

For any query, leave your comment.