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