if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetallePmd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetallePmd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquidaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiquidaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemCum_Muc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemCum_Muc]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetallePmd] @pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmModoPmd CHAR(1),@pmFecIni SMALLDATETIME
,@pmFecFin SMALLDATETIME,@pmIdConcepto VARCHAR(4)=Null
 AS 
 IF @pmModoPmd='F' --SALARIOS FIJO
  SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV,SUM(VrBaseLiq) AS SBASELIQ
  FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
  WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
  AND  ClaseLiq IN ('NOMINA','NOVEDAD')  AND VrBaseLiq>0 AND IdConcepto NOT IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC')
  GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
 ELSE 
  BEGIN
   IF @pmModoPmd='B' --SALARIO BASICO
    SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
   ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
  ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN','STC') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
    FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
    WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD')  AND IdConcepto IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC','DNR','SAN','STC')
   AND (VrBaseLiq>0 OR IdConcepto='DNR' OR IdConcepto='SAN' OR IdConcepto='STC')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  ELSE --SALARIO VARIABLE
   SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes, IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
  ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
 ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN','STC') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
   FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
   WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD') AND (VrBaseLiq>0 OR IdConcepto='DNR' OR IdConcepto='SAN' OR IdConcepto='STC')
   AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiquidaFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT LQ.TipDoc AS TipLiq,TipoDoc,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,CN.Compania AS NomCompania,LQ.Fecha AS FechaLiq,LQ.IdConcepto AS CdConcepto,Concepto
,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,VrTotal,LQ.CxPagar AS CPagar,LQ.TipCom AS CdTipCom,TipoCom,LQ.Comprobante AS NumComp,LQ.IdCiaCom AS CdCiaComp
,LQ.Anulado AS Anuldo,LQ.NumDev AS NDevolucion,LQ.FecDev AS FechDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida
,LQ.TimeSys AS FechaAdd,LQ.FecUpdate AS FechaAct,LQ.IdCiaCrea AS CdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de ordenes de pago
,LP.TipOdp AS TipOrden,LP.OrdPago AS NumOrden,LP.IdCiaOdp AS CdCiaOdp,COP.Compania AS NomCiaOrden,OP.Fecha AS FechaOdp,OP.TipMuc AS TipManif,OP.Manifiesto AS NumManif,OP.IdCiaMuc AS CdCiaManif
,OP.IdVehiculo AS PlacaVeh,OP.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,OP.VrTotalFletes,OP.VrDescuento AS Vr_Dcto,OP.VrRetencion AS Vr_RetFte,OP.VrReteIca AS Vr_RetIca,OP.VrAnticipos,OP.VrFaltantes,OP.VrSeguros
,OP.VrFondos,OP.VrAportes,OP.VrOtrosDctos,OP.VrImpuestos,OP.VrEstampilla,OP.VrOtrosPagos,OP.VrNeto AS Vr_Neto,VrAbonado,TotAbonado,OP.TarifaTabla,OP.TarifaFlete AS Tarifa_Flete,OP.UnidTarifa,OP.PesoTotal AS Peso_Total
,OP.Unidades,OP.Volumen AS VolmTotal,OP.PesoOrigen,OP.UnidOrigen,OP.VolOrigen,OP.PesoDestino,OP.UnidDestino,OP.VolDestino,OP.BaseRet,OP.BaseIca,OP.BaseSeg,OP.BaseImp,OP.TarifaRet,OP.TarifaIca,OP.TarifaSeg,OP.TarifaImp,OP.NumCheque,OP.Referencia AS Referncia,OP.EdoLiqCausac
,OP.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,OP.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,OP.IdRuta AS CdRuta,R.Ruta AS DescRuta,OP.nRemolque
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,OP.FecManif,VrApos,VrFam,VrFsc,VrFgi,VrTarApos ,VrTarFam ,VrTarFGi,VrTarFsc,VrBaseApos ,VrBaseFam,VrBaseFgi,VrBaseFsc,TPV.NumCuenta AS NumCtaProveedor,CTA.ClaseCuenta
,TPV.NitContac,TPV.NomContac,BA.Banco 
 FROM Trn_TraLiquida AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON LQ.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON LQ.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario 
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Trn_TraLiquidaOdp AS LP ON LQ.TipDoc=LP.TipDoc AND LQ.Liquidacion=LP.Liquidacion AND LQ.IdCia=LP.IdCia
--* SubConsulta: Ordenes de pago/ Liquidaciones
 INNER JOIN (SELECT OP.TipDoc,OP.OrdPago,OP.IdCia,OP.Fecha,OP.IdConcepto,OP.Modalidad,OP.Anulado,OP.Observacion,O.TipMuc,O.Manifiesto,O.IdCiaMuc
,O.IdVehiculo,O.IdConductor,O.IdPropietario,O.IdPoseedor,O.VrTotalFletes,O.VrDescuento,O.VrRetencion,O.VrReteIca,O.VrAnticipos,O.VrFaltantes,O.VrSeguros,O.VrFondos,O.VrAportes,O.VrReteCREE
,O.VrOtrosDctos,O.VrImpuestos,O.VrEstampilla,O.VrOtrosPagos,O.VrNeto,O.TarifaTabla,O.TarifaFlete,O.UnidTarifa,O.PesoTotal,O.Unidades,O.Volumen,O.PesoOrigen,O.UnidOrigen,O.VolOrigen,O.PesoDestino,O.UnidDestino,O.VolDestino
,O.BaseRet,O.BaseIca,O.BaseSeg,O.BaseImp,O.TarifaRet,O.TarifaIca,O.TarifaSeg,O.TarifaImp,O.VrConcPagos,O.VrConcDctos,O.VrConcFondo,O.VrConcSeguro,O.VrConcAporte,O.VrConcImpuesto,O.VrImpAvTa
,O.TipoLiq,O.TipEgr,O.Egreso,O.IdCiaEgr,O.FechaEgr,O.NumCheque,O.Referencia,O.CdCenCosto,O.CdSubCenCos,O.EdoLiqCausac
,M.Fecha AS FecManif,M.FecDespacho,M.IdOrigen,M.IdDestino,M.IdRuta,M.nRemolque,M.TipoAfiVehic,M.Remesa,M.IdCiaRem,M.Cumplido,M.IdCiaCump,M.FechaCump,M.EstCumplido
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 --Datos de Liq. ordenes de servico
 UNION ALL SELECT L.TipDoc,L.Liquidacion,L.IdCia,L.Fecha,L.IdConcepto,O.Modalidad,L.Anulado,L.Observacion,L.TipOds,L.NumOrden,L.IdCiaOds
,L.IdVehiculo,L.IdConductor,L.IdPoseedor,L.IdPoseedor,L.VrTotal,0,L.VrRetencion,L.VrReteIca,L.VrAnticipo,0,0,0,0,L.VrReteCREE,L.VrDescuento,L.VrIva,0,L.VrOtros,L.VrNeto,L.TarifaPago,L.TarifaPago,'UNIDAD',0,L.Cantidad,0,0,0,0,0,0,0,L.BaseRet,L.BaseIca,0,L.BaseImp
,L.TarifaRet,L.TarifaIca,0,L.TarifaImp,L.VrConcCargos,L.VrConcDed,0,0,0,0,0,0,L.TipEgr,L.Egreso,L.IdCiaEgr,Null,L.NumCheque,L.Referencia,L.CdCCosto,L.CdSubCos,0,O.Fecha,O.FecDespacho,O.IdOrigen,O.IdDestino,O.CdRuta,O.nRemolque,O.TipoAfiVehic,O.NumRemesa
,O.CdCiaRem,O.NumCump,O.CdCiaCum,Null,O.EstCumplido
 FROM Trn_TraOrdenLiq AS L INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia WHERE L.TipDoc='OPT') AS OP ON LP.TipOdp=OP.TipDoc AND LP.OrdPago=OP.OrdPago AND LP.IdCiaOdp=OP.IdCia
--* Fin consulta liquidaciones
 INNER JOIN Vehiculos AS V ON OP.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Terceros AS CDT ON OP.IdConductor=CDT.IdTercero
 INNER JOIN Companias AS COP ON LP.IdCiaOdp=COP.IdCia
 INNER JOIN Localidades AS CO ON OP.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON OP.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON OP.IdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN (SELECT TipDoc,Documento,IdCiaDoc,SUM(VrAbonado) AS TotAbonado FROM Trn_ComFactura WHERE (TipFac='ODP' OR TipFac='OPT') AND (TipDoc='ODP' OR TipDoc='OPT') GROUP BY TipDoc,Documento,IdCiaDoc)
 AS ABO ON LP.TipOdp=ABO.TipDoc AND LP.OrdPago=ABO.Documento AND LP.IdCiaOdp=ABO.IdCiaDoc
 --fondos-conceptos
 LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN Valor ELSE 0 END ) AS VrFgi,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN Vrtarifa ELSE 0 END ) as VrTarFGi,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN VrBase ELSE 0 END ) as VrBaseFgi
 ,SUM(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN Valor ELSE 0 END ) AS VrFam,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN Vrtarifa ELSE 0 END ) as VrTarFam,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN VrBase ELSE 0 END ) as VrBaseFam,
 SUM(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN Valor ELSE 0 END ) AS VrFsc, MAX(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN Vrtarifa ELSE 0 END ) as VrTarFsc,MAX(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN VrBase ELSE 0 END ) as VrBaseFsc
,SUM(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN Valor ELSE 0 END) AS VrApos,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN Vrtarifa ELSE 0 END ) as VrTarApos,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN VrBase ELSE 0 END ) as VrBaseApos
 FROM Trn_DetCuentas WHERE (TipDoc='ODP' OR TipDoc='OPT') GROUP BY TipDoc,Documento,IdCia) AS FCN ON LP.TipOdp=FCN.TipDoc AND LP.OrdPago=FCN.Documento AND LP.IdCiaOdp=FCN.IdCia
 LEFT JOIN TercProvee TPV ON LQ.IdPoseedor=TPV.IdProv  LEFT JOIN ClaseCta CTA ON TPV.IdClase=CTA.IdClase 
 LEFT JOIN Bancos BA ON TPV.IdBanco=BA.IdBanco 
 WHERE LQ.TipDoc=@pmTipDoc AND LQ.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND LQ.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemCum_Muc] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_TraRemCum (tmNumero,tmItem,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifClie,tmTarifPago,tmUndTarif,tmUndTarifPago,tmCantCargue
,tmPesoCargue,tmVolCargue,tmCasesCargue,tmCajasCargue,tmPaletsCargue,tmEstadoCump,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmDetalle,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino
,tmIdDestino,tmTarifTabla,tmTipoCum,tmMotivoSusp,tmHoraLlegaCargue,tmHoraEntraCargue,tmHoraSaleCargue,tmHoraLlegaDescargue,tmHoraEntraDescargue,tmHoraSaleDescargue,tmCdCCosto,tmCdSubCos,tmNitCliente)
SELECT @pmtmNumero,D.Item,TipRem,Remesa,IdCiaRem,ItemRem,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets,R.TarifClie,CASE WHEN D.TarifPago>0 THEN D.TarifPago ELSE R.TarifPago END,UndTarifa,UndTarifPago,Cantidad
,PesoNeto,Volumen,Cases,Cajas,Palets,1,Remision,DocCliente,Referencia1,Referencia2,Referencia3,DetalleCump,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,D.TarifTabla
,'','',FecLlegCargue,FecInicioCargue,FecFinCargue,FecLlegdesc,FecIniciodesc,FecFindesc,'','',''
 FROM  Trn_TraManifRem AS D INNER JOIN Trn_TraRemMcias AS R ON D.TipRem=R.TipDoc AND D.Remesa=R.NumOrden AND D.IdCiaRem=R.IdCia AND D.ItemRem=R.Item
 WHERE D.TipDoc=@pmTipDoc AND D.Manifiesto=@pmManifiesto AND D.IdCia=@pmIdCia
GO