if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdPedidoInsuFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdPedidoInsuFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComFactura_Uni]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsComFactura_Uni]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaLta]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryComFacturaLta] @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
,@pmIdCuenta VARCHAR(16)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
,@pmTipFac VARCHAR(3)=Null,@pmTipDoc VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT C.IdCuenta AS CdCuenta,NomCuenta,C.IdProveedor,RazonSocial,C.TipFac,TipoDoc,C.Factura,C.IdCia AS CdCia,Compania
,Item,VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo,FecEmision,FecVence,DATEDIFF(day,FecVence,@pmFecActual) AS DiasMora
,C.TipDoc,C.Documento,C.IdCiaDoc,C.TipCom,TipoCom,C.Comprobante,C.ItemCom,CM.Fecha AS FechaComp,Detalle,C.Referencia,C.pVehiculo,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob
--datos del proveedor
,TipoId,Dv,T.Codigo AS CodigoProv,NomCial,SiglaRaz,T.Direccion AS DirProveedor,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDep,Departamento
,Telefono,Fax,e_mail,SitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
 FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero
 INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN  Companias AS CI ON C.IdCia=CI.IdCia
 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
 LEFT JOIN TiposCom AS TC ON C.TipCom=TC.IdCom  LEFT JOIN Sys_TiposDoc AS TD ON C.TipFac=TD.IdDoc
 LEFT JOIN TercProvee AS TP ON C.IdProveedor=TP.IdProv  LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco
 LEFT JOIN Trn_Comprobantes AS CM ON C.TipCom=CM.TipCom AND C.Comprobante=CM.Comprobante AND C.IdCia=CM.IdCia
 WHERE (VrFactura-VrAbonado)>@pmSaldo AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND C.IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND C.TipFac LIKE ISNULL(@pmTipFac,'%')
 AND (FecEmision>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecEmision<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY C.IdCuenta,RazonSocial,FecVence
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdPedidoInsuFmt] @pmTipDoc VARCHAR(3),@pmPedidoIni INT,@pmPedidoFin INT,@pmIdCia CHAR(2)
AS
SELECT D.TipDoc,D.Pedido,D.IdCia,D.Item,D.ItemDet,D.IdProducto,P.DescripProd,D.IdInsumo,IM.DescripProd AS DescInsumo
,D.IdTipProc,TP.TipoProceso,TP.IndOrden,D.Cantidad,IM.IdSubgrupo AS CdSubgpo,Subgrupo
FROM Trn_ProdPedidoInsu AS D INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
INNER JOIN ProdMcias AS IM ON D.IdInsumo=IM.IdProducto INNER JOIN SubGrupos AS S ON IM.IdSubgrupo=S.IdSubgrupo
INNER JOIN TiposProceso AS TP ON D.IdTipProc=TP.IdTipProc
WHERE D.TipDoc=@pmTipDoc AND D.Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsComFactura_Uni] @pmIdProveedor VARCHAR(16),@pmNewProveedor VARCHAR(16)
 AS 
 INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob)
 SELECT TipFac,Factura,IdCia,Item,@pmNewProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob
 FROM Trn_ComFactura WHERE IdProveedor=@pmIdProveedor
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME
,@pmFecVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT
,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT,@pmMontoAprob MONEY,@pmFecProgPago SMALLDATETIME,@pmObservAprob VARCHAR(250)
 AS 
 INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob)
 VALUES (@pmTipFac,@pmFactura,@pmIdCia,@pmItem,@pmIdProveedor,@pmIdCuenta,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmFecEmision,@pmFecVence,@pmVrFactura,@pmVrAbonado,@pmTipCom,@pmComprobante,@pmItemCom
,@pmReferencia,@pmDetalle,@pmpVehiculo,@pmVehPropio,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmEstadoApr,@pmMontoAprob,@pmFecProgPago,@pmObservAprob)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME,@pmFecVence SMALLDATETIME
,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT
,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT,@pmMontoAprob MONEY,@pmFecProgPago SMALLDATETIME,@pmObservAprob VARCHAR(250)
 AS 
 UPDATE Trn_ComFactura SET IdCuenta=@pmIdCuenta,TipDoc=@pmTipDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,FecEmision=@pmFecEmision,FecVence=@pmFecVence,VrFactura=@pmVrFactura,VrAbonado=@pmVrAbonado,TipCom=@pmTipCom,Comprobante=@pmComprobante,ItemCom=@pmItemCom
,Referencia=@pmReferencia,Detalle=@pmDetalle,pVehiculo=@pmpVehiculo,VehPropio=@pmVehPropio,TipRef=@pmTipRef,DocRef=@pmDocRef,IdCiaRef=@pmIdCiaRef,EstadoApr=@pmEstadoApr,MontoAprob=@pmMontoAprob,FecProgPago=@pmFecProgPago,ObservAprob=@pmObservAprob
 WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem AND IdProveedor=@pmIdProveedor
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmIdProveedor VARCHAR(16),@pmItem INT
 AS 
 SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom
,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob
 FROM Trn_ComFactura
 WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND IdProveedor=@pmIdProveedor
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647)) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComFacturaDso] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmIdCia CHAR(2)=Null,@pmTipFac VARCHAR(3)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmpVehiculo VARCHAR(10)=Null,@pmTipCom VARCHAR(3)=Null,@pmComprobante INT=Null
AS
 SELECT  TipFac, Factura, IdCia, Item,IdCuenta,IdProveedor,T.RazonSocial AS Proveedor,TipDoc, Documento, IdCiaDoc, FecEmision, FecVence, VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo
,TipCom, Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob,FecProgPago,ObservAprob
 FROM Trn_ComFactura AS V INNER JOIN Terceros AS T ON V.IdProveedor=T.IdTercero
 WHERE V.FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND (VrFactura-VrAbonado)>=@pmSaldo AND IdCia like ISNULL(@pmIdCia,'%%') AND TipFac like ISNULL(@pmTipFac,'%')
 AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND  IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND pVehiculo LIKE ISNULL(@pmpVehiculo,'%')
 AND TipCom LIKE ISNULL(@pmTipCom,'%')
 AND (Comprobante>=ISNULL(@pmComprobante,-1)  AND Comprobante<=ISNULL(@pmComprobante,2147483647)) 
ORDER BY TipFac,Factura,Item
GO
--junio 3/2020
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
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
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.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
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