eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PQP_GB_PSI_SCH

Source


1 PACKAGE body PQP_GB_PSI_SCH
2   /* $Header: pqpgbpsisch.pkb 120.0.12020000.9 2013/01/02 13:23:01 achandwa noship $ */
3 AS
4 PROCEDURE DEBUG(
5     p_trace_message  IN VARCHAR2,
6     p_trace_location IN NUMBER)
7 IS
8   --
9 BEGIN
10   --
11   if g_debug then
12    pqp_utilities.DEBUG( p_trace_message => p_trace_message ,p_trace_location => p_trace_location );
13   end if;
14   --
15 END DEBUG;
16 -- This procedure is used for debug purposes
17 -- debug_enter checks the debug flag and sets the trace on/off
18 --
19 -- ----------------------------------------------------------------------------
20 -- |----------------------------< debug_enter >-------------------------------|
21 -- ----------------------------------------------------------------------------
22 PROCEDURE debug_enter(
23     p_proc_name IN VARCHAR2,
24     p_trace_on  IN VARCHAR2)
25 IS
26 BEGIN
27   --
28  if g_debug then
29   IF pqp_utilities.g_nested_level = 0 THEN
30     hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
31   END IF;
32   --       g_nested_level := g_nested_level + 1;
33   --       debug('Entering: ' || NVL(p_proc_name, g_proc_name)
34   --            ,g_nested_level * 100);
35   pqp_utilities.debug_enter(p_proc_name => p_proc_name ,p_trace_on => p_trace_on);
36   --
37   end if;
38 END debug_enter;
39 -- This procedure is used for debug purposes
40 --
41 -- ----------------------------------------------------------------------------
42 -- |----------------------------< debug_exit >--------------------------------|
43 -- ----------------------------------------------------------------------------
44 PROCEDURE debug_exit(
45     p_proc_name IN VARCHAR2,
46     p_trace_off IN VARCHAR2)
47 IS
48 BEGIN
49   --
50     if g_debug then
51   --       DEBUG (
52   --             'Leaving: '
53   --          || NVL (p_proc_name, g_proc_name),
54   --          -g_nested_level * 100
55   --       );
56   --       g_nested_level :=   g_nested_level
57   --                         - 1;
58   pqp_utilities.debug_exit(p_proc_name => p_proc_name ,p_trace_off => p_trace_off);
59   -- debug enter sets trace ON when g_trace = 'Y' and nested level = 0
60   -- so we must turn it off for the same condition
61   -- Also turn off tracing when the override flag of p_trace_off has been
62   -- passed as Y
63   IF pqp_utilities.g_nested_level = 0 THEN
64     hr_utility.trace_off;
65   END IF; -- (g_nested_level = 0
66   end if;
67   --
68 END debug_exit;
69 -- This procedure is used for debug purposes
70 --
71 -- ----------------------------------------------------------------------------
72 -- |----------------------------< debug_others >------------------------------|
73 -- ----------------------------------------------------------------------------
74 PROCEDURE debug_others(
75     p_proc_name IN VARCHAR2,
76     p_proc_step IN NUMBER)
77 IS
78 BEGIN
79   --
80  if g_debug then
81   pqp_utilities.debug_others(p_proc_name => p_proc_name ,p_proc_step => p_proc_step);
82  end if;
83   --
84 END debug_others;
85 -- This procedure is used for clear cache
86 --
87 -- ----------------------------------------------------------------------------
88 -- |----------------------------< clear_cache >------------------------------|
89 -- ----------------------------------------------------------------------------
90 PROCEDURE clear_cache
91 IS
92   --
93   l_proc_name VARCHAR2(80) := g_proc_name || 'clear_cache';
94   l_proc_step PLS_INTEGER;
95   --
96 BEGIN
97   --
98   IF g_debug THEN
99     l_proc_step := 10;
100     debug_enter(l_proc_name);
101   END IF;
102   -- start clearing globals
103   g_business_group_id := NULL;
104   g_assignment_id     := NULL;
105   g_person_id         := NULL;
106   g_person_dtl        := NULL;
107   g_assignment_dtl    := NULL;
108   g_effective_date    := NULL;
109   g_extract_type      := NULL;
110   g_current_run       := NULL;
111   g_altkey            := NULL;
112   -- globals set by set_shared_globals
113   g_paypoint     := NULL;
114   g_cutover_date := NULL;
115   g_ext_dfn_id   := NULL;
116   --
117   IF g_debug THEN
118     debug_exit(l_proc_name);
119   END IF;
120 EXCEPTION
121 WHEN OTHERS THEN
122   IF SQLCODE <> hr_utility.hr_error_number THEN
123     debug_others (l_proc_name, l_proc_step);
124     IF g_debug THEN
125       DEBUG ( 'Leaving: ' || l_proc_name, -999);
126     END IF;
127     fnd_message.raise_error;
128   ELSE
129     RAISE;
130   END IF;
131 END clear_cache;
132 -- This procedure is used to check if current event is already process
133 -- If processesed it will not be picked again
134 -- ----------------------------------------------------------------------------
135 -- |----------------------------< debug_others >------------------------------|
136 -- ----------------------------------------------------------------------------
137 FUNCTION is_curr_evt_processed
138   RETURN BOOLEAN
139 IS
140   l_proc VARCHAR2(72) := g_proc_name||'.is_curr_evt_processed';
141   l_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
142   l_flag   VARCHAR2(1);
143   l_return BOOLEAN;
144 BEGIN
145   l_return:= false;
146   debug_enter(l_proc);
147   IF g_prev_event_dtl_rec.dated_table_id         IS NOT NULL THEN
148     l_prev_event_dtl_rec                         := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
149     l_prev_event_dtl_rec.change_mode             := g_prev_event_dtl_rec.change_mode;
150     l_flag                                       := 'Y';
151     IF l_prev_event_dtl_rec.dated_table_id       <> g_prev_event_dtl_rec.dated_table_id THEN
152       l_flag                                     := 'N';
153     ELSIF l_prev_event_dtl_rec.datetracked_event <> g_prev_event_dtl_rec.datetracked_event THEN
154       l_flag                                     := 'N';
155     ELSIF l_prev_event_dtl_rec.update_type       <> g_prev_event_dtl_rec.update_type THEN
156       l_flag                                     := 'N';
157     ELSIF l_prev_event_dtl_rec.surrogate_key     <> g_prev_event_dtl_rec.surrogate_key THEN
158       l_flag                                     := 'N';
159     ELSIF l_prev_event_dtl_rec.column_name       <> g_prev_event_dtl_rec.column_name THEN
160       l_flag                                     := 'N';
161     ELSIF l_prev_event_dtl_rec.effective_date    <> g_prev_event_dtl_rec.effective_date THEN
162       l_flag                                     := 'N';
163     ELSIF l_prev_event_dtl_rec.old_value         <> g_prev_event_dtl_rec.old_value THEN
164       l_flag                                     := 'N';
165     ELSIF l_prev_event_dtl_rec.new_value         <> g_prev_event_dtl_rec.new_value THEN
166       l_flag                                     := 'N';
167     ELSIF l_prev_event_dtl_rec.change_values     <> g_prev_event_dtl_rec.change_values THEN
168       l_flag                                     := 'N';
169     ELSIF l_prev_event_dtl_rec.proration_type    <> g_prev_event_dtl_rec.proration_type THEN
170       l_flag                                     := 'N';
171     ELSIF l_prev_event_dtl_rec.event_group_id    <> g_prev_event_dtl_rec.event_group_id THEN
172       l_flag                                     := 'N';
173     ELSIF l_prev_event_dtl_rec.actual_date       <> g_prev_event_dtl_rec.actual_date THEN
174       l_flag                                     := 'N';
175     END IF;
176     IF l_flag = 'Y' THEN
177       debug('Event already processed',30);
178       debug_exit(l_proc);
179       l_return:= TRUE;
180     ELSE
181       g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
182       l_return             := FALSE;
183     END IF;
184   ELSE
185     debug('First event');
186     g_prev_event_dtl_rec := g_pay_proc_evt_tab(ben_ext_person.g_chg_pay_evt_index);
187     l_return             := FALSE;
188   END IF;
189   RETURN l_return;
190   debug('leaving is_curr_evt_processed');
191 END is_curr_evt_processed;
192 -- This procedure sets a record structure for pension scheme mapping
193 -- configuration to be used to find, which scheme does the assignment belong to
194 -- as on effective date
195 -- ----------------------------------------------------------------------------
196 -- |----------------------------< fetch_pension_scheme_map_cv >------------------------------|
197 -- ----------------------------------------------------------------------------
198 PROCEDURE fetch_pension_scheme_map_cv(
199     p_business_group_id IN NUMBER ,
200     p_tab_pen_sch_map_cv OUT NOCOPY pqp_utilities.t_config_values )
201 IS
202   --
203   l_proc_name VARCHAR2(80) := g_proc_name || 'fetch_pension_scheme_map_cv';
204   l_proc_step PLS_INTEGER;
205   l_element_type_id NUMBER;
206   l_configuration_type pqp_configuration_types.configuration_type%TYPE;
207   l_tab_config_values pqp_utilities.t_config_values;
208   i NUMBER;
209   --
210 BEGIN
211   --
212   IF g_debug THEN
213     l_proc_step := 10;
214     debug_enter(l_proc_name);
215   END IF;
216   --
217   -- Call configuration value function to retrieve all data
218   -- for a configuration type
219   IF g_debug THEN
220     l_proc_step := 20;
221     DEBUG(l_proc_name, l_proc_step);
222     DEBUG('p_business_group_id: ' || p_business_group_id);
223   END IF;
224   l_configuration_type                                  := 'PQP_GB_PENSERV_SCHEME_MAP_INFO';
225   IF pqp_gb_psi_functions.g_pension_scheme_mapping.COUNT = 0 THEN
226     pqp_utilities.get_config_type_values( p_configuration_type => l_configuration_type ,p_business_group_id => p_business_group_id ,p_legislation_code => g_legislation_code ,p_tab_config_values => l_tab_config_values );
227   ELSE -- get it from cached collection
228     l_tab_config_values := pqp_gb_psi_functions.g_pension_scheme_mapping;
229   END IF;
230   IF g_debug THEN
231     l_proc_step := 30;
232     DEBUG(l_proc_name, l_proc_step);
233     DEBUG('l_configuration_type: ' || l_configuration_type);
234     DEBUG('l_tab_config_values.count: ' || l_tab_config_values.COUNT);
235   END IF;
236   -- Return the
237   -- collection for pension scheme elements
238   p_tab_pen_sch_map_cv := l_tab_config_values;
239   -- Penserver Pension Scheme PCV_INFORMATION2
240   -- Template Pension Scheme          PCV_INFORMATION1
241   i := l_tab_config_values.FIRST;
242   IF g_debug THEN
243     l_proc_step := 40;
244     DEBUG(l_proc_name, l_proc_step);
245   END IF;
246   WHILE i IS NOT NULL
247   LOOP
248     IF g_debug THEN
249       DEBUG( 'Penserver Pension Scheme: ' || l_tab_config_values(i).pcv_information2 );
250       DEBUG( 'Template Pension Scheme: ' || l_tab_config_values(i).pcv_information1 );
251       DEBUG('Partnership Scheme: '|| l_tab_config_values(i).pcv_information3);
252     END IF;
253     i := l_tab_config_values.NEXT(i);
254   END LOOP;
255   IF g_debug THEN
256     l_proc_step := 50;
257     debug_exit(l_proc_name);
258   END IF;
259 EXCEPTION
260 WHEN OTHERS THEN
261   clear_cache;
262   IF SQLCODE <> hr_utility.hr_error_number THEN
263     debug_others(l_proc_name, l_proc_step);
264     IF g_debug THEN
265       DEBUG('Leaving: ' || l_proc_name, -999);
266     END IF;
267     fnd_message.raise_error;
268   ELSE
269     RAISE;
270   END IF;
271 END fetch_pension_scheme_map_cv;
272 -- This procedure gets the pension scheme of the assignment
273 -- ----------------------------------------------------------------------------
274 -- |----------------------------< get_pen_scheme_memb >------------------------------|
275 -- ----------------------------------------------------------------------------
276 FUNCTION get_pen_scheme_memb(
277     p_assignment_id      IN NUMBER ,
278     p_effective_date     IN DATE ,
279     p_tab_pen_sch_map_cv IN pqp_utilities.t_config_values ,
280     p_rec_ele_ent_details OUT NOCOPY r_ele_ent_details )
281   RETURN VARCHAR2
282 IS
283   --
284   -- Cursor to get pension scheme element details
285   -- for this person
286   CURSOR csr_get_ele_ent_details(c_element_type_id NUMBER)
287   IS
288     SELECT pee.element_entry_id,
289       pee.effective_start_date ,
290       pee.effective_end_date,
291       pel.element_type_id
292     FROM pay_element_entries_f pee,
293       pay_element_links_f pel
294     WHERE pee.assignment_id = p_assignment_id
295     AND pee.entry_type      = 'E'
296     AND pee.element_link_id = pel.element_link_id
297     AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
298     AND pel.element_type_id = c_element_type_id
299     AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
300     ORDER BY pee.effective_start_date DESC;
301   l_proc_name VARCHAR2(80) := g_proc_name || 'get_pen_scheme_memb';
302   l_proc_step PLS_INTEGER;
303   l_pension_category pqp_configuration_values.pcv_information1%TYPE;
304   l_rec_ele_ent_details r_ele_ent_details;
305   l_element_type_id NUMBER;
306   i                 NUMBER;
307   l_eff_start_date DATE := TO_DATE('01-01-0001', 'DD-MM-YYYY');
308   --
309 BEGIN
310   --
311   IF g_debug THEN
312     l_proc_step := 10;
313     debug_enter(l_proc_name);
314     DEBUG('p_assignment_id: ' || p_assignment_id);
315     DEBUG('p_effective_date: ' || TO_CHAR(p_effective_date, 'DD/MON/YYYY'));
316   END IF;
317   i       := g_tab_pen_sch_map_cv.FIRST;
318   WHILE i IS NOT NULL
319   LOOP
320     l_element_type_id := fnd_number.canonical_to_number(p_tab_pen_sch_map_cv(i).pcv_information1);
321     IF g_debug THEN
322       l_proc_step := 20;
323       DEBUG(l_proc_name, l_proc_step);
324       DEBUG('l_element_type_id: ' || l_element_type_id);
325     END IF;
326     OPEN csr_get_ele_ent_details(l_element_type_id);
327     FETCH csr_get_ele_ent_details INTO l_rec_ele_ent_details;
328     debug('l_rec_ele_ent_details.effective_start_date:'||l_rec_ele_ent_details.effective_start_date);
329     -- We are only interested in the latest pension scheme
330     -- membership details
331     IF csr_get_ele_ent_details%FOUND AND l_eff_start_date < l_rec_ele_ent_details.effective_start_date THEN
332       l_pension_category                                 := p_tab_pen_sch_map_cv(i).pcv_information2;
333       l_eff_start_date                                   := l_rec_ele_ent_details.effective_start_date;
334       --valid only if partnerhip scheme is partner
335       IF g_debug THEN
336         l_proc_step := 30;
337         DEBUG('l_pension_category: ' || l_pension_category);
338         DEBUG( 'l_eff_start_date: ' || TO_CHAR(l_eff_start_date, 'DD/MON/YYYY') );
339         DEBUG(l_proc_name, l_proc_step);
340       END IF;
341     END IF; -- cursor found check ...
342     CLOSE csr_get_ele_ent_details;
343     i := p_tab_pen_sch_map_cv.NEXT(i);
344   END LOOP;
345   IF g_debug THEN
346     l_proc_step := 40;
347     DEBUG('l_eff_start_date: ' || TO_CHAR(l_eff_start_date, 'DD/MON/YYYY'));
348     DEBUG('l_pension_category: ' || l_pension_category);
349     DEBUG( 'l_rec_ele_ent_details.element_entry_id: ' || l_rec_ele_ent_details.element_entry_id );
350     DEBUG( 'l_rec_ele_ent_details.effective_start_date: ' || l_rec_ele_ent_details.effective_start_date );
351     DEBUG( 'l_rec_ele_ent_details.effective_end_date: ' || l_rec_ele_ent_details.effective_end_date );
352     DEBUG('l_element_type_id: ' || l_element_type_id);
353     debug_exit(l_proc_name);
354   END IF;
355   p_rec_ele_ent_details := l_rec_ele_ent_details;
356   --p_partnership_scheme     :=l_partnership_scheme;
357   RETURN l_pension_category;
358 EXCEPTION
359 WHEN OTHERS THEN
360   clear_cache;
361   IF SQLCODE <> hr_utility.hr_error_number THEN
362     debug_others(l_proc_name, l_proc_step);
363     IF g_debug THEN
364       DEBUG('Leaving: ' || l_proc_name, -999);
365     END IF;
366     fnd_message.raise_error;
367   ELSE
368     RAISE;
369   END IF;
370 END get_pen_scheme_memb;
371 -- This procedure gets the pension scheme of the assignment
372 -- ----------------------------------------------------------------------------
373 -- |----------------------------< set_sch_globals >------------------------------|
374 -- ----------------------------------------------------------------------------
375 PROCEDURE set_sch_globals(
376     p_business_group_id IN NUMBER ,
377     p_assignment_id     IN NUMBER ,
378     p_effective_date    IN DATE )
379 IS
380   l_index     NUMBER;
381   l_proc_name VARCHAR2(72) := g_proc_name||'set_wps_history_globals';
382   -- l_element_type_id     NUMBER := NULL;
383 BEGIN
384   debug_enter(l_proc_name);
385   -- set global business group id
386   g_business_group_id := p_business_group_id;
387   g_legislation_code  := 'GB';
388   fetch_pension_scheme_map_cv( p_business_group_id => g_business_group_id ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv );
389   -- store in global, to be used in periodic criteria
390   debug('g_legislation_code: '||g_legislation_code,10);
391   debug('g_business_group_id: '||g_business_group_id,20);
392   debug('p_effective_date: '||p_effective_date,30);
393   debug_exit(l_proc_name);
394 EXCEPTION
395 WHEN OTHERS THEN
396   IF SQLCODE <> hr_utility.hr_error_number THEN
397     debug_others (l_proc_name, 10);
398     IF g_debug THEN
399       DEBUG ( 'Leaving: ' || l_proc_name, -999);
400     END IF;
401     fnd_message.raise_error;
402   ELSE
403     RAISE;
404   END IF;
405 END set_sch_globals;
406 PROCEDURE set_assignment_globals(
407     p_assignment_id  IN NUMBER ,
408     p_effective_date IN DATE )
409 IS
410   l_proc_name VARCHAR2(72) := g_proc_name||'.set_assignment_globals';
411 BEGIN -- set_assignment_globals
412   debug_enter(l_proc_name);
413   debug('Inputs are: ',10);
414   debug('p_assignment_id: '||p_assignment_id,10);
415   debug('p_effective_date: '||TO_CHAR(p_effective_date,'dd/mm/yyyy'),10);
416   -- set the global events table
417   g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
418   -- set global assignment_id
419   g_assignment_id := p_assignment_id;
420   debug('g_assignment_id: '||g_assignment_id,20);
421   debug('now calling PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob ',30);
422   PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob();
423   g_is_terminated := 'N';
424   debug_exit(l_proc_name);
425 EXCEPTION
426 WHEN OTHERS THEN
427   IF SQLCODE <> hr_utility.hr_error_number THEN
428     debug_others (l_proc_name, 10);
429     IF g_debug THEN
430       DEBUG ( 'Leaving: ' || l_proc_name, -999);
431     END IF;
432     fnd_message.raise_error;
433   ELSE
434     RAISE;
435   END IF;
436 END set_assignment_globals;
437 
438 -- This procedure fetches the old and new percentage contribution based upon
439 -- the contractual pay change
440 -- ----------------------------------------------------------------------------
441 -- |----------------------------< get_cont_percentage_change >------------------------------|
442 -- ----------------------------------------------------------------------------
443 PROCEDURE get_cont_percentage_change(
444     p_old_value         IN NUMBER ,
445     p_new_value         IN NUMBER ,
446     p_user_table_name   IN VARCHAR2 ,
447     p_user_table_column IN VARCHAR2 ,
448     p_per_change OUT nocopy NUMBER,
449     p_per_new_percentage OUT nocopy NUMBER 	)
450 IS
451   l_old_percentage NUMBER;
452   l_new_percentage NUMBER;
453   l_effective_start_date date:=fnd_date.canonical_to_date('2012/04/01 00:00:00');
454 BEGIN
455   debug('start of get_cont_percentage_change');
456   debug('p_user_table_name:'||p_user_table_name);
457   debug('p_user_table_column:'||p_user_table_column);
458   l_old_percentage := hruserdt.get_table_value (p_bus_group_id =>g_business_group_id, p_table_name => p_user_table_name,
459        p_col_name => p_user_table_column, p_row_value => p_old_value, p_effective_date => l_effective_start_date);
460   l_new_percentage := hruserdt.get_table_value (p_bus_group_id =>g_business_group_id, p_table_name =>p_user_table_name,
461        p_col_name =>p_user_table_column, p_row_value =>p_new_value, p_effective_date =>l_effective_start_date);
462   debug('old percentage is:'||l_old_percentage);
463   debug('new percentage is:'||l_new_percentage);
464   debug('p_per_change:'||p_per_change);
465   p_per_change     := l_new_percentage-l_old_percentage;
466   p_per_new_percentage:= l_new_percentage;
467   IF (p_per_change)<>0 THEN
468     NULL; -- there is a change, hence is to be reported
469   END IF;
470   debug('end of get_cont_percentage_change');
471 END get_cont_percentage_change;
472 PROCEDURE get_old_and_new_values(
473     p_input_value IN VARCHAR2 ,
474     p_old_value OUT nocopy NUMBER ,
475     p_new_value OUT nocopy NUMBER )
476 IS
477   l_old_value    VARCHAR2(30);
478   l_length       NUMBER;
479   l_new_value    VARCHAR2(30);
480   l_del_position NUMBER;
481 BEGIN
482   SELECT instr(p_input_value,'->'),
483     LENGTH(p_input_value)
484   INTO l_del_position,
485     l_length
486   FROM dual;
487   SELECT decode(trim(SUBSTR(p_input_value,0,l_del_position-1)),'<null>','0',to_number(trim(SUBSTR(p_input_value,0,l_del_position-1)))),
488     decode(trim(SUBSTR(p_input_value,l_del_position +3,l_length-1)),'<null>','0',
489     to_number(SUBSTR(p_input_value,l_del_position                 +3,l_length-1)))
490   INTO l_old_value,
491     l_new_value
492   FROM dual;
493   p_new_value:= to_number(l_new_value);
494   p_old_value:= to_number(l_old_value);
495   debug('after coming out from get_old_and_new_values p_new_value:'||p_new_value);
496   debug('after coming out from get_old_and_new_values p_old_value:'||p_old_value);
497   debug('leaving get_old_and_new_values');
498 END get_old_and_new_values;
499 --SCH Cutover Criteria
500 -- ----------------------------------------------------------------------------
501 -- |----------------------------< chk_sch_contri_his_cut_crit >------------------------------|
502 -- ----------------------------------------------------------------------------
503 FUNCTION chk_sch_contri_his_cut_crit(
504     p_business_group_id IN NUMBER ,
505     p_effective_date    IN DATE ,
506     p_assignment_id     IN NUMBER )
507   RETURN VARCHAR2
508 IS
509   l_curr_evt_index    NUMBER;
510   l_dated_table_id    NUMBER;
511   l_chg_table         VARCHAR2(61);
512   l_chg_column_name   VARCHAR2(61);
513   l_return            VARCHAR2(1) := 'N';
514   l_chg_surrogate_key NUMBER;
515   l_update_type       VARCHAR2(30);
516   l_chg_value         VARCHAR2(70);
517   l_old_value         NUMBER;
518   l_new_value         NUMBER;
519   l_percent_change    NUMBER(10,2);
520   l_new_percent       NUMBER(10,2);
521   l_contribution_type VARCHAR2(100);
522   l_scheme_name       VARCHAR2(100);
523   l_rec_ele_ent_details r_ele_ent_details;
524   l_count_change NUMBER;
525   l_change_record csr_lgps_pension_change%rowtype;
526   l_proc_name VARCHAR2(61):= g_proc_name||'chk_sch_contri_his_cut_crit';
527 BEGIN
528   debug('Globals setting');
529   debug('g_ext_dfn_id:'||g_ext_dfn_id);
530   debug('g_paypoint:'||g_paypoint);
531   debug('g_cutover_date:'||g_cutover_date);
532   PQP_GB_PSI_FUNCTIONS.set_shared_globals (p_business_group_id => p_business_group_id ,p_paypoint => g_paypoint -- OUT
533   ,p_cutover_date => g_cutover_date                                                                             -- OUT
534   ,p_ext_dfn_id => g_ext_dfn_id                                                                                 -- OUT
535   );
536   set_sch_globals ( p_business_group_id => p_business_group_id ,p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date );
537   debug('Globals setting for assignment');
538   set_assignment_globals ( p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date );
539   l_return     := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria (p_business_group_id => p_business_group_id ,
540   p_effective_date => p_effective_date ,p_assignment_id => p_assignment_id ,p_person_dtl => g_person_dtl ,p_assignment_dtl => g_assignment_dtl );
541   g_current_run:= 'CUTOVER';
542   debug('New scheme contribution periodic criteria:'||p_assignment_id);
543   debug('after current event processed function');
544   l_scheme_name    := get_pen_scheme_memb( p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv ,p_rec_ele_ent_details => l_rec_ele_ent_details );
545   IF l_scheme_name IS NOT NULL THEN
546     l_return       := 'Y';
547   ELSE
548     l_return := 'N';
549   END IF;
550 
551   IF l_return           = 'Y' THEN
552   l_count_change:=0;
553    debug('cutover criteria:cutover date is:'||g_cutover_date);
554     open csr_lgps_pension_change(p_assignment_id, g_cutover_date);
555        fetch csr_lgps_pension_change into l_change_record;
556        if csr_lgps_pension_change%notfound then
557             l_return := 'N';
558             --return l_return;
559        else
560              l_return :='Y';
561        end if;
562      close csr_lgps_pension_change;
563 
564   /*
565     g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
566     debug('count is:'||g_pay_proc_evt_tab.COUNT);
567     l_curr_evt_index    := ben_ext_person.g_chg_pay_evt_index;
568     l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
569     debug('l_chg_surrogate_key:'||l_chg_surrogate_key);
570     debug('g_pay_proc_evt_tab(l_curr_evt_index).update_type:'||g_pay_proc_evt_tab(l_curr_evt_index).update_type);
571     l_update_type     := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
572     l_dated_table_id  := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
573     l_chg_column_name := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
574     l_chg_table       := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
575     l_chg_value       := g_pay_proc_evt_tab(l_curr_evt_index).change_values;
576     debug('----------');
577     debug('Record :'||l_curr_evt_index);
578     debug('----------');
579     debug('dated_table_id    :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
580     debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
581     debug('surrogate_key     :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
582     debug('column_name       :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
583     debug('update_type       :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
584     debug('effective_date    :'||TO_CHAR(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
585     debug('actual_date       :'||TO_CHAR(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
586     debug('old_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
587     debug('new_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
588     debug('change_values     :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
589     debug('proration_type    :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
590     debug('change_mode       :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
591     debug('l_chg_value:'||l_chg_value);
592     debug('calling function to check the pension scheme of the person:'||g_pension_element_type_id);
593     --calling function to check the pension scheme of the person
594     debug('calling function to check the pension scheme of the person:'||g_pension_element_type_id);
595     IF l_update_type ='C' THEN
596       get_old_and_new_values( l_chg_value ,l_old_value ,l_new_value );
597     elsif l_update_type ='U' THEN
598       l_old_value      := g_pay_proc_evt_tab(l_curr_evt_index).old_value;
599       l_new_value      := g_pay_proc_evt_tab(l_curr_evt_index).new_value;
600     END IF;
601     get_cont_percentage_change( p_old_value =>l_old_value ,p_new_value => l_new_value ,p_user_table_name => 'PQP_PCSPS_OTHER_SCHEMES_CONTRIBUTION_RATES' ,p_user_table_column => 'Employee Extra Contribution' ,p_per_change =>l_percent_change
602   	,p_per_new_percentage=>l_new_percent);
603     debug('l_percent_change:'||l_percent_change);
604     IF l_percent_change <> 0 THEN
605       debug('percentage change is non zero hence adding it into include_event');
606       l_return := pqp_gb_psi_functions.include_event (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date );
607     ELSE -- no change in percentage, hence no need to report this event
608       l_return := 'N';
609       debug('percentage change is zero hence no need to include this event');
610     END IF;
611     debug('leaving '||l_proc_name);
612 	*/
613   END IF;-- end of l_return checn
614   RETURN l_return;
615 END chk_sch_contri_his_cut_crit;
616 --SCH Periodic Criteria
617 -- ----------------------------------------------------------------------------
618 -- |----------------------------< chk_sch_contri_hist_per_crit >------------------------------|
619 -- ----------------------------------------------------------------------------
620 FUNCTION chk_sch_contri_hist_per_crit(
621     p_business_group_id IN NUMBER ,
622     p_effective_date    IN DATE ,
623     p_assignment_id     IN NUMBER )
624   RETURN VARCHAR2
625 IS
626   l_curr_evt_index    NUMBER;
627   l_dated_table_id    NUMBER;
628   l_chg_table         VARCHAR2(61);
629   l_chg_column_name   VARCHAR2(61);
630   l_return            VARCHAR2(1) := 'N';
631   l_chg_surrogate_key NUMBER;
632   l_update_type       VARCHAR2(30);
633   l_chg_value         VARCHAR2(70);
634   l_old_value         NUMBER;
635   l_new_value         NUMBER;
636   l_percent_change    NUMBER(10,2);
637   l_new_percent       NUMBER(10,2);
638   l_contribution_type VARCHAR2(100);
639   l_scheme_name       VARCHAR2(100);
640   l_change_record csr_lgps_pension_change%rowtype;
641   l_rec_ele_ent_details r_ele_ent_details;
642   l_proc_name VARCHAR2(61):= g_proc_name||'chk_sch_contri_his_per_crit';
643 BEGIN
644   debug('Globals setting');
645   debug('g_ext_dfn_id:'||g_ext_dfn_id);
646   debug('g_paypoint:'||g_paypoint);
647   debug('g_cutover_date:'||g_cutover_date);
648   PQP_GB_PSI_FUNCTIONS.set_shared_globals (p_business_group_id => p_business_group_id ,p_paypoint => g_paypoint -- OUT
649   ,p_cutover_date => g_cutover_date                                                                             -- OUT
650   ,p_ext_dfn_id => g_ext_dfn_id                                                                                 -- OUT
651   );
652   set_sch_globals ( p_business_group_id => p_business_group_id ,p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date );
653   debug('Globals setting for assignment');
654   set_assignment_globals ( p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date );
655   l_return     := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria (p_business_group_id => p_business_group_id ,
656    p_effective_date => p_effective_date ,p_assignment_id => p_assignment_id ,p_person_dtl => g_person_dtl ,p_assignment_dtl => g_assignment_dtl );
657   g_current_run:= 'PERIODIC';
658   debug('New scheme contribution periodic criteria:'||p_assignment_id);
659   IF is_curr_evt_processed() THEN
660     l_return := 'N';
661     debug('Returning : '||l_return,20);
662     debug_exit(l_proc_name);
663     --return l_return;
664   END IF;
665   debug('after current event processed function');
666   l_scheme_name    := get_pen_scheme_memb( p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date ,p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv ,p_rec_ele_ent_details => l_rec_ele_ent_details );
667   IF l_scheme_name IS NOT NULL THEN
668     l_return       := 'Y';
669   ELSE
670     l_return := 'N';
671   END IF;
672   IF l_return           = 'Y' THEN
673     g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
674     debug('count is:'||g_pay_proc_evt_tab.COUNT);
675     l_curr_evt_index    := ben_ext_person.g_chg_pay_evt_index;
676     l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
677     l_update_type       := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
678     l_dated_table_id    := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
679     l_chg_column_name   := g_pay_proc_evt_tab(l_curr_evt_index).column_name;
680     l_chg_table         := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
681     l_chg_value         := g_pay_proc_evt_tab(l_curr_evt_index).change_values;
682     debug('----------');
683     debug('Record :'||l_curr_evt_index);
684     debug('----------');
685     debug('dated_table_id    :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
686     debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
687     debug('surrogate_key     :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
688     debug('column_name       :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
689     debug('update_type       :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
690     debug('effective_date    :'||TO_CHAR(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
691     debug('actual_date       :'||TO_CHAR(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
692     debug('old_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
693     debug('new_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
694     debug('change_values     :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
695     debug('proration_type    :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
696     debug('change_mode       :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
697     debug('l_chg_value:'||l_chg_value);
698     IF l_update_type ='C' THEN
699       get_old_and_new_values( l_chg_value ,l_old_value ,l_new_value );
700     elsif l_update_type ='U' THEN
701       l_old_value      := g_pay_proc_evt_tab(l_curr_evt_index).old_value;
702       l_new_value      := g_pay_proc_evt_tab(l_curr_evt_index).new_value;
703     END IF;
704     get_cont_percentage_change( p_old_value =>l_old_value ,p_new_value => l_new_value ,p_user_table_name => 'PQP_PCSPS_OTHER_SCHEMES_CONTRIBUTION_RATES' ,p_user_table_column => 'Employee Extra Contribution' ,p_per_change =>l_percent_change
705 	,p_per_new_percentage=>l_new_percent);
706     debug('l_percent_change:'||l_percent_change);
707     IF l_percent_change <> 0 THEN
708       debug('There is a change in percentage contribution, hence adding it into include_event');
709       l_return := pqp_gb_psi_functions.include_event (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date );
710     ELSE -- no change in percentage, hence no need to report this event
711       l_return := 'N';
712       debug('percentage change is zero hence no need to include this event');
713     END IF;
714     debug('leaving '||l_proc_name);
715   END IF;-- end of if l_return
716   RETURN l_return;
717 END chk_sch_contri_hist_per_crit;
718 FUNCTION get_schPercent(
719     p_business_group_id IN NUMBER ,
720     p_effective_date    IN DATE ,
721     p_assignment_id     IN NUMBER )
722   RETURN VARCHAR2
723 IS
724   l_proc_name            VARCHAR2(61):= g_proc_name||'get_schPercent';
725   l_value                NUMBER;
726   l_effective_start_date VARCHAR2(60);
727   l_effective_end_date   VARCHAR2(60);
728   l_element_entry_id     NUMBER;
729   l_chg_surrogate_key    NUMBER;
730   l_update_type          VARCHAR2(5);
731   l_curr_evt_index       NUMBER;
732   l_effective_date DATE;
733   sch_percent VARCHAR2(60);
734   l_start_date DATE;
735   l_end_date DATE;
736   l_return          VARCHAR2(2);
737   l_dated_table_id  NUMBER;
738   l_chg_table       VARCHAR2(61);
739   l_chg_value       VARCHAR2(61);
740   l_old_value       NUMBER;
741   l_new_value       NUMBER;
742   l_percent_change  NUMBER(10,2);
743   l_new_percent     NUMBER(10,2);
744   l_user_table_name VARCHAR2(100);
745   l_rec_ele_ent_details r_ele_ent_details;
746   l_scheme_name VARCHAR2(30);
747   l_change_record csr_lgps_pension_change%rowtype;
748 BEGIN
749   debug('entering '||l_proc_name);
750   l_scheme_name:= get_pen_scheme_memb( p_assignment_id => p_assignment_id ,p_effective_date => p_effective_date ,
751     p_tab_pen_sch_map_cv => g_tab_pen_sch_map_cv ,p_rec_ele_ent_details => l_rec_ele_ent_details );
752    if  g_current_run = 'PERIODIC' then
753     g_pay_proc_evt_tab  := ben_ext_person.g_pay_proc_evt_tab;
754     l_curr_evt_index    := ben_ext_person.g_chg_pay_evt_index;
755     l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
756     l_update_type       := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
757     l_dated_table_id    := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
758     l_chg_table         := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
759     l_chg_value         := g_pay_proc_evt_tab(l_curr_evt_index).change_values;
760     debug('update type:'||l_update_type);
761     debug('l_dated_table_id:'||l_dated_table_id);
762     debug('l_chg_table:'||l_chg_table);
763     debug('old_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
764     debug('new_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
765     debug('change_values     :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
766     IF l_chg_table  = 'PQP_ASSIGNMENT_ATTRIBUTES_F' THEN
767 
768      debug('scheme returned by PQP_GB_PSI_FUNCTIONS.check_employee_pension_scheme function:'||l_scheme_name);
769      debug('l_update_type:'||l_update_type);
770      IF l_update_type = 'C' THEN
771        get_old_and_new_values( l_chg_value ,l_old_value ,l_new_value ); -- get the value from pattern matching code
772        debug('old_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
773        debug('new_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
774        debug('change_values     :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
775      elsif l_update_type = 'U' THEN
776       debug('old_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
777       debug('new_value         :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
778       debug('change_values     :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
779       l_old_value:= g_pay_proc_evt_tab(l_curr_evt_index).old_value;
780       l_new_value:= g_pay_proc_evt_tab(l_curr_evt_index).new_value;
781      END IF;
782     END IF;
783   elsif  g_current_run = 'CUTOVER' then
784    open csr_lgps_pension_change(p_assignment_id, g_cutover_date);
785       fetch csr_lgps_pension_change into l_change_record;
786           l_new_value:=l_change_record.pensionable_pay;
787 		  l_old_value:='0';
788           debug('the date is:'||g_cutover_date);
789           l_old_value:='0';
790           debug('l_new_value:'||l_new_value);
791           get_cont_percentage_change( p_old_value =>l_old_value ,p_new_value => l_new_value ,
792 				   p_user_table_name => 'PQP_PCSPS_OTHER_SCHEMES_CONTRIBUTION_RATES' ,
793 			     p_user_table_column => 'Employee Extra Contribution' ,p_per_change =>l_percent_change  ,p_per_new_percentage=>l_new_percent);
794 	close csr_lgps_pension_change;
795   end if;
796   IF l_scheme_name IS NOT NULL THEN
797     get_cont_percentage_change( p_old_value =>l_old_value ,p_new_value => l_new_value ,p_user_table_name => 'PQP_PCSPS_OTHER_SCHEMES_CONTRIBUTION_RATES' ,p_user_table_column => 'Employee Extra Contribution' ,p_per_change =>l_percent_change
798      ,p_per_new_percentage=>l_new_percent);
799     IF l_scheme_name = 'CLASSIC' THEN              --classic scheme
800       NULL;                                        -- ONLY REPORT EXTRA CONTRIBUTION
801     ELSIF l_scheme_name IS NOT NULL THEN           -- schemes other than classic
802       l_new_percent  := l_new_percent + 3.5; --STANDARD Contribution been added to the extra CONTRIBUTION VALUE
803     END IF;
804   ELSE
805     -- this scenario will not come, as employee with no pension scheme will not be picked even
806     debug('Not a member of CS--------Employee is attached to no schemes.');
807   END IF;
808   debug('Contribution percentage value is:'||l_new_percent);
809   sch_percent := l_new_percent;
810   debug('leaving :'||l_proc_name);
811   RETURN sch_percent;
812 END get_schPercent;
813 FUNCTION getContributionStartDate(
814     p_business_group_id IN NUMBER -- context
815     ,
816     p_effective_date IN DATE -- context
817     ,
818     p_assignment_id IN NUMBER -- context
819   )
820   RETURN VARCHAR2
821 IS
822   p_effective_start_date VARCHAR2(12);
823   l_curr_evt_index       NUMBER;
824   l_change_record csr_lgps_pension_change%rowtype;
825   l_proc_name            VARCHAR2(61):= g_proc_name||'getContributionStartDate';
826 BEGIN
827   debug('entering '||l_proc_name);
828   if  g_current_run = 'PERIODIC' then
829    g_pay_proc_evt_tab    := ben_ext_person.g_pay_proc_evt_tab;
830    l_curr_evt_index      := ben_ext_person.g_chg_pay_evt_index;
831    p_effective_start_date:= TO_CHAR(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY');
832   elsif  g_current_run = 'CUTOVER' then
833     open csr_lgps_pension_change(p_assignment_id, g_cutover_date);
834     fetch csr_lgps_pension_change into l_change_record;
835          p_effective_start_date:=l_change_record.effective_start_date;
836    close csr_lgps_pension_change;
837   end if;
838   debug('leaving '||l_proc_name);
839   RETURN p_effective_start_date;
840    EXCEPTION
841     WHEN others THEN
842         IF SQLCODE <> hr_utility.hr_error_number
843         THEN
844             debug_others (l_proc_name, 10);
845             IF g_debug
846             THEN
847               DEBUG (   'Leaving: '
848                      || l_proc_name, -999);
849              END IF;
850              fnd_message.raise_error;
851          ELSE
852              RAISE;
853          END IF;
854 
855 END getContributionStartDate;
856 
857 --SCH Data Element Values Fetching
858 -- ----------------------------------------------------------------------------
859 -- |----------------------------< sch_contrib_history_main >------------------------------|
860 -- ----------------------------------------------------------------------------
861 FUNCTION sch_contrib_history_main(
862     p_business_group_id IN NUMBER -- context
863     ,
864     p_effective_date IN DATE -- context
865     ,
866     p_assignment_id IN NUMBER -- context
867     ,
868     p_rule_parameter IN VARCHAR2 -- parameter
869     ,
870     p_output OUT NOCOPY VARCHAR2 )
871   RETURN NUMBER
872 IS
873   l_proc_name         VARCHAR2(61):= g_proc_name||'getContributionStartDate';
874   l_proc_step PLS_INTEGER;
875   l_curr_evt_index    NUMBER;
876   l_dated_table_id    NUMBER;
877   l_chg_table         VARCHAR2(61);
878   l_chg_column_name   VARCHAR2(61);
879   l_return            VARCHAR2(1) := 'N';
880   l_chg_surrogate_key NUMBER;
881   l_update_type       VARCHAR2(30);
882   l_effective_date    DATE;
883 BEGIN
884   debug('Entering '||g_proc_name);
885   if  g_current_run = 'PERIODIC' then
886    g_pay_proc_evt_tab  := ben_ext_person.g_pay_proc_evt_tab;
887    l_curr_evt_index    := ben_ext_person.g_chg_pay_evt_index;
888    l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
889    l_update_type       := g_pay_proc_evt_tab(l_curr_evt_index).update_type;
890    l_dated_table_id    := g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id;
891    l_chg_table         := pqp_gb_psi_functions.get_dated_table_name(l_dated_table_id);
892    debug('l_curr_evt_index:'||l_curr_evt_index,20);
893    debug('l_chg_surrogate_key         :'||l_chg_surrogate_key ,20);
894    debug('l_update_type         :'||l_update_type ,20);
895    debug('l_dated_table_id     :'||l_dated_table_id ,20);
896    debug('l_chg_table    :'||l_chg_table ,20);
897    debug('change_mode       :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
898   l_effective_date:= p_effective_date;
899   elsif g_current_run = 'CUTOVER' then
900      l_effective_date:= g_cutover_date;
901   end if;
902   IF p_rule_parameter    = 'StartDate' THEN
903     p_output            := getContributionStartDate (p_business_group_id => p_business_group_id ,p_effective_date => l_effective_date ,p_assignment_id => p_assignment_id );
904   ELSIF p_rule_parameter = 'Percentage' THEN
905     p_output            := get_schPercent (p_business_group_id =>p_business_group_id ,p_effective_date =>l_effective_date ,p_assignment_id =>p_assignment_id );
906   ELSIF p_rule_parameter = 'Filler' THEN
907     p_output:=NULL;
908   END IF;
909   debug('Leaving '||g_proc_name);
910   RETURN 0;
911   EXCEPTION
912   WHEN OTHERS THEN
913   --clear_cache;
914   IF SQLCODE <> hr_utility.hr_error_number THEN
915     debug_others(l_proc_name, l_proc_step);
916     IF g_debug THEN
917       DEBUG('Leaving: ' || l_proc_name, -999);
918     END IF;
919     fnd_message.raise_error;
920   ELSE
921     RAISE;
922   END IF;
923 END sch_contrib_history_main;
924 FUNCTION sch_cont_history_post_process
925   RETURN VARCHAR2
926 IS
927   l_proc_name VARCHAR2(80) := g_proc_name || 'sch_cont_history_post_process';
928   l_proc_step PLS_INTEGER;
929   l_return_value VARCHAR2(100);
930   --
931 BEGIN
932   --
933   IF g_debug THEN
934     l_proc_step := 10;
935     debug_enter(l_proc_name);
936   END IF;
937   pqp_gb_psi_functions.common_post_process(p_business_group_id => g_business_group_id);
938   IF g_debug THEN
939     l_proc_step := 20;
940     debug_exit(l_proc_name);
941   END IF;
942   RETURN l_return_value;
943 EXCEPTION
944 WHEN OTHERS THEN
945   --clear_cache;
946   IF SQLCODE <> hr_utility.hr_error_number THEN
947     debug_others(l_proc_name, l_proc_step);
948     IF g_debug THEN
949       DEBUG('Leaving: ' || l_proc_name, -999);
950     END IF;
951     fnd_message.raise_error;
952   ELSE
953     RAISE;
954   END IF;
955 END sch_cont_history_post_process;
956 END PQP_GB_PSI_SCH;