CREATE OR REPLACE PROCEDURE "&1".MESSAGE_ENQUEUE_TO_SOLACE(TRANSFER IN NUMBER) IS
  --version 0.1
  L_ERRM             VARCHAR2(500);
  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;
  L_COUNTRY          VARCHAR2(3);
  L_PLANT            VARCHAR2(6);
  L_CUSTOMER_CRM     VARCHAR2(18);
  L_SOLACE_TOPIC_OUT VARCHAR2(100);
  L_SEND_RESPONSE    VARCHAR2(1);
  L_CUSTOMER_NUMBER   NUMBER;

BEGIN
  SELECT ST.SOLACE_TOPIC_OUT
    INTO L_SOLACE_TOPIC_OUT
    FROM "&1".SOLACE_TOPIC ST
   WHERE ST.PROCESSING_NUMBER = 1;
  BEGIN
    SELECT ERP.COUNTRY,
           ERP.BUSINESSUNIT,
           ERP.CRMCUSTOMERNUMBER,
           ERP.SEND_RESPONSE,
           ERP.CUSTOMERNUMBER
      INTO L_COUNTRY,
           L_PLANT,
           L_CUSTOMER_CRM,
           L_SEND_RESPONSE,
           L_CUSTOMER_NUMBER
      FROM "&3".ERP_I_CUSTOMER ERP
     WHERE ERP.TRANSFER_ID = TRANSFER
       AND ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN;
  END;

  UPDATE "&3".CUSTOMER C
     SET C.CRMCUSTOMERNUMBER = L_CUSTOMER_CRM
   WHERE C.CUSTOMERNUMBER = L_CUSTOMER_NUMBER;

  IF L_SEND_RESPONSE = 'Y' THEN
	MESSAGE_TEXT := '{"elisCustomerId": "' || L_CUSTOMER_CRM || '"
                     , "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_PLANT || '/' || L_CUSTOMER_CRM);

  
	DBMS_AQ.ENQUEUE(QUEUE_NAME         => 'SEND_TO_SOLACE_QUEUE',
                  ENQUEUE_OPTIONS    => ENQUEUE_OPTIONS,
                  MESSAGE_PROPERTIES => MESSAGE_PROPERTIES,
                  PAYLOAD            => MESSAGE,
                  MSGID              => MESSAGE_HANDLE);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    L_ERRM := SQLERRM;
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      ('MESSAGE_ENQUEUE_TO_SOLACE', L_SOLACE_TOPIC_OUT, L_ERRM, SYSDATE);
END MESSAGE_ENQUEUE_TO_SOLACE;
/
exit;