1 PACKAGE BODY pqp_hross_reports AS
2 /* $Header: pqphrossrpt.pkb 120.6 2006/01/05 04:14 nkkrishn noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_debug BOOLEAN;
8 g_pkg CONSTANT VARCHAR2(150) := 'pqp_hross_reports.';
9
10 -- =============================================================================
11 -- ~ Compare_Values: Function to determine whether two strings are the same or
12 -- ~ different
13 -- =============================================================================
14 FUNCTION Compare_Values
15 (p_parameter1 IN VARCHAR2
16 ,p_parameter2 IN VARCHAR2
17 ) RETURN Varchar2 IS
18 l_parameter1 VARCHAR2(100);
19 l_parameter2 VARCHAR2(100);
20 l_return_status VARCHAR2(5);
21 l_proc_name CONSTANT VARCHAR2(150) := g_pkg||'Compare_Values';
22
23 BEGIN
24 Hr_utility.set_location('Entering:' || l_proc_name, 10);
25
26 IF p_parameter1 IS NOT NULL OR p_parameter2 IS NOT NULL THEN
27
28 --remove blank spaces from in between
29 SELECT REPLACE(p_parameter1, ' ') INTO l_parameter1 FROM DUAL;
30 SELECT REPLACE(p_parameter2, ' ') INTO l_parameter2 FROM DUAL;
31
32 IF l_parameter1 = l_parameter2 THEN
33 l_return_status := '=';
34 ELSE
35 l_return_status := '<>';
36 P_DATA_MISMATCH_FLAG := TRUE;
37 END IF;
38 END IF;
39 RETURN l_return_status;
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 Hr_Utility.set_location('Leaving:' || l_proc_name, 90);
44 RETURN l_return_status;
45
46 END Compare_Values;
47
48 -- =============================================================================
49 -- ~ Before_Report_Trigger:
50 -- =============================================================================
51 PROCEDURE Before_Report_Trigger IS
52
53 l_proc_name CONSTANT VARCHAR2(150):= g_pkg ||
54 'Before_Report_Trigger';
55 l_date_format VARCHAR2(20);
56 l_date_value VARCHAR2(20);
57 BEGIN
58 hr_utility.set_location('Entering: ' || l_proc_name, 10);
59
60 --Set the value of person id where clause
61 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_date_format);
62
63 GENERIC_WHERE := ' AND 1=1 ' ||
64 ' AND ppf.business_group_id LIKE (''' ||
65 P_BUSINESS_GROUP_ID || ''') ';
66
67 IF P_PERSON_TYPE IS NOT NULL AND P_PERSON_TYPE <> ' ' THEN
68 PERSON_TYPE_WHERE := ' AND ppt.person_type_id LIKE (''' ||
69 P_PERSON_TYPE || ''') ';
70 END IF;
71 IF P_ORGANIZATION_ID IS NOT NULL AND P_ORGANIZATION_ID <> ' ' THEN
72 ORGANNIZATION_NAME_WHERE := ' AND paf.organization_id LIKE (''' ||
73 P_ORGANIZATION_ID || ''') ';
74 END IF;
75 IF P_PAYROLL_ID IS NOT NULL AND P_PAYROLL_ID <> ' ' THEN
76 PAYROLL_NAME_WHERE := ' AND paf.payroll_id LIKE (''' ||
77 P_PAYROLL_ID || ''') ';
78 END IF;
79 IF P_LOCATION_ID IS NOT NULL AND P_LOCATION_ID <> ' ' THEN
80 LOCATION_NAME_WHERE := ' AND paf.location_id LIKE (''' ||
81 P_LOCATION_ID || ''') ';
82 END IF;
83 IF P_LAST_NAME IS NOT NULL AND P_LAST_NAME <> ' ' THEN
84 LAST_NAME_WHERE := ' AND lower(ppf.last_name) LIKE LOWER(''' ||
85 P_LAST_NAME || ''') ';
86 END IF;
87
88 IF P_FIRST_NAME IS NOT NULL AND P_FIRST_NAME <> ' ' THEN
89 FIRST_NAME_WHERE := ' AND lower(ppf.first_name) LIKE LOWER(''' ||
90 P_FIRST_NAME || ''') ';
91 END IF;
92
93 IF P_NATIONAL_IDENTIFIER IS NOT NULL AND P_NATIONAL_IDENTIFIER <> ' ' THEN
94 NATIONAL_IDENTIFIER_WHERE := ' AND ppf.national_identifier ' ||
95 ' LIKE (''' || P_NATIONAL_IDENTIFIER || ''') ';
96 END IF;
97
98 IF P_STUDENT_NUMBER IS NOT NULL AND P_STUDENT_NUMBER <> ' ' THEN
99 STUDENT_NUMBER_WHERE := ' AND ipe.person_number LIKE (''' ||
100 P_STUDENT_NUMBER || ''') ';
101 END IF;
102 IF P_PERSON_ID_GROUP_QUERY IS NOT NULL AND
103 P_PERSON_ID_GROUP_QUERY <> ' ' THEN
104 PERSON_ID_GROUP_QUERY_WHERE := ' AND ppf.party_id IN ' ||
105 P_PERSON_ID_GROUP_QUERY ;
106 END IF;
107 PERSON_ID_LIST_WHERE := GENERIC_WHERE || PERSON_TYPE_WHERE ||
108 ORGANNIZATION_NAME_WHERE || PAYROLL_NAME_WHERE ||
109 LOCATION_NAME_WHERE || LAST_NAME_WHERE ||
110 FIRST_NAME_WHERE || NATIONAL_IDENTIFIER_WHERE ||
111 STUDENT_NUMBER_WHERE || PERSON_ID_GROUP_QUERY_WHERE;
112
113 --Set the value of end date check where clause
114 PERSON_END_DATE_WHERE := ' AND ppf.effective_end_date = (SELECT max(ppf1.effective_end_date) ' ||
115 ' FROM per_people_f ppf1, per_person_types ppt1 ' ||
116 ' WHERE (to_date(''' || P_EFFECTIVE_START_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ' ||
117 ' OR to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ) ' ||
118 ' AND ppf1.person_id = ppf.person_id ' ||
119 ' AND ppf1.person_type_id = ppt1.person_type_id ' ||
120 ' AND ppt1.system_person_type in (''EMP'', ''EMP_APL'') ' ||
121 ' AND ppt1.active_flag = ''Y'') ';
122
123 -- PERSON_END_DATE_WHERE2 := ' AND to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between ppf.effective_start_date and ppf.effective_end_date ';
124 ASSIGNMENT_END_DATE_WHERE := ' AND (to_date(''' || P_EFFECTIVE_START_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ' ||
125 ' OR to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date )';
126 ADDRESS_END_DATE_WHERE := ' AND to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between per.date_from(+) and nvl(per.date_to(+), to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''')) ';
127
128 --Set the value of order by clause
129 ORDER_BY_CLAUSE := 'ORDER BY MismatchIndicatorFlag DESC, UPPER(LastName), UPPER(FirstName), NationalIdentifier';
130 IF P_SORT_BY = 'STU_LNM' THEN
131 ORDER_BY_CLAUSE := 'ORDER BY UPPER(LastName), UPPER(FirstName), NationalIdentifier';
132 END IF;
133 IF P_SORT_BY = 'STU_NID' THEN
134 ORDER_BY_CLAUSE := 'ORDER BY NationalIdentifier'; --Should be unique, so no need of secondary sort column
135 END IF;
136 IF P_SORT_BY = 'STU_NUM' THEN
137 ORDER_BY_CLAUSE := 'ORDER BY StudentNumber'; --Should be unique, so no need of secondary sort column
138 END IF;
139
140 --Set the value of data mismatch flag to false
141 P_DATA_MISMATCH_FLAG := FALSE;
142 MATCHING_RECORDS_COUNTER := 0;
143 MISMATCH_RECORDS_COUNTER := 0;
144
145 hr_utility.set_location('Leaving: ' || l_proc_name, 20);
146
147 END Before_Report_Trigger;
148
149 -- =============================================================================
150 -- ~ Before_Report_Trigger:
151 -- =============================================================================
152 FUNCTION Before_Report_Trigger RETURN BOOLEAN IS
153
154 l_proc_name CONSTANT VARCHAR2(150):= g_pkg ||
155 'Before_Report_Trigger';
156 l_date_format VARCHAR2(20);
157 l_date_value VARCHAR2(20);
158 BEGIN
159 hr_utility.set_location('Entering: ' || l_proc_name, 10);
160
161 --Set the value of person id where clause
162 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_date_format);
163
164 GENERIC_WHERE := ' AND 1=1 ';
165
166 IF P_PERSON_TYPE IS NOT NULL AND P_PERSON_TYPE <> ' ' THEN
167 PERSON_TYPE_WHERE := ' AND ppt.person_type_id LIKE (''' ||
168 P_PERSON_TYPE || ''') ';
169 END IF;
170 IF P_ORGANIZATION_ID IS NOT NULL AND P_ORGANIZATION_ID <> ' ' THEN
171 ORGANNIZATION_NAME_WHERE := ' AND paf.organization_id LIKE (''' ||
172 P_ORGANIZATION_ID || ''') ';
173 END IF;
174 IF P_PAYROLL_ID IS NOT NULL AND P_PAYROLL_ID <> ' ' THEN
175 PAYROLL_NAME_WHERE := ' AND paf.payroll_id LIKE (''' ||
176 P_PAYROLL_ID || ''') ';
177 END IF;
178 IF P_LOCATION_ID IS NOT NULL AND P_LOCATION_ID <> ' ' THEN
179 LOCATION_NAME_WHERE := ' AND paf.location_id LIKE (''' ||
180 P_LOCATION_ID || ''') ';
181 END IF;
182 IF P_LAST_NAME IS NOT NULL AND P_LAST_NAME <> ' ' THEN
183 LAST_NAME_WHERE := ' AND lower(ppf.last_name) LIKE LOWER(''' ||
184 P_LAST_NAME || ''') ';
185 END IF;
186
187 IF P_FIRST_NAME IS NOT NULL AND P_FIRST_NAME <> ' ' THEN
188 FIRST_NAME_WHERE := ' AND lower(ppf.first_name) LIKE LOWER(''' ||
189 P_FIRST_NAME || ''') ';
190 END IF;
191
192 IF P_NATIONAL_IDENTIFIER IS NOT NULL AND P_NATIONAL_IDENTIFIER <> ' ' THEN
193 NATIONAL_IDENTIFIER_WHERE := ' AND ppf.national_identifier ' ||
194 ' LIKE (''' || P_NATIONAL_IDENTIFIER || ''') ';
195 END IF;
196
197 IF P_STUDENT_NUMBER IS NOT NULL AND P_STUDENT_NUMBER <> ' ' THEN
198 STUDENT_NUMBER_WHERE := ' AND ipe.person_number LIKE (''' ||
199 P_STUDENT_NUMBER || ''') ';
200 END IF;
201 IF P_PERSON_ID_GROUP_QUERY IS NOT NULL AND
202 P_PERSON_ID_GROUP_QUERY <> ' ' THEN
203 PERSON_ID_GROUP_QUERY_WHERE := ' AND ppf.party_id IN ' ||
204 P_PERSON_ID_GROUP_QUERY ;
205 END IF;
206 PERSON_ID_LIST_WHERE := GENERIC_WHERE || PERSON_TYPE_WHERE ||
207 ORGANNIZATION_NAME_WHERE || PAYROLL_NAME_WHERE ||
208 LOCATION_NAME_WHERE || LAST_NAME_WHERE ||
209 FIRST_NAME_WHERE || NATIONAL_IDENTIFIER_WHERE ||
210 STUDENT_NUMBER_WHERE || PERSON_ID_GROUP_QUERY_WHERE;
211
212 --Set the value of end date check where clause
213 PERSON_END_DATE_WHERE := ' AND ppf.effective_end_date = (SELECT max(ppf1.effective_end_date) ' ||
214 ' FROM per_people_f ppf1, per_person_types ppt1 ' ||
215 ' WHERE (to_date(''' || P_EFFECTIVE_START_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ' ||
216 ' OR to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ) ' ||
217 ' AND ppf1.person_id = ppf.person_id ' ||
218 ' AND ppf1.person_type_id = ppt1.person_type_id ' ||
219 ' AND ppt1.system_person_type in (''EMP'', ''EMP_APL'') ' ||
220 ' AND ppt1.active_flag = ''Y'') ';
221
222
223 -- PERSON_END_DATE_WHERE2 := ' AND to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between ppf.effective_start_date and ppf.effective_end_date ';
224 ASSIGNMENT_END_DATE_WHERE := ' AND (to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date ' ||
225 ' OR to_date(''' || P_EFFECTIVE_START_DATE || ''', ''' || l_date_format || ''') between paf.effective_start_date and paf.effective_end_date) ';
226 ADDRESS_END_DATE_WHERE := ' AND to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''') between per.date_from(+) and nvl(per.date_to(+), to_date(''' || P_EFFECTIVE_END_DATE || ''', ''' || l_date_format || ''')) ';
227
228 --Set the value of order by clause
229 ORDER_BY_CLAUSE := 'ORDER BY MismatchIndicatorFlag DESC, UPPER(LastName), UPPER(FirstName), NationalIdentifier';
230 IF P_SORT_BY = 'STU_LNM' THEN
231 ORDER_BY_CLAUSE := 'ORDER BY UPPER(LastName), UPPER(FirstName), NationalIdentifier';
232 END IF;
233 IF P_SORT_BY = 'STU_NID' THEN
234 ORDER_BY_CLAUSE := 'ORDER BY NationalIdentifier'; --Should be unique, so no need of secondary sort column
235 END IF;
236 IF P_SORT_BY = 'STU_NUM' THEN
237 ORDER_BY_CLAUSE := 'ORDER BY StudentNumber'; --Should be unique, so no need of secondary sort column
238 END IF;
239
240 --Set the value of data mismatch flag to false
241 P_DATA_MISMATCH_FLAG := FALSE;
242 MATCHING_RECORDS_COUNTER := 0;
243 MISMATCH_RECORDS_COUNTER := 0;
244
245 hr_utility.set_location('Leaving: ' || l_proc_name, 20);
246 RETURN TRUE;
247 END Before_Report_Trigger;
248
249 -- =============================================================================
250 -- ~ Generate_Report:
254 ,p_business_group_name IN VARCHAR2
251 -- =============================================================================
252 PROCEDURE Generate_Report
253 (p_person_id_group_query IN VARCHAR2
255 ,p_business_group_id IN VARCHAR2
259 ,p_person_type_desc IN VARCHAR2
256 ,p_effective_start_date IN VARCHAR2
257 ,p_effective_end_date IN VARCHAR2
258 ,p_person_type IN VARCHAR2
260 ,p_organization_name IN VARCHAR2
261 ,p_organization_id IN VARCHAR2
262 ,p_payroll_name IN VARCHAR2
263 ,p_payroll_id IN VARCHAR2
264 ,p_location_value IN VARCHAR2
265 ,p_location_id IN VARCHAR2
266 ,p_person_id_group IN VARCHAR2
267 ,p_last_name IN VARCHAR2
268 ,p_first_name IN VARCHAR2
269 ,p_national_identifier IN VARCHAR2
270 ,p_student_number IN VARCHAR2
271 ,p_template_code IN VARCHAR2
272 ,p_template_lang IN VARCHAR2
273 ,p_template_ter IN VARCHAR2
274 ,p_output_format IN VARCHAR2
275 ,p_report_name IN VARCHAR2
276 ,p_data_match_filter IN VARCHAR2
277 ,p_report_run_date IN VARCHAR2
278 ,p_data_match_filter_desc IN VARCHAR2
279 ,p_sort_by IN VARCHAR2
280 ,p_sort_by_desc IN VARCHAR2
281 ,p_return_status IN OUT NOCOPY VARCHAR2) AS
282 l_proc_name CONSTANT VARCHAR2(150):= g_pkg ||
283 'Generate_Report';
284 l_request_id NUMBER;
285 l_layout_flag BOOLEAN;
286 BEGIN
287 hr_utility.set_location('Entering: ' || l_proc_name, 10);
288
289 l_layout_flag := fnd_request.add_layout(template_appl_name => 'PQP'
290 ,template_code => p_template_code
291 ,template_language => p_template_lang
292 ,template_territory => p_template_ter
293 ,output_format => p_output_format);
294
295 l_request_id := fnd_request.submit_request(application => 'PQP'
296 ,program => 'PQPCMPRPT'
297 ,description => p_report_name
298 ,argument1 => p_person_id_group_query
299 ,argument2 => p_business_group_name
300 ,argument3 => p_business_group_id
301 ,argument4 => p_effective_start_date
302 ,argument5 => p_effective_end_date
303 ,argument6 => p_person_type
304 ,argument7 => p_person_type_desc
308 ,argument11 => p_payroll_id
305 ,argument8 => p_organization_name
306 ,argument9 => p_organization_id
307 ,argument10 => p_payroll_name
309 ,argument12 => p_location_value
310 ,argument13 => p_location_id
311 ,argument14 => p_person_id_group
312 ,argument15 => p_last_name
313 ,argument16 => p_first_name
314 ,argument17 => p_national_identifier
315 ,argument18 => p_student_number
316 ,argument19 => p_report_name
317 ,argument20 => p_data_match_filter
318 ,argument21 => p_report_run_date
319 ,argument22 => p_data_match_filter_desc
320 ,argument23 => p_sort_by
321 ,argument24 => p_sort_by_desc);
322
323 COMMIT;
324 p_return_status := l_request_id;
325
326 hr_utility.set_location ('Request Id:' || l_request_id ,20);
327 hr_utility.set_location('Leaving: ' || l_proc_name, 30);
328
329 END Generate_Report;
330
331 -- =============================================================================
332 -- ~ Get_Date: Get Date field in session format
333 -- =============================================================================
334 FUNCTION Get_Date(p_date IN DATE) RETURN VARCHAR2 IS
335 l_return_value VARCHAR2(20);
336 l_date_format VARCHAR2(20);
337 BEGIN
338 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_date_format);
339 SELECT to_char(fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_date)), l_date_format) into l_return_value FROM dual;
340 RETURN l_return_value;
341 END Get_Date;
342
343 -- =============================================================================
344 -- ~ Get_Count: Function to return the count of matching and mismatching records
345 -- =============================================================================
346 Function Get_Count(p_parameter IN VARCHAR2) RETURN VARCHAR2 IS
347 l_return_value VARCHAR2(20);
348 BEGIN
349 IF p_parameter = 'MATCH_COUNT' THEN
350 l_return_value := MATCHING_RECORDS_COUNTER;
351 END IF;
352 IF p_parameter = 'MISMATCH_COUNT' THEN
353 l_return_value := MISMATCH_RECORDS_COUNTER;
354 END IF;
355 IF p_parameter = 'TOTAL_COUNT' THEN
356 l_return_value := MATCHING_RECORDS_COUNTER + MISMATCH_RECORDS_COUNTER;
357 END IF;
358 RETURN l_return_value;
359 END Get_Count;
360
361
362 -- =============================================================================
363 -- ~ Get_Mismatch_Indicator_Flag: Function to return Mismatch Indicator Flag
364 -- =============================================================================
365 FUNCTION Get_Mismatch_Indicator_Flag RETURN VARCHAR2 IS
366 l_return_value VARCHAR2(1);
367 BEGIN
368 IF P_DATA_MISMATCH_FLAG THEN
369 l_return_value := 'Y';
370 MISMATCH_RECORDS_COUNTER := MISMATCH_RECORDS_COUNTER + 1;
371 ELSE
372 l_return_value := 'N';
373 MATCHING_RECORDS_COUNTER := MATCHING_RECORDS_COUNTER + 1;
374 END IF;
375 P_DATA_MISMATCH_FLAG := FALSE;
376 RETURN l_return_value;
377 END Get_Mismatch_Indicator_Flag;
378
379 -- =============================================================================
380 -- ~ Record_Filter: Function to return Mismatch Indicator Flag
381 -- =============================================================================
382 FUNCTION Record_Filter(p_mismatch_indicator_flag IN VARCHAR2
383 ,p_full_name IN VARCHAR2) RETURN BOOLEAN IS
384 l_return_value BOOLEAN;
385 BEGIN
386
387 IF P_DATA_MATCH_FILTER = 'ALL_RECORDS' THEN
388 RETURN TRUE;
389 END IF;
390 IF P_DATA_MATCH_FILTER = 'MATCHING_RECORDS' THEN
391 IF p_mismatch_indicator_flag = 'Y' THEN
392 RETURN FALSE;
393 ELSE
394 RETURN TRUE;
395 END IF;
396 END IF;
397
398 IF P_DATA_MATCH_FILTER = 'MISMATCH_RECORDS' THEN
399 IF p_mismatch_indicator_flag = 'Y' THEN
400 RETURN TRUE;
401 ELSE
402 RETURN FALSE;
403 END IF;
404 END IF;
405 END Record_Filter;
406
407 -- =============================================================================
408 -- ~ Get_Primary_Telephone_Number: Returns the primary telephone number
409 -- =============================================================================
410 Function Get_Primary_Telephone_Number(p_owner_table_id IN VARCHAR2) RETURN VARCHAR2 IS
411
412 -- Cursor to get the primary telephone number
413 CURSOR csr_pri_telephone_num (c_owner_table_id IN VARCHAR2) IS
414 SELECT phone_country_code, phone_area_code, phone_number, phone_extension
415 FROM hz_contact_points
416 WHERE owner_table_id = c_owner_table_id
417 AND phone_line_type = 'GEN'
418 AND primary_flag = 'Y';
419
420 l_primary_return_value VARCHAR2(50);
421 l_primary_country_code hz_contact_points.phone_country_code%TYPE;
422 l_primary_area_code hz_contact_points.phone_area_code%TYPE;
423 l_primary_phone_number hz_contact_points.phone_number%TYPE;
424 l_primary_phone_extn hz_contact_points.phone_extension%TYPE;
425 BEGIN
426
427 IF (csr_pri_telephone_num%ISOPEN) THEN
428 CLOSE csr_pri_telephone_num;
429 END IF;
430
431 OPEN csr_pri_telephone_num (c_owner_table_id => p_owner_table_id);
432 FETCH csr_pri_telephone_num
433 INTO l_primary_country_code, l_primary_area_code, l_primary_phone_number, l_primary_phone_extn;
434 --Return blank if no primary telephone number found
435 IF csr_pri_telephone_num%NOTFOUND THEN
436 CLOSE csr_pri_telephone_num;
437 RETURN ' ';
438 END IF;
439 CLOSE csr_pri_telephone_num;
440
441
445 -- l_primary_return_value := '+' || l_primary_country_code;
442 --Else, return the telephone number in correct format
443 l_primary_return_value := ' ';
444 IF l_primary_country_code IS NOT NULL THEN
446 l_primary_return_value := l_primary_country_code;
447 END IF;
448 IF l_primary_area_code IS NOT NULL THEN
449 -- l_primary_return_value := l_primary_return_value || ' (' || l_primary_area_code || ')';
450 l_primary_return_value := l_primary_return_value || ' ' || l_primary_area_code;
451 END IF;
452 IF l_primary_phone_number IS NOT NULL THEN
453 l_primary_return_value := l_primary_return_value || ' ' || l_primary_phone_number;
454 END IF;
455 IF l_primary_phone_extn IS NOT NULL THEN
456 -- l_primary_return_value := l_primary_return_value || ' x ' || l_primary_phone_extn;
457 l_primary_return_value := l_primary_return_value || ' ' || l_primary_phone_extn;
458 END IF;
459
460 RETURN l_primary_return_value;
461
462 END Get_Primary_Telephone_Number;
463
464
465 -- =============================================================================
466 -- ~ Get_Secondary_Telephone_Number: Returns the secondary telephone number
467 -- =============================================================================
468 Function Get_Secondary_Telephone_Number(p_owner_table_id IN VARCHAR2) RETURN VARCHAR2 IS
469
470 -- Cursor to get the secondary telephone number
471 CURSOR csr_sec_telephone_num (c_owner_table_id IN VARCHAR2) IS
472 SELECT phone_country_code, phone_area_code, phone_number, phone_extension
473 FROM hz_contact_points
474 WHERE owner_table_id = c_owner_table_id
475 AND phone_line_type = 'GEN'
476 AND primary_flag <> 'Y'
477 AND creation_date = (SELECT MAX(creation_date)
478 FROM hz_contact_points
479 WHERE owner_table_id = c_owner_table_id
480 AND phone_line_type = 'GEN'
481 AND primary_flag <> 'Y');
482
483 l_secondary_return_value VARCHAR2(50);
484 l_secondary_country_code hz_contact_points.phone_country_code%TYPE;
485 l_secondary_area_code hz_contact_points.phone_area_code%TYPE;
486 l_secondary_phone_number hz_contact_points.phone_number%TYPE;
487 l_secondary_phone_extn hz_contact_points.phone_extension%TYPE;
488 BEGIN
489
490 IF (csr_sec_telephone_num%ISOPEN) THEN
491 CLOSE csr_sec_telephone_num;
492 END IF;
493
494 OPEN csr_sec_telephone_num (c_owner_table_id => p_owner_table_id);
495 FETCH csr_sec_telephone_num
496 INTO l_secondary_country_code, l_secondary_area_code, l_secondary_phone_number, l_secondary_phone_extn;
497 --Return blank if no secondary telephone number found
498 IF csr_sec_telephone_num%NOTFOUND THEN
499 CLOSE csr_sec_telephone_num;
500 RETURN ' ';
501 END IF;
502 CLOSE csr_sec_telephone_num;
503
504 --Else, return the telephone number in correct format
505 l_secondary_return_value := ' ';
506 IF l_secondary_country_code IS NOT NULL THEN
507 -- l_secondary_return_value := '+' || l_secondary_country_code;
508 l_secondary_return_value := l_secondary_country_code;
509 END IF;
510 IF l_secondary_area_code IS NOT NULL THEN
511 -- l_secondary_return_value := l_secondary_return_value || ' (' || l_secondary_area_code || ')';
512 l_secondary_return_value := l_secondary_return_value || ' ' || l_secondary_area_code;
513 END IF;
514 IF l_secondary_phone_number IS NOT NULL THEN
515 l_secondary_return_value := l_secondary_return_value || ' ' || l_secondary_phone_number;
516 END IF;
517 IF l_secondary_phone_extn IS NOT NULL THEN
518 -- l_secondary_return_value := l_secondary_return_value || ' x ' || l_secondary_phone_extn;
519 l_secondary_return_value := l_secondary_return_value || ' ' || l_secondary_phone_extn;
520 END IF;
521
522 RETURN l_secondary_return_value;
523
524 END Get_Secondary_Telephone_Number;
525
526 END pqp_hross_reports;
[Home] [Help]