Oracle OAF Absance - Personalizaton & Extension for Checking If Attachement available.

ADD Controller Extension

Navigation :- Employee Self-Service ->  Leave Management


Create a new leave request then  Click on Personalize page


Update the extension in the below field.

Controller Code :
========================================

package xx.oracle.apps.per.selfservice.absence.webui;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.per.selfservice.absence.webui.AbsenceCreateCO;
import oracle.jbo.ViewObject;

public class XxHRAbsCreateCOEx extends AbsenceCreateCO {
 public XxHRAbsCreateCOEx() {}

 public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
   OAApplicationModule am = pageContext.getRootApplicationModule();
   OAViewObject absenceVO = (OAViewObject)am.findViewObject("AbsenceVO");
                                         
   if("HrNext".equals(pageContext.getParameter("_FORM_SUBMIT_BUTTON")))
                        {
                       
                            String absenceAttendanceTypeId = absenceVO.first().getAttribute("AbsenceAttendanceTypeId").toString();
                           
                            String sqlLeaveTypeLookup= "SELECT decode(count(*),'0',null,count(*)) from FND_LOOKUP_VALUES_VL where lookup_type = 'XXHR_ABSANCE_EXN_LEAVE_TYPE' AND MEANING = " + absenceAttendanceTypeId;
                           
                            if (am.findViewObject("XXLeaveTypeLookup") != null) {
                                 am.findViewObject("XXLeaveTypeLookup").remove();
                               }
                               ViewObject getLeaveType = am.createViewObjectFromQueryStmt("XXLeaveTypeLookup", sqlLeaveTypeLookup);
                               getLeaveType.executeQuery();
   //&&"2091".equals(absenceAttendanceTypeId))
                        if (getLeaveType.first().getAttribute(0) != null)
       {
                             boolean b= checkAttachment( pageContext, webBean);
                             if (b){super.processFormRequest(pageContext, webBean);}
                             else{
                                //    throw new OAException(" AbsenceAttendanceTypeId :-  " + absenceAttendanceTypeId + " Leave Type Count :- " + getLeaveType.first().getAttribute(0) ,OAException.ERROR);
                                OAException message = new OAException("PER","XXHR_ABSANCE_ATTACHEMENT_MS",null ,OAException.ERROR,null);
                                pageContext.putDialogMessage(message);
                                }
                             }
                            else{super.processFormRequest(pageContext, webBean);}
                        }
   else{super.processFormRequest(pageContext, webBean);}
  }
 
 public boolean checkAttachment(OAPageContext oapagecontext,OAWebBean oawebbean)
  {
   OAApplicationModule am = oapagecontext.getRootApplicationModule();
   String amName = "";
   String objectivesAMName = "OAAttachmentsAM";
   String objectiveVOName = "FndAttachedDocumentsDomExtensionVO";
   String nestedAMArray[] = am.getApplicationModuleNames();
   oapagecontext.writeDiagnostics(this,"Root AM=>"+am.getName() + " Child AMs=>"+ nestedAMArray.length,1);
   OAApplicationModule currentAM = null;
   OAApplicationModule currentAM2 = null;
   OAApplicationModule attAM = null;
   OAViewObject vo = null;
   OAViewObject attvo = null;
   boolean isAttachmentFound = false;
   currentAM = am;
                            for(int i = 0; i < nestedAMArray.length; i++)
                                {
                                amName = nestedAMArray[i];
                                oapagecontext.writeDiagnostics(this,"Nested AM Name=>"+amName + "and amName.indexOf(objectivesAMName) "+amName.indexOf(objectivesAMName),1);
                                currentAM = (OAApplicationModule)am.findApplicationModule(amName);
                                String nestedAMArray2[] = currentAM.getApplicationModuleNames();
                                    for (String s: currentAM.getApplicationModuleNames())
                                    {
                                    currentAM2 = (OAApplicationModule)currentAM.findApplicationModule(s);
                                        for (String s2: currentAM2.getViewObjectNames())
                                        {
                                        // get attachments VO Name
                                            if ("Attach_0_FndAttachedDocumentsDomExtensionVO".equalsIgnoreCase(s2) )
                                            {
                                            attvo = (OAViewObject)currentAM2.findViewObject("Attach_0_FndAttachedDocumentsDomExtensionVO");
                                                    if(attvo!=null && attvo.getFetchedRowCount() > 0)
                                                    {
                                                    oapagecontext.writeDiagnostics(this,"View object name  Row Count"+attvo.getFetchedRowCount(),1);
                                                    isAttachmentFound = true;
                                                    break;
                                                    }
                                            }
                                        }
                                    }
                                //Get the view names
                                oapagecontext.writeDiagnostics(this,"Nested AM Value "+currentAM.getName(),1);
                                if(!(amName.indexOf(objectivesAMName)==-1))
                                {
                                    String[] viewNames = currentAM.getViewObjectNames();
                                    for (int j =0 ;j<viewNames.length ;j++ )
                                    {
                                    if(viewNames[j].indexOf(objectiveVOName) > 0)
                                    {
                                    vo = (OAViewObject)currentAM.findViewObject(viewNames[j]);
                                    if(vo!=null && vo.getFetchedRowCount() > 0)
                                    {
                                    oapagecontext.writeDiagnostics(this,"View object name "+viewNames[j]+" Row Count"+vo.getFetchedRowCount(),1);
                                    isAttachmentFound = true;
                                    break;
                                    }
                                    }
                                    }
                                    oapagecontext.writeDiagnostics(this,"Found Handle to My Nested AM " + amName ,1);
                                    }
                            }
                    return isAttachmentFound;
                }
  }

Oracle Shell Script to download Concurrent Program LDT

CUR_DIR=`pwd`
BKTIMESTAMP=`date +%d%h%Y_%H%M`
PRINTTIME=`date +%d-%h-%Y_%T`
APPS_LOGIN_ID="$1"
APPS_CONNECT="$1"
# *******************************************************************         
#  Function to Check the validity of DB Login                   
# ******************************************************************* 
CHKLOGIN(){
    if sqlplus -s /nolog <<! >/dev/null 2>&1
         WHENEVER SQLERROR EXIT 1;
         CONNECT $1 ;
         COLUMN tbl NEW_VALUE tbl_name NOPRINT;
          SELECT table_name tbl
             FROM user_tables
             WHERE table_name = '$2' ;
         EXIT;
!
     then
          echo OK
     else
          echo NOK
     fi
}

start_ldt_download()
{
echo " "
echo " "
echo "Hi Current time is  : $PRINTTIME"
echo "###########################################################################"
echo "                  Development Backup & Migration Tool                      "
echo "###########################################################################"
echo "   "
echo "   Select the desired Option from the list below..."
echo "  "
echo "  1. Concurrent Program"
read userAction
if [ "$userAction" = "1" ];then               
          concurrentLDT                                                     
        fi                               
}
read_apps_pwd()
{
while [ "$APPS_LOGIN_ID" = "" -o `CHKLOGIN "$APPS_LOGIN_ID" "DUAL"` = "NOK" ] 
do
                                                                         
    if [ "$APPS_LOGIN_ID" = "" ];then                                         
        echo "Enter APPS Password : "            
        read pass
         APPS_LOGIN_ID="APPS/$pass" 
        if [ "$APPS_LOGIN_ID" = "" ];then                                     
            APPS_LOGIN_ID="apps/apps"                                         
        fi                                                                    
    else                                                                      
        echo "APPS Userid/Passwd INCORRECT. Please Re-enter"                  
        APPS_LOGIN_ID=""                                                      
    fi                                                                        
done 
                                                                         
APPS_LOGIN=`echo $APPS_LOGIN_ID | cut -d"/" -f1`                              
APPS_PWD=`echo $APPS_LOGIN_ID | cut -d"/" -f2`

}
read_app_short_name()
{
echo "Getting Application Short Name "
APPS_SHORT_NAME=$(sqlplus -s $APPS_LOGIN_ID <<END
set heading off;
set echo off;
select fa.application_short_name
from  fnd_concurrent_programs fcp , fnd_application fa
where fcp.application_id = fa.application_id
and CONCURRENT_PROGRAM_NAME = '$cpShortName';
EXIT;
END)
while [ "$APPS_SHORT_NAME" = "" ]
do
echo "Enter Application Short Name : "
read APPS_SHORT_NAME
done
echo " Application Short Name is $APPS_SHORT_NAME"
}
read_cp_name()
{
echo "Please Enter the Concurrent Program Short Name :  "
read cpShortName
while [ "$cpShortName" = "" ]
do
echo "Please Enter the Concurrent Program Short Name :  "
read cpShortName
done
}
read_jdbc_connect()
{
 if [ "$APPS_CONNECT" = "" ]
then
  APPS_CONNECT="$AD_APPS_JDBC_URL"
fi
  if [ "$APPS_CONNECT" = "" ]
  then
    echo  "Enter APPS JDBC Connect String for RTF loader [host_ipaddr:port_no:sid]"
read APPS_CONNECT
while [ "$APPS_CONNECT" = "" ]                                  
do
   echo  "Enter APPS JDBC Connect String for RTF loader [host_ipaddr:port_no:sid]"
   read APPS_CONNECT
done
  fi
}
# +*******************************************************************+
#  Function to Copy the Files to the folder                              
# +*******************************************************************+
mcp()
{
 
#if [ -e "$2" ]
#  then
#cp $2 "$2.$BKTIMESTAMP" >> $LOG_FILE
#echo "Copied $2 to $2.$BKTIMESTAMP" >>$LOG_FILE
#  fi
 
  cp $1 $2
  if [ $? = 0 ]
  then
    echo "Copied $1 to $2" >>$LOG_FILE
  else
    echo "Copy of $1 failed!! Exiting.... " >>$LOG_FILE
    exit 2
  fi
}
concurrentLDT()
{                 
     read_apps_pwd          
     read_cp_name
     read_app_short_name
     read_jdbc_connect
     download_ldt
     mcp $XXINJ_TOP/reports/US/$cpShortName.rdf $CUR_DIR/$cpShortName.rdf
}
# *******************************************************************         
#  Check if APPS Login Id is entered else prompt to get it                    
# *******************************************************************         

download_ldt()
{
 echo "Downloading $cpShortName"

if [ "$APPS_LOGIN_ID" = "" ]
  then
    read_apps_pwd
fi

LCT_FILE="$FND_TOP/patch/115/import/afcpprog.lct"
LDT_FILE_NAME="$cpShortName.ldt"
LDT_FILE_TYPE="_CP.ldt" 
echo "Type : Concurrent Program. LDT Download Started ....."
if FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD $LCT_FILE $cpShortName$LDT_FILE_TYPE PROGRAM APPLICATION_SHORT_NAME=$APPS_SHORT_NAME CONCURRENT_PROGRAM_NAME=$cpShortName  >> $LOG_FILE
# CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE
then
   echo
   echo "Successfully loaded LDT : $2."       >> $LOG_FILE
   echo
else
   echo
   echo "Failed to load LDT."          >> $LOG_FILE
    exit 2
   echo
fi
echo "FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD $LCT_FILE $LDT_FILE_NAME PROGRAM APPLICATION_SHORT_NAME=$APPS_SHORT_NAME CONCURRENT_PROGRAM_NAME=$cpShortName"  >> $LOG_FILE

LCT_FILE="$XDO_TOP/patch/115/import/xdotmpl.lct"
LDT_FILE_NAME="$cpShortName.ldt"
LDT_FILE_TYPE="_DD.ldt" 
echo "Type : XML. LDT Download Started ....."
echo "FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD $LCT_FILE  $cpShortName$LDT_FILE_TYPE XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=$APPS_SHORT_NAME DATA_SOURCE_CODE=$cpShortName TMPL_APP_SHORT_NAME=$APPS_SHORT_NAME TEMPLATE_CODE=$cpShortName"
if FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD $LCT_FILE  $cpShortName$LDT_FILE_TYPE XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=$APPS_SHORT_NAME DATA_SOURCE_CODE=$cpShortName TMPL_APP_SHORT_NAME=$APPS_SHORT_NAME TEMPLATE_CODE=$cpShortName  >> $LOG_FILE
# CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE
then
   echo
   echo "Successfully loaded LDT : $2."       >> $LOG_FILE
   echo
else
   echo
   echo "Failed to load LDT."          >> $LOG_FILE
    exit 2
   echo
fi
echo "FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD LCT_FILE  $cpShortName$LDT_FILE_TYPE XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=$APPS_SHORT_NAME DATA_SOURCE_CODE=$cpShortName TMPL_APP_SHORT_NAME=$APPS_SHORT_NAME TEMPLATE_CODE=$cpShortName"  >> $LOG_FILE
LCT_FILE="$XDO_TOP/patch/115/import/xdotmpl.lct"
LDT_FILE_NAME="$cpShortName.ldt"
LDT_FILE_TYPE="_DD.ldt" 
echo "Downloading RTF file ....."
echo "java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME $APPS_LOGIN -DB_PASSWORD $APPS_PWD -JDBC_CONNECTION "$APPS_CONNECT" -LOB_TYPE TEMPLATE -APPS_SHORT_NAME $APPS_SHORT_NAME -LOB_CODE $cpShortName -LANGUAGE en -TERRITORY 00 -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct"
if java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME $APPS_LOGIN -DB_PASSWORD $APPS_PWD -JDBC_CONNECTION "$APPS_CONNECT" -LOB_TYPE TEMPLATE -APPS_SHORT_NAME $APPS_SHORT_NAME -LOB_CODE $cpShortName -LANGUAGE en -TERRITORY 00 -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct
# CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE
then
   echo
   echo "Successfully loaded LDT : $2."       >> $LOG_FILE
   echo
else
   echo
   echo "Failed to load LDT."          >> $LOG_FILE
    exit 2
   echo
fi
echo "FNDLOAD $APPS_LOGIN_ID O Y DOWNLOAD LCT_FILE  $cpShortName$LDT_FILE_TYPE XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=$APPS_SHORT_NAME DATA_SOURCE_CODE=$cpShortName TMPL_APP_SHORT_NAME=$APPS_SHORT_NAME TEMPLATE_CODE=$cpShortName"  >> $LOG_FILE

}
print_log()
{
echo "############ Log Messages during installation #####################"
cat $LOG_FILE
echo "############ End of Log Messages ##################################"
echo "-------------------------------------------------------------------"
echo "Script completed successfully."
echo "See $LOG_FILE for complete log information."
echo "-------------------------------------------------------------------"
}

#========================================================================================
# Actual instructions starts here.
#========================================================================================
#-------------MANDATORY & Default Parameters----------------------------------------
MRFNO="CP_Log_File"
export LOG_FILE=$CUR_DIR/$MRFNO.log
start_ldt_download
#-------------Print Log-------------------------------------------------------------
print_log

Oracle API For OAF Personalization Profile Options 11.5 to 12.2 ERP

/*
FND: Diagnostics  - Yes  -  FND_DIAGNOSTICS
Hide Diagnostics menu entry - No - FND_HIDE_DIAGNOSTICS
SLA: Enable Diagnostics - Yes - XLA_DIAGNOSTIC_MODE
Utilities:Diagnostics  - Yes - DIAGNOSTICS
FND: Enable Personalization Workbench - Yes - FND_ENABLE_PERSONALIZATION_WORKBENCH
Personalize Self-Service Defn - Yes - FND_CUSTOM_OA_DEFINTION
FND: Personalization Region Link Enabled -  Yes - FND_PERSONALIZATION_REGION_LINK_ENABLED
FND: Personalization Seeding Mode - Yes - FND_PERSONALIZATION_SEEDING_MODE
FND: Debug Log Enabled - No -  AFLOG_ENABLED (Turn Off Low-Level Diagnostic Logging)
*/

DECLARE
  l_resp_id     NUMBER;
  l_resp_app_id NUMBER;
  l_success     BOOLEAN;
 
  l_user_id   NUMBER;
 
BEGIN
SELECT USER_ID INTO l_user_id  FROM FND_USER WHERE USER_NAME = 'USERNAME' ;
  l_success := FND_PROFILE.save
               ( x_name                 => 'FND_DIAGNOSTICS'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile FND: Diagnostics Updated successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile FND: Diagnostics Update Failed at User Level. Error:'||sqlerrm);
  END IF;
 
  l_success := FND_PROFILE.save
               ( x_name                 => 'FND_HIDE_DIAGNOSTICS'
               , x_value                => 'N'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Hide Diagnostics menu entry Updated successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Hide Diagnostics menu entry Update Failed at User Level. Error:'||sqlerrm);
  END IF;
 
   l_success := FND_PROFILE.save
               ( x_name                 => 'XLA_DIAGNOSTIC_MODE'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile SLA: Enable Diagnostics  Updated successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile SLA: Enable Diagnostics   Update Failed at User Level. Error:'||sqlerrm);
  END IF;
 
     l_success := FND_PROFILE.save
               ( x_name                 => 'DIAGNOSTICS'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Utilities:Diagnostics successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Utilities:Diagnostics Failed at User Level. Error:'||sqlerrm);
  END IF;
 
 
 
     l_success := FND_PROFILE.save
               ( x_name                 => 'FND_ENABLE_PERSONALIZATION_WORKBENCH'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile FND: Enable Personalization Workbench successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile FND: Enable Personalization Workbench Failed at User Level. Error:'||sqlerrm);
  END IF;
 
       l_success := FND_PROFILE.save
               ( x_name                 => 'FND_CUSTOM_OA_DEFINTION'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile Personalize Self-Service Defn successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile Personalize Self-Service Defn Failed at User Level. Error:'||sqlerrm);
  END IF;
 
 
         l_success := FND_PROFILE.save
               ( x_name                 => 'FND_PERSONALIZATION_SEEDING_MODE'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile FND: Personalization Seeding Mode successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile FND: Personalization Seeding Mode Failed at User Level. Error:'||sqlerrm);
  END IF;
 
         l_success := FND_PROFILE.save
               ( x_name                 => 'FND_PERSONALIZATION_REGION_LINK_ENABLED'
               , x_value                => 'Y'
               , x_level_name           =>  'USER'  -- 'SITE' , 'APPL' , 'RESP' , 'USER'
               , x_level_value          => l_user_id --   null for site , application id, responsibility id , , User Id 
               , x_level_value_app_id   => NULL --  only for responsibility - l_resp_app_id
               ) ;
  IF l_success
  THEN
     DBMS_OUTPUT.put_line('Profile FND: Personalization Region Link Enabled successfully at User Level');
  ELSE
     DBMS_OUTPUT.put_line('Profile FND: Personalization Region Link Enabled Failed at User Level. Error:'||sqlerrm);
  END IF;
  Commit;
END;
/

Document of Record in Oracle HRMS Query

SELECT
    per.employee_number,
    hdt.category_code,
    hdt.system_document_type,
    dei.document_number,
    date_from,
    date_to,
    issued_date,
    dei_information_category,
    dei_information1,
    dei_information1,
    dei_information2,
    dei_information3,
    dei_information4,
    dei_information5,
    dei_information6,
    dei_information7
FROM
    hr_document_extra_info dei,
    per_all_people_f per,
    hr_document_types hdt
WHERE
    per.person_id = dei.person_id
    AND   per.business_group_id = 81
    AND   per.employee_number =:p_employee_number
    AND   per.current_employee_flag = 'Y'
    AND   trunc(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
    AND   hdt.document_type_id = dei.document_type_id




Quey With Attachement Deatils



SELECT  per.employee_number, hdt.category_code , DBMS_LOB.getlength (fl.file_data) file_length,
             fl.file_data,
             fl.file_name
        FROM hr_document_extra_info dei,
             per_all_people_f per,
             hr_document_types hdt,
             fnd_attached_documents fat,
             fnd_documents fd,
             fnd_lobs fl
       WHERE     per.person_id = dei.person_id
             AND per.business_group_id = 81
              --     AND per.employee_number =  :p_employee_number
             AND per.current_employee_flag = 'Y'
             AND TRUNC (SYSDATE) BETWEEN per.effective_start_date
                                     AND per.effective_end_date
             AND hdt.document_type_id = dei.document_type_id
             AND fat.entity_name = 'R_DOCUMENT_EXTRA_INFO'
             AND pk1_value = dei.document_extra_info_id
             AND fd.document_id = fat.document_id
             AND fl.file_id = fd.media_id

Document of Record in Oracle HRMS Download attachements

DECLARE
   CURSOR cur_new_attmt
   IS
      SELECT DBMS_LOB.getlength (fl.file_data) file_length,
             fl.file_data,
             fl.file_name
        FROM hr_document_extra_info dei,
             per_all_people_f per,
             hr_document_types hdt,
             fnd_attached_documents fat,
             fnd_documents fd,
             fnd_lobs fl
       WHERE     per.person_id = dei.person_id
             AND per.business_group_id = 81
        --     AND per.employee_number =  :p_employee_number
             AND per.current_employee_flag = 'Y'
             AND TRUNC (SYSDATE) BETWEEN per.effective_start_date
                                     AND per.effective_end_date
             AND hdt.document_type_id = dei.document_type_id
             AND fat.entity_name = 'R_DOCUMENT_EXTRA_INFO'
             AND pk1_value = dei.document_extra_info_id
             AND fd.document_id = fat.document_id
             AND fl.file_id = fd.media_id
             AND hdt.category_code = 'VISA_INFO';

   l_start           NUMBER DEFAULT 1;
   l_bytelen         NUMBER DEFAULT 32000;
   l_len_copy        NUMBER;
   l_raw_var         RAW (32000);
   l_output          UTL_FILE.file_type;
   l_inv_file_name   VARCHAR2 (100);
   l_position        NUMBER;
   l_first_rec       BOOLEAN DEFAULT TRUE;
BEGIN
   l_position := 10;

   FOR get_details IN cur_new_attmt
   LOOP
      l_inv_file_name := NULL;
      l_inv_file_name := get_details.file_name;
      l_position := 20;

      -- create or replace directory MY_PDF2 as '/var/tmp' -- Folder should have Read/Write Privs
      --select * from dba_directories --> Check if the Directory has been created Successfully
      DBMS_OUTPUT.put_line (l_inv_file_name);
      l_output :=
         UTL_FILE.fopen ('MY_PDF2',
                         l_inv_file_name,
                         'wb',
                         32767);
      DBMS_OUTPUT.put_line ('Open');
      l_position := 30;

      -- maximum size OF buffer parameter IS 32767 BEFORE
      -- which you have TO flush  your buffer
      IF get_details.file_length < 32760
      THEN
         UTL_FILE.put_raw (l_output, get_details.file_data);
         UTL_FILE.fflush (l_output);
      ELSE
         l_position := 40;
         l_start := 1;
         l_bytelen := 32000;
         l_len_copy := get_details.file_length;

         DBMS_OUTPUT.put_line (l_len_copy);

         WHILE l_start < get_details.file_length AND l_bytelen > 0
         LOOP
            l_position := 50;
            DBMS_LOB.read (get_details.file_data,
                           l_bytelen,
                           l_start,
                           l_raw_var);
            l_position := 60;
            UTL_FILE.put_raw (l_output, l_raw_var);
            l_position := 70;
            UTL_FILE.fflush (l_output);
            l_start := l_start + l_bytelen;
            l_len_copy := l_len_copy - l_bytelen;

            IF l_len_copy < 32000
            THEN
               l_bytelen := l_len_copy;
            END IF;
         END LOOP;

         l_position := 80;
         UTL_FILE.fclose (l_output);
      END IF;

      l_position := 90;
      DBMS_OUTPUT.put_line (
         'File has been Copied Successfully: ' || get_details.file_name);
   END LOOP;
END;