if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPolizas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsPolizas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaRst]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaRst]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCompra_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOCompra_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPolizas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPolizas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPolizasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPolizasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpPolizas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpPolizas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraRemAnexo]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaRst] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT N.IdEmpleado,Apellidos,Nombres,DATEDIFF(YEAR,E.FecNacmto,GETDATE()) AS Edad,E.Sexo,N.NContrato,N.IdNom,TipoNomina,ISNULL(D.HoraInicial,N.FecFinal) AS Fecha,N.IdPeriodo
,D.IdConcepto,CN.Concepto,ISNULL(D.HoraInicial,N.FecFinal) AS Hora_Inicial,ISNULL(D.HoraFinal,N.FecFinal) AS Fec_Final
,D.CantDevg AS CantHoras,D.VrUnitario,D.VrTotDevg AS VrLiquidado,D.VrOrigen AS BaseLiq,D.Tarifa,C.IdCargo,Cargo,D.Detalle,D.ClaseLiq,D.OrigCargue,CN.Unidad
,CN.Tarifa AS ConcTarifa,N.VrSalario,C.IdArea,Area,C.IdDep,Dependencia,C.IdInstala,Instlacion,C.IdCia,Compania,C.IdCCosto,CCosto,C.IdSubCos,SubCosto
FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro AND N.IdEmpleado=D.IdEmpleado
INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
INNER JOIN Instalaciones AS IST ON C.IdInstala=IST.IdInstala INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos 
WHERE (N.IdPeriodo=@pmIdPeriodo OR @pmIdPeriodo IS NULL) AND (N.IdEmpleado=@pmIdEmpleado OR @pmIdEmpleado IS NULL)
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipDoc,TipoDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,CN.Compania,R.Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,IdLocDestino,CD.Localidad AS CiuDestino
,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,Concepto,CdRutaTarif,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario,Leyenda
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,RA.CantViajes
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,RA.RemMTEstado,RA.Rem_FecchaReg,RA.Rem_Descr_Prod,TipSal,NumSalida,IdCiaSal,TipOrdMud,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,CdBodega,Bodega,RA.TipoDevRemesa,RA.CodCiuTransb,LT.Localidad AS CiudadTransb,RA.MvoTransbordo
,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--detalle mcias
,Item,D.IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,UMP.CodUMFE,dmsAlto,dmsAncho,dmsLargo,Volumen,D.UndVol,UMV.Unidad AS DescUMVol,UMV.CodUMFE AS CodUMVol,Cases,Cajas,Palets,NitRemite,Remitente
,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,TarifPago,TarifTabla,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont,TipDocInv,NumDocInv,IdCiaDocInv,ItemDocInv,D.SedeRem,SDR.NomSede AS NomSedeRem,SDR.CoordLat AS CoordLatRem,SDR.CoordLong AS CoordLongRem,D.SedeDest,SDD.NomSede AS NomSedeDest,SDD.CoordLat AS CoordLatDest,SDD.CoordLong AS CoordLongDest
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango,MC.CodigoUN,MC.EstadoMcia,RSP.Designacion AS DescResPel,RSP.Clase AS ClaseResPel,RSP.PeligroSec,RSP.GrupoEmb
--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
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,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,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
,RA.PolizaMerPel,MP.Vigencia AS MerPelVigencia,MP.NitCompania AS MerPelNit,MP.Compania AS MerPelCompania
 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 Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc AND R.NumOrden=D.NumOrden AND R.IdCia=D.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 Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc  INNER JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal
 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 adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 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 Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia INNER JOIN Mercancias AS MC ON D.IdMercancia=MC.IdMercancia
 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN Sys_Um AS UMV ON D.UndVol=UMV.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto
 LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom LEFT JOIN AlmBodegas AS BG ON R.CdBodega=BG.IdBodega
 LEFT JOIN TercCndtores AS CT ON R.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN TercSedes AS SDR ON D.NitRemite=SDR.IdTercero AND D.SedeRem=SDR.IdSede LEFT JOIN TercSedes AS SDD ON D.NitDestntario=SDD.IdTercero AND D.SedeDest=SDD.IdSede
 LEFT JOIN Localidades AS LT ON RA.CodCiuTransb=LT.IdLocal LEFT JOIN ResiduosPeligrosos AS RSP ON MC.CodigoUN=RSP.CodigoUN
 LEFT JOIN Polizas AS MP ON RA.PolizaMerPel=MP.NumPoliza
 WHERE R.TipDoc=@pmTipDoc AND R.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND R.IdCia=@pmIdCia
 ORDER BY R.NumOrden
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryOCompra_Cr] @pmTipDoc VARCHAR(3),@pmOCompraIni INT,@pmOCompraFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCompra,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto
,O.IdProv AS NitProvee,T.RazonSocial AS NomProvee,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrNeto,Cantidad
,VrRetencion,VrReteICA,VrReteIVA,CodTarRiv,O.TarifaRet AS OrdTarifRet,O.TarifaIca AS OrdTarifIca,O.CodTarRet AS OrdCodTarRef,O.CodTarIca AS OrdCodTarIca,O.VrImpuBA,O.VrImpuCUP,IdEmpresa,TE.RazonSocial AS NomEmpresa,DirEntrega
,IdLocEtga,LE.Localidad AS CiudadEntrega,LE.IdDep AS CdDepEntrega,DE.Departamento AS DptoEntrega,DiasEntraga,O.NitContac AS Nit_Contacto,O.NomContac AS NombContacto,O.TelContac AS Tel_Contacto,O.emlContac AS Eml_Contacto
,O.IdCCosto AS CdCentCosto,CC.CCosto AS CentCosto,O.IdSubCos AS CdSubcost,SC.SubCosto AS SubcCosto,O.IdForma AS CdForma,FormaPago,DetallePago
,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.CdMney AS CodMoney,NitEmpTrans,EmpTrans,O.LiqFletes AS Liq_Fletes,O.pVehiculo AS OrdPlacaVeh
,CdConductor,TCD.RazonSocial AS Conductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,VrIvaMvc,VrReteCREE,TarifaRtc,CodTarRtc,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ
,NumAprob,FecAprob,CdUsuAprob,UA.Usuario AS NomUsuAprob,NivelAprob,O.NumDocSop,O.CdCiaSop,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fec_Add,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercCiudad,L.IdDep AS TercIdDep,D.Departamento AS TercDpto
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercEnte
--Información del proveedor
,TP.IdGrupo AS ProvGrupo,GrupoClie,TP.NitContac AS ProvNitContact,TP.NomContac AS ProvNomContacto,TP.TelContac AS ProvTelContact,TP.emlContac AS ProvEmailContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
--detalles
,K.Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS PlacaVeh,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden,K.CodTarBa,K.TarifaIba,K.VrImpuBa AS KarImpuBa,K.CodTarCup,K.TarifaCup,K.VrImpuCup AS KarImpuCup
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_OCompra AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCompra=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdProv=T.IdTercero INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TE ON O.IdEmpresa=TE.IdTercero  INNER JOIN CentroCosto AS CC ON O.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN Localidades AS LE ON O.IdLocEtga=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TCD ON O.CdConductor=TCD.IdTercero
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON O.IdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON O.IdProv=TP.IdProv
 LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND OCompra BETWEEN @pmOCompraIni AND @pmOCompraFin  AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino
,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv
,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes,TipOrdMud,ItemOsm
,RemMTEstado,Rem_Descr_Prod,Rem_FecchaReg,PolizaMerPel
 FROM Trn_TraRemAnexo
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150)
,@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME
,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME
,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT,@pmTipOrdMud VARCHAR(3),@pmItemOsm INT,@pmRemMTEstado INT,@pmRem_Descr_Prod VARCHAR(500),@pmRem_FecchaReg DATETIME,@pmPolizaMerPel VARCHAR(30)
 AS 
 INSERT INTO Trn_TraRemAnexo (TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans
,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes,TipOrdMud,ItemOsm,RemMTEstado,Rem_Descr_Prod,Rem_FecchaReg,PolizaMerPel)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmIdVend,@pmIdTarifCom,@pmNomCliente,@pmNomRemite,@pmNomDestino,@pmLugarCargue,@pmLugarDescargue,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmContacDestino,@pmTelContacDest,@pmemlContacDest,@pmTomadorPoliza,@pmPolizaSeguro,@pmNitCiaPoliza,@pmNomCiaPoliza
,@pmFecVencePol,@pmManifMintrans,@pmRemMintrans,@pmTipSal,@pmNumSalida,@pmIdCiaSal,@pmNumInvent,@pmIdCiaInv,@pmTipoRuta,@pmTipoMintrans,@pmNitEmpTransp,@pmNumFactEmp,@pmMvoAnulacion,@pmPactoCargue,@pmHorasPactoCargue,@pmMinPactoCargue,@pmHoraCitaCargue,@pmHoraLlegaCargue,@pmHoraEntraCargue
,@pmHoraSaleCargue,@pmPactoDescargue,@pmHorasPactoDescargue,@pmMinPactoDescargue,@pmHoraCitaDescargue,@pmHoraLlegaDescargue,@pmHoraEntraDescargue,@pmHoraSaleDescargue,@pmTipoDevRemesa,@pmCodCiuTransb,@pmMvoTransbordo,@pmCantViajes,@pmTipOrdMud,@pmItemOsm,@pmRemMTEstado,@pmRem_Descr_Prod,@pmRem_FecchaReg,@pmPolizaMerPel)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30)
,@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2)
,@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT,@pmTipOrdMud VARCHAR(3),@pmItemOsm INT
,@pmRemMTEstado INT,@pmRem_Descr_Prod VARCHAR(500),@pmRem_FecchaReg DATETIME,@pmPolizaMerPel VARCHAR(30)
 AS 
 UPDATE Trn_TraRemAnexo SET IdVend=@pmIdVend,IdTarifCom=@pmIdTarifCom,NomCliente=@pmNomCliente,NomRemite=@pmNomRemite,NomDestino=@pmNomDestino,LugarCargue=@pmLugarCargue,LugarDescargue=@pmLugarDescargue,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,ContacDestino=@pmContacDestino,TelContacDest=@pmTelContacDest
,emlContacDest=@pmemlContacDest,PolizaSeguro=@pmPolizaSeguro,NitCiaPoliza=@pmNitCiaPoliza,NomCiaPoliza=@pmNomCiaPoliza,FecVencePol=@pmFecVencePol,ManifMintrans=@pmManifMintrans,RemMintrans=@pmRemMintrans,TipSal=@pmTipSal,NumSalida=@pmNumSalida,IdCiaSal=@pmIdCiaSal,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TomadorPoliza=@pmTomadorPoliza
,TipoRuta=@pmTipoRuta,TipoMintrans=@pmTipoMintrans,NitEmpTransp=@pmNitEmpTransp,NumFactEmp=@pmNumFactEmp,MvoAnulacion=@pmMvoAnulacion,PactoCargue=@pmPactoCargue,HorasPactoCargue=@pmHorasPactoCargue,MinPactoCargue=@pmMinPactoCargue
,HoraCitaCargue=@pmHoraCitaCargue,HoraLlegaCargue=@pmHoraLlegaCargue,HoraEntraCargue=@pmHoraEntraCargue,HoraSaleCargue=@pmHoraSaleCargue,PactoDescargue=@pmPactoDescargue,HorasPactoDescargue=@pmHorasPactoDescargue,MinPactoDescargue=@pmMinPactoDescargue
,HoraCitaDescargue=@pmHoraCitaDescargue,HoraLlegaDescargue=@pmHoraLlegaDescargue,HoraEntraDescargue=@pmHoraEntraDescargue,HoraSaleDescargue=@pmHoraSaleDescargue,TipoDevRemesa=@pmTipoDevRemesa,CodCiuTransb=@pmCodCiuTransb,MvoTransbordo=@pmMvoTransbordo,CantViajes=@pmCantViajes,TipOrdMud=@pmTipOrdMud,ItemOsm=@pmItemOsm
,RemMTEstado=@pmRemMTEstado,Rem_Descr_Prod=@pmRem_Descr_Prod,Rem_FecchaReg=@pmRem_FecchaReg,PolizaMerPel=@pmPolizaMerPel
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsPolizas] @pmNumPoliza VARCHAR(30),@pmFecha SMALLDATETIME,@pmVigencia SMALLDATETIME,@pmNitCompania VARCHAR(16),@pmCompania VARCHAR(150)
,@pmNitTomador VARCHAR(16),@pmTomador VARCHAR(150),@pmNitAseg VARCHAR(16),@pmAsegurado VARCHAR(150),@pmNitBenef VARCHAR(16),@pmBeneficiario VARCHAR(150)
,@pmInactivo BIT,@pmDescFirma VARCHAR(2000),@pmCodEmpresa VARCHAR(20),@pmTipo_Poliza VARCHAR(20),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Polizas (NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado,NitBenef,Beneficiario,DescFirma,Inactivo,FechaAdd,IdUsuario,CodEmpresa,Tipo_Poliza)
 VALUES (@pmNumPoliza,@pmFecha,@pmVigencia,@pmNitCompania,@pmCompania,@pmNitTomador,@pmTomador,@pmNitAseg,@pmAsegurado
,@pmNitBenef,@pmBeneficiario,@pmDescFirma,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmCodEmpresa,@pmTipo_Poliza)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpPolizas] @pmNumPoliza VARCHAR(30),@pmFecha SMALLDATETIME,@pmVigencia SMALLDATETIME,@pmNitCompania VARCHAR(16),@pmCompania VARCHAR(150),@pmNitTomador VARCHAR(16)
,@pmTomador VARCHAR(150),@pmNitAseg VARCHAR(16),@pmAsegurado VARCHAR(150),@pmNitBenef VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmDescFirma VARCHAR(2000),@pmInactivo BIT,@pmCodEmpresa VARCHAR(20),@pmTipo_Poliza VARCHAR(20),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Polizas SET Fecha=@pmFecha,Vigencia=@pmVigencia,NitCompania=@pmNitCompania,Compania=@pmCompania,NitTomador=@pmNitTomador,Tomador=@pmTomador,NitAseg=@pmNitAseg
,Asegurado=@pmAsegurado,NitBenef=@pmNitBenef,Beneficiario=@pmBeneficiario,Inactivo=@pmInactivo,DescFirma=@pmDescFirma,CodEmpresa=@pmCodEmpresa,Tipo_Poliza=@pmTipo_Poliza,FechaUpdate=@pmFechaUpdate
 WHERE NumPoliza=@pmNumPoliza
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryPolizas] @pmNumPoliza VARCHAR(30)
 AS 
 SELECT NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado
,NitBenef,Beneficiario,Inactivo,CodEmpresa,Tipo_Poliza,FechaAdd,FechaUpdate,IdUsuario,DescFirma
 FROM Polizas
 WHERE NumPoliza=@pmNumPoliza
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryPolizasLta]
 AS 
 SELECT NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado
,NitBenef,Beneficiario,DescFirma,CodEmpresa,Tipo_Poliza,P.IdUsuario AS IdUsuari,Usuario,FechaAdd,FechaUpdate
 FROM Polizas AS P INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 WHERE P.Inactivo=0
 ORDER BY NumPoliza
GO
--abril 28
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosDsoT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosDsoT]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosDsoT] @pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT T.IdTercero,T.RazonSocial,T.TipoId,CASE WHEN T.TipoId <> 'N' AND IniStgNom>1 THEN SUBSTRING(T.RazonSocial,1,IniStgNom-1) ELSE '' END AS Apellidos
 ,CASE WHEN T.TipoId <> 'N' AND IniStgNom>0 THEN SUBSTRING(T.RazonSocial,IniStgNom,(LEN(T.RazonSocial)-IniStgNom)+1) ELSE '' END Nombres
,T.FechaNac,T.Direccion,T.e_mail,T.Telefono,T.TelMovil,T.RecPublicos,T.CargoPublico,T.PersonaPub,T.FecInicioPP,T.FecFinalPP,T.IdSector,S.SectorEco
,T.RL_Nombre,T.IdLocal,L.Localidad,D.Departamento,P.NombrePais,T.VrIngresos,T.VrOtroIng,T.VrGastos,T.VrActivos,T.VrPasivos,T.OperActVirtual,T.TipoOperVirtual
,T.LavadoActivos,T.Inactivo AS Inactvo
,CASE WHEN T.EsCliente = 1 THEN TC.Referencia WHEN T.EsProveedor = 1 THEN TP.NomContac ELSE '' END AS NombreContacto
,CASE WHEN T.EsCliente = 1 THEN TC.TelRefncia WHEN T.EsProveedor = 1 THEN TP.TelContac ELSE '' END AS TelefonoContacto
,CASE WHEN T.EsCliente = 1 THEN TC.CargRefncia WHEN T.EsProveedor = 1 THEN TP.CargContac ELSE '' END AS CargoContacto
 FROM Terceros AS T INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN adm_Paises AS P ON D.IdPais=P.IdPais
 LEFT JOIN TercProvee AS TP ON T.IdTercero=TP.IdProv LEFT JOIN TercClieRef AS TC ON T.IdTercero=TC.IdClie 
 WHERE (T.IdEstado=@pmIdEstado OR @pmIdEstado IS NULL)  
GO
--MAY 5
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAstnciaTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAstnciaTot]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomAstnciaTot] @pmIdEmpleado VARCHAR(16)=Null,@pmFecInicio SMALLDATETIME=Null,@pmFecFinal SMALLDATETIME=Null
,@pmEsDmncal BIT=Null,@pmTipoJrnada VARCHAR(10)=Null
 AS 
 SELECT Count(FechaDia) AS Cant,SUM(HHDiurno) AS STotHHD,SUM(HHNocturno) AS STotHHN,SUM(HEDiurno) AS STotHED ,SUM(HENocturno) AS STotHEN
 ,SUM(CASE WHEN (TipoJrnada='DESCANSO' OR TipoJrnada='DSCSINCOMP') THEN Horas_Dia ELSE 0 END) AS STotDesc
 FROM Trn_NomAstncia
 WHERE FechaDia BETWEEN @pmFecInicio AND @pmFecFinal 
 AND IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (TipoJrnada=@pmTipoJrnada OR @pmTipoJrnada IS NULL)
 AND (EsDmncal=ISNULL(@pmEsDmncal,0) or EsDmncal=ISNULL(@pmEsDmncal,1))
GO