Migrating Oracle e-business Business Events through WFXLoad

//Migrating Oracle e-business Business Events through WFXLoad
To download Business Event System object definitions from a database in Oracle e-ebusiness to a flat XML file, you can either run the Workflow XML Loader manually, or, if you are using the standalone version of Oracle Workflow, you an use a script to run the loader.

To run the Workflow XML Loader manually, run java / jre against oracle.apps.fnd.wf.WFXLoad.

You must specify your CLASSPATH pointing to the Java Runtime Environment, the directory containing the Workflow JAR files, the Oracle JDBC implementation, and the following Workflow JAR files:
• wfjava.jar - Workflow Java utilities
• wfapi.jar - Workflow Java APIs

java -classpath "$/rt.jar:$:$/wfjava.jar:$/wfapi.jar:$/jdbc/lib/classes111.zip:" oracle.apps.fnd.wf.WFXLoad [-d|-u]   <apps_username> <apps_pwd> <server:server_port:sid> thin US <filename> [EVENTS|SUBSCRIPTIONS] <event_name>

    Specify whether you want to upload(-u) or download(-d)
    Specify the apps user name and password
    Provide the server details i.e the servername/port/sid
    Provide the name of the file to which you want to download or from which you want to upload
    Specify whether you want to download subscriptions/events
    Specify the name of the event for download

Sample for downloading events:
adjava oracle.apps.fnd.wf.WFXLoad -d aapps **** myserver:1541:sid thin US ./xx_oracle_apps_po_rfq_event_RFQActiveEvent.wfx EVENTS xx.oracle.apps.po.rfq.event.RFQActiveEvent
Sample for downloading subscriptions:
adjava oracle.apps.fnd.wf.WFXLoad -d apps **** myserver:1541:sid thin US ./xx_oracle_apps_po_rfq_event_RFQActiveEventSubscription.wfx SUBSCRIPTIONS xx.oracle.apps.po.rfq.event.RFQActiveEvent
Sample for uploading events

adjava oracle.apps.fnd.wf.WFXLoad -u apps **** myserver:1554:sid thin US  xx_oracle_apps_po_rfq_event_RFQActiveEvent.wfx
Sample for uploading subscriptions
adjava oracle.apps.fnd.wf.WFXLoad -u apps **** myserver:1554: sid thin US  xx_oracle_apps_po_rfq_event_RFQActiveEventSubscription.wfx

Load file from server directory to oracle table CLOB column by dbms_lob.loadfromfile

--Load file from server directory to oracle table CLOB column by dbms_lob.loadfromfile

CREATE OR REPLACE PROCEDURE XXEAA_LOAD_A_FILE_P
   ( p_dir_name in  VARCHAR2,
     p_file_name  in VARCHAR2
   ) IS
  l_bfile   BFILE;
  l_clob    CLOB;
BEGIN
   l_bfile := BFILENAME(p_dir_name, p_file_name);
   IF (dbms_lob.fileexists(l_bfile) = 1) THEN
      dbms_output.put_line('File Exists');
      INSERT INTO XXERP.XXEAA_QNB_HOST2HOST_CHECKS T
      (cp_request_id,FILE_TEXT,FILE_NAME)
        VALUES (xxpk_debug_Seq.nextval,               
               EMPTY_CLOB(),
               p_file_name
      ) RETURN FILE_TEXT INTO l_clob;
      L_BFILE := bfilename(p_dir_name, p_file_name);
      dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
      dbms_lob.fileclose( l_bfile );
      COMMIT;
   ELSE
     dbms_output.put_line('File does not exist');
   END IF;
   exception when others then
   xxpk_debug_prc('XXEAA_LOAD_A_FILE_P','Main Exception','ZZ',null,substr(SQLERRM,1,299)); 
END;

Download Oracle Menu FNDSLOAD Command

FNDSLOAD apps/<appspw> 0 Y LOCAL test.txt DOWNLOAD menu_name

EBS R12 Account Value Set Details with Segment Qualifier Values Query

/*EBS R12 Account Value Set Details with Segment Qualifier Values Query*/

SELECT DISTINCT fv.flex_value VALUE, fv.flex_value_meaning meaning,
                fv.description, fv.start_date_active, fv.end_date_active,
                fv.compiled_value_attributes,
                SUBSTR (fv.compiled_value_attributes, 1, 1) allow_budgeting,
                SUBSTR (fv.compiled_value_attributes, 3, 1) allow_posting,
                SUBSTR (fv.compiled_value_attributes, 5, 1) account_type,
                SUBSTR (fv.compiled_value_attributes,
                        7,
                        1
                       ) third_party_ac_type,
                SUBSTR (fv.compiled_value_attributes, 9, 1) reconcile
           FROM fnd_flex_value_sets fvs,
                fnd_flex_values_vl fv,
                fnd_flex_validation_qualifiers ffvq
          WHERE fv.flex_value_set_id = fvs.flex_value_set_id
            AND ffvq.flex_value_set_id = fv.flex_value_set_id
            AND fv.flex_value_set_id = 1015409--Account Value Set ID
            AND fv.enabled_flag = 'Y';

EBS R12 AP Invoice Auto Approval By Custom Procedure

CREATE OR REPLACE PROCEDURE xxap_inv_auto_approval (p_invoice_id IN NUMBER)
IS
   lv_invoice_amount          ap_invoices_all.invoice_amount%TYPE;
   lv_validation_request_id   ap_invoices_all.validation_request_id%TYPE;
   lv_wfapproval_status       ap_invoices_all.wfapproval_status%TYPE;  
   lv_hist_id                 NUMBER;
   lv_user_id                 NUMBER;
   lv_login_id                NUMBER;
   lv_user_name               VARCHAR2 (100)                  := 'FIN_INV';
   lv_validation_status       VARCHAR2 (60);
   lv_stop_approval_result    BOOLEAN;
   lv_hist_rec                ap_inv_aprvl_hist_all%ROWTYPE;
   lv_org_id                  ap_invoices_all.org_id%TYPE;

BEGIN
   BEGIN
      SELECT org_id, invoice_amount, wfapproval_status
        INTO lv_org_id, lv_invoice_amount, lv_wfapproval_status
        FROM ap_invoices_all
       WHERE invoice_id = p_invoice_id;

      SELECT user_id
        INTO lv_user_id
        FROM fnd_user
       WHERE user_name = lv_user_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,'Exception in first block-' || SQLERRM
                           );
         DBMS_OUTPUT.put_line ('Exception in first block-' || SQLERRM);
   END;

   BEGIN
      lv_validation_status :=
         apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
                                                   NULL,
                                                   NULL,
                                                   NULL
                                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line
                        (fnd_file.LOG,
                            'Exception retrieving invoice validation status-'
                         || SQLERRM
                        );
         DBMS_OUTPUT.put_line
                         (   'Exception retrieving invoice validation status-'
                          || SQLERRM
                         );
   END;

   IF (UPPER (lv_validation_status) <> 'APPROVED')
   THEN
      fnd_file.put_line (fnd_file.LOG, 'Invoice is not validated');
      DBMS_OUTPUT.put_line ('Invoice is not validated');
   ELSE
      /*set values for manual approval*/
      IF (lv_wfapproval_status = 'INITIATED')
      THEN
         lv_stop_approval_result :=
            ap_workflow_pkg.stop_approval
                               (p_invoice_id,
                                NULL,
                                'INV_SUM_ACTIONS_WFAPPROVE.do_force_approval'
                               );

         --Bug5527190
         IF lv_stop_approval_result = TRUE
         THEN
            fnd_file.put_line
                             (fnd_file.LOG,
                              'Invoice approval workflow is forcibly stopped'
                             );
            DBMS_OUTPUT.put_line
                              ('Invoice approval workflow is forcibly stopped');
         END IF;
      END IF;

      UPDATE ap_invoices_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      UPDATE ap_invoice_lines_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      --insert into the history table
      lv_hist_rec.history_type := 'DOCUMENTAPPROVAL';
      lv_hist_rec.invoice_id := p_invoice_id;
      lv_hist_rec.iteration := 0;
      lv_hist_rec.org_id := lv_org_id;
      lv_hist_rec.approver_name := lv_user_name;
      lv_hist_rec.amount_approved := lv_invoice_amount;
      lv_hist_rec.created_by := lv_user_id;
      lv_hist_rec.last_updated_by := lv_user_id;
      lv_hist_rec.last_update_login := -1;
      lv_hist_rec.creation_date := SYSDATE;
      lv_hist_rec.last_update_date := SYSDATE;
      lv_hist_rec.response := 'MANUALLY APPROVED';
      lv_hist_rec.approver_comments := 'System Auto Approved';
      ap_workflow_pkg.insert_history_table (lv_hist_rec);
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG,
                         'Exception in auto approval main block-' || SQLERRM
                        );
      DBMS_OUTPUT.put_line ('Exception in auto approval main block-' || SQLERRM);
END xxap_inv_auto_approval;

OAF two dates difference in Years with 2 decimal points

import oracle.jbo.domain.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageAttachmentLinkBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageStyledTextBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean; 

  if("endDateUpdtd".equals(pageContext.getParameter(EVENT_PARAM)))
         {
             System.out.println("endDateUpdtd fireAction Event Called"); 
           String sdate=pageContext.getParameter("entryStartDt");
           String edate=pageContext.getParameter("entryEndDt");
            
             System.out.println("String sdate :-" + sdate);
             System.out.println("String edate :-" + edate);
           DateFormat formatter;
             DateFormat formatter2;
             java.util.Date date; //commented on 12 May20
            java.util.Date date2;//commented on 12 May20

                    if (sdate != null && edate != null) {
                        try {
                            formatter = new SimpleDateFormat("dd-MMM-yyyy");
                            date = formatter.parse(sdate);//commented on 12 May20
                              date2 = formatter.parse(edate);//commented on 12 May20

                               DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
                              java.sql.Date sqlStartDateSd = new java.sql.Date (dateFormat.parse(sdate).getTime());
                              java.sql.Date sqlEndDateEd = new java.sql.Date (dateFormat.parse(edate).getTime());
                               Date jboDomainStartDate = new Date(sqlStartDateSd);
                               Date jboDomainEndDate = new Date(sqlEndDateEd);
                              
                              if(date.after(date2)) {
                                  System.out.println("Start Date is Greater than End Date");
                                  throw new OAException("Start Date is Greater than End Date",OAException.ERROR);
                              }
                      
                              if (date2.after(date))
                              {
                                  System.out.println("End Date is Greater than Start Date");
                                 

                                  long  noOfDays =((date2.getTime()-date.getTime() )/(1000*60*60*24));
                                  long  noOfyrs =(noOfDays/(365));

                                java.sql.Date sqlStartDate = new java.sql.Date(date.getTime());
                                java.sql.Date sqlEndDate = new java.sql.Date(date2.getTime());
                                  long noOfyrsJsql =((sqlEndDate.getTime()-sqlStartDate.getTime() )/(1000*60*60*24*365));
                                  System.out.println("String noOfyrsJsql :-" + noOfyrsJsql+ "");
                               String StrngnoOfDays=String.valueOf(noOfDays);
                                  String StrngnoOfyrs=String.valueOf(noOfyrs);

                             
                                   OAMessageTextInputBean DurationTextBean = (OAMessageTextInputBean)webBean.findChildRecursive("duration");

                                double dblDiffDays = ((jboDomainEndDate.getValue().getTime() - jboDomainStartDate.getValue().getTime()) / (1000 * 60 * 60 * 24));
                                  double  dblNoOfyrs =(dblDiffDays/(365));
                                  DecimalFormat df = new DecimalFormat("#.##");
                                  double  dblNoOfyrsFnl = Double.parseDouble(dblNoOfyrs+ ""); //df.format((double)dblNoOfyrs);
                                  String dblNoOfyrsFnlStg =df.format(dblNoOfyrsFnl);

                                  DurationTextBean.setValue(pageContext, dblNoOfyrsFnlStg+" Year(s)");
                           
                              }
                                                
                           java.sql.Date sqlDate = new java.sql.Date(date.getTime());
                           oracle.jbo.domain.Date jboDate =
                               new oracle.jbo.domain.Date(sqlDate);
                               System.out.println("jboDate"+jboDate+"sqlDate"+sqlDate);
                                              } catch (ParseException e) {
                            e.printStackTrace();
                       }
                    }
         }

Playing with Dates in Oracle Application Framework (OAF)

//Playing with Dates in Oracle Application Framework (OAF)

//below classes are used in the examples:

oracle.jbo.domain.Date;
oracle.jbo.domain.Timestamp;
java.sql.Date;
java.sql.Timestamp;
java.util.Date;
java.util.Calendar;
java.text.DateFormat;
java.text.SimpleDateFormat;
java.lang.String;

//1. Get the current Date time.

    Using java.util.Date.

 import java.util.Date; 

 Date utilDate = new Date();  
 System.out.println("utilDate :" + utilDate);  

    Using oracle.jbo.domain.Date.

 import java.sql.Timestamp;                  
 import oracle.jbo.domain.Date; 

 Timestamp sqlTimestamp = new Timestamp(System.currentTimeMillis());   
 Date jboDomainDate = new Date(sqlTimestamp); 
 System.out.println("jboDomainDate :" + jboDomainDate); 

    Using java.util.Calendar.

 import java.util.Calendar; 

 Calendar utilCalendar = Calendar.getInstance();  
 System.out.println("utilCalendar :" + utilCalendar); 

//2. Get the current Date time in oracle.jbo.domain.Date and add 10 hours to that and get the time back in oracle.jbo.domain.Date along with the time component.

 import oracle.jbo.domain.Date; 
 import java.sql.Timestamp; 
 import java.util.Calendar; 

 Timestamp datetime = new Timestamp(System.currentTimeMillis());  
 Date jboDomainStartDate = new Date(datetime);  
 System.out.println("jboDomainDate :" + jboDomainStartDate);
 
 Calendar utilCalendar = Calendar.getInstance();  
 utilCalendar.setTime(jboDomainStartDate.getValue());  
 System.out.println("utilCalendar Start:" + utilCalendar.getTime());
  
 int hoursToAdd = 10;  
 utilCalendar.add(Calendar.HOUR, hoursToAdd);  
 System.out.println("utilCalendar End:" + utilCalendar.getTime());
  
 Timestamp sqlTimeStampEnd = new Timestamp(utilCalendar.getTime().getTime());  
 Date jboDomainEndDate = new Date(sqlTimeStampEnd);  
 System.out.println("jboDomainEndDate :" + jboDomainEndDate);
 

//3. To print the Current time in DD-MON-YYYY HH24:MI:SS format.

 import java.text.SimpleDateFormat; 
 import java.text.DateFormat; 
 import java.util.Calendar; 

 DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); 
 Calendar cal = Calendar.getInstance();
 String dateStr = dateFormat.format(cal.getTime());   
 System.out.println("Current Time : " + dateStr); 

Refer: Class SimpleDateFormat
//4. To get the oracle.jbo.domain.Date in DD-MON-YYYY HH24:MI:SS format.

 import java.text.SimpleDateFormat; 
 import java.text.DateFormat; 
 import java.util.Calendar; 

 // This is a sample code to initialize the domain Date
 Timestamp datetime = new Timestamp(System.currentTimeMillis());  
 Date jboDomainDate = new Date(datetime);
        
 DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); 
 String dateStr = dateFormat.format(jboDomainDate.getValue());  
 System.out.println("Current Time : " + dateStr); 

//5. To get the difference between two oracle.jbo.domain.Date in days.

 import oracle.jbo.domain.Date; 

 Date jboDomainStartDate = null; 
 Date jboDomainEndDate  = null;

 // Code to get the date Values has to be written here
 
 int diffDays = (int)((jboDomainEndDate.getValue().getTime() - jboDomainStartDate.getValue().getTime()) / (1000 * 60 * 60 * 24)); 
 System.out.println("Diff in days:"+ diffDays); 

//6. To convert string oracle.jbo.domain.Date.

 import java.text.SimpleDateFormat;  
 import java.text.DateFormat; 
 import oracle.jbo.domain.Date; 

 try 
 { 
      DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy"); 
      java.sql.Date sqlDate = new java.sql.Date(dateFormat.parse("10-JAN-2010").getTime());  
      Date jboDomainDate = new Date(sqlDate); 
 }catch(Exception e) { 
      System.out.println(e.getMessage()); 
      // throw appropirate Error Message 
 } 

//7. Calculate the age based on the Date of Birth from a oracle.jbo.domain.Date.

  import oracle.jbo.domain.Date;  
  import java.util.Calendar; 

  Date doB = null; 
  // get the DOB and populate the doB variable 
    
  Calendar dob = Calendar.getInstance(); 
  dob.setTimeInMillis(doB.getValue().getTime()); 
    
  Calendar sysDate = Calendar.getInstance(); 
    
  int age = sysDate.get(Calendar.YEAR) - dob.get(Calendar.YEAR); 
    
  if ((dob.get(Calendar.MONTH) > sysDate.get(Calendar.MONTH)) 
       ||  
      (dob.get(Calendar.MONTH) == sysDate.get(Calendar.MONTH) &&  
           dob.get(Calendar.DAY_OF_MONTH) > sysDate.get(Calendar.DAY_OF_MONTH))) { 
    age--; 
  } 
  System.out.println("Age:" + age); 

 //8. Calculate the difference between two Dates in OAF


Date StartDate= (Date)CurrentRow.getAttribute("StartDate");
    Date CurrentDate = (Date)am.getOADBTransaction().getCurrentDBDate();
    java.sql.Date sdate = (java.sql.Date) StartDate.dateValue();
    java.sql.Date cdate = (java.sql.Date) CurrentDate.dateValue();
    int days = daysBetween(sdate,cdate );

  public int daysBetween(java.sql.Date d1, java.sql.Date d2){
     return (int)( (d2.getTime() - d1.getTime()) / (1000 * 60 * 60 * 24));
         }