ALTER TABLE FmtoDetalle ADD ValorCol19 MONEY DEFAULT(0),ValorCol20 MONEY DEFAULT(0)
,ValorCol21 MONEY DEFAULT(0),ValorCol22 MONEY DEFAULT(0),ValorCol23 MONEY DEFAULT(0)
,EntidadInfo INT DEFAULT(0)
GO
ALTER TABLE tmDetalle ADD ValorCol19 MONEY DEFAULT(0),ValorCol20 MONEY DEFAULT(0)
,ValorCol21 MONEY DEFAULT(0),ValorCol22 MONEY DEFAULT(0),ValorCol23 MONEY DEFAULT(0)
,EntidadInfo INT DEFAULT(0)
GO
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].[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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytmDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytmDetalle]
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,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23
,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,D.Estado AS EstDetalle,Observ
,MunNom,DptoNom,VrMinimo,Criterios,TipoDoc,CantMax,email,emailcnt,TipoFide,SubTipFide,EntidadInfo
 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
,email,emailcnt,Estado,Observ,MunNom,DptoNom,VrMinimo,Criterios,Concepto
,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9,ValorCol10,ValorCol11,ValorCol12
,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,TipoFide,SubTipFide,EntidadInfo
 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,email,CodPais,VrTotal,VrOtros,ValorCol2,ValorCol3,ValorCol4,ValorCol5,ValorCol6,ValorCol7,ValorCol8,ValorCol9
,ValorCol10,ValorCol11,ValorCol12,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23
,Referncia,TDcnt,Nitcnt,Dvcnt,Apl1cnt,Apl2cnt,Nom1cnt,Nom2cnt,Razcnt,emailcnt,TipoFide,SubTipFide,EntidadInfo,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,email,emailcnt,TipoFide,SubTipFide
,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,EntidadInfo)
 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,email,emailcnt,TipoFide,SubTipFide
,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,EntidadInfo
 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
,email,emailcnt,TipoFide,SubTipFide,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,EntidadInfo)
 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
,email,emailcnt,TipoFide,SubTipFide,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,EntidadInfo
 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,@pmemail VARCHAR(100),@pmemailcnt VARCHAR(100),@pmTipoFide VARCHAR(4),@pmSubTipFide VARCHAR(4),@pmValorCol13 MONEY,@pmValorCol14 MONEY,@pmValorCol15 MONEY
,@pmValorCol16 MONEY,@pmValorCol17 MONEY,@pmValorCol18 MONEY,@pmValorCol19 MONEY,@pmValorCol20 MONEY,@pmValorCol21 MONEY,@pmValorCol22 MONEY,@pmValorCol23 MONEY,@pmEntidadInfo 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,email,emailcnt,TipoFide,SubTipFide,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18
,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23,EntidadInfo)
 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
,@pmemail,@pmemailcnt,@pmTipoFide,@pmSubTipFide,@pmValorCol13,@pmValorCol14,@pmValorCol15,@pmValorCol16,@pmValorCol17,@pmValorCol18,@pmValorCol19,@pmValorCol20,@pmValorCol21,@pmValorCol22,@pmValorCol23,@pmEntidadInfo)
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,@pmemail VARCHAR(100),@pmemailcnt VARCHAR(100),@pmTipoFide VARCHAR(4),@pmSubTipFide VARCHAR(4)
,@pmValorCol13 MONEY,@pmValorCol14 MONEY,@pmValorCol15 MONEY,@pmValorCol16 MONEY,@pmValorCol17 MONEY,@pmValorCol18 MONEY,@pmValorCol19 MONEY,@pmValorCol20 MONEY,@pmValorCol21 MONEY,@pmValorCol22 MONEY,@pmValorCol23 MONEY,@pmEntidadInfo INT
 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,email=@pmemail,emailcnt=@pmemailcnt,TipoFide=@pmTipoFide,SubTipFide=@pmSubTipFide
,ValorCol13=@pmValorCol13,ValorCol14=@pmValorCol14,ValorCol15=@pmValorCol15,ValorCol16=@pmValorCol16,ValorCol17=@pmValorCol17,ValorCol18=@pmValorCol18,ValorCol19=@pmValorCol19,ValorCol20=@pmValorCol20,ValorCol21=@pmValorCol21
,ValorCol22=@pmValorCol22,ValorCol23=@pmValorCol23,EntidadInfo=@pmEntidadInfo
 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
,ValorCol13,ValorCol14,ValorCol15,ValorCol16,ValorCol17,ValorCol18,ValorCol19,ValorCol20,ValorCol21,ValorCol22,ValorCol23
,email,emailcnt,TipoFide,SubTipFide,EntidadInfo
 FROM tmDetalle
 WHERE IdEst=@pmIdEst AND IdFmto=@pmIdFmto
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY  Item
GO