Wednesday, 15 May 2024

Oracle Apps : Global Descriptive Flexfield not enabling for Malaysia on AR Transaction Form

For one of our customers, we enabled the Global Descriptive Flexfield (GDFF) for India and Malaysia.  The GDFF appeared successfully for India responsibility, but was not enabled for Malaysia responsibility. All the needed configurations were correctly done for both countries.

Configurations at a high level.

  • Added 2 contexts (and the needed segments) for the DFF JG_RA_CUSTOMER_TRX as below 
    • JA.IN.ARXTWMAI.RA_CUSTOMER_TRX
    • JA.MY.ARXTWMAI.RA_CUSTOMER_TRX
  • Set the following profiles under Profile Options:
    • JG: Applications
    • JG: Product 
    • JG: Territory
  • Added the function JG_ENABLE_GLOBAL_FLEX to the root menu of both country responsibilities

Upon conducting extensive investigation, we discovered that the 'JA.pll' was not getting called when the form was opened from the Malaysia responsibility.  This pll is supposed to be opened for the Asia Pacific countries, which includes both India and Malaysia.

Further investigation revealed that 'GLOBE.pll' was being called, but there was a code in that PLL which was missing Malaysia (MY) in the list within the procedure 'GET_PRODUCT_FROM_COUNTRY'.

So, the solution was to modify 'GLOBE.pll' and add the country code 'MY' to the list.

The modified code looks like below:



After compiling the modified PLL in the server, the GDFF started appearing in the AR Transaction Form.




Note: If your version of PLL is a higher version, then this may not be an issue for you.



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

Saturday, 11 May 2024

PL/SQL: Sample code for submitting a DBMS_SCHEDULER jobs

 In this post I will provide a sample simple code to submit a dbms_scheduler job.

  • Create a procedure. You can create a procedure inside a package also if needed.
 --create table   
 CREATE TABLE xxaj_debug (  
              ,id         NUMBER  
              ,msg        VARCHAR2(4000)  
              ,call_stack VARCHAR2(4000)  
              ,date_stamp DATE  
             );  
 --create sequence   
 CREATE SEQUENCE xxaj_seq  
  START WITH 1  
  INCREMENT BY 1; 
  
 --create procedure   
 CREATE OR REPLACE PROCEDURE xxaj_proc (msg VARCHAR2)  
 IS  
  PRAGMA autonomous_transaction;  
 BEGIN  
   INSERT INTO xxaj_debug  
   VALUES ( xxaj_seq.NEXTVAL  
           ,msg  
           ,dbms_utility.format_call_stack  
           ,SYSDATE);  
   COMMIT;  
 END xxaj_proc;   

You can execute the below code to submit the above procedure as a dbms job.
            Parameters
                job_name      : you can give any name. Give something meaningful so that you can filter with 
                                        the  name when trouble shooting
                job_type        : in this case it is a stored procedure
                job_action     : in this case we are executing a stored procedure, so specify the procedure                                                  name
                enabled         : we want the job to run straight away, so set to true  

 BEGIN  
   dbms_scheduler.create_job (job_name   => 'XXAJ_JOB_'||TO_CHAR(SYSDATE, 'DDMMHH24MI')  
                             ,job_type   => 'STORED_PROCEDURE'  
                             ,job_action => 'xxaj_proc'  
                             ,enabled    => TRUE);  
 END;   

There are lot more options on the job submission. I will create a separate blog with different variances. 

You can also refer the below oracle site for the details.

Refer:Scheduler (DBMS_SCHEDULER) in Oracle Database 10g Onward



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

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.