ALTER TABLE Trn_Traslados ADD TdOrden VARCHAR(3),CdCiaOrden CHAR(2)
GO
ALTER TABLE Trn_ProdConsInsu ADD TipTdo VARCHAR(3),Traslado INT DEFAULT(0) NOT NULL,IdCiaTdo CHAR(2)
GO
ALTER TABLE Trn_ProdConsTdo ADD TipTdo VARCHAR(3),Traslado INT DEFAULT(0) NOT NULL,IdCiaTdo CHAR(2)
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('CPO','IMPORTAR CONCEPTOS DE GASTOS DEL PRESUPUESTO DE ANTICIPOS (ORDENES DE SERVICIO)','BOOLEAN','0',5,'TRANS')
GO
INSERT INTO Sys_Roles  (IdObj,IdRole,Funcion)
 VALUES ('FRMDCOS','REF','Actualizar Referencia en Orden de servicio')
GO
CREATE TABLE Trn_ProdEntregaInsu ( 
TipDoc VARCHAR(3) DEFAULT ('OEP') NOT NULL,
NumEntrega INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
ItemCons INT DEFAULT ((0)) NOT NULL,
IdInsumo VARCHAR(16) NOT NULL,
CantTraslado DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
CantSobrante DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
CdBodOrig VARCHAR(4) NOT NULL,
CdBodTdo VARCHAR(4) NOT NULL,
TipTdo VARCHAR(3),
Traslado INT DEFAULT ((0)) NOT NULL,
IdCiaTdo CHAR(2),
CdTipProc VARCHAR(4)
 CONSTRAINT PK_Trn_ProdEntregaInsu PRIMARY KEY CLUSTERED (TipDoc,NumEntrega,IdCia,Item),
 CONSTRAINT CK_Trn_ProdEntregaInsuCdBodOrig CHECK ((len([CdBodOrig])>(0))),
 CONSTRAINT CK_Trn_ProdEntregaInsuCdBodTdo CHECK ((len([CdBodTdo])>(0))),
 CONSTRAINT CK_Trn_ProdEntregaInsuIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_Trn_ProdEntregaInsuIdInsumo CHECK ((len([IdInsumo])>(0))),
 CONSTRAINT CK_Trn_ProdEntregaInsuTipDoc CHECK ((len([TipDoc])>(0))))
GO
ALTER TABLE Trn_ProdEntregaInsu ADD 
 CONSTRAINT FK_Trn_ProdEntregaInsu_ProdMcias FOREIGN KEY (IdInsumo) REFERENCES [dbo].[ProdMcias] (IdProducto)

--SP 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Nts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Nts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_NtsDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_NtsDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdEntregaInsuFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdEntregaInsuFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexNts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexNts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraslados_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraslados_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTrasladosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTrasladosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTrasladosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTrasladosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraslados]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdEntregaInsuFmt] @pmTipDoc VARCHAR(3),@pmNumEntregaIni INT,@pmNumEntregaFin INT,@pmIdCia CHAR(2)
AS 
SELECT D.TipDoc,D.NumEntrega,D.IdCia,D.Item,D.ItemCons,D.IdInsumo,IM.DescripProd AS Insumo,D.CantTraslado,D.CantSobrante
,D.CdBodOrig,B.Bodega,D.CdBodTdo,BD.Bodega AS BodegaDtno,D.TipTdo,D.Traslado,D.IdCiaTdo,D.CdTipProc,TP.TipoProceso
FROM Trn_ProdEntregaInsu AS D INNER JOIN ProdMcias AS IM ON D.IdInsumo=IM.IdProducto
INNER JOIN Bodegas AS B ON D.CdBodOrig=B.IdBodega
LEFT JOIN TiposProceso AS TP ON D.CdTipProc=TP.IdTipProc
LEFT JOIN Bodegas AS BD ON D.CdBodTdo=BD.IdBodega
WHERE D.TipDoc=@pmTipDoc AND D.NumEntrega BETWEEN @pmNumEntregaIni AND @pmNumEntregaFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexNts] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmComptmntos,tmSalidas,tmVrUnitario
,tmSalidas*tmVrUnitario AS VrTotal,tmCdProdEquiv,tmUnidades,tmIdUnd,Unidad
,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmNumLote,tmFecVceLote,Tanques,tmTipDoc,tmDocumento,tmIdCia,tmItemCbo
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmSalidas>0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTrasladosRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT N.TipDoc AS Tip_Doc,Traslado,N.IdCia AS CdCia,Compania,N.Fecha AS FechaDoc,N.IdConcepto AS CdConcepto,Concepto,N.IdTercero AS NitTercero,RazonSocial
,VrSubTotal,Cantidad,NumDcmto,N.TdOrden,N.NumOrden AS Num_Orden,N.CdCiaOrden,N.IdCCosto AS CdCcosto,CC.CCosto AS CentCosto,N.IdSubCos AS CdSubCent,SC.SubCosto AS SubcCosto,Modalidad
,OrigenAdd,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado
,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario
,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario
,VrPrecio,VrCostProm,TarifaIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal,VrCostoEnt,VrCostoSal,VrReteEnt,VrReteSal,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote
,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,pVehiculo,K.Referencia AS KarReferencia
,Descripcion,Comptmntos,CdProdEquiv,TipOrd,K.NumOrden AS KarNumOrden,IdCiaOrd,Cotizacion,IdCiaCot
,Remision,IdCiaRem,Factura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,Precio1,Precio2,Precio3,Precio4,Precio5,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_Traslados AS N  INNER JOIN Trn_Kardex AS K ON N.TipDoc=K.TipDoc AND N.Traslado=K.Documento AND N.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia  
 INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 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 ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd  
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 WHERE N.TipDoc=@pmTipDoc  AND N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND N.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  
 ORDER BY N.IdCia,Traslado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraslados_Cr] @pmTipDoc VARCHAR(3),@pmTrasladoIni INT,@pmTrasladoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc AS Tip_Doc,TipoDoc,Traslado,N.IdCia AS CdCia,Compania,N.Fecha AS FechaDoc,N.IdConcepto AS CdConcepto,Concepto,N.IdTercero AS NitTercero,RazonSocial
,VrSubTotal,Cantidad,NumDcmto,N.TdOrden,N.NumOrden AS Num_Orden,N.CdCiaOrden,N.IdCCosto AS CdCcosto,CC.CCosto AS CentCosto,N.IdSubCos AS CdSubCent,SC.SubCosto AS SubcCosto,Modalidad
,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado
,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario,Leyenda
,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario
,VrPrecio,VrCostProm,TarifaIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal,VrCostoEnt,VrCostoSal,VrReteEnt,VrReteSal,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote
,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,pVehiculo,K.Referencia AS KarReferencia
,Descripcion,Comptmntos,CdProdEquiv,TipOrd,K.NumOrden AS KarNumOrden,IdCiaOrd,Cotizacion,IdCiaCot
,Remision,IdCiaRem,Factura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,Precio1,Precio2,Precio3,Precio4,Precio5,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_Traslados AS N  INNER JOIN Trn_Kardex AS K ON N.TipDoc=K.TipDoc AND N.Traslado=K.Documento AND N.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 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 ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd  
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposCom AS TC ON N.TipCom=TC.IdCom
 WHERE N.TipDoc=@pmTipDoc AND Traslado BETWEEN @pmTrasladoIni AND @pmTrasladoFin AND N.IdCia=@pmIdCia
 ORDER BY Traslado,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_Nts] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont
,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,Entradas,Salidas,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,VrIvaEnt+VrIvaSal,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec
,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN TipOrd ELSE TipDoc END,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN NumOrden ELSE Documento END,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN IdCiaOrd ELSE IdCia END
,VrBase,CdMoneda,VrTasaCamb,'REM',Remision,IdCiaRem,Referencia2,FecOrden,0,0,0,'',0,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq,BaseIvaCom,ImpCarbono,IngBaseCom
,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 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 EsProdBase=0 AND Salidas>0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_NtsDev] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont
,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,Salidas,Entradas,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,VrIvaEnt+VrIvaSal,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec
,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN TipOrd ELSE TipDoc END,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN NumOrden ELSE Documento END,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN IdCiaOrd ELSE IdCia END
,VrBase,CdMoneda,VrTasaCamb,'REM',Remision,IdCiaRem,Referencia2,FecOrden,0,0,0,'',0,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco
,BaseIvp,TarifaIvp,IvaIngProd
 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 EsProdBase=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTrasladosLta]  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmTrasladoIni INT=Null,@pmTrasladoFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT Traslado,IdCia,Fecha,N.IdConcepto AS CdConcepto,Concepto,N.IdTercero AS NitTercero,RazonSocial,VrSubTotal,Cantidad,NumDcmto,TdOrden,NumOrden,CdCiaOrden,N.IdCCosto AS CdCcosto,CCosto
,IdSubCos,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Traslados AS N INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero
 INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON  N.IdCCosto=CC.IdCCosto
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND Traslado BETWEEN ISNULL(@pmTrasladoIni,0) AND ISNULL(@pmTrasladoFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND N.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY IdCia,Traslado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16)
,@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4),@pmNumDcmto INT,@pmNumOrden INT,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmModalidad VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTdOrden VARCHAR(3),@pmCdCiaOrden CHAR(2),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME
,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Traslados (TipDoc,Traslado,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,Cantidad,NumDcmto,NumOrden,IdCCosto,IdSubCos,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion
,IdEstado,TimeSys,IdCiaCrea,IdUsuario,TdOrden,CdCiaOrden)
 VALUES (@pmTipDoc,@pmTraslado,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdTercero,@pmVrSubTotal,@pmCantidad,@pmNumDcmto,@pmNumOrden,@pmIdCCosto,@pmIdSubCos,@pmModalidad,@pmOrigenAdd
,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTdOrden,@pmCdCiaOrden)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4)
,@pmNumDcmto INT,@pmNumOrden INT,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTdOrden VARCHAR(3),@pmCdCiaOrden CHAR(2),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Traslados SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdTercero=@pmIdTercero,VrSubTotal=@pmVrSubTotal,Cantidad=@pmCantidad,NumDcmto=@pmNumDcmto,NumOrden=@pmNumOrden
,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,TdOrden=@pmTdOrden,CdCiaOrden=@pmCdCiaOrden
,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Traslado=@pmTraslado AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Traslado,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,Cantidad,NumDcmto,NumOrden,IdCCosto,IdSubCos,Modalidad
,TdOrden,CdCiaOrden,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Traslados
 WHERE TipDoc=@pmTipDoc AND Traslado=@pmTraslado AND IdCia=@pmIdCia
GO