CREATE OR REPLACE PROCEDURE "&1".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(40);
  L_MAILADDRESS2               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 NUMBER;
  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               := SUBSTR(REGEXP_SUBSTR(V_JSON_OBJECT.GET_STRING('ADDRESS'), '[^' || CHR(10) || ']+', 1, 2), 1, 41);
  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               := 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           := '1';
  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('ELISID');
  L_SEND_RESPONSE              := V_JSON_OBJECT.GET_STRING('PESTCONTROL');

   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;