Ajustes após importação de parceiros

AJUSTAR ENDEREÇO

UPDATE TGFPAR SET CODEND = (SELECT MIN(CODEND) FROM TSICEP C WHERE TGFPAR.CEP=C.CEP)

WHERE ATIVO=’S’ AND CLIENTE=’S’ AND CODEND=0 AND CEP IN (SELECT CEP FROM TSICEP);

 

AJUSTAR BAIRRO

UPDATE TGFPAR SET CODBAI = (SELECT MIN(CODBAI) FROM TSICEP C WHERE TGFPAR.CEP=C.CEP)

WHERE ATIVO=’S’ AND CLIENTE=’S’ AND CODEND=0 AND CEP IN (SELECT CEP FROM TSICEP);

 

AJUSTAT CIDADE

UPDATE TGFPAR SET CODCID = (SELECT MIN(CODCID) FROM TSICEP C WHERE TGFPAR.CEP=C.CEP )

WHERE ATIVO=’S’ AND CLIENTE=’S’ AND CODEND=0 AND CEP IN

(SELECT CEP FROM TSICEP WHERE CODCID IS NOT NULL AND CODCID IN (SELECT CODCID FROM TSICID) );

 

AJUSTAR CAMPO TELEFONE


 

UPDATE TGFPAR

   SET TELEFONE = SUBSTR( TRIM( TELEFONE ), 1, 2 ) ||

                  LPAD( SUBSTR( TRIM( TELEFONE ), 3, LENGTH( TELEFONE ) ), 10, ' ' )

WHERE CODPARC = &CODPARC

AJUSTAR CPF

UPDATE TGFPAR SET CGC_CPF=LPAD(CGC_CPF, 11,'0') WHERE TIPPESSOA='F’ AND ATIVO=’S'

AJUSTAR CNPJ

UPDATE TGFPAR SET CGC_CPF=LPAD(CGC_CPF, 14,'0') WHERE TIPPESSOA='J' AND ATIVO=’S';

AJUSTAR CLASSIFICAÇAO ICMS

UPDATE TGFPAR SET CLASSIFICMS='C' WHERE TIPPESSOA='F' AND CLASSIFICMS IS NULL

UPDATE TGFPAR SET CLASSIFICMS='R' WHERE TIPPESSOA='J' AND CLASSIFICMS IS NULL 

Fonte: Anderson e Luciana (BP Recife)