1 PACKAGE pay_interpreter_pkg AUTHID DEFINER AS
2 /* $Header: pyinterp.pkh 120.3.12010000.3 2008/11/03 13:43:41 salogana ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2000 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 Package File Name : pyinterp.pkh
10 Description :
11
12 Change List:
13 ------------
14 Name Date Version Bug Text
15 -------------- ----------- ------- ------- -----------------------------
16 salogana 03-Oct-2008 115.30 7443747 Added the global variable
17 g_pen_collect_reports
18 for penserver issue.
19 ayegappa 28-MAY-2008 115.29 7120770 Commented set_internal_cache
20 procedure
21 ayegappa 09-MAY-2008 115.28 6992642 Added penserver flag to
22 entries, entry affected and
23 event_group_table_affected
24 procedures.
25 nbristow 05-JUL-2007 115.27 Added set_internal_cache.
26 SuSivasu 02-Nov-2005 115.26 Added creation date to
27 t_detailed_output_tab_rec
28 and process_mode to entries_affected.
29 nbristow 13-JUN-2005 115.25 Using valid_group_event_for_asg
30 to prequalify events
31 nbristow 28-APR-2005 115.24 Performance changes for
32 RetroNotification.
33 nbristow 23-FEB-2005 115.23 Changes for Period Allocation.
34 nbristow 29-APR-2004 115.22 Previous change had issues on
35 8.1.7 Db version.
36 nbristow 20-APR-2004 115.21 Fixed GCSS warnings.
37 nbristow 20-APR-2004 115.20 Added entries_affected procedure.
38 jford 10-MAR-2004 115.19 Add global types
39 jford 16-FEB-2004 115.18 3446200 Add get_subset_given_new_evg
40 jford 01-FEB-2004 115.17 Get pde.dyt_type
41 jford 03-JAN-2004 115.16 3329824 Change date dfts, guarantee not null
42 jford 05-DEC-2003 115.15 Get pde.column_name
43 jford 25-NOV-2003 115.14 3257307 New entry_affected for ADV_RETRONOT
44 jford 06-SEP-2003 115.12 Make largest entry_affected allow
45 IN OUT tables, eg half full results
46 nbristow 02-MAY-2003 115.11 Added functions to return values
47 nbristow 02-MAY-2003 115.10 Added g_asg_id and g_ee_id.
48 jford 01-FEB-2003 115.9 Major alterations so Continuous
49 Calculation can use this interpreter.
50 nbristow 18-JUL-2002 115.6 Changes for Run proration.
51 Ed Jones 14-JUN-2002 115.5 Changes to support the
52 portal summarisation wrapper
53 Made a couple of extra routines
54 and types public, added owner
55 to the detailed output record.
56 T Battoo 05-MAR-2001 115.4 Unknown (comment added by exjones)
57 Ashu Gupta 06-FEB-2001 115.3 Changed the name of the table
58 from pay_tables to
59 pay_dated_tables.
60 nbristow 26-JAN-2001 115.2 Added the prorate_start_date
61 function.
62 Ashu GUPTA 15-JAN-2001 115.0 Initial version.
63 ========================================================================
64 */
65 --
66 --Global Variables
67 --
68 g_effective_date date;
69 g_object_key varchar2(2000);
70 g_parent_key varchar2(2000);
71 g_asg_id number;
72 g_ee_id number;
73 --
74 -- Record Types
75 --bug 7443747:Start
76 g_pen_collect_reports VARCHAR2(5);
77 --bug 7443747:Stop
78 --
79 -- Event Qualifier Caches
80 --
81 type t_child_evt_qual_rec is record
82 (
83 from_value pay_event_value_changes_f.from_value%type,
84 to_value pay_event_value_changes_f.to_value%type,
85 valid_event pay_event_value_changes_f.valid_event%type,
86 proration_style pay_event_value_changes_f.proration_style%type,
87 qualifier_value pay_event_value_changes_f.qualifier_value%type,
88 qualifier_definition pay_event_qualifiers_f.qualifier_definition%type,
89 comparison_column pay_event_qualifiers_f.comparison_column%type,
90 qualifier_where_clause pay_event_qualifiers_f.qualifier_where_clause%type,
91 multi_event_sql pay_event_qualifiers_f.multi_event_sql%type
92 );
93 --
94 type t_child_evt_qual_tab is table of t_child_evt_qual_rec
95 index by binary_integer;
96 --
97 type t_evt_qual_rec is record
98 (
99 valid_event pay_event_value_changes_f.valid_event%type,
100 proration_style pay_event_value_changes_f.proration_style%type,
101 assignment_qualification pay_event_qualifiers_f.assignment_qualification%type,
102 entry_qualification pay_event_qualifiers_f.entry_qualification%type,
103 start_qual_ptr number,
104 end_qual_ptr number
105 );
106 --
107 type t_evt_qual_tab is table of t_evt_qual_rec
108 index by binary_integer;
109 --
110 -- table columns
111 --
112 type t_table_columns_rec is record
113 (
114 column_name pay_event_procedures.column_name%type,
115 evt_proc_start_ptr number,
116 evt_proc_end_ptr number,
117 next_ptr number
118 );
119
120 type t_table_columns_tab is table of t_table_columns_rec
121 index by binary_integer;
122
123 -- Event Procedures
124 type t_event_procedure_rec is record
125 (
126 procedure_name pay_event_procedures.procedure_name%type,
127 next_ptr number
128 );
129
130 type t_event_procedure_tab is table of t_event_procedure_rec
131 index by binary_integer;
132
133 /***
134 *** The following type will hold the records from c_distinct_table. This will
135 *** be used in caching.
136 ***/
137 TYPE t_distinct_table_rec IS RECORD
138 (
139 table_id pay_dated_tables.dated_table_id%TYPE ,
140 table_name pay_dated_tables.table_name%TYPE ,
141 owner pay_dated_tables.owner%TYPE ,
142 dyt_type pay_dated_tables.dyn_trigger_type%TYPE ,
143 surrogate_key_name pay_dated_tables.surrogate_key_name%TYPE ,
144 start_date_name pay_dated_tables.start_date_name%TYPE ,
145 end_date_name pay_dated_tables.end_date_name%TYPE ,
146 datetracked_event_id pay_datetracked_events.datetracked_event_id%TYPE,
147 column_name pay_datetracked_events.column_name%TYPE ,
148 update_type pay_datetracked_events.update_type%TYPE ,
149 proration_type pay_datetracked_events.proration_style%TYPE
150 );
151
152 TYPE t_distinct_table IS TABLE OF t_distinct_table_rec
153 INDEX BY BINARY_INTEGER ;
154 t_distinct_tab t_distinct_table ;
155
156 TYPE t_detailed_output_tab_rec IS RECORD
157 (
158 dated_table_id pay_dated_tables.dated_table_id%TYPE ,
159 datetracked_event pay_datetracked_events.datetracked_event_id%TYPE ,
160 update_type pay_datetracked_events.update_type%TYPE ,
161 surrogate_key pay_process_events.surrogate_key%type ,
162 column_name pay_event_updates.column_name%TYPE ,
163 effective_date date,
164 creation_date date,
165 old_value varchar2(2000),
166 new_value varchar2(2000),
167 change_values varchar2(2000),
168 proration_type varchar2(10),
169 change_mode pay_process_events.change_type%type,--'DATE_PROCESSED' etc
170 element_entry_id pay_element_entries_f.element_entry_id%type,
171 next_ee number
172 );
173
174 TYPE t_proration_dates_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER ;
175
176 TYPE t_proration_type_table_type IS TABLE OF VARCHAR2(10) INDEX BY
177 BINARY_INTEGER ;
178
179 TYPE t_detailed_output_table_type IS TABLE OF t_detailed_output_tab_rec
180 INDEX BY BINARY_INTEGER ;
181 TYPE t_datetrack_ee_rec IS RECORD
182 (
183 datetracked_evt_id pay_datetracked_events.datetracked_event_id%TYPE ,
184 element_entry_id pay_element_entries_f.element_entry_id%type,
185 next_ptr number
186 );
187
188 TYPE t_datetrack_ee_tab IS TABLE OF t_datetrack_ee_rec
189 INDEX BY BINARY_INTEGER ;
190
191 TYPE t_hash_table_type IS TABLE OF number
192 INDEX BY BINARY_INTEGER ;
193 TYPE t_global_env_rec IS RECORD
194 (
195 /* A number of entries in this record have been
196 removed and replaced with glo_ variables.
197 This is a restriction placed in the version
198 of this db that we have to support. When this
199 restriction is removed we should remove the
200 glo_ versions
201 */
202 -- ee_hash_table t_hash_table_type,
203 -- datetrack_ee_tab t_datetrack_ee_tab,
204 -- datetrack_ee_hash_tab t_hash_table_type,
205 datetrack_ee_tab_use boolean,
206 validate_run_actions boolean,
207 -- monitored_events t_distinct_table,
208 monitor_start_ptr number,
209 monitor_end_ptr number
210 --,
211 -- Values needed for the event
212 -- procedures
213 -- column_hash_tab t_hash_table_type,
214 -- table_columns t_table_columns_tab,
215 -- event_procedures t_event_procedure_tab,
216 -- Values needed for the Event Qualifiers
217 -- event_qualifiers t_evt_qual_tab,
218 -- child_event_qualifiers t_child_evt_qual_tab
219 );
220 glo_ee_hash_table t_hash_table_type;
221 glo_datetrack_ee_tab t_datetrack_ee_tab;
222 glo_datetrack_ee_hash_tab t_hash_table_type;
223 glo_monitored_events t_distinct_table;
224 glo_column_hash_tab t_hash_table_type;
225 glo_table_columns t_table_columns_tab;
226 glo_event_procedures t_event_procedure_tab;
227 glo_event_qualifiers t_evt_qual_tab;
228 glo_child_event_qualifiers t_child_evt_qual_tab;
229 --
230 /***
231 *** The following type will hold the records for a proration group id. This will
232 *** be used in caching.
233 ***/
234 TYPE t_proration_group_rec IS RECORD
235 (
236 -- proration_group_id pay_element_types_f.proration_group_id%TYPE ,
237 range_start NUMBER ,
238 range_end NUMBER
239 );
240 TYPE t_proration_group_table IS TABLE OF t_proration_group_rec
241 INDEX BY BINARY_INTEGER ;
242
243 t_proration_group_tab t_proration_group_table ;
244
245 TYPE t_process_event_rec IS RECORD
246 (
247 process_event_id pay_process_events.process_event_id%TYPE
248 );
249
250 TYPE t_process_event_table IS TABLE OF t_process_event_rec;
251
252 --< Required to be public for the pay_events_wrapper package
253 -- The following type will hold the records that will contain the column_name,
254 -- its old and new value.
255 TYPE t_dynamic_sql_rec IS RECORD
256 (
257 date_tracked_id pay_datetracked_events.datetracked_event_id%TYPE ,
258 column_name pay_datetracked_events.column_name%TYPE ,
259 old_value VARCHAR2(100) ,
260 new_value VARCHAR2(100) ,
261 proration_style pay_datetracked_events.proration_style%TYPE
262 );
263 TYPE t_dynamic_sql_tab IS TABLE OF t_dynamic_sql_rec INDEX BY BINARY_INTEGER;
264 -->
265
266 /* The following procedure will be called from ADV_RETRONOT */
267 /* The following procedure will be called from CONT_CALC */
268 --
269 --procedure set_internal_cache; Bug 7120770
270 --
271 procedure initialise_global(p_global_env IN OUT NOCOPY t_global_env_rec);
272 --
273 procedure add_datetrack_event_to_entry
274 (p_datetracked_evt_id in number,
275 p_element_entry_id in number,
276 p_global_env in out nocopy t_global_env_rec);
277 --
278 procedure clear_dt_event_for_entry
279 ( p_global_env in out nocopy t_global_env_rec);
280 --
281 procedure event_group_tables
282 (
283 p_event_group_id IN NUMBER,
284 p_distinct_tab IN OUT NOCOPY t_distinct_table
285 );
286 --
287 procedure get_prorated_dates
288 (
289 p_element_entry_id IN NUMBER DEFAULT NULL ,
290 p_assignment_action_id IN NUMBER DEFAULT NULL ,
291 p_time_definition_id IN NUMBER DEFAULT NULL ,
292 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
293 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
294 t_proration_type OUT NOCOPY t_proration_type_table_type
295 );
296 --
297 PROCEDURE entry_affected
298 (
299 p_element_entry_id IN NUMBER DEFAULT NULL ,
300 p_assignment_action_id IN NUMBER DEFAULT NULL ,
301 p_assignment_id IN NUMBER DEFAULT NULL ,
302 p_mode IN VARCHAR2 DEFAULT NULL ,
303 p_process IN VARCHAR2 DEFAULT NULL ,
304 p_event_group_id IN NUMBER DEFAULT NULL ,
305 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE' ,
306 p_start_date IN DATE DEFAULT hr_api.g_sot,
307 p_end_date IN DATE DEFAULT hr_api.g_eot,
308 p_process_date IN DATE DEFAULT SYSDATE,
309 p_unique_sort IN VARCHAR2 DEFAULT 'Y',
310 p_business_group_id IN NUMBER DEFAULT null,
311 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
312 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
313 t_proration_change_type OUT NOCOPY t_proration_type_table_type,
314 t_proration_type OUT NOCOPY t_proration_type_table_type,
315 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
316 );
317
318 /* The following procedure will be called from Payroll for Proration use*/
319 PROCEDURE entry_affected
320 (
321 p_element_entry_id IN NUMBER DEFAULT NULL ,
322 p_assignment_action_id IN NUMBER DEFAULT NULL ,
323 p_assignment_id IN NUMBER DEFAULT NULL ,
324 p_mode IN VARCHAR2 DEFAULT NULL ,
325 p_process IN VARCHAR2 DEFAULT NULL ,
326 p_event_group_id IN NUMBER DEFAULT NULL ,
327 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_EFFECTIVE_DATE' ,
328 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
329 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
330 t_proration_change_type OUT NOCOPY t_proration_type_table_type,
331 t_proration_type OUT NOCOPY t_proration_type_table_type
332 );
333
334 PROCEDURE entry_affected
335 (
336 p_element_entry_id IN NUMBER DEFAULT NULL ,
337 p_assignment_id IN NUMBER DEFAULT NULL ,
338 p_mode IN VARCHAR2 DEFAULT NULL ,
339 p_process IN VARCHAR2 DEFAULT NULL ,
340 p_event_group_id IN NUMBER DEFAULT NULL ,
341 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
345
342 t_proration_change_type OUT NOCOPY t_proration_type_table_type
343 );
344
346 PROCEDURE entry_affected
347 (
348 p_element_entry_id IN NUMBER DEFAULT NULL ,
349 p_assignment_action_id IN NUMBER DEFAULT NULL ,
350 t_detailed_output OUT NOCOPY t_detailed_output_table_type ,
351 t_proration_dates OUT NOCOPY t_proration_dates_table_type ,
352 t_proration_type OUT NOCOPY t_proration_type_table_type
353 );
354
355 FUNCTION time_fn(p_assignment_action_id IN NUMBER ,
356 p_proration_group_id IN NUMBER ,
357 p_element_entry_id IN NUMBER ) RETURN DATE;
358
359 /* The following procedure will be called from Payroll for Continous Calc use*/
360
361 PROCEDURE asg_action_affected(p_assignment_action_id IN NUMBER);
362
363 /* The following procedure will be called from Payroll for Continous Calc use*/
364
365 PROCEDURE asg_action_event(p_assignment_action_id IN NUMBER ,
366 p_process_event_tab IN t_process_event_table ,
367 p_affected OUT NOCOPY VARCHAR2 );
368 --
369 --< Required to be public for the pay_events_wrapper package
370 PROCEDURE event_group_tables_affected
371 (
372 p_element_entry_id IN NUMBER DEFAULT NULL ,
373 p_assignment_action_id IN NUMBER,
374 p_event_group_id IN NUMBER,
375 p_assignment_id IN NUMBER,
376 p_business_group_id IN NUMBER,
377 p_start_date IN DATE,
378 p_end_date IN DATE,
379 p_mode IN VARCHAR2,
380 p_process IN VARCHAR2,
381 p_process_mode IN VARCHAR2,
382 t_dynamic_sql IN OUT NOCOPY t_dynamic_sql_tab,
383 t_proration_dates_temp IN OUT NOCOPY t_proration_dates_table_type ,
384 t_proration_change_type IN OUT NOCOPY t_proration_type_table_type,
385 t_detailed_output IN OUT NOCOPY t_detailed_output_table_type,
386 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
387 );
388 procedure event_group_tables
389 (
390 p_event_group_id IN NUMBER
391 );
392 -->
393
394
395 /****************************************************************************
396 Name : prorate_start_date
397 Purpose : This function returns the start date of a proration period.
398 Arguments :
399 IN : p_assignment_action_id
400 p_proration_group_id
401 OUT : p_start_date
402 Notes : Public
403 ****************************************************************************/
404 FUNCTION prorate_start_date(p_assignment_action_id IN NUMBER ,
405 p_proration_group_id IN NUMBER
406 ) RETURN DATE;
407 procedure generic_data_validation(p_dated_table_id in number,
408 p_datetracked_event_id in number,
409 p_old_value in varchar2,
410 p_new_value in varchar2,
411 p_date in date,
412 p_key in varchar2,
413 p_ee_id in number,
414 p_asg_id in number,
415 p_valid OUT NOCOPY varchar2,
416 p_type OUT NOCOPY varchar2,
417 p_global_env IN OUT NOCOPY t_global_env_rec);
418 --
419 function get_object_key return varchar2;
420 function get_parent_key return varchar2;
421 function get_effective_date return date;
422 function get_assignment_id return number;
423 function get_element_entry_id return number;
424 --
425 TYPE t_mst_process_event_rec IS RECORD
426 (
427 updated_column_name pay_event_updates.column_name%type,
428 event_type pay_event_updates.event_type%type,
429 event_update_id pay_event_updates.event_update_id%type,
430 effective_date pay_process_events.effective_date%type,
431 assignment_id pay_process_events.assignment_id%type,
432 surrogate_key pay_process_events.surrogate_key%type,
433 process_event_id pay_process_events.surrogate_key%type,
434 change_values pay_process_events.description%type,
435 calculation_date pay_process_events.calculation_date%type,
436 creation_date pay_process_events.creation_date%type,
437 change_mode pay_process_events.change_type%type,
438 table_name pay_dated_tables.table_name%TYPE
439 );
440
441 --used in extra_tests_dbt_i and extra_tests_dbt_p
442 TYPE t_key_date_cache_rec is record
443 (
444 key varchar2(240),
445 min_date pay_process_events.creation_date%type,
446 max_date pay_process_events.creation_date%type,
447 got_flag varchar2(15) default 'N'
448 );
449 TYPE t_key_date_cache is
450 table of t_key_date_cache_rec INDEX BY BINARY_INTEGER;
451 --
452 /****************************************************************************
453 Name : get_subset_given_new_evg
454 Purpose : This procedure returns a new table of discovered events that
455 match the given new event group. Passed in is a table of events to compare.
456 I.e. used when call the Interpreter once, get results, then want to filter
457 this first results table with a new event group
458 Arguments :
459 IN : p_filter_event_group_id --The event group of filtering events
460 p_complete_detail_tab --The full table of events
461 OUT : p_subset_detail_tab --The resultant table
462 Notes : Public, created for HRI wrapper to call
463 ****************************************************************************/
464 PROCEDURE get_subset_given_new_evg
465 (
469 );
466 p_filter_event_group_id IN NUMBER ,
467 p_complete_detail_tab IN t_detailed_output_table_type ,
468 p_subset_detail_tab IN OUT NOCOPY t_detailed_output_table_type
470
471 PROCEDURE entries_affected
472 (
473 p_assignment_id IN NUMBER DEFAULT NULL ,
474 p_mode IN VARCHAR2 DEFAULT NULL ,
475 p_start_date IN DATE DEFAULT hr_api.g_sot,
476 p_end_date IN DATE DEFAULT hr_api.g_eot,
477 p_business_group_id IN NUMBER,
478 p_global_env IN OUT NOCOPY t_global_env_rec,
479 t_detailed_output OUT NOCOPY t_detailed_output_table_type,
480 p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE',
481 p_penserv_mode IN VARCHAR2 DEFAULT 'N'
482 );
483 --
484 function valid_group_event_for_asg(p_table_name in varchar2,
485 p_assignment_id in number,
486 p_surrogate_key in varchar2)
487 return varchar2;
488 END pay_interpreter_pkg;
[Home] [Help]