if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLogRecDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[paQryLogRecDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpLogRecaudoEMAIL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[paUpLogRecaudoEMAIL]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsLogRecDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[paInsLogRecDetalle]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryLogRecDetalle] @pmNumero INT
AS
SELECT Numero,Item,IdCliente,CdAgencia,TipoDoc,NumDoc,IdCia,ValorRec,Referencia,Estado,NumRecibo,IdCiaRec,FechaRec
,FechaFact,ProcPago,MedioPago,SecRegistro,CodEntidad,CodSucursal,NumOper,NumAutoriza,CodServicio,NumLote,DescRegistro
,Comentario,IdVend,CodTarCom,TarifaCom,CdCiudad,VrFactura,VrAbonado,NomCliente,VrSaldoClie,VrSaldoAge,Vr_Afavor
FROM LogRecDetalle
WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpLogRecaudoEMAIL] @pmNumeroIni INT,@pmNumeroFin INT,@pmFechaRec SMALLDATETIME=Null
,@pmIdCliente varchar(16)=Null,@pmprofile VARCHAR(MAX),@pmrecipients VARCHAR(MAX),@pmcopy_recipients VARCHAR(MAX)
AS
DECLARE @body nvarchar(MAX)
DECLARE @xml NVARCHAR(MAX)
SET @xml = CAST(( SELECT R.Numero AS 'td','',R.FechaRec AS 'td','', FechaCrea AS 'td','', Secuencia AS 'td','', TipoCuenta AS 'td','', NumCuenta AS 'td','', TotalReg AS 'td','',
Observacion AS 'td','', TimeSys AS 'td','', IdUsuario AS 'td','', Nombre AS 'td','', NomArchivo AS 'td','', EntidadRec AS 'td',''
--Detalle
,Item AS 'td','',IdCliente AS 'td','', CdAgencia AS 'td','', ValorRec AS 'td','', Referencia AS 'td','', Estado AS 'td','', NumRecibo AS 'td','',IdCiaRec AS 'td','',
D.FechaRec AS 'td','', FechaFact AS 'td','', ProcPago AS 'td','', MedioPago AS 'td','', CodEntidad AS 'td','', CodSucursal AS 'td','', CodServicio AS 'td','',
NumLote AS 'td','', DescRegistro AS 'td','', Comentario AS 'td','', NomCliente AS 'td',''
FROM LogRecaudo AS R LEFT JOIN LogRecDetalle AS D ON R.Numero=D.Numero
WHERE R.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin AND R.FechaRec= @pmFechaRec
AND (D.IdCliente=@pmidCliente OR @pmidCliente IS NULL)
AND (CodServicio='RRC' OR CodServicio='CDEV' OR (CodServicio='RRC' AND Comentario NOT LIKE '%Cartera%')) AND D.Estado=0
--ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='TRANSACCIONES REVERSADAS DAVICASH
| NumeroReg | FecRecaudo | FechaCrea | Secuencia | TipoCuenta | NumCuenta | TotalReg | Observacion | TimeSys | IdUsuario | Nombre | NomArchivo | EntidadRec | Item | IdCliente | CdAgencia | ValorRec | Referencia | Estado | NumRecibo | IdCiaRec | FechaRec | FechaFact | ProcPago | MedioPago | CodEntidad | CodSucursal | CodServicio | NumLote | DescRegistro | Comentario | NomCliente |
'
SET @body = @body + @xml +'
'
Exec msdb.dbo.sp_send_dbmail
@profile_name = @pmprofile,
@recipients = @pmrecipients,
@copy_recipients = @pmcopy_recipients,
@subject = 'TRANSACCIONES REVERSADAS DAVICASH',
@body_format = 'html',
@body = @body;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsLogRecDetalle] @pmNumero INT,@pmItem INT,@pmIdCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmTipoDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2),@pmValorRec MONEY,@pmReferencia VARCHAR(50),@pmEstado INT,@pmNumRecibo INT
,@pmIdCiaRec CHAR(2),@pmFechaRec SMALLDATETIME,@pmFechaFact SMALLDATETIME,@pmProcPago INT,@pmMedioPago INT,@pmSecRegistro INT,@pmCodEntidad VARCHAR(10),@pmCodSucursal VARCHAR(10),@pmNumOper VARCHAR(20),@pmNumAutoriza VARCHAR(20),@pmCodServicio VARCHAR(50)
,@pmNumLote VARCHAR(10),@pmDescRegistro VARCHAR(250),@pmComentario VARCHAR(250),@pmIdVend VARCHAR(16),@pmCodTarCom VARCHAR(4),@pmTarifaCom DECIMAL(14,4),@pmCdCiudad VARCHAR(8),@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmNomCliente VARCHAR(150)
,@pmVrSaldoClie MONEY,@pmVrSaldoAge MONEY,@pmVr_Afavor MONEY
AS
INSERT INTO LogRecDetalle (Numero,Item,IdCliente,CdAgencia,TipoDoc,NumDoc,IdCia,ValorRec,Referencia,Estado,NumRecibo,IdCiaRec,FechaRec,FechaFact,ProcPago,MedioPago,SecRegistro,CodEntidad,CodSucursal,NumOper,NumAutoriza,CodServicio
,NumLote,DescRegistro,Comentario,IdVend,CodTarCom,TarifaCom,CdCiudad,VrFactura,VrAbonado,NomCliente,VrSaldoClie,VrSaldoAge,Vr_Afavor)
VALUES (@pmNumero,@pmItem,@pmIdCliente,@pmCdAgencia,@pmTipoDoc,@pmNumDoc,@pmIdCia,@pmValorRec,@pmReferencia,@pmEstado,@pmNumRecibo,@pmIdCiaRec,@pmFechaRec,@pmFechaFact,@pmProcPago,@pmMedioPago,@pmSecRegistro,@pmCodEntidad
,@pmCodSucursal,@pmNumOper,@pmNumAutoriza,@pmCodServicio,@pmNumLote,@pmDescRegistro,@pmComentario,@pmIdVend,@pmCodTarCom,@pmTarifaCom,@pmCdCiudad, @pmVrFactura,@pmVrAbonado,@pmNomCliente,@pmVrSaldoClie,@pmVrSaldoAge,@pmVr_Afavor)
GO