alter procedure usr_sp_factESfactura
--DECLARE
@fecha AS datetime= '20201111 00:00:00',
@fechaf AS datetime='20201112 00:00:00',
@sub_tip AS varchar(5)='010',
@num_doc as varchar(10)='%'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @GUIDS TABLE (rowguid VARCHAR(20))
DECLARE @base_iva AS MONEY;
DECLARE @iva AS NUMERIC=0;
DECLARE @num_docg AS VARCHAR=10;
CREATE TABLE #fact (idnumeracion VARCHAR(10),numero VARCHAR(10),idnumeracion_referencia VARCHAR(2),numero_referencia VARCHAR(2),idconceptonota VARCHAR(10),fechadocumento VARCHAR(10),horadocumento VARCHAR(10),documentoexportacion VARCHAR(2),notas VARCHAR(200),moneda VARCHAR(4),idreporte VARCHAR(200),tipocontenido VARCHAR(200),idtipopersona VARCHAR(2),responsableiva VARCHAR(2),idtipodocumentoidentidad VARCHAR(20),identificacion VARCHAR(20),digito_verificacion VARCHAR(20),
nombres VARCHAR(200),apellidos VARCHAR(200),emailcontacto VARCHAR(200),emailentrega VARCHAR(200),idciudad VARCHAR(20),direccion VARCHAR(200),codigopostal VARCHAR(20),telefono VARCHAR(200),idtiporegimen VARCHAR(3),idactividadeconomica VARCHAR(20),idciudadfiscal VARCHAR(20),direccionfiscal VARCHAR(20),codigopostalfiscal VARCHAR(20),nombrecomercial VARCHAR(20),matriculamercantil VARCHAR(200),totalbruto MONEY,baseimponible MONEY,totalbrutoconimpuestos MONEY,totalapagar MONEY,
idmetodopago_formapago VARCHAR(20),idmediopago_formapago VARCHAR(20),identificador_formapago VARCHAR(20),fechavencimiento_formapago VARCHAR(200))
INSERT INTO @GUIDS
select num_doc from inv_cabdoc WITH (NOLOCK) where sub_tip=@sub_tip and (fec_grab between @fecha and @fechaf) and num_doc like @num_doc and cliente>'0'
--Select * from @GUIDS
--
WHILE EXISTS (SELECT TOP (1) rowguid FROM @GUIDS)
BEGIN
SELECT TOP (1) @num_docg=rowguid FROM @GUIDS
set @base_iva=0
set @iva=0
select TOP (1) @base_iva=SUM(inv.base_iva_aiu) from inv_cuedoc inv WITH (NOLOCK) INNER JOIN @GUIDS i on i.rowguid=inv.num_doc group by num_doc
select TOP (1) @iva=SUM(inv.mon_iva) from inv_cuedoc inv WITH (NOLOCK) INNER JOIN @GUIDS i on i.rowguid=inv.num_doc group by num_doc
----- si tiene iva y no tiene base, el iva es por todo el subtotal; si no tiene iva ni base es examen; si tiene iva y base es de nomina.
IF @iva>0 and @base_iva=0
BEGIN
set @base_iva=0
set @iva=0
Select TOP (1) @base_iva=SUM(inv.pre_vta) from inv_cuedoc inv WITH (NOLOCK) INNER JOIN @GUIDS i on i.rowguid=inv.num_doc group by num_doc
END
ELSE
IF @iva=0 and @base_iva=0
BEGIN
set @base_iva=0
set @iva=0
Select TOP (1) @base_iva=0 from inv_cuedoc inv WITH (NOLOCK) INNER JOIN @GUIDS i on i.rowguid=inv.num_doc
END
ELSE
IF @iva>0 and @base_iva>0
BEGIN
set @base_iva=0
set @iva=0
Select TOP (1) @base_iva=SUM(inv.base_iva_aiu) from inv_cuedoc inv WITH (NOLOCK) INNER JOIN @GUIDS i on i.rowguid=inv.num_doc group by num_doc
END
IF @sub_tip='01080'
BEGIN
INSERT INTO #fact
select TOP (1) CASE SUBSTRING(invc.num_doc,1,4 ) WHEN 'MEDE' THEN '1376' WHEN 'BTAE' THEN '1386' WHEN 'CART' THEN '1385' END AS idnumeracion,SUBSTRING(invc.num_doc,5,6 ) AS numero,'0' AS idnumeracion_referencia,'0' AS numero_referencia,'0' AS idconceptonota, (concat(DATEPART(YEAR,invc.fec_grab),'-' ,DATEPART(MOnTH,invc.fec_grab),'-' , DATEPART(DAY,invc.fec_grab))) AS fechadocumento,concat(DATEPART(HOUR,invc.fec_grab), ':' ,DATEPART(MinUTE,invc.fec_grab),':' , DATEPART(SECOnD,invc.fec_grab)) AS horadocumento,
'0' AS documentoexportacion,RTRIM(obs.NOTA) AS notas,'COP' AS moneda,'1007' AS idreporte,'' AS tipocontenido,'2' AS idtipopersona,'0' AS responsableiva,case cli.tip_ide WHEN '10' THEN '31' WHEN '01' THEN '13' WHEN '02' THEN '22' END AS idtipodocumentoidentidad,cli.nit_cli AS identificacion,dig_ver AS digito_verificacion,
REPLACE(REPLACE(nom_cli,'.',''),'ñ','ñ') AS nombres,'' AS apellidos,CASE RTRIM(cli.e_mail) WHEN NULL THEN 'contabilidad@saitempsa.com' WHEN '' THEN 'contabilidad@saitempsa.com' ELSE RTRIM(cli.e_mail) END AS emailcontacto,CASE RTRIM(cli.e_mail) WHEN NULL THEN 'contabilidad@saitempsa.com' WHEN '' THEN 'contabilidad@saitempsa.com' ELSE RTRIM(cli.e_mail) END AS emailentrega,CASE cli.cod_ciu WHEN '05896' THEN '05631' ELSE cli.cod_ciu END AS idciudad,RTRIM(cli.di1_cli) AS direccion,'05001' AS codigopostal,RTRIM(REPLACE(cli.te1_cli,'/','')) AS telefono,CASE cli.tip_cli WHEN '2' THEN '48' WHEN '3' THEN '49' WHEN '1' THEN '48' END AS idtiporegimen,RTRiM(ter.cod_act) AS idactividadeconomica,
'' AS idciudadfiscal,'' AS direccionfiscal,'' AS codigopostalfiscal,'' AS nombrecomercial,'' AS matriculamercantil,SUM(inv.pre_vta) AS totalbruto,@base_iva AS baseimponible,(sum(inv.pre_vta)+sum(inv.mon_iva)) AS totalbrutoconimpuestos,(sum(inv.pre_vta)+sum(inv.mon_iva)) AS totalapagar,'2' AS idmetodopago_formapago,'2' AS idmediopago_formapago, '1' AS identificador_formapago, (concat(DATEPART(YEAR,invc.fec_ven),'-' ,DATEPART(MOnTH,invc.fec_ven),'-' , DATEPART(DAY,invc.fec_ven))) AS fechavencimiento_formapago --,convert(decimal,sum(inv.val_tot)-sum(inv.mon_ret)-sum(inv.ret_iva)) AS totalapagar
from @GUIDS a
--inner join cnt_cabdoc cnt WITH (NOLOCK) on cnt.num_doc=a.rowguid and cnt.sub_tip=@sub_tip
inner Join cxc_cabdoc cxc WITH (NOLOCK) on cxc.num_doc=a.rowguid and cxc.sub_tip=@sub_tip
inner Join cxc_cliente cli WITH (NOLOCK) on cli.cod_cli=cxc.cliente
inner Join inv_cuedoc inv WITH (NOLOCK) on inv.num_doc=a.rowguid and inv.sub_tip=@sub_tip
inner Join inv_cabdoc invc WITH (NOLOCK) on invc.num_doc=a.rowguid and invc.sub_tip=@sub_tip
inner Join gen_terceros ter WITH (NOLOCK) on ter.ter_nit=cli.nit_cli
--inner join @GUIDS a on a.rowguid=cnt.num_doc
left Join (select distinct CONVERT(DATE,fec_cte) fec_cte, CONCAT('PERIODO DE ',CONVERT(DATE,p.fec_ini) , ' A ' ,CONVERT(DATE,p.fec_fin),'; CLIENTE: ',c.nom_conv,'; Centro Costo: ',v.conv_cco_des) NOTA ,num_fact,h.cod_conv,c.cod_tlq from rhh_liqhis h WITH (NOLOCK)INNER JOIN rhh_Convenio c WITH (NOLOCK) ON c.cod_conv=h.cod_conv
INNER JOIN rhh_pertlq p WITH (NOLOCK) ON p.fec_fin=h.fec_cte and c.cod_tlq=p.cod_tlq --NUEVO para centro de costo 20200311
INNER JOIN V_Rhh_ConvCl8 v WITH (NOLOCK) ON h.conv_estruc = v.conv_estruc
INNER JOIN @GUIDS r ON r.rowguid=h.num_fact
) obs on obs.num_fact=a.rowguid
where invc.num_doc = a.rowguid
group by invc.num_doc,invc.fec_grab,invc.fec_grab,invc.fec_grab,invc.fec_grab,invc.fec_grab,invc.fec_grab,cli.tip_ide,cli.nit_cli,dig_ver,nom_cli,cli.ap1_cli,cli.e_mail,cli.e_mail,cli.cod_ciu,cli.di1_cli,cli.te1_cli,cli.tip_cli,ter.cod_act,invc.fec_ven,
cli.age_ret,obs.NOTA
END
ELSE
BEGIN
-- ORIGINAL para 010
INSERT INTO #fact
select TOP (1) CASE SUBSTRING(cnt.num_doc,1,4 ) WHEN 'MEDE' THEN '1376' WHEN 'BTAE' THEN '1386' WHEN 'CART' THEN '1385' END AS idnumeracion,SUBSTRING(cnt.num_doc,5,6 ) AS numero,'0' AS idnumeracion_referencia,'0' AS numero_referencia,'0' AS idconceptonota, (concat(DATEPART(YEAR,cnt.fec_grab),'-' ,DATEPART(MOnTH,cnt.fec_grab),'-' , DATEPART(DAY,cnt.fec_grab))) AS fechadocumento,concat(DATEPART(HOUR,cnt.fec_grab), ':' ,DATEPART(MinUTE,cnt.fec_grab),':' , DATEPART(SECOnD,cnt.fec_grab)) AS horadocumento,
'0' AS documentoexportacion,RTRIM(cnt.det_doc) AS notas,'COP' AS moneda,'1007' AS idreporte,'' AS tipocontenido,'2' AS idtipopersona,'0' AS responsableiva,case cli.tip_ide WHEN '10' THEN '31' WHEN '01' THEN '13' WHEN '02' THEN '22' END AS idtipodocumentoidentidad,cli.nit_cli AS identificacion,dig_ver AS digito_verificacion,
REPLACE(REPLACE(nom_cli,'.',''),'ñ','ñ') AS nombres,'' AS apellidos,CASE RTRIM(cli.e_mail) WHEN NULL THEN 'contabilidad@saitempsa.com' WHEN '' THEN 'contabilidad@saitempsa.com' ELSE RTRIM(cli.e_mail) END AS emailcontacto,CASE RTRIM(cli.e_mail) WHEN NULL THEN 'contabilidad@saitempsa.com' WHEN '' THEN 'contabilidad@saitempsa.com' ELSE RTRIM(cli.e_mail) END AS emailentrega,CASE cli.cod_ciu WHEN '05896' THEN '05631' ELSE cli.cod_ciu END AS idciudad,RTRIM(cli.di1_cli) AS direccion,'05001' AS codigopostal,RTRIM(REPLACE(cli.te1_cli,'/','')) AS telefono,CASE cli.tip_cli WHEN '2' THEN '48' WHEN '3' THEN '49' WHEN '1' THEN '48' END AS idtiporegimen,RTRiM(ter.cod_act) AS idactividadeconomica,
'' AS idciudadfiscal,'' AS direccionfiscal,'' AS codigopostalfiscal,'' AS nombrecomercial,'' AS matriculamercantil,SUM(inv.pre_vta) AS totalbruto,@base_iva AS baseimponible,(sum(inv.pre_vta)+sum(inv.mon_iva)) AS totalbrutoconimpuestos,(sum(inv.pre_vta)+sum(inv.mon_iva)) AS totalapagar,'2' AS idmetodopago_formapago,'2' AS idmediopago_formapago, '1' AS identificador_formapago, (concat(DATEPART(YEAR,invc.fec_ven),'-' ,DATEPART(MOnTH,invc.fec_ven),'-' , DATEPART(DAY,invc.fec_ven))) AS fechavencimiento_formapago --,convert(decimal,sum(inv.val_tot)-sum(inv.mon_ret)-sum(inv.ret_iva)) AS totalapagar
from cnt_cabdoc cnt WITH (NOLOCK)
inner Join cxc_cabdoc cxc WITH (NOLOCK) on cxc.num_doc=cnt.num_doc and cxc.sub_tip=cnt.sub_tip
inner Join cxc_cliente cli WITH (NOLOCK) on cli.cod_cli=cxc.cliente
inner Join inv_cuedoc inv WITH (NOLOCK) on inv.num_doc=cnt.num_doc
inner Join inv_cabdoc invc WITH (NOLOCK) on invc.num_doc=cnt.num_doc
inner Join gen_terceros ter WITH (NOLOCK) on ter.ter_nit=cli.nit_cli
inner join @GUIDS a on a.rowguid=invc.num_doc
where cnt.num_doc = a.rowguid
group by cnt.num_doc,cnt.fec_grab,cnt.fec_grab,cnt.fec_grab,cnt.fec_grab,cnt.fec_grab,cnt.fec_grab,cnt.det_doc,cli.tip_ide,cli.nit_cli,dig_ver,nom_cli,cli.ap1_cli,cli.e_mail,cli.e_mail,cli.cod_ciu,cli.di1_cli,cli.te1_cli,cli.tip_cli,ter.cod_act,invc.fec_ven,
cli.age_ret
END
DELETE TOP (1) FROM @GUIDS
END
Select * from #fact --ORDER BY idnumeracion,numero
DROP TABLE #fact
END
