1 PACKAGE BODY per_ro_medical_assessment AS
2 /* $Header: perromedc.pkb 120.0.12020000.2 2013/04/03 08:55:08 kbajaj noship $ */
3
4 g_package VARCHAR2(30) := 'PER_RO_MEDICAL_ASSESSMENT.';
5
6 /*cursor csr_get_owner(p_security_id number,p_consultation_type_result varchar2,p_lookup_type varchar2) is
7 select flv.last_updated_by upd
8 from fnd_lookup_values flv
9 where flv.lookup_type= p_lookup_type
10 and flv.language = userenv('LANG')
11 and flv.security_group_id =p_security_id
12 and flv.lookup_code = p_consultation_type_result;
13
14
15 cursor csr_get_owner_std(p_security_id number,p_consultation_type_result varchar2,p_lookup_type varchar2) is
16 select flv.last_updated_by upd
17 from fnd_lookup_values flv
18 where flv.lookup_type= p_lookup_type
19 and flv.language = userenv('LANG')
20 and flv.security_group_id =p_security_id
21 and flv.lookup_code = p_consultation_type_result; */
22
23 cursor csr_get_lookup_code(p_lookup_type varchar2,p_tag varchar2) is
24 SELECT lookup_code
25 FROM fnd_lookup_values
26 WHERE lookup_type = p_lookup_type
27 AND tag = p_tag
28 AND language = userenv ('LANG');
29
30
31 PROCEDURE CHECK_RO_MEDICAL_ASSMNT
32 (p_consultation_type IN varchar2
33 ,p_consultation_result IN varchar2
34 ,p_disability_id IN number DEFAULT NULL
35 ,p_incident_id IN number DEFAULT NULL)
36 AS
37 l_proc varchar2(100);
38 l_lookup_codetype varchar2(200) := '';
39 l_lookup_coderesult varchar2(200) := '';
40 l_lookup_code varchar2(30);
41 BEGIN
42 l_proc:= g_package||'CHECK_RO_MEDICAL_ASSMNT';
43 hr_utility.set_location('Entering:'|| l_proc, 5);
44 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
45 hr_utility.set_location('Leaving : '||l_proc,10);
46 return;
47 END IF;
48
49 open csr_get_lookup_code('CONSULTATION_TYPE','+RO');
50 loop
51 fetch csr_get_lookup_code into l_lookup_code;
52 exit when csr_get_lookup_code%notfound;
53 if l_lookup_codetype is null then
54 l_lookup_codetype := l_lookup_code;
55 ELSE
56 l_lookup_codetype := l_lookup_codetype || ',' || l_lookup_code;
57
58 END IF;
59 END LOOP;
60 CLOSE csr_get_lookup_code;
61
62 open csr_get_lookup_code('CONSULTATION_RESULT','+FR+RO');
63 loop
64 fetch csr_get_lookup_code into l_lookup_code;
65 exit when csr_get_lookup_code%notfound;
66 if l_lookup_coderesult is null then
67 l_lookup_coderesult := l_lookup_code;
68 else
69 l_lookup_coderesult := l_lookup_coderesult || ',' || l_lookup_code;
70 end if;
71 end loop;
72 close csr_get_lookup_code;
73
74 if (p_disability_id is null or p_disability_id = -987123654) and (p_incident_id is null or p_incident_id = -987123654 ) then
75 if p_consultation_type is not null and p_consultation_type not in ('RO_FIRST_CONTROL','RO_PERIODICAL') then
76 hr_utility.set_message(800, 'HR_520020_INV_MED_ASSMNT_TYPE');
77 hr_utility.set_message_token('CONSULTATION_TYPE', l_lookup_codetype);
78 hr_utility.raise_error;
79 end if;
80 end if;
81 IF p_consultation_result is not null and p_consultation_result not in ('ABLE','UNABLE','CONDITIONAL_ABLE','TEMPORARY_UNABLE') THEN
82 hr_utility.set_message(800, 'HR_520021_INV_MED_ASSMNT_RES');
83 hr_utility.set_message_token('CONSULTATION_RESULT', l_lookup_coderesult);
84 hr_utility.raise_error;
85 END IF;
86 hr_utility.set_location('Leaving : '||l_proc,20);
87 END CHECK_RO_MEDICAL_ASSMNT;
88
89
90
91 END per_ro_medical_assessment;