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.6                                                                                                         *
  *  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'  *
  *            23.04.2025 CSOLN-12 Additional developments Solace - remove some fields from insert.                        *
  *            06.10.2025 ABSN-9813 Container tracking via Solace                                                          *
  *            15.01.2026 CSOLN-44 Flat linen traceability via Solace                                                      *
  *            30.01.2026 ABSN-9813 Container tracking via Solace                                                          *
  \************************************************************************************************************************/

  -- 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);

  procedure process_message_to_solace(fp_message_text in clob,
                                      l_processnumber in number,
                                      l_country       in varchar2,
                                      l_plant         in varchar2,
                                      l_containerid   in varchar2);
  
  procedure process_scan_to_solace;
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
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      (LOG_MESSAGE, LOG_TOPIC, LOG_ERROR,  SYSDATE);
    COMMIT;
  END WRITE_SOLACE_ERROR;

  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,
     ZIPCODE,
     CITY,
     TIMESTAMP)
     VALUES
     (L_TRANSFER_ID,
       L_TRANSFER_STATUS,
       L_CUSTOMERNUMBER,
       '1',
       NULL,
       '-',
       L_VISITADDRESS,
       L_VISITADDRESS2,
       L_VISITZIPCODE,
       L_VISITCITY,
       SYSDATE);

    INSERT INTO "&3".ERP_I_COSTCENTER
       (TRANSFER_ID,
       TRANSFER_STATUS,
       CUSTOMERNUMBER,
       COSTCENTERCODE,
       COSTCENTERCODE_OLD,
       DESCRIPTION,
       EXPORTEDI,
       INVOICEADDRESS,
       INVOICEADDRESS2,
       INVOICEZIPCODE,
       INVOICECITY,
       TIMESTAMP)
     VALUES
     (L_TRANSFER_ID,
       L_TRANSFER_STATUS,
       L_CUSTOMERNUMBER,
       '1',
       NULL,
       '-',
       0,
       L_VISITADDRESS,
       L_VISITADDRESS2,
       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
      ROLLBACK;      
      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
      ROLLBACK;
      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
      ROLLBACK;
      L_ERRM := SQLERRM;
      WRITE_SOLACE_ERROR(JSON_STRING, TOPIC, L_ERRM);
  END MERGE_CUSTOMER;

  procedure container_insertupdate(json_string in clob, topic in varchar2) is
    v_json_object       json_object_t;
    v_keys              json_key_list;
    lv_message          varchar2(500);
    lv_rec_container    "&3".container%rowtype;
    lv_rec_seccontainer "&3".secondarycontainer%rowtype;
    lv_update           varchar2(1) := 'Y';
  
    function get_businessunit(fp_businessunitcode in varchar2) return number is
      lv_businessunitid number;
    begin
      --get_businessunit
      lv_message := 'businessunit ' || fp_businessunitcode;
      select bu.businessunit_id
      into   lv_businessunitid
      from   "&3".businessunit bu
      where  bu.code = upper(fp_businessunitcode);
      return lv_businessunitid;
    end get_businessunit;
  
    function get_containertype(fp_containertype in varchar2) return number is
      lv_containertypeid number;
    begin
      begin
        --get_containertype
        lv_message := 'get containertype ' || fp_containertype;
        select ct.containertype_id
        into   lv_containertypeid
        from   "&3".containertype ct
        where  ct.code = trim(upper(fp_containertype));
      exception
        when no_data_found then
          begin
            insert into "&3".containertype
              (containertype_id, code, defaulttaraweight, systemuser_id, timestamp, active,
               disinfectbeforedelivery)
            values
              ("&3".seq_containertype.nextval, nvl(fp_containertype, '-'),
               nvl(lv_rec_container.tareweight, 0), 1, sysdate, 'Y', 'N')
            returning containertype_id into lv_containertypeid;
            insert into "&3".containertype_desc
              (containertype_id, language_id, description, systemuser_id, timestamp)
              select lv_containertypeid, l.language_id, nvl(fp_containertype, '-'), 1, sysdate
              from   "&3".languages l;
          end;
      end;
      return lv_containertypeid;
    end get_containertype;
  
    procedure add_secondaryid is
    begin
      --add secondary container idcode
      if lv_rec_seccontainer.secondaryid is not null then
        begin
          lv_message := 'add secondaryid';
          update "&3".secondarycontainer sc
          set    sc.secondaryid = lv_rec_seccontainer.secondaryid
          where  sc.container_id = lv_rec_container.container_id;
          if sql%rowcount = 0 then
            begin
              insert into "&3".secondarycontainer
                (container_id, secondaryid, systemuser_id, timestamp)
              values
                (lv_rec_container.container_id, lv_rec_seccontainer.secondaryid, 1, sysdate);
            end;
          end if;
        end;
      end if;
    end add_secondaryid;
  
    procedure init_container is
      lv_query varchar2(1000);
      type cur_typ is ref cursor;
      rcur_typ cur_typ;
    begin
      --init fill the record with all default values
      lv_message := 'init_container';
      for rec in (select data_default, data_type
                  from   all_tab_columns t
                  where  table_name = 'CONTAINER'
                   and    t.owner = '&3'
                  order  by column_id) loop
        if rec.data_default is null then
          lv_query := lv_query || ', null';
        else
          lv_query := lv_query || ', ' || rec.data_default;
        end if;
      end loop;
      lv_query := 'SELECT' || substr(lv_query, 2) || ' FROM DUAL';
      open rcur_typ for lv_query;
      fetch rcur_typ
        into lv_rec_container;
      close rcur_typ;
      --get id
      select "&3".seq_container.nextval into lv_rec_container.container_id from dual;
      --set default that CANNOT and MAY NOT be updated or updated differently
      lv_rec_container.primaryid := substr(v_json_object.get_string('CODE'), 1, 24);
      select st.stay_id
      into   lv_rec_container.stay_id
      from   "&3".stay st
      where  st.staytype = 2 --Container
      and    st.staynr = 50;
      lv_rec_container.containertype_id := get_containertype(substr(json_value(json_string,
                                                                               '$.lmsCategory.lmsProductCode'),
                                                                    1, 6));
      --get businessunit
      lv_rec_container.ownerbusinessunit_id := get_businessunit(substr(json_value(json_string,
                                                                                  '$.plantCreatedIn.lmscode'),
                                                                       1, 6));
      ---extra missing defaults
      lv_rec_container.numberofshipments      := 0;
      lv_rec_container.numberofdisinfections  := 0;
      lv_rec_container.numberofdaysatcustprev := 0;
      lv_rec_container.dateactive             := nvl(to_date(substr(v_json_object.get_string('COMMISSIONDATE'),
                                                                    1, 10), 'YYYY-MM-DD'),
                                                     trunc(sysdate));
      lv_rec_container.dateinactive           := to_date('2099-12-31', 'YYYY-MM-DD');
      lv_rec_container.usedbyotherbu          := 'N';
    end init_container;
  
    procedure create_containerscan is
    begin
      --create_containerscan
      lv_message := 'create_containerscan';
      insert into "&3".containerscan
        (containerscan_id, scantimestamp, businessunit_id, primaryid, secondaryid,
         distributionpoint_link_id, containertype_id, stay_id, prevstay_id, transactiontype_id,
         scanstation, customerowned, year, week, dayno, container_id, tareweight, systemuser_id,
         timestamp)
      values
        ("&3".seq_containerscan.nextval, sysdate, lv_rec_container.ownerbusinessunit_id,
         lv_rec_container.primaryid, lv_rec_seccontainer.secondaryid,
         lv_rec_container.distributionpoint_id, lv_rec_container.containertype_id,
         lv_rec_container.stay_id, lv_rec_container.stay_id,
         case when lv_update = 'Y' then 550 else 580 end, 'Solace',
         case when lv_rec_container.ownercustomer_id is not null then 'Y' else 'N' end,
         "&3".getyearofweek(sysdate), "&3".getisoweek(sysdate),
         "&3".getabsdaynumber(sysdate), lv_rec_container.container_id,
         lv_rec_container.tareweight, 1, sysdate);
    end create_containerscan;
  
  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;
    --container_insertupdate
    lv_rec_container.primaryid := substr(v_json_object.get_string('CODE'), 1, 24);
    begin
      --check if container exists
      select *
      into   lv_rec_container
      from   "&3".container c
      where  c.primaryid = lv_rec_container.primaryid;
    exception
      when no_data_found then
        begin
          lv_update := 'N';
          init_container;
        end;
    end;
    lv_rec_seccontainer.secondaryid := substr(v_json_object.get_string('EPC'), 1, 24);
    lv_rec_container.tareweight     := substr(v_json_object.get_string('WEIGHT'), 1, 8);
    lv_rec_container.systemuser_id  := 1;
    lv_rec_container.timestamp      := sysdate;
    if upper(trim(substr(v_json_object.get_string('STATUS'), 1, 8))) = 'INACTIVE' then
      lv_rec_container.dateinactive := trunc(sysdate);
    else
      lv_rec_container.dateinactive := to_date('2099-12-31', 'YYYY-MM-DD');
    end if;
    if nvl(lv_update, 'Y') = 'Y' then
      begin
        update "&3".container ct
        set    row = lv_rec_container
        where  ct.container_id = lv_rec_container.container_id;
      end;
    else
      begin
        insert into "&3".container values lv_rec_container;
      end;
    end if;
    --add secondary container idcode
    add_secondaryid;
    --create scan
    create_containerscan;
    --write processed
    insert into message_from_solace_processed
      (message, topic, timestamp)
    values
      (json_string, topic, sysdate);
    commit;
  exception
    when no_data_found then
      rollback;
      write_solace_error(json_string, topic, 'No data found ' || lv_message);
    when others then
      rollback;
      write_solace_error(json_string, topic, lv_message || ' ' || sqlerrm);
  end container_insertupdate;
  
  procedure container_move(json_string in clob, topic in varchar2) is
    v_json_object    json_object_t;
    v_keys           json_key_list;
    lv_message       varchar2(500);
    lv_rec_container "&3".container%rowtype;
    lv_userexception exception;
  
    function get_businessunit(fp_businessunitcode in varchar2) return number is
      lv_businessunitid number;
    begin
      --get_businessunit
      lv_message := 'businessunit ' || fp_businessunitcode;
      select bu.businessunit_id
      into   lv_businessunitid
      from   "&3".businessunit bu
      where  bu.code = upper(fp_businessunitcode);
      return lv_businessunitid;
    end get_businessunit;
  
    procedure set_container_inactive is
    begin
      --set_container_inactive
      lv_message := 'set container inactive';
      update "&3".container c
      set    c.dateinactive = lv_rec_container.dateinactive, c.systemuser_id = 1,
             c.timestamp = sysdate
      where  c.container_id = lv_rec_container.container_id;
    end set_container_inactive;
  
    procedure create_containerscan is
    begin
      --create_containerscan
      lv_message := 'create_containerscan';
      insert into "&3".containerscan
        (containerscan_id, scantimestamp, businessunit_id, primaryid, secondaryid,
         distributionpoint_link_id, containertype_id, stay_id, prevstay_id, transactiontype_id,
         scanstation, customerowned, year, week, dayno, container_id, tareweight, systemuser_id,
         timestamp)
      values
        ("&3".seq_containerscan.nextval, sysdate, lv_rec_container.ownerbusinessunit_id,
         lv_rec_container.primaryid, null, lv_rec_container.distributionpoint_id,
         lv_rec_container.containertype_id, lv_rec_container.stay_id, lv_rec_container.stay_id, 560,
         'Solace', case when lv_rec_container.ownercustomer_id is not null then 'Y' else 'N' end,
         "&3".getyearofweek(sysdate), "&3".getisoweek(sysdate),
         "&3".getabsdaynumber(sysdate), lv_rec_container.container_id,
         lv_rec_container.tareweight, 1, sysdate);
    end create_containerscan;
  
    procedure stop_rent is
    begin
      --stop_rent
      lv_message := 'stop rent';
      update "&3".containerrent cr
      set    cr.returndate = lv_rec_container.dateinactive, cr.systemuser_id = 1,
             cr.timestamp = sysdate
      where  cr.container_id = lv_rec_container.container_id
      and    cr.returndate = to_date('31-12-2099', 'DD-MM-YYYY');
    end stop_rent;
  
    procedure cancel_charges is
    begin
      --cancel_charges
      lv_message := 'cancel charges';
      delete from "&3".containercharge cc
      where  cc.container_id = lv_rec_container.container_id
      and    cc.chargecreateddate > lv_rec_container.dateinactive
      and    cc.invoiceheader_id is null;
    end cancel_charges;
  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;
    --container_move
    lv_rec_container.primaryid := substr(v_json_object.get_string('CAGECODE'), 1, 24);
    begin
      --check if container exists
      select *
      into   lv_rec_container
      from   "&3".container c
      where  c.primaryid = lv_rec_container.primaryid;
    exception
      when no_data_found then
        begin
          lv_message := 'container ' || lv_rec_container.primaryid || ' does not exists';
          raise lv_userexception;
        end;
    end;
    --the inactive date is the scantimestamp
    lv_rec_container.dateinactive := to_date(substr(v_json_object.get_string('SCANTIMESTAMP'), 1, 10),
                                             'YYYY-MM-DD');
    --check BU
    if lv_rec_container.ownerbusinessunit_id is null then
      begin
        --get businessunit
        lv_rec_container.ownerbusinessunit_id := get_businessunit(substr(json_value(json_string,
                                                                                    '$.currentPlant.lmscode'),
                                                                         1, 6));
      end;
    end if;
    if lv_rec_container.dateinactive is null then
      begin
        lv_message := 'No scantimestamp, processing stopped';
        raise lv_userexception;
      end;
    else
      begin
        --set_container_inactive
        set_container_inactive;
        --create_containerscan
        create_containerscan;
        --stop rent
        stop_rent;
        --cancel charges
        cancel_charges;
      end;
    end if;
    --write processed
    insert into message_from_solace_processed
      (message, topic, timestamp)
    values
      (json_string, topic, sysdate);
    commit;
  exception
    when lv_userexception then
      rollback;
      write_solace_error(json_string, topic, lv_message);
    when no_data_found then
      rollback;
      write_solace_error(json_string, topic, 'No data found ' || lv_message);
    when others then
      rollback;
      write_solace_error(json_string, topic, lv_message || ' ' || sqlerrm);
  end container_move;

  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
        ROLLBACK;
        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);
      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);
      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);
      ---4 New container insert / Existing container update
      when 4 then
        container_insertupdate(message_clob, solace_topic_name);
      ---5 Moved container
      when 5 then
        container_move(message_clob, solace_topic_name);
      ELSE
        INSERT INTO MESSAGE_FROM_SOLACE_NOT_PROCESSED
          (MESSAGE, TOPIC, TIMESTAMP)
        VALUES
          (MESSAGE_CLOB, SOLACE_TOPIC_NAME, SYSDATE);
    END CASE;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      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);

     insert into message_to_solace_processed(message,
                                             topic,
                                             timestamp)
      values (MESSAGE_TEXT, L_SOLACE_TOPIC_OUT || '/' || L_COUNTRY || '/' ||L_PLANT || '/' || L_CUSTOMER_CRM, sysdate);
    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;

    procedure process_message_to_solace(fp_message_text in clob,
                                      l_processnumber in number,
                                      l_country       in varchar2,
                                      l_plant         in varchar2,
                                      l_containerid   in varchar2) is
    le_user_exception  exception;
    lv_message         varchar2(4000);
    enqueue_options    dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle     raw(16);
    message            sys.aq$_jms_text_message;
    l_solace_topic_out varchar2(100);
  begin
    --check if number exists
    lv_message := 'Get solace topic out processnumber ' || nvl(to_char(l_processnumber),'NULL');
    select st.solace_topic_out
    into   l_solace_topic_out
    from   solace_topic st
    where  st.processing_number = l_processnumber;
    --build message
    message := sys.aq$_jms_text_message.construct;
    message.set_text(fp_message_text);
    message.set_string_property('solace_topic_out',
                                l_solace_topic_out || '/' || l_country || '/' || l_plant || '/' ||
                                 l_containerid);
    --queue message
    dbms_aq.enqueue(queue_name => 'SEND_TO_SOLACE_QUEUE', enqueue_options => enqueue_options,
                    message_properties => message_properties, payload => message,
                    msgid => message_handle);
    insert into message_to_solace_processed
      (message, topic, timestamp)
    values
      (fp_message_text,
       l_solace_topic_out || '/' || l_country || '/' || l_plant || '/' || l_containerid, sysdate);
  exception
    when le_user_exception then
      begin
        write_solace_error('MESSAGE_ENQUEUE_TO_SOLACE', l_solace_topic_out, lv_message);
      end;
    when others then
      begin
        write_solace_error('MESSAGE_ENQUEUE_TO_SOLACE', l_solace_topic_out,
                           lv_message || ' ' || sqlerrm);
      end;
  end process_message_to_solace;

 procedure process_scan_to_solace is
    le_user_exception  exception;
    lv_processnumber   number;
    lv_message         varchar2(4000);
    l_country          varchar2(3);
    l_plant            varchar2(6);
    lv_pnnumber        "&3".packingnote.notenumber%type;
    lv_deliverydate    "&3".packingnote.deliverydate%type;
    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;
    lv_glx_abs         varchar2(3);
    l_solace_topic_out varchar2(100);
    lv_tom              "&3".PRODUCT.TYPEOFMERCHANDISE%TYPE;
    lv_transactiontype_id "&3".SCAN.TRANSACTIONTYPE_ID%TYPE;
    lv_processing_number number;
    c_scans  "&3".solace_scans%rowtype;
    cursor c_solace_scans is (select * from "&3".solace_scans);

  procedure tagcreated(fp_scan_id "&3".scan.scan_id%type) is
   begin
      --Process 10 - Tag created
      lv_message := 'Process 10 - Tag created';
      select c.code countrycode, pl.code plantcode,
             json_object( 'scanType' value 'createdOrUpdated',
                          'scans' value json_object(
                                        'scan' value
                                        json_object('workstationSource' value sc.scanstation,
                                                     'scanTimestamp' value sc.timestamp,
                                                     'deviceType' value 'UHF',
                                                     'logisticCenterId' value c.code ||b.code),
                                        'tags' value
                                        json_object('epc' value ui.primaryid),
                                        
                                        'createOrUpdate' value
                                        json_object('activationDate' value uif.firstissuedate,
                                                    'status' value 'active',
                                                    'isCirculatingPool' value (case ui.poolitem
                                                                                    when 1 then 'true'
                                                                                    else 'false'
                                                                                    end ),
                                                    'isNew' value 'true',
                                                    'washCounter' value 0,
                                                    'article' value json_object('code' value ( case lv_glx_abs
                                                                                               when 'GLX' then 'GLX_' || b.code|| '_' ||p.legacyproductcode
                                                                                               when 'ABS' then p.code
                                                                                                 end),
                                                                                'size' value sz.code,
                                                                                'isLocal' value (case
                                                                                                 WHEN lv_glx_abs = 'ABS' OR NVL(p.code, '*') = NVL(p.legacyproductcode, '*')
                                                                                                    THEN 'false'
                                                                                                 ELSE 'true'
                                                                                                  END ) )),
                                          'plant' value
                                          json_object('elisSiteId' value '',
                                                      'lmsCode' value ( case lv_glx_abs
                                                                        when 'GLX' then c.code||b.code 
                                                                        when 'ABS' then pl.code
                                                                          end),
                                                      'CountryCode' value c.code,
                                                      'systemLMS' value lv_glx_abs ))
                                                                                                  returning clob) message_text
         into   l_country, l_plant,  message_text
         from "&3".scan sc, "&3".uniqueitem UI,
              "&3".uniqueitemfixedinfo uif,"&3".product p,
              "&3".sizedefinition sz, "&3".businessunit b, "&3".plant pl, "&3".country c

         where sc.uniqueitem_link_id = ui.uniqueitem_id
         and ui.uniqueitem_id = uif.uniqueitem_id
         and ui.product_id = p.product_id
         and ui.sizedefinition_id = sz.sizedefinition_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
         and sc.scan_id = fp_scan_id;
end tagcreated;

  procedure tagupdated(fp_scan_id "&3".scan.scan_id%type, fp_transactiontype_id "&3".scan.TRANSACTIONTYPE_ID%type) is
  begin
      --Process 11 - Tag updated
      lv_message := 'Process 11 - Tag updated';
      if (fp_transactiontype_id = 81)
        then
          begin
              select c.code countrycode, pl.code plantcode,
             json_object('currentPlant' value json_object('elisSiteId' value '',
                                                     'lmsCode' value pl.code,
                                                     'CountryCode' value c.code,
                                                     'systemLMS' value 'ABS'),
                          'tags' value json_object ('epc' value ui.primaryid,
                                                    'tag' value json_object  ('activationDate' value uif.firstissuedate,
                                                                              'status' value 'active',
                                                                              'isInvoiceLost' value NULL,
                                                                              'isCirculatingPool' value (case ui.poolitem
                                                                                                         when 1 then 'true'
                                                                                                         else 'false'
                                                                                                         end ),
                                                                              'isNew' value NULL,
                                                                              'washCounter' value ui.washestotal,
                                                                              'updatedAt' value sc.timestamp,
                                                                              'lastScanAt' value sc.scantimestamp,

                                                                              'article' value json_object( 'code' value p.code, --tbc
                                                                                                           'size' value sz.code,
                                                                                                           'isLocal' value  'false' ) )  returning clob)) message_text
         into   l_country, l_plant,  message_text
         from "&3".scan sc, "&3".uniqueitem UI,
              "&3".uniqueitemfixedinfo uif,"&3".product p,
              "&3".sizedefinition sz, "&3".businessunit b, "&3".plant pl, "&3".country c
         where sc.uniqueitem_link_id = ui.uniqueitem_id
         and ui.uniqueitem_id = uif.uniqueitem_id
         and ui.product_id = p.product_id
         and ui.sizedefinition_id = sz.sizedefinition_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
        and sc.scan_id = fp_scan_id;
        end;
       else
          begin
          select c.code countrycode, pl.code plantcode,
             json_object('currentPlant' value json_object('elisSiteId' value '',
                                                     'lmsCode' value pl.code,
                                                     'CountryCode' value c.code,
                                                     'systemLMS' value 'ABS'),
                          'tags' value json_object ('epc' value r.primaryid,
                                                    'tag' value json_object  ('activationDate' value r.firstissuedate,
                                                                              'status' value 'inactive',
                                                                              'isInvoiceLost' value NULL,
                                                                              'isCirculatingPool' value (case r.poolitem
                                                                                                         when 1 then 'true'
                                                                                                         else 'false'
                                                                                                         end ),
                                                                              'isNew' value NULL,
                                                                              'washCounter' value r.washestotal,
                                                                              'updatedAt' value sc.timestamp,
                                                                              'lastScanAt' value sc.scantimestamp,--?

                                                                              'article' value json_object( 'code' value p.code, --tbc
                                                                                                           'size' value sz.code,
                                                                                                           'isLocal' value  'false' ) ), 
                                                    'discarded' value json_object('Code' value rg.code, 
                                                                                  'Cause' value rd.description)  returning clob)) message_text
         into   l_country, l_plant,  message_text
         from "&3".scan sc, "&3".uniqueitem_link ui,"&3".raggeditem r, "&3".ragreason rg,
              "&3".product p, "&3".sizedefinition sz,
              "&3".ragreason_desc rd, "&3".languages l,
              "&3".businessunit b, "&3".plant pl, "&3".country c
         where sc.uniqueitem_link_id = ui.uniqueitem_link_id
         and ui.uniqueitem_link_id = r.raggeditem_id
         and r.product_link_id = p.product_id
         and r.sizedefinition_link_id = sz.sizedefinition_id
         and r.ragreason_id = rg.ragreason_id
         and rg.ragreason_id = rd.ragreason_id
         and rd.language_id = l.language_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
         and l.language_id = pl.language_id
         and sc.scan_id = fp_scan_id;
        end;
         end if;
        
  end tagupdated;

procedure tagpacked(fp_scan_id "&3".scan.scan_id%type) is
  begin
    lv_message := 'Process 12 - get pn number';
    select min(pn.notenumber)
      into lv_pnnumber
      from "&3".scan sc,"&3".uniqueitem ui,
           "&3".packingnote pn,  "&3".packingnoteline pkl
     where sc.uniqueitem_link_id = ui.uniqueitem_id
       and pn.distributionpoint_id = SC.DISTRIBUTIONPOINT_LINK_ID
       and  pkl.product_id = ui.product_id
       and pkl.sizedefinition_id = ui.sizedefinition_id
       and pkl.finishingmethod_id = ui.finishingmethod_id
       and pkl.tobedeliveredamount >0
       and pn.deliverydate >= trunc(sc.scantimestamp) 
       and pn.status_id in (select s.status_id
                              from "&3".status s
                             where s.code in (20, 25, 30)
                               and s.statustype = 'PN'); --cant be cancelled or new

       lv_message := 'Process 12 get delivery date';
       select pn.deliverydate
         into lv_deliverydate
         from "&3".packingnote pn
        where pn.notenumber = lv_pnnumber;

   -- Process 12 - Tag packed
   lv_message := 'Process 12 - Tag packed';
          select c.code countrycode, pl.code plantcode,
      json_object ('scanType' value 'packed',
                   'scans' value  json_object ('scan' value json_object(
                                                       'workstationSource' value sc.scanstation,
                                                       'scanTimestamp' value sc.scantimestamp,
                                                       'deviceType' value 'UHF',
                                                       'logisticCenterId' value c.code|| b.code),
                                              'tags' value json_object ('epc' value ui.primaryid,
                                                                       'washCounter' value ui.washestotal),
                                              'customer' value json_object ('elisCustomerId' value cust.crmcustomernumber,
                                                                            'customerId' value (case lv_glx_abs
                                                                                                  when 'GLX' then 'GLX'|| '_' || b.code || '_' ||cust.legacycustomernumber
                                                                                                  when 'ABS' then to_char(cust.customernumber)
                                                                                               end),
                                                                            'pointofDeliveryId' value (case lv_glx_abs
                                                                                                        when 'GLX' then 'GLX'|| '_' || b.businessunit_id || '_' ||'GLX Department Number'
                                                                                                        when 'ABS' then to_char(dep.departmentnumber)
                                                                                                      end),
                                                                            'pointOfServiceId' value (case lv_glx_abs
                                                                                                        when 'GLX' then ''
                                                                                                        when 'ABS' then to_char(distp.distributionpointnumber)
                                                                                                     end)),
                                             'packing' value json_object ('deliveryDate' value lv_deliverydate,
                                                                          'routeId' value r.routenumber,
                                                                          'deliveryNoteId' value (case lv_glx_abs
                                                                                           when 'GLX' then ''
                                                                                            when 'ABS' then to_char(lv_pnnumber)
                                                                                            end),
                                                                          'packingLabelIds' value (case lv_glx_abs
                                                                                            when 'GLX' then to_char(lv_pnnumber)
                                                                                            when 'ABS' then  ''
                                                                                            end)),
                                           'plant' value json_object ('elisSiteId' value '',
                                                                      'lmsCode' value (case lv_glx_abs
                                                                                         when 'GLX' then c.code || b.code
                                                                                         when 'ABS' then pl.code
                                                                                       end),
                                                                      'countryCode' value c.code,
                                                                      'systemLMS' value lv_glx_abs)) returning clob) message_text
   into l_country, l_plant, message_text
   from "&3".scan sc, "&3".uniqueitem ui,
        "&3".businessunit b, "&3".plant pl, "&3".country c,
        "&3".customer cust, "&3".department dep, "&3".distributionpoint distp,
        "&3".packingnote pn, "&3".route r, "&3".packingnoteline pkl
   where sc.uniqueitem_link_id = ui.uniqueitem_id
     and sc.businessunit_id = b.businessunit_id
     and b.plant_id = pl.plant_id
     and pl.country_id = c.country_id
     and cust.customer_id = sc.customer_link_id
     and cust.customer_id = dep.customer_id
     and distp.department_id = dep.department_id
     and pn.distributionpoint_id = distp.distributionpoint_id
     and pn.route_id = r.route_id
     and pkl.product_id = ui.product_id
     and pkl.sizedefinition_id = ui.sizedefinition_id
     and pkl.finishingmethod_id = ui.finishingmethod_id
     and pn.deliverydate >= trunc(sysdate)
     and pn.status_id in (20, 25, 30) --cant be cancelled or new
     and sc.scan_id = fp_scan_id;
  end tagpacked;

   procedure tagreturned(fp_scan_id "&3".scan.scan_id%type) is
  begin
   --Process 13 - Tag returned
      lv_message := 'Process 13 - Tag returned';
      select c.code countrycode, pl.code plantcode,
             json_object('scanType' value 'returned',
                         'scans' value json_object(
                                       'scan' value json_object( 'workstationSource' value sc.scanstation,
                                                                 'scanTimestamp' value sc.timestamp,
                                                                 'deviceType' value 'UHF',
                                                                 'logisticCenterId' value c.country_id||b.code),
                          'tags' value
                                 json_object('epc' value ui.primaryid),

                          'plant' value
                                  json_object('elisSiteId' value '',
                                               'lmsCode' value ( case lv_glx_abs
                                                          when 'GLX' then c.code||b.code
                                                          when 'ABS' then pl.code
                                                            end),
                                                'countryCode' value c.code,
                                                'systemLMS' value lv_glx_abs ))
                                                                                    returning clob) message_text
         into   l_country, l_plant,  message_text
         from "&3".scan sc, "&3".uniqueitem UI,
              "&3".businessunit b, "&3".plant pl, "&3".country c

         where sc.uniqueitem_link_id = ui.uniqueitem_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
         and sc.scan_id = fp_scan_id;
  end tagreturned;

  procedure taginventoried(fp_scan_id "&3".scan.scan_id%type) is
  begin
     --Process 14 - Tag inventoried 
   lv_message := 'Process 14 - Tag inventoried';
   select c.code countrycode, pl.code plantcode,
     json_object('scanType' value 'inventory',
                 'scans' value json_object (
                                            'scan' value  json_object(
                                                                       'workstationSource' value sc.scanstation,
                                                                       'scanTimestamp' value sc.timestamp,
                                                                       'deviceType' value 'UHF', 
                                                                       'user' value su.username),
                                            'tags' value json_object('epc' value ui.primaryid,
                                                                     'washCounter' value 0),
                                            'customer' value json_object('elisCustomerId' value cust.crmcustomernumber,
                                                                         'customerId' value (case lv_glx_abs
                                                                                      when 'GLX' then 'GLX'||'_'||b.code||'_'||cust.legacycustomernumber
                                                                                      when 'ABS' then to_char(cust.customernumber)
                                                                                      end),
                                                                         'pointofDeliveryId' value (case lv_glx_abs
                                                                                      when 'GLX' then 'GLX_'||'Business unit ID'||'_GLX Department Number'
                                                                                      when 'ABS' then to_char(dep.departmentnumber)
                                                                                      end),
                                                                         'pointOfServiceId' value (case lv_glx_abs
                                                                                       when 'GLX' then ''
                                                                                       when 'ABS' then to_char(distp.distributionpointnumber)
                                                                                       end)),
                                           'inventory' value json_object('type' value (case lv_glx_abs
                                                                                when 'GLX' then 'INVOICE'
                                                                                when 'ABS' then 'LOSS for linen assistant'
                                                                                end),
                                                                        'pointOfConsumption' value ''),
                                           'plant' value json_object ('elisSiteId' value '',
                                                                      'lmsCode' value (case lv_glx_abs
                                                                                when 'GLX' then c.code|| b.code
                                                                                when 'ABS' then pl.code
                                                                                end),
                                                                       'countryCode' value c.code,
                                                                       'systemLMS' value lv_glx_abs)) returning clob) message_text
    into l_country, l_plant,  message_text
    from "&3".scan sc, "&3".uniqueitem ui,
         "&3".businessunit b, "&3".plant pl, "&3".country c, "&3".systemuser su,
         "&3".customer cust, "&3".department dep, "&3".distributionpoint distp
         where sc.uniqueitem_link_id = ui.uniqueitem_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
         and cust.customer_id = sc.customer_link_id
         and cust.customer_id = dep.customer_id
         and distp.department_id = dep.department_id
         and su.systemuser_id = sc.systemuser_id
         and sc.scan_id = fp_scan_id; 
  end taginventoried;

   procedure tagdiscarded(fp_scan_id "&3".scan.scan_id%type) is
   begin
   --Process 15 - Tag discarded
      lv_message := 'Process 15 - Tag discarded';
                        select c.code countrycode, pl.code plantcode,
             json_object(
                          'scanType': 'discarded',            
                          'scans' value json_object( 
                                        'scan' value json_object
                                               ('workstationSource' value sc.scanstation,
                                                'scanTimestamp' value sc.timestamp,
                                                'deviceType' value 'UHF',
                                                'logisticCenterId' value c.country_id||b.code),
                                         'tags' value
                                                json_object('epc' value r.primaryid),

                                          'plant' value
                                                   json_object('elisSiteId' value '',
                                                                'lmsCode' value ( case  lv_glx_abs
                                                                                when 'GLX' then c.code|| b.code 
                                                                                when 'ABS' then pl.code
                                                                                end),
                                                                 'countryCode' value c.code,
                                                                 'systemLMS' value lv_glx_abs),
                          'discarded' value
                                 json_object('discardingDate' value r.ragtimestamp,
                                      'code' value rg.code,
                                      'cause' value rd.description,
                                      'status' value 'inactive'))
                                                                                    returning clob) message_text
         into   l_country, l_plant,  message_text
         from "&3".scan sc, "&3".uniqueitem_link ui,"&3".raggeditem r, "&3".ragreason rg,
              "&3".ragreason_desc rd, "&3".languages l,
              "&3".businessunit b, "&3".plant pl, "&3".country c
         where sc.uniqueitem_link_id = ui.uniqueitem_link_id
         and ui.uniqueitem_link_id = r.raggeditem_id
         and r.ragreason_id = rg.ragreason_id
         and rg.ragreason_id = rd.ragreason_id
         and rd.language_id = l.language_id
         and sc.businessunit_id = b.businessunit_id
         and b.plant_id = pl.plant_id
         and pl.country_id = c.country_id
         and l.language_id = pl.language_id
         and sc.scan_id = fp_scan_id;
  end tagdiscarded;

   begin
   lv_glx_abs := NVL(NULLIF('&5', ''), 'ABS');
     begin
      -- while data exists loop
       open c_solace_scans;
       loop
         fetch c_solace_scans into c_scans;
         exit when c_solace_scans%notfound;
begin
 lv_message := 'Get lv_tom, lv_transactiontype_id and lv_processnumber for scan_id = ' || c_scans.scan_id;
 select p.typeofmerchandise, s.transactiontype_id,
                case
                  when s.transactiontype_id in ( 20, 22, 21, 23, 27) then 10 --tag created
                  when s.transactiontype_id in (43,81) then 11 --tag updated
                  when s.transactiontype_id in (2) then 12 --tag packed
                  when s.transactiontype_id in (1) then 13 --tag returned
                  when s.transactiontype_id in (65, 76) then 14 --tag inventoried
                  when s.transactiontype_id in (42) then 15 --tag discarder
                  end
           into lv_tom, lv_transactiontype_id, lv_processnumber
           from "&3".product p
           join "&3".uniqueitem ui
             on p.product_id = ui.product_id
           join "&3".scan s
             on ui.uniqueitem_id = s.uniqueitem_link_id
          where s.scan_id = c_scans.scan_id;


         if (lv_tom = 2) then
           delete from "&3".solace_scans ss where ss.scan_id = c_scans.scan_id;
         else


     --check if number exists
     lv_message := 'Get solace topic out ';
     select st.solace_topic_out
       into l_solace_topic_out
       from solace_topic st
      where st.processing_number = lv_processnumber;
     --process data
     case lv_processnumber
       when 10 then
         begin
           --process 10 - tag created
           tagcreated(c_scans.scan_id);
         end;
        when 11 then
         begin
           --process 11 - tag updated
           tagupdated(c_scans.scan_id, lv_transactiontype_id);
         end;
        when 12 then
         begin
           --process 12 - tag packed
           tagpacked(c_scans.scan_id);
         end;
        when 13 then
         begin
           --process 13 - tag returned
           tagreturned(c_scans.scan_id);
         end;
        when 14 then
         begin
           --process 14 - tag inventoried
           taginventoried(c_scans.scan_id);
         end;
        when 15 then
         begin
           --process 15 - tag discarded
           tagdiscarded(c_scans.scan_id);
         end;
       else
         begin
           --Unknown or not (yet) supported process number
           lv_message := 'Process number ' || nvl(to_char(lv_processnumber), 'NULL') ||
                         ' does not exists as an solace_topic (yet)';
           raise le_user_exception;
         end;
    end case;
    --build messagef
    message := sys.aq$_jms_text_message.construct;
    message.set_text(replace(message_text, ':null', ':""'));
    message.set_string_property('solace_topic_out',
                                l_solace_topic_out || '/' || l_country || '/' || l_plant);
    --queue message
    dbms_aq.enqueue(queue_name => 'SEND_TO_SOLACE_QUEUE', enqueue_options => enqueue_options,
                    message_properties => message_properties, payload => message,
                    msgid => message_handle);
     insert into message_to_solace_processed(message,
                                             topic,
                                             timestamp)
      values (replace(message_text, ':null', ':""'), l_solace_topic_out || '/' || l_country || '/' || l_plant, sysdate);
      delete from "&3".solace_scans s where s.scan_id = c_scans.scan_id;
  end if;
  commit;

   
   exception
    when le_user_exception then
      begin
        write_solace_error('process_scan_to_solace', l_solace_topic_out, 'Scan_id: ' || c_scans.scan_id || ': ' ||lv_message || ' ' || sqlerrm);
        delete from "&3".solace_scans s where s.scan_id = c_scans.scan_id;
        commit;
      end;
    when others then
      begin
        write_solace_error('process_scan_to_solace', l_solace_topic_out,  'Scan_id: ' || c_scans.scan_id || ': ' ||lv_message || ' ' || sqlerrm);
        delete from "&3".solace_scans s where s.scan_id = c_scans.scan_id;
        commit;
      end;
    end;   
  end loop;

   close c_solace_scans; --close cursor
 end;
   
  exception

    when others then
      begin
        write_solace_error('process_scan_to_solace', l_solace_topic_out, lv_message || ' ' || sqlerrm);
      end;
  end process_scan_to_solace;


end PACK_CONNSOLACE;
/
exit;
