Database objects creation scripts

The first part shows the scripts to create views, tables, sequences, synonyms and triggers and have to be executed as database user APPS. The second part consists of a few SQL statements that have to be executed as database user APEX_EBS and are necessary to grant APPS to some of the database objects created in the first part.

Execute as database user APPS (not possible in the SQL Workshop in Apex):

   CREATE OR REPLACE FORCE VIEW APPS.XXAPX_USER_V
   (  USER_ID,
      USER_NAME,
      START_DATE,
      END_DATE,
      DESCRIPTION,
      EMAIL_ADDRESS
   )
   AS
      SELECT user_id,
             user_name,
             start_date,
             end_date,
             description,
             email_address
      FROM fnd_user;
/

   GRANT SELECT ON APPS.XXAPX_USER_V TO APEX_EBS;
/

   DECLARE
     l_count NUMBER := 0;
   BEGIN
     SELECT COUNT(*) INTO l_count FROM all_tables WHERE table_name='XXAPX_FILES' AND owner = 'APEX_EBS'; 
     IF l_count = 1 THEN
       EXECUTE IMMEDIATE 'DROP TABLE APEX_EBS.XXAPX_FILES';
       EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXAPX_FILES';
     END IF;
   END;
/ 
   
   CREATE TABLE APEX_EBS.XXAPX_FILES 
   (  FILE_ID                NUMBER,
      FILE_NAME              VARCHAR2(256 BYTE), 
      FILE_CONTENT_TYPE      VARCHAR2(256 BYTE), 
      LANGUAGE               VARCHAR2(4 BYTE)  DEFAULT (USERENV('LANG')),
      ORACLE_CHARSET         VARCHAR2(30 BYTE) DEFAULT ( SUBSTR(USERENV( 'LANGUAGE'),INSTR(USERENV('LANGUAGE'),'.')+1) ),
      FILE_FORMAT            VARCHAR2(10 BYTE),        
      FILE_DATA              BLOB, 
      UPLOAD_DATE            DATE,
      ORG_ID                 NUMBER,
      CREATION_DATE          DATE, 
      CREATED_BY             VARCHAR2(255), 
      LAST_UPDATE_DATE       DATE, 
      LAST_UPDATED_BY        VARCHAR2(255), 
      CONSTRAINT XXAPX_FILES_PK PRIMARY KEY (FILE_ID) ENABLE
   );
/

   CREATE SYNONYM APPS.XXAPX_FILES FOR APEX_EBS.XXAPX_FILES;
/

   CREATE OR REPLACE TRIGGER apex_ebs.xxapx_files_biu 
      BEFORE INSERT OR UPDATE ON apex_ebs.xxapx_files
   FOR EACH ROW
      DECLARE
         l_ebs_user_name   VARCHAR2(255);
      
      FUNCTION get_ebs_user_name
         RETURN VARCHAR2
      IS
         l_user_id         NUMBER;
         l_user_name       VARCHAR2(255);
      BEGIN
         l_user_id := apex_util.get_session_state('EBS_USER_ID');

         BEGIN
            SELECT user_name
            INTO   l_user_name
            FROM   apps.xxapx_user_v
            WHERE  user_id = l_user_id;
         
         EXCEPTION
            WHEN OTHERS THEN
               l_user_name := NULL;            
         END;         

         RETURN l_user_name;         
      END;
   BEGIN
      IF LENGTH(:NEW.file_name)<4 THEN
         raise_application_error(-20000, 'Please enter a correct filename (e.g. "XXAPX_<name>.xls")');
      ELSIF (UPPER(SUBSTR(:NEW.file_name,1,5)) <> 'XXAPX') THEN 
         raise_application_error(-20000,  'The filename needs to start with ''XXAPX''.');      
      ELSIF SUBSTR(:NEW.file_name,-4) <> '.xls' THEN 
         raise_application_error(-20000,  'File must be Excel and extension .xls');      
      ELSIF NVL(dbms_lob.getlength(:NEW.file_data),0) > 15728640 THEN
          raise_application_error(-20000, 'The size of the uploaded file was over 15MB. Please upload a smaller sized file.');
      ELSE
         l_ebs_user_name := get_ebs_user_name;
        
         IF INSERTING THEN
             :NEW.creation_date := SYSDATE;
             :NEW.created_by :=  NVL(l_ebs_user_name,NVL(wwv_flow.g_user,USER));
         END IF;
         
         IF INSERTING OR UPDATING THEN
             :NEW.org_id := apex_util.get_session_state('EBS_ORG_ID');
             :NEW.last_update_date := SYSDATE;
             :NEW.last_updated_by  := NVL(l_ebs_user_name,NVL(wwv_flow.g_user,USER));
         END IF;
      END IF;   
   END;
/

   ALTER TRIGGER apex_ebs.xxapx_files_biu ENABLE
/

   -- Sequence for ID on APEX_EBS.XXAPX_FILES     
   DECLARE
     l_count NUMBER := 0;
   BEGIN
     SELECT COUNT(*) INTO l_count FROM all_sequences WHERE sequence_name='XXAPX_FILES_S' AND sequence_owner = 'APEX_EBS'; 
     IF l_count = 1 THEN
       EXECUTE IMMEDIATE 'DROP SEQUENCE APEX_EBS.XXAPX_FILES_S';
       EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXAPX_FILES_S';
     END IF;
   END;
/

   CREATE SEQUENCE APEX_EBS.XXAPX_FILES_S
     MINVALUE 1 
     MAXVALUE 999999999999999 
     INCREMENT BY 1 
     START WITH 1 
     NOCACHE 
     NOORDER 
     NOCYCLE;
/

   CREATE OR REPLACE SYNONYM APPS.XXAPX_FILES_S FOR APEX_EBS.XXAPX_FILES_S;
/

Do the following as database user APEX_EBS (can be done in the SQL Workshop in Apex):

  GRANT ALL ON APEX_EBS.XXAPX_FILES TO APPS WITH GRANT OPTION;
  GRANT ALL ON APEX_EBS.XXAPX_FILES_S TO APPS WITH GRANT OPTION;
/