[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
161: ppe.surrogate_key,
162: peu.dated_table_id,
163: MIN(ppe.effective_date) effective_start_date,
164: MAX(ppe.effective_date) effective_end_date
165: FROM pay_process_events ppe,
166: pay_event_updates peu
167: WHERE ppe.creation_date BETWEEN p_st AND p_en
168: AND ppe.event_update_id = peu.event_update_id
169: GROUP BY ppe.assignment_id,ppe.surrogate_key, peu.dated_table_id
176: peu.dated_table_id,
177: ppe.surrogate_key,
178: MIN(ppe.effective_date) start_date,
179: MAX(ppe.effective_date) end_date
180: FROM pay_process_events ppe,
181: pay_event_updates peu
182: WHERE ppe.creation_date BETWEEN p_st AND p_en
183: AND ppe.event_update_id = peu.event_update_id
184: AND substr(peu.event_type,1,1) in ('D','I','Z')
198: peu.dated_table_id,
199: ppe.surrogate_key,
200: MIN(ppe.effective_date) start_date,
201: MAX(ppe.effective_date) end_date
202: FROM pay_process_events ppe,
203: pay_event_updates peu
204: WHERE ppe.creation_date BETWEEN p_st AND p_en
205: AND ppe.event_update_id = peu.event_update_id
206: AND substr(peu.event_type,1,1) IN ('U','C')
216: AND pde.update_type = 'C'
217: )
218: GROUP BY ppe.assignment_id,peu.dated_table_id,ppe.surrogate_key;
219: --
220: -- Decode the description column of pay_process_events to obtain the
221: -- before and after values, just for the supervisor ID column on
222: -- per_all_assignments_f, and only if that column is one of the ones
223: -- we're tracking via our event group. Group by supervisor ID and
224: -- optionally location ID and return the earliest and latest effective
236: MIN(sic.effective_date) effective_start_date,
237: MAX(sic.effective_date) effective_end_date
238: FROM (
239: -- Get the 'before' information, i.e. the ID before the '->' character sequence
240: SELECT /*+ ordered index(ppe pay_process_events_n3) */
241: DECODE(SUBSTR(ppe.description,1,INSTR(ppe.description,' -> ')-1),'
242: ppe.effective_date,
243: ppe.assignment_id,
244: peu.dated_table_id,
242: ppe.effective_date,
243: ppe.assignment_id,
244: peu.dated_table_id,
245: peu.column_name
246: FROM pay_process_events ppe,
247: pay_event_updates peu,
248: pay_dated_tables pdt,
249: pay_datetracked_events pde
250: WHERE INSTR(ppe.description,' -> ') > 0
259: AND cp_evt = pde.event_group_id
260: AND pde.dated_table_id = peu.dated_table_id
261: UNION
262: -- Add the 'after' information, i.e. the ID after the '->' character sequence, don't UNION ALL 'cos that would give us duplicates
263: SELECT /*+ ordered index(ppe pay_process_events_n3) */
264: DECODE(SUBSTR(ppe.description,INSTR(ppe.description,' -> ')+4),'
265: ppe.effective_date,
266: ppe.assignment_id,
267: peu.dated_table_id,
265: ppe.effective_date,
266: ppe.assignment_id,
267: peu.dated_table_id,
268: peu.column_name
269: FROM pay_process_events ppe,
270: pay_event_updates peu,
271: pay_dated_tables pdt,
272: pay_datetracked_events pde
273: WHERE INSTR(ppe.description,' -> ') > 0
1043: l_qry := l_qry ||') '||
1044: 'AND n.assignment_id IN ('||
1045: 'SELECT '||
1046: ' ppe.assignment_id '||
1047: ' FROM pay_process_events ppe,pay_event_updates peu '||
1048: ' WHERE ppe.creation_date BETWEEN :1 AND :2 '||
1049: ' AND peu.event_update_id = ppe.event_update_id '||
1050: ' AND peu.dated_table_id = '||p_dtid||
1051: ') '||