CREATE OR REPLACE PROCEDURE "&1".PROCESS_MESSAGE_FROM_SOLACE(MESSAGE_CLOB      IN CLOB,
                                                        SOLACE_TOPIC_NAME IN VARCHAR2) IS
  --version 0.1														
  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 MESSAGE_CLOB IS NULL THEN
    L_PROCESS_NUMBER := -1;
  END IF;
  CASE L_PROCESS_NUMBER
    WHEN 1 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;
    WHEN 2 THEN
      "&1".GOLDENED_CUSTOMER(MESSAGE_CLOB, SOLACE_TOPIC_NAME);
      INSERT INTO "&1".MESSAGE_FROM_SOLACE_PROCESSED
        (MESSAGE, TOPIC, TIMESTAMP)
      VALUES
        (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
      COMMIT;
    WHEN 3 THEN
      "&1".MERGE_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 CASE;
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;