CREATE OR REPLACE PROCEDURE "&1".PROCESS_MESSAGE_FROM_SOLACE(MESSAGE_CLOB      IN CLOB,
                                                        SOLACE_TOPIC_NAME IN VARCHAR2) IS
  L_ERRM           VARCHAR2(1000);
  L_PROCESS_NUMBER NUMBER;
BEGIN
  BEGIN
    SELECT ST.PROCESSING_NUMBER
      INTO L_PROCESS_NUMBER
      FROM "&1".SOLACE_TOPIC ST
     WHERE UPPER(ST.SOLACE_TOPIC_IN) = UPPER(SOLACE_TOPIC_NAME);
  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO "&1".MESSAGE_FROM_SOLACE_NOT_PROCESSED
        (MESSAGE, TOPIC, TIMESTAMP)
      VALUES
        (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
      COMMIT;
  END;
  IF L_PROCESS_NUMBER = 1 AND MESSAGE_CLOB IS NOT NULL THEN
    "&1".INSERT_ERP_CUSTOMER(MESSAGE_CLOB, SOLACE_TOPIC_NAME);
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_PROCESSED
      (MESSAGE, TOPIC, TIMESTAMP)
    VALUES
      (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
    COMMIT;
  ELSE
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_NOT_PROCESSED
      (MESSAGE, TOPIC, TIMESTAMP)
    VALUES
      (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    L_ERRM := SQLERRM;
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      (MESSAGE_CLOB, SOLACE_TOPIC_NAME, L_ERRM, SYSDATE);
    COMMIT;  
END;
/
exit;