CREATE OR REPLACE PROCEDURE "&1".INSERT_ERP_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_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(40);
  L_MAILADDRESS2               VARCHAR2(40);
  L_MAILADDRESS3			   VARCHAR2(40);
  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(40);
  L_VISITADDRESS2              VARCHAR2(40);
  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(40);
  L_INVOICEADDRESS2            VARCHAR2(40);
  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);

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_CUSTOMERNUMBER       	   := "&1".GET_ABS_CUSTOMER_NUMBER;
  L_CUSTOMERNUMBER_OLD   	   := NULL;
  L_LEGACYCUSTOMERNUMBER 	   := NULL; 
  L_CUSTOMERNAME                := V_JSON_OBJECT.GET_STRING('COMMERCIALNAME');
  L_LEGALNAME                   := V_JSON_OBJECT.GET_STRING('COMPANYNAME');
  L_BILLTOCUSTOMERNUMBER        := L_CUSTOMERNUMBER;
  L_BUSINESSUNIT                := V_JSON_OBJECT.GET_STRING('PLANTNUMBER');
  L_SEARCHNAME                  := SUBSTR(L_CUSTOMERNAME, 1, 10);
  L_DATEACTIVE                  := SYSDATE;
  L_DATEINACTIVE                := TO_DATE('31-12-2099', 'dd-mm-yyyy');
  L_ACTIVE                      := 'Y';
  L_MAILADDRESS                 := SUBSTR(REGEXP_SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESS'),
                                                        '[^' || CHR(10) || ']+',
                                                        1,
                                                        1),
                                          1,
                                          41);
  L_MAILADDRESS2                := TRIM(BOTH ' ' FROM SUBSTR(REGEXP_SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESS'),
                                                        '[^' || CHR(10) || ']+',
                                                        1,
                                                        2),
                                          1,
                                          41));
  L_MAILADDRESS3                := TRIM(BOTH ' ' FROM SUBSTR(REGEXP_SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESS'),
                                                        '[^' || CHR(10) || ']+',
                                                        1,
                                                        3),
                                          1,
                                          41));  
  IF LENGTH(L_MAILADDRESS2) < 40 AND L_MAILADDRESS3 IS NOT NULL THEN
    L_MAILADDRESS2 := SUBSTR(L_MAILADDRESS2 || ' ' || L_MAILADDRESS3, 1, 41);
  END IF;                                                                                   
  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('COUNTRY');
  L_EMAIL                       := V_JSON_OBJECT.GET_STRING('EMAIL');
  L_PHONENO                     := SUBSTR(V_JSON_OBJECT.GET_STRING('PHONENUMBER'),
                                          1,
                                          31);
  L_FAXNO                       := SUBSTR(V_JSON_OBJECT.GET_STRING('FAXNUMBER'),
                                          1,
                                          31);
  L_LANGUAGECODE                := UPPER(V_JSON_OBJECT.GET_STRING('LANGUAGE'));
  L_MARKETSEGMENTCODE           := '-';
  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                   := V_JSON_OBJECT.GET_STRING('VATNUMBER');
  L_SENDINVOICETO               := 1;
  L_EXTRANAME1                  := NULL;
  L_EXTRANAME2                  := NULL;
  L_INVOICEADDRESS              := NULL;
  L_INVOICEADDRESS2             := NULL;
  L_INVOICEZIPCODE              := NULL;
  L_INVOICECITY                 := NULL;
  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                     := '5610';
  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;
  L_CRMCUSTOMERNUMBER           := V_JSON_OBJECT.GET_STRING('ELISCUSTOMERID');
  IF V_JSON_OBJECT.get_Boolean('PESTCONTROL') = TRUE THEN
    L_SEND_RESPONSE := 'N';
  ELSE
    L_SEND_RESPONSE := 'Y';
  END IF;
   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);
	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;