drop procedure "DB2ADMIN"."SPAPP_INSERT_CUSTOMERADDRESS"$ CREATE OR REPLACE PROCEDURE "DB2ADMIN"."SPAPP_INSERT_CUSTOMERADDRESS" ( IN CUSTOMER_ID int DEFAULT 0, IN ADDRESSTYPE CHAR(1) DEFAULT '', IN NEIGHBORHOOD VARCHAR(25) DEFAULT '', IN CITY VARCHAR(50) DEFAULT '', IN ADDR VARCHAR(50) DEFAULT '', IN COMPLEMENT VARCHAR(60) DEFAULT '', IN ADDRNUMBER VARCHAR(60) DEFAULT '', IN ZIPCODE CHAR(10) DEFAULT '', IN COUNTRY_ABBREV CHAR(3) DEFAULT '', IN STATE_ABBREV CHAR(5) DEFAULT '', IN FINALCUSTOMERCGC VARCHAR(18) DEFAULT '', IN STATEINCRIPTION VARCHAR(18) DEFAULT '', IN GUID VARCHAR(50) DEFAULT '', IN CONTACT VARCHAR(70) DEFAULT '', IN REFERENCE_POINT VARCHAR(80) DEFAULT '', IN CITY_CODE_IBGE INT DEFAULT 0, IN PHONE1 VARCHAR(20) DEFAULT '', IN PHONE2 VARCHAR(20) DEFAULT '', IN MAIL VARCHAR(40) DEFAULT '', OUT RECNUM_RETURN INT, OUT ERROR_STATE varchar(5)) LANGUAGE SQL BEGIN DECLARE ERROR_CODE int; DECLARE CGC_PARAM char(1); DECLARE CLIENTE_CGC varchar(18); DECLARE STATE_INSCRIPTION varchar(18); DECLARE CONSUMER_STATE_INSCRIPTION varchar(18); DECLARE FINAL_CLIENT int; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SEQ_END_COB int; DECLARE SEQ_END_RET int; DECLARE SEQ_END_ENT int; DECLARE CURRENT_SEQ int DEFAULT 0; DECLARE CONSUMER_ID int; DECLARE CITYNAME varchar(50); DECLARE cursor1_validation CURSOR FOR SELECT SPAPP_VALIDATE_CUSTOMERADDRESS(ADDRESSTYPE, CONSUMER_ID, CUSTOMER_ID) FROM SYSIBM.SYSDUMMY1; DECLARE cursor6_cityname CURSOR FOR SELECT FUNCAPP_UTLGETCITYBYIBGECODE(CITY_CODE_IBGE) FROM SYSIBM.SYSDUMMY1; DECLARE cursor2_config2 CURSOR FOR SELECT CGC_ENDCLI FROM CONFIG2; DECLARE cursor3_clientinfo CURSOR FOR SELECT CGC, INSCR_ESTADUAL, PRX_COBRANCA, PRX_RETIRADA, PRX_ENTREGA FROM CLIENTE WHERE CLIENTE.CODIGO_CLIENTE = CUSTOMER_ID; DECLARE cursor4_consumerinfo CURSOR FOR SELECT CODIGO_CLIENTE, INSCR_ESTADUAL FROM CLIENTE WHERE CLIENTE.CGC = FINALCUSTOMERCGC; DECLARE cursor5_getmaskedcgc CURSOR FOR SELECT FUNCAPP_MASK_CGC(FINALCUSTOMERCGC) FROM SYSIBM.SYSDUMMY1; DECLARE insert_into_endcli CURSOR FOR (Select RECNUM From Final Table (INSERT INTO DB2ADMIN.ENDCLI (CODIGO_CLIENTE, SEQ_END, TIPO_END, BAIRRO, CIDADE, ENDCLI.END, COMPL_END, END_NRO, CEP, SIGLA_PAIS, SIGLA_ESTADO, CGC, INSCR_ESTADUAL, GUID, CONTATO, PONTO_REF,COD_CID_IBGE, TELEFONE_1, TELEFONE_2, E_MAIL, END_ATIVO, CLIENTE_DESTINO) VALUES (CUSTOMER_ID, nVl(CURRENT_SEQ,1), left(nVl(ADDRESSTYPE,''),1), left(nVl(NEIGHBORHOOD,''),25), left(nVl(CITY, ''),60), left(nVl(ADDR,''),50), left(nVl(COMPLEMENT,''),60), left(nVl(ADDRNUMBER,''),60), left(nVl(ZIPCODE,''),10), left(nVl(COUNTRY_ABBREV,''),3), left(nVl(STATE_ABBREV,''),5), left(nVl(FINALCUSTOMERCGC,''),18), left(nVl(STATEINCRIPTION,''),18), left(nVl(GUID,''),50), left(nVl(CONTACT,''),70), left(nVl(REFERENCE_POINT,''),80), nVl(CITY_CODE_IBGE, 0), left(nVl(PHONE1,''),20), left(nVl(PHONE2,''),20), left(nVl(MAIL,''),40), 'S', nVl(CONSUMER_ID,0)))); OPEN cursor5_getmaskedcgc; FETCH FROM cursor5_getmaskedcgc INTO FINALCUSTOMERCGC; CLOSE cursor5_getmaskedcgc; OPEN cursor4_consumerinfo; FETCH FROM cursor4_consumerinfo INTO CONSUMER_ID, CONSUMER_STATE_INSCRIPTION; CLOSE cursor4_consumerinfo; OPEN cursor1_validation; FETCH FROM cursor1_validation INTO ERROR_CODE; CLOSE cursor1_validation; SET RECNUM_RETURN = error_code; IF (ERROR_CODE > 0) THEN OPEN cursor2_config2; FETCH FROM cursor2_config2 INTO CGC_PARAM; CLOSE cursor2_config2; OPEN cursor3_clientinfo; FETCH FROM cursor3_clientinfo INTO CLIENTE_CGC, STATE_INSCRIPTION, SEQ_END_COB, SEQ_END_RET, SEQ_END_ENT; CLOSE cursor3_clientinfo; IF (ADDRESSTYPE = 'C') THEN Set CURRENT_SEQ = SEQ_END_COB + 1; UPDATE CLIENTE SET PRX_COBRANCA = nvl(CURRENT_SEQ,1) WHERE CODIGO_CLIENTE = CUSTOMER_ID; ELSE IF (ADDRESSTYPE = 'R') THEN Set CURRENT_SEQ = SEQ_END_RET + 1; UPDATE CLIENTE SET PRX_RETIRADA = nvl(CURRENT_SEQ,1) WHERE CODIGO_CLIENTE = CUSTOMER_ID; ELSE Set CURRENT_SEQ = SEQ_END_ENT + 1; UPDATE CLIENTE SET PRX_ENTREGA = nvl(CURRENT_SEQ,1) WHERE CODIGO_CLIENTE = CUSTOMER_ID; END IF; END IF; IF (ADDRESSTYPE = 'T' and CGC_PARAM <> 'N') THEN Set STATEINCRIPTION = CONSUMER_STATE_INSCRIPTION; Set FINALCUSTOMERCGC = FINALCUSTOMERCGC; ELSE Set STATEINCRIPTION = STATE_INSCRIPTION; Set FINALCUSTOMERCGC = CLIENTE_CGC; END IF; OPEN cursor6_cityname; FETCH FROM cursor6_cityname INTO CITYNAME; CLOSE cursor6_cityname; IF (CITYNAME <> '') THEN Set CITY = CITYNAME; END IF; OPEN insert_into_endcli; FETCH FROM insert_into_endcli INTO RECNUM_RETURN; CLOSE insert_into_endcli; INSERT INTO PIENVIA (ID_INTERNO, EVENTO, DATAHORA) Values (RECNUM_RETURN, 'CustomerAddressCreatedEvent', current_timestamp); COMMIT; SET ERROR_STATE = SQLSTATE; END IF; END$