if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoOpp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoOpp]
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 dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Ord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Ord]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexPed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexPed]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdPedidoInsu]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdPedidoInsu]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdOrdenTipo]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdOrdenTipo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdConsProc]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdConsProc]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdConsProcFinal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdConsProcFinal]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdOrdenFecha]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdOrdenFecha]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncProdOrdenNum]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[FuncProdOrdenNum]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexPed] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrSubTotal
,tmTarifaIva,tmVrIva,((tmSalidas*tmVrPrecio)-tmVrDcto)+tmVrIva AS VrTotal,tmComptmntos,tmSobretasa,tmImpGlobal,tmSoldicom,tmTasaNac,tmTasaDep,tmTasaMun
,tmVrBruto,tmListaPrec,tmUnidades,tmReferencia,tmDescripcion,tmCodTarDct
,tmIdVend,tmComision,tmCodTarCom,tmServcios,Tanques,tmVrUnitario,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipDoc,tmDocumento,tmIdCia,tmRec_Costo,tmMgenCont
,tmIvaComb,tmImpCarb,tmCdOperario,tmComisnOper,tmCodTarCmc,tmpVehiculo,tmBaseIvp,tmTarifaIvp,tmIvaIngProd,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
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
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
,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].[paInstm_Kdex_Ord] @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,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO') THEN Salidas-CantObseq 
 WHEN (TipDoc='PED' AND TipDocDev='PPD' AND Unidades>0) THEN Unidades ELSE Salidas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END
,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,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdOrdenTipo] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2),@pmItemCons INT,@pmModalidad VARCHAR(10))
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Ordenes BIT
  IF @pmModalidad IS NULL
   SET @Ordenes=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdOrden INNER JOIN Trn_ProdOrdenDet ON Trn_ProdOrden.TipDoc=Trn_ProdOrdenDet.TipDoc AND Trn_ProdOrden.NumOrden=Trn_ProdOrdenDet.NumOrden AND Trn_ProdOrden.IdCia=Trn_ProdOrdenDet.IdCia
     WHERE Trn_ProdOrdenDet.TipCons=@pmTipDoc AND Trn_ProdOrdenDet.NumCons=@pmNumCons AND Trn_ProdOrdenDet.IdCiaCons=@pmIdCia AND Trn_ProdOrdenDet.ItemCons=@pmItemCons
     AND Trn_ProdOrden.Anulado=0 AND Trn_ProdOrden.Modalidad=@pmModalidad)
     BEGIN
      SET @Ordenes=1
     END
    ELSE
     BEGIN
      SET @Ordenes=0
     END
   END
 RETURN @Ordenes
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdOrdenFecha] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2),@pmItemCons INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME)
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Ordenes BIT
  IF @pmFechaIni IS NULL
   SET @Ordenes=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdOrden INNER JOIN Trn_ProdOrdenDet ON Trn_ProdOrden.TipDoc=Trn_ProdOrdenDet.TipDoc AND Trn_ProdOrden.NumOrden=Trn_ProdOrdenDet.NumOrden AND Trn_ProdOrden.IdCia=Trn_ProdOrdenDet.IdCia
     WHERE Trn_ProdOrdenDet.TipCons=@pmTipDoc AND Trn_ProdOrdenDet.NumCons=@pmNumCons AND Trn_ProdOrdenDet.IdCiaCons=@pmIdCia AND Trn_ProdOrdenDet.ItemCons=@pmItemCons
     AND Trn_ProdOrden.Anulado=0 AND Trn_ProdOrden.Fecha BETWEEN @pmFechaIni AND @pmFechaFin)
     BEGIN
      SET @Ordenes=1
     END
    ELSE
     BEGIN
      SET @Ordenes=0
     END
   END
 RETURN @Ordenes
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdOrdenNum] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2),@pmItemCons INT,@pmNumOrdenIni INT,@pmNumOrdenFin INT)
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Ordenes BIT
  IF @pmNumOrdenIni IS NULL
   SET @Ordenes=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdOrdenDet WHERE TipCons=@pmTipDoc AND NumCons=@pmNumCons AND IdCiaCons=@pmIdCia AND ItemCons=@pmItemCons
     AND NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin)
     BEGIN
      SET @Ordenes=1
     END
    ELSE
     BEGIN
      SET @Ordenes=0
     END
   END
 RETURN @Ordenes
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdPedidoInsu] (@pmTipDoc VARCHAR(3),@pmPedido 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_ProdPedidoInsu WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido 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 FUNCTION [dbo].[FuncProdConsProc] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2)
,@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmItemPed INT,@pmIdTipProc VARCHAR(4))
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Procesos BIT
  IF @pmIdTipProc IS NULL
   SET @Procesos=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdConsProc WHERE TipDoc=@pmTipDoc AND NumCons=@pmNumCons AND IdCia=@pmIdCia
     AND TipPed=@pmTipPed AND Pedido=@pmPedido AND IdCiaPed=@pmIdCiaPed AND ItemPed=@pmItemPed AND IdTipProc=@pmIdTipProc)
     BEGIN
      SET @Procesos=1
     END
    ELSE
     BEGIN
      SET @Procesos=0
     END
   END
 RETURN @Procesos
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncProdConsProcFinal] (@pmTipDoc VARCHAR(3),@pmNumCons INT,@pmIdCia CHAR(2)
,@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmItemPed INT,@pmProcFinal BIT)
RETURNS BIT
 AS  
 BEGIN
  DECLARE @Procesos BIT
  IF @pmProcFinal IS NULL
   SET @Procesos=1
  ELSE
   BEGIN 
    IF EXISTS (SELECT 1 FROM Trn_ProdConsProc WHERE TipDoc=@pmTipDoc AND NumCons=@pmNumCons AND IdCia=@pmIdCia
     AND TipPed=@pmTipPed AND Pedido=@pmPedido AND IdCiaPed=@pmIdCiaPed AND ItemPed=@pmItemPed AND ProcFinal=@pmProcFinal)
     BEGIN
      SET @Procesos=1
     END
    ELSE
     BEGIN
      SET @Procesos=0
     END
   END
 RETURN @Procesos
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoOpp] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdInsumo VARCHAR(16)=Null,@pmIdTipProc VARCHAR(4)=Null,@pmProcFinal BIT=Null,@pmModalidad VARCHAR(10)=NuLL,@pmFecOrdenIni SMALLDATETIME=Null,@pmFecOrdenFin SMALLDATETIME=Null
,@pmNumOrdenIni INT=Null,@pmNumOrdenFin INT=Null
AS
--desde @pmIdInsumo: son parametros opcionales para comprobar en funciones IF EXISTS
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.Unidades AS CantAprobado,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.galsbruto AS CantRemision,D.EsProdBase,(CASE WHEN D.Unidades>0 THEN D.Unidades ELSE D.Salidas END) AS Cant_AprobPed
,C.NumCons,C.IdCia AS IdCiaCons,DC.Item AS ItemCons,DC.CantPedido AS CantPedidoCons,DC.CantAprob AS CantAprobCons,DC.CantTdo AS CantTdoCons,DC.CdBodTdo,BT.Bodega AS BodegaTdo
--resumen procesos
,OP.CantProcCons,OP.NumOrdenProc,OP.CiaOrdenProc,OP.Cant_OrdenProc,OP.Cant_Arreglos,OP.NumEntrega,OP.IdCiaEntrega,OP.Cant_Entrega,OP.Cant_Rechazo,OP.CantEntInvent,OP.NumEntInvent,OP.CiaEntInvent,OP.Costo_Entrega,OP.Costo_Arreglos
,IC.Cant_Salidas,IC.Costo_Insumos,OP.Ord_Terminados,OP.Ord_Pendientes
,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.TipRem,D.Remision,D.IdCiaRem,O.Modalidad,O.Vigencia,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
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 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 Trn_ProdConsDet AS DC ON D.TipDoc=DC.TipPed AND D.Documento=DC.Pedido AND D.IdCia=DC.IdCiaPed AND D.Item=DC.ItemPed
LEFT JOIN Trn_ProdConsolida AS C ON DC.TipDoc=C.TipDoc AND DC.NumCons=C.NumCons AND DC.IdCia=C.IdCia
LEFT JOIN Bodegas AS BT ON DC.CdBodTdo=BT.IdBodega
--PROCESOS
LEFT JOIN (SELECT CP.TipDoc,CP.NumCons,CP.IdCia,CP.TipPed,CP.Pedido,CP.IdCiaPed,CP.ItemPed
,SUM(CASE WHEN CP.ProcFinal=1 THEN CP.Cantidad ELSE 0 END) AS CantProcCons
,MAX(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.NumOrden ELSE 0 END) AS NumOrdenProc
,MAX(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.IdCia ELSE Null END) AS CiaOrdenProc
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.Cantidad ELSE 0 END) AS Cant_OrdenProc
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 AND O.Modalidad='ARREGLO' THEN OD.Cantidad ELSE 0 END) AS Cant_Arreglos
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.CantEntrada ELSE 0 END) AS CantEntInvent
,MAX(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.NumEntrada ELSE 0 END) AS NumEntInvent
,MAX(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 THEN OD.CdCiaEnt ELSE 0 END) AS CiaEntInvent
,MAX(CASE WHEN ISNULL(E.Anulado,0)=0 AND CP.ProcFinal=1 THEN ED.NumEntrega ELSE 0 END) AS NumEntrega
,MAX(CASE WHEN ISNULL(E.Anulado,0)=0 AND CP.ProcFinal=1 THEN ED.IdCia ELSE Null END) AS IdCiaEntrega
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 AND ISNULL(E.Anulado,0)=0 THEN ED.Cantidad+ED.Defectuoso ELSE 0 END) AS Cant_Entrega
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND CP.ProcFinal=1 AND ISNULL(E.Anulado,0)=0 THEN ED.Rechazado ELSE 0 END) AS Cant_Rechazo
,SUM(CASE WHEN O.Modalidad<>'ARREGLO' AND ISNULL(O.Anulado,0)=0 AND ISNULL(E.Anulado,0)=0 THEN (((ED.Cantidad+ED.Defectuoso)-ED.Rechazado)*ED.CostoUnit)-ED.VrDcto ELSE 0 END) AS Costo_Entrega
,SUM(CASE WHEN O.Modalidad='ARREGLO' AND ISNULL(O.Anulado,0)=0 AND ISNULL(E.Anulado,0)=0 THEN (((ED.Cantidad+ED.Defectuoso)-ED.Rechazado)*ED.CostoUnit)-ED.VrDcto ELSE 0 END) AS Costo_Arreglos
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND ((ISNULL(OD.Terminado,0)+ISNULL(OD.Defectuoso,0))-ISNULL(OD.Rechazado,0))>=ISNULL(OD.Cantidad,0) THEN 1 ELSE 0 END) AS Ord_Terminados
,SUM(CASE WHEN ISNULL(O.Anulado,0)=0 AND ((ISNULL(OD.Terminado,0)+ISNULL(OD.Defectuoso,0))-ISNULL(OD.Rechazado,0))<ISNULL(OD.Cantidad,0) THEN 1 ELSE 0 END) AS Ord_Pendientes
FROM Trn_ProdConsProc AS CP LEFT JOIN Trn_ProdOrdenDet AS OD ON CP.TipDoc=OD.TipCons AND CP.NumCons=OD.NumCons AND CP.IdCia=OD.IdCiaCons AND CP.Item=OD.ItemCons
LEFT JOIN Trn_ProdOrden AS O ON OD.TipDoc=O.TipDoc AND OD.NumOrden=O.NumOrden AND OD.IdCia=O.IdCia 
LEFT JOIN Trn_ProdEntregaDet AS ED ON OD.TipDoc=ED.TipOrden AND OD.NumOrden=ED.NumOrden AND OD.IdCia=ED.IdCiaOrden AND OD.Item=ED.ItemOrden
LEFT JOIN Trn_ProdEntrega AS E ON ED.TipDoc=E.TipDoc AND ED.NumEntrega=E.NumEntrega AND ED.IdCia=E.IdCia
WHERE CP.TipDoc='CPP' GROUP BY CP.TipDoc,CP.NumCons,CP.IdCia,CP.TipPed,CP.Pedido,CP.IdCiaPed,CP.ItemPed)
AS OP ON D.TipDoc=OP.TipPed AND D.Documento=OP.Pedido AND D.IdCia=OP.IdCiaPed AND D.Item=OP.ItemPed AND DC.TipDoc=OP.TipDoc AND DC.NumCons=OP.NumCons AND DC.IdCia=OP.IdCia
--Costo de insumos
LEFT JOIN (SELECT IM.TipDoc,IM.NumCons,IM.IdCia,IM.TipPed,IM.Pedido,IM.IdCiaPed,IM.ItemPed,SUM(IM.Cantidad) AS Cant_Insumos
,SUM(IM.CantSalidas) AS Cant_Salidas,SUM(IM.CantSalidas*IM.CostoUntSai) AS Costo_Insumos
FROM Trn_ProdConsInsu AS IM WHERE IM.TipDoc='CPP' GROUP BY IM.TipDoc,IM.NumCons,IM.IdCia,IM.TipPed,IM.Pedido,IM.IdCiaPed,IM.ItemPed) AS IC ON D.TipDoc=IC.TipPed AND D.Documento=IC.Pedido AND D.IdCia=IC.IdCiaPed AND D.Item=IC.ItemPed AND DC.TipDoc=IC.TipDoc AND DC.NumCons=IC.NumCons AND DC.IdCia=IC.IdCia
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (O.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND ISNULL(C.Anulado,0)=0
AND dbo.FuncProdPedidoInsu(O.TipDoc,O.Pedido,O.IdCia,D.IdProducto,@pmIdInsumo)=1 
AND dbo.FuncProdConsProc(DC.TipDoc,DC.NumCons,DC.IdCia,O.TipDoc,O.Pedido,O.IdCia,D.Item,@pmIdTipProc)=1
AND dbo.FuncProdConsProcFinal(DC.TipDoc,DC.NumCons,DC.IdCia,O.TipDoc,O.Pedido,O.IdCia,D.Item,@pmProcFinal)=1
AND dbo.FuncProdOrdenTipo(DC.TipDoc,DC.NumCons,DC.IdCia,DC.Item,@pmModalidad)=1
AND dbo.FuncProdOrdenFecha(DC.TipDoc,DC.NumCons,DC.IdCia,DC.Item,@pmFecOrdenIni,@pmFecOrdenFin)=1
AND dbo.FuncProdOrdenNum(DC.TipDoc,DC.NumCons,DC.IdCia,DC.Item,@pmNumOrdenIni,@pmNumOrdenFin)=1
GO