[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CASE WHEN set_uom = 'Years'
THEN (band_range_high - band_range_low) * 12
WHEN set_uom = 'Months'
THEN (band_range_high - band_range_low)
ELSE to_number(null)
END pow1_no_months
,CASE WHEN set_uom = 'Weeks'
THEN (band_range_high - band_range_low) * 7
WHEN set_uom = 'Days'
THEN (band_range_high - band_range_low)
ELSE to_number(null)
END pow1_no_days
FROM hri_cs_pow_band_ct
WHERE band_sequence = 1
AND wkth_wktyp_sk_fk = 'EMP';
hri_opl_multi_thread.update_parameters
(p_mthd_action_id => p_mthd_action_id,
p_full_refresh => g_full_refresh,
p_global_start_date => l_dbi_collection_start_date);
PROCEDURE bulk_insert_rows IS
BEGIN
IF g_no_rows > 0 THEN
FORALL i IN 1..g_no_rows
INSERT INTO hri_mb_rec_cand_pipln_ct
(time_day_evt_fk,
time_day_evt_end_fk,
time_day_stg_evt_eff_end_fk,
per_person_cand_fk,
per_person_mngr_fk,
per_person_rcrt_fk,
per_person_rmgr_fk,
per_person_auth_fk,
per_person_refr_fk,
per_person_rsed_fk,
per_person_mrgd_fk,
org_organztn_fk,
org_organztn_mrgd_fk,
org_organztn_recr_fk,
geo_location_fk,
job_job_fk,
grd_grade_fk,
pos_position_fk,
prfm_perfband_fk,
rvac_vacncy_fk,
ract_recactvy_fk,
rev_recevent_fk,
rern_recevtrn_fk,
tarn_trmaplrn_fk,
headcount,
fte,
post_hire_nrmlsd_perf_rtng,
event_seq,
appl_ind,
appl_new_ind,
appl_emp_ind,
appl_cwk_ind,
appl_strt_evnt_ind,
appl_strt_nevnt_ind,
asmt_strt_evnt_ind,
asmt_strt_nevnt_ind,
asmt_end_evnt_ind,
asmt_end_nevnt_ind,
offr_extd_evnt_ind,
offr_extd_nevnt_ind,
offr_rjct_evnt_ind,
offr_rjct_nevnt_ind,
offr_acpt_evnt_ind,
offr_acpt_nevnt_ind,
appl_term_evnt_ind,
appl_term_nevnt_ind,
appl_term_vol_evnt_ind,
appl_term_vol_nevnt_ind,
appl_term_invol_evnt_ind,
appl_term_invol_nevnt_ind,
appl_hire_evnt_ind,
appl_hire_nevnt_ind,
hire_evnt_ind,
hire_nevnt_ind,
post_hire_pow1_end_evnt_ind,
post_hire_pow1_end_nevnt_ind,
post_hire_perf_evnt_ind,
post_hire_perf_nevnt_ind,
emp_sprtn_evnt_ind,
emp_sprtn_nevnt_ind,
hire_org_chng_ind,
hire_job_chng_ind,
hire_pos_chng_ind,
hire_grd_chng_ind,
hire_loc_chng_ind,
current_record_ind,
current_stage_strt_ind,
gen_record_ind,
appl_strt_to_asmt_strt_days,
appl_strt_to_asmt_end_days,
appl_strt_to_offr_extd_days,
appl_strt_to_offr_rjct_days,
appl_strt_to_offr_acpt_days,
appl_strt_to_hire_days,
appl_strt_to_term_days,
init_appl_stg_ind,
asmt_stg_ind,
offr_extd_stg_ind,
strt_pndg_stg_ind,
hire_stg_ind,
appl_strt_date,
asmt_strt_date,
asmt_end_date,
offr_extd_date,
offr_rjct_date,
offr_acpt_date,
appl_term_date,
hire_date,
emp_sprtn_date,
event_date,
stage_start_date,
adt_assignment_id,
adt_asg_effctv_start_date,
adt_asg_effctv_end_date,
adt_application_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
VALUES
(g_time_day_evt_fk(i),
g_time_day_evt_end_fk(i),
g_time_day_stg_evt_eff_end_fk(i),
g_person_cand_fk(i),
g_person_mngr_fk(i),
g_person_rcrt_fk(i),
g_person_rmgr_fk(i),
g_person_auth_fk(i),
g_person_refr_fk(i),
g_person_rsed_fk(i),
g_person_mrgd_fk(i),
g_org_organztn_fk(i),
g_org_organztn_mrgd_fk(i),
g_org_organztn_recr_fk(i),
g_geo_location_fk(i),
g_job_job_fk(i),
g_grd_grade_fk(i),
g_pos_position_fk(i),
g_prfm_perfband_fk(i),
g_rvac_vacncy_fk(i),
g_ract_recactvy_fk(i),
g_rev_recevent_fk(i),
g_rern_recevtrn_fk(i),
g_tarn_trmaplrn_fk(i),
g_headcount(i),
g_fte(i),
g_nrmlsd_perf_rtng(i),
g_event_seq(i),
g_appl_ind(i),
g_appl_new_ind(i),
g_appl_emp_ind(i),
g_appl_cwk_ind(i),
g_appl_strt_evnt_ind(i),
g_appl_strt_nevnt_ind(i),
g_asmt_strt_evnt_ind(i),
g_asmt_strt_nevnt_ind(i),
g_asmt_end_evnt_ind(i),
g_asmt_end_nevnt_ind(i),
g_offr_extd_evnt_ind(i),
g_offr_extd_nevnt_ind(i),
g_offr_rjct_evnt_ind(i),
g_offr_rjct_nevnt_ind(i),
g_offr_acpt_evnt_ind(i),
g_offr_acpt_nevnt_ind(i),
g_appl_term_evnt_ind(i),
g_appl_term_nevnt_ind(i),
g_appl_term_vol_evnt_ind(i),
g_appl_term_vol_nevnt_ind(i),
g_appl_term_invol_evnt_ind(i),
g_appl_term_invol_nevnt_ind(i),
g_appl_hire_evnt_ind(i),
g_appl_hire_nevnt_ind(i),
g_hire_evnt_ind(i),
g_hire_nevnt_ind(i),
g_pow1_end_evnt_ind(i),
g_pow1_end_nevnt_ind(i),
g_perf_rtng_evnt_ind(i),
g_perf_rtng_nevnt_ind(i),
g_emp_sprtn_evnt_ind(i),
g_emp_sprtn_nevnt_ind(i),
g_hire_org_chng_ind(i),
g_hire_job_chng_ind(i),
g_hire_pos_chng_ind(i),
g_hire_grd_chng_ind(i),
g_hire_loc_chng_ind(i),
g_current_record_ind(i),
g_current_stage_strt_ind(i),
g_gen_record_ind(i),
g_appl_strt_to_asmt_strt_days(i),
g_appl_strt_to_asmt_end_days(i),
g_appl_strt_to_offr_extd_days(i),
g_appl_strt_to_offr_rjct_days(i),
g_appl_strt_to_offr_acpt_days(i),
g_appl_strt_to_hire_days(i),
g_appl_strt_to_term_days(i),
g_init_appl_stg_ind(i),
g_asmt_stg_ind(i),
g_offr_extd_stg_ind(i),
g_strt_pndg_stg_ind(i),
g_hire_stg_ind(i),
g_appl_strt_date(i),
g_asmt_strt_date(i),
g_asmt_end_date(i),
g_offr_extd_date(i),
g_offr_rjct_date(i),
g_offr_acpt_date(i),
g_appl_term_date(i),
g_hire_date(i),
g_emp_sprtn_date(i),
g_event_date(i),
g_stage_start_date(i),
g_adt_assignment_id(i),
g_adt_asg_effctv_start_date(i),
g_adt_asg_effctv_end_date(i),
g_adt_application_id(i),
g_sysdate,
g_user,
g_user,
g_user,
g_sysdate);
END bulk_insert_rows;
PROCEDURE insert_row
(p_time_day_evt_fk IN DATE,
p_time_day_evt_end_fk IN DATE,
p_time_day_stg_evt_eff_end_fk IN DATE,
p_person_cand_fk IN NUMBER,
p_person_mngr_fk IN NUMBER,
p_person_rcrt_fk IN NUMBER,
p_person_rmgr_fk IN NUMBER,
p_person_auth_fk IN NUMBER,
p_person_refr_fk IN NUMBER,
p_person_rsed_fk IN NUMBER,
p_person_mrgd_fk IN NUMBER,
p_org_organztn_fk IN NUMBER,
p_org_organztn_mrgd_fk IN NUMBER,
p_org_organztn_recr_fk IN NUMBER,
p_geo_location_fk IN NUMBER,
p_job_job_fk IN NUMBER,
p_grd_grade_fk IN NUMBER,
p_pos_position_fk IN NUMBER,
p_prfm_perfband_fk IN NUMBER,
p_rvac_vacncy_fk IN NUMBER,
p_ract_recactvy_fk IN NUMBER,
p_rev_recevent_fk IN VARCHAR2,
p_rern_recevtrn_fk IN VARCHAR2,
p_tarn_trmaplrn_fk IN VARCHAR2,
p_headcount IN NUMBER,
p_fte IN NUMBER,
p_nrmlsd_perf_rtng IN NUMBER,
p_event_seq IN NUMBER,
p_appl_ind IN NUMBER,
p_appl_new_ind IN NUMBER,
p_appl_emp_ind IN NUMBER,
p_appl_cwk_ind IN NUMBER,
p_appl_strt_evnt_ind IN NUMBER,
p_appl_strt_nevnt_ind IN NUMBER,
p_asmt_strt_evnt_ind IN NUMBER,
p_asmt_strt_nevnt_ind IN NUMBER,
p_asmt_end_evnt_ind IN NUMBER,
p_asmt_end_nevnt_ind IN NUMBER,
p_offr_extd_evnt_ind IN NUMBER,
p_offr_extd_nevnt_ind IN NUMBER,
p_offr_rjct_evnt_ind IN NUMBER,
p_offr_rjct_nevnt_ind IN NUMBER,
p_offr_acpt_evnt_ind IN NUMBER,
p_offr_acpt_nevnt_ind IN NUMBER,
p_appl_term_evnt_ind IN NUMBER,
p_appl_term_nevnt_ind IN NUMBER,
p_appl_term_vol_evnt_ind IN NUMBER,
p_appl_term_vol_nevnt_ind IN NUMBER,
p_appl_term_invol_evnt_ind IN NUMBER,
p_appl_term_invol_nevnt_ind IN NUMBER,
p_appl_hire_evnt_ind IN NUMBER,
p_appl_hire_nevnt_ind IN NUMBER,
p_hire_evnt_ind IN NUMBER,
p_hire_nevnt_ind IN NUMBER,
p_pow1_end_evnt_ind IN NUMBER,
p_pow1_end_nevnt_ind IN NUMBER,
p_perf_rtng_evnt_ind IN NUMBER,
p_perf_rtng_nevnt_ind IN NUMBER,
p_emp_sprtn_evnt_ind IN NUMBER,
p_emp_sprtn_nevnt_ind IN NUMBER,
p_hire_org_chng_ind IN NUMBER,
p_hire_job_chng_ind IN NUMBER,
p_hire_pos_chng_ind IN NUMBER,
p_hire_grd_chng_ind IN NUMBER,
p_hire_loc_chng_ind IN NUMBER,
p_current_record_ind IN NUMBER,
p_current_stage_strt_ind IN NUMBER,
p_gen_record_ind IN NUMBER,
p_appl_strt_to_asmt_strt_days IN NUMBER,
p_appl_strt_to_asmt_end_days IN NUMBER,
p_appl_strt_to_offr_extd_days IN NUMBER,
p_appl_strt_to_offr_rjct_days IN NUMBER,
p_appl_strt_to_offr_acpt_days IN NUMBER,
p_appl_strt_to_hire_days IN NUMBER,
p_appl_strt_to_term_days IN NUMBER,
p_init_appl_stg_ind IN NUMBER,
p_asmt_stg_ind IN NUMBER,
p_offr_extd_stg_ind IN NUMBER,
p_strt_pndg_stg_ind IN NUMBER,
p_hire_stg_ind IN NUMBER,
p_appl_strt_date IN DATE,
p_asmt_strt_date IN DATE,
p_asmt_end_date IN DATE,
p_offr_extd_date IN DATE,
p_offr_rjct_date IN DATE,
p_offr_acpt_date IN DATE,
p_appl_term_date IN DATE,
p_hire_date IN DATE,
p_emp_sprtn_date IN DATE,
p_event_date IN DATE,
p_stage_start_date IN DATE,
p_adt_assignment_id IN NUMBER,
p_adt_asg_effctv_start_date IN DATE,
p_adt_asg_effctv_end_date IN DATE,
p_adt_application_id IN NUMBER) IS
BEGIN
g_no_rows := g_no_rows + 1;
END insert_row;
PROCEDURE merge_and_insert_data
(p_master_tab IN g_master_tab_type,
p_apl_tab IN g_apl_tab_type,
p_dt_idx_tab IN g_number_tab_type,
p_info_rec IN g_info_rec_type,
p_ind_rec IN g_ind_rec_type) IS
l_master_idx NUMBER;
insert_row
(p_time_day_evt_fk => p_apl_tab(l_apl_idx).time_day_evt_fk
,p_time_day_evt_end_fk => p_apl_tab(l_apl_idx).time_day_evt_end_fk
,p_time_day_stg_evt_eff_end_fk => l_end_date
,p_person_cand_fk => p_apl_tab(l_apl_idx).person_cand_fk
,p_person_mngr_fk => p_apl_tab(l_apl_idx).person_mngr_fk
,p_person_rcrt_fk => p_apl_tab(l_apl_idx).person_rcrt_fk
,p_person_rmgr_fk => p_apl_tab(l_apl_idx).person_rmgr_fk
,p_person_auth_fk => p_apl_tab(l_apl_idx).person_auth_fk
,p_person_refr_fk => p_apl_tab(l_apl_idx).person_refr_fk
,p_person_rsed_fk => p_apl_tab(l_apl_idx).person_rsed_fk
,p_person_mrgd_fk => p_apl_tab(l_apl_idx).person_mrgd_fk
,p_org_organztn_fk => p_apl_tab(l_apl_idx).org_organztn_fk
,p_org_organztn_mrgd_fk => p_apl_tab(l_apl_idx).org_organztn_mrgd_fk
,p_org_organztn_recr_fk => p_apl_tab(l_apl_idx).org_organztn_recr_fk
,p_geo_location_fk => p_apl_tab(l_apl_idx).geo_location_fk
,p_job_job_fk => p_apl_tab(l_apl_idx).job_job_fk
,p_grd_grade_fk => p_apl_tab(l_apl_idx).grd_grade_fk
,p_pos_position_fk => p_apl_tab(l_apl_idx).pos_position_fk
,p_prfm_perfband_fk => p_info_rec.perf_band
,p_rvac_vacncy_fk => p_apl_tab(l_apl_idx).rvac_vacncy_fk
,p_ract_recactvy_fk => p_apl_tab(l_apl_idx).ract_recactvy_fk
,p_rev_recevent_fk => l_master_tab(l_master_idx)(l_event_idx).event_code
,p_rern_recevtrn_fk => p_apl_tab(l_apl_idx).rern_recevtrn_fk
,p_tarn_trmaplrn_fk => p_apl_tab(l_apl_idx).tarn_trmaplrn_fk
,p_headcount => p_apl_tab(l_apl_idx).headcount
,p_fte => p_apl_tab(l_apl_idx).fte
,p_nrmlsd_perf_rtng => p_info_rec.perf_norm_rtng
,p_event_seq => l_master_tab(l_master_idx)(l_event_idx).event_seq
,p_appl_ind => l_ind_rec.appl_ind
,p_appl_new_ind => l_ind_rec.appl_new_ind
,p_appl_emp_ind => l_ind_rec.appl_emp_ind
,p_appl_cwk_ind => l_ind_rec.appl_cwk_ind
,p_appl_strt_evnt_ind => l_ind_rec.appl_strt_evnt_ind
,p_appl_strt_nevnt_ind => p_ind_rec.appl_strt_nevnt_ind
,p_asmt_strt_evnt_ind => l_ind_rec.asmt_strt_evnt_ind
,p_asmt_strt_nevnt_ind => p_ind_rec.asmt_strt_nevnt_ind
,p_asmt_end_evnt_ind => l_ind_rec.asmt_end_evnt_ind
,p_asmt_end_nevnt_ind => p_ind_rec.asmt_end_nevnt_ind
,p_offr_extd_evnt_ind => l_ind_rec.offr_extd_evnt_ind
,p_offr_extd_nevnt_ind => p_ind_rec.offr_extd_nevnt_ind
,p_offr_rjct_evnt_ind => l_ind_rec.offr_rjct_evnt_ind
,p_offr_rjct_nevnt_ind => p_ind_rec.offr_rjct_nevnt_ind
,p_offr_acpt_evnt_ind => l_ind_rec.offr_acpt_evnt_ind
,p_offr_acpt_nevnt_ind => p_ind_rec.offr_acpt_nevnt_ind
,p_appl_term_evnt_ind => l_ind_rec.appl_term_evnt_ind
,p_appl_term_nevnt_ind => p_ind_rec.appl_term_nevnt_ind
,p_appl_term_vol_evnt_ind => l_ind_rec.appl_term_vol_evnt_ind
,p_appl_term_vol_nevnt_ind => p_ind_rec.appl_term_vol_nevnt_ind
,p_appl_term_invol_evnt_ind => l_ind_rec.appl_term_invol_evnt_ind
,p_appl_term_invol_nevnt_ind => p_ind_rec.appl_term_invol_nevnt_ind
,p_appl_hire_evnt_ind => l_ind_rec.appl_hire_evnt_ind
,p_appl_hire_nevnt_ind => p_ind_rec.appl_hire_nevnt_ind
,p_hire_evnt_ind => l_ind_rec.hire_evnt_ind
,p_hire_nevnt_ind => p_ind_rec.hire_nevnt_ind
,p_pow1_end_evnt_ind => l_ind_rec.pow1_end_evnt_ind
,p_pow1_end_nevnt_ind => p_ind_rec.pow1_end_nevnt_ind
,p_perf_rtng_evnt_ind => l_ind_rec.perf_rtng_evnt_ind
,p_perf_rtng_nevnt_ind => p_ind_rec.perf_rtng_nevnt_ind
,p_emp_sprtn_evnt_ind => l_ind_rec.emp_sprtn_evnt_ind
,p_emp_sprtn_nevnt_ind => p_ind_rec.emp_sprtn_nevnt_ind
,p_hire_org_chng_ind => l_ind_rec.hire_org_chng_ind
,p_hire_job_chng_ind => l_ind_rec.hire_job_chng_ind
,p_hire_pos_chng_ind => l_ind_rec.hire_pos_chng_ind
,p_hire_grd_chng_ind => l_ind_rec.hire_grd_chng_ind
,p_hire_loc_chng_ind => l_ind_rec.hire_loc_chng_ind
,p_current_record_ind => l_ind_rec.current_record_ind
,p_current_stage_strt_ind => l_current_stage_strt_ind
,p_gen_record_ind => l_gen_record_ind
,p_appl_strt_to_asmt_strt_days => p_info_rec.asmt_strt_date - p_info_rec.appl_strt_date
,p_appl_strt_to_asmt_end_days => p_info_rec.asmt_end_date - p_info_rec.appl_strt_date
,p_appl_strt_to_offr_extd_days => p_info_rec.offr_extd_date - p_info_rec.appl_strt_date
,p_appl_strt_to_offr_rjct_days => p_info_rec.offr_rjct_date - p_info_rec.appl_strt_date
,p_appl_strt_to_offr_acpt_days => p_info_rec.offr_acpt_date - p_info_rec.appl_strt_date
,p_appl_strt_to_hire_days => p_info_rec.hire_date - p_info_rec.appl_strt_date
,p_appl_strt_to_term_days => p_info_rec.appl_term_date - p_info_rec.appl_strt_date
,p_init_appl_stg_ind => l_ind_rec.init_appl_stg_ind
,p_asmt_stg_ind => l_ind_rec.asmt_stg_ind
,p_offr_extd_stg_ind => l_ind_rec.offr_extd_stg_ind
,p_strt_pndg_stg_ind => l_ind_rec.strt_pndg_stg_ind
,p_hire_stg_ind => l_ind_rec.hire_stg_ind
,p_appl_strt_date => p_info_rec.appl_strt_date
,p_asmt_strt_date => p_info_rec.asmt_strt_date
,p_asmt_end_date => p_info_rec.asmt_end_date
,p_offr_extd_date => p_info_rec.offr_extd_date
,p_offr_rjct_date => p_info_rec.offr_rjct_date
,p_offr_acpt_date => p_info_rec.offr_acpt_date
,p_appl_term_date => p_info_rec.appl_term_date
,p_hire_date => p_info_rec.hire_date
,p_emp_sprtn_date => to_date(null)
,p_event_date => p_apl_tab(l_apl_idx).event_date
,p_stage_start_date => p_apl_tab(l_apl_idx).stage_start_date
,p_adt_assignment_id => p_info_rec.asg_id
,p_adt_asg_effctv_start_date => p_apl_tab(l_apl_idx).time_day_evt_fk
,p_adt_asg_effctv_end_date => p_apl_tab(l_apl_idx).time_day_evt_end_fk
,p_adt_application_id => p_apl_tab(l_apl_idx).adt_application_id);
END merge_and_insert_data;
SELECT
asg.effective_start_date time_day_evt_fk
,asg.effective_end_date time_day_evt_end_fk
,asg.person_id person_cand_fk
,NVL(asg.supervisor_id, -1) person_mngr_fk
,NVL(vac.recruiter_id, -1) person_rcrt_fk
,NVL(vac.manager_id, -1) person_rmgr_fk
,NVL(pra.authorising_person_id, -1) person_auth_fk
,NVL(asg.person_referred_by_id, -1) person_refr_fk
,NVL(prq.person_id, -1) person_rsed_fk
,NVL(vac.organization_id, -1) org_organztn_fk
,vac.business_group_id org_organztn_bgrp_fk
,NVL(pra.run_by_organization_id, -1) org_organztn_recr_fk
,NVL(vac.location_id, -1) geo_location_fk
,NVL(vac.job_id, -1) job_job_fk
,NVL(vac.grade_id, -1) grd_grade_fk
,NVL(vac.position_id, -1) pos_position_fk
,NVL(vac.vacancy_id, -1) rvac_vacncy_fk
,NVL(asg.recruitment_activity_id, -1) ract_recactvy_fk
,NVL(ias.status_change_reason, NVL(asg.change_reason, 'NA_EDW'))
rern_recevtrn_fk
,asg.application_id adt_application_id
,asg.business_group_id adt_business_group_id
,NVL(TRUNC(ias.status_change_date), asg.effective_start_date)
event_date
,NVL(ast_irc.per_system_status, ast.per_system_status)
stage_code
,NVL(ast_irc.user_status, ast.user_status)
stage_name
,NVL(ast_irc.assignment_status_type_id, ast.assignment_status_type_id)
assignment_status_type_id
,NVL(apl.termination_reason, 'NA_EDW') termination_reason
FROM
per_all_assignments_f asg
,per_assignment_status_types ast
,per_assignment_status_types ast_irc
,per_all_vacancies vac
,irc_assignment_statuses ias
,per_applications apl
,per_recruitment_activities pra
,per_requisitions prq
WHERE asg.assignment_id = p_info_rec.asg_id
AND apl.application_id = asg.application_id
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ias.assignment_status_type_id = ast_irc.assignment_status_type_id (+)
AND asg.assignment_type = 'A'
AND asg.recruitment_activity_id = pra.recruitment_activity_id (+)
AND asg.vacancy_id = vac.vacancy_id (+)
AND vac.requisition_id = prq.requisition_id (+)
AND asg.assignment_id = ias.assignment_id (+)
AND ias.status_change_date (+) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND vac.date_from (+) >= g_dbi_start_date
AND apl.date_received >= g_dbi_start_date
AND ast_irc.per_system_status (+) <> 'TERM_APL'
ORDER BY asg.effective_start_date;
SELECT
ppt.system_person_type
FROM
per_person_type_usages_f ptu
,per_person_types ppt
WHERE ptu.person_id = v_person_id
AND v_effective_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type IN ('EMP','CWK');
SELECT
pos.date_start
,pos.actual_termination_date
,emp.assignment_id
,emp.effective_start_date
,emp.effective_end_date
,emp.person_id
,NVL(emp.supervisor_id, -1)
,emp.organization_id
,NVL(emp.location_id, -1)
,NVL(emp.job_id, -1)
,NVL(emp.grade_id, -1)
,NVL(emp.position_id, -1)
,emp.business_group_id
FROM
per_all_assignments_f emp
,per_periods_of_service pos
WHERE emp.person_id = p_info_rec.psn_id
AND emp.assignment_type = 'E'
AND p_info_rec.appl_end_date + 1 BETWEEN emp.effective_start_date
AND emp.effective_end_date
AND (emp.assignment_id = p_info_rec.asg_id OR
(emp.primary_flag = 'Y' and
emp.effective_start_date = p_info_rec.appl_end_date + 1))
AND emp.period_of_service_id = pos.period_of_service_id;
SELECT
pos.date_start
,pos.actual_termination_date
,emp.assignment_id
,emp.effective_start_date
,emp.effective_end_date
,emp.person_id
,NVL(emp.supervisor_id, -1)
,emp.organization_id
,NVL(emp.location_id, -1)
,NVL(emp.job_id, -1)
,NVL(emp.grade_id, -1)
,NVL(emp.position_id, -1)
,emp.business_group_id
FROM
per_all_assignments_f emp
,per_periods_of_service pos
WHERE emp.assignment_id = p_info_rec.asg_id
AND emp.effective_start_date = pos.date_start
AND emp.period_of_service_id = pos.period_of_service_id
AND emp.effective_start_date >= g_dbi_start_date;
SELECT
effective_change_date
,perf_nrmlsd_rating
,perf_band
FROM hri_mb_asgn_events_ct evt
WHERE assignment_id = v_asg_id
AND effective_change_date BETWEEN v_hire_date
AND add_months(v_hire_date, 24)
AND perf_rating_change_ind = 1;
merge_and_insert_data
(p_master_tab => l_master_tab,
p_apl_tab => l_apl_tab,
p_dt_idx_tab => l_dt_idx_tab,
p_info_rec => l_info_rec,
p_ind_rec => l_ind_rec);
SELECT DISTINCT
assignment_id
,person_id
FROM per_all_assignments_f
WHERE assignment_type IN ('E','A')
AND effective_end_date >= g_dbi_start_date
AND assignment_id BETWEEN p_start_object_id AND p_end_object_id;
bulk_insert_rows;
INSERT /*+ APPEND */ INTO hri_mb_rec_cand_pipln_ct
(time_day_evt_fk,
time_day_evt_end_fk,
time_day_stg_evt_eff_end_fk,
per_person_cand_fk,
per_person_mngr_fk,
per_person_rcrt_fk,
per_person_rmgr_fk,
per_person_auth_fk,
per_person_refr_fk,
per_person_rsed_fk,
per_person_mrgd_fk,
org_organztn_fk,
org_organztn_mrgd_fk,
org_organztn_recr_fk,
geo_location_fk,
job_job_fk,
grd_grade_fk,
pos_position_fk,
prfm_perfband_fk,
rvac_vacncy_fk,
ract_recactvy_fk,
rev_recevent_fk,
rern_recevtrn_fk,
tarn_trmaplrn_fk,
event_seq,
appl_ind,
appl_new_ind,
appl_emp_ind,
appl_cwk_ind,
appl_strt_evnt_ind,
appl_strt_nevnt_ind,
asmt_strt_evnt_ind,
asmt_strt_nevnt_ind,
asmt_end_evnt_ind,
asmt_end_nevnt_ind,
offr_extd_evnt_ind,
offr_extd_nevnt_ind,
offr_rjct_evnt_ind,
offr_rjct_nevnt_ind,
offr_acpt_evnt_ind,
offr_acpt_nevnt_ind,
appl_term_evnt_ind,
appl_term_nevnt_ind,
appl_term_vol_evnt_ind,
appl_term_vol_nevnt_ind,
appl_term_invol_evnt_ind,
appl_term_invol_nevnt_ind,
appl_hire_evnt_ind,
appl_hire_nevnt_ind,
hire_evnt_ind,
hire_nevnt_ind,
post_hire_pow1_end_evnt_ind,
post_hire_pow1_end_nevnt_ind,
post_hire_perf_evnt_ind,
post_hire_perf_nevnt_ind,
emp_sprtn_evnt_ind,
emp_sprtn_nevnt_ind,
hire_org_chng_ind,
hire_job_chng_ind,
hire_pos_chng_ind,
hire_grd_chng_ind,
hire_loc_chng_ind,
current_record_ind,
current_stage_strt_ind,
init_appl_stg_ind,
asmt_stg_ind,
offr_extd_stg_ind,
strt_pndg_stg_ind,
hire_stg_ind,
gen_record_ind,
adt_assignment_id,
adt_application_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
SELECT
date_from
,NVL(date_to, g_end_of_time)
,to_date(null)
,-1
,-1
,-1
,-1
,-1
,-1
,NVL(recruiter_id, -1)
,NVL(manager_id, -1)
,NVL(organization_id, -1)
,-1
,business_group_id
,NVL(location_id, -1)
,NVL(job_id, -1)
,NVL(grade_id, -1)
,NVL(position_id, -1)
,-5
,vacancy_id
,-1
,'VAC_OPEN'
,'NA_EDW'
,'NA_EDW'
,-1
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,1
,-1
,-1
,g_sysdate
,g_user
,g_user
,g_user
,g_sysdate
FROM per_all_vacancies
WHERE date_from >= g_dbi_start_date;
'SELECT DISTINCT
asg.assignment_id object_id
FROM
per_all_assignments_f asg
WHERE asg.assignment_type IN (''E'',''A'')
AND asg.effective_end_date >=
hri_bpl_parameter.get_bis_global_start_date
ORDER BY 1';
'SELECT DISTINCT
assignment_id object_id
FROM per_all_assignments_f
WHERE 1 = 0
ORDER BY 1';
SELECT
chunk_no
,MIN(assignment_id) start_asg_id
,MAX(assignment_id) end_asg_id
FROM
(SELECT
assignment_id
,CEIL(ROWNUM / 20) chunk_no
FROM
(SELECT DISTINCT
assignment_id
FROM per_all_assignments_f
WHERE assignment_type IN ('E','A')
AND effective_end_date >= g_dbi_start_date
ORDER BY assignment_id
) tab
) chunks
GROUP BY
chunk_no;
SELECT DISTINCT person_id INTO l_psn_id
FROM per_all_assignments_f
WHERE assignment_id = p_asg_id;
DELETE FROM hri_mb_rec_cand_pipln_ct
WHERE adt_assignment_id = p_asg_id;
bulk_insert_rows;