[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
model updates, this is to detect
and record old and new supervisors
in the list of refresh records
Ed Jones 27-Mar-2003 115.9 2870801 Changes to support date track
updates to supervisor as well as
corrections.
Ed Jones 10-Apr-2003 115.10 Changes to pick up correct start date
for updated supervisor correctly
Ed Jones 02-Jun-2003 115.11 2984406 Moved pay_interpreter_pkg.event_group_tables
call and reset of g_DATED_TABLE_EXTRAS
cache to a separate procedure (from
get_event_details) so that it's only
called once per run.
Don't pass around event group name
parameter, use the global ID populated in
the one-off init_event_group_cache
procedure.
Major changes to the way in which affected
assignments are detected, see specific
sections for details (search for this bug).
Ed Jones 07-Jul-2003 115.13 Moved various cursors to be visible at package
level for ease of access by diagnostics
routines.
Made dt update SQL building function accessible
for this reason too.
Change csr_inserts_deletes cursor to decode
various event types to match update types.
Corrections cursor looks for C type updates
as well as U (database updates may be stored
in the incident register as corrections)
3033981 Changed incident register accessing cursors
to get surrogate key and pass that on to
the event interpreter, if the table in use
is element entries.
Ed Jones 07-Jul-2003 115.14 Remove 'show errors' for gscc
Andy Logue 23-DEC-2003 115.15 3329824 Performance fix
Andy Logue 05-JAN-2004 115.16 Performance fix
N Bristow 26-JAN-2004 115.17 get_assignments_affected changed to drive
off pay_process_events and to only use
salary entries.
N Bristow 10-MAR-2004 115.18 Performance changes, the PL/SQL
tables were being over
referenced. Change these tables
to use a hash cache.
Andy Logue 13-FEB-2006 115.19 Schema clone for all_tab_columns.
nvankadh 28-NOV-2012 115.20 15882261 Modified few cursor definitions to refer to
dba_tab_columns instead of all_tab_columns
nvankadh 13-DEC-2012 115.21 15983018 Modified due to regression because of
the changes made for 15882261
===============================================================================
*/
--
-- < PRIVATE TYPES > -----------------------------------------------------
TYPE t_dated_table_extras_rec IS RECORD(
has_supervisor_id VARCHAR2(1),
has_location_id VARCHAR2(1),
has_assignment_id VARCHAR2(1),
sql_statement VARCHAR2(32767)
);
SELECT
ppe.assignment_id,
ppe.surrogate_key,
peu.dated_table_id,
MIN(ppe.effective_date) effective_start_date,
MAX(ppe.effective_date) effective_end_date
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.creation_date BETWEEN p_st AND p_en
AND ppe.event_update_id = peu.event_update_id
GROUP BY ppe.assignment_id,ppe.surrogate_key, peu.dated_table_id
ORDER BY ppe.assignment_id, ppe.surrogate_key;
CURSOR csr_inserts_deletes(p_evt IN NUMBER,p_st IN DATE,p_en IN DATE) RETURN csr_return IS
SELECT
ppe.assignment_id,
peu.dated_table_id,
ppe.surrogate_key,
MIN(ppe.effective_date) start_date,
MAX(ppe.effective_date) end_date
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.creation_date BETWEEN p_st AND p_en
AND ppe.event_update_id = peu.event_update_id
AND substr(peu.event_type,1,1) in ('D','I','Z')
AND EXISTS (
SELECT 'X'
FROM pay_datetracked_events pde
WHERE pde.event_group_id = p_evt
AND pde.dated_table_id = peu.dated_table_id
AND pde.update_type = SUBSTR(DECODE(peu.event_type,'ZAP','D',peu.event_type),1,1)
)
GROUP BY ppe.assignment_id,peu.dated_table_id,ppe.surrogate_key;
SELECT
ppe.assignment_id,
peu.dated_table_id,
ppe.surrogate_key,
MIN(ppe.effective_date) start_date,
MAX(ppe.effective_date) end_date
FROM pay_process_events ppe,
pay_event_updates peu
WHERE ppe.creation_date BETWEEN p_st AND p_en
AND ppe.event_update_id = peu.event_update_id
AND substr(peu.event_type,1,1) IN ('U','C')
AND EXISTS (
SELECT 'X'
FROM pay_datetracked_events pde,
pay_dated_tables pdt
WHERE pde.event_group_id = p_evt
AND pde.dated_table_id = peu.dated_table_id
AND pdt.dated_table_id = pde.dated_table_id
AND pde.column_name = peu.column_name
AND NOT (pdt.table_name = 'PER_ALL_ASSIGNMENTS_F' AND pde.column_name = 'SUPERVISOR_ID')
AND pde.update_type = 'C'
)
GROUP BY ppe.assignment_id,peu.dated_table_id,ppe.surrogate_key;
SELECT TO_NUMBER(DECODE(sic.column_name,'SUPERVISOR_ID',sic.id,NULL)) supervisor_id,
DECODE(cp_str,'Y',paaf.location_id,c_BLANK_LOCATION_ID) location_id,
NULL dummy,
MIN(sic.effective_date) effective_start_date,
MAX(sic.effective_date) effective_end_date
FROM (
-- Get the 'before' information, i.e. the ID before the '->' character sequence
SELECT /*+ ordered index(ppe pay_process_events_n3) */
DECODE(SUBSTR(ppe.description,1,INSTR(ppe.description,' -> ')-1),'',NULL,SUBSTR(ppe.description,1,INSTR(ppe.description,' -> ')-1)) id,
ppe.effective_date,
ppe.assignment_id,
peu.dated_table_id,
peu.column_name
FROM pay_process_events ppe,
pay_event_updates peu,
pay_dated_tables pdt,
pay_datetracked_events pde
WHERE INSTR(ppe.description,' -> ') > 0
AND SUBSTR(ppe.description,1,6) <> ''
AND peu.event_update_id = ppe.event_update_id
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.table_name = 'PER_ALL_ASSIGNMENTS_F'
AND peu.column_name = 'SUPERVISOR_ID'
AND pde.update_type = 'C'
AND pde.column_name = peu.column_name
AND ppe.creation_date BETWEEN cp_st AND cp_en
AND cp_evt = pde.event_group_id
AND pde.dated_table_id = peu.dated_table_id
UNION
-- Add the 'after' information, i.e. the ID after the '->' character sequence, don't UNION ALL 'cos that would give us duplicates
SELECT /*+ ordered index(ppe pay_process_events_n3) */
DECODE(SUBSTR(ppe.description,INSTR(ppe.description,' -> ')+4),'',NULL,SUBSTR(ppe.description,INSTR(ppe.description,' -> ')+4)) id,
ppe.effective_date,
ppe.assignment_id,
peu.dated_table_id,
peu.column_name
FROM pay_process_events ppe,
pay_event_updates peu,
pay_dated_tables pdt,
pay_datetracked_events pde
WHERE INSTR(ppe.description,' -> ') > 0
AND SUBSTR(ppe.description,length(ppe.description)-5) <> ''
AND peu.event_update_id = ppe.event_update_id
AND peu.dated_table_id = pdt.dated_table_id
AND pdt.table_name = 'PER_ALL_ASSIGNMENTS_F'
AND peu.column_name = 'SUPERVISOR_ID'
AND pde.update_type = 'C'
AND pde.column_name = peu.column_name
AND ppe.creation_date BETWEEN cp_st AND cp_en
AND cp_evt = pde.event_group_id
AND pde.dated_table_id = pdt.dated_table_id
) sic,
per_all_assignments_f paaf
-- Join to the assignment at the effective date of the change to get the location
WHERE sic.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_id = sic.assignment_id
GROUP BY
TO_NUMBER(DECODE(sic.column_name,'SUPERVISOR_ID',sic.id,NULL)),
DECODE(cp_str,'Y',paaf.location_id,c_BLANK_LOCATION_ID);
SELECT DISTINCT pdt.dated_table_id,NULL,pdt.table_name,NULL,NULL
FROM pay_dated_tables pdt,
pay_datetracked_events pde
WHERE pde.dated_table_id = pdt.dated_table_id
AND pde.event_group_id = p_evt
AND pde.update_type = 'U';
SELECT business_group_id
FROM per_assignments_f
WHERE assignment_id = cp_ass_id;
SELECT 'Y'
FROM dba_tab_columns tab, user_synonyms syn
WHERE tab.table_name = syn.table_name
AND tab.owner = syn.table_owner
AND syn.synonym_name = cp_name
AND tab.column_name = cp_column
AND tab.owner = cp_owner;
PROCEDURE get_additional_select(
p_tab_id IN NUMBER,
p_want_location IN BOOLEAN,
p_sql IN OUT NOCOPY VARCHAR2
) IS
--
l_tab_info pay_interpreter_pkg.t_distinct_table_rec;
p_sql := 'SELECT ';
END get_additional_select;
SELECT event_group_id
FROM pay_event_groups
WHERE event_group_name = p_grp;
get_additional_select(
p_detailed_output(i).dated_table_id,
p_location_stripe,
l_sql
);
FUNCTION build_csr_dt_updates(p_dtid IN NUMBER,p_dtname IN VARCHAR2,p_eeid IN NUMBER) RETURN VARCHAR2 IS
--
-- Get a list of the columns that are in the event group and table
CURSOR get_columns(p_evt IN NUMBER,p_tab IN NUMBER) IS
SELECT column_name
FROM pay_datetracked_events pde
WHERE event_group_id = p_evt
AND dated_table_id = p_tab
AND update_type = 'U';
l_qry := 'SELECT n.assignment_id, ';
'SELECT '||
' ppe.assignment_id '||
' FROM pay_process_events ppe,pay_event_updates peu '||
' WHERE ppe.creation_date BETWEEN :1 AND :2 '||
' AND peu.event_update_id = ppe.event_update_id '||
' AND peu.dated_table_id = '||p_dtid||
') '||
'GROUP BY n.assignment_id';
END build_csr_dt_updates;
SELECT dated_table_id
INTO l_element_entries_dt_id
FROM pay_dated_tables
WHERE table_name = 'PAY_ELEMENT_ENTRIES_F';
select /*+ ordered */ distinct pee.element_entry_id
into l_dummy
from pay_element_entries_f pee,
per_all_assignments_f paf,
per_pay_bases ppb,
pay_element_entry_values_f peev
where pee.element_entry_id = p_ee_id
and pee.assignment_id = paf.assignment_id
and paf.pay_basis_id = ppb.pay_basis_id
and pee.element_entry_id = peev.element_entry_id
and ppb.input_value_id = peev.input_value_id;
-- Get those affected by inserts and deletes
msg('Getting inserts and deletes');
l_detailed_output.delete;
l_proration_dates.delete;
p_update_mode IN BOOLEAN
) IS
l_found NUMBER := -1;
p_update_mode => FALSE
);
p_summary_refresh.DELETE;
g_DATED_TABLE_EXTRAS.DELETE;