Report Printing in EXCEL and PDF format in oracle forms 11g

Report Printing code in oracle forms 11g  With parameter

DECLARE          
   v_parameter_id PARAMLIST;
   v_report_title  VARCHAR2(200) := NULL;
   v_finyear_title VARCHAR2(200) := NULL;
   v_branch_title  VARCHAR2(200) := NULL;
   v_period_title  VARCHAR2(200) := NULL;
   v_report_code   fatempreport.report_code%TYPE;
   
  --Code added by CTE
  REPORT_ID      Report_object;

BEGIN

   IF frmpkg_general.data_check() = TRUE 
THEN
   
 v_parameter_id := get_parameter_list('REPOPARAMETER');
 
  IF NOT id_null(v_parameter_id) 
  THEN
      destroy_parameter_list(v_parameter_id);
  END IF;

 v_parameter_id := create_parameter_list('REPOPARAMETER');
 IF NOT id_null(v_parameter_id) 
 THEN
 
         add_Parameter(v_parameter_id, 'DESTYPE', TEXT_PARAMETER, null);
    add_Parameter(v_parameter_id, 'DESFORMAT', TEXT_PARAMETER, null);
    add_Parameter(v_parameter_id, 'DESNAME', TEXT_PARAMETER, null);
    add_Parameter(v_parameter_id, 'COPIES', TEXT_PARAMETER, 1);
  add_parameter(v_parameter_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
         add_parameter(v_parameter_id, 'P_COMPANY_NAME', TEXT_PARAMETER,                                :GLOBAL.g_company_name);
         Message('Processing, Please wait....', NO_ACKNOWLEDGE);
         SYNCHRONIZE;
      
      IF :PARAMETER.P_REPORT_TYPE = 'JOURNAL_PERIOD_WISE'
      THEN

         v_finyear_title := TO_CHAR(:report_block.txt_from_date,:GLOBAL.g_date_format) || ' - ' || TO_CHAR(:report_block.txt_to_date,:GLOBAL.g_date_format);
         v_branch_title := :GLOBAL.g_branch_name;
         v_report_title  :=  :report_block.txt_journal_name;
         v_period_title := TO_CHAR(:report_block.txt_from_date,:GLOBAL.g_date_format) || ' - ' || TO_CHAR(:report_block.txt_to_date,:GLOBAL.g_date_format);
add_parameter(v_parameter_id, 'P_BOOK_CODE',TEXT_PARAMETER, :report_block.lb_journal);
add_parameter(v_parameter_id,'P_BRANCH_CODE',TEXT_PARAMETER,:GLOBAL.g_branch_code);
add_parameter(v_parameter_id, 'P_BRANCH_TITLE', TEXT_PARAMETER, v_branch_title);
add_parameter(v_parameter_id,'P_FINYEAR_CODE',TEXT_PARAMETER,:GLOBAL.g_finyear_code);
add_parameter(v_parameter_id, 'P_FINYEAR_TITLE', TEXT_PARAMETER, v_finyear_title);
add_parameter(v_parameter_id,'P_FROM_DATE',TEXT_PARAMETER,:report_block.txt_from_date);
add_parameter(v_parameter_id, 'P_PERIOD_TITLE', TEXT_PARAMETER, v_period_title);
add_parameter(v_parameter_id, 'P_REPORT_TITLE', TEXT_PARAMETER, v_report_title);
add_parameter(v_parameter_id, 'P_VOUCHERTYPE', TEXT_PARAMETER, :report_block.txt_voucher_type); 
add_parameter(v_parameter_id, 'P_TO_DATE', TEXT_PARAMETER, :report_block.txt_to_date);
add_parameter(v_parameter_id, 'P_HIDE_CANCELLED', TEXT_PARAMETER, :report_block.chk_hide_cancelled);
    
REPORT_ID:=FIND_REPORT_OBJECT('REPOBJ');
                      SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,'JournalPeriodWise');
                   web_report_xl(REPORT_ID, v_parameter_id);
      END IF;
         
         Message(' ');

  destroy_parameter_list(v_parameter_id);
 END IF;
 
   END IF;
END;

/*----------------------------Excel Print------------------*/
PROCEDURE WEB_REPORT_XL (REPID REPORT_OBJECT, PLID PARAMLIST)IS
  REPORT_JOB_ID     VARCHAR2(100);  
  REP_STATUS        VARCHAR2(100);  
  REPORTSERVERJOB   VARCHAR2(100);    
BEGIN  
SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_COMM_MODE,SYNCHRONOUS);
  SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_DESTYPE,CACHE);                
  SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_DESFORMAT,'SPREADSHEET');
SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_SERVER,'repserv');
  REPORT_JOB_ID := RUN_REPORT_OBJECT (REPID,PLID);  
  REP_STATUS := REPORT_OBJECT_STATUS (REPORT_JOB_ID);  
  IF REP_STATUS = 'FINISHED' THEN
WEB.SHOW_DOCUMENT ('http://100.100.11.100:8888/reports/rwservlet/getjobid='||substr(report_job_id,instr(report_job_id,'_',-1)+1)||'?server='||'repserv'||'+MIMETYPE=REPORTS/LOCAL','_self');
ELSE
    MESSAGE ('Report Failed with Error - ' || REP_STATUS);
  END IF;
END;

/*----------------------------Pdf Print------------------*/
PROCEDURE WEB_REPORT (REPID REPORT_OBJECT, PLID PARAMLIST)IS
  REPORT_JOB_ID     VARCHAR2(100);  
  REP_STATUS        VARCHAR2(100);  
  REPORTSERVERJOB   VARCHAR2(100);    
BEGIN  
SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_COMM_MODE,SYNCHRONOUS);
  SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_DESTYPE,CACHE);                
  SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_DESFORMAT,'PDF');
SET_REPORT_OBJECT_PROPERTY (REPID,REPORT_SERVER,'repserv');
  REPORT_JOB_ID := RUN_REPORT_OBJECT (REPID,PLID);  
  REP_STATUS := REPORT_OBJECT_STATUS (REPORT_JOB_ID);  
  IF REP_STATUS = 'FINISHED' THEN
WEB.SHOW_DOCUMENT ('http://100.100.100.11:8888/reports/rwservlet/getjobid='||substr(report_job_id,instr(report_job_id,'_',-1)+1)||'?server='||'repserv','_blank');
ELSE
    MESSAGE ('Report Failed with Error - ' || REP_STATUS);
  END IF;
END;

Read More

Auto refresh form in oracle forms and reports 11g

This is the code show how to auto refresh the values shown in the form it will auto refresh in every 5 min or given time and display the result.

1) you need to design the form as per your requirement
2)then you need to write code in two trigger
   a).WHEN-TIMER-EXPIRED
   b).WHEN-NEW-FORM-INSTANCE


a).WHEN-TIMER-EXPIRED :

DECLARE 
  timer_id  TIMER; 
  v_count NUMBER;
  CURSOR CFP 
  IS 
  select BMR.BMRBATCHMST_CODE,bmr.BMR_no,bmr.BMR_DATE,bmr.batch_code,IT.description pro_desc,decode(PROCESS_CATEGORY_STATUS,'INP','INPROGRESS - ') ||' UNDER - '||CM.description as status from BMRPRODUCTBATCHMASTER BMR,items IT,
  BMRBATCHPROCESSCATEGORY BPC,pdssstepcategory CM where bmr.product_code = it.sl_no and BPC.PROCESS_CATEGORY = CM.code
  and BPC.BMRBATCHMST_CODE = BMR.BMRBATCHMST_CODE and PROCESS_CATEGORY_STATUS = 'INP' order by BMR.BMRBATCHMST_CODE ;
BEGIN   
  timer_id := FIND_TIMER('refresh_timer');
   BEGIN

        GO_BLOCK('SHOWATTENDANCEDETAILS_BLOCK');
        Clear_Block(No_Validate);
       For mFP  IN CFP  LOOP 
        :showattendancedetails_block.BMRBATCHMST_CODE := mfp.BMRBATCHMST_CODE;
        :showattendancedetails_block.BMR_NO := mfp.BMR_no;
        :showattendancedetails_block.BMR_DATE := mfp.BMR_DATE;
        :showattendancedetails_block.BATCH_NO:= mfp.batch_code;
        :showattendancedetails_block.PRODUCT_NAME := mfp.pro_desc;
        :showattendancedetails_block.STATUS:= mfp.status;
          NEXT_RECORD;
        SYNCHRONIZE;
       END LOOP;
    
 END; 
END;

b).WHEN-NEW-FORM-INSTANCE

DECLARE
   v_count NUMBER;
   timer_id Timer; 
   five_sec NUMBER(5) := 15000;

BEGIN

timer_id := CREATE_TIMER('refresh_timer', five_sec,REPEAT);

EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN
     exit_form(NO_VALIDATE);

END;


Here You can see Full output of the form 

Read More