DECLARE
   QUEUE_TABLE_EXISTS EXCEPTION;
   PRAGMA EXCEPTION_INIT(QUEUE_TABLE_EXISTS, -24001);
BEGIN
  BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
      QUEUE_TABLE        => '"&1".RECEIVE_FROM_SOLACE_QUEUE_TABLE',
      SORT_LIST          => 'PRIORITY,ENQ_TIME', 
      QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE');

    DBMS_AQADM.CREATE_QUEUE(
      QUEUE_NAME         => '"&1".RECEIVE_FROM_SOLACE_QUEUE',
      QUEUE_TABLE        => '"&1".RECEIVE_FROM_SOLACE_QUEUE_TABLE');

    DBMS_AQADM.START_QUEUE(
      QUEUE_NAME         => '"&1".RECEIVE_FROM_SOLACE_QUEUE');
  EXCEPTION
    WHEN QUEUE_TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
      QUEUE_TABLE        => '"&1".SEND_TO_SOLACE_QUEUE_TABLE',
      QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE');

    DBMS_AQADM.CREATE_QUEUE(
      QUEUE_NAME         => '"&1".SEND_TO_SOLACE_QUEUE',
      QUEUE_TABLE        => '"&1".SEND_TO_SOLACE_QUEUE_TABLE');

    DBMS_AQADM.START_QUEUE(
      QUEUE_NAME         => '"&1".SEND_TO_SOLACE_QUEUE');
  EXCEPTION
    WHEN QUEUE_TABLE_EXISTS THEN
      NULL;
  END;
END;
/
-------------
ALTER SESSION SET CURRENT_SCHEMA = "&1";
DECLARE
   TABLE_EXISTS EXCEPTION;
   PRAGMA EXCEPTION_INIT(TABLE_EXISTS, -00955);
   STMT VARCHAR2(5000);

BEGIN
  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_ERROR
            (
              MESSAGE   CLOB,
              TOPIC     VARCHAR2(100),
              ERROR     VARCHAR2(1000),
              TIMESTAMP DATE
            )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_NOT_PROCESSED
            (
              MESSAGE   CLOB NOT NULL,
              TOPIC     VARCHAR2(100) NOT NULL,
              TIMESTAMP DATE NOT NULL
            )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_PROCESSED
              (
                MESSAGE   CLOB NOT NULL,
                TOPIC     VARCHAR2(100) NOT NULL,
                TIMESTAMP DATE NOT NULL
              )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;


  BEGIN
    STMT := 'CREATE TABLE SOLACE_TOPIC
              (
                SOLACE_TOPIC_IN   VARCHAR2(100) NOT NULL,
                PRIORITY          NUMBER NOT NULL,
                PROCESSING_NUMBER NUMBER DEFAULT 0 NOT NULL,
                SOLACE_TOPIC_OUT  VARCHAR2(100),
                CONSTRAINT PROCESSING_ORDER UNIQUE (PROCESSING_NUMBER)
              )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;
END;
/
ALTER SESSION SET CURRENT_SCHEMA = "&3";
DECLARE
  STMT CLOB := Q'{
  ALTER TABLE ERP_I_CUSTOMER ADD CRMCUSTOMERNUMBER VARCHAR2(18);
  ALTER TABLE ERP_I_CUSTOMER ADD SEND_RESPONSE VARCHAR2(1);
  DROP INDEX XAK3CUSTOMER;
  CREATE INDEX XIE6CUSTOMER ON CUSTOMER (CRMCUSTOMERNUMBER);

  CREATE OR REPLACE TRIGGER CSOL_AFTER_INSERT_ERP_I_CUST_LOG
  -- Version : 0.1
    AFTER INSERT ON ERP_I_CUSTOMER_LOG
    FOR EACH ROW
  DECLARE
  BEGIN
      "&1".PACK_CONNSOLACE.MESSAGE_ENQUEUE_TO_SOLACE(:NEW.TRANSFER_ID);
  END CSOL_AFTER_INSERT_ERP_I_CUST_LOG;

  CREATE OR REPLACE TRIGGER CSOL_BEFORE_INSERT_CUSTOMER
  -- Version : 0.1
    BEFORE INSERT ON CUSTOMER
    FOR EACH ROW
  DECLARE
  L_CUSTNR_FOUND NUMBER;

  BEGIN
    L_CUSTNR_FOUND := 0;
    SELECT 1 INTO L_CUSTNR_FOUND FROM ERP_I_CUSTOMER ERP WHERE ERP.CUSTOMERNUMBER = :NEW.CUSTOMERNUMBER;
    EXCEPTION
      WHEN no_data_found THEN
       RAISE_APPLICATION_ERROR(-20031,'Customer can''t be created from scratch in ABS. E-Force must deliver the customer to ABS');
  END CSOL_BEFORE_INSERT_CUSTOMER;                
}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/
exit;
