CREATE OR REPLACE PROCEDURE "&1".MERGE_CUSTOMER(JSON_STRING IN CLOB,
                                           TOPIC       IN VARCHAR2) IS
  --version 0.1										   
  V_JSON_OBJECT JSON_OBJECT_T;
  V_KEYS        JSON_KEY_LIST;
  L_ERRM        VARCHAR2(500);

  L_CRMCUSTOMERNUMBER VARCHAR2(18);
  L_CUSTOMER_NUMBER   NUMBER;
 
  L_SOLACE_TOPIC_OUT  VARCHAR2(100);
  L_COUNTRY           VARCHAR2(3);
  ENQUEUE_OPTIONS     DBMS_AQ.ENQUEUE_OPTIONS_T;
  MESSAGE_PROPERTIES  DBMS_AQ.MESSAGE_PROPERTIES_T;
  MESSAGE_HANDLE      RAW(16);
  MESSAGE             SYS.AQ$_JMS_TEXT_MESSAGE;
  MESSAGE_TEXT        CLOB;

BEGIN

  V_JSON_OBJECT := JSON_OBJECT_T.PARSE(JSON_STRING);
  V_KEYS        := V_JSON_OBJECT.GET_KEYS();
  FOR I IN 1 .. V_KEYS.COUNT LOOP
    V_JSON_OBJECT.RENAME_KEY(V_KEYS(I), UPPER(V_KEYS(I)));
  END LOOP;

  L_CRMCUSTOMERNUMBER := V_JSON_OBJECT.GET_STRING('ELISCUSTOMERID');
  L_CUSTOMER_NUMBER   := TO_NUMBER(V_JSON_OBJECT.GET_STRING('LMSID'));
 
  SELECT ST.SOLACE_TOPIC_OUT
    INTO L_SOLACE_TOPIC_OUT
  FROM "&1".SOLACE_TOPIC ST
  WHERE UPPER(ST.SOLACE_TOPIC_IN) = UPPER(TOPIC);

  SELECT C.COUNTRY_ID
    INTO L_COUNTRY
  FROM "&3".CUSTOMER C
  WHERE C.CUSTOMERNUMBER = L_CUSTOMER_NUMBER;

  UPDATE "&3".CUSTOMER C
  SET C.CRMCUSTOMERNUMBER = L_CRMCUSTOMERNUMBER,
	  C.TIMESTAMP = SYSDATE
  WHERE C.CUSTOMERNUMBER = L_CUSTOMER_NUMBER;

  MESSAGE_TEXT := '{ "elisCustomerId": "' || L_CRMCUSTOMERNUMBER || '"
                   , "lmsId": "' || L_CUSTOMER_NUMBER || '"                                
                   , "lmsSystemCode":" ABS IE"}';
  MESSAGE      := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT;
  MESSAGE.SET_TEXT(MESSAGE_TEXT);
  MESSAGE.SET_STRING_PROPERTY('solace_topic_out',
                              L_SOLACE_TOPIC_OUT || '/' || L_COUNTRY || '/' ||
                              L_CRMCUSTOMERNUMBER);

  DBMS_AQ.ENQUEUE(QUEUE_NAME         => 'SEND_TO_SOLACE_QUEUE',
                  ENQUEUE_OPTIONS    => ENQUEUE_OPTIONS,
                  MESSAGE_PROPERTIES => MESSAGE_PROPERTIES,
                  PAYLOAD            => MESSAGE,
                  MSGID              => MESSAGE_HANDLE);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    L_ERRM := SQLERRM;
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      (JSON_STRING, TOPIC, L_ERRM, SYSDATE);
    COMMIT;
  
END;
/
exit;