ALTER TABLE FmtoDetalle ADD ValorCol10 MONEY DEFAULT(0),ValorCol11 MONEY DEFAULT(0),ValorCol12 MONEY DEFAULT(0)
GO
ALTER TABLE tmDetalle ADD ValorCol10 MONEY DEFAULT(0),ValorCol11 MONEY DEFAULT(0),ValorCol12 MONEY DEFAULT(0)
GO
--PROCEDIMIENTOS ALMACENADOS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFmtoDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsFmtoDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstmDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstmDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstmDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstmDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFmtoEncabDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFmtoEncabDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytmDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytmDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytmDetalleCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytmDetalleCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytmDetalleCri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytmDetalleCri]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytmDetalleDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytmDetalleDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptmDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptmDetalle]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFmtoEncabDso] @pmIdFmto INT,@pmnAnno INT,@pmNumero INT,@pmEstado VARCHAR(20)=Null
 AS 
 SELECT E.IdFmto AS CodFmto,Formto,E.nAnno AS NumAnno,E.Numero AS NumEnvio,Modaldad,nVersion,Fecha,FecInicial,FecFinal,ValorTotal,CantReg,E.Estado AS EstadFmto,Observciones,TimeSys
--detalles
,Item,D.IdConcepto AS CodCpto,Concepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,CodPais,VrTotal,VrOtros,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9
,ValorCol10,ValorCol11,ValorCol12,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,D.Estado AS EstDetalle,Observ,MunNom,DptoNom,VrMinimo,Criterios,TipoDoc,CantMax
 FROM FmtoEncab AS E INNER JOIN FmtoDetalle AS D ON E.IdFmto=D.IdFmto AND E.nAnno=D.nAnno AND E.Numero=D.Numero
 INNER JOIN Formtos AS F ON E.IdFmto=F.IdFmto
 INNER JOIN Conceptos AS C ON D.IdFmto=C.IdFmto AND D.IdConcepto=C.IdConcepto
 LEFT JOIN TiposId AS TI ON D.TDoc=TI.IdTdoc
 WHERE E.IdFmto=@pmIdFmto 
 AND  (E.nAnno>=ISNULL(@pmnAnno,0)  AND E.nAnno<=ISNULL(@pmnAnno,2147483647))
 AND  (E.Numero>=ISNULL(@pmNumero,0)  AND E.Numero<=ISNULL(@pmNumero,2147483647))
 AND D.Estado LIKE ISNULL(@pmEstado,'%')
 ORDER BY E.nAnno,E.Numero,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytmDetalleCri] @pmIdEst CHAR(2),@pmIdFmto INT
 AS 
 SELECT IdEst,D.IdFmto AS CodFmto,Item,D.IdConcepto AS IdConcep,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial
,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,CodPais
,Estado,Observ,MunNom,DptoNom,VrMinimo,Criterios,Concepto
,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
 FROM tmDetalle AS D INNER JOIN Conceptos AS C ON D.IdFmto=C.IdFmto AND D.IdConcepto=C.IdConcepto
 WHERE IdEst=@pmIdEst AND D.IdFmto=@pmIdFmto AND  VrMinimo>0
 ORDER BY  TDoc,Nid,D.IdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytmDetalleDso] @pmIdEst CHAR(2),@pmIdFmto INT
 AS 
 SELECT Item,D.IdConcepto AS CodCpto,Concepto,TDoc,TipoDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion
 ,Mun,MunNom,Dpto,DptoNom,CodPais,VrTotal,VrOtros,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9
,ValorCol10,ValorCol11,ValorCol12,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,D.IdFmto AS CodFmto
 FROM tmDetalle AS D INNER JOIN Conceptos AS C ON D.IdFmto=C.IdFmto AND D.IdConcepto=C.IdConcepto
 LEFT JOIN TiposId AS TI ON D.TDoc=TI.IdTdoc
 WHERE IdEst=@pmIdEst AND D.IdFmto=@pmIdFmto
 ORDER BY  Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsFmtoDetalle_Sel] @pmIdEst CHAR(2),@pmIdFmto INT,@pmnAnno INT,@pmNumero INT,@pmEstado VARCHAR(20)=Null
 AS
 INSERT INTO FmtoDetalle (IdFmto,nAnno,Numero,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia
,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12)
 SELECT IdFmto,@pmnAnno,@pmNumero,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia
,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
 FROM tmDetalle 
 WHERE IdEst=@pmIdEst AND IdFmto=@pmIdFmto AND Estado like ISNULL(@pmEstado,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstmDetalle_Sel] @pmIdEst CHAR(2),@pmIdFmto INT,@pmnAnno INT,@pmNumero INT
 AS
 INSERT INTO tmDetalle (IdEst,IdFmto,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia
,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12)
 SELECT @pmIdEst,IdFmto,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia
,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
 FROM FmtoDetalle
 WHERE IdFmto=@pmIdFmto AND nAnno=@pmnAnno AND Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstmDetalle] @pmIdEst CHAR(2),@pmIdFmto INT,@pmItem INT,@pmIdConcepto INT,@pmTDoc INT,@pmNid VARCHAR(20),@pmDv CHAR(1),@pmApl1 VARCHAR(80),@pmApl2 VARCHAR(60),@pmNom1 VARCHAR(80),@pmNom2 VARCHAR(60),@pmRazSocial VARCHAR(250)
,@pmDireccion VARCHAR(250),@pmMun INT,@pmDpto INT,@pmVrTotal MONEY,@pmVrOtros MONEY,@pmReferncia VARCHAR(50),@pmTDcnt INT,@pmNitcnt VARCHAR(20),@pmDvcnt CHAR(1),@pmApl1cnt VARCHAR(80),@pmApl2cnt VARCHAR(60),@pmNom1cnt VARCHAR(80),@pmNom2cnt VARCHAR(60)
,@pmRazcnt VARCHAR(250),@pmEstado VARCHAR(20),@pmObserv VARCHAR(100),@pmMunNom VARCHAR(50),@pmDptoNom VARCHAR(50),@pmCodPais VARCHAR(8),@pmValorCol2 MONEY,@pmValorCol3 MONEY,@pmValorCol4 MONEY,@pmValorCol5 MONEY,@pmValorCol6 MONEY
,@pmValorCol7 MONEY,@pmValorCol8 MONEY,@pmValorCol9 MONEY,@pmValorCol10 MONEY,@pmValorCol11 MONEY,@pmValorCol12 MONEY
 AS 
 INSERT INTO tmDetalle (IdEst,IdFmto,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais
,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12)
 VALUES (@pmIdEst,@pmIdFmto,@pmItem,@pmIdConcepto,@pmTDoc,@pmNid,@pmDv,@pmApl1,@pmApl2,@pmNom1,@pmNom2,@pmRazSocial,@pmDireccion,@pmMun,@pmDpto,@pmVrTotal,@pmVrOtros,@pmReferncia,@pmTDcnt,@pmNitcnt,@pmDvcnt
,@pmApl1cnt,@pmApl2cnt,@pmNom1cnt,@pmNom2cnt,@pmRazcnt,@pmEstado,@pmObserv,@pmMunNom,@pmDptoNom,@pmCodPais,@pmValorCol2,@pmValorCol3,@pmValorCol4,@pmValorCol5,@pmValorCol6,@pmValorCol7,@pmValorCol8,@pmValorCol9,@pmValorCol10,@pmValorCol11,@pmValorCol12)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUptmDetalle] @pmIdEst CHAR(2),@pmIdFmto INT,@pmItem INT,@pmIdConcepto INT,@pmTDoc INT,@pmNid VARCHAR(20),@pmDv CHAR(1),@pmApl1 VARCHAR(80),@pmApl2 VARCHAR(60),@pmNom1 VARCHAR(80),@pmNom2 VARCHAR(60)
,@pmRazSocial VARCHAR(250),@pmDireccion VARCHAR(250),@pmMun INT,@pmDpto INT,@pmVrTotal MONEY,@pmVrOtros MONEY,@pmReferncia VARCHAR(50),@pmTDcnt INT,@pmNitcnt VARCHAR(20),@pmDvcnt CHAR(1),@pmApl1cnt VARCHAR(80),@pmApl2cnt VARCHAR(60)
,@pmNom1cnt VARCHAR(80),@pmNom2cnt VARCHAR(60),@pmRazcnt VARCHAR(250),@pmEstado VARCHAR(20),@pmObserv VARCHAR(100),@pmMunNom VARCHAR(50),@pmDptoNom VARCHAR(50),@pmCodPais VARCHAR(8),@pmValorCol2 MONEY,@pmValorCol3 MONEY,@pmValorCol4 MONEY
,@pmValorCol5 MONEY,@pmValorCol6 MONEY,@pmValorCol7 MONEY,@pmValorCol8 MONEY,@pmValorCol9 MONEY,@pmValorCol10 MONEY,@pmValorCol11 MONEY,@pmValorCol12 MONEY
 AS 
 UPDATE tmDetalle SET IdConcepto=@pmIdConcepto,TDoc=@pmTDoc,Nid=@pmNid,Dv=@pmDv,Apl1=@pmApl1,Apl2=@pmApl2,Nom1=@pmNom1,Nom2=@pmNom2,RazSocial=@pmRazSocial,Direccion=@pmDireccion,Mun=@pmMun,Dpto=@pmDpto,VrTotal=@pmVrTotal
,VrOtros=@pmVrOtros,Referncia=@pmReferncia,TDcnt=@pmTDcnt,Nitcnt=@pmNitcnt,Dvcnt=@pmDvcnt,Apl1cnt=@pmApl1cnt,Apl2cnt=@pmApl2cnt,Nom1cnt=@pmNom1cnt,Nom2cnt=@pmNom2cnt,Razcnt=@pmRazcnt,Estado=@pmEstado,Observ=@pmObserv,CodPais=@pmCodPais
,MunNom=@pmMunNom,DptoNom=@pmDptoNom,ValorCol2=@pmValorCol2,ValorCol3=@pmValorCol3,ValorCol4=@pmValorCol4,ValorCol5=@pmValorCol5,ValorCol6=@pmValorCol6,ValorCol7=@pmValorCol7
,ValorCol8=@pmValorCol8,ValorCol9=@pmValorCol9,ValorCol10=@pmValorCol10,ValorCol11=@pmValorCol11,ValorCol12=@pmValorCol12
 WHERE IdEst=@pmIdEst AND IdFmto=@pmIdFmto AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytmDetalle] @pmIdEst CHAR(2),@pmIdFmto INT,@pmItem INT
 AS 
 SELECT IdEst,IdFmto,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros
,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais
,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
 FROM tmDetalle
 WHERE IdEst=@pmIdEst AND IdFmto=@pmIdFmto
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY  Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytmDetalleCon] @pmIdEst CHAR(2),@pmIdFmto INT,@pmIdConcepto INT,@pmTDoc INT
,@pmNid VARCHAR(20),@pmItem INT
 AS 
 SELECT IdEst,IdFmto,Item,IdConcepto,TDoc,Nid,Dv,Apl1,Apl2,Nom1,Nom2,RazSocial,Direccion,Mun,Dpto,VrTotal,VrOtros
,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,Estado,Observ,MunNom,DptoNom,CodPais
,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
 FROM tmDetalle
 WHERE IdEst=@pmIdEst AND IdFmto=@pmIdFmto AND IdConcepto=@pmIdConcepto AND TDoc=@pmTDoc
 AND Nid=@pmNid AND Item<>@pmItem
 ORDER BY  Item
GO