create or replace package "&1".PACK_CONNSOLACE is
  /************************************************************************************************************************\
  *  Author  : ABS
  *  Purpose : This package contains the functional code to be used by ConnSolace application
  *  Version : 0.2
  *  Created : 27.02.2025 CSOLN-1 															  
  *  Updated : 14.04.2025 CSOLN-12 Additional developments Solace						      
  *            17.04.2025 CSOLN-16 Country code replaced by 1 on operations_account_merging_ack_v1_'countryCode'_'elisId'
  \************************************************************************************************************************/

  -- Public function and procedure declarations
  PROCEDURE PROCESS_MESSAGE_FROM_SOLACE(MESSAGE_CLOB      IN CLOB,
                                        SOLACE_TOPIC_NAME IN VARCHAR2);
                                       
  PROCEDURE CUSTOMER_I_EVENT_CALLBACK(CONTEXT  RAW,
                                      REGINFO  SYS.AQ$_REG_INFO,
                                      DESCR    SYS.AQ$_DESCRIPTOR,
                                      PAYLOAD  RAW,
                                      PAYLOADL NUMBER);  
                                            
  PROCEDURE MESSAGE_ENQUEUE_TO_SOLACE(TRANSFER IN NUMBER);                                                                     
end PACK_CONNSOLACE;
/

create or replace package body "&1".PACK_CONNSOLACE is

  -- Function and procedure implementations
  PROCEDURE WRITE_SOLACE_ERROR(LOG_MESSAGE IN CLOB,  LOG_TOPIC IN VARCHAR2, LOG_ERROR IN VARCHAR2) IS
  BEGIN
    INSERT INTO MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      (LOG_MESSAGE, LOG_TOPIC, LOG_ERROR,  SYSDATE);
    COMMIT;  
  END;
  
  FUNCTION GET_ABS_CUSTOMER_NUMBER (BUSINESSUNITCODE VARCHAR2) RETURN NUMBER IS
    CUSTOMERNUMBER NUMBER;
    CUSTOMERMINNR NUMBER;
    L_ERRM        VARCHAR2(500);
  BEGIN
    BEGIN
      SELECT SS.VALUE INTO CUSTOMERMINNR
      FROM "&3".SYSTEMSETTING SS
      WHERE SS.NAME ='DEFAULTCUSTOMERMIN'
      AND SS.PLANT_ID = (SELECT B.PLANT_ID FROM "&3".BUSINESSUNIT B WHERE B.CODE = BUSINESSUNITCODE);
    EXCEPTION
    WHEN OTHERS THEN
      L_ERRM := SQLERRM;
      WRITE_SOLACE_ERROR(BUSINESSUNITCODE, '', L_ERRM);
      CUSTOMERMINNR := 1; 
    END;
      
    WITH CUSTNUMBER AS
     (SELECT C.CUSTOMERNUMBER CUSTNR,
             RANK() OVER(ORDER BY C.CUSTOMERNUMBER) AS R,
             MAX(C.CUSTOMERNUMBER) OVER() MAXCUSTNR
        FROM ( SELECT CUST.CUSTOMERNUMBER FROM "&3".CUSTOMER CUST WHERE CUST.CUSTOMERNUMBER > CUSTOMERMINNR UNION ALL
               SELECT ERP.CUSTOMERNUMBER FROM "&3".ERP_I_CUSTOMER ERP WHERE ERP.CUSTOMERNUMBER > CUSTOMERMINNR) C )
    SELECT MIN((CASE
                 WHEN CUSTNUMBER.CUSTNR > CUSTNUMBER.R + CUSTOMERMINNR THEN
                  CUSTNUMBER.R + CUSTOMERMINNR
                 ELSE
                  CUSTNUMBER.MAXCUSTNR + 1
               END))
      INTO CUSTOMERNUMBER
      FROM CUSTNUMBER;
  
    IF CUSTOMERNUMBER IS NULL THEN
      CUSTOMERNUMBER := CUSTOMERMINNR;
    END IF;
    RETURN CUSTOMERNUMBER;
  END GET_ABS_CUSTOMER_NUMBER;

  PROCEDURE INSERT_ERP_CUSTOMER(JSON_STRING IN CLOB, TOPIC IN VARCHAR2) IS
    V_JSON_OBJECT JSON_OBJECT_T;
    V_KEYS        JSON_KEY_LIST;
    L_ERRM        VARCHAR2(500);

    L_TRANSFER_ID                 NUMBER;
    L_TRANSFER_STATUS             NUMBER;
    L_CUSTOMERNUMBER              NUMBER;
    L_CUSTOMERNUMBER_OLD          NUMBER;
    L_LEGACYCUSTOMERNUMBER        VARCHAR2(12);
    L_CUSTOMERNAME                VARCHAR2(40);
    L_LEGALNAME                   VARCHAR2(80);
    L_BILLTOCUSTOMERNUMBER        NUMBER;
    L_BUSINESSUNIT                VARCHAR2(6);
    L_SEARCHNAME                  VARCHAR2(10);
    L_DATEACTIVE                  DATE;
    L_DATEINACTIVE                DATE;
    L_ACTIVE                      VARCHAR2(1);
    L_MAILADDRESS                 VARCHAR2(30);
    L_MAILADDRESS2                VARCHAR2(30);
    L_MAILADDRESS3                VARCHAR2(30);
    L_MAILZIPCODE                 VARCHAR2(15);
    L_MAILCITY                    VARCHAR2(30);
    L_COUNTRY                     VARCHAR2(3);
    L_EMAIL                       VARCHAR2(300);
    L_PHONENO                     VARCHAR2(30);
    L_FAXNO                       VARCHAR2(30);
    L_LANGUAGECODE                VARCHAR2(5);
    L_MARKETSEGMENTCODE           VARCHAR2(6);
    L_SALESAREACODE               VARCHAR2(6);
    L_CHAINCODE                   VARCHAR2(6);
    L_MASTERACCOUNTNUMBER         NUMBER;
    L_REMARK                      VARCHAR2(1000);
    L_CURRENCYCODE                VARCHAR2(3);
    L_COCNUMBER                   VARCHAR2(20);
    L_PNNUMBER                    VARCHAR2(20);
    L_CUSTOMERRANKING             VARCHAR2(10);
    L_VISITADDRESS                VARCHAR2(30);
    L_VISITADDRESS2               VARCHAR2(30);
    L_VISITZIPCODE                VARCHAR2(15);
    L_VISITCITY                   VARCHAR2(30);
    L_TAXABLE                     VARCHAR2(1);
    L_VATNUMBER                   VARCHAR2(15);
    L_SENDINVOICETO               NUMBER;
    L_EXTRANAME1                  VARCHAR2(40);
    L_EXTRANAME2                  VARCHAR2(40);
    L_INVOICEADDRESS              VARCHAR2(30);
    L_INVOICEADDRESS2             VARCHAR2(30);
    L_INVOICEZIPCODE              VARCHAR2(15);
    L_INVOICECITY                 VARCHAR2(30);
    L_PAYMENTTERMSCODE            VARCHAR2(6);
    L_PAYMENTTYPE                 NUMBER;
    L_ILNNUMBER                   NUMBER;
    L_SALESEMPLOYEE1              NUMBER;
    L_SALESEMPLOYEE2              NUMBER;
    L_COVERPAGE_NAME              VARCHAR2(40);
    L_COVERPAGE_EXTRANAME1        VARCHAR2(40);
    L_COVERPAGE_EXTRANAME2        VARCHAR2(40);
    L_COVERPAGE_CONTACTPERSON     VARCHAR2(40);
    L_COVERPAGE_COUNTRY           VARCHAR2(2);
    L_COVERPAGE_ADDRESS           VARCHAR2(40);
    L_COVERPAGE_ADDRESS2          VARCHAR2(40);
    L_COVERPAGE_ZIPCODE           VARCHAR2(15);
    L_COVERPAGE_CITY              VARCHAR2(30);
    L_SICCODE                     VARCHAR2(6);
    L_DEFAULTINVBU                VARCHAR2(6);
    L_VISITSTATE                  VARCHAR2(20);
    L_INVOICESTATE                VARCHAR2(20);
    L_NATIONALACCOUNTCODE         VARCHAR2(15);
    L_STATETAXCODE                VARCHAR2(6);
    L_COUNTYTAXAUTHORITYCODE      VARCHAR2(12);
    L_CITYTAXAUTHORITYCODE        VARCHAR2(12);
    L_TAXEXEMPTCODE               VARCHAR2(18);
    L_MAILSTATE                   VARCHAR2(20);
    L_INVOICELAYOUTCODE           VARCHAR2(6);
    L_INVOICECURRENCYCODE         VARCHAR2(3);
    L_FINANCECHARGEPUTON          NUMBER;
    L_FINANCECHARGEDAYS           NUMBER;
    L_FULLPAYMENTONDELIVERY       VARCHAR2(1);
    L_STATEMENTLAYOUTCODE         VARCHAR2(6);
    L_STATEMENTHANDLINGSTARTDATE  DATE;
    L_TIMESTAMP                   DATE;
    L_AUTOSOILPERCENTAGE          NUMBER;
    L_GARMENTEMBLEM               VARCHAR2(1);
    L_USEPRODSIZESURCHARGEPRICING VARCHAR2(1);
    L_INVOICECOPIES               NUMBER;
    L_PAYBYCUSTOMER               NUMBER;
    L_SENDSTATEMENTBYEMAIL        VARCHAR2(1);
    L_CHARGELOSSCHARGE            VARCHAR2(1);
    L_PAYMENTMETHOD               NUMBER;
    L_NOPREPCHARGE1STINVOICE      VARCHAR2(1);
    L_NOEMBLEMCHARGE1STINVOICE    VARCHAR2(1);
    L_GLOBALLOCATIONNUMBER        VARCHAR2(20);
    L_CRMCUSTOMERNUMBER           VARCHAR2(18);
    L_SEND_RESPONSE               VARCHAR2(1);
    L_ADDRESS                     VARCHAR2(200);
    L_POSITION1                   NUMBER;
    L_POSITION2                   NUMBER;
    L_POSITION                    NUMBER;
  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_TRANSFER_ID          := "&3".SEQ_TRANSFER.NEXTVAL;
    L_TRANSFER_STATUS      := 0;
    L_CRMCUSTOMERNUMBER    := SUBSTR(V_JSON_OBJECT.GET_STRING('ELISCUSTOMERID'), 1, 18);
    L_BUSINESSUNIT         := SUBSTR(V_JSON_OBJECT.GET_STRING('PLANTNUMBER'), 1, 6);
    IF V_JSON_OBJECT.get_Boolean('PESTCONTROL') = TRUE THEN
      L_SEND_RESPONSE    := 'N';
      L_CUSTOMERNUMBER   := TO_NUMBER(SUBSTR(L_CRMCUSTOMERNUMBER, 3));
    ELSE
      L_SEND_RESPONSE    := 'Y';
      L_CUSTOMERNUMBER   := GET_ABS_CUSTOMER_NUMBER(L_BUSINESSUNIT);
    END IF;
    L_CUSTOMERNUMBER_OLD   := NULL;
    L_LEGACYCUSTOMERNUMBER := NULL;
    L_CUSTOMERNAME         := SUBSTR(V_JSON_OBJECT.GET_STRING('COMMERCIALNAME'), 1, 40);
    L_LEGALNAME            := SUBSTR(V_JSON_OBJECT.GET_STRING('COMPANYNAME'), 1, 80);
    L_BILLTOCUSTOMERNUMBER := L_CUSTOMERNUMBER;
    L_SEARCHNAME           := SUBSTR(L_CUSTOMERNAME, 1, 10);
    L_DATEACTIVE           := SYSDATE;
    L_DATEINACTIVE         := TO_DATE('31-12-2099', 'dd-mm-yyyy');
    L_ACTIVE               := 'Y';
    L_ADDRESS              := REPLACE(V_JSON_OBJECT.GET_STRING('ADDRESS'), '\', '\\');
    L_ADDRESS              := REPLACE(L_ADDRESS, CHR(10), '\\n');
    L_POSITION             := INSTR(L_ADDRESS, '\\n', 1, 1) - 1;
    IF L_POSITION <= 0 THEN
      L_POSITION1 := 30;
    ELSE
      L_POSITION1 := L_POSITION;
    END IF;
    L_MAILADDRESS          := TRIM(BOTH ' ' FROM
                        SUBSTR(L_ADDRESS, 1, LEAST(L_POSITION1, 30)));

    IF L_POSITION > 0 THEN
      L_POSITION2 := INSTR(L_ADDRESS, '\\n', 1, 2) - 1;
      IF L_POSITION2 > 0 THEN
        L_MAILADDRESS2 := TRIM(BOTH ' ' FROM
                               SUBSTR(L_ADDRESS,
                                      L_POSITION1 + 4,
                                      LEAST(L_POSITION2 - L_POSITION1 - 3, 30)));
      ELSE
         L_MAILADDRESS2       := TRIM(BOTH ' ' FROM
                                     SUBSTR(L_ADDRESS, L_POSITION1 + 4, 30));
      END IF;
    END IF;
    L_POSITION2 := INSTR(L_ADDRESS, '\\n', 1, 2) - 1;
    IF L_POSITION2 > 0 THEN
      L_MAILADDRESS2 := TRIM(BOTH ' ' FROM
                             SUBSTR(L_ADDRESS,
                                    L_POSITION1 + 4,
                                    LEAST(L_POSITION2 - L_POSITION1 - 3, 30)));
    ELSE
      L_MAILADDRESS2 := TRIM(BOTH ' ' FROM
                             SUBSTR(L_ADDRESS, L_POSITION1 + 4, 30));
    END IF;

    IF L_POSITION2 > 0 THEN
      L_MAILADDRESS3         := TRIM(BOTH ' ' FROM
                                   SUBSTR(L_ADDRESS, L_POSITION2 + 4, 30));
    END IF;
    IF LENGTH(L_MAILADDRESS2) < 30 AND L_MAILADDRESS3 IS NOT NULL THEN
      L_MAILADDRESS2 := SUBSTR(L_MAILADDRESS2 || ' ' || L_MAILADDRESS3, 1, 30);
    END IF;
    L_MAILZIPCODE                 := SUBSTR(V_JSON_OBJECT.GET_STRING('ZIPCODE'),
                                            1,
                                          15);
    L_MAILCITY                    := SUBSTR(V_JSON_OBJECT.GET_STRING('CITY'),
                                            1,
                                            30);
    L_COUNTRY           := SUBSTR(V_JSON_OBJECT.GET_STRING('COUNTRY'), 1, 3);
    L_EMAIL                       := V_JSON_OBJECT.GET_STRING('EMAIL');
    IF L_EMAIL = '-' THEN
      L_EMAIL := NULL;
    END IF;
    L_PHONENO                     := SUBSTR(V_JSON_OBJECT.GET_STRING('PHONENUMBER'),
                                            1,
                                          30);
    L_FAXNO                       := SUBSTR(V_JSON_OBJECT.GET_STRING('FAXNUMBER'),
                                            1,
                                          30);
    L_LANGUAGECODE      := SUBSTR(UPPER(V_JSON_OBJECT.GET_STRING('LANGUAGE')), 1, 5);
    IF L_LANGUAGECODE IS NULL OR L_LANGUAGECODE = '-' THEN
      SELECT L.CODE INTO L_LANGUAGECODE
      FROM "&3".PLANT P
      JOIN "&3".LANGUAGES L
      ON P.LANGUAGE_ID = L.LANGUAGE_ID
      JOIN "&3".BUSINESSUNIT B
      ON B.PLANT_ID = P.PLANT_ID
      WHERE B.CODE = L_BUSINESSUNIT;
    END IF;
    L_MARKETSEGMENTCODE := SUBSTR(V_JSON_OBJECT.GET_STRING('ACTIVITYCODE'), 1, 6);
    IF L_MARKETSEGMENTCODE IS NULL THEN
      L_MARKETSEGMENTCODE := '999999';
    END IF;
    L_SALESAREACODE               := '-';
    L_CHAINCODE                   := NULL;
    L_MASTERACCOUNTNUMBER         := NULL;
    L_REMARK                      := NULL;
    L_CURRENCYCODE                := NULL;
    L_COCNUMBER                   := NULL;
    L_PNNUMBER                    := NULL;
    L_CUSTOMERRANKING             := NULL;
    L_VISITADDRESS                := L_MAILADDRESS;
    L_VISITADDRESS2               := L_MAILADDRESS2;
    L_VISITZIPCODE                := L_MAILZIPCODE;
    L_VISITCITY                   := L_MAILCITY;
    L_TAXABLE                     := 'Y';
    L_VATNUMBER                   := SUBSTR(V_JSON_OBJECT.GET_STRING('VATNUMBER'), 1, 15);
    L_SENDINVOICETO               := 1;
    L_EXTRANAME1                  := NULL;
    L_EXTRANAME2                  := NULL;
    L_INVOICEADDRESS              := L_MAILADDRESS;
    L_INVOICEADDRESS2             := L_MAILADDRESS2;
    L_INVOICEZIPCODE              := L_MAILZIPCODE;
    L_INVOICECITY                 := L_MAILCITY;
    L_PAYMENTTERMSCODE            := '-';
    L_PAYMENTTYPE                 := NULL;
    L_ILNNUMBER                   := NULL;
    L_SALESEMPLOYEE1              := NULL;
    L_SALESEMPLOYEE2              := NULL;
    L_COVERPAGE_NAME              := NULL;
    L_COVERPAGE_EXTRANAME1        := NULL;
    L_COVERPAGE_EXTRANAME2        := NULL;
    L_COVERPAGE_CONTACTPERSON     := NULL;
    L_COVERPAGE_COUNTRY           := NULL;
    L_COVERPAGE_ADDRESS           := NULL;
    L_COVERPAGE_ADDRESS2          := NULL;
    L_COVERPAGE_ZIPCODE           := NULL;
    L_COVERPAGE_CITY              := NULL;
    L_SICCODE                     := NULL;
    L_DEFAULTINVBU                := L_BUSINESSUNIT;
    L_VISITSTATE                  := NULL;
    L_INVOICESTATE                := NULL;
    L_NATIONALACCOUNTCODE         := NULL;
    L_STATETAXCODE                := NULL;
    L_COUNTYTAXAUTHORITYCODE      := NULL;
    L_CITYTAXAUTHORITYCODE        := NULL;
    L_TAXEXEMPTCODE               := NULL;
    L_MAILSTATE                   := NULL;
    L_INVOICELAYOUTCODE           := NULL;
    L_INVOICECURRENCYCODE         := NULL;
    L_FINANCECHARGEPUTON          := 1;
    L_FINANCECHARGEDAYS           := 0;
    L_FULLPAYMENTONDELIVERY       := 'N';
    L_STATEMENTLAYOUTCODE         := '1';
    L_STATEMENTHANDLINGSTARTDATE  := NULL;
    L_TIMESTAMP                   := SYSDATE;
    L_AUTOSOILPERCENTAGE          := 0;
    L_GARMENTEMBLEM               := 'N';
    L_USEPRODSIZESURCHARGEPRICING := 'N';
    L_INVOICECOPIES               := 1;
    L_PAYBYCUSTOMER               := 0;
    L_SENDSTATEMENTBYEMAIL        := 'N';
    L_CHARGELOSSCHARGE            := 'N';
    L_PAYMENTMETHOD               := 2;
    L_NOPREPCHARGE1STINVOICE      := 'N';
    L_NOEMBLEMCHARGE1STINVOICE    := 'N';
    L_GLOBALLOCATIONNUMBER        := NULL;

    INSERT INTO "&3".ERP_I_CUSTOMER
      (TRANSFER_ID,
       TRANSFER_STATUS,
       CUSTOMERNUMBER,
       CUSTOMERNUMBER_OLD,
       LEGACYCUSTOMERNUMBER,
       CUSTOMERNAME,
       LEGALNAME,
       BILLTOCUSTOMERNUMBER,
       BUSINESSUNIT,
       SEARCHNAME,
       DATEACTIVE,
       DATEINACTIVE,
       ACTIVE,
       MAILADDRESS,
       MAILADDRESS2,
       MAILZIPCODE,
       MAILCITY,
       COUNTRY,
       EMAIL,
       PHONENO,
       FAXNO,
       LANGUAGECODE,
       MARKETSEGMENTCODE,
       SALESAREACODE,
       CHAINCODE,
       MASTERACCOUNTNUMBER,
       REMARK,
       CURRENCYCODE,
       COCNUMBER,
       PNNUMBER,
       CUSTOMERRANKING,
       VISITADDRESS,
       VISITADDRESS2,
       VISITZIPCODE,
       VISITCITY,
       TAXABLE,
       VATNUMBER,
       SENDINVOICETO,
       EXTRANAME1,
       EXTRANAME2,
       INVOICEADDRESS,
       INVOICEADDRESS2,
       INVOICEZIPCODE,
       INVOICECITY,
       PAYMENTTERMSCODE,
       PAYMENTTYPE,
       ILNNUMBER,
       SALESEMPLOYEE1,
       SALESEMPLOYEE2,
       COVERPAGE_NAME,
       COVERPAGE_EXTRANAME1,
       COVERPAGE_EXTRANAME2,
       COVERPAGE_CONTACTPERSON,
       COVERPAGE_COUNTRY,
       COVERPAGE_ADDRESS,
       COVERPAGE_ADDRESS2,
       COVERPAGE_ZIPCODE,
       COVERPAGE_CITY,
       SICCODE,
       DEFAULTINVBU,
       VISITSTATE,
       INVOICESTATE,
       NATIONALACCOUNTCODE,
       STATETAXCODE,
       COUNTYTAXAUTHORITYCODE,
       CITYTAXAUTHORITYCODE,
       TAXEXEMPTCODE,
       MAILSTATE,
       INVOICELAYOUTCODE,
       INVOICECURRENCYCODE,
       FINANCECHARGEPUTON,
       FINANCECHARGEDAYS,
       FULLPAYMENTONDELIVERY,
       STATEMENTLAYOUTCODE,
       STATEMENTHANDLINGSTARTDATE,
       TIMESTAMP,
       AUTOSOILPERCENTAGE,
       GARMENTEMBLEM,
       USEPRODSIZESURCHARGEPRICING,
       INVOICECOPIES,
       PAYBYCUSTOMER,
       SENDSTATEMENTBYEMAIL,
       CHARGELOSSCHARGE,
       PAYMENTMETHOD,
       NOPREPCHARGE1STINVOICE,
       NOEMBLEMCHARGE1STINVOICE,
       GLOBALLOCATIONNUMBER,
       CRMCUSTOMERNUMBER,
       SEND_RESPONSE)
    VALUES
      (L_TRANSFER_ID,
       L_TRANSFER_STATUS,
       L_CUSTOMERNUMBER,
       L_CUSTOMERNUMBER_OLD,
       L_LEGACYCUSTOMERNUMBER,
       L_CUSTOMERNAME,
       L_LEGALNAME,
       L_BILLTOCUSTOMERNUMBER,
       L_BUSINESSUNIT,
       L_SEARCHNAME,
       L_DATEACTIVE,
       L_DATEINACTIVE,
       L_ACTIVE,
       L_MAILADDRESS,
       L_MAILADDRESS2,
       L_MAILZIPCODE,
       L_MAILCITY,
       L_COUNTRY,
       L_EMAIL,
       L_PHONENO,
       L_FAXNO,
       L_LANGUAGECODE,
       L_MARKETSEGMENTCODE,
       L_SALESAREACODE,
       L_CHAINCODE,
       L_MASTERACCOUNTNUMBER,
       L_REMARK,
       L_CURRENCYCODE,
       L_COCNUMBER,
       L_PNNUMBER,
       L_CUSTOMERRANKING,
       L_VISITADDRESS,
       L_VISITADDRESS2,
       L_VISITZIPCODE,
       L_VISITCITY,
       L_TAXABLE,
       L_VATNUMBER,
       L_SENDINVOICETO,
       L_EXTRANAME1,
       L_EXTRANAME2,
       L_INVOICEADDRESS,
       L_INVOICEADDRESS2,
       L_INVOICEZIPCODE,
       L_INVOICECITY,
       L_PAYMENTTERMSCODE,
       L_PAYMENTTYPE,
       L_ILNNUMBER,
       L_SALESEMPLOYEE1,
       L_SALESEMPLOYEE2,
       L_COVERPAGE_NAME,
       L_COVERPAGE_EXTRANAME1,
       L_COVERPAGE_EXTRANAME2,
       L_COVERPAGE_CONTACTPERSON,
       L_COVERPAGE_COUNTRY,
       L_COVERPAGE_ADDRESS,
       L_COVERPAGE_ADDRESS2,
       L_COVERPAGE_ZIPCODE,
       L_COVERPAGE_CITY,
       L_SICCODE,
       L_DEFAULTINVBU,
       L_VISITSTATE,
       L_INVOICESTATE,
       L_NATIONALACCOUNTCODE,
       L_STATETAXCODE,
       L_COUNTYTAXAUTHORITYCODE,
       L_CITYTAXAUTHORITYCODE,
       L_TAXEXEMPTCODE,
       L_MAILSTATE,
       L_INVOICELAYOUTCODE,
       L_INVOICECURRENCYCODE,
       L_FINANCECHARGEPUTON,
       L_FINANCECHARGEDAYS,
       L_FULLPAYMENTONDELIVERY,
       L_STATEMENTLAYOUTCODE,
       L_STATEMENTHANDLINGSTARTDATE,
       L_TIMESTAMP,
       L_AUTOSOILPERCENTAGE,
       L_GARMENTEMBLEM,
       L_USEPRODSIZESURCHARGEPRICING,
       L_INVOICECOPIES,
       L_PAYBYCUSTOMER,
       L_SENDSTATEMENTBYEMAIL,
       L_CHARGELOSSCHARGE,
       L_PAYMENTMETHOD,
       L_NOPREPCHARGE1STINVOICE,
       L_NOEMBLEMCHARGE1STINVOICE,
       L_GLOBALLOCATIONNUMBER,
       L_CRMCUSTOMERNUMBER,
       L_SEND_RESPONSE);
    
    INSERT INTO "&3".ERP_I_DELIVERYPOINT
     (TRANSFER_ID, 
     TRANSFER_STATUS,   
     CUSTOMERNUMBER,  
     DELIVERYPOINTCODE,   
     DELIVERYPOINTCODE_OLD,   
     DESCRIPTION,  
     ADDRESS,   
     ADDRESS2,
     ADDRESS3,   
     ZIPCODE,   
     CITY,   
     TIMESTAMP)  
     VALUES
     (L_TRANSFER_ID,
       L_TRANSFER_STATUS,
       L_CUSTOMERNUMBER,
       '1', 
       NULL,
       '-',
       L_VISITADDRESS,
       L_VISITADDRESS2,
       NULL,
       L_VISITZIPCODE,
       L_VISITCITY,
       SYSDATE);
  
    INSERT INTO "&3".ERP_I_COSTCENTER
       (TRANSFER_ID,  
       TRANSFER_STATUS,   
       CUSTOMERNUMBER,   
       COSTCENTERCODE,   
       COSTCENTERCODE_OLD,   
       DESCRIPTION,   
       EXPORTEDI,   
       INVOICEADDRESS,   
       INVOICEADDRESS2, 
       INVOICEADDRESS3,  
       INVOICEZIPCODE,   
       INVOICECITY,
       TIMESTAMP)
     VALUES
     (L_TRANSFER_ID,
       L_TRANSFER_STATUS,
       L_CUSTOMERNUMBER,
       '1', 
       NULL,
       '-',
       0,
       L_VISITADDRESS,
       L_VISITADDRESS2,
       NULL,
       L_VISITZIPCODE,
       L_VISITCITY,
       SYSDATE
      );
      INSERT INTO "&3".ERP_I_DEPARTMENT
      (TRANSFER_ID,  
       TRANSFER_STATUS,  
       CUSTOMERNUMBER,   
       DEPARTMENTNUMBER,  
       DEPARTMENTNUMBER_OLD,   
       DESCRIPTION,  
       COSTCENTERCODE,   
       TIMESTAMP  
       )
       VALUES
       (L_TRANSFER_ID,
        L_TRANSFER_STATUS,
        L_CUSTOMERNUMBER,
        1, 
        NULL,
        '-',
        '1',
        SYSDATE
       );
    INSERT INTO "&3".ERP_I_CONSUMPTIONPOINT
    (TRANSFER_ID,
     TRANSFER_STATUS,  
     CUSTOMERNUMBER,   
     DEPARTMENTNUMBER, 
     CONSUMPTIONPOINTNUMBER, 
     CONSUMPTIONPOINTNUMBER_OLD,  
     LEGACYDISTRIBUTIONPOINTNUMBER,   
     DESCRIPTION, 
     DELIVERYPOINTCODE,  
     COSTCENTERCODE,
     ACTIVE,
     TIMESTAMP
    ) 
    VALUES
   (L_TRANSFER_ID,
    L_TRANSFER_STATUS,
    L_CUSTOMERNUMBER,
    1,
    1,
    NULL,
    NULL,
    '-',
    '1',
    '1',
    'Y',
    SYSDATE 
    );    
    COMMIT;  
  EXCEPTION
    WHEN OTHERS THEN
      L_ERRM := SQLERRM;
      WRITE_SOLACE_ERROR(JSON_STRING, TOPIC, L_ERRM);
  END INSERT_ERP_CUSTOMER;
  
  PROCEDURE GOLDENED_CUSTOMER(JSON_STRING IN CLOB, TOPIC IN VARCHAR2) IS
    V_JSON_OBJECT JSON_OBJECT_T;
    V_KEYS        JSON_KEY_LIST;
    L_ERRM        VARCHAR2(500);

    L_CRMCUSTOMERNUMBER VARCHAR2(18);
    L_LEGALNAME         VARCHAR2(80);
    L_MAILSTREET   VARCHAR2(30);
    L_MAILSTREET2  VARCHAR2(30);
    L_MAILZIPCODE       VARCHAR2(15);
    L_MAILCITY          VARCHAR2(30);
    L_COUNTRY           VARCHAR2(3);
    L_FAXNO             VARCHAR2(30);
    L_EMAIL             VARCHAR2(300);
    L_PHONENO           VARCHAR2(30);
    L_VATNUMBER         VARCHAR2(15);
    L_LANGUAGECODE      VARCHAR2(5);
    L_MARKETSEGMENTCODE VARCHAR2(6);
    L_COCNUMBER         VARCHAR2(20);
	L_MARKETSEGMENT_ID  NUMBER;

  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 := SUBSTR(V_JSON_OBJECT.GET_STRING('ELISID'), 1, 18);
    L_LEGALNAME         := SUBSTR(V_JSON_OBJECT.GET_STRING('LEGALNAME'), 1, 80);
    L_MAILSTREET        := TRIM(BOTH ' ' FROM SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESSLINE1'), 1, 30));
    L_MAILSTREET2       := TRIM(BOTH ' ' FROM SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESSLINE2'), 1, 30));
    IF L_MAILSTREET IS NULL AND L_MAILSTREET2 IS NOT NULL THEN
      L_MAILSTREET := L_MAILSTREET2;
      L_MAILSTREET2 := NULL;
    END IF;  
    L_MAILZIPCODE       := SUBSTR(V_JSON_OBJECT.GET_STRING('ZIPCODE'), 1, 15);
    L_MAILCITY          := SUBSTR(V_JSON_OBJECT.GET_STRING('CITY'), 1, 30);
    L_COUNTRY           := SUBSTR(V_JSON_OBJECT.GET_STRING('COUNTRYCODE'), 1, 3);
    L_FAXNO             := SUBSTR(V_JSON_OBJECT.GET_STRING('FAXNUMBER'), 1, 30);
    L_EMAIL             := V_JSON_OBJECT.GET_STRING('EMAIL');
    L_PHONENO           := SUBSTR(V_JSON_OBJECT.GET_STRING('PHONENUMBER'), 1, 30);
    L_VATNUMBER         := SUBSTR(V_JSON_OBJECT.GET_STRING('VATNUMBER'), 1, 30);
    L_LANGUAGECODE      := SUBSTR(V_JSON_OBJECT.GET_STRING('LANGUAGE'), 1, 5);  
	L_MARKETSEGMENTCODE := SUBSTR(V_JSON_OBJECT.GET_STRING('ACTIVITY'), 1, 6);
	IF L_MARKETSEGMENTCODE IS NOT NULL THEN
     SELECT M.MARKETSEGMENT_ID INTO L_MARKETSEGMENT_ID
     FROM "&3".MARKETSEGMENT M 
     WHERE M.CODE = L_MARKETSEGMENTCODE;
	END IF;
    L_COCNUMBER         := SUBSTR(V_JSON_OBJECT.GET_STRING('NATIONALID'), 1, 20);

    UPDATE "&3".CUSTOMER C
       SET C.LEGALNAME = CASE
                           WHEN L_LEGALNAME IS NOT NULL THEN
                            L_LEGALNAME
                           ELSE
                            C.LEGALNAME
                         END,
           C.COUNTRY_ID = CASE
                            WHEN L_COUNTRY IS NOT NULL THEN
                             (SELECT CT.COUNTRY_ID
                                FROM "&3".COUNTRY CT
                               WHERE CT.CODE = L_COUNTRY)
                            ELSE
                             C.COUNTRY_ID
                          END,
           C.FAXNO = CASE
                       WHEN L_FAXNO IS NOT NULL THEN
                        L_FAXNO
                       ELSE
                        C.FAXNO
                     END,
           C.EMAIL = CASE
                       WHEN L_EMAIL IS NOT NULL THEN
                        L_EMAIL
                       ELSE
                        C.EMAIL
                     END,
           C.PHONENO = CASE
                         WHEN L_PHONENO IS NOT NULL THEN
                          L_PHONENO
                         ELSE
                          C.PHONENO
                       END,
           C.VATNUMBER = CASE
                           WHEN L_VATNUMBER IS NOT NULL THEN
                            L_VATNUMBER
                           ELSE
                            C.VATNUMBER
                         END,
           C.LANGUAGE_ID = CASE
                             WHEN L_LANGUAGECODE IS NOT NULL THEN
                              COALESCE((SELECT L.LANGUAGE_ID
                                 FROM "&3".LANGUAGES L
                                WHERE L.CODE = L_LANGUAGECODE), C.LANGUAGE_ID)
                             ELSE
                              C.LANGUAGE_ID
                           END,
		   C.MARKETSEGMENT_ID = CASE
							 WHEN L_MARKETSEGMENT_ID IS NOT NULL THEN
								L_MARKETSEGMENT_ID
							 ELSE
								C.MARKETSEGMENT_ID
						   END,
           C.COCNUMBER = CASE
                           WHEN L_COCNUMBER IS NOT NULL THEN
                            L_COCNUMBER
                           ELSE
                            C.COCNUMBER
                         END,
         C.TIMESTAMP   = SYSDATE,
		     C.SYSTEMUSER_ID = 1
     WHERE C.CRMCUSTOMERNUMBER = L_CRMCUSTOMERNUMBER;

    UPDATE "&3".ADDRESS A
       SET A.STREET = CASE
                        WHEN L_MAILSTREET IS NOT NULL THEN
                         L_MAILSTREET
                        ELSE
                         A.STREET
                      END,
           A.STREET2 = CASE
                       WHEN L_MAILSTREET IS NOT NULL THEN
                          L_MAILSTREET2
                         ELSE
                          A.STREET2
                       END,
           A.ZIPCODE = CASE
                         WHEN L_MAILZIPCODE IS NOT NULL THEN
                          L_MAILZIPCODE
                         ELSE
                          A.ZIPCODE
                       END,
           A.CITY = CASE
                      WHEN L_MAILCITY IS NOT NULL THEN
                       L_MAILCITY
                      ELSE
                       A.CITY
                    END,
         A.TIMESTAMP = SYSDATE,
		     A.SYSTEMUSER_ID = 1
     WHERE A.ADDRESS_ID IN
           (SELECT DISTINCT C.MAILADDRESS_ID
              FROM "&3".CUSTOMER C
             WHERE C.CRMCUSTOMERNUMBER = L_CRMCUSTOMERNUMBER);

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      L_ERRM := SQLERRM;
      WRITE_SOLACE_ERROR(JSON_STRING, TOPIC, L_ERRM);
  END GOLDENED_CUSTOMER;

  PROCEDURE MERGE_CUSTOMER(JSON_STRING IN CLOB, TOPIC IN VARCHAR2) IS
    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 := SUBSTR(V_JSON_OBJECT.GET_STRING('ELISCUSTOMERID'), 1, 18);
    L_CUSTOMER_NUMBER   := TO_NUMBER(V_JSON_OBJECT.GET_STRING('LMSID'));

    SELECT ST.SOLACE_TOPIC_OUT
      INTO L_SOLACE_TOPIC_OUT
      FROM SOLACE_TOPIC ST
     WHERE UPPER(ST.SOLACE_TOPIC_IN) = UPPER(TOPIC);

    SELECT CT.CODE
      INTO L_COUNTRY
      FROM "&3".COUNTRY CT
     WHERE CT.COUNTRY_ID = (SELECT C.COUNTRY_ID FROM "&3".CUSTOMER C WHERE C.CUSTOMERNUMBER = L_CUSTOMER_NUMBER);

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

    MESSAGE_TEXT := '{ "elisCustomerId": "' || L_CRMCUSTOMERNUMBER || '"
                     , "lmsId": "' || L_CUSTOMER_NUMBER || '"                                
                     , "lmsSystemCode":"&4"}';
    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;
      WRITE_SOLACE_ERROR(JSON_STRING, TOPIC, L_ERRM);
  END MERGE_CUSTOMER;


  PROCEDURE 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 SOLACE_TOPIC ST
       WHERE UPPER(ST.SOLACE_TOPIC_IN) = UPPER(SOLACE_TOPIC_NAME);
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO MESSAGE_FROM_SOLACE_NOT_PROCESSED
          (MESSAGE, TOPIC, TIMESTAMP)
        VALUES
          (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
        COMMIT;
        RETURN;
    END;
    IF MESSAGE_CLOB IS NULL THEN
      L_PROCESS_NUMBER := -1;
    END IF;
    CASE L_PROCESS_NUMBER
      WHEN 1 THEN
        INSERT_ERP_CUSTOMER(MESSAGE_CLOB, SOLACE_TOPIC_NAME);
        INSERT INTO MESSAGE_FROM_SOLACE_PROCESSED
          (MESSAGE, TOPIC, TIMESTAMP)
        VALUES
          (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
        COMMIT;
      WHEN 2 THEN
        GOLDENED_CUSTOMER(MESSAGE_CLOB, SOLACE_TOPIC_NAME);
        INSERT INTO MESSAGE_FROM_SOLACE_PROCESSED
          (MESSAGE, TOPIC, TIMESTAMP)
        VALUES
          (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
        COMMIT;
      WHEN 3 THEN
        MERGE_CUSTOMER(MESSAGE_CLOB, SOLACE_TOPIC_NAME);
        INSERT INTO MESSAGE_FROM_SOLACE_PROCESSED
          (MESSAGE, TOPIC, TIMESTAMP)
        VALUES
          (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
        COMMIT;
      ELSE
        INSERT INTO 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;
      WRITE_SOLACE_ERROR(MESSAGE_CLOB, SOLACE_TOPIC_NAME, L_ERRM);
  END PROCESS_MESSAGE_FROM_SOLACE;

  PROCEDURE CUSTOMER_I_EVENT_CALLBACK(CONTEXT  RAW,
                                      REGINFO  SYS.AQ$_REG_INFO,
                                      DESCR    SYS.AQ$_DESCRIPTOR,
                                      PAYLOAD  RAW,
                                      PAYLOADL NUMBER) IS
    L_DEQUEUE_OPTIONS    DBMS_AQ.DEQUEUE_OPTIONS_T;
    L_MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
    L_MESSAGE_HANDLE     RAW(16);
    L_PAYLOAD            SYS.AQ$_JMS_TEXT_MESSAGE;
    MESSAGE_CLOB         CLOB;
    SOLACE_TOPIC         VARCHAR2(500);

  BEGIN

    L_DEQUEUE_OPTIONS.MSGID      := DESCR.MSG_ID;
    L_DEQUEUE_OPTIONS.WAIT       := DBMS_AQ.NO_WAIT;
    L_DEQUEUE_OPTIONS.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;

    DBMS_AQ.DEQUEUE(QUEUE_NAME         => DESCR.QUEUE_NAME,
                    DEQUEUE_OPTIONS    => L_DEQUEUE_OPTIONS,
                    MESSAGE_PROPERTIES => L_MESSAGE_PROPERTIES,
                    PAYLOAD            => L_PAYLOAD,
                    MSGID              => L_MESSAGE_HANDLE);

    SOLACE_TOPIC := L_PAYLOAD.GET_STRING_PROPERTY('solace_topic');
    DBMS_LOB.CREATETEMPORARY(MESSAGE_CLOB, TRUE);
    IF L_PAYLOAD.TEXT_LOB IS NOT NULL THEN
      DBMS_LOB.COPY(MESSAGE_CLOB,
                    L_PAYLOAD.TEXT_LOB,
                    DBMS_LOB.GETLENGTH(L_PAYLOAD.TEXT_LOB));
    ELSE
      DBMS_LOB.WRITEAPPEND(MESSAGE_CLOB,
                           LENGTH(L_PAYLOAD.TEXT_VC),
                           L_PAYLOAD.TEXT_VC);
    END IF;
    PROCESS_MESSAGE_FROM_SOLACE(MESSAGE_CLOB, SOLACE_TOPIC);
    DBMS_LOB.FREETEMPORARY(MESSAGE_CLOB);
  END CUSTOMER_I_EVENT_CALLBACK;

  PROCEDURE MESSAGE_ENQUEUE_TO_SOLACE(TRANSFER IN NUMBER) IS
    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 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": "&4"}';
    
      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;
      WRITE_SOLACE_ERROR('MESSAGE_ENQUEUE_TO_SOLACE', L_SOLACE_TOPIC_OUT, L_ERRM);
  END MESSAGE_ENQUEUE_TO_SOLACE;

end PACK_CONNSOLACE;
/
exit;
