Monday 28 June 2021

Oracle Apps: How to Schedule Concurrent Program to run at a specific time from back-end

In my previous posts I have explained about how can we make the parent concurrent program wait for all the child programs to complete and how to submit a concurrent program from the backend periodically etc.... In this one I thought of giving an example of how to submit a concurrent program to run at a specific time.

Inorder to do that we just need to specify the 'start_date' parameter in the procedure fnd_request.submit_request. But the thing which we need to be careful is, this parameter is of type VARCHAR2. So if don't pass a date variable to this value, it will truncate the date and the time component will be removed. It will result in the program starting straight away if the date is on the same day or exactly 12:00 AM of the date (if the date is a future date). When you pass the parameter pass the date and time in the format 'DD-Mon-YYYY HH24:MI:SS'

A Sample can be like below. I have just added 3 hours to current date and converted into varchar using TO_CHAR function. You can also specify the time directly like '29-Jun-2021 10:00:00'

DECLARE
 
   v_request_id        NUMBER;
   v_status            BOOLEAN;
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_PROGRAM'
                                              , start_time  => TO_CHAR(SYSDATE + 3/24,'DD-Mon-YYYY HH24:MI:SS')
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 

Reference: 



Feel free to point out if anything is wrong/missing in this blog.

Wednesday 21 April 2021

Oracle Apps: How to Schedule Concurrent Program to run periodically from back-end

In my previous posts I have explained about how can we make the parent concurrent program wait for all the child programs to complete and how to submit a concurrent program from the backend. In this one I thought of giving an example of how to submit a concurrent program and schedule that to run periodically.

For this you will have to use the function set_repeat_options in the package fnd_request.
  
  --
  -- Name
  --   set_repeat_options
  -- Purpose
  --   Called before submitting request if the request to be submitted
  --   is a repeating request.
  --
  -- Arguments
  --   repeat_time      - Time of day at which it has to be repeated
  --   repeat_interval  - Frequency at which it has to be repeated
  --                    - This will be used/applied only when repeat_time
  --                    - is NULL ( non null repeat_interval overrides )
  --   repeat_unit      - Unit for repeat interval. Default is DAYS.
  --                    - MONTHS/DAYS/HOURS/MINUTES
  --   repeat_type      - Apply repeat interval from START or END of request
  --                    - default is START. START/END
  --   repeat_end_time  - Time at which the repetition should be stopped
  --   incrment_dates   - 'Y' if dates should be incremented each run,
  --                      otherwise 'N'
  --
  function set_repeat_options (repeat_time      IN varchar2 default NULL,
                               repeat_interval  IN number   default NULL,
                               repeat_unit      IN varchar2 default 'DAYS',
                               repeat_type      IN varchar2 default 'START',
                               repeat_end_time  IN varchar2 default NULL,
                               increment_dates  IN varchar2 default NULL)
                               return boolean is
Below is an example to schedule a job to run every 5 minutes.
DECLARE
 
   v_request_id        NUMBER;
   v_status            BOOLEAN;
 
BEGIN
   --Initialize the session with appropirate values
   fnd_global.apps_initialize (user_id=>100
                              ,resp_id=>100
                              ,resp_appl_id=>100);
 
   --Set the Repeat Options
   v_status := fnd_request.set_repeat_options ( repeat_interval => 5
                                              , repeat_unit     => 'MINUTES'
                                              , repeat_type     => 'START');
   --Submit the Request
   v_request_id := fnd_request.submit_request ( application => 'XXAJ'
                                              , program     => 'XXAJ_CHILD'
                                              , start_time  => SYSDATE
                                              , sub_request => FALSE);
   COMMIT;
 
   IF v_request_id = 0 THEN
      DBMS_OUTPUT.put_line('Request not submitted: '|| fnd_message.get);
   ELSE
      DBMS_OUTPUT.put_line('Request submitted successfully. Request id: ' || v_request_id);
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line('Exception: ' || SQLERRM);   
END;
 






Feel free to point out if anything is wrong/missing in this blog.

Saturday 27 February 2021

Oracle Apps: How to set Concurrent Program Parameter Disabled / Readonly

This post is to show how can me make a concurrent program parameter disabled / readonly.

You might be wondering why do you want to make a concurrent program parameter readonly, when we could just set the display false for the parameter. But this is to fix the issues identified by my colleagues in the solution given in the below post. 

Oracle Apps : Selecting Multiple values for a parameter in Concurrent Program 

The issues identified on the above solution is

If the user removes the value from the Multiselect field manually, then user will not be able to select any more.

The reason is because once you modify the parameter value, then the default query will not be executed. The solution is to make the field disabled, so that user will not be able to clear the values manually. They will be forced to use the 'Clear' option in the original list.

Steps to make the field disabled/read only.

1.  Create a new valueset of type 'Special'. We just need to put some dummy PLSQL block for the Edit and Validate events.

Override the Edit event, this will make the field read only. You don't need to do write any logic in this, just add a dummy PLSQL block for this event.

Validate event user exit is mandatory for a special valueset, else you will get an error when submitting the concurrent program.  So just add a dummy PLSQL block for validate event as well.

Code used in both the events.

FND PLSQL   
 "  
  DECLARE  
   v_sel_fruit_list VARCHAR2(240):= :!VALUE;  
  BEGIN  
   NULL;  
  END;  
 "


2. Attach this valuset to the Multiselect parameter.

Now, when you submit the concurrent program, this parameter will be disabled. User will not be able to modify the values in this parameter directly. The value in this parameter will need to be updated by the first parameter.



To see how to use special valuset for validation on concurrent program parameter :




Feel free to point out if anything is missing/wrong in this blog.


Monday 8 February 2021

Oracle Apps : Selecting Multiple values for a parameter in Concurrent Program

We had a requirement to select multiple values from a list when submitting the Concurrent Program.

Few of the options suggested were as below:

1. Create a custom Form and capture the values and then submit the Concurrent Program form a button click .  

  • This was ruled out as we didn't want to create a new Form

2. Let the users enter a comma separated values in the parameter field and split the values in the code.

  • This was ruled out as there is a probability that the user may enter wrong values

We found a blog where someone suggested some solution with some limitations. 

The solution given below is without the limitation mentioned in the above link. The limitation we have is the length of the parameter field (240 characters). I have suggested a workaround for that also below.

Edited: This solution needs to be read along with the below post.

Oracle Apps: How to set Concurrent Program Parameter Disabled / Readonly

The Concurrent Program example given below has 2 Parameters. One is the original list and the second is the selected values from the list.

1. Create a Valueset with the needed values. Make sure that you have an extra value in the list, which will be user to clear the selected list. In my example I have created a valueset with the list of Fruits. Please note, there is a value in the list 'Clear'. Usage of the value 'Clear' is explained below.


2. Create the Executable and the Concurrent Program.


3. Add the original list as the first parameter. This parameter is used only as the selection list. The actual program ignores this parameter. 

    

4. Now is the interesting part of this solution. Create another parameter which will get populated using the values you select from the first parameter. Before creating this parameter, you need to have the package and procedure ready.  This parameter should have the below properties.

Valueset : 

240 Characters

Default Type: 

SQL Statement

Default Value :  

select xxaj_fruit_platter_pkg.get_selected_list(:$FLEX$.XXAJ_FRUIT_LISTfrom dual

 


The code for the function get_selected_list is as below :

 FUNCTION get_selected_list(p_fruit VARCHAR2)  
 RETURN VARCHAR2  
 IS  
 BEGIN
   IF p_fruit = 'Clear' THEN
     v_selected_list := NULL;
    
   ELSE 
     --Concatenate the selected value to the existing list  
     SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_fruit   
     INTO v_selected_list   
      FROM dual;  
    END IF;
    
    RETURN v_selected_list;  
  END get_selected_list;  

v_selected_list is a package level variable and everytime when this function gets called, it just keep appending the values in the variable v_selected_list. If the value Clear  selected in the first parameter, the function will clear the package variable and then also remove the values from the second parameter. Package level variable is visible only on the session, so if multiple users try to submit the job at the same time, this will not cause any issues.

The full code of the package is given below.

Now we can see how this works when you try to submit the job.



Select one value 'Apple' from the first parameter.


Select another value 'Orange' from the first paramater.


Now if you really interested in this solution, create a program as above and try to select 'Clear'. Then you can see how that works :)


From the program log, you can see that you have got the comma separated values inside the program. Now you use just PLSQL code to extract the individual values as use it as per your requirement.



Few points which you might be interersted in :

  • User can modify the list before submitting. If they want to remove one specific value , they could just modify the value in the second parameter before submitting.
  • If you don't want user to modify the second parameter manually, just remove the 'Display' property from the second parameter in the concurrent program definition.
  • If the list is too big and the values exceeds 240 Characters, then try to pass a code  with lesser characters instead of the full value and then translate that in the code.
  • You could also add a new parameter called, remove list, so that user can select from this list to remove a value selected by mistake.

The package code with the variable declaration is given below:

Package Spec:

 CREATE OR REPLACE PACKAGE xxaj_fruit_platter_pkg AS  
  PROCEDURE create_platter(x_errbuf      OUT VARCHAR2  
                          ,x_retcode     OUT VARCHAR2  
                          ,p_dummy_fruit IN  VARCHAR2  
                          ,p_fruit_list  IN  VARCHAR2);  
  FUNCTION get_selected_list(p_fruit VARCHAR2)  
  RETURN VARCHAR2;  
 END xxaj_fruit_platter_pkg;  

Package Body:

 CREATE OR REPLACE PACKAGE BODY xxaj_fruit_platter_pkg AS  
  --Package level variable which holds the value  
  v_selected_list VARCHAR2(1000);  
  PROCEDURE create_platter (x_errbuf      OUT VARCHAR2  
                           ,x_retcode     OUT VARCHAR2  
                           ,p_dummy_fruit IN  VARCHAR2  
                           ,p_fruit_list  IN  VARCHAR2)  
  IS  
  BEGIN  
   --This will have the last selected fruit. Just ignore it :)  
   fnd_file.put_line(fnd_file.log ,'p_dummy_fruit : '|| p_dummy_fruit);   
   fnd_file.put_line(fnd_file.log ,'p_fruit_list  : '|| p_fruit_list);   
  END create_platter;  
  FUNCTION get_selected_list(p_fruit VARCHAR2)  
  RETURN VARCHAR2  
  IS  
  BEGIN  
    IF p_fruit = 'Clear' THEN  
     v_selected_list := NULL;  
    ELSE   
     --Concatenate the selected value to the existing list  
     SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_fruit   
     INTO v_selected_list   
     FROM dual;  
    END IF;  
      
    RETURN v_selected_list;  
  END get_selected_list;  
 END xxaj_fruit_platter_pkg;  

If there is anything which you think will be an issue in the above solution, feel free to post a comment below.





Feel free to point out if anything is missing/wrong in this blog.




Wednesday 28 October 2020

OAF: How to default current date time into a MessageDateFieldBean in Controller

This post is to show how we can default the current date time to a MessageDateFieldBean in OA framework page, where the field is not mapped to a View Object. If the field is mapped to a ViewAttribute in a View Object, we could just set the value to the ViewAttribute and it will get defaulted in the bean, when the page loads.

We need to make sure that the format matches the user's date preference set as well.

We could use OANLSServices class to acheive this.

Get the current date using java.util.Date. Pass that as an input to OANLSServices.dateTimeToString. This will return the date value in the current user preference format.

Sample Code which can be used in the processRequest function.

 //get the handle to the date field bean  
 OAMessageDateFieldBean dateFieldBean = (OAMessageDateFieldBean)webBean.findChildRecursive("CurrentDate");  
 pageContext.writeDiagnostics(this, "dateFieldBean :" + dateFieldBean, 4);  
 if (dateFieldBean != null)  
 {  
       // Get the current date  
       java.util.Date utilDate = new java.util.Date();  
       //Convert the date value into users date format  
       String dateStr = pageCtxt.getOANLSServices().dateTimeToString(utilDate);  
       pageContext.writeDiagnostics(this, "dateStr :" + dateStr, 4);  
       //set the value to the datefield bean  
       dateFieldBean.setValue(pageContext,dateStr);  
 }  

The above code will take care of the date format set in the user preferences.

Date Format set to dd-MMM-yyyy

Output
Date Format set to yyyy/MMM/dd


Output




Feel free to point out if anything is missing/wrong in this blog.

Monday 15 June 2020

Oracle Apps: How to create validation for a date parameter using a value set for Oracle Concurrent Program

One of my colleague was looking for a solution to restrict a date parameter on a concurrent program not to allow future dates.This post is to just show how to do that as it could help someone else as well.

Step 1. 
Create a Special Value set as below



Format Type     : Standard Date
Validation Type : Special
 Event                : Validate
 
FND PLSQL "
DECLARE
  v_date DATE:= :!VALUE;
BEGIN
  IF v_date > SYSDATE THEN
    FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
    FND_MESSAGE.SET_TOKEN('MESSAGE','Please choose current or past date.');
    FND_MESSAGE.RAISE_ERROR;
  END IF;
END;
"
 
Step 2.
Attach this value set to the Concurrent Program Parameter.



Now try to enter a future date parameter while submitting the concurrent program.






Feel free to point out if anything is wrong/missing in this post


Tuesday 9 June 2020

Oracle Apps: FRM-92102: A network error has occured.

At one of our client sites, some of the users were getting the below error when they try to access the environment after a clone.



"FRM-92102: A network error has occured.
        The Forms Client has attemted to reestablish its
              connection to the Server 1 time(s) without success.
                        Please check the network connection and try again later."

Initially we thought, it was something to do with their network connection. Once we confirm that the network is not an issue, we tried to do clear cache on the browser and restarting the machine etc. with no luck.

This got fixed after we cleared the Java cache. Steps to do that is given below.

1. Close all the brower windows
2. Open the Java Control Panel
        Control Panel --> Java


3. Click on Settings button
4. Click on Delete Files
5. Click OK

This will clear the Java Cache. Once the Java Cache is cleared we were able to open the forms screen without any issue.



Feel free to point out if anything is wrong/missing in this post