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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexSub @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase
,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexSubc @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd
,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 AND EsCombo<>0 AND Combo<>0 AND TipoRef<>'SERVICIO'  AND  EsProdBase=0
 GROUP BY IdSubgrupo,K.IdProducto,DescripProd
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexCc @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmIdProducto VARCHAR(16)
 AS 
 SELECT IdProducto AS tmIdProducto,CdCCosto AS tmCdCCosto,CdSubCos AS tmCdSubCos
,CdLocal AS tmCdLocal
 FROM  Trn_Kardex
 WHERE   TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
  AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 ORDER BY Item

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Cartera @pmtmEst CHAR(2),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmVrSaldo MONEY
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmComision DECIMAL(14,4),@pmTarifDcto DECIMAL(14,4),@pmTipoDcto CHAR(1),@pmTipoBase VARCHAR(10),@pmFecLmtDcto SMALLDATETIME,@pmIdConcepto VARCHAR(4)
,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(150),@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmFecUltPago SMALLDATETIME,@pmNumNota INT,@pmIdCiaNot CHAR(2),@pmTarifInt DECIMAL(14,4),@pmFecLiqMora SMALLDATETIME
,@pmCtaForma VARCHAR(30),@pmBeneficiario VARCHAR(150),@pmRefCheque VARCHAR(50),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4)
 AS 
 INSERT INTO tm_Cartera (tmEst,TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,VrSaldo,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,IdConcepto,Referencia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot
,TarifInt,FecLiqMora,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco)
 VALUES (@pmtmEst,@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrAbonado,@pmVrSaldo,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmComision,@pmTarifDcto,@pmTipoDcto,@pmTipoBase,@pmFecLmtDcto
,@pmIdConcepto,@pmReferencia,@pmDetalle,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmFecUltPago,@pmNumNota,@pmIdCiaNot,@pmTarifInt,@pmFecLiqMora,@pmCtaForma,@pmBeneficiario,@pmRefCheque,@pmNumCheque,@pmCdBanco)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Cartera_Saf @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 INSERT INTO tm_Cartera (tmEst,TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,VrSaldo,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,IdConcepto
,Referencia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco)
SELECT @pmtmEst,'SAF',MAX(Recibo),IdCia,0,getdate(),getdate(),SUM(VrAFavor-VrOtrosDb),SUM(VrAbono),SUM((VrAFavor-VrOtrosDb)-VrAbono)
,IdCliente,IdAgencia,IdVend,0,0,'%','TOTAL',NULL,'0','SALDO A FAVOR','SALDO A FAVOR','0',0,'00',Null,0,'00',0,Null,'0','','','',''
 FROM Trn_RecAfavor
 WHERE  (VrAFavor-VrOtrosDb)>VrAbono AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 GROUP BY IdCia,IdCliente,IdAgencia,IdVend
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Cartera_Sel @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 INSERT INTO tm_Cartera (tmEst,TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,VrSaldo,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,IdConcepto
,Referencia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco)
SELECT @pmtmEst,TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,VrFactura-VrAbonado,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,IdConcepto
,Referencia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,'0','','','',''
 FROM Trn_Vencimientos 
 WHERE  VrFactura>VrAbonado  AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND  IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_Cartera @pmtmEst CHAR(2),@pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
 AS 
 SELECT  IdCliente,T.RazonSocial AS NomCliente,V.IdAgencia AS CdAgencia,Agencia,TipDoc,TipoDoc,Factura,V.IdCia AS CdCia,Compania,Item
,VrFactura,VrAbonado,CASE TipDoc WHEN 'SAF' THEN V.VrSaldo*-1  ELSE  V.VrSaldo END AS ValorSaldo,Fecha,FechaVence,DATEDIFF(day,FechaVence,@pmFecActual) AS DiasMora
,V.IdConcepto AS CdConcepto,Concepto,V.Referencia AS FacReferencia,Detalle,V.IdVend AS CdVend,VN.RazonSocial AS Vendedor,V.Comision AS TarifComs
,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora
--información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,VrCupo,CLI.VrSaldo AS SaldoClie,Contrato,CLI.NContrato AS CliNumContrato
,CodAgencia,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep
,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,A.NContrato AS AgeNumContrato,CiaCont,CLI.IdVend AS VendedorPre,VNC.RazonSocial AS NomVendedorPre
,PZ.DiasPago AS DiasPlazoAge,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco
-- PZC.DiasPago AS DiasPlazoAgeCom
 FROM tm_Cartera AS V INNER JOIN  Companias AS CI ON V.IdCia=CI.IdCia
 INNER JOIN  Terceros AS T ON V.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON V.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON V.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Agencias AS A ON V.IdAgencia=A.IdAgencia  
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Plazos AS PZ ON A.IdPlazo=PZ.IdPlazo   INNER JOIN Terceros AS VNC ON CLI.IdVend=VNC.IdTercero
 LEFT JOIN Conceptos AS C ON V.IdConcepto=C.IdConcepto  LEFT JOIN Sys_TiposDoc AS TD ON V.TipDoc=TD.IdDoc
 -- LEFT JOIN Plazos AS PZC ON A.CdPlazoComb=PZC.IdPlazo
 WHERE tmEst=@pmtmEst AND V.VrSaldo>@pmSaldo
 ORDER BY T.RazonSocial,FechaVence
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO