Oracle Forms

Hi All,

I am using Oracle Database 11g, with Oracle Forms 11g.

We have a multiple forms that also call reports.

In some form multiple reports are calling, while some form calls only one report.

We have created below procedure to call the report, that is working fine in whole application, we only have a little issue when there are multiple reports are calling with different parameters. i.e.

Report 1 calls with From_Date and End_Date parameters

Report 2 calls with Department Parameter

Report 3 Calls with Section, Employee_Id parameters.

PROCEDURE CALL_REPORT(P_REPORTSERVERNAME varchar2,
                          P_print_mode       VARCHAR2,
                          P_REPORT_NAME      VARCHAR2,
                          P_PARAM_FORM       VARCHAR2) IS
  pl_id        ParamList;
  pl_name       VARCHAR2(20) := 'da_data';
  v_SRCDESNAME    VARCHAR2(200) := :global.REP_FSRC||:global.user_name||'_'|| P_REPORT_NAME;
  v_DST_DESNAME    VARCHAR2(200) := :global.REP_FDST||:global.user_name||'_'|| P_REPORT_NAME;     
       
  V_REPID       REPORT_OBJECT;
  V_REPORTSERVERNAME VARCHAR2(200) := P_REPORTSERVERNAME;
  V_REPORTSERVERJOB  VARCHAR2(200);
  V_REPORTSTATUS   VARCHAR2(100); -- 6i
  V_JOBID       VARCHAR2(200);
  MDATE        date;
  xstatus       BOOLEAN;
  /*Follwoing variables for calling report with Parameter Form*/
  vc_url           varchar2(2000);
  vc_user_name     VARCHAR2(100);
  vc_user_password VARCHAR2(100);
  vc_user_connect  VARCHAR2(100);
  vc_connect       VARCHAR2(300);
  vc_report varchar2(100) := :GLOBAL.REP_PATH || P_REPORT_NAME || '.rep';
  vc_server varchar2(50) := P_REPORTSERVERNAME;
  /*Follwoing variables for calling report with Parameter Form*/

BEGIN
  IF P_PARAM_FORM = 'N' THEN
    --message('V_REPORTSERVERNAME : '||V_REPORTSERVERNAME); -- hassan for testing  
    --message('P_REPORT_NAME : '||P_REPORT_NAME); -- hassan for testing
  
    /* Coding For Report Parametes - Starts Here*/
    pl_id := GET_PARAMETER_LIST(pl_name);
  
    IF NOT ID_NULL(pl_id) THEN
      DESTROY_PARAMETER_LIST(pl_id);
    END IF;
  
    pl_id := CREATE_PARAMETER_LIST(pl_name);
    IF ID_NULL(pl_id) THEN
      MESSAGES('Error creating parameter list ' || pl_name);
      --MESSAGE('Error creating parameter list ' || pl_name);
      RAISE FORM_TRIGGER_FAILURE;
    END IF;
  
    V_REPID := FIND_REPORT_OBJECT('REPS');
  
    -- Just Replace the following Parameters when required parameters of the calling report
    /* Coding For Report Parametes - Ends Heare*/
  
    SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_EXECUTION_MODE, BATCH);
    SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_COMM_MODE, SYNCHRONOUS);
    SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_SERVER, V_REPORTSERVERNAME);
    SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_FILENAME,:GLOBAL.rep_path ||P_REPORT_NAME||'.rep');
    
    IF P_print_mode <> 'PRINT' THEN     
      ADD_PARAMETER(pl_id, 'DESTYPE', TEXT_PARAMETER, FILE);
      ADD_PARAMETER(pl_id, 'DESFORMAT', TEXT_PARAMETER, P_print_mode);
      ADD_PARAMETER(pl_id, 'DESNAME',   TEXT_PARAMETER, v_SRCDESNAME||'.'||SUBSTR(P_print_mode, 1, 3));
      
      SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_DESTYPE, FILE);
      SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_DESFORMAT, P_print_mode);
      SET_REPORT_OBJECT_PROPERTY(V_REPID, REPORT_DESNAME, v_SRCDESNAME || '.' ||SUBSTR(P_print_mode, 1, 3));
    else
      ADD_PARAMETER(pl_id, 'DESTYPE',   TEXT_PARAMETER, CACHE);
      ADD_PARAMETER(pl_id, 'DESFORMAT', TEXT_PARAMETER, P_print_mode);
      ADD_PARAMETER(pl_id, 'DESNAME',   TEXT_PARAMETER, v_SRCDESNAME||'.'||SUBSTR(P_print_mode, 1, 3));
    END IF;
  
    V_REPORTSERVERJOB := RUN_REPORT_OBJECT(V_REPID, pl_id);
    V_JOBID           := SUBSTR(V_REPORTSERVERJOB,
                                LENGTH(V_REPORTSERVERNAME) + 2,
                                LENGTH(V_REPORTSERVERJOB));
                                
    V_REPORTSTATUS    := REPORT_OBJECT_STATUS(V_REPORTSERVERJOB);
  
    WHILE V_REPORTSTATUS in ('RUNNING','OPENING_REPORT','ENQUEUED') 
    LOOP 
      V_REPORTSTATUS := report_object_status(V_REPORTSERVERJOB); 
    END LOOP; 
 
  IF V_REPORTSTATUS = 'FINISHED' THEN 
    IF P_print_mode <> 'PRINT' THEN
  DOWNLOAD_AS(v_SRCDESNAME||'.' ||SUBSTR(P_print_mode, 1, 3), v_DST_DESNAME||'.' ||SUBSTR(P_print_mode, 1, 3));
        
      MESSAGES ('FILE ' ||v_DST_DESNAME || '.' ||SUBSTR(P_print_mode, 1, 3)||' has been created. You can view it ');
    
    ELSE
      WEB.SHOW_DOCUMENT('/reports/rwservlet/getjobid=' || V_JOBID ||'?paramform=no','_blank'); 
      SYNCHRONIZE;
    END IF;
    
  END IF;
  
  
    
  ELSE
  
    vc_user_name     := get_application_property(username);
    vc_user_password := get_application_property(password);
    vc_user_connect  := get_application_property(connect_string);
    vc_connect       := vc_user_name || '/' || vc_user_password || '@' ||
                        vc_user_connect;
    vc_url := :GLOBAL.REP_URL||'/reports/rwservlet?server=' ||
              vc_server || '&report=' || vc_report ||
              '&desformat=pdf&destype=cache' || '&userid=' || vc_connect ||
              '&P_REP_NM=' || vc_report || '&P_ACTION=' ||
              :GLOBAL.REP_URL||'/reports/rwservlet?' ||
              '&P_SERVERNAME=' || vc_server || '&P_USER_CONNECT=' ||
              vc_connect || '&paramform=yes';
    WEB.SHOW_DOCUMENT(vc_url, '_blank');
  END IF;
END;

Now the issue is, I want to add a parameter as ParamList in this procedure, and want to send the Report's User Parameter values in this parameter while calling this procedure to generate the report.

i.e.

RUN_PRODUCT_11G(:GLOBAL.REP_SERVER,'PRINT', 'NPADVLET','Y',p_param_list);

How it would be possible?

I also want to add Reports System parameters in this list

Thanks,

Hassan