public static List<Employee> getPuestos(string parNumeroEmpleado)
{
Employee filtros = new Employee();
filtros.Numero = parNumeroEmpleado;
NegocioCapitalHumano negocio = new NegocioCapitalHumano();
List<Employee> listaEmpleados2 = negocio.ListaEmpleados2(parNumeroEmpleado, "%", "%", "%", "%", "%", "%", "%", "%", "%", "%", Convert.ToDateTime("01/Jan/1950"), Convert.ToDateTime("01/Jan/9999"));
string puestoempleado = listaEmpleados2[0].Puesto.ToString();
return listaEmpleados2;
}
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<Employee> EmpleadosTodosxCompania(string xcompania)
{
TablaEmpIncidencias tabla = new TablaEmpIncidencias();
List<Employee> lista = tabla.allempleadosxcompania(xcompania);
if (lista != null)
{
return lista;
}
else
{
Employee error = new Employee();
error.Log = tabla.Log;
return this.Error(error);
}
}
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;
}
}
protected List<Employee> Error(Employee parError)
{
List<Employee> listaError = new List<Employee>();
listaError.Add(parError);
return listaError;
}
//--------------------------------------------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;
}
}
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 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;
}
}
//------------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------OBTENER nombresupervisor JEFE-------------------------------------------------------------------------------------------------------
public List<Employee> ObtenerNomSupervisor(string parPersonid)
{
ManagerOracle baseOracle = new ManagerOracle("EBS");
OracleCommand comando = new OracleCommand();
comando.CommandText = " select b.supervisor_id as jefe_inmediato, a.employee_number as employee_number_jefe, " +
" a.full_name as full_name, a.person_id " +
" from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id " +
" where a.person_id = :parPersonid and a.employee_number is not null " +
" 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.CommandText = " select b.supervisor_id as jefe_inmediato, a.employee_number as employee_number_jefe, " +
// " a.full_name as full_name, a.person_id " +
// " from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id " +
// " where a.person_id = :parPersonid order by b.effective_start_date ";
comando.Parameters.Clear();
comando.Parameters.Add(":parPersonid", OracleType.VarChar).Value = parPersonid;
DataTable datos = baseOracle.Consultar(comando);
if (datos != null)
{
if (datos.Rows.Count != 0)
{
List<Employee> listaPersonaName = new List<Employee>();
foreach (DataRow fila in datos.Rows)
{
Employee empleadoid = new Employee();
empleadoid.SupervisorName = fila["full_name"].ToString();
empleadoid.SupervisorNoemp = fila["employee_number_jefe"].ToString();
listaPersonaName.Add(empleadoid);
}
this.log = listaPersonaName.Count.ToString();
return listaPersonaName;
}
else
{
this.log = "VACIO";
return null;
}
}
else
{
this.log = baseOracle.Log;
return null;
}
}
//------------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------OBTENER supervisorid JEFE-------------------------------------------------------------------------------------------------------
public List<Employee> ObtenerPersonaid(string parNumeroEmpleado)
{
ManagerOracle baseOracle = new ManagerOracle("EBS");
OracleCommand comando = new OracleCommand();
comando.CommandText = " select b.supervisor_id as jefe_inmediato, a.employee_number as employee_number, " +
" a.full_name as full_name, a.person_id, round(months_between(sysdate, a.START_DATE),2) as mestranscurridos " +
" from PER_ALL_ASSIGNMENTS_F b inner join PER_ALL_PEOPLE_F a on b.person_id=a.person_id " +
" where a.employee_number = :parNumeroEmpleado and b.supervisor_id is not null and TO_CHAR(b.EFFECTIVE_END_DATE,'YYYY-MM-DD' ) = '4712-12-31' " +
" and TO_CHAR(a.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(":parNumeroEmpleado", OracleType.VarChar).Value = parNumeroEmpleado;
DataTable datos = baseOracle.Consultar(comando);
if (datos != null)
{
if (datos.Rows.Count != 0)
{
List<Employee> listaPersona = new List<Employee>();
foreach (DataRow fila in datos.Rows)
{
Employee empleadoid = new Employee();
empleadoid.Supervisor = fila["jefe_inmediato"].ToString();
empleadoid.mesContratacion = fila["mestranscurridos"].ToString();
listaPersona.Add(empleadoid);
}
this.log = listaPersona.Count.ToString();
return listaPersona;
}
else
{
this.log = "VACIO";
return null;
}
}
else
{
this.log = baseOracle.Log;
return null;
}
}