[Home] [Help]
105:
106: l_dbi_collection_start_date :=
107: hri_oltp_conc_param.get_date_parameter_value
108: (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
109: p_process_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT');
110:
111: -- If called for the first time set the defaulted parameters
112: IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
113:
113:
114: g_full_refresh :=
115: hri_oltp_conc_param.get_parameter_value
116: (p_parameter_name => 'FULL_REFRESH',
117: p_process_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT');
118:
119: -- Log defaulted parameters so the slave processes pick up
120: hri_opl_multi_thread.update_parameters
121: (p_mthd_action_id => p_mthd_action_id,
152:
153: IF g_row_count > 0 THEN
154:
155: FORALL i IN 1..g_row_count
156: INSERT INTO hri_mds_wrkfc_mgrh_c01_ct
157: (sup_person_fk
158: ,sup_mngrsc_fk
159: ,sup_directs_only_flag
160: ,time_day_mnth_start_fk
476: ,fct.adt_pow_band
477: FROM
478: hri_cs_mngrsc_ct chn
479: ,hri_cs_suph suph
480: ,hri_mds_wrkfc_mgrh_c01_ct fct
481: WHERE chn.mgrs_person_fk = p_manager_id
482: AND p_snapshot_date BETWEEN chn.mgrs_date_start
483: AND chn.mgrs_date_end
484: AND suph.sup_person_id = chn.mgrs_person_fk
831:
832: BEGIN
833:
834: -- Remove rows to be replaced for range
835: DELETE FROM hri_mds_wrkfc_mgrh_c01_ct snp
836: WHERE snp.rowid IN
837: (SELECT /*+ ORDERED */
838: snp2.rowid
839: FROM
838: snp2.rowid
839: FROM
840: hri_eq_wrkfc_evt_mgrh eq
841: ,hri_cs_mngrsc_ct mgr
842: ,hri_mds_wrkfc_mgrh_c01_ct snp2
843: WHERE eq.sup_person_id = snp2.sup_person_fk
844: AND eq.sup_person_id = mgr.mgrs_person_fk
845: AND mgr.mgrs_level = p_mthd_range_lvl
846: AND eq.erlst_evnt_effective_date <= mgr.mgrs_date_end
956: null;
957: END IF;
958:
959: -- Disable WHO trigger
960: run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_MGRH_C01_CT_WHO DISABLE');
961:
962: -- ********************
963: -- Full Refresh Section
964: -- ********************
965: IF (g_full_refresh = 'Y' OR
966: g_mthd_action_array.foundation_hr_flag = 'Y') THEN
967:
968: -- Empty out absence dimension table
969: l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MDS_WRKFC_MGRH_C01_CT';
970: EXECUTE IMMEDIATE(l_sql_stmt);
971:
972: -- In shared HR mode do not return a SQL statement so that the
973: -- process_range and post_process will not be executed
981:
982: -- Drop all the indexes on the table
983: hri_utl_ddl.log_and_drop_indexes
984: (p_application_short_name => 'HRI',
985: p_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT',
986: p_table_owner => l_schema,
987: p_index_excptn_lst => 'HRI_MDS_WRKFC_MGRH_C01_CT_N2');
988:
989: -- Set the SQL statement for the entire range
983: hri_utl_ddl.log_and_drop_indexes
984: (p_application_short_name => 'HRI',
985: p_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT',
986: p_table_owner => l_schema,
987: p_index_excptn_lst => 'HRI_MDS_WRKFC_MGRH_C01_CT_N2');
988:
989: -- Set the SQL statement for the entire range
990: p_sqlstr :=
991: 'SELECT /*+ PARALLEL(mgr, default, default) */ DISTINCT
1046: (p_mthd_action_id => p_mthd_action_id,
1047: p_mthd_stage_code => 'POST_PROCESS');
1048:
1049: -- Log process end
1050: hri_bpl_conc_log.record_process_start('HRI_MDS_WRKFC_MGRH_C01_CT');
1051: hri_bpl_conc_log.log_process_end(
1052: p_status => TRUE
1053: ,p_period_from => TRUNC(g_refresh_start_date)
1054: ,p_period_to => TRUNC(SYSDATE)
1067: -- Recreate indexes in full refresh mode
1068: IF (g_full_refresh = 'Y') THEN
1069: hri_utl_ddl.recreate_indexes
1070: (p_application_short_name => 'HRI',
1071: p_table_name => 'HRI_MDS_WRKFC_MGRH_C01_CT',
1072: p_table_owner => l_schema);
1073:
1074: END IF;
1075: