[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hri_eq_wrkfc_evt
(assignment_id
,erlst_evnt_effective_date)
SELECT
assignment_id
,erlst_evnt_effective_date
FROM
hri_eq_asgn_evnts;
INSERT INTO hri_eq_wrkfc_mnth
(assignment_id
,erlst_evnt_effective_date)
SELECT
assignment_id
,erlst_evnt_effective_date
FROM
hri_eq_asgn_evnts;
INSERT INTO hri_eq_wrkfc_evt_orgh
(organization_id
,erlst_evnt_effective_date)
-- Previous organization chains
SELECT /*+ ORDERED */
orgh.orgh_sup_organztn_fk
,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
FROM
hri_eq_asgn_evnts eq
,hri_mb_asgn_events_ct evt
,hri_cs_orgh_ct orgh
WHERE eq.assignment_id = evt.assignment_id
AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
AND evt.organization_id = orgh.orgh_organztn_fk
UNION ALL
-- New organization chains
SELECT /*+ ORDERED */
orgh.orgh_sup_organztn_fk
,GREATEST(eq.erlst_evnt_effective_date, asg.effective_start_date)
FROM
hri_eq_asgn_evnts eq
,per_all_assignments_f asg
,hri_cs_orgh_ct orgh
WHERE eq.assignment_id = asg.assignment_id
AND eq.erlst_evnt_effective_date <= asg.effective_end_date
AND asg.organization_id = orgh.orgh_organztn_fk;
INSERT INTO hri_eq_wrkfc_evt_mgrh
(sup_person_id
,erlst_evnt_effective_date
,source_code)
-- Previous manager chains
SELECT /*+ ORDERED */
suph.sup_person_id
,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
,'ASG_EVENT_PREV'
FROM
hri_eq_asgn_evnts eq
,hri_mb_asgn_events_ct evt
,hri_cs_suph suph
WHERE eq.assignment_id = evt.assignment_id
AND evt.supervisor_id = suph.sub_person_id
AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
AND eq.erlst_evnt_effective_date - 1 <= suph.effective_end_date
UNION ALL
-- New manager chains
SELECT /*+ ORDERED */
suph.sup_person_id
,GREATEST(eq.erlst_evnt_effective_date, asg.effective_start_date)
,'ASG_EVENT_CURR'
FROM
hri_eq_asgn_evnts eq
,per_all_assignments_f asg
,hri_cs_suph suph
WHERE eq.assignment_id = asg.assignment_id
AND eq.erlst_evnt_effective_date <= asg.effective_end_date
AND asg.supervisor_id = suph.sub_person_id
AND eq.erlst_evnt_effective_date <= suph.effective_end_date;
USING (SELECT assignment_id,
erlst_evnt_effective_date,
'ASG_EVENT' source_type
FROM hri_eq_asgn_evnts) asg_eq
ON ( delta_eq.source_type = 'ASG_EVENT'
AND asg_eq.assignment_id = delta_eq.source_id)
WHEN MATCHED THEN
UPDATE SET delta_eq.erlst_evnt_effective_date =
least(delta_eq.erlst_evnt_effective_date,asg_eq.erlst_evnt_effective_date)
WHEN NOT MATCHED THEN
INSERT (delta_eq.source_type,
delta_eq.source_id,
delta_eq.erlst_evnt_effective_date
)
VALUES (asg_eq.source_type,
asg_eq.assignment_id,
asg_eq.erlst_evnt_effective_date);
PROCEDURE insert_pow_change_events
IS
--
-- Cursor to fetch the Period of work band change records
-- for employees and contingent workers
-- 4086548 changed the SQL for performance reasons
-- Drive the query off asg events fact table. This also prevents the process
-- from creating stray events for terminated person's and unrequired assignments.
--
CURSOR c_pow_changes IS
SELECT DISTINCT asgn.assignment_id,
add_months(pow_start_date_adj, band_range_high) first_event
FROM hri_mb_Asgn_events_ct asgn,
hri_cs_pow_band_ct powb
WHERE asgn.pow_band_sk_fk = powb.pow_band_sk_pk
AND powb.band_range_high is not null
AND asgn.worker_term_ind = 0
AND g_refresh_start_date <= asgn.effective_change_end_date
AND asgn.pow_start_date_adj BETWEEN add_months(g_refresh_start_date,-powb.band_range_high) AND
add_months(SYSDATE,-powb.band_range_high);
INSERT /*+ APPEND */ INTO hri_eq_asgn_evnts(
assignment_id,
erlst_evnt_effective_date
)
VALUES(
l_pow_event_asg_id(l_loop),
l_pow_event_date(l_loop)
);
dbg('done inserting into events queue');
UPDATE hri_eq_asgn_evnts
SET erlst_evnt_effective_date = least(erlst_evnt_effective_date,l_upd_asg_date)
WHERE assignment_id = l_upd_asg_id;
g_msg_sub_group := NVL(g_msg_sub_group, 'INSERT_POW_CHANGE_EVENTS');
END insert_pow_change_events;
PROCEDURE check_update_smrztn_rqrmnt
(p_effective_start_date IN DATE,
p_indicator_rec IN OUT NOCOPY g_indicator_record,
p_summarization_ind_prev IN NUMBER
) IS
--
l_ff_exists NUMBER;
END check_update_smrztn_rqrmnt;
PROCEDURE bulk_insert_rows
(p_asgn_events_tab IN g_asgn_events_tab_type) IS
--
-- Row count
--
l_row_count PLS_INTEGER;
dbg('Inserting data into table');
INSERT INTO HRI_MB_ASGN_EVENTS_CT (
--
-- Unique key generated for the events fact
--
event_id
--
-- Effective Dates
--
,effective_change_date
,effective_change_end_date
--
-- Id Keys
--
,assignment_id
,person_id
--
-- Assignment related FK ID's which are present in the
-- assignment records after the event
--
,business_group_id
,grade_id
,job_id
,location_id
,organization_id
,supervisor_id
,position_id
,primary_flag
,asg_type_code
,pow_start_date_adj
--
-- Period of work related changes
--
,pow_start_date
--
-- Assignment releated FK ID's existing prior to the event
--
,grade_prv_id
,job_prv_id
,location_prv_id
,organization_prv_id
,supervisor_prv_id
,position_prv_id
,primary_flag_prv
--
-- Other assignment related values
--
,change_reason_code
,leaving_reason_code
--
-- Separation Category related information for a person
--
,separation_category
,separation_category_nxt
,pow_days_on_event_date
,pow_months_on_event_date
,days_since_last_prmtn
,months_since_last_prmtn
--
-- Headcount related Measures and information for an assignment
--
,fte
,fte_prv
,headcount
,headcount_prv
--
-- Salary related Measures and information for a person
--
,anl_slry
,anl_slry_prv
,anl_slry_currency
,anl_slry_currency_prv
,pay_proposal_id
--
-- Performance Related measures and information for a person
--
,perf_nrmlsd_rating
,perf_nrmlsd_rating_prv
,perf_band
,perf_band_prv
,performance_review_id
,perf_review_type_cd
,performance_rating_cd
--
-- Period of work related measure and information for a person
--
,pow_band_sk_fk
,pow_band_prv_sk_fk
,pow_extn_strt_dt
--
-- Person type usage related measures
--
,prsntyp_sk_fk
,summarization_rqd_ind
,summarization_rqd_chng_ind
,summarization_rqd_chng_nxt_ind
--
--
-- Indicators
--
,asg_rtrspctv_strt_event_ind
,assignment_change_ind
,salary_change_ind
--
-- Performance related indicators
--
,perf_rating_change_ind
,perf_band_change_ind
--
-- Period of work related indicators
--
,pow_band_change_ind
--
-- Various Indicators
--
,headcount_gain_ind
,headcount_loss_ind
,fte_gain_ind
,fte_loss_ind
,contingent_ind
,employee_ind
,grade_change_ind
,job_change_ind
,position_change_ind
,location_change_ind
,organization_change_ind
,supervisor_change_ind
,worker_hire_ind
,post_hire_asgn_start_ind
,pre_sprtn_asgn_end_ind
,term_voluntary_ind
,term_involuntary_ind
,worker_term_ind
,start_asg_sspnsn_ind
,end_asg_sspnsn_ind
,worker_term_nxt_ind
,term_voluntary_nxt_ind
,term_involuntary_nxt_ind
,supervisor_change_nxt_ind
,pre_sprtn_asgn_end_nxt_ind
,promotion_ind
,last_update_date
,last_update_login
,last_updated_by
,created_by
,creation_date)
VALUES
--
-- Unique key generated for the events fact
--
(hri_mb_asgn_events_ct_s.nextval
--
-- Effective Dates
--
,l_tab_change_date(i)
,l_tab_change_end_date(i)
--
-- Id Keys
--
,l_tab_assignment_id(i)
,l_tab_person_id(i)
--
-- Assignment related FK ID's which are present in the
-- assignment records after the event
--
,l_tab_bus_grp_id(i)
,l_tab_grade_id(i)
,l_tab_job_id(i)
,l_tab_location_id(i)
,l_tab_organization_id(i)
,l_tab_supervisor_id(i)
,l_tab_position_id(i)
,l_tab_primary_flag(i)
,l_tab_asg_type_code(i)
,l_tab_pow_start_date_adj(i)
--
-- Period of work start date
--
,l_tab_pow_start_date(i)
--
-- Assignment releated FK ID's existing prior to the event
--
,l_tab_grade_prv_id(i)
,l_tab_job_prv_id(i)
,l_tab_location_prv_id(i)
,l_tab_organization_prv_id(i)
,l_tab_supervisor_prv_id(i)
,l_tab_position_prv_id(i)
,l_tab_primary_flag_prv(i)
--
-- Other assignment related values
--
,l_tab_change_reason_code(i)
,l_tab_leaving_reason_code(i)
--
-- Separation Category related information
--
,l_tab_separation_category(i)
,l_tab_separation_category_nxt(i)
,l_tab_pow_days_on_event_date(i)
,l_tab_pow_months_on_event_date(i)
,l_tab_days_since_last_prmtn(i)
,l_tab_months_since_last_prmtn(i)
--
-- Headcount related Measures and information for an assignment
--
,l_tab_fte(i)
,l_tab_fte_prv(i)
,l_tab_headcount(i)
,l_tab_headcount_prv(i)
--
-- Salary related Measures and information for a person
--
,l_tab_anl_slry(i)
,l_tab_anl_slry_prv(i)
,l_tab_anl_slry_currency(i)
,l_tab_anl_slry_currency_prv(i)
,l_tab_pay_proposal_id(i)
--
-- Performance rating related measures
--
,l_tab_perf_nrmlsd_rating(i)
,l_tab_perf_nrmlsd_rating_prv(i)
,l_tab_perf_band(i)
,l_tab_perf_band_prv(i)
,l_tab_perf_review_id(i)
,l_tab_perf_review_type_cd(i)
,l_tab_performance_rating_cd(i)
--
-- Period of work related measures
--
,l_tab_pow_band_sk_fk(i)
,l_tab_pow_band_prv_sk_fk(i)
,l_tab_pow_extn_strt_dt(i)
--
-- Person type related measures
--
,l_tab_prsntyp_sk_fk(i)
,l_tab_summarization_rqd_ind(i)
,l_tab_sum_rqd_chng_ind(i)
,l_tab_sum_rqd_chng_nxt_ind(i)
--
-- Various Indicators
--
,l_tab_asg_rtr_strt_event_ind(i)
,l_tab_assignment_change_ind(i)
,l_tab_salary_change_ind(i)
--
-- Performance Rating related indicators
--
,l_tab_perf_change_ind(i)
,l_tab_perf_band_change_ind(i)
--
-- Period of work related indicators
--
,l_tab_pow_band_change_ind(i)
--
-- Various Indicators
--
,l_tab_headcount_gain_ind(i)
,l_tab_headcount_loss_ind(i)
,l_tab_fte_gain_ind(i)
,l_tab_fte_loss_ind(i)
,l_tab_contingent_ind(i)
,l_tab_employee_ind(i)
,l_tab_grade_change_ind(i)
,l_tab_job_change_ind(i)
,l_tab_position_change_ind(i)
,l_tab_location_change_ind(i)
,l_tab_organization_change_ind(i)
,l_tab_supervisor_change_ind(i)
,l_tab_worker_hire_ind(i)
,l_tab_post_hire_asgn_start_ind(i)
,l_tab_pre_sprtn_asgn_end_ind(i)
,l_tab_term_voluntary_ind(i)
,l_tab_term_involuntary_ind(i)
,l_tab_worker_term_ind(i)
,l_tab_start_asg_sspnsn_ind(i)
,l_tab_end_asg_sspnsn_ind(i)
,l_tab_worker_term_nxt_ind(i)
,l_tab_term_voluntary_nxt_ind(i)
,l_tab_term_involuntary_nxt_ind(i)
,l_tab_sup_change_nxt_ind(i)
,l_tab_pre_sep_asgn_end_nxt_ind(i)
,l_tab_promotion_ind(i)
,l_current_time
,l_user_id
,l_user_id
,l_user_id
,l_current_time);
dbg('Done insert ok');
END bulk_insert_rows;
PROCEDURE delete_records
(p_start_assignment_id IN NUMBER,
p_end_assignment_id IN NUMBER) IS
--
BEGIN
--
--
dbg('Entering delete_records');
DELETE FROM hri_mb_asgn_events_ct evt
WHERE evt.rowid IN
(SELECT evt2.rowid
FROM hri_eq_asgn_evnts eq
, hri_mb_asgn_events_ct evt2
WHERE eq.assignment_id = evt2.assignment_id
AND evt2.effective_change_date >= eq.erlst_evnt_effective_date
AND eq.assignment_id BETWEEN p_start_assignment_id AND p_end_assignment_id);
dbg('Deleted records occuring on or after '||g_refresh_start_date);
END delete_records;
SELECT
GREATEST(ptu.effective_start_date, asg.effective_start_date) effective_start_date
,least(ptu.effective_end_date, asg.effective_end_date) effective_end_date
,NVL(pos.date_start,pop.date_start) hire_date
,NVL(pos.actual_termination_date,pop.actual_termination_date)
termination_date
,asg.assignment_id assignment_id
,asg.person_id person_id
,asg.business_group_id business_group_id
,NVL(asg.organization_id,-1) organization_id
,NVL(asg.location_id,-1) location_id
,NVL(asg.job_id,-1) job_id
,NVL(asg.grade_id,-1) grade_id
,NVL(asg.position_id,-1) position_id
,NVL(asg.supervisor_id,-1) supervisor_id
,NVL(asg.primary_flag,'N') primary_flag
,asg.assignment_type assignment_type
,NVL(NVL(pos.leaving_reason,pop.termination_reason),'NA_EDW')
separation_reason_code
,NVL(asg.change_reason,'NA_EDW') assignment_reason_code
,ast.per_system_status assignment_status_code
,NVL(asg.payroll_id,-1) payroll_id
,NVL(asg.pay_basis_id,-1) pay_basis_id
,pos.adjusted_svc_date pow_start_date_adj
,hpt.prsntyp_sk_pk prsntyp_sk_fk
,nvl(decode(hpt.include_flag_code,'Y',1,0),1) summarization_rqd_ind
,hpt.wkth_wktyp_code wkth_wktyp_code
FROM per_all_assignments_f asg
,per_assignment_status_types ast
,per_periods_of_service pos
,per_periods_of_placement pop
,per_person_type_usages_f ptu
,hri_cs_prsntyp_ct hpt
WHERE asg.assignment_id = g_assignment_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND pos.period_of_service_id(+) = asg.period_of_service_id
AND pop.person_id(+) = asg.person_id
AND pop.date_start(+) = asg.period_of_placement_date_start
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.assignment_type IN ('E','C')
--
-- Need assignment details on refresh date - 1 otherwise it would be
-- difficult to tell whether an assignment that starts on refresh start
-- date was an assignment start or an assignment change
--
AND (asg.effective_start_date >= (g_refresh_start_date - 1)
OR (g_refresh_start_date - 1) BETWEEN asg.effective_start_date
AND asg.effective_end_date)
AND (asg.effective_start_date between ptu.effective_start_date and ptu.effective_end_date OR
ptu.effective_start_date between asg.effective_start_date and asg.effective_end_date)
AND ptu.person_id = asg.person_id
AND hpt.person_type_id = ptu.person_type_id
AND hpt.employment_category_code = nvl(asg.employment_category,'NA_EDW')
AND hpt.primary_flag_code = nvl(asg.primary_flag,'NA_EDW')
AND hpt.assignment_type_code = asg.assignment_type
ORDER BY 1;
dbg('Inserting a termination record');
SELECT
abv.value value
,abv.unit unit
,abv.effective_start_date abv_start_date
,GREATEST(abv.effective_start_date, p_asg_dates.start_date_active)
effective_start_date
,LEAST(abv.effective_end_date, p_asg_dates.end_date_active)
effective_end_date
,DECODE(SIGN(p_asg_dates.start_date_active - abv.effective_start_date),1,0,1)
abv_evt_ind
FROM per_assignment_budget_values_f abv
WHERE abv.assignment_id = g_assignment_id
AND abv.unit IN ('HEAD','FTE')
--
-- Only ABVs in collection period needs to be selected
--
AND (abv.effective_start_date BETWEEN p_asg_dates.start_date_active AND p_asg_dates.end_date_active
OR p_asg_dates.start_date_active BETWEEN abv.effective_start_date AND abv.effective_end_date)
ORDER BY abv.unit, abv.effective_start_date;
SELECT
CASE WHEN ppb.pay_annualization_factor IS NULL
AND ppb.pay_basis = 'PERIOD' THEN
--
-- When the salary basis is PERIOD, the annualization can be
-- null in such a case the the annualization factor is
-- equal to the payroll frequency or the numer of paroll in a
-- year. The function returns the payroll frequency
--
pro.proposed_salary_n *
hri_bpl_sal.get_perd_annualization_factor
(asg.assignment_id, pro.change_date)
ELSE
pro.proposed_salary_n * ppb.pay_annualization_factor
END salary
--
-- Time
--
,pro.change_date change_date
--
-- Dimensions
--
,NVL(pro.pay_proposal_id,-1) pay_proposal_id
,NVL(pet.input_currency_code, 'NA_EDW') currency_code
FROM
per_all_assignments_f asg
,per_pay_bases ppb
,per_pay_proposals pro
,pay_input_values_f piv
,pay_element_types_f pet
WHERE pro.approved = 'Y'
AND asg.assignment_id = g_assignment_id
AND asg.assignment_id = pro.assignment_id
AND asg.pay_basis_id = ppb.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pro.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pro.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date
--
-- Only Salary changes before assignment end
--
AND pro.change_date <= p_asg_dates.end_date_active
ORDER BY pro.change_date;
l_last_update_date g_date_tab_type;
p_business_group_id, -- perf select
p_person_id, -- perf where
p_asg_dates.end_date_active, -- perf where
p_asg_dates.hire_date, -- perf where
p_business_group_id, -- app select
p_person_id, -- app where
p_asg_dates.end_date_active, -- app where
p_asg_dates.hire_date, -- app where
g_assignment_id;
,l_last_update_date
,l_perf_effective_end_date
,l_perf_review_id
,l_perf_review_type_cd
,l_app_temp_name
,l_perf_nrmlsd_rating
,l_perf_band
,l_same_day_rank;
'same date. The rating given for the last updated record will '||
'be considered for collection.');
SELECT min(asg.projected_assignment_end)
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
--
-- 4469175 incase if rehire of placement, the extension date should be
-- derived from asg records in the same term. placement start date
-- is the fk to placement table
--
AND asg.period_of_placement_date_start = p_asg_dates.hire_date;
dbg('inserting pow band info in master index at index '||l_date_index);
SELECT
grade_id
,job_id
,location_id
,organization_id
,supervisor_id
,position_id
,primary_flag
,fte
,headcount
,anl_slry
,anl_slry_currency
--
-- Performance Values
--
,perf_nrmlsd_rating
,perf_band
,to_date(null)
,to_date(null)
--
-- Period of Work Value
--
,pow_band_sk_fk
,summarization_rqd_ind
,ROWID
FROM HRI_MB_ASGN_EVENTS_CT
WHERE assignment_id = g_assignment_id
AND (g_refresh_start_date - 1) BETWEEN effective_change_date AND effective_change_end_date;
PROCEDURE merge_and_insert_data(
p_date_master_tab IN g_master_tab_type,
p_asg_change_tab IN g_asg_change_tab_type,
p_sal_change_tab IN g_sal_change_tab_type,
p_perf_change_tab IN g_perf_change_tab_type,
p_asg_dates IN g_asg_date_type,
p_prv_rec IN g_prv_record,
p_nxt_ind_rec OUT NOCOPY g_nxt_ind_record,
p_asgn_events_tab IN OUT NOCOPY g_asgn_events_tab_type) IS
--
-- -----------------------------------------------------------------------------
-- Start of Local Package Variable eclaration
--
-- Reset every time procedure is called
--
--
-- Date track period dates
--
l_effective_start_date DATE;
dbg('Inside merge_and_insert_data');
check_update_smrztn_rqrmnt(p_effective_start_date => l_effective_start_date,
p_indicator_rec => l_indicator_rec,
p_summarization_ind_prev => l_summarization_ind_prev);
END merge_and_insert_data;
PROCEDURE update_end_record(p_nxt_ind_rec IN g_nxt_ind_record
,p_row_id IN ROWID) IS
BEGIN
--
dbg('Entering update_end_record');
UPDATE hri_mb_asgn_events_ct
SET effective_change_end_date = (g_refresh_start_date - 1),
worker_term_nxt_ind = p_nxt_ind_rec.worker_term_nxt_ind,
term_voluntary_nxt_ind = p_nxt_ind_rec.term_voluntary_nxt_ind,
term_involuntary_nxt_ind = p_nxt_ind_rec.term_involuntary_nxt_ind,
supervisor_change_nxt_ind = p_nxt_ind_rec.supervisor_change_nxt_ind,
pre_sprtn_asgn_end_nxt_ind = p_nxt_ind_rec.pre_sprtn_asgn_end_nxt_ind,
summarization_rqd_chng_nxt_ind= p_nxt_ind_rec.summarization_rqd_chng_nxt_ind
WHERE ROWID = p_row_id;
dbg('Existing update_end_record');
END update_end_record;
dbg('Calling merge_and_insert_data');
MERGE_AND_INSERT_DATA(
p_date_master_tab => l_date_master_tab
,p_asg_change_tab => l_asg_change_tab
,p_sal_change_tab => l_sal_change_tab
,p_perf_change_tab => l_perf_change_tab
,p_asg_dates => l_asg_dates
,p_prv_rec => l_prv_rec
,p_nxt_ind_rec => l_nxt_ind_rec
,p_asgn_events_tab => p_asgn_events_tab);
dbg('Calling update_end_record');
UPDATE_END_RECORD(p_nxt_ind_rec => l_nxt_ind_rec
,p_row_id => l_prv_rec.row_id );
INSERT /*+ APPEND */ INTO hri_mb_asgn_events_ct
(event_id,
effective_change_date,
effective_change_end_date,
assignment_id,
person_id,
grade_id,
grade_prv_id,
job_id,
job_prv_id,
location_id,
location_prv_id,
organization_id,
organization_prv_id,
supervisor_id,
supervisor_prv_id,
position_id,
position_prv_id,
primary_flag,
primary_flag_prv,
pow_start_date_adj,
change_reason_code,
leaving_reason_code,
fte,
fte_prv,
headcount,
headcount_prv,
anl_slry,
anl_slry_prv,
anl_slry_currency,
anl_slry_currency_prv,
pay_proposal_id,
asg_rtrspctv_strt_event_ind,
assignment_change_ind,
salary_change_ind,
headcount_gain_ind,
headcount_loss_ind,
fte_gain_ind,
fte_loss_ind,
contingent_ind,
employee_ind,
grade_change_ind,
job_change_ind,
position_change_ind,
location_change_ind,
organization_change_ind,
supervisor_change_ind,
worker_hire_ind,
post_hire_asgn_start_ind,
pre_sprtn_asgn_end_ind,
term_voluntary_ind,
term_involuntary_ind,
worker_term_ind,
start_asg_sspnsn_ind,
end_asg_sspnsn_ind,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
effective_change_date_prv,
worker_term_nxt_ind,
pre_sprtn_asgn_end_nxt_ind,
supervisor_change_nxt_ind,
term_voluntary_nxt_ind,
term_involuntary_nxt_ind,
pow_days_on_event_date,
separation_category,
separation_category_nxt,
pow_months_on_event_date,
pow_start_date,
pow_band_change_ind,
perf_nrmlsd_rating,
perf_nrmlsd_rating_prv,
performance_review_id,
perf_review_type_cd,
performance_rating_cd,
perf_band,
perf_band_prv,
perf_rating_change_ind,
perf_band_change_ind,
prsntyp_sk_fk,
summarization_rqd_ind
)
SELECT
hri_mb_asgn_events_ct_s.nextval event_id,
GREATEST(pos.date_start, g_dbi_collection_start_date) effective_change_date,
NVL(pos.actual_termination_date,g_end_of_time) effective_change_end_date,
asg.assignment_id assignment_id,
asg.person_id person_id,
-1 grade_id,
-1 grade_prv_id,
NVL(asg.job_id,-1) job_id,
-1 job_prv_id,
NVL(asg.location_id,-1) location_id,
-1 location_prv_id,
NVL(asg.organization_id,-1) organization_id,
-1 organization_prv_id,
NVL(asg.supervisor_id,-1) supervisor_id,
-1 supervisor_prv_id,
NVL(asg.position_id,-1) position_id,
-1 position_prv_id,
asg.primary_flag primary_flag,
'NA_EDW' primary_flag_prv,
pos.adjusted_svc_date adjusted_svc_date,
'NA_EDW' change_reason_code,
'NA_EDW' leaving_reason_code,
1 fte,
0 fte_prv,
1 headcount,
0 headcount_prv,
0 anl_slry,
0 anl_slry_prv,
'NA_EDW' anl_slry_currency,
'NA_EDW' anl_slry_currency_prv,
-1 pay_proposal_id,
0 asg_rtrspctv_strt_event_ind,
0 assignment_change_ind,
0 salary_change_ind,
0 headcount_gain_ind,
0 headcount_loss_ind,
0 fte_gain_ind,
0 fte_loss_ind,
0 contingent_ind,
1 employee_ind,
0 grade_change_ind,
0 job_change_ind,
0 position_change_ind,
0 location_change_ind,
0 organization_change_ind,
0 supervisor_change_ind,
0 worker_hire_ind,
0 post_hire_asgn_start_ind,
0 pre_sprtn_asgn_end_ind,
0 term_voluntary_ind,
0 term_involuntary_ind,
0 worker_term_ind,
0 start_asg_sspnsn_ind,
0 end_asg_sspnsn_ind,
l_current_time last_update_date,
l_user_id last_updated_by,
l_user_id last_update_login,
l_user_id created_by,
l_current_time creation_date,
null effective_change_date_prv,
0 worker_term_nxt_ind,
0 pre_sprtn_asgn_end_nxt_ind,
0 supervisor_change_nxt_ind,
0 term_voluntary_nxt_ind,
0 term_involuntary_nxt_ind,
0 pow_days_on_event_date,
'NA_EDW' separation_category,
'NA_EDW' separation_category_nxt,
months_between(SYSDATE,pos.date_start) pow_months_on_event_date,
pos.date_start pow_start_date,
0 pow_band_change_ind,
-1 perf_nrmlsd_rating,
-1 perf_nrmlsd_rating_prv,
-1 performance_review_id,
'NA_EDW' perf_review_type_cd,
'NA_EDW' performance_rating_cd,
g_perf_not_rated_id perf_band,
g_perf_not_rated_id perf_band_prv,
0 perf_rating_change_ind,
0 perf_band_change_ind,
hpt.prsntyp_sk_pk prsntyp_sk_fk,
1 summarization_rqd_ind
FROM per_all_assignments_f asg,
per_periods_of_service pos,
hri_cs_prsntyp_ct hpt,
per_person_type_usages_f ptu
WHERE asg.assignment_type = 'E'
AND asg.primary_flag = 'Y'
AND asg.period_of_service_id = pos.period_of_service_id
AND ptu.person_id = asg.person_id
AND hpt.person_type_id = ptu.person_type_id
AND hpt.employment_category_code = NVL(asg.employment_category,'NA_EDW')
AND hpt.primary_flag_code = NVL(asg.primary_flag,'NA_EDW')
AND hpt.assignment_type_code = asg.assignment_type
AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ptu.effective_start_date
AND ptu.effective_end_date;
hri_opl_multi_thread.update_parameters
(p_mthd_action_id => p_mthd_action_id,
p_full_refresh => g_full_refresh,
p_global_start_date => g_dbi_collection_start_date);
dbg('Parameters selected:');
'SELECT /*+ PARALLEL(asgn, DEFAULT, DEFAULT) */
DISTINCT
asgn.assignment_id object_id
FROM per_all_assignments_f asgn
WHERE asgn.assignment_type in (''E'',''C'')
AND asgn.effective_end_date >= to_date(''' ||
to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
''',''DD-MM-YYYY'') - 1
ORDER BY asgn.assignment_id';
insert_pow_change_events;
'SELECT /*+ PARALLEL(evt, DEFAULT, DEFAULT) */
evt.assignment_id object_id
FROM hri_eq_asgn_evnts evt
ORDER BY evt.assignment_id';
SELECT DISTINCT
asg.assignment_id assignment_id,
l_system_date change_date
FROM per_all_assignments_f asg
,per_assignment_status_types ast
WHERE asg.assignment_type in ('E','C')
AND asg.assignment_id BETWEEN p_start_object_id and p_end_object_id
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.effective_end_date >= g_refresh_start_date - 1;
SELECT DISTINCT
evts.assignment_id assignment_id,
erlst_evnt_effective_date change_date
FROM hri_eq_asgn_evnts evts
WHERE evts.assignment_id BETWEEN p_start_object_id and p_end_object_id;
DELETE_RECORDS
(p_start_assignment_id => p_start_object_id,
p_end_assignment_id => p_end_object_id);
hri_opl_wrkfc_trnsfr_events.delete_transfers
(p_start_object_id => p_start_object_id,
p_end_object_id => p_end_object_id);
bulk_insert_rows(p_asgn_events_tab => l_asgn_events_tab);
hri_opl_wrkfc_trnsfr_events.bulk_insert_transfers;
dbg('Full Refresh selected - Creating indexes');
dbg('Full Refresh selected - gathering stats');
DELETE
FROM hri_mb_asgn_events_ct
WHERE assignment_id = p_assignment_id;
bulk_insert_rows(l_asgn_events_tab);