PROCEDURE RUN_REPORT

PROCEDURE RUN_REPORT IS

-- Some variable you use to populate a report table named REPORT, 
-- or call some procedure to generate data for a report

v_report_uui    REPORT.report_uui%TYPE;

-- Parameter List used in call to Oracle Report "rpt_name"

p_rpt_param_list  paramlist;

  report_id       REPORT_OBJECT;
  ReportServerJob VARCHAR2(100);
  v_jobID         VARCHAR2(100);
  rep_status      VARCHAR2(20);
  reportserver    VARCHAR2(30);
  v_url           VARCHAR2(200);
  v_url2          VARCHAR2(200);

-- However you keep track of reports; this is just one way

  usr_sessionid   NUMBER;
 
  report_not_generated EXCEPTION;

BEGIN

--Get the user session ID

  SELECT USERENV('sessionid') into usr_sessionid FROM DUAL;

-- Execute database stored package.procedure "some_package.some_procedure"
-- that populates a REPORT table

some_package.some_procedure 
  (
   to_char(:block.some_item),      -- IN
   v_report_uui                    -- OUT
   );

-- Destroy the parameter list if it already exists

IF NOT Id_Null(p_rpt_param_list) then
      Destroy_Parameter_List(p_rpt_param_list);
END IF;

-- Create a Parameter List

p_rpt_param_list := Create_Parameter_List ('tmp');

-- Generate report - on the Web
-- You can add an ELSE clause to this to re-use your 6i code

IF (get_application_property(user_interface)='WEB') THEN

-- You need to provide the name of the report server
-- The "rpt_name" is a report object you create in the Form Builder Object
-- Navigator

   reportserver := :parameter.reportserver;
   report_id := find_report_object('rpt_name');

-- These five calls to the built-in cover what 10g requires

   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_COMM_MODE, SYNCHRONOUS);
   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_EXECUTION_MODE, BATCH);
   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESTYPE, FILE);
   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESFORMAT, 'pdf');
   SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_SERVER, reportserver);

-- Here is where you can add your own parameters to the report

   Add_Parameter(p_rpt_param_list,'paramform',TEXT_PARAMETER,'no');
   
   Add_Parameter(p_rpt_param_list,'p_report_uui',TEXT_PARAMETER, to_char(v_report_uui));

   Add_Parameter(p_rpt_param_list,'PRINTJOB',TEXT_PARAMETER,'NO');

-- A common error message has to do with not being able to find the report object.
-- No report object means no report_id, and you'll get lots of errors.

   ReportServerJob := run_report_object(report_id, p_rpt_param_list);
   v_jobID := substr(ReportServerJob,length(reportserver)+2,length(ReportServerJob));

   IF ReportServerJob is NOT NULL THEN
       rep_status := report_object_status(ReportServerJob);

       WHILE rep_status in ('RUNNING', 'OPENING_REPORT','ENQUEUED') LOOP
         rep_status := report_object_status(ReportServerJob);
       END LOOP;
       IF rep_status != 'FINISHED' THEN
          raise report_not_generated;
       END IF;

 -- The JavaScript command/string can be used to show a basic browser window

   v_url := '/reports/rwservlet/getjobid'||v_jobID||'?server='||reportserver;

-- The following is one long string, carriage returns used for formatting

v_url2 := 'javascript:window.open("'||v_url ||'", "", "fullscreen=no, 
titlebar=no, location=no, toolbar=no, menubar=no, status=no, resizable=yes");

-- "self.close" shown below is continued from the line above, carriage return 
-- here for formatting, but it is really one long string

       self.close()';

-- This is the built-in that calls a new browser window

       Web.Show_Document(v_url2,'_blank');
   ELSE
       raise report_not_generated;
   END IF;


END IF;

-- Destroy the parameter list. Report has been generated - it's no longer needed

Destroy_Parameter_List(p_rpt_param_list);

EXCEPTION
  WHEN report_not_generated THEN

-- "am" is a shortcut to call an alert message. If you are raising a lot of alert messages in 
-- your forms, add a procedure in Program Units. It takes the string you pass in and will
-- save you lots of time by not having to keep writing set_alert_property(...).

       am('There was an error in running the report.'||chr(10)||
          'Contact the Application Server administrator for assistance.');

  WHEN OTHERS THEN
    user_show_error;

END;