1 PACKAGE BODY pay_events_wrapper AS
2 /* $Header: pyevtwrp.pkb 120.1.12020000.3 2012/12/13 10:32:59 nvankadh ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2002 Oracle Corporation UK |
6 | Thames Valley Park, Reading, UK |
7 | All rights reserved. |
8 +======================================================================+
9 File Name : pyevtwrp.pkh
10
11 Description : A wrapper on top of the Payroll Events Model interpreter
12 for use with the summarisation collection programs which
13 are used to increase the performance of the Daily Business
14 Intelligence portlet queries.
15
16 Change History
17 --------------
18 Name Date Version Bug Text
19 -------------- ----------- ------- ------- ------------------------------------
20 Ed Jones 31-May-2002 115.0 Initial (Stub) version
21 Ed Jones 31-May-2002 115.1 Added dbdrv lines
22 Removed dbms_output
23 Ed Jones 31-May-2002 115.2 Corrected invalid dbdrv lines
24 Ed Jones 14-Jun-2002 115.3 Replaced stubs with proper
25 code, moved wrapper specific
26 detailed output fields from
27 interpreter to an additional
28 cache table here (dated table
29 extras global variable)
30 Ed Jones 02-Jul-2002 115.4 Added refresh period start
31 and end dates to the
32 get_refresh_periods routine
33 and pass these to the interpreter
34 rather than the effective assignment
35 dates
36 Ed Jones 02-Jul-2002 115.5 Removed owner from join to
37 all_tab_columns
38 Ed Jones 23-Jul-2002 115.6 Add debugging mode and allow
39 messages to be sent to conc.
40 manager output file
41 Ed Jones 23-Jul-2002 115.7 Added more debug information
42 Send conc. messages to log
43 Ed Jones 23-Jul-2002 115.8 Add supervisor ID column changes
44 separately to normal payroll event
45 model updates, this is to detect
46 and record old and new supervisors
47 in the list of refresh records
48 Ed Jones 27-Mar-2003 115.9 2870801 Changes to support date track
49 updates to supervisor as well as
50 corrections.
51 Ed Jones 10-Apr-2003 115.10 Changes to pick up correct start date
52 for updated supervisor correctly
53 Ed Jones 02-Jun-2003 115.11 2984406 Moved pay_interpreter_pkg.event_group_tables
54 call and reset of g_DATED_TABLE_EXTRAS
55 cache to a separate procedure (from
56 get_event_details) so that it's only
57 called once per run.
58 Don't pass around event group name
59 parameter, use the global ID populated in
60 the one-off init_event_group_cache
61 procedure.
62 Major changes to the way in which affected
63 assignments are detected, see specific
64 sections for details (search for this bug).
65 Ed Jones 07-Jul-2003 115.13 Moved various cursors to be visible at package
66 level for ease of access by diagnostics
67 routines.
68 Made dt update SQL building function accessible
69 for this reason too.
70 Change csr_inserts_deletes cursor to decode
71 various event types to match update types.
72 Corrections cursor looks for C type updates
73 as well as U (database updates may be stored
74 in the incident register as corrections)
75 3033981 Changed incident register accessing cursors
76 to get surrogate key and pass that on to
77 the event interpreter, if the table in use
78 is element entries.
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
88 to use a hash cache.
89 Andy Logue 13-FEB-2006 115.19 Schema clone for all_tab_columns.
90 nvankadh 28-NOV-2012 115.20 15882261 Modified few cursor definitions to refer to
91 dba_tab_columns instead of all_tab_columns
92 nvankadh 13-DEC-2012 115.21 15983018 Modified due to regression because of
93 the changes made for 15882261
94 ===============================================================================
95 */
96 --
97 -- < PRIVATE TYPES > -----------------------------------------------------
98 TYPE t_dated_table_extras_rec IS RECORD(
99 has_supervisor_id VARCHAR2(1),
100 has_location_id VARCHAR2(1),
101 has_assignment_id VARCHAR2(1),
102 sql_statement VARCHAR2(32767)
103 );
104 TYPE t_dated_table_extras_tab IS
105 TABLE OF t_dated_table_extras_rec
106 INDEX BY BINARY_INTEGER;
107 --
108 type t_indexing_rec is record(
109 start_ptr number
110 );
111 --
112 type t_indexing_tab is table of t_indexing_rec index by BINARY_INTEGER;
113 --
114 type t_location_chn_rec is record
115 (
116 supervisor_id number,
117 location_id number,
118 summary_ptr number,
119 next_ptr number
120 );
121 --
122 type t_location_chn_tab is table of t_location_chn_rec
123 index by BINARY_INTEGER;
124 --
125 g_supervisor_hash_tab t_indexing_tab;
126 g_location_chn_tab t_location_chn_tab;
127
128 --
129 --
130 -- < PRIVATE CONSTANTS > -------------------------------------------------
131 --
132 -- The event model processing mode and other animals
133 c_PROCESS_MODE CONSTANT VARCHAR2(30) := 'ASG_CREATION';
134 c_ASSIGNMENTS_TABLE CONSTANT VARCHAR2(30) := 'per_all_assignments_f';
135 c_OUTPUT_BUFFER CONSTANT NUMBER := 2000000;
136 c_OUTPUT_LINE_LENGTH CONSTANT NUMBER := 255;
137 --
138 -- < PRIVATE GLOBALS > ---------------------------------------------------
139 --
140 g_debugging BOOLEAN := FALSE;
141 g_concurrent BOOLEAN := FALSE;
142 --
143 -- How long did the last run take
144 g_SECONDS_ELAPSED NUMBER := 0;
145 --
146 -- Cached information about an event group
147 g_DATED_TABLE_EXTRAS t_dated_table_extras_tab;
148 g_EVENT_GROUP_ID NUMBER := NULL;
149 --
150 -- Globals for record looping
151 g_FIRST_RECORD NUMBER := 0;
152 g_LAST_RECORD NUMBER := 0;
153 g_CURRENT_RECORD NUMBER := 0;
154 --
155 -- < CURSORS > -----------------------------------------------------------
156 --
157 --
158 CURSOR csr_all_changes(p_st IN DATE,p_en IN DATE) IS
159 SELECT
160 ppe.assignment_id,
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
170 ORDER BY ppe.assignment_id, ppe.surrogate_key;
171 --
172 -- Get the inserts into and deletes from the tables we care about
173 CURSOR csr_inserts_deletes(p_evt IN NUMBER,p_st IN DATE,p_en IN DATE) RETURN csr_return IS
174 SELECT
175 ppe.assignment_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')
185 AND EXISTS (
186 SELECT 'X'
187 FROM pay_datetracked_events pde
188 WHERE pde.event_group_id = p_evt
189 AND pde.dated_table_id = peu.dated_table_id
190 AND pde.update_type = SUBSTR(DECODE(peu.event_type,'ZAP','D',peu.event_type),1,1)
191 )
192 GROUP BY ppe.assignment_id,peu.dated_table_id,ppe.surrogate_key;
193 --
194 -- Get the updates (date-track corrections) to columns we care about, excluding supervisor ID
195 CURSOR csr_dt_corrections(p_evt IN NUMBER,p_st IN DATE,p_en IN DATE) RETURN csr_return IS
196 SELECT
197 ppe.assignment_id,
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')
207 AND EXISTS (
208 SELECT 'X'
209 FROM pay_datetracked_events pde,
210 pay_dated_tables pdt
211 WHERE pde.event_group_id = p_evt
212 AND pde.dated_table_id = peu.dated_table_id
213 AND pdt.dated_table_id = pde.dated_table_id
214 AND pde.column_name = peu.column_name
215 AND NOT (pdt.table_name = 'PER_ALL_ASSIGNMENTS_F' AND pde.column_name = 'SUPERVISOR_ID')
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
225 -- dates that were affected by the change
226 -- 2984406: Changes for performance
227 CURSOR csr_supv_corrections(
228 cp_evt IN NUMBER,
229 cp_st IN DATE,
230 cp_en IN DATE,
231 cp_str IN VARCHAR2
232 ) RETURN csr_return IS
233 SELECT TO_NUMBER(DECODE(sic.column_name,'SUPERVISOR_ID',sic.id,NULL)) supervisor_id,
234 DECODE(cp_str,'Y',paaf.location_id,c_BLANK_LOCATION_ID) location_id,
235 NULL dummy,
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),'<null>',NULL,SUBSTR(ppe.description,1,INSTR(ppe.description,' -> ')-1)) 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,
252 AND peu.event_update_id = ppe.event_update_id
249 pay_datetracked_events pde
250 WHERE INSTR(ppe.description,' -> ') > 0
251 AND SUBSTR(ppe.description,1,6) <> '<null>'
253 AND peu.dated_table_id = pdt.dated_table_id
254 AND pdt.table_name = 'PER_ALL_ASSIGNMENTS_F'
255 AND peu.column_name = 'SUPERVISOR_ID'
256 AND pde.update_type = 'C'
257 AND pde.column_name = peu.column_name
258 AND ppe.creation_date BETWEEN cp_st AND cp_en
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),'<null>',NULL,SUBSTR(ppe.description,INSTR(ppe.description,' -> ')+4)) 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
274 AND SUBSTR(ppe.description,length(ppe.description)-5) <> '<null>'
275 AND peu.event_update_id = ppe.event_update_id
276 AND peu.dated_table_id = pdt.dated_table_id
277 AND pdt.table_name = 'PER_ALL_ASSIGNMENTS_F'
278 AND peu.column_name = 'SUPERVISOR_ID'
279 AND pde.update_type = 'C'
280 AND pde.column_name = peu.column_name
281 AND ppe.creation_date BETWEEN cp_st AND cp_en
282 AND cp_evt = pde.event_group_id
283 AND pde.dated_table_id = pdt.dated_table_id
284 ) sic,
285 per_all_assignments_f paaf
286 -- Join to the assignment at the effective date of the change to get the location
287 WHERE sic.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
288 AND paaf.assignment_id = sic.assignment_id
289 GROUP BY
290 TO_NUMBER(DECODE(sic.column_name,'SUPERVISOR_ID',sic.id,NULL)),
291 DECODE(cp_str,'Y',paaf.location_id,c_BLANK_LOCATION_ID);
292 --
293 -- Get a list of the tables that are in our event group
294 CURSOR csr_table_list(p_evt IN NUMBER) RETURN csr_return IS
295 SELECT DISTINCT pdt.dated_table_id,NULL,pdt.table_name,NULL,NULL
296 FROM pay_dated_tables pdt,
297 pay_datetracked_events pde
298 WHERE pde.dated_table_id = pdt.dated_table_id
299 AND pde.event_group_id = p_evt
300 AND pde.update_type = 'U';
301 --
302 procedure get_summary_idx(p_super_id in number,
303 p_location_id in number,
304 p_idx out nocopy number,
305 p_summary_refresh in out nocopy t_summary_refresh_tab_type
306 )
307 is
308 hash_key number;
309 loc_idx number;
310 sum_idx number;
311 prev_idx number;
312 l_found boolean;
313 begin
314 --
315 hash_key := (p_super_id mod 1009 ) + 1;
316 --
317 begin
318 --
319 if (g_supervisor_hash_tab.exists(hash_key)) then
320 --
321 loc_idx := g_supervisor_hash_tab(hash_key).start_ptr;
322 --
323 l_found := FALSE;
324 while (l_found <> TRUE and loc_idx is not null) loop
325 if ( g_location_chn_tab(loc_idx).supervisor_id = p_super_id
326 and g_location_chn_tab(loc_idx).location_id = p_location_id)
327 then
328 l_found := TRUE;
329 else
330 prev_idx := loc_idx;
331 loc_idx := g_location_chn_tab(loc_idx).next_ptr;
332 end if;
333 end loop;
334 --
335 /* OK if we didn't find one the create one */
336 if (l_found = FALSE) then
337 loc_idx := g_location_chn_tab.count + 1;
338 g_location_chn_tab(loc_idx).supervisor_id := p_super_id;
339 g_location_chn_tab(loc_idx).location_id := p_location_id;
340 g_location_chn_tab(loc_idx).summary_ptr := null;
341 g_location_chn_tab(loc_idx).next_ptr := null;
342 --
343 -- Set the previous pointer.
344 g_location_chn_tab(prev_idx).next_ptr := loc_idx;
345 end if;
346 --
347 else
348 --
349 loc_idx := g_location_chn_tab.count + 1;
350 g_location_chn_tab(loc_idx).supervisor_id := p_super_id;
351 g_location_chn_tab(loc_idx).location_id := p_location_id;
352 g_location_chn_tab(loc_idx).summary_ptr := null;
353 g_location_chn_tab(loc_idx).next_ptr := null;
354 --
355 g_supervisor_hash_tab(hash_key).start_ptr := loc_idx;
356 end if;
357 --
358 end;
359 --
360 /* OK we should now have a row for the location table.
361 Need to see if we have a row in the summary table
362 */
363 --
364 if (g_location_chn_tab(loc_idx).summary_ptr is null) then
365 --
366 sum_idx := p_summary_refresh.count + 1;
367 --
368 p_summary_refresh(sum_idx).supervisor_id := p_super_id;
369 p_summary_refresh(sum_idx).location_id := p_location_id;
370 --
371 g_location_chn_tab(loc_idx).summary_ptr := sum_idx;
372 --
373 end if;
374 --
375 p_idx := g_location_chn_tab(loc_idx).summary_ptr;
376 --
377 end get_summary_idx;
378 --
379 -- < PRIVATE FUNCTIONS > -------------------------------------------------
380 --
381 -- Increment a data
382 FUNCTION inc_date(p_date IN DATE) RETURN DATE IS
383 BEGIN
384 IF p_date < hr_general.end_of_time THEN
385 RETURN p_date + 1;
386 ELSE
387 RETURN p_date;
388 END IF;
389 END inc_date;
390 --
391 -- Decrement a date
392 FUNCTION dec_date(p_date IN DATE) RETURN DATE IS
393 BEGIN
394 IF p_date > hr_general.start_of_time THEN
395 RETURN p_date - 1;
396 ELSE
397 RETURN p_date;
398 END IF;
399 END dec_date;
400 --
401 -- Get the business group of an assignment
402 FUNCTION get_business_group_id(p_assignment_id IN NUMBER) RETURN NUMBER IS
403 --
404 CURSOR csr_bg(cp_ass_id IN NUMBER) IS
405 SELECT business_group_id
406 FROM per_assignments_f
407 WHERE assignment_id = cp_ass_id;
408 --
409 l_business_group_id NUMBER;
410 --
411 BEGIN
412 OPEN csr_bg(p_assignment_id);
413 FETCH csr_bg INTO l_business_group_id;
414 CLOSE csr_bg;
415 --
416 RETURN l_business_group_id;
417 END get_business_group_id;
418 --
419 -- Find the information we need from the dated table cache
420 FUNCTION query_dated_table_cache(p_dated_table_id IN NUMBER) RETURN NUMBER IS
421 --
422 l_tab_idx NUMBER;
423 --
424 BEGIN
425 --
426 -- Try to find the table the event occurred on in the cache
427 l_tab_idx := -1;
428 --
429 IF NVL(pay_interpreter_pkg.t_distinct_tab.FIRST,0) > 0 THEN
430 FOR k IN pay_interpreter_pkg.t_distinct_tab.FIRST .. pay_interpreter_pkg.t_distinct_tab.LAST LOOP
431 IF pay_interpreter_pkg.t_distinct_tab(k).table_id = p_dated_table_id THEN
432 msg('Found dated table '||p_dated_table_id||' at index '||k);
433 l_tab_idx := k;
434 EXIT;
435 END IF;
436 END LOOP;
437 ELSE
438 -- Log some debugging info and bail
439 msg('No dated table information was cached.');
440 RAISE dated_table_cache_empty;
441 END IF;
442 --
443 -- Bail if we didn't find the cached info we wanted
444 IF l_tab_idx = -1 THEN
445 msg('Dated table '||p_dated_table_id||' was not cached.');
446 RAISE dated_table_cache_miss;
447 END IF;
448 --
449 RETURN l_tab_idx;
450 END query_dated_table_cache;
451 --
452 -- Return the value of the elapsed time global populated when a full run is
453 -- completed
454 FUNCTION get_elapsed_time RETURN NUMBER IS
455 BEGIN
456 RETURN g_SECONDS_ELAPSED;
457 END get_elapsed_time;
458 --
459 -- Get the time taken to execute the last run
460 FUNCTION get_elapsed_time_text RETURN VARCHAR2 IS
461 BEGIN
462 RETURN 'Elapsed time: '||TO_CHAR(get_elapsed_time,'fm99999990.000')||' seconds';
463 END get_elapsed_time_text;
464 --
465 -- < PRIVATE PROCEDURES > ------------------------------------------------
466 --
467 -- Get the event details for a single assignment ID
468 PROCEDURE get_event_details(
469 p_start_date IN DATE,
470 p_end_date IN DATE,
471 p_assignment_id IN NUMBER,
472 p_element_entry_id IN NUMBER,
473 p_detailed_output IN OUT NOCOPY pay_interpreter_pkg.t_detailed_output_table_type,
474 p_proration_dates IN OUT NOCOPY pay_interpreter_pkg.t_proration_dates_table_type
475 ) IS
476 -- Business group ID
477 l_business_group_id NUMBER;
478 --
479 -- Temporary table variables to hold the results from the event model
480 -- procedure calls. These results aren't used
481 l_dynamic_sql pay_interpreter_pkg.t_dynamic_sql_tab;
482 l_proration_change_type pay_interpreter_pkg.t_proration_type_table_type;
483 l_proration_type pay_interpreter_pkg.t_proration_type_table_type;
484 --
485 BEGIN
486 msg('Getting event details for assignment: '||p_assignment_id);
487 --
488 -- Get the business group ID
489 l_business_group_id := get_business_group_id(p_assignment_id);
490 --
491 -- Get and parse the events that occurred. Note that we don't call
492 -- unique_sort as we do in entry_affected, since we do actually want
493 -- a list of all the events that occurred and the effective date of
494 -- each one, not just the unique dates, since we'll later merge the
495 -- events up to the supervisor level
496 pay_interpreter_pkg.event_group_tables_affected(
497 p_element_entry_id,
498 NULL,
499 g_EVENT_GROUP_ID,
500 p_assignment_id,
501 l_business_group_id,
502 p_start_date,
503 p_end_date,
504 NULL,
505 NULL,
506 c_PROCESS_MODE,
507 l_dynamic_sql,
508 p_proration_dates,
509 l_proration_change_type,
510 p_detailed_output
511 );
512 --
513 END get_event_details;
514 --
515 -- Get a flag to indicated whether or not the given table has the requested column
516 PROCEDURE get_column_flag(
517 p_table_info IN pay_interpreter_pkg.t_distinct_table_rec,
518 p_column IN VARCHAR2,
519 p_flag IN OUT NOCOPY VARCHAR2
520 ) IS
521 -- Find the column in the data-dictionary
522 -- CURSOR get_info(cp_name IN VARCHAR2,cp_column IN VARCHAR2,cp_owner IN VARCHAR2) IS
523 -- SELECT 'Y'
524 -- FROM all_tab_columns
525 -- WHERE table_name = cp_name
526 -- AND column_name = cp_column
527 -- AND owner = cp_owner;
528
529 -- Bug 15882261 : Modified cursor defination to refer to dba_tab_columns
530 -- instead of all_tab_columns
531
532 CURSOR get_info(cp_name IN VARCHAR2,cp_column IN VARCHAR2,cp_owner IN VARCHAR2) IS
533 SELECT 'Y'
534 FROM dba_tab_columns tab, user_synonyms syn
535 WHERE tab.table_name = syn.table_name
536 AND tab.owner = syn.table_owner
537 AND syn.synonym_name = cp_name
538 AND tab.column_name = cp_column
539 AND tab.owner = cp_owner;
540
541
542 --
543 l_schema VARCHAR2(30);
544 --
545 BEGIN
546 l_schema := paywsdyg_pkg.get_table_owner(UPPER(p_table_info.table_name));
547 -- If we haven't already populated this flag
548 IF NVL(p_flag,'X') = 'X' THEN
549 --
550 -- Fetch from the cursor
551 OPEN get_info(
552 UPPER(p_table_info.table_name),
553 UPPER(p_column),
554 UPPER(l_schema)
555 );
556 FETCH get_info INTO p_flag;
557 --
558 -- If nothing came back then that column's not in the table we're looking at
559 IF get_info%NOTFOUND THEN
560 p_flag := 'N';
561 END IF;
562 CLOSE get_info;
563 --
564 -- Write out some debug info
565 msg('table = '||LOWER(p_table_info.table_name)||' '||LOWER(p_column)||' ? = '||p_flag);
566 END IF;
567 END get_column_flag;
568 --
572 p_idx IN NUMBER,
569 -- Set the column flags in the dated table cache and return a copy of
570 -- the record we modified
571 PROCEDURE set_dated_table_column_flags(
573 p_rec IN OUT NOCOPY pay_interpreter_pkg.t_distinct_table_rec,
574 p_xrec IN OUT NOCOPY t_dated_table_extras_rec
575 ) IS
576 BEGIN
577 -- See if there's a supervisor ID on the dated table
578 get_column_flag(
579 pay_interpreter_pkg.t_distinct_tab(p_idx),
580 'SUPERVISOR_ID',
581 g_DATED_TABLE_EXTRAS(p_idx).has_supervisor_id
582 );
583 -- See if there's a location ID on the dated table
584 get_column_flag(
585 pay_interpreter_pkg.t_distinct_tab(p_idx),
586 'LOCATION_ID',
587 g_DATED_TABLE_EXTRAS(p_idx).has_location_id
588 );
589 --
590 -- See if we've got an assignment ID (only really need one if supervisor or location is missing)
591 get_column_flag(
592 pay_interpreter_pkg.t_distinct_tab(p_idx),
593 'ASSIGNMENT_ID',
594 g_DATED_TABLE_EXTRAS(p_idx).has_assignment_id
595 );
596 --
597 -- Copy the records we updated to the return parameter
598 p_rec := pay_interpreter_pkg.t_distinct_tab(p_idx);
599 p_xrec := g_DATED_TABLE_EXTRAS(p_idx);
600 --
601 END set_dated_table_column_flags;
602 --
603 -- Build the SQL statement we'll need to use to get the supervisor and location IDs
604 -- Note that all statements must have the surrogate key ID and the effective
605 -- date bind variables, event if there're not used, so we can dynamically open the
606 -- cursor in a consistent way
607 -- This statement is cached in the t_distinct_tab record
608 PROCEDURE get_additional_select(
609 p_tab_id IN NUMBER,
610 p_want_location IN BOOLEAN,
611 p_sql IN OUT NOCOPY VARCHAR2
612 ) IS
613 --
614 l_tab_info pay_interpreter_pkg.t_distinct_table_rec;
615 l_tab_extra t_dated_table_extras_rec;
616 l_used_skt BOOLEAN := FALSE;
617 l_used_paf BOOLEAN := FALSE;
618 l_tab_idx NUMBER;
619 --
620 BEGIN
621 --
622 -- Find the dated table information in the cache which was
623 -- populated when we called event_group_tables in get_assignment_event_details
624 l_tab_idx := query_dated_table_cache(p_tab_id);
625 --
626 -- Check the cached info in the dated table record to see if we've already built
627 -- the SQL statement for this dated table
628 IF g_DATED_TABLE_EXTRAS(l_tab_idx).sql_statement IS NOT NULL THEN
629 msg('Reusing SQL statement from dated table cache');
630 p_sql := g_DATED_TABLE_EXTRAS(l_tab_idx).sql_statement;
631 RETURN;
632 END IF;
633 --
634 -- Set the flags indicating which columns we've got on this table and put
635 -- a copy of the cached information into l_tab_info
636 set_dated_table_column_flags(
637 l_tab_idx,
638 l_tab_info,
639 l_tab_extra
640 );
641 --
642 -- Build the SQL depending on what columns we've got
643 msg('Building SQL statement...');
644 --
645 -- Add the select list
646 p_sql := 'SELECT ';
647 --
648 -- Get the supervisor ID
649 IF l_tab_extra.has_supervisor_id = 'Y' THEN
650 -- We've got a supervisor ID in this table
651 p_sql := p_sql||'skt.supervisor_id, ';
652 msg('Got supervisor_id locally');
653 l_used_skt := TRUE;
654 ELSIF l_tab_extra.has_assignment_id = 'Y' THEN
655 -- Find it from the assignment
656 p_sql := p_sql||'paf.supervisor_id, ';
657 msg('Going to assignment for supervisor_id');
658 l_used_paf := TRUE;
659 ELSE
660 -- Can't get it
661 p_sql := p_sql||c_ALL_SUPERVISORS_ID||' supervisor_id, ';
662 msg('Can''t get supervisor_id');
663 END IF;
664 --
665 -- Get the location ID
666 IF p_want_location THEN
667 IF l_tab_extra.has_location_id = 'Y' THEN
668 -- We've got a location ID in this table
669 p_sql := p_sql||'skt.location_id, ';
670 msg('Got location_id locally');
671 l_used_skt := TRUE;
672 ELSIF l_tab_extra.has_assignment_id = 'Y' THEN
673 -- Find it from the assignment
674 p_sql := p_sql||'paf.location_id, ';
675 msg('Going to assignment for location_id');
676 l_used_paf := TRUE;
677 ELSE
678 -- Can't get it
679 p_sql := p_sql||c_BLANK_LOCATION_ID||' location_id, ';
680 msg('Can''t get location_id');
681 END IF;
682 ELSE
683 -- Don't want it
684 p_sql := p_sql||c_BLANK_LOCATION_ID||' location_id, ';
685 msg('Don''t want location_id');
686 END IF;
687 --
688 -- Get the effective dates
689 IF (NOT l_used_paf) AND (NOT l_used_skt) THEN
690 p_sql := p_sql||'TRUNC(SYSDATE) effective_start_date, '||
691 'TRUNC(SYSDATE) effective_end_date ';
692 msg('Adding default dates');
693 --
694 ELSE
695 p_sql := p_sql||'skt.'||l_tab_info.start_date_name||' effective_start_date, '||
696 'skt.'||l_tab_info.end_date_name||' effective_end_date ';
697 msg('Using surrogate key table dates');
698 --
699 END IF;
700 --
701 -- Add the from list
702 p_sql := p_sql||'FROM ';
703 --
704 -- Which tables did we have to go to?
705 IF (NOT l_used_paf) AND (NOT l_used_skt) THEN
706 -- Didn't look at any tables
707 p_sql := p_sql||'dual ';
708 msg('No tables used');
709 --
710 ELSE
711 -- Must always join to the table to which the surrogate key relates
712 p_sql := p_sql||l_tab_info.table_name||' skt ';
713 msg('Getting info from '||LOWER(l_tab_info.table_name));
714 --
715 -- Did we also have to go back to the assignment to get anything
716 IF l_used_paf THEN
717 p_sql := p_sql||', '||c_ASSIGNMENTS_TABLE||' paf ';
718 msg('Also getting info from '||c_ASSIGNMENTS_TABLE);
719 END IF;
720 END IF;
721 --
722 -- Add the where clause
723 p_sql := p_sql||'WHERE ';
724 --
725 -- Which tables did we have to go to?
726 IF (NOT l_used_paf) AND (NOT l_used_skt) THEN
727 -- Didn't look at any tables
728 p_sql := p_sql||':surrogate_key IS NOT NULL '||
729 'AND :effective_start_date IS NOT NULL '||
730 'AND :effective_end_date IS NOT NULL ';
731 msg('Didn''t need any where clause, adding default');
732 --
733 ELSE
734 -- Always have to join to the table to which the surrogate key relates
735 p_sql := p_sql||'skt.'||l_tab_info.surrogate_key_name||' = :surrogate_key '||
736 'AND :effective_start_date <= skt.'||l_tab_info.end_date_name||' '||
737 'AND :effective_end_date >= skt.'||l_tab_info.start_date_name||' ';
738 msg(
739 'Adding where clause for '||LOWER(l_tab_info.surrogate_key_name)||', '||
740 LOWER(l_tab_info.start_date_name)||' and '||
741 LOWER(l_tab_info.end_date_name)||' columns'
742 );
743 --
744 -- Did we also have to go back to the assignment to get anything
745 IF l_used_paf THEN
746 p_sql := p_sql||'AND paf.assignment_id = skt.assignment_id '||
747 'AND paf.effective_end_date >= skt.'||l_tab_info.start_date_name||' '||
748 'AND paf.effective_start_date <= skt.'||l_tab_info.end_date_name||' ';
749 msg('Also joining to assignments table');
750 --
751 END IF;
752 END IF;
753 --
754 -- Only include rows where the supervisor ID is set (and the location if needed)
755 IF l_tab_extra.has_supervisor_id = 'Y' THEN
756 p_sql := p_sql||'AND skt.supervisor_id IS NOT NULL ';
757 msg('Surrogate table supervisor must not be null');
758 ELSIF l_tab_extra.has_assignment_id = 'Y' THEN
759 p_sql := p_sql||'AND paf.supervisor_id IS NOT NULL ';
760 msg('Assignment table supervisor must not be null');
761 END IF;
762 --
763 IF p_want_location THEN
764 IF l_tab_extra.has_location_id = 'Y' THEN
765 p_sql := p_sql||'AND skt.location_id IS NOT NULL ';
766 msg('Surrogate table location_id must not be null');
767 ELSIF l_tab_extra.has_assignment_id = 'Y' THEN
768 p_sql := p_sql||'AND paf.location_id IS NOT NULL ';
769 msg('Assignment table location_id must not be null');
770 END IF;
771 ELSE
772 msg('Don''t care where location_id comes from, or if it''s null');
773 END IF;
774 --
775 -- Lets see the SQL statement
776 msg('Finished building statement');
777 msg('<sqlstatement>');
778 msg(p_sql);
779 msg('</sqlstatement>');
780 --
781 msg('Caching statement in record '||l_tab_idx);
782 g_DATED_TABLE_EXTRAS(l_tab_idx).sql_statement := p_sql;
783 --
784 END get_additional_select;
785 --
786 -- < PUBLIC FUNCTIONS > --------------------------------------------------
787 --
788 -- Return the "all supervisors" constant
789 FUNCTION all_supervisors_id RETURN NUMBER IS
790 BEGIN
791 RETURN c_ALL_SUPERVISORS_ID;
792 END all_supervisors_id;
793 --
794 -- Return the "blank location" constant
795 FUNCTION blank_location_id RETURN NUMBER IS
796 BEGIN
797 RETURN c_BLANK_LOCATION_ID;
798 END blank_location_id;
799 --
800 -- Get the event group ID based on its name and initialise the cache
801 PROCEDURE init_event_group_cache(p_event_group_name IN VARCHAR2) IS
802 --
803 CURSOR get_evt(p_grp IN VARCHAR2) IS
804 SELECT event_group_id
805 FROM pay_event_groups
806 WHERE event_group_name = p_grp;
807 --
808 BEGIN
809 --
810 -- Find the event group ID, raises no_data_found
811 -- if the event group name is invalid (i.e. not found)
812 -- 2984406 - Fetch into a global ID to save repeated queries to get the ID
813 OPEN get_evt(p_event_group_name);
814 FETCH get_evt INTO g_EVENT_GROUP_ID;
815 IF get_evt%NOTFOUND THEN
816 -- Trace some debug info and raise the error
817 dbg('Event group "'||p_event_group_name||'" not found.');
818 CLOSE get_evt;
819 RAISE event_group_not_found;
820 END IF;
821 CLOSE get_evt;
822 --
823 -- Populate the internal package global caches to hold details of all
824 -- the dated tables that this event group uses
825 pay_interpreter_pkg.event_group_tables(g_EVENT_GROUP_ID);
826 --
827 -- Make sure we've got enough extra information records for all the
828 -- dated tables we're going to use
829 FOR i IN pay_interpreter_pkg.t_distinct_tab.FIRST .. pay_interpreter_pkg.t_distinct_tab.LAST LOOP
830 g_DATED_TABLE_EXTRAS(i).has_supervisor_id := 'X';
831 g_DATED_TABLE_EXTRAS(i).has_location_id := 'X';
832 g_DATED_TABLE_EXTRAS(i).has_assignment_id := 'X';
833 g_DATED_TABLE_EXTRAS(i).sql_statement := NULL;
834 END LOOP;
835 --
836 END init_event_group_cache;
837 --
838 -- < PUBLIC PROCEDURES > -------------------------------------------------
839 --
840 -- Get the event group ID based on its name
841 FUNCTION get_event_group_id(p_event_group_name IN VARCHAR2) RETURN NUMBER IS
842 BEGIN
843 IF g_EVENT_GROUP_ID IS NULL THEN
844 init_event_group_cache(p_event_group_name);
845 END IF;
846 RETURN g_EVENT_GROUP_ID;
847 END get_event_group_id;
848 --
849 -- Log a message, either using fnd_file, or hr_utility.trace
850 PROCEDURE msg(p_text IN VARCHAR2) IS
851 l_pos NUMBER := 1;
855 -- Chop up the string into 250 char chunks if we're writing to the
852 l_txt VARCHAR2(255);
853 BEGIN
854 --
856 -- concurrent manager log file
857 IF g_concurrent THEN
858 LOOP
859 l_txt := SUBSTR(p_text,l_pos,c_OUTPUT_LINE_LENGTH);
860 fnd_file.put_line(fnd_file.LOG,l_txt);
861 --
862 l_pos := l_pos + c_OUTPUT_LINE_LENGTH;
863 EXIT WHEN l_pos > LENGTH(p_text);
864 END LOOP;
865 ELSE
866 -- Use the normal trace stuff
867 hr_utility.trace(p_text);
868 END IF;
869 END msg;
870 --
871 PROCEDURE dbg(p_text IN VARCHAR2) IS
872 BEGIN
873 IF g_debugging THEN
874 msg(p_text);
875 END IF;
876 END dbg;
877 --
878 -- Switch on or off client debugging.
879 PROCEDURE set_client_debugging(p_on IN BOOLEAN) IS
880 BEGIN
881 -- Stubbed out because we're not allowed to use dbms_output
882 RAISE feature_not_supported;
883 END set_client_debugging;
884 --
885 -- Replacement for the above - allow logging to concurrent manager log
886 PROCEDURE set_concurrent_logging(p_on IN BOOLEAN) IS
887 BEGIN
888 g_concurrent := p_on;
889 END set_concurrent_logging;
890 --
891 -- Switch debugging messages on
892 PROCEDURE set_debugging(p_on IN BOOLEAN) IS
893 BEGIN
894 g_debugging := p_on;
895 END set_debugging;
896 --
897 -- Process the detailed output information from an assignment event
898 PROCEDURE process_event_details(
899 p_detailed_output IN pay_interpreter_pkg.t_detailed_output_table_type,
900 p_proration_dates IN pay_interpreter_pkg.t_proration_dates_table_type,
901 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
902 p_location_stripe IN BOOLEAN DEFAULT FALSE
903 ) IS
904 --
905 -- Local variables
906 l_idx NUMBER;
907 --
908 -- The SQL statement we'll need to use to get the supervisor ID
909 -- and the dynamic cursor stuff
910 TYPE t_csr IS REF CURSOR;
911 --
912 l_csr t_csr;
913 l_sql VARCHAR2(2000);
914 l_supv NUMBER;
915 l_loct NUMBER;
916 l_sdt DATE;
917 l_edt DATE;
918 --
919 BEGIN
920 --
921 -- Make sure we have some detailed output and some dates
922 IF NVL(p_detailed_output.FIRST,0) < 1 AND
923 NVL(p_proration_dates.FIRST,0) < 1
924 THEN
925 msg('No detailed output supplied to process_event_details, ignoring');
926 RETURN;
927 END IF;
928 --
929 -- There should be the same number of records in the detailed output
930 -- and proration dates tables, if not then that's an error since we need
931 -- to have an effective date for each event
932 IF p_detailed_output.FIRST <> p_proration_dates.FIRST OR
933 p_detailed_output.LAST <> p_proration_dates.LAST
934 THEN
935 -- Trace some debug info and raise a custom error
936 msg('Records in detailed output don''t match those in proration dates.');
937 msg('t_detailed_output = '||p_detailed_output.FIRST||' -> '||p_detailed_output.LAST);
938 msg('t_proration_dates = '||p_proration_dates.FIRST||' -> '||p_proration_dates.LAST);
939 RAISE mismatch_when_summarizing;
940 END IF;
941 --
942 -- Process each record in the detailed output
943 FOR i IN p_detailed_output.FIRST .. p_detailed_output.LAST LOOP
944 --
945 -- Debugging information for event found
946 msg(
947 'Processing event found at '||
948 dec_date(p_proration_dates(i))||'/'||inc_date(p_proration_dates(i))||' on '||
949 p_detailed_output(i).dated_table_id||' ID '||
950 p_detailed_output(i).surrogate_key
951 );
952 --
953 -- Build the query to get the additional IDs based on the
954 -- information about the dated table that the event occurred on,
955 -- must always include the 3 bind variables; surrogate_key,
956 -- effective_start/end_date
957 get_additional_select(
958 p_detailed_output(i).dated_table_id,
959 p_location_stripe,
960 l_sql
961 );
962 --
963 -- Open a cursor for the SQL we just built
964 OPEN l_csr FOR l_sql USING
965 p_detailed_output(i).surrogate_key,
966 dec_date(p_proration_dates(i)),
967 inc_date(p_proration_dates(i));
968 LOOP
969 -- Get the IDs and bail when we run out
970 FETCH l_csr INTO l_supv,l_loct,l_sdt,l_edt;
971 EXIT WHEN l_csr%NOTFOUND;
972 --
973 -- Find the entry in the summary table
974 --
975 get_summary_idx(l_supv, l_loct, l_idx, p_summary_refresh);
976 --
977 -- The start date is the earliest out of the currently recorded effective date
978 -- for this combination (NVL'd in case we haven't recorded anything yet) and the
982 p_summary_refresh(l_idx).effective_start_date,
979 -- effective date of the event we're recording
980 p_summary_refresh(l_idx).effective_start_date := LEAST(
981 NVL(
983 p_proration_dates(i)
984 ),
985 dec_date(l_sdt)
986 );
987 --
988 -- Update the end date similarly, but with the most recent of the two dates
989 p_summary_refresh(l_idx).effective_end_date := GREATEST(
990 NVL(
991 p_summary_refresh(l_idx).effective_end_date,
992 p_proration_dates(i)
993 ),
994 inc_date(l_edt)
995 );
996 --
997 END LOOP;
998 --
999 -- Done with the cursor;
1000 CLOSE l_csr;
1001 END LOOP;
1002 --
1003 END process_event_details;
1004 --
1005 -- Build up the SQL query for determining date-effective updates
1006 FUNCTION build_csr_dt_updates(p_dtid IN NUMBER,p_dtname IN VARCHAR2,p_eeid IN NUMBER) RETURN VARCHAR2 IS
1007 --
1008 -- Get a list of the columns that are in the event group and table
1009 CURSOR get_columns(p_evt IN NUMBER,p_tab IN NUMBER) IS
1010 SELECT column_name
1011 FROM pay_datetracked_events pde
1012 WHERE event_group_id = p_evt
1013 AND dated_table_id = p_tab
1014 AND update_type = 'U';
1015 --
1016 l_qry VARCHAR2(32767);
1017 BEGIN
1018 l_qry := 'SELECT n.assignment_id, ';
1019 --
1020 IF p_dtid = p_eeid THEN
1021 l_qry := l_qry||'n.element_entry_id, ';
1022 END IF;
1023 --
1024 l_qry := l_qry||
1025 ' MIN(LEAST(o.effective_start_date,n.effective_start_date)) effective_start_date, '||
1026 ' MAX(GREATEST(o.effective_start_date,n.effective_start_date)) effective_end_date '||
1027 'FROM '||p_dtname||' n, '||
1028 ' '||p_dtname||' o '||
1029 'WHERE n.assignment_id = o.assignment_id '||
1030 'AND n.effective_start_date = o.effective_end_date + 1 '||
1031 'AND (';
1032 --
1033 FOR col_rec IN get_columns(g_EVENT_GROUP_ID,p_dtid) LOOP
1034 IF get_columns%rowcount > 1 THEN
1035 l_qry := l_qry||' OR ';
1036 END IF;
1037 --
1038 l_qry := l_qry||'NVL(TO_CHAR(o.'||col_rec.column_name||'), ''$Sys_Def$'') <> '||
1039 'NVL(TO_CHAR(n.'||col_rec.column_name||'), ''$Sys_Def$'')';
1040 --
1041 END LOOP;
1042 --
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 ') '||
1052 'GROUP BY n.assignment_id';
1053 IF p_dtid = p_eeid THEN
1054 l_qry := l_qry||',n.element_entry_id';
1055 END IF;
1056 --
1057 RETURN l_qry;
1058 END build_csr_dt_updates;
1059 --
1060 FUNCTION get_element_entry_table_id RETURN NUMBER IS
1061 l_element_entries_dt_id NUMBER;
1062 BEGIN
1063 --
1064 -- Get the (special case) element entries table ID
1065 BEGIN
1066 SELECT dated_table_id
1067 INTO l_element_entries_dt_id
1068 FROM pay_dated_tables
1069 WHERE table_name = 'PAY_ELEMENT_ENTRIES_F';
1070 EXCEPTION
1071 WHEN OTHERS THEN l_element_entries_dt_id := NULL;
1072 END;
1073 RETURN l_element_entries_dt_id;
1074 END get_element_entry_table_id;
1075 --
1076 --
1077 -- Is the Entry Id supplied a Salary Element
1078 --
1079 FUNCTION is_salary(p_ee_id in number)
1080 RETURN BOOLEAN IS
1081 l_dummy number;
1082 BEGIN
1083 --
1084 select /*+ ordered */ distinct pee.element_entry_id
1085 into l_dummy
1086 from pay_element_entries_f pee,
1087 per_all_assignments_f paf,
1088 per_pay_bases ppb,
1089 pay_element_entry_values_f peev
1090 where pee.element_entry_id = p_ee_id
1091 and pee.assignment_id = paf.assignment_id
1092 and paf.pay_basis_id = ppb.pay_basis_id
1093 and pee.element_entry_id = peev.element_entry_id
1094 and ppb.input_value_id = peev.input_value_id;
1095 --
1096 return TRUE;
1097 --
1098 EXCEPTION
1099 when no_data_found then
1100 return FALSE;
1101 --
1102 END is_salary;
1103 --
1104 -- Get a list of the assignments that have events recorded for them.
1105 -- Bug 2984406: Restructure to fetch affected assignments in three stages,
1106 -- basically changes the whole structure of this procedure
1107 PROCEDURE get_assignments_affected(
1108 p_start_date IN DATE,
1109 p_end_date IN DATE,
1110 p_assignments IN OUT NOCOPY t_assignment_id_tab_type
1111 ) IS
1112 --
1113 l_csr csr_dyn_ref;
1114 l_qry VARCHAR2(32767);
1115 l_assignment_id NUMBER;
1116 l_element_entry_id NUMBER;
1117 l_effective_start_date DATE;
1118 l_effective_end_date DATE;
1119 l_element_entries_dt_id NUMBER;
1120 --
1121 l_loop NUMBER;
1122 curr_ass_id NUMBER;
1123 new_assignment BOOLEAN;
1124 --
1125 BEGIN
1126 -- Get the affected assignments
1127 msg('Getting affected assignments for '||fnd_date.date_to_canonical(p_start_date)||' '||fnd_date.date_to_canonical(p_end_date));
1128 l_loop := 0;
1129 l_element_entries_dt_id := get_element_entry_table_id;
1130 --
1131 -- Get those affected by inserts and deletes
1132 msg('Getting inserts and deletes');
1133 curr_ass_id := -1;
1134 FOR assrec in csr_all_changes(p_start_date,p_end_date) loop
1135 --
1136 if(curr_ass_id <> assrec.assignment_id) then
1137 curr_ass_id := assrec.assignment_id;
1138 new_assignment := TRUE;
1139 end if;
1140 --
1141 /* If the table is element entries then we need to do some thing */
1142 if assrec.dated_table_id = l_element_entries_dt_id then
1143 --
1144 if( is_salary(assrec.surrogate_key) = TRUE) then
1145 --
1146 /* It is salary, here comes the tricky part
1147 */
1148 if (new_assignment = TRUE) then
1149 l_loop := l_loop + 1;
1150 p_assignments(l_loop).element_entry_id
1151 := assrec.surrogate_key;
1152 p_assignments(l_loop).assignment_id
1153 := assrec.assignment_id;
1154 p_assignments(l_loop).effective_start_date
1155 := dec_date(assrec.effective_start_date);
1156 p_assignments(l_loop).effective_end_date
1157 := inc_date(assrec.effective_end_date);
1158 new_assignment := FALSE;
1159 else
1160 if (p_assignments(l_loop).element_entry_id is null) then
1161 p_assignments(l_loop).element_entry_id
1162 := assrec.surrogate_key;
1163 p_assignments(l_loop).effective_start_date
1164 := least(p_assignments(l_loop).effective_start_date,
1165 dec_date(assrec.effective_start_date));
1166 p_assignments(l_loop).effective_end_date
1167 := greatest(p_assignments(l_loop).effective_end_date,
1168 inc_date(assrec.effective_end_date));
1169 else
1170 /* Yeah we really need to create a new one */
1171 l_loop := l_loop + 1;
1172 p_assignments(l_loop).element_entry_id
1173 := assrec.surrogate_key;
1174 p_assignments(l_loop).assignment_id
1175 := assrec.assignment_id;
1176 p_assignments(l_loop).effective_start_date
1177 := dec_date(assrec.effective_start_date);
1178 p_assignments(l_loop).effective_end_date
1179 := inc_date(assrec.effective_end_date);
1180 end if;
1181 end if;
1182 --
1183 else
1184 /* do nothing it's not salary, hence
1185 not interested
1186 */
1187 null;
1188 end if;
1189 --
1190 else
1191 /* It's not an element entry change.
1192 Check that a row has not already been placed
1193 in the pl/sql table for this assignment
1194 If it has just adjust the dates.
1195 */
1196 if (new_assignment = TRUE) then
1197 l_loop := l_loop + 1;
1198 p_assignments(l_loop).element_entry_id := NULL;
1199 p_assignments(l_loop).assignment_id
1200 := assrec.assignment_id;
1201 p_assignments(l_loop).effective_start_date
1202 := dec_date(assrec.effective_start_date);
1203 p_assignments(l_loop).effective_end_date
1204 := inc_date(assrec.effective_end_date);
1205 new_assignment := FALSE;
1206 else
1207 p_assignments(l_loop).effective_start_date
1208 := least(p_assignments(l_loop).effective_start_date,
1209 dec_date(assrec.effective_start_date));
1210 p_assignments(l_loop).effective_end_date
1211 := greatest(p_assignments(l_loop).effective_end_date,
1212 inc_date(assrec.effective_end_date));
1213 end if;
1214 end if;
1215 END LOOP;
1216 --
1217 IF NVL(p_assignments.FIRST,0) < 1 THEN
1218 msg('No assignment events found within specified date range');
1219 RAISE no_assignment_events_found;
1220 END IF;
1221 --
1222 END get_assignments_affected;
1223 --
1224 -- Get the payroll event details based on a list of assignment IDs
1225 PROCEDURE get_refresh_periods(
1226 p_assignments IN OUT NOCOPY t_assignment_id_tab_type,
1227 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
1228 p_start_date IN DATE,
1229 p_end_date IN DATE,
1230 p_location_stripe IN BOOLEAN DEFAULT FALSE
1231 ) IS
1232 --
1233 -- Local table-type variables for use with processing the event details
1234 l_detailed_output pay_interpreter_pkg.t_detailed_output_table_type;
1235 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
1236 --
1237 BEGIN
1238 --
1239 -- Check we've got something to process
1240 IF NVL(p_assignments.FIRST,0) < 1 THEN
1241 msg('No data from process in get_assignment_events');
1242 RAISE no_assignments_supplied;
1243 END IF;
1244 --
1245 -- Process all the assignments we got
1246 FOR i IN p_assignments.FIRST .. p_assignments.LAST LOOP
1247 --
1248 l_detailed_output.delete;
1249 l_proration_dates.delete;
1250 --
1251 -- Get the detailed event information for this assignment
1252 get_event_details(
1253 p_start_date,
1254 p_end_date,
1255 p_assignments(i).assignment_id,
1256 p_assignments(i).element_entry_id,
1257 l_detailed_output,
1258 l_proration_dates
1259 );
1260 --
1261 -- Process the event details for this assignment
1262 -- (a check is done within this procedure for the detailed output being empty)
1263 msg(
1264 'Processing event details ('||
1265 p_assignments(i).assignment_id||' '||
1266 p_assignments(i).effective_start_date||' -> '||
1267 p_assignments(i).effective_end_date||')'
1268 );
1269 process_event_details(
1270 l_detailed_output,
1271 l_proration_dates,
1272 p_summary_refresh,
1273 p_location_stripe
1274 );
1275 END LOOP;
1276 END get_refresh_periods;
1277 --
1278 -- If we recorded some information for "all records" (i.e. an event ocurred on a
1279 -- table which didn't allow us to get a proper supervisor or location ID)
1280 -- then we need to delete any specific records that fall completely within
1281 -- "refresh all" period, and chop up any records that just overlap that period, otherwise
1282 -- we'll just copy the temporary table to the output parameter
1283 PROCEDURE de_dupe_refresh_periods(
1284 p_summary_refresh_temp IN OUT NOCOPY t_summary_refresh_tab_type,
1285 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
1286 p_all_supv IN OUT NOCOPY BOOLEAN,
1287 p_out_num IN OUT NOCOPY NUMBER,
1288 p_all_start IN OUT NOCOPY DATE,
1289 p_all_end IN OUT NOCOPY DATE,
1290 p_want_location IN BOOLEAN DEFAULT FALSE
1291 ) IS
1292 BEGIN
1293 p_all_supv := FALSE;
1294 p_out_num := 0;
1295 --
1296 IF NVL(p_summary_refresh_temp.FIRST,0) > 0 THEN
1297 --
1298 -- Find the "all" record
1299 FOR i IN p_summary_refresh_temp.FIRST .. p_summary_refresh_temp.LAST LOOP
1300 IF p_summary_refresh_temp(i).supervisor_id = c_ALL_SUPERVISORS_ID OR
1301 (p_summary_refresh_temp(i).location_id = c_BLANK_LOCATION_ID AND p_want_location)
1302 THEN
1303 p_all_start := p_summary_refresh(i).effective_start_date;
1304 p_all_end := p_summary_refresh(i).effective_end_date;
1305 p_all_supv := TRUE;
1306 END IF;
1307 END LOOP;
1308 --
1309 IF p_all_supv THEN
1310 --
1311 -- If either the start or end date is null then bail
1312 IF p_all_start IS NULL OR p_all_end IS NULL THEN
1313 msg('An "all" record was missing one or other of the required dates');
1314 RAISE missing_dates_in_all_record;
1315 END IF;
1316 --
1317 -- Record the "all" record
1318 p_out_num := p_out_num + 1;
1319 p_summary_refresh(p_out_num).supervisor_id := c_ALL_SUPERVISORS_ID;
1320 p_summary_refresh(p_out_num).location_id := c_BLANK_LOCATION_ID;
1321 p_summary_refresh(p_out_num).effective_start_date := p_all_start;
1322 p_summary_refresh(p_out_num).effective_end_date := p_all_end;
1323 --
1324 -- Go through the other records (skipping the "all" one) and chopping the dates
1325 FOR i IN p_summary_refresh_temp.FIRST .. p_summary_refresh_temp.LAST LOOP
1326 IF p_summary_refresh_temp(i).supervisor_id <> c_ALL_SUPERVISORS_ID AND
1327 (p_summary_refresh_temp(i).location_id <> c_BLANK_LOCATION_ID OR (NOT p_want_location))
1328 THEN
1329 --
1330 -- If either the start or end date is null then bail
1331 IF p_summary_refresh_temp(i).effective_start_date IS NULL OR
1332 p_summary_refresh_temp(i).effective_end_date IS NULL
1333 THEN
1334 msg(
1335 'A specific ('||p_summary_refresh_temp(i).supervisor_id||
1336 '/'||p_summary_refresh_temp(i).location_id||
1337 ') refresh record is missing a start or end date'
1338 );
1339 RAISE missing_dates_for_specific;
1340 END IF;
1341 --
1342 -- If the specific refresh record falls completely within the "all"
1343 -- period the don't process it
1344 IF p_summary_refresh_temp(i).effective_start_date >= p_all_start AND
1345 p_summary_refresh_temp(i).effective_end_date <= p_all_end
1346 THEN
1347 msg(
1348 'Specific '||p_summary_refresh_temp(i).supervisor_id||
1349 '/'||p_summary_refresh_temp(i).location_id||
1350 ' falls entirely within "all" refresh period, ignoring.'
1351 );
1352 ELSE
1353 -- If this record starts before the "all" period then record a segment
1354 IF p_summary_refresh_temp(i).effective_start_date < p_all_start THEN
1355 msg(
1356 'Specific '||p_summary_refresh_temp(i).supervisor_id||
1357 '/'||p_summary_refresh_temp(i).location_id||
1358 ' starts before the "all" refresh period, processing.'
1359 );
1360 --
1361 p_out_num := p_out_num + 1;
1362 p_summary_refresh(p_out_num) := p_summary_refresh_temp(i);
1363 p_summary_refresh(p_out_num).effective_end_date := p_all_start - 1;
1364 END IF;
1365 --
1366 -- If this record end after the "all" period then record a segment
1367 IF p_summary_refresh_temp(i).effective_end_date > p_all_end THEN
1368 msg(
1369 'Specific '||p_summary_refresh_temp(i).supervisor_id||
1370 '/'||p_summary_refresh_temp(i).location_id||
1371 ' ends after the "all" refresh period, processing.'
1372 );
1373 --
1374 p_out_num := p_out_num + 1;
1375 p_summary_refresh(p_out_num) := p_summary_refresh_temp(i);
1376 p_summary_refresh(p_out_num).effective_start_date := p_all_end + 1;
1377 END IF;
1378 END IF;
1379 END IF;
1380 END LOOP;
1381 --
1382 ELSE
1383 -- No "all" period, just copy everything to the output parameter
1384 FOR i IN p_summary_refresh_temp.FIRST .. p_summary_refresh_temp.LAST LOOP
1385 --
1386 -- If either the start or end date is null then bail
1387 IF p_summary_refresh_temp(i).effective_start_date IS NULL OR
1388 p_summary_refresh_temp(i).effective_end_date IS NULL
1389 THEN
1390 msg('A specific ('||
1391 p_summary_refresh_temp(i).supervisor_id||'/'||
1392 p_summary_refresh_temp(i).location_id||
1393 ') refresh record is missing a start or end date'
1394 );
1395 RAISE missing_dates_for_specific;
1396 END IF;
1397 --
1398 p_out_num := p_out_num + 1;
1399 p_summary_refresh(p_out_num) := p_summary_refresh_temp(i);
1400 END LOOP;
1401 END IF;
1402 END IF;
1403 --
1404 END de_dupe_refresh_periods;
1405 --
1406 -- Add a record to the refresh table, as long as it's not there already
1407 PROCEDURE add_summary_refresh_record(
1408 p_idx IN OUT NOCOPY NUMBER,
1409 p_table IN OUT NOCOPY t_summary_refresh_tab_type,
1410 p_supervisor IN NUMBER,
1411 p_start_date IN DATE,
1412 p_end_date IN DATE,
1413 p_location IN NUMBER,
1414 p_update_mode IN BOOLEAN
1415 ) IS
1416 l_found NUMBER := -1;
1417 BEGIN
1418 --
1419 get_summary_idx(p_supervisor, p_location, p_idx, p_table);
1420 --
1421 p_table(p_idx).effective_start_date :=
1422 LEAST(dec_date(p_start_date),
1423 nvl(p_table(p_idx).effective_start_date,
1424 p_start_date));
1425 p_table(p_idx).effective_end_date :=
1426 GREATEST(inc_date(p_end_date),
1427 nvl(p_table(p_idx).effective_end_date,
1428 p_end_date));
1429 --
1430 END add_summary_refresh_record;
1431 --
1432 -- Add any date track corrections to supervisor ID on per_all_assignments_f
1433 -- if we've got that column in our event group
1434 PROCEDURE add_supervisor_corrections(
1435 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
1436 p_start_date IN DATE,
1437 p_end_date IN DATE,
1438 p_location_stripe IN BOOLEAN DEFAULT FALSE
1439 ) IS
1440 --
1441 l_start NUMBER := NVL(p_summary_refresh.LAST,0) + 1;
1442 l_idx NUMBER := l_start;
1443 l_end NUMBER;
1444 l_stripe VARCHAR2(1) := 'N';
1445 --
1446 BEGIN
1447 msg('Adding supervisor ID correction changes');
1448 dbg('Start index is at row '||l_idx);
1449 dbg('Parameters are (not including output table):');
1450 dbg('p_start_date => '||fnd_date.date_to_canonical(p_start_date));
1451 dbg('p_end_date => '||fnd_date.date_to_canonical(p_end_date));
1452 --
1453 dbg('g_EVENT_GROUP_ID => '||g_EVENT_GROUP_ID);
1454 --
1455 -- Switch on location striping if desired
1456 IF p_location_stripe THEN
1457 dbg('Switching on location striping');
1458 l_stripe := 'Y';
1459 END IF;
1460 dbg('p_location_stripe => '||l_stripe);
1461 --
1462 -- Get all supervisor ID changes and add them to the list of refresh periods
1463 FOR l_rec IN csr_supv_corrections(
1464 g_EVENT_GROUP_ID,
1465 p_start_date,
1466 p_end_date,
1467 l_stripe
1468 ) LOOP
1469 add_summary_refresh_record(
1470 l_idx,
1471 p_summary_refresh,
1472 l_rec.assignment_or_supervisor_id,
1473 l_rec.effective_start_date,
1474 l_rec.effective_end_date,
1475 l_rec.table_or_location_id,
1476 p_update_mode => FALSE
1477 );
1478 END LOOP;
1479 --
1483 dbg('End index is now at row '||l_end);
1480 -- Make sure we added some rows to the table, this isn't a fatal exception
1481 -- yet 'cos we could already have something in the table
1482 l_end := NVL(p_summary_refresh.LAST,0) + 1;
1484 IF l_start = l_end THEN
1485 RAISE no_supervisor_corrections;
1486 END IF;
1487 --
1488 END add_supervisor_corrections;
1489 --
1490 -- Get the list of supervisors and the date range across which
1491 -- each of those supervisors should be refreshed.
1492 PROCEDURE get_summaries_affected(
1493 p_event_group IN VARCHAR2,
1494 p_start_date IN DATE,
1495 p_end_date IN DATE,
1496 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
1497 p_location_stripe IN BOOLEAN DEFAULT FALSE,
1498 p_raise_no_data IN BOOLEAN DEFAULT FALSE
1499 ) IS
1500 --
1501 -- A list of assignments that events have ocurred for
1502 l_assignments t_assignment_id_tab_type;
1503 --
1504 -- Temporary table to store the results in, before we post-process it
1505 -- to handle "all supervisor refresh" events
1506 l_summary_refresh_temp t_summary_refresh_tab_type;
1507 --
1508 -- Parameters used to process the "all supervisor refresh" events
1509 l_all_supv BOOLEAN;
1510 l_out_num NUMBER;
1511 l_all_start DATE;
1512 l_all_end DATE;
1513 --
1514 -- Profiling (timing) variables
1515 l_start NUMBER;
1516 l_curr NUMBER;
1517 --
1518 BEGIN
1519 --
1520 dbg('Running get_summaries_affected, parameters;');
1521 dbg('p_event_group => '||p_event_group);
1522 dbg('p_start_date => '||p_start_date);
1523 dbg('p_end_date => '||p_end_date);
1524 IF p_location_stripe THEN
1525 dbg('p_location_stripe => TRUE');
1526 ELSE
1527 dbg('p_location_stripe => FALSE');
1528 END IF;
1529 IF p_raise_no_data THEN
1530 dbg('p_raise_no_data => TRUE');
1531 ELSE
1532 dbg('p_raise_no_data => FALSE');
1533 END IF;
1534 --
1535 -- Get the current time (100th's of a second)
1536 l_start := dbms_utility.get_time;
1537 g_SECONDS_ELAPSED := 0;
1538 --
1539 -- Clear out the results table, and the event group cache
1540 p_summary_refresh.DELETE;
1541 g_DATED_TABLE_EXTRAS.DELETE;
1542 g_EVENT_GROUP_ID := NULL;
1543 --
1544 -- Initialise the events group cache
1545 -- 2984406: Moved to here, instead of on a per-assignment basis
1546 init_event_group_cache(p_event_group);
1547 --
1548 BEGIN
1549 --
1550 -- Get all the assignment IDs for which events
1551 -- have occurred, but ignore supervisor ID changes
1552 get_assignments_affected(
1553 p_start_date,
1554 p_end_date,
1555 l_assignments
1556 );
1557 --
1558 -- Process all the assignments we found
1559 get_refresh_periods(
1560 l_assignments,
1561 l_summary_refresh_temp,
1562 p_start_date,
1563 p_end_date,
1564 p_location_stripe
1565 );
1566 EXCEPTION WHEN no_assignment_events_found THEN
1567 msg('No affected assignments were found in the refresh period');
1568 END;
1569 --
1570 BEGIN
1571 --
1572 -- Add the refresh periods for changes to the supervisor ID column
1573 add_supervisor_corrections(
1574 l_summary_refresh_temp,
1575 p_start_date,
1576 p_end_date,
1577 p_location_stripe
1578 );
1579 EXCEPTION WHEN no_supervisor_corrections THEN
1580 msg('No datetrack corrections to supervisor ID were found within refresh period');
1581 END;
1582 --
1583 -- Check that we've got something in the summary refresh table
1584 IF NVL(l_summary_refresh_temp.LAST,0) <= 0 THEN
1585 msg('No records in refresh table, nothing to do');
1586 dbg('Finished get_summaries_affected');
1587 --
1588 -- Record the time taken (to get nothing!)
1589 l_curr := dbms_utility.get_time;
1590 g_SECONDS_ELAPSED := (l_curr - l_start) / 100;
1591 msg(get_elapsed_time_text);
1592 --
1593 -- Clear the looping globals
1594 g_FIRST_RECORD := 0;
1595 g_LAST_RECORD := 0;
1596 g_CURRENT_RECORD := 0;
1597 --
1598 RETURN;
1599 END IF;
1600 --
1601 -- De-duplicate the records, what this means is that we remove any
1602 -- portions of refresh records for specific supervisors that overlap
1603 -- the "all" period. This period will always be contiguous, after the
1604 -- de-dupe process the specific supervisor records may not be.
1605 de_dupe_refresh_periods(
1606 l_summary_refresh_temp,
1607 p_summary_refresh,
1608 l_all_supv,
1609 l_out_num,
1610 l_all_start,
1611 l_all_end
1612 );
1613 --
1614 -- We've finished. Record some diagnostics trace information
1615 msg('Supervisor refresh events recorded: '||l_out_num);
1616 IF NOT l_all_supv THEN
1617 msg('There is no "refresh all" period');
1618 ELSE
1619 msg('Refresh all supervisors for: '||l_all_start||' -> '||l_all_end);
1620 END IF;
1621 --
1622 -- Record the time taken for the full run
1623 l_curr := dbms_utility.get_time;
1624 g_SECONDS_ELAPSED := (l_curr - l_start) / 100;
1625 msg(get_elapsed_time_text);
1626 --
1627 -- Initialise the globals we use for simplified record looping
1628 g_FIRST_RECORD := NVL(p_summary_refresh.FIRST,0);
1629 g_LAST_RECORD := NVL(p_summary_refresh.LAST,0);
1630 g_CURRENT_RECORD := 0;
1631 --
1632 -- If we asked then raise no_data_found if there's no data in the table
1633 dbg('Finished get_summaries_affected');
1634 IF p_raise_no_data THEN
1635 IF NVL(p_summary_refresh.FIRST,0) <= 0 THEN
1636 RAISE no_data_found;
1637 END IF;
1638 END IF;
1639 END get_summaries_affected;
1640 --
1641 FUNCTION next_record RETURN BOOLEAN IS
1642 BEGIN
1643 g_CURRENT_RECORD := g_CURRENT_RECORD + 1;
1644 RETURN (g_CURRENT_RECORD >= g_FIRST_RECORD AND g_CURRENT_RECORD <= g_LAST_RECORD);
1645 END next_record;
1646 --
1647 FUNCTION current_record RETURN NUMBER IS
1648 BEGIN
1649 RETURN LEAST(g_CURRENT_RECORD,g_LAST_RECORD + 1);
1650 END current_record;
1651 --
1652 END pay_events_wrapper;
[Home] [Help]