VALIDATE CPF, CNPJ, CEP

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Post Reply
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

Someone has some source that validates if the CPF, CNPJ and the CEP are true :?:

if someone has

available aee !!

Please

Hugs
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

CNPJ

Select all

CREATE OR REPLACE FUNCTION CNPJ 
      (p_cgc     IN CHAR) 
       RETURN    BOOLEAN 
IS 
     m_total     NUMBER   :=  0; 
     m_digito    NUMBER   :=  0; 
BEGIN 
     FOR i IN 1..4 LOOP 
         m_total := m_total + substr(p_cgc,i,1) * (6 - i); 
     END LOOP; 
 
     FOR i IN 5..12 LOOP 
         m_total := m_total + substr(p_cgc,i,1) * (14 - i); 
     END LOOP; 
 
     m_digito := 11 - mod(m_total,11); 
 
     IF m_digito > 9 THEN 
        m_digito := 0; 
     END IF; 
 
     IF m_digito != substr(p_cgc,13,1) THEN 
        RETURN FALSE; 
     END IF; 
 
     m_digito := 0; 
     m_total  := 0; 
 
     FOR i IN 1..5 LOOP 
         m_total := m_total + substr(p_cgc,i,1) * (7 - i); 
     END LOOP; 
 
     FOR i IN 6..13 LOOP 
         m_total := m_total + substr(p_cgc,i,1) * (15 - i); 
     END LOOP; 
 
     m_digito := 11 - mod(m_total,11); 
 
     IF m_digito > 9 THEN 
        m_digito := 0; 
     END IF; 
 
     IF m_digito != substr(p_cgc,14,1) THEN 
        RETURN FALSE; 
     END IF; 
 
     RETURN TRUE; 
end; 
CPF

Select all

CREATE OR REPLACE FUNCTION CPF 
      (p_cpf     IN CHAR) 
       RETURN    BOOLEAN 
IS 
     m_total     NUMBER   :=  0; 
     m_digito    NUMBER   :=  0; 
BEGIN 
     FOR i IN 1..9 LOOP 
         m_total := m_total + substr(p_cpf,i,1) * (11 - i); 
     END LOOP; 
 
     m_digito := 11 - mod(m_total,11); 
 
     IF m_digito > 9 THEN 
        m_digito := 0; 
     END IF; 
 
     IF m_digito != substr(p_cpf,10,1) THEN 
        RETURN FALSE; 
     END IF; 
 
     m_digito := 0; 
     m_total  := 0; 
 
     FOR i IN 1..10 LOOP 
         m_total := m_total + substr(p_cpf,i,1) * (12 - i); 
     END LOOP; 
 
     m_digito := 11 - mod(m_total,11); 
 
     IF m_digito > 9 THEN 
        m_digito := 0; 
     END IF; 
 
     IF m_digito != substr(p_cpf,11,1) THEN 
        RETURN FALSE; 
     END IF; 
 
     RETURN TRUE; 
 
end; 

Now the CEP , you will need access BD from the post office, or at least have a zip code.
If someone has this base, it could make available to everyone who is a very precious source!


: -O


.
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

then face

I never worked with Function in Forms


After I create this function in the bank
as I call her in forms ???

Sorry for ignorance!
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

It was Mals, I forgot to speak.

You call it as for example a post_change

CPF ('block.item');


and ready.
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

within the field at Trigger When Validate Item

CPF ('Participants.cic_participant');


But he accuses me error

Now I was lost from time to time !!!


[IMG http://sp4.fotologs.net/photo/52/49/100 ... 2418_f.jpg[/img]
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Once again I'm sorry


I spent you wrong the call there is difficult for you

Select all

 
if not cpf(:participantes.cic_participante) then 
	 message('CPF invalido'); 
	 message(' '); 
	 raise form_trigger_failure; 
end if; 
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

aí Alexandre

VLW face


Roller Certinho

He validates and Tals !!!!

só I had to inhibit typing characters because it does not treat não é? uhahua
but aaa old muito fuck huahua vlw
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Inhibit characters? like this? I put the Number item and use mascara 000 "." 000 "." 000 "-" 000 to treat. I do not know if that's what you said
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

this
for the person does not type
letters ...

I put in format mask what you put up there
gave this error

FRM-50027: invalid format mask for given datatype.
Edit Item: CIC_Participant
Item: CIC_Participant
Block: Participants
Form: SAT_CAD_PARTICIPANTS
FRM-30085: Unable to ADJUST FORM FOR OUTPUT.

Type

The code was not validating Quano I placed for exe. 359.589
But if I put 359589 it validates ...

understood?
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Yes, I got it!
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

hehe

vlw !!!

AAA I can not make him validate. (point) hehe
francisco.angelo
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 78
Joined: Wed, 16 Jan 2008 5:06 pm
Location: RJ

Alexandre ...

I did this way that you said there and it was show ... even with the format mask ... CPF (999 "." 999 "." 99)

Only I have a question .... How can I validate the same field the CPF or CNPJ or do I have to make a field for each ...?

I was able to validate the two ... more only when I put one in each field ... in the same field it will not, only the CPF ...
99]] Do you have any way to validate the two in the same field ????

Another question ... Only the message appears when the CPF is invalid.
How to put Message to appear also when the CPF is correct ??

Thanks
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

has any way to validate the two in the same field ????

Then

I believe that you have to make him understand that what is being typed.
CPF or CNPJ


Good


You can put an option where the person selects physics or Legal,
porque senao has parameters there is no way he can understand

through the parameter he checks with an IF

if it is physical. .
Senao does ...
End

understood ??


----------------------------------- --------------

How to put Message to appear also when the CPF is correct ??

Yes
There are two equal lines ,,, the first has the incorrect CPF message.
The second among parentheses has a '' ... among them put the message ... porque é a trigger return.



------------
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

To make the correct message you put this in the When Validate item

Select all

if not cpf(:TPESAGEM.CPF_MOTORISTA) then 
	 message('CPF invalido'); 
	 message(' '); 
	 raise form_trigger_failure; 
        else  
                 message('CPF válido'); 
	 message(' '); 
end if;	
francisco.angelo
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 78
Joined: Wed, 16 Jan 2008 5:06 pm
Location: RJ
Francisco Angelo
///////////////////////////////

Thanks ...

Thanks ...
dante
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Thu, 24 Jan 2008 1:58 pm
Location: são paulo sp
Danilo Maranini
Aprendendo e Compartilhando

If I put
11111111111111
22222222222222222
333333333333333
444444444444
55555555555
66666666666666
777777777777
88888888888888
99999999999999999
000000000000000000

He considers CPF valid .....
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

For it is Dante. It really has this bug, to fix this you will have to make an if it compares with these values ??and matches it from Return False.
edson.amorim
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 135
Joined: Thu, 04 Oct 2007 3:36 pm
Location: Belo Horizonte - MG

Dear Alexandre and colleagues,

I have counted on the help of you and especially from our friend Francisco Angelo. I have the CPF Engrave field on my bank set to Number (11). I put the mask as informed above. As soon as I compiled and type the CPF, it gives ok, navigate between the records and everything right but if I leave the program and return again, EELE does not show the recorded CPFs. There in the bank it is recorded like this: 8,8889E + 10 What is happening? How do I correct this?

grateful,

Edson
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

I have counted on the help of you and especially from our friend Francisco Angelo. I have the CPF Engrave field on my bank set to Number (11). I put the mask as informed above. As soon as I compiled and type the CPF, it gives ok, navigate between the records and everything right but if I leave the program and return again, EELE does not show the recorded CPFs. There in the bank it is recorded like this: 8,8889E + 10 What is happening? How do I correct this?
Edson,

The data were certainly recorded correctly at the base.
Probably the problem is in the tool you are using to retrieve this data, which is set to return very large numbers in scientific notation.

In PLSQLDeveloper, for example, there is a configuration to convert numerical fields returned by a query to char in order to display large numbers.

already in SQL * plus, the solution would be to query with the to_char function on the field, or use the command

Select all

col <coluna> format 99999999999999 
select <coluna> from <tabela> where ...
edson.amorim
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 135
Joined: Thu, 04 Oct 2007 3:36 pm
Location: Belo Horizonte - MG

Dear colleague,


In my case, I am using Forms 6i, would I have to make some configuration in the forms? Already in the bank, how the field was defined as Number (11) would it be able to clean the field? Or would I have to change the kind of field on my bank?

grateful,

Edson
francisco.angelo
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 78
Joined: Wed, 16 Jan 2008 5:06 pm
Location: RJ
Francisco Angelo
///////////////////////////////

Edison,

Changes the CPF field of Number to Varchar.

In mine it appears right ... in mine is CPF Varchar (15).

When I consult in the bank, it appears right ..
edson.amorim
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 135
Joined: Thu, 04 Oct 2007 3:36 pm
Location: Belo Horizonte - MG

Dear Francisco,

Perfect !! You did exactly as you sent and gave right, it's working beauty !!

As I am excited, I come back at any time to pester so much to you and the other colleagues from here for help, because I will still need you a lot !! I already have some material and would like to know if you have to make it available or would indicate how to find some more material about the forms !!

Once again, my thank you,

Edson Amorim
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 9 guests