Empleados (alp_cat_hcm_employees)
1. Modulo
Recursos Humanos (HCM)
2. Descripción
Human Capital Management: Maestro de colaboradores, empleados, puestos e identificadores de nómina.
3. Columnas
| No. | Columna | Descripción |
|---|---|---|
| 1 | person_id | Identificador interno de la persona (empleado) en HCM. |
| 2 | assignment_id | Identificador de la asignación laboral del empleado. |
| 3 | person_number | Número de empleado en Oracle HCM. |
| 4 | user_person_type | Tipo de persona (Employee, Contingent Worker, etc.). |
| 5 | assignment_status_type_code | Código del estado de la asignación laboral. |
| 6 | assignment_status_type | Descripción del estado de la asignación laboral. |
| 7 | last_name | Apellido paterno del empleado. |
| 8 | previous_last_name | Apellido materno o apellido previo del empleado. |
| 9 | first_name | Nombre del empleado. |
| 10 | middle_names | Segundos nombres del empleado. |
| 11 | full_name | Nombre completo concatenado del empleado. |
| 12 | region_of_birth | Región o estado de nacimiento del empleado. |
| 13 | age | Edad del empleado calculada a partir de la fecha de nacimiento. |
| 14 | country_of_birth | País de nacimiento del empleado. |
| 15 | date_of_birth | Fecha de nacimiento del empleado. |
| 16 | sex | Género del empleado. |
| 17 | marital_status_code | Código del estado civil del empleado. |
| 18 | marital_status | Descripción del estado civil del empleado. |
| 19 | date_start | Fecha de inicio de relación laboral del empleado. |
| 20 | actual_termination_date | Fecha real de terminación laboral del empleado. |
| 21 | primary_email_address | Correo electrónico principal del empleado. |
| 22 | primary_phone | Teléfono principal del empleado. |
| 23 | checa_tarjeta | Indicador de uso de tarjeta de checado. |
| 24 | ubicacion_real | Ubicación física real del empleado. |
| 25 | ubicacion_administrativa | Ubicación administrativa del empleado. |
| 26 | grupo_reloj_chec | Grupo asignado para control de checador/reloj. |
| 27 | legal_entity_identifier | Identificador de la entidad legal asignada al empleado. |
| 28 | legal_entity_code | Código de la entidad legal. |
| 29 | legal_entity | Nombre de la entidad legal. |
| 30 | bu_code | Código de Business Unit. |
| 31 | bu_name | Nombre de Business Unit. |
| 32 | seccion_codigo | Código de sección organizacional. |
| 33 | seccion | Descripción de la sección organizacional. |
| 34 | area_codigo | Código del área organizacional. |
| 35 | area | Descripción del área organizacional. |
| 36 | area_op_codigo | Código del área operativa. |
| 37 | area_op | Descripción del área operativa. |
| 38 | department_code | Código del departamento. |
| 39 | department | Nombre del departamento. |
| 40 | ass_attribute15 | Atributo flexible de asignación (uso personalizado). |
| 41 | assignment_name | Nombre de la asignación laboral. |
| 42 | assignment_number | Número de asignación laboral. |
| 43 | sindicato | Nombre del sindicato asociado al empleado. |
| 44 | seccion_sindicato | Sección sindical del empleado. |
| 45 | primary_assignment_flag | Indicador de asignación principal del empleado. |
| 46 | labour_union_member_flag | Indicador de pertenencia a sindicato. |
| 47 | default_expense_account_code | Código de cuenta contable por defecto (centro de costos). |
| 48 | default_expense_account | Descripción de la cuenta contable por defecto. |
| 49 | cc_compania_code | Código de compañía en estructura contable. |
| 50 | cc_compania | Descripción de compañía contable. |
| 51 | cc_unidad_operativa_code | Código de unidad operativa contable. |
| 52 | cc_unidad_operativa | Descripción de unidad operativa contable. |
| 53 | cc_canal_code | Código de canal contable. |
| 54 | cc_canal | Descripción de canal contable. |
| 55 | cc_categoria_code | Código de categoría contable. |
| 56 | cc_categoria | Descripción de categoría contable. |
| 57 | cc_centro_costo_code | Código de centro de costo. |
| 58 | cc_centro_costo | Descripción de centro de costo. |
| 59 | cc_cuenta_code | Código de cuenta contable. |
| 60 | cc_cuenta | Descripción de cuenta contable. |
| 61 | cc_filiales_code | Código de filiales en estructura contable. |
| 62 | cc_filiales | Descripción de filiales contables. |
| 63 | cc_futuro_code | Código de cuenta futura contable. |
| 64 | cc_futuro | Descripción de cuenta futura contable. |
| 65 | employee_category_code | Código de categoría de empleado. |
| 66 | employee_category | Descripción de categoría de empleado. |
| 67 | employment_category_code | Código de tipo de empleo. |
| 68 | employment_category | Descripción del tipo de empleo. |
| 69 | email_address | Correo electrónico alterno del empleado. |
| 70 | phone_number | Número telefónico alterno del empleado. |
| 71 | bank_name | Nombre del banco del empleado. |
| 72 | bank_account_num | Número de cuenta bancaria del empleado. |
| 73 | highest_education_level_code | Código del nivel educativo más alto. |
| 74 | highest_education_level | Descripción del nivel educativo. |
| 75 | address_line_1 | Dirección línea 1 del empleado. |
| 76 | address_line_2 | Dirección línea 2 del empleado. |
| 77 | town_or_city | Ciudad del domicilio del empleado. |
| 78 | addl_address_attribute2 | Atributo adicional de dirección. |
| 79 | postal_code | Código postal del domicilio. |
| 80 | region_1 | Región o estado 1 del domicilio. |
| 81 | region_2 | Región o estado 2 del domicilio. |
| 82 | address_id | Identificador de dirección del empleado. |
| 83 | ubicacion_codigo | Código de ubicación física del empleado. |
| 84 | ubicacion | Descripción de la ubicación física del empleado. |
| 85 | hourly_salaried_code | Código de tipo de salario (hora o sueldo). |
| 86 | hourly_salaried | Descripción del tipo de salario. |
| 87 | manager_person_number | Número de empleado del manager directo. |
| 88 | manager_name | Nombre completo del manager. |
| 89 | user_oracle | Usuario Oracle asociado al empleado. |
| 90 | collective_agreement_name | Nombre del contrato colectivo aplicable. |
| 91 | puesto_code | Código del puesto del empleado. |
| 92 | puesto | Nombre del puesto del empleado. |
| 93 | grade_code | Código del nivel o grado del puesto. |
| 94 | grade_name | Descripción del nivel o grado del puesto. |
| 95 | imss | Número de afiliación IMSS del empleado. |
| 96 | curp | CURP del empleado. |
| 97 | rfc | RFC del empleado. |
| 98 | issste | Número ISSSTE del empleado. |
| 99 | fgad | Identificador FGAD del empleado. |
| 100 | msd | Identificador MSD del empleado. |
| 101 | nid | Identificador nacional adicional del empleado. |
| 102 | position_code | Código del puesto organizacional. |
| 103 | position_name | Nombre completo del puesto estructurado. |
| 104 | contract_type_code | Código del tipo de contrato. |
| 105 | contract_type | Descripción del tipo de contrato. |
| 106 | contract_number | Número de contrato del empleado. |
| 107 | subordinados | Número de empleados subordinados del manager. |
| 108 | creation_date | Fecha de creación del registro del empleado. |
| 109 | created_by | Usuario que creó el registro. |
| 110 | last_update_date | Fecha de última actualización del registro. |
| 111 | last_updated_by | Usuario que realizó la última actualización. |
| 112 | last_update_login | Sesión o login de la última actualización. |
| 113 | puesto_director | Puesto del director asociado en la jerarquía. |
| 114 | nomina_director | Número de nómina del director. |
| 115 | director | Nombre del director responsable. |
| 116 | status_as400 | Estado del empleado en sistema AS400. |
| 117 | fecha_baja_as400 | Fecha de baja del empleado en AS400 o terminación laboral. |
4. Objesto Databricks - Oracle
Nota de Arquitectura: Convención de Nomenclatura
Las entidades listadas en este documento corresponden a la capa final de datos en Databricks. Para identificar la persistencia y el aislamiento de los datos según el ciclo de vida del software, se implementa la siguiente estrategia de sufijos por entorno:
- 🥉 Desarrollo (DEV): Sufijo
_dev(ej.nombre_tabla_dev). - 🥈 Pruebas (UAT): Sufijo
_stg(ej.nombre_tabla_stg). - 🥇 Producción (PROD): Sufijo
_prd(ej.nombre_tabla_prd).
| No. | Databricks | Oracle Fusion | Descripción Operativa / Módulo |
|---|---|---|---|
| 1 | as4_nomperso | as4_nomperso | Información de los Empleados en AS400 |
| 2 | cmp_salary | cmp_salary | Historial y componentes de compensaciones y salarios de empleados. |
| 3 | fnd_flex_value_sets | fnd_flex_value_sets | Grupos de valores válidos de los segmentos contables (Value Sets). |
| 4 | fscm_as_valuesetvaluespvo | fnd_flex_values | Valores individuales asignados a los segmentos contables. |
| 5 | fscm_as_valuesetvaluestlpvo | fnd_flex_values_tl | Textos descriptivos traducidos de los valores contables. |
| 6 | fscm_fin_aes_lookupvaluesextractpvo | fnd_lookup_values | Catálogo maestro de códigos y Lookups generales del sistema financiero. |
| 7 | fscm_fin_aes_lookupvaluestlextractpvo | fnd_lookup_values_tl | Traducciones de los códigos Lookup generales de finanzas. |
| 8 | fscm_fin_gl_codecombinationextractpvo | gl_code_combinations | Combinaciones contables completas de la cuenta mayor (Cuentas Contables). |
| 9 | hcm_hcm_user_userextractpvo | per_users | Usuarios del sistema vinculados a Recursos Humanos (HCM). |
| 10 | hcm_person_personnationalidentifierpvo | per_national_identifiers | Identificadores nacionales oficiales de los trabajadores (CURP, etc.). |
| 11 | hr_all_organization_units | hr_all_organization_units | Unidades organizacionales, departamentos y estructuras internas. |
| 12 | hr_all_positions_f | hr_all_positions_f | Plazas e histórico de puestos de trabajo de la organización. |
| 13 | hr_legal_entities | hr_legal_entities | Empresas y entidades legales constituidas ante la ley. |
| 14 | hr_locations_all | hr_locations_all | Oficinas, plantas, centros de distribución y ubicaciones corporativas. |
| 15 | hr_locations_all_tl | hr_locations_all_tl | Descripciones traducidas de las ubicaciones corporativas. |
| 16 | hr_organization_information_f | hr_organization_information_f | Detalles y clasificaciones adicionales de las unidades organizacionales. |
| 17 | pay_bank_accounts | pay_bank_accounts | Cuentas bancarias de la compañía y de terceros para dispersión. |
| 18 | pay_person_pay_methods_f | pay_person_pay_methods_f | Métodos de pago configurados para los empleados (Nómina). |
| 19 | pay_rel_groups_dn | pay_rel_groups_dn | Relaciones y grupos desnormalizados de procesamiento de nómina. |
| 20 | per_addresses_f | per_addresses_f | Direcciones particulares e históricas de los empleados. |
| 21 | per_all_assignments_f | per_all_assignments_f | Asignaciones laborales de los empleados (Puesto, Departamento, Salario). |
| 22 | per_all_people_f | per_all_people_f | Datos maestros e históricos del personal (Nombre, fecha nacimiento). |
| 23 | per_col_agreements_f_tl | per_col_agreements_f_tl | Contratos colectivos de trabajo y acuerdos sindicales. |
| 24 | per_contracts_f | per_contracts_f | Contratos laborales individuales vigentes e históricos. |
| 25 | per_email_addresses | per_email_addresses | Direcciones de correo electrónico (institucionales y personales). |
| 26 | per_employees_x | per_employees_x | Vista rápida de datos de empleados activos. |
| 27 | per_grades_f | per_grades_f | Tabulador de grados o niveles de puestos de la empresa. |
| 28 | per_grades_f_tl | per_grades_f_tl | Descripciones multi-idioma de los grados de puestos. |
| 29 | per_jobs_f | per_jobs_f | Catálogo de puestos lógicos corporativos. |
| 30 | per_jobs_f_tl | per_jobs_f_tl | Títulos comerciales de los puestos en diferentes idiomas. |
| 31 | per_manager_hrchy_cf | per_manager_hrchy_cf | Estructura y jerarquía desnormalizada de jefes y reportes (Organigrama). |
| 32 | per_people_legislative_f | per_people_legislative_f | Información legislativa y atributos personales de los empleados, asociados al país o legislación aplicable. |
| 33 | per_periods_of_service | per_periods_of_service | Periodos de servicio, fechas de alta y bajas del personal. |
| 34 | per_person_names_f | per_person_names_f | Formatos de nombres del personal (Legal, Preferido, Anterior). |
| 35 | per_person_types_tl | per_person_types_tl | Tipificaciones de personal (Empleado, Contratista, Ex-empleado). |
| 36 | per_persons | per_persons | Entidad base de personas dentro del ecosistema HCM. |
| 37 | per_phones | per_phones | Números de teléfono corporativos, móviles y de emergencia. |
| 38 | xle_entity_profiles | xle_entity_profiles | Perfiles e información regulatoria de las Entidades Legales. |
5. Querys
- Databricks DEV
- Databricks UAT
- Databricks PROD
- Oracle
with alp_asignacion as
( select ROW_NUMBER() OVER (partition by person_id order by assignment_id desc) reg
, person_id
, assignment_id
, assignment_name
, assignment_number
, assignment_type
, assignment_status_type
, legal_entity_id
, business_unit_id
, union_id
, organization_id
, location_id
, employee_category
, employment_category
, primary_assignment_flag
, labour_union_member_flag
, hourly_salaried_code
, default_code_comb_id
, collective_agreement_id
, position_id
, job_id
, grade_id
, contract_id
, ass_attribute1
, ass_attribute2
, ass_attribute3
, ass_attribute4
, ass_attribute5
, ass_attribute6
, ass_attribute8
, ass_attribute9
, ass_attribute15
from cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_dev
where assignment_type = 'E'
and current_date() between effective_start_date
and effective_end_date)
, alp_periods_of_service as
( select ROW_NUMBER() OVER (partition by person_id order by period_of_service_id desc) max_period
, ROW_NUMBER() OVER (partition by person_id order by period_of_service_id) min_period
, person_id
, date_start
, actual_termination_date
from cat_master_oracle_fusion.sch_gold_layer.per_periods_of_service_dev)
, alp_addresses as
( select ROW_NUMBER() OVER (partition by ppauf.person_id order by ppauf.effective_end_date desc, ppauf.person_addr_usage_id desc, paf.effective_end_date desc) reg
, ppauf.person_id
, paf.address_line_1
, paf.address_line_2
, paf.town_or_city
, paf.addl_address_attribute2
, paf.postal_code
, paf.region_1
, paf.region_2
, paf.address_id
from cat_master_oracle_fusion.sch_gold_layer.per_person_addr_usages_f_dev ppauf
left join cat_master_oracle_fusion.sch_gold_layer.per_addresses_f_dev paf
on ppauf.address_id = paf.address_id)
, alp_sindicatos as
( select haou.organization_id
, hoif.org_information2
, haou.name
from cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haou
join cat_master_oracle_fusion.sch_gold_layer.hr_organization_information_f_dev hoif
on haou.organization_id = hoif.organization_id
where current_date() between hoif.effective_start_date
and hoif.effective_end_date)
, alp_emails as
( select ROW_NUMBER() OVER (partition by person_id order by email_address_id desc) reg
, person_id
, email_address
from cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_dev
where email_address not like '%@alpura.com'
and current_date() between date_from
and date_to)
, alp_phones as
( select ROW_NUMBER() OVER (partition by person_id order by phone_id desc) reg
, person_id
, phone_number
from cat_master_oracle_fusion.sch_gold_layer.per_phones_dev
where phone_type = 'H1'
and current_date() between date_from
and date_to)
, alp_bancos as
( select ROW_NUMBER() OVER (partition by prgd.assignment_id order by pppmf.personal_payment_method_id desc) reg
, prgd.assignment_id
, pba.bank_name
, pba.bank_account_num_electronic
from cat_master_oracle_fusion.sch_gold_layer.pay_rel_groups_dn_dev prgd
join cat_master_oracle_fusion.sch_gold_layer.pay_person_pay_methods_f_dev pppmf
on prgd.payroll_relationship_id = pppmf.payroll_relationship_id
and pppmf.percentage is not null
join cat_master_oracle_fusion.sch_gold_layer.pay_bank_accounts_dev pba
on pppmf.bank_account_id = pba.bank_account_id
and pppmf.percentage is not null
and current_date() between prgd.start_date
and prgd.end_date)
, alp_managers as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, pmhc.person_id
, pmhc.assignment_id
, papf.person_number
, mpapf.person_number manager_person_number
, mgr.last_name
|| nvl2(mgr.previous_last_name,' ' || mgr.previous_last_name,'')
|| ' ' || mgr.first_name
|| nvl2(mgr.middle_names,' ' || mgr.middle_names,'') manager_name
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_dev papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_dev paaf
on paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_dev pmhc
on paaf.assignment_id = pmhc.assignment_id
and paaf.person_id = pmhc.person_id
and current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_dev mgr
on pmhc.level1_manager_id = mgr.person_id
and mgr.name_type = 'GLOBAL'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_dev mpapf
on mgr.person_id = mpapf.person_id
and current_date() between mpapf.effective_start_date
and mpapf.effective_end_date
where current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_jobs as
( select pjf.job_id
, pjf.job_code
, pjft.name
, ROW_NUMBER() OVER (partition by pjf.job_id order by pjf.insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_dev pjf
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_dev pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
where current_date() between pjf.effective_start_date
and pjf.effective_end_date
and current_date() between pjft.effective_start_date
and pjft.effective_end_date)
, alp_grades as
( select pgf.grade_id
, pgf.grade_code
, pgft.name
from cat_master_oracle_fusion.sch_gold_layer.per_grades_f_dev pgf
join cat_master_oracle_fusion.sch_gold_layer.per_grades_f_tl_dev pgft
on pgf.grade_id = pgft.grade_id
and pgft.language = 'E'
where current_date() between pgf.effective_start_date
and pgf.effective_end_date
and current_date() between pgft.effective_start_date
and pgft.effective_end_date)
, alp_documentos as
( select ROW_NUMBER() OVER ( partition by nationalidentifierpeopersonid
, nationalidentifierpeonationalidentifiertype
order by nationalidentifierid desc) reg
, nationalidentifierpeopersonid person_id
, nationalidentifierpeonationalidentifiertype national_identifier_type
, nationalidentifierpeonationalidentifiernumber national_identifier_number
from cat_master_oracle_fusion.sch_gold_layer.hcm_person_personnationalidentifierpvo_dev)
, alp_subalternos as
( select pmhc.level1_manager_id person_id
, count(*) subordinados
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_dev pmhc
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_dev papf
on pmhc.person_id = papf.person_id
and current_date() between papf.effective_start_date
and papf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_dev mgr
on pmhc.person_id = mgr.person_id
and mgr.name_type = 'MX'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_dev paaf
on pmhc.person_id = paaf.person_id
and pmhc.assignment_id = paaf.assignment_id
and paaf.assignment_status_type = 'ACTIVE'
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
where current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
group by pmhc.level1_manager_id)
, alp_directores as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, paaf.person_id
, pjft.name puesto_director
, papf.person_number nomina_director
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') director
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_dev papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_dev paaf
on papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_dev pjf
on paaf.job_id = pjf.job_id
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_dev pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_dev ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'MX'
where paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and (UPPER(pjft.name) like '%DIR %'
or UPPER(pjft.name) like '%DIRECTOR%')
and UPPER(pjft.name) not like '%ASISTENTE%'
and current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_dirtores_responsables as
( select pmhc.person_id
, pmhc.assignment_id
, ROW_NUMBER() OVER (partition by pmhc.person_id, pmhc.assignment_id order by pmhc.insertion_date desc) reg
, nvl(ad1.puesto_director,nvl(ad2.puesto_director,nvl(ad3.puesto_director,nvl(ad4.puesto_director,nvl(ad5.puesto_director,nvl(ad6.puesto_director,nvl(ad7.puesto_director,nvl(ad8.puesto_director,nvl(ad9.puesto_director,nvl(ad10.puesto_director,nvl(ad11.puesto_director,nvl(ad12.puesto_director,nvl(ad13.puesto_director,nvl(ad14.puesto_director,nvl(ad15.puesto_director,nvl(ad16.puesto_director,nvl(ad17.puesto_director,nvl(ad18.puesto_director,nvl(ad19.puesto_director,nvl(ad20.puesto_director,'')))))))))))))))))))) puesto_director
, nvl(ad1.nomina_director,nvl(ad2.nomina_director,nvl(ad3.nomina_director,nvl(ad4.nomina_director,nvl(ad5.nomina_director,nvl(ad6.nomina_director,nvl(ad7.nomina_director,nvl(ad8.nomina_director,nvl(ad9.nomina_director,nvl(ad10.nomina_director,nvl(ad11.nomina_director,nvl(ad12.nomina_director,nvl(ad13.nomina_director,nvl(ad14.nomina_director,nvl(ad15.nomina_director,nvl(ad16.nomina_director,nvl(ad17.nomina_director,nvl(ad18.nomina_director,nvl(ad19.nomina_director,nvl(ad20.nomina_director,'')))))))))))))))))))) nomina_director
, nvl(ad1.director,nvl(ad2.director,nvl(ad3.director,nvl(ad4.director,nvl(ad5.director,nvl(ad6.director,nvl(ad7.director,nvl(ad8.director,nvl(ad9.director,nvl(ad10.director,nvl(ad11.director,nvl(ad12.director,nvl(ad13.director,nvl(ad14.director,nvl(ad15.director,nvl(ad16.director,nvl(ad17.director,nvl(ad18.director,nvl(ad19.director,nvl(ad20.director,'')))))))))))))))))))) director
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_dev pmhc
left join alp_directores ad1
on pmhc.level1_manager_id = ad1.person_id
and ad1.reg = 1
left join alp_directores ad2
on pmhc.level2_manager_id = ad2.person_id
and ad2.reg = 1
left join alp_directores ad3
on pmhc.level3_manager_id = ad3.person_id
and ad3.reg = 1
left join alp_directores ad4
on pmhc.level4_manager_id = ad4.person_id
and ad4.reg = 1
left join alp_directores ad5
on pmhc.level5_manager_id = ad5.person_id
and ad5.reg = 1
left join alp_directores ad6
on pmhc.level6_manager_id = ad6.person_id
and ad6.reg = 1
left join alp_directores ad7
on pmhc.level7_manager_id = ad7.person_id
and ad7.reg = 1
left join alp_directores ad8
on pmhc.level8_manager_id = ad8.person_id
and ad8.reg = 1
left join alp_directores ad9
on pmhc.level9_manager_id = ad9.person_id
and ad9.reg = 1
left join alp_directores ad10
on pmhc.level10_manager_id = ad10.person_id
and ad10.reg = 1
left join alp_directores ad11
on pmhc.level11_manager_id = ad11.person_id
and ad11.reg = 1
left join alp_directores ad12
on pmhc.level2_manager_id = ad12.person_id
and ad12.reg = 1
left join alp_directores ad13
on pmhc.level3_manager_id = ad13.person_id
and ad13.reg = 1
left join alp_directores ad14
on pmhc.level4_manager_id = ad14.person_id
and ad14.reg = 1
left join alp_directores ad15
on pmhc.level5_manager_id = ad15.person_id
and ad15.reg = 1
left join alp_directores ad16
on pmhc.level6_manager_id = ad16.person_id
and ad16.reg = 1
left join alp_directores ad17
on pmhc.level7_manager_id = ad17.person_id
and ad17.reg = 1
left join alp_directores ad18
on pmhc.level8_manager_id = ad18.person_id
and ad18.reg = 1
left join alp_directores ad19
on pmhc.level9_manager_id = ad19.person_id
and ad19.reg = 1
left join alp_directores ad20
on pmhc.level10_manager_id = ad20.person_id
and ad20.reg = 1
where current_date between pmhc.effective_start_date
and pmhc.effective_end_date)
, alp_cat_fnd_lookups as
( select flv.lookuptype type
, flv.lookupcode code
, flvt.meaning
, flv.setid set_id
, flv.tag
, flv.viewapplicationid view_appl_id
, flv.enabledflag enabled
, flv.startdateactive start_date
, flv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_dev flv
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_dev flvt
on flv.lookuptype = flvt.lookuptype
and flv.lookupcode = flvt.lookupcode
and flv.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets_dev ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo_dev ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo_dev ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_legislative_info as
( select person_id
, marital_status
, highest_education_level
, sex
, ROW_NUMBER() OVER (partition by person_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_people_legislative_f_dev
where current_date() between effective_start_date
and effective_end_date)
, alp_positions_all as
( select business_unit_id
, organization_id
, position_id
, location_id
, job_id
, position_code
, ROW_NUMBER() OVER (partition by business_unit_id, organization_id, position_id, location_id, job_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.hr_all_positions_f_dev
where current_date() between effective_start_date
and effective_end_date)
, alp_as400 as
( SELECT
CAST(numemp AS STRING) AS numemp,
'Inactiva' AS stsemp,
CASE
WHEN CAST(fecbajn AS STRING) = '0' THEN NULL
ELSE date_format(
try_to_timestamp(CAST(fecbajn AS STRING), 'yyyyMMdd'),
'yyyy-MM-dd HH:mm:ss'
)
END AS fecbajn
FROM cat_master_alpura.sch_silver_layer.as4_nomperso
WHERE stsemp = 'B')
, alp_empleados as
( select ROW_NUMBER() OVER (partition by papf.person_id order by papf.person_id desc) reg
, papf.person_id
, paaf.assignment_id
, papf.person_number
, pptt.user_person_type
, paaf.assignment_status_type assignment_status_type_code
, flvtpass.meaning assignment_status_type
, ppnf.last_name
, ppnf.previous_last_name
, ppnf.first_name
, ppnf.middle_names
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') full_name
, pp.region_of_birth
, floor(months_between(current_date(), pp.date_of_birth) / 12) age
, flvt.meaning country_of_birth
, pp.date_of_birth
, pplf.sex
, pplf.marital_status marital_status_code
, flvtms.meaning marital_status
, ppos_min.date_start
, ppos_max.actual_termination_date
, peap.email_address primary_email_address
, ppp.phone_number primary_phone
, paaf.ass_attribute1 checa_tarjeta
, paaf.ass_attribute5 ubicacion_real
, paaf.ass_attribute6 ubicacion_administrativa
, paaf.ass_attribute8 grupo_reloj_chec
, xep.legal_entity_identifier
, flvtle.meaning legal_entity_code
, haoule.name legal_entity
, haoubu.name bu_code
, paaf.ass_attribute9 bu_name
, get(split(paaf.ass_attribute2, '-'), 0) seccion_codigo
, get(split(paaf.ass_attribute2, '-'), 1) seccion
, get(split(paaf.ass_attribute3, '-'), 0) area_codigo
, get(split(paaf.ass_attribute3, '-'), 1) area
, get(split(paaf.ass_attribute4, '-'), 0) area_op_codigo
, get(split(paaf.ass_attribute4, '-'), 1) area_op
, haoudepto.attribute_number1 department_code
, haoudep.name department
, paaf.ass_attribute15
, paaf.assignment_name
, paaf.assignment_number
, asi.name sindicato
, asi.org_information2 seccion_sindicato
, paaf.primary_assignment_flag
, paaf.labour_union_member_flag
, ascgp.value
|| '.'
|| ascuo.value
|| '.'
|| ascc.value
|| '.'
|| ascca.value
|| '.'
|| asccc.value
|| '.'
|| ascta.value
|| '.'
|| ascf.value
|| '.'
|| ascfut.value default_expense_account_code
, ascgp.description
|| '.'
|| ascuo.description
|| '.'
|| ascc.description
|| '.'
|| ascca.description
|| '.'
|| asccc.description
|| '.'
|| ascta.description
|| '.'
|| ascf.description
|| '.'
|| ascfut.description default_expense_account
, ascgp.value cc_compania_code
, ascgp.description cc_compania
, ascuo.value cc_unidad_operativa_code
, ascuo.description cc_unidad_operativa
, ascc.value cc_canal_code
, ascc.description cc_canal
, ascca.value cc_categoria_code
, ascca.description cc_categoria
, asccc.value cc_centro_costo_code
, asccc.description cc_centro_costo
, ascta.value cc_cuenta_code
, ascta.description cc_cuenta
, ascf.value cc_filiales_code
, ascf.description cc_filiales
, ascfut.value cc_futuro_code
, ascfut.description cc_futuro
, paaf.employee_category employee_category_code
, flvtct.meaning employee_category
, paaf.employment_category employment_category_code
, flvtec.meaning employment_category
, ae.email_address email_address
, ap.phone_number phone_number
, ab.bank_name
, ab.bank_account_num_electronic bank_account_num
, pplf.highest_education_level highest_education_level_code
, flvthelc.meaning highest_education_level
, aa.address_line_1
, aa.address_line_2
, aa.town_or_city
, aa.addl_address_attribute2
, aa.postal_code
, aa.region_1
, aa.region_2
, aa.address_id
, hla.internal_location_code ubicacion_codigo
, hlat.location_name ubicacion
, paaf.hourly_salaried_code
, flvthsc.meaning hourly_salaried
, am.manager_person_number
, am.manager_name
, pu.username user_oracle
, pcaftl.collective_agreement_name
, aj.job_code puesto_code
, aj.name puesto
, ag.grade_code
, ag.name grade_name
--, cs.salary_amount salary
, imss.national_identifier_number imss
, curp.national_identifier_number curp
, rfc.national_identifier_number rfc
, issste.national_identifier_number issste
, fgad.national_identifier_number fgad
, msd.national_identifier_number msd
, nid.national_identifier_number nid
, hapf.position_code
, haoule.name
|| '|'
|| paaf.ass_attribute9
|| '|'
|| hlat.location_name
|| '|'
|| haoudep.name
|| '|'
|| get(split(paaf.ass_attribute2, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute3, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute4, '-'), 1)
|| '|'
|| paaf.assignment_name position_name
, pcf.type contract_type_code
, flvtc.meaning contract_type
, pcf.contract_number
, nvl(asu.subordinados,0) subordinados
, papf.creation_date
, papf.created_by
, papf.last_update_date
, papf.last_updated_by
, papf.last_update_login
, adr.puesto_director
, adr.nomina_director
, adr.director
, nvl(as4.stsemp,flvtpass.meaning) status_as400
, nvl(as4.fecbajn,ppos_max.actual_termination_date) fecha_baja_as400
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_dev papf
join alp_asignacion paaf
on paaf.person_id = papf.person_id
and paaf.assignment_type = 'E'
and paaf.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_dev ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and current_date() between ppnf.effective_start_date
and ppnf.effective_end_date
join alp_periods_of_service ppos_min
on paaf.person_id = ppos_min.person_id
and ppos_min.min_period = 1
join alp_periods_of_service ppos_max
on paaf.person_id = ppos_max.person_id
and ppos_max.max_period = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_dev peap
on papf.primary_email_id = peap.email_address_id
left join cat_master_oracle_fusion.sch_gold_layer.per_phones_dev ppp
on papf.primary_phone_id = ppp.phone_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haou
on paaf.business_unit_id = haou.organization_id
left join alp_legislative_info pplf
on papf.person_id = pplf.person_id
and pplf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_codecombinationextractpvo_dev gcc
on paaf.default_code_comb_id = gcc.codecombinationcodecombinationid
join alp_addresses aa
on papf.person_id = aa.person_id
and aa.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_employees_x_dev pex
on paaf.person_id = pex.person_id
and paaf.assignment_id = pex.assignment_id
join cat_master_oracle_fusion.sch_gold_layer.per_person_types_tl_dev pptt
on pex.person_type_id = pptt.person_type_id
and pptt.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_persons_dev pp
on papf.person_id = pp.person_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haoubu
on paaf.business_unit_id = haoubu.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haoudep
on paaf.organization_id = haoudep.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haoudepto
on haoudep.organization_id = haoudepto.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_dev haoule
on paaf.legal_entity_id = haoule.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_legal_entities_dev hle
on haoule.organization_id = hle.organization_id
and hle.classification_code = 'HCM_LEMP'
left join cat_master_oracle_fusion.sch_gold_layer.xle_entity_profiles_dev xep
on hle.legal_entity_id = xep.legal_entity_id
left join alp_sindicatos asi
on paaf.union_id = asi.organization_id
left join alp_emails ae
on papf.person_id = ae.person_id
and ae.reg = 1
left join alp_phones ap
on papf.person_id = ap.person_id
and ap.reg = 1
left join alp_bancos ab
on paaf.assignment_id = ab.assignment_id
and ab.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_dev hla
on paaf.location_id = hla.location_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_tl_dev hlat
on hla.location_code = hlat.location_code
and hla.location_details_id = hlat.location_details_id
and hlat.language = 'E'
left join alp_managers am
on paaf.person_id = am.person_id
and paaf.assignment_id = am.assignment_id
and am.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hcm_hcm_user_userextractpvo_dev pu
on papf.person_id = pu.personid
left join cat_master_oracle_fusion.sch_gold_layer.per_col_agreements_f_tl_dev pcaftl
on paaf.collective_agreement_id = pcaftl.collective_agreement_id
and pcaftl.language = 'E'
left join alp_jobs aj
on paaf.job_id = aj.job_id
and aj.reg = 1
left join alp_grades ag
on paaf.grade_id = ag.grade_id
left join cat_master_oracle_fusion.sch_gold_layer.cmp_salary_dev cs
on paaf.assignment_id = cs.assignment_id
and paaf.assignment_type = cs.assignment_type
and current_date() between cs.date_from
and cs.date_to
left join alp_documentos imss
on papf.person_id = imss.person_id
and imss.reg = 1
and imss.national_identifier_type = 'IMSS'
left join alp_documentos curp
on papf.person_id = curp.person_id
and curp.reg = 1
and curp.national_identifier_type = 'CURP'
left join alp_documentos rfc
on papf.person_id = rfc.person_id
and rfc.reg = 1
and rfc.national_identifier_type = 'RFC'
left join alp_documentos issste
on papf.person_id = issste.person_id
and issste.reg = 1
and issste.national_identifier_type = 'ISSSTE'
left join alp_documentos fgad
on papf.person_id = fgad.person_id
and fgad.reg = 1
and fgad.national_identifier_type = 'FGAD'
left join alp_documentos msd
on papf.person_id = msd.person_id
and msd.reg = 1
and msd.national_identifier_type = 'MSD'
left join alp_documentos nid
on papf.person_id = nid.person_id
and nid.reg = 1
and nid.national_identifier_type = 'NID'
left join alp_positions_all hapf
on paaf.business_unit_id = hapf.business_unit_id
and paaf.organization_id = hapf.organization_id
and paaf.position_id = hapf.position_id
and paaf.location_id = hapf.location_id
and paaf.job_id = hapf.job_id
and hapf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_contracts_f_dev pcf
on paaf.contract_id = pcf.contract_id
and current_date() between pcf.effective_start_date
and pcf.effective_end_date
left join alp_subalternos asu
on papf.person_id = asu.person_id
left join alp_dirtores_responsables adr
on paaf.person_id = adr.person_id
and paaf.assignment_id = adr.assignment_id
and adr.reg = 1
left join alp_as400 as4
on papf.person_number = as4.numemp
left join alp_cat_fnd_lookups flvt
on pp.country_of_birth = flvt.code
and flvt.type = 'HZ_DOMAIN_SUFFIX_LIST'
left join alp_cat_fnd_lookups flvtms
on pplf.marital_status = flvtms.code
and flvtms.type = 'MAR_STATUS'
left join alp_cat_fnd_lookups flvtle
on xep.legal_entity_identifier = flvtle.meaning
and flvtle.type = 'ALP_REPORTE_EMPLEADOS'
left join alp_cat_fnd_lookups flvtct
on paaf.employee_category = flvtct.code
and flvtct.type = 'EMPLOYEE_CATG'
left join alp_cat_fnd_lookups flvtec
on paaf.employment_category = flvtec.code
and flvtec.type = 'EMP_CAT'
left join alp_cat_fnd_lookups flvtpass
on paaf.assignment_status_type = flvtpass.code
and flvtpass.type = 'PER_ASS_SYS_STATUS'
left join alp_cat_fnd_lookups flvthelc
on pplf.highest_education_level = flvthelc.code
and flvthelc.type = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
left join alp_cat_fnd_lookups flvthsc
on paaf.hourly_salaried_code = flvthsc.code
and flvthsc.type = 'HOURLY_SALARIED_CODE'
left join alp_cat_fnd_lookups flvtc
on pcf.type = flvtc.code
and flvtc.type = 'CONTRACT_TYPE'
left join alp_cat_fnd_flex_fields ascgp
on gcc.codecombinationsegment1 = ascgp.value
and ascgp.flex_value_set_name = 'GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascuo
on gcc.codecombinationsegment2 = ascuo.value
and ascuo.flex_value_set_name = 'UNIDAD OPERATIVA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascc
on gcc.codecombinationsegment3 = ascc.value
and ascc.flex_value_set_name = 'CANAL GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascca
on gcc.codecombinationsegment4 = ascca.value
and ascca.flex_value_set_name = 'CATEGORIA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields asccc
on gcc.codecombinationsegment5 = asccc.value
and asccc.flex_value_set_name = 'CENTRO DE COSTOS GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascta
on gcc.codecombinationsegment6 = ascta.value
and ascta.flex_value_set_name = 'CUENTA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascf
on gcc.codecombinationsegment7 = ascf.value
and ascf.flex_value_set_name = 'FILIALES GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascfut
on gcc.codecombinationsegment8 = ascfut.value
and ascfut.flex_value_set_name = 'FUTURO GPLP_PRIMARIO'
where current_date() between papf.effective_start_date
and papf.effective_end_date)
select person_id,
assignment_id,
person_number,
user_person_type,
assignment_status_type_code,
assignment_status_type,
last_name,
previous_last_name,
first_name,
middle_names,
full_name,
region_of_birth,
age,
country_of_birth,
date_of_birth,
sex,
marital_status_code,
marital_status,
date_start,
actual_termination_date,
primary_email_address,
primary_phone,
checa_tarjeta,
ubicacion_real,
ubicacion_administrativa,
grupo_reloj_chec,
legal_entity_identifier,
legal_entity_code,
legal_entity,
bu_code,
bu_name,
seccion_codigo,
seccion,
area_codigo,
area,
area_op_codigo,
area_op,
cast(department_code as DECIMAL(10,0)),
department,
ass_attribute15,
assignment_name,
assignment_number,
sindicato,
seccion_sindicato,
primary_assignment_flag,
labour_union_member_flag,
default_expense_account_code,
default_expense_account,
cc_compania_code,
cc_compania,
cc_unidad_operativa_code,
cc_unidad_operativa,
cc_canal_code,
cc_canal,
cc_categoria_code,
cc_categoria,
cc_centro_costo_code,
cc_centro_costo,
cc_cuenta_code,
cc_cuenta,
cc_filiales_code,
cc_filiales,
cc_futuro_code,
cc_futuro,
employee_category_code,
employee_category,
employment_category_code,
employment_category,
email_address,
phone_number,
bank_name,
bank_account_num,
highest_education_level_code,
highest_education_level,
address_line_1,
address_line_2,
town_or_city,
addl_address_attribute2,
postal_code,
region_1,
region_2,
address_id,
ubicacion_codigo,
ubicacion,
hourly_salaried_code,
hourly_salaried,
manager_person_number,
manager_name,
user_oracle,
collective_agreement_name,
puesto_code,
puesto,
grade_code,
grade_name,
imss,
curp,
rfc,
issste,
fgad,
msd,
nid,
position_code,
position_name,
contract_type_code,
contract_type,
contract_number,
subordinados,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
puesto_director,
nomina_director,
director,
status_as400,
fecha_baja_as400
from alp_empleados
where reg = 1
with alp_asignacion as
( select ROW_NUMBER() OVER (partition by person_id order by assignment_id desc) reg
, person_id
, assignment_id
, assignment_name
, assignment_number
, assignment_type
, assignment_status_type
, legal_entity_id
, business_unit_id
, union_id
, organization_id
, location_id
, employee_category
, employment_category
, primary_assignment_flag
, labour_union_member_flag
, hourly_salaried_code
, default_code_comb_id
, collective_agreement_id
, position_id
, job_id
, grade_id
, contract_id
, ass_attribute1
, ass_attribute2
, ass_attribute3
, ass_attribute4
, ass_attribute5
, ass_attribute6
, ass_attribute8
, ass_attribute9
, ass_attribute15
from cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_stg
where assignment_type = 'E'
and current_date() between effective_start_date
and effective_end_date)
, alp_periods_of_service as
( select ROW_NUMBER() OVER (partition by person_id order by period_of_service_id desc) max_period
, ROW_NUMBER() OVER (partition by person_id order by period_of_service_id) min_period
, person_id
, date_start
, actual_termination_date
from cat_master_oracle_fusion.sch_gold_layer.per_periods_of_service_stg)
, alp_addresses as
( select ROW_NUMBER() OVER (partition by ppauf.person_id order by ppauf.effective_end_date desc, ppauf.person_addr_usage_id desc, paf.effective_end_date desc) reg
, ppauf.person_id
, paf.address_line_1
, paf.address_line_2
, paf.town_or_city
, paf.addl_address_attribute2
, paf.postal_code
, paf.region_1
, paf.region_2
, paf.address_id
from cat_master_oracle_fusion.sch_gold_layer.per_person_addr_usages_f_stg ppauf
left join cat_master_oracle_fusion.sch_gold_layer.per_addresses_f_stg paf
on ppauf.address_id = paf.address_id)
, alp_sindicatos as
( select haou.organization_id
, hoif.org_information2
, haou.name
from cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
join cat_master_oracle_fusion.sch_gold_layer.hr_organization_information_f_stg hoif
on haou.organization_id = hoif.organization_id
where current_date() between hoif.effective_start_date
and hoif.effective_end_date)
, alp_emails as
( select ROW_NUMBER() OVER (partition by person_id order by email_address_id desc) reg
, person_id
, email_address
from cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_stg
where email_address not like '%@alpura.com'
and current_date() between date_from
and date_to)
, alp_phones as
( select ROW_NUMBER() OVER (partition by person_id order by phone_id desc) reg
, person_id
, phone_number
from cat_master_oracle_fusion.sch_gold_layer.per_phones_stg
where phone_type = 'H1'
and current_date() between date_from
and date_to)
, alp_bancos as
( select ROW_NUMBER() OVER (partition by prgd.assignment_id order by pppmf.personal_payment_method_id desc) reg
, prgd.assignment_id
, pba.bank_name
, pba.bank_account_num_electronic
from cat_master_oracle_fusion.sch_gold_layer.pay_rel_groups_dn_stg prgd
join cat_master_oracle_fusion.sch_gold_layer.pay_person_pay_methods_f_stg pppmf
on prgd.payroll_relationship_id = pppmf.payroll_relationship_id
and pppmf.percentage is not null
join cat_master_oracle_fusion.sch_gold_layer.pay_bank_accounts_stg pba
on pppmf.bank_account_id = pba.bank_account_id
and pppmf.percentage is not null
and current_date() between prgd.start_date
and prgd.end_date)
, alp_managers as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, pmhc.person_id
, pmhc.assignment_id
, papf.person_number
, mpapf.person_number manager_person_number
, mgr.last_name
|| nvl2(mgr.previous_last_name,' ' || mgr.previous_last_name,'')
|| ' ' || mgr.first_name
|| nvl2(mgr.middle_names,' ' || mgr.middle_names,'') manager_name
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_stg papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_stg paaf
on paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_stg pmhc
on paaf.assignment_id = pmhc.assignment_id
and paaf.person_id = pmhc.person_id
and current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_stg mgr
on pmhc.level1_manager_id = mgr.person_id
and mgr.name_type = 'GLOBAL'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_stg mpapf
on mgr.person_id = mpapf.person_id
and current_date() between mpapf.effective_start_date
and mpapf.effective_end_date
where current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_jobs as
( select pjf.job_id
, pjf.job_code
, pjft.name
, ROW_NUMBER() OVER (partition by pjf.job_id order by pjf.insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_stg pjf
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_stg pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
where current_date() between pjf.effective_start_date
and pjf.effective_end_date
and current_date() between pjft.effective_start_date
and pjft.effective_end_date)
, alp_grades as
( select pgf.grade_id
, pgf.grade_code
, pgft.name
from cat_master_oracle_fusion.sch_gold_layer.per_grades_f_stg pgf
join cat_master_oracle_fusion.sch_gold_layer.per_grades_f_tl_stg pgft
on pgf.grade_id = pgft.grade_id
and pgft.language = 'E'
where current_date() between pgf.effective_start_date
and pgf.effective_end_date
and current_date() between pgft.effective_start_date
and pgft.effective_end_date)
, alp_documentos as
( select ROW_NUMBER() OVER ( partition by nationalidentifierpeopersonid
, nationalidentifierpeonationalidentifiertype
order by nationalidentifierid desc) reg
, nationalidentifierpeopersonid person_id
, nationalidentifierpeonationalidentifiertype national_identifier_type
, nationalidentifierpeonationalidentifiernumber national_identifier_number
from cat_master_oracle_fusion.sch_gold_layer.hcm_person_personnationalidentifierpvo_stg)
, alp_subalternos as
( select pmhc.level1_manager_id person_id
, count(*) subordinados
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_stg pmhc
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_stg papf
on pmhc.person_id = papf.person_id
and current_date() between papf.effective_start_date
and papf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_stg mgr
on pmhc.person_id = mgr.person_id
and mgr.name_type = 'MX'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_stg paaf
on pmhc.person_id = paaf.person_id
and pmhc.assignment_id = paaf.assignment_id
and paaf.assignment_status_type = 'ACTIVE'
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
where current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
group by pmhc.level1_manager_id)
, alp_directores as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, paaf.person_id
, pjft.name puesto_director
, papf.person_number nomina_director
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') director
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_stg papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_stg paaf
on papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_stg pjf
on paaf.job_id = pjf.job_id
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_stg pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_stg ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'MX'
where paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and (UPPER(pjft.name) like '%DIR %'
or UPPER(pjft.name) like '%DIRECTOR%')
and UPPER(pjft.name) not like '%ASISTENTE%'
and current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_dirtores_responsables as
( select pmhc.person_id
, pmhc.assignment_id
, ROW_NUMBER() OVER (partition by pmhc.person_id, pmhc.assignment_id order by pmhc.insertion_date desc) reg
, nvl(ad1.puesto_director,nvl(ad2.puesto_director,nvl(ad3.puesto_director,nvl(ad4.puesto_director,nvl(ad5.puesto_director,nvl(ad6.puesto_director,nvl(ad7.puesto_director,nvl(ad8.puesto_director,nvl(ad9.puesto_director,nvl(ad10.puesto_director,nvl(ad11.puesto_director,nvl(ad12.puesto_director,nvl(ad13.puesto_director,nvl(ad14.puesto_director,nvl(ad15.puesto_director,nvl(ad16.puesto_director,nvl(ad17.puesto_director,nvl(ad18.puesto_director,nvl(ad19.puesto_director,nvl(ad20.puesto_director,'')))))))))))))))))))) puesto_director
, nvl(ad1.nomina_director,nvl(ad2.nomina_director,nvl(ad3.nomina_director,nvl(ad4.nomina_director,nvl(ad5.nomina_director,nvl(ad6.nomina_director,nvl(ad7.nomina_director,nvl(ad8.nomina_director,nvl(ad9.nomina_director,nvl(ad10.nomina_director,nvl(ad11.nomina_director,nvl(ad12.nomina_director,nvl(ad13.nomina_director,nvl(ad14.nomina_director,nvl(ad15.nomina_director,nvl(ad16.nomina_director,nvl(ad17.nomina_director,nvl(ad18.nomina_director,nvl(ad19.nomina_director,nvl(ad20.nomina_director,'')))))))))))))))))))) nomina_director
, nvl(ad1.director,nvl(ad2.director,nvl(ad3.director,nvl(ad4.director,nvl(ad5.director,nvl(ad6.director,nvl(ad7.director,nvl(ad8.director,nvl(ad9.director,nvl(ad10.director,nvl(ad11.director,nvl(ad12.director,nvl(ad13.director,nvl(ad14.director,nvl(ad15.director,nvl(ad16.director,nvl(ad17.director,nvl(ad18.director,nvl(ad19.director,nvl(ad20.director,'')))))))))))))))))))) director
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_stg pmhc
left join alp_directores ad1
on pmhc.level1_manager_id = ad1.person_id
and ad1.reg = 1
left join alp_directores ad2
on pmhc.level2_manager_id = ad2.person_id
and ad2.reg = 1
left join alp_directores ad3
on pmhc.level3_manager_id = ad3.person_id
and ad3.reg = 1
left join alp_directores ad4
on pmhc.level4_manager_id = ad4.person_id
and ad4.reg = 1
left join alp_directores ad5
on pmhc.level5_manager_id = ad5.person_id
and ad5.reg = 1
left join alp_directores ad6
on pmhc.level6_manager_id = ad6.person_id
and ad6.reg = 1
left join alp_directores ad7
on pmhc.level7_manager_id = ad7.person_id
and ad7.reg = 1
left join alp_directores ad8
on pmhc.level8_manager_id = ad8.person_id
and ad8.reg = 1
left join alp_directores ad9
on pmhc.level9_manager_id = ad9.person_id
and ad9.reg = 1
left join alp_directores ad10
on pmhc.level10_manager_id = ad10.person_id
and ad10.reg = 1
left join alp_directores ad11
on pmhc.level11_manager_id = ad11.person_id
and ad11.reg = 1
left join alp_directores ad12
on pmhc.level2_manager_id = ad12.person_id
and ad12.reg = 1
left join alp_directores ad13
on pmhc.level3_manager_id = ad13.person_id
and ad13.reg = 1
left join alp_directores ad14
on pmhc.level4_manager_id = ad14.person_id
and ad14.reg = 1
left join alp_directores ad15
on pmhc.level5_manager_id = ad15.person_id
and ad15.reg = 1
left join alp_directores ad16
on pmhc.level6_manager_id = ad16.person_id
and ad16.reg = 1
left join alp_directores ad17
on pmhc.level7_manager_id = ad17.person_id
and ad17.reg = 1
left join alp_directores ad18
on pmhc.level8_manager_id = ad18.person_id
and ad18.reg = 1
left join alp_directores ad19
on pmhc.level9_manager_id = ad19.person_id
and ad19.reg = 1
left join alp_directores ad20
on pmhc.level10_manager_id = ad20.person_id
and ad20.reg = 1
where current_date between pmhc.effective_start_date
and pmhc.effective_end_date)
, alp_cat_fnd_lookups as
( select flv.lookuptype type
, flv.lookupcode code
, flvt.meaning
, flv.setid set_id
, flv.tag
, flv.viewapplicationid view_appl_id
, flv.enabledflag enabled
, flv.startdateactive start_date
, flv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_stg flv
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvt
on flv.lookuptype = flvt.lookuptype
and flv.lookupcode = flvt.lookupcode
and flv.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets_stg ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo_stg ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo_stg ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_legislative_info as
( select person_id
, marital_status
, highest_education_level
, sex
, ROW_NUMBER() OVER (partition by person_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_people_legislative_f_stg
where current_date() between effective_start_date
and effective_end_date)
, alp_positions_all as
( select business_unit_id
, organization_id
, position_id
, location_id
, job_id
, position_code
, ROW_NUMBER() OVER (partition by business_unit_id, organization_id, position_id, location_id, job_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.hr_all_positions_f_stg
where current_date() between effective_start_date
and effective_end_date)
, alp_as400 as
( SELECT
CAST(numemp AS STRING) AS numemp,
'Inactiva' AS stsemp,
CASE
WHEN CAST(fecbajn AS STRING) = '0' THEN NULL
ELSE date_format(
try_to_timestamp(CAST(fecbajn AS STRING), 'yyyyMMdd'),
'yyyy-MM-dd HH:mm:ss'
)
END AS fecbajn
FROM cat_master_alpura.sch_silver_layer.as4_nomperso
WHERE stsemp = 'B')
, alp_empleados as
( select ROW_NUMBER() OVER (partition by papf.person_id order by papf.person_id desc) reg
, papf.person_id
, paaf.assignment_id
, papf.person_number
, pptt.user_person_type
, paaf.assignment_status_type assignment_status_type_code
, flvtpass.meaning assignment_status_type
, ppnf.last_name
, ppnf.previous_last_name
, ppnf.first_name
, ppnf.middle_names
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') full_name
, pp.region_of_birth
, floor(months_between(current_date(), pp.date_of_birth) / 12) age
, flvt.meaning country_of_birth
, pp.date_of_birth
, pplf.sex
, pplf.marital_status marital_status_code
, flvtms.meaning marital_status
, ppos_min.date_start
, ppos_max.actual_termination_date
, peap.email_address primary_email_address
, ppp.phone_number primary_phone
, paaf.ass_attribute1 checa_tarjeta
, paaf.ass_attribute5 ubicacion_real
, paaf.ass_attribute6 ubicacion_administrativa
, paaf.ass_attribute8 grupo_reloj_chec
, xep.legal_entity_identifier
, flvtle.meaning legal_entity_code
, haoule.name legal_entity
, haoubu.name bu_code
, paaf.ass_attribute9 bu_name
, get(split(paaf.ass_attribute2, '-'), 0) seccion_codigo
, get(split(paaf.ass_attribute2, '-'), 1) seccion
, get(split(paaf.ass_attribute3, '-'), 0) area_codigo
, get(split(paaf.ass_attribute3, '-'), 1) area
, get(split(paaf.ass_attribute4, '-'), 0) area_op_codigo
, get(split(paaf.ass_attribute4, '-'), 1) area_op
, haoudepto.attribute_number1 department_code
, haoudep.name department
, paaf.ass_attribute15
, paaf.assignment_name
, paaf.assignment_number
, asi.name sindicato
, asi.org_information2 seccion_sindicato
, paaf.primary_assignment_flag
, paaf.labour_union_member_flag
, ascgp.value
|| '.'
|| ascuo.value
|| '.'
|| ascc.value
|| '.'
|| ascca.value
|| '.'
|| asccc.value
|| '.'
|| ascta.value
|| '.'
|| ascf.value
|| '.'
|| ascfut.value default_expense_account_code
, ascgp.description
|| '.'
|| ascuo.description
|| '.'
|| ascc.description
|| '.'
|| ascca.description
|| '.'
|| asccc.description
|| '.'
|| ascta.description
|| '.'
|| ascf.description
|| '.'
|| ascfut.description default_expense_account
, ascgp.value cc_compania_code
, ascgp.description cc_compania
, ascuo.value cc_unidad_operativa_code
, ascuo.description cc_unidad_operativa
, ascc.value cc_canal_code
, ascc.description cc_canal
, ascca.value cc_categoria_code
, ascca.description cc_categoria
, asccc.value cc_centro_costo_code
, asccc.description cc_centro_costo
, ascta.value cc_cuenta_code
, ascta.description cc_cuenta
, ascf.value cc_filiales_code
, ascf.description cc_filiales
, ascfut.value cc_futuro_code
, ascfut.description cc_futuro
, paaf.employee_category employee_category_code
, flvtct.meaning employee_category
, paaf.employment_category employment_category_code
, flvtec.meaning employment_category
, ae.email_address email_address
, ap.phone_number phone_number
, ab.bank_name
, ab.bank_account_num_electronic bank_account_num
, pplf.highest_education_level highest_education_level_code
, flvthelc.meaning highest_education_level
, aa.address_line_1
, aa.address_line_2
, aa.town_or_city
, aa.addl_address_attribute2
, aa.postal_code
, aa.region_1
, aa.region_2
, aa.address_id
, hla.internal_location_code ubicacion_codigo
, hlat.location_name ubicacion
, paaf.hourly_salaried_code
, flvthsc.meaning hourly_salaried
, am.manager_person_number
, am.manager_name
, pu.username user_oracle
, pcaftl.collective_agreement_name
, aj.job_code puesto_code
, aj.name puesto
, ag.grade_code
, ag.name grade_name
--, cs.salary_amount salary
, imss.national_identifier_number imss
, curp.national_identifier_number curp
, rfc.national_identifier_number rfc
, issste.national_identifier_number issste
, fgad.national_identifier_number fgad
, msd.national_identifier_number msd
, nid.national_identifier_number nid
, hapf.position_code
, haoule.name
|| '|'
|| paaf.ass_attribute9
|| '|'
|| hlat.location_name
|| '|'
|| haoudep.name
|| '|'
|| get(split(paaf.ass_attribute2, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute3, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute4, '-'), 1)
|| '|'
|| paaf.assignment_name position_name
, pcf.type contract_type_code
, flvtc.meaning contract_type
, pcf.contract_number
, nvl(asu.subordinados,0) subordinados
, papf.creation_date
, papf.created_by
, papf.last_update_date
, papf.last_updated_by
, papf.last_update_login
, adr.puesto_director
, adr.nomina_director
, adr.director
, nvl(as4.stsemp,flvtpass.meaning) status_as400
, nvl(as4.fecbajn,ppos_max.actual_termination_date) fecha_baja_as400
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_stg papf
join alp_asignacion paaf
on paaf.person_id = papf.person_id
and paaf.assignment_type = 'E'
and paaf.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_stg ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and current_date() between ppnf.effective_start_date
and ppnf.effective_end_date
join alp_periods_of_service ppos_min
on paaf.person_id = ppos_min.person_id
and ppos_min.min_period = 1
join alp_periods_of_service ppos_max
on paaf.person_id = ppos_max.person_id
and ppos_max.max_period = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_stg peap
on papf.primary_email_id = peap.email_address_id
left join cat_master_oracle_fusion.sch_gold_layer.per_phones_stg ppp
on papf.primary_phone_id = ppp.phone_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on paaf.business_unit_id = haou.organization_id
left join alp_legislative_info pplf
on papf.person_id = pplf.person_id
and pplf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_codecombinationextractpvo_stg gcc
on paaf.default_code_comb_id = gcc.codecombinationcodecombinationid
join alp_addresses aa
on papf.person_id = aa.person_id
and aa.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_employees_x_stg pex
on paaf.person_id = pex.person_id
and paaf.assignment_id = pex.assignment_id
join cat_master_oracle_fusion.sch_gold_layer.per_person_types_tl_stg pptt
on pex.person_type_id = pptt.person_type_id
and pptt.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_persons_stg pp
on papf.person_id = pp.person_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haoubu
on paaf.business_unit_id = haoubu.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haoudep
on paaf.organization_id = haoudep.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haoudepto
on haoudep.organization_id = haoudepto.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haoule
on paaf.legal_entity_id = haoule.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_legal_entities_stg hle
on haoule.organization_id = hle.organization_id
and hle.classification_code = 'HCM_LEMP'
left join cat_master_oracle_fusion.sch_gold_layer.xle_entity_profiles_stg xep
on hle.legal_entity_id = xep.legal_entity_id
left join alp_sindicatos asi
on paaf.union_id = asi.organization_id
left join alp_emails ae
on papf.person_id = ae.person_id
and ae.reg = 1
left join alp_phones ap
on papf.person_id = ap.person_id
and ap.reg = 1
left join alp_bancos ab
on paaf.assignment_id = ab.assignment_id
and ab.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_stg hla
on paaf.location_id = hla.location_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_tl_stg hlat
on hla.location_code = hlat.location_code
and hla.location_details_id = hlat.location_details_id
and hlat.language = 'E'
left join alp_managers am
on paaf.person_id = am.person_id
and paaf.assignment_id = am.assignment_id
and am.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hcm_hcm_user_userextractpvo_stg pu
on papf.person_id = pu.personid
left join cat_master_oracle_fusion.sch_gold_layer.per_col_agreements_f_tl_stg pcaftl
on paaf.collective_agreement_id = pcaftl.collective_agreement_id
and pcaftl.language = 'E'
left join alp_jobs aj
on paaf.job_id = aj.job_id
and aj.reg = 1
left join alp_grades ag
on paaf.grade_id = ag.grade_id
left join cat_master_oracle_fusion.sch_gold_layer.cmp_salary_stg cs
on paaf.assignment_id = cs.assignment_id
and paaf.assignment_type = cs.assignment_type
and current_date() between cs.date_from
and cs.date_to
left join alp_documentos imss
on papf.person_id = imss.person_id
and imss.reg = 1
and imss.national_identifier_type = 'IMSS'
left join alp_documentos curp
on papf.person_id = curp.person_id
and curp.reg = 1
and curp.national_identifier_type = 'CURP'
left join alp_documentos rfc
on papf.person_id = rfc.person_id
and rfc.reg = 1
and rfc.national_identifier_type = 'RFC'
left join alp_documentos issste
on papf.person_id = issste.person_id
and issste.reg = 1
and issste.national_identifier_type = 'ISSSTE'
left join alp_documentos fgad
on papf.person_id = fgad.person_id
and fgad.reg = 1
and fgad.national_identifier_type = 'FGAD'
left join alp_documentos msd
on papf.person_id = msd.person_id
and msd.reg = 1
and msd.national_identifier_type = 'MSD'
left join alp_documentos nid
on papf.person_id = nid.person_id
and nid.reg = 1
and nid.national_identifier_type = 'NID'
left join alp_positions_all hapf
on paaf.business_unit_id = hapf.business_unit_id
and paaf.organization_id = hapf.organization_id
and paaf.position_id = hapf.position_id
and paaf.location_id = hapf.location_id
and paaf.job_id = hapf.job_id
and hapf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_contracts_f_stg pcf
on paaf.contract_id = pcf.contract_id
and current_date() between pcf.effective_start_date
and pcf.effective_end_date
left join alp_subalternos asu
on papf.person_id = asu.person_id
left join alp_dirtores_responsables adr
on paaf.person_id = adr.person_id
and paaf.assignment_id = adr.assignment_id
and adr.reg = 1
left join alp_as400 as4
on papf.person_number = as4.numemp
left join alp_cat_fnd_lookups flvt
on pp.country_of_birth = flvt.code
and flvt.type = 'HZ_DOMAIN_SUFFIX_LIST'
left join alp_cat_fnd_lookups flvtms
on pplf.marital_status = flvtms.code
and flvtms.type = 'MAR_STATUS'
left join alp_cat_fnd_lookups flvtle
on xep.legal_entity_identifier = flvtle.meaning
and flvtle.type = 'ALP_REPORTE_EMPLEADOS'
left join alp_cat_fnd_lookups flvtct
on paaf.employee_category = flvtct.code
and flvtct.type = 'EMPLOYEE_CATG'
left join alp_cat_fnd_lookups flvtec
on paaf.employment_category = flvtec.code
and flvtec.type = 'EMP_CAT'
left join alp_cat_fnd_lookups flvtpass
on paaf.assignment_status_type = flvtpass.code
and flvtpass.type = 'PER_ASS_SYS_STATUS'
left join alp_cat_fnd_lookups flvthelc
on pplf.highest_education_level = flvthelc.code
and flvthelc.type = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
left join alp_cat_fnd_lookups flvthsc
on paaf.hourly_salaried_code = flvthsc.code
and flvthsc.type = 'HOURLY_SALARIED_CODE'
left join alp_cat_fnd_lookups flvtc
on pcf.type = flvtc.code
and flvtc.type = 'CONTRACT_TYPE'
left join alp_cat_fnd_flex_fields ascgp
on gcc.codecombinationsegment1 = ascgp.value
and ascgp.flex_value_set_name = 'GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascuo
on gcc.codecombinationsegment2 = ascuo.value
and ascuo.flex_value_set_name = 'UNIDAD OPERATIVA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascc
on gcc.codecombinationsegment3 = ascc.value
and ascc.flex_value_set_name = 'CANAL GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascca
on gcc.codecombinationsegment4 = ascca.value
and ascca.flex_value_set_name = 'CATEGORIA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields asccc
on gcc.codecombinationsegment5 = asccc.value
and asccc.flex_value_set_name = 'CENTRO DE COSTOS GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascta
on gcc.codecombinationsegment6 = ascta.value
and ascta.flex_value_set_name = 'CUENTA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascf
on gcc.codecombinationsegment7 = ascf.value
and ascf.flex_value_set_name = 'FILIALES GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascfut
on gcc.codecombinationsegment8 = ascfut.value
and ascfut.flex_value_set_name = 'FUTURO GPLP_PRIMARIO'
where current_date() between papf.effective_start_date
and papf.effective_end_date)
select person_id,
assignment_id,
person_number,
user_person_type,
assignment_status_type_code,
assignment_status_type,
last_name,
previous_last_name,
first_name,
middle_names,
full_name,
region_of_birth,
age,
country_of_birth,
date_of_birth,
sex,
marital_status_code,
marital_status,
date_start,
actual_termination_date,
primary_email_address,
primary_phone,
checa_tarjeta,
ubicacion_real,
ubicacion_administrativa,
grupo_reloj_chec,
legal_entity_identifier,
legal_entity_code,
legal_entity,
bu_code,
bu_name,
seccion_codigo,
seccion,
area_codigo,
area,
area_op_codigo,
area_op,
cast(department_code as DECIMAL(10,0)),
department,
ass_attribute15,
assignment_name,
assignment_number,
sindicato,
seccion_sindicato,
primary_assignment_flag,
labour_union_member_flag,
default_expense_account_code,
default_expense_account,
cc_compania_code,
cc_compania,
cc_unidad_operativa_code,
cc_unidad_operativa,
cc_canal_code,
cc_canal,
cc_categoria_code,
cc_categoria,
cc_centro_costo_code,
cc_centro_costo,
cc_cuenta_code,
cc_cuenta,
cc_filiales_code,
cc_filiales,
cc_futuro_code,
cc_futuro,
employee_category_code,
employee_category,
employment_category_code,
employment_category,
email_address,
phone_number,
bank_name,
bank_account_num,
highest_education_level_code,
highest_education_level,
address_line_1,
address_line_2,
town_or_city,
addl_address_attribute2,
postal_code,
region_1,
region_2,
address_id,
ubicacion_codigo,
ubicacion,
hourly_salaried_code,
hourly_salaried,
manager_person_number,
manager_name,
user_oracle,
collective_agreement_name,
puesto_code,
puesto,
grade_code,
grade_name,
imss,
curp,
rfc,
issste,
fgad,
msd,
nid,
position_code,
position_name,
contract_type_code,
contract_type,
contract_number,
subordinados,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
puesto_director,
nomina_director,
director,
status_as400,
fecha_baja_as400
from alp_empleados
where reg = 1
with alp_asignacion as
( select ROW_NUMBER() OVER (partition by person_id order by assignment_id desc) reg
, person_id
, assignment_id
, assignment_name
, assignment_number
, assignment_type
, assignment_status_type
, legal_entity_id
, business_unit_id
, union_id
, organization_id
, location_id
, employee_category
, employment_category
, primary_assignment_flag
, labour_union_member_flag
, hourly_salaried_code
, default_code_comb_id
, collective_agreement_id
, position_id
, job_id
, grade_id
, contract_id
, ass_attribute1
, ass_attribute2
, ass_attribute3
, ass_attribute4
, ass_attribute5
, ass_attribute6
, ass_attribute8
, ass_attribute9
, ass_attribute15
from cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_prd
where assignment_type = 'E'
and current_date() between effective_start_date
and effective_end_date)
, alp_periods_of_service as
( select ROW_NUMBER() OVER (partition by person_id order by period_of_service_id desc) max_period
, ROW_NUMBER() OVER (partition by person_id order by period_of_service_id) min_period
, person_id
, date_start
, actual_termination_date
from cat_master_oracle_fusion.sch_gold_layer.per_periods_of_service_prd)
, alp_addresses as
( select ROW_NUMBER() OVER (partition by ppauf.person_id order by ppauf.effective_end_date desc, ppauf.person_addr_usage_id desc, paf.effective_end_date desc) reg
, ppauf.person_id
, paf.address_line_1
, paf.address_line_2
, paf.town_or_city
, paf.addl_address_attribute2
, paf.postal_code
, paf.region_1
, paf.region_2
, paf.address_id
from cat_master_oracle_fusion.sch_gold_layer.per_person_addr_usages_f_prd ppauf
left join cat_master_oracle_fusion.sch_gold_layer.per_addresses_f_prd paf
on ppauf.address_id = paf.address_id)
, alp_sindicatos as
( select haou.organization_id
, hoif.org_information2
, haou.name
from cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
join cat_master_oracle_fusion.sch_gold_layer.hr_organization_information_f_prd hoif
on haou.organization_id = hoif.organization_id
where current_date() between hoif.effective_start_date
and hoif.effective_end_date)
, alp_emails as
( select ROW_NUMBER() OVER (partition by person_id order by email_address_id desc) reg
, person_id
, email_address
from cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_prd
where email_address not like '%@alpura.com'
and current_date() between date_from
and date_to)
, alp_phones as
( select ROW_NUMBER() OVER (partition by person_id order by phone_id desc) reg
, person_id
, phone_number
from cat_master_oracle_fusion.sch_gold_layer.per_phones_prd
where phone_type = 'H1'
and current_date() between date_from
and date_to)
, alp_bancos as
( select ROW_NUMBER() OVER (partition by prgd.assignment_id order by pppmf.personal_payment_method_id desc) reg
, prgd.assignment_id
, pba.bank_name
, pba.bank_account_num_electronic
from cat_master_oracle_fusion.sch_gold_layer.pay_rel_groups_dn_prd prgd
join cat_master_oracle_fusion.sch_gold_layer.pay_person_pay_methods_f_prd pppmf
on prgd.payroll_relationship_id = pppmf.payroll_relationship_id
and pppmf.percentage is not null
join cat_master_oracle_fusion.sch_gold_layer.pay_bank_accounts_prd pba
on pppmf.bank_account_id = pba.bank_account_id
and pppmf.percentage is not null
and current_date() between prgd.start_date
and prgd.end_date)
, alp_managers as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, pmhc.person_id
, pmhc.assignment_id
, papf.person_number
, mpapf.person_number manager_person_number
, mgr.last_name
|| nvl2(mgr.previous_last_name,' ' || mgr.previous_last_name,'')
|| ' ' || mgr.first_name
|| nvl2(mgr.middle_names,' ' || mgr.middle_names,'') manager_name
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_prd papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_prd paaf
on paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_prd pmhc
on paaf.assignment_id = pmhc.assignment_id
and paaf.person_id = pmhc.person_id
and current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_prd mgr
on pmhc.level1_manager_id = mgr.person_id
and mgr.name_type = 'GLOBAL'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_prd mpapf
on mgr.person_id = mpapf.person_id
and current_date() between mpapf.effective_start_date
and mpapf.effective_end_date
where current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_jobs as
( select pjf.job_id
, pjf.job_code
, pjft.name
, ROW_NUMBER() OVER (partition by pjf.job_id order by pjf.insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_prd pjf
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_prd pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
where current_date() between pjf.effective_start_date
and pjf.effective_end_date
and current_date() between pjft.effective_start_date
and pjft.effective_end_date)
, alp_grades as
( select pgf.grade_id
, pgf.grade_code
, pgft.name
from cat_master_oracle_fusion.sch_gold_layer.per_grades_f_prd pgf
join cat_master_oracle_fusion.sch_gold_layer.per_grades_f_tl_prd pgft
on pgf.grade_id = pgft.grade_id
and pgft.language = 'E'
where current_date() between pgf.effective_start_date
and pgf.effective_end_date
and current_date() between pgft.effective_start_date
and pgft.effective_end_date)
, alp_documentos as
( select ROW_NUMBER() OVER ( partition by nationalidentifierpeopersonid
, nationalidentifierpeonationalidentifiertype
order by nationalidentifierid desc) reg
, nationalidentifierpeopersonid person_id
, nationalidentifierpeonationalidentifiertype national_identifier_type
, nationalidentifierpeonationalidentifiernumber national_identifier_number
from cat_master_oracle_fusion.sch_gold_layer.hcm_person_personnationalidentifierpvo_prd)
, alp_subalternos as
( select pmhc.level1_manager_id person_id
, count(*) subordinados
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_prd pmhc
join cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_prd papf
on pmhc.person_id = papf.person_id
and current_date() between papf.effective_start_date
and papf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_prd mgr
on pmhc.person_id = mgr.person_id
and mgr.name_type = 'MX'
and current_date() between mgr.effective_start_date
and mgr.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_prd paaf
on pmhc.person_id = paaf.person_id
and pmhc.assignment_id = paaf.assignment_id
and paaf.assignment_status_type = 'ACTIVE'
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
where current_date() between pmhc.effective_start_date
and pmhc.effective_end_date
group by pmhc.level1_manager_id)
, alp_directores as
( select ROW_NUMBER() OVER (partition by paaf.person_id order by paaf.assignment_id desc) reg
, paaf.person_id
, pjft.name puesto_director
, papf.person_number nomina_director
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') director
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_prd papf
join cat_master_oracle_fusion.sch_gold_layer.per_all_assignments_f_prd paaf
on papf.person_id = paaf.person_id
and current_date() between paaf.effective_start_date
and paaf.effective_end_date
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_prd pjf
on paaf.job_id = pjf.job_id
join cat_master_oracle_fusion.sch_gold_layer.per_jobs_f_tl_prd pjft
on pjf.job_id = pjft.job_id
and pjft.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_prd ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'MX'
where paaf.assignment_type = 'E'
and paaf.assignment_status_type = 'ACTIVE'
and (UPPER(pjft.name) like '%DIR %'
or UPPER(pjft.name) like '%DIRECTOR%')
and UPPER(pjft.name) not like '%ASISTENTE%'
and current_date() between papf.effective_start_date
and papf.effective_end_date)
, alp_dirtores_responsables as
( select pmhc.person_id
, pmhc.assignment_id
, ROW_NUMBER() OVER (partition by pmhc.person_id, pmhc.assignment_id order by pmhc.insertion_date desc) reg
, nvl(ad1.puesto_director,nvl(ad2.puesto_director,nvl(ad3.puesto_director,nvl(ad4.puesto_director,nvl(ad5.puesto_director,nvl(ad6.puesto_director,nvl(ad7.puesto_director,nvl(ad8.puesto_director,nvl(ad9.puesto_director,nvl(ad10.puesto_director,nvl(ad11.puesto_director,nvl(ad12.puesto_director,nvl(ad13.puesto_director,nvl(ad14.puesto_director,nvl(ad15.puesto_director,nvl(ad16.puesto_director,nvl(ad17.puesto_director,nvl(ad18.puesto_director,nvl(ad19.puesto_director,nvl(ad20.puesto_director,'')))))))))))))))))))) puesto_director
, nvl(ad1.nomina_director,nvl(ad2.nomina_director,nvl(ad3.nomina_director,nvl(ad4.nomina_director,nvl(ad5.nomina_director,nvl(ad6.nomina_director,nvl(ad7.nomina_director,nvl(ad8.nomina_director,nvl(ad9.nomina_director,nvl(ad10.nomina_director,nvl(ad11.nomina_director,nvl(ad12.nomina_director,nvl(ad13.nomina_director,nvl(ad14.nomina_director,nvl(ad15.nomina_director,nvl(ad16.nomina_director,nvl(ad17.nomina_director,nvl(ad18.nomina_director,nvl(ad19.nomina_director,nvl(ad20.nomina_director,'')))))))))))))))))))) nomina_director
, nvl(ad1.director,nvl(ad2.director,nvl(ad3.director,nvl(ad4.director,nvl(ad5.director,nvl(ad6.director,nvl(ad7.director,nvl(ad8.director,nvl(ad9.director,nvl(ad10.director,nvl(ad11.director,nvl(ad12.director,nvl(ad13.director,nvl(ad14.director,nvl(ad15.director,nvl(ad16.director,nvl(ad17.director,nvl(ad18.director,nvl(ad19.director,nvl(ad20.director,'')))))))))))))))))))) director
from cat_master_oracle_fusion.sch_gold_layer.per_manager_hrchy_cf_prd pmhc
left join alp_directores ad1
on pmhc.level1_manager_id = ad1.person_id
and ad1.reg = 1
left join alp_directores ad2
on pmhc.level2_manager_id = ad2.person_id
and ad2.reg = 1
left join alp_directores ad3
on pmhc.level3_manager_id = ad3.person_id
and ad3.reg = 1
left join alp_directores ad4
on pmhc.level4_manager_id = ad4.person_id
and ad4.reg = 1
left join alp_directores ad5
on pmhc.level5_manager_id = ad5.person_id
and ad5.reg = 1
left join alp_directores ad6
on pmhc.level6_manager_id = ad6.person_id
and ad6.reg = 1
left join alp_directores ad7
on pmhc.level7_manager_id = ad7.person_id
and ad7.reg = 1
left join alp_directores ad8
on pmhc.level8_manager_id = ad8.person_id
and ad8.reg = 1
left join alp_directores ad9
on pmhc.level9_manager_id = ad9.person_id
and ad9.reg = 1
left join alp_directores ad10
on pmhc.level10_manager_id = ad10.person_id
and ad10.reg = 1
left join alp_directores ad11
on pmhc.level11_manager_id = ad11.person_id
and ad11.reg = 1
left join alp_directores ad12
on pmhc.level2_manager_id = ad12.person_id
and ad12.reg = 1
left join alp_directores ad13
on pmhc.level3_manager_id = ad13.person_id
and ad13.reg = 1
left join alp_directores ad14
on pmhc.level4_manager_id = ad14.person_id
and ad14.reg = 1
left join alp_directores ad15
on pmhc.level5_manager_id = ad15.person_id
and ad15.reg = 1
left join alp_directores ad16
on pmhc.level6_manager_id = ad16.person_id
and ad16.reg = 1
left join alp_directores ad17
on pmhc.level7_manager_id = ad17.person_id
and ad17.reg = 1
left join alp_directores ad18
on pmhc.level8_manager_id = ad18.person_id
and ad18.reg = 1
left join alp_directores ad19
on pmhc.level9_manager_id = ad19.person_id
and ad19.reg = 1
left join alp_directores ad20
on pmhc.level10_manager_id = ad20.person_id
and ad20.reg = 1
where current_date between pmhc.effective_start_date
and pmhc.effective_end_date)
, alp_cat_fnd_lookups as
( select flv.lookuptype type
, flv.lookupcode code
, flvt.meaning
, flv.setid set_id
, flv.tag
, flv.viewapplicationid view_appl_id
, flv.enabledflag enabled
, flv.startdateactive start_date
, flv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_prd flv
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvt
on flv.lookuptype = flvt.lookuptype
and flv.lookupcode = flvt.lookupcode
and flv.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets_prd ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo_prd ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo_prd ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_legislative_info as
( select person_id
, marital_status
, highest_education_level
, sex
, ROW_NUMBER() OVER (partition by person_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.per_people_legislative_f_prd
where current_date() between effective_start_date
and effective_end_date)
, alp_positions_all as
( select business_unit_id
, organization_id
, position_id
, location_id
, job_id
, position_code
, ROW_NUMBER() OVER (partition by business_unit_id, organization_id, position_id, location_id, job_id order by insertion_date desc) reg
from cat_master_oracle_fusion.sch_gold_layer.hr_all_positions_f_prd
where current_date() between effective_start_date
and effective_end_date)
, alp_as400 as
( select CAST(numemp AS STRING) AS numemp
, 'Inactiva' AS stsemp
, CASE
WHEN CAST(fecbajn AS STRING) = '0' THEN NULL
ELSE date_format(try_to_timestamp(CAST(fecbajn AS STRING), 'yyyyMMdd'),'yyyy-MM-dd HH:mm:ss')
END AS fecbajn
FROM cat_master_alpura.sch_silver_layer.as4_nomperso
WHERE stsemp = 'B')
, alp_empleados as
( select ROW_NUMBER() OVER (partition by papf.person_id order by papf.person_id desc) reg
, papf.person_id
, paaf.assignment_id
, papf.person_number
, pptt.user_person_type
, paaf.assignment_status_type assignment_status_type_code
, flvtpass.meaning assignment_status_type
, ppnf.last_name
, ppnf.previous_last_name
, ppnf.first_name
, ppnf.middle_names
, trim(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || trim(ppnf.previous_last_name),'')
|| ' ' || trim(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || trim(ppnf.middle_names),'') full_name
, pp.region_of_birth
, floor(months_between(current_date(), pp.date_of_birth) / 12) age
, flvt.meaning country_of_birth
, pp.date_of_birth
, pplf.sex
, pplf.marital_status marital_status_code
, flvtms.meaning marital_status
, ppos_min.date_start
, ppos_max.actual_termination_date
, peap.email_address primary_email_address
, ppp.phone_number primary_phone
, paaf.ass_attribute1 checa_tarjeta
, paaf.ass_attribute5 ubicacion_real
, paaf.ass_attribute6 ubicacion_administrativa
, paaf.ass_attribute8 grupo_reloj_chec
, xep.legal_entity_identifier
, flvtle.meaning legal_entity_code
, haoule.name legal_entity
, haoubu.name bu_code
, paaf.ass_attribute9 bu_name
, get(split(paaf.ass_attribute2, '-'), 0) seccion_codigo
, get(split(paaf.ass_attribute2, '-'), 1) seccion
, get(split(paaf.ass_attribute3, '-'), 0) area_codigo
, get(split(paaf.ass_attribute3, '-'), 1) area
, get(split(paaf.ass_attribute4, '-'), 0) area_op_codigo
, get(split(paaf.ass_attribute4, '-'), 1) area_op
, haoudepto.attribute_number1 department_code
, haoudep.name department
, paaf.ass_attribute15
, paaf.assignment_name
, paaf.assignment_number
, asi.name sindicato
, asi.org_information2 seccion_sindicato
, paaf.primary_assignment_flag
, paaf.labour_union_member_flag
, ascgp.value
|| '.'
|| ascuo.value
|| '.'
|| ascc.value
|| '.'
|| ascca.value
|| '.'
|| asccc.value
|| '.'
|| ascta.value
|| '.'
|| ascf.value
|| '.'
|| ascfut.value default_expense_account_code
, ascgp.description
|| '.'
|| ascuo.description
|| '.'
|| ascc.description
|| '.'
|| ascca.description
|| '.'
|| asccc.description
|| '.'
|| ascta.description
|| '.'
|| ascf.description
|| '.'
|| ascfut.description default_expense_account
, ascgp.value cc_compania_code
, ascgp.description cc_compania
, ascuo.value cc_unidad_operativa_code
, ascuo.description cc_unidad_operativa
, ascc.value cc_canal_code
, ascc.description cc_canal
, ascca.value cc_categoria_code
, ascca.description cc_categoria
, asccc.value cc_centro_costo_code
, asccc.description cc_centro_costo
, ascta.value cc_cuenta_code
, ascta.description cc_cuenta
, ascf.value cc_filiales_code
, ascf.description cc_filiales
, ascfut.value cc_futuro_code
, ascfut.description cc_futuro
, paaf.employee_category employee_category_code
, flvtct.meaning employee_category
, paaf.employment_category employment_category_code
, flvtec.meaning employment_category
, ae.email_address email_address
, ap.phone_number phone_number
, ab.bank_name
, ab.bank_account_num_electronic bank_account_num
, pplf.highest_education_level highest_education_level_code
, flvthelc.meaning highest_education_level
, aa.address_line_1
, aa.address_line_2
, aa.town_or_city
, aa.addl_address_attribute2
, aa.postal_code
, aa.region_1
, aa.region_2
, aa.address_id
, hla.internal_location_code ubicacion_codigo
, hlat.location_name ubicacion
, paaf.hourly_salaried_code
, flvthsc.meaning hourly_salaried
, am.manager_person_number
, am.manager_name
, pu.username user_oracle
, pcaftl.collective_agreement_name
, aj.job_code puesto_code
, aj.name puesto
, ag.grade_code
, ag.name grade_name
--, cs.salary_amount salary
, imss.national_identifier_number imss
, curp.national_identifier_number curp
, rfc.national_identifier_number rfc
, issste.national_identifier_number issste
, fgad.national_identifier_number fgad
, msd.national_identifier_number msd
, nid.national_identifier_number nid
, hapf.position_code
, haoule.name
|| '|'
|| paaf.ass_attribute9
|| '|'
|| hlat.location_name
|| '|'
|| haoudep.name
|| '|'
|| get(split(paaf.ass_attribute2, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute3, '-'), 1)
|| '|'
|| get(split(paaf.ass_attribute4, '-'), 1)
|| '|'
|| paaf.assignment_name position_name
, pcf.type contract_type_code
, flvtc.meaning contract_type
, pcf.contract_number
, nvl(asu.subordinados,0) subordinados
, papf.creation_date
, papf.created_by
, papf.last_update_date
, papf.last_updated_by
, papf.last_update_login
, adr.puesto_director
, adr.nomina_director
, adr.director
, nvl(as4.stsemp,flvtpass.meaning) status_as400
, nvl(as4.fecbajn,ppos_max.actual_termination_date) fecha_baja_as400
from cat_master_oracle_fusion.sch_gold_layer.per_all_people_f_prd papf
join alp_asignacion paaf
on paaf.person_id = papf.person_id
and paaf.assignment_type = 'E'
and paaf.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_person_names_f_prd ppnf
on paaf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and current_date() between ppnf.effective_start_date
and ppnf.effective_end_date
join alp_periods_of_service ppos_min
on paaf.person_id = ppos_min.person_id
and ppos_min.min_period = 1
join alp_periods_of_service ppos_max
on paaf.person_id = ppos_max.person_id
and ppos_max.max_period = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_email_addresses_prd peap
on papf.primary_email_id = peap.email_address_id
left join cat_master_oracle_fusion.sch_gold_layer.per_phones_prd ppp
on papf.primary_phone_id = ppp.phone_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on paaf.business_unit_id = haou.organization_id
left join alp_legislative_info pplf
on papf.person_id = pplf.person_id
and pplf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_codecombinationextractpvo_prd gcc
on paaf.default_code_comb_id = gcc.codecombinationcodecombinationid
join alp_addresses aa
on papf.person_id = aa.person_id
and aa.reg = 1
join cat_master_oracle_fusion.sch_gold_layer.per_employees_x_prd pex
on paaf.person_id = pex.person_id
and paaf.assignment_id = pex.assignment_id
join cat_master_oracle_fusion.sch_gold_layer.per_person_types_tl_prd pptt
on pex.person_type_id = pptt.person_type_id
and pptt.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.per_persons_prd pp
on papf.person_id = pp.person_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haoubu
on paaf.business_unit_id = haoubu.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haoudep
on paaf.organization_id = haoudep.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haoudepto
on haoudep.organization_id = haoudepto.organization_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haoule
on paaf.legal_entity_id = haoule.organization_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_legal_entities_prd hle
on haoule.organization_id = hle.organization_id
and hle.classification_code = 'HCM_LEMP'
left join cat_master_oracle_fusion.sch_gold_layer.xle_entity_profiles_prd xep
on hle.legal_entity_id = xep.legal_entity_id
left join alp_sindicatos asi
on paaf.union_id = asi.organization_id
left join alp_emails ae
on papf.person_id = ae.person_id
and ae.reg = 1
left join alp_phones ap
on papf.person_id = ap.person_id
and ap.reg = 1
left join alp_bancos ab
on paaf.assignment_id = ab.assignment_id
and ab.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_prd hla
on paaf.location_id = hla.location_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_locations_all_tl_prd hlat
on hla.location_code = hlat.location_code
and hla.location_details_id = hlat.location_details_id
and hlat.language = 'E'
left join alp_managers am
on paaf.person_id = am.person_id
and paaf.assignment_id = am.assignment_id
and am.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.hcm_hcm_user_userextractpvo_prd pu
on papf.person_id = pu.personid
left join cat_master_oracle_fusion.sch_gold_layer.per_col_agreements_f_tl_prd pcaftl
on paaf.collective_agreement_id = pcaftl.collective_agreement_id
and pcaftl.language = 'E'
left join alp_jobs aj
on paaf.job_id = aj.job_id
and aj.reg = 1
left join alp_grades ag
on paaf.grade_id = ag.grade_id
left join cat_master_oracle_fusion.sch_gold_layer.cmp_salary_prd cs
on paaf.assignment_id = cs.assignment_id
and paaf.assignment_type = cs.assignment_type
and current_date() between cs.date_from
and cs.date_to
left join alp_documentos imss
on papf.person_id = imss.person_id
and imss.reg = 1
and imss.national_identifier_type = 'IMSS'
left join alp_documentos curp
on papf.person_id = curp.person_id
and curp.reg = 1
and curp.national_identifier_type = 'CURP'
left join alp_documentos rfc
on papf.person_id = rfc.person_id
and rfc.reg = 1
and rfc.national_identifier_type = 'RFC'
left join alp_documentos issste
on papf.person_id = issste.person_id
and issste.reg = 1
and issste.national_identifier_type = 'ISSSTE'
left join alp_documentos fgad
on papf.person_id = fgad.person_id
and fgad.reg = 1
and fgad.national_identifier_type = 'FGAD'
left join alp_documentos msd
on papf.person_id = msd.person_id
and msd.reg = 1
and msd.national_identifier_type = 'MSD'
left join alp_documentos nid
on papf.person_id = nid.person_id
and nid.reg = 1
and nid.national_identifier_type = 'NID'
left join alp_positions_all hapf
on paaf.business_unit_id = hapf.business_unit_id
and paaf.organization_id = hapf.organization_id
and paaf.position_id = hapf.position_id
and paaf.location_id = hapf.location_id
and paaf.job_id = hapf.job_id
and hapf.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.per_contracts_f_prd pcf
on paaf.contract_id = pcf.contract_id
and current_date() between pcf.effective_start_date
and pcf.effective_end_date
left join alp_subalternos asu
on papf.person_id = asu.person_id
left join alp_dirtores_responsables adr
on paaf.person_id = adr.person_id
and paaf.assignment_id = adr.assignment_id
and adr.reg = 1
left join alp_as400 as4
on papf.person_number = as4.numemp
left join alp_cat_fnd_lookups flvt
on pp.country_of_birth = flvt.code
and flvt.type = 'HZ_DOMAIN_SUFFIX_LIST'
left join alp_cat_fnd_lookups flvtms
on pplf.marital_status = flvtms.code
and flvtms.type = 'MAR_STATUS'
left join alp_cat_fnd_lookups flvtle
on xep.legal_entity_identifier = flvtle.meaning
and flvtle.type = 'ALP_REPORTE_EMPLEADOS'
left join alp_cat_fnd_lookups flvtct
on paaf.employee_category = flvtct.code
and flvtct.type = 'EMPLOYEE_CATG'
left join alp_cat_fnd_lookups flvtec
on paaf.employment_category = flvtec.code
and flvtec.type = 'EMP_CAT'
left join alp_cat_fnd_lookups flvtpass
on paaf.assignment_status_type = flvtpass.code
and flvtpass.type = 'PER_ASS_SYS_STATUS'
left join alp_cat_fnd_lookups flvthelc
on pplf.highest_education_level = flvthelc.code
and flvthelc.type = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
left join alp_cat_fnd_lookups flvthsc
on paaf.hourly_salaried_code = flvthsc.code
and flvthsc.type = 'HOURLY_SALARIED_CODE'
left join alp_cat_fnd_lookups flvtc
on pcf.type = flvtc.code
and flvtc.type = 'CONTRACT_TYPE'
left join alp_cat_fnd_flex_fields ascgp
on gcc.codecombinationsegment1 = ascgp.value
and ascgp.flex_value_set_name = 'GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascuo
on gcc.codecombinationsegment2 = ascuo.value
and ascuo.flex_value_set_name = 'UNIDAD OPERATIVA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascc
on gcc.codecombinationsegment3 = ascc.value
and ascc.flex_value_set_name = 'CANAL GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascca
on gcc.codecombinationsegment4 = ascca.value
and ascca.flex_value_set_name = 'CATEGORIA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields asccc
on gcc.codecombinationsegment5 = asccc.value
and asccc.flex_value_set_name = 'CENTRO DE COSTOS GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascta
on gcc.codecombinationsegment6 = ascta.value
and ascta.flex_value_set_name = 'CUENTA GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascf
on gcc.codecombinationsegment7 = ascf.value
and ascf.flex_value_set_name = 'FILIALES GPLP_PRIMARIO'
left join alp_cat_fnd_flex_fields ascfut
on gcc.codecombinationsegment8 = ascfut.value
and ascfut.flex_value_set_name = 'FUTURO GPLP_PRIMARIO'
where current_date() between papf.effective_start_date
and papf.effective_end_date)
select person_id,
assignment_id,
person_number,
user_person_type,
assignment_status_type_code,
assignment_status_type,
last_name,
previous_last_name,
first_name,
middle_names,
full_name,
region_of_birth,
age,
country_of_birth,
date_of_birth,
sex,
marital_status_code,
marital_status,
date_start,
actual_termination_date,
primary_email_address,
primary_phone,
checa_tarjeta,
ubicacion_real,
ubicacion_administrativa,
grupo_reloj_chec,
legal_entity_identifier,
legal_entity_code,
legal_entity,
bu_code,
bu_name,
seccion_codigo,
seccion,
area_codigo,
area,
area_op_codigo,
area_op,
department_code,
department,
ass_attribute15,
assignment_name,
assignment_number,
sindicato,
seccion_sindicato,
primary_assignment_flag,
labour_union_member_flag,
default_expense_account_code,
default_expense_account,
cc_compania_code,
cc_compania,
cc_unidad_operativa_code,
cc_unidad_operativa,
cc_canal_code,
cc_canal,
cc_categoria_code,
cc_categoria,
cc_centro_costo_code,
cc_centro_costo,
cc_cuenta_code,
cc_cuenta,
cc_filiales_code,
cc_filiales,
cc_futuro_code,
cc_futuro,
employee_category_code,
employee_category,
employment_category_code,
employment_category,
email_address,
phone_number,
bank_name,
bank_account_num,
highest_education_level_code,
highest_education_level,
address_line_1,
address_line_2,
town_or_city,
addl_address_attribute2,
postal_code,
region_1,
region_2,
address_id,
ubicacion_codigo,
ubicacion,
hourly_salaried_code,
hourly_salaried,
manager_person_number,
manager_name,
user_oracle,
collective_agreement_name,
puesto_code,
puesto,
grade_code,
grade_name,
imss,
curp,
rfc,
issste,
fgad,
msd,
nid,
position_code,
position_name,
contract_type_code,
contract_type,
contract_number,
subordinados,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
puesto_director,
nomina_director,
director,
status_as400,
fecha_baja_as400
from alp_empleados
where reg = 1
WITH alp_asignacion AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY person_id
ORDER BY assignment_id DESC) reg
, person_id
, assignment_id
, assignment_name
, assignment_number
, assignment_type
, assignment_status_type
, legal_entity_id
, business_unit_id
, union_id
, organization_id
, location_id
, employee_category
, employment_category
, primary_assignment_flag
, labour_union_member_flag
, hourly_salaried_code
, default_code_comb_id
, collective_agreement_id
, position_id
, job_id
, grade_id
, contract_id
, ass_attribute1
, ass_attribute2
, ass_attribute3
, ass_attribute4
, ass_attribute5
, ass_attribute6
, ass_attribute8
, ass_attribute9
, ass_attribute15
FROM per_all_assignments_f
WHERE assignment_type = 'E'
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date)
, alp_periods_of_service AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY person_id
ORDER BY period_of_service_id DESC) max_period
, ROW_NUMBER()
OVER ( PARTITION BY person_id
ORDER by period_of_service_id) min_period
, person_id
, date_start
, actual_termination_date
FROM per_periods_of_service)
, alp_addresses AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY ppauf.person_id
ORDER BY ppauf.effective_end_date DESC
, ppauf.person_addr_usage_id DESC
, paf.effective_end_date DESC) reg
, ppauf.person_id
, paf.address_line_1
, paf.address_line_2
, paf.town_or_city
, paf.addl_address_attribute2
, paf.postal_code
, paf.region_1
, paf.region_2
, paf.address_id
FROM per_person_addr_usages_f ppauf
, per_addresses_f paf
WHERE ppauf.address_id = paf.address_id)
, alp_sindicatos AS
( SELECT haou.organization_id
, hoif.org_information2
, haou.name
FROM hr_all_organization_units haou
, hr_organization_information_f hoif
WHERE haou.organization_id = hoif.organization_id
AND SYSDATE BETWEEN hoif.effective_start_date
AND hoif.effective_end_date)
, alp_emails AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY person_id
ORDER BY email_address_id DESC) reg
, person_id
, email_address
FROM per_email_addresses
WHERE email_address NOT LIKE '%@alpura.com'
AND SYSDATE BETWEEN date_from
AND date_to)
, alp_phones AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY person_id
ORDER BY phone_id DESC) reg
, person_id
, phone_number
FROM per_phones
WHERE phone_type = 'H1'
AND SYSDATE BETWEEN date_from
AND date_to)
, alp_bancos AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY prgd.assignment_id
ORDER BY pppmf.personal_payment_method_id DESC) reg
, prgd.assignment_id
, pba.bank_name
, pba.bank_account_num_electronic
FROM pay_rel_groups_dn prgd
, pay_person_pay_methods_f pppmf
, pay_bank_accounts pba
WHERE prgd.payroll_relationship_id = pppmf.payroll_relationship_id
AND pppmf.bank_account_id = pba.bank_account_id
AND pppmf.percentage IS NOT NULL
AND pppmf.percentage IS NOT NULL
AND SYSDATE BETWEEN prgd.start_date
AND prgd.end_date)
, alp_managers AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY paaf.person_id
ORDER BY paaf.assignment_id DESC) reg
, pmhc.person_id
, pmhc.assignment_id
, papf.person_number
, mpapf.person_number manager_person_number
, mgr.last_name
|| NVL2(mgr.previous_last_name,' ' || mgr.previous_last_name,'')
|| ' ' || mgr.first_name
|| NVL2(mgr.middle_names,' ' || mgr.middle_names,'') manager_name
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_manager_hrchy_cf pmhc
, per_person_names_f mgr
, per_all_people_f mpapf
WHERE papf.person_id = paaf.person_id
AND paaf.assignment_id = pmhc.assignment_id
AND paaf.person_id = pmhc.person_id
AND pmhc.level1_manager_id = mgr.person_id
AND mgr.person_id = mpapf.person_id
AND paaf.assignment_type = 'E'
AND paaf.assignment_status_type = 'ACTIVE'
AND mgr.name_type = 'GLOBAL'
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN pmhc.effective_start_date
AND pmhc.effective_end_date
AND SYSDATE BETWEEN mgr.effective_start_date
AND mgr.effective_end_date
AND SYSDATE BETWEEN mpapf.effective_start_date
AND mpapf.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date)
, alp_jobs AS
( SELECT pjf.job_id
, pjf.job_code
, pjft.name
FROM per_jobs_f pjf
, per_jobs_f_tl pjft
WHERE pjf.job_id = pjft.job_id
AND pjft.language = 'E'
AND SYSDATE BETWEEN pjf.effective_start_date
AND pjf.effective_end_date
AND SYSDATE BETWEEN pjft.effective_start_date
AND pjft.effective_end_date)
, alp_grades AS
( SELECT pgf.grade_id
, pgf.grade_code
, pgft.name
FROM per_grades_f pgf
, per_grades_f_tl pgft
WHERE pgf.grade_id = pgft.grade_id
AND pgft.language = 'E'
AND SYSDATE BETWEEN pgf.effective_start_date
AND pgf.effective_end_date
AND SYSDATE BETWEEN pgft.effective_start_date
AND pgft.effective_end_date)
, alp_documentos AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY person_id
, national_identifier_type
ORDER BY national_identifier_id DESC) reg
, person_id
, national_identifier_type
, national_identifier_number
FROM per_national_identifiers)
, alp_subalternos AS
( SELECT pmhc.level1_manager_id person_id
, COUNT(*) subordinados
FROM per_manager_hrchy_cf pmhc
, per_all_people_f papf
, per_person_names_f mgr
, per_all_assignments_f paaf
WHERE pmhc.person_id = papf.person_id
AND pmhc.person_id = mgr.person_id
AND pmhc.person_id = paaf.person_id
AND pmhc.assignment_id = paaf.assignment_id
AND mgr.name_type = 'MX'
AND paaf.assignment_status_type = 'ACTIVE'
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN mgr.effective_start_date
AND mgr.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN pmhc.effective_start_date
AND pmhc.effective_end_date
GROUP BY pmhc.level1_manager_id)
, alp_directores AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY paaf.person_id
ORDER BY paaf.assignment_id DESC) reg
, paaf.person_id
, pjft.name puesto_director
, papf.person_number nomina_director
, TRIM(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' ' || TRIM(ppnf.previous_last_name),'')
|| ' ' || TRIM(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || TRIM(ppnf.middle_names),'') director
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_jobs_f pjf
, per_jobs_f_tl pjft
, per_person_names_f ppnf
WHERE papf.person_id = paaf.person_id
AND paaf.job_id = pjf.job_id
AND pjf.job_id = pjft.job_id
AND paaf.person_id = ppnf.person_id
AND paaf.assignment_type = 'E'
AND paaf.assignment_status_type = 'ACTIVE'
AND pjft.language = 'E'
AND ppnf.name_type = 'MX'
AND (UPPER(pjft.name) LIKE '%DIR %'
OR UPPER(pjft.name) LIKE '%DIRECTOR%')
AND UPPER(pjft.name) NOT LIKE '%ASISTENTE%'
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date)
, alp_dirtores_responsables AS
( SELECT pmhc.person_id
, pmhc.assignment_id
, NVL(ad1.puesto_director,NVL(ad2.puesto_director,NVL(ad3.puesto_director,NVL(ad4.puesto_director,NVL(ad5.puesto_director,NVL(ad6.puesto_director,NVL(ad7.puesto_director,NVL(ad8.puesto_director,NVL(ad9.puesto_director,NVL(ad10.puesto_director,NVL(ad11.puesto_director,NVL(ad12.puesto_director,NVL(ad13.puesto_director,NVL(ad14.puesto_director,NVL(ad15.puesto_director,NVL(ad16.puesto_director,NVL(ad17.puesto_director,NVL(ad18.puesto_director,NVL(ad19.puesto_director,NVL(ad20.puesto_director,'')))))))))))))))))))) puesto_director
, NVL(ad1.nomina_director,NVL(ad2.nomina_director,NVL(ad3.nomina_director,NVL(ad4.nomina_director,NVL(ad5.nomina_director,NVL(ad6.nomina_director,NVL(ad7.nomina_director,NVL(ad8.nomina_director,NVL(ad9.nomina_director,NVL(ad10.nomina_director,NVL(ad11.nomina_director,NVL(ad12.nomina_director,NVL(ad13.nomina_director,NVL(ad14.nomina_director,NVL(ad15.nomina_director,NVL(ad16.nomina_director,NVL(ad17.nomina_director,NVL(ad18.nomina_director,NVL(ad19.nomina_director,NVL(ad20.nomina_director,'')))))))))))))))))))) nomina_director
, NVL(ad1.director,NVL(ad2.director,NVL(ad3.director,NVL(ad4.director,NVL(ad5.director,NVL(ad6.director,NVL(ad7.director,NVL(ad8.director,NVL(ad9.director,NVL(ad10.director,NVL(ad11.director,NVL(ad12.director,NVL(ad13.director,NVL(ad14.director,NVL(ad15.director,NVL(ad16.director,NVL(ad17.director,NVL(ad18.director,NVL(ad19.director,NVL(ad20.director,'')))))))))))))))))))) director
FROM per_manager_hrchy_cf pmhc
, alp_directores ad1
, alp_directores ad2
, alp_directores ad3
, alp_directores ad4
, alp_directores ad5
, alp_directores ad6
, alp_directores ad7
, alp_directores ad8
, alp_directores ad9
, alp_directores ad10
, alp_directores ad11
, alp_directores ad12
, alp_directores ad13
, alp_directores ad14
, alp_directores ad15
, alp_directores ad16
, alp_directores ad17
, alp_directores ad18
, alp_directores ad19
, alp_directores ad20
WHERE pmhc.level1_manager_id = ad1.person_id(+)
AND pmhc.level2_manager_id = ad2.person_id(+)
AND pmhc.level3_manager_id = ad3.person_id(+)
AND pmhc.level4_manager_id = ad4.person_id(+)
AND pmhc.level5_manager_id = ad5.person_id(+)
AND pmhc.level6_manager_id = ad6.person_id(+)
AND pmhc.level7_manager_id = ad7.person_id(+)
AND pmhc.level8_manager_id = ad8.person_id(+)
AND pmhc.level9_manager_id = ad9.person_id(+)
AND pmhc.level10_manager_id = ad10.person_id(+)
AND pmhc.level11_manager_id = ad11.person_id(+)
AND pmhc.level12_manager_id = ad12.person_id(+)
AND pmhc.level13_manager_id = ad13.person_id(+)
AND pmhc.level14_manager_id = ad14.person_id(+)
AND pmhc.level15_manager_id = ad15.person_id(+)
AND pmhc.level16_manager_id = ad16.person_id(+)
AND pmhc.level17_manager_id = ad17.person_id(+)
AND pmhc.level18_manager_id = ad18.person_id(+)
AND pmhc.level19_manager_id = ad19.person_id(+)
AND pmhc.level20_manager_id = ad20.person_id(+)
AND ad1.reg(+) = 1
AND ad2.reg(+) = 1
AND ad3.reg(+) = 1
AND ad4.reg(+) = 1
AND ad5.reg(+) = 1
AND ad6.reg(+) = 1
AND ad7.reg(+) = 1
AND ad8.reg(+) = 1
AND ad9.reg(+) = 1
AND ad10.reg(+) = 1
AND ad11.reg(+) = 1
AND ad12.reg(+) = 1
AND ad13.reg(+) = 1
AND ad14.reg(+) = 1
AND ad15.reg(+) = 1
AND ad16.reg(+) = 1
AND ad17.reg(+) = 1
AND ad18.reg(+) = 1
AND ad19.reg(+) = 1
AND ad20.reg(+) = 1
AND SYSDATE BETWEEN pmhc.effective_start_date
AND pmhc.effective_end_date)
, alp_cat_fnd_lookups AS
( SELECT lookup_type type
, lookup_code code
, meaning
, set_id
, tag
, view_application_id view_appl_id
, enabled_flag enabled
, start_date_active start_date
, end_date_active end_date
FROM fnd_lookup_values
WHERE language = 'E')
, alp_cat_fnd_flex_fields AS
( SELECT ffvs.flex_value_set_id
, ffv.flex_value_id
, ffvs.flex_value_set_name
, ffv.flex_value value
, ffvt.description
, ffv.enabled_flag
, ffv.start_date_active start_date
, ffv.end_date_active end_date
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = 'E')
, alp_legislative_info AS
( SELECT person_id
, marital_status
, highest_education_level
, sex
FROM per_people_legislative_f
WHERE SYSDATE BETWEEN effective_start_date
AND effective_end_date)
, alp_positions_all as
( SELECT business_unit_id
, organization_id
, position_id
, location_id
, job_id
, position_code
FROM hr_all_positions_f
WHERE SYSDATE BETWEEN effective_start_date
AND effective_end_date)
, alp_empleados AS
( SELECT ROW_NUMBER()
OVER ( PARTITION BY papf.person_id
ORDER BY papf.person_id DESC) reg
, papf.person_id
, paaf.assignment_id
, papf.person_number
, pptt.user_person_type
, paaf.assignment_status_type assignment_status_type_code
, flvtpass.meaning assignment_status_type
, ppnf.last_name
, ppnf.previous_last_name
, ppnf.first_name
, ppnf.middle_names
, TRIM(ppnf.last_name)
|| NVL2(ppnf.previous_last_name,' '
|| TRIM(ppnf.previous_last_name),'')
|| ' '
|| TRIM(ppnf.first_name)
|| NVL2(ppnf.middle_names,' ' || TRIM(ppnf.middle_names),'') full_name
, pp.region_of_birth
, TRUNC(MONTHS_BETWEEN(SYSDATE, pp.date_of_birth) / 12) age
, flvt.meaning country_of_birth
, pp.date_of_birth
, pplf.sex
, pplf.marital_status marital_status_code
, flvtms.meaning marital_status
, ppos_min.date_start
, ppos_max.actual_termination_date
, peap.email_address primary_email_address
, ppp.phone_number primary_phone
, paaf.ass_attribute1 checa_tarjeta
, paaf.ass_attribute5 ubicacion_real
, paaf.ass_attribute6 ubicacion_administrativa
, paaf.ass_attribute8 grupo_reloj_chec
, xep.legal_entity_identifier
, flvtle.meaning legal_entity_code
, haoule.name legal_entity
, haoubu.name bu_code
, paaf.ass_attribute9 bu_name
, REGEXP_SUBSTR(paaf.ass_attribute2, '[^-]+', 1, 1) seccion_codigo
, REGEXP_SUBSTR(paaf.ass_attribute2, '[^-]+', 1, 2) seccion
, REGEXP_SUBSTR(paaf.ass_attribute3, '[^-]+', 1, 1) area_codigo
, REGEXP_SUBSTR(paaf.ass_attribute3, '[^-]+', 1, 2) area
, REGEXP_SUBSTR(paaf.ass_attribute4, '[^-]+', 1, 1) area_op_codigo
, REGEXP_SUBSTR(paaf.ass_attribute4, '[^-]+', 1, 2) area_op
, haoudepto.attribute_number1 department_code
, haoudep.name department
, paaf.ass_attribute15
, paaf.assignment_name
, paaf.assignment_number
, asi.name sindicato
, asi.org_information2 seccion_sindicato
, paaf.primary_assignment_flag
, paaf.labour_union_member_flag
, ascgp.value
|| '.'
|| ascuo.value
|| '.'
|| ascc.value
|| '.'
|| ascca.value
|| '.'
|| asccc.value
|| '.'
|| ascta.value
|| '.'
|| ascf.value
|| '.'
|| ascfut.value default_expense_account_code
, ascgp.description
|| '.'
|| ascuo.description
|| '.'
|| ascc.description
|| '.'
|| ascca.description
|| '.'
|| asccc.description
|| '.'
|| ascta.description
|| '.'
|| ascf.description
|| '.'
|| ascfut.description default_expense_account
, ascgp.value cc_compania_code
, ascgp.description cc_compania
, ascuo.value cc_unidad_operativa_code
, ascuo.description cc_unidad_operativa
, ascc.value cc_canal_code
, ascc.description cc_canal
, ascca.value cc_categoria_code
, ascca.description cc_categoria
, asccc.value cc_centro_costo_code
, asccc.description cc_centro_costo
, ascta.value cc_cuenta_code
, ascta.description cc_cuenta
, ascf.value cc_filiales_code
, ascf.description cc_filiales
, ascfut.value cc_futuro_code
, ascfut.description cc_futuro
, paaf.employee_category employee_category_code
, flvtct.meaning employee_category
, paaf.employment_category employment_category_code
, flvtec.meaning employment_category
, ae.email_address email_address
, ap.phone_number phone_number
, ab.bank_name
, ab.bank_account_num_electronic bank_account_num
, pplf.highest_education_level highest_education_level_code
, flvthelc.meaning highest_education_level
, aa.address_line_1
, aa.address_line_2
, aa.town_or_city
, aa.addl_address_attribute2
, aa.postal_code
, aa.region_1
, aa.region_2
, aa.address_id
, hla.internal_location_code ubicacion_codigo
, hlat.location_name ubicacion
, paaf.hourly_salaried_code
, flvthsc.meaning hourly_salaried
, am.manager_person_number
, am.manager_name
, pu.username user_oracle
, pcaftl.collective_agreement_name
, aj.job_code puesto_code
, aj.name puesto
, ag.grade_code
, ag.name grade_name
--, cs.salary_amount salary
, imss.national_identifier_number imss
, curp.national_identifier_number curp
, rfc.national_identifier_number rfc
, issste.national_identifier_number issste
, fgad.national_identifier_number fgad
, msd.national_identifier_number msd
, nid.national_identifier_number nid
, hapf.position_code
, haoule.name
|| '|'
|| paaf.ass_attribute9
|| '|'
|| hlat.location_name
|| '|'
|| haoudep.name
|| '|'
|| REGEXP_SUBSTR(paaf.ass_attribute2, '[^-]+', 1, 2)
|| '|'
|| REGEXP_SUBSTR(paaf.ass_attribute3, '[^-]+', 1, 2)
|| '|'
|| REGEXP_SUBSTR(paaf.ass_attribute4, '[^-]+', 1, 2)
|| '|'
|| paaf.assignment_name position_name
, pcf.type contract_type_code
, flvtc.meaning contract_type
, pcf.contract_number
, NVL(asu.subordinados,0) subordinados
, papf.creation_date
, papf.created_by
, papf.last_update_date
, papf.last_updated_by
, papf.last_update_login
, adr.puesto_director
, adr.nomina_director
, adr.director
, NVL(/*as4.stsemp*/NULL,flvtpass.meaning) status_as400 -- Es información que se valida primero en AS400
, NVL(/*as4.fecbajn*/NULL,ppos_max.actual_termination_date) fecha_baja_as400 -- Es información que se valida primero en AS400
FROM per_all_people_f papf
, alp_asignacion paaf
, per_person_names_f ppnf
, alp_periods_of_service ppos_min
, alp_periods_of_service ppos_max
, per_email_addresses peap
, per_phones ppp
, hr_all_organization_units haou
, alp_legislative_info pplf
, gl_code_combinations gcc
, alp_addresses aa
, per_employees_x pex
, per_person_types_tl pptt
, per_persons pp
, hr_all_organization_units haoubu
, hr_all_organization_units haoudep
, hr_all_organization_units haoudepto
, hr_all_organization_units haoule
, hr_legal_entities hle
, xle_entity_profiles xep
, alp_sindicatos asi
, alp_emails ae
, alp_phones ap
, alp_bancos ab
, hr_locations_all hla
, hr_locations_all_tl hlat
, alp_managers am
, per_users pu
, per_col_agreements_f_tl pcaftl
, alp_jobs aj
, alp_grades ag
, cmp_salary cs
, alp_documentos imss
, alp_documentos curp
, alp_documentos rfc
, alp_documentos issste
, alp_documentos fgad
, alp_documentos msd
, alp_documentos nid
, alp_positions_all hapf
, per_contracts_f pcf
, alp_subalternos asu
, alp_dirtores_responsables adr
, alp_cat_fnd_lookups flvt
, alp_cat_fnd_lookups flvtms
, alp_cat_fnd_lookups flvtle
, alp_cat_fnd_lookups flvtct
, alp_cat_fnd_lookups flvtec
, alp_cat_fnd_lookups flvtpass
, alp_cat_fnd_lookups flvthelc
, alp_cat_fnd_lookups flvthsc
, alp_cat_fnd_lookups flvtc
, alp_cat_fnd_flex_fields ascgp
, alp_cat_fnd_flex_fields ascuo
, alp_cat_fnd_flex_fields ascc
, alp_cat_fnd_flex_fields ascca
, alp_cat_fnd_flex_fields asccc
, alp_cat_fnd_flex_fields ascta
, alp_cat_fnd_flex_fields ascf
, alp_cat_fnd_flex_fields ascfut
WHERE paaf.person_id = papf.person_id
AND paaf.person_id = ppnf.person_id
AND paaf.person_id = ppos_min.person_id
AND paaf.person_id = ppos_max.person_id
AND papf.primary_email_id = peap.email_address_id(+)
AND papf.primary_phone_id = ppp.phone_id(+)
AND paaf.business_unit_id = haou.organization_id(+)
AND papf.person_id = pplf.person_id(+)
AND paaf.default_code_comb_id = gcc.code_combination_id
AND papf.person_id = aa.person_id
AND paaf.person_id = pex.person_id
AND paaf.assignment_id = pex.assignment_id
AND pex.person_type_id = pptt.person_type_id
AND papf.person_id = pp.person_id
AND paaf.business_unit_id = haoubu.organization_id
AND paaf.organization_id = haoudep.organization_id
AND haoudep.organization_id = haoudepto.organization_id(+)
AND paaf.legal_entity_id = haoule.organization_id
AND haoule.organization_id = hle.organization_id(+)
AND hle.legal_entity_id = xep.legal_entity_id(+)
AND paaf.union_id = asi.organization_id(+)
AND papf.person_id = ae.person_id(+)
AND papf.person_id = ap.person_id(+)
AND paaf.assignment_id = ab.assignment_id(+)
AND paaf.location_id = hla.location_id(+)
AND hla.location_code = hlat.location_code(+)
AND hla.location_details_id = hlat.location_details_id(+)
AND paaf.person_id = am.person_id(+)
AND paaf.assignment_id = am.assignment_id(+)
AND papf.person_id = pu.person_id(+)
AND paaf.collective_agreement_id = pcaftl.collective_agreement_id(+)
AND paaf.job_id = aj.job_id(+)
AND paaf.grade_id = ag.grade_id(+)
AND paaf.assignment_id = cs.assignment_id(+)
AND paaf.assignment_type = cs.assignment_type(+)
AND papf.person_id = imss.person_id(+)
AND papf.person_id = curp.person_id(+)
AND papf.person_id = rfc.person_id(+)
AND papf.person_id = issste.person_id(+)
AND papf.person_id = fgad.person_id(+)
AND papf.person_id = msd.person_id(+)
AND papf.person_id = nid.person_id(+)
AND paaf.business_unit_id = hapf.business_unit_id(+)
AND paaf.organization_id = hapf.organization_id(+)
AND paaf.position_id = hapf.position_id(+)
AND paaf.location_id = hapf.location_id(+)
AND paaf.job_id = hapf.job_id(+)
AND paaf.contract_id = pcf.contract_id(+)
AND papf.person_id = asu.person_id(+)
AND paaf.person_id = adr.person_id(+)
AND paaf.assignment_id = adr.assignment_id(+)
AND pp.country_of_birth = flvt.code(+)
AND pplf.marital_status = flvtms.code(+)
AND xep.legal_entity_identifier = flvtle.meaning(+)
AND paaf.employee_category = flvtct.code(+)
AND paaf.employment_category = flvtec.code(+)
AND paaf.assignment_status_type = flvtpass.code(+)
AND pplf.highest_education_level = flvthelc.code(+)
AND paaf.hourly_salaried_code = flvthsc.code(+)
AND pcf.type = flvtc.code(+)
AND gcc.segment1 = ascgp.value(+)
AND gcc.segment2 = ascuo.value(+)
AND gcc.segment3 = ascc.value(+)
AND gcc.segment4 = ascca.value(+)
AND gcc.segment5 = asccc.value(+)
AND gcc.segment6 = ascta.value(+)
AND gcc.segment7 = ascf.value(+)
AND gcc.segment8 = ascfut.value(+)
AND hle.classification_code(+) = 'HCM_LEMP'
AND paaf.assignment_type = 'E'
AND pptt.language = 'E'
AND hlat.language(+) = 'E'
AND pcaftl.language(+) = 'E'
AND ppnf.name_type = 'GLOBAL'
AND imss.national_identifier_type(+) = 'IMSS'
AND curp.national_identifier_type(+) = 'CURP'
AND rfc.national_identifier_type(+) = 'RFC'
AND issste.national_identifier_type(+) = 'ISSSTE'
AND fgad.national_identifier_type(+) = 'FGAD'
AND msd.national_identifier_type(+) = 'MSD'
AND nid.national_identifier_type(+) = 'NID'
AND flvt.type(+) = 'HZ_DOMAIN_SUFFIX_LIST'
AND flvtms.type(+) = 'MAR_STATUS'
AND flvtle.type(+) = 'ALP_REPORTE_EMPLEADOS'
AND flvtct.type(+) = 'EMPLOYEE_CATG'
AND flvtec.type(+) = 'EMP_CAT'
AND flvtpass.type(+) = 'PER_ASS_SYS_STATUS'
AND flvthelc.type(+) = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
AND flvthsc.type(+) = 'HOURLY_SALARIED_CODE'
AND flvtc.type(+) = 'CONTRACT_TYPE'
AND ascgp.flex_value_set_name(+) = 'GPLP_PRIMARIO'
AND ascuo.flex_value_set_name(+) = 'UNIDAD OPERATIVA GPLP_PRIMARIO'
AND ascc.flex_value_set_name(+) = 'CANAL GPLP_PRIMARIO'
AND ascca.flex_value_set_name(+) = 'CATEGORIA GPLP_PRIMARIO'
AND asccc.flex_value_set_name(+) = 'CENTRO DE COSTOS GPLP_PRIMARIO'
AND ascta.flex_value_set_name(+) = 'CUENTA GPLP_PRIMARIO'
AND ascf.flex_value_set_name(+) = 'FILIALES GPLP_PRIMARIO'
AND ascfut.flex_value_set_name(+) = 'FUTURO GPLP_PRIMARIO'
AND paaf.reg = 1
AND ppos_min.min_period = 1
AND ppos_max.max_period = 1
AND aa.reg = 1
AND ae.reg(+) = 1
AND ap.reg(+) = 1
AND ab.reg(+) = 1
AND am.reg(+) = 1
AND imss.reg(+) = 1
AND curp.reg(+) = 1
AND rfc.reg(+) = 1
AND issste.reg(+) = 1
AND fgad.reg(+) = 1
AND msd.reg(+) = 1
AND nid.reg(+) = 1
AND SYSDATE BETWEEN ppnf.effective_start_date
AND ppnf.effective_end_date
AND SYSDATE BETWEEN NVL(cs.date_from,SYSDATE)
AND NVL(cs.date_to,SYSDATE)
AND SYSDATE BETWEEN NVL(pcf.effective_start_date,SYSDATE)
AND NVL(pcf.effective_end_date,SYSDATE)
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date)
SELECT person_id
, assignment_id
, person_number
, user_person_type
, assignment_status_type_code
, assignment_status_type
, last_name
, previous_last_name
, first_name
, middle_names
, full_name
, region_of_birth
, age
, country_of_birth
, date_of_birth
, sex
, marital_status_code
, marital_status
, date_start
, actual_termination_date
, primary_email_address
, primary_phone
, checa_tarjeta
, ubicacion_real
, ubicacion_administrativa
, grupo_reloj_chec
, legal_entity_identifier
, legal_entity_code
, legal_entity
, bu_code
, bu_name
, seccion_codigo
, seccion
, area_codigo
, area
, area_op_codigo
, area_op
, department_code
, department
, ass_attribute15
, assignment_name
, assignment_number
, sindicato
, seccion_sindicato
, primary_assignment_flag
, labour_union_member_flag
, default_expense_account_code
, default_expense_account
, cc_compania_code
, cc_compania
, cc_unidad_operativa_code
, cc_unidad_operativa
, cc_canal_code
, cc_canal
, cc_categoria_code
, cc_categoria
, cc_centro_costo_code
, cc_centro_costo
, cc_cuenta_code
, cc_cuenta
, cc_filiales_code
, cc_filiales
, cc_futuro_code
, cc_futuro
, employee_category_code
, employee_category
, employment_category_code
, employment_category
, email_address
, phone_number
, bank_name
, bank_account_num
, highest_education_level_code
, highest_education_level
, address_line_1
, address_line_2
, town_or_city
, addl_address_attribute2
, postal_code
, region_1
, region_2
, address_id
, ubicacion_codigo
, ubicacion
, hourly_salaried_code
, hourly_salaried
, manager_person_number
, manager_name
, user_oracle
, collective_agreement_name
, puesto_code
, puesto
, grade_code
, grade_name
, imss
, curp
, rfc
, issste
, fgad
, msd
, nid
, position_code
, position_name
, contract_type_code
, contract_type
, contract_number
, subordinados
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, puesto_director
, nomina_director
, director
, status_as400
, fecha_baja_as400
FROM alp_empleados
WHERE reg = 1