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));
         }