· 8 years ago · Dec 20, 2017, 12:48 PM
1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spucc_oCtaCteStandard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2drop procedure [dbo].[spucc_oCtaCteStandard]
3GO
4
5SET QUOTED_IDENTIFIER OFF
6GO
7SET ANSI_NULLS OFF
8GO
9CREATE PROCEDURE spucc_oCtaCteStandard
10 @pcNroTarjeta VARCHAR(20),
11 @piIdProtocolo INT,
12 @piIdMensaje INT,
13 @piIdProducto INT = NULL,
14 @piIdComercio INT = 0,
15 @piIdTerminal INT = 0,
16 @piIdSucursal INT = 0,
17 @piIdMoneda INT = 32,
18 @pbClienteInputMode BIT = 1,
19 @pfMonto DECIMAL(9,2)=NULL,
20 @piCuotas INT = 1,
21 @pdTRXFechaHora SMALLDATETIME=NULL,
22 @piTRXNroTicket INT = 0,
23 @piPOSNroTicket INT = NULL,
24 @piPOSNroZ INT = NULL,
25 @piPOSUser INT = NULL,
26 @piPOSSupervisor INT = NULL,
27 @piTrxModoAutorizacion INT = 1,
28 @piMesInicio INT = NULL,
29 @piAnioInicio INT = NULL,
30 @piCodSeg INT = NULL,
31 @pcPIN VARCHAR(30) = NULL,
32 @piRRN INT = NULL,
33 @pcMotivo VARCHAR(150)=NULL,
34 @piIdPersAutoriza INT=NULL,
35 @pbRetornoSelect BIT = 1,
36 @pdFechaFaltante SMALLDATETIME = NULL
37
38as
39
40set nocount on
41
42/*
43 Descripcion:
44 SP. que trabaja con las POS de TIPRE para el manejo de las cuentas corrientes de clientes
45
46 AYUDA:
47
48 Operaciones
49 @OP_CONSULTASALDO = 1000
50 @OP_CONSULTAULTOPS = 1100
51 @OP_CONSULTAULTLIQ = 1200
52 @OP_COMPRA = 2000
53 @OP_ADELANTO = 2100
54 @OP_PAGO = 3000
55 @OP_REVERSA = 9000
56*/
57
58-- Area Constantes
59
60DECLARE @OK int,
61 @errorPROTOCOLO_INVALIDO int,
62 @errorMENSAJE_INVALIDO int,
63 @errorPRODUCTO_INVALIDO int,
64 @errorREVERSA_INVALIDA int,
65 @errorMONEDA_INVALIDA int,
66 @errorTARJETA_NOHABILITADA int,
67 @errorCLIENTE_INHABILITADO int,
68 @errorPRODUCTOCLIENTE_OUTDATED int,
69 @errorPERSONA_INHABILITADA int,
70 @errorPERSONA_OUTDATED int,
71 @errorNOPUEDETENER_FALTANTES int,
72 @errorOPERACION_NO_PERMITIDA int,
73 @errorCOMERCIO_INHAB_NOEXIS int,
74 @errorSUCURSAL_INHAB_NOEXIS int,
75 @errorTERMINAL_INHAB int,
76 @errorCOMERCIO_INHAB_NOSIS int,
77 @errorCLIENTE_EN_MORA int,
78 @errorNO_CUOTAS int,
79 @errorSIN_DISPONIBLE int,
80 @errorNO_HAY_DEUDA int,
81 @errorMONTOPAGO_SUPERA_DEUDA int,
82 @errorINSERCION int,
83 @errorUPD_ACUMULADOS int,
84 @errorOPERACION_SPROCNOPERMITIDA int,
85 @errorFALTAPARAMANIOMES int,
86 @errorNOCALENDARIOPARAOP int,
87 @errorMONTOPAGO_INCORRECTO int,
88 @errorPLAN_INVALIDO int,
89 @errorTRANSACCION_DUPLICADA int,
90 @errorTARJETADNI_INVALIDOS int,
91 @errorMONTO_INCORRECTO int,
92 @cEANtj char(3)
93
94--ATENCION: Rango valido permitido 0, -999 (los otros en otros sprocs!)
95SET @OK = 0
96SET @errorPROTOCOLO_INVALIDO = -1
97SET @errorMENSAJE_INVALIDO = -2
98SET @errorPRODUCTO_INVALIDO = -3
99SET @errorREVERSA_INVALIDA = -4
100SET @errorMONEDA_INVALIDA = -7
101SET @errorTARJETA_NOHABILITADA = -8
102SET @errorCLIENTE_INHABILITADO = -9
103SET @errorPRODUCTOCLIENTE_OUTDATED = -10
104SET @errorPERSONA_INHABILITADA = -11
105SET @errorPERSONA_OUTDATED = -12
106SET @errorNOPUEDETENER_FALTANTES = -13
107SET @errorOPERACION_NO_PERMITIDA = -14
108SET @errorCOMERCIO_INHAB_NOEXIS = -15
109SET @errorSUCURSAL_INHAB_NOEXIS = -16
110SET @errorTERMINAL_INHAB = -17
111SET @errorCOMERCIO_INHAB_NOSIS = -18
112SET @errorCLIENTE_EN_MORA = -19
113SET @errorNO_CUOTAS = -20
114SET @errorSIN_DISPONIBLE = -21
115SET @errorNO_HAY_DEUDA = -22
116SET @errorMONTOPAGO_SUPERA_DEUDA = -23
117SET @errorINSERCION = -24
118SET @errorUPD_ACUMULADOS = -25
119SET @errorOPERACION_SPROCNOPERMITIDA = -26
120SET @errorFALTAPARAMANIOMES = -27
121SET @errorNOCALENDARIOPARAOP = -28
122SET @errorMONTOPAGO_INCORRECTO = -29
123SET @errorPLAN_INVALIDO = -30
124SET @errorTRANSACCION_DUPLICADA = -31
125SET @errorTARJETADNI_INVALIDOS = -32
126SET @errorMONTO_INCORRECTO = -33
127
128--Mensajes../Operaciones
129declare @OP_CONSULTASALDO int,
130 @OP_CONSULTAULTOPS int,
131 @OP_CONSULTAULTLIQ int,
132 @OP_COMPRA int,
133 @OP_ADELANTO int,
134 @OP_PAGO int,
135 @OP_PAGO_PRESTAMO INT,
136 @OP_REVERSA int,
137 @OP_FALTANTE_CAJA int,
138 @OP_FALTANTE_ARQ INT,
139 @OP_ANULACION int,
140 @OP_PRESTAMO INT,
141 @OP_GASTOSRESUMEN int,
142 @OP_DESCUENTOS int,
143 @OP_INTERESES_FINANCIEROS int,
144 @OP_INTERESESPUNITORIOS int
145
146set @OP_CONSULTASALDO = 1000
147set @OP_CONSULTAULTOPS = 1100
148set @OP_CONSULTAULTLIQ = 1200
149set @OP_COMPRA = 2000
150set @OP_ADELANTO = 2100
151set @OP_PAGO = 3000
152SET @OP_PAGO_PRESTAMO = 3100
153set @OP_REVERSA = 9000
154set @OP_FALTANTE_CAJA = 10100
155set @OP_FALTANTE_ARQ = 10500
156set @OP_ANULACION = 10200
157set @OP_PRESTAMO = 2200
158
159-- @OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PAGO,@OP_PAGO_PRESTAMO,@OP_REVERSA,@OP_FALTANTE_CAJA,@OP_FALTANTE_ARQ,@OP_ANULACION,@OP_PRESTAMO
160
161select @cEANtj=LTRIM(cont) from parsis where codi='EANTARCLUB'
162
163-- Variables para Resultados
164DECLARE @cCodigo CHAR(13)
165 , @cDni varchar(15)
166 , @cCod_Oper char(10)
167 , @cCod_Ant char(10)
168 , @nNeto_Ant Numeric(20,2)
169 , @nSal_Ant Numeric(20,2)
170 , @nSaldoUsado NUMERIC(20,2)
171 , @nSaldoUsadoTj NUMERIC(20,2)
172 , @SaldoDisponible NUMERIC(20,2)
173 , @nSaldoUsadoAdel NUMERIC(20,2)
174 , @nRestarAlFinal NUMERIC(20,2)
175 , @dFechaMasVieja DATETIME
176 , @dFechaMasViejaPermitida DATETIME
177 , @cError varchar(100)
178 , @iError int
179 , @cBigError varchar(7000)
180 , @cNombre char(50)
181 , @cDomicilio char(50)
182 , @cApellido CHAR(50)
183 , @iCtaCte int
184 , @iCtaCteHabilitada int
185 , @cNomTarjeta CHAR(50)
186 , @nLimiteCuenta NUMERIC(20,2)
187 , @nLimiteTj NUMERIC(20,2)
188 , @nSaldoAlCierre NUMERIC(20,2)
189 , @nSaldoLiquidacion NUMERIC(20,2)
190 , @dVctoLiquidacion DATETIME
191 , @nImporteLiquidacion NUMERIC(18,2)
192 , @iNroCierre INT
193 , @dFechaEmision SMALLDATETIME
194 , @fImporteSaldoAnterior NUMERIC(18,2)
195 , @fImporteTotalConsumo NUMERIC(18,2)
196 , @fImporteTotalPagos NUMERIC(18,2)
197 , @fImporteSaldoActual NUMERIC(18,2)
198 , @nCantMovimientos SMALLINT
199 , @cFecha char(8)
200 , @cMensaje char(22)
201 , @cMonto char(10)
202 , @cLinea varchar(80)
203 , @nVuelta TINYINT
204 , @cMensajeLiq char(40)
205 , @cComprobante CHAR(6)
206
207-- Valores por Defecto
208set @cBigError = '..'
209set @SaldoDisponible = 0
210set @cNombre = ''
211set @cApellido = ''
212set @cCod_Oper = ''
213
214-- Variables para movimientos y calculos
215DECLARE @C_COD_SUC CHAR(4)
216 , @Fechaoper datetime
217 , @dFechaVcto DATETIME
218 , @nCuotas SMALLINT
219 , @nValorCuota NUMERIC(18,2)
220 , @nAcumPrestamo NUMERIC(18,2)
221
222-- Variables para liquidaciones
223DECLARE @nId_grupo_CC SMALLINT , @nAnticipo_CC NUMERIC(18,2) , @nPrestamo_CC NUMERIC(18,2) , @nCuotas_CC NUMERIC(18,2)
224SET @nId_grupo_CC = 0
225SET @nAnticipo_CC = 0
226SET @nPrestamo_CC = 0
227SET @nCuotas_CC = 0
228SET @fImporteSaldoAnterior = 0
229SET @fImporteTotalConsumo = 0
230SET @fImporteTotalPagos = 0
231SET @fImporteSaldoActual = 0
232SET @nRestarAlFinal = 0
233
234SET @iError = 0
235
236SET @C_COD_SUC = STR(@piIdSucursal,4)
237
238IF ISNULL(@C_COD_SUC,' 0') = ' 0'
239 SELECT @C_COD_SUC = STR(CONVERT(INT, CONT), 4) FROM PARSIS WHERE CODI = 'NRO.SUCURS'
240
241
242if @pdTRXFechaHora is null
243set @fechaoper = convert(datetime,convert(char(10),getdate(),103),103)
244else
245set @fechaoper = convert(datetime,convert(char(10),@pdTRXFechaHora,103),103)
246
247--===================================================================================================================
248-- CONTROLES STANDARD
249--===================================================================================================================
250
251---------------------------------------------------------------------------------------------------------------------
252-- Buscamos el codigo de Cliente de Agenda
253---------------------------------------------------------------------------------------------------------------------
254IF @cEANtj <> LEFT(LTRIM(@pcNroTarjeta),3) -- YA VIENE EL NUMERO DE CUENTA
255 SELECT @cCodigo = Codigo, @cDni = REPLACE(ISNULL(DOCUM,''),'.','') , @cNomTarjeta = RAZO
256 , @nLimiteTj = 0
257 FROM Agenda WHERE Codigo = STR(CONVERT(NUMERIC(13), @pcNroTarjeta),13)
258ELSE
259begin
260 SELECT @cCodigo = cCodigo,
261 @cDni = CASE WHEN RIGHT(tj.cComentario,10)<>'' AND RIGHT(tj.cComentario,10)<>' 0'
262 THEN RIGHT(tj.cComentario,10) ELSE
263 REPLACE(ISNULL(DOCUM,''),'.','') END
264 , @cNomTarjeta = LEFT(tj.cComentario,40)
265 , @nLimiteTj = nLimiteCredito
266 FROM socioclubtj tj,Agenda ag WHERE nTarjeta = @pcNroTarjeta and tj.ccodigo=ag.codigo
267END
268---------------------------------------------------------------------------------------------------------------------
269-- Verifica cliente Inexistente
270---------------------------------------------------------------------------------------------------------------------
271if @cCodigo is null
272begin
273 set @iError = @errorPERSONA_INHABILITADA ; set @cError = 'CLIENTE INEXISTENTE.'
274
275 -- Bambu
276 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
277 BEGIN
278 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
279 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
280 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
281 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
282 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
283 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
284 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
285 END
286 ELSE -- Tipre
287 BEGIN
288 SELECT @iError AS iError,@cError AS cError
289 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
290 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
291 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
292 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
293 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
294 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
295 END
296 RETURN 1
297end
298
299---------------------------------------------------------------------------------------------------------------------
300-- DNI Invalido
301-- Chequea si Pegassus, controla DNI
302-- Esto lo hace solo para COMPRAS Y ADELANTOS
303---------------------------------------------------------------------------------------------------------------------
304if exists(select cont from parsis where codi='CONTROLDNI' AND cont='T')
305AND ( @piIdMensaje = @OP_COMPRA OR @piIdMensaje = @OP_ADELANTO OR @piIdMensaje = @OP_PAGO )
306and @cDni <> '' and @PcPin <> '' and ltrim(@cDni) <> @PcPin
307begin
308 set @iError = @errorPERSONA_INHABILITADA ; set @cError = 'NO CORRESPONDE DNI ('+ltrim(@PcPin)+').'
309
310 -- Bambu
311 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
312 BEGIN
313 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
314 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
315 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
316 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
317 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
318 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
319 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
320 END
321 ELSE -- Tipre
322 BEGIN
323 SELECT @iError AS iError,@cError AS cError
324 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
325 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
326 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
327 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
328 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
329 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
330 END
331 RETURN 1
332end
333
334
335---------------------------------------------------------------------------------------------------------------------
336-- Monto de la operacion actual (interpretación del signo)
337---------------------------------------------------------------------------------------------------------------------
338IF ( @pfMonto is null ) OR @piIdMensaje = 1000 OR @piIdMensaje = 1100 OR @piIdMensaje = 1200
339 set @pfMonto = 0
340else
341begin
342 set @pfMonto =
343 case
344 when @piIdMensaje = @OP_COMPRA then abs(@pfMonto)
345 when @piIdMensaje = @OP_ADELANTO then abs(@pfMonto)
346 when @piIdMensaje = @OP_PAGO then abs(@pfMonto)*(-1)
347 when @piIdMensaje = 9000 then abs(@pfMonto)*
348 isnull((select sign(neto)*(-1) from compprim where cod_oper = @cCod_Oper ),0)
349 else NULL
350 end
351end
352
353-- Operacion Invalida
354if @pfMonto is null OR ( ISNULL(@pfMonto,0) = 0 AND @piIdMensaje IN (@OP_COMPRA,@OP_ADELANTO,@OP_PAGO))
355begin
356 set @iError = @errorMENSAJE_INVALIDO ; set @cError = 'OPERACION INVALIDA.'
357 SELECT @iError AS iError,@cError AS cError
358 RETURN 1
359end
360
361
362
363--===================================================================================================================
364-- BUSCAMOS DATOS DE ACUERDO A LA OPERACION
365--===================================================================================================================
366
367---------------------------------------------------------------------------------------------------------------------
368-- Buscamos los datos del tipo de liquidacion y los limites de anticipos y prestamos
369---------------------------------------------------------------------------------------------------------------------
370IF @piIdMensaje IN (@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
371BEGIN
372 SELECT @nId_grupo_CC = ISNULL(nId_Grupo,0) ,
373 @nAnticipo_CC = ISNULL(nAnticipo_CC,0) ,
374 @nPrestamo_CC = ISNULL(nPrestamo_CC,0) ,
375 @nCuotas_CC = ISNULL(nCuotas_CC,0)
376 FROM perfprov WHERE codigo = @cCodigo
377END
378
379---------------------------------------------------------------------------------------------------------------------
380-- Si hay PRESTAMO, es una OP_ADELANTO, y coincide el importe con el prestamo --> Cambia a OP_PRESTAMO
381---------------------------------------------------------------------------------------------------------------------
382IF @piIdMensaje = @OP_ADELANTO AND @pfMonto = @nPrestamo_CC AND @nPrestamo_CC>0
383 SET @piIdMensaje = @OP_PRESTAMO
384
385---------------------------------------------------------------------------------------------------------------------
386-- Busca vcto y saldo de la ultima liquidacion
387---------------------------------------------------------------------------------------------------------------------
388IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
389BEGIN
390
391 /* cambio por lo que sigue, por problemas al tener 2 liquidaciones
392 SELECT TOP 1 @dVctoLiquidacion = dFecha_vcto
393 , @nSaldoLiquidacion = nSafa
394 , @iNroCierre = nId_liquidacion
395 , @dFechaEmision = dFecha
396 , @fImporteSaldoAnterior = nSaldoAnterior
397 FROM VIEW_ctasCtes Vc
398 LEFT JOIN relfaman rf ON vc.cod_oper = rf.oper_mand
399 LEFT JOIN ctasctes_liquidaciones liq ON rf.oper_fact = liq.cCod_oper
400 WHERE vc.codigo = @cCodigo
401 AND liq.nId_liquidacion IS NOT NULL
402 ORDER BY dFecha_vcto DESC */
403
404 SELECT TOP 1 @nSaldoLiquidacion = (SELECT SUM(nsafa) FROM ctasctes_liquidaciones liq WHERE liq.Codigo=@cCodigo )
405 , @dVctoLiquidacion = dFecha_Vcto
406 , @dFechaEmision = dFecha
407 , @iNroCierre = nId_liquidacion
408 , @fImporteSaldoAnterior = nSaldoAnterior
409 , @nImporteLiquidacion = nImporte
410 FROM ctasctes_liquidaciones liq
411 WHERE liq.Codigo=@cCodigo
412 ORDER BY dFecha DESC
413
414 SET DATEFORMAT DMY
415 SET @dVctoLiquidacion = ISNULL(@dVctoLiquidacion,'31-12-2099')
416 SET @nSaldoLiquidacion = ISNULL(@nSaldoLiquidacion,0)
417 SET @fImporteSaldoAnterior = ISNULL(@fImporteSaldoAnterior,0)
418 SET @nImporteLiquidacion = ISNULL(@nImporteLiquidacion,0)
419
420 -- Saldo de la ultima liquidacion lo pasa a variable para devolver
421 SET @nSaldoAlCierre = @nSaldoLiquidacion
422
423
424 -- Si la liquidacion no existe, buscamos el saldo al cierre del mes anterior.
425 IF @dVctoLiquidacion = '31-12-2099'
426 BEGIN
427 SELECT @nSaldoAlCierre = ISNULL( SUM( CASE WHEN fech_vcto <= DATEADD(dd,-1,CONVERT(DATETIME,'01'+RIGHT(CONVERT(CHAR(10),getdate(),103),8),103))
428 THEN safa ELSE 0 END) , 0)
429 FROM CTASSALD CS
430 LEFT JOIN compprim co ON co.cod_oper = cs.cod_oper
431 WHERE codigo = @cCodigo and safa <> 0
432 END
433
434 -- Ver los datos obtenidos
435 /*SELECT @dVctoLiquidacion AS dVctoLiquidacion,@nSaldoLiquidacion AS nSaldoLiquidacion, @fImporteSaldoAnterior AS fImporteSaldoAnterior, @nSaldoAlCierre AS nSaldoAlCierre*/
436
437END
438
439---------------------------------------------------------------------------------------------------------------------
440-- Busca fecha mas vieja y saldo usado de la CUENTA para las operaciones abiertas sin liquidacion
441-- Saldo Usado de Adelantos
442---------------------------------------------------------------------------------------------------------------------
443IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
444BEGIN
445 -- Primero traemos la fecha más vieja sin tener en cuenta los recibos
446 SELECT @dFechaMasVieja = ISNULL(MIN(fech_vcto),GETDATE())
447 FROM VIEW_ctassald Vc
448 LEFT JOIN relfaman rf ON vc.cod_oper = rf.oper_mand
449 LEFT JOIN ctasctes_liquidaciones liq ON rf.oper_fact = liq.cCod_oper
450 WHERE vc.codigo = @cCodigo
451 AND liq.nId_liquidacion IS NULL
452 AND vc.cod_comp <>'RECPOS'
453
454 -- Luego traemos los saldos usados de la cuenta de compra y la de adelantos, teniendo en cuenta los recibos
455 /*SELECT @nSaldoUsado = ISNULL(SUM(CASE WHEN vc.cod_comp='ADELCC' THEN 0 ELSE
456 CASE WHEN (@nSaldoLiquidacion > 0 AND safa > 0) OR @nSaldoLiquidacion=0 THEN safa ELSE 0 END
457 END),0)
458 , @nSaldoUsadoAdel = ISNULL(SUM(CASE WHEN vc.cod_comp='ADELCC' THEN CASE WHEN (@nSaldoLiquidacion > 0 AND safa > 0) OR @nSaldoLiquidacion=0 THEN safa ELSE 0 END
459 ELSE 0 END),0) */ -- 13/6/14 anulo porque no tomaba las liq negativas
460 SELECT @nSaldoUsado = ISNULL(SUM(CASE WHEN vc.cod_comp='ADELCC' THEN 0 ELSE safa END),0)
461 , @nSaldoUsadoAdel = ISNULL(SUM(CASE WHEN vc.cod_comp='ADELCC' THEN safa ELSE 0 END),0)
462 FROM VIEW_ctassald Vc
463 LEFT JOIN relfaman rf ON vc.cod_oper = rf.oper_mand
464 LEFT JOIN ctasctes_liquidaciones liq ON rf.oper_fact = liq.cCod_oper
465 WHERE vc.codigo = @cCodigo
466 AND liq.nId_liquidacion IS NULL
467
468 /* -- No ponemos esto, con el criterio de no liberar nuevamente el saldo de la cuenta luego de la liq "ficticia" de fin de mes
469 -- Si no usamos liquidacion, en saldo cierre tomamos el saldo al fin del mes anterior, por lo tanto, restamos saldo cierre al usado.
470 IF @dVctoLiquidacion = '31-12-2099'
471 BEGIN
472 SET @nSaldoUsado = @nSaldoUsado - @nSaldoAlCierre
473 END
474 */
475 IF @dVctoLiquidacion = '31-12-2099'
476 BEGIN
477 SET @nRestarAlFinal = @nSaldoAlCierre
478 END
479
480END
481
482---------------------------------------------------------------------------------------------------------------------
483-- Busca el saldo usado de la tarjeta
484---------------------------------------------------------------------------------------------------------------------
485IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
486BEGIN
487 --SELECT @nSaldoUsadoTj = ISNULL(SUM( CASE WHEN (@nSaldoLiquidacion > 0 AND safa > 0) OR @nSaldoLiquidacion=0 THEN safa ELSE 0 END ),0)
488 SELECT @nSaldoUsadoTj = ISNULL(SUM( safa ),0)
489 FROM (
490 SELECT fech_vcto,safa,ISNULL(SUBSTRING(vc.descri,14,13),(
491 SELECT TOP 1 STR(nTarjeta,13) FROM socioclubtj WHERE cCodigo=@cCodigo order by nTarjeta
492 )) AS cTarjeta
493 FROM VIEW_ctassald Vc
494 LEFT JOIN relfaman rf ON vc.cod_oper = rf.oper_mand
495 LEFT JOIN ctasctes_liquidaciones liq ON rf.oper_fact = liq.cCod_oper
496 LEFT JOIN (SELECT * FROM socioclubtj WHERE cCodigo=@cCodigo) tj ON SUBSTRING(vc.descri,14,13) = STR(tj.nTarjeta,13)
497 where vc.codigo = @cCodigo and vc.safa <> 0 AND liq.nId_liquidacion IS NULL AND vc.cod_comp<>'ADELCC') cc
498 WHERE cTarjeta = @pcNroTarjeta
499END
500
501---------------------------------------------------------------------------------------------------------------------
502-- Para todos, traemos los datos de la agenda
503---------------------------------------------------------------------------------------------------------------------
504SELECT @iCtaCte = ISNULL(ag.ctacte,0)
505 , @iCtaCteHabilitada = CONVERT(tinyint, ISNULL(ag.habilit,0))
506 , @dFechaMasViejaPermitida = DATEADD(dd, ISNULL( ( CASE WHEN ag.nLimDiasVcto = -1 THEN
507 (SELECT CONVERT(Numeric(8),cont) FROM parsis WHERE codi='DIASVCTOCC')
508 ELSE ag.nLimDiasVcto END *(-1) ), 30) , GETDATE())
509 , @SaldoDisponible = 0
510 , @nLimiteCuenta = ag.limite
511 , @cNombre = SUBSTRING(ag.razo,CHARINDEX(' ',ag.razo)+1,50)
512 , @cApellido = LEFT(ag.razo,CHARINDEX(' ',ag.razo)-1)
513 , @cDomicilio = ISNULL(ag.dire,'')
514FROM agenda ag
515WHERE codigo = @cCodigo
516
517
518-- Si no es el titular, ponemos los datos del adicional
519IF LEFT(@cApellido+' '+@cNombre+SPACE(40),40) <> @cNomTarjeta AND @cNomTarjeta <>' '
520BEGIN
521 SET @cNombre = SUBSTRING(@cNomTarjeta,CHARINDEX(' ',@cNomTarjeta)+1,50)
522 SET @cApellido = LEFT(@cNomTarjeta,CHARINDEX(' ',@cNomTarjeta)-1)
523END
524
525---------------------------------------------------------------------------------------------------------------------
526-- Pone el limite de cuenta de acuerdo al limite que tenga en la cuenta y en la tarjeta
527---------------------------------------------------------------------------------------------------------------------
528SET @nLimiteCuenta = CASE WHEN @nLimiteCuenta = 0 THEN 9999.99 ELSE @nLimiteCuenta END
529SET @nLimiteTj = CASE WHEN @nLimiteTj = 0 THEN @nLimiteCuenta ELSE @nLimiteTj END
530
531---------------------------------------------------------------------------------------------------------------------
532-- En saldo disponible pone el disponible de adelantos (si es operacion de adelanto)
533-- o pone el menor entre la cuenta y la tarjeta si es de venta
534---------------------------------------------------------------------------------------------------------------------
535IF @piIdMensaje IN (@OP_ADELANTO)
536BEGIN
537 SET @saldoDisponible = @nAnticipo_CC - @nSaldoUsadoAdel
538END
539ELSE
540BEGIN
541 IF @piIdMensaje IN (@OP_PRESTAMO)
542 BEGIN
543 SET @saldoDisponible = @nPrestamo_CC
544 END
545 ELSE
546 BEGIN
547 SET @saldoDisponible = CASE WHEN ( @nLimiteCuenta - @nSaldoUsado - @pfMonto ) < ( @nLimiteTj - @nSaldoUsadoTj - @pfMonto )
548 THEN ( @nLimiteCuenta - @nSaldoUsado - @pfMonto ) ELSE ( @nLimiteTj - @nSaldoUsadoTj - @pfMonto ) END
549
550 SET @saldoDisponible = CASE WHEN @saldoDisponible <0 THEN 0 ELSE @saldoDisponible END
551 END
552END
553
554
555--IF @saldoDisponible > 0 -- saco IF, que muestre siempre
556SET @cError = 'DISP:'+STR(@saldoDisponible,9,2)+' Consumido:'+STR(@nSaldoUsado,9,2)
557
558
559
560
561--===================================================================================================================
562-- CONTROLES DE ACUERDO A LA OPERACION
563--===================================================================================================================
564
565---------------------------------------------------------------------------------------------------------------------
566-- Controla si tiene Cuenta corriente
567---------------------------------------------------------------------------------------------------------------------
568IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
569AND @iCtaCte = 0
570BEGIN
571 set @iError = @errorPRODUCTO_INVALIDO ; set @cError = 'NO POSEE CTA. CTE. '
572
573 -- Bambu
574 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
575 BEGIN
576 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id,@iError AS iError,@cError AS cError
577 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
578 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
579 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
580 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
581 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
582 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
583 END
584 ELSE -- Tipre
585 BEGIN
586 SELECT @iError AS iError,@cError AS cError
587 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
588 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
589 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
590 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
591 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
592 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
593 END
594 RETURN 1
595END
596
597---------------------------------------------------------------------------------------------------------------------
598-- Controla si está habilitada la Cuenta corriente
599---------------------------------------------------------------------------------------------------------------------
600IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_CONSULTAULTLIQ,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
601AND @iCtaCteHabilitada = 0
602BEGIN
603 set @iError = @errorCLIENTE_INHABILITADO ; set @cError = 'CTA. CTE. INHABILITADA '
604
605 -- Bambu
606 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
607 BEGIN
608 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
609 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
610 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
611 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
612 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
613 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
614 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
615 END
616 ELSE -- Tipre
617 BEGIN
618 SELECT @iError AS iError,@cError AS cError
619 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
620 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
621 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
622 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
623 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
624 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
625 END
626 RETURN 1
627END
628
629---------------------------------------------------------------------------------------------------------------------
630-- Controla si está Vencida la ultima liquidacion
631---------------------------------------------------------------------------------------------------------------------
632IF @piIdMensaje IN (@OP_COMPRA,@OP_CONSULTASALDO) AND ( @nSaldoLiquidacion > 0 ) AND ( @dVctoLiquidacion < @fechaoper )
633BEGIN
634 set @iError = @errorCLIENTE_INHABILITADO ; set @cError = 'HAY LIQUIDACIONES VENCIDAS'
635
636 -- Bambu
637 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
638 BEGIN
639 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id,@iError AS iError,@cError AS cError
640 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
641 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
642 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
643 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
644 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
645 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
646 END
647 ELSE -- Tipre
648 BEGIN
649 SELECT @iError AS iError,@cError AS cError
650 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
651 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
652 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
653 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
654 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
655 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
656 END
657 RETURN 1
658END
659
660---------------------------------------------------------------------------------------------------------------------
661-- Controla si está Vencida la Cuenta corriente
662---------------------------------------------------------------------------------------------------------------------
663IF @piIdMensaje IN (@OP_CONSULTASALDO,@OP_CONSULTAULTOPS,@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO)
664AND @dFechaMasViejaPermitida > @dFechaMasVieja
665BEGIN
666 set @iError = @errorCLIENTE_INHABILITADO ; set @cError = 'CTA. CTE. VENCIDA '
667
668 -- Bambu
669 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
670 BEGIN
671 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
672 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
673 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
674 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
675 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
676 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
677 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
678 END
679 ELSE -- Tipre
680 BEGIN
681 SELECT @iError AS iError,@cError AS cError
682 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
683 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
684 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
685 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
686 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
687 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
688 END
689 RETURN 1
690END
691
692---------------------------------------------------------------------------------------------------------------------
693-- Controla si está excedido el saldo
694---------------------------------------------------------------------------------------------------------------------
695IF @piIdMensaje IN (@OP_COMPRA) AND ( @nSaldoUsado+@pfMonto ) > @nLimiteCuenta
696BEGIN
697 set @iError = @errorSIN_DISPONIBLE ; set @cError = 'LIM. DE CREDITO EXC.CUENTA'
698
699 -- Bambu
700 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
701 BEGIN
702 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
703 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
704 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
705 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
706 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
707 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
708 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
709 END
710 ELSE -- Tipre
711 BEGIN
712 SELECT @iError AS iError,@cError AS cError
713 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
714 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
715 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
716 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
717 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
718 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
719 END
720 RETURN 1
721END
722
723---------------------------------------------------------------------------------------------------------------------
724-- Controla si está excedido el saldo de la tarjeta
725---------------------------------------------------------------------------------------------------------------------
726IF @piIdMensaje IN (@OP_COMPRA) AND ( @nSaldoUsadoTj+@pfMonto ) > @nLimiteTj
727BEGIN
728 set @iError = @errorSIN_DISPONIBLE ; set @cError = 'LIM. DE CREDITO EXC.TARJE.'
729
730 -- Bambu
731 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
732 BEGIN
733 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
734 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
735 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
736 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
737 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
738 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
739 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
740 END
741 ELSE -- Tipre
742 BEGIN
743 SELECT @iError AS iError,@cError AS cError
744 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
745 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
746 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
747 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
748 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
749 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
750 END
751 RETURN 1
752END
753
754---------------------------------------------------------------------------------------------------------------------
755-- Controla si est est excedido En el adelanto
756---------------------------------------------------------------------------------------------------------------------
757IF @piIdMensaje IN (@OP_ADELANTO) AND ( @nSaldoUsadoAdel+@pfMonto ) > @nAnticipo_CC
758BEGIN
759 set @iError = @errorSIN_DISPONIBLE ; set @cError = 'LIM. DE ADELANTO EXCEDIDO '
760
761 -- Bambu
762 IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
763 BEGIN
764 SELECT @piIdProducto AS caja ,@piIdComercio AS last_id, @iError AS iError,@cError AS cError
765 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
766 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
767 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
768 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
769 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
770 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
771 END
772 ELSE -- Tipre
773 BEGIN
774 SELECT @iError AS iError,@cError AS cError
775 ,'' AS cBigError, 0 as cCodigoAutorizacion, 0 as iRRN,'' AS cNombre, '' as cApellido
776 , 0 AS SaldoDisponible,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual,'No hay' as cFechaPresentacion
777 ,'No hay' as dFechaVencimiento, 0 AS fDeudaActual ,0 as fPagoMinimo ,0 AS fSaldoAlCierre
778 ,0 as fTotalConsumos ,0 as fTotalPagos ,0 as fAcContTitular ,0 as fAcumuladoPrestamoContado
779 ,0 as fAcumuladoPrestamoCuotas ,0 as fDisponiblePrestamoContado ,0 as fDisponiblePrestamoCuotas
780 ,0 as fAcumuladoPeriodoConsumos ,0 as fAcumuladoPeriodoPagos ,0 AS fAcumuladoCuotasTitular
781 END
782 RETURN 1
783END
784
785--===================================================================================================================
786-- PASA LOS CONTROLES - ARMA DATOS PARA DEVOLVER
787--===================================================================================================================
788
789/*
790 SELECT @cError = CASE
791 WHEN CONVERT(tinyint, ISNULL(ag.ctacte,0)) = 0 THEN 'NO POSEE CTA. CTE. '
792 WHEN CONVERT(tinyint, ISNULL(ag.habilit,0)) = 0 THEN 'CTA. CTE. INHABILITADA '
793 WHEN DATEADD(dd, ISNULL( ( CASE WHEN ag.nLimDiasVcto = -1 THEN
794 (SELECT CONVERT(Numeric(8),cont) FROM parsis WHERE codi='DIASVCTOCC')
795 ELSE ag.nLimDiasVcto END *(-1) ), 30) , GETDATE()) > @dFechaMasVieja THEN 'CTA. CTE. VENCIDA '
796 WHEN ag.limite > 0 and ISNULL( @nSaldoUsado+@pfMonto ,0) > ag.limite THEN 'LIM. DE CREDITO EXC.CUENTA'
797 WHEN @nLimiteTj > 0 and ISNULL( @nSaldoUsadoTj+@pfMonto ,0) > @nLimiteTj THEN 'LIM. DE CREDITO EXC.TARJ.'
798 WHEN @dVctoLiquidacion <= CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),103),103) AND @nSaldoLiquidacion>0
799 THEN 'LIQUIDACION VENCIDA '
800 ELSE 'DISP:'+STR(0,9,2)+' Saldo:'+STR(0,9,2)
801 END
802 , @SaldoDisponible = 0
803 , @nLimiteCuenta = ag.limite
804 , @cNombre = SUBSTRING(razo,CHARINDEX(' ',razo)+1,50)
805 , @cApellido = LEFT(razo,CHARINDEX(' ',razo)-1)
806 , @cDomicilio = ISNULL(dire,'')
807 FROM agenda ag WHERE codigo = @cCodigo
808*/
809
810
811
812------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
813-- Consulta de Saldo, Operacion 1000 -----------------------------------------------------------------------------------------------------------------------------------------------------------
814-- Consulta de Ultimas 10 operaciones , Operacion 1100 ----------------------------------------------------------------------------------------------------------------------------------------
815-- Consulta de Ultimas Liquidacion , Operacion 1200 ------------------------------------------------------------------------------------------------------------------
816------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
817
818IF @piIdMensaje = @OP_CONSULTASALDO OR @piIdMensaje = @OP_CONSULTAULTOPS OR @piIdMensaje = @OP_CONSULTAULTLIQ
819BEGIN
820
821 -- Ultimos 10 movimientos
822 IF @piIdMensaje = @OP_CONSULTAULTOPS
823 BEGIN
824 SET @nVuelta = 0
825 SET @cBigError = CHAR(13)+REPLICATE('-',40)+CHAR(13)+' Fecha Suc --- Operacion --- Monto'+CHAR(13)
826
827 declare cursUOps cursor local read_only forward_only
828 FOR SELECT TOP 50 CONVERT( CHAR(8), co.fecha, 3) as Fecha
829 ,RIGHT(co.cod_suc,3)+' '+LEFT(co.cod_comp,4)+' '+co.ptovta+' '+co.numero as Mensaje
830 ,CONVERT( CHAR(10),STR( SUM(vc.importe),9,2)) as Monto
831 FROM VIEW_ctasCtes Vc
832 LEFT JOIN relfaman rf ON vc.cod_oper = rf.oper_mand
833 LEFT JOIN ctasctes_liquidaciones liq ON rf.oper_fact = liq.cCod_oper
834 LEFT JOIN compprim co ON vc.cod_oper = co.cod_oper
835 WHERE vc.codigo = STR(@cCodigo,13)
836 AND liq.nId_liquidacion IS NULL
837 GROUP BY co.fecha,co.cod_suc,co.cod_comp,co.ptovta,co.numero
838 ORDER BY co.fecha DESC
839
840 OPEN cursUOps
841 FETCH NEXT FROM cursUOps INTO @cFecha, @cMensaje, @cMonto
842 WHILE (@@FETCH_STATUS = 0)
843 BEGIN
844 set @cLinea= @cFecha+' '+@cMensaje+@cMonto + CASE WHEN @nVuelta < 9 THEN CHAR(13) ELSE '' END
845 IF LEFT(@cBigError,2) = '..'
846 set @cBigError = CHAR(13)+@cLinea
847 ELSE
848 set @cBigError = @cBigError + @cLinea
849
850 SET @nVuelta = @nVuelta + 1
851 FETCH NEXT FROM cursUOps INTO @cFecha, @cMensaje, @cMonto
852
853 IF @nVuelta = 10
854 BREAK
855 END
856
857 CLOSE cursUOps
858 DEALLOCATE cursUOps
859
860 END
861 ELSE
862 BEGIN
863 -- Ultima Liquidacion
864 IF @piIdMensaje = @OP_CONSULTAULTLIQ
865 BEGIN
866 -- Si es cuenta corriente sin corte, devuelve la leyenda de No tiene liquidacion
867 IF @nId_grupo_CC = 0
868 BEGIN
869 -- Comienza a Armar el Ticket
870 SET @cBigError = REPLICATE('-',40) + CHAR(13)
871 + CONVERT( CHAR(9),GETDATE() , 3) + CONVERT( CHAR(8),GETDATE(), 8)+CHAR(13)
872 + REPLICATE('-',40) +CHAR(13)
873 set @cBigError = @cBigError + 'NROCTA : '+ LTRIM(@cCodigo)+CHAR(13)
874 set @cBigError = @cBigError + 'APELLIDO: '+ LTRIM(@cApellido)+CHAR(13)
875 set @cBigError = @cBigError + 'NOMBRE : '+ LTRIM(@cNombre)+CHAR(13)
876 SET @cBigError = @cBigError + REPLICATE('-',40)+CHAR(13)
877 SET @cBigError = @cBigError + '** Cuenta corriente sin corte,'+CHAR(13)
878 SET @cBigError = @cBigError + ' NO EXISTEN LIQUIDACIONES para'+CHAR(13)
879 SET @cBigError = @cBigError + ' este cliente.'+CHAR(13)
880 SET @cBigError = @cBigError + REPLICATE('-',40)+CHAR(13)
881 END
882 ELSE
883 BEGIN
884 -- Busca los datos de la liquidacion
885 DECLARE cursUOps cursor local read_only forward_only FOR
886 SELECT CONVERT(CHAR(8),co.fecha,3)+' '+RIGHT(co.cod_suc,2)+' '+LEFT(Co.cod_comp,4)+' '+co.ptovta+' '+co.numero
887 +STR(SUM(cc.importe),9,2) AS mensaje
888 FROM relfaman rf
889 LEFT JOIN ctasctes cc ON rf.oper_mand = cc.cod_oper AND rf.vencim = cc.vencim
890 LEFT JOIN compprim co ON rf.oper_mand = co.cod_oper
891 WHERE rf.oper_fact IN (
892 SELECT cCod_oper FROM ctasctes_LIQUIDACIONES WHERE NID_LIQUIDACION = @iNroCierre)
893 GROUP BY co.fecha,co.cod_suc,co.cod_comp,co.ptovta,co.numero
894 ORDER BY co.fecha
895
896 SET @nCantMovimientos = 0
897 SET @fImporteTotalConsumo = 0
898
899 -- Recorre cursor para sumar pagos y Ventas
900 OPEN cursUOps
901 FETCH NEXT FROM cursUOps INTO @cMensajeLiq
902 WHILE (@@FETCH_STATUS = 0)
903 BEGIN
904 SET @nCantMovimientos = @nCantMovimientos + 1
905
906 IF CONVERT(NUMERIC(10,2),SUBSTRING(@cMensajeLiq,31,9)) >= 0
907 SET @fImporteTotalConsumo = @fImporteTotalConsumo + CONVERT(NUMERIC(10,2),SUBSTRING(@cMensajeLiq,31,9))
908 ELSE
909 SET @fImporteTotalPagos = @fImporteTotalPagos + ABS(CONVERT(NUMERIC(10,2),SUBSTRING(@cMensajeLiq,31,9)))
910
911 FETCH NEXT FROM cursUOps INTO @cMensajeLiq
912 END
913
914 CLOSE cursUOps
915
916
917 -- Comienza a Armar el Ticket
918 SET @cBigError = REPLICATE('-',40) + CHAR(13)
919 + CONVERT( CHAR(9),GETDATE() , 3) + CONVERT( CHAR(8),GETDATE(), 8)+CHAR(13)
920 + REPLICATE('-',40) +CHAR(13)
921 set @cBigError = @cBigError + 'NROCTA : '+ LTRIM(@cCodigo)+CHAR(13)
922 set @cBigError = @cBigError + 'APELLIDO: '+ LTRIM(@cApellido)+CHAR(13)
923 set @cBigError = @cBigError + 'NOMBRE : '+ LTRIM(@cNombre)+CHAR(13)
924
925 if LEN(@cDomicilio)>0
926 set @cBigError = @cBigError + 'CALLE : '+ LEFT(@cDomicilio,29)+CHAR(13)
927
928 set @cBigError = @cBigError + 'NROCIERRE : '+ LTRIM(STR(@iNroCierre))+CHAR(13)
929 if @dFechaEmision IS NOT NULL
930 set @cBigError = @cBigError + 'FECHA EMISION: '+ CONVERT( CHAR(9),@dFechaEmision, 3) +CHAR(13)
931 if @dVctoLiquidacion IS NOT NULL
932 set @cBigError = @cBigError + 'FECHA VENCIM. : '+ CONVERT( CHAR(9),@dVctoLiquidacion, 3) +CHAR(13)
933 set @cBigError = @cBigError + 'SALDO ANTERIOR: '+ LTRIM(STR(@fImporteSaldoAnterior,9,2)) +CHAR(13)
934 set @cBigError = @cBigError + 'TOTAL CONSUMOS: '+ LTRIM(STR(@fImporteTotalConsumo,9,2)) +CHAR(13)
935 set @cBigError = @cBigError + 'TOTAL PAGOS : '+ LTRIM(STR(@fImporteTotalPagos,9,2)) +CHAR(13)
936 set @cBigError = @cBigError + 'NETO LIQUIDAC : '+ LTRIM(STR(@nImporteLiquidacion,9,2)) +CHAR(13)
937 + REPLICATE('-',40) +CHAR(13)
938
939 set @cBigError = @cBigError + ' Fecha Suc --- Operacion --- Monto' +CHAR(13)
940
941
942 -- Recorre cursor
943 OPEN cursUOps
944 FETCH NEXT FROM cursUOps INTO @cMensajeLiq
945 WHILE (@@FETCH_STATUS = 0)
946 BEGIN
947 set @cBigError = @cBigError + @cMensajeLiq + CHAR(13)
948
949 FETCH NEXT FROM cursUOps INTO @cMensajeLiq
950 END
951
952 CLOSE cursUOps
953 DEALLOCATE cursUOps
954
955 SET @cBigError = @cBigError+ REPLICATE('-',40) + CHAR(13) + 'TOT.DE OPERACIONES: '+LTRIM(STR(@nCantMovimientos))+CHAR(13)
956 +REPLICATE('-',40)
957
958
959 END
960 END
961 ELSE
962 SET @cBigError = '..'
963 END
964
965
966 IF @iError <> 0
967 SET @SaldoDisponible = 0
968
969END
970
971
972
973------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
974-- COMPRA, Operacion 2000 -----------------------------------------------------------------------------------------------------------------------------------------------------------
975-- ADELANTO EN EFVO, Operacion 2100 -----------------------------------------------------------------------------------------------------------------------------------------------------------
976-- PRESTAMO EN CUOTAS, @OP_PRESTAMO -----------------------------------------------------------------------------------------------------------------------------------------------------------
977------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
978IF @piIdMensaje in (@OP_COMPRA,@OP_ADELANTO,@OP_PRESTAMO) and @iError = 0
979BEGIN
980 SET @cComprobante = CASE WHEN @piIdMensaje IN (@OP_ADELANTO,@OP_PRESTAMO)
981 THEN 'ADELCC' ELSE 'TICKCC' END
982
983 -- Control de duplicados:
984 -- Buscamos si existe la transaccion
985 IF EXISTS(SELECT * FROM COMPPRIMAX WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10) )
986 BEGIN
987 set @iError = @errorTRANSACCION_DUPLICADA
988 SET @cError = 'Transaccion duplicada'
989 RETURN 1
990 END
991 ELSE
992 BEGIN
993
994 /*
995
996 COMPRIMAX
997 nItem=1 VTA
998 nItem>=10 y <99 R.P (Reversa Pendiente)
999 nItem=99 Reversa cancelada por vta
1000
1001 */
1002
1003 -- Buscamos si quedó una reversa Pendiente
1004 IF EXISTS(SELECT * FROM COMPPRIMAX WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RP' AND nItem > 1 AND nItem < 99)
1005 BEGIN
1006
1007 -- HabÃa una reversa pendiente, por lo tanto la primer operacion cancelamos las RP poniendole 999 (18/05/2015)
1008 UPDATE compprimax
1009 SET nitem = 99
1010 WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RP'
1011 AND nitem > 1
1012
1013 set @iError = @errorTRANSACCION_DUPLICADA
1014 SET @cError = 'Transaccion reversada'
1015 RETURN 1
1016 END
1017 ELSE
1018 BEGIN
1019
1020 /* CODIGO DE OPERACION PRINCIPAL */
1021 Exec Cod_Oper @C_cod_suc, @cCod_Oper output
1022
1023 SET XACT_ABORT ON
1024 BEGIN TRANSACTION
1025 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
1026 /*
1027 -- POR NO HACER LA REVERSA, BORRAMOS E INSERTAMOS
1028 if @cCod_Ant <> ''
1029 begin
1030 DELETE FROM CompPrim WHERE Cod_Oper = @cCod_Ant
1031 DELETE FROM CtasCtes WHERE Cod_Oper = @cCod_Ant
1032 DELETE FROM CtasSald WHERE Cod_Oper = @cCod_Ant
1033 end
1034 */
1035 -- Inserta COMPPRIM (comun para todos)
1036 INSERT INTO COMPPRIM
1037 (COD_COMP, PTOVTA, NUMERO, FECHA, CODIGO, COD_OPER, FCARGA, LETRA,
1038 RAZO, DIRE, CODIVA, CUIT, VENDEDOR, GRAV1, GRAV2, EXENTO, IMPU1,
1039 IMPU2, IMPU3, IMPU4, IMPU5, IMPU6, IMPU7, IMPU8, IMPU9, IMPU10, IMPU11, IMPU12,
1040 RETEN1, RETEN2, RETEN3, RETEN4, RETEN5, RETEN6, NETO, COD_SUC, COD_TIPO, COD_PAGO, FORMA,
1041 OPRETENC, ENGANCHE, COMISION, CONTROL, IMPRESO, NRONP)
1042 SELECT @cComprobante, replace(STR(@piIdTerminal,4),' ','0'), replace(STR(isnull(@piPOSNroTicket,0),8),' ','0'),
1043 @fechaoper, @ccodigo, @cCod_Oper, GETDATE(), case when timpu in ('I','N') then 'A' ELSE 'B' END,
1044 Razo, Dire, left(timpu,1), Cuit, 0, 0, 0,
1045 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1046 0, 0, 0, 0, 0, 0, 0, 0, @pfMonto, @C_COD_SUC,
1047 ' ', 0, 'E', ' ', ' ', 0, 1, 0, '00000000'
1048 FROM agenda
1049 WHERE codigo = @cCodigo
1050
1051 -- Inserta en COMPPRIMAX la relación entre el codigo de operacion de Pegassus y la Transacion de Tipre (14/1/13)
1052 INSERT INTO COMPPRIMAX
1053 (cod_oper,nitem,oper_micard,nro_tarj)
1054 SELECT @cCod_Oper AS cod_oper
1055 ,1 AS nItem
1056 ,@C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'VT' AS oper_micard
1057 ,ISNULL(@pcNroTarjeta,'') AS nro_tarj
1058
1059
1060 -- Si NO es un prestamo, graba la operacion con fecha de vcto hoy.
1061 IF @piIdMensaje <> @OP_PRESTAMO
1062 BEGIN
1063 -- Inserta en CTASCTES el vencimiento en cuenta corriente (el mismo dia)
1064 INSERT INTO CTASCTES (COD_OPER, VENCIM, COD_CTACON, FECH_VCTO, IMPORTE, SAFA, DESCRI,
1065 OPER_ENG, VENC_ENG, bloqueo, cod_suc )
1066 SELECT @cCod_Oper, 1, isnull(cuenclie,'DEUDOR'), @fechaoper, @pfMonto, @pfMonto,
1067 'C.C.POS - Tj:'+LEFT(ISNULL(@pcNroTarjeta,'')+' '+ISNULL(@cNomTarjeta,50),37),
1068 null, null, 0, @c_Cod_suc
1069 from agenda where codigo = @cCodigo
1070 END
1071 ELSE
1072 BEGIN
1073 -- Calcula las fechas de vcto. y arma una tabla
1074 CREATE TABLE #vctos (nVencim SMALLINT, dFechaVcto DATETIME, nValorCuota NUMERIC(18,2))
1075 SET @nCuotas = 0
1076 -- @nValorCuota , @nAcumPrestamo
1077
1078 -- Volvemos a fecha 20, para que entre en las liquidaciones
1079 -- Desactivamos esto del 20 ya que cobra 2 cuotas en el mismo perÃodo
1080 SET @dFechaVcto = CONVERT(DATETIME,REPLACE(STR(CASE WHEN DAY(GETDATE()) > 20 THEN DAY(GETDATE()) ELSE DAY(GETDATE()) END,2)+'/'
1081 +STR(MONTH(GETDATE()),2)+'/'+STR(YEAR(GETDATE()),4),' ','0'),103)
1082
1083 -- Calculamos el valor de la cuota
1084 SET @nValorCuota = ROUND(@pfMonto/@nCuotas_CC,2)
1085 SET @nAcumPrestamo = 0
1086
1087 -- Recorremos "n" cuotas
1088 WHILE @nCuotas < @nCuotas_CC
1089 BEGIN
1090 SET @nCuotas = @nCuotas + 1
1091
1092 -- Inserta la fecha y el valor de la cuota o el resto.-
1093 INSERT INTO #vctos
1094 SELECT @nCuotas,
1095 @dFechaVcto,
1096 CASE WHEN @nCuotas = @nCuotas_CC THEN @pfMonto - @nAcumPrestamo
1097 ELSE @nValorCuota END
1098 SET @nAcumPrestamo = @nAcumPrestamo + @nValorCuota
1099
1100 -- Despues de agregar la cuota suma 1 mes.
1101 SET @dFechaVcto = DATEADD(mm,1,@dFechaVcto)
1102
1103 END
1104
1105 -- Inserta en CTASCTES el vencimiento en cuenta corriente (el mismo dia)
1106 INSERT INTO CTASCTES (COD_OPER, VENCIM, COD_CTACON, FECH_VCTO, IMPORTE, SAFA, DESCRI,
1107 OPER_ENG, VENC_ENG, bloqueo, cod_suc )
1108 SELECT @cCod_Oper, vc.nVencim, isnull(ag.cuenclie,'DEUDOR'), vc.dFechaVcto
1109 , vc.nValorCuota, vc.nValorCuota,
1110 'PRESTAMO - Tj:'+LEFT(ISNULL(@pcNroTarjeta,'')+' '+ISNULL(@cNomTarjeta,50),37),
1111 null, null, 0, @c_Cod_suc
1112 FROM agenda ag, #vctos vc
1113 WHERE ag.codigo = @cCodigo
1114
1115 -- Borra el prestamo desde el padron
1116 UPDATE perfprov
1117 SET nPrestamo_CC = 0 , nCuotas_CC = 0
1118 WHERE codigo = @cCodigo
1119
1120 END
1121
1122 -- Inserta el CTASSALD, igual al CTASCTES
1123 INSERT INTO CTASSALD SELECT * FROM CTASCTES WHERE cod_oper = @cCod_Oper
1124
1125 /*
1126 insert into auditori
1127 select 'pos','TICKET EN CUENTA CORRIENTE', GETDATE(),1,'('+@cCodigo+') '+left(razo,20)+'TICKET '+STR(@piPOSNroTicket,4)+'-'+STR(@piPOSNroTicket,8)+' '+STR(@pfMonto,12,2),'TICKCC',39
1128 FROM Agenda where codigo=@cCodigo
1129 */
1130
1131 END
1132 Commit Transaction
1133
1134 END
1135
1136END
1137
1138
1139------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1140-- REVERSA, Operacion 9000 -----------------------------------------------------------------------------------------------------------------------------------------------------------
1141------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1142IF @piIdMensaje = 9000 and @iError = 0
1143BEGIN
1144 -- Esperamos 2 segundos para hacer la reversa y asà esperamos que llegue la operación de ticket
1145 WAITFOR DELAY '00:00:02'
1146
1147 SET @cCod_Oper =''
1148
1149 -- Busca los datos de la operacion, si hay una operacion de venta anterior
1150 -- Primero busca como VT, generalmente va a ser una anulacion de venta
1151 SELECT @cCod_Oper = cod_oper
1152 FROM compprimax cx
1153 WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'VT'
1154 AND nItem = 1
1155
1156 -- Si la encontro, la anulo
1157 IF ISNULL(@cCod_Oper ,'') <>''
1158 BEGIN
1159 -- Actualiza Safa
1160 UPDATE cs SET cs.safa = CS.SAFA-CC.IMPORTE
1161 FROM ctasctes cc
1162 LEFT JOIN ctassald cs
1163 ON cc.oper_eng = cs.cod_oper
1164 AND cc.venc_eng = cs.vencim
1165 where cc.cod_oper =@cCod_Oper
1166 AND cs.cod_oper IS NOT NULL
1167
1168 -- Borra
1169 DELETE compprim where cod_oper =@cCod_Oper
1170 DELETE ctasctes where cod_oper =@cCod_Oper
1171 DELETE ctassald where cod_oper =@cCod_Oper
1172
1173 SET @iError = 0 ; SET @cError = 'REVERSA REALIZADA CORRECTAMENTE.-'
1174 SELECT @iError AS iError,@cError AS cError
1175 RETURN 1
1176 END
1177 ELSE
1178 BEGIN
1179 -- Si no la encontramos como VT (Venta) la buscamos como RE (PAGO)
1180 SELECT @cCod_Oper = cod_oper
1181 FROM compprimax cx
1182 WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RE'
1183 AND nItem = 1
1184
1185 -- Si encontro el Pago, Anula el recibo y vueve el saldo
1186 IF ISNULL(@cCod_Oper ,'') <>''
1187 BEGIN
1188
1189
1190 -- Vuelve el SAFA de los comprobantes que se habian pagado
1191 UPDATE cc
1192 SET safa = CC.safa - c2.safa
1193 FROM ctassald cc
1194 LEFT JOIN ctasctes c2
1195 ON cc.cod_oper = c2.OPER_ENG
1196 AND cc.vencim = c2.VENC_ENG
1197 WHERE c2.cod_oper=@cCod_oper
1198
1199 -- Borra datos del RECPOS
1200 DELETE ctasctes where cod_oper=@cCod_oper
1201 DELETE compprim where cod_oper=@cCod_oper
1202
1203 -- Fin Reversa
1204 set @iError = 0 ; set @cError = 'REVERSA DE PAGO REALIZADA OK.-'
1205 SELECT @iError AS iError,@cError AS cError
1206 RETURN 1
1207
1208 END
1209 ELSE
1210 BEGIN
1211 -- Si no la encontramos, la dejamos como REVERSA PENDIENTE en COMPRIMAX
1212 -- (agregado 5/5/15 para ver si agarramos las REVERSAS ANTERIORES
1213
1214 -- Necesitamos cod_oper para la operacion
1215 Exec Cod_Oper @C_cod_suc, @cCod_Oper output
1216
1217
1218 -- Insertamos en COMPPRIMAX
1219 INSERT INTO COMPPRIMAX
1220 (cod_oper,nitem,oper_micard,nro_tarj)
1221 SELECT @cCod_Oper AS cod_oper
1222 ,10 AS nItem
1223 ,@C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RP' AS oper_micard
1224 ,ISNULL(@pcNroTarjeta,'') AS nro_tarj
1225 -- Fin Agregado
1226 set @iError = @errorREVERSA_INVALIDA ; set @cError = 'REVERSA INVALIDA.-'
1227 SELECT @iError AS iError,@cError AS cError
1228 RETURN 1
1229 END
1230 END
1231
1232END
1233
1234------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1235-- PAGO, Operacion 3000 (@OP_PAGO)----------------------------------------------------------------------------------------------------------------------------------------------------------
1236------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1237IF @piIdMensaje = @OP_PAGO and @iError = 0
1238BEGIN
1239
1240--Pago de cuenta
1241--exec upCtaCte.dbo.spucc_oCtaCteStandard @pcNroTarjeta='88000501', @piIdProtocolo='1', @piIdMensaje='3000', @piIdProducto='2' , @piIdComercio='0', @piIdSucursal='20', @piIdMoneda='32', @pbClienteInputMode='0', @pfMonto=' 0.01', @piCuotas='0', @piTRXNroTicket='1', @piPOSNroTicket='35', @piPOSNroZ='3', @piPOSUser='11234', @piPOSSupervisor='0', @piTrxModoAutorizacion='2', @piIdTerminal='164' , @pcPIN='5092252'
1242
1243
1244
1245 -- Imputar comprobantes
1246 -- y grabar
1247 -- en COMPPRIM - CTASCTES - CTASSALD
1248
1249 /* CODIGO DE OPERACION PRINCIPAL */
1250 Exec Cod_Oper @C_cod_suc, @cCod_Oper output
1251
1252
1253 DECLARE @CONC_ANT CHAR(6), @OPER_IMP CHAR(10), @VENC_IMP INT, @CONC_IMP CHAR(6), @SALDIMP NUMERIC(20,3),
1254 @cNumero char(8), @cPtoVta char(4), @NetoImp Numeric(20,2), @nVenc smallint
1255
1256 /* Conceptos del cliente */
1257 SELECT @conc_ant = ISNULL(ANTICLIE,''), @CONC_IMP = ISNULL(CUENCLIE,'')
1258 from agenda
1259 where codigo = @cCodigo
1260
1261 if @conc_ant = ''
1262
1263 set @conc_ant = 'ANTCLI'
1264 if @conc_imp = ''
1265 set @conc_imp = 'DEUDOR'
1266
1267 /* Monto del pago */
1268 set @netoimp = abs(@pFmonto)
1269
1270 -- punto de venta del recibo de pos
1271 SET @cPtoVta = '0000'
1272 --SELECT @cPtoVta = PTOVTA from comproba where cod_comp = 'RECPOS'
1273 SET @cPtoVta = replace(STR(@piIdTerminal,4),' ','0')
1274
1275 -- Control de duplicados:
1276 -- Buscamos si existe la transaccion
1277 IF EXISTS(SELECT * FROM COMPPRIMAX WHERE oper_micard = @C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RE' )
1278 BEGIN
1279 set @iError = @errorTRANSACCION_DUPLICADA
1280 SET @cError = 'Transaccion duplicada'
1281 RETURN 1
1282 END
1283
1284
1285
1286 SET XACT_ABORT ON
1287 BEGIN TRANSACTION
1288 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
1289
1290
1291
1292 /* ASIGNANDO NUMERO AL RECIBO */
1293
1294
1295 SET @cNumero = ''
1296 EXEC [VESP_TALONARI] 'RECPOS', @cNumero OUTPUT --Va a buscar el numero al talonario, lo reserva (graba) y lo trae
1297 ,'' --letra
1298 ,'' --local
1299 ,'' -- concepto
1300 ,'' -- deposito
1301 ,1 -- graba
1302 IF LTRIM(@cNumero) = ''
1303 SET @cNumero = '99999999'
1304
1305
1306
1307 /* Grabando Recibo */
1308 -- Se agrega la fecha y hora en la columna FACT, para poder chequear si se está grabando el mismo
1309 INSERT INTO COMPPRIM
1310 (COD_COMP, PTOVTA, NUMERO, FECHA, CODIGO, COD_OPER, FCARGA, LETRA,
1311 RAZO, DIRE, CODIVA, CUIT, VENDEDOR, GRAV1, GRAV2, EXENTO, IMPU1,
1312 IMPU2, IMPU3, IMPU4, IMPU5, IMPU6, IMPU7, IMPU8, IMPU9, IMPU10, IMPU11, IMPU12,
1313 RETEN1, RETEN2, RETEN3, RETEN4, RETEN5, RETEN6, NETO, COD_SUC, COD_TIPO, COD_PAGO, FORMA,
1314 OPRETENC, ENGANCHE, COMISION, CONTROL, IMPRESO, NRONP, fact)
1315 SELECT 'RECPOS', @cPtovta, @cNumero,
1316 @fechaoper, @ccodigo, @cCod_Oper, @fechaoper, 'X',
1317 Razo, Dire, left(timpu,1), Cuit, 0, 0, 0,
1318 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1319 0, 0, 0, 0, 0, 0, 0, 0, @pfMonto, @C_COD_SUC,
1320 ' ', 0, 'E', ' ', ' ', 0, 1, 0, '00000000',GETDATE()
1321 from agenda where codigo = @cCodigo
1322
1323
1324 -- Inserta en COMPPRIMAX la relación entre el codigo de operacion de Pegassus y la Transacion de Tipre (14/1/13)
1325 INSERT INTO COMPPRIMAX
1326 (cod_oper,nitem,oper_micard,nro_tarj)
1327 SELECT @cCod_Oper AS cod_oper
1328 ,1 AS nItem
1329 ,@C_COD_SUC+replace(STR(@piIdTerminal,4),' ','0')+STR(@piTRXNroTicket,10)+'RE' AS oper_micard
1330 ,ISNULL(@pcNroTarjeta,'') AS nro_tarj
1331
1332
1333
1334 /* Recorriendo la deuda para imputar */
1335 DECLARE Cur_Detrec CURSOR FOR
1336 SELECT cs.cod_oper, cs.vencim, cs.cod_ctacon, cs.safa
1337 FROM compprim cp, ctassald cs
1338 WHERE cs.safa <> 0 and cp.cod_oper = cs.cod_oper AND
1339 (cp.codigo = @ccodigo OR cp.codigo IN
1340 (SELECT codigo2 FROM RELAGENDA WHERE codigo1 = @ccodigo))
1341 ORDER BY sign(cs.safa),cs.fech_vcto, cp.fcarga
1342
1343 SET @NVENC = 0
1344 OPEN Cur_Detrec
1345 FETCH NEXT FROM Cur_Detrec INTO @OPER_IMP, @VENC_IMP, @CONC_IMP, @SALDIMP
1346 WHILE @@FETCH_STATUS = 0
1347 BEGIN
1348 IF @SALDIMP > @NETOIMP
1349 SET @SALDIMP = @NETOIMP
1350
1351
1352 -- AGREGANDO DETALLE RECIBO
1353 SET @NVENC = @NVENC + 1
1354 INSERT INTO CTASCTES
1355 ([COD_OPER], [VENCIM], [COD_CTACON], [FECH_VCTO], [IMPORTE], [SAFA], [OPER_ENG], [VENC_ENG],[COD_SUC],[DESCRI])
1356 VALUES (@cCod_Oper, @NVENC, @CONC_IMP, @fechaoper, @SALDIMP * (-1), @SALDIMP * (-1), @OPER_IMP, @VENC_IMP,@c_Cod_Suc
1357 ,'C.C.POS - Tj:'+LEFT(ISNULL(@pcNroTarjeta,'')+' '+ISNULL(@cNomTarjeta,50),37))
1358
1359 /* AQUI SE DESCUENTAN SALDOS */
1360 UPDATE CtasSald SET Safa = Safa + ( @SALDIMP * (-1) )
1361 FROM CtasSald
1362 WHERE @OPER_IMP = Cod_oper AND
1363 @VENC_IMP = Vencim
1364
1365
1366 SET @NETOIMP = @NETOIMP - @SALDIMP
1367
1368 IF @NETOIMP = 0
1369 BREAK
1370
1371 FETCH NEXT FROM Cur_Detrec INTO @OPER_IMP, @VENC_IMP, @CONC_IMP, @SALDIMP
1372 END
1373 CLOSE Cur_Detrec
1374 DEALLOCATE Cur_Detrec
1375
1376 -- ANTICIPO
1377 IF @NETOIMP <> 0
1378 BEGIN
1379 SET @NVENC = @NVENC + 1
1380 INSERT INTO CTASCTES
1381 ([COD_OPER], [VENCIM], [COD_CTACON], [FECH_VCTO], [IMPORTE],[SAFA],[COD_SUC],[DESCRI])
1382 VALUES (@cCod_Oper, @NVENC, @CONC_ANT, @fechaoper, ABS(@NETOIMP) * (-1), ABS(@NETOIMP) * (-1) ,@c_Cod_Suc
1383 ,'C.C.POS - Tj:'+LEFT(ISNULL(@pcNroTarjeta,'')+' '+ISNULL(@cNomTarjeta,50),37))
1384 INSERT INTO CTASSALD
1385 ([COD_OPER], [VENCIM], [COD_CTACON], [FECH_VCTO], [IMPORTE],[SAFA],[COD_SUC],[DESCRI])
1386 VALUES (@cCod_Oper, @NVENC, @CONC_ANT, @fechaoper, ABS(@NETOIMP) * (-1), ABS(@NETOIMP) * (-1),@c_Cod_Suc
1387 ,'C.C.POS - Tj:'+LEFT(ISNULL(@pcNroTarjeta,'')+' '+ISNULL(@cNomTarjeta,50),37))
1388
1389 END
1390
1391 /******************************************/
1392 /* FIN REALIZANDO IMPUTACIONES DE RECIBOS */
1393 /******************************************/
1394
1395 -- actualiza el safa de la table ctasctes_liquidaciones
1396 UPDATE cc SET nSafa = sa.nSaldoActual FROM
1397 (SELECT oper_fact AS cCod_oper,SUM(ISNULL(cs.safa,0)) AS nSaldoActual
1398 FROM relfaman rf
1399 LEFT JOIN ctassald cs
1400 ON rf.oper_mand = cs.cod_oper AND rf.vencim = cs.vencim
1401 WHERE rf.oper_fact IN (
1402 SELECT cCod_oper FROM ctasctes_liquidaciones ccl WHERE nSafa <>0)
1403 GROUP BY oper_fact) Sa
1404 LEFT JOIN ctasctes_liquidaciones cc
1405 ON sa.cCod_oper = cc.cCod_oper
1406
1407
1408 -- Grabar auditoria
1409 /*
1410 insert into auditori
1411 select 'pos','RECIBO DESDE POS', GETDATE(),1,'('+@cCodigo+') '+left(razo,20)+'RECIBO '+@cPtoVta+'-'+@cNumero+' '+STR(@pfMonto,12,2),'RECPOS',40
1412 FROM Agenda where codigo=@cCodigo
1413 */
1414
1415 COMMIT TRANSACTION
1416
1417
1418END
1419-- Para ver como queda la liquidacion (sacar el comentario)
1420--SELECT LEFT(@cBigError,256),SUBSTRING(@cBigError,257,256),SUBSTRING(@cBigError,513,256),SUBSTRING(@cBigError,769,256)
1421
1422-- Bambu
1423IF EXISTS(SELECT * FROM parsis WHERE codi='EMPRESAPOS' AND LEFT(cont,1)='I')
1424BEGIN
1425 /* DEVUELVO RESULTADO PARA BAMBU*/
1426 SELECT @piIdProducto AS caja
1427 ,@piIdComercio AS last_id
1428 ,@iError as iError
1429 ,@cError as cError
1430 ,@cBigError as cBigError, 0 as cCodigoAutorizacion, 0 as iRRN
1431 ,@cNombre AS cNombre, @cApellido as cApellido
1432 ,@SaldoDisponible as SaldoDisponible
1433 ,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual
1434 ,'No hay' as cFechaPresentacion
1435 ,'No hay' as dFechaVencimiento
1436 ,@nSaldoUsado+@nSaldoAlCierre-@nRestarAlFinal AS fDeudaActual
1437 ,0 as fPagoMinimo
1438 ,@nSaldoAlCierre as fSaldoAlCierre
1439 ,0 as fTotalConsumos
1440 ,0 as fTotalPagos
1441 ,0 as fAcContTitular
1442 --Prestamos
1443 ,0 as fAcumuladoPrestamoContado
1444 ,0 as fAcumuladoPrestamoCuotas
1445 ,0 as fDisponiblePrestamoContado
1446 ,0 as fDisponiblePrestamoCuotas
1447 ,0 as fAcumuladoPeriodoConsumos
1448 ,0 as fAcumuladoPeriodoPagos
1449 --Agregado de Cuotas:
1450 ,0 AS fAcumuladoCuotasTitular
1451
1452END
1453ELSE
1454BEGIN
1455 /* DEVUELVO RESULTADO PARA TIPRE*/
1456 SELECT @iError as iError
1457 ,@cError as cError
1458 ,@cBigError as cBigError, 0 as cCodigoAutorizacion, 0 as iRRN
1459 ,@cNombre AS cNombre, @cApellido as cApellido
1460 ,@SaldoDisponible as SaldoDisponible
1461 ,CONVERT(CHAR(8),GETDATE(), 3) as FechaActual
1462 ,'No hay' as cFechaPresentacion
1463 ,'No hay' as dFechaVencimiento
1464 ,@nSaldoUsado+@nSaldoAlCierre-@nRestarAlFinal AS fDeudaActual
1465 ,0 as fPagoMinimo
1466 ,@nSaldoAlCierre as fSaldoAlCierre
1467 ,0 as fTotalConsumos
1468 ,0 as fTotalPagos
1469 ,0 as fAcContTitular
1470 --Prestamos
1471 ,0 as fAcumuladoPrestamoContado
1472 ,0 as fAcumuladoPrestamoCuotas
1473 ,0 as fDisponiblePrestamoContado
1474 ,0 as fDisponiblePrestamoCuotas
1475 ,0 as fAcumuladoPeriodoConsumos
1476 ,0 as fAcumuladoPeriodoPagos
1477 --Agregado de Cuotas:
1478 ,0 AS fAcumuladoCuotasTitular
1479END
1480
1481RETURN case when @ierror = 0 then 0 else 1 end
1482
1483-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1484
1485
1486GO
1487SET QUOTED_IDENTIFIER OFF
1488GO
1489SET ANSI_NULLS ON
1490GO
1491
1492
1493
1494
1495----- adelantos con tarjeta
1496--exec dbo.spucc_oCtaCteStandard @pcNroTarjeta='280001301034', @piIdProtocolo='1', @piIdMensaje='2100', @piIdProducto='2' , @piIdComercio='0', @piIdSucursal='40', @piIdMoneda='32', @pbClienteInputMode='0', @pfMonto=' 10.00', @piCuotas='0', @piTRXNroTicket='132', @piPOSNroTicket='85353', @piPOSNroZ='548', @piPOSUser='23', @piPOSSupervisor='0', @piTrxModoAutorizacion='2', @piIdTerminal='300' , @pcPIN='1'