public List<Capacitaciones> ObtenerNoProveedor(string parNoproveedor) { try { ManagerOracle baseOracle = new ManagerOracle("JDE"); OracleCommand comando = new OracleCommand(); //AuditoriaIntelectual entidadauditoria; //nombre de la entidad DataTable datos; comando.CommandText = " SELECT * " + " FROM PRODDTA.F0101 " + " where ABAT1='V' and ABAN8 like :parNoproveedor "; comando.Parameters.Clear(); comando.Parameters.Add(":parNoproveedor", OracleType.VarChar).Value = parNoproveedor; datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<Capacitaciones> listanProveedor = new List<Capacitaciones>(); foreach (DataRow fila in datos.Rows) { Capacitaciones proveedores = new Capacitaciones(); proveedores.idproveedor = fila["ABAN8"].ToString(); proveedores.nomproveedor = fila["ABALPH"].ToString(); listanProveedor.Add(proveedores); } this.log = listanProveedor.Count.ToString(); return listanProveedor; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } } catch (Exception ex) { this.log = ex.Message; return null; } }
public List<Employee> allempleados() { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); string consulta = " select a.employee_number as id_emp, a.full_name as full_name, a.person_id, c.name, b.organization_id, " + " b.supervisor_id as id_jefe_inmediato " + " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id , HR_ALL_ORGANIZATION_UNITS c " + " where b.organization_id =c.organization_id " + " and TO_CHAR(a.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' and TO_CHAR(b.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " and a.employee_number<>' ' and a.PERSON_TYPE_ID<>'1123' " + " order by 2 "; DataSet setDatos = baseOracle.Consultar(consulta); if (setDatos != null) { DataTable datos = setDatos.Tables[0]; if (datos.Rows.Count != 0) { List<Employee> listaEmpleados = new List<Employee>(); foreach (DataRow fila in datos.Rows) { Employee EmpleadoEBS = new Employee(); EmpleadoEBS.Clave = int.Parse(fila["id_emp"].ToString()); EmpleadoEBS.NombreCompleto = fila["full_name"].ToString(); listaEmpleados.Add(EmpleadoEBS); } this.log = listaEmpleados.Count.ToString(); return listaEmpleados; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<GradoAcademico> Obtener() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string sentenciaSQL = "SELECT " + "QUALIFICATION_TYPE_ID, " + "NAME " + "FROM PER_QUALIFICATION_TYPES_TL " + "WHERE LANGUAGE = 'ESA'"; DataSet conjuntoDatos = baseOracle.Consultar(sentenciaSQL); if (conjuntoDatos != null) // Valida que no exista error { DataTable datos = conjuntoDatos.Tables[0]; if (datos.Rows.Count != 0) // Valida que existan registros { List<GradoAcademico> listaGradoAcademico = new List<GradoAcademico>(); foreach (DataRow fila in datos.Rows) { GradoAcademico GradoAcademico = new GradoAcademico(); GradoAcademico.Clave = fila["QUALIFICATION_TYPE_ID"].ToString(); GradoAcademico.Descripcion = fila["NAME"].ToString(); GradoAcademico.Clave = GradoAcademico.Clave.ToUpper(); GradoAcademico.Descripcion = GradoAcademico.Descripcion.ToUpper(); listaGradoAcademico.Add(GradoAcademico); } this.log = listaGradoAcademico.Count.ToString(); return listaGradoAcademico; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Nomina> Obtener() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string consulta = "SELECT " + "FASE.LOOKUP_CODE AS NOMINA_CLAVE, " + "FASE.DESCRIPTION AS NOMINA_DESC " + "FROM FND_LOOKUP_VALUES FASE " + "WHERE 1=1 " + "AND FASE.lookup_type = 'NVL_PAY_NOMINA' " + "AND FASE.LANGUAGE = 'ESA' "; DataSet setDatos = baseOracle.Consultar(consulta); if (setDatos != null) { DataTable datos = setDatos.Tables[0]; if (datos.Rows.Count != 0) { List<Nomina> listaNom = new List<Nomina>(); foreach (DataRow fila in datos.Rows) { Nomina Nom = new Nomina(); Nom.Clave = int.Parse(fila["NOMINA_CLAVE"].ToString()); Nom.Descripcion = fila["NOMINA_DESC"].ToString(); listaNom.Add(Nom); } this.log = listaNom.Count.ToString(); return listaNom; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Departamento> Obtener() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string consulta = "SELECT " + "ORGANIZATION_ID AS DEPA_CLAVE, " + "NAME AS DEPA_DESC " + "FROM HR_ALL_ORGANIZATION_UNITS DEPARTAMENTOS " + "WHERE BUSINESS_GROUP_ID = '81' " + "AND LOCATION_ID in ('146','147','142','145') " + "AND ORGANIZATION_ID not in ('147','148','149','150','151','346') "; DataSet setDatos = baseOracle.Consultar(consulta); if (setDatos != null) { DataTable datos = setDatos.Tables[0]; if (datos.Rows.Count != 0) { List<Departamento> listaDeptos = new List<Departamento>(); foreach (DataRow fila in datos.Rows) { Departamento departamento = new Departamento(); departamento.Clave = int.Parse(fila["DEPA_CLAVE"].ToString()); departamento.Descripcion = fila["DEPA_DESC"].ToString(); listaDeptos.Add(departamento); } this.log = listaDeptos.Count.ToString(); return listaDeptos; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public TablaUdcs(EntidadUdc parUdc) { this.managerOracle = new ManagerOracle("JDE"); this.comando = new OracleCommand(); this.log = string.Empty; if (parUdc != null) { this.udc = parUdc; } else { this.udc = new EntidadUdc(); } }
public List<Tipo> Obtener() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string consulta = "SELECT " + "PERSON_TYPE_ID AS TIPO_CLAVE, " + "USER_PERSON_TYPE AS TIPO_DESC " + "FROM PER_PERSON_TYPES TIPOS " + "WHERE BUSINESS_GROUP_ID = '81' "; DataSet setDatos = baseOracle.Consultar(consulta); if (setDatos != null) { DataTable datos = setDatos.Tables[0]; if (datos.Rows.Count != 0) { List<Tipo> listaTipos = new List<Tipo>(); foreach (DataRow fila in datos.Rows) { Tipo tipo = new Tipo(); tipo.Clave = Convert.ToInt32(fila["TIPO_CLAVE"].ToString()); tipo.Descripcion = fila["TIPO_DESC"].ToString(); listaTipos.Add(tipo); } this.log = listaTipos.Count.ToString(); return listaTipos; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Especialidades> Obtener() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string sentenciaSQL = "SELECT DISTINCT " + "UPPER(TITLE) AS QUA_ESPECIALIDAD " + "FROM PER_QUALIFICATIONS " + "WHERE not translate(title,'T_0123456789 +-.,;:*!¡=/\','T') is null " + "ORDER BY QUA_ESPECIALIDAD "; DataSet conjuntoDatos = baseOracle.Consultar(sentenciaSQL); if (conjuntoDatos != null) // Valida que no exista error { DataTable datos = conjuntoDatos.Tables[0]; if (datos.Rows.Count != 0) // Valida que existan registros { List<Especialidades> listaeEspecialidades = new List<Especialidades>(); foreach (DataRow fila in datos.Rows) { Especialidades especialidades = new Especialidades(); especialidades.Clave = fila["QUA_ESPECIALIDAD"].ToString(); especialidades.Descripcion = fila["QUA_ESPECIALIDAD"].ToString(); listaeEspecialidades.Add(especialidades); } this.log = listaeEspecialidades.Count.ToString(); return listaeEspecialidades; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Categorias> ObtenerCategoria() { ManagerOracle baseOracle = new ManagerOracle("EBS"); string sentenciaSQL = " select RD.NAME as GRADODES, RD.GRADE_ID as IDGRADO from PER_ALL_ASSIGNMENTS_F PA, PER_GRADES RD " + " where RD.GRADE_ID = PA.GRADE_ID " + " group by RD.NAME, RD.GRADE_ID " + " order by 1 "; DataSet conjuntoDatos = baseOracle.Consultar(sentenciaSQL); if (conjuntoDatos != null) // Valida que no exista error { DataTable datos = conjuntoDatos.Tables[0]; if (datos.Rows.Count != 0) // Valida que existan registros { List<Categorias> listaeCategorias = new List<Categorias>(); foreach (DataRow fila in datos.Rows) { Categorias categorias = new Categorias(); categorias.Clave = fila["IDGRADO"].ToString(); categorias.Descripcion = fila["GRADODES"].ToString(); listaeCategorias.Add(categorias); } this.log = listaeCategorias.Count.ToString(); return listaeCategorias; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Capacitaciones> ObtenerProveedor() { ManagerOracle baseOracle = new ManagerOracle("JDE"); string sentenciaSQL = " SELECT * " + " FROM PRODDTA.F0101 " + " where ABAT1='V' or ABAT1='E' "; DataSet conjuntoDatos = baseOracle.Consultar(sentenciaSQL); if (conjuntoDatos != null) // Valida que no exista error { DataTable datos = conjuntoDatos.Tables[0]; if (datos.Rows.Count != 0) // Valida que existan registros { List<Capacitaciones> listaProveedor = new List<Capacitaciones>(); foreach (DataRow fila in datos.Rows) { Capacitaciones proveedores = new Capacitaciones(); proveedores.nomproveedor = fila["ABALPH"].ToString(); proveedores.idproveedor = fila["ABAN8"].ToString(); listaProveedor.Add(proveedores); } this.log = listaProveedor.Count.ToString(); return listaProveedor; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
/*===================================================*\ METODOS PUBLICOS \*===================================================*/ public List<EntidadCompania> Obtener() { ManagerOracle baseOracle = new ManagerOracle("JDE"); OracleCommand comando = new OracleCommand( "SELECT " + " CCCO AS COMP_CODE, " + " CCNAME AS COMP_DESC, " + " CCAN8 AS COMP_BOOK_CODE, " + " CCAN8 || ' - ' || ABALPH || ALADD1 AS BOOK_DESC " + "FROM PRODDTA.F0010 " + "LEFT OUTER JOIN PRODDTA.F0101 ON ABAN8 = CCAN8 " + "LEFT OUTER JOIN PRODDTA.F0116 ON CCAN8 = ALAN8 AND ALEFTB = 0 " + "WHERE 1=1 " + "and CCAN8 not in (0,5) " + "order by CCCO " ); return this.Consultar(comando); }
public DataTable BuscarEmpleosAnterioresAI(AuditoriaIntelectual parAuditoriaIntelectual) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " PEM.EMPLOYER_NAME AS PEM_EMPLEADOR, " + " PEM.EMPLOYER_COUNTRY AS PEM_PAIS, " + " PEM.EMPLOYER_ADDRESS AS PEM_DIRECCION, " + " PEM.DESCRIPTION AS PEM_PUESTO, " + " PEM.START_DATE AS PEM_FECHA_DESDE, " + " PEM.END_DATE AS PEM_FECHA_HASTA " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + " WHERE nvl(EMPLOYEE_NUMBER,'0') like :parNumeroEmpleado " + " ORDER BY PEM.START_DATE DESC"; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parAuditoriaIntelectual.NumeroEmpleado; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } }
public List<AuditoriaIntelectual> BuscarEmpleado() { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); //AuditoriaIntelectual entidadauditoria; //nombre de la entidad DataTable datos; comando.CommandText = " SELECT EMPLOYEE_NUMBER, full_name " + " FROM PER_ALL_PEOPLE_F PERS " + " WHERE 1=1 AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') and " + " nvl(EMPLOYEE_NUMBER,'0') like :parNumeroEmpleado "; comando.Parameters.Clear(); if (eAuditoria.NumeroEmpleado == string.Empty) { comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = "%"; } else { comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = eAuditoria.NumeroEmpleado.ToString(); } datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<AuditoriaIntelectual> listaEmpleadoAud = new List<AuditoriaIntelectual>(); foreach (DataRow fila in datos.Rows) { AuditoriaIntelectual datosauditoria = new AuditoriaIntelectual(); datosauditoria.NumeroEmpleado = fila["EMPLOYEE_NUMBER"].ToString(); datosauditoria.Nombre = fila["full_name"].ToString(); listaEmpleadoAud.Add(datosauditoria); } this.log = listaEmpleadoAud.Count.ToString(); return listaEmpleadoAud; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } } catch (Exception ex) { this.log = ex.Message; return null; } }
public DataTable BuscarCompetenciasAI(AuditoriaIntelectual parAuditoriaIntelectual) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " SELECT " + " COMP.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER, " + " SUP.FULL_NAME, " + " COMP.COMPETENCE_ELEMENT_ID, " + " COMP.OBJECT_VERSION_NUMBER, " + " COMP.TYPE, " + " COMP.COMPETENCE_ID, " + " COMP.EFFECTIVE_DATE_FROM AS COMP_FECHA_DESDE, " + " COMP.EFFECTIVE_DATE_TO AS COMP_FECHA_HASTA, " + " COMP.STATUS, " + " CCOMP.OBJECT_VERSION_NUMBER, " + " CCOMP.NAME AS COMP_APTITUD, " + " CCOMP.DESCRIPTION, " + " CCOMP.BEHAVIOURAL_INDICATOR, " + " CCOMP.CERTIFICATION_REQUIRED, " + " CCOMP.MIN_LEVEL AS COMP_NIVEL_MIN, " + " CCOMP.MAX_LEVEL AS COMP_NIVEL_MAX, " + " CCOMP.RATING_SCALE_ID, " + " RATL1.NAME AS COMP_NIVEL_DESCRIPCION " + "FROM PER_COMPETENCE_ELEMENTS COMP " + " LEFT OUTER JOIN PER_COMPETENCES CCOMP ON COMP.COMPETENCE_ID = CCOMP.COMPETENCE_ID " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON COMP.PERSON_ID = SUP.PERSON_ID " + " LEFT OUTER JOIN PER_RATING_LEVELS RATL1 ON COMP.PROFICIENCY_LEVEL_ID = RATL1.RATING_LEVEL_ID " + "WHERE COMP.PERSON_ID IS NOT NULL " + "AND EMPLOYEE_NUMBER IS NOT NULL " + "AND nvl(EMPLOYEE_NUMBER,'0') like :parNumeroEmpleado " + "ORDER BY COMP.PERSON_ID"; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parAuditoriaIntelectual.NumeroEmpleado; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } }
public DataTable BuscarEquipoAI(AuditoriaIntelectual parAuditoriaIntelectual) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + " AN.PERSON_ANALYSIS_ID, " + " AN.BUSINESS_GROUP_ID, " + " AN.ANALYSIS_CRITERIA_ID, " + " AN.PERSON_ID, " + " AN.DATE_FROM AS AN_FECHA_DESDE, " + " AN.DATE_TO AS AN_FECHA_HASTA, " + " AN.ID_FLEX_NUM, " + " AN.OBJECT_VERSION_NUMBER, " + " AC.SUMMARY_FLAG, " + " AC.ENABLED_FLAG, " + " AC.SEGMENT1 AS AN_EQUIPO, " + " AC.SEGMENT2 AS AN_PERIODO_ENTREGA, " + " AC.SEGMENT3 AS AN_CANTIDAD, " + " AC.SEGMENT4 AS AN_TALLA, " + " AC.SEGMENT5, " + " AC.SEGMENT6, " + " AC.SEGMENT7, " + " AC.SEGMENT8, " + " AC.SEGMENT9, " + " AC.SEGMENT10, " + " SUP.EMPLOYEE_NUMBER " + "FROM PER_PERSON_ANALYSES AN " + " LEFT OUTER JOIN PER_ANALYSIS_CRITERIA AC ON AC.ANALYSIS_CRITERIA_ID = AN.ANALYSIS_CRITERIA_ID " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON AN.PERSON_ID = SUP.PERSON_ID " + "WHERE nvl(EMPLOYEE_NUMBER,'0') like :parNumeroEmpleado " + "ORDER BY DATE_FROM DESC"; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parAuditoriaIntelectual.NumeroEmpleado; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } }
public List<AuditoriaIntelectual> ObtenerDetalleEmpleosAnteriores(string parNumempleado) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " PEM.EMPLOYER_NAME AS PEM_EMPLEADOR, " + " PEM.EMPLOYER_COUNTRY AS PEM_PAIS, " + " PEM.EMPLOYER_ADDRESS AS PEM_DIRECCION, " + " PEM.DESCRIPTION AS PEM_PUESTO, " + " to_char(PEM.START_DATE, 'DD/MM/YYYY') AS PEM_FECHA_DESDE, " + " to_char(PEM.END_DATE, 'DD/MM/YYYY') AS PEM_FECHA_HASTA " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + " WHERE nvl(EMPLOYEE_NUMBER,'0') like :parNumeroEmpleado " + " ORDER BY PEM.START_DATE DESC "; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parNumempleado; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<AuditoriaIntelectual> listaEmpleosAnt = new List<AuditoriaIntelectual>(); foreach (DataRow fila in datos.Rows) { AuditoriaIntelectual empOldJob = new AuditoriaIntelectual(); empOldJob.NumeroEmpleado = fila["PER_EMPLEADO_NUMERO"].ToString(); empOldJob.EmpleoAnterior = fila["PEM_EMPLEADOR"].ToString(); empOldJob.FechaIniJobAnt= fila["PEM_FECHA_DESDE"].ToString(); empOldJob.FechaFinJobAnt= fila["PEM_FECHA_HASTA"].ToString(); empOldJob.EmpleoAnteriorPuesto = fila["PEM_PUESTO"].ToString(); listaEmpleosAnt.Add(empOldJob); } this.log = listaEmpleosAnt.Count.ToString(); return listaEmpleosAnt; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
//----------------------------------------------------------------------------- // para sacar la direccion que le corresponde public List<AuditoriaIntelectual> ObtenerDireccion(string parPersonID) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " select a.employee_number as id_emp, a.full_name as full_name, a.person_id, c.name, b.organization_id, " + " b.supervisor_id as id_jefe_inmediato " + " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id , HR_ALL_ORGANIZATION_UNITS c " + " where b.organization_id =c.organization_id and b.person_id like :parNumeroPersonID " + " and TO_CHAR(a.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' and TO_CHAR(b.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " and a.employee_number<>' ' and a.PERSON_TYPE_ID<>'1123' "; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroPersonID", OracleType.VarChar).Value = parPersonID; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<AuditoriaIntelectual> listaJefaturas = new List<AuditoriaIntelectual>(); foreach (DataRow fila in datos.Rows) { AuditoriaIntelectual personId = new AuditoriaIntelectual(); personId.NumeroEmpleado = fila["ID_EMP"].ToString(); personId.Nombre = fila["FULL_NAME"].ToString(); personId.Departamento = fila["NAME"].ToString(); personId.CveJefeDirecto = fila["ID_JEFE_INMEDIATO"].ToString(); listaJefaturas.Add(personId); } this.log = listaJefaturas.Count.ToString(); return listaJefaturas; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
//--------------------------------------------PRUEBA------------------------------------------------------------------------------------------------------- public List<Employee> ObtenerEbs20(string parNumeroEmpleado) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + "* " + "FROM " + "( " + " SELECT " + " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO, " + " EMPLEADOS.TITLE AS PERS_TITULO, " + " EMPLEADOS.FIRST_NAME AS PERS_PRIMER_NOMBRE, " + " EMPLEADOS.MIDDLE_NAMES AS PERS_SEGUNDO_NOMBRE, " + " EMPLEADOS.LAST_NAME AS PERS_APELLIDO_PATERNO, " + " EMPLEADOS.PER_INFORMATION1 AS PERS_APELLIDO_MATERNO, " + " EMPLEADOS.FULL_NAME AS PERS_NOMBRE_COMPLETO, " + " EMPLEADOS.SEX AS PERS_GENERO_CLAVE, " + " CASE " + " WHEN EMPLEADOS.SEX = 'M' THEN 'Masculino' " + " WHEN EMPLEADOS.SEX = 'F' THEN 'Femenino' " + " WHEN EMPLEADOS.SEX IS NULL THEN ' ' " + " ELSE 'Sexo desconocido' " + " END PERS_GENERO_DESC, " + " EMPLEADOS.NATIONAL_IDENTIFIER AS PERS_CURP, " + " EMPLEADOS.NATIONALITY AS PERS_NACIONALIDAD_CLAVE, " + " EMPLEADOS.PER_INFORMATION2 AS PERS_RFC, " + " EMPLEADOS.PER_INFORMATION3 AS PERS_NUMERO_IMSS, " + " EMPLEADOS.PER_INFORMATION5 AS PERS_IFE, " + " EMPLEADOS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " EMPLEADOS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " EMPLEADOS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " EMPLEADOS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " EMPLEADOS.EFFECTIVE_START_DATE AS PERS_FECHA_EFECTIVE_DESDE, " + " EMPLEADOS.EFFECTIVE_END_DATE AS PERS_FECHA_EFECTIVE_HASTA, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_EMAIL, " + " EMPLEADOS.MARITAL_STATUS AS PERS_ESTADO_CIVIL, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_CORREO_ELECTRONICO, " + " CASE " + " WHEN EMPLEADOS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN EMPLEADOS.MARITAL_STATUS = 'M' THEN 'Cazad@' " + " WHEN EMPLEADOS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " EMPLEADOS.START_DATE as PERS_FECHA_CONTRATACION " + " FROM PER_ALL_PEOPLE_F EMPLEADOS " + " INNER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID " + ") EMP " + "left outer join ( " + " SELECT " + " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + " GRADOS.NAME AS ASIG_GRADO_DESC, " + " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + " UBICACIONES.location_code AS ASIG_UBICACION_DESC, " + " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + " ASIGNACIONES.EMPLOYMENT_CATEGORY AS ASIG_CATEGORIA_CODIGO, " + " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + " PAYB.name as ASIG_SALARIO_BASE_DESC, " + " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION " + " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + " INNER JOIN ( " + " SELECT " + " ASSIGNMENT_NUMBER, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " max(EFFECTIVE_END_DATE) as asig_date " + " FROM PER_ALL_ASSIGNMENTS_F " + " where TO_CHAR(EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " GROUP BY ASSIGNMENT_NUMBER " + " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + "LEFT OUTER JOIN ( " + " SELECT " + " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE, " + " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + " PROY.LOOKUP_CODE AS GRUP_PROYECTO_CODE_JDE, " + " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + " FASE.LOOKUP_CODE AS GRUP_FASE_CODE_JDE, " + " grupos.segment7 as GRUP_PUESTO_JDE, " + " IMMS.LOOKUP_CODE AS GRUP_PUESTO_CODE_JDE " + " FROM PAY_PEOPLE_GROUPS GRUPOS " + " LEFT OUTER JOIN FND_LOOKUP_VALUES PROY ON PROY.MEANING = GRUPOS.SEGMENT2 " + " AND PROY.lookup_type = 'NVL_PAY_PROYECTO' " + " AND PROY.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES FASE ON FASE.MEANING = GRUPOS.SEGMENT3 " + " AND FASE.lookup_type = 'NVL_PAY_FASE_V2' " + " AND FASE.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES IMMS ON IMMS.MEANING = GRUPOS.segment7 " + " AND IMMS.lookup_type = 'NVL_PUESTO_IMSS' " + " AND IMMS.LANGUAGE = 'ESA' " + " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + "left outer join ( " + " select " + " PPPM.ASSIGNMENT_ID AS METODO_ASIGNACION_ID, " + " POPN.ORG_PAYMENT_METHOD_NAME AS METODO_NOMBRE, " + " PTI.PAYMENT_TYPE_NAME AS METODO_TIPO, " + " PPPM.PRIORITY AS METODO_PRIORIDAD, " + " PPPM.EFFECTIVE_START_DATE AS METODO_FECHA_EFEC_DESDE, " + " PPPM.EFFECTIVE_END_DATE AS METODO_FECHA_EFEC_HASTA, " + " PPPM.AMOUNT AS METODO_IMPORTE_SALDO, " + " PPPM.PERCENTAGE AS METODO_PORCENTAJE, " + " PPPM.ATTRIBUTE1 AS METODO_PAGO, " + " PEA.SEGMENT2 AS METODO_SUCURSAL, " + " PEA.SEGMENT3 AS METODO_CUENTA, " + " PEA.SEGMENT4 AS METODO_TIPO_CUENTA_ID, " + " PEA.SEGMENT5 AS METODO_CLABE " + " from PAY_PERSONAL_PAYMENT_METHODS_F PPPM " + " LEFT OUTER JOIN PAY_EXTERNAL_ACCOUNTS PEA " + " ON PEA.EXTERNAL_ACCOUNT_ID = PPPM.EXTERNAL_ACCOUNT_ID " + " LEFT OUTER JOIN PAY_ORG_PAYMENT_METHODS_F POPN " + " ON POPN.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID " + " LEFT OUTER JOIN PAY_PAYMENT_TYPES PTI " + " ON PTI.PAYMENT_TYPE_ID = POPN.PAYMENT_TYPE_ID " + " WHERE TO_CHAR(PPPM.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) MTP ON MTP.METODO_ASIGNACION_ID = asg.ASIG_CLAVE " + "WHERE NVL(PERS_EMPLEADO_NUMERO,'VACIO') LIKE :parNumeroEmpleado "; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parNumeroEmpleado; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<Employee> listaEmpleados = new List<Employee>(); foreach (DataRow fila in datos.Rows) { Employee empleado = new Employee(); empleado.Numero = fila["PERS_EMPLEADO_NUMERO"].ToString(); empleado.Tipo = fila["PERS_TIPO_DESC"].ToString(); empleado.PrimerNombre = fila["PERS_PRIMER_NOMBRE"].ToString(); empleado.SegundoNombre = fila["PERS_SEGUNDO_NOMBRE"].ToString(); empleado.ApellidoPaterno = fila["PERS_APELLIDO_PATERNO"].ToString(); empleado.ApellidoMaterno = fila["PERS_APELLIDO_MATERNO"].ToString(); empleado.NombreCompleto = fila["PERS_NOMBRE_COMPLETO"].ToString(); empleado.Genero = fila["PERS_GENERO_DESC"].ToString(); empleado.Curp = fila["PERS_CURP"].ToString(); empleado.Rfc = fila["PERS_RFC"].ToString(); empleado.Imss = fila["PERS_NUMERO_IMSS"].ToString(); empleado.Ife = fila["PERS_IFE"].ToString(); empleado.CorreoElectronico = fila["PERS_CORREO_ELECTRONICO"].ToString(); empleado.FechaContratacion = fila["PERS_FECHA_CONTRATACION"].ToString(); empleado.FechaInicio = fila["PERS_FECHA_EFECTIVE_DESDE"].ToString(); empleado.FechaFinal = fila["PERS_FECHA_EFECTIVE_HASTA"].ToString(); empleado.FechaNacimiento = fila["PERS_FECHA_NACIMIENTO"].ToString(); empleado.CiudadNacimiento = fila["PERS_CIUDAD_NACIMIENTO"].ToString(); empleado.EstadoNacimiento = fila["PERS_ESTADO_NACIMIENTO"].ToString(); empleado.PaisNacimiento = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); empleado.Puesto = fila["ASIG_PUESTO_DESC"].ToString(); empleado.Grado = fila["ASIG_GRADO_DESC"].ToString(); empleado.Ubicacion = fila["ASIG_UBICACION_DESC"].ToString(); empleado.CompañiaJde = fila["GRUP_COMPANIA_JDE"].ToString(); empleado.ProyectoJde = fila["GRUP_PROYECTO_JDE"].ToString(); empleado.FaseJde = fila["GRUP_FASE_JDE"].ToString(); listaEmpleados.Add(empleado); } this.log = listaEmpleados.Count.ToString(); return listaEmpleados; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------ //--------------------------Sacar Depto x personaID ---------------------------------------------------------------------------- public DataTable IdperxDepto(string parIdEmpleado) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " select a.employee_number as employee_number, a.full_name as full_name, a.person_id, b.organization_id, c.name as NombreDepto from HR_ALL_ORGANIZATION_UNITS c, PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id " + " where b.organization_id =c.organization_id and a.employee_number=a.employee_number=:parIdEmp and a.employee_number<>' ' and " + " a.employee_number not in (select employee_number from PER_ALL_PEOPLE_F where person_type_id='1120' and effective_end_date<> TO_DATE ( '12/31/4712' , 'MM / DD / YY' )) " + " group by a.employee_number, a.full_name, a.person_id, c.name, b.organization_id "; comando.Parameters.Clear(); comando.Parameters.Add(":parIdEmp", OracleType.VarChar).Value = parIdEmpleado; DataTable datos = baseOracle.Consultar(comando); return datos; }
//-------------------------------PRUEBA.------------------------------------------------- public List<AuditoriaIntelectual> BuscarEmpleadosAuditoria2(string parNumempleado, string parGradoAcademico, string parArea, string parEspecialidad, string parPuesto, string parUn, string parCategoriaId, string parGenero, string parTipoemp) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); //AuditoriaIntelectual entidadauditoria; //nombre de la entidad comando.CommandText = " SELECT " + " PERS.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " PERS.PERSON_ID AS PER_PERSON_ID, " + " ASG.SEGMENT4 AS ASG_AREA, " + " ASG.SEGMENT2 AS ASG_DIRECCION, " + " ASG.SEGMENT3 AS ASG_DEPTO, " + " ' ' AS PER_UN, " + " PERS.FULL_NAME AS ASG_EMPLEADO, " + " ASG.NAME AS ASG_PUESTO, " + " to_char(PERS.EFFECTIVE_START_DATE, 'DD/MM/YYYY') AS ASG_EMPLEADO_FINGRESO, " + //antes tenia ASG. " ASG.PROPOSED_SALARY_N AS ASG_EMPLEADO_SALARIO, " + " ASG.ATTRIBUTE2 AS ASG_EMPLEADO_SALARIOTOTAL, " + " QUA.QUA_GRADO_ACADEMICO, " + " QUA.QUA_ULTIMO_ESTUDIO, " + " QUA.QUA_ESPECIALIDAD, " + " PERS.ATTRIBUTE1 AS PER_GPO_SANGUINEO, " + " PERS.ATTRIBUTE2 AS PER_ALERGIAS, " + " PERS.ATTRIBUTE3 AS PER_LICENCIA_INTERNA, " + " PERS.ATTRIBUTE4 AS PER_LI_FECHA_VENC, " + " PERS.ATTRIBUTE5 AS PER_LI_CERTIFICACION, " + " PERS.ATTRIBUTE6 AS PER_LICENCIA_EXTERNA, " + " PERS.ATTRIBUTE7 AS PER_LE_FECHA_VENC, " + " PERS.NATIONAL_IDENTIFIER AS PER_CURP, " + " PERS.PER_INFORMATION2 AS PER_RFC, " + " CASE " + " WHEN PERS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN PERS.MARITAL_STATUS = 'M' THEN 'Casad@' " + " WHEN PERS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " PERS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " PERS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " PERS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " PERS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " ASG.GRADE_ID as CLAVE_CATEGORIA, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " ASG.GRADODES as CATEGORIA, " + " ASG.LOCATION_CODE as PER_EMPLEADO_UBICACION, " + " JOBOLD.PEM_EMPLEADOR as JOBS_ANTERIOR, " + " ASG.ASIG_ORGANIZACION_DESC as DEPTO, " + " ASIG_JEFE_DIRECTO_CLAVE as CVEJEFEDIRECTO, " + " ASIG_JEFE_DIRECTO_DESC as NOMBREJEFEDIRECTO " + " FROM PER_ALL_PEOPLE_F PERS " + " INNER JOIN ( " + " SELECT " + " PERALL.PERSON_ID, " + " MAX(PERALL.OBJECT_VERSION_NUMBER) AS VER, " + " MAX(PERALL.EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F PERALL " + " GROUP BY PERALL.PERSON_ID " + " ) ACTT ON ACTT.PERSON_ID = PERS.PERSON_ID AND ACTT.VER = PERS.OBJECT_VERSION_NUMBER AND ACTT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + " " + " LEFT OUTER JOIN ( " + " SELECT " + " PA.GRADE_ID, " + " PA.EFFECTIVE_START_DATE, " + " PA.PERSON_ID, " + " PA.SUPERVISOR_ID AS ASIG_JEFE_DIRECTO_CLAVE, " + " GPO.SEGMENT4, " + " GPO.SEGMENT2, " + " GPO.SEGMENT3, " + " GPO.PEOPLE_GROUP_ID, " + " PUESTOS.NAME, " + " SAL.PROPOSED_SALARY_N, " + " SAL.ATTRIBUTE2, " + " RD.NAME as GRADODES, " + " UBICACIONES.LOCATION_CODE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " JEFATURAS.FULL_NAME AS ASIG_JEFE_DIRECTO_DESC " + " FROM PER_ALL_ASSIGNMENTS_F PA " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F JEFATURAS ON JEFATURAS.PERSON_ID = PA.SUPERVISOR_ID AND TO_CHAR(JEFATURAS.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = PA.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = PA.LOCATION_ID " + " LEFT OUTER JOIN PER_GRADES RD ON RD.GRADE_ID = PA.GRADE_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GPO ON GPO.PEOPLE_GROUP_ID = PA.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = PA.POSITION_ID " + " LEFT OUTER JOIN PER_PAY_PROPOSALS SAL ON SAL.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND TO_CHAR(SAL.DATE_TO,'YYYY-MM-DD' ) = '4712-12-31' " + " WHERE PA.EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " ) ASG ON ASG.PERSON_ID = PERS.PERSON_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " PER_QUALIFICATIONS.QUALIFICATION_ID, " + " PER_QUALIFICATIONS.PERSON_ID, " + " PER_QUALIFICATIONS.TITLE AS QUA_ESPECIALIDAD, " + " PER_QUALIFICATIONS.GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + " PER_QUALIFICATIONS.AWARDING_BODY, " + " PER_QUALIFICATIONS.COMMENTS AS QUA_COMENTARIOS, " + " UPPER(TIPOS2.NAME) AS QUA_GRADO_ACADEMICO, " + " HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + " FROM PER_QUALIFICATIONS " + " INNER JOIN ( " + " SELECT QUA.PERSON_ID ,max(QUA.object_version_number) as object_version_number " + " FROM PER_QUALIFICATIONS QUA " + " GROUP BY QUA.PERSON_ID " + " ) valid " + " on valid.object_version_number = PER_QUALIFICATIONS.object_version_number " + " LEFT OUTER JOIN ( SELECT * " + " FROM PER_QUALIFICATION_TYPES_TL " + " WHERE LANGUAGE = 'ESA' " + " ) TIPOS2 ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS2.QUALIFICATION_TYPE_ID " + " WHERE valid.PERSON_ID = PER_QUALIFICATIONS.PERSON_ID " + " ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + //" LEFT OUTER JOIN ( " + //" SELECT " + //" PER_QUALIFICATIONS.QUALIFICATION_ID, " + //" PER_QUALIFICATIONS.PERSON_ID, " + //" PER_QUALIFICATIONS.TITLE AS QUA_ESPECIALIDAD, " + //" PER_QUALIFICATIONS.GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + //" PER_QUALIFICATIONS.AWARDING_BODY, " + //" PER_QUALIFICATIONS.COMMENTS AS QUA_COMENTARIOS, " + //" UPPER(TIPOS2.NAME) AS QUA_GRADO_ACADEMICO, " + //" HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + //" FROM PER_QUALIFICATIONS " + //" LEFT OUTER JOIN ( SELECT QUA.PERSON_ID ,max(QUA.object_version_number) as object_version_number " + //" FROM PER_ALL_PEOPLE_F PER, PER_QUALIFICATIONS QUA " + //" WHERE PER.employee_number like :parNumempleado and PER.PERSON_ID = QUA.PERSON_ID " + //" GROUP BY QUA.PERSON_ID " + //" ) TIPOS ON PER_QUALIFICATIONS.object_version_number = TIPOS.object_version_number " + //" LEFT OUTER JOIN ( SELECT * " + //" FROM PER_QUALIFICATION_TYPES_TL " + //" WHERE LANGUAGE = 'ESA' " + //" ) TIPOS2 ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS2.QUALIFICATION_TYPE_ID " + //" WHERE PER_QUALIFICATIONS.object_version_number = TIPOS.object_version_number " + //" ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + " " + " LEFT OUTER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = PERS.PERSON_ID AND ACT.VER = PERS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " count(PEM.EMPLOYER_NAME) AS PEM_EMPLEADOR " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID AND " + " SUP.EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " group by SUP.EMPLOYEE_NUMBER, PEM.PERSON_ID " + " ) JOBOLD ON JOBOLD.PERSON_ID = PERS.PERSON_ID " + " " + " WHERE 1=1 " + " AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like :parNumempleado " + " AND NVL(TRIM(QUA_GRADO_ACADEMICO),'VACIO') like :parGradoAcademico " + " AND NVL(TRIM(SEGMENT4),'VACIO') like :parArea " + " AND NVL(TRIM(QUA_ESPECIALIDAD),'VACIO') like :parEspecialidad " + " AND NVL(TRIM(ASG.NAME),'VACIO') like :parPuesto " + " AND NVL(TRIM(ASG.SEGMENT3),'VACIO') like :parUn " + " AND NVL(TRIM(ASG.GRADE_ID),'VACIO') like :parCategoriaId " + " AND NVL(PERS.SEX,'VACIO') LIKE :parGenero " + " AND NVL(TRIM(TIPOS.PERSON_TYPE_ID),'VACIO') like :parTipoemp " + " ORDER BY PERS.PERSON_ID "; // " and nvl(PERS_TIPO_CODIGO,'0' ) like :parTipo " + // " AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like '" + parNumempleado + "' " + ////"AND NVL(TRIM(SEGMENT2),' ') like '" + parAuditoriaIntelectual.UN + "'" + // " AND NVL(TRIM(QUA_GRADO_ACADEMICO),' ') like '" + parGradoAcademico + "' " + // " AND NVL(TRIM(SEGMENT4),' ') like '" + parArea + "' " + // " AND NVL(TRIM(QUA_ESPECIALIDAD),' ') like '" + parEspecialidad + "' " + // " AND NVL(TRIM(ASG.NAME),' ') like '" + parPuesto + "' " + // " AND NVL(TRIM(ASG.SEGMENT3),' ') like trim('" + parUn + "') " + // " ORDER BY PERS.PERSON_ID "; comando.Parameters.Clear(); comando.Parameters.Add(":parNumempleado", OracleType.VarChar).Value = parNumempleado; comando.Parameters.Add(":parGradoAcademico", OracleType.VarChar).Value = parGradoAcademico; comando.Parameters.Add(":parArea", OracleType.VarChar).Value = parArea; comando.Parameters.Add(":parEspecialidad", OracleType.VarChar).Value = parEspecialidad; comando.Parameters.Add(":parPuesto", OracleType.VarChar).Value = parPuesto; comando.Parameters.Add(":parUn", OracleType.VarChar).Value = parUn; comando.Parameters.Add(":parCategoriaId", OracleType.VarChar).Value = parCategoriaId; comando.Parameters.Add(":parGenero", OracleType.VarChar).Value = parGenero; comando.Parameters.Add(":parTipoemp", OracleType.VarChar).Value = parTipoemp; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<AuditoriaIntelectual> listaEmpleadosAuditoria = new List<AuditoriaIntelectual>(); foreach (DataRow fila in datos.Rows) { AuditoriaIntelectual empAuditoria = new AuditoriaIntelectual(); empAuditoria.NumeroEmpleado = fila["PER_EMPLEADO_NUMERO"].ToString(); empAuditoria.Area = fila["ASG_AREA"].ToString(); empAuditoria.Direccion = fila["ASG_DIRECCION"].ToString(); empAuditoria.Departamento = fila["DEPTO"].ToString(); empAuditoria.UN = fila["PER_UN"].ToString(); empAuditoria.Nombre = fila["ASG_EMPLEADO"].ToString(); empAuditoria.Puesto = fila["ASG_PUESTO"].ToString(); empAuditoria.FIngreso = fila["ASG_EMPLEADO_FINGRESO"].ToString(); empAuditoria.Salario1 = fila["ASG_EMPLEADO_SALARIO"].ToString(); empAuditoria.Salario2 = fila["ASG_EMPLEADO_SALARIOTOTAL"].ToString(); empAuditoria.GSanguineo = fila["PER_GPO_SANGUINEO"].ToString(); empAuditoria.CURP = fila["PER_CURP"].ToString(); empAuditoria.RFC = fila["PER_RFC"].ToString(); empAuditoria.Especialidad = fila["QUA_ESPECIALIDAD"].ToString(); empAuditoria.GradoAcademico = fila["QUA_GRADO_ACADEMICO"].ToString(); empAuditoria.UEstudio= fila["QUA_ULTIMO_ESTUDIO"].ToString(); empAuditoria.Ubicacion = fila["PER_EMPLEADO_UBICACION"].ToString(); empAuditoria.Categoria = fila["CATEGORIA"].ToString(); empAuditoria.Tipoemp = fila["PERS_TIPO_DESC"].ToString(); empAuditoria.PaisNacimiento = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); empAuditoria.EstadoCivil = fila["PERS_ESTADO_CIVIL_DESC"].ToString(); empAuditoria.FechaIng = fila["ASG_EMPLEADO_FINGRESO"].ToString(); empAuditoria.CveJefeDirecto = fila["CVEJEFEDIRECTO"].ToString(); empAuditoria.NomJefeDirecto = fila["NOMBREJEFEDIRECTO"].ToString(); empAuditoria.personId = fila["PER_PERSON_ID"].ToString(); //empAuditoria.NumeroEmpleado = fila["CLAVE_CATEGORIA"].ToString(); //empAuditoria.NumeroEmpleado = fila["PERS_TIPO_DESC"].ToString(); //empAuditoria.NumeroEmpleado = fila["CATEGORIA"].ToString(); //empAuditoria.NumeroEmpleado = fila["PERS_EMPLEADO_UBICACION"].ToString(); //empAuditoria. = fila["JOB_ANTERIOR"].ToString(); //empAuditoria.Departamento = fila["DEPTO"].ToString(); listaEmpleadosAuditoria.Add(empAuditoria); } this.log = listaEmpleadosAuditoria.Count.ToString(); return listaEmpleadosAuditoria; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public List<Employee> ObtenerEbs() { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + "PERS_CLAVE, " + "PERS_TIPO_CODIGO, " + "PERS_TIPO_DESC, " + "PERS_EMPLEADO_NUMERO, " + "PERS_PRIMER_NOMBRE, " + "PERS_SEGUNDO_NOMBRE, " + "PERS_APELLIDO_PATERNO, " + "PERS_APELLIDO_MATERNO, " + "PERS_NOMBRE_COMPLETO, " + "PERS_GENERO_CLAVE, " + "PERS_GENERO_DESC, " + "PERS_CURP, " + "PERS_NACIONALIDAD_CLAVE, " + "PERS_RFC, " + "PERS_NUMERO_IMSS, " + "PERS_IFE, " + "TO_CHAR(PERS_FECHA_NACIMIENTO, 'DD-MM-YYYY' ) AS PERS_FECHA_NACIMIENTO, " + "PERS_CIUDAD_NACIMIENTO, " + "PERS_ESTADO_NACIMIENTO, " + "PERS_PAIS_NACIMIENTO_CLAVE, " + "TO_CHAR(PERS_FECHA_EFECTIVE_DESDE, 'DD-MM-YYYY' ) AS PERS_FECHA_EFECTIVE_DESDE, " + "TO_CHAR(PERS_FECHA_EFECTIVE_HASTA, 'DD-MM-YYYY' ) AS PERS_FECHA_EFECTIVE_HASTA, " + "TO_CHAR(PERS_FECHA_CONTRATACION, 'DD-MM-YYYY' ) AS PERS_FECHA_CONTRATACION, " + "TO_CHAR(PERS_FECHA_ULTIMO_INICIO, 'DD-MM-YYYY' ) AS PERS_FECHA_ULTIMO_INICIO, " + "TO_CHAR(PERS_FECHA_CREACION, 'DD-MM-YYYY' ) AS PERS_FECHA_CREACION, " + "ASIG_TRABAJO_CLAVE, " + "ASIG_TRABAJO_DESC, " + "ASIG_GRADO_CLAVE, " + "ASIG_GRADO_DESC, " + "ASIG_PUESTO_CLAVE, " + "ASIG_PUESTO_DESC, " + "ASIG_ORGANIZACION_CLAVE, " + "ASIG_ORGANIZACION_DESC, " + "ASIG_UBICACION_CLAVE, " + "ASIG_UBICACION_DESC, " + "GRUP_ZONA_JDE, " + "GRUP_PROYECTO_JDE, " + "GRUP_FASE_JDE, " + "GRUP_NOMINA_JDE, " + "GRUP_TRABAJO_JDE, " + "GRUP_COMPANIA_JDE, " + "PERS_CORREO_ELECTRONICO " + "FROM " + "( " + " SELECT " + " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO, " + " EMPLEADOS.FIRST_NAME AS PERS_PRIMER_NOMBRE, " + " EMPLEADOS.MIDDLE_NAMES AS PERS_SEGUNDO_NOMBRE, " + " EMPLEADOS.LAST_NAME AS PERS_APELLIDO_PATERNO, " + " EMPLEADOS.PER_INFORMATION1 AS PERS_APELLIDO_MATERNO, " + " EMPLEADOS.FULL_NAME AS PERS_NOMBRE_COMPLETO, " + " EMPLEADOS.SEX AS PERS_GENERO_CLAVE, " + " CASE " + " WHEN EMPLEADOS.SEX = 'M' THEN 'Masculino' " + " WHEN EMPLEADOS.SEX = 'F' THEN 'Femenino' " + " WHEN EMPLEADOS.SEX IS NULL THEN ' ' " + " ELSE 'Sexo desconocido' " + " END PERS_GENERO_DESC, " + " EMPLEADOS.NATIONAL_IDENTIFIER AS PERS_CURP, " + " EMPLEADOS.NATIONALITY AS PERS_NACIONALIDAD_CLAVE, " + " EMPLEADOS.PER_INFORMATION2 AS PERS_RFC, " + " EMPLEADOS.PER_INFORMATION3 AS PERS_NUMERO_IMSS, " + " EMPLEADOS.PER_INFORMATION5 AS PERS_IFE, " + " EMPLEADOS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " EMPLEADOS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " EMPLEADOS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " EMPLEADOS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " EMPLEADOS.EFFECTIVE_START_DATE AS PERS_FECHA_EFECTIVE_DESDE, " + " EMPLEADOS.EFFECTIVE_END_DATE as PERS_FECHA_EFECTIVE_HASTA, " + " EMPLEADOS.START_DATE as PERS_FECHA_CONTRATACION, " + " EMPLEADOS.ORIGINAL_DATE_OF_HIRE AS PERS_FECHA_ULTIMO_INICIO, " + " EMPLEADOS.CREATION_DATE AS PERS_FECHA_CREACION, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_CORREO_ELECTRONICO " + " FROM PER_ALL_PEOPLE_F EMPLEADOS " + " INNER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID " + ") EMP " + "left outer join ( " + " SELECT " + " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + " GRADOS.NAME AS ASIG_GRADO_DESC, " + " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + " UBICACIONES.dESCRIPTION AS ASIG_UBICACION_DESC, " + " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + " PAYB.name as ASIG_SALARIO_BASE_DESC, " + " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION " + " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + " INNER JOIN ( " + " SELECT " + " ASSIGNMENT_NUMBER, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " max(EFFECTIVE_END_DATE) as asig_date " + " FROM PER_ALL_ASSIGNMENTS_F " + " where to_char(EFFECTIVE_END_DATE,'DD/MM/YYYY','NLS_LANGUAGE = MEXICAN SPANISH') = '31/12/4712' " + " GROUP BY ASSIGNMENT_NUMBER " + " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + "LEFT OUTER JOIN ( " + " SELECT " + " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + " GRUPOS.SEGMENT1 AS GRUP_ZONA_JDE, " + " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + " GRUPOS.SEGMENT5 AS GRUP_TRABAJO_JDE, " + " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE " + " FROM PAY_PEOPLE_GROUPS GRUPOS " + " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + "WHERE PERS_TIPO_CODIGO = 1120"; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<Employee> listaEmpleados = new List<Employee>(); foreach (DataRow fila in datos.Rows) { Employee empleado = new Employee(); empleado.Numero = fila["PERS_EMPLEADO_NUMERO"].ToString(); empleado.Tipo = fila["PERS_TIPO_DESC"].ToString(); empleado.PrimerNombre = fila["PERS_PRIMER_NOMBRE"].ToString(); empleado.SegundoNombre = fila["PERS_SEGUNDO_NOMBRE"].ToString(); empleado.ApellidoPaterno = fila["PERS_APELLIDO_PATERNO"].ToString(); empleado.ApellidoMaterno = fila["PERS_APELLIDO_MATERNO"].ToString(); empleado.NombreCompleto = fila["PERS_NOMBRE_COMPLETO"].ToString(); empleado.Genero = fila["PERS_GENERO_DESC"].ToString(); empleado.Curp = fila["PERS_CURP"].ToString(); empleado.Rfc = fila["PERS_RFC"].ToString(); empleado.Imss = fila["PERS_NUMERO_IMSS"].ToString(); empleado.Ife = fila["PERS_IFE"].ToString(); empleado.CorreoElectronico = fila["PERS_CORREO_ELECTRONICO"].ToString(); empleado.FechaContratacion = fila["PERS_FECHA_CONTRATACION"].ToString(); empleado.FechaInicio = fila["PERS_FECHA_EFECTIVE_DESDE"].ToString(); empleado.FechaFinal = fila["PERS_FECHA_EFECTIVE_HASTA"].ToString(); empleado.FechaNacimiento = fila["PERS_FECHA_NACIMIENTO"].ToString(); empleado.CiudadNacimiento = fila["PERS_CIUDAD_NACIMIENTO"].ToString(); empleado.EstadoNacimiento = fila["PERS_ESTADO_NACIMIENTO"].ToString(); empleado.PaisNacimiento = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); empleado.Puesto = fila["ASIG_PUESTO_DESC"].ToString(); empleado.Grado = fila["ASIG_GRADO_DESC"].ToString(); empleado.Ubicacion = fila["ASIG_UBICACION_DESC"].ToString(); empleado.CompañiaJde = fila["GRUP_COMPANIA_JDE"].ToString(); empleado.ProyectoJde = fila["GRUP_PROYECTO_JDE"].ToString(); empleado.FaseJde = fila["GRUP_FASE_JDE"].ToString(); listaEmpleados.Add(empleado); } this.log = listaEmpleados.Count.ToString(); return listaEmpleados; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
//------------------------------------------------------------------------------------------------------------------------------------------ public DataTable allempleados() { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); //comando.CommandText = " select a.employee_number as id_emp, a.full_name as full_name, a.person_id, c.name, b.organization_id " + // " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id , " + // " HR_ALL_ORGANIZATION_UNITS c " + // " where b.organization_id =c.organization_id and a.employee_number not in " + // " (select employee_number from PER_ALL_PEOPLE_F where person_type_id='1120' and effective_end_date<> TO_DATE ( '12/31/4712' , 'MM / DD / YY' )) " + // " group by a.employee_number, a.full_name, a.person_id, c.name, b.organization_id "; comando.CommandText = " select a.employee_number as id_emp, a.full_name as full_name, a.person_id, c.name, b.organization_id, " + " b.supervisor_id as id_jefe_inmediato " + " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id , HR_ALL_ORGANIZATION_UNITS c " + " where b.organization_id =c.organization_id " + " and TO_CHAR(a.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' and TO_CHAR(b.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " and a.employee_number<>' ' and a.PERSON_TYPE_ID<>'1123' "; comando.Parameters.Clear(); DataTable datos = baseOracle.Consultar(comando); return datos; }
protected int EjecutarConsulta(OracleCommand parComando, string parBd) { datosTabla = new DataTable(); ManagerOracle gestor = new ManagerOracle(parBd); int noRegistros = gestor.Select(parComando, datosTabla); if (noRegistros != -1) // Sin errores { if (noRegistros != 0) { this.log = "OK"; } else { this.log = "VACIO"; } } else { this.log = gestor.Log; } return noRegistros; }
public List<Employee> allempleadosxcompania(string xcompania) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + "PERS_CLAVE, " + "PERS_TIPO_CODIGO, " + "PERS_TIPO_DESC, " + "PERS_EMPLEADO_NUMERO, " + "PERS_TITULO, " + "PERS_PRIMER_NOMBRE, " + "PERS_SEGUNDO_NOMBRE, " + "PERS_APELLIDO_PATERNO, " + "PERS_APELLIDO_MATERNO, " + "PERS_NOMBRE_COMPLETO, " + "PERS_GENERO_CLAVE, " + "PERS_GENERO_DESC, " + "PERS_CURP, " + "PERS_NACIONALIDAD_CLAVE, " + "PERS_RFC, " + "PERS_NUMERO_IMSS, " + "PERS_IFE, " + "to_char(PERS_FECHA_NACIMIENTO, 'DD/MM/YYYY') AS PERS_FECHA_NACIMIENTO, " + "PERS_CIUDAD_NACIMIENTO, " + "PERS_ESTADO_NACIMIENTO, " + "PERS_PAIS_NACIMIENTO_CLAVE, " + "TO_CHAR(PERS_FECHA_EFECTIVE_DESDE, 'DD/MM/YYYY') as PERS_FECHA_EFECTIVE_DESDE, " + "to_char(PERS_FECHA_EFECTIVE_HASTA, 'DD/MM/YYYY') AS PERS_FECHA_EFECTIVE_HASTA, " + "PERS_EMAIL, " + "PERS_ESTADO_CIVIL, " + "PERS_CORREO_ELECTRONICO, " + "PERS_ESTADO_CIVIL_DESC, " + "TO_CHAR(PERS_FECHA_CONTRATACION, 'DD/MM/YYYY') AS PERS_FECHA_CONTRATACION, " + "ASIG_CLAVE, " + "ASIG_EMPLEADO_NUMERO, " + "ASIG_PERSONA_CLAVE, " + "TO_CHAR(ASIG_FECHA_INICIO, 'DD/MM/YYYY') as ASIG_FECHA_INICIO, " + "to_char(ASIG_FECHA_FIN, 'DD/MM/YYYY') AS ASIG_FECHA_FIN, " + "ASIG_ORGANIZACION_CLAVE, " + "ASIG_ORGANIZACION_DESC, " + "ASIG_TRABAJO_CLAVE, " + "ASIG_TRABAJO_DESC, " + "ASIG_GRADO_CLAVE, " + "ASIG_GRADO_DESC, " + "ASIG_UBICACION_CLAVE, " + "ASIG_UBICACION_DESC, " + "ASIG_GRUPO_CLAVE, " + "ASIG_GRUPO_DESC, " + "ASIG_PUESTO_CLAVE, " + "ASIG_PUESTO_DESC, " + "ASIG_NOMINA_CLAVE, " + "ASIG_NOMINA_DESC, " + "ASIG_ESTADO_CLAVE, " + "ASIG_ESTADO_DESC, " + "ASIG_CATEGORIA_CODIGO, " + "ASIG_SALARIO_BASE_CLAVE, " + "ASIG_SALARIO_BASE_DESC, " + "INFORMACION_ESTATUTARIA_CLAVE, " + "INFORMACION_ESTATUTARIA_DESC, " + "ASIG_VERSION, " + "ASIG_JEFE_DIRECTO_CLAVE, " + "ASIG_JEFE_DIRECTO_DESC, " + "ASIG_SALARIO_IN, " + "ASIG_SALARIO_OUT, " + "ASIG_TIPO_EMPLEADO, " + "GRUP_CLAVE, " + "GRUP_NOMBRE, " + "GRUP_BANDERA_HABILITADO, " + "GRUP_NOMINA_JDE, " + "GRUP_COMPANIA_JDE, " + "GRUP_PROYECTO_JDE, " + "GRUP_PROYECTO_CODE_JDE, " + "GRUP_FASE_JDE, " + "GRUP_FASE_CODE_JDE, " + "GRUP_PUESTO_JDE, " + "GRUP_PUESTO_CODE_JDE, " + "METODO_ASIGNACION_ID, " + "METODO_NOMBRE, " + "METODO_TIPO, " + "METODO_PRIORIDAD, " + "TO_CHAR(METODO_FECHA_EFEC_DESDE, 'DD/MM/YYYY') as METODO_FECHA_EFEC_DESDE, " + "TO_CHAR(METODO_FECHA_EFEC_HASTA, 'DD/MM/YYYY') AS METODO_FECHA_EFEC_HASTA, " + "METODO_IMPORTE_SALDO, " + "METODO_PORCENTAJE, " + "METODO_PAGO, " + "METODO_SUCURSAL, " + "METODO_CUENTA, " + "METODO_BANCO, " + "METODO_TIPO_CUENTA_ID, " + "METODO_CLABE " + "FROM " + "( " + " SELECT " + " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO, " + " EMPLEADOS.TITLE AS PERS_TITULO, " + " EMPLEADOS.FIRST_NAME AS PERS_PRIMER_NOMBRE, " + " EMPLEADOS.MIDDLE_NAMES AS PERS_SEGUNDO_NOMBRE, " + " EMPLEADOS.LAST_NAME AS PERS_APELLIDO_PATERNO, " + " EMPLEADOS.PER_INFORMATION1 AS PERS_APELLIDO_MATERNO, " + " EMPLEADOS.FULL_NAME AS PERS_NOMBRE_COMPLETO, " + " EMPLEADOS.SEX AS PERS_GENERO_CLAVE, " + " CASE " + " WHEN EMPLEADOS.SEX = 'M' THEN 'Masculino' " + " WHEN EMPLEADOS.SEX = 'F' THEN 'Femenino' " + " WHEN EMPLEADOS.SEX IS NULL THEN ' ' " + " ELSE 'Sexo desconocido' " + " END PERS_GENERO_DESC, " + " EMPLEADOS.NATIONAL_IDENTIFIER AS PERS_CURP, " + " EMPLEADOS.NATIONALITY AS PERS_NACIONALIDAD_CLAVE, " + " EMPLEADOS.PER_INFORMATION2 AS PERS_RFC, " + " EMPLEADOS.PER_INFORMATION3 AS PERS_NUMERO_IMSS, " + " EMPLEADOS.PER_INFORMATION5 AS PERS_IFE, " + " EMPLEADOS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " EMPLEADOS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " EMPLEADOS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " EMPLEADOS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " EMPLEADOS.EFFECTIVE_START_DATE AS PERS_FECHA_EFECTIVE_DESDE, " + " EMPLEADOS.EFFECTIVE_END_DATE AS PERS_FECHA_EFECTIVE_HASTA, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_EMAIL, " + " EMPLEADOS.MARITAL_STATUS AS PERS_ESTADO_CIVIL, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_CORREO_ELECTRONICO, " + " CASE " + " WHEN EMPLEADOS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN EMPLEADOS.MARITAL_STATUS = 'M' THEN 'Cazad@' " + " WHEN EMPLEADOS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " EMPLEADOS.START_DATE as PERS_FECHA_CONTRATACION " + " FROM PER_ALL_PEOPLE_F EMPLEADOS " + " INNER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID " + ") EMP " + "left outer join ( " + " SELECT " + " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + " GRADOS.NAME AS ASIG_GRADO_DESC, " + " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + " UBICACIONES.location_code AS ASIG_UBICACION_DESC, " + " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + " ASIGNACIONES.EMPLOYMENT_CATEGORY AS ASIG_CATEGORIA_CODIGO, " + " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + " PAYB.name as ASIG_SALARIO_BASE_DESC, " + " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION, " + " ASIGNACIONES.SUPERVISOR_ID AS ASIG_JEFE_DIRECTO_CLAVE, " + " JEFATURAS.FULL_NAME AS ASIG_JEFE_DIRECTO_DESC, " + " SAL.PROPOSED_SALARY_N AS ASIG_SALARIO_IN, " + " SAL.ATTRIBUTE2 AS ASIG_SALARIO_OUT, " + " CASE SAL.PROPOSED_SALARY_N WHEN 0 THEN 'O' ELSE 'I' END AS ASIG_TIPO_EMPLEADO " + " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + " INNER JOIN ( " + " SELECT " + " ASSIGNMENT_NUMBER, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " max(EFFECTIVE_END_DATE) as asig_date " + " FROM PER_ALL_ASSIGNMENTS_F " + " where TO_CHAR(EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " GROUP BY ASSIGNMENT_NUMBER " + " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN ( select " + " ppp.assignment_id " + " ,ppp.pay_proposal_id " + " ,ppp.proposed_salary_n " + " ,ppp.attribute2 " + " FROM per_pay_proposals ppp " + " inner join ( " + " SELECT " + " assignment_id " + " ,MAX (pay_proposal_id) pay_object " + " FROM per_pay_proposals " + " group by " + " assignment_id " + " ) valid " + " on valid.assignment_id = ppp.assignment_id " + " and valid.pay_object = ppp.pay_proposal_id " + " ) SAL ON SAL.ASSIGNMENT_ID = ASIGNACIONES.ASSIGNMENT_ID " + " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F JEFATURAS ON JEFATURAS.PERSON_ID = ASIGNACIONES.SUPERVISOR_ID AND TO_CHAR(JEFATURAS.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + "LEFT OUTER JOIN ( " + " SELECT " + " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE, " + " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + " PROY.LOOKUP_CODE AS GRUP_PROYECTO_CODE_JDE, " + " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + " FASE.LOOKUP_CODE AS GRUP_FASE_CODE_JDE, " + " grupos.segment7 as GRUP_PUESTO_JDE, " + " IMMS.LOOKUP_CODE AS GRUP_PUESTO_CODE_JDE " + " FROM PAY_PEOPLE_GROUPS GRUPOS " + " LEFT OUTER JOIN FND_LOOKUP_VALUES PROY ON PROY.MEANING = GRUPOS.SEGMENT2 " + " AND PROY.lookup_type = 'NVL_PAY_PROYECTO' " + " AND PROY.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES FASE ON FASE.MEANING = GRUPOS.SEGMENT3 " + " AND FASE.lookup_type = 'NVL_PAY_FASE_V2' " + " AND FASE.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES IMMS ON IMMS.MEANING = GRUPOS.segment7 " + " AND IMMS.lookup_type = 'NVL_PUESTO_IMSS' " + " AND IMMS.LANGUAGE = 'ESA' " + " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + "left outer join ( " + " select " + " PPPM.ASSIGNMENT_ID AS METODO_ASIGNACION_ID, " + " POPN.ORG_PAYMENT_METHOD_NAME AS METODO_NOMBRE, " + " PTI.PAYMENT_TYPE_NAME AS METODO_TIPO, " + " PPPM.PRIORITY AS METODO_PRIORIDAD, " + " PPPM.EFFECTIVE_START_DATE AS METODO_FECHA_EFEC_DESDE, " + " PPPM.EFFECTIVE_END_DATE AS METODO_FECHA_EFEC_HASTA, " + " PPPM.AMOUNT AS METODO_IMPORTE_SALDO, " + " PPPM.PERCENTAGE AS METODO_PORCENTAJE, " + " PPPM.ATTRIBUTE1 AS METODO_PAGO, " + " PEA.SEGMENT2 AS METODO_SUCURSAL, " + " PEA.SEGMENT3 AS METODO_CUENTA, " + " PEA.MEANING AS METODO_BANCO, " + " PEA.SEGMENT4 AS METODO_TIPO_CUENTA_ID, " + " PEA.SEGMENT5 AS METODO_CLABE " + " from PAY_PERSONAL_PAYMENT_METHODS_F PPPM " + " LEFT OUTER JOIN ( " + " SELECT * " + " FROM PAY_EXTERNAL_ACCOUNTS PE " + " LEFT OUTER JOIN FND_LOOKUP_VALUES LUV " + " ON PE.SEGMENT1 = LUV.LOOKUP_CODE AND LOOKUP_TYPE LIKE 'MX_BANK' AND LANGUAGE = 'ESA' " + " ) PEA ON PEA.EXTERNAL_ACCOUNT_ID = PPPM.EXTERNAL_ACCOUNT_ID " + " LEFT OUTER JOIN PAY_ORG_PAYMENT_METHODS_F POPN " + " ON POPN.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID " + " LEFT OUTER JOIN PAY_PAYMENT_TYPES PTI " + " ON PTI.PAYMENT_TYPE_ID = POPN.PAYMENT_TYPE_ID " + " WHERE TO_CHAR(PPPM.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) MTP ON MTP.METODO_ASIGNACION_ID = asg.ASIG_CLAVE " + " WHERE PERS_TIPO_CODIGO = '1121' OR PERS_TIPO_CODIGO = '1120' " + " AND NVL(GRUP_COMPANIA_JDE, 'VACIO') LIKE :parNombreCompania "; comando.Parameters.Clear(); //comando.Parameters.Add(":parNombreCompania", OracleType.VarChar).Value = parNombreCompania.CompañiaJde; comando.Parameters.Add(":parNombreCompania", OracleType.VarChar).Value = xcompania; //comando.Parameters.AddWithValue(":parNombreCompania", xcompania); DataTable setDatos = baseOracle.Consultar(comando); //DataSet setDatos = baseOracle.Consultar(consulta); //comando.Parameters.AddWithValue(":parNombreCompania", xcompania); //if (setDatos != null) //{ // DataTable datos = setDatos.Tables[0]; // if (datos.Rows.Count != 0) // { // List<Employee> listaEmpleados = new List<Employee>(); // foreach (DataRow fila in datos.Rows) // { if (setDatos != null) { if (setDatos.Rows.Count != 0) { List<Employee> listaEmpleados = new List<Employee>(); foreach (DataRow fila in setDatos.Rows) { Employee EmpleadoEBS = new Employee(); EmpleadoEBS.Clave = int.Parse(fila["PERS_EMPLEADO_NUMERO"].ToString()); EmpleadoEBS.NombreCompleto = fila["PERS_NOMBRE_COMPLETO"].ToString(); listaEmpleados.Add(EmpleadoEBS); } this.log = listaEmpleados.Count.ToString(); return listaEmpleados; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
//public TablaAuditoriaIntelectual(AuditoriaIntelectual parNumeroEmpleado) //{ // this.managerOracle = new ManagerOracle("EBS"); // this.comando = new OracleCommand(); // this.log = string.Empty; // if (parNumeroEmpleado != null) // { // this.eAuditoria = parNumeroEmpleado; // } // else // { // this.eAuditoria = new AuditoriaIntelectual(); // } //} public DataTable BuscarEmpleadosAI(AuditoriaIntelectual parAuditoriaIntelectual) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " SELECT " + " PERS.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " PERS.PERSON_ID AS PER_PERSON_ID, " + " ASG.SEGMENT4 AS ASG_AREA, " + " ASG.SEGMENT2 AS ASG_DIRECCION, " + " ASG.SEGMENT3 AS ASG_DEPTO, " + " ' ' AS PER_UN, " + " PERS.FULL_NAME AS ASG_EMPLEADO, " + " ASG.NAME AS ASG_PUESTO, " + " ASG.EFFECTIVE_START_DATE AS ASG_EMPLEADO_FINGRESO, " + " ASG.PROPOSED_SALARY_N AS ASG_EMPLEADO_SALARIO, " + " ASG.ATTRIBUTE2 AS ASG_EMPLEADO_SALARIOTOTAL, " + " QUA.QUA_GRADO_ACADEMICO, " + " QUA.QUA_ULTIMO_ESTUDIO, " + " QUA.QUA_ESPECIALIDAD, " + " PERS.ATTRIBUTE1 AS PER_GPO_SANGUINEO, " + " PERS.ATTRIBUTE2 AS PER_ALERGIAS, " + " PERS.ATTRIBUTE3 AS PER_LICENCIA_INTERNA, " + " PERS.ATTRIBUTE4 AS PER_LI_FECHA_VENC, " + " PERS.ATTRIBUTE5 AS PER_LI_CERTIFICACION, " + " PERS.ATTRIBUTE6 AS PER_LICENCIA_EXTERNA, " + " PERS.ATTRIBUTE7 AS PER_LE_FECHA_VENC, " + " PERS.NATIONAL_IDENTIFIER AS PER_CURP, " + " PERS.PER_INFORMATION2 AS PER_RFC, " + " CASE " + " WHEN PERS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN PERS.MARITAL_STATUS = 'M' THEN 'Casad@' " + " WHEN PERS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " PERS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " PERS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " PERS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " PERS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " ASG.GRADE_ID as CLAVE_CATEGORIA, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " ASG.GRADODES as CATEGORIA, " + " ASG.LOCATION_CODE as PER_EMPLEADO_UBICACION, " + " JOBOLD.PEM_EMPLEADOR as JOBS_ANTERIOR, " + " ASG.ASIG_ORGANIZACION_DESC as DEPTO " + " FROM PER_ALL_PEOPLE_F PERS " + " LEFT OUTER JOIN ( " + " SELECT " + " PA.GRADE_ID, " + " PA.EFFECTIVE_START_DATE, " + " PA.PERSON_ID, " + " GPO.SEGMENT4, " + " GPO.SEGMENT2, " + " GPO.SEGMENT3, " + " GPO.PEOPLE_GROUP_ID, " + " PUESTOS.NAME, " + " SAL.PROPOSED_SALARY_N, " + " SAL.ATTRIBUTE2, " + " RD.NAME as GRADODES, " + " UBICACIONES.LOCATION_CODE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC " + " FROM PER_ALL_ASSIGNMENTS_F PA " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = PA.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = PA.LOCATION_ID " + " LEFT OUTER JOIN PER_GRADES RD ON RD.GRADE_ID = PA.GRADE_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GPO ON GPO.PEOPLE_GROUP_ID = PA.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = PA.POSITION_ID " + " LEFT OUTER JOIN PER_PAY_PROPOSALS SAL ON SAL.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND TO_CHAR(SAL.DATE_TO,'YYYY-MM-DD' ) = '4712-12-31' " + " WHERE EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " ) ASG ON ASG.PERSON_ID = PERS.PERSON_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " QUALIFICATION_ID, " + " PERSON_ID, " + " TITLE AS QUA_ESPECIALIDAD, " + " GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + " AWARDING_BODY, " + " COMMENTS AS QUA_COMENTARIOS, " + " UPPER(NAME) AS QUA_GRADO_ACADEMICO, " + " HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + " FROM PER_QUALIFICATIONS " + " LEFT OUTER JOIN ( SELECT * " + " FROM PER_QUALIFICATION_TYPES_TL " + " WHERE LANGUAGE = 'ESA' " + " ) TIPOS ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS.QUALIFICATION_TYPE_ID " + " ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + " " + " LEFT OUTER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = PERS.PERSON_ID AND ACT.VER = PERS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " count(PEM.EMPLOYER_NAME) AS PEM_EMPLEADOR " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + " group by SUP.EMPLOYEE_NUMBER, PEM.PERSON_ID " + " ) JOBOLD ON JOBOLD.PERSON_ID = PERS.PERSON_ID " + " " + "WHERE 1=1 " + "AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + "AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like '" + parAuditoriaIntelectual.NumeroEmpleado + "'" + //"AND NVL(TRIM(SEGMENT2),' ') like '" + parAuditoriaIntelectual.UN + "'" + "AND NVL(TRIM(QUA_GRADO_ACADEMICO),' ') like '" + parAuditoriaIntelectual.GradoAcademico + "'" + "AND NVL(TRIM(SEGMENT4),' ') like '" + parAuditoriaIntelectual.Area + "'" + "AND NVL(TRIM(QUA_ESPECIALIDAD),' ') like '" + parAuditoriaIntelectual.Especialidad + "'" + "AND NVL(TRIM(ASG.NAME),' ') like '" + parAuditoriaIntelectual.Puesto + "'" + "AND NVL(TRIM(ASG.SEGMENT3),' ') like trim('" + parAuditoriaIntelectual.UN +"')" + "ORDER BY PERS.PERSON_ID "; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } // "WHERE NVL(PERS_EMPLEADO_NUMERO,'VACIO') LIKE :parNumeroEmpleado " + // "AND NVL(PERS_PRIMER_NOMBRE,'VACIO') LIKE :parPrimerNombre " + // "AND NVL(PERS_SEGUNDO_NOMBRE,'VACIO') LIKE :parSegundoNombre " + // "AND NVL(PERS_APELLIDO_PATERNO,'VACIO') LIKE :parApellidoPaterno " + // "AND NVL(PERS_APELLIDO_MATERNO,'VACIO') LIKE :parApellidoMaterno " + // "AND NVL(PERS_GENERO_CLAVE,'VACIO') LIKE :parGeneroClave " + // "AND NVL(GRUP_COMPANIA_JDE,'VACIO') LIKE :parCompania " + // "AND NVL(ASIG_PUESTO_CLAVE,'0') LIKE :parPuestoClave " + // "AND NVL(ASIG_ORGANIZACION_CLAVE,'0') LIKE :parDepartamentoClave " + // "and nvl(PERS_TIPO_CODIGO,'0' ) like :parTipo " + // "AND PERS_FECHA_CONTRATACION between :parContratacionInicio AND :parContratacionFin"; //comando.Parameters.Clear(); //comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parNumeroEmpleado; //comando.Parameters.Add(":parPrimerNombre", OracleType.VarChar).Value = parPrimerNombre; //comando.Parameters.Add(":parSegundoNombre", OracleType.VarChar).Value = parSegundoNombre; //comando.Parameters.Add(":parApellidoPaterno", OracleType.VarChar).Value = parApellidoPaterno; //comando.Parameters.Add(":parApellidoMaterno", OracleType.VarChar).Value = parApellidoMaterno; //comando.Parameters.Add(":parGeneroClave", OracleType.VarChar).Value = parGeneroClave; //comando.Parameters.Add(":parCompania", OracleType.VarChar).Value = parCompañia; //comando.Parameters.Add(":parPuestoClave", OracleType.VarChar).Value = parPuestoClave; //comando.Parameters.Add(":parDepartamentoClave", OracleType.VarChar).Value = parDepartamentoClave; //comando.Parameters.Add(":parTipo", OracleType.VarChar).Value = parTipo; //comando.Parameters.Add(":parContratacionInicio", OracleType.DateTime).Value = Convert.ToDateTime(parContratacionInicio); //comando.Parameters.Add(":parContratacionFin", OracleType.DateTime).Value = Convert.ToDateTime(parContratacionFin); }
//------------------------------------------------------------------------------- //public List<AuditoriaIntelectual> ObtenerAgrupador2( // string parNumempleado, // string parGradoAcademico, // string parArea, // string parEspecialidad, // string parPuesto, // string parUn ) //{ // ManagerOracle baseOracle = new ManagerOracle("EBS"); // OracleCommand comando = new OracleCommand(); // DataTable datos; // comando.CommandText = " SELECT " + // " PERS.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + // " PERS.PERSON_ID AS PER_PERSON_ID, " + // " ASG.SEGMENT4 AS ASG_AREA, " + // " ASG.SEGMENT2 AS ASG_DIRECCION, " + // " ASG.SEGMENT3 AS ASG_DEPTO, " + // " ' ' AS PER_UN, " + // " PERS.FULL_NAME AS ASG_EMPLEADO, " + // " ASG.NAME AS ASG_PUESTO, " + // " ASG.EFFECTIVE_START_DATE AS ASG_EMPLEADO_FINGRESO, " + // " ASG.PROPOSED_SALARY_N AS ASG_EMPLEADO_SALARIO, " + // " ASG.ATTRIBUTE2 AS ASG_EMPLEADO_SALARIOTOTAL, " + // " QUA.QUA_GRADO_ACADEMICO, " + // " QUA.QUA_ULTIMO_ESTUDIO, " + // " QUA.QUA_ESPECIALIDAD, " + // " PERS.ATTRIBUTE1 AS PER_GPO_SANGUINEO, " + // " PERS.ATTRIBUTE2 AS PER_ALERGIAS, " + // " PERS.ATTRIBUTE3 AS PER_LICENCIA_INTERNA, " + // " PERS.ATTRIBUTE4 AS PER_LI_FECHA_VENC, " + // " PERS.ATTRIBUTE5 AS PER_LI_CERTIFICACION, " + // " PERS.ATTRIBUTE6 AS PER_LICENCIA_EXTERNA, " + // " PERS.ATTRIBUTE7 AS PER_LE_FECHA_VENC, " + // " PERS.NATIONAL_IDENTIFIER AS PER_CURP, " + // " PERS.PER_INFORMATION2 AS PER_RFC, " + // " CASE " + // " WHEN PERS.MARITAL_STATUS = 'S' THEN 'Solter@' " + // " WHEN PERS.MARITAL_STATUS = 'M' THEN 'Casad@' " + // " WHEN PERS.MARITAL_STATUS IS NULL THEN ' ' " + // " ELSE '--' " + // " END PERS_ESTADO_CIVIL_DESC, " + // " PERS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + // " PERS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + // " PERS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + // " PERS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + // " ASG.GRADE_ID as CLAVE_CATEGORIA, " + // " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + // " ASG.GRADODES as CATEGORIA, " + // " ASG.LOCATION_CODE as PER_EMPLEADO_UBICACION, " + // " JOBOLD.PEM_EMPLEADOR as JOBS_ANTERIOR, " + // " ASG.ASIG_ORGANIZACION_DESC as DEPTO " + // " FROM PER_ALL_PEOPLE_F PERS " + // " LEFT OUTER JOIN ( " + // " SELECT " + // " PA.GRADE_ID, " + // " PA.EFFECTIVE_START_DATE, " + // " PA.PERSON_ID, " + // " GPO.SEGMENT4, " + // " GPO.SEGMENT2, " + // " GPO.SEGMENT3, " + // " GPO.PEOPLE_GROUP_ID, " + // " PUESTOS.NAME, " + // " SAL.PROPOSED_SALARY_N, " + // " SAL.ATTRIBUTE2, " + // " RD.NAME as GRADODES, " + // " UBICACIONES.LOCATION_CODE, " + // " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC " + // " FROM PER_ALL_ASSIGNMENTS_F PA " + // " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = PA.ORGANIZATION_ID " + // " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = PA.LOCATION_ID " + // " LEFT OUTER JOIN PER_GRADES RD ON RD.GRADE_ID = PA.GRADE_ID " + // " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GPO ON GPO.PEOPLE_GROUP_ID = PA.PEOPLE_GROUP_ID " + // " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = PA.POSITION_ID " + // " LEFT OUTER JOIN PER_PAY_PROPOSALS SAL ON SAL.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND TO_CHAR(SAL.DATE_TO,'YYYY-MM-DD' ) = '4712-12-31' " + // " WHERE EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + // " ) ASG ON ASG.PERSON_ID = PERS.PERSON_ID " + // " LEFT OUTER JOIN ( " + // " SELECT " + // " QUALIFICATION_ID, " + // " PERSON_ID, " + // " TITLE AS QUA_ESPECIALIDAD, " + // " GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + // " AWARDING_BODY, " + // " COMMENTS AS QUA_COMENTARIOS, " + // " UPPER(NAME) AS QUA_GRADO_ACADEMICO, " + // " HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + // " FROM PER_QUALIFICATIONS " + // " LEFT OUTER JOIN ( SELECT * " + // " FROM PER_QUALIFICATION_TYPES_TL " + // " WHERE LANGUAGE = 'ESA' " + // " ) TIPOS ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS.QUALIFICATION_TYPE_ID " + // " ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + // " " + // " LEFT OUTER JOIN ( " + // " SELECT " + // " PERSON_ID, " + // " MAX(OBJECT_VERSION_NUMBER) AS VER, " + // " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + // " FROM PER_ALL_PEOPLE_F " + // " GROUP BY PERSON_ID " + // " ) ACT ON ACT.PERSON_ID = PERS.PERSON_ID AND ACT.VER = PERS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + // " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID " + // " LEFT OUTER JOIN ( " + // " SELECT " + // " PEM.PERSON_ID, " + // " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + // " count(PEM.EMPLOYER_NAME) AS PEM_EMPLEADOR " + // " FROM PER_PREVIOUS_EMPLOYERS PEM " + // " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + // " group by SUP.EMPLOYEE_NUMBER, PEM.PERSON_ID " + // " ) JOBOLD ON JOBOLD.PERSON_ID = PERS.PERSON_ID " + // " " + // "WHERE 1=1 " + // "AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + // "AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like '" + parNumempleado + "'" + // "AND NVL(TRIM(QUA_GRADO_ACADEMICO),' ') like '" + parGradoAcademico + "'" + // "AND NVL(TRIM(SEGMENT4),' ') like '" + parArea + "'" + // "AND NVL(TRIM(QUA_ESPECIALIDAD),' ') like '" + parEspecialidad + "'" + // "AND NVL(TRIM(ASG.NAME),' ') like '" + parPuesto + "'" + // "AND NVL(TRIM(ASG.SEGMENT3),' ') like trim('" + parUn + "')" + // "ORDER BY PERS.PERSON_ID "; // comando.Parameters.Clear(); // comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parNumempleado; // comando.Parameters.Add(":parGradoAcademico", OracleType.VarChar).Value = parGradoAcademico; // comando.Parameters.Add(":parArea", OracleType.VarChar).Value = parArea; // comando.Parameters.Add(":parEspecialidad", OracleType.VarChar).Value = parEspecialidad; // comando.Parameters.Add(":parPuesto", OracleType.VarChar).Value = parPuesto; // comando.Parameters.Add(":parUn", OracleType.VarChar).Value = parUn; // datos = baseOracle.Consultar(comando); // if (datos != null) // { // if (datos.Rows.Count != 0) // { // List<AuditoriaIntelectual> listaDocumentos = new List<AuditoriaIntelectual>(); // foreach (DataRow fila in datos.Rows) // { // AuditoriaIntelectual empAuditoria = new AuditoriaIntelectual(); // empAuditoria.NumeroEmpleado = fila["PER_EMPLEADO_NUMERO"].ToString(); // empAuditoria.Area = fila["ASG_AREA"].ToString(); // empAuditoria.Direccion = fila["ASG_DIRECCION"].ToString(); // empAuditoria.Departamento = fila["ASG_DEPTO"].ToString(); // empAuditoria.UN = fila["PER_UN"].ToString(); // empAuditoria.Nombre = fila["ASG_EMPLEADO"].ToString(); // empAuditoria.Puesto = fila["ASG_PUESTO"].ToString(); // empAuditoria.FIngreso = fila["ASG_EMPLEADO_FINGRESO"].ToString(); // empAuditoria.Salario1 = fila["ASG_EMPLEADO_SALARIO"].ToString(); // empAuditoria.Salario2 = fila["ASG_EMPLEADO_SALARIOTOTAL"].ToString(); // empAuditoria.GSanguineo = fila["PER_GPO_SANGUINEO"].ToString(); // empAuditoria.CURP = fila["PER_CURP"].ToString(); // empAuditoria.RFC = fila["PER_RFC"].ToString(); // empAuditoria.NumeroEmpleado = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); // empAuditoria.NumeroEmpleado = fila["CLAVE_CATEGORIA"].ToString(); // empAuditoria.NumeroEmpleado = fila["PERS_TIPO_DESC"].ToString(); // empAuditoria.NumeroEmpleado = fila["CATEGORIA"].ToString(); // empAuditoria.NumeroEmpleado = fila["PERS_EMPLEADO_UBICACION"].ToString(); // listaDocumentos.Add(empAuditoria); // } // this.log = listaDocumentos.Count.ToString(); // return listaDocumentos; // } // else // { // this.log = "VACIO"; // return null; // } // } // else // { // this.log = baseOracle.Log; // return null; // } //} public List<AuditoriaIntelectual> ObtenerAgrupador2() { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); DataTable datos; comando.CommandText = " SELECT " + " PERS.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " PERS.PERSON_ID AS PER_PERSON_ID, " + " ASG.SEGMENT4 AS ASG_AREA, " + " ASG.SEGMENT2 AS ASG_DIRECCION, " + " ASG.SEGMENT3 AS ASG_DEPTO, " + " ' ' AS PER_UN, " + " PERS.FULL_NAME AS ASG_EMPLEADO, " + " ASG.NAME AS ASG_PUESTO, " + " ASG.EFFECTIVE_START_DATE AS ASG_EMPLEADO_FINGRESO, " + " ASG.PROPOSED_SALARY_N AS ASG_EMPLEADO_SALARIO, " + " ASG.ATTRIBUTE2 AS ASG_EMPLEADO_SALARIOTOTAL, " + " QUA.QUA_GRADO_ACADEMICO, " + " QUA.QUA_ULTIMO_ESTUDIO, " + " QUA.QUA_ESPECIALIDAD, " + " PERS.ATTRIBUTE1 AS PER_GPO_SANGUINEO, " + " PERS.ATTRIBUTE2 AS PER_ALERGIAS, " + " PERS.ATTRIBUTE3 AS PER_LICENCIA_INTERNA, " + " PERS.ATTRIBUTE4 AS PER_LI_FECHA_VENC, " + " PERS.ATTRIBUTE5 AS PER_LI_CERTIFICACION, " + " PERS.ATTRIBUTE6 AS PER_LICENCIA_EXTERNA, " + " PERS.ATTRIBUTE7 AS PER_LE_FECHA_VENC, " + " PERS.NATIONAL_IDENTIFIER AS PER_CURP, " + " PERS.PER_INFORMATION2 AS PER_RFC, " + " CASE " + " WHEN PERS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN PERS.MARITAL_STATUS = 'M' THEN 'Casad@' " + " WHEN PERS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " PERS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " PERS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " PERS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " PERS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " ASG.GRADE_ID as CLAVE_CATEGORIA, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " ASG.GRADODES as CATEGORIA, " + " ASG.LOCATION_CODE as PER_EMPLEADO_UBICACION, " + " JOBOLD.PEM_EMPLEADOR as JOBS_ANTERIOR, " + " ASG.ASIG_ORGANIZACION_DESC as DEPTO " + " FROM PER_ALL_PEOPLE_F PERS " + " LEFT OUTER JOIN ( " + " SELECT " + " PA.GRADE_ID, " + " PA.EFFECTIVE_START_DATE, " + " PA.PERSON_ID, " + " GPO.SEGMENT4, " + " GPO.SEGMENT2, " + " GPO.SEGMENT3, " + " GPO.PEOPLE_GROUP_ID, " + " PUESTOS.NAME, " + " SAL.PROPOSED_SALARY_N, " + " SAL.ATTRIBUTE2, " + " RD.NAME as GRADODES, " + " UBICACIONES.LOCATION_CODE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC " + " FROM PER_ALL_ASSIGNMENTS_F PA " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = PA.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = PA.LOCATION_ID " + " LEFT OUTER JOIN PER_GRADES RD ON RD.GRADE_ID = PA.GRADE_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GPO ON GPO.PEOPLE_GROUP_ID = PA.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = PA.POSITION_ID " + " LEFT OUTER JOIN PER_PAY_PROPOSALS SAL ON SAL.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND TO_CHAR(SAL.DATE_TO,'YYYY-MM-DD' ) = '4712-12-31' " + " WHERE EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " ) ASG ON ASG.PERSON_ID = PERS.PERSON_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " QUALIFICATION_ID, " + " PERSON_ID, " + " TITLE AS QUA_ESPECIALIDAD, " + " GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + " AWARDING_BODY, " + " COMMENTS AS QUA_COMENTARIOS, " + " UPPER(NAME) AS QUA_GRADO_ACADEMICO, " + " HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + " FROM PER_QUALIFICATIONS " + " LEFT OUTER JOIN ( SELECT * " + " FROM PER_QUALIFICATION_TYPES_TL " + " WHERE LANGUAGE = 'ESA' " + " ) TIPOS ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS.QUALIFICATION_TYPE_ID " + " ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + " " + " LEFT OUTER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = PERS.PERSON_ID AND ACT.VER = PERS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " count(PEM.EMPLOYER_NAME) AS PEM_EMPLEADOR " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + " group by SUP.EMPLOYEE_NUMBER, PEM.PERSON_ID " + " ) JOBOLD ON JOBOLD.PERSON_ID = PERS.PERSON_ID " + " " + "WHERE 1=1 " + "AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + "AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like :parNumempleado " + "AND NVL(TRIM(QUA_GRADO_ACADEMICO),' ') like :parGradoAcademico " + "AND NVL(TRIM(SEGMENT4),' ') like :parArea " + "AND NVL(TRIM(QUA_ESPECIALIDAD),' ') like :parEspecialidad " + "AND NVL(TRIM(ASG.NAME),' ') like :parPuesto " + "AND NVL(TRIM(ASG.SEGMENT3),' ') like trim(':parUn') " + "ORDER BY PERS.PERSON_ID "; comando.Parameters.Clear(); if (eAuditoria.NumeroEmpleado != null) { comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = eAuditoria.NumeroEmpleado; } else { comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = "%"; } if (eAuditoria.GradoAcademico != null) { comando.Parameters.Add(":parGradoAcademico", OracleType.VarChar).Value = eAuditoria.GradoAcademico; } else { comando.Parameters.Add(":parGradoAcademico", OracleType.VarChar).Value = "%"; } if (eAuditoria.Area != null) { comando.Parameters.Add(":parArea", OracleType.VarChar).Value = eAuditoria.Area; } else { comando.Parameters.Add(":parArea", OracleType.VarChar).Value = "%"; } if (eAuditoria.Especialidad != null) { comando.Parameters.Add(":parEspecialidad", OracleType.VarChar).Value = eAuditoria.Especialidad; } else { comando.Parameters.Add(":parEspecialidad", OracleType.VarChar).Value = "%"; } if (eAuditoria.Puesto != null) { comando.Parameters.Add(":parPuesto", OracleType.VarChar).Value = eAuditoria.Puesto; } else { comando.Parameters.Add(":parUN", OracleType.VarChar).Value = "%"; } if (eAuditoria.UN != null) { comando.Parameters.Add(":parUN", OracleType.VarChar).Value = eAuditoria.UN; } else { comando.Parameters.Add(":parPuesto", OracleType.VarChar).Value = "%"; } //comando.Parameters.Add("parNumeroEmpleado", OracleType.VarChar).Value = eAuditoria.NumeroEmpleado; //comando.Parameters.Add("parGradoAcademico", OracleType.VarChar).Value = eAuditoria.GradoAcademico; //comando.Parameters.Add("parArea", OracleType.VarChar).Value = eAuditoria.Area; //comando.Parameters.Add("parEspecialidad", OracleType.VarChar).Value = eAuditoria.Especialidad; //comando.Parameters.Add("parPuesto", OracleType.VarChar).Value = eAuditoria.Puesto; //comando.Parameters.Add("parUn", OracleType.VarChar).Value = eAuditoria.UN; datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<AuditoriaIntelectual> listaDocumentos = new List<AuditoriaIntelectual>(); foreach (DataRow fila in datos.Rows) { AuditoriaIntelectual empAuditoria = new AuditoriaIntelectual(); empAuditoria.NumeroEmpleado = fila["PER_EMPLEADO_NUMERO"].ToString(); empAuditoria.Area = fila["ASG_AREA"].ToString(); empAuditoria.Direccion = fila["ASG_DIRECCION"].ToString(); empAuditoria.Departamento = fila["ASG_DEPTO"].ToString(); empAuditoria.UN = fila["PER_UN"].ToString(); empAuditoria.Nombre = fila["ASG_EMPLEADO"].ToString(); empAuditoria.Puesto = fila["ASG_PUESTO"].ToString(); empAuditoria.FIngreso = fila["ASG_EMPLEADO_FINGRESO"].ToString(); empAuditoria.Salario1 = fila["ASG_EMPLEADO_SALARIO"].ToString(); empAuditoria.Salario2 = fila["ASG_EMPLEADO_SALARIOTOTAL"].ToString(); empAuditoria.GSanguineo = fila["PER_GPO_SANGUINEO"].ToString(); empAuditoria.CURP = fila["PER_CURP"].ToString(); empAuditoria.RFC = fila["PER_RFC"].ToString(); empAuditoria.NumeroEmpleado = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); empAuditoria.NumeroEmpleado = fila["CLAVE_CATEGORIA"].ToString(); empAuditoria.NumeroEmpleado = fila["PERS_TIPO_DESC"].ToString(); empAuditoria.NumeroEmpleado = fila["CATEGORIA"].ToString(); empAuditoria.NumeroEmpleado = fila["PERS_EMPLEADO_UBICACION"].ToString(); listaDocumentos.Add(empAuditoria); } this.log = listaDocumentos.Count.ToString(); return listaDocumentos; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }
public DataTable BuscarEmpleadosAINum(AuditoriaIntelectual parAuditoriaIntelectual) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " SELECT " + " PERS.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO " + " FROM PER_ALL_PEOPLE_F PERS " + " LEFT OUTER JOIN ( " + " SELECT " + " PA.GRADE_ID, " + " PA.EFFECTIVE_START_DATE, " + " PA.PERSON_ID, " + " GPO.SEGMENT4, " + " GPO.SEGMENT2, " + " GPO.SEGMENT3, " + " GPO.PEOPLE_GROUP_ID, " + " PUESTOS.NAME, " + " SAL.PROPOSED_SALARY_N, " + " SAL.ATTRIBUTE2, " + " RD.NAME as GRADODES, " + " UBICACIONES.LOCATION_CODE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC " + " FROM PER_ALL_ASSIGNMENTS_F PA " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = PA.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = PA.LOCATION_ID " + " LEFT OUTER JOIN PER_GRADES RD ON RD.GRADE_ID = PA.GRADE_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GPO ON GPO.PEOPLE_GROUP_ID = PA.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = PA.POSITION_ID " + " LEFT OUTER JOIN PER_PAY_PROPOSALS SAL ON SAL.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND TO_CHAR(SAL.DATE_TO,'YYYY-MM-DD' ) = '4712-12-31' " + " WHERE EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + " ) ASG ON ASG.PERSON_ID = PERS.PERSON_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " QUALIFICATION_ID, " + " PERSON_ID, " + " TITLE AS QUA_ESPECIALIDAD, " + " GRADE_ATTAINED AS QUA_ULTIMO_ESTUDIO, " + " AWARDING_BODY, " + " COMMENTS AS QUA_COMENTARIOS, " + " UPPER(NAME) AS QUA_GRADO_ACADEMICO, " + " HR_GENERAL.DECODE_LOOKUP('PER_SUBJECT_STATUSES',PER_QUALIFICATIONS.STATUS) QUA_ESTATUS " + " FROM PER_QUALIFICATIONS " + " LEFT OUTER JOIN ( SELECT * " + " FROM PER_QUALIFICATION_TYPES_TL " + " WHERE LANGUAGE = 'ESA' " + " ) TIPOS ON PER_QUALIFICATIONS.QUALIFICATION_TYPE_ID = TIPOS.QUALIFICATION_TYPE_ID " + " ) QUA ON QUA.PERSON_ID = PERS.PERSON_ID " + " " + " LEFT OUTER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = PERS.PERSON_ID AND ACT.VER = PERS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = PERS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID " + " LEFT OUTER JOIN ( " + " SELECT " + " PEM.PERSON_ID, " + " SUP.EMPLOYEE_NUMBER AS PER_EMPLEADO_NUMERO, " + " count(PEM.EMPLOYER_NAME) AS PEM_EMPLEADOR " + " FROM PER_PREVIOUS_EMPLOYERS PEM " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F SUP ON PEM.PERSON_ID = SUP.PERSON_ID " + " group by SUP.EMPLOYEE_NUMBER, PEM.PERSON_ID " + " ) JOBOLD ON JOBOLD.PERSON_ID = PERS.PERSON_ID " + " " + "WHERE 1=1 " + "AND EFFECTIVE_END_DATE = to_date('4712/12/31', 'yyyy/mm/dd') " + "AND NVL(TRIM(EMPLOYEE_NUMBER),'0') like '" + parAuditoriaIntelectual.NumeroEmpleado + "'" + //"AND NVL(TRIM(SEGMENT2),' ') like '" + parAuditoriaIntelectual.UN + "'" + "AND NVL(TRIM(QUA_GRADO_ACADEMICO),' ') like '" + parAuditoriaIntelectual.GradoAcademico + "'" + "AND NVL(TRIM(SEGMENT4),' ') like '" + parAuditoriaIntelectual.Area + "'" + "AND NVL(TRIM(QUA_ESPECIALIDAD),' ') like '" + parAuditoriaIntelectual.Especialidad + "'" + "AND NVL(TRIM(ASG.NAME),' ') like '" + parAuditoriaIntelectual.Puesto + "'" + "AND NVL(TRIM(ASG.SEGMENT3),' ') like trim('" + parAuditoriaIntelectual.UN + "')" + "ORDER BY PERS.PERSON_ID "; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } }
//public List<EntidadIncidenciasGraficas> ObtenerEmpleadoZonax() //{ // ManagerOracle baseOracle = new ManagerOracle("EBS"); // string sentenciaSQL = // " SELECT " + // " CASE " + // " WHEN ASIG_UBICACION_CLAVE = '144' or ASIG_UBICACION_CLAVE = '143' or ASIG_UBICACION_CLAVE = '142' THEN 'VILLAHERMOSA' " + // " WHEN ASIG_UBICACION_CLAVE = '148' or ASIG_UBICACION_CLAVE = '147' THEN 'POZA RICA' " + // " WHEN ASIG_UBICACION_CLAVE = '145' THEN 'REYNOSA' " + // " WHEN ASIG_UBICACION_CLAVE = '146' THEN 'VERACRUZ' " + // " WHEN ASIG_UBICACION_CLAVE = '243' THEN 'H3A1' " + // " ELSE '--' " + // " END zona , " + // " count( PERS_CLAVE) as total " + // " FROM " + // " ( " + // " SELECT " + // " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + // " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + // " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + // " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO " + // " FROM PER_ALL_PEOPLE_F EMPLEADOS " + // " INNER JOIN ( " + // " SELECT " + // " PERSON_ID, " + // " MAX(OBJECT_VERSION_NUMBER) AS VER, " + // " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + // " FROM PER_ALL_PEOPLE_F " + // " GROUP BY PERSON_ID " + // " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + // " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID "+ // " ) EMP " + // " left outer join ( " + // " SELECT " + // " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + // " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + // " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + // " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + // " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + // " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + // " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + // " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + // " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + // " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + // " GRADOS.NAME AS ASIG_GRADO_DESC, " + // " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + // " UBICACIONES.location_code AS ASIG_UBICACION_DESC, " + // " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + // " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + // " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + // " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + // " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + // " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + // " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + // " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + // " ASIGNACIONES.EMPLOYMENT_CATEGORY AS ASIG_CATEGORIA_CODIGO, " + // " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + // " PAYB.name as ASIG_SALARIO_BASE_DESC, " + // " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + // " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + // " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION " + // " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + // " INNER JOIN ( " + // " SELECT " + // " ASSIGNMENT_NUMBER, " + // " MAX(OBJECT_VERSION_NUMBER) AS VER, " + // " max(EFFECTIVE_END_DATE) as asig_date " + // " FROM PER_ALL_ASSIGNMENTS_F " + // " where TO_CHAR(EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + // " GROUP BY ASSIGNMENT_NUMBER " + // " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + // " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + // " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + // " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + // " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + // " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + // " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + // " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + // " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + // " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + // " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + // " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + // " LEFT OUTER JOIN ( " + // " SELECT " + // " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + // " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + // " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + // " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + // " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE, " + // " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + // " PROY.LOOKUP_CODE AS GRUP_PROYECTO_CODE_JDE, " + // " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + // " FASE.LOOKUP_CODE AS GRUP_FASE_CODE_JDE, " + // " grupos.segment7 as GRUP_PUESTO_JDE, " + // " IMMS.LOOKUP_CODE AS GRUP_PUESTO_CODE_JDE " + // " FROM PAY_PEOPLE_GROUPS GRUPOS " + // " LEFT OUTER JOIN FND_LOOKUP_VALUES PROY ON PROY.MEANING = GRUPOS.SEGMENT2 " + // " AND PROY.lookup_type = 'NVL_PAY_PROYECTO' " + // " AND PROY.LANGUAGE = 'ESA' " + // " LEFT OUTER JOIN FND_LOOKUP_VALUES FASE ON FASE.MEANING = GRUPOS.SEGMENT3 " + // " AND FASE.lookup_type = 'NVL_PAY_FASE_V2' " + // " AND FASE.LANGUAGE = 'ESA' " + // " LEFT OUTER JOIN FND_LOOKUP_VALUES IMMS ON IMMS.MEANING = GRUPOS.segment7 " + // " AND IMMS.lookup_type = 'NVL_PUESTO_IMSS' " + // " AND IMMS.LANGUAGE = 'ESA' " + // " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + // " left outer join ( " + // " select " + // " PPPM.ASSIGNMENT_ID AS METODO_ASIGNACION_ID, " + // " POPN.ORG_PAYMENT_METHOD_NAME AS METODO_NOMBRE, " + // " PTI.PAYMENT_TYPE_NAME AS METODO_TIPO, " + // " PPPM.PRIORITY AS METODO_PRIORIDAD, " + // " PPPM.EFFECTIVE_START_DATE AS METODO_FECHA_EFEC_DESDE, " + // " PPPM.EFFECTIVE_END_DATE AS METODO_FECHA_EFEC_HASTA, " + // " PPPM.AMOUNT AS METODO_IMPORTE_SALDO, " + // " PPPM.PERCENTAGE AS METODO_PORCENTAJE, " + // " PPPM.ATTRIBUTE1 AS METODO_PAGO, " + // " PEA.SEGMENT2 AS METODO_SUCURSAL, " + // " PEA.SEGMENT3 AS METODO_CUENTA, " + // " PEA.SEGMENT4 AS METODO_TIPO_CUENTA_ID, " + // " PEA.SEGMENT5 AS METODO_CLABE " + // " from PAY_PERSONAL_PAYMENT_METHODS_F PPPM " + // " LEFT OUTER JOIN PAY_EXTERNAL_ACCOUNTS PEA " + // " ON PEA.EXTERNAL_ACCOUNT_ID = PPPM.EXTERNAL_ACCOUNT_ID " + // " LEFT OUTER JOIN PAY_ORG_PAYMENT_METHODS_F POPN " + // " ON POPN.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID " + // " LEFT OUTER JOIN PAY_PAYMENT_TYPES PTI " + // " ON PTI.PAYMENT_TYPE_ID = POPN.PAYMENT_TYPE_ID " + // " WHERE TO_CHAR(PPPM.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + // " ) MTP ON MTP.METODO_ASIGNACION_ID = asg.ASIG_CLAVE " + // " WHERE " + // " NVL(PERS_TIPO_DESC,'VACIO' ) like 'Administrativo' " + // " and ASIG_UBICACION_CLAVE IS NOT NULL " + // " group by " + // " PERS_TIPO_DESC, " + // " CASE " + // " WHEN ASIG_UBICACION_CLAVE = '144' or ASIG_UBICACION_CLAVE = '143' or ASIG_UBICACION_CLAVE = '142' THEN 'VILLAHERMOSA' " + // " WHEN ASIG_UBICACION_CLAVE = '148' or ASIG_UBICACION_CLAVE = '147' THEN 'POZA RICA' " + // " WHEN ASIG_UBICACION_CLAVE = '145' THEN 'REYNOSA' " + // " WHEN ASIG_UBICACION_CLAVE = '146' THEN 'VERACRUZ' " + // " WHEN ASIG_UBICACION_CLAVE = '243' THEN 'H3A1' " + // " ELSE '--' " + // " END "; // DataSet conjuntoDatos = baseOracle.Consultar(sentenciaSQL); // if (conjuntoDatos != null) // Valida que no exista error // { // DataTable datos = conjuntoDatos.Tables[0]; // if (datos.Rows.Count != 0) // Valida que existan registrosx // { // List<EntidadIncidenciasGraficas> listaGradoAcademico = new List<EntidadIncidenciasGraficas>(); // foreach (DataRow fila in datos.Rows) // { // GradoAcademico GradoAcademico = new GradoAcademico(); // GradoAcademico.Clave = fila["QUALIFICATION_TYPE_ID"].ToString(); // GradoAcademico.Descripcion = fila["NAME"].ToString(); // GradoAcademico.Clave = GradoAcademico.Clave.ToUpper(); // GradoAcademico.Descripcion = GradoAcademico.Descripcion.ToUpper(); // listaGradoAcademico.Add(GradoAcademico); // } // this.log = listaGradoAcademico.Count.ToString(); // return listaGradoAcademico; // } // else // { // this.log = "VACIO"; // return null; // } // } // else // { // this.log = baseOracle.Log; // return null; // } //} public DataTable ObtenerEmpleadoZona(EntidadIncidenciasGraficas parTipo) { try { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " SELECT " + " CASE " + " WHEN ASIG_UBICACION_CLAVE = '144' or ASIG_UBICACION_CLAVE = '143' or ASIG_UBICACION_CLAVE = '142' THEN 'VILLAHERMOSA' " + " WHEN ASIG_UBICACION_CLAVE = '148' or ASIG_UBICACION_CLAVE = '147' THEN 'POZA RICA' " + " WHEN ASIG_UBICACION_CLAVE = '145' THEN 'REYNOSA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and GRUP_PROYECTO_CODE_JDE = '133' THEN 'CD.CARMEN-PLATAFORMA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and GRUP_PROYECTO_CODE_JDE = 'E07' THEN 'SINEA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and(GRUP_PROYECTO_CODE_JDE != 'E07' or GRUP_PROYECTO_CODE_JDE != '133') THEN 'VERACRUZ' " + " WHEN ASIG_UBICACION_CLAVE = '243' THEN 'H3A1' " + " ELSE '--' " + " END zona , " + " count( PERS_CLAVE) as total " + " FROM " + " ( " + " SELECT " + " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO " + " FROM PER_ALL_PEOPLE_F EMPLEADOS " + " INNER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID " + " ) EMP " + " left outer join ( " + " SELECT " + " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + " GRADOS.NAME AS ASIG_GRADO_DESC, " + " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + " UBICACIONES.location_code AS ASIG_UBICACION_DESC, " + " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + " ASIGNACIONES.EMPLOYMENT_CATEGORY AS ASIG_CATEGORIA_CODIGO, " + " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + " PAYB.name as ASIG_SALARIO_BASE_DESC, " + " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION " + " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + " INNER JOIN ( " + " SELECT " + " ASSIGNMENT_NUMBER, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " max(EFFECTIVE_END_DATE) as asig_date " + " FROM PER_ALL_ASSIGNMENTS_F " + " where TO_CHAR(EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " GROUP BY ASSIGNMENT_NUMBER " + " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + " LEFT OUTER JOIN ( " + " SELECT " + " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE, " + " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + " PROY.LOOKUP_CODE AS GRUP_PROYECTO_CODE_JDE, " + " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + " FASE.LOOKUP_CODE AS GRUP_FASE_CODE_JDE, " + " grupos.segment7 as GRUP_PUESTO_JDE, " + " IMMS.LOOKUP_CODE AS GRUP_PUESTO_CODE_JDE " + " FROM PAY_PEOPLE_GROUPS GRUPOS " + " LEFT OUTER JOIN FND_LOOKUP_VALUES PROY ON PROY.MEANING = GRUPOS.SEGMENT2 " + " AND PROY.lookup_type = 'NVL_PAY_PROYECTO' " + " AND PROY.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES FASE ON FASE.MEANING = GRUPOS.SEGMENT3 " + " AND FASE.lookup_type = 'NVL_PAY_FASE_V2' " + " AND FASE.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES IMMS ON IMMS.MEANING = GRUPOS.segment7 " + " AND IMMS.lookup_type = 'NVL_PUESTO_IMSS' " + " AND IMMS.LANGUAGE = 'ESA' " + " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + " left outer join ( " + " select " + " PPPM.ASSIGNMENT_ID AS METODO_ASIGNACION_ID, " + " POPN.ORG_PAYMENT_METHOD_NAME AS METODO_NOMBRE, " + " PTI.PAYMENT_TYPE_NAME AS METODO_TIPO, " + " PPPM.PRIORITY AS METODO_PRIORIDAD, " + " PPPM.EFFECTIVE_START_DATE AS METODO_FECHA_EFEC_DESDE, " + " PPPM.EFFECTIVE_END_DATE AS METODO_FECHA_EFEC_HASTA, " + " PPPM.AMOUNT AS METODO_IMPORTE_SALDO, " + " PPPM.PERCENTAGE AS METODO_PORCENTAJE, " + " PPPM.ATTRIBUTE1 AS METODO_PAGO, " + " PEA.SEGMENT2 AS METODO_SUCURSAL, " + " PEA.SEGMENT3 AS METODO_CUENTA, " + " PEA.SEGMENT4 AS METODO_TIPO_CUENTA_ID, " + " PEA.SEGMENT5 AS METODO_CLABE " + " from PAY_PERSONAL_PAYMENT_METHODS_F PPPM " + " LEFT OUTER JOIN PAY_EXTERNAL_ACCOUNTS PEA " + " ON PEA.EXTERNAL_ACCOUNT_ID = PPPM.EXTERNAL_ACCOUNT_ID " + " LEFT OUTER JOIN PAY_ORG_PAYMENT_METHODS_F POPN " + " ON POPN.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID " + " LEFT OUTER JOIN PAY_PAYMENT_TYPES PTI " + " ON PTI.PAYMENT_TYPE_ID = POPN.PAYMENT_TYPE_ID " + " WHERE TO_CHAR(PPPM.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) MTP ON MTP.METODO_ASIGNACION_ID = asg.ASIG_CLAVE " + " WHERE " + " NVL(PERS_TIPO_DESC,'VACIO' ) like 'Administrativo' " + " and ASIG_UBICACION_CLAVE IS NOT NULL " + " group by " + " PERS_TIPO_DESC, " + " CASE " + " WHEN ASIG_UBICACION_CLAVE = '144' or ASIG_UBICACION_CLAVE = '143' or ASIG_UBICACION_CLAVE = '142' THEN 'VILLAHERMOSA' " + " WHEN ASIG_UBICACION_CLAVE = '148' or ASIG_UBICACION_CLAVE = '147' THEN 'POZA RICA' " + " WHEN ASIG_UBICACION_CLAVE = '145' THEN 'REYNOSA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and GRUP_PROYECTO_CODE_JDE = '133' THEN 'CD.CARMEN-PLATAFORMA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and GRUP_PROYECTO_CODE_JDE = 'E07' THEN 'SINEA' " + " WHEN ASIG_UBICACION_CLAVE = '146' and(GRUP_PROYECTO_CODE_JDE != 'E07' or GRUP_PROYECTO_CODE_JDE != '133') THEN 'VERACRUZ' " + " WHEN ASIG_UBICACION_CLAVE = '243' THEN 'H3A1' " + " ELSE '--' " + " END "; DataTable datos = baseOracle.Consultar(comando); return datos; } catch (Exception ex) { this.log = ex.Message; return null; } }
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------ //--------------------------DropDownList Muestra Depto de Subordinados para Gerente/ Director ---------------------------------------------------------------------------- public DataTable SupxDepto(string parSupervisor) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = " select b.organization_id as organization_id, c.name as NombreDepto " + " from PER_ALL_POSITIONS p, HR_ALL_ORGANIZATION_UNITS c, PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id " + " where b.SUPERVISOR_ID = :parSupervisor and a.employee_number<>' ' and b.organization_id =c.organization_id and b.position_id=p.position_id and b.organization_id in " + " ( select b.organization_id " + " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id , HR_ALL_ORGANIZATION_UNITS c " + " where b.organization_id =c.organization_id and " + " a.employee_number not in (select employee_number from PER_ALL_PEOPLE_F where person_type_id='1120' and effective_end_date<> TO_DATE ( '12/31/4712' , 'MM / DD / YY' )) " + " group by a.employee_number, a.full_name, a.person_id, c.name, b.organization_id ) " + " group by b.organization_id, c.name " + " order by 1 "; comando.Parameters.Clear(); comando.Parameters.Add(":parSupervisor", OracleType.VarChar).Value = parSupervisor; DataTable datos = baseOracle.Consultar(comando); return datos; }
//----------------------------------------------------------------------------------------- public List<Employee> ObtenerEbs(string parNumeroEmpleado, string parTipo, string parPrimerNombre, string parSegundoNombre, string parApellidoPaterno, string parApellidoMaterno, string parGeneroClave, string parZona, string parCompañia, string parPuestoClave, string parDepartamentoClave, string parTipoNomina, string parCentroCostos, DateTime parContratacionInicio, DateTime parContratacionFin ) { ManagerOracle baseOracle = new ManagerOracle("EBS"); OracleCommand comando = new OracleCommand(); comando.CommandText = "SELECT " + "PERS_CLAVE, " + "PERS_TIPO_CODIGO, " + "PERS_TIPO_DESC, " + "PERS_EMPLEADO_NUMERO, " + "PERS_TITULO, " + "PERS_PRIMER_NOMBRE, " + "PERS_SEGUNDO_NOMBRE, " + "PERS_APELLIDO_PATERNO, " + "PERS_APELLIDO_MATERNO, " + "PERS_NOMBRE_COMPLETO, " + "PERS_GENERO_CLAVE, " + "PERS_GENERO_DESC, " + "PERS_CURP, " + "PERS_NACIONALIDAD_CLAVE, " + "PERS_RFC, " + "PERS_NUMERO_IMSS, " + "PERS_IFE, " + "to_char(PERS_FECHA_NACIMIENTO, 'DD/MM/YYYY') AS PERS_FECHA_NACIMIENTO, " + "PERS_CIUDAD_NACIMIENTO, " + "PERS_ESTADO_NACIMIENTO, " + "PERS_PAIS_NACIMIENTO_CLAVE, " + "TO_CHAR(PERS_FECHA_EFECTIVE_DESDE, 'DD/MM/YYYY') as PERS_FECHA_EFECTIVE_DESDE, " + "to_char(PERS_FECHA_EFECTIVE_HASTA, 'DD/MM/YYYY') AS PERS_FECHA_EFECTIVE_HASTA, " + "PERS_EMAIL, " + "PERS_ESTADO_CIVIL, " + "PERS_CORREO_ELECTRONICO, " + "PERS_ESTADO_CIVIL_DESC, " + "TO_CHAR(PERS_FECHA_CONTRATACION, 'DD/MM/YYYY') AS PERS_FECHA_CONTRATACION, " + "ASIG_CLAVE, " + "ASIG_EMPLEADO_NUMERO, " + "ASIG_PERSONA_CLAVE, " + "TO_CHAR(ASIG_FECHA_INICIO, 'DD/MM/YYYY') as ASIG_FECHA_INICIO, " + "to_char(ASIG_FECHA_FIN, 'DD/MM/YYYY') AS ASIG_FECHA_FIN, " + "ASIG_ORGANIZACION_CLAVE, " + "ASIG_ORGANIZACION_DESC, " + "ASIG_TRABAJO_CLAVE, " + "ASIG_TRABAJO_DESC, " + "ASIG_GRADO_CLAVE, " + "ASIG_GRADO_DESC, " + "ASIG_UBICACION_CLAVE, " + "ASIG_UBICACION_DESC, " + "ASIG_GRUPO_CLAVE, " + "ASIG_GRUPO_DESC, " + "ASIG_PUESTO_CLAVE, " + "ASIG_PUESTO_DESC, " + "ASIG_NOMINA_CLAVE, " + "ASIG_NOMINA_DESC, " + "ASIG_ESTADO_CLAVE, " + "ASIG_ESTADO_DESC, " + "ASIG_CATEGORIA_CODIGO, " + "ASIG_SALARIO_BASE_CLAVE, " + "ASIG_SALARIO_BASE_DESC, " + "INFORMACION_ESTATUTARIA_CLAVE, " + "INFORMACION_ESTATUTARIA_DESC, " + "ASIG_VERSION, " + "ASIG_JEFE_DIRECTO_CLAVE, " + "ASIG_JEFE_DIRECTO_DESC, " + "ASIG_SALARIO_IN, " + "ASIG_SALARIO_OUT, " + "ASIG_TIPO_EMPLEADO, " + "GRUP_CLAVE, " + "GRUP_NOMBRE, " + "GRUP_BANDERA_HABILITADO, " + "GRUP_NOMINA_JDE, " + "GRUP_COMPANIA_JDE, " + "GRUP_PROYECTO_JDE, " + "GRUP_PROYECTO_CODE_JDE, " + "GRUP_FASE_JDE, " + "GRUP_FASE_CODE_JDE, " + "GRUP_PUESTO_JDE, " + "GRUP_PUESTO_CODE_JDE, " + "METODO_ASIGNACION_ID, " + "METODO_NOMBRE, " + "METODO_TIPO, " + "METODO_PRIORIDAD, " + "TO_CHAR(METODO_FECHA_EFEC_DESDE, 'DD/MM/YYYY') as METODO_FECHA_EFEC_DESDE, " + "TO_CHAR(METODO_FECHA_EFEC_HASTA, 'DD/MM/YYYY') AS METODO_FECHA_EFEC_HASTA, " + "METODO_IMPORTE_SALDO, " + "METODO_PORCENTAJE, " + "METODO_PAGO, " + "METODO_SUCURSAL, " + "METODO_CUENTA, " + "METODO_BANCO, " + "METODO_TIPO_CUENTA_ID, " + "METODO_CLABE " + "FROM " + "( " + " SELECT " + " EMPLEADOS.PERSON_ID AS PERS_CLAVE, " + " EMPLEADOS.PERSON_TYPE_ID AS PERS_TIPO_CODIGO, " + " TIPOS.USER_PERSON_TYPE AS PERS_TIPO_DESC, " + " EMPLEADOS.EMPLOYEE_NUMBER AS PERS_EMPLEADO_NUMERO, " + " EMPLEADOS.TITLE AS PERS_TITULO, " + " EMPLEADOS.FIRST_NAME AS PERS_PRIMER_NOMBRE, " + " EMPLEADOS.MIDDLE_NAMES AS PERS_SEGUNDO_NOMBRE, " + " EMPLEADOS.LAST_NAME AS PERS_APELLIDO_PATERNO, " + " EMPLEADOS.PER_INFORMATION1 AS PERS_APELLIDO_MATERNO, " + " EMPLEADOS.FULL_NAME AS PERS_NOMBRE_COMPLETO, " + " EMPLEADOS.SEX AS PERS_GENERO_CLAVE, " + " CASE " + " WHEN EMPLEADOS.SEX = 'M' THEN 'Masculino' " + " WHEN EMPLEADOS.SEX = 'F' THEN 'Femenino' " + " WHEN EMPLEADOS.SEX IS NULL THEN ' ' " + " ELSE 'Sexo desconocido' " + " END PERS_GENERO_DESC, " + " EMPLEADOS.NATIONAL_IDENTIFIER AS PERS_CURP, " + " EMPLEADOS.NATIONALITY AS PERS_NACIONALIDAD_CLAVE, " + " EMPLEADOS.PER_INFORMATION2 AS PERS_RFC, " + " EMPLEADOS.PER_INFORMATION3 AS PERS_NUMERO_IMSS, " + " EMPLEADOS.PER_INFORMATION5 AS PERS_IFE, " + " EMPLEADOS.DATE_OF_BIRTH AS PERS_FECHA_NACIMIENTO, " + " EMPLEADOS.TOWN_OF_BIRTH AS PERS_CIUDAD_NACIMIENTO, " + " EMPLEADOS.REGION_OF_BIRTH AS PERS_ESTADO_NACIMIENTO, " + " EMPLEADOS.COUNTRY_OF_BIRTH AS PERS_PAIS_NACIMIENTO_CLAVE, " + " EMPLEADOS.EFFECTIVE_START_DATE AS PERS_FECHA_EFECTIVE_DESDE, " + " EMPLEADOS.EFFECTIVE_END_DATE AS PERS_FECHA_EFECTIVE_HASTA, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_EMAIL, " + " EMPLEADOS.MARITAL_STATUS AS PERS_ESTADO_CIVIL, " + " EMPLEADOS.EMAIL_ADDRESS AS PERS_CORREO_ELECTRONICO, " + " CASE " + " WHEN EMPLEADOS.MARITAL_STATUS = 'S' THEN 'Solter@' " + " WHEN EMPLEADOS.MARITAL_STATUS = 'M' THEN 'Cazad@' " + " WHEN EMPLEADOS.MARITAL_STATUS IS NULL THEN ' ' " + " ELSE '--' " + " END PERS_ESTADO_CIVIL_DESC, " + " EMPLEADOS.START_DATE as PERS_FECHA_CONTRATACION " + " FROM PER_ALL_PEOPLE_F EMPLEADOS " + " INNER JOIN ( " + " SELECT " + " PERSON_ID, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " MAX(EFFECTIVE_END_DATE) AS EFEC_DATE " + " FROM PER_ALL_PEOPLE_F " + " GROUP BY PERSON_ID " + " ) ACT ON ACT.PERSON_ID = EMPLEADOS.PERSON_ID AND ACT.VER = EMPLEADOS.OBJECT_VERSION_NUMBER AND ACT.EFEC_DATE = EMPLEADOS.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN PER_PERSON_TYPES TIPOS ON TIPOS.PERSON_TYPE_ID = EMPLEADOS.PERSON_TYPE_ID " + ") EMP " + "left outer join ( " + " SELECT " + " ASIGNACIONES.ASSIGNMENT_ID AS ASIG_CLAVE, " + " ASIGNACIONES.ASSIGNMENT_NUMBER AS ASIG_EMPLEADO_NUMERO, " + " ASIGNACIONES.PERSON_ID AS ASIG_PERSONA_CLAVE, " + " ASIGNACIONES.EFFECTIVE_START_DATE AS ASIG_FECHA_INICIO, " + " ASIGNACIONES.EFFECTIVE_END_DATE as ASIG_FECHA_FIN, " + " ASIGNACIONES.ORGANIZATION_ID AS ASIG_ORGANIZACION_CLAVE, " + " ORGANIZACIONES.NAME AS ASIG_ORGANIZACION_DESC, " + " ASIGNACIONES.JOB_ID AS ASIG_TRABAJO_CLAVE, " + " TRABAJOS.NAME AS ASIG_TRABAJO_DESC, " + " ASIGNACIONES.GRADE_ID AS ASIG_GRADO_CLAVE, " + " GRADOS.NAME AS ASIG_GRADO_DESC, " + " ASIGNACIONES.LOCATION_ID AS ASIG_UBICACION_CLAVE, " + " UBICACIONES.location_code AS ASIG_UBICACION_DESC, " + " ASIGNACIONES.PEOPLE_GROUP_ID AS ASIG_GRUPO_CLAVE, " + " GRUPOS.GROUP_NAME AS ASIG_GRUPO_DESC, " + " ASIGNACIONES.POSITION_ID AS ASIG_PUESTO_CLAVE, " + " PUESTOS.NAME AS ASIG_PUESTO_DESC, " + " ASIGNACIONES.PAYROLL_ID AS ASIG_NOMINA_CLAVE, " + " NOMINA.PAYROLL_NAME AS ASIG_NOMINA_DESC, " + " ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID AS ASIG_ESTADO_CLAVE, " + " ESTADOS.USER_STATUS AS ASIG_ESTADO_DESC, " + " ASIGNACIONES.EMPLOYMENT_CATEGORY AS ASIG_CATEGORIA_CODIGO, " + " ASIGNACIONES.PAY_BASIS_ID AS ASIG_SALARIO_BASE_CLAVE, " + " PAYB.name as ASIG_SALARIO_BASE_DESC, " + " ASIGNACIONES.SOFT_CODING_KEYFLEX_ID AS INFORMACION_ESTATUTARIA_CLAVE, " + " ESTATUTARIA.CONCATENATED_SEGMENTS AS INFORMACION_ESTATUTARIA_DESC, " + " ASIGNACIONES.OBJECT_VERSION_NUMBER AS ASIG_VERSION, " + " ASIGNACIONES.SUPERVISOR_ID AS ASIG_JEFE_DIRECTO_CLAVE, " + " JEFATURAS.FULL_NAME AS ASIG_JEFE_DIRECTO_DESC, " + " SAL.PROPOSED_SALARY_N AS ASIG_SALARIO_IN, " + " SAL.ATTRIBUTE2 AS ASIG_SALARIO_OUT, " + " CASE SAL.PROPOSED_SALARY_N WHEN 0 THEN 'O' ELSE 'I' END AS ASIG_TIPO_EMPLEADO " + " FROM PER_ALL_ASSIGNMENTS_F ASIGNACIONES " + " INNER JOIN ( " + " SELECT " + " ASSIGNMENT_NUMBER, " + " MAX(OBJECT_VERSION_NUMBER) AS VER, " + " max(EFFECTIVE_END_DATE) as asig_date " + " FROM PER_ALL_ASSIGNMENTS_F " + " where TO_CHAR(EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " GROUP BY ASSIGNMENT_NUMBER " + " ) ACT ON ACT.ASSIGNMENT_NUMBER = ASIGNACIONES.ASSIGNMENT_NUMBER AND ACT.VER = ASIGNACIONES.OBJECT_VERSION_NUMBER and ACT.ASIG_DATE = ASIGNACIONES.EFFECTIVE_END_DATE " + " LEFT OUTER JOIN ( select " + " ppp.assignment_id " + " ,ppp.pay_proposal_id " + " ,ppp.proposed_salary_n " + " ,ppp.attribute2 " + " FROM per_pay_proposals ppp " + " inner join ( " + " SELECT " + " assignment_id " + " ,MAX (pay_proposal_id) pay_object " + " FROM per_pay_proposals " + " group by " + " assignment_id " + " ) valid " + " on valid.assignment_id = ppp.assignment_id " + " and valid.pay_object = ppp.pay_proposal_id " + " ) SAL ON SAL.ASSIGNMENT_ID = ASIGNACIONES.ASSIGNMENT_ID " + " LEFT OUTER JOIN PER_GRADES GRADOS ON GRADOS.GRADE_ID = ASIGNACIONES.GRADE_ID " + " LEFT OUTER JOIN HR_ALL_ORGANIZATION_UNITS ORGANIZACIONES ON ORGANIZACIONES.ORGANIZATION_ID = ASIGNACIONES.ORGANIZATION_ID " + " LEFT OUTER JOIN HR_SOFT_CODING_KEYFLEX ESTATUTARIA ON ESTATUTARIA.SOFT_CODING_KEYFLEX_ID = ASIGNACIONES.SOFT_CODING_KEYFLEX_ID " + " LEFT OUTER JOIN PER_PAY_BASES PAYB ON PAYB.PAY_BASIS_ID = ASIGNACIONES.PAY_BASIS_ID " + " LEFT OUTER JOIN PER_JOBS TRABAJOS ON TRABAJOS.JOB_ID = ASIGNACIONES.JOB_ID " + " LEFT OUTER JOIN PER_ASSIGNMENT_STATUS_TYPES ESTADOS ON ESTADOS.ASSIGNMENT_STATUS_TYPE_ID = ASIGNACIONES.ASSIGNMENT_STATUS_TYPE_ID " + " LEFT OUTER JOIN HR_LOCATIONS_ALL UBICACIONES ON UBICACIONES.LOCATION_ID = ASIGNACIONES.LOCATION_ID " + " LEFT OUTER JOIN PAY_PEOPLE_GROUPS GRUPOS ON GRUPOS.PEOPLE_GROUP_ID = ASIGNACIONES.PEOPLE_GROUP_ID " + " LEFT OUTER JOIN PER_ALL_POSITIONS PUESTOS ON PUESTOS.POSITION_ID = ASIGNACIONES.POSITION_ID " + " LEFT OUTER JOIN PAY_ALL_PAYROLLS_F NOMINA ON NOMINA.PAYROLL_ID = ASIGNACIONES.PAYROLL_ID " + " LEFT OUTER JOIN PER_ALL_PEOPLE_F JEFATURAS ON JEFATURAS.PERSON_ID = ASIGNACIONES.SUPERVISOR_ID AND TO_CHAR(JEFATURAS.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) ASG ON ASG.ASIG_PERSONA_CLAVE = EMP.PERS_CLAVE " + "LEFT OUTER JOIN ( " + " SELECT " + " GRUPOS.PEOPLE_GROUP_ID AS GRUP_CLAVE, " + " GRUPOS.GROUP_NAME AS GRUP_NOMBRE, " + " GRUPOS.ENABLED_FLAG AS GRUP_BANDERA_HABILITADO, " + " GRUPOS.SEGMENT4 AS GRUP_NOMINA_JDE, " + " GRUPOS.SEGMENT6 AS GRUP_COMPANIA_JDE, " + " GRUPOS.SEGMENT2 AS GRUP_PROYECTO_JDE, " + " PROY.LOOKUP_CODE AS GRUP_PROYECTO_CODE_JDE, " + " GRUPOS.SEGMENT3 AS GRUP_FASE_JDE, " + " FASE.LOOKUP_CODE AS GRUP_FASE_CODE_JDE, " + " grupos.segment7 as GRUP_PUESTO_JDE, " + " IMMS.LOOKUP_CODE AS GRUP_PUESTO_CODE_JDE " + " FROM PAY_PEOPLE_GROUPS GRUPOS " + " LEFT OUTER JOIN FND_LOOKUP_VALUES PROY ON PROY.MEANING = GRUPOS.SEGMENT2 " + " AND PROY.lookup_type = 'NVL_PAY_PROYECTO' " + " AND PROY.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES FASE ON FASE.MEANING = GRUPOS.SEGMENT3 " + " AND FASE.lookup_type = 'NVL_PAY_FASE_V2' " + " AND FASE.LANGUAGE = 'ESA' " + " LEFT OUTER JOIN FND_LOOKUP_VALUES IMMS ON IMMS.MEANING = GRUPOS.segment7 " + " AND IMMS.lookup_type = 'NVL_PUESTO_IMSS' " + " AND IMMS.LANGUAGE = 'ESA' " + " ) GRP ON GRP.GRUP_CLAVE = ASG.ASIG_GRUPO_CLAVE " + "left outer join ( " + " select " + " PPPM.ASSIGNMENT_ID AS METODO_ASIGNACION_ID, " + " POPN.ORG_PAYMENT_METHOD_NAME AS METODO_NOMBRE, " + " PTI.PAYMENT_TYPE_NAME AS METODO_TIPO, " + " PPPM.PRIORITY AS METODO_PRIORIDAD, " + " PPPM.EFFECTIVE_START_DATE AS METODO_FECHA_EFEC_DESDE, " + " PPPM.EFFECTIVE_END_DATE AS METODO_FECHA_EFEC_HASTA, " + " PPPM.AMOUNT AS METODO_IMPORTE_SALDO, " + " PPPM.PERCENTAGE AS METODO_PORCENTAJE, " + " PPPM.ATTRIBUTE1 AS METODO_PAGO, " + " PEA.SEGMENT2 AS METODO_SUCURSAL, " + " PEA.SEGMENT3 AS METODO_CUENTA, " + " PEA.MEANING AS METODO_BANCO, " + " PEA.SEGMENT4 AS METODO_TIPO_CUENTA_ID, " + " PEA.SEGMENT5 AS METODO_CLABE " + " from PAY_PERSONAL_PAYMENT_METHODS_F PPPM " + " LEFT OUTER JOIN ( " + " SELECT * " + " FROM PAY_EXTERNAL_ACCOUNTS PE " + " LEFT OUTER JOIN FND_LOOKUP_VALUES LUV " + " ON PE.SEGMENT1 = LUV.LOOKUP_CODE AND LOOKUP_TYPE LIKE 'MX_BANK' AND LANGUAGE = 'ESA' " + " ) PEA ON PEA.EXTERNAL_ACCOUNT_ID = PPPM.EXTERNAL_ACCOUNT_ID " + " LEFT OUTER JOIN PAY_ORG_PAYMENT_METHODS_F POPN " + " ON POPN.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID " + " LEFT OUTER JOIN PAY_PAYMENT_TYPES PTI " + " ON PTI.PAYMENT_TYPE_ID = POPN.PAYMENT_TYPE_ID " + " WHERE TO_CHAR(PPPM.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " + " ) MTP ON MTP.METODO_ASIGNACION_ID = asg.ASIG_CLAVE " + "WHERE NVL(PERS_EMPLEADO_NUMERO,'VACIO') LIKE :parNumeroEmpleado " + "AND NVL(PERS_PRIMER_NOMBRE,'VACIO') LIKE :parPrimerNombre " + "AND NVL(PERS_SEGUNDO_NOMBRE,'VACIO') LIKE :parSegundoNombre " + "AND NVL(PERS_APELLIDO_PATERNO,'VACIO') LIKE :parApellidoPaterno " + "AND NVL(PERS_APELLIDO_MATERNO,'VACIO') LIKE :parApellidoMaterno " + "AND NVL(PERS_GENERO_CLAVE,'VACIO') LIKE :parGeneroClave " + "AND NVL(GRUP_COMPANIA_JDE,'VACIO') LIKE :parCompania " + "AND NVL(ASIG_PUESTO_CLAVE,'0') LIKE :parPuestoClave " + "AND NVL(ASIG_ORGANIZACION_CLAVE,'0') LIKE :parDepartamentoClave " + "and nvl(PERS_TIPO_CODIGO,'0' ) like :parTipo " + "AND PERS_FECHA_CONTRATACION between :parContratacionInicio AND :parContratacionFin " + "AND NVL(GRUP_NOMINA_JDE, 'VACIO') LIKE :parTipoNomina " + "AND NVL(GRUP_FASE_JDE, 'VACIO') LIKE :parCentroCostos" ; comando.Parameters.Clear(); comando.Parameters.Add(":parNumeroEmpleado", OracleType.VarChar).Value = parNumeroEmpleado; comando.Parameters.Add(":parPrimerNombre", OracleType.VarChar).Value = parPrimerNombre; comando.Parameters.Add(":parSegundoNombre", OracleType.VarChar).Value = parSegundoNombre; comando.Parameters.Add(":parApellidoPaterno", OracleType.VarChar).Value = parApellidoPaterno; comando.Parameters.Add(":parApellidoMaterno", OracleType.VarChar).Value = parApellidoMaterno; comando.Parameters.Add(":parGeneroClave", OracleType.VarChar).Value = parGeneroClave; comando.Parameters.Add(":parCompania", OracleType.VarChar).Value = parCompañia; comando.Parameters.Add(":parPuestoClave", OracleType.VarChar).Value = parPuestoClave; comando.Parameters.Add(":parDepartamentoClave", OracleType.VarChar).Value = parDepartamentoClave; comando.Parameters.Add(":parTipo", OracleType.VarChar).Value = parTipo; comando.Parameters.Add(":parContratacionInicio", OracleType.DateTime).Value = parContratacionInicio; comando.Parameters.Add(":parContratacionFin", OracleType.DateTime).Value = parContratacionFin; comando.Parameters.Add(":parTipoNomina", OracleType.VarChar).Value = parTipoNomina; comando.Parameters.Add(":parCentroCostos", OracleType.VarChar).Value = parCentroCostos; DataTable datos = baseOracle.Consultar(comando); if (datos != null) { if (datos.Rows.Count != 0) { List<Employee> listaEmpleados = new List<Employee>(); foreach (DataRow fila in datos.Rows) { Employee empleado = new Employee(); empleado.Tipo = fila["PERS_TIPO_DESC"].ToString(); empleado.PrimerNombre = fila["PERS_PRIMER_NOMBRE"].ToString(); empleado.SegundoNombre = fila["PERS_SEGUNDO_NOMBRE"].ToString(); empleado.ApellidoPaterno = fila["PERS_APELLIDO_PATERNO"].ToString(); empleado.ApellidoMaterno = fila["PERS_APELLIDO_MATERNO"].ToString(); empleado.Titulo = fila["PERS_TITULO"].ToString(); empleado.Genero = fila["PERS_GENERO_DESC"].ToString(); empleado.Curp = fila["PERS_CURP"].ToString(); empleado.Rfc = fila["PERS_RFC"].ToString(); empleado.Numero = fila["PERS_EMPLEADO_NUMERO"].ToString(); empleado.Imss = fila["PERS_NUMERO_IMSS"].ToString(); empleado.Ife = fila["PERS_IFE"].ToString(); empleado.FechaContratacion = fila["PERS_FECHA_CONTRATACION"].ToString(); empleado.FechaNacimiento = fila["PERS_FECHA_NACIMIENTO"].ToString(); empleado.CiudadNacimiento = fila["PERS_CIUDAD_NACIMIENTO"].ToString(); empleado.EstadoNacimiento = fila["PERS_ESTADO_NACIMIENTO"].ToString(); empleado.PaisNacimiento = fila["PERS_PAIS_NACIMIENTO_CLAVE"].ToString(); empleado.EstadoCivil = fila["PERS_ESTADO_CIVIL_DESC"].ToString(); empleado.CorreoElectronico = fila["PERS_CORREO_ELECTRONICO"].ToString(); empleado.FechaInicioAsignacion = fila["ASIG_FECHA_INICIO"].ToString(); empleado.Organizacion = fila["ASIG_ORGANIZACION_DESC"].ToString(); empleado.Trabajo = fila["ASIG_TRABAJO_DESC"].ToString(); empleado.Grado = fila["ASIG_GRADO_DESC"].ToString(); empleado.Ubicacion = fila["ASIG_UBICACION_DESC"].ToString(); empleado.Puesto = fila["ASIG_PUESTO_DESC"].ToString(); empleado.Nomina = fila["ASIG_NOMINA_DESC"].ToString(); empleado.Estado = fila["ASIG_ESTADO_DESC"].ToString(); empleado.BaseSalario = fila["ASIG_SALARIO_BASE_DESC"].ToString(); empleado.InformacionEstatutaria = fila["INFORMACION_ESTATUTARIA_DESC"].ToString(); empleado.NominaJde = fila["GRUP_NOMINA_JDE"].ToString(); empleado.CompañiaJde = fila["GRUP_COMPANIA_JDE"].ToString(); empleado.ProyectoCodigoJde = fila["GRUP_PROYECTO_CODE_JDE"].ToString(); empleado.ProyectoJde = fila["GRUP_PROYECTO_JDE"].ToString(); empleado.FaseCodigoJde = fila["GRUP_FASE_CODE_JDE"].ToString(); empleado.FaseJde = fila["GRUP_FASE_JDE"].ToString(); empleado.PuestoImss = fila["GRUP_PUESTO_JDE"].ToString(); empleado.NombreMetodoPago = fila["METODO_NOMBRE"].ToString(); empleado.TipoMetodoPago = fila["METODO_TIPO"].ToString(); empleado.PrioridadMetodoPago = fila["METODO_PRIORIDAD"].ToString(); empleado.ImporteSaldoMetodoPago = fila["METODO_IMPORTE_SALDO"].ToString(); empleado.PorcentajeMetodoPago = fila["METODO_PORCENTAJE"].ToString(); empleado.DetalleAdicionalMetodoPago = fila["METODO_PAGO"].ToString(); empleado.SucursalMetodoPago = fila["METODO_SUCURSAL"].ToString(); empleado.CuentaMetodoPago = fila["METODO_CUENTA"].ToString(); empleado.ClabeMetodoPago = fila["METODO_CLABE"].ToString(); empleado.BancoMetodoPago = fila["METODO_BANCO"].ToString(); empleado.JefeDirecto = fila["ASIG_JEFE_DIRECTO_DESC"].ToString(); empleado.TipoNomina = fila["ASIG_TIPO_EMPLEADO"].ToString(); listaEmpleados.Add(empleado); } this.log = listaEmpleados.Count.ToString(); return listaEmpleados; } else { this.log = "VACIO"; return null; } } else { this.log = baseOracle.Log; return null; } }