1 PACKAGE BODY per_cn_shared_info AS
2 /*REM $Header: pecnshin.pkb 120.3 2010/08/31 06:39:17 dduvvuri noship $ */
3
4 FUNCTION get_lookup_meaning(p_code IN VARCHAR2, p_type IN VARCHAR2)
5 RETURN VARCHAR2
6 IS
7
8 CURSOR get_meaning IS
9 select meaning from fnd_lookup_values
10 where lookup_type = p_type
11 and enabled_flag = 'Y'
12 and lookup_code = p_code
13 and language = userenv('lang')
14 order by lookup_code;
15
16 l_meaning fnd_lookup_values.meaning%type;
17
18 BEGIN
19
20 OPEN get_meaning;
21 FETCH get_meaning INTO l_meaning;
22 CLOSE get_meaning;
23
24 IF p_type IN ('CN_MINISTRY_LABELS','CN_SOE_LABELS') THEN
25 l_meaning := REPLACE(l_meaning,' ','_');
26 END IF;
27
28 return l_meaning;
29
30 END get_lookup_meaning ;
31
32 Function cn_get_doc_details
33 (p_person_id IN NUMBER,
34 p_date IN DATE,
35 p_type IN VARCHAR2
36 )
37 RETURN VARCHAR2
38 IS
39 /* define all cursors and variables */
40 l_val VARCHAR2(1);
41 l_cin varchar2(100);
42 l_hk NUMBER;
43 l_tw NUMBER;
44 l_passport VARCHAR2(100);
45 l_doc_type VARCHAR2(100);
46 l_doc_num VARCHAR2(100);
47
48 CURSOR c_check_person(p_person_id IN NUMBER,p_date IN DATE) IS
49 SELECT '1'
50 FROM PER_ALL_PEOPLE_F
51 WHERE PERSON_ID = p_person_id
52 AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
53
54 CURSOR c_get_cit_ident_no(p_person_id IN NUMBER,p_date IN DATE) IS
55 SELECT NATIONAL_IDENTIFIER
56 FROM PER_ALL_PEOPLE_F
57 WHERE PERSON_ID = p_person_id
58 AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
59
60 CURSOR c_get_pass_no(p_person_id IN NUMBER) IS
61 SELECT PEI_INFORMATION1 A, PEI_INFORMATION2 B
62 FROM PER_PEOPLE_EXTRA_INFO
63 WHERE PERSON_ID = p_person_id
64 AND INFORMATION_TYPE = 'PER_PASS_INFO_CN'
65 AND PEI_INFORMATION_CATEGORY = 'PER_PASS_INFO_CN'
66 ORDER BY A;
67
68 CURSOR c_get_passport_no(p_person_id IN NUMBER) IS
69 SELECT PEI_INFORMATION2
70 FROM PER_PEOPLE_EXTRA_INFO
71 WHERE PERSON_ID = p_person_id
72 AND INFORMATION_TYPE = 'PER_PASSPORT_INFO_CN'
73 AND PEI_INFORMATION_CATEGORY = 'PER_PASSPORT_INFO_CN';
74
75 BEGIN
76
77 l_hk := 0;
78 l_tw := 0;
79
80 /* No need to check if the person is effective on the date provided because
81 the input date is always the effective start date of the person.*/
82
83 OPEN c_get_cit_ident_no(p_person_id , p_date);
84 FETCH c_get_cit_ident_no INTO l_cin;
85 /* At any point of time only 1 CIN is available to the person */
86 IF l_cin IS NULL THEN
87 CLOSE c_get_cit_ident_no;
88 /* Search for Pass Information. There can be multiple pass information */
89 /* First check for HongKong Pass Info and if not present check for Taiwanese Pass Info */
90 FOR itr in c_get_pass_no(p_person_id) LOOP
91 IF itr.A = 'PHM' THEN
92 l_hk := 1;
93 l_doc_type := get_lookup_meaning('PASS_HK_MACAO','CN_AUDIT_DATA');
94 l_doc_num := itr.B;
95 EXIT;
96 ELSIF itr.A = 'PTR' THEN
97 l_tw := 1;
98 l_doc_type := get_lookup_meaning('PASS_TAIWAN','CN_AUDIT_DATA');
99 l_doc_num := itr.B;
100 EXIT;
101 ELSE
102 EXIT;
103 END IF;
104 END LOOP;
105 IF l_hk = 1 OR l_tw = 1 THEN
106 IF p_type='TYPE' then
107 RETURN l_doc_type;
108 ELSE
109 RETURN l_doc_num;
110 END IF;
111
112 ELSE
113 /* Search for Passport Information. Assume only 1 passport information is available */
114 OPEN c_get_passport_no(p_person_id);
115 FETCH c_get_passport_no INTO l_passport;
116 IF l_passport IS NULL THEN
117 CLOSE c_get_passport_no;
118 l_doc_type := NULL;
119 l_doc_num := NULL;
120 return NULL;
121 ELSE
122 CLOSE c_get_passport_no;
123 l_doc_type := get_lookup_meaning('PASSPORT','CN_AUDIT_DATA');
124 l_doc_num := l_passport;
125 IF p_type='TYPE' then
126 RETURN l_doc_type;
127 ELSE
128 RETURN l_doc_num;
129 END IF;
130 END IF;
131 END IF;
132 ELSE
133 /* Citizen Identification Number found .*/
134 CLOSE c_get_cit_ident_no;
135 l_doc_type := get_lookup_meaning('CIN','CN_AUDIT_DATA');
136 l_doc_num := l_cin;
137 IF p_type='TYPE' then
138 RETURN l_doc_type;
139 ELSE
140 RETURN l_doc_num;
141 END IF;
142 END IF;
143
144 RETURN NULL;
145 END cn_get_doc_details;
146
147 /* Function to get parent organization id */
148 Function get_parent_org_id
149 (p_organization_id IN NUMBER
150 )
151 RETURN NUMBER IS
152 CURSOR c_parent_org_id IS
153 SELECT POSE.organization_id_parent organization_id
154 FROM per_org_structure_elements POSE
155 ,per_organization_structures POS
156 ,per_org_structure_versions POSV
157 WHERE POSV.org_structure_version_id = POSE.org_structure_version_id
158 AND POS.primary_structure_flag='Y'
159 AND POS.organization_structure_id = POSV.organization_structure_id
160 AND POSE.organization_id_child = p_organization_id
161 AND EXISTS (SELECT 1
162 FROM hr_organization_information info
163 WHERE info.org_information1 = 'HR_ORG'
164 AND info.org_information_context = 'CLASS'
165 AND info.organization_id = POSE.organization_id_parent
166 AND info.org_information2 = 'Y');
167 /* Need to understand Primary_Structure_Flag importance and what if the parent is a non-HR org */
168 /* Can we pass BG id from Fin Responsibility */
169
170 l_parent_org_id NUMBER;
171
172 BEGIN
173
174 OPEN c_parent_org_id;
175 FETCH c_parent_org_id INTO l_parent_org_id;
176 CLOSE c_parent_org_id;
177
178 RETURN l_parent_org_id;
179 END get_parent_org_id;
180
181 Function get_cadre_job_details(p_person_id IN NUMBER , p_date IN DATE)
182 return VARCHAR2 IS
183
184 CURSOR get_anal_cri_id
185 IS
186 select ANALYSIS_CRITERIA_ID
187 from PER_PERSON_ANALYSES
188 where person_id = p_person_id
189 and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
190 and p_date between date_from and nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
191 and ID_FLEX_NUM = (select ID_FLEX_NUM
192 from FND_ID_FLEX_STRUCTURES_VL
193 where ID_FLEX_CODE='PEA' and
194 ID_FLEX_STRUCTURE_CODE = 'PER_JOB_CLASS_INFO_CN'
195 )
196 order by date_from desc;
197
198 CURSOR get_details(p_anal_cri_id IN NUMBER) IS
199 select per_cn_shared_info.get_lookup_meaning(segment1,'CN_CADRE_JOB_CLASS') from PER_ANALYSIS_CRITERIA
200 where ANALYSIS_CRITERIA_ID = p_anal_cri_id
201 and enabled_flag = 'Y';
202
203 l_cad_job_class varchar2(1000);
204 l_cri_id NUMBER;
205
206 BEGIN
207
208 OPEN get_anal_cri_id;
209 FETCH get_anal_cri_id INTO l_cri_id;
210 CLOSE get_anal_cri_id;
211
212 IF l_cri_id IS NOT NULL THEN
213 OPEN get_details(l_cri_id);
214 FETCH get_details INTO l_cad_job_class;
215 CLOSE get_details;
216 ELSE
217 return NULL;
218 END IF;
219
220 return l_cad_job_class;
221
222 END get_cadre_job_details;
223
224 Function get_tech_post_details(p_person_id IN NUMBER , p_date IN DATE)
225 return VARCHAR2 IS
226
227 CURSOR get_anal_cri_id
228 IS
229 select ANALYSIS_CRITERIA_ID
230 from PER_PERSON_ANALYSES
231 where person_id = p_person_id
232 and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
233 and p_date between date_from and nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
234 and ID_FLEX_NUM = (select ID_FLEX_NUM
235 from FND_ID_FLEX_STRUCTURES_VL
236 where ID_FLEX_CODE='PEA' and
237 ID_FLEX_STRUCTURE_CODE = 'PER_TECH_PROF_POST_CN'
238 )
239 order by date_from desc;
240
241 CURSOR get_details(p_anal_cri_id IN NUMBER) IS
242 select per_cn_shared_info.get_lookup_meaning(segment1,'CN_TECH_TITLE') from PER_ANALYSIS_CRITERIA
243 where ANALYSIS_CRITERIA_ID = p_anal_cri_id
244 and enabled_flag = 'Y';
245
246 l_cri_id NUMBER;
247 l_tech_post_detail VARCHAR2(1000);
248
249 BEGIN
250
251 OPEN get_anal_cri_id;
252 FETCH get_anal_cri_id INTO l_cri_id;
253 CLOSE get_anal_cri_id;
254
255 IF l_cri_id IS NOT NULL THEN
256 OPEN get_details(l_cri_id);
257 FETCH get_details INTO l_tech_post_detail ;
258 CLOSE get_details;
259 ELSE
260 return NULL;
261 END IF;
262
263 return l_tech_post_detail;
264
265 END get_tech_post_details;
266
267 END per_cn_shared_info;
[Home] [Help]