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