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.

How to call procedure with parameters in a trigger Oracle PL/SQL

Requirement:

When an absence is entered into System, an SIT should be auto filled based on data available on Absence form.

Here is the procedure to upload data into SIT(Special Information Type)

How to create SIT in Oracle HRMS

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_SPECIAL_INFO(P_PERSON_ID IN NUMBER,P_DATE_START IN DATE,P_DATE_END IN DATE)

IS

   L_ID_FLEX_NUM               NUMBER;

   L_PERSON_ID                 NUMBER;

   L_NAME                      VARCHAR2 (240);

   ERROR_DESC                  VARCHAR2 (240);

   LV_SIT_FLAG                 CHAR (1);

   L_OBJECT_VERSION_NUMBER     NUMBER;

   L_ANALYSIS_CRITERIA_ID      NUMBER;

   L_PERSON_ANALYSIS_ID        NUMBER;

   L_PEA_OBJECT_VERSION_NUMBER NUMBER;

  

  

BEGIN

    L_PERSON_ID := P_PERSON_ID;

   

   

       

       

      BEGIN

         HR_SIT_API.CREATE_SIT

                        (P_VALIDATE                   => FALSE,

                         P_PERSON_ID                  => L_PERSON_ID,

                         P_BUSINESS_GROUP_ID          => 2217,--FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),

                         P_ID_FLEX_NUM                => 50647,--L_ID_FLEX_NUM,

                         P_DATE_FROM                  => P_DATE_START,

                         P_DATE_TO                    => P_DATE_END,

                         P_EFFECTIVE_DATE             => P_DATE_END,

                         P_SEGMENT1                   => to_char(P_DATE_START,'YYYY/MM/DD HH24:MI:SS'),-- P_DATE_START,

                         P_SEGMENT2                   => to_char(P_DATE_END,'YYYY/MM/DD HH24:MI:SS'),--P_DATE_END,

                         --P_SEGMENT3                   => LC_C_SIT.SEGMENT3,

                         --,P_CONCAT_SEGMENTS           =>

                         --P_ATTRIBUTE_CATEGORY         => LC_C_SIT.ATTRIBUTE_CATEGORY,

                         --P_ATTRIBUTE1                 => LC_C_SIT.ATTRIBUTE1,

                         --P_ATTRIBUTE2                 => LC_C_SIT.ATTRIBUTE2,                        

                         P_ANALYSIS_CRITERIA_ID       => L_ANALYSIS_CRITERIA_ID,

                         P_PERSON_ANALYSIS_ID         => L_PERSON_ANALYSIS_ID,

                         P_PEA_OBJECT_VERSION_NUMBER  => L_PEA_OBJECT_VERSION_NUMBER

                        

                        );

      END;

      L_OBJECT_VERSION_NUMBER := '';

      ERROR_DESC := '';

      LV_SIT_FLAG := '';

      L_OBJECT_VERSION_NUMBER  :=   '';

      L_ANALYSIS_CRITERIA_ID   :=   '';

      L_PERSON_ANALYSIS_ID     :=  '';

      L_PEA_OBJECT_VERSION_NUMBER := '';

  

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

/

Trigger

create or replace trigger Auto_ticket

AFTER insert on per_absence_attendances

for each row

declare

    PRAGMA AUTONOMOUS_TRANSACTION;

begin

  APPS.DEV_CREATE_SPECIAL_INFO(:new.person_id,:new.date_start,:new.date_end);

  COMMIT;

end;

Leave comment for any query.