[Home] [Help]
104:
105: l_dbi_collection_start_date :=
106: hri_oltp_conc_param.get_date_parameter_value
107: (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
108: p_process_table_name => 'HRI_MDS_WRKFC_ORGH_C01_CT');
109:
110: -- If called for the first time set the defaulted parameters
111: IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
112:
112:
113: g_full_refresh :=
114: hri_oltp_conc_param.get_parameter_value
115: (p_parameter_name => 'FULL_REFRESH',
116: p_process_table_name => 'HRI_MDS_WRKFC_ORGH_C01_CT');
117:
118: -- Log defaulted parameters so the slave processes pick up
119: hri_opl_multi_thread.update_parameters
120: (p_mthd_action_id => p_mthd_action_id,
155:
156: IF g_row_count > 0 THEN
157:
158: FORALL i IN 1..g_row_count
159: INSERT INTO hri_mds_wrkfc_orgh_c01_ct
160: (sup_organztn_fk
161: ,sup_directs_only_flag
162: ,time_day_mnth_start_fk
163: ,time_day_mnth_end_fk
476: ,fct.contingent_ind
477: ,fct.adt_pow_band
478: FROM
479: hri_cs_orgh_ct orgh
480: ,hri_mds_wrkfc_orgh_c01_ct fct
481: WHERE orgh.orgh_sup_organztn_fk = p_organization_fk
482: AND orgh.orgh_relative_level = 1
483: AND orgh.orgh_sub_node_has_workers_flag = 'Y'
484: AND orgh.orgh_organztn_fk = fct.sup_organztn_fk
796:
797: BEGIN
798:
799: -- Remove rows to be replaced for range
800: DELETE FROM hri_mds_wrkfc_orgh_c01_ct snp
801: WHERE snp.rowid IN
802: (SELECT /*+ ORDERED */
803: snp2.rowid
804: FROM
803: snp2.rowid
804: FROM
805: hri_eq_wrkfc_evt_orgh eq
806: ,hri_cs_orgh_ct orgh
807: ,hri_mds_wrkfc_orgh_c01_ct snp2
808: WHERE eq.organization_id = snp2.sup_organztn_fk
809: AND eq.organization_id BETWEEN p_start_object_id
810: AND p_end_object_id
811: AND orgh.orgh_sup_organztn_fk = eq.organization_id
923: null;
924: END IF;
925:
926: -- Disable WHO trigger
927: run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_ORGH_C01_CT_WHO DISABLE');
928:
929: -- ********************
930: -- Full Refresh Section
931: -- ********************
932: IF (g_full_refresh = 'Y' OR
933: g_mthd_action_array.foundation_hr_flag = 'Y') THEN
934:
935: -- Empty out absence dimension table
936: l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MDS_WRKFC_ORGH_C01_CT';
937: EXECUTE IMMEDIATE(l_sql_stmt);
938:
939: -- In shared HR mode do not return a SQL statement so that the
940: -- process_range and post_process will not be executed
948:
949: -- Drop all the indexes on the table
950: hri_utl_ddl.log_and_drop_indexes
951: (p_application_short_name => 'HRI',
952: p_table_name => 'HRI_MDS_WRKFC_ORGH_C01_CT',
953: p_table_owner => l_schema,
954: p_index_excptn_lst => 'HRI_MDS_WRKFC_ORGH_C01_CT_N1');
955:
956: -- Set the SQL statement for the entire range
950: hri_utl_ddl.log_and_drop_indexes
951: (p_application_short_name => 'HRI',
952: p_table_name => 'HRI_MDS_WRKFC_ORGH_C01_CT',
953: p_table_owner => l_schema,
954: p_index_excptn_lst => 'HRI_MDS_WRKFC_ORGH_C01_CT_N1');
955:
956: -- Set the SQL statement for the entire range
957: p_sqlstr :=
958: 'SELECT
1008: (p_mthd_action_id => p_mthd_action_id,
1009: p_mthd_stage_code => 'POST_PROCESS');
1010:
1011: -- Log process end
1012: hri_bpl_conc_log.record_process_start('HRI_MDS_WRKFC_ORGH_C01_CT');
1013: hri_bpl_conc_log.log_process_end(
1014: p_status => TRUE
1015: ,p_period_from => TRUNC(g_refresh_start_date)
1016: ,p_period_to => TRUNC(SYSDATE)
1018:
1019: END IF;
1020:
1021: -- Enable WHO trigger
1022: run_sql_stmt_noerr('ALTER TRIGGER HRI_MDS_WRKFC_ORGH_C01_CT_WHO ENABLE');
1023:
1024: -- Get HRI schema name - get_app_info populates l_schema
1025: IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
1026: null;
1029: -- Recreate indexes
1030: IF (g_full_refresh = 'Y') THEN
1031: hri_utl_ddl.recreate_indexes
1032: (p_application_short_name => 'HRI',
1033: p_table_name => 'HRI_MDS_WRKFC_ORGH_C01_CT',
1034: p_table_owner => l_schema);
1035: END IF;
1036:
1037: -- Empty out workforce organization summary event queue