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;
}
}
