if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocSoporte]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraConc_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraConc_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Dev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Dev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Dlg]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Dlg]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Leg]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Leg]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraFacRemesas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFacRemesas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraLiqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraLiqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehRemolq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehRemolq_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehRemolq_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDevFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDevFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSopDevLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSopDevLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporte]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporteFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporteFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporteRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporteRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetConcLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetConcLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFactLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFactLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptosFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptosFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptosItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptosItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFacRemesasItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFacRemesasItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqDetalleFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqDetalleFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehRemolq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemolqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehRemolqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDocSopDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocSopDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocSoporte]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpVehRemolq]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRen]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmNovEstLiq INT=Null,@pmNovEstFact INT=Null
 AS 
SELECT RM.TipDoc AS TipMuc,RM.Manifiesto AS NumManif,RM.IdCia AS CdCia,Compania,M.Fecha AS MucFecha,M.FecDespacho AS MucFecDespacho,RM.TipRem AS TipRem,RM.Remesa AS NumRemesa,RM.IdCiaRem AS CdCiaRem
,RM.ItemRem AS ItemRemsa,R.Fecha AS RemFecha,R.FecDespacho AS RemFecDespacho,IdMercancia,DescripMcias,D.Cantidad AS RemCantidad,D.PesoNeto AS RemPesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS RemVolumen,UndVol,Cases,Cajas,Palets
,D.TarifClie AS RemTarifClie,UndTarifa,D.VrDeclarado AS RemVrDeclara,D.VrSeguro AS RemVrSeguro,D.TarifSeguro AS RemTarifSeguro,RM.TarifPago AS MucTarifPago,RM.TarifTabla AS MucTarifTabla,UndTarifPago,D.Cumplido AS NumCumplido,D.IdCiaCump AS CdCiaCump
,D.FechaCump AS FecCumplido,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,UndTarCump,PagoCump,UndTarPagoCump,DetalleCump,D.TarifClieFac AS RemTarifFact,D.TarifOdp AS RemTarifOdp,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,NitRemite,Remitente,NitDestntario,Destinatario,D.IdOrigen AS CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.IdDestino AS CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia,R.Modalidad AS TipoRemesa,M.IdVehiculo AS PlacaVeh,M.TipoAfiVehic AS TipoAfiVeh,M.IdConductor AS CedCondutor,CDT.RazonSocial AS NomConductor,M.nRemolque AS NumRemolque
,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,M.VrFletes AS MucVrFletes,M.VrRetencion AS MucVrRetencion,M.VrReteIca AS MucVrReteIca,M.VrAnticipo+VrAntAdic AS MucAnticipos,M.TarifaRet AS MucTarifRet,M.TarifaIca AS MucTarifIca,M.IdRuta AS CdRuta
,M.TipOdp AS CdTipOdp,M.OrdPago AS MucNumOdp,M.IdCiaOdp AS MucCiaOdp,M.FechaOdp AS FecOrdenPago,EstOrden,M.Observacion AS MucObserv
--datos de orden pago
,OP.PesoTotal AS OdpPesoNeto,OP.Unidades AS OdpCantidad,OP.Volumen AS OdpVolumen,OP.TarifaTabla AS OdpTarifTabla,OP.TarifaFlete AS OdpTarifFlete,OP.UnidTarifa AS OdpUndTarifa,OP.VrTotalFletes AS OdpTotalFletes,OP.VrDescuento AS OdpVrDcto
,OP.VrRetencion AS OdpVrRetFte,OP.VrReteIca AS OdpReteIca,OP.VrAnticipos AS OdpAnticipos,OP.VrFaltantes AS OdpFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla
,VrOtrosPagos,OP.VrNeto AS OdpVrNetoPagar,VrRemesas,VrRecCaja,OP.TarifaRet AS OdpTarifRet,OP.TarifaIca AS OdpTarifIca
,OP.IdPoseedor AS OdpNitPoseedor,ODP.Anulado AS OdpAnulado,ODP.Observacion AS OdpObserv,TipEgr,Egreso,IdCiaEgr,NumCheque,OP.Referencia AS OdpReferencia
,R.NumPedido AS nPedido,IdCiaPed,FechaPed,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,R.EstCumplido AS RemEdoCumplido,R.EstFactura AS RemEdoFactura,SerieGuia,NumGuia,R.Observacion AS RemObserv
--columnas de detalles ocultas,TipFac,Factura,IdCiaFac,FechaFac,TipOdp,NumeroOdp,IdCiaOdp,PesoCont
--datos de factura
,D.TipFac AS CdTipFact,Factura,NumFactura,D.IdCiaFac AS CdCiaFact,FechaFac,FacCantidad,FacUnidades,FacPesoNeto,FacVolumen,FacValorTotal,FacCostoTotal,FacFaltantes
,NumDevFactura,DfaCantidad,DfaUnidades,DfaPesoNeto,DfaVolumen,DfaValorTotal,DfaCostoTotal,DfaFaltantes
,NumNotaDb,NumNotaCr,NotCantidad,NotPesoNeto,NotVolumen,NotValorTotal,NotCostoTotal,EGR.FecEgr
,ISNULL(NM.Cargos,0) AS MucNovCargos,ISNULL(NM.Deducciones,0) AS MucNovDeducc,ISNULL(NR.Cargos,0) AS RemNovCargos,ISNULL(NR.Deducciones,0) AS RemNovDeducc
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia AND RM.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa=R.NumOrden AND RM.IdCiaRem=R.IdCia
 INNER JOIN Companias AS CN ON RM.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Trn_TraOrdenPago AS ODP ON M.TipOdp=ODP.TipDoc AND M.OrdPago=ODP.OrdPago AND M.IdCiaOdp=ODP.IdCia
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
--subconsulta de facturas 
LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad ELSE 0 END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN CantPago*VrCosto ELSE 0 END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN VrFaltante ELSE 0 END) AS FacFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Unidades ELSE 0 END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN PesoNeto ELSE 0 END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Volumen ELSE 0 END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cases ELSE 0 END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cajas ELSE 0 END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Palets ELSE 0 END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad ELSE 0 END) AS DfaCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfaValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN CantPago*VrCosto ELSE 0 END) AS DfaCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante ELSE 0 END) AS DfaFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades ELSE 0 END) AS DfaUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto ELSE 0 END) AS DfaPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen ELSE 0 END) AS DfaVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases ELSE 0 END) AS DfaCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas ELSE 0 END) AS DfaCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets ELSE 0 END) AS DfaPalets
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad WHEN 'NCR' THEN Cantidad*-1 ELSE 0 END) AS NotCantidad
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrUnitario WHEN 'NCR' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS NotValorTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrCosto WHEN 'NCR' THEN (Cantidad*VrCosto)*-1 ELSE 0 END) AS NotCostoTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN PesoNeto WHEN 'NCR' THEN PesoNeto*-1 ELSE 0 END) AS NotPesoNeto
,SUM(CASE TipDoc WHEN 'NDB' THEN Volumen WHEN 'NCR' THEN Volumen*-1 ELSE 0 END) AS NotVolumen
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Factura ELSE 0 END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFactura
,MAX(CASE TipDoc WHEN 'NDB' THEN Factura ELSE 0 END) AS NumNotaDb
,MAX(CASE TipDoc WHEN 'NCR' THEN Factura ELSE 0 END) AS NumNotaCr
 FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON RM.TipRem=RF.TipRem AND RM.Remesa=RF.Remesa AND RM.IdCiaRem=RF.IdCiaRem AND RM.ItemRem=RF.ItemRem
LEFT JOIN(select OE.TipOdp ,OE.OrdPago,OE.IdCiaOdp,MAX(E.Fecha)AS FecEgr from Trn_TraEgrOrden  OE inner join Trn_Comprobantes E
ON OE.TipCom =E.TipCom AND OE.NumEgreso =E.Comprobante  AND OE.IdCia=E.IdCia 
group by OE.TipOdp ,OE.OrdPago,OE.IdCiaOdp) AS EGR ON ODP.TipDoc =EGR.TipOdp AND ODP.OrdPago=EGR.OrdPago AND ODP.IdCia =EGR.IdCiaOdp 
--Novedades
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE WHEN (TipoConc='PAGO' OR TipoConc='GASTO') THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE 0 END) AS Cargos
,SUM(CASE TipoConc WHEN 'DEDUCCION' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE  0 END) AS Deducciones
FROM Trn_TraConceptos WHERE TipDoc='MUC' AND Est_Liquida>=ISNULL(@pmNovEstLiq,0) GROUP BY TipDoc,Documento,IdCia) AS NM ON M.TipDoc=NM.TipDoc AND M.Manifiesto=NM.Documento AND M.IdCia=NM.IdCia
LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE TipoConc WHEN 'CARGO' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE 0 END) AS Cargos
,SUM(CASE TipoConc WHEN 'DEDUCCION' THEN (CASE TipoTarif WHEN '%' THEN (VrBase*Tarifa)/100 ELSE Tarifa END) ELSE  0 END) AS Deducciones
FROM Trn_TraConceptos WHERE TipDoc='RMT' AND Est_Factura>=ISNULL(@pmNovEstFact,0) GROUP BY TipDoc,Documento,IdCia) AS NR ON RM.TipRem=NR.TipDoc AND RM.Remesa=NR.Documento AND RM.IdCiaRem=NR.IdCia
--main query condition
WHERE  RM.TipDoc='MUC' AND M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.Anulado=0 AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
AND  M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSoporteRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmnIdTercero VARCHAR(16)=Null
AS 
 SELECT D.TipDoc,TipoDoc,D.NumDoc,D.IdCia AS CdCia,CI.Compania,D.Fecha,D.IdTercero AS NitTercero,T.RazonSocial,D.IdAdquiriente,N.RazonSocial AS Adquiriente
 ,D.Modalidad,D.IdConcepto,Concepto,D.IdMedPago,MedioPago,D.MetPago,D.NumFactura,D.FechaFac,D.FechaVence,D.SubTotal,D.Descuento,D.Retencion,D.ReteIca,D.Bomberil,D.ImpAviTab,D.OtrosCargos,D.OtrosDctos,D.ValorTotal,D.VrOtrosImpu
 ,D.BaseRet,D.BaseBom,D.TarifaRet,D.TarifaIca,D.TarifaBom,D.TarifaAvta,D.CodTarRet,D.CodTarIca,D.CodTarifBom,D.CodTarifAvta,D.DescServicio
 ,D.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.TipDocRef,D.NumDocRef,D.IdCiaRef
,D.CodCueProv,D.NitCueProv,NP.RazonSocial AS NomProveedor,D.Anulado,D.TipDocDev,D.NumDev,D.CdCiaDev,D.FecDev,D.Observacion,D.IdEstado AS CdEstado,ED.Estado,D.TimeSys AS FechaCrea,D.OrigenAdd,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
FROM Trn_DocSoporte AS D INNER JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
LEFT JOIN TiposComRes AS R ON D.CodRes=R.IdRes LEFT JOIN Terceros AS N ON D.IdAdquiriente=N.IdTercero
LEFT JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON D.IdMedPago=MP.IdMedPago
LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN Terceros AS NP ON D.NitCueProv=NP.IdTercero
WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (D.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND D.IdTercero LIKE ISNULL(@pmnIdTercero,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSopDevFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
AS 
 SELECT DV.TipDev,TD.TipoDoc,DV.Devolucion,DV.IdCia,CI.Compania,DV.Fecha,DV.TipDoc,TS.TipoDoc AS DescTipDoc,DV.NumDoc,DV.IdCiaDoc,CD.Compania AS CiaDoc,DV.FecDoc,DV.IdTercero,T.RazonSocial,DV.SubTotal,DV.Descuento,DV.Retencion,DV.ReteIca,DV.Bomberil,DV.ImpAviTab
,DV.OtrosCargos,DV.OtrosDctos,DV.VrOtrosImpu,DV.ValorTotal,DV.BaseRet,DV.BaseBom,DS.TarifaRet,DS.TarifaIca,DS.TarifaBom,DS.TarifaAvta,DS.CodTarRet,DS.CodTarIca,DS.CodTarifBom,DS.CodTarifAvta
,DS.IdMedPago,MedioPago,DS.MetPago,DS.NumFactura,DS.FechaFac,DS.FechaVence,DS.IdAdquiriente,N.RazonSocial AS Adquiriente,DV.IdConcepto,Concepto,DV.DescServicio,DV.CdCCosto,CCosto,DV.CdSubCos,SubCosto
,DV.TipDocRef,DV.NumDocRef,DV.IdCiaRef,DV.NitCueProv,NP.RazonSocial AS NomProveedor,DV.CodCueProv,DV.TipCom,TipoCom,DV.Comprobante,DV.IdCiaCom,DV.Observacion,DV.OrigenAdd AS OrigenCrea,DV.TimeSys AS FechaCrea,DV.IdCiaCrea,DV.IdUsuario,Usuario
--detalles
,DT.Item,DT.IdProducto,P.DescripProd,DT.Descripcion,DT.Cantidad,DT.VrUnitario,DT.ValorOper,DT.VrDcto,DT.VrRetencion,DT.VrRetIca,DT.VrBomberil,DT.VrAviTab,DT.TarifaDct AS PorcDcto
,DT.TarifaRet AS PorcRet,DT.TarifaIca AS PorcICA,DT.TarifaBom AS PorcBom,DT.TarifaAvta AS PorcAvta,DT.CodTarDct AS DetCodDcto,DT.CodTarRet AS DetCodRet
,DT.CodTarIca AS DetCodIca,DT.CodTarifBom AS DetCodBom,DT.CodTarifAvta AS DetCodAvta,DT.ItemCom,DT.CdCuenta,PC.NomCuenta,DT.CdCueCre,DT.PlacaVeh,DT.Veh_Propio,DT.NitCliente,DT.CodAgencia
--Datos resolución
,DV.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,FC.EstadoFE,FC.Prefijo AS FEPrefijo,FC.NumFace,FC.CUFE,FC.CUFE_QR,FC.Resolucion AS FENumResol,FC.RangoNum AS FERangoNum,FC.FecVigencia AS FEVigencia,FC.FechaValidacion AS FEFecValida
FROM Trn_DocSopDev AS DV INNER JOIN Sys_TiposDoc AS TD ON DV.TipDev=TD.IdDoc INNER JOIN Companias AS CI ON DV.IdCia=CI.IdCia
INNER JOIN Sys_TiposDoc AS TS ON DV.TipDoc=TS.IdDoc INNER JOIN Companias AS CD ON DV.IdCiaDoc=CD.IdCia
INNER JOIN Terceros AS T ON DV.IdTercero=T.IdTercero INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario
INNER JOIN Trn_DocSoporte AS DS ON DV.TipDoc=DS.TipDoc AND DV.NumDoc=DS.NumDoc AND DV.IdCiaDoc=DS.IdCia
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
INNER JOIN Trn_DocSopDevDet AS DT ON DV.TipDev=DT.TipDev AND DV.Devolucion=DT.Devolucion AND DV.IdCia=DT.IdCia
LEFT JOIN ProdMcias AS P ON DT.IdProducto=P.IdProducto
LEFT JOIN Conceptos AS CN ON DV.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON DS.IdMedPago=MP.IdMedPago
LEFT JOIN TiposComRes AS R ON DV.CodRes=R.IdRes LEFT JOIN Terceros AS N ON DS.IdAdquiriente=N.IdTercero
LEFT JOIN TiposCom AS TC ON DV.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON DV.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON DV.CdSubCos=SC.IdSubCos LEFT JOIN Puc AS PC ON DT.CdCuenta=PC.IdCuenta
LEFT JOIN Terceros AS NP ON DV.NitCueProv=NP.IdTercero
LEFT JOIN Trn_Face AS FC ON DV.TipDev=FC.TipDoc AND DV.Devolucion=FC.Documento AND DV.IdCia=FC.IdCia
WHERE DV.TipDev=@pmTipDev AND DV.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND DV.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSopDevLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT DV.TipDev,DV.Devolucion,DV.IdCia,CI.Compania,DV.Fecha,DV.TipDoc,DV.NumDoc,DV.IdCiaDoc,DV.FecDoc,DV.IdTercero,T.RazonSocial,DV.SubTotal,DV.Descuento,DV.Retencion,DV.ReteIca,DV.Bomberil,DV.ImpAviTab
,DV.OtrosCargos,DV.OtrosDctos,DV.VrOtrosImpu,DV.ValorTotal,DV.BaseRet,DV.BaseBom,DS.NumFactura,DS.Modalidad,DV.IdConcepto,Concepto,DV.CodRes,DV.DescServicio,DV.CdCCosto,CCosto,DV.CdSubCos,SubCosto
,DV.TipDocRef,DV.NumDocRef,DV.IdCiaRef,DV.NitCueProv,NP.RazonSocial AS NomProveedor,DV.CodCueProv,DV.TipCom,DV.Comprobante,DV.IdCiaCom,DV.Observacion,DV.OrigenAdd AS OrigenCrea,DV.TimeSys AS FechaCrea,DV.IdCiaCrea,DV.IdUsuario,Usuario
 FROM Trn_DocSopDev AS DV INNER JOIN Companias AS CI ON DV.IdCia=CI.IdCia INNER JOIN Terceros AS T ON DV.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario
 INNER JOIN Trn_DocSoporte AS DS ON DV.TipDoc=DS.TipDoc AND DV.NumDoc=DS.NumDoc AND DV.IdCiaDoc=DS.IdCia
 LEFT JOIN CentroCosto AS CC ON DV.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DV.CdSubCos=SC.IdSubCos 
 LEFT JOIN Terceros AS NP ON DV.NitCueProv=NP.IdTercero LEFT JOIN Conceptos AS CN ON DV.IdConcepto=CN.IdConcepto
 WHERE DV.TipDev=@pmTipDev AND DV.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND (DV.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumDoc,IdCia,Fecha,IdTercero,IdAdquiriente,IdConcepto,IdMedPago,MetPago,Modalidad,NumFactura,FechaFac,FechaVence,SubTotal,Descuento,Retencion,ReteIca,Bomberil
,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,TarifaRet,TarifaIca,TarifaBom,TarifaAvta,CodTarRet,CodTarIca,CodTarifBom,CodTarifAvta,DescServicio,CodRes,CdCCosto,CdSubCos,TipCom,Comprobante,IdCiaCom
,TipDocRef,NumDocRef,IdCiaRef,Anulado,TipDocDev,NumDev,CdCiaDev,FecDev,Observacion,IdEstado,NitCueProv,CodCueProv,VrOtrosImpu,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DocSoporte
 WHERE TipDoc=@pmTipDoc AND NumDoc=@pmNumDoc AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmIdAdquiriente VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmIdMedPago VARCHAR(4),@pmMetPago INT,@pmModalidad VARCHAR(20),@pmNumFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaBom DECIMAL(14,4),@pmTarifaAvta DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarifBom VARCHAR(4),@pmCodTarifAvta VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCodRes VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmAnulado BIT,@pmTipDocDev VARCHAR(3),@pmNumDev INT,@pmCdCiaDev CHAR(2),@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(500),@pmIdEstado VARCHAR(4),@pmCodCueProv VARCHAR(16),@pmNitCueProv VARCHAR(16),@pmVrOtrosImpu DECIMAL(16,6),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DocSoporte (TipDoc,NumDoc,IdCia,Fecha,IdTercero,IdAdquiriente,IdConcepto,IdMedPago,MetPago,Modalidad,NumFactura,FechaFac,FechaVence,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,TarifaRet,TarifaIca,TarifaBom,TarifaAvta,CodTarRet,CodTarIca,CodTarifBom,CodTarifAvta,DescServicio,CodRes,CdCCosto,CdSubCos,TipCom,Comprobante,IdCiaCom,TipDocRef,NumDocRef,IdCiaRef,Anulado,TipDocDev,NumDev,CdCiaDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,CodCueProv,NitCueProv,VrOtrosImpu)
 VALUES (@pmTipDoc,@pmNumDoc,@pmIdCia,@pmFecha,@pmIdTercero,@pmIdAdquiriente,@pmIdConcepto,@pmIdMedPago,@pmMetPago,@pmModalidad,@pmNumFactura,@pmFechaFac,@pmFechaVence,@pmSubTotal,@pmDescuento,@pmRetencion,@pmReteIca,@pmBomberil,@pmImpAviTab,@pmOtrosCargos,@pmOtrosDctos,@pmValorTotal,@pmBaseRet,@pmBaseBom,@pmTarifaRet,@pmTarifaIca,@pmTarifaBom,@pmTarifaAvta,@pmCodTarRet,@pmCodTarIca,@pmCodTarifBom,@pmCodTarifAvta,@pmDescServicio,@pmCodRes,@pmCdCCosto,@pmCdSubCos,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmAnulado,@pmTipDocDev,@pmNumDev,@pmCdCiaDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCodCueProv,@pmNitCueProv,@pmVrOtrosImpu)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmIdAdquiriente VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmIdMedPago VARCHAR(4),@pmMetPago INT,@pmModalidad VARCHAR(20),@pmNumFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6),@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaBom DECIMAL(14,4),@pmTarifaAvta DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarifBom VARCHAR(4),@pmCodTarifAvta VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCodRes VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmAnulado BIT,@pmTipDocDev VARCHAR(3),@pmNumDev INT,@pmCdCiaDev CHAR(2),@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(500),@pmIdEstado VARCHAR(4),@pmCodCueProv VARCHAR(16),@pmNitCueProv VARCHAR(16),@pmVrOtrosImpu DECIMAL(16,6),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DocSoporte SET Fecha=@pmFecha,IdTercero=@pmIdTercero,IdAdquiriente=@pmIdAdquiriente,IdConcepto=@pmIdConcepto,IdMedPago=@pmIdMedPago,MetPago=@pmMetPago,Modalidad=@pmModalidad,NumFactura=@pmNumFactura,FechaFac=@pmFechaFac,FechaVence=@pmFechaVence,SubTotal=@pmSubTotal,Descuento=@pmDescuento,Retencion=@pmRetencion,ReteIca=@pmReteIca,Bomberil=@pmBomberil,ImpAviTab=@pmImpAviTab,OtrosCargos=@pmOtrosCargos,OtrosDctos=@pmOtrosDctos,ValorTotal=@pmValorTotal,BaseRet=@pmBaseRet,BaseBom=@pmBaseBom,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaBom=@pmTarifaBom,TarifaAvta=@pmTarifaAvta,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarifBom=@pmCodTarifBom,CodTarifAvta=@pmCodTarifAvta,DescServicio=@pmDescServicio,CodRes=@pmCodRes,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,Anulado=@pmAnulado,TipDocDev=@pmTipDocDev,NumDev=@pmNumDev,CdCiaDev=@pmCdCiaDev
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CodCueProv=@pmCodCueProv,NitCueProv=@pmNitCueProv,VrOtrosImpu=@pmVrOtrosImpu,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumDoc=@pmNumDoc AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSoporteFmt] @pmTipDoc VARCHAR(3),@pmNumDocIni INT,@pmNumDocFin INT,@pmIdCia CHAR(2)
AS 
 SELECT D.TipDoc,TipoDoc,D.NumDoc,D.IdCia AS CdCia,CI.Compania,D.Fecha,D.IdTercero AS NitTercero,T.RazonSocial,D.IdAdquiriente,N.RazonSocial AS Adquiriente
 ,D.Modalidad,D.IdConcepto,Concepto,D.IdMedPago,MedioPago,D.MetPago,D.NumFactura,D.FechaFac,D.FechaVence,D.SubTotal,D.Descuento,D.Retencion,D.ReteIca,D.Bomberil,D.ImpAviTab,D.OtrosCargos,D.OtrosDctos,D.VrOtrosImpu,D.ValorTotal
 ,D.BaseRet,D.BaseBom,D.TarifaRet,D.TarifaIca,D.TarifaBom,D.TarifaAvta,D.CodTarRet,D.CodTarIca,D.CodTarifBom,D.CodTarifAvta,D.DescServicio
,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.TipDocRef,D.NumDocRef,D.IdCiaRef,D.CodCueProv,D.NitCueProv,NP.RazonSocial AS NomProveedor,D.Anulado,D.TipDocDev,D.NumDev,D.CdCiaDev,D.FecDev
,D.Observacion,D.IdEstado AS CdEstado,ED.Estado,D.TimeSys AS FechaCrea,D.OrigenAdd,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
--detalles
,DT.Item,DT.IdProducto,P.DescripProd,DT.Descripcion,DT.Cantidad,DT.VrUnitario,DT.ValorOper,DT.VrDcto,DT.VrRetencion,DT.VrRetIca,DT.VrBomberil,DT.VrAviTab,DT.TarifaDct AS PorcDcto
,DT.TarifaRet AS PorcRet,DT.TarifaIca AS PorcICA,DT.TarifaBom AS PorcBom,DT.TarifaAvta AS PorcAvta,DT.CodTarDct AS DetCodDcto,DT.CodTarRet AS DetCodRet
,DT.CodTarIca AS DetCodIca,DT.CodTarifBom AS DetCodBom,DT.CodTarifAvta AS DetCodAvta,DT.ItemCom,DT.CdCuenta,PC.NomCuenta,DT.CdCueCre,DT.PlacaVeh,DT.Veh_Propio,DT.NitCliente,DT.CodAgencia
--Datos resolución
,D.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,FC.EstadoFE,FC.Prefijo AS FEPrefijo,FC.NumFace,FC.CUFE,FC.CUFE_QR,FC.Resolucion AS FENumResol,FC.RangoNum AS FERangoNum,FC.FecVigencia AS FEVigencia,FC.FechaValidacion AS FEFecValida
,FD.CUFE AS CUDSDEV
FROM Trn_DocSoporte AS D INNER JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
INNER JOIN Trn_DocSoporteDet AS DT ON D.TipDoc=DT.TipDoc AND D.NumDoc=DT.NumDoc AND D.IdCia=DT.IdCia
LEFT JOIN ProdMcias AS P ON DT.IdProducto=P.IdProducto
LEFT JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON D.IdMedPago=MP.IdMedPago
LEFT JOIN TiposComRes AS R ON D.CodRes=R.IdRes LEFT JOIN Terceros AS N ON D.IdAdquiriente=N.IdTercero
LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN Puc AS PC ON DT.CdCuenta=PC.IdCuenta
LEFT JOIN Terceros AS NP ON D.NitCueProv=NP.IdTercero
LEFT JOIN Trn_Face AS FC ON D.TipDoc=FC.TipDoc AND D.NumDoc=FC.Documento AND D.IdCia=FC.IdCia
LEFT JOIN Trn_Face AS FD ON D.TipDocDev=FD.TipDoc AND D.NumDev=FD.Documento AND D.CdCiaDev=FD.IdCia
WHERE D.TipDoc=@pmTipDoc AND D.NumDoc BETWEEN @pmNumDocIni AND @pmNumDocFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServNov] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
 AS 
 SELECT O.TipDoc AS IdTipDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,A.Agencia
,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,O.EstFactura,O.TipFact AS OrdTipFac,O.NumFactura AS OrdFactura,O.CdCiaFact
,kmtInicial,kmtFinal,Cantkms,EstOrden,O.TipLiq AS OrdTipLiq,O.NumLiquida AS OrdNumLiquida,O.CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea
--novedades
,C.Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta
,NitTercero,TCN.RazonSocial AS NomTercero,C.FechaNov,C.VrBase,C.TipoTarif,C.RefConc,C.Referencia2,C.Referencia3,C.Fijos,C.IncBaseRet,C.Est_Factura,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.Est_Liquida,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
--Cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,T.Telefono AS TercTelefono,T.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraConceptos AS C ON O.TipDoc=C.TipDoc AND O.NumOrden=C.Documento AND O.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto LEFT JOIN Terceros AS TCN ON C.NitTercero=TCN.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehRemolqLta] @pmIdMarca VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
 AS 
 SELECT IdRemque,R.IdMarca AS CdMarca,Marca,R.IdColor AS CodColor,NomColor,R.IdCrceria AS CodCarr,TipoCar,Modelo,ConfEjes,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen
,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NP.RazonSocial AS Propietario,NitProv,NPV.RazonSocial AS Proveedor,FecCompra,VidaUtil
,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,kmRuta,kmOtro,EquipoAdic,Descripcion,CdCenSer,CentroServ,CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,Ubicacion,R.Observacion AS Observ,R.ArcFotoRem,R.IdEstado AS CodEstado,Estado,R.Inactivo AS Inactiv,R.IdUsuario AS IdUsuari,Usuario
,FecAdd,FecUpdate,EV.NColor AS NumColor,OutDemand,TarjetaProp,FecTarjProp,VigTarjProp,CdLocTarj,Hidrostatica,FecPruebaHid,VigPruebaHid,Kingpin,FecKingpin,VigKingpin,FecVigExtintor
,Operador_GPS,Usuario_GPS,Clave_GPS,IdOper_GPS
 FROM VehRemolq AS R INNER JOIN Marcas AS M ON R.IdMarca=M.IdMarca  INNER JOIN TiposCol AS CL ON R.IdColor=CL.IdColor
 INNER JOIN TiposCar AS TC ON R.IdCrceria=TC.IdCrceria  INNER JOIN Terceros AS NP ON  R.IdPropietario=NP.IdTercero
 INNER JOIN EstadoVeh AS EV ON R.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS NPV ON R.NitProv=NPV.IdTercero
 LEFT JOIN CentrosServ AS CS ON R.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON R.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 WHERE R.IdMarca LIKE ISNULL(@pmIdMarca,'%')  AND R.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')
 AND Modelo LIKE ISNULL(@pmModelo,'%')  AND IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (R.Inactivo=ISNULL(@pmInactivo,0) or R.Inactivo=ISNULL(@pmInactivo,1))
 AND (FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoNov]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
, @pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal,BaseRet,TarifaRet,TarifaIca
,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,Anulado,FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado
,NomRemite,NomDestino,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
--Datos de novedades
,C.Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta
,NitTercero,TC.RazonSocial AS NomTercero,CdTipoEsc,TipoEscolta,C.FechaNov,C.VrBase,C.TipoTarif,C.RefConc,C.Referencia2,C.Referencia3,C.Est_Factura,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.Est_Liquida,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
--datos del propietario
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,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,Modelo,Config,V.Descripcion AS VehDescripcion
 FROM Trn_TraManifiesto AS M INNER JOIN  Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON M.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  Trn_TraConceptos AS C ON M.TipDoc=C.TipDoc AND M.Manifiesto=C.Documento AND M.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS TC ON C.NitTercero=TC.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 WHERE M.TipDoc='MUC'  AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaNov] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT R.TipDoc AS TipRem,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS NombreClie
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items
,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,CR.Concepto AS DesConcRem,CdRutaTarif,TipCom,Comprobante,IdCiaCom,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,ManifMintrans,RemMintrans,TipoRuta,TipoMintrans
--datos de conceptos
,C.Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CodConcepto,CDV.Concepto AS DescConcepto,CdCuenta,NomCuenta
,NitTercero,TC.RazonSocial AS NomTercero,CdTipoEsc,TipoEscolta,C.FechaNov,C.VrBase,C.TipoTarif,C.RefConc,C.Referencia2,C.Referencia3,C.Est_Factura,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.Est_Liquida,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
--Datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,Modelo,Config,V.Descripcion AS VehDescripcion,V.IdPoseedor AS NitPoseedor
 FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN Trn_TraConceptos AS C ON R.TipDoc=C.TipDoc AND R.NumOrden=C.Documento AND R.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS TC ON C.NitTercero=TC.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  
 LEFT JOIN Conceptos AS CR ON R.CdConcepto=CR.IdConcepto 
 WHERE R.TipDoc='RMT'  AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Modalidad LIKE ISNULL(@pmModalidad,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmCodRes VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmNitCueProv VARCHAR(16),@pmCodCueProv VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500),@pmVrOtrosImpu DECIMAL(16,6)
 AS 
 UPDATE Trn_DocSopDev SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,NumDoc=@pmNumDoc,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdTercero=@pmIdTercero,SubTotal=@pmSubTotal,Descuento=@pmDescuento,Retencion=@pmRetencion,ReteIca=@pmReteIca,Bomberil=@pmBomberil,ImpAviTab=@pmImpAviTab,OtrosCargos=@pmOtrosCargos,OtrosDctos=@pmOtrosDctos,ValorTotal=@pmValorTotal,BaseRet=@pmBaseRet,BaseBom=@pmBaseBom,CodRes=@pmCodRes,DescServicio=@pmDescServicio,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos
,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,NitCueProv=@pmNitCueProv,CodCueProv=@pmCodCueProv,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion,VrOtrosImpu=@pmVrOtrosImpu
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumDoc,IdCiaDoc,FecDoc,IdTercero,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,CodRes,DescServicio,CdCCosto,CdSubCos,TipDocRef,NumDocRef,IdCiaRef,NitCueProv,CodCueProv,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,VrOtrosImpu,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_DocSopDev
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocSopDev] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmCodRes VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmNitCueProv VARCHAR(16),@pmCodCueProv VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500),@pmVrOtrosImpu DECIMAL(16,6),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DocSopDev (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumDoc,IdCiaDoc,FecDoc,IdTercero,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,CodRes,DescServicio,CdCCosto,CdSubCos,TipDocRef,NumDocRef,IdCiaRef,NitCueProv,CodCueProv,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,VrOtrosImpu)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmNumDoc,@pmIdCiaDoc,@pmFecDoc,@pmIdTercero,@pmSubTotal,@pmDescuento,@pmRetencion,@pmReteIca,@pmBomberil,@pmImpAviTab,@pmOtrosCargos,@pmOtrosDctos,@pmValorTotal,@pmBaseRet,@pmBaseBom,@pmCodRes,@pmDescServicio,@pmCdCCosto,@pmCdSubCos,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmNitCueProv,@pmCodCueProv,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrOtrosImpu)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraConceptosLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.TipDoc,C.Documento,C.IdCia,Compania,C.Item,C.FechaNov,C.IdConcepto AS CdConcepto,Concepto,C.Descripcion,C.VrBase,C.TipoTarif,C.Tarifa,C.TipoConc,C.RubroConcep,C.Cantidad,C.VrUnitario,C.TarifIva
,C.CdCuenta,P.NomCuenta,C.NitTercero,T.RazonSocial,C.CdTipoEsc,TipoEscolta,C.RefConc AS Referencia1,C.Referencia2,C.Referencia3,C.Est_Factura,C.Est_Liquida,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
,O.Fecha AS FecOrden,O.FecDespacho,O.IdVehiculo,O.IdConductor,NC.RazonSocial AS Conductor,O.TipoAfiVehic,O.IdPoseedor,NP.RazonSocial AS Poseedor,O.IdPropietario AS IdCliente,CL.RazonSocial AS NomCliente
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,O.Cumplido,O.IdCiaCump,O.EstCumplido,O.EstOrden,O.TipOdp,O.OrdPago,O.IdCiaOdp,O.EstFactura,O.TipFact AS OrdTipFact,O.NumFactura AS OrdNumFactura,O.CdCiaFact,O.Anulado,O.Observacion
 FROM Trn_TraConceptos AS C INNER JOIN ConcDiversos AS CD ON C.IdConcepto=CD.IdConcepto  INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia
 INNER JOIN (SELECT TipDoc,Manifiesto,IdCia,Fecha,FecDespacho,IdVehiculo,IdConductor,TipoAfiVehic,IdPoseedor,IdPropietario,IdOrigen,IdDestino,Cumplido,IdCiaCump,EstCumplido
 ,EstOrden,TipOdp,OrdPago,IdCiaOdp,Remesa AS EstFactura,'0' AS TipFact,Remesa AS NumFactura,IdCiaRem AS CdCiaFact,Anulado,Observacion FROM Trn_TraManifiesto
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)
 UNION ALL
 SELECT R.TipDoc,R.NumOrden,R.IdCia,R.Fecha,R.FecDespacho,R.IdVehiculo,R.IdConductor,R.TipoAfiVehic,V.IdPoseedor,R.IdCliente,R.IdLocOrigen,R.IdLocDestino,0,'',R.EstCumplido
 ,R.NumManif,'',R.NumManif,R.IdCiaManif,R.EstFactura,R.TipDcm,R.NumDocmto,R.IdCiaDcm,R.Anulado,R.Observacion FROM Trn_TraRemesa AS R LEFT JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo
 WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (R.IdCia=@pmIdCia OR @pmIdCia IS NULL)
 UNION ALL
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdVehiculo,IdConductor,TipoAfiVehic,IdPoseedor,IdCliente,IdOrigen,IdDestino,NumCump,CdCiaCum,EstCumplido
 ,EstOrden,TipLiq,NumLiquida,CdCiaLiq,EstFactura,TipFact,NumFactura,CdCiaFact,Anulado,Observacion FROM Trn_TraOrdenServ 
 WHERE TipDoc='OSA' AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (IdCia=@pmIdCia OR @pmIdCia IS NULL)) AS O ON C.TipDoc=O.TipDoc AND C.Documento=O.Manifiesto AND C.IdCia=O.IdCia
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Terceros AS NC ON O.IdConductor=NC.IdTercero LEFT JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS CL ON O.IdPropietario=CL.IdTercero
 WHERE (C.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiqDetalleFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
AS 
 SELECT TipDoc,Liquidacion,D.IdCia,Compania,D.Item,D.IdConcepto AS CdConcepto,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito
,D.IdTercero AS NitTercero,RazonSocial,D.CodAgncia,CdCCosto,CCosto,CdSubCos,SubCosto,pVehiculo
,D.RubroConc,D.VrBase,D.TarifBase,D.TipFac,D.Factura,D.IdCiaFac,ItemFac,D.FecVence,RefConcep,Concepto,D.TipSop,D.NumSoporte,D.IdCiaSop
,D.DocSop,D.TipoImp,D.CodTarImp,D.ItemBase,D.TipoNov,D.TipDocNov,D.NumDocNov,D.CiaDocNov,D.ItemNov
 FROM Trn_TraLiqDetalle AS D INNER JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto 
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia
 INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta LEFT JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE D.TipDoc=@pmTipDoc AND D.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraConceptos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmDescripcion VARCHAR(250),@pmTarifa DECIMAL(14,4),@pmTipoConc VARCHAR(10),@pmRubroConcep VARCHAR(20)
,@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmTarifIva DECIMAL(14,4),@pmIdConcepto VARCHAR(4),@pmCdCuenta VARCHAR(16),@pmNitTercero VARCHAR(16),@pmCdTipoEsc VARCHAR(4),@pmFechaNov SMALLDATETIME
,@pmVrBase MONEY,@pmTipoTarif CHAR(1),@pmRefConc VARCHAR(50),@pmFijos BIT,@pmIncBaseRet INT,@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmEst_Factura INT,@pmEst_Liquida INT
,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCiaFact CHAR(2),@pmVrTotalFact MONEY,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCiaLiq CHAR(2),@pmVrTotalLiq MONEY
 AS 
 INSERT INTO Trn_TraConceptos (TipDoc,Documento,IdCia,Item,Descripcion,Tarifa,TipoConc,RubroConcep,Cantidad,VrUnitario,TarifIva,IdConcepto,CdCuenta,NitTercero,CdTipoEsc,FechaNov,VrBase,TipoTarif,RefConc,Fijos,IncBaseRet
,Referencia2,Referencia3,Est_Factura,Est_Liquida,TipFact,NumFactura,CiaFact,VrTotalFact,TipLiq,NumLiquida,CiaLiq,VrTotalLiq)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmDescripcion,@pmTarifa,@pmTipoConc,@pmRubroConcep,@pmCantidad,@pmVrUnitario,@pmTarifIva,@pmIdConcepto,@pmCdCuenta,@pmNitTercero
,@pmCdTipoEsc,@pmFechaNov,@pmVrBase,@pmTipoTarif,@pmRefConc,@pmFijos,@pmIncBaseRet,@pmReferencia2,@pmReferencia3,@pmEst_Factura,@pmEst_Liquida,@pmTipFact,@pmNumFactura,@pmCiaFact,@pmVrTotalFact,@pmTipLiq,@pmNumLiquida,@pmCiaLiq,@pmVrTotalLiq)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraConceptos] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Descripcion,Tarifa,TipoConc,RubroConcep,Cantidad,VrUnitario,TarifIva
,IdConcepto,CdCuenta,NitTercero,CdTipoEsc,FechaNov,VrBase,TipoTarif,RefConc,Fijos,IncBaseRet,Referencia2,Referencia3
,Est_Factura,Est_Liquida,TipFact,NumFactura,CiaFact,VrTotalFact,TipLiq,NumLiquida,CiaLiq,VrTotalLiq
 FROM Trn_TraConceptos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraConceptosItem] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmItem INT
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Descripcion,Tarifa,TipoConc,RubroConcep,Cantidad,VrUnitario,TarifIva,IdConcepto
,CdCuenta,NitTercero,CdTipoEsc,FechaNov,VrBase,TipoTarif,RefConc,Fijos,IncBaseRet,Referencia2,Referencia3
,Est_Factura,Est_Liquida,TipFact,NumFactura,CiaFact,VrTotalFact,TipLiq,NumLiquida,CiaLiq,VrTotalLiq
 FROM Trn_TraConceptos
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia   AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraConceptosFmt] @pmTipDoc VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Documento,IdCia,Item,Descripcion,Tarifa,TipoConc,RubroConcep,Cantidad,VrUnitario,TarifIva
,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta,NitTercero,RazonSocial,CdTipoEsc,TipoEscolta
,FechaNov,VrBase,TipoTarif,RefConc,Referencia2,Referencia3
,Est_Factura,Est_Liquida,TipFact,NumFactura,CiaFact,VrTotalFact,TipLiq,NumLiquida,CiaLiq,VrTotalLiq
 FROM Trn_TraConceptos AS C INNER JOIN ConcDiversos AS CD ON C.IdConcepto=CD.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 WHERE TipDoc=@pmTipDoc AND C.Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin AND C.IdCia=@pmIdCia 
 ORDER BY C.Documento
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraConc_Sel] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraConc (tmNumero,tmItem,tmDescripcion,tmTarifa,tmTipoConc,tmRubro,tmCantidad,tmVrUnitario,tmTarifIva,tmIdConcepto,tmCdCuenta,tmNitTercero,tmVrBase,tmTipoTarif,tmRefConc,tmFijos,tmBaseRet
,tmTipDocRef,tmNumOrden,tmIdCiaOrd,tmItemOrd,tmTipoNov)
 SELECT @pmtmNumero,Item,Descripcion,Tarifa,TipoConc,RubroConcep, Cantidad, VrUnitario, TarifIva, IdConcepto, CdCuenta, NitTercero,VrBase,TipoTarif,RefConc,Fijos,IncBaseRet
 ,TipLiq,NumLiquida,CiaLiq,Est_Liquida,CASE WHEN (TipLiq='MUC' OR TipLiq='OSA') THEN 1 ELSE 0 END
 FROM Trn_TraConceptos
 WHERE TipDoc=@pmTipDoc  AND Documento=@pmDocumento AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraConceptosFac] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT RF.Factura,RF.IdCia AS CdCiaFac,C.TipDoc AS TipRem,C.Documento AS NumRemesa,C.IdCia AS CdCia,C.Item,C.Descripcion,Tarifa,TipoConc
,RubroConcep,C.Cantidad,C.VrUnitario,C.TarifIva,C.IdConcepto AS CdConcepto,Concepto,C.CdCuenta,NomCuenta
,C.NitTercero,RazonSocial,CdTipoEsc,TipoEscolta,FechaNov,VrBase,TipoTarif,RefConc,C.Referencia2,C.Referencia3
,C.Est_Factura,C.Est_Liquida,C.TipFact,C.NumFactura,C.CiaFact,C.VrTotalFact,C.TipLiq,C.NumLiquida,C.CiaLiq,C.VrTotalLiq
 FROM Trn_TraFacRemesas AS RF INNER JOIN Trn_TraConceptos AS C ON RF.TipRem=C.TipDoc AND RF.Remesa=C.Documento AND RF.IdCiaRem=C.IdCia
 INNER JOIN ConcDiversos AS CD ON C.IdConcepto=CD.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 WHERE RF.TipDoc=@pmTipDoc AND RF.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND RF.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpTraConceptos]  @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmDescripcion VARCHAR(250),@pmTarifa DECIMAL(14,4),@pmTipoConc VARCHAR(10),@pmRubroConcep VARCHAR(20),@pmCantidad DECIMAL(14,4)
,@pmVrUnitario MONEY,@pmTarifIva DECIMAL(14,4),@pmIdConcepto VARCHAR(4),@pmCdCuenta VARCHAR(16),@pmNitTercero VARCHAR(16),@pmCdTipoEsc VARCHAR(4),@pmFechaNov SMALLDATETIME,@pmVrBase MONEY,@pmTipoTarif CHAR(1),@pmRefConc VARCHAR(50)
,@pmFijos BIT,@pmIncBaseRet INT,@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmEst_Factura INT,@pmEst_Liquida INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCiaFact CHAR(2),@pmVrTotalFact MONEY,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCiaLiq CHAR(2),@pmVrTotalLiq MONEY
 AS 
 UPDATE Trn_TraConceptos SET Descripcion=@pmDescripcion,Tarifa=@pmTarifa,TipoConc=@pmTipoConc,RubroConcep=@pmRubroConcep,Cantidad=@pmCantidad,VrUnitario=@pmVrUnitario,TarifIva=@pmTarifIva,IdConcepto=@pmIdConcepto,CdCuenta=@pmCdCuenta
,NitTercero=@pmNitTercero,CdTipoEsc=@pmCdTipoEsc,FechaNov=@pmFechaNov,VrBase=@pmVrBase,TipoTarif=@pmTipoTarif,RefConc=@pmRefConc,Fijos=@pmFijos,IncBaseRet=@pmIncBaseRet,Referencia2=@pmReferencia2,Referencia3=@pmReferencia3
,Est_Factura=@pmEst_Factura,Est_Liquida=@pmEst_Liquida,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CiaFact=@pmCiaFact,VrTotalFact=@pmVrTotalFact,TipLiq=@pmTipLiq,NumLiquida=@pmNumLiquida,CiaLiq=@pmCiaLiq,VrTotalLiq=@pmVrTotalLiq
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetConc] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos
,tmpVehiculo,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb
,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef
 FROM tm_TraDetConc
 WHERE tmNumero=@pmtmNumero AND tmItem=@pmtmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetConcLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,NomCuenta,tmNitTercero,RazonSocial
,tmCdCCosto,CCosto,tmCdSubCos,SubCosto,tmpVehiculo,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia
,tmTipDocRef,tmDocRef,tmCiaDocRef,tmItemDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmTipoNov,tmCodAgncia,tmCantComb
 FROM tm_TraDetConc AS T LEFT JOIN CentroCosto AS CC ON T.tmCdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON T.tmCdSubCos=SC.IdSubCos
 LEFT JOIN Terceros  AS N ON T.tmNitTercero=N.IdTercero LEFT JOIN Puc AS P ON T.tmCdCuenta=P.IdCuenta
 WHERE tmNumero=@pmtmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Leg]  @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,DescConcepto,RubroConc,VrDebito,VrCredito,IdCuenta,IdTercero,CdCCosto,CdSubCos,pVehiculo
,'TERCEROS',VrBase,TarifBase,RefConcep,TipSop,NumSoporte,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodAgncia,CantComb,DocSop,TipoImp,CodTarImp,ItemBase,TipSop,NumSoporte,IdCiaSop,TipoNov,CiaDocNov,ItemNov
 FROM Trn_TraLiqDetalle
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Sel]  @pmTipDoc VARCHAR(3),@pmCausacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,Descripcion,TipoConc,VrDebito,VrCredito,CdCuenta,NitTercero,CdCCosto,CdSubCos,pVehiculo
,TipoAfiVehic,VrBase,TarifBase,Referencia,TipDocRef,DocRef,TipFac,Factura,IdCiaFac,ItemFac,FecVence,'0',0,0,'','',0,'0',0,'00',0,'',0
 FROM Trn_TraCauDetalle
 WHERE TipDoc=@pmTipDoc AND Causacion=@pmCausacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Dlg]  @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,DescConcepto,RubroConc,VrCredito,VrDebito,IdCuenta,IdTercero,CdCCosto,CdSubCos,pVehiculo
,'TERCEROS',VrBase,TarifBase,RefConcep,TipDoc,Liquidacion,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodAgncia,CantComb,DocSop,TipoImp,CodTarImp,ItemBase,TipSop,NumSoporte,IdCiaSop,TipoNov,CiaDocNov,ItemNov
 FROM Trn_TraLiqDetalle
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmDescripcion VARCHAR(250),@pmtmTipoConc VARCHAR(10),@pmtmVrDebito MONEY,@pmtmVrCredito MONEY,@pmtmCdCuenta VARCHAR(16)
,@pmtmNitTercero VARCHAR(16),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmpVehiculo VARCHAR(10),@pmtmTipoAfiVehic VARCHAR(10),@pmtmVrBase MONEY,@pmtmTarifBase DECIMAL(14,4),@pmtmReferencia VARCHAR(50)
,@pmtmTipDocRef VARCHAR(3),@pmtmDocRef INT,@pmtmTipFac VARCHAR(3),@pmtmFactura VARCHAR(15),@pmtmIdCiaFac CHAR(2),@pmtmItemFac INT,@pmtmFecVence SMALLDATETIME,@pmtmCodAgncia VARCHAR(16),@pmtmCantComb DECIMAL(14,4)
,@pmtmDocSop BIT,@pmtmTipoImp VARCHAR(4),@pmtmCodTarImp VARCHAR(4),@pmtmItemBase INT,@pmtmTipoDs VARCHAR(3),@pmtmNumDs INT,@pmtmIdCiaDs CHAR(2),@pmtmTipoNov INT,@pmtmCiaDocRef CHAR(2),@pmtmItemDocRef INT
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef
,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdConcepto,@pmtmDescripcion,@pmtmTipoConc,@pmtmVrDebito,@pmtmVrCredito,@pmtmCdCuenta,@pmtmNitTercero,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmpVehiculo,@pmtmTipoAfiVehic
,@pmtmVrBase,@pmtmTarifBase,@pmtmReferencia,@pmtmTipDocRef,@pmtmDocRef,@pmtmTipFac,@pmtmFactura,@pmtmIdCiaFac,@pmtmItemFac,@pmtmFecVence,@pmtmCodAgncia,@pmtmCantComb,@pmtmDocSop,@pmtmTipoImp,@pmtmCodTarImp,@pmtmItemBase,@pmtmTipoDs,@pmtmNumDs,@pmtmIdCiaDs,@pmtmTipoNov,@pmtmCiaDocRef,@pmtmItemDocRef)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Dev]  @pmTipDoc VARCHAR(3),@pmCausacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,Descripcion,TipoConc,VrCredito,VrDebito,CdCuenta,NitTercero,CdCCosto,CdSubCos,pVehiculo
,TipoAfiVehic,VrBase,TarifBase,Referencia,TipDocRef,DocRef,TipFac,Factura,IdCiaFac,ItemFac,FecVence,'0',0,0,'','',0,'0',0,'00',0,IdCiaFac,ItemFac
 FROM Trn_TraCauDetalle
 WHERE TipDoc=@pmTipDoc AND Causacion=@pmCausacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFacRemesas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipoReg INT,@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmItemRem INT,@pmFecRemesa SMALLDATETIME,@pmDescripcion VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrCosto MONEY,@pmUndTarifa VARCHAR(10),@pmUndCosto VARCHAR(10),@pmUnidades DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10)
,@pmCases INT,@pmCajas INT,@pmPalets INT,@pmTarifaIva DECIMAL(14,4),@pmVrImpuesto MONEY,@pmTarifaDct DECIMAL(14,4),@pmVrDescuento MONEY,@pmTarifaRet DECIMAL(14,4),@pmVrRetencion MONEY,@pmTarifaIca DECIMAL(14,4),@pmVrReteIca MONEY,@pmVrFaltante MONEY,@pmRemision DECIMAL(18,2),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmCdMercancia VARCHAR(16),@pmCdConcepto VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16)
,@pmNitTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmAnulado INT,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME,@pmCantidadFalt DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarDct VARCHAR(4),@pmNumPedRem INT,@pmCiaPedRem CHAR(2),@pmVrDeclMcia MONEY,@pmTarifaSeg DECIMAL(14,4),@pmVrSeguroRem MONEY
,@pmNitAsegurad VARCHAR(16),@pmCantPago DECIMAL(14,4),@pmVrAutRetIca MONEY,@pmRemTransb INT,@pmCostoAdicTbdo MONEY,@pmNitMucTbdo VARCHAR(16),@pmTipoNovFact INT,@pmItemDocRef INT
 AS 
 INSERT INTO Trn_TraFacRemesas (TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino
,Anulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,CantPago,VrAutRetIca,RemTransb,CostoAdicTbdo,NitMucTbdo,TipoNovFact,ItemDocRef)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmTipoReg,@pmFechaFact,@pmTipRem,@pmRemesa,@pmIdCiaRem,@pmItemRem,@pmFecRemesa,@pmDescripcion,@pmCantidad,@pmVrUnitario,@pmVrCosto,@pmUndTarifa,@pmUndCosto,@pmUnidades,@pmPesoNeto,@pmUndMed,@pmVolumen,@pmUndVol,@pmCases,@pmCajas,@pmPalets,@pmTarifaIva,@pmVrImpuesto,@pmTarifaDct,@pmVrDescuento,@pmTarifaRet,@pmVrRetencion,@pmTarifaIca,@pmVrReteIca,@pmVrFaltante,@pmRemision,@pmDocCliente,@pmReferencia1,@pmReferencia2,@pmReferencia3
,@pmCdMercancia,@pmCdConcepto,@pmCdCCosto,@pmCdSubCos,@pmNitTercero,@pmCdAgencia,@pmpVehiculo,@pmTipoAfiVehic,@pmIdOrigen,@pmIdDestino,@pmAnulado,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmFecDocRef,@pmCantidadFalt,@pmUnidadFalt,@pmCodTarRet,@pmCodTarIca,@pmCodTarDct,@pmNumPedRem,@pmCiaPedRem,@pmVrDeclMcia,@pmTarifaSeg,@pmVrSeguroRem,@pmNitAsegurad,@pmCantPago,@pmVrAutRetIca,@pmRemTransb,@pmCostoAdicTbdo,@pmNitMucTbdo,@pmTipoNovFact,@pmItemDocRef)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact_Sel] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets
,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido
,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago,tmVrAutoIca,tmRemTransb,tmCostoAdic,tmNitMucTbdo,tmTipoNov,tmItemOrd)
 SELECT @pmtmNumero,Item,TipoReg,TipRem,Remesa,IdCiaRem,ItemRem,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia
,pVehiculo,TipoAfiVehic,'0','','',IdOrigen,'0','','',IdDestino,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,0,FecRemesa,0,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,0,TipDocRef,NumDocRef,IdCiaRef,CantPago,VrAutRetIca
,RemTransb,CostoAdicTbdo,NitMucTbdo,TipoNovFact,ItemDocRef
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraFacRemesasItem] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero
,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino,Anulado,TipoNovFact,TipDocRef,NumDocRef,IdCiaRef,ItemDocRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem
,NitAsegurad,CantPago,VrAutRetIca,RemTransb,CostoAdicTbdo,NitMucTbdo
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehRemolq] @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4)
,@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4),@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30)
,@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50)
,@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME
,@pmVigTarjProp SMALLDATETIME,@pmConfEjes VARCHAR(5),@pmHidrostatica VARCHAR(30),@pmFecPruebaHid SMALLDATETIME,@pmVigPruebaHid SMALLDATETIME,@pmkmRuta INT,@pmkmOtro INT,@pmKingpin VARCHAR(50),@pmFecKingpin SMALLDATETIME
,@pmVigKingpin SMALLDATETIME,@pmArcFotoRem VARCHAR(50),@pmFecVigExtintor SMALLDATETIME,@pmCdLocTarj VARCHAR(8),@pmOperador_GPS VARCHAR(250),@pmUsuario_GPS VARCHAR(50),@pmClave_GPS VARCHAR(50),@pmIdOper_GPS VARCHAR(16),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO VehRemolq (IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,ConfEjes,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje
,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion,Observacion,IdEstado,Inactivo,FecAdd,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,Hidrostatica,FecPruebaHid,VigPruebaHid,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor,CdLocTarj,Operador_GPS,Usuario_GPS,Clave_GPS,IdOper_GPS)
 VALUES (@pmIdRemque,@pmIdMarca,@pmIdColor,@pmIdCrceria,@pmModelo,@pmNEjes,@pmnLlantas,@pmConfEjes,@pmAltura,@pmAncho,@pmLargo,@pmVlumen,@pmPesoKg,@pmCapneta,@pmUndCapc,@pmNumComp,@pmCapcComp,@pmNumSerial,@pmIdPropietario
,@pmNitProv,@pmFecCompra,@pmVidaUtil,@pmVrComercial,@pmVrAvaludo,@pmVrAsegurado,@pmKilmetraje,@pmEquipoAdic,@pmDescripcion,@pmCdCenSer,@pmCdLocal,@pmUbicacion,@pmObservacion,@pmIdEstado,@pmInactivo,@pmFecAdd,@pmIdUsuario
,@pmTarjetaProp,@pmFecTarjProp,@pmVigTarjProp,@pmHidrostatica,@pmFecPruebaHid,@pmVigPruebaHid,@pmkmRuta,@pmkmOtro,@pmKingpin,@pmFecKingpin,@pmVigKingpin,@pmArcFotoRem,@pmFecVigExtintor,@pmCdLocTarj,@pmOperador_GPS,@pmUsuario_GPS,@pmClave_GPS,@pmIdOper_GPS)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpVehRemolq] @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4),@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4)
,@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30),@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME
,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50),@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME,@pmVigTarjProp SMALLDATETIME,@pmConfEjes VARCHAR(5),@pmHidrostatica VARCHAR(30),@pmFecPruebaHid SMALLDATETIME,@pmVigPruebaHid SMALLDATETIME,@pmkmRuta INT,@pmkmOtro INT
,@pmKingpin VARCHAR(50),@pmFecKingpin SMALLDATETIME,@pmVigKingpin SMALLDATETIME,@pmArcFotoRem VARCHAR(50),@pmFecVigExtintor SMALLDATETIME,@pmCdLocTarj VARCHAR(8),@pmOperador_GPS VARCHAR(250),@pmUsuario_GPS VARCHAR(50),@pmClave_GPS VARCHAR(50),@pmIdOper_GPS VARCHAR(16),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE VehRemolq SET IdMarca=@pmIdMarca,IdColor=@pmIdColor,IdCrceria=@pmIdCrceria,Modelo=@pmModelo,NEjes=@pmNEjes,nLlantas=@pmnLlantas,ConfEjes=@pmConfEjes,Altura=@pmAltura,Ancho=@pmAncho,Largo=@pmLargo,Vlumen=@pmVlumen,PesoKg=@pmPesoKg,Capneta=@pmCapneta,UndCapc=@pmUndCapc
,NumComp=@pmNumComp,CapcComp=@pmCapcComp,NumSerial=@pmNumSerial,IdPropietario=@pmIdPropietario,NitProv=@pmNitProv,FecCompra=@pmFecCompra,VidaUtil=@pmVidaUtil,VrComercial=@pmVrComercial,VrAvaludo=@pmVrAvaludo,VrAsegurado=@pmVrAsegurado,Kilmetraje=@pmKilmetraje
,EquipoAdic=@pmEquipoAdic,Descripcion=@pmDescripcion,CdCenSer=@pmCdCenSer,CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,Observacion=@pmObservacion,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,TarjetaProp=@pmTarjetaProp,FecTarjProp=@pmFecTarjProp,VigTarjProp=@pmVigTarjProp
,Hidrostatica=@pmHidrostatica,FecPruebaHid=@pmFecPruebaHid,VigPruebaHid=@pmVigPruebaHid,kmRuta=@pmkmRuta,kmOtro=@pmkmOtro,Kingpin=@pmKingpin,FecKingpin=@pmFecKingpin,VigKingpin=@pmVigKingpin,ArcFotoRem=@pmArcFotoRem,FecVigExtintor=@pmFecVigExtintor,CdLocTarj=@pmCdLocTarj
,Operador_GPS=@pmOperador_GPS,Usuario_GPS=@pmUsuario_GPS,Clave_GPS=@pmClave_GPS,IdOper_GPS=@pmIdOper_GPS
 WHERE IdRemque=@pmIdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehRemolq] @pmIdRemque VARCHAR(10)
 AS 
 SELECT IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp
,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion
,Observacion,IdEstado,Inactivo,FecAdd,FecUpdate,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,ConfEjes,Hidrostatica,FecPruebaHid,VigPruebaHid
,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor,CdLocTarj,Operador_GPS,Usuario_GPS,Clave_GPS,IdOper_GPS
 FROM VehRemolq
 WHERE IdRemque=@pmIdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehRemolq_Sel] @pmIdRemque VARCHAR(10),@pmNewId VARCHAR(10)
 AS 
INSERT INTO VehRemolq (IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,ConfEjes,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje
,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion,Observacion,IdEstado,Inactivo,FecAdd,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,Hidrostatica,FecPruebaHid,VigPruebaHid,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor,CdLocTarj,Operador_GPS,Usuario_GPS,Clave_GPS,IdOper_GPS)
SELECT @pmNewId,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,ConfEjes,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje
,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion,Observacion,IdEstado,Inactivo,FecAdd,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,Hidrostatica,FecPruebaHid,VigPruebaHid,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor,CdLocTarj,Operador_GPS,Usuario_GPS,Clave_GPS,IdOper_GPS
FROM VehRemolq WHERE IdRemque=@pmIdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraConc] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmDescripcion VARCHAR(250),@pmtmTarifa DECIMAL(14,4),@pmtmTipoConc VARCHAR(10),@pmtmRubro VARCHAR(20)
,@pmtmCantidad DECIMAL(14,4),@pmtmVrUnitario MONEY,@pmtmTarifIva DECIMAL(14,4),@pmtmIdConcepto VARCHAR(4),@pmtmCdCuenta VARCHAR(16),@pmtmNitTercero VARCHAR(16),@pmtmVrBase MONEY
,@pmtmTipoTarif CHAR(1),@pmtmRefConc VARCHAR(50),@pmtmFijos BIT,@pmtmBaseRet INT,@pmtmTipDocRef VARCHAR(3),@pmtmNumOrden INT,@pmtmIdCiaOrd CHAR(2),@pmtmItemOrd INT,@pmtmTipoNov INT
 AS 
 INSERT INTO tm_TraConc (tmNumero,tmItem,tmDescripcion,tmTarifa,tmTipoConc,tmRubro,tmCantidad,tmVrUnitario,tmTarifIva,tmIdConcepto,tmCdCuenta,tmNitTercero,tmVrBase,tmTipoTarif,tmRefConc,tmFijos,tmBaseRet,tmTipDocRef,tmNumOrden,tmIdCiaOrd,tmItemOrd,tmTipoNov)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmDescripcion,@pmtmTarifa,@pmtmTipoConc,@pmtmRubro,@pmtmCantidad,@pmtmVrUnitario,@pmtmTarifIva,@pmtmIdConcepto,@pmtmCdCuenta,@pmtmNitTercero,@pmtmVrBase,@pmtmTipoTarif,@pmtmRefConc,@pmtmFijos,@pmtmBaseRet
 ,@pmtmTipDocRef,@pmtmNumOrden,@pmtmIdCiaOrd,@pmtmItemOrd,@pmtmTipoNov)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTraLiqDetalle] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDescConcepto VARCHAR(250),@pmIdCuenta VARCHAR(16),@pmVrDebito MONEY,@pmVrCredito MONEY,@pmIdTercero VARCHAR(16),@pmCodAgncia VARCHAR(16)
,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmRubroConc VARCHAR(10),@pmVrBase MONEY,@pmTarifBase DECIMAL(14,4),@pmCantComb DECIMAL(14,4),@pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCiaFac CHAR(2),@pmItemFac INT
,@pmFecVence SMALLDATETIME,@pmRefConcep VARCHAR(50),@pmTipSop VARCHAR(3),@pmNumSoporte INT,@pmIdCiaSop CHAR(2),@pmDocSop BIT,@pmTipoImp VARCHAR(4),@pmCodTarImp VARCHAR(4),@pmItemBase INT,@pmTipoNov INT,@pmTipDocNov VARCHAR(3),@pmNumDocNov INT,@pmCiaDocNov CHAR(2),@pmItemNov INT

 AS 
 INSERT INTO Trn_TraLiqDetalle (TipDoc,Liquidacion,IdCia,Item,IdConcepto,DescConcepto,IdCuenta,VrDebito,VrCredito,IdTercero,CodAgncia,CdCCosto,CdSubCos,pVehiculo,RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep,TipSop,NumSoporte,IdCiaSop
,DocSop,TipoImp,CodTarImp,ItemBase,TipoNov,TipDocNov,NumDocNov,CiaDocNov,ItemNov)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmItem,@pmIdConcepto,@pmDescConcepto,@pmIdCuenta,@pmVrDebito,@pmVrCredito,@pmIdTercero,@pmCodAgncia,@pmCdCCosto,@pmCdSubCos,@pmpVehiculo,@pmRubroConc,@pmVrBase,@pmTarifBase
,@pmCantComb,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmItemFac,@pmFecVence,@pmRefConcep,@pmTipSop,@pmNumSoporte,@pmIdCiaSop,@pmDocSop,@pmTipoImp,@pmCodTarImp,@pmItemBase,@pmTipoNov,@pmTipDocNov,@pmNumDocNov,@pmCiaDocNov,@pmItemNov)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipoReg INT,@pmtmTipRem VARCHAR(3),@pmtmRemesa INT,@pmtmIdCiaRem CHAR(2),@pmtmItemRem INT,@pmtmDescripcion VARCHAR(250),@pmtmCantidad DECIMAL(14,4),@pmtmVrUnitario MONEY,@pmtmVrCosto MONEY,@pmtmUndTarifa VARCHAR(10),@pmtmUndCosto VARCHAR(10),@pmtmUnidades DECIMAL(14,4),@pmtmPesoNeto DECIMAL(14,4),@pmtmUndMed VARCHAR(10),@pmtmVolumen DECIMAL(14,4)
,@pmtmUndVol VARCHAR(10),@pmtmCases INT,@pmtmCajas INT,@pmtmPalets INT,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaDct DECIMAL(14,4),@pmtmVrDscto MONEY,@pmtmTarifaRet DECIMAL(14,4),@pmtmVrRetFte MONEY,@pmtmTarifaIca DECIMAL(14,4),@pmtmVrReteIca MONEY,@pmtmVrFaltante MONEY,@pmtmRemision DECIMAL(18,2),@pmtmDocCliente VARCHAR(30),@pmtmReferencia1 VARCHAR(50),@pmtmReferencia2 VARCHAR(50),@pmtmReferencia3 VARCHAR(50)
,@pmtmCdMercancia VARCHAR(16),@pmtmCdConc VARCHAR(4),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmNitTercero VARCHAR(16),@pmtmCdAgencia VARCHAR(16),@pmtmpVehiculo VARCHAR(10),@pmtmTipoVehic VARCHAR(10),@pmtmNitRemite VARCHAR(16),@pmtmRemitente VARCHAR(250),@pmtmDirOrigen VARCHAR(250),@pmtmIdOrigen VARCHAR(8),@pmtmNitDestntario VARCHAR(16),@pmtmDestinatario VARCHAR(250),@pmtmDirDestino VARCHAR(250)
,@pmtmIdDestino VARCHAR(8),@pmtmCantFalt DECIMAL(14,4),@pmtmUnidadFalt VARCHAR(10),@pmtmCdTarRet VARCHAR(4),@pmtmCdTarIca VARCHAR(4),@pmtmCdTarDct VARCHAR(4),@pmtmPedRem INT,@pmtmCiaPed CHAR(2),@pmtmEtdoFact INT,@pmtmFecRemesa SMALLDATETIME,@pmtmIntCumplido INT,@pmtmVrDeclarado MONEY,@pmtmTarifSeguro DECIMAL(14,4),@pmtmVrSeguro MONEY,@pmtmNitAsegurad VARCHAR(16),@pmtmCostoCump MONEY,@pmtmTipoOrd VARCHAR(3),@pmtmNumOrden INT,@pmtmIdCiaOrd CHAR(2)
,@pmtmCantPago DECIMAL(14,4),@pmtmVrAutoIca MONEY,@pmtmRemTransb INT,@pmtmCostoAdic MONEY,@pmtmNitMucTbdo VARCHAR(16),@pmtmTipoNov INT,@pmtmItemOrd INT
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago,tmVrAutoIca,tmRemTransb,tmCostoAdic,tmNitMucTbdo,tmTipoNov,tmItemOrd)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipoReg,@pmtmTipRem,@pmtmRemesa,@pmtmIdCiaRem,@pmtmItemRem,@pmtmDescripcion,@pmtmCantidad,@pmtmVrUnitario,@pmtmVrCosto,@pmtmUndTarifa,@pmtmUndCosto,@pmtmUnidades,@pmtmPesoNeto,@pmtmUndMed,@pmtmVolumen,@pmtmUndVol,@pmtmCases,@pmtmCajas,@pmtmPalets,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaDct,@pmtmVrDscto,@pmtmTarifaRet,@pmtmVrRetFte,@pmtmTarifaIca,@pmtmVrReteIca,@pmtmVrFaltante,@pmtmRemision
,@pmtmDocCliente,@pmtmReferencia1,@pmtmReferencia2,@pmtmReferencia3,@pmtmCdMercancia,@pmtmCdConc,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmNitTercero,@pmtmCdAgencia,@pmtmpVehiculo,@pmtmTipoVehic,@pmtmNitRemite,@pmtmRemitente,@pmtmDirOrigen,@pmtmIdOrigen,@pmtmNitDestntario,@pmtmDestinatario,@pmtmDirDestino,@pmtmIdDestino,@pmtmCantFalt,@pmtmUnidadFalt,@pmtmCdTarRet,@pmtmCdTarIca,@pmtmCdTarDct,@pmtmPedRem,@pmtmCiaPed,@pmtmEtdoFact,@pmtmFecRemesa,@pmtmIntCumplido
,@pmtmVrDeclarado,@pmtmTarifSeguro,@pmtmVrSeguro,@pmtmNitAsegurad,@pmtmCostoCump,@pmtmTipoOrd,@pmtmNumOrden,@pmtmIdCiaOrd,@pmtmCantPago,@pmtmVrAutoIca,@pmtmRemTransb,@pmtmCostoAdic,@pmtmNitMucTbdo,@pmtmTipoNov,@pmtmItemOrd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFactLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCantPago,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmVrAutoIca,tmNitAsegurad,tmCostoCump,tmTipoNov
,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmItemOrd,tmRemTransb,tmCostoAdic,tmNitMucTbdo
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd
,tmCantPago,tmVrAutoIca,tmRemTransb,tmCostoAdic,tmNitMucTbdo,tmTipoNov,tmItemOrd
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero AND tmItem=@pmtmItem
GO
--dic 12
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNotas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNotasBuses]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotas_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotasBuses]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBuses_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotasBuses_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBusesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotasBusesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNotasRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNotas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNotasBuses]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotasBuses_Cr] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdClase VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdUsuario VARCHAR(11)=Null,@pmPrestCaja BIT=Null,@pmIntMora BIT=Null,@pmNumNotaIni INT=Null,@pmNumNotaFin INT=Null,@pmIdGrupo VARCHAR(4)=Null
AS
 SELECT TipDoc,NumNota,N.IdCia AS CodCia,Compania, Fecha,MulPlazos, N.IdPlazo AS codPlazo,Plazo,DiasPago,NVmto, FecVence, N.IdConcepto AS IdConcept,C.Concepto AS ConceptDoc, IdCliente,NP.RazonSocial AS Propietario,N.IdVehiculo AS PlacaVeh, N.IdConductor AS CedConductor
, NC.RazonSocial AS Conductor,ValorTotal, ValorIva, ValorReteFte,ValorReteIca, ValorNeto, ValorAplicado,NumCheque, N.IdBanco AS CodBanco,Banco,N.IdClase AS IdClas,ClaseCuenta, N.IdCargo AS CodCargo,F.Concepto AS ConceptCargo, Referencia, TipDcm, Documento, IdCiaDoc
 ,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ, N.IdEstado AS IdEstad,ED.Estado AS EstadDoc,N.IdUsuario AS IdUsuari,Usuario,TipoDoc,TipoNotaFE,IdConcFE,TimeSys, FecUpdate, IdCiaCrea,PrestCaja, IntMora,N.IdCCosto AS IdCcost,CCosto,BaseIva, BaseRet, TarifaIva, TarifaRet, IdTarIva
,IdTarRet,N.VehPropio AS VhPropio
--Datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
 FROM  Trn_NotasBuses AS N INNER JOIN  Companias  AS CI ON N.IdCia=CI.IdCia
 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 Vehiculos AS V ON N.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS NP ON N.IdCliente=NP.IdTercero
 INNER JOIN Terceros AS NC ON N.IdConductor=NC.IdTercero  INNER JOIN BusClaseCue AS CU ON N.IdClase=CU.IdClase
 INNER JOIN Bancos AS B ON N.IdBanco=B.IdBanco INNER JOIN Fijos AS F ON N.IdCargo=F.IdCargo
 INNER JOIN Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 INNER JOIN Sys_TiposDoc  AS TD ON N.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
  INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
  LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
  LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
  LEFT JOIN  TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa 
  LEFT JOIN TercCndtores AS CT ON N.IdConductor=CT.IdConductor
  LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
  LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar  LEFT JOIN TiposCom AS TM ON N.TipCom=TM.IdCom
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  NumNota BETWEEN ISNULL(@pmNumNotaIni,0)  AND ISNULL(@pmNumNotaFin,2147483647)
 AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND N.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND N.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND N.IdClase LIKE ISNULL(@pmIdClase,'%')
 AND N.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND N.IdUsuario LIKE ISNULL(@pmIdUsuario,'%') 
 AND (PrestCaja=ISNULL(@pmPrestCaja,0) or PrestCaja=ISNULL(@pmPrestCaja,1))
 AND (IntMora=ISNULL(@pmIntMora,0) or IntMora=ISNULL(@pmIntMora,1))
 AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 ORDER BY TipDoc,N.IdCia,NumNota
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotasRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCCosto  VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT TipDoc,NumNota,N.IdCia AS CdCia,Compania,Fecha,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,N.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,CodAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,N.VrCosto AS ValCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv
,N.IdCCosto AS CdCentCosto,CCosto,N.IdSubCos AS CdSubCentro,SubCosto,N.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,N.IdLocal AS NotCdCiudad,LN.Localidad AS NotCiudad,N.Referencia AS NotReferencia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm
,Documento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,DescripProd,CdForma,NumCheque,CdBanco,BN.Banco AS NomBanco,MulPlazos,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,PrestCaja,Modalidad,OrigenAdd
,TipCom,Comprobante,IdCiaCom,VrReteCREE,TarifaRtc,CodTarRtc,N.IdConcFE,N.TipoNotaFE,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,CdFntePago,FuentePago
 FROM Trn_Notas AS N INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON N.IdCliente=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  Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON N.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON N.IdAgencia=A.IdAgencia  INNER JOIN Localidades  AS LN ON N.IdLocal=LN.IdLocal
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON N.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN ProdMcias  AS PM ON N.CdProducto=PM.IdProducto  LEFT JOIN Bancos AS BN ON N.CdBanco=BN.IdBanco
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS TC ON N.CdConductor=TC.IdTercero
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND N.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdVend LIKE ISNULL(@pmIdVend,'%') 
 AND N.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY N.IdCia,NumNota
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotas_Cr] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc AS Tip_Doc,TipoDoc,NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FechaDoc,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,N.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,CodAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,N.VrCosto AS VrTotCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv
,N.IdCCosto AS CdCentCosto,CC.CCosto AS NotCenCosto,N.IdSubCos AS CdSubCentro,SC.SubCosto AS NotSubCentro,N.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,N.IdLocal AS NotCdCiudad,LN.Localidad AS NotCiudad,N.Referencia AS NotReferencia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm,N.Documento AS NumDocmento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,DescripProd
,CdForma,N.NumCheque AS NroCheque,CdBanco,BN.Banco AS NomBanco,MulPlazos,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,PrestCaja,Modalidad,VrReteCREE,TarifaRtc,CodTarRtc,IdConcFE,N.TipoNotaFE,OrigenAdd
,N.TipCom AS CodTipoComp,TipoCom,N.Comprobante AS NumComprob,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update
,N.IdCiaCrea AS CodCiaCrea,N.IdUsuario AS IdUsuari,Usuario,Leyenda
--detalle del comprobante
,Item,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,D.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,IdVehiculo,D.IdCCosto AS DetCodCentCost,DC.CCosto AS DetCentCost
,D.IdSubCos AS DetCodSubCentro,DS.SubCosto  AS DetSubCentro,VrBase,TarifaBase,D.TipDoc AS DetTipoDoc,D.Documento AS DetNumDoc,IdCiaDoc,TipFac,Factura,IdCiaFac,ItemFac
,FecVence,D.NumCheque AS DetNumCheque,TipoAplica,NitOtros,Referncia
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia,FE.CUFE_QR AS QR,FE.FechaValidacion
 FROM Trn_Notas AS N 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.IdCliente=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  Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON N.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON N.IdAgencia=A.IdAgencia  INNER JOIN Localidades  AS LN ON N.IdLocal=LN.IdLocal
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON N.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN ProdMcias  AS PM ON N.CdProducto=PM.IdProducto  LEFT JOIN Bancos AS BN ON N.CdBanco=BN.IdBanco
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS TC ON N.CdConductor=TC.IdTercero
 LEFT JOIN  Trn_ComDetalle AS D ON N.TipCom=D.TipCom AND N.Comprobante=D.Comprobante AND N.IdCiaCom=D.IdCia
 LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta LEFT JOIN Terceros AS DT ON D.IdTercero=DT.IdTercero
 LEFT JOIN CentroCosto AS DC ON D.IdCCosto=DC.IdCCosto LEFT JOIN SubCentros AS DS ON D.IdSubCos=DS.IdSubCos
 LEFT JOIN Trn_Face AS FE ON N.TipDoc=FE.TipDoc AND N.NumNota=FE.Documento AND N.IdCia=FE.IdCia
 WHERE N.TipDoc=@pmTipDoc AND NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
 ORDER BY  NumNota
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotasBusesLta] @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCliente VARCHAR(16),@pmIdVehiculo VARCHAR(10)
AS
 SELECT NumNota,IdCia,Fecha,FecVence,N.IdConcepto AS IdConcept,Concepto,IdCliente,T.RazonSocial AS Cliente, IdAgencia, IdVehiculo, IdConductor,O.RazonSocial AS Conductor,VehPropio, ValorTotal, ValorIva, ValorReteFte
,ValorReteIca, ValorCosto, ValorSancion, ValorNeto, ValorAplicado, IdVendedor,V.RazonSocial AS Vendedor,N.Comision AS Comison, IdTarCom, MulPlazos, N.IdPlazo AS IdPlaz,DiasPago AS Dias, PrestCaja, IntMora
,NumCheque,N.IdBanco AS IdBanc,Banco, IdCCosto, N.IdClase AS IdClas,IdCargo,Referencia,N.IdConcFE,N.TipoNotaFE,TipDcm,Documento,IdCiaDoc,BaseIva, BaseRet, TarifaIva, TarifaRet, IdTarIva
,IdTarRet, TipCom, Comprobante, IdCiaCom, N.Observacion AS Observ, N.IdEstado AS IdEstad,Estado,TimeSys, FecUpdate, IdCiaCrea,N.IdUsuario AS IdUsuari
 FROM Trn_NotasBuses AS N INNER JOIN Conceptos AS C ON N.IdConcepto=C.IdConcepto
  INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero  INNER JOIN Terceros AS O ON N.IdConductor=O.IdTercero
  LEFT JOIN Terceros AS V ON N.IdVendedor=V.IdTercero  LEFT JOIN Plazos AS P ON N.IdPlazo=P.IdPlazo
  LEFT JOIN Bancos AS B ON N.IdBanco=B.IdBanco LEFT JOIN EstadoDoc AS E ON N.IdEstado=E.IdEstado
 WHERE TipDoc=@pmTipDoc AND  Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 ORDER BY NumNota
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmValorTotal MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIca MONEY,@pmValorCosto MONEY,@pmValorSancion MONEY,@pmValorNeto MONEY,@pmValorAplicado MONEY
,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4),@pmIdTarCom VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmIntMora BIT,@pmNumCheque VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdClase VARCHAR(4),@pmIdCargo VARCHAR(4),@pmReferencia VARCHAR(20),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmIdTarIva VARCHAR(4)
,@pmIdTarRet VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmIdConcFE INT,@pmTipoNotaFE INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NotasBuses SET Fecha=@pmFecha,FecVence=@pmFecVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,VehPropio=@pmVehPropio,ValorTotal=@pmValorTotal,ValorIva=@pmValorIva,ValorReteFte=@pmValorReteFte,ValorReteIca=@pmValorReteIca,ValorCosto=@pmValorCosto,ValorSancion=@pmValorSancion,ValorNeto=@pmValorNeto,ValorAplicado=@pmValorAplicado,IdVendedor=@pmIdVendedor,Comision=@pmComision
,IdTarCom=@pmIdTarCom,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,PrestCaja=@pmPrestCaja,IntMora=@pmIntMora,NumCheque=@pmNumCheque,IdBanco=@pmIdBanco,IdCCosto=@pmIdCCosto,IdClase=@pmIdClase,IdCargo=@pmIdCargo,Referencia=@pmReferencia,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,BaseIva=@pmBaseIva,BaseRet=@pmBaseRet,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,IdTarIva=@pmIdTarIva,IdTarRet=@pmIdTarRet,TipCom=@pmTipCom
,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,IdConcFE=@pmIdConcFE,TipoNotaFE=@pmTipoNotaFE,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,FecVence,IdConcepto,IdCliente,IdAgencia,IdVehiculo,IdConductor,VehPropio,ValorTotal,ValorIva,ValorReteFte,ValorReteIca,ValorCosto,ValorSancion,ValorNeto,ValorAplicado,IdVendedor
,Comision,IdTarCom,MulPlazos,IdPlazo,PrestCaja,IntMora,NumCheque,IdBanco,IdCCosto,IdClase,IdCargo,Referencia,TipDcm,Documento,IdCiaDoc,BaseIva,BaseRet,TarifaIva,TarifaRet,IdTarIva,IdTarRet,TipCom,Comprobante
,IdCiaCom,Observacion,IdEstado,IdConcFE,TipoNotaFE,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_NotasBuses
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmValorTotal MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIca MONEY,@pmValorCosto MONEY,@pmValorSancion MONEY,@pmValorNeto MONEY,@pmValorAplicado MONEY
,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4),@pmIdTarCom VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmIntMora BIT,@pmNumCheque VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdClase VARCHAR(4),@pmIdCargo VARCHAR(4),@pmReferencia VARCHAR(20),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmIdTarIva VARCHAR(4)
,@pmIdTarRet VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmIdConcFE INT,@pmTipoNotaFE INT,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NotasBuses (TipDoc,NumNota,IdCia,Fecha,FecVence,IdConcepto,IdCliente,IdAgencia,IdVehiculo,IdConductor,VehPropio,ValorTotal,ValorIva,ValorReteFte,ValorReteIca,ValorCosto,ValorSancion,ValorNeto,ValorAplicado,IdVendedor,Comision,IdTarCom,MulPlazos,IdPlazo,PrestCaja,IntMora,NumCheque,IdBanco,IdCCosto,IdClase,IdCargo,Referencia,TipDcm,Documento,IdCiaDoc,BaseIva,BaseRet,TarifaIva,TarifaRet,IdTarIva,IdTarRet,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,IdConcFE,TipoNotaFE)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmFecVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdVehiculo,@pmIdConductor,@pmVehPropio,@pmValorTotal,@pmValorIva,@pmValorReteFte,@pmValorReteIca,@pmValorCosto,@pmValorSancion,@pmValorNeto,@pmValorAplicado,@pmIdVendedor,@pmComision,@pmIdTarCom,@pmMulPlazos,@pmIdPlazo,@pmPrestCaja,@pmIntMora,@pmNumCheque,@pmIdBanco,@pmIdCCosto,@pmIdClase,@pmIdCargo,@pmReferencia,@pmTipDcm
,@pmDocumento,@pmIdCiaDoc,@pmBaseIva,@pmBaseRet,@pmTarifaIva,@pmTarifaRet,@pmIdTarIva,@pmIdTarRet,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmIdConcFE,@pmTipoNotaFE)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNotas] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos
,IdVend,TarifaCom,CodTarCom,CdCajero,IdLocal,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,CdProducto,CdForma,NumCheque,CdBanco
,MulPlazos,IdPlazo,PrestCaja,Modalidad,nRemesa,CdCiaRem,ItemRem,VrReteCREE,TarifaRtc,CodTarRtc,IdConcFE,TipoNotaFE,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado
,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Notas
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNotas] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCosto MONEY,@pmVrSancion MONEY,@pmVrOtros MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmVrAnticipo MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY
,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmCdCajero VARCHAR(11),@pmIdLocal VARCHAR(8),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT
,@pmIdCiaDcm CHAR(2),@pmFecDcm SMALLDATETIME,@pmCdProducto VARCHAR(16),@pmCdForma VARCHAR(4),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmnRemesa INT,@pmCdCiaRem CHAR(2),@pmItemRem INT,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmIdConcFE INT,@pmTipoNotaFE INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Notas (TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,CdCajero,IdLocal,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,CdProducto,CdForma,NumCheque,CdBanco,MulPlazos,IdPlazo,PrestCaja
,Modalidad,nRemesa,CdCiaRem,ItemRem,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrReteCREE,TarifaRtc,CodTarRtc,IdConcFE,TipoNotaFE)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrCosto,@pmVrSancion,@pmVrOtros,@pmVrNeto,@pmVrAplicado,@pmVrAnticipo,@pmCantidad,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmCdCajero,@pmIdLocal,@pmReferencia,@pmpVehiculo
,@pmCdConductor,@pmTipDcm,@pmDocumento,@pmIdCiaDcm,@pmFecDcm,@pmCdProducto,@pmCdForma,@pmNumCheque,@pmCdBanco,@pmMulPlazos,@pmIdPlazo,@pmPrestCaja,@pmModalidad,@pmnRemesa,@pmCdCiaRem,@pmItemRem,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmIdConcFE,@pmTipoNotaFE)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNotas] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCosto MONEY,@pmVrSancion MONEY,@pmVrOtros MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY
,@pmVrAnticipo MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4)
,@pmCodTarCom VARCHAR(4),@pmCdCajero VARCHAR(11),@pmIdLocal VARCHAR(8),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmFecDcm SMALLDATETIME,@pmCdProducto VARCHAR(16),@pmCdForma VARCHAR(4),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT
,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmnRemesa INT,@pmCdCiaRem CHAR(2),@pmItemRem INT,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmIdConcFE INT,@pmTipoNotaFE INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Notas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrCosto=@pmVrCosto,VrSancion=@pmVrSancion,VrOtros=@pmVrOtros,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,VrAnticipo=@pmVrAnticipo,Cantidad=@pmCantidad,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,CdCajero=@pmCdCajero,IdLocal=@pmIdLocal,Referencia=@pmReferencia
,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDcm=@pmIdCiaDcm,FecDcm=@pmFecDcm,CdProducto=@pmCdProducto,CdForma=@pmCdForma,NumCheque=@pmNumCheque,CdBanco=@pmCdBanco,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,PrestCaja=@pmPrestCaja,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion
,IdEstado=@pmIdEstado,nRemesa=@pmnRemesa,CdCiaRem=@pmCdCiaRem,ItemRem=@pmItemRem,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,IdConcFE=@pmIdConcFE,TipoNotaFE=@pmTipoNotaFE,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNotasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmNotaIni INT=Null,@pmNotaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT NumNota,IdCia,Fecha,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,N.IdCCosto AS CodCcosto,CCosto,IdSubCos,IdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,CdCajero,N.IdLocal AS CodCiudad,Localidad,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,Modalidad,PrestCaja
,CdForma,NumCheque,CdBanco,MulPlazos,IdPlazo,VrReteCREE,TarifaRtc,CodTarRtc,IdConcFE,TipoNotaFE,OrigenAdd,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Notas AS N  INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON N.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON N.IdConcepto=CN.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 N.IdLocal=L.IdLocal
 WHERE TipDoc=@pmTipDoc   AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NumNota BETWEEN ISNULL(@pmNotaIni,0) AND ISNULL(@pmNotaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY IdCia,NumNota
GO
--dic 16
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Leg]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Leg]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetConc_Dlg]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetConc_Dlg]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Leg]  @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,DescConcepto,RubroConc,VrDebito,VrCredito,IdCuenta,IdTercero,CdCCosto,CdSubCos,pVehiculo
,'TERCEROS',VrBase,TarifBase,RefConcep,TipDocNov,NumDocNov,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodAgncia,CantComb,DocSop,TipoImp,CodTarImp,ItemBase,TipSop,NumSoporte,IdCiaSop,TipoNov,CiaDocNov,ItemNov
 FROM Trn_TraLiqDetalle
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetConc_Dlg]  @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetConc (tmNumero,tmItem,tmIdConcepto,tmDescripcion,tmTipoConc,tmVrDebito,tmVrCredito,tmCdCuenta,tmNitTercero,tmCdCCosto,tmCdSubCos,tmpVehiculo
,tmTipoAfiVehic,tmVrBase,tmTarifBase,tmReferencia,tmTipDocRef,tmDocRef,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodAgncia,tmCantComb,tmDocSop,tmTipoImp,tmCodTarImp,tmItemBase,tmTipoDs,tmNumDs,tmIdCiaDs,tmTipoNov,tmCiaDocRef,tmItemDocRef)
 SELECT @pmtmNumero,Item,IdConcepto,DescConcepto,RubroConc,VrCredito,VrDebito,IdCuenta,IdTercero,CdCCosto,CdSubCos,pVehiculo
,'TERCEROS',VrBase,TarifBase,RefConcep,TipDocNov,NumDocNov,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodAgncia,CantComb,DocSop,TipoImp,CodTarImp,ItemBase,TipSop,NumSoporte,IdCiaSop,TipoNov,CiaDocNov,ItemNov
 FROM Trn_TraLiqDetalle
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
--dic 23
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAjustes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAjustes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAjustes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAjustesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAjustesLta]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAjustesLta] @pmTipNota VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT A.TipNota,TD.TipoDoc,A.NumNota,A.IdCia,Compania,A.IdPeriodo,A.FechaInicial,A.FechaFinal,A.IdEmpleado,E.Apellidos,E.Nombres,A.NContrato
,D.Item,D.IdConcepto,C.Concepto,C.ClaseCon,D.Detalle,D.CantDevg,D.CantDed,D.VrUnitario,D.VrTotDevg,D.VrTotDed,D.VrBaseLiq,D.VrBasExceso,D.Unidad,D.ClaseLiq,D.VrOrigen,D.Tarifa
,D.DiasCalc,D.DiasNov,D.FecInicial AS DetFecFinal,D.FecFinal AS DetFecInicial,D.CodFondo,FN.Fondo,D.NPrestamo,D.NCuota,D.OrigCargue,D.NitTercero,T.RazonSocial AS NomTercero,D.TipoIncLab
,A.IdRegNom,A.NumLiquida,A.NumSemLiq,A.IdNom,TipoNomina,A.TipoLiq,A.Observacion,A.NA_Estado,A.NA_Prefijo,A.NA_Numero,A.NA_FecEnvio,A.NA_CUNE,A.NA_QR
,A.NE_Prefijo,A.NE_Numero,A.NE_CUNE,A.NE_QR,A.NE_TipoAju,A.FechaCrea,A.IdCiaCrea,A.IdUsuario,Usuario
--contrato/empleado
,CN.IdArea,Area,CN.IdDep AS CodDep,DP.Dependencia,CN.IdCargo,Cargo,CN.IdTipCon,TipoContrato,CN.FecIngreso,CN.FecVigencia,CN.FecRetiro,CN.VrSalario,CN.VrAuxTrans,CN.Inactivo
,E.Codigo,E.IdLugarCed,LC.Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf AS CodProf,Profesion
FROM Trn_NomAjustes AS A INNER JOIN Trn_NomAjustesDet AS D ON A.TipNota=D.TipNota AND A.NumNota=D.NumNota AND A.IdCia=D.IdCia
INNER JOIN Empleados AS E ON A.IdEmpleado=E.IdEmpleado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
INNER JOIN TiposNom AS TN ON A.IdNom=TN.IdNom INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
INNER JOIN Emp_Contrato AS CN ON A.NContrato=CN.NContrato INNER JOIN Dependencias AS DP ON CN.IdDep=DP.IdDep
INNER JOIN Areas AS AR ON CN.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON CN.IdCargo=CG.IdCargo 
INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN TiposCon AS TC ON CN.IdTipCon=TC.IdTipCon
LEFT JOIN Sys_TiposDoc AS TD ON A.TipNota=TD.IdDoc LEFT JOIN Fondos AS FN ON D.CodFondo=FN.IdFondo
LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
WHERE A.FechaInicial BETWEEN @pmFechaIni AND @pmFechaFin AND (A.TipNota=@pmTipNota OR @pmTipNota IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPeriodo VARCHAR(8),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmIdNom VARCHAR(4),@pmTipoLiq CHAR(1),@pmObservacion VARCHAR(2000),@pmNA_Estado INT
,@pmNA_Prefijo VARCHAR(20),@pmNA_Numero INT,@pmNA_FecEnvio SMALLDATETIME,@pmNA_CUNE VARCHAR(500),@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_CUNE VARCHAR(500),@pmNE_TipoAju INT,@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomAjustes (TipNota,NumNota,IdCia,IdEmpleado,NContrato,IdPeriodo,FechaInicial,FechaFinal,IdRegNom,NumLiquida,NumSemLiq,IdNom,TipoLiq,Observacion,NA_Estado,NA_Prefijo,NA_Numero,NA_FecEnvio,NA_CUNE,NE_Prefijo,NE_Numero,NE_CUNE,FechaCrea,IdCiaCrea,IdUsuario,NE_TipoAju)
 VALUES (@pmTipNota,@pmNumNota,@pmIdCia,@pmIdEmpleado,@pmNContrato,@pmIdPeriodo,@pmFechaInicial,@pmFechaFinal,@pmIdRegNom,@pmNumLiquida,@pmNumSemLiq,@pmIdNom,@pmTipoLiq,@pmObservacion,@pmNA_Estado,@pmNA_Prefijo,@pmNA_Numero,@pmNA_FecEnvio,@pmNA_CUNE,@pmNE_Prefijo,@pmNE_Numero,@pmNE_CUNE,@pmFechaCrea,@pmIdCiaCrea,@pmIdUsuario,@pmNE_TipoAju)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPeriodo VARCHAR(8),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmIdNom VARCHAR(4),@pmTipoLiq CHAR(1),@pmObservacion VARCHAR(2000),@pmNA_Estado INT
,@pmNA_Prefijo VARCHAR(20),@pmNA_Numero INT,@pmNA_FecEnvio SMALLDATETIME,@pmNA_CUNE VARCHAR(500),@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_CUNE VARCHAR(500),@pmNE_TipoAju INT
 AS 
 UPDATE Trn_NomAjustes SET IdEmpleado=@pmIdEmpleado,NContrato=@pmNContrato,IdPeriodo=@pmIdPeriodo,FechaInicial=@pmFechaInicial,FechaFinal=@pmFechaFinal,IdRegNom=@pmIdRegNom,NumLiquida=@pmNumLiquida,NumSemLiq=@pmNumSemLiq,IdNom=@pmIdNom,TipoLiq=@pmTipoLiq
,Observacion=@pmObservacion,NA_Estado=@pmNA_Estado,NA_Prefijo=@pmNA_Prefijo,NA_Numero=@pmNA_Numero,NA_FecEnvio=@pmNA_FecEnvio,NA_CUNE=@pmNA_CUNE,NE_Prefijo=@pmNE_Prefijo,NE_Numero=@pmNE_Numero,NE_CUNE=@pmNE_CUNE,NE_TipoAju=@pmNE_TipoAju
 WHERE TipNota=@pmTipNota AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipNota,NumNota,IdCia,IdEmpleado,NContrato,IdPeriodo,FechaInicial,FechaFinal,IdRegNom,NumLiquida,NumSemLiq,IdNom,TipoLiq,Observacion,NA_Estado,NA_Prefijo,NA_Numero,NA_FecEnvio,NA_CUNE,NA_QR
,NE_Prefijo,NE_Numero,NE_CUNE,NE_QR,NE_TipoAju,FechaCrea,IdCiaCrea,IdUsuario
 FROM Trn_NomAjustes
 WHERE TipNota=@pmTipNota AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO