if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAportante]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAportante]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmpleadosCnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmpleadosCnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmpleadosLab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmpleadosLab]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAportante]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAportante]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdOrdenEntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdOrdenEntFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAportante]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAportante]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpProdEntrega]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpProdEntrega]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdEntrega]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdEntrega]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdEntrega_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdEntrega_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdEntrega]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsProdEntrega]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmpleadosCnt] @pmIdNom VARCHAR(4),@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT E.IdEmpleado AS IdEmpledo,Apellidos,Nombres,E.TipoId,E.Dv,IdLugarCed,LC.Localidad AS LugarCed,E.IdProf AS CodProf,Profesion,TallaCam,TallaPan,TallaZap
,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom
,CC.Fondo AS CajaComp,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,CdLocCue,E.NContrato AS NumContrato,FecUltIng,FecUltRet,FecVincula,NCAnterior,FecIngAnt,FecRetAnt
,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,FecUltAum,FecUltCes
,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,FecUltParc,EsCondtor,pVehiculo,DecRenta,E.IdEstado AS IdEst_Emp,ET.Estado AS Estad_Emp,E.Inactivo AS Inctivo,Codigo,E.IdUsuario  AS IdUsuar
--datos del contrato
,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,Jornada,Rangos,C.IdArea AS CodArea,Area,C.IdDep AS CodDep
,Dependencia,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac
,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,TipoTrabj,IdCot,SubTipoCot,IdGrupo,VrUpc,Reclmto,Notas,C.IdEstado AS IdEst_Con,EC.Estado AS Estad_Con,C.Inactivo AS Inactvo,CdFonAvp,FspFinmes,FecFinPrueba
,LiqEspSalud,DctoSalud,PagoSalud,TN.NomDiario
 FROM Empleados AS E INNER JOIN Emp_Contrato AS C ON E.NContrato=C.NContrato
 INNER JOIN  Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia  INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado
 WHERE E.Inactivo=0 AND  C.IdNom LIKE ISNULL(@pmIdNom,'%')  AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND E.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER  BY E.IdEmpleado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmpleadosLab] @pmIdEmpleado VARCHAR(16),@pmCodigo VARCHAR(16)
 AS 
 SELECT IdEmpleado,Apellidos,Nombres,TipoId,Dv,IdLugarCed,LC.Localidad AS LugarCed,E.IdProf AS CodProf,Profesion,TallaCam,TallaPan,TallaZap
,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom
,CC.Fondo AS CajaComp,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,CdLocCue,NContrato,FecVincula
,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,FecUltAum,FecUltCes
,FecUltPri,FecUltVac,FecUltIng,FecUltRet,FecUltDot,FecUltExm,FecUltCap,FecUltParc,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,DecRenta,NivelEduc
,E.IdEstado AS IdEstdo,Estado,E.Inactivo AS Inctivo,Codigo,IdUsuario,CdFonAvp
 FROM Empleados AS E INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado
 WHERE IdEmpleado=@pmIdEmpleado OR Codigo=@pmCodigo
 ORDER  BY IdEmpleado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdOrdenEntFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
AS 
SELECT O.TipDoc,O.NumEntrega,O.IdCia,Compania,O.Fecha,O.FecEntrega,O.TipoEntrega,O.NitTercero,RazonSocial,O.NomContacto,O.TelContacto
,O.TipOrden,O.NumOrden,O.IdCiaOrden,O.EdoOrden,O.Anulado,O.FecDev,O.Observacion,O.TimeSys AS FechaCrea,O.IdUsuario,Usuario
,D.Item,D.ItemOrden,D.CantOrden,D.Cantidad,D.Defectuoso,D.Rechazado,D.CostoUnit,D.VrDcto,D.TipPed,D.Pedido,D.IdCiaPed,D.ItemPed
,D.IdProducto,P.DescripProd,D.Descripcion,D.CdTipProc,TipoProceso,D.Tallas,D.ProcFinal,D.Observacion AS MotivoRchzo,P.Tallaje
FROM Trn_ProdEntrega AS O INNER JOIN Trn_ProdEntregaDet AS D ON O.TipDoc=D.TipDoc AND O.NumEntrega=D.NumEntrega AND O.IdCia=D.IdCia
INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
LEFT JOIN TiposProceso AS TP ON D.CdTipProc=TP.IdTipProc LEFT JOIN Terceros AS T ON O.NitTercero=T.IdTercero
WHERE O.TipOrden=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin  AND O.IdCia=@pmIdCia
AND O.Anulado=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAportante] @pmIdAportnte VARCHAR(16),@pmRazonSocial VARCHAR(200),@pmTipoId CHAR(1),@pmDvNit CHAR(1),@pmIdRepLegal VARCHAR(16),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pme_mail VARCHAR(100)
,@pmClaseApo CHAR(1),@pmTipoEco CHAR(1),@pmTipoPersna CHAR(1),@pmCIIUDane VARCHAR(8),@pmFecIniCndato SMALLDATETIME,@pmTipAccCndato INT,@pmFecFinCndato SMALLDATETIME,@pmCodOperInfo VARCHAR(10),@pmTipoApo CHAR(1),@pmFecMatMerc SMALLDATETIME
,@pmExonParaf INT,@pmExonCajas INT,@pmCondEspecial VARCHAR(250),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO NomAportante (IdAportnte,RazonSocial,TipoId,DvNit,IdRepLegal,Direccion,IdLocal,Telefono,Fax,e_mail,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo,FecAdd,IdUsuario,FecMatMerc,ExonParaf,ExonCajas,CondEspecial)
 VALUES (@pmIdAportnte,@pmRazonSocial,@pmTipoId,@pmDvNit,@pmIdRepLegal,@pmDireccion,@pmIdLocal,@pmTelefono,@pmFax,@pme_mail,@pmClaseApo,@pmTipoEco,@pmTipoPersna,@pmCIIUDane,@pmFecIniCndato,@pmTipAccCndato
,@pmFecFinCndato,@pmCodOperInfo,@pmTipoApo,@pmFecAdd,@pmIdUsuario,@pmFecMatMerc,@pmExonParaf,@pmExonCajas,@pmCondEspecial)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomAportante] @pmIdAportnte VARCHAR(16),@pmRazonSocial VARCHAR(200),@pmTipoId CHAR(1),@pmDvNit CHAR(1),@pmIdRepLegal VARCHAR(16),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pme_mail VARCHAR(100),@pmClaseApo CHAR(1),@pmTipoEco CHAR(1),@pmTipoPersna CHAR(1)
,@pmCIIUDane VARCHAR(8),@pmFecIniCndato SMALLDATETIME,@pmTipAccCndato INT,@pmFecFinCndato SMALLDATETIME,@pmCodOperInfo VARCHAR(10),@pmTipoApo CHAR(1),@pmFecMatMerc SMALLDATETIME,@pmExonParaf INT,@pmExonCajas INT,@pmCondEspecial VARCHAR(250),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE NomAportante SET RazonSocial=@pmRazonSocial,TipoId=@pmTipoId,DvNit=@pmDvNit,IdRepLegal=@pmIdRepLegal,Direccion=@pmDireccion,IdLocal=@pmIdLocal,Telefono=@pmTelefono,Fax=@pmFax,e_mail=@pme_mail,ClaseApo=@pmClaseApo,TipoEco=@pmTipoEco,TipoPersna=@pmTipoPersna,CIIUDane=@pmCIIUDane,FecIniCndato=@pmFecIniCndato,TipAccCndato=@pmTipAccCndato
,FecFinCndato=@pmFecFinCndato,CodOperInfo=@pmCodOperInfo,FecUpdate=@pmFecUpdate,TipoApo=@pmTipoApo,FecMatMerc=@pmFecMatMerc,ExonParaf=@pmExonParaf,ExonCajas=@pmExonCajas,CondEspecial=@pmCondEspecial
 WHERE IdAportnte=@pmIdAportnte
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAportante] @pmIdAportnte VARCHAR(16)
 AS 
 SELECT IdAportnte,RazonSocial,TipoId,DvNit,IdRepLegal,Direccion,IdLocal,Telefono,Fax,e_mail,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato
,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo,FecMatMerc,ExonParaf,ExonCajas,CondEspecial,FecAdd,FecUpdate,IdUsuario
 FROM NomAportante
 WHERE IdAportnte LIKE ISNULL(@pmIdAportnte,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomina_Sel]  @pmIdEmpleado  VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep
,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante)
 SELECT  IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END,@pmNewCedula,FecInicial,FecFinal,Cantidad,VrDevengado
,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante
 FROM Trn_Nomina 
 WHERE IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmEsRetiro BIT,@pmNLiqRet INT
,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmVrDevengado,@pmVrDeducido,@pmNContrato,@pmIdDep,@pmIdInstala,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrATransMes,@pmpVehiculo,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado
,@pmEsRetiro,@pmNLiqRet,@pmTipoLiq,@pmCantNoRem,@pmTimeSys,@pmIdUsuario,@pmCdTipoCot,@pmSubTipo_Cot,@pmSal_Integral,@pmTipoIdent,@pmNotasAportante)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmEsRetiro BIT,@pmNLiqRet INT,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Nomina SET IdEmpleado=@pmIdEmpleado,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,Cantidad=@pmCantidad,VrDevengado=@pmVrDevengado,VrDeducido=@pmVrDeducido,NContrato=@pmNContrato,IdDep=@pmIdDep,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdNom=@pmIdNom
,VrSalario=@pmVrSalario,VrATransMes=@pmVrATransMes,pVehiculo=@pmpVehiculo,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EsRetiro=@pmEsRetiro,FecUpdate=@pmFecUpdate,NLiqRet=@pmNLiqRet,TipoLiq=@pmTipoLiq,CantNoRem=@pmCantNoRem
,CdTipoCot=@pmCdTipoCot,SubTipo_Cot=@pmSubTipo_Cot,Sal_Integral=@pmSal_Integral,TipoIdent=@pmTipoIdent,NotasAportante=@pmNotasAportante
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)
 AS 
 SELECT IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom
,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,TimeSys,FecUpdate,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante
 FROM Trn_Nomina
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%')
 ORDER BY KeyRegistro
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdEntrega_Cr] @pmTipDoc VARCHAR(3),@pmNumEntregaIni INT,@pmNumEntregaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT E.TipDoc,TipoDoc,E.NumEntrega,E.IdCia,Compania,E.Fecha,E.FecEntrega,E.TipOrden,E.NumOrden,E.IdCiaOrden,E.CostoTotal,E.Descuento,E.CantTotal
,E.TipoEntrega,E.NitTercero,RazonSocial,E.NomContacto,E.TelContacto,E.EdoOrden AS EdoEntrega
,O.Fecha AS FecOrden,O.TipCons,O.NumCons,O.IdCiaCons,O.Modalidad,O.IdTipProc,TP.TipoProceso,TP.IndOrden,O.IdTaller,TL.NomTaller,TL.Direccion,TL.IdLocal,LC.Localidad,TL.Telefono,TL.TelCelular,TL.TipoTaller 
,O.ConPago,O.ProcFinal,O.Observacion AS ObservOrden,O.EdoOrden
,E.Anulado,E.FecDev,E.NumDev,E.Observacion,E.CodConcepto,E.TipCom,E.Comprobante,E.IdCiaCom,E.IdEstado,Estado,E.OrigenAdd,E.TimeSys AS FecCrea,E.FecUpdate AS FecActualiza,E.IdCiaCrea,E.IdUsuario,Usuario
--detalles
,D.Item,D.ItemOrden,D.CantOrden,D.Cantidad,D.Defectuoso,D.Rechazado,D.CostoUnit,D.VrDcto,D.TipPed,D.Pedido,D.IdCiaPed,D.ItemPed,D.IdProducto,P.DescripProd,D.Descripcion
,D.CdTipProc AS DetCdProc,PS.TipoProceso AS DetProceso,D.Tallas,D.ProcFinal AS DetProcFinal,D.Observacion AS MotivoRechz
,P.CodBarras,P.Referencia,P.TipoRef,G.IdLinea AS CdLinea,Linea,S.IdGrupo AS CdGrupo,Grupo,P.IdSubgrupo AS CdSubgpo,Subgrupo
,P.IdMarca,Marca,P.IdUnd,UD.Unidad,P.Color,P.Tamano,P.Tallaje,P.Seriales,P.Lotes,P.Combo,P.Inactivo AS ProdInactivo,P.DescripLong
FROM Trn_ProdEntrega AS E INNER JOIN Trn_ProdEntregaDet AS D ON E.TipDoc=D.TipDoc AND E.NumEntrega=D.NumEntrega AND E.IdCia=D.IdCia
INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON E.TipDoc=TD.IdDoc
INNER JOIN Trn_ProdOrden AS O ON E.TipOrden=O.TipDoc AND E.NumOrden=O.NumOrden AND E.IdCiaOrden=O.IdCia
INNER JOIN TiposProceso AS TP ON O.IdTipProc=TP.IdTipProc
INNER JOIN Talleres AS TL ON O.IdTaller=TL.IdTaller INNER JOIN Localidades AS LC ON TL.IdLocal=LC.IdLocal
INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea
INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca INNER JOIN UndMed AS UD ON P.IdUnd=UD.IdUnd
LEFT JOIN TiposProceso AS PS ON D.CdTipProc=PS.IdTipProc 
LEFT JOIN Terceros AS T ON E.NitTercero=T.IdTercero
WHERE E.TipDoc=@pmTipDoc AND E.NumEntrega BETWEEN @pmNumEntregaIni AND @pmNumEntregaFin AND E.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpProdEntrega] @pmTipDoc VARCHAR(3),@pmNumEntrega INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipOrden VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrden CHAR(2),@pmFecEntrega SMALLDATETIME,@pmCostoTotal MONEY,@pmDescuento MONEY,@pmCantTotal DECIMAL(14,4),@pmTipoEntrega VARCHAR(10),@pmNitTercero VARCHAR(16)
,@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmEdoOrden INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmCodConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmNumDev INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_ProdEntrega SET Fecha=@pmFecha,TipOrden=@pmTipOrden,NumOrden=@pmNumOrden,IdCiaOrden=@pmIdCiaOrden,FecEntrega=@pmFecEntrega,CostoTotal=@pmCostoTotal,Descuento=@pmDescuento,CantTotal=@pmCantTotal,TipoEntrega=@pmTipoEntrega,NitTercero=@pmNitTercero,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,EdoOrden=@pmEdoOrden
,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CodConcepto=@pmCodConcepto,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,NumDev=@pmNumDev,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumEntrega=@pmNumEntrega AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdEntrega] @pmTipDoc VARCHAR(3),@pmNumEntrega INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumEntrega,IdCia,Fecha,TipOrden,NumOrden,IdCiaOrden,FecEntrega,CostoTotal,Descuento,CantTotal,TipoEntrega,NitTercero,NomContacto,TelContacto,EdoOrden,Anulado,FecDev,Observacion,IdEstado
,CodConcepto,TipCom,Comprobante,IdCiaCom,NumDev,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_ProdEntrega
 WHERE TipDoc=@pmTipDoc AND NumEntrega=@pmNumEntrega AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsProdEntrega] @pmTipDoc VARCHAR(3),@pmNumEntrega INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipOrden VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrden CHAR(2),@pmFecEntrega SMALLDATETIME,@pmCostoTotal MONEY,@pmDescuento MONEY,@pmCantTotal DECIMAL(14,4),@pmTipoEntrega VARCHAR(10),@pmNitTercero VARCHAR(16)
,@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmEdoOrden INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmCodConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmNumDev INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_ProdEntrega (TipDoc,NumEntrega,IdCia,Fecha,TipOrden,NumOrden,IdCiaOrden,FecEntrega,CostoTotal,Descuento,CantTotal,TipoEntrega,NitTercero,NomContacto,TelContacto,EdoOrden,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,CodConcepto,TipCom,Comprobante,IdCiaCom,NumDev)
 VALUES (@pmTipDoc,@pmNumEntrega,@pmIdCia,@pmFecha,@pmTipOrden,@pmNumOrden,@pmIdCiaOrden,@pmFecEntrega,@pmCostoTotal,@pmDescuento,@pmCantTotal,@pmTipoEntrega,@pmNitTercero,@pmNomContacto,@pmTelContacto,@pmEdoOrden,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCodConcepto,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmNumDev)
GO