[Home] [Help]
79: Ed Jones 07-Jul-2003 115.14 Remove 'show errors' for gscc
80: Andy Logue 23-DEC-2003 115.15 3329824 Performance fix
81: Andy Logue 05-JAN-2004 115.16 Performance fix
82: N Bristow 26-JAN-2004 115.17 get_assignments_affected changed to drive
83: off pay_process_events and to only use
84: salary entries.
85: N Bristow 10-MAR-2004 115.18 Performance changes, the PL/SQL
86: tables were being over
87: referenced. Change these tables
157: ppe.surrogate_key,
158: peu.dated_table_id,
159: MIN(ppe.effective_date) effective_start_date,
160: MAX(ppe.effective_date) effective_end_date
161: FROM pay_process_events ppe,
162: pay_event_updates peu
163: WHERE ppe.creation_date BETWEEN p_st AND p_en
164: AND ppe.event_update_id = peu.event_update_id
165: GROUP BY ppe.assignment_id,ppe.surrogate_key, peu.dated_table_id
172: peu.dated_table_id,
173: ppe.surrogate_key,
174: MIN(ppe.effective_date) start_date,
175: MAX(ppe.effective_date) end_date
176: FROM pay_process_events ppe,
177: pay_event_updates peu
178: WHERE ppe.creation_date BETWEEN p_st AND p_en
179: AND ppe.event_update_id = peu.event_update_id
180: AND substr(peu.event_type,1,1) in ('D','I','Z')
194: peu.dated_table_id,
195: ppe.surrogate_key,
196: MIN(ppe.effective_date) start_date,
197: MAX(ppe.effective_date) end_date
198: FROM pay_process_events ppe,
199: pay_event_updates peu
200: WHERE ppe.creation_date BETWEEN p_st AND p_en
201: AND ppe.event_update_id = peu.event_update_id
202: AND substr(peu.event_type,1,1) IN ('U','C')
212: AND pde.update_type = 'C'
213: )
214: GROUP BY ppe.assignment_id,peu.dated_table_id,ppe.surrogate_key;
215: --
216: -- Decode the description column of pay_process_events to obtain the
217: -- before and after values, just for the supervisor ID column on
218: -- per_all_assignments_f, and only if that column is one of the ones
219: -- we're tracking via our event group. Group by supervisor ID and
220: -- optionally location ID and return the earliest and latest effective
232: MIN(sic.effective_date) effective_start_date,
233: MAX(sic.effective_date) effective_end_date
234: FROM (
235: -- Get the 'before' information, i.e. the ID before the '->' character sequence
236: SELECT /*+ ordered index(ppe pay_process_events_n3) */
237: DECODE(SUBSTR(ppe.description,1,INSTR(ppe.description,' -> ')-1),'
238: ppe.effective_date,
239: ppe.assignment_id,
240: peu.dated_table_id,
238: ppe.effective_date,
239: ppe.assignment_id,
240: peu.dated_table_id,
241: peu.column_name
242: FROM pay_process_events ppe,
243: pay_event_updates peu,
244: pay_dated_tables pdt,
245: pay_datetracked_events pde
246: WHERE INSTR(ppe.description,' -> ') > 0
255: AND cp_evt = pde.event_group_id
256: AND pde.dated_table_id = peu.dated_table_id
257: UNION
258: -- Add the 'after' information, i.e. the ID after the '->' character sequence, don't UNION ALL 'cos that would give us duplicates
259: SELECT /*+ ordered index(ppe pay_process_events_n3) */
260: DECODE(SUBSTR(ppe.description,INSTR(ppe.description,' -> ')+4),'
261: ppe.effective_date,
262: ppe.assignment_id,
263: peu.dated_table_id,
261: ppe.effective_date,
262: ppe.assignment_id,
263: peu.dated_table_id,
264: peu.column_name
265: FROM pay_process_events ppe,
266: pay_event_updates peu,
267: pay_dated_tables pdt,
268: pay_datetracked_events pde
269: WHERE INSTR(ppe.description,' -> ') > 0
1025: l_qry := l_qry ||') '||
1026: 'AND n.assignment_id IN ('||
1027: 'SELECT '||
1028: ' ppe.assignment_id '||
1029: ' FROM pay_process_events ppe,pay_event_updates peu '||
1030: ' WHERE ppe.creation_date BETWEEN :1 AND :2 '||
1031: ' AND peu.event_update_id = ppe.event_update_id '||
1032: ' AND peu.dated_table_id = '||p_dtid||
1033: ') '||