[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_SS_ABSENCE_TEMPLATE
Source
1 PACKAGE BODY pqp_gb_ss_absence_template AS
2 -- $Header: pqpgbabd.pkb 120.1 2005/09/30 05:38:23 rrazdan noship $
3 --
4 -- Define global Variables / Cursors
5 -- **********************************
6 --
7 g_proc_name varchar2(31) := 'pqp_gb_ss_absence_template.' ;
8 -- Cursor to fill the absence type table from lookup
9 --
10 cursor g_c_get_lookupdata
11 (P_LOOKUP_TYPE fnd_lookup_values_vl.lookup_type%TYPE
12 ,P_EFFECTIVE_START_DATE fnd_lookup_values_vl.start_date_active%TYPE
13 )
14 IS
15 select lookup_code abs_type_id,meaning abs_type_name
16 from fnd_lookup_values_vl
17 where lookup_type = P_LOOKUP_TYPE
18 and enabled_flag = 'Y'
19 and P_EFFECTIVE_START_DATE BETWEEN
20 nvl(start_date_active,P_EFFECTIVE_START_DATE)
21 AND nvl(end_date_active,P_EFFECTIVE_START_DATE);
22
23 --
24 -- Procedure to compile the formulas including the
25 -- BEN formulas created and compiled only once for
26 -- the primary base name
27 --
28 procedure compile_formulas
29 (P_ELEMENT_TYPE_ID IN NUMBER
30 ,P_ABS_PRIMARY_YN IN VARCHAR2
31 ,P_EFF_START_DATE IN DATE
32 ,P_BASE_NAME IN VARCHAR2
33 ,P_ABSENCE_TYPE IN VARCHAR2
34 ,P_BG_ID IN NUMBER
35 ,P_REQUEST_ID OUT NOCOPY NUMBER
36 )
37 IS
38 --
39 -- Cursor to get the formula details necessary to compile
40 --
41 CURSOR c_get_formula_info(p_element_type_id NUMBER)
42 IS
43 SELECT
44 fra.formula_id,
45 fra.formula_name,
46 fty.formula_type_id,
47 fty.formula_type_name
48 FROM
49 ff_formulas_f fra,
50 ff_formula_types fty,
51 pay_status_processing_rules_f spr
52 WHERE fty.formula_type_id = fra.formula_type_id
53 AND fra.formula_id = spr.formula_id
54 AND spr.assignment_status_type_id IS NULL
55 AND spr.element_type_id = p_element_type_id;
56
57 --
58 -- Cursor to get the BEN formula details necessary to compile
59 --
60 CURSOR c_get_ben_formula_info(p_formula_id NUMBER)
61 IS
62 SELECT
63 fra.formula_id,
64 fra.formula_name,
65 fty.formula_type_id,
66 fty.formula_type_name
67 FROM
68 ff_formulas_f fra,
69 ff_formula_types fty
70 WHERE fty.formula_type_id = fra.formula_type_id
71 AND fra.formula_id = p_formula_id;
72
73 l_ff_id ff_formulas_f.formula_id%TYPE;
74 l_ftype_id ff_formula_types.formula_type_id%TYPE;
75 l_ff_name ff_formulas_f.formula_name%TYPE;
76 l_ftype_name ff_formula_types.formula_type_name%TYPE;
77 l_request_id number;
78 l_ben_formulas pqp_gb_gap_ben_formulas.t_formulas;
79 l_error_code number;
80 l_err_msg VARCHAR2(100);
81 BEGIN
82 -- Query formula info (ie. the formula attached to this
83 -- element's Standard status proc rule.
84 --
85 OPEN c_get_formula_info(p_element_type_id);
86 FETCH c_get_formula_info INTO l_ff_id
87 ,l_ff_name
88 ,l_ftype_id
89 ,l_ftype_name;
90 CLOSE c_get_formula_info;
91 hr_utility.trace('FF Name :'||l_ff_name);
92
93 --
94 -- Submitt the request to compile the formula
95 --
96 l_request_id := fnd_request.submit_request
97 (application => 'FF'
98 ,program => 'SINGLECOMPILE'
99 ,argument1 => l_ftype_name --'Oracle Payroll' -- formula type
100 ,argument2 => l_ff_name); -- formula name
101
102 p_request_id := l_request_id;
103 hr_utility.trace('Request Id :'||p_request_id);
104
105 --hr_utility.trace('P_ABS_PRIMARY_YN :'||P_ABS_PRIMARY_YN);
106 --
107 -- Compile the additional Ben Formulas here only for the Primary Base Name
108 --
109 --IF upper(P_ABS_PRIMARY_YN) = 'Y' THEN
110 --hr_utility.trace('Entered the If Stmt');
111 ----
112 ---- Create the BEN formulas
113 ----
114 --pqp_gb_gap_ben_formulas.create_ben_formulas
115 --(p_business_group_id => p_bg_id
116 --,p_effective_date => P_EFF_START_DATE
117 --,p_absence_pay_plan_category => P_ABSENCE_TYPE
118 --,p_base_name => UPPER(P_BASE_NAME)
119 --,p_formulas => l_ben_formulas
120 --,p_error_code => l_error_code
121 --,p_error_message => l_err_msg
122 --);
123 --
124 --hr_utility.trace('BEN FF Count :'||l_ben_formulas.count);
125 ----
126 ---- Loop to compile all the BEN formulas
127 ----
128 --IF l_ben_formulas.COUNT > 0
129 --THEN
130 --FOR i IN l_ben_formulas.FIRST..l_ben_formulas.LAST
131 --LOOP
132 --hr_utility.trace('l_ben_formulas(i): '||l_ben_formulas(i));
133 --OPEN c_get_ben_formula_info(l_ben_formulas(i));
134 --FETCH c_get_ben_formula_info INTO l_ff_id
135 --,l_ff_name
136 --,l_ftype_id
137 --,l_ftype_name;
138 --CLOSE c_get_ben_formula_info;
139 --hr_utility.trace('FF Name :'||l_ff_name);
140 --hr_utility.trace('FT Name :'||l_ftype_name);
141 ----
142 ---- Submitt request to compile the formula
143 ----
144 --l_request_id := fnd_request.submit_request
145 --(application => 'FF'
146 --,program => 'SINGLECOMPILE'
147 --,argument1 => l_ftype_name --'Oracle Payroll' -- formula type
148 --,argument2 => l_ff_name); -- formula name
149 --hr_utility.trace('Request Id :'||l_request_id);
150 --END LOOP;
151 --END IF;
152 --END IF;
153 -- Added by tmehra for nocopy changes Feb'03
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 hr_utility.trace('Entering exception when others ');
158 p_request_id := NULL;
159 raise;
160
161
162 end compile_formulas;
163
164 --
165 -- Function to get the Security Group Id
166 --
167 Function get_security_grp_id
168 (P_BUSINESS_GROUP_ID IN per_business_groups.business_group_id%TYPE
169 )
170 RETURN per_business_groups.security_group_id%TYPE
171 IS
172
173 --
174 -- Cursor to get the Security Group Id
175 --
176 CURSOR c_get_security_grp_id
177 IS
178 select
179 security_group_id
180 from per_business_groups
181 where business_group_id = P_BUSINESS_GROUP_ID;
182
183 l_security_grp_id per_business_groups.security_group_id%TYPE;
184 BEGIN
185
186 OPEN c_get_security_grp_id;
187 FETCH c_get_security_grp_id INTO l_security_grp_id;
188 CLOSE c_get_security_grp_id;
189 RETURN l_security_grp_id;
190 END get_security_grp_id;
191
192 --
193 -- Procedure called from SS forms to create/ update an OMP template
194 --
195 Procedure Create_omp_template
196 (P_PLAN_ID IN NUMBER
197 ,P_PLAN_DESCRIPTION IN VARCHAR2
198 ,P_ABSE_DAYS_DEF IN VARCHAR2
199 ,P_MATERNITY_ABSE_ENT_UDT IN NUMBER
200 ,P_HOLIDAYS_UDT IN NUMBER
201 ,P_DAILY_RATE_CALC_METHOD IN VARCHAR2
202 ,P_DAILY_RATE_CALC_PERIOD IN VARCHAR2
203 ,P_DAILY_RATE_CALC_DIVISOR IN NUMBER
204 ,P_WORKING_PATTERN IN VARCHAR2
205 ,P_LOS_CALC IN VARCHAR2
206 ,P_LOS_CALC_UOM IN VARCHAR2
207 ,P_LOS_CALC_DURATION IN VARCHAR2
208 ,P_AVG_EARNINGS_DURATION IN VARCHAR2
209 ,P_AVG_EARNINGS_UOM IN VARCHAR2
210 ,P_AVG_EARNINGS_BALANCE IN VARCHAR2
211 ,P_PRI_ELE_NAME IN VARCHAR2
212 ,P_PRI_ELE_REPORTING_NAME IN VARCHAR2
213 ,P_PRI_ELE_DESCRIPTION IN VARCHAR2
214 ,P_PRI_ELE_PROCESSING_PRIORITY IN NUMBER
215 ,P_ABSE_PRIMARY_YN IN VARCHAR2
216 ,P_PAY_ELE_REPORTING_NAME IN VARCHAR2
217 ,P_PAY_ELE_DESCRIPTION IN VARCHAR2
218 ,P_PAY_ELE_PROCESSING_PRIORITY IN NUMBER
219 ,P_PAY_SRC_PAY_COMPONENT IN VARCHAR2
220 ,P_BAND1_ELE_BASE_NAME IN VARCHAR2
221 ,P_BAND2_ELE_BASE_NAME IN VARCHAR2
222 ,P_BAND3_ELE_BASE_NAME IN VARCHAR2
223 ,P_BAND4_ELE_BASE_NAME IN VARCHAR2
224 ,P_EFFECTIVE_START_DATE IN DATE
225 ,P_EFFECTIVE_END_DATE IN DATE
226 ,P_ABSE_TYPE_LOOKUP_TYPE IN VARCHAR2
227 ,P_ABSE_TYPE_LOOKUP_VALUE IN pqp_gb_osp_template.t_abs_types
228 ,P_ELEMENT_TYPE_ID OUT NOCOPY NUMBER
229 ,P_REQUEST_ID OUT NOCOPY NUMBER
230 ,P_SECURITY_GROUP_ID IN NUMBER
231 ,P_BG_ID IN NUMBER
232 )
233 IS
234
235 -- Table to pass as param to the create_osp_template proc.
236 l_lookup_table pqp_gb_osp_template.t_abs_types;
237
238 l_element_type_id pay_element_types_f.element_type_id%TYPE;
239 l_sec_grp_id per_business_groups.security_group_id%TYPE;
240 i number;
241 l_proc_name VARCHAR2 (80)
242 := g_proc_name
243 || 'create_omp_template';
244 BEGIN
245
246 -- for Multi Messages
247 hr_multi_message.enable_message_list;
248
249 hr_utility.trace('Entering my proc........');
250 hr_utility.trace('P_ABSE_TYPE_LOOKUP_TYPE :' || P_ABSE_TYPE_LOOKUP_TYPE);
251
252 --
253 -- If the Absence type is null
254 -- then we create a new absence scheme
255 --
256 IF P_ABSE_TYPE_LOOKUP_TYPE IS NULL THEN
257 --
258 -- Fill the Absence types table.
259 --
260 l_lookup_table := P_ABSE_TYPE_LOOKUP_VALUE;
261 ELSE
262 i := 0;
263 FOR r_lookup IN g_c_get_lookupdata
264 (P_LOOKUP_TYPE => P_ABSE_TYPE_LOOKUP_TYPE
265 -- Changed canonical_to_date to displaydate_to_date as the format passed
266 -- not in canonical format.
267 -- ,P_EFFECTIVE_START_DATE => fnd_date.displaydate_to_date(P_EFFECTIVE_START_DATE)
268 ,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
269 )
270 LOOP
271 l_lookup_table(i).abs_type_id:=r_lookup.abs_type_id;
272 l_lookup_table(i).abs_type_name:=r_lookup.abs_type_name;
273 hr_utility.trace('Abs Type Name :'||l_lookup_table(i).abs_type_name);
274 i := i + 1;
275 END LOOP;
276 END IF;
277 l_sec_grp_id := get_security_grp_id(p_bg_id);
278 -- l_sec_grp_id := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
279 hr_utility.trace('l_sec_grp_id :' || l_sec_grp_id);
280 hr_utility.trace('Calling the create_user_template from my proc...');
281
282 l_element_type_id := pqp_gb_omp_template.create_user_template
283 (p_plan_id => P_PLAN_ID
284 ,p_plan_description => P_PLAN_DESCRIPTION
285 ,p_abse_days_def => P_ABSE_DAYS_DEF
286 ,p_maternity_abse_ent_udt => P_MATERNITY_ABSE_ENT_UDT
287 ,p_holidays_udt => P_HOLIDAYS_UDT
288 ,p_daily_rate_calc_method => P_DAILY_RATE_CALC_METHOD
289 ,p_daily_rate_calc_period => P_DAILY_RATE_CALC_PERIOD
290 ,p_daily_rate_calc_divisor => P_DAILY_RATE_CALC_DIVISOR
291 ,p_working_pattern => P_WORKING_PATTERN
292 ,p_los_calc => P_LOS_CALC
293 ,p_los_calc_uom => P_LOS_CALC_UOM
294 ,p_los_calc_duration => P_LOS_CALC_DURATION
295 ,p_avg_earnings_duration => P_AVG_EARNINGS_DURATION
296 ,p_avg_earnings_uom => P_AVG_EARNINGS_UOM
297 ,p_avg_earnings_balance => P_AVG_EARNINGS_BALANCE
298 ,p_pri_ele_name => P_PRI_ELE_NAME
299 ,p_pri_ele_reporting_name => P_PRI_ELE_REPORTING_NAME
300 ,p_pri_ele_description => P_PRI_ELE_DESCRIPTION
301 ,p_pri_ele_processing_priority => P_PRI_ELE_PROCESSING_PRIORITY
302 ,p_abse_primary_yn => P_ABSE_PRIMARY_YN
303 ,p_pay_ele_reporting_name => P_PRI_ELE_REPORTING_NAME
304 ,p_pay_ele_description => P_PRI_ELE_DESCRIPTION
305 -- Pay Element Reporting Name and Description are changed to pass same as
306 -- Absence Element Reporting Name and Description.
307 ,p_pay_ele_processing_priority => P_PAY_ELE_PROCESSING_PRIORITY
308 ,p_pay_src_pay_component => P_PAY_SRC_PAY_COMPONENT
309 ,p_band1_ele_base_name => P_BAND1_ELE_BASE_NAME
310 ,p_band2_ele_base_name => P_BAND2_ELE_BASE_NAME
311 ,p_band3_ele_base_name => P_BAND3_ELE_BASE_NAME
312 ,p_band4_ele_base_name => P_BAND4_ELE_BASE_NAME
313 ,p_effective_start_date => P_EFFECTIVE_START_DATE
314 ,p_effective_end_date => P_EFFECTIVE_END_DATE
315 ,p_abse_type_lookup_type => P_ABSE_TYPE_LOOKUP_TYPE
316 ,p_abse_type_lookup_value => l_lookup_table
317 ,p_security_group_id => l_sec_grp_id --FND_PROFILE.VALUE('SECURITY_GROUP_ID')
318 ,p_bg_id => P_BG_ID
319 );
320
321 p_element_type_id := l_element_type_id;
322 hr_utility.trace('Ele Type Id :'||p_element_type_id);
323
324 --
325 -- Compile and create(BEN) all the Formulas
326 --
327 compile_formulas
328 (P_ELEMENT_TYPE_ID => L_ELEMENT_TYPE_ID
329 ,P_ABS_PRIMARY_YN => P_ABSE_PRIMARY_YN
330 ,P_EFF_START_DATE => P_EFFECTIVE_START_DATE
331 ,P_BASE_NAME => P_PRI_ELE_NAME
332 ,P_ABSENCE_TYPE => 'MATERNITY'
333 ,P_BG_ID => P_BG_ID
334 ,P_REQUEST_ID => P_REQUEST_ID
335 );
336
337 -- Added by tmehra for nocopy changes Feb'03
338
339 EXCEPTION
340 /* WHEN OTHERS THEN
341 hr_utility.trace('Entering exception when others ');
342 p_element_type_id := NULL;
343 p_request_id := NULL;
344 raise;*/
345 WHEN hr_multi_message.error_message_exist
346 THEN
347 --
348 -- Catch the Multiple Message List exception which
349 -- indicates API processing has been aborted because
350 -- at least one message exists in the list.
351 --
352 p_element_type_id := NULL;
353 p_request_id := NULL;
354 hr_utility.set_location ( ' Leaving:'
355 || l_proc_name, 40);
356 WHEN OTHERS
357 THEN
358 --
359 -- When Multiple Message Detection is enabled catch
360 -- any Application specific or other unexpected
361 -- exceptions. Adding appropriate details to the
362 -- Multiple Message List. Otherwise re-raise the
363 -- error.
364 --
365 IF hr_multi_message.unexpected_error_add (l_proc_name)
366 THEN
367 p_element_type_id := NULL;
368 p_request_id := NULL;
369 hr_utility.set_location ( ' Leaving:'
370 || l_proc_name, 50);
371 RAISE;
372 END IF;
373
374 END Create_omp_template;
375
376
377 --
378 -- Procedure to create/update an OSP template
379 --
380 Procedure Create_osp_template
381 (P_PLAN_ID IN NUMBER
382 ,P_PLAN_DESCRIPTION IN VARCHAR2
383 ,P_SCH_CAL_TYPE IN VARCHAR2
384 ,P_SCH_CAL_DURATION IN NUMBER
385 ,P_SCH_CAL_UOM IN VARCHAR2
386 --,P_SCH_CAL_START_DATE IN VARCHAR2
387 ,P_SCH_CAL_START_DATE IN DATE
388 --,P_SCH_CAL_END_DATE IN VARCHAR2
389 ,P_SCH_CAL_END_DATE IN DATE
390 ,P_ABS_DAYS IN VARCHAR2
391 ,P_ABS_ENT_SICK_LEAVES IN NUMBER
392 ,P_ABS_ENT_HOLIDAYS IN NUMBER
393 ,P_ABS_DAILY_RATE_CALC_METHOD IN VARCHAR2
394 ,P_ABS_DAILY_RATE_CALC_PERIOD IN VARCHAR2
395 ,P_ABS_DAILY_RATE_CALC_DIVISOR IN NUMBER
396 ,P_ABS_WORKING_PATTERN IN VARCHAR2
397 ,P_ABS_OVERLAP_RULE IN VARCHAR2
398 ,P_ABS_ELE_NAME IN VARCHAR2
399 ,P_ABS_ELE_REPORTING_NAME IN VARCHAR2
400 ,P_ABS_ELE_DESCRIPTION IN VARCHAR2
401 ,P_ABS_ELE_PROCESSING_PRIORITY IN NUMBER
402 ,P_ABS_PRIMARY_YN IN VARCHAR2
403 ,P_PAY_ELE_REPORTING_NAME IN VARCHAR2
404 ,P_PAY_ELE_DESCRIPTION IN VARCHAR2
405 ,P_PAY_ELE_PROCESSING_PRIORITY IN NUMBER
406 ,P_PAY_SRC_PAY_COMPONENT IN VARCHAR2
407 ,P_BND1_ELE_SUB_NAME IN VARCHAR2
408 ,P_BND2_ELE_SUB_NAME IN VARCHAR2
409 ,P_BND3_ELE_SUB_NAME IN VARCHAR2
410 ,P_BND4_ELE_SUB_NAME IN VARCHAR2
411 --,P_ELE_EFF_START_DATE IN VARCHAR2
412 ,P_ELE_EFF_START_DATE IN DATE
413 --,P_ELE_EFF_END_DATE IN VARCHAR2
414 ,P_ELE_EFF_END_DATE IN DATE
415 ,P_ABS_TYPE_LOOKUP_TYPE IN VARCHAR2
416 ,P_ABS_TYPE_LOOKUP_VALUE IN pqp_gb_osp_template.t_abs_types
417 ,P_ELEMENT_TYPE_ID OUT NOCOPY NUMBER
418 ,P_REQUEST_ID OUT NOCOPY NUMBER
419 ,P_SECURITY_GROUP_ID IN NUMBER
420 ,P_BG_ID IN NUMBER
421 ,P_PLAN_TYPE_LOOKUP_TYPE IN VARCHAR2 --LG
422 ,P_PLAN_TYPE_LOOKUP_VALUE IN pqp_gb_osp_template.t_plan_types --LG
423 ,P_ENABLE_ENT_PRORATION IN VARCHAR2 DEFAULT NULL --LG
424 ,P_SCHEME_TYPE IN VARCHAR2 DEFAULT NULL -- LG
425 ,P_ABS_SCHEDULE_WP IN VARCHAR2 DEFAULT NULL -- LG
426 ,P_DUAL_ROLLING_DURATION IN NUMBER DEFAULT NULL -- LG
427 ,P_DUAL_ROLLING_UOM IN VARCHAR2 DEFAULT NULL -- LG
428 ,P_FT_ROUND_CONFIG IN VARCHAR2 DEFAULT NULL
429 ,P_PT_ROUND_CONFIG IN VARCHAR2 DEFAULT NULL
430 )
431 IS
432
433
434 -- Table to pass as param to the create_osp_template proc.
435 l_lookup_table pqp_gb_osp_template.t_abs_types;
436 l_lookup_table_plan_typ pqp_gb_osp_template.t_plan_types;
437
438 --
439 -- Ref cursor to execute the statement that comes as param
440 --
441 TYPE ref_csr_type IS REF CURSOR;
442 c_cursor ref_csr_type;
443
444 l_element_type_id pay_element_types_f.element_type_id%TYPE;
445 l_abs_type_lookup_type varchar2(100);
446 l_sch_cal_start_date VARCHAR2(11);
447 l_sch_cal_end_date VARCHAR2(11);
448 l_select VARCHAR2(100);
449 l_sec_grp_id per_business_groups.security_group_id%TYPE;
450 l_proc_name VARCHAR2(61) := g_proc_name ||
451 'Create_osp_template' ;
452 i number;
453 BEGIN
454
455 -- for Multi Messages
456 hr_multi_message.enable_message_list;
457
458
459 hr_utility.trace('Entering :'||l_proc_name);
460 hr_utility.trace('P_ABS_TYPE_LOOKUP_TYPE :' || P_ABS_TYPE_LOOKUP_TYPE);
461 hr_utility.trace('P_SCH_CAL_START_DATE : '||P_SCH_CAL_START_DATE);
462 hr_utility.trace('Enable Proration : '||P_ENABLE_ENT_PRORATION);
463 hr_utility.trace('ABS_SHEDWP : '||P_ABS_SCHEDULE_WP);
464 hr_utility.trace('P_FT_ROUND_CONFIG : '||P_FT_ROUND_CONFIG);
465 hr_utility.trace('P_PT_ROUND_CONFIG : '||P_PT_ROUND_CONFIG);
466
467
468
469 --
470 -- If the Absence type is null
471 -- then we create a new absence scheme
472 --
473 IF P_SCH_CAL_START_DATE IS NOT NULL THEN
474
475 l_sch_cal_start_date := p_sch_cal_start_date ;
476 l_sch_cal_end_date := p_sch_cal_end_date ;
477
478 -- l_select := 'select '|| P_SCH_CAL_START_DATE
479 -- || ' from dual';
480 --hr_utility.trace(l_select);
481 --
482 -- Prepare the Calendar start date string
483 --
484 -- open c_cursor for l_select;
485 -- fetch c_cursor into l_sch_cal_start_date;
486 -- close c_cursor;
487
488 hr_utility.trace('1_SCH_CAL_START_DATE : '||l_SCH_CAL_START_DATE);
489
490 hr_utility.trace('P_SCH_CAL_END_DATE : '||P_SCH_CAL_END_DATE);
491 -- l_select := 'select '|| P_SCH_CAL_END_DATE
492 -- || ' from dual';
493 --hr_utility.trace(l_select);
494 --
495 -- Prepare the Calendar end date string
496 --
497 -- open c_cursor for l_select;
498 -- fetch c_cursor into l_sch_cal_end_date;
499 -- close c_cursor;
500
501 hr_utility.trace('1_SCH_CAL_END_DATE : '||l_SCH_CAL_END_DATE);
502 ELSE
503 l_sch_cal_start_date := NULL ;
504 l_sch_cal_end_date := NULL ;
505 END IF;
506
507 IF P_ABS_TYPE_LOOKUP_TYPE IS NULL THEN
508 IF P_SCH_CAL_START_DATE IS NULL THEN
509 l_sch_cal_start_date := NULL;
510 l_sch_cal_end_date := NULL;
511 END IF;
512 --
513 -- Fill the Absence types table.
514 --
515 l_lookup_table := P_ABS_TYPE_LOOKUP_VALUE;
516 ELSE
517 i := 0;
518 FOR r_lookup IN g_c_get_lookupdata
519 (P_LOOKUP_TYPE => P_ABS_TYPE_LOOKUP_TYPE
520 -- Changed canonical_to_date to displaydate_to_date as the format passed
521 -- not in canonical format.
522 -- ,P_EFFECTIVE_START_DATE => fnd_date.displaydate_to_date(P_ELE_EFF_START_DATE)
523 ,P_EFFECTIVE_START_DATE => P_ELE_EFF_START_DATE
524
525 )
526 LOOP
527 l_lookup_table(i).abs_type_id:=r_lookup.abs_type_id;
528 l_lookup_table(i).abs_type_name:=r_lookup.abs_type_name;
529 hr_utility.trace('Abs Type Name :'||l_lookup_table(i).abs_type_name);
530 i := i + 1;
531 END LOOP;
532 END IF;
533 l_sec_grp_id := get_security_grp_id(p_bg_id);
534
535
536 -- Populating lookup for plan type l_lookup_table_plan_typ ---LG
537
538 IF P_PLAN_TYPE_LOOKUP_TYPE IS NOT NULL THEN
539 i := 0;
540 FOR r_lookup IN g_c_get_lookupdata
541 (P_LOOKUP_TYPE => P_PLAN_TYPE_LOOKUP_TYPE
542 ,P_EFFECTIVE_START_DATE => P_ELE_EFF_START_DATE
543 )
544 LOOP
545 l_lookup_table_plan_typ(i).plan_type_id:=r_lookup.abs_type_id;
546 l_lookup_table_plan_typ(i).name:=r_lookup.abs_type_name;
547 hr_utility.trace('Abs Type Name :'||l_lookup_table_plan_typ(i).name);
548 i := i + 1;
549 END LOOP;
550 ELSE
551 l_lookup_table_plan_typ := P_PLAN_TYPE_LOOKUP_VALUE;
552 hr_utility.trace('In Else: assigning l_lookup_table_plan_typ value');
553
554 END IF;
555
556 --End of Populating lookup for plan type l_lookup_table_plan_typ ---LG
557
558
559 -- l_sec_grp_id := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
560 hr_utility.trace('l_sec_grp_id :' || l_sec_grp_id);
561 hr_utility.trace('Calling the create_user_template from my proc...');
562
563
564 l_element_type_id := pqp_gb_osp_template.create_user_template
565 (p_plan_id => P_PLAN_ID
566 ,p_plan_description => P_PLAN_DESCRIPTION
567 ,p_sch_cal_type => P_SCH_CAL_TYPE
568 ,p_sch_cal_duration => P_SCH_CAL_DURATION
569 ,p_sch_cal_uom => P_SCH_CAL_UOM
570 ,p_sch_cal_start_date => l_SCH_CAL_START_DATE
571 ,p_sch_cal_end_date => l_SCH_CAL_END_DATE
572 ,p_abs_days => P_ABS_DAYS
573 ,p_abs_ent_sick_leaves => P_ABS_ENT_SICK_LEAVES
574 ,p_abs_ent_holidays => P_ABS_ENT_HOLIDAYS
575 ,p_abs_daily_rate_calc_method => P_ABS_DAILY_RATE_CALC_METHOD
576 ,p_abs_daily_rate_calc_period => P_ABS_DAILY_RATE_CALC_PERIOD
577 ,p_abs_daily_rate_calc_divisor => P_ABS_DAILY_RATE_CALC_DIVISOR
578 ,p_abs_working_pattern => P_ABS_WORKING_PATTERN
579 ,p_abs_overlap_rule => P_ABS_OVERLAP_RULE
580 ,p_abs_ele_name => P_ABS_ELE_NAME
581 ,p_abs_ele_reporting_name => P_ABS_ELE_REPORTING_NAME
582 ,p_abs_ele_description => P_ABS_ELE_DESCRIPTION
583 ,p_abs_ele_processing_priority => P_ABS_ELE_PROCESSING_PRIORITY
584 ,p_abs_primary_yn => P_ABS_PRIMARY_YN
585 ,p_pay_ele_reporting_name => P_ABS_ELE_REPORTING_NAME
586 ,p_pay_ele_description => P_ABS_ELE_DESCRIPTION
587 -- Pay Element Reporting Name and Description are changed to pass same as
588 -- Absence Element Reporting Name and Description.
589 ,p_pay_ele_processing_priority => P_PAY_ELE_PROCESSING_PRIORITY
590 ,p_pay_src_pay_component => P_PAY_SRC_PAY_COMPONENT
591 ,p_bnd1_ele_sub_name => P_BND1_ELE_SUB_NAME
592 ,p_bnd2_ele_sub_name => P_BND2_ELE_SUB_NAME
593 ,p_bnd3_ele_sub_name => P_BND3_ELE_SUB_NAME
594 ,p_bnd4_ele_sub_name => P_BND4_ELE_SUB_NAME
595 ,p_ele_eff_start_date => P_ELE_EFF_START_DATE
596 ,p_ele_eff_end_date => P_ELE_EFF_END_DATE
597 ,p_abs_type_lookup_type => P_ABS_TYPE_LOOKUP_TYPE
598 ,p_abs_type_lookup_value => l_lookup_table
599 ,p_security_group_id => l_sec_grp_id
600 ,p_bg_id => p_bg_id
601 ,p_plan_type_lookup_type => P_PLAN_TYPE_LOOKUP_TYPE-- LG
602 ,p_plan_type_lookup_value => l_lookup_table_plan_typ -- LG
603 ,p_enable_ent_proration => P_ENABLE_ENT_PRORATION -- LG
604 ,p_scheme_type => P_SCHEME_TYPE -- LG
605 ,p_abs_schedule_wp => P_ABS_SCHEDULE_WP -- LG
606 ,p_dual_rolling_duration => P_DUAL_ROLLING_DURATION -- LG
607 ,p_dual_rolling_UOM => P_DUAL_ROLLING_UOM -- LG
608 ,p_ft_round_config => P_FT_ROUND_CONFIG
609 ,p_pt_round_config => P_PT_ROUND_CONFIG
610 );
611
612 p_element_type_id := l_element_type_id;
613 hr_utility.trace('Ele Type Id :'||p_element_type_id);
614
615 --
616 -- Compile and create(BEN) all the Formulas
617 --
618 compile_formulas
619 (P_ELEMENT_TYPE_ID => L_ELEMENT_TYPE_ID
620 ,P_ABS_PRIMARY_YN => P_ABS_PRIMARY_YN
621 ,P_EFF_START_DATE => P_ELE_EFF_START_DATE
622 ,P_BASE_NAME => P_ABS_ELE_NAME
623 ,P_ABSENCE_TYPE => 'SICKNESS'
624 ,P_BG_ID => P_BG_ID
625 ,P_REQUEST_ID => P_REQUEST_ID
626 );
627
628 -- Added by tmehra for nocopy changes Feb'03
629
630 EXCEPTION
631 /* WHEN OTHERS THEN
632 hr_utility.trace('Entering exception when others ');
633 p_element_type_id := NULL;
634 p_request_id := NULL;
635 raise; */
636 WHEN hr_multi_message.error_message_exist THEN
637 --
638 -- Catch the Multiple Message List exception which
639 -- indicates API processing has been aborted because
640 -- at least one message exists in the list.
641 --
642 p_element_type_id := NULL;
643 p_request_id := NULL;
644 hr_utility.set_location ( ' Leaving:'
645 || l_proc_name, 40);
646 WHEN OTHERS THEN
647 --
648 -- When Multiple Message Detection is enabled catch
649 -- any Application specific or other unexpected
650 -- exceptions. Adding appropriate details to the
651 -- Multiple Message List. Otherwise re-raise the
652 -- error.
653 --
654 IF hr_multi_message.unexpected_error_add (l_proc_name)
655 THEN
656 p_element_type_id := NULL;
657 p_request_id := NULL;
658 hr_utility.set_location ( ' Leaving:'
659 || l_proc_name, 50);
660 RAISE;
661 END IF;
662
663 END Create_osp_template;
664
665
666 Procedure Create_unp_template
667 (P_PLAN_ID IN NUMBER
668 ,P_PLAN_DESCRIPTION IN VARCHAR2
669 ,P_ABS_DAYS IN VARCHAR2
670 ,P_ABS_ENT_SICK_LEAVES IN NUMBER
671 ,P_ABS_ENT_HOLIDAYS IN NUMBER
672 ,P_ABS_DAILY_RATE_CALC_METHOD IN VARCHAR2
673 ,P_ABS_DAILY_RATE_CALC_PERIOD IN VARCHAR2
674 ,P_ABS_DAILY_RATE_CALC_DIVISOR IN NUMBER
675 ,P_ABS_WORKING_PATTERN IN VARCHAR2
676 ,P_ABS_ELE_NAME IN VARCHAR2
677 ,P_ABS_ELE_REPORTING_NAME IN VARCHAR2
678 ,P_ABS_ELE_DESCRIPTION IN VARCHAR2
679 ,P_ABS_ELE_PROCESSING_PRIORITY IN NUMBER
680 ,P_ABS_PRIMARY_YN IN VARCHAR2
681 ,P_PAY_ELE_REPORTING_NAME IN VARCHAR2
682 ,P_PAY_ELE_DESCRIPTION IN VARCHAR2
683 ,P_PAY_ELE_PROCESSING_PRIORITY IN NUMBER
684 ,P_PAY_SRC_PAY_COMPONENT IN VARCHAR2
685 ,P_ELE_EFF_START_DATE IN DATE
686 ,P_ELE_EFF_END_DATE IN DATE
687 ,P_ABS_TYPE_LOOKUP_TYPE IN VARCHAR2
688 ,P_ABS_TYPE_LOOKUP_VALUE IN pqp_gb_osp_template.t_abs_types
689 ,P_ELEMENT_TYPE_ID OUT NOCOPY NUMBER
690 ,P_REQUEST_ID OUT NOCOPY NUMBER
691 ,P_SECURITY_GROUP_ID IN NUMBER
692 ,P_BG_ID IN NUMBER
693 ,P_ABS_SCHEDULE_WP IN VARCHAR2 DEFAULT NULL -- LG
694 )
695 IS
696
697
698 -- Table to pass as param to the create_osp_template proc.
699 l_lookup_table pqp_gb_osp_template.t_abs_types;
700
701 --
702 -- Ref cursor to execute the statement that comes as param
703 --
704 TYPE ref_csr_type IS REF CURSOR;
705 c_cursor ref_csr_type;
706
707 l_element_type_id pay_element_types_f.element_type_id%TYPE;
708 l_abs_type_lookup_type varchar2(100);
709 l_select VARCHAR2(100);
710 l_sec_grp_id per_business_groups.security_group_id%TYPE;
711 l_proc_name VARCHAR2(61) := g_proc_name ||
712 'Create_unp_template' ;
713 i number;
714 BEGIN
715
716 -- for Multi Messages
717 hr_multi_message.enable_message_list;
718
719
720 hr_utility.trace('Entering :'||l_proc_name);
721 hr_utility.trace('P_ABS_TYPE_LOOKUP_TYPE :' || P_ABS_TYPE_LOOKUP_TYPE);
722 hr_utility.trace('ABS_SHEDWP : '||P_ABS_SCHEDULE_WP);
723
724 IF P_ABS_TYPE_LOOKUP_TYPE IS NULL THEN
725
726 -- Fill the Absence types table.
727 --
728 l_lookup_table := P_ABS_TYPE_LOOKUP_VALUE;
729
730 ELSE
731 i := 0;
732 FOR r_lookup IN g_c_get_lookupdata
733 (P_LOOKUP_TYPE => P_ABS_TYPE_LOOKUP_TYPE
734 ,P_EFFECTIVE_START_DATE => P_ELE_EFF_START_DATE
735 )
736 LOOP
737 l_lookup_table(i).abs_type_id:=r_lookup.abs_type_id;
738 l_lookup_table(i).abs_type_name:=r_lookup.abs_type_name;
739 hr_utility.trace('Abs Type Name :'||l_lookup_table(i).abs_type_name);
740 i := i + 1;
741 END LOOP;
742 END IF;
743 l_sec_grp_id := get_security_grp_id(p_bg_id);
744
745
746 -- l_sec_grp_id := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
747 hr_utility.trace('l_sec_grp_id :' || l_sec_grp_id);
748 hr_utility.trace('Calling the create_user_template from my proc...');
749
750
751 l_element_type_id := pqp_gb_unpaid_template.create_user_template
752 (p_plan_id => P_PLAN_ID
753 ,p_plan_description => P_PLAN_DESCRIPTION
754 ,p_abs_days => P_ABS_DAYS
755 ,p_abs_ent_sick_leaves => P_ABS_ENT_SICK_LEAVES
756 ,p_abs_ent_holidays => P_ABS_ENT_HOLIDAYS
757 ,p_abs_daily_rate_calc_method => P_ABS_DAILY_RATE_CALC_METHOD
758 ,p_abs_daily_rate_calc_period => P_ABS_DAILY_RATE_CALC_PERIOD
759 ,p_abs_daily_rate_calc_divisor => P_ABS_DAILY_RATE_CALC_DIVISOR
760 ,p_abs_working_pattern => P_ABS_WORKING_PATTERN
761 ,p_abs_ele_name => P_ABS_ELE_NAME
762 ,p_abs_ele_reporting_name => P_ABS_ELE_REPORTING_NAME
763 ,p_abs_ele_description => P_ABS_ELE_DESCRIPTION
764 ,p_abs_ele_processing_priority => P_ABS_ELE_PROCESSING_PRIORITY
765 ,p_abs_primary_yn => P_ABS_PRIMARY_YN
766 ,p_pay_ele_reporting_name => P_ABS_ELE_REPORTING_NAME
767 ,p_pay_ele_description => P_ABS_ELE_DESCRIPTION
768 ,p_pay_ele_processing_priority => P_PAY_ELE_PROCESSING_PRIORITY
769 ,p_pay_src_pay_component => P_PAY_SRC_PAY_COMPONENT
770 ,p_ele_eff_start_date => P_ELE_EFF_START_DATE
771 ,p_ele_eff_end_date => P_ELE_EFF_END_DATE
772 ,p_abs_type_lookup_type => P_ABS_TYPE_LOOKUP_TYPE
773 ,p_abs_type_lookup_value => l_lookup_table
774 ,p_security_group_id => l_sec_grp_id
775 ,p_bg_id => p_bg_id
776 );
777
778 p_element_type_id := l_element_type_id;
779 hr_utility.trace('Ele Type Id :'||p_element_type_id);
780
781 --
782 -- Compile and create(BEN) all the Formulas
783 --
784 compile_formulas
785 (P_ELEMENT_TYPE_ID => L_ELEMENT_TYPE_ID
786 ,P_ABS_PRIMARY_YN => P_ABS_PRIMARY_YN
787 ,P_EFF_START_DATE => P_ELE_EFF_START_DATE
788 ,P_BASE_NAME => P_ABS_ELE_NAME
789 ,P_ABSENCE_TYPE => 'SICKNESS'
790 ,P_BG_ID => P_BG_ID
791 ,P_REQUEST_ID => P_REQUEST_ID
792 );
793
794 -- Added by tmehra for nocopy changes Feb'03
795
796 EXCEPTION
797 WHEN hr_multi_message.error_message_exist THEN
798 --
799 -- Catch the Multiple Message List exception which
800 -- indicates API processing has been aborted because
801 -- at least one message exists in the list.
802 --
803 p_element_type_id := NULL;
804 p_request_id := NULL;
805 hr_utility.set_location ( ' Leaving:'
806 || l_proc_name, 40);
807 WHEN OTHERS THEN
808 --
809 -- When Multiple Message Detection is enabled catch
810 -- any Application specific or other unexpected
811 -- exceptions. Adding appropriate details to the
812 -- Multiple Message List. Otherwise re-raise the
813 -- error.
814 --
815 IF hr_multi_message.unexpected_error_add (l_proc_name)
816 THEN
817 p_element_type_id := NULL;
818 p_request_id := NULL;
819 hr_utility.set_location ( ' Leaving:'
820 || l_proc_name, 50);
821 RAISE;
822 END IF;
823
824 END create_unp_template;
825
826
827
828 End pqp_gb_ss_absence_template;