[Home] [Help]
109: --
110: -- -----------------------------------------------------------------------------
111: --
112: -- MAIN PL/SQL TABLE FOR BULK INSERT
113: TYPE g_asgn_events_tab_type IS TABLE OF hri_mb_asgn_events_ct%ROWTYPE
114: INDEX BY BINARY_INTEGER;
115: --
116: -- Number table type with varchar2 indexing.
117: --
250: ,row_id ROWID
251: );
252: --
253: -- Type for storing the different next indicator columns for updating the
254: -- table HRI_MB_ASGN_EVENTS_CT during incremental refresh
255: --
256: TYPE g_nxt_ind_record IS RECORD
257: (worker_term_nxt_ind PLS_INTEGER
258: ,term_voluntary_nxt_ind PLS_INTEGER
432: orgh.orgh_sup_organztn_fk
433: ,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
434: FROM
435: hri_eq_asgn_evnts eq
436: ,hri_mb_asgn_events_ct evt
437: ,hri_cs_orgh_ct orgh
438: WHERE eq.assignment_id = evt.assignment_id
439: AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
440: AND evt.organization_id = orgh.orgh_organztn_fk
477: ,GREATEST(eq.erlst_evnt_effective_date, evt.effective_change_date)
478: ,'ASG_EVENT_PREV'
479: FROM
480: hri_eq_asgn_evnts eq
481: ,hri_mb_asgn_events_ct evt
482: ,hri_cs_suph suph
483: WHERE eq.assignment_id = evt.assignment_id
484: AND evt.supervisor_id = suph.sub_person_id
485: AND eq.erlst_evnt_effective_date <= evt.effective_change_end_date
570: --
571: CURSOR c_pow_changes IS
572: SELECT DISTINCT asgn.assignment_id,
573: add_months(pow_start_date_adj, band_range_high) first_event
574: FROM hri_mb_Asgn_events_ct asgn,
575: hri_cs_pow_band_ct powb
576: WHERE asgn.pow_band_sk_fk = powb.pow_band_sk_pk
577: AND powb.band_range_high is not null
578: AND asgn.worker_term_ind = 0
992: --
993: dbg('Inserting data into table');
994: --
995: FORALL i IN 1..l_row_count
996: INSERT INTO HRI_MB_ASGN_EVENTS_CT (
997: --
998: -- Unique key generated for the events fact
999: --
1000: event_id
1141: VALUES
1142: --
1143: -- Unique key generated for the events fact
1144: --
1145: (hri_mb_asgn_events_ct_s.nextval
1146: --
1147: -- Effective Dates
1148: --
1149: ,l_tab_change_date(i)
1307: --
1308: -- Delete all assingment event records for the events that have occurred on or
1309: -- after the refresh date.
1310: --
1311: DELETE FROM hri_mb_asgn_events_ct evt
1312: WHERE evt.rowid IN
1313: (SELECT evt2.rowid
1314: FROM hri_eq_asgn_evnts eq
1315: , hri_mb_asgn_events_ct evt2
1311: DELETE FROM hri_mb_asgn_events_ct evt
1312: WHERE evt.rowid IN
1313: (SELECT evt2.rowid
1314: FROM hri_eq_asgn_evnts eq
1315: , hri_mb_asgn_events_ct evt2
1316: WHERE eq.assignment_id = evt2.assignment_id
1317: AND evt2.effective_change_date >= eq.erlst_evnt_effective_date
1318: AND eq.assignment_id BETWEEN p_start_assignment_id AND p_end_assignment_id);
1319: --
2966: --
2967: ,pow_band_sk_fk
2968: ,summarization_rqd_ind
2969: ,ROWID
2970: FROM HRI_MB_ASGN_EVENTS_CT
2971: WHERE assignment_id = g_assignment_id
2972: AND (g_refresh_start_date - 1) BETWEEN effective_change_date AND effective_change_end_date;
2973: --
2974: l_effective_start_date DATE;
3646: -- set_indicator procedure to set the indicator
3647: --
3648: l_index_rec g_index_record;
3649: --
3650: -- Holds the next indicator columns for updating of HRI_MB_ASGN_EVENTS_CT
3651: -- table during incremental refresh
3652: --
3653: l_nxt_ind_rec g_nxt_ind_record;
3654: --
4251: --
4252: -- End date the assignment records for the assignment that ovelap the
4253: -- earliest event date
4254: --
4255: UPDATE hri_mb_asgn_events_ct
4256: SET effective_change_end_date = (g_refresh_start_date - 1),
4257: worker_term_nxt_ind = p_nxt_ind_rec.worker_term_nxt_ind,
4258: term_voluntary_nxt_ind = p_nxt_ind_rec.term_voluntary_nxt_ind,
4259: term_involuntary_nxt_ind = p_nxt_ind_rec.term_involuntary_nxt_ind,
4313: --
4314: l_placeholder_rec g_placeholder_rec;
4315: --
4316: -- Holds the indicator values to update the next indicator columns in the
4317: -- table HRI_MB_ASGN_EVENTS_CT during incremental refresh
4318: --
4319: l_nxt_ind_rec g_nxt_ind_record;
4320: --
4321: --
4455: --
4456: l_placeholder_rec.pow_band_sk_fk_prv := l_prv_rec.pow_band_sk_fk_prv;
4457: --
4458: -- 5I Merge and insert data
4459: -- Merge the data collected and insert into the table HRI_MB_ASGN_EVENTS_CT
4460: --
4461: dbg('Calling merge_and_insert_data');
4462: --
4463: MERGE_AND_INSERT_DATA(
4494: -- ----------------------------------------------------------------------------
4495: -- shared_hrms_dflt_prcss
4496: -- This process will be launched by shared_hrms_dflt_prcss (OVERLOADED).
4497: -- ============================================================================
4498: -- This procedure contains the code required to populate hri_mb_asgn_events_ct in shared
4499: -- HR.
4500: --
4501: PROCEDURE shared_hrms_dflt_prcss
4502: IS
4509: l_current_time DATE;
4510: --
4511: BEGIN
4512: --
4513: dbg('Populating hri_mb_asgn_events_ct in shared HR');
4514: --
4515: l_user_id := fnd_global.user_id;
4516: l_current_time := SYSDATE;
4517: --
4517: --
4518: -- 4126398, Added new columns and changed all default values for indicators to 0
4519: -- Inserts row
4520: --
4521: INSERT /*+ APPEND */ INTO hri_mb_asgn_events_ct
4522: (event_id,
4523: effective_change_date,
4524: effective_change_end_date,
4525: assignment_id,
4602: prsntyp_sk_fk,
4603: summarization_rqd_ind
4604: )
4605: SELECT
4606: hri_mb_asgn_events_ct_s.nextval event_id,
4607: GREATEST(pos.date_start, g_dbi_collection_start_date) effective_change_date,
4608: NVL(pos.actual_termination_date,g_end_of_time) effective_change_end_date,
4609: asg.assignment_id assignment_id,
4610: asg.person_id person_id,
4783: IF (g_refresh_start_date IS NULL) THEN
4784:
4785: g_dbi_collection_start_date := hri_oltp_conc_param.get_date_parameter_value
4786: (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
4787: p_process_table_name => 'HRI_MB_ASGN_EVENTS_CT');
4788:
4789: -- If called for the first time set the defaulted parameters
4790: IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
4791:
4790: IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
4791:
4792: g_full_refresh := hri_oltp_conc_param.get_parameter_value
4793: (p_parameter_name => 'FULL_REFRESH',
4794: p_process_table_name => 'HRI_MB_ASGN_EVENTS_CT');
4795:
4796: -- Log defaulted parameters so the slave processes pick up
4797: hri_opl_multi_thread.update_parameters
4798: (p_mthd_action_id => p_mthd_action_id,
4912: END IF;
4913: --
4914: -- Disable the WHO trigger
4915: --
4916: EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MB_ASGN_EVENTS_CT_WHO DISABLE';
4917: EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MDP_ORGH_TRANSFERS_CT_WHO DISABLE';
4918: --
4919: -- ---------------------------------------------------------------------------
4920: -- Full Refresh Section (including shared HR)
4930: -- Drop Indexes
4931: --
4932: hri_utl_ddl.log_and_drop_indexes(
4933: p_application_short_name => 'HRI',
4934: p_table_name => 'HRI_MB_ASGN_EVENTS_CT',
4935: p_table_owner => l_schema);
4936: hri_utl_ddl.log_and_drop_indexes(
4937: p_application_short_name => 'HRI',
4938: p_table_name => 'HRI_MDP_ORGH_TRANSFERS_CT',
4939: p_table_owner => l_schema);
4940: --
4941: -- Truncate the tables
4942: --
4943: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_ASGN_EVENTS_CT';
4944: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MDP_ORGH_TRANSFERS_CT';
4945: --
4946: -- In shared HR mode populate the table in a single direct insert
4947: -- Do not return a SQL statement so that the process_range and
5362: set_parameters
5363: (p_mthd_action_id => p_mthd_action_id,
5364: p_mthd_stage_code => 'POST_PROCESS');
5365: --
5366: hri_bpl_conc_log.record_process_start('HRI_MB_ASGN_EVENTS_CT');
5367: --
5368: -- Recreate indexes and gather stats for full refresh or shared HR insert
5369: --
5370: IF (g_full_refresh = 'Y' OR
5375: dbg('Full Refresh selected - Creating indexes');
5376: --
5377: HRI_UTL_DDL.recreate_indexes(
5378: p_application_short_name => 'HRI',
5379: p_table_name => 'HRI_MB_ASGN_EVENTS_CT',
5380: p_table_owner => l_schema);
5381: hri_utl_ddl.recreate_indexes(
5382: p_application_short_name => 'HRI',
5383: p_table_name => 'HRI_MDP_ORGH_TRANSFERS_CT',
5384: p_table_owner => l_schema);
5385: --
5386: dbg('Full Refresh selected - gathering stats');
5387: --
5388: fnd_stats.gather_table_stats(l_schema,'HRI_MB_ASGN_EVENTS_CT');
5389: --
5390: END IF;
5391: --
5392: ELSE
5403: --
5404: -- Enable the WHO trigger on the events fact table
5405: --
5406: dbg('Enabling the who trigger');
5407: EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MB_ASGN_EVENTS_CT_WHO ENABLE';
5408: EXECUTE IMMEDIATE 'ALTER TRIGGER HRI_MDP_ORGH_TRANSFERS_CT_WHO ENABLE';
5409: --
5410: -- Purge the Events Queue. The events queue needs to be purged
5411: -- even after the after full refresh. Recollecting incremental changes
5459: g_collect_hdc := 'Y';
5460: g_perf_query := hri_bpl_perf_rating.get_perf_sql;
5461: --
5462: DELETE
5463: FROM hri_mb_asgn_events_ct
5464: WHERE assignment_id = p_assignment_id;
5465: --
5466: collect(p_assignment_id => p_assignment_id,
5467: p_asgn_events_tab => l_asgn_events_tab) ;