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

  L_CRMCUSTOMERNUMBER VARCHAR2(18);
  L_LEGALNAME         VARCHAR2(80);
  L_MAILSTREET   VARCHAR2(40);
  L_MAILSTREET2  VARCHAR2(40);
  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_SICCODE      VARCHAR2(6);
  L_COCNUMBER    VARCHAR2(20);

  L_ADDRESS_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 := V_JSON_OBJECT.GET_STRING('ELISID');
  L_LEGALNAME         := V_JSON_OBJECT.GET_STRING('LEGALNAME');
  L_MAILSTREET        := V_JSON_OBJECT.GET_STRING('ADDRESSLINE1');
  L_MAILSTREET2       := V_JSON_OBJECT.GET_STRING('ADDRESSLINE2');
  L_MAILZIPCODE       := SUBSTR(V_JSON_OBJECT.GET_STRING('ZIPCODE'), 1, 16);
  L_MAILCITY          := V_JSON_OBJECT.GET_STRING('CITY');
  L_COUNTRY           := V_JSON_OBJECT.GET_STRING('COUNTRYCODE');
  L_FAXNO             := SUBSTR(V_JSON_OBJECT.GET_STRING('FAXNUMBER'), 1, 31);
  L_EMAIL             := V_JSON_OBJECT.GET_STRING('EMAIL');
  L_PHONENO           := SUBSTR(V_JSON_OBJECT.GET_STRING('PHONENUMBER'), 1, 31);
  L_VATNUMBER         := V_JSON_OBJECT.GET_STRING('VATNUMBER');
  L_LANGUAGECODE      := V_JSON_OBJECT.GET_STRING('LANGUAGE');
  L_SICCODE           := V_JSON_OBJECT.GET_STRING('ACTIVITY');
  L_COCNUMBER         := V_JSON_OBJECT.GET_STRING('NATIONALID');

  UPDATE "&3".CUSTOMER C
     SET C.LEGALNAME = CASE
                         WHEN L_LEGALNAME IS NOT NULL THEN
                          L_LEGALNAME
                         ELSE
                          C.LEGALNAME
                       END,
         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
                            (SELECT L.LANGUAGE_ID
                               FROM "&3".LANGUAGES L
                              WHERE L.CODE = L_LANGUAGECODE)
                           ELSE
                            C.LANGUAGE_ID
                         END,
         C.SICCODE = CASE
                       WHEN L_SICCODE IS NOT NULL THEN
                        L_SICCODE
                       ELSE
                        C.SICCODE
                     END,
         C.COCNUMBER = CASE
                         WHEN L_COCNUMBER IS NOT NULL THEN
                          L_COCNUMBER
                         ELSE
                          C.COCNUMBER
                       END,
         C.TIMESTAMP   = SYSDATE
   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_MAILSTREET2 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
   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;
    INSERT INTO "&1".MESSAGE_FROM_SOLACE_ERROR
      (MESSAGE, TOPIC, ERROR, TIMESTAMP)
    VALUES
      (JSON_STRING, TOPIC, L_ERRM, SYSDATE);

  COMMIT;
  
END;
/
exit;