[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_row_count > 0 THEN
FORALL i IN 1..g_row_count
INSERT INTO hri_mds_wrkfc_orgh_c01_ct
(sup_organztn_fk
,sup_directs_only_flag
,time_day_mnth_start_fk
,time_day_mnth_end_fk
,time_month_snp_fk
,job_function_fk
,job_family_fk
,geo_country_fk
,prfm_perfband_fk
,pow_powband_fk
,ptyp_wrktyp_fk
,cur_currency_fk
,headcount_start
,headcount_end
,headcount_hire
,headcount_term
,headcount_sep_vol
,headcount_sep_invol
,headcount_prmtn
,fte_start
,fte_end
,fte_hire
,fte_term
,fte_sep_vol
,fte_sep_invol
,fte_prmtn
,count_pasg_end
,count_pasg_hire
,count_pasg_term
,count_pasg_sep_vol
,count_pasg_sep_invol
,count_asg_end
,count_asg_hire
,count_asg_term
,count_asg_sep_vol
,count_asg_sep_invol
,count_asg_prmtn
,pow_days_on_end_date
,pow_months_on_end_date
,days_since_last_prmtn
,months_since_last_prmtn
,anl_slry_start
,anl_slry_end
,employee_ind
,contingent_ind
,adt_pow_band
,creation_date
,created_by
,last_updated_by
,last_update_login
,last_update_date)
VALUES
(g_tab_sup_organztn_fk(i)
,g_tab_sup_directs_only_flag(i)
,g_tab_time_day_mnth_start_fk(i)
,g_tab_time_day_mnth_end_fk(i)
,g_tab_time_month_snp_fk(i)
,g_tab_job_function_fk(i)
,g_tab_job_family_fk(i)
,g_tab_geo_country_fk(i)
,g_tab_prfm_perfband_fk(i)
,g_tab_pow_powband_fk(i)
,g_tab_ptyp_wrktyp_fk(i)
,g_tab_cur_currency_fk(i)
,g_tab_headcount_start(i)
,g_tab_headcount_end(i)
,g_tab_headcount_hire(i)
,g_tab_headcount_term(i)
,g_tab_headcount_sep_vol(i)
,g_tab_headcount_sep_invol(i)
,g_tab_headcount_prmtn(i)
,g_tab_fte_start(i)
,g_tab_fte_end(i)
,g_tab_fte_hire(i)
,g_tab_fte_term(i)
,g_tab_fte_sep_vol(i)
,g_tab_fte_sep_invol(i)
,g_tab_fte_prmtn(i)
,g_tab_count_pasg_end(i)
,g_tab_count_pasg_hire(i)
,g_tab_count_pasg_term(i)
,g_tab_count_pasg_sep_vol(i)
,g_tab_count_pasg_sep_invol(i)
,g_tab_count_asg_end(i)
,g_tab_count_asg_hire(i)
,g_tab_count_asg_term(i)
,g_tab_count_asg_sep_vol(i)
,g_tab_count_asg_sep_invol(i)
,g_tab_count_asg_prmtn(i)
,g_tab_pow_days_on_end_date(i)
,g_tab_pow_months_on_end_date(i)
,g_tab_days_since_last_prmtn(i)
,g_tab_months_since_last_prmtn(i)
,g_tab_anl_slry_start(i)
,g_tab_anl_slry_end(i)
,g_tab_employee_ind(i)
,g_tab_contingent_ind(i)
,g_tab_adt_pow_band(i)
,g_sysdate
,g_user
,g_user
,g_user
,g_sysdate);
END bulk_insert_rows;
PROCEDURE insert_row
(p_sup_organztn_fk IN NUMBER
,p_sup_directs_only_flag IN VARCHAR2
,p_time_day_mnth_start_fk IN DATE
,p_time_day_mnth_end_fk IN DATE
,p_time_month_snp_fk IN NUMBER
,p_job_function_fk IN VARCHAR2
,p_job_family_fk IN VARCHAR2
,p_geo_country_fk IN VARCHAR2
,p_prfm_perfband_fk IN NUMBER
,p_pow_powband_fk IN NUMBER
,p_ptyp_wrktyp_fk IN VARCHAR2
,p_cur_currency_fk IN VARCHAR2
,p_headcount_start IN NUMBER
,p_headcount_end IN NUMBER
,p_headcount_hire IN NUMBER
,p_headcount_term IN NUMBER
,p_headcount_sep_vol IN NUMBER
,p_headcount_sep_invol IN NUMBER
,p_headcount_prmtn IN NUMBER
,p_fte_start IN NUMBER
,p_fte_end IN NUMBER
,p_fte_hire IN NUMBER
,p_fte_term IN NUMBER
,p_fte_sep_vol IN NUMBER
,p_fte_sep_invol IN NUMBER
,p_fte_prmtn IN NUMBER
,p_count_pasg_end IN NUMBER
,p_count_pasg_hire IN NUMBER
,p_count_pasg_term IN NUMBER
,p_count_pasg_sep_vol IN NUMBER
,p_count_pasg_sep_invol IN NUMBER
,p_count_asg_end IN NUMBER
,p_count_asg_hire IN NUMBER
,p_count_asg_term IN NUMBER
,p_count_asg_sep_vol IN NUMBER
,p_count_asg_sep_invol IN NUMBER
,p_count_asg_prmtn IN NUMBER
,p_pow_days_on_end_date IN NUMBER
,p_pow_months_on_end_date IN NUMBER
,p_days_since_last_prmtn IN NUMBER
,p_months_since_last_prmtn IN NUMBER
,p_anl_slry_start IN NUMBER
,p_anl_slry_end IN NUMBER
,p_employee_ind IN NUMBER
,p_contingent_ind IN NUMBER
,p_adt_pow_band IN NUMBER) IS
BEGIN
g_row_count := g_row_count + 1;
END insert_row;
SELECT
tab.sup_organztn_fk
,tab.sup_directs_only_flag
,tab.time_day_mnth_start_fk
,tab.time_day_mnth_end_fk
,tab.time_month_snp_fk
,tab.job_function_fk
,tab.job_family_fk
,tab.geo_country_fk
,tab.prfm_perfband_fk
,tab.pow_powband_fk
,tab.ptyp_wrktyp_fk
,tab.cur_currency_fk
,SUM(tab.headcount_start) headcount_start
,SUM(tab.headcount_end) headcount_end
,SUM(tab.headcount_hire) headcount_hire
,SUM(tab.headcount_term) headcount_term
,SUM(tab.headcount_sep_vol) headcount_sep_vol
,SUM(tab.headcount_sep_invol) headcount_sep_invol
,SUM(tab.headcount_prmtn) headcount_prmtn
,SUM(tab.fte_start) fte_start
,SUM(tab.fte_end) fte_end
,SUM(tab.fte_hire) fte_hire
,SUM(tab.fte_term) fte_term
,SUM(tab.fte_sep_vol) fte_sep_vol
,SUM(tab.fte_sep_invol) fte_sep_invol
,SUM(tab.fte_prmtn) fte_prmtn
,SUM(tab.count_pasg_end) count_pasg_end
,SUM(tab.count_pasg_hire) count_pasg_hire
,SUM(tab.count_pasg_term) count_pasg_term
,SUM(tab.count_pasg_sep_vol) count_pasg_sep_vol
,SUM(tab.count_pasg_sep_invol) count_pasg_sep_invol
,SUM(tab.count_asg_end) count_asg_end
,SUM(tab.count_asg_hire) count_asg_hire
,SUM(tab.count_asg_term) count_asg_term
,SUM(tab.count_asg_sep_vol) count_asg_sep_vol
,SUM(tab.count_asg_sep_invol) count_asg_sep_invol
,SUM(tab.count_asg_prmtn) count_asg_prmtn
,SUM(tab.pow_days_on_end_date) pow_days_on_end_date
,SUM(tab.pow_months_on_end_date) pow_months_on_end_date
,SUM(tab.days_since_last_prmtn) days_since_last_prmtn
,SUM(tab.months_since_last_prmtn) months_since_last_prmtn
,SUM(tab.anl_slry_start) anl_slry_start
,SUM(tab.anl_slry_end) anl_slry_end
,tab.employee_ind
,tab.contingent_ind
,tab.adt_pow_band
FROM
(SELECT
orgh.orgh_sup_organztn_fk sup_organztn_fk
,'N' sup_directs_only_flag
,fct.time_day_mnth_start_fk
,fct.time_day_mnth_end_fk
,fct.time_month_snp_fk
,fct.job_function_fk
,fct.job_family_fk
,fct.geo_country_fk
,fct.prfm_perfband_fk
,fct.pow_powband_fk
,fct.ptyp_wrktyp_fk
,fct.cur_currency_fk
,fct.headcount_start
,fct.headcount_end
,fct.headcount_hire
,fct.headcount_term
,fct.headcount_sep_vol
,fct.headcount_sep_invol
,fct.headcount_prmtn
,fct.fte_start
,fct.fte_end
,fct.fte_hire
,fct.fte_term
,fct.fte_sep_vol
,fct.fte_sep_invol
,fct.fte_prmtn
,fct.count_pasg_end
,fct.count_pasg_hire
,fct.count_pasg_term
,fct.count_pasg_sep_vol
,fct.count_pasg_sep_invol
,fct.count_asg_end
,fct.count_asg_hire
,fct.count_asg_term
,fct.count_asg_sep_vol
,fct.count_asg_sep_invol
,fct.count_asg_prmtn
,fct.pow_days_on_end_date
,fct.pow_months_on_end_date
,fct.days_since_last_prmtn
,fct.months_since_last_prmtn
,fct.anl_slry_start
,fct.anl_slry_end
,fct.employee_ind
,fct.contingent_ind
,fct.adt_pow_band
FROM
hri_cs_orgh_ct orgh
,hri_mds_wrkfc_orgh_c01_ct fct
WHERE orgh.orgh_sup_organztn_fk = p_organization_fk
AND orgh.orgh_relative_level = 1
AND orgh.orgh_sub_node_has_workers_flag = 'Y'
AND orgh.orgh_organztn_fk = fct.sup_organztn_fk
AND fct.time_month_snp_fk = v_month_id
AND fct.sup_directs_only_flag = 'N'
UNION ALL
SELECT /*+ ORDERED USE_NL(wevt ctr jobh ptyp) */
wevt.org_organztn_fk sup_organztn_fk
,dcts.sup_directs_only_flag sup_directs_only_flag
,wevt.time_day_mnth_start_fk time_day_mnth_start_fk
,wevt.time_day_mnth_end_fk time_day_mnth_end_fk
,wevt.time_month_snp_fk time_month_snp_fk
,jobh.job_fnctn_code job_function_fk
,jobh.job_fmly_code job_family_fk
,ctr.country_code geo_country_fk
,wevt.prfm_perfband_fk prfm_perfband_fk
,wevt.pow_powband_fk pow_powband_fk
,ptyp.wkth_wktyp_sk_fk ptyp_wrktyp_fk
,wevt.cur_currency_fk cur_currency_fk
,wevt.headcount_start headcount_start
,wevt.headcount_end headcount_end
,wevt.headcount_hire headcount_hire
,wevt.headcount_term headcount_term
,wevt.headcount_sep_vol headcount_sep_vol
,wevt.headcount_sep_invol headcount_sep_invol
,wevt.headcount_prmtn headcount_prmtn
,wevt.fte_start fte_start
,wevt.fte_end fte_end
,wevt.fte_hire fte_hire
,wevt.fte_term fte_term
,wevt.fte_sep_vol fte_sep_vol
,wevt.fte_sep_invol fte_sep_invol
,wevt.fte_prmtn fte_prmtn
,wevt.count_pasg_end count_pasg_end
,wevt.count_pasg_hire count_pasg_hire
,wevt.count_pasg_term count_pasg_term
,wevt.count_pasg_sep_vol count_pasg_sep_vol
,wevt.count_pasg_sep_invol count_pasg_sep_invol
,wevt.count_asg_end count_asg_end
,wevt.count_asg_hire count_asg_hire
,wevt.count_asg_term count_asg_term
,wevt.count_asg_sep_vol count_asg_sep_vol
,wevt.count_asg_sep_invol count_asg_sep_invol
,wevt.count_asg_prmtn count_asg_prmtn
,wevt.pow_days_on_end_date pow_days_on_end_date
,wevt.pow_months_on_end_date pow_months_on_end_date
,wevt.days_since_last_prmtn days_since_last_prmtn
,wevt.months_since_last_prmtn months_since_last_prmtn
,wevt.anl_slry_start anl_slry_start
,wevt.anl_slry_end anl_slry_end
,wevt.employee_ind employee_ind
,wevt.contingent_ind contingent_ind
,wevt.adt_pow_band adt_pow_band
FROM
hri_mds_wrkfc_mnth_ct wevt
,hri_cs_geo_lochr_ct ctr
,hri_cs_jobh_ct jobh
,hri_cs_prsntyp_ct ptyp
,(SELECT 'Y' sup_directs_only_flag FROM dual
UNION ALL
SELECT 'N' sup_directs_only_flag FROM dual
) dcts
WHERE wevt.org_organztn_fk = p_organization_fk
AND wevt.time_month_snp_fk = v_month_id
AND ctr.location_id = wevt.geo_location_fk
AND jobh.job_id = wevt.job_job_fk
AND ptyp.prsntyp_sk_pk = wevt.ptyp_pertyp_fk
) tab
GROUP BY
tab.sup_organztn_fk
,tab.sup_directs_only_flag
,tab.time_day_mnth_start_fk
,tab.time_day_mnth_end_fk
,tab.time_month_snp_fk
,tab.cur_currency_fk
,tab.job_function_fk
,tab.job_family_fk
,tab.geo_country_fk
,tab.prfm_perfband_fk
,tab.pow_powband_fk
,tab.ptyp_wrktyp_fk
,tab.cur_currency_fk
,tab.employee_ind
,tab.contingent_ind
,tab.adt_pow_band;
insert_row
(p_sup_organztn_fk => l_sup_organztn_fk(i)
,p_sup_directs_only_flag => l_sup_directs_only_flag(i)
,p_time_day_mnth_start_fk => l_time_day_mnth_start_fk(i)
,p_time_day_mnth_end_fk => l_time_day_mnth_end_fk(i)
,p_time_month_snp_fk => l_time_month_snp_fk(i)
,p_job_function_fk => l_job_function_fk(i)
,p_job_family_fk => l_job_family_fk(i)
,p_geo_country_fk => l_geo_country_fk(i)
,p_prfm_perfband_fk => l_prfm_perfband_fk(i)
,p_pow_powband_fk => l_pow_powband_fk(i)
,p_ptyp_wrktyp_fk => l_ptyp_wrktyp_fk(i)
,p_cur_currency_fk => l_cur_currency_fk(i)
,p_headcount_start => l_headcount_start(i)
,p_headcount_end => l_headcount_end(i)
,p_headcount_hire => l_headcount_hire(i)
,p_headcount_term => l_headcount_term(i)
,p_headcount_sep_vol => l_headcount_sep_vol(i)
,p_headcount_sep_invol => l_headcount_sep_invol(i)
,p_headcount_prmtn => l_headcount_prmtn(i)
,p_fte_start => l_fte_start(i)
,p_fte_end => l_fte_end(i)
,p_fte_hire => l_fte_hire(i)
,p_fte_term => l_fte_term(i)
,p_fte_sep_vol => l_fte_sep_vol(i)
,p_fte_sep_invol => l_fte_sep_invol(i)
,p_fte_prmtn => l_fte_prmtn(i)
,p_count_pasg_end => l_count_pasg_end(i)
,p_count_pasg_hire => l_count_pasg_hire(i)
,p_count_pasg_term => l_count_pasg_term(i)
,p_count_pasg_sep_vol => l_count_pasg_sep_vol(i)
,p_count_pasg_sep_invol => l_count_pasg_sep_invol(i)
,p_count_asg_end => l_count_asg_end(i)
,p_count_asg_hire => l_count_asg_hire(i)
,p_count_asg_term => l_count_asg_term(i)
,p_count_asg_sep_vol => l_count_asg_sep_vol(i)
,p_count_asg_sep_invol => l_count_asg_sep_invol(i)
,p_count_asg_prmtn => l_count_asg_prmtn(i)
,p_pow_days_on_end_date => l_pow_days_on_end_date(i)
,p_pow_months_on_end_date => l_pow_months_on_end_date(i)
,p_days_since_last_prmtn => l_days_since_last_prmtn(i)
,p_months_since_last_prmtn => l_months_since_last_prmtn(i)
,p_anl_slry_start => l_anl_slry_start(i)
,p_anl_slry_end => l_anl_slry_end(i)
,p_employee_ind => l_employee_ind(i)
,p_contingent_ind => l_contingent_ind(i)
,p_adt_pow_band => l_adt_pow_band(i));
bulk_insert_rows;
SELECT
orgh.orgh_sup_organztn_fk organztn_fk
,add_months(to_date(SUBSTR(to_char(mnth.month_id), 1, 4) || '-' ||
SUBSTR(to_char(mnth.month_id), 6, 2), 'YYYY-MM'),
1) - 1 snapshot_date
FROM
hri_cs_orgh_ct orgh
,fii_time_month mnth
WHERE orgh.orgh_relative_level = 0
AND mnth.month_id >= to_number(to_char(g_dbi_start_date, 'YYYYQMM'))
AND mnth.month_id <= to_number(to_char(g_sysdate, 'YYYYQMM'))
AND orgh.orgh_sub_node_has_workers_flag = 'Y'
AND orgh.orgh_sup_organztn_fk BETWEEN p_start_object_id
AND p_end_object_id
AND orgh.orgh_level = p_mthd_range_lvl;
SELECT
eq.organization_id organztn_fk
,add_months(to_date(SUBSTR(to_char(mnth.month_id), 1, 4) || '-' ||
SUBSTR(to_char(mnth.month_id), 6, 2), 'YYYY-MM'),
1) - 1 snapshot_date
FROM
hri_eq_wrkfc_evt_orgh eq
,hri_cs_orgh_ct orgh
,fii_time_month mnth
WHERE mnth.month_id >= to_number(to_char(eq.erlst_evnt_effective_date, 'YYYYQMM'))
AND mnth.month_id >= to_number(to_char(g_dbi_start_date, 'YYYYQMM'))
AND mnth.month_id <= to_number(to_char(g_sysdate, 'YYYYQMM'))
AND eq.organization_id BETWEEN p_start_object_id
AND p_end_object_id
AND orgh.orgh_sup_organztn_fk = eq.organization_id
AND orgh.orgh_relative_level = 0
AND orgh.orgh_level = p_mthd_range_lvl;
DELETE FROM hri_mds_wrkfc_orgh_c01_ct snp
WHERE snp.rowid IN
(SELECT /*+ ORDERED */
snp2.rowid
FROM
hri_eq_wrkfc_evt_orgh eq
,hri_cs_orgh_ct orgh
,hri_mds_wrkfc_orgh_c01_ct snp2
WHERE eq.organization_id = snp2.sup_organztn_fk
AND eq.organization_id BETWEEN p_start_object_id
AND p_end_object_id
AND orgh.orgh_sup_organztn_fk = eq.organization_id
AND orgh.orgh_level = p_mthd_range_lvl
AND orgh.orgh_relative_level = 0
AND orgh.orgh_sub_node_has_workers_flag = 'Y'
AND snp2.time_month_snp_fk >=
to_number(to_char(eq.erlst_evnt_effective_date, 'YYYYQMM'))
);
bulk_insert_rows;
DELETE FROM hri_eq_wrkfc_evt_orgh eq
WHERE EXISTS
(SELECT null
FROM hri_eq_wrkfc_evt_orgh eq2
WHERE eq2.organization_id = eq.organization_id
AND (eq2.erlst_evnt_effective_date < eq.erlst_evnt_effective_date
OR (eq2.rowid < eq.rowid AND
eq2.erlst_evnt_effective_date = eq.erlst_evnt_effective_date)));
'SELECT
orgh_sup_organztn_fk object_id
,orgh_level object_lvl
FROM
hri_cs_orgh_ct orgh
WHERE orgh.orgh_relative_level = 0
AND orgh.orgh_sub_node_has_workers_flag = ''Y''
ORDER BY 2 DESC, 1';
'SELECT
orgh.orgh_sup_organztn_fk object_id
,orgh.orgh_level object_lvl
FROM
hri_cs_orgh_ct orgh
,hri_eq_wrkfc_evt_orgh eq
WHERE orgh.orgh_relative_level = 0
AND orgh.orgh_sub_node_has_workers_flag = ''Y''
AND orgh.orgh_sup_organztn_fk = eq.organization_id
ORDER BY 2 DESC, 1';
SELECT
mthd_range_id
,object_lvl
,min(object_id) start_object_id
,max(object_id) end_object_id
FROM
(SELECT
1000 - object_lvl + CEIL(ROWNUM / 20) mthd_range_id
,object_lvl
,object_id
FROM
(SELECT
orgh_sup_organztn_fk object_id
,orgh_level object_lvl
FROM
hri_cs_orgh_ct orgh
WHERE orgh.orgh_relative_level = 0
AND orgh.orgh_sub_node_has_workers_flag = 'Y'
ORDER BY 2 DESC, 1
)
)
GROUP BY
mthd_range_id
,object_lvl
ORDER BY object_lvl DESC, mthd_range_id;
SELECT
chunk_no
,MIN(object_id) start_object_id
,MAX(object_id) end_object_id
,1 object_lvl
FROM
(SELECT
object_id
,CEIL(ROWNUM / 20) chunk_no
FROM
(SELECT
(eq.organization_id * g_no_qtrs_to_process) +
(months_between(qtr.start_date,
trunc(g_dbi_start_date,'Q')) / 3) object_id
FROM
hri_eq_wrkfc_evt_orgh eq
,fii_time_qtr qtr
WHERE qtr.end_date >= eq.erlst_evnt_effective_date
AND qtr.start_date <= g_refresh_to_date
ORDER BY 1
) tab
) chunks
GROUP BY
chunk_no;
bulk_insert_rows;
bulk_insert_rows;