if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoRmsn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoRmsn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdOrdenLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdOrdenLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTallajesMov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTallajesMov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTallasMovDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTallasMovDoc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTallasMovPed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTallasMovPed]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdConsInsu]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdConsInsu]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoRmsn] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS
SELECT O.TipDoc,O.Pedido,O.IdCia,Compania,O.Fecha,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia,Agencia,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,O.TarifaCom
,D.Item,D.IdProducto,DescripProd,D.IdBodega AS CdBodega,B.Bodega,D.Salidas,D.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,D.VrUnitario,D.VrPrecio,D.TarifaIva
,D.VrIvaSal,D.TarifaDct,D.VrDctoSal,D.Descripcion,D.Comptmntos AS DescTallas,D.galsneto AS CantFact,D.TipOrd AS DetTipDoc,D.NumOrden AS DetNumDoc,D.IdCiaOrd AS DetCiaDoc,D.ItemCombo AS ItemDoc
,D.Remision,D.IdCiaRem,D.galsbruto AS CantTotalRem,D.Cotizacion,D.IdCiaCot,D.EsProdBase,(CASE WHEN D.Unidades>0 AND D.TipDocDev='PPD' THEN D.Unidades ELSE D.Salidas END) AS Cant_Aprob,D.Referencia,D.Referencia2
,D.CdOperario,OP.RazonSocial AS NomOperario,D.ComisnOper,D.TipDocDev,D.Servcios
,O.DiasEntraga,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,O.CargoContac,O.FechaVence
,O.MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.RefPedido,O.NumAutSicom AS NumOrdenCom,O.TipFac,O.Factura,O.IdCiaFac,O.FechaFact,O.NumAprob,O.IdCiaApr,O.FecAprob,O.DetalleAprob
,O.Modalidad,O.Vigencia,O.IdConcepto,Concepto,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.Anulado,O.TimeSys AS FechaCrea,O.IdUsuario AS CdUsuario,Usuario
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb
,P.Referencia AS Prod_Referencia,P.TipoRef,P.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,P.IdMarca AS CdMarca,Marca,P.Tamano
,P.ExtciaMin,P.ExtciaMax,P.ExtciaAct,P.VrCostAnt,P.VrCosto,P.VrCostPmd,P.FecUltcom,P.FecUltVta,P.Seriales,P.Lotes,P.Combo,P.Tanques,P.Tallaje,P.DescripLong
,P.Precio1,P.Precio2,P.Precio3,P.Precio4,P.Precio5
--detalle de remisiones
,RM.NumRemison,RM.IdCia AS CiaRemision,RM.ItemRem,RM.Salidas AS CantRemision,RM.Referencia AS RefRemision,RM.Descripcion AS DescRemision,RM.DescTallas AS TallasRem
,RM.Fecha AS FecRemision,RM.FechaDesp,RM.FechaVence AS FecVenceRem,RM.DiasEntraga AS DiasEntregaRem,RM.Observacion AS ObservRemision
FROM Trn_Opedido AS O INNER JOIN Trn_Kardex AS D ON O.TipDoc=D.TipDoc AND O.Pedido=D.Documento AND O.IdCia=D.IdCia
INNER JOIN Companias AS CI ON O.IdCia=CI.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero
INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN Bodegas AS B ON D.IdBodega=B.IdBodega
INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea
INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
LEFT JOIN Terceros AS OP ON D.CdOperario=OP.IdTercero
--remisiones
LEFT JOIN (SELECT K.Documento AS NumRemison,K.IdCia,K.Item AS ItemRem,K.IdProducto,K.IdBodega,K.Entradas,K.Salidas,K.Unidades,K.VrUnitario,K.VrPrecio,K.pVehiculo
,K.Referencia,K.Descripcion,K.Comptmntos AS DescTallas,K.TipOrd,K.NumOrden,K.IdCiaOrd,K.ItemCombo AS ItemOrden,K.Servcios
,R.Fecha,R.FechaDesp,R.FechaVence,R.DiasEntraga,R.IdLocEnv,R.DirEnvio,R.EmpTrans,R.NitEmpTrans,R.pVehiculo AS RemVehiculo,R.CdConductor,R.DetalleEnvio
,R.TipFac,R.Factura,R.IdCiaFac,R.Modalidad,R.Observacion
FROM Trn_Kardex AS K INNER JOIN Trn_Remision AS R ON K.TipDoc=R.TipDoc AND K.Documento=R.Remision AND K.IdCia=R.IdCia
WHERE K.TipDoc='REM' AND K.TipOrd='PED' AND K.NumOrden>0 AND R.Anulado=0) AS RM ON O.TipDoc=RM.TipOrd AND O.Pedido=RM.NumOrden AND O.IdCia=RM.IdCiaOrd AND D.Item=RM.ItemOrden
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (O.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTallasMovPed] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
SELECT TM.TipDoc,TM.Documento,TM.IdCia,Compania,TM.Item,TM.Fecha,TM.ItemKdx,TM.IdProducto AS CdProducto,DescripProd
,TM.IdBodega AS CdBodega,Bodega,TM.IdUbic,TM.NumTalla,TM.Entradas,TM.Salidas,TM.CantPedido,TM.CantRem,TM.CantFactura
,TL.Cantidad AS CantSaldo,TL.Pedidos AS CantPed,O.Fecha,O.IdCliente
FROM Trn_TallasMov AS TM INNER JOIN ProdMcias AS P ON TM.IdProducto=P.IdProducto
INNER JOIN Bodegas AS B ON TM.IdBodega=B.IdBodega INNER JOIN Companias AS CN ON TM.IdCia=CN.IdCia
INNER JOIN Trn_Opedido AS O ON TM.TipDoc=O.TipDoc AND TM.Documento=O.Pedido AND TM.IdCia=O.IdCia
LEFT JOIN Trn_Tallajes AS TL ON TM.IdProducto=TL.IdProducto AND TM.IdCia=TL.IdCia AND TM.IdBodega=TL.IdBodega
AND TM.NumTalla=TL.NumTalla
WHERE TM.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (TM.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTallasMovDoc] @pmTipDoc VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2)
AS 
SELECT TM.TipDoc,TM.Documento,TM.IdCia,Compania,TM.Item,TM.Fecha,TM.ItemKdx,TM.IdProducto AS CdProducto,DescripProd
,TM.IdBodega AS CdBodega,Bodega,TM.IdUbic,TM.NumTalla,TM.Entradas,TM.Salidas,TM.CantRem,TM.CantFactura
,TL.Cantidad AS CantSaldo,TL.Pedidos AS CantPed
FROM Trn_TallasMov AS TM INNER JOIN ProdMcias AS P ON TM.IdProducto=P.IdProducto
INNER JOIN Bodegas AS B ON TM.IdBodega=B.IdBodega INNER JOIN Companias AS CN ON TM.IdCia=CN.IdCia
LEFT JOIN Trn_Tallajes AS TL ON TM.IdProducto=TL.IdProducto AND TM.IdCia=TL.IdCia AND TM.IdBodega=TL.IdBodega
AND TM.NumTalla=TL.NumTalla
WHERE TM.TipDoc=@pmTipDoc AND TM.Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin AND TM.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdConsInsu] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2),@pmIdProducto VARCHAR(16),@pmIdInsumo VARCHAR(16))
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Insumos BIT
  IF @pmIdInsumo IS NULL
   SET @Insumos=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdConsInsu WHERE TipDoc=@pmTipDoc AND NumCons=@pmNumCons AND IdCia=@pmIdCia AND IdProducto=@pmIdProducto AND IdInsumo LIKE @pmIdInsumo)
     BEGIN
      SET @Insumos=1
     END
    ELSE
     BEGIN
      SET @Insumos=0
     END
   END
 RETURN @Insumos
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdOrdenLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdInsumo VARCHAR(16)=Null
 AS 
SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia,Compania,O.Fecha,O.IdTipProc,TipoProceso
,D.Item,D.IdProducto AS CdProducto,D.Descripcion,D.Cantidad,D.CostoEst,D.Terminado,D.Defectuoso,D.Rechazado
,D.MotivoDev,D.TipCons,D.NumCons,D.IdCiaCons,D.ItemCons,CD.TipPed,CD.Pedido,CD.IdCiaPed,CD.ItemPed,CD.CantAprob
,CD.Cantidad AS CantConsolida,CD.CantTdo,CD.Tallas,CD.EdoOrden AS EdoConsolida,D.TipEntInv,D.NumEntrada,D.CdCiaEnt,D.ItemKdxEnt,D.CantEntrada
,O.Modalidad,O.ConPago,O.IdTaller AS CdTaller,TL.NomTaller,TL.TelCelular,TL.TipoTaller,O.NitTercero,T.RazonSocial,O.NomContacto,O.TelContacto
,O.EdoOrden,O.ProcFinal,TP.IndOrden AS ProcOrden,O.FecEntrega,O.Num_Entrega AS NumEntArreglo,O.CdCiaEnt AS CiaEntArreglo,EG.FechaUltEnt,EG.MaxNumEntrega AS UltNumEntrega,EG.MaxIdCia AS Cd_CiaEnt
,O.Observacion,O.Anulado,O.FecDev,O.IdEstado,ED.Estado,O.TimeSys AS FechaCrea,O.IdUsuario,U.Usuario
--datos del producto
,P.DescripProd,P.TipoRef,P.Referencia,P.CodBarras,G.IdLinea,Linea,SG.IdGrupo,Grupo,P.IdSubgrupo,Subgrupo,P.IdMarca,Marca,P.Tamano,P.Color
,P.UndMed,UM.Unidad AS DescUndMed,P.IdUnd,UM.Unidad,P.Tallaje,P.Inactivo
--datos del pedidO
,OP.Fecha AS FecPedido,OP.FechaVence,OP.IdCliente,TC.RazonSocial AS NomCliente,OP.IdAgencia,OP.IdVend,VN.RazonSocial AS NomVendedor,OP.DiasEntraga,OP.RefPedido
,OP.TipFac,OP.Factura,OP.IdCiaFac,OP.FechaFact,OP.NumAutSicom as NumOrdenCom
FROM Trn_ProdOrden AS O INNER JOIN Trn_ProdOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
INNER JOIN TiposProceso AS TP ON O.IdTipProc=TP.IdTipProc INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON O.IdCia=CI.IdCia INNER JOIN EstadoDoc  AS ED ON O.IdEstado=ED.IdEstado INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
INNER JOIN Talleres AS TL ON O.IdTaller=TL.IdTaller 
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN UndMed AS UM ON P.IdUnd=UM.IdUnd
INNER JOIN SubGrupos AS SG ON P.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo
INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
LEFT JOIN Terceros AS T ON O.NitTercero=T.IdTercero
LEFT JOIN Trn_ProdConsProc AS CD ON D.TipCons=CD.TipDoc AND D.NumCons=CD.NumCons AND D.IdCiaCons=CD.IdCia AND D.ItemCons=CD.Item
LEFT JOIN Trn_Opedido AS OP ON CD.TipPed=OP.TipDoc AND CD.Pedido=OP.Pedido AND CD.IdCiaPed=OP.IdCia
LEFT JOIN Terceros AS TC ON OP.IdCliente=TC.IdTercero LEFT JOIN Terceros AS VN ON OP.IdVend=VN.IdTercero
--datos ultima entrega
LEFT JOIN (SELECT TipOrden,NumOrden,IdCiaOrden,MAX(FecEntrega) AS FechaUltEnt,MAX(NumEntrega) AS MaxNumEntrega,MAX(IdCia) AS MaxIdCia
FROM Trn_ProdEntrega WHERE TipDoc='OEP' AND Anulado=0 GROUP BY TipOrden,NumOrden,IdCiaOrden) AS EG ON O.TipDoc=EG.TipOrden AND O.NumOrden=EG.NumOrden AND O.IdCia=EG.IdCiaOrden
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (O.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND dbo.FuncProdConsInsu(D.TipCons,D.NumCons,D.IdCiaCons,D.IdProducto,@pmIdInsumo)=1
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTallajesMov] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmAnnoAnt INT,@pmMesAnt INT
 AS  
SELECT T.IdProducto,DescripProd,T.IdCia,CI.Compania,T.IdBodega,Bodega,T.IdUbic,T.NumTalla,T.Cantidad,T.Pedidos,S.SaldoActual
,D.Fecha,D.TipDoc,TD.TipoDoc,D.Documento,D.IdCia AS DetIdCia,CM.Compania AS DetCompania,D.Item,D.Entradas,D.Salidas,D.CantPedido,D.CantRem,D.CantFactura
,dbo.FuncAcuInvTallasSan(@pmAnnoAnt,@pmMesAnt,D.IdCia,D.IdProducto,D.IdBodega,D.IdUbic,D.NumTalla) AS SaldoAnt,D.ItemKdx,D.IdUbic AS DetUbic
--datos del producto 
,P.TipoRef,P.Referencia,P.CodBarras,G.IdLinea,Linea,SG.IdGrupo,Grupo,P.IdSubgrupo,Subgrupo,P.IdMarca,Marca,P.Tamano,P.Color
,P.UndMed,UM.Unidad AS DescUndMed,P.IdUnd,U.Unidad,P.Tallaje,P.Inactivo
FROM Trn_Tallajes AS T INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto
INNER JOIN Bodegas AS B ON T.IdBodega=B.IdBodega INNER JOIN Companias AS CI ON T.IdCia=CI.IdCia
INNER JOIN SubGrupos AS SG ON P.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo
INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
INNER JOIN UndMed AS U ON P.IdUnd=U.IdUnd 
LEFT JOIN Trn_TallasMov AS D ON T.IdProducto=D.IdProducto AND T.NumTalla=D.NumTalla AND T.IdCia=D.IdCia AND T.IdBodega=D.IdBodega
LEFT JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc LEFT JOIN Companias AS CM ON D.IdCia=CM.IdCia
LEFT JOIN Sys_Um AS UM ON P.UndMed=UM.UndMed
LEFT JOIN ProdSaldos AS S ON T.IdProducto=S.IdProducto AND T.IdBodega=S.IdBodega AND T.IdCia=S.IdCia
WHERE ((D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin) OR D.Fecha IS NULL)
GO