[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Asgn_Evnt_Fct_Evnt_Q
(p_assignment_id IN NUMBER
,p_change_date IN DATE -- The effective change date
,p_start_date IN DATE -- The date the events were captured from
);
SELECT dated_table_id
FROM pay_dated_tables
WHERE table_name = p_table_name;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'nls_date_format';
INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_mnth
(assignment_id
,erlst_evnt_effective_date)
SELECT DISTINCT
asg_assgnmnt_fk
,add_months(trunc(g_capture_from_date,'MONTH'), 1)
FROM
hri_mb_wrkfc_evt_ct
WHERE g_capture_from_date <= time_day_evt_end_fk
AND add_months(trunc(sysdate,'MONTH'), 1) > time_day_evt_fk
AND term_or_end_ind = 0;
INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_evt_mgrh
(sup_person_id
,erlst_evnt_effective_date
,source_code)
SELECT DISTINCT
mgrs_person_fk
,add_months(trunc(g_capture_from_date,'MONTH'), 1)
,'NEW_SNAP_DATE'
FROM
hri_cs_mngrsc_ct
WHERE g_capture_from_date <= mgrs_date_end
AND add_months(trunc(sysdate,'MONTH'), 1) > mgrs_date_start;
INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_evt_orgh
(organization_id
,erlst_evnt_effective_date)
SELECT
orgh_sup_organztn_fk
,add_months(trunc(g_capture_from_date,'MONTH'), 1)
FROM
hri_cs_orgh_ct
WHERE orgh_relative_level = 0;
SELECT period_from
FROM bis_refresh_log
WHERE object_name = 'HRI_CS_SUPH'
AND status='SUCCESS'
AND attribute1 = 'Y'
AND last_update_date =( SELECT max(last_update_date)
FROM bis_refresh_log
WHERE object_name= 'HRI_CS_SUPH'
AND status='SUCCESS'
AND attribute1 = 'Y')
ORDER BY last_update_date DESC;
SELECT min(effective_start_date)
FROM hri_cs_suph;
SELECT 1
FROM per_person_type_usages_f ptu,
per_person_types ppt
WHERE 1=1
AND ptu.person_type_usage_id = p_person_type_usage_id
AND p_effective_date BETWEEN ptu.effective_start_date and ptu.effective_end_date
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','CWK');
SELECT asgn.person_id, NVL(min(asgn.projected_assignment_end) + 1, p_effective_date)
FROM per_all_assignments_f asgn
WHERE primary_flag = 'Y'
--
-- For people with multiple placement (rehire), the extsn date specified during
-- a particular term should only be considered for evaluating the extsn date
--
AND (person_id,period_of_placement_date_start) =
( SELECT asgn.person_id , asgn.period_of_placement_date_start
FROM per_all_assignments_f asgn
WHERE asgn.assignment_id = p_assignment_id
AND rownum = 1)
GROUP BY asgn.person_id
--
-- The cursor should only return a record is the extension date in asg event
-- is not equal to the min date in the asg table
-- Bug 4533293 - changed subquery to use NVLs so that cursor will return a
-- record in the null case i.e. no previous extension existed
-- or previous extension removed
--
HAVING NVL(min(asgn.projected_assignment_end), g_end_of_time) <>
(SELECT NVL(MIN(asg.pow_extn_strt_dt) - 1, g_end_of_time)
FROM hri_mb_asgn_events_ct asg
WHERE asg.assignment_id = p_assignment_id);
SELECT assignment_id,
least(min(asgn.effective_change_date),l_extnsn_strt_dt) change_date
FROM hri_mb_asgn_events_ct asgn
WHERE asgn.person_id = l_person_id
AND pow_extn_strt_dt is not null
AND asgn.pow_extn_strt_dt <> l_extnsn_strt_dt
AND assignment_id <> p_assignment_id
GROUP BY asgn.assignment_id;
Update_Asgn_Evnt_Fct_Evnt_Q
(p_assignment_id => l_asg.assignment_id
,p_change_date => l_asg.change_date
,p_start_date => l_asg.change_date
);
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'nls_date_format';
hri_bpl_conc_log.delete_process_log(c_object_name);
SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT event_group_id
FROM pay_event_groups
WHERE event_group_name = p_grp;
||', update_type: '||p_master_events_table(i).update_type
||', surrogate_key: '||TO_CHAR(p_master_events_table(i).surrogate_key)
||', column_name: '||TO_CHAR(p_master_events_table(i).column_name)
||', Effective_date: '||p_master_events_table(i).effective_date
||', old_value: '||p_master_events_table(i).old_value
||', new_value: '||p_master_events_table(i).new_value
||', change_values: '||p_master_events_table(i).change_values
||', proration_type: '||p_master_events_table(i).proration_type
||', change_mode: '||p_master_events_table(i).change_mode
||', element_entry_id: '||p_master_events_table(i).element_entry_id
||', next_ee: '||p_master_events_table(i).next_ee
);
PROCEDURE Update_archive_record
(p_assignment_id IN NUMBER
,p_change_date IN DATE -- The effective change date
,p_event_queue_table IN VARCHAR2 -- The table name of the event queue we
-- have identified the event for.
,p_action_taken IN VARCHAR2 -- The action taken with the event we
-- have identified.
,p_capture_from_date IN DATE -- The date that the event queue was
-- was using as a start date when the
-- event was found.
)
IS
--
BEGIN
--
INSERT INTO hri_archive_events
(
assignment_id
,event_queue_table
,action_taken
,erlst_evnt_effective_date
,capture_from_date
)
VALUES
(
p_assignment_id
,p_event_queue_table
,p_action_taken
,p_change_date
,p_capture_from_date
);
END Update_archive_record;
PROCEDURE Update_Sprvsr_Hstry_Evnt_Q
(p_assignment_id IN NUMBER
,p_change_date IN DATE -- The effective change date
,p_start_date IN DATE -- The date the events were captured from
)
IS
--
-- Select the erlst_evnt_processed_date from the event queue
-- for the assignment_id if it exists, so that we can decide
-- whether we need to:
--
-- + Insert if there is no record for the assignment in the queue.
-- + Update the queue if p_change_date is earlier than
-- erlst_evnt_processed_date.
-- + Do nothing.
--
CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
SELECT erlst_evnt_effective_date
FROM hri_eq_sprvsr_hstry_chgs
WHERE assignment_id = cp_assignment_id;
dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
INSERT INTO hri_eq_sprvsr_hstry_chgs
(
assignment_id
,erlst_evnt_effective_date
)
VALUES
(
p_assignment_id
,p_change_date
);
l_action_taken := 'INSERTED';
dbg('Record is earlier than one in queue currently for '||p_assignment_id||', so UPDATE.');
UPDATE hri_eq_sprvsr_hstry_chgs
SET erlst_evnt_effective_date = p_change_date
WHERE assignment_id = p_assignment_id;
l_action_taken := 'UPDATED';
Update_archive_record
(
p_assignment_id => p_assignment_id
,p_change_date => p_change_date
,p_event_queue_table => 'HRI_EQ_SPRVSR_HSTRY_CHGS'
,p_action_taken => l_action_taken
,p_capture_from_date => p_start_date
);
END Update_Sprvsr_Hstry_Evnt_Q;
PROCEDURE Update_Sprvsr_Hrchy_Evnt_Q
(p_assignment_id IN NUMBER
,p_change_date IN DATE -- The effective change date
,p_start_date IN DATE -- The date the events were captured from
)
IS
--
-- Select the erlst_evnt_processed_date from the event queue
-- for the assignment_id if it exists, so that we can decide
-- whether we need to:
--
-- + Insert if there is no record for the assignment in the queue.
-- + Update the queue if p_change_date is earlier than
-- erlst_evnt_processed_date.
-- + Do nothing.
--
CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
SELECT erlst_evnt_effective_date
FROM hri_eq_sprvsr_hrchy_chgs
WHERE assignment_id = cp_assignment_id;
dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
INSERT INTO hri_eq_sprvsr_hrchy_chgs
(
assignment_id
,erlst_evnt_effective_date
)
VALUES
(
p_assignment_id
,p_change_date
);
l_action_taken := 'INSERTED';
||p_assignment_id||', so UPDATE.');
UPDATE hri_eq_sprvsr_hrchy_chgs
SET erlst_evnt_effective_date = p_change_date
WHERE assignment_id = p_assignment_id;
l_action_taken := 'UPDATED';
Update_archive_record
(
p_assignment_id => p_assignment_id
,p_change_date => p_change_date
,p_event_queue_table => 'HRI_EQ_SPRVSR_HRCHY_CHGS'
,p_action_taken => l_action_taken
,p_capture_from_date => p_start_date
);
END Update_Sprvsr_Hrchy_Evnt_Q;
Update_Sprvsr_Hrchy_Evnt_Q
(
p_assignment_id => p_assignment_id
,p_change_date => l_min_date
,p_start_date => p_start_date
);
Update_Sprvsr_Hstry_Evnt_Q
(
p_assignment_id => p_assignment_id
,p_change_date => l_min_date
,p_start_date => p_start_date
);
PROCEDURE Update_Asgn_Evnt_Fct_Evnt_Q
(p_assignment_id IN NUMBER
,p_change_date IN DATE -- The effective change date
,p_start_date IN DATE -- The date the events were captured from
)
IS
--
-- Select the erlst_evnt_processed_date from the event queue
-- for the assignment_id if it exists, so that we can decide
-- whether we need to:
--
-- + Insert if there is no record for the assignment in the queue.
-- + Update the queue if p_change_date is earlier than
-- erlst_evnt_processed_date.
-- + Do nothing.
--
CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
SELECT erlst_evnt_effective_date
FROM hri_eq_asgn_evnts
WHERE assignment_id = cp_assignment_id;
dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
INSERT INTO hri_eq_asgn_evnts
(
assignment_id
,erlst_evnt_effective_date
)
VALUES
(
p_assignment_id
,p_change_date
);
l_action_taken := 'INSERTED';
dbg('Record is earlier than one in queue currently for '||p_assignment_id||', so UPDATE.');
UPDATE hri_eq_asgn_evnts
SET erlst_evnt_effective_date = p_change_date
WHERE assignment_id = p_assignment_id;
l_action_taken := 'UPDATED';
Update_archive_record
(
p_assignment_id => p_assignment_id
,p_change_date => p_change_date
,p_event_queue_table => 'HRI_EQ_ASGN_EVNTS'
,p_action_taken => l_action_taken
,p_capture_from_date => p_start_date
);
END Update_Asgn_Evnt_Fct_Evnt_Q;
PROCEDURE Update_Absence_Dim_Evnt_Q
(p_assignment_id IN NUMBER
,p_start_date IN DATE -- The date the events were captured from
,p_sub_evt_grp_tbl IN pay_interpreter_pkg.t_detailed_output_table_type
-- The events found in the sub event group
-- that need to be processed to identify
-- which absence_attendance_ids have had
-- events.
)
IS
--
-- Identify if an event is already queued for the absence_attendance_id
--
CURSOR c_get_queued_event(cp_absence_attendance_id IN NUMBER) IS
SELECT 'X' h_dummy
FROM hri_eq_utl_absnc_dim
WHERE absence_attendance_id = cp_absence_attendance_id;
dbg('In procedure Update_Absence_Dim_Evnt_Q ...');
dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
INSERT INTO hri_eq_utl_absnc_dim
(
absence_attendance_id
)
VALUES
(
p_sub_evt_grp_tbl(i).surrogate_key
);
l_action_taken := 'INSERTED';
Update_archive_record
(
p_assignment_id => p_sub_evt_grp_tbl(i).surrogate_key
,p_change_date => NULL
,p_event_queue_table => 'HRI_EQ_UTL_ABSNC_DIM '
,p_action_taken => l_action_taken
,p_capture_from_date => p_start_date
);
END Update_Absence_Dim_Evnt_Q;
Update_Asgn_Evnt_Fct_Evnt_Q
(
p_assignment_id => p_assignment_id
,p_change_date => l_min_date
,p_start_date => p_start_date
);
dbg('Calling Update_Absence_Dim_Evnt_Q ....');
Update_Absence_Dim_Evnt_Q
(
p_assignment_id => p_assignment_id
,p_start_date => p_start_date
,p_sub_evt_grp_tbl => l_sub_evt_grp_tbl
);
SELECT DISTINCT assignment_id
FROM per_all_assignments_f
WHERE assignment_type = 'E'
AND business_group_id = NVL(p_business_group_id, business_group_id)
AND (effective_start_date >= p_collect_from
OR effective_end_date >= p_collect_from);
UPDATE hri_adm_mthd_actions
SET full_refresh_flag = g_full_refresh,
collect_from_date = g_capture_from_date,
attribute1 = g_master_event_group_id,
attribute2 = g_assgnmnt_evnt_event_grp_id,
attribute3 = g_sprvsr_change_event_grp_id,
attribute4 = g_col_asg_events_eq,
attribute5 = g_col_sup_hrchy_eq,
attribute6 = g_col_sup_hstry_eq,
attribute7 = g_enable_archive_flag,
attribute8 = g_min_suph_date,
attribute9 = g_prd_of_srvc_table_id,
attribute10 = g_appraisal_table_id,
attribute11 = g_perf_review_table_id,
attribute12 = g_asg_table_id,
attribute13 = g_person_type_table_id -- Dated table id of PER_PERSON_TYPE_USAGES_F
WHERE mthd_action_id = p_mthd_action_id;
'SELECT /*+ parallel(ppe , default, default) */ DISTINCT
ppe.assignment_id object_id
FROM pay_process_events ppe
WHERE ppe.creation_date
BETWEEN to_date('''
|| to_char(g_capture_from_date, 'DD-MON-YYYY HH24:MI:SS')
|| ''',''DD-MON-YYYY HH24:MI:SS'')
AND to_date('''
|| to_char(g_end_of_time, 'DD-MON-YYYY HH24:MI:SS')
|| ''',''DD-MON-YYYY HH24:MI:SS'')
AND EXISTS (SELECT distinct event_update_id
FROM pay_datetracked_events pde,
pay_event_updates peu
WHERE pde.event_group_id = '||g_master_event_group_id||'
AND pde.dated_table_id = peu.dated_table_id
AND ppe.event_update_id = peu.event_update_id )
ORDER BY ppe.assignment_id';
SELECT DISTINCT ppe.assignment_id
FROM pay_process_events ppe
WHERE assignment_id between p_start_object_id and p_end_object_id
AND ppe.creation_date BETWEEN g_capture_from_date and g_end_of_time
AND EXISTS (SELECT distinct event_update_id
FROM pay_datetracked_events pde,
pay_event_updates peu
WHERE pde.event_group_id = g_master_event_group_id
AND pde.dated_table_id = peu.dated_table_id
AND ppe.event_update_id = peu.event_update_id );