How "Copy File" works in 'View Output" screen in Oracle Apps

Usualy, when we submit a concurrent report and it finishes running, we click 'Output' button to view the output and click 'Tools'->'Copy file' to copy it to browser window and thus save to local disk. Basically this 'Copy File' menu uses following logic:

1. it finds the mime type based on the file type which is in output_file_type column of fnd_concurrent_requests.

2. then it calls browser_show, passing filename which is the actual output file fullpath name, node, mime type and request_id .

3. This browser_show will create a temp_id and generate a URL using above filename, node, mime type and request_id.

4. Of course, in my testing I use FND_REQUEST.SUBMIT_REQUEST to submit request and fnd_concurrent.wait_for_request to wait for the submitted concurrent program to finish.

5. Once the program finishes, it will generate a URL and reurn it back to browser, by simply clicking on the URL, it can bring out the output of the request.

 

Sample:

 

  PROCEDURE get_mime_type(ftype IN VARCHAR2
                         ,mtype IN OUT VARCHAR2
                         ,wopen IN OUT BOOLEAN)
  IS
    profile_defined   BOOLEAN      := FALSE;
    errcode           NUMBER;
    g_size            NUMBER;
    initial_mtype     VARCHAR2(80);                                                   -- initial value for viewer poplist
  BEGIN
    wopen := FALSE;
    -- check profile options for each file format
    IF (ftype = 'TEXT') THEN
      IF (fnd_profile.defined('FS_MIME_TEXT')) THEN
        fnd_profile.get('FS_MIME_TEXT', mtype);
        profile_defined := TRUE;
      END IF;
    ELSIF(ftype = 'HTML') THEN
      IF (fnd_profile.defined('FS_MIME_HTML')) THEN
        fnd_profile.get('FS_MIME_HTML', mtype);
        profile_defined := TRUE;
      END IF;
    ELSIF(ftype = 'PDF') THEN
      IF (fnd_profile.defined('FS_MIME_PDF')) THEN
        fnd_profile.get('FS_MIME_PDF', mtype);
        profile_defined := TRUE;
      END IF;
    ELSIF(ftype = 'PS') THEN
      IF (fnd_profile.defined('FS_MIME_PS')) THEN
        fnd_profile.get('FS_MIME_PS', mtype);
        profile_defined := TRUE;
      END IF;
    ELSIF(ftype = 'PCL') THEN
      IF (fnd_profile.defined('FS_MIME_PCL')) THEN
        fnd_profile.get('FS_MIME_PCL', mtype);
        profile_defined := TRUE;
      END IF;
    ELSIF(ftype = 'XML') THEN
      IF (fnd_profile.defined('FS_MIME_XML')) THEN
        fnd_profile.get('FS_MIME_XML', mtype);
        profile_defined := TRUE;
      END IF;
    ELSE
      mtype := 'text/plain';
      profile_defined := TRUE;
    END IF;

    -- if profile option does not exists then look for fnd_mime_types_vl
    -- view for mime type.
    IF (NOT profile_defined) THEN
      FOR c IN (SELECT mime_type
                  FROM fnd_mime_types_vl
                 WHERE file_format_code = ftype
                   AND ROWNUM = 1)
      LOOP
        mtype := c.mime_type;
        EXIT;
      END LOOP;
    END IF;                                                                                         -- not profile_defined
  END; 
 
 
  PROCEDURE browser_show(fname VARCHAR2
                        ,node VARCHAR2
                        ,mtype VARCHAR2
                        ,req_id NUMBER DEFAULT NULL)
  IS
    base         VARCHAR2(255);
    url          VARCHAR2(255);
    ID           VARCHAR2(32);
    gwyuid       VARCHAR2(32);
    two_task     VARCHAR2(64);
    fs_enabled   VARCHAR2(2);
    pos          NUMBER;
    svc          VARCHAR2(240);
    x_mode       VARCHAR2(30)  := 'TEXT';
    oftype       VARCHAR2(4);
  BEGIN
    fnd_profile.get('TWO_TASK', two_task);
    fnd_profile.get('GWYUID', gwyuid);
    fnd_profile.get('APPS_WEB_AGENT', base);

    IF (fnd_profile.defined('FS_SVC_PREFIX')) THEN
      fnd_profile.get('FS_SVC_PREFIX', svc);

      IF (svc IS NOT NULL) THEN
        svc := SUBSTR(svc || node, 1, 255);
      ELSE
        svc := 'FNDFS_' || node;
      END IF;
    ELSE
      svc := 'FNDFS_' || node;
    END IF;

    -- Decide the file transfer mode
    IF (req_id IS NOT NULL) THEN
      BEGIN
        SELECT NVL(output_file_type, 'TEXT')
          INTO oftype
          FROM fnd_concurrent_requests
         WHERE request_id = req_id;

        IF (oftype IN('PDF', 'PS', 'PCL')) THEN
          x_mode := 'BINARY';
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          x_mode := 'TEXT';
      END;
    END IF;

    ID := fnd_webfile.create_id(fname, svc, 10, mtype, req_id, x_mode);

    pos := INSTR(base, '/', 1, 3);

    IF (pos > 0) THEN
      base := SUBSTR(base, 1, pos - 1);
    END IF;

    url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || ID || '&' || 'login=' || gwyuid || '@' || two_task;

    htp.htmlOpen;
    htp.headOpen;
    htp.title('Get request output');
    htp.headClose;
    htp.bodyOpen;
    htp.p('<A HREF='||url||'>Click to get request output</a>');
    htp.bodyclose;
    htp.htmlclose;

  END browser_show;
 
  --------------------------------------------
  --Public procedures/functions
  --------------------------------------------
  --paste this link in browser and it will return the report output
  --http://ctloradev04.ctl.creaf.com:8006/pls/FCWW/XFND_CLE_WEB_REQUEST.report?p_conc_prog_code=TEST

  PROCEDURE report(
    p_conc_prog_code            IN       VARCHAR2
   --,p_debug_level               IN       NUMBER
   --,p_arg1                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg2                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg3                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg4                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg5                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg6                      IN       VARCHAR2 DEFAULT NULL
   --,p_arg7                      IN       VARCHAR2 DEFAULT NULL
  )
  IS
    l_req_id                    NUMBER;
    l_user_id                   NUMBER := 4612;
    l_resp_id                   NUMBER := 50646;  --22910;
    l_resp_appl_id              NUMBER := 222; --800;

    ftype                       varchar2(30) := 'TEXT';
    mtype                       varchar2(80);
    wopen                       boolean := FALSE;
    filename                    fnd_concurrent_requests.outfile_name%type;
    node                        fnd_concurrent_requests.outfile_node_name%type;
   
    l_request_completed         BOOLEAN                                   := FALSE;
    l_req_phase                 VARCHAR2(20);
    l_req_status                VARCHAR2(1000);
    l_req_dev_phase             VARCHAR2(1000);
    l_req_dev_status            VARCHAR2(1000);
    l_req_message               VARCHAR2(1000);

     b_success                  BOOLEAN;
  BEGIN
 
    b_success:=  fnd_user_pkg.validatelogin('GUEST','ORACLE');
    if NOT b_success then
      htp.p('Login failed');
      RETURN;
    end if;
 
    FND_GLOBAL.APPS_INITIALIZE(USER_ID            => l_user_id
                              ,RESP_ID            => l_resp_id
                              ,RESP_APPL_ID       => l_resp_appl_id
                            );
     l_req_id := FND_REQUEST.SUBMIT_REQUEST(application  => 'FND'
                                          ,program      => 'FNDSCURS'
                                          ,description  => 'Active Users'
                                          ,start_time   => NULL
                                           );      

    commit;
   
    l_request_completed :=
      fnd_concurrent.wait_for_request(request_id     => l_req_id
                                     ,INTERVAL       => 1
                                     ,phase          => l_req_phase
                                     ,status         => l_req_status
                                     ,dev_phase      => l_req_dev_phase
                                     ,dev_status     => l_req_dev_status
                                     ,MESSAGE        => l_req_message);
    COMMIT;   
   
    FOR c IN (SELECT nvl(output_file_type, 'TEXT') ftype
                    ,outfile_name filename
                    ,outfile_node_name node
                    ,ofile_size
                FROM fnd_concurrent_requests
               WHERE request_id = l_req_id)
    LOOP
      ftype := c.ftype;
      filename := c.filename;
      node := c.node;
      EXIT;
    END LOOP;
   
    get_mime_type(ftype   => ftype
                 ,mtype   => mtype
                 ,wopen   => wopen);

                
    IF not wopen THEN
      browser_show(filename,node,mtype,l_req_id);
    END IF;
                
   
  END report;