Useful Workflow Commands

 

WFLOAD apps/columbus789 0 Y DOWNLOAD APEXP_FINDEV.wft APEXP

Locations:

$PO_TOP/patch/115/import/US/porpocha.wft

$PO_TOP/patch/115/import/US/poxwfpag.wft

$PO_TOP/patch/115/import/US/poappame.wft

Actions:

porpocha.wft - Updated ACCESS_LEVEL from 20 to 0

poxwfpag.wft - Updated ACCESS_LEVEL from 20 to 0

poappame.wft - Updated ACCESS_LEVEL from 20 to 0

Command/Results should be:

$FND_TOP/bin/WFLOAD apps/apps_password 0 Y UPLOAD $PO_TOP/patch/115/import/US/porpocha.wft

$FND_TOP/bin/WFLOAD apps/apps_password 0 Y UPLOAD $PO_TOP/patch/115/import/US/poxwfpag.wft

$FND_TOP/bin/WFLOAD apps/apps_password 0 Y UPLOAD $PO_TOP/patch/115/import/US/poappame.wft

WFLOAD apps/columbus789 0 Y DOWNLOAD /u02/migrate/nagaraj/files/workflow/porpocha.wft porpocha

WFLOAD apps/columbus789 0 Y DOWNLOAD /u02/migrate/nagaraj/files/workflow/poxwfpag.wft poxwfpag

WFLOAD apps/columbus789 0 Y DOWNLOAD /u02/migrate/nagaraj/files/workflow/poappame.wft poappame

/u01/applmgr/fs1/EBSapps/appl/po/12.0.0/patch/115/import/US

cp porpocha.wft /u02/migrate/nagaraj/files/workflow

cp poxwfpag.wft /u02/migrate/nagaraj/files/workflow

cp poappame.wft /u02/migrate/nagaraj/files/workflow

cp poxwfnot.wft /u02/migrate/nagaraj/files/workflow

$FND_TOP/bin/WFLOAD apps/columbus789 0 Y UPLOAD /u01/applmgr/fs1/EBSapps/appl/po/12.0.0/patch/115/import/US/porpocha.wft

$FND_TOP/bin/WFLOAD apps/columbus789 0 Y UPLOAD /u01/applmgr/fs1/EBSapps/appl/po/12.0.0/patch/115/import/US/poxwfpag.wft

$FND_TOP/bin/WFLOAD apps/columbus789 0 Y FORCE /u01/applmgr/fs1/EBSapps/appl/po/12.0.0/patch/115/import/US/poappame.wft

$FND_TOP/bin/WFLOAD apps/columbus789 0 Y UPLOAD /u01/applmgr/fs1/EBSapps/appl/po/12.0.0/patch/115/import/US/poxwfnot.wft

WFLOAD apps/columbus789 0 Y FORCE /u02/migrate/nagaraj/files/workflow/access/poxwfnot.wft

Script to add employee as buyer in Purchasing

 DECLARE

   l_agent_id          NUMBER;

   l_employee_number   VARCHAR2 (20) := 120273;

   l_buyer_rowid       VARCHAR2 (100);

BEGIN

   BEGIN

      SELECT person_id

        INTO l_agent_id

        FROM apps.per_all_people_f f

       WHERE employee_number = l_employee_number;

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line (   'Unable to find Agent ID for '

                               || l_employee_number

                               || SQLERRM

                              );

   END;

   apps.po_agents_pkg.insert_row (x_rowid                    => l_buyer_rowid,

                                  x_agent_id                 => l_agent_id,

                                  x_last_update_date         => SYSDATE,

                                  x_last_updated_by          => 0,

                                  x_last_update_login        => 0,

                                  x_creation_date            => SYSDATE,

                                  x_created_by               => 0,

                                  x_location_id              => NULL,

                                  x_category_id              => NULL,

                                  x_authorization_limit      => NULL,

                                  x_start_date_active        => SYSDATE,

                                  x_end_date_active          => NULL,

                                  x_attribute_category       => NULL,

                                  x_attribute1               => NULL,

                                  x_attribute2               => NULL,

                                  x_attribute3               => NULL,

                                  x_attribute4               => NULL,

                                  x_attribute5               => NULL,

                                  x_attribute6               => NULL,

                                  x_attribute7               => NULL,

                                  x_attribute8               => NULL,

                                  x_attribute9               => NULL,

                                  x_attribute10              => NULL,

                                  x_attribute11              => NULL,

                                  x_attribute12              => NULL,

                                  x_attribute13              => NULL,

                                  x_attribute14              => NULL,

                                  x_attribute15              => NULL

                                 );

   COMMIT;

   DBMS_OUTPUT.put_line ('Setup as a buyer ' || l_employee_number);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error in Setting up as Buyer ' || SQLERRM);

END;

Force LTR to print right aligned in BI Report

 <fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?format-number:<column name>;'###,###,##0.00'?></fo:bidi-override>

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"></fo:bidi-override>

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?TASK_NUMBER?></fo:bidi-override>

Download Concurrent Program LDT File

 Download Concurrent Programs:

FNDLOAD apps/matrix O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Add Responsibility to the user via Script

 

DECLARE

   v_user_name             VARCHAR2 (30)  := '&user_name';

   v_responsibility_name   VARCHAR2 (100) := '&responsibility_name';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key    VARCHAR2 (100) := NULL;

   v_security_group        VARCHAR2 (100) := NULL;

   v_description           VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description

     INTO v_application_name, v_responsibility_key,

          v_security_group, v_description

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name = v_responsibility_name

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_id = fr.responsibility_id

      AND fr.application_id = fa.application_id

      AND fr.data_group_id = fsg.security_group_id;

   fnd_user_pkg.addresp (username            => v_user_name,

                         resp_app            => v_application_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

   COMMIT;

   DBMS_OUTPUT.put_line(   'Responsiblity '

                         || v_responsibility_name

                         || ' is attached to the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

/

Materialized Views in 12.2.X

 There are two steps to create Materialized Views in 12.2

1. Create Logical View
2. Upgrade to Materialized View

Create Logical View
CREATE OR REPLACE <Custom Schema>.XXBLOG_CREATE_LOGICAL_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXBLOG_CREATE_LOGICAL_MV');

How to check if the Materialized Views got created properly in 12.2
SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXBLOG_CREATE_LOGICAL_MV%';

-- 4 rows Selected

These 4 Rows get created in the process for

1. Logical View (Ending with '#') in Custom Schema
2. Materialized View in Custom Schema
3. Table in Custom Schema
4. Synonym in APPS

Possible Issue:
Create mview gives the following error when the mview definition query refers to editioned function:
ORA-38818: illegal reference to editioned object %.%
Possible Solution:
Re-create the referenced pl/sql objects in a schema where editioning is not turned on
If the referenced objects are be placed in a non editioned schema that is different from the schema where the mview is to be created, then grant execute permission on the package to the mview owner.References:
12.2 E-Business Suite Upgrade Of Custom Materialized Views Fails Due To Changes Required For 12.2 ADOP Editioned Objects (Doc ID 2205375.1)
ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)

How to Refresh Materialized Views:
exec DBMS_MVIEW.REFRESH('XXBLOG_HOWTOREFRESH_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);


Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

Out-of-place refresh: It uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well.

Oracle Branding Logo Change

Oracle Branding Logo Change

Step 1 : Place the XXX.Png file under $OA_MEDIA directory in App Tier.

Step 2 : Query the Profile & Change the value . 

              System Administrator > Profile - System.

              Query profile "Corporate Branding Image for Oracle Applications"

              Change the SITE level value to the name of the custom image file { XX_CUSTOM_LOGO.jpg }

Step 3 :  Clear the Cache From Functional Administrator.

OAF Controller Functions

1. Deriving Various Bean values

a. messageStyledText

OAMessageStyledTextBean lvariable = (OAMessageStyledTextBean)webBean.findChildRecursive("column1");

b. messageTextInput

OAMessageTextInputBean lvariable = (OAMessageTextInputBean)webBean.findChildRecursive("column1");

c. messageChoice

OAMessageChoiceBean lvariable = (OAMessageChoiceBean)webBean.findChildRecursive("column1");

d. messageLovInput

OAMessageLovInputBean lvariable = (OAMessageLovInputBean)webBean.findChildRecursive("column1");

e. submitButton

OASubmitButtonBean lvariableSubmitButton = (OASubmitButtonBean)webBean.findChildRecursive("submitButton1");

f. button

OAButtonBean lvariableButton = (OAButtonBean )webBean.findChildRecursive("button1");

2. To commit the database transactions

getTransaction().commit();

3. To discard the database transactions

getTransaction().rollback();

4. To Forward a page from one screen to another

if (pageContext.getParameter("nextPage")!=null) {

      pageContext.forwardImmediately("OA.jsp?page=/oracle/apps/fnd/framework/newpage/webui/xxSearchPG",
                                           null,
                                           OAWebBeanConstants.KEEP_MENU_CONTEXT,
                                           null,
                                           params,
                                           true, // retain AM
                                           OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
     

    }

5. To initiate an Application Module (AM)

xxSearchAMImpl am = (xxSearchAMImpl)pageContext.getApplicationModule(webBean);
 
am.invokeMethod("apply");

6. To put a session value

pageContext.putSessionValue("currentPage","Page1");

7. To get a session value

String variableNew = (String)pageContext.getSessionValue("variableOld");

8. To hide a button/submit button using the Render property

OASubmitButtonBean SaveVariable = (OASubmitButtonBean)webBean.findChildRecursive("button1");

SaveVariable.setRendered(false);

SaveVariable.setRendered(true);

9. Forming Dynamic Parameter in Controller

HashMap params = new HashMap(1);
params.put("PageMode","CREATE");

      if ( pageContext.getParameter("PageMode")!=null && pageContext.getParameter("PageMode").equals("CREATE")  )

       {
     
System.out.println("Page for Create Action");

 am.invokeMethod("createRecord");

//Set Parameter value for PageMode back to Original State.
             
           HashMap params = new HashMap(1);
           params.put("PageMode","NULL");
         
         }

Hide a mandatory field | OAF Page

If the business wants to hide a field in the seeded page, you can do it by personalizing the page and set Rendered property to false.
But this solution will not help you if the field is marked as mandatory by the seeded page.

The reasons are Mandatory Validations are usually done at the EO level. Hence even if you hide the mandatory field, the validation would still happen at the server. If the column has not null constraint in the table, it will throw a database error.


How to solve this ?

You can fix this by setting a default value for the mandatory field.

Extend the controller and set the value in the process Request() method. This is the safest way to default the value to the mandatory field.

After defaulting the mandatory variable, personalize the page and set the rendered property to false.

Hope it helps !!

OAF Date Field conversion to String using standard library SimpleDateFormat

In OAF if we create an attribute with DATE type then the value retained will be in the format of  yyyy-MM-dd hh:mm:ss.S

Ex: 2019-05-25 00:00:00.0

In order to convert the date format from yyyy-MM-dd hh:mm:ss.S to dd-MMM-yyyy, use the below piece of snippet.

Code to handle the Date Conversion:

import java.text.SimpleDateFormat;

    String convStr1=null;

OAMessageDateFieldBean  par1=(OAMessageDateFieldBean )webBean.findIndexedChildRecursive("crTargetFrom");             
                Timestamp TargetFrom = (Timestamp)par1.getValue(pageContext);

System.out.println("Target From :"+TargetFrom);

SimpleDateFormat format                = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
SimpleDateFormat formatDate            = new SimpleDateFormat("dd-MMM-yyyy");

                try
                   {

                    java.util.Date dateStr1 = format.parse(TargetFrom.toString());

                    convStr1 = formatDate.format(dateStr1);

                                     
                    System.out.println("String date Converted : "+convStr1);

                    }
                 
                catch (Exception e)
                    {

                    System.out.println("String date exception "+e);

                    }

Throw Bundled Exception | OAF Page

  ArrayList bundle=new ArrayList();     //first we have to initialize the Array List

             

  if(empname.contains("@"))

                {

                bundle.add(new OABundleeption("Special character not allowed",OABundleeption.ERROR));

                }

  if("".equalsIgnoreCase(pageContext.getParameter("StartDate").trim()))

                {

                bundle.add(new OABundleeption("Start date shold not be null",OABundleeption.ERROR));

                }

  if(!bundle.isEmpty())

                {

                OABundleeption.raiseBundledOABundleeption(bundle);

                }

                }

List all the Parameter names and values | OAF Page

import java.util.Enumeration; 

Enumeration enums = pageContext.getParameterNames(); 

 while(enums.hasMoreElements())

{ 

   String paramName = enums.nextElement().toString(); 

   System.out.println("Param name:-->" +paramName+ ";Value:-->"+pageContext.getParameter(paramName)); 

 }

Create VO Programmatically | Dynamic VO | OAF

ViewObject dynamicViewObject = appModule.findViewObject("XXDynamicVO"); 

 if(dynamicViewObject == null) 

 { 

  String voQuery = " SELECT USER_NAME " + 

                                " FROM fnd_users FU " + 

                                " WHERE FU.user_id = :1 "; 

  customViewObject = appModule.createViewObjectFromQueryStmt("XXDynamicVO",voQuery); 

 } 

 if(customViewObject != null) 

 { 

  customViewObject.setWhereClause(null); 

  customViewObject.setWhereClauseParams(null); 

  customViewObject.setWhereClauseParam(0,userID); 

  customViewObject.executeQuery(); 

  Row customViewObjectRow = customViewObject.first(); 

  if(customViewObjectRow != null) 

  { 

       String userName = (String)customViewObjectRow.getAttribute(0); 

  } 

 }  

Call PL/SQL Procedure | From OAF Page

 import oracle.jdbc.OracleCallableStatement;  

 import oracle.jdbc.OracleTypes;

 import oracle.jbo.domain.Number;

 import oracle.jbo.domain.Date;

   

 public void callPLSQLProc(Number id,String code,Date currentDate)  

 {  

      OracleCallableStatement callableStatement = null;  

      try  

      {  

           String callProc = " BEGIN xxx_pkg.xxx_proc "+  

                                         "(p_id             => :1," +  

                                         " p_code           => :2," +

                                         " p_date           => :3," +

                                         " p_commit         => :4," +                                                  

                                         " x_return_status  => :5," +  

                                         " x_msg_data       => :6," +  

                                         " x_msg_count      => :7);"+  

                                    " END; ";  

           callableStatement = (OracleCallableStatement)getOADBTransaction().createCallableStatement(callProc,1);  

           callableStatement.setNUMBER(1, id);  

           callableStatement.setString(2, code);          

           callableStatement.setDATE(3, currentDate);    

           callableStatement.setString(4, "Y");     

           callableStatement.registerOutParameter(5,OracleTypes.VARCHAR,255);  

           callableStatement.registerOutParameter(6,OracleTypes.VARCHAR,255);  

           callableStatement.registerOutParameter(7,OracleTypes.NUMBER,255);    

           callableStatement.execute();  

           String resultMessage = (String)callableStatement.getString(5);  

           String msgData  = (String)callableStatement.getString(6);  

      }  

      catch(Exception e)  

      {  

           e.printStackTrace();  

           throw new OAException(e.toString(),OAException.ERROR);  

      }  

      finally  

      {  

           try  

           {  

                callableStatement.close();  

           }  

           catch(Exception exception2)  

           {  

                throw OAException.wrapperException(exception2);  

           }  

      }  

 }        

:1,:2,:3 & :4 è IN Parameter   

:5,:6,:7 è Out Parameter

Call PL/SQL Function | From OAF Page

 import oracle.apps.fnd.framework.server.OADBTransaction; 

 import oracle.jdbc.driver.OracleTypes; 

 import oracle.jdbc.OracleCallableStatement;

 

 public String callFunction( String id) 

 { 

      String status = null;

      OADBTransaction oadbtransaction = getOADBTransaction(); 

      OracleCallableStatement oraclecallablestatement = null; 

      Object obj = null; 

     

      String sqlString = "BEGIN :1:=xx_pkg.xx_func( p_id => :2 ); END;"; 

      

      try 

      { 

           oraclecallablestatement =             (oracleCallableStatement)oadbtransaction.createCallableStatement(sqlString , 1); 

           oraclecallablestatement.registerOutParameter(1, OracleTypes.VARCHAR, 0, 10); 

           oraclecallablestatement.setString(2, id); 

           oraclecallablestatement.executeQuery(); 

           status = String.valueOf(oraclecallablestatement.getString(1)); 

      } 

      catch(Exception exception1) 

      { 

           throw OAException.wrapperException(exception1); 

      } 

      finally 

      { 

           try 

           { 

                oraclecallablestatement.close(); 

           } 

           catch(Exception exception2) 

           { 

                throw OAException.wrapperException(exception2); 

           } 

      } 

      return status ; 

 }  

:1 è Out Parameter to hold results from function.

:2 è IN Parameter   

 

Display Current Date | OAF Page

        To Create an Date Bean programmatically

        OAMessageDateFieldBean currentdate =  (OAMessageDateFieldBean)createWebBean(pageContext,

                                                                                      OAWebBeanConstants.MESSAGE_DATE_FIELD_BEAN,

                                                                                       null,

                                                                                           "datefield");

                                                                                           currentdate.setReadOnly(true);

        pageLayoutBean.addIndexedChild(currentdate);

               

         To Initialize Date

               

   OAApplicationModule am = pageContext.getApplicationModule(webBean);--Initialize/Invoke AM

   SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");   -- Formatting

   String dateString = am.getOADBTransaction().getCurrentDBDate().toString();--Get DB Date from AM

   java.sql.Date sqlDate = null;

        Parsing Date Format 

        try {

            sqlDate = new Date(f.parse(dateString).getTime());

            System.out.println("What is sqlDate value ********** " + sqlDate);

         }

        catch (ParseException e) {

            e.getMessage();

        }

        OAMessageDateFieldBean dateField =

            (OAMessageDateFieldBean)webBean.findIndexedChildRecursive("datefield");

       

        To set current Date in Bean

        if (dateField != null) {

            dateField.setValue(pageContext, sqlDate);

            System.out.println("I am checking dateField  not null ********* " + dateField );

        }

Oracle Applications Concurrent Request phase codes and status codes

Table Name: FND_CONCURRENT_REQUESTS

Column Name: PHASE_CODE
 

Value	Meaning
C	Completed
I	Inactive
P	Pending
R	Running

 
Table Name: FND_CONCURRENT_REQUESTS

Column Name: STATUS_CODE
 

Value	Meaning
D	Cancelled
U	Disabled
E	Error
M	No Manager
R	Normal
I	Normal
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
Z	Waiting
G	Warning