if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spArticulos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spArticulos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spClientes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spClientes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTiposDoc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTiposDoc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDoc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomRetIng]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNomRetIng]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalPspto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalPspto]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalPsptoCue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalPsptoCue]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spArticulos @Tanques BIT,@Fecha SMALLDATETIME
AS
 SELECT IdProducto,DescripProd,DescripAbrv,Referencia,CodBarras,VrCostPmd AS CostoPromed,Precio1,Precio2 
 ,Precio3,Precio4,Precio5,IvaInc,Tarifa AS TarifaIva,ExtciaMax,ExtciaAct,P.IdUnd AS CodUnidad,Unidad,IdEstado,P.Inactivo AS ProdInactivo
,FechaAdd AS FechaCreacion,FechaUpdate AS FechaActualizacion
 FROM  ProdMcias AS P INNER JOIN UndMed AS U ON P.IdUnd=U.IdUnd  LEFT JOIN Tablapor AS T ON P.IdTarIva=T.IdTarifa
 WHERE  TipoRef='PRODUCTO'  AND Tanques=@Tanques 
 AND ISNULL(FechaUpdate,FechaAdd)>@Fecha
ORDER BY IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spClientes @Fecha SMALLDATETIME
AS
 SELECT  IdClie AS NitCliente,RazonSocial,Codigo,TipoId,Dv,Direccion,T.IdLocal AS CodLocalidad,Localidad,Telefono,TelMovil,Fax,e_mail
 ,VrCupo,VrSaldo,VrCupo-VrSaldo AS Disponible,Cmntario1,C.IdEstado AS CodEstado,C.Inactivo AS ClieInactivo
,C.FechaAdd AS FechaCreacion,C.FechaUpdate  AS FechaActualizacion
 FROM TercCliente AS C INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 WHERE ISNULL(C.FechaUpdate,C.FechaAdd)>@Fecha
 ORDER BY RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTiposDoc @pmIdDoc VARCHAR(3),@pmTipoDoc VARCHAR(50),@pmConsUnico BIT
,@pmLeyenda VARCHAR(250),@pmInactivo BIT,@pmIntegraCias BIT
 AS 
 INSERT INTO Sys_TiposDoc (IdDoc,TipoDoc,ConsUnico,Leyenda,Inactivo,IntegraCias)
 VALUES (@pmIdDoc,@pmTipoDoc,@pmConsUnico,@pmLeyenda,@pmInactivo,@pmIntegraCias)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTiposDoc @pmIdDoc VARCHAR(3),@pmTipoDoc VARCHAR(50),@pmConsUnico BIT
,@pmLeyenda VARCHAR(250),@pmInactivo BIT,@pmIntegraCias BIT
 AS 
 UPDATE Sys_TiposDoc SET TipoDoc=@pmTipoDoc,ConsUnico=@pmConsUnico
,Leyenda=@pmLeyenda,Inactivo=@pmInactivo,IntegraCias=@pmIntegraCias
 WHERE IdDoc=@pmIdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDoc @pmIdDoc VARCHAR(3)
 AS 
 IF @pmIdDoc IS NULL
  SELECT IdDoc,TipoDoc,ConsUnico,Leyenda,IntegraCias
  FROM Sys_TiposDoc
  WHERE Inactivo=0 
  ORDER BY IdDoc
 ELSE
  SELECT IdDoc,TipoDoc,ConsUnico,Leyenda,IntegraCias,Inactivo
  FROM Sys_TiposDoc
  WHERE IdDoc=@pmIdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomRetIng @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdNom VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdEmpleado VARCHAR(16)=Null
,@pmNContrato INT=Null,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null
 AS 
 SELECT R.IdEmpleado AS IdEmpldo,Apellidos,Nombres,R.NContrato AS NumContto,MONTH(R.Fecha) AS Nmes,YEAR(R.Fecha) AS NumAnno,R.IdDep AS CodDep,C.IdInstala AS CodInstala
,R.IdCCosto AS CodCcto,R.IdSubCos AS CodSubcc,R.IdNom AS CodNom,R.pVehiculo AS PlacaVeh,R.VrSalario  AS Salbasico,TipoLiq,IdArea,IdTipCon
,D.IdConcepto AS CodConcepto,Concepto
,SUM(Cantidad) AS SCANT,SUM(VrDevngado) AS STotDev,SUM(VrDeducido) AS STotDed,SUM(VrBase) AS SValBase
,SUM(DiasBase) AS DIAS_BASE,SUM(D.DiasNoRem) AS SCanNoRem,SUM(DiasBase-D.DiasNoRem) AS DiasNeto
,SUM(VrBaseLiq) AS SbaseLiq
 FROM Trn_NomRet AS R INNER JOIN  Trn_NomRetDet AS  D ON R.IdEmpleado=D.IdEmpleado AND R.Numero=D.Numero
 INNER JOIN Empleados AS E ON R.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON R.NContrato=C.NContrato
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE Fecha BETWEEN @pmFecIni  AND @pmFecFin  AND ClaseLiq='LIQUDACION'
 AND R.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND R.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')   AND IdTipCon LIKE ISNULL(@pmIdTipCon,'%')
 AND R.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  
 AND (R.NContrato>=ISNULL(@pmNContrato,0)  AND R.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 GROUP BY  R.IdEmpleado,Apellidos,Nombres,R.NContrato,MONTH(R.Fecha),YEAR(R.Fecha),R.IdDep,C.IdInstala
,R.IdCCosto,R.IdSubCos,R.IdNom,R.pVehiculo,R.VrSalario,TipoLiq,IdArea,IdTipCon,D.IdConcepto,Concepto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_BalPspto @pmtmEst CHAR(2)
 AS 
 SELECT T.IdCuenta AS CdCuenta,NomCuenta,T.IdCCosto AS CdCcosto,CCosto,T.IdSubCos AS CdSubCentro,SubCosto,T.IdCia AS CdCia,Compania,VrPstoEne,VrSanEne,VrDebEne,VrCreEne,VrPstoFeb,VrSanFeb,VrDebFeb,VrCreFeb,VrPstoMar,VrSanMar,VrDebMar,VrCreMar
,VrPstoAbr,VrSanAbr,VrDebAbr,VrCreAbr,VrPstoMay,VrSanMay,VrDebMay,VrCreMay,VrPstoJun,VrSanJun,VrDebJun,VrCreJun,VrPstoJul,VrSanJul,VrDebJul,VrCreJul,VrPstoAgo,VrSanAgo,VrDebAgo,VrCreAgo,VrPstoSep,VrSanSep,VrDebSep,VrCreSep
,VrPstoOct,VrSanOct,VrDebOct,VrCreOct,VrPstoNov,VrSanNov,VrDebNov,VrCreNov,VrPstoDic,VrSanDic,VrDebDic,VrCreDic
,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Naturaleza,CentroCosto,SubCentro
 FROM tm_BalPspto AS T INNER JOIN Puc AS P ON T.IdCuenta=P.IdCuenta
 LEFT JOIN CentroCosto AS CC ON T.IdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON T.IdSubCos=SC.IdSubCos
 LEFT JOIN Companias AS C ON T.IdCia=C.IdCia
 LEFT JOIN tm_NivCue AS NV ON T.IdCuenta=NV.tmIdCuenta
 WHERE T.tmEst=@pmtmEst
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_BalPsptoCue @pmtmEst CHAR(2)
 AS
 SELECT IdCuenta AS  tmIdCuenta,COUNT(IdCia) AS StotItm
 FROM tm_BalPspto
 WHERE tmEst=@pmtmEst
 GROUP BY IdCuenta
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paDelTanqAforoFrac @pmIdTanque VARCHAR(4),@pmUndAforo DECIMAL(14,4)
 AS 
 DELETE FROM TanqAforoFrac
 WHERE IdTanque=@pmIdTanque 
 AND (UndAforo>=ISNULL(@pmUndAforo,0)  AND UndAforo<=ISNULL(@pmUndAforo,9999999999))

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTanqAforoFrac @pmIdTanque VARCHAR(4),@pmUndAforo DECIMAL(14,4),@pmFraccion DECIMAL(14,4),@pmMilimetros DECIMAL(14,4)
 AS 
 INSERT INTO TanqAforoFrac (IdTanque,UndAforo,Fraccion,Milimetros)
 VALUES (@pmIdTanque,@pmUndAforo,@pmFraccion,@pmMilimetros)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTanqAforoFrac @pmIdTanque VARCHAR(4),@pmUndAforo DECIMAL(14,4)
 AS 
 SELECT IdTanque,UndAforo,Fraccion,Milimetros
 FROM TanqAforoFrac
 WHERE IdTanque=@pmIdTanque AND UndAforo=@pmUndAforo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTanqAforoFracLta @pmIdTanque VARCHAR(4)=Null
 AS 
 SELECT A.IdTanque AS NumTanque,UndAforo,Fraccion,Milimetros,T.IdProducto AS CdProd,DescripProd,CapTanq,NivAgua,CapNeta
,SaldoActual,Descripcion,TnqGas,TnqUnido,TnqVirtual
 FROM TanqAforoFrac AS A INNER JOIN Tanques AS T ON A.IdTanque=T.IdTanque
 INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto
 WHERE T.Inactivo=0 AND A.IdTanque LIKE ISNULL(@pmIdTanque,'%')
 ORDER BY A.IdTanque,UndAforo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTanqAforoFrac @pmIdTanque VARCHAR(4),@pmUndAforo DECIMAL(14,4),@pmFraccion DECIMAL(14,4),@pmMilimetros DECIMAL(14,4)
 AS 
 UPDATE TanqAforoFrac SET Fraccion=@pmFraccion,Milimetros=@pmMilimetros
 WHERE IdTanque=@pmIdTanque AND UndAforo=@pmUndAforo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO