Nomas no veo por donde...
Código SQL
[-]SET TERM ^^ ;
CREATE PROCEDURE P_CALCULO_RFC (
NOMBRES_AUX VarChar(100),
APATERNO_AUX VarChar(100),
AMATERNO_AUX VarChar(100),
FECHANACIMIENTO Date)
returns (
RFC_OUT Char(16))
AS
DECLARE NOMBRES VARCHAR(100);
DECLARE APATERNO VARCHAR(100);
DECLARE AMATERNO VARCHAR(100);
DECLARE T_NOMTOT CHAR(52);
DECLARE NOMBRE1 VARCHAR(100);
DECLARE NOMBRE2 VARCHAR(100);
DECLARE NOMBRES_LONGITUD INT;
DECLARE NOMBRE1_LONGITUD INT ;
DECLARE APATERNO1 VARCHAR(100);
DECLARE APATERNO2 VARCHAR(100);
DECLARE APATERNO_LONGITUD INT;
DECLARE APATERNO1_LONGITUD INT ;
DECLARE AMATERNO1 VARCHAR(100);
DECLARE AMATERNO2 VARCHAR(100);
DECLARE AMATERNO_LONGITUD INT ;
DECLARE AMATERNO1_LONGITUD INT ;
DECLARE VARLOOPS INT ;
DECLARE RFC CHAR(16);
DECLARE T_NOMNUM CHAR(102);
DECLARE T_SUMA INT ;
DECLARE T_DIVID INT;
DECLARE T_MOD INT;
DECLARE T_HOMOCLV CHAR(3);
DECLARE T_NUMERO INT ;
DECLARE T_PARCIAL INT ;
begin
NOMBRES = TRIM(NOMBRES_AUX);
APATERNO = TRIM(APATERNO_AUX);
AMATERNO = TRIM(AMATERNO_AUX);
T_NOMTOT =APATERNO||' '||AMATERNO||' '||NOMBRES;
VARLOOPS = 0 ;
WHILE VARLOOPS <> 1 do
BEGIN
NOMBRES_LONGITUD = CHAR_LENGTH (NOMBRES);
NOMBRE1_LONGITUD = position('% %',NOMBRES);
IF (NOMBRE1_LONGITUD = 0) then
NOMBRE1_LONGITUD = NOMBRES_LONGITUD;
NOMBRE1 = TRIM(LEFT(NOMBRES,NOMBRE1_LONGITUD));
NOMBRE2 = TRIM(RIGHT(NOMBRES,NOMBRES_LONGITUD - NOMBRE1_LONGITUD));
IF (NOMBRE1 IN ('JOSE','MARIA','MA.','MA','DE','LA','LAS','MC','VON','DEL','LOS','Y','MAC','VAN') AND NOMBRE2 <> '') then
BEGIN
NOMBRES = NOMBRE2;
END
ELSE
BEGIN
VARLOOPS = 1 ;
END
END
VARLOOPS = 0;
WHILE VARLOOPS <> 1 do
BEGIN
APATERNO_LONGITUD = CHAR_LENGTH (APATERNO) ;
APATERNO1_LONGITUD =position('% %',APATERNO);
IF (APATERNO1_LONGITUD = 0 ) then
APATERNO1_LONGITUD = APATERNO_LONGITUD;
APATERNO1 = TRIM(LEFT(APATERNO,APATERNO1_LONGITUD))
APATERNO2 = TRIM(RIGHT(APATERNO,APATERNO_LONGITUD - APATERNO1_LONGITUD))
IF APATERNO1 IN ('DE','LA','LAS','MC','VON','DEL','LOS','Y','MAC','VAN') AND APATERNO2 <> ''
BEGIN
APATERNO = APATERNO2;
END
ELSE
BEGIN
VARLOOPS = 1 ;
END
END
VARLOOPS = 0;
WHILE VARLOOPS <> 1 do
BEGIN
AMATERNO_LONGITUD = LEN(AMATERNO);
AMATERNO1_LONGITUD = PATINDEX('% %',AMATERNO);
IF AMATERNO1_LONGITUD = 0
AMATERNO1_LONGITUD = AMATERNO_LONGITUD ;
AMATERNO1 = TRIM(LEFT(AMATERNO,AMATERNO1_LONGITUD));
AMATERNO2 = TRIM(RIGHT(AMATERNO,AMATERNO_LONGITUD - AMATERNO1_LONGITUD));
IF (AMATERNO1 IN ('DE','LA','LAS','MC','VON','DEL','LOS','Y','MAC','VAN') AND AMATERNO2 <> '')then
BEGIN
AMATERNO = AMATERNO2;
END
ELSE
BEGIN
VARLOOPS = 1 ;
END
END
RFC = LEFT(APATERNO1,1);
APATERNO1_LONGITUD= LEN(APATERNO1) ;
VARLOOPS = 1 ;
WHILE APATERNO1_LONGITUD > VARLOOPS do
BEGIN
VARLOOPS = VARLOOPS + 1;
IF (SUBSTRING(APATERNO1,VARLOOPS,1) IN ('A','E','I','O','U')) then
BEGIN
RFC = RTRIM(RFC)+CONVERT(CHAR(1),SUBSTRING APATERNO1,@VARLOOPS,1));
VARLOOPS = APATERNO1_LONGITUD ;
END
END
IF (IS NULL(AMATERNO1,'') = '') then
BEGIN
RFC = RTRIM(RFC)+CONVERT(CHAR(1),SUBSTRING(APATERNO1,1,1)) ;
END
ELSE
BEGIN
RFC = RTRIM(RFC)+CONVERT(CHAR(1),SUBSTRING(AMATERNO1,1,1));
END
RFC = TRIM(RFC)+CONVERT(CHAR(1),SUBSTRING(NOMBRE1,1,1));
RFC = RTRIM(RFC) + CONVERT(CHAR,FECHANACIMIENTO,12) ;
T_NOMNUM = '0';
VARLOOPS = 1 ;
WHILE VARLOOPS <= 52 do
BEGIN
T_NOMNUM = LTRIM(RTRIM (T_NOMNUM)) +
CASE
WHEN SUBSTRING (T_NOMTOT , VARLOOPS, 1) = 'A' THEN '11'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'B' THEN '12'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'C' THEN '13'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'D' THEN '14'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'E' THEN '15'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'F' THEN '16'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'G' THEN '17'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'H' THEN '18'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'I' THEN '19'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'J' THEN '21'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'K' THEN '22'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'L' THEN '23'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'M' THEN '24'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'N' THEN '25'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'O' THEN '26'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'P' THEN '27'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'Q' THEN '28'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'R' THEN '29'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'S' THEN '32'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'T' THEN '33'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'U' THEN '34'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'V' THEN '35'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'W' THEN '36'
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) = 'X' THEN '37'
SUBSTRING (T_NOMTOT , VARLOOPS , 1) <= '9' THEN CONVERT(VARCHAR,CONVERT(INT, SUBSTRING (T_NOMTOT , VARLOOPS , 1)) , 2)
WHEN SUBSTRING (T_NOMTOT , VARLOOPS , 1) IN ('&','Ñ') THEN '10'
ELSE '00'
END
VARLOOPS = VARLOOPS + 1;
END
VARLOOPS = 1 ;
T_SUMA = 0;
while VARLOOPS <= 99 do
T_SUMA = T_SUMA + ((CONVERT(INT,SUBSTRING (T_NOMNUM , VARLOOPS , 1))*10) + CONVERT(INT,SUBSTRING (T_NOMNUM , VARLOOPS+1 , 1))) * CONVERT(INT,SUBSTRING (T_NOMNUM , VARLOOPS+1 , 1));
VARLOOPS = VARLOOPS + 1 ;
end
SELECT T_DIVID = T_SUMA%1000
SELECT T_MOD = T_DIVID%34
SELECT T_DIVID = (T_DIVID - T_MOD) / 34
VARLOOPS = 0;
WHILE VARLOOPS <= 1 do
BEGIN
T_HOMOCLV =
CASE CASE VARLOOPS WHEN 0 THEN T_DIVID ELSE T_MOD END
WHEN 0 THEN '1'
WHEN 1 THEN '2'
WHEN 2 THEN '3'
WHEN 3 THEN '4'
WHEN 4 THEN '5'
WHEN 5 THEN '6'
WHEN 6 THEN '7'
WHEN 7 THEN '8'
WHEN 8 THEN '9'
WHEN 9 THEN 'A'
WHEN 10 THEN 'B'
WHEN 11 THEN 'C'
WHEN 12 THEN 'D'
WHEN 13 THEN 'E'
WHEN 14 THEN 'F'
WHEN 15 THEN 'G'
WHEN 16 THEN 'H'
WHEN 17 THEN 'I'
WHEN 18 THEN 'J'
WHEN 19 THEN 'K'
WHEN 20 THEN 'L'
WHEN 21 THEN 'M'
WHEN 22 THEN 'N'
WHEN 23 THEN 'P'
WHEN 24 THEN 'Q'
WHEN 25 THEN 'R'
WHEN 26 THEN 'S'
WHEN 27 THEN 'T'
WHEN 28 THEN 'U'
WHEN 29 THEN 'V'
WHEN 30 THEN 'W'
WHEN 31 THEN 'X'
WHEN 32 THEN 'Y'
ELSE 'Z'
END
VARLOOPS = VARLOOPS + 1;
RFC = LTRIM(RTRIM (RFC)) + LTRIM(RTRIM (T_HOMOCLV));
END
VARLOOPS = 0 ;
T_PARCIAL = 0;
WHILE VARLOOPS < 12 do
BEGIN
SELECT VARLOOPS = VARLOOPS + 1
T_NUMERO =
CASE
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'A' THEN 10
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'B' THEN 11
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'C' THEN 12
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'D' THEN 13
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'E' THEN 14
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'F' THEN 15
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'G' THEN 16
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'H' THEN 17
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'I' THEN 18
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'J' THEN 19
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'K' THEN 20
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'L' THEN 21
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'M' THEN 22
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'N' THEN 23
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'O' THEN 25
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'P' THEN 26
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'Q' THEN 27
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'R' THEN 28
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'S' THEN 29
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'T' THEN 30
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'U' THEN 31
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'V' THEN 32
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'W' THEN 33
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'X' THEN 34
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'Y' THEN 35
WHEN SUBSTRING (RFC , VARLOOPS , 1) = 'Z' THEN 36
WHEN SUBSTRING (RFC , VARLOOPS , 1) >= '0' AND
SUBSTRING (RFC , VARLOOPS , 1) <= '9'
THEN CONVERT(INT,SUBSTRING (RFC , VARLOOPS , 1))
WHEN SUBSTRING (RFC , VARLOOPS , 1) = ''
THEN 24
WHEN SUBSTRING (RFC , VARLOOPS , 1) = ' '
THEN 37
ELSE 0
END
SELECT T_PARCIAL = T_PARCIAL + (T_NUMERO * (14 - VARLOOPS))
END
T_MOD = ROUND(T_PARCIAL%11,1);
IF (T_MOD = 0 ) then
RFC = LTRIM(RTRIM (RFC)) + '0' ;
ELSE
BEGIN
T_PARCIAL = 11 - T_MOD ;
IF (T_PARCIAL = 10 ) then
SELECT RFC = LTRIM(RTRIM (RFC)) + 'A'
ELSE
SELECT RFC = LTRIM(RTRIM (RFC)) + CONVERT(VARCHAR ,T_PARCIAL)
END
:RFC_OUT = :RFC;
SELECT :RFC AS 'RFC'
end ^^
SET TERM ; ^^