Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- ============================================
CREATE OR ALTER PROCEDURE dbo.CLAE_JUJUY_
AS
BEGIN
SET NOCOUNT ON;
declare @cuit varchar(20)
declare @cod_clae varchar(10)
declare @descripcion varchar(500)
declare @principal int
declare @cuit_soc varchar(20)
declare @cargo varchar(20)
declare @domicilios table (id int, domicilio varchar(200), pai_nombre varchar(50), prv_nombre varchar(100), loc_nombre varchar(100), codpos varchar(10), tipo_domicilio varchar(10), created datetime)
declare @domicilio varchar(200),
@localidad varchar(100),
@provincia varchar(100),
@codpos varchar(10) ,
@tipo_domicilio varchar(10)
declare @p_ultimo_cuit varchar(20) = null
select @p_ultimo_cuit = cuit from CLAE_JUJUY_ultimo
IF @p_ultimo_cuit = null set @p_ultimo_cuit = ''
DECLARE W_CURSOR CURSOR FOR
SELECT /*top 10000*/ cuit, cod_clae, descripcion, principal
from clae c
where c.cuit like '3%'
and c.cuit > @p_ultimo_cuit
and c.visible = 1 and c.cod_clae in ('021010',
'021020',
'021030',
'022010',
'022020',
'024010',
'024020',
'051000',
'052000',
'061000',
'062000',
'071000',
'072100',
'072910',
'072990',
'081100',
'081200',
'081300',
'081400',
'089110',
'089120',
'089200',
'089300',
'089900',
'091000',
'099000',
'191000',
'192000',
'201110',
'201120',
'201130',
'201140',
'201180',
'201190',
'201210',
'201220',
'201300',
'201401',
'201409',
'202101',
'202200',
'202311',
'202907',
'202908',
'203000',
'204000',
'221110',
'221120',
'221901',
'221909',
'222010',
'222090',
'261000',
'262000',
'263000',
'264000',
'265101',
'265102',
'265200',
'266010',
'266090',
'267001',
'267002',
'268000',
'611010',
'611090',
'612000',
'613000',
'614010',
'614090',
'619000',
'620900',
'631110',
'631120',
'631190',
'631200',
'639100',
'639900',
'711003',
'281100',
'281201',
'281301',
'281400',
'281500',
'281600',
'281700',
'281900',
'282110',
'282120',
'282130',
'282200',
'282300',
'282400',
'282500',
'282600',
'282901',
'282909',
'331101',
'331210',
'331220',
'331290',
'331301',
'331400',
'331900',
'271010',
'271020',
'272000',
'273110',
'273190',
'332000',
'351110',
'351120',
'351130',
'351190',
'351201',
'351310',
'351320',
'352010',
'352020',
'353001',
'360010',
'360020',
'370000',
'231010',
'231020',
'231090',
'239100',
'239201',
'239202',
'239209',
'239310',
'239391',
'239399',
'239410',
'239421',
'239422',
'239510',
'239591',
'239592',
'239593',
'239600',
'239900',
'410011',
'410021',
'421000',
'422100',
'422200',
'429010',
'429090',
'431100',
'431210',
'431220',
'432110',
'432190',
'432200',
'432910',
'432920',
'432990',
'433010',
'433020',
'433030',
'433040',
'433090',
'439100',
'439910',
'439990',
'711001',
'711002',
'711009',
'712000',
'491110',
'491120',
'491200',
'492110',
'492120',
'492130',
'492140',
'492150',
'492160',
'492170',
'492180',
'492190',
'492210',
'492221',
'492229',
'492230',
'492240',
'492250',
'492280',
'492290',
'493110',
'493120',
'493200',
'501100',
'501200',
'502101',
'502200',
'511000',
'512000',
'521010',
'521020',
'521030',
'522010',
'522020',
'522091',
'522092',
'522099',
'523011',
'523019',
'523020',
'523031',
'523032',
'523039',
'523090',
'524110',
'524120',
'524130',
'524190',
'524210',
'524220',
'524230',
'524290',
'524310',
'524320',
'524330',
'524390',
'530010',
'530090',
'771110',
'771190',
'771210',
'771220',
'771290',
'773010',
'773020',
'773030',
'773040',
'773090',
'774000',
'551021',
'551022',
'551023',
'551090',
'552000',
'561011',
'561013',
'561014',
'561019',
'561020',
'561040',
'562010',
'562091',
'562099',
'620100',
'620200',
'620300',
'681010',
'681020',
'681098',
'681099',
'682010',
'682091',
'682099',
'691001',
'691002',
'692000',
'702010',
'702091',
'702092',
'702099',
'731001',
'731009',
'732000',
'749001',
'749009',
'780000',
'791100',
'791200',
'791909',
'381100',
'381200',
'382010',
'382020',
'390000',
'951100',
'951200',
'952100',
'952910',
'960101',
'960102',
'960990')
order by c.cuit
declare @jujuy bit
OPEN W_CURSOR
FETCH NEXT FROM W_CURSOR INTO @cuit,@cod_clae, @descripcion,@principal
WHILE @@fetch_status = 0
BEGIN
set @jujuy = 0
DELETE FROM @domicilios
insert into @domicilios
exec sp_obtener_domicilios @cuit, 0
set @domicilio = null
set @localidad = null
set @provincia = null
set @codpos = null
--select @cuit, * from @domicilios
SELECT top 1
@domicilio =
isnull(d.domicilio,''),
@localidad =
isnull(d.loc_nombre,''),
@provincia =
isnull(d.prv_nombre,''),
@codpos =
isnull(d.codpos,''),
@tipo_domicilio = isnull(d.tipo_domicilio,'')
from @domicilios d
where d.prv_nombre = 'JUJUY'
and d.domicilio not like 'RESIDE EN'
and d.domicilio not like 'DOMICILIADA EN'
order by (case
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1'
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '') then '2'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '') then '4'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '') then '8'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '') then '9'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '') then '10'
else '11'
end)
--select 'antes de entrar', @domicilio domi, @localidad local, @provincia pcia, @codpos codpos
if (isnull(@domicilio,'') != '')
begin
set @jujuy = 1
--select 'despues de entrar', @cuit, @cod_clae clae, @descripcion as des, @principal ppal,@domicilio as domi, @localidad as localidad, @provincia as pcia, @codpos as codpos
insert into CLAE_JUJUY
select @cuit, nombre_persona, @cod_clae, @descripcion, @principal, null,null,null,
@domicilio, @localidad, @provincia, @codpos
from padrones where identificacion = @cuit and habilitado = 1 and identificador in (select identificador from identificadores where country_code = 'AR')
end
else
begin
DECLARE W_CURSOR_2 CURSOR FOR
select c01_cuit_soc, car_nombre
from socios
inner join cargos on cargos.car_codigo = socios.car_codigo
-- Chequeamos que sea un cuit valido el del socio
inner join padrones p2 on (p2.identificacion = @cuit_soc and p2.habilitado = 1 and p2.identificador in (select identificador from identificadores where country_code = 'AR'))
where c01_cuit = @cuit and visible = 1 and c04_ultimo = 'S'
OPEN W_CURSOR_2
FETCH NEXT FROM W_CURSOR_2 INTO @cuit_soc, @cargo
WHILE @@fetch_status = 0
BEGIN
DELETE FROM @domicilios
insert into @domicilios
exec sp_obtener_domicilios @cuit_soc, 0
--select @cuit, @cuit_soc, * from @domicilios
set @domicilio = null
set @localidad = null
set @provincia = null
set @codpos = null
SELECT top 1
@domicilio =
isnull(d.domicilio,''),
@localidad =
isnull(d.loc_nombre,''),
@provincia =
isnull(d.prv_nombre,''),
@codpos =
isnull(d.codpos,''),
@tipo_domicilio = isnull(d.tipo_domicilio,'')
from @domicilios d
where d.prv_nombre = 'JUJUY'
and d.domicilio not like 'RESIDE EN'
and d.domicilio not like 'DOMICILIADA EN'
order by (case
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1'
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '') then '2'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '') then '4'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '') then '8'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '') then '9'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '') then '10'
else '11'
end)
--select 'antes de entrar SOCIO', @domicilio domi, @localidad local, @provincia pcia, @codpos codpos
if (isnull(@domicilio,'') != '')
begin
set @jujuy = 1
--select 'adentro SOCIO', @cuit, @cod_clae clae, @descripcion as des, @principal ppal, @domicilio domi, @localidad local, @provincia pcia, @codpos codpos, @cuit_soc as socio
insert into CLAE_JUJUY
select @cuit, p1.nombre_persona, @cod_clae, @descripcion, @principal,
@cuit_soc, p2.nombre_persona, @cargo,
@domicilio, @localidad, @provincia, @codpos
from padrones p1, padrones p2
where (p1.identificacion = @cuit and p1.habilitado = 1 and p1.identificador in (select identificador from identificadores where country_code = 'AR'))
and (p2.identificacion = @cuit_soc and p2.habilitado = 1 and p2.identificador in (select identificador from identificadores where country_code = 'AR'))
end
FETCH NEXT FROM W_CURSOR_2 INTO @cuit_soc, @cargo
END
CLOSE W_CURSOR_2
DEALLOCATE W_CURSOR_2
end
update CLAE_JUJUY_ultimo set cuit = @cuit, cantidad = cantidad + 1, jujuy = iif(@jujuy = 1, jujuy + 1, jujuy)
FETCH NEXT FROM W_CURSOR INTO @cuit,@cod_clae, @descripcion,@principal
END
CLOSE W_CURSOR
DEALLOCATE W_CURSOR
select @cuit as ultimo
END
/*
-- Cuando lo queremos correr todo otra vez
delete from CLAE_JUJUY
update CLAE_JUJUY_ultimo set cuit = '', cantidad = 0, jujuy = 0
*/
GO
GO
-- ============================================
CREATE OR ALTER PROCEDURE dbo.CLAE_JUJUY_
AS
BEGIN
SET NOCOUNT ON;
declare @cuit varchar(20)
declare @cod_clae varchar(10)
declare @descripcion varchar(500)
declare @principal int
declare @cuit_soc varchar(20)
declare @cargo varchar(20)
declare @domicilios table (id int, domicilio varchar(200), pai_nombre varchar(50), prv_nombre varchar(100), loc_nombre varchar(100), codpos varchar(10), tipo_domicilio varchar(10), created datetime)
declare @domicilio varchar(200),
@localidad varchar(100),
@provincia varchar(100),
@codpos varchar(10) ,
@tipo_domicilio varchar(10)
declare @p_ultimo_cuit varchar(20) = null
select @p_ultimo_cuit = cuit from CLAE_JUJUY_ultimo
IF @p_ultimo_cuit = null set @p_ultimo_cuit = ''
DECLARE W_CURSOR CURSOR FOR
SELECT /*top 10000*/ cuit, cod_clae, descripcion, principal
from clae c
where c.cuit like '3%'
and c.cuit > @p_ultimo_cuit
and c.visible = 1 and c.cod_clae in ('021010',
'021020',
'021030',
'022010',
'022020',
'024010',
'024020',
'051000',
'052000',
'061000',
'062000',
'071000',
'072100',
'072910',
'072990',
'081100',
'081200',
'081300',
'081400',
'089110',
'089120',
'089200',
'089300',
'089900',
'091000',
'099000',
'191000',
'192000',
'201110',
'201120',
'201130',
'201140',
'201180',
'201190',
'201210',
'201220',
'201300',
'201401',
'201409',
'202101',
'202200',
'202311',
'202907',
'202908',
'203000',
'204000',
'221110',
'221120',
'221901',
'221909',
'222010',
'222090',
'261000',
'262000',
'263000',
'264000',
'265101',
'265102',
'265200',
'266010',
'266090',
'267001',
'267002',
'268000',
'611010',
'611090',
'612000',
'613000',
'614010',
'614090',
'619000',
'620900',
'631110',
'631120',
'631190',
'631200',
'639100',
'639900',
'711003',
'281100',
'281201',
'281301',
'281400',
'281500',
'281600',
'281700',
'281900',
'282110',
'282120',
'282130',
'282200',
'282300',
'282400',
'282500',
'282600',
'282901',
'282909',
'331101',
'331210',
'331220',
'331290',
'331301',
'331400',
'331900',
'271010',
'271020',
'272000',
'273110',
'273190',
'332000',
'351110',
'351120',
'351130',
'351190',
'351201',
'351310',
'351320',
'352010',
'352020',
'353001',
'360010',
'360020',
'370000',
'231010',
'231020',
'231090',
'239100',
'239201',
'239202',
'239209',
'239310',
'239391',
'239399',
'239410',
'239421',
'239422',
'239510',
'239591',
'239592',
'239593',
'239600',
'239900',
'410011',
'410021',
'421000',
'422100',
'422200',
'429010',
'429090',
'431100',
'431210',
'431220',
'432110',
'432190',
'432200',
'432910',
'432920',
'432990',
'433010',
'433020',
'433030',
'433040',
'433090',
'439100',
'439910',
'439990',
'711001',
'711002',
'711009',
'712000',
'491110',
'491120',
'491200',
'492110',
'492120',
'492130',
'492140',
'492150',
'492160',
'492170',
'492180',
'492190',
'492210',
'492221',
'492229',
'492230',
'492240',
'492250',
'492280',
'492290',
'493110',
'493120',
'493200',
'501100',
'501200',
'502101',
'502200',
'511000',
'512000',
'521010',
'521020',
'521030',
'522010',
'522020',
'522091',
'522092',
'522099',
'523011',
'523019',
'523020',
'523031',
'523032',
'523039',
'523090',
'524110',
'524120',
'524130',
'524190',
'524210',
'524220',
'524230',
'524290',
'524310',
'524320',
'524330',
'524390',
'530010',
'530090',
'771110',
'771190',
'771210',
'771220',
'771290',
'773010',
'773020',
'773030',
'773040',
'773090',
'774000',
'551021',
'551022',
'551023',
'551090',
'552000',
'561011',
'561013',
'561014',
'561019',
'561020',
'561040',
'562010',
'562091',
'562099',
'620100',
'620200',
'620300',
'681010',
'681020',
'681098',
'681099',
'682010',
'682091',
'682099',
'691001',
'691002',
'692000',
'702010',
'702091',
'702092',
'702099',
'731001',
'731009',
'732000',
'749001',
'749009',
'780000',
'791100',
'791200',
'791909',
'381100',
'381200',
'382010',
'382020',
'390000',
'951100',
'951200',
'952100',
'952910',
'960101',
'960102',
'960990')
order by c.cuit
declare @jujuy bit
OPEN W_CURSOR
FETCH NEXT FROM W_CURSOR INTO @cuit,@cod_clae, @descripcion,@principal
WHILE @@fetch_status = 0
BEGIN
set @jujuy = 0
DELETE FROM @domicilios
insert into @domicilios
exec sp_obtener_domicilios @cuit, 0
set @domicilio = null
set @localidad = null
set @provincia = null
set @codpos = null
--select @cuit, * from @domicilios
SELECT top 1
@domicilio =
isnull(d.domicilio,''),
@localidad =
isnull(d.loc_nombre,''),
@provincia =
isnull(d.prv_nombre,''),
@codpos =
isnull(d.codpos,''),
@tipo_domicilio = isnull(d.tipo_domicilio,'')
from @domicilios d
where d.prv_nombre = 'JUJUY'
and d.domicilio not like 'RESIDE EN'
and d.domicilio not like 'DOMICILIADA EN'
order by (case
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1'
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '') then '2'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '') then '4'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '') then '8'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '') then '9'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '') then '10'
else '11'
end)
--select 'antes de entrar', @domicilio domi, @localidad local, @provincia pcia, @codpos codpos
if (isnull(@domicilio,'') != '')
begin
set @jujuy = 1
--select 'despues de entrar', @cuit, @cod_clae clae, @descripcion as des, @principal ppal,@domicilio as domi, @localidad as localidad, @provincia as pcia, @codpos as codpos
insert into CLAE_JUJUY
select @cuit, nombre_persona, @cod_clae, @descripcion, @principal, null,null,null,
@domicilio, @localidad, @provincia, @codpos
from padrones where identificacion = @cuit and habilitado = 1 and identificador in (select identificador from identificadores where country_code = 'AR')
end
else
begin
DECLARE W_CURSOR_2 CURSOR FOR
select c01_cuit_soc, car_nombre
from socios
inner join cargos on cargos.car_codigo = socios.car_codigo
-- Chequeamos que sea un cuit valido el del socio
inner join padrones p2 on (p2.identificacion = @cuit_soc and p2.habilitado = 1 and p2.identificador in (select identificador from identificadores where country_code = 'AR'))
where c01_cuit = @cuit and visible = 1 and c04_ultimo = 'S'
OPEN W_CURSOR_2
FETCH NEXT FROM W_CURSOR_2 INTO @cuit_soc, @cargo
WHILE @@fetch_status = 0
BEGIN
DELETE FROM @domicilios
insert into @domicilios
exec sp_obtener_domicilios @cuit_soc, 0
--select @cuit, @cuit_soc, * from @domicilios
set @domicilio = null
set @localidad = null
set @provincia = null
set @codpos = null
SELECT top 1
@domicilio =
isnull(d.domicilio,''),
@localidad =
isnull(d.loc_nombre,''),
@provincia =
isnull(d.prv_nombre,''),
@codpos =
isnull(d.codpos,''),
@tipo_domicilio = isnull(d.tipo_domicilio,'')
from @domicilios d
where d.prv_nombre = 'JUJUY'
and d.domicilio not like 'RESIDE EN'
and d.domicilio not like 'DOMICILIADA EN'
order by (case
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1'
when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '') then '2'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3'
when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '') then '4'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '') then '8'
WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '') then '9'
WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '') then '10'
else '11'
end)
--select 'antes de entrar SOCIO', @domicilio domi, @localidad local, @provincia pcia, @codpos codpos
if (isnull(@domicilio,'') != '')
begin
set @jujuy = 1
--select 'adentro SOCIO', @cuit, @cod_clae clae, @descripcion as des, @principal ppal, @domicilio domi, @localidad local, @provincia pcia, @codpos codpos, @cuit_soc as socio
insert into CLAE_JUJUY
select @cuit, p1.nombre_persona, @cod_clae, @descripcion, @principal,
@cuit_soc, p2.nombre_persona, @cargo,
@domicilio, @localidad, @provincia, @codpos
from padrones p1, padrones p2
where (p1.identificacion = @cuit and p1.habilitado = 1 and p1.identificador in (select identificador from identificadores where country_code = 'AR'))
and (p2.identificacion = @cuit_soc and p2.habilitado = 1 and p2.identificador in (select identificador from identificadores where country_code = 'AR'))
end
FETCH NEXT FROM W_CURSOR_2 INTO @cuit_soc, @cargo
END
CLOSE W_CURSOR_2
DEALLOCATE W_CURSOR_2
end
update CLAE_JUJUY_ultimo set cuit = @cuit, cantidad = cantidad + 1, jujuy = iif(@jujuy = 1, jujuy + 1, jujuy)
FETCH NEXT FROM W_CURSOR INTO @cuit,@cod_clae, @descripcion,@principal
END
CLOSE W_CURSOR
DEALLOCATE W_CURSOR
select @cuit as ultimo
END
/*
-- Cuando lo queremos correr todo otra vez
delete from CLAE_JUJUY
update CLAE_JUJUY_ultimo set cuit = '', cantidad = 0, jujuy = 0
*/
GO
Depends On
7Used By
No items found