1 Package Body pqh_fr_stat_sit_util As
2 /* $Header: pqstsutl.pkb 120.0 2005/05/29 02:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_fr_stat_sit_util.'; -- Global package name.
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- ----------------------------------------------------------------------------
12 FUNCTION Is_input_is_valid(p_txn_category_attribute_id NUMBER, p_from_value varchar2 ) return varchar2
13 IS
14 -- Declare Cursors
15 --
16 l_proc varchar2(72) := g_package||'get_txn_catg_attr_meaning';
17 l_txn_category_attribute_id number := null;
18 l_from_value varchar2(1000) := null;
19 l_query varchar2(3000) := null;
20 l_Addl_where_clause varchar2(1000) := null;
21 l_result varchar2(1000) := null;
22 l_to_value varchar2(1000) := null;
23 l_value varchar2(1000) := null;
24 --
25 begin
26 -- Get the Query out of Tranasaction Category Id
27 l_query := get_txn_value_query(p_txn_category_attribute_id);
28 if (l_query = 'select null Id, null Val,null Att_Name from dual Where 1 = 2' ) then
29 return 'Y';
30 End if;
31 l_Addl_where_clause := ' Where Id = :1';
32 l_value := p_from_value;
33 l_query := 'Select Val From ('|| l_query ||' ) '|| l_Addl_where_clause;
34 begin
35 Execute Immediate l_query into l_result using l_value;
36 exception
37 when no_data_found then
38 l_result := null;
39 end ;
40 if (l_result is null) then
41 return 'N';
42 else
43 return 'Y';
44 end if;
45 end ;
46 --
47 Function get_txn_catg_attr_meaning(p_stat_situation_rule_id NUMBER, p_value_for VARCHAR2 DEFAULT 'FROM')
48 RETURN VARCHAR2
49 IS
50 --
51 -- Declare Cursors
52 --
53 Cursor csr_get_situation_rule IS
54 Select txn_category_attribute_id, from_value , to_value
55 from pqh_fr_stat_situation_rules
56 where stat_situation_rule_id = p_stat_situation_rule_id;
57 --
58 -- Declare Local Variables
59 --
60 l_proc varchar2(72) := g_package||'get_txn_catg_attr_meaning';
61 l_txn_category_attribute_id number := null;
62 l_from_value varchar2(1000) := null;
63 l_query varchar2(3000) := null;
64 l_Addl_where_clause varchar2(1000) := null;
65 l_result varchar2(1000) := null;
66 l_to_value varchar2(1000) := null;
67 l_value varchar2(1000) := null;
68 --
69 begin
70 g_debug := hr_utility.debug_enabled;
71 if g_debug then
72 --
73 hr_utility.set_location(' Entering:'||l_proc, 1);
74 --
75 End if;
76 -- Get the current row from situation rules
77 -- Get the txn_vlue_query out of txn id
78 -- Attach where clause for Id column in result query with from value
79 -- If query results none , return from_value
80 Open csr_get_situation_rule ;
81 --
82 Fetch csr_get_situation_rule into l_txn_category_attribute_id, l_from_value,l_to_value;
83 --
84 Close csr_get_situation_rule;
85 -- Get the Query out of Tranasaction Category Id
86 l_query := get_txn_value_query(l_txn_category_attribute_id);
87 if (p_value_for = 'TO') then
88 --
89 l_Addl_where_clause := ' Where Id = :1 ';
90 l_value := l_to_value;
91 --
92 else
93 --
94 l_Addl_where_clause := ' Where Id = :1';
95 l_value := l_from_value;
96 --
97 end if;
98 l_query := 'Select Val From ('|| l_query ||' ) '|| l_Addl_where_clause;
99 begin
100 Execute Immediate l_query into l_result using l_value;
101 exception
102 when no_data_found then
103 l_result := null;
104 end ;
105 If (l_result is null) Then
106 --
107 -- Its a RANGE Style Attribute
108 --
109 if (p_value_for ='TO') then
110 l_result := l_to_value;
111 else
112 l_result := l_from_value;
113 end if;
114 --
115 End if;
116 If g_debug then
117 --
118 hr_utility.set_location(' Leaving:'||l_proc, 1);
119 --
120 End if;
121 return l_result;
122 end get_txn_catg_attr_meaning;
123 --
124 FUNCTION GET_TXN_VALUE_QUERY (p_txn_category_attribute_id NUMBER)
125 RETURN VARCHAR2
126 IS
127 --
128 --
129 Cursor csr_get_txn_record IS
130 Select value_style_cd, value_set_id
131 from pqh_txn_category_attributes
132 where txn_category_attribute_id = p_txn_category_attribute_id;
133 --
134 --
135 l_query varchar2(5000) := 'select null Id, null Val,null Att_Name from dual Where 1 = 2';
136 l_value_style_cd pqh_txn_category_attributes.value_style_cd%type;
137 l_value_set_id pqh_txn_category_attributes.value_set_id%type;
138 l_ret varchar2(10);
139 l_validation_type varchar2(10);
140 --
141 begin
142 -- Open the cursor and get value_set_id
143 Open csr_get_txn_record ;
144 --
145 Fetch csr_get_txn_record into l_value_style_cd, l_value_set_id;
146 --
147 Close csr_get_txn_record;
148 -- Check value style CD is EXACT / RANGE
149 If (l_value_style_cd = 'EXACT') Then
150 --
151 pqh_utility.get_valueset_sql
152 (p_value_set_id => l_value_set_id,
153 p_validation_type => l_validation_type,
154 p_sql_stmt => l_query,
155 p_error_status => l_ret) ;
156 --
157 End if;
158 return l_query;
159 --
160 end GET_TXN_VALUE_QUERY;
161 --
162 --
163 FUNCTION RULES_EXIST(p_stat_situation_id NUMBER)
164 RETURN VARCHAR2
165 IS
166 --
167 --
168 Cursor csr_rules_list IS
169 Select null
170 from pqh_fr_stat_situation_rules
171 where statutory_situation_id = p_stat_situation_id;
172 --
173 l_value varchar2(1);
174 l_return varchar2(10) := 'Y';
175 Begin
176 Open csr_rules_list;
177 ---
178 Fetch csr_rules_list into l_value;
179 if csr_rules_list%NOTFOUND then
180 l_return := 'N';
181 end if;
182 ---
183 close csr_rules_list;
184 return l_return;
185 End rules_exist;
186 --
187 /* following functions are added for transaction attributes processing */
188
189 Function get_los_in_ps ( p_person_id IN NUMBER default NULL,
190 p_determination_date IN DATE default NULL)
191 return number
192 IS
193 cursor csr_person_info is
194 select business_group_id
195 from per_all_people_f where
196 person_id = p_person_id
197 and p_determination_date between effective_start_date and effective_end_date;
198
199 l_bg_id number;
200
201 begin
202 open csr_person_info;
203 fetch csr_person_info into l_bg_id;
204 close csr_person_info;
205
206 return pqh_length_of_service_pkg.get_length_of_service(l_bg_id, p_person_id,
207 NULL, '20', 'M', p_determination_date);
208 end;
209
210 Function get_general_los (p_person_id IN NUMBER default NULL,
211 p_determination_date IN DATE default NULL)
212 return number
213 IS
214 cursor csr_person_info is
215 select business_group_id
216 from per_all_people_f where
217 person_id = p_person_id
218 and p_determination_date between effective_start_date and effective_end_date;
219
220 l_bg_id number;
221 begin
222 open csr_person_info;
223 fetch csr_person_info into l_bg_id;
224 close csr_person_info;
225
226 return pqh_length_of_service_pkg.get_length_of_service(l_bg_id, p_person_id,
227 NULL, '10', 'M', p_determination_date);
228
229 end;
230
231 Function get_employee_type (p_person_id IN per_all_people_f.person_id%TYPE,
232 p_determination_date IN DATE) return varchar2
233 IS
234 Cursor csr_emp_type is
235 SELECT scl.segment10 emp_type
236 FROM per_all_assignments_f asg,
237 hr_soft_coding_keyflex scl
238 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
239 AND person_id = p_person_id
240 AND p_determination_date between effective_start_date and effective_end_date
241 AND primary_flag = 'Y';
242
243 l_emp_type varchar2(2);
244 begin
245 open csr_emp_type;
246 fetch csr_emp_type into l_emp_type;
247 close csr_emp_type;
248
249 return l_emp_type;
250
251 end;
252
253 Function get_situation_type (p_person_id IN per_all_people_f.person_id%TYPE,
254 p_determination_date IN DATE) return varchar2
255 IS
256 cursor csr_situation_info is
257 select ss.situation_type, ss.situation_name, ss.situation_type_name
258 from PQH_FR_EMP_STAT_SITUATIONS ess, PQH_FR_STAT_SITUATIONS_V ss
259 where ess.Statutory_situation_id = ss.Statutory_situation_id
260 and ess.person_id = p_person_id
261 and p_determination_date between ess.actual_start_date and nvl(ess.actual_end_date, ess.provisional_end_date);
262
263 l_situation_type pqh_fr_stat_situations_v.situation_type%TYPE ;
264 l_situation_name pqh_fr_stat_situations_v.situation_name%TYPE ;
265 l_situation_type_name pqh_fr_stat_situations_v.situation_type_name%TYPE ;
266
267 begin
268 open csr_situation_info;
269 fetch csr_situation_info into l_situation_type, l_situation_name, l_situation_type_name;
270 close csr_situation_info;
271
272 return l_situation_type;
273 end;
274
275 Function get_relationship_type (p_person_id IN per_all_people_f.person_id%TYPE,
276 p_determination_date IN DATE) return varchar2
277 IS
278 begin
279 return null;
280 end;
281
282 Function get_dependent_age (p_person_id IN per_all_people_f.person_id%TYPE,
283 p_determination_date IN DATE) return number
284 IS
285 l_dependent_cnt number;
286 l_dependent_dob date;
287 l_dependent_age number;
288
289 begin
290 select count(1) into l_dependent_cnt
291 from per_contact_relationships
295 return null;
292 where person_id = p_person_id
293 and p_determination_date between date_start and nvl(date_end,hr_general.end_of_time);
294 if l_dependent_cnt = 0 then
296 else
297 select max(date_of_birth) into l_dependent_dob
298 from per_all_people_f
299 where person_id in (select contact_person_id
300 from per_contact_relationships
301 where person_id = p_person_id
302 and p_determination_date between date_start and nvl(date_end,hr_general.end_of_time));
303 l_dependent_age := Months_Between(p_determination_date,l_dependent_dob)/12;
304 return l_dependent_age;
305 end if;
306 end;
307 --
308 --Changes for Employee Statutory Situation Placement
309 FUNCTION is_situation_renewable(p_emp_stat_situation_id NUMBER,
310 p_statutory_situation_id NUMBER) RETURN VARCHAR2 IS
311 --
312 CURSOR csr_renewable(p_statutory_situation_id NUMBER) IS
313 SELECT NVL(renewable_allowed,'N'),NVL(max_no_of_renewals,0)
314 FROM pqh_fr_stat_situations
315 WHERE statutory_situation_id = p_statutory_situation_id;
316 --
317 l_renewable Varchar2(10);
318 l_max_renewals NUMBER(10) := 0;
319 l_no_of_renewals NUMBER(10) := 0;
320 BEGIN
321 --
322 OPEN csr_renewable(p_statutory_situation_id);
323 FETCH csr_renewable INTO l_renewable,l_max_renewals;
324 CLOSE csr_renewable;
325 --
326 IF l_renewable = 'N' OR l_max_renewals = '0' THEN
327 RETURN 'NO';
328 END IF;
329 --
330 l_no_of_renewals := get_number_of_renewals(p_emp_stat_situation_id);
331 --
332 IF l_no_of_renewals >= l_max_renewals THEN
333 RETURN 'NO';
334 END IF;
335 --
336 RETURN 'YES';
337 --Below lines commented by deenath, since base situation may not be current.
338 /*
339 IF (is_current_situation(p_emp_stat_situation_id) = 'Y') THEN
340 RETURN 'YES';
341 ELSE
342 RETURN 'NO';
343 END IF;
344 */
345 END is_situation_renewable;
346 --
347 Function is_current_situation(p_emp_stat_situation_id NUMBER) RETURN varchar2 IS
348 Cursor Csr_current_situation(p_emp_statutory_situation_id NUMBER) IS
349 SELECT NVL(actual_start_date,provisional_start_date),NVL(NVL(actual_end_date,provisional_end_date),TRUNC(SYSDATE)),NVL(approval_flag,'N')
350 FROM pqh_fr_emp_stat_situations
351 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
352 l_start_date DATE;
353 l_end_date DATE;
354 l_approved varchar2(10);
355 BEGIN
356 OPEN csr_current_situation(p_emp_stat_situation_id);
357 FETCH csr_current_situation INTO l_start_date, l_end_date,l_approved;
358 CLOSE csr_current_situation;
359 IF (TRUNC(SYSDATE) <= l_end_date AND TRUNC(SYSDATE) >= l_start_date ) AND l_approved = 'Y' THEN
360 RETURN 'Y';
361 END IF;
362 RETURN 'N';
363 END is_current_situation;
364 --
365 FUNCTION get_number_of_renewals(p_emp_stat_situation_id NUMBER) RETURN NUMBER IS
366 CURSOR csr_no_of_renewals(p_emp_stat_situation_id IN NUMBER) IS
367 SELECT NVL(count(emp_stat_situation_id),0)
368 FROM pqh_fr_emp_stat_situations
369 WHERE renewal_flag = 'Y'
370 AND renew_stat_situation_id = p_emp_stat_situation_id;
371 l_no_of_renewals NUMBER(10) := 0;
372 BEGIN
373 OPEN csr_no_of_renewals(p_emp_stat_situation_id);
374 FETCH csr_no_of_renewals INTO l_no_of_renewals;
375 CLOSE csr_no_of_renewals;
376 RETURN l_no_of_renewals;
377 END get_number_of_renewals;
378 --
379 --deenath - New function to get number of renewals created since in Update Renewal Situation,
380 --we dont want to count the situation being updated as a renewal. Invoked from PQH_PSU_BUS.
381 FUNCTION get_num_renewals(p_emp_stat_situation_id IN NUMBER,
382 p_renew_stat_situation_id IN NUMBER) RETURN NUMBER IS
383 --
384 --Cursor to fetch total number of renewals.
385 CURSOR csr_no_of_renewals IS
386 SELECT NVL(COUNT(emp_stat_situation_id),0)
387 FROM pqh_fr_emp_stat_situations
388 WHERE emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
389 AND renewal_flag = 'Y'
390 AND renew_stat_situation_id = p_renew_stat_situation_id;
391 --
392 --Variable Declaration.
393 l_no_of_renewals NUMBER(10) := 0;
394 --
395 BEGIN
396 --
397 OPEN csr_no_of_renewals;
398 FETCH csr_no_of_renewals INTO l_no_of_renewals;
399 CLOSE csr_no_of_renewals;
400 --
401 RETURN l_no_of_renewals;
402 --
403 END get_num_renewals;
404 --
405 --
406 FUNCTION chk_rule_condition(p_emp_stat_situation_id IN NUMBER,
407 p_statutory_situation_id IN NUMBER,
408 p_txn_category_attribute_id IN NUMBER,
409 p_from_value IN VARCHAR2,
410 p_to_value IN VARCHAR2,
411 p_negate IN VARCHAR2) RETURN BOOLEAN IS
412 l_rule_valid BOOLEAN := FALSE;
413 CURSOR csr_attr_dtls (p_txn_catg_attribute_id IN NUMBER) IS
414 SELECT tca.value_style_cd,
415 tr.from_clause,
416 tr.where_clause,
417 a.column_name,
418 a.column_type
419 FROM pqh_txn_category_attributes tca,
420 pqh_table_route tr,
421 pqh_attributes a
422 WHERE tca.txn_category_attribute_id = p_txn_catg_attribute_id
423 AND tca.attribute_id = a.attribute_id
424 AND a.master_table_route_id = tr.table_route_id;
428 l_txn_value_v varchar2(100);
425 l_attr_dtls csr_attr_dtls%ROWTYPE;
426 l_where_clause_out varchar2(2000);
427 l_sql_stmt varchar2(2000);
429 l_txn_value_n NUMBER;
430 BEGIN
431 OPEN csr_attr_dtls(p_txn_category_attribute_id);
432 FETCH csr_attr_dtls INTO l_attr_dtls.value_style_cd,
433 l_attr_dtls.from_clause,
434 l_attr_dtls.where_clause,
435 l_attr_dtls.column_name,
436 l_attr_dtls.column_type;
437 IF csr_attr_dtls%NOTFOUND THEN
438 hr_utility.set_location('Invalid Rule Attribute',10);
439 END IF;
440 CLOSE csr_attr_dtls;
441 pqh_refresh_data.replace_where_params(
442 p_where_clause_in => l_attr_dtls.where_clause,
443 p_txn_tab_flag => 'Y',
444 p_txn_id => p_emp_stat_situation_id,
445 p_where_clause_out => l_where_clause_out);
446 --dbms_output.put_line('Out Where'||l_where_clause_out);
447 l_sql_stmt := 'SELECT '||l_attr_dtls.column_name
448 ||' FROM '|| l_attr_dtls.from_clause
449 ||' WHERE '||l_where_clause_out;
450 --dbms_output.put_line('SQL 1 - '||substr(l_sql_stmt,1,150));
451 --dbms_output.put_line('SQL 1 - '||substr(l_sql_stmt,151,150));
452 --dbms_output.put_line('SQL 1 - '||substr(l_sql_stmt,301,150)) ;
453 IF l_attr_dtls.column_type = 'V' THEN
454 BEGIN
455 EXECUTE IMMEDIATE l_sql_stmt INTO l_txn_value_v;
456 EXCEPTION
457 When Others THEN
458 RAISE;
459 --dbms_output.put_line('Error in Dyn Sql - Varchar2');
460 --dbms_output.put_line(SqlErrm);
461 END;
462 --dbms_output.put_line('txn Value - '||l_txn_value_v);
463 IF l_attr_dtls.value_style_cd = 'EXACT' THEN
464 IF l_txn_value_v = p_from_value THEN
465 IF p_negate = 'Y' THEN
466 RETURN FALSE;
467 ELSE
468 RETURN TRUE;
469 END IF;
470 END IF;
471 ELSIF l_attr_dtls.value_style_cd = 'RANGE' THEN
472 IF l_txn_value_v >= p_from_value
473 AND l_txn_value_v <= NVL(p_to_value,l_txn_value_v) THEN
474 IF p_negate = 'Y' THEN
475 RETURN FALSE;
476 ELSE
477 RETURN TRUE;
478 END IF;
479 END IF;
480 END IF;
481 ELSIF l_attr_dtls.column_type = 'N' THEN
482 BEGIN
483 EXECUTE IMMEDIATE l_sql_stmt INTO l_txn_value_n;
484 EXCEPTION
485 When Others THEN
486 RAISE;
487 --dbms_output.put_line('Error in Dyn Sql - NUMBER');
488 --dbms_output.put_line(SqlErrm);
489 END;
490 --dbms_output.put_line('txn Value - '||l_txn_value_n);
491 IF l_attr_dtls.value_style_cd = 'EXACT' THEN
492 IF l_txn_value_n = fnd_number.canonical_to_number(p_from_value) THEN
493 IF p_negate = 'Y' THEN
494 RETURN FALSE;
495 ELSE
496 RETURN TRUE;
497 END IF;
498 END IF;
499 ELSIF l_attr_dtls.value_style_cd = 'RANGE' THEN
500 IF l_txn_value_n >= fnd_number.canonical_to_number(p_from_value)
501 AND l_txn_value_n <= NVL(fnd_number.canonical_to_number(p_to_value),l_txn_value_n) THEN
502 IF p_negate = 'Y' THEN
503 RETURN FALSE;
504 ELSE
505 RETURN TRUE;
506 END IF;
507 END IF;
508 END IF;
509 END IF;
510 RETURN FALSE;
511 END chk_rule_condition;
512 Function Check_Situation_rules(p_emp_stat_situation_id IN NUMBER,
513 p_statutory_situation_id IN NUMBER,
514 p_rule_type IN VARCHAR2 DEFAULT 'REQUIRED')
515 RETURN VARCHAR2 IS
516 CURSOR csr_rule_conditions(p_stat_sit_id NUMBER,
517 p_required_flag VARCHAR2) IS
518 SELECT txn_category_attribute_id,
519 from_value,
520 to_value,
521 exclude_flag
522 FROM pqh_fr_stat_situation_rules
523 WHERE statutory_situation_id = p_stat_sit_id
524 AND NVL(enabled_flag,'N') = 'Y'
525 AND NVL(required_flag,'N') = p_required_flag
526 ORDER BY processing_sequence;
527 l_required varchar2(10);
528 lr_rule csr_rule_conditions%ROWTYPE;
529 l_all_valid_rules VARCHAR2(30) := 'NO_RULES_DEFINED';
530 l_rule_result BOOLEAN := FALSE;
531 BEGIN
532 IF p_rule_type = 'REQUIRED' THEN
533 l_required := 'Y';
534 ELSE
535 l_required := 'N';
536 END IF;
537 OPEN csr_rule_conditions(p_statutory_situation_id,l_required);
538 LOOP
539 FETCH csr_rule_conditions INTO lr_rule.txn_category_attribute_id,
540 lr_rule.from_value,
541 lr_rule.to_value,
542 lr_rule.exclude_flag;
543 EXIT WHEN Csr_rule_conditions%NOTFOUND;
544 l_rule_result := chk_rule_condition(p_emp_stat_situation_id,
545 p_statutory_situation_id,
546 lr_rule.txn_category_attribute_id,
547 lr_rule.from_value,
548 lr_rule.to_value,
549 lr_rule.exclude_flag);
550 IF l_rule_result THEN
551 l_all_valid_rules := 'TRUE';
552 IF p_rule_type = 'OPTIONAL' THEN
553 EXIT;
554 END IF;
555 ELSE
556 l_all_valid_rules := 'FALSE';
557 IF p_rule_type = 'REQUIRED' THEN
558 EXIT;
559 END IF;
560 END IF;
561 END LOOP;
565 Function is_situation_valid(p_person_id NUMBER,
562 CLOSE csr_rule_conditions;
563 RETURN l_all_valid_rules;
564 END Check_Situation_rules;
566 p_emp_stat_situation_id NUMBER,
567 p_statutory_situation_id NUMBER) RETURN VARCHAR2 IS
568 l_passed_all_reqd VARCHAR2(30);
569 l_passed VARCHAR2(30);
570 l_return_status VARCHAR2(10) := 'N';
571 l_rule_valid BOOLEAN := FALSE;
572 BEGIN
573 --Validate the Mandatory Conditions first. If it meets all the conditions, then situation is valid.
574 l_passed_all_reqd := Check_Situation_Rules(p_emp_stat_situation_id,
575 p_statutory_situation_id,
576 'REQUIRED');
577 IF l_passed_all_reqd = 'YES' THEN
578 l_return_status := 'Y';
579 ELSIF l_passed_all_reqd = 'NO' THEN
580 l_return_status := 'N';
581 ELSIF l_passed_all_reqd = 'NO_RULES_DEFINED' THEN
582 --No Required rules defined. See if meets atleast one optional rule.
583 l_passed := Check_Situation_Rules(p_emp_stat_situation_id,
584 p_statutory_situation_id,
585 'OPTIONAL');
586 IF l_passed = 'YES' OR l_passed = 'NO_RULES_DEFINED' THEN
587 l_return_status := 'Y';
588 ELSE
589 l_return_status := 'N';
590 END IF;
591 END IF;
592 RETURN l_return_status;
593 END is_situation_valid;
594 --
595 FUNCTION get_dflt_situation(p_business_group_id IN NUMBER,
596 p_situation_type IN VARCHAR2,
597 p_sub_type IN VARCHAR2,
598 p_effective_date IN DATE)
599 RETURN NUMBER IS
600 CURSOR csr_dflt_inactivity(p_business_group_id IN NUMBER,
601 p_situation_type IN VARCHAR2,
602 p_sub_type IN VARCHAR2,
603 p_eff_date IN DATE) IS
604 SELECT statutory_situation_id
605 FROM pqh_fr_stat_situations_v sit
606 ,per_shared_types_vl sh
607 WHERE sh.shared_type_id = type_of_ps
608 AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
609 AND sit.business_group_id = p_business_group_id
610 AND sit.situation_type = p_situation_type
611 AND sit.sub_type = NVL(p_sub_type,sub_type)
612 AND sit.default_flag = 'Y'
613 AND TRUNC(p_eff_date) BETWEEN sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time);
614 /* --Commented by deenath and replaced by above cursor sql.
615 SELECT statutory_situation_id
616 FROM pqh_fr_stat_situations
617 WHERE business_group_id = p_business_group_id
618 AND situation_type = p_situation_type
619 AND sub_type = NVL(p_sub_type,sub_type)
620 AND default_flag = 'Y'
621 AND trunc(p_eff_date) BETWEEN date_from and NVL(date_to,hr_general.end_of_time);
622 */
623 l_reinstate_situation NUMBER(15);
624 BEGIN
625 OPEN csr_dflt_inactivity(p_business_group_id,p_situation_type,p_sub_type,p_effective_date);
626 FETCH csr_dflt_inactivity INTO l_reinstate_situation;
627 CLOSE csr_dflt_inactivity;
628 RETURN NVL(l_reinstate_situation,-1);
629 END get_dflt_situation;
630 --
631 FUNCTION get_time_line(p_provisional_start_date IN DATE,
632 p_provisional_end_date IN DATE,
633 p_effective_date IN DATE)
634 RETURN VARCHAR2 IS
635 BEGIN
636 /* --Commented by deenath to display Timeline even for End Of Time Situations.
637 If p_provisional_end_date = hr_general.end_of_time then
638 Return null;
639 End if;
640 */
641 IF p_effective_date BETWEEN p_provisional_start_date AND p_provisional_end_date THEN
642 RETURN HR_GENERAL.decode_lookup('PQH_FR_SIT_TIME_LINES','PRESENT');
643 ELSIF p_effective_date > p_provisional_end_date THEN
644 RETURN HR_GENERAL.decode_lookup('PQH_FR_SIT_TIME_LINES','PAST');
645 ELSIF p_provisional_start_date > p_effective_date Then
646 RETURN HR_GENERAL.decode_lookup('PQH_FR_SIT_TIME_LINES','FUTURE');
647 END IF;
648 RETURN NULL; --added by deenath
649 END get_time_line;
650 --
651 FUNCTION get_time_line_code(p_provisional_start_date IN DATE,
652 p_actual_end_date IN DATE,
653 p_provisional_end_date IN DATE,
654 p_effective_date IN DATE) RETURN VARCHAR2
655 IS
656 BEGIN
657 --
658 IF p_provisional_end_date = HR_GENERAL.end_of_time OR p_actual_end_date = HR_GENERAL.end_of_time THEN
659 RETURN NULL;
660 END IF;
661 --
662 IF p_actual_end_date IS NOT NULL THEN
663 RETURN NULL;
664 END IF;
665 --
666 IF p_effective_date BETWEEN p_provisional_start_date AND NVL(p_actual_end_date,p_provisional_end_date) THEN
667 RETURN 'PRESENT';
668 ELSIF p_effective_date > NVL(p_actual_end_date,p_provisional_end_date) Then
669 RETURN 'PAST';
670 ELSIF p_provisional_start_date > p_effective_date Then
671 RETURN 'FUTURE';
672 END IF;
673 --
674 END get_time_line_code;
675 --
676 FUNCTION get_update_time_line_code(p_provisional_start_date IN DATE,
677 p_provisional_end_date IN DATE,
678 p_effective_date IN DATE,
679 p_approval_flag IN VARCHAR2,
680 p_renew_flag IN VARCHAR2,
681 p_situation_type IN VARCHAR2,
685 l_return_value VARCHAR2(10) := 'NO';
682 p_sub_type IN VARCHAR2,
683 p_default_flag IN VARCHAR2)
684 RETURN VARCHAR2 IS
686 BEGIN
687 IF p_effective_date BETWEEN p_provisional_start_date AND p_provisional_end_date THEN
688 l_return_value := 'PRESENT';
689 ELSIF p_effective_date > p_provisional_end_date THEN
690 l_return_value := 'PAST';
691 ELSIF p_provisional_start_date > p_effective_date THEN
692 l_return_value := 'FUTURE';
693 END IF;
694 IF(l_return_value = 'FUTURE') THEN
695 l_return_value := 'SHOW';
696 ELSE
697 l_return_value := 'NO';
698 END IF;
699 --Added by deenath
700 IF (p_situation_type = 'IA' AND p_sub_type = 'IA_N' AND p_default_flag = 'Y') THEN
701 l_return_value := 'NO';
702 END IF;
703 IF NVL(p_renew_flag,'N') = 'Y' THEN
704 l_return_value := 'NO';
705 END IF;
706 /* --Commented by deenath. Replaced with above if condition.
707 Do not show for Present timeline irrespective of Approval Flag.
708 if (l_return_value ='PRESENT') and (p_approval_flag = 'N') then
709 l_return_value := 'SHOW';
710 elsif (l_return_value = 'FUTURE') then
711 l_return_value := 'SHOW';
712 else
713 l_return_value := 'NO';
714 end if;
715 */
716 RETURN l_return_value;
717 END get_update_time_line_code;
718 --
719 FUNCTION get_delete_time_line_code(p_person_id IN NUMBER,
720 p_provisional_start_date IN DATE,
721 p_provisional_end_date IN DATE,
722 p_effective_date IN DATE)
723 RETURN VARCHAR2 IS
724 l_return_value VARCHAR2(10) := NULL;
725 BEGIN
726 IF p_provisional_end_date = hr_general.end_of_time THEN
727 RETURN NULL;
728 END IF;
729 IF p_effective_date BETWEEN p_provisional_start_date AND p_provisional_end_date THEN
730 RETURN NULL;
731 ELSIF p_effective_date > p_provisional_end_date THEN
732 RETURN NULL;
733 ELSIF p_provisional_start_date > p_effective_date THEN
734 --added by deenath
735 SELECT DECODE(TRUNC(MAX(provisional_start_date)),TRUNC(p_provisional_start_date),'DEL-TRUE',NULL)
736 INTO l_return_value
737 FROM pqh_fr_emp_stat_situations
738 WHERE person_id = p_person_id
739 AND statutory_situation_id NOT IN (SELECT statutory_situation_id
740 FROM pqh_fr_stat_situations_v sit
741 ,per_shared_types_vl sh
742 WHERE sh.shared_type_id = type_of_ps
743 AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
744 AND sit.business_group_id= HR_GENERAL.get_business_group_id
745 AND sit.default_flag = 'Y'
746 AND sit.situation_type = 'IA'
747 AND sit.sub_type = 'IA_N'
748 AND TRUNC(SYSDATE) BETWEEN sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time));
749 END IF;
750 RETURN l_return_value;
751 END get_delete_time_line_code;
752 --
753 END pqh_fr_stat_sit_util;
[Home] [Help]