Code of package apex_global

CREATE OR REPLACE PACKAGE apex_global AUTHID DEFINER AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pks
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    18-06-2014   Initial Version
   *****************************************************************/
   
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN;
      
   PROCEDURE apps_initialize (
      user_id           IN NUMBER,
      resp_id           IN NUMBER,
      resp_appl_id      IN NUMBER,
      security_group_id IN NUMBER DEFAULT 0,
      server_id         IN NUMBER DEFAULT -1);

   FUNCTION submit_request (
      application IN VARCHAR2 DEFAULT NULL,
      program     IN VARCHAR2 DEFAULT NULL,
      description IN VARCHAR2 DEFAULT NULL,
      start_time  IN VARCHAR2 DEFAULT NULL,
      sub_request IN BOOLEAN  DEFAULT FALSE,
      argument1   IN VARCHAR2 DEFAULT CHR(0),
      argument2   IN VARCHAR2 DEFAULT CHR(0),
      argument3   IN VARCHAR2 DEFAULT CHR(0),
      argument4   IN VARCHAR2 DEFAULT CHR(0),
      argument5   IN VARCHAR2 DEFAULT CHR(0),
      argument6   IN VARCHAR2 DEFAULT CHR(0),
      argument7   IN VARCHAR2 DEFAULT CHR(0),
      argument8   IN VARCHAR2 DEFAULT CHR(0),
      argument9   IN VARCHAR2 DEFAULT CHR(0),
      argument10  IN VARCHAR2 DEFAULT CHR(0),
      argument11  IN VARCHAR2 DEFAULT CHR(0),
      argument12  IN VARCHAR2 DEFAULT CHR(0),
      argument13  IN VARCHAR2 DEFAULT CHR(0),
      argument14  IN VARCHAR2 DEFAULT CHR(0),
      argument15  IN VARCHAR2 DEFAULT CHR(0),
      argument16  IN VARCHAR2 DEFAULT CHR(0),
      argument17  IN VARCHAR2 DEFAULT CHR(0),
      argument18  IN VARCHAR2 DEFAULT CHR(0),
      argument19  IN VARCHAR2 DEFAULT CHR(0),
      argument20  IN VARCHAR2 DEFAULT CHR(0),
      argument21  IN VARCHAR2 DEFAULT CHR(0),
      argument22  IN VARCHAR2 DEFAULT CHR(0),
      argument23  IN VARCHAR2 DEFAULT CHR(0),
      argument24  IN VARCHAR2 DEFAULT CHR(0),
      argument25  IN VARCHAR2 DEFAULT CHR(0),
      argument26  IN VARCHAR2 DEFAULT CHR(0),
      argument27  IN VARCHAR2 DEFAULT CHR(0),
      argument28  IN VARCHAR2 DEFAULT CHR(0),
      argument29  IN VARCHAR2 DEFAULT CHR(0),
      argument30  IN VARCHAR2 DEFAULT CHR(0),
      argument31  IN VARCHAR2 DEFAULT CHR(0),
      argument32  IN VARCHAR2 DEFAULT CHR(0),
      argument33  IN VARCHAR2 DEFAULT CHR(0),
      argument34  IN VARCHAR2 DEFAULT CHR(0),
      argument35  IN VARCHAR2 DEFAULT CHR(0),
      argument36  IN VARCHAR2 DEFAULT CHR(0),
      argument37  IN VARCHAR2 DEFAULT CHR(0),
      argument38  IN VARCHAR2 DEFAULT CHR(0),
      argument39  IN VARCHAR2 DEFAULT CHR(0),
      argument40  IN VARCHAR2 DEFAULT CHR(0),
      argument41  IN VARCHAR2 DEFAULT CHR(0),
      argument42  IN VARCHAR2 DEFAULT CHR(0),
      argument43  IN VARCHAR2 DEFAULT CHR(0),
      argument44  IN VARCHAR2 DEFAULT CHR(0),
      argument45  IN VARCHAR2 DEFAULT CHR(0),
      argument46  IN VARCHAR2 DEFAULT CHR(0),
      argument47  IN VARCHAR2 DEFAULT CHR(0),
      argument48  IN VARCHAR2 DEFAULT CHR(0),
      argument49  IN VARCHAR2 DEFAULT CHR(0),
      argument50  IN VARCHAR2 DEFAULT CHR(0),
      argument51  IN VARCHAR2 DEFAULT CHR(0),
      argument52  IN VARCHAR2 DEFAULT CHR(0),
      argument53  IN VARCHAR2 DEFAULT CHR(0),
      argument54  IN VARCHAR2 DEFAULT CHR(0),
      argument55  IN VARCHAR2 DEFAULT CHR(0),
      argument56  IN VARCHAR2 DEFAULT CHR(0),
      argument57  IN VARCHAR2 DEFAULT CHR(0),
      argument58  IN VARCHAR2 DEFAULT CHR(0),
      argument59  IN VARCHAR2 DEFAULT CHR(0),
      argument60  IN VARCHAR2 DEFAULT CHR(0),
      argument61  IN VARCHAR2 DEFAULT CHR(0),
      argument62  IN VARCHAR2 DEFAULT CHR(0),
      argument63  IN VARCHAR2 DEFAULT CHR(0),
      argument64  IN VARCHAR2 DEFAULT CHR(0),
      argument65  IN VARCHAR2 DEFAULT CHR(0),
      argument66  IN VARCHAR2 DEFAULT CHR(0),
      argument67  IN VARCHAR2 DEFAULT CHR(0),
      argument68  IN VARCHAR2 DEFAULT CHR(0),
      argument69  IN VARCHAR2 DEFAULT CHR(0),
      argument70  IN VARCHAR2 DEFAULT CHR(0),
      argument71  IN VARCHAR2 DEFAULT CHR(0),
      argument72  IN VARCHAR2 DEFAULT CHR(0),
      argument73  IN VARCHAR2 DEFAULT CHR(0),
      argument74  IN VARCHAR2 DEFAULT CHR(0),
      argument75  IN VARCHAR2 DEFAULT CHR(0),
      argument76  IN VARCHAR2 DEFAULT CHR(0),
      argument77  IN VARCHAR2 DEFAULT CHR(0),
      argument78  IN VARCHAR2 DEFAULT CHR(0),
      argument79  IN VARCHAR2 DEFAULT CHR(0),
      argument80  IN VARCHAR2 DEFAULT CHR(0),
      argument81  IN VARCHAR2 DEFAULT CHR(0),
      argument82  IN VARCHAR2 DEFAULT CHR(0),
      argument83  IN VARCHAR2 DEFAULT CHR(0),
      argument84  IN VARCHAR2 DEFAULT CHR(0),
      argument85  IN VARCHAR2 DEFAULT CHR(0),
      argument86  IN VARCHAR2 DEFAULT CHR(0),
      argument87  IN VARCHAR2 DEFAULT CHR(0),
      argument88  IN VARCHAR2 DEFAULT CHR(0),
      argument89  IN VARCHAR2 DEFAULT CHR(0),
      argument90  IN VARCHAR2 DEFAULT CHR(0),
      argument91  IN VARCHAR2 DEFAULT CHR(0),
      argument92  IN VARCHAR2 DEFAULT CHR(0),
      argument93  IN VARCHAR2 DEFAULT CHR(0),
      argument94  IN VARCHAR2 DEFAULT CHR(0),
      argument95  IN VARCHAR2 DEFAULT CHR(0),
      argument96  IN VARCHAR2 DEFAULT CHR(0),
      argument97  IN VARCHAR2 DEFAULT CHR(0),
      argument98  IN VARCHAR2 DEFAULT CHR(0),
      argument99  IN VARCHAR2 DEFAULT CHR(0),
      argument100 IN VARCHAR2 DEFAULT CHR(0))
      RETURN NUMBER;

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER);
END;
/

GRANT EXECUTE ON APPS.APEX_GLOBAL TO APEX_EBS;
/

CREATE OR REPLACE PACKAGE BODY apex_global AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pkb
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    18-06-2014   Initial Version
   *****************************************************************/
   
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN
   IS
      c_ebs             VARCHAR2(240) := 'E-Business Suite';
      
      l_authorized      BOOLEAN;
      l_user_id         NUMBER;
      l_resp_id         NUMBER;
      l_resp_appl_id    NUMBER;    
      l_sec_group_id    NUMBER;
      l_org_id          NUMBER;
      l_time_out        NUMBER;
      l_ebs_url         VARCHAR2(100);
      l_appl_name       VARCHAR2(240);

      CURSOR get_apps_credentials
      IS
         SELECT iss.user_id
         ,      iss.responsibility_id
         ,      iss.responsibility_application_id
         ,      iss.security_group_id
         ,      iss.org_id
         ,      iss.time_out
         ,      isa.value
         FROM  apps.icx_sessions iss
         ,     apps.icx_session_attributes isa 
         WHERE iss.session_id = apps.icx_sec.getsessioncookie
         AND   isa.session_id = iss.session_id
         AND   isa.name = '_USERORSSWAPORTALURL';
         
      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
         
   BEGIN
      OPEN get_apps_credentials;
      FETCH get_apps_credentials 
      INTO l_user_id
      ,    l_resp_id 
      ,    l_resp_appl_id
      ,    l_sec_group_id
      ,    l_org_id
      ,    l_time_out
      ,    l_ebs_url;
      
      IF get_apps_credentials%NOTFOUND THEN  
         l_authorized := FALSE;
      ELSE
         l_authorized := TRUE;
         
         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;
         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;
         CLOSE get_appl_name;

         apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
         apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
         apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));      
--       apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));      
         apex_util.set_session_state('EBS_URL',l_ebs_url);     
         apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);    

         apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');         
      END IF;
      
      CLOSE get_apps_credentials;
      RETURN l_authorized;
      
   EXCEPTION
      WHEN OTHERS THEN
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      
   END;

   PROCEDURE apps_initialize (
      user_id           IN NUMBER,
      resp_id           IN NUMBER,
      resp_appl_id      IN NUMBER,
      security_group_id IN NUMBER DEFAULT 0,
      server_id         IN NUMBER DEFAULT -1) IS 
   BEGIN
      fnd_global.apps_initialize
         ( user_id
         , resp_id
         , resp_appl_id
         , security_group_id
         , server_id);
   END;

   FUNCTION get_profile_value (
      name IN VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN fnd_profile.value(name);
   END;   
   
   FUNCTION submit_request (
      application IN VARCHAR2 DEFAULT NULL,
      program     IN VARCHAR2 DEFAULT NULL,
      description IN VARCHAR2 DEFAULT NULL,
      start_time  IN VARCHAR2 DEFAULT NULL,
      sub_request IN BOOLEAN  DEFAULT FALSE,
      argument1   IN VARCHAR2 DEFAULT CHR(0),
      argument2   IN VARCHAR2 DEFAULT CHR(0),
      argument3   IN VARCHAR2 DEFAULT CHR(0),
      argument4   IN VARCHAR2 DEFAULT CHR(0),
      argument5   IN VARCHAR2 DEFAULT CHR(0),
      argument6   IN VARCHAR2 DEFAULT CHR(0),
      argument7   IN VARCHAR2 DEFAULT CHR(0),
      argument8   IN VARCHAR2 DEFAULT CHR(0),
      argument9   IN VARCHAR2 DEFAULT CHR(0),
      argument10  IN VARCHAR2 DEFAULT CHR(0),
      argument11  IN VARCHAR2 DEFAULT CHR(0),
      argument12  IN VARCHAR2 DEFAULT CHR(0),
      argument13  IN VARCHAR2 DEFAULT CHR(0),
      argument14  IN VARCHAR2 DEFAULT CHR(0),
      argument15  IN VARCHAR2 DEFAULT CHR(0),
      argument16  IN VARCHAR2 DEFAULT CHR(0),
      argument17  IN VARCHAR2 DEFAULT CHR(0),
      argument18  IN VARCHAR2 DEFAULT CHR(0),
      argument19  IN VARCHAR2 DEFAULT CHR(0),
      argument20  IN VARCHAR2 DEFAULT CHR(0),
      argument21  IN VARCHAR2 DEFAULT CHR(0),
      argument22  IN VARCHAR2 DEFAULT CHR(0),
      argument23  IN VARCHAR2 DEFAULT CHR(0),
      argument24  IN VARCHAR2 DEFAULT CHR(0),
      argument25  IN VARCHAR2 DEFAULT CHR(0),
      argument26  IN VARCHAR2 DEFAULT CHR(0),
      argument27  IN VARCHAR2 DEFAULT CHR(0),
      argument28  IN VARCHAR2 DEFAULT CHR(0),
      argument29  IN VARCHAR2 DEFAULT CHR(0),
      argument30  IN VARCHAR2 DEFAULT CHR(0),
      argument31  IN VARCHAR2 DEFAULT CHR(0),
      argument32  IN VARCHAR2 DEFAULT CHR(0),
      argument33  IN VARCHAR2 DEFAULT CHR(0),
      argument34  IN VARCHAR2 DEFAULT CHR(0),
      argument35  IN VARCHAR2 DEFAULT CHR(0),
      argument36  IN VARCHAR2 DEFAULT CHR(0),
      argument37  IN VARCHAR2 DEFAULT CHR(0),
      argument38  IN VARCHAR2 DEFAULT CHR(0),
      argument39  IN VARCHAR2 DEFAULT CHR(0),
      argument40  IN VARCHAR2 DEFAULT CHR(0),
      argument41  IN VARCHAR2 DEFAULT CHR(0),
      argument42  IN VARCHAR2 DEFAULT CHR(0),
      argument43  IN VARCHAR2 DEFAULT CHR(0),
      argument44  IN VARCHAR2 DEFAULT CHR(0),
      argument45  IN VARCHAR2 DEFAULT CHR(0),
      argument46  IN VARCHAR2 DEFAULT CHR(0),
      argument47  IN VARCHAR2 DEFAULT CHR(0),
      argument48  IN VARCHAR2 DEFAULT CHR(0),
      argument49  IN VARCHAR2 DEFAULT CHR(0),
      argument50  IN VARCHAR2 DEFAULT CHR(0),
      argument51  IN VARCHAR2 DEFAULT CHR(0),
      argument52  IN VARCHAR2 DEFAULT CHR(0),
      argument53  IN VARCHAR2 DEFAULT CHR(0),
      argument54  IN VARCHAR2 DEFAULT CHR(0),
      argument55  IN VARCHAR2 DEFAULT CHR(0),
      argument56  IN VARCHAR2 DEFAULT CHR(0),
      argument57  IN VARCHAR2 DEFAULT CHR(0),
      argument58  IN VARCHAR2 DEFAULT CHR(0),
      argument59  IN VARCHAR2 DEFAULT CHR(0),
      argument60  IN VARCHAR2 DEFAULT CHR(0),
      argument61  IN VARCHAR2 DEFAULT CHR(0),
      argument62  IN VARCHAR2 DEFAULT CHR(0),
      argument63  IN VARCHAR2 DEFAULT CHR(0),
      argument64  IN VARCHAR2 DEFAULT CHR(0),
      argument65  IN VARCHAR2 DEFAULT CHR(0),
      argument66  IN VARCHAR2 DEFAULT CHR(0),
      argument67  IN VARCHAR2 DEFAULT CHR(0),
      argument68  IN VARCHAR2 DEFAULT CHR(0),
      argument69  IN VARCHAR2 DEFAULT CHR(0),
      argument70  IN VARCHAR2 DEFAULT CHR(0),
      argument71  IN VARCHAR2 DEFAULT CHR(0),
      argument72  IN VARCHAR2 DEFAULT CHR(0),
      argument73  IN VARCHAR2 DEFAULT CHR(0),
      argument74  IN VARCHAR2 DEFAULT CHR(0),
      argument75  IN VARCHAR2 DEFAULT CHR(0),
      argument76  IN VARCHAR2 DEFAULT CHR(0),
      argument77  IN VARCHAR2 DEFAULT CHR(0),
      argument78  IN VARCHAR2 DEFAULT CHR(0),
      argument79  IN VARCHAR2 DEFAULT CHR(0),
      argument80  IN VARCHAR2 DEFAULT CHR(0),
      argument81  IN VARCHAR2 DEFAULT CHR(0),
      argument82  IN VARCHAR2 DEFAULT CHR(0),
      argument83  IN VARCHAR2 DEFAULT CHR(0),
      argument84  IN VARCHAR2 DEFAULT CHR(0),
      argument85  IN VARCHAR2 DEFAULT CHR(0),
      argument86  IN VARCHAR2 DEFAULT CHR(0),
      argument87  IN VARCHAR2 DEFAULT CHR(0),
      argument88  IN VARCHAR2 DEFAULT CHR(0),
      argument89  IN VARCHAR2 DEFAULT CHR(0),
      argument90  IN VARCHAR2 DEFAULT CHR(0),
      argument91  IN VARCHAR2 DEFAULT CHR(0),
      argument92  IN VARCHAR2 DEFAULT CHR(0),
      argument93  IN VARCHAR2 DEFAULT CHR(0),
      argument94  IN VARCHAR2 DEFAULT CHR(0),
      argument95  IN VARCHAR2 DEFAULT CHR(0),
      argument96  IN VARCHAR2 DEFAULT CHR(0),
      argument97  IN VARCHAR2 DEFAULT CHR(0),
      argument98  IN VARCHAR2 DEFAULT CHR(0),
      argument99  IN VARCHAR2 DEFAULT CHR(0),
      argument100 IN VARCHAR2 DEFAULT CHR(0))
      RETURN NUMBER
   IS 
   BEGIN
      RETURN fnd_request.submit_request
               ( application
               , program    
               , description
               , start_time 
               , sub_request
               , argument1
               , argument2
               , argument3
               , argument4
               , argument5
               , argument6
               , argument7
               , argument8
               , argument9
               , argument10
               , argument11
               , argument12
               , argument13
               , argument14
               , argument15
               , argument16
               , argument17
               , argument18
               , argument19
               , argument20
               , argument21
               , argument22
               , argument23
               , argument24
               , argument25
               , argument26
               , argument27
               , argument28
               , argument29
               , argument30
               , argument31
               , argument32
               , argument33
               , argument34
               , argument35
               , argument36
               , argument37
               , argument38
               , argument39
               , argument40
               , argument41
               , argument42
               , argument43
               , argument44
               , argument45
               , argument46
               , argument47
               , argument48
               , argument49
               , argument50
               , argument51
               , argument52
               , argument53
               , argument54
               , argument55
               , argument56
               , argument57
               , argument58
               , argument59
               , argument60
               , argument61
               , argument62
               , argument63
               , argument64
               , argument65
               , argument66
               , argument67
               , argument68
               , argument69
               , argument70
               , argument71
               , argument72
               , argument73
               , argument74
               , argument75
               , argument76
               , argument77
               , argument78
               , argument79
               , argument80
               , argument81
               , argument82
               , argument83
               , argument84
               , argument85
               , argument86
               , argument87
               , argument88
               , argument89
               , argument90
               , argument91
               , argument92
               , argument93
               , argument94
               , argument95
               , argument96
               , argument97
               , argument98
               , argument99
               , argument100);
   END;

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER)
   IS
      c_chunck_size  CONSTANT   NUMBER := 32760;

      l_file_id                 NUMBER; 
      l_filename                VARCHAR2(100);

      l_blob                    BLOB;
      l_blob_length             NUMBER;
      l_bytes_written           NUMBER := 0;
      l_chunck                  RAW(32760);
      l_chunck_size             NUMBER;

      l_output                  utl_file.file_type;

   BEGIN
      -- select filename, blob incl length into variables
      SELECT file_name
      ,      dbms_lob.getlength(file_data)
      ,      file_data
      INTO   l_filename
      ,      l_blob_length
      ,      l_blob
      FROM   xxoic_files_pons
      WHERE  file_id = p_file_id;

      -- define output directory
      l_output := utl_file.fopen(p_location, l_filename, 'W', c_chunck_size);

      -- if small enough for a single write
      IF l_blob_length <= c_chunck_size THEN
         utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_blob),chr(13)));
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         l_chunck_size := c_chunck_size;
         WHILE l_bytes_written < l_blob_length
         LOOP
            l_chunck_size := least(c_chunck_size, l_blob_length - l_bytes_written);
            dbms_lob.read(l_blob, l_chunck_size, l_bytes_written + 1, l_chunck);

            utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_chunck),chr(13)));
            utl_file.fflush(l_output); 

            l_bytes_written := l_bytes_written + l_chunck_size;
        END LOOP; 
      END IF;
      utl_file.fclose(l_output);
   END;
   
END;
/

SHOW ERROR;
EXIT;

1 comment:

  1. I tried same process to access views information in apex iinteractive grid, but no luck.
    Can i know, what i am missing

    ReplyDelete