1 PACKAGE BODY IGS_GE_GEN_002 AS
2 /* $Header: IGSGE02B.pls 120.3 2006/01/25 09:13:08 skpandey ship $ */
3
4 -------------------------------------------------------------------------------------------
5 -- Change History
6 -- Who When What
7 -- pkpatel 27-MAR-2003 Bug 2261717
8 -- Tuned genp_get_mail_addr, genp_get_pdv_name, genp_get_prsn_names procedures
9 -- asbala 29-DEC-2003 Bug 3330997. 10GCERT
10 -- ssawhney 4257183 igs_pe_person usage changed to igs_pe_person_base_v.
11 -- Perf tuned genp_get_person_name,genp_get_prsn_email,genp_get_prsn_names
12 --------------------------------------------------------------------------------------------
13
14 FUNCTION GENP_GET_DELIMIT_STR(
15 p_input_str IN VARCHAR2 ,
16 p_element_num IN NUMBER ,
17 p_delimiter IN VARCHAR2 DEFAULT ',')
18 RETURN VARCHAR2 AS
19 gv_other_detail VARCHAR2(255);
20 BEGIN -- genp_get_delemit_str
21 -- Parse the p_input_str, return the p_element_num_th
22 -- of the string delimited by p_delimiter.
23 DECLARE
24 v_ret_val VARCHAR2(1000);
25 v_start_position NUMBER(5);
26 v_end_position NUMBER(5);
27 BEGIN
28 -- Validate input parameter
29 IF (p_input_str IS NULL OR p_element_num IS NULL) THEN
30 RETURN NULL;
31 END IF;
32 IF (p_element_num = 1) THEN
33 v_start_position := 1;
34 ELSE
35 v_start_position := INSTR(p_input_str, p_delimiter, 1,
36 p_element_num - 1) + 1;
37 END IF;
38 v_end_position := INSTR(p_input_str, p_delimiter, 1, p_element_num) - 1;
39 IF (v_end_position = -1) THEN
40 IF (v_start_position <>1) THEN
41 -- The last element in the string
42 v_end_position := LENGTH(p_input_str);
43 ELSE
44 -- There not exists this element in the string
45 RETURN NULL;
46 END IF;
47 END IF;
48 v_ret_val := SUBSTR(p_input_str, v_start_position, v_end_position - v_start_position + 1);
49 RETURN v_ret_val;
50 END;
51 END genp_get_delimit_str;
52
53
54 FUNCTION genp_get_initials(
55 p_given_names IN VARCHAR2 )
56 RETURN VARCHAR2 AS
57 FUNCTION skip_spaces (
58 p_length_str IN NUMBER,
59 p_trimmed_string IN VARCHAR2,
60 p_current_pos IN NUMBER)
61 RETURN NUMBER AS
62 v_non_space_current_pos NUMBER;
63 v_letter_current_pos NUMBER;
64 v_other_detail VARCHAR2(255);
65 BEGIN
66 -- if the current position of the string is filled (ie.
67 -- a letter of the input names, continue looking until
68 -- we find a space
69 v_non_space_current_pos := p_current_pos;
70 while SUBSTR(p_trimmed_string, v_non_space_current_pos, 1) <>' '
71 LOOP
72 v_non_space_current_pos := v_non_space_current_pos + 1;
73 END LOOP;
74 v_letter_current_pos := v_non_space_current_pos;
75 -- if a space is found, continue until we find a letter
76 while SUBSTR(p_trimmed_string, v_letter_current_pos, 1) = ' '
77 LOOP
78 v_letter_current_pos := v_letter_current_pos + 1;
79 END LOOP;
80 -- return the position of the letter found
81 RETURN v_letter_current_pos;
82 END skip_spaces;
83 BEGIN
84 DECLARE
85 v_trimmed_string VARCHAR2(255);
86 v_string_length NUMBER;
87 v_first_letter CHAR;
88 v_returned_position NUMBER;
89 v_current_position NUMBER;
90 v_next_letter CHAR;
91 v_other_detail VARCHAR2(255);
92 v_final_output VARCHAR2(100);
93 BEGIN
94 -- removes the leading spaces from the initial string
95 v_trimmed_string := UPPER(LTRIM(p_given_names));
96 -- find out the number of characters in the names
97 -- entered
98 v_string_length := LENGTH(v_trimmed_string);
99 -- find out the letter of the first name
100 v_first_letter := SUBSTR(v_trimmed_string, 1, 1);
101 v_final_output := v_first_letter;
102 -- the current position that we are pointing to
103 -- in the string of names
104 v_current_position := 1;
105 -- continue until all letters of the string have
106 -- been accounted for
107 WHILE (v_current_position <= v_string_length)
108 LOOP
109 -- this call returned_position which returns the
110 -- position of the next space in the the string
111 v_returned_position := skip_spaces(
112 v_string_length,
113 v_trimmed_string,
114 v_current_position);
115 -- this find the next letter after the space
116 v_next_letter := SUBSTR(v_trimmed_string, v_returned_position, 1);
117 -- this concatenates the initial to v_final_output
118 v_final_output := v_final_output || v_next_letter;
119 -- set the current position to the position returned from
120 -- skip_spaces, so the position is now at the next non-space
121 v_current_position := v_returned_position;
122 END LOOP;
123 RETURN v_final_output;
124 END;
125 END genp_get_initials;
126
127
128 FUNCTION genp_get_mail_addr(
129 p_person_id NUMBER ,
130 p_org_unit_cd VARCHAR2 ,
131 p_institution_cd VARCHAR2 ,
132 p_location_cd VARCHAR2 ,
133 p_addr_type VARCHAR2 ,
134 p_case_type VARCHAR2 DEFAULT 'UPPER',
135 p_phone_no VARCHAR2 DEFAULT 'Y',
136 p_name_style VARCHAR2 DEFAULT 'CONTEXT',
137 p_inc_addr VARCHAR2 DEFAULT 'Y')
138 RETURN VARCHAR2 AS
139 -------------------------------------------------------------------------------------------
140 -- Change History
141 -- Who When What
142 -- pkpatel 27-MAR-2003 Bug 2261717
143 -- Removed the initial_last_name from igs_pe_person_v and write specific cursor to find the value.
144 -- masehgal 05-June-2002 # 2382471 Added Country,State and Zip Code to Address in the
145 -- local function genp_get_per_addr
146 --
147 --------------------------------------------------------------------------------------------
148 v_line_1 VARCHAR2(256) := NULL; -- first line of address
149 v_line_2 VARCHAR2(256) := NULL; -- second line of address
150 v_line_3 VARCHAR2(256) := NULL; -- third line of address
151 v_line_4 VARCHAR2(256) := NULL; -- 4th line of address
152 v_line_5 VARCHAR2(256) := NULL; -- 5th line of address
153 v_addr VARCHAR2(2000) := NULL; -- final address variable
154 v_phone VARCHAR2(100) := NULL; -- placeholder for phone handling
155 v_name VARCHAR2(256) := NULL; -- IGS_PE_PERSON name placeholder
156 -- # 2382471 Added for Country,State and Zip Code
157 v_state VARCHAR2(256) := NULL; -- State
158 v_postal_code VARCHAR2(256) := NULL; -- ZIP CODE
159 v_country_desc VARCHAR2(256) := NULL; -- Country
160
161 gv_other_detail VARCHAR2(1000) := NULL; -- global for error trapping
162 -- Local IGS_GE_EXCEPTIONS
163 e_addr EXCEPTION; -- overall exception for trapping and handling errors
164 e_case_error EXCEPTION; -- case type error
165 --
166 -- Local Functions
167 -------------------------------------------------------------------------------
168 -- Module: genp_get_per_addr
169 -- Purpose: Function for returning formatted IGS_PE_PERSON names and addresses
170 -- based on variations of the parameters passed
171 -- Notes:
172 -- p_surname_first is a boolean to place the surname before the given name
173 -- TRUE formats in surname + , + IGS_PE_TITLE + given name
174 -- FALSE formats in IGS_PE_TITLE + given name + surname
175 --
176 -- p_phone_no is used to toggle the display of the phone number
177 -- Y populates the p_phone_line parameter with the phone number
178 -- N populates the p_phone_line parameter with a null value
179 -- Exception Handlers
180 -- e_name_error: returns false and populates the p_name variable with
181 -- 'IGS_PE_PERSON name not found'
182 -- e_addr_error: returns false and populates p_line_1 with
183 -- 'No address record found'
184 --
185 -- Module History
186 -------------------------------------------------------------------------------
187 FUNCTION genp_get_per_addr(
188 p_per_id NUMBER,
189 p_adr_type VARCHAR2,
190 p_phone_num VARCHAR2,
191 p_name_style VARCHAR2,
192 p_name OUT NOCOPY VARCHAR2,
193 p_line_1 OUT NOCOPY VARCHAR2,
194 p_line_2 OUT NOCOPY VARCHAR2,
195 p_line_3 OUT NOCOPY VARCHAR2,
196 p_line_4 OUT NOCOPY VARCHAR2,
197 p_line_5 OUT NOCOPY VARCHAR2,
198 p_phone_line OUT NOCOPY VARCHAR2,
199 -- # 2382471 Added for Country,State and Zip Code
200 p_state OUT NOCOPY VARCHAR2,
201 p_postal_code OUT NOCOPY VARCHAR2,
202 p_country_desc OUT NOCOPY VARCHAR2)
203 RETURN BOOLEAN AS
204
205 BEGIN
206 DECLARE
207 -- Local Cursors
208 -- cursor for selection of the IGS_PE_PERSON name in seperate parts to allow
209 -- construction based on the user preferences
210 --skpandey, Bug#4937960: Changed c_per_name cursor definition to optimize query
211 CURSOR c_per_name (cp_person_id NUMBER)IS
212 SELECT p.PERSON_TITLE per_title,
213 p.PERSON_LAST_NAME per_surname,
214 NVL(P.KNOWN_AS,p.PERSON_FIRST_NAME) per_first_name,
215 NVL(P.KNOWN_AS, SUBSTR (P.PERSON_FIRST_NAME, 1, DECODE(INSTR(P.PERSON_FIRST_NAME, ' '), 0, LENGTH(P.PERSON_FIRST_NAME), (INSTR(P.PERSON_FIRST_NAME, ' ')-1)))) || ' ' || P.PERSON_LAST_NAME per_preferred_name ,
216 P.PERSON_TITLE || ' ' || p.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME per_title_name ,
217 p.PERSON_LAST_NAME || ', ' || p.PERSON_TITLE || ' ' || NVL(p.KNOWN_AS,p.PERSON_FIRST_NAME) per_context_block_name
218 FROM hz_parties p
219 WHERE p.party_id = cp_person_id;
220
221
222
223 -- cursor for selection of the IGS_PE_PERSON address when
224 -- only the person_id is supplied
225 -- ssawhney, changing to hz_parties, as co=Y record is checked.
226
227 CURSOR c_pa(
228 cp_person_id NUMBER)IS
229 SELECT padv.party_id padv_person_id,
230 hpsu.site_use_type padv_addr_type,
231 padv.address1 padv_addr_line_1,
232 padv.address2 padv_addr_line_2,
233 padv.address3 padv_addr_line_3,
234 padv.address4 padv_addr_line_4,
235 padv.city padv_city,
236 -- # 2382471 Added for Country ,State and Zip Code
237 padv.state padv_state,
238 padv.postal_code padv_postal_code,
239 fnd.TERRITORY_SHORT_NAME padv_country_desc
240 FROM
241 hz_parties padv,
242 hz_party_sites hps,
243 hz_party_site_uses hpsu,
244 fnd_territories_vl fnd
245 WHERE padv.party_id = cp_person_id AND
246 padv.party_type = 'PERSON' AND
247 padv.country = fnd.territory_code AND
248 hps.party_id = padv.party_id AND
249 hps.identifying_address_flag ='Y' AND
250 hps.party_site_id = hpsu.party_site_id (+) ;
251
252 --padv.correspondence_ind = 'Y';
253
254 -- cursor for selection of the IGS_PE_PERSON address when
255 -- only the person_id and IGS_CO_ADDR_TYPE is supplied
256 CURSOR c_pat(
257 cp_person_id NUMBER,
258 cp_addr_type VARCHAR2)IS
259 SELECT padv.person_id padv_person_id,
260 padv.addr_type padv_addr_type,
261 padv.addr_line_1 padv_addr_line_1,
262 padv.addr_line_2 padv_addr_line_2,
263 padv.addr_line_3 padv_addr_line_3,
264 padv.addr_line_4 padv_addr_line_4,
265 padv.city padv_city,
266 -- # 2382471 Added for Country ,State and Zip Code
267 padv.state padv_state,
268 padv.postal_code padv_postal_code,
269 padv.country_desc padv_country_desc
270 FROM IGS_PE_PERSON_ADDR_V padv
271 WHERE padv.person_id = cp_person_id AND
272 padv.addr_type = cp_addr_type;
273
274 CURSOR initial_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
275 SELECT SUBSTR(igs_ge_gen_002.genp_get_initials(person_first_name), 1, 10) || ' ' || person_last_name
276 FROM hz_parties
277 WHERE party_id = cp_person_id;
278
279
280 CURSOR initial_last_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
281 SELECT RTRIM(DECODE(person_last_name,null,'',DECODE(person_first_name,null,person_last_name,person_last_name
282 || ', ' ) ) || NVL(person_first_name,'')|| ' '||person_middle_name,' ')
283 FROM hz_parties
284 WHERE party_id = cp_person_id;
285
286 v_name VARCHAR2(256) := NULL;
287 v_line_1 VARCHAR2(256) := NULL;
288
289 e_name_error EXCEPTION; -- IGS_PE_PERSON name exception handler
290 e_addr_error EXCEPTION; -- IGS_PE_PERSON address exception handler
291
292 BEGIN
293 -- test for open cursor, then loop and select the persons name
294 IF (c_per_name%ISOPEN) THEN
295 CLOSE c_per_name;
296 END IF;
297 FOR c_per_rec IN c_per_name(p_per_id)LOOP
298
299 IF p_name_style = 'PREFER' THEN
300 v_name := c_per_rec.per_title || ' ' ||
301 c_per_rec.per_preferred_name;
302 ELSIF p_name_style = 'TITLE' THEN
303 v_name := c_per_rec.per_title_name;
304 ELSIF p_name_style = 'INIT_F' THEN
305
306 OPEN initial_name_cur(p_per_id);
307 FETCH initial_name_cur INTO v_name;
308 CLOSE initial_name_cur;
309
310 ELSIF p_name_style = 'INIT_L' THEN
311
312 OPEN initial_last_name_cur(p_per_id);
313 FETCH initial_last_name_cur INTO v_name;
314 CLOSE initial_last_name_cur;
315
316 ELSIF p_name_style = 'CONTEXT' THEN
317 v_name := c_per_rec.per_context_block_name;
318 ELSIF p_name_style = 'SALUTAT' THEN
319
320 v_name := c_per_rec.per_title || ' ' || c_per_rec.per_surname;
321
322 ELSE
323 v_name := c_per_rec.per_context_block_name;
324 END IF; -- IF p_name_style
325
326 -- Determin if p_addr_type is passed and open correct cursor
327 IF p_adr_type IS NULL THEN
328 FOR c_pa_rec IN c_pa(p_per_id) LOOP
329 v_line_1 := c_pa_rec.padv_addr_line_1;
330 p_line_2 := c_pa_rec.padv_addr_line_2;
331 p_line_3 := c_pa_rec.padv_addr_line_3;
332 p_line_4 := c_pa_rec.padv_addr_line_4;
333 p_line_5 := c_pa_rec.padv_city;
334 -- # 2382471 Added for Country ,State and Zip Code
335 p_state := c_pa_rec.padv_state;
336 p_postal_code := c_pa_rec.padv_postal_code;
337 p_country_desc := c_pa_rec.padv_country_desc ;
338 END LOOP;
339 ELSE
340 FOR c_pat_rec IN c_pat(p_per_id, p_adr_type) LOOP
341 v_line_1 := c_pat_rec.padv_addr_line_1;
342 p_line_2 := c_pat_rec.padv_addr_line_2;
343 p_line_3 := c_pat_rec.padv_addr_line_3;
344 p_line_4 := c_pat_rec.padv_addr_line_4;
345 p_line_5 := c_pat_rec.padv_city;
346 -- # 2382471 Added for Country ,State and Zip Code
347 p_state := c_pat_rec.padv_state;
348 p_postal_code := c_pat_rec.padv_postal_code;
349 p_country_desc := c_pat_rec.padv_country_desc ;
350 END LOOP;
351 END IF;
352 END LOOP;
353 -- test if name has been selected
354 IF v_name IS NULL THEN
355 RAISE e_name_error;
356 ELSE
357 p_name := v_name;
358 END IF;
359 -- test if name has been selected
360 IF v_line_1 IS NULL THEN
361 RAISE e_addr_error;
362 ELSE
363 p_line_1 := v_line_1;
364 END IF;
365 RETURN TRUE;
366 EXCEPTION
367 WHEN e_name_error THEN
368 p_name := 'Person name not found';
369 RETURN FALSE;
370 WHEN e_addr_error THEN
371 p_line_1 := 'No Address Record Found';
372 RETURN TRUE;
373 WHEN OTHERS THEN
374 APP_EXCEPTION.RAISE_EXCEPTION ;
375 END;
376 EXCEPTION
377 WHEN OTHERS THEN
378 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
379 IGS_GE_MSG_STACK.ADD;
380 APP_EXCEPTION.RAISE_EXCEPTION;
381 RETURN FALSE;
382 END genp_get_per_addr;
383 -------------------------------------------------------------------------------
384 -- Module: genp_get_org_addr
385 -- Purpose: Function for returning formatted IGS_OR_UNIT names and addresses
386 -- based on variations of the parameters passed
387 -- Notes:
388 -- p_phone_no is used to toggle the display of the phone number
389 -- Y populates the p_phone_line parameter with the phone number
390 -- N populates the p_phone_line parameter with a null value
391 -- Exception Handlers
392 -- e_name_error: returns false and populates the p_name variable with
393 -- 'Org IGS_PS_UNIT not found'
394 -- e_addr_error: returns false and populates p_line_1 with
395 -- 'No address record found'
396 --
397 -- Module History
398 -------------------------------------------------------------------------------
399 -- 03/03/1998 MSONTER Intial creation of Module
400 -- 05/03/1998 MSONTER Modified cursor c_org_name to search
401 -- IGS_CO_ADDR_TYPE.IGS_CO_TYPE = 'Y'
402 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
403 -------------------------------------------------------------------------------
404 FUNCTION genp_get_org_addr(
405 p_org_unit_cd VARCHAR2,
406 p_addr_type VARCHAR2,
407 p_phone_no VARCHAR2,
408 p_name OUT NOCOPY VARCHAR2,
409 p_line_1 OUT NOCOPY VARCHAR2,
410 p_line_2 OUT NOCOPY VARCHAR2,
411 p_line_3 OUT NOCOPY VARCHAR2,
412 p_line_4 OUT NOCOPY VARCHAR2,
413 p_line_5 OUT NOCOPY VARCHAR2,
414 p_phone_line OUT NOCOPY VARCHAR2)
415 RETURN BOOLEAN
416 AS
417 -- cursor for selection of the IGS_OR_UNIT name
418 CURSOR c_org_name (
419 cp_org_unit_cd VARCHAR2)IS
420 SELECT ou.description ou_description
421 FROM IGS_OR_UNIT ou
422 WHERE ou.org_unit_cd = cp_org_unit_cd;
423 -- cursor for selection of the IGS_OR_UNIT address when
424 -- only the org_unit_cd is supplied
425 CURSOR c_ou(
426 cp_org_unit_cd VARCHAR2)IS
427 SELECT oadv.org_unit_cd oadv_org_unit_cd,
428 oadv.addr_type oadv_addr_type,
429 oadv.addr_line_1 oadv_addr_line_1,
430 oadv.addr_line_2 oadv_addr_line_2,
431 oadv.addr_line_3 oadv_addr_line_3,
432 oadv.addr_line_4 oadv_addr_line_4,
433 oadv.city oadv_city
434 FROM IGS_OR_ADDR oadv
435 WHERE oadv.org_unit_cd = cp_org_unit_cd AND
436 oadv.correspondence_ind = 'Y';
437
438 -- cursor for selection of the IGS_OR_UNIT address when
439 -- only the org_unit_cd and IGS_CO_ADDR_TYPE is supplied
440 CURSOR c_out(
441 cp_org_unit_cd VARCHAR2,
442 cp_addr_type VARCHAR2) IS
443 SELECT oadv.org_unit_cd oadv_org_unit_cd,
444 oadv.addr_type oadv_addr_type,
445 oadv.addr_line_1 oadv_addr_line_1,
446 oadv.addr_line_2 oadv_addr_line_2,
447 oadv.addr_line_3 oadv_addr_line_3,
448 oadv.addr_line_4 oadv_addr_line_4,
449 oadv.city oadv_city
450 FROM IGS_OR_ADDR oadv
451 WHERE oadv.org_unit_cd = cp_org_unit_cd AND
452 oadv.addr_type = cp_addr_type;
453
454 -- Local Variables
455 v_name VARCHAR2(256) := NULL;
456 v_line_1 VARCHAR2(256) := NULL;
457 -- Local IGS_GE_EXCEPTIONS
458 e_name_error EXCEPTION; -- IGS_OR_UNIT name exception handler
459 e_addr_error EXCEPTION; -- IGS_OR_UNIT address exception handler
460 BEGIN
461 -- test for open cursor, then loop and select the persons name
462 IF c_org_name%ISOPEN THEN
463 CLOSE c_org_name;
464 END IF;
465 FOR c_org_rec IN c_org_name(
466 p_org_unit_cd) LOOP
467 v_name := c_org_rec.ou_description;
468 -- Determin if p_addr_type is passed and open correct cursor
469 IF p_addr_type IS NULL THEN
470 FOR c_ou_rec IN c_ou(
471 p_org_unit_cd) LOOP
472 v_line_1 := c_ou_rec.oadv_addr_line_1;
473 p_line_2 := c_ou_rec.oadv_addr_line_2;
474 p_line_3 := c_ou_rec.oadv_addr_line_3;
475 p_line_4 := c_ou_rec.oadv_addr_line_4;
476 p_line_5 := c_ou_rec.oadv_city;
477 END LOOP; -- FOR c_ou_rec IN c_ou(p_org_unit_cd)
478 ELSE
479 FOR c_out_rec IN c_out(
480 p_org_unit_cd,
481 p_addr_type) LOOP
482 v_line_1 := c_out_rec.oadv_addr_line_1;
483 p_line_2 := c_out_rec.oadv_addr_line_2;
484 p_line_3 := c_out_rec.oadv_addr_line_3;
485 p_line_4 := c_out_rec.oadv_addr_line_4;
486 p_line_5 := c_out_rec.oadv_city;
487 END LOOP; --
488 END IF;
489 END LOOP;
490 -- test if name has been selected
491 IF v_name IS NULL THEN
492 RAISE e_name_error;
493 ELSE
494 p_name := v_name;
495 END IF;
496 -- test if name has been selected
497 IF v_line_1 IS NULL THEN
498 RAISE e_addr_error;
499 ELSE
500 p_line_1 := v_line_1;
501 END IF;
502 RETURN TRUE;
503 EXCEPTION
504 WHEN e_name_error THEN
505 p_name := 'Org Unit not found';
506 RETURN FALSE;
507 WHEN e_addr_error THEN
508 p_line_1 := 'No Address Record Found';
509 RETURN TRUE;
510 WHEN OTHERS THEN
511 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
512 IGS_GE_MSG_STACK.ADD;
513 App_Exception.Raise_Exception ;
514 END genp_get_org_addr;
515 -------------------------------------------------------------------------------
516 -- Module: genp_get_loc_addr
517 -- Purpose: Function for returning formatted IGS_AD_LOCATION names and addresses
518 -- based on variations of the parameters passed
519 -- Notes:
520 -- p_phone_no is used to toggle the display of the phone number
521 -- Y populates the p_phone_line parameter with the phone number
522 -- N populates the p_phone_line parameter with a null value
523 -- Exception Handlers
524 -- e_name_error: returns false and populates the p_name variable with
525 -- 'IGS_AD_LOCATION not found'
526 -- e_addr_error: returns false and populates p_line_1 with
527 -- 'No address record found'
528 -- Module History
529 -------------------------------------------------------------------------------
530 -- 04/03/1998 MSONTER Intial creation of Module
531 -- 05/03/1998 MSONTER Modified cursor c_loc_name to search
532 -- IGS_CO_ADDR_TYPE.IGS_CO_TYPE = 'Y'
533 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
534 -------------------------------------------------------------------------------
535 FUNCTION genp_get_loc_addr(
536 p_location_cd VARCHAR2,
537 p_addr_type VARCHAR2,
538 p_phone_no VARCHAR2,
539 p_name OUT NOCOPY VARCHAR2,
540 p_line_1 OUT NOCOPY VARCHAR2,
541 p_line_2 OUT NOCOPY VARCHAR2,
542 p_line_3 OUT NOCOPY VARCHAR2,
543 p_line_4 OUT NOCOPY VARCHAR2,
544 p_line_5 OUT NOCOPY VARCHAR2,
545 p_phone_line OUT NOCOPY VARCHAR2)
546 RETURN BOOLEAN
547 AS
548 -- cursor for selection of the IGS_AD_LOCATION name
549 CURSOR c_loc_name (
550 cp_location_cd VARCHAR2)IS
551 SELECT loc.description loc_description
552 FROM IGS_AD_LOCATION loc
553 WHERE loc.location_cd = cp_location_cd;
554 -- cursor for selection of the IGS_AD_LOCATION address when
555 -- only the loc_unit_cd is supplied
556 -- skpandey, Bug#3687111, Changed definition of cursor c_loc to optimize query
557 CURSOR c_loc(
558 cp_location_cd VARCHAR2)IS
559 SELECT
560 LA.LOCATION_VENUE_CD ladv_location_cd,
561 HL.ADDRESS1 ladv_addr_line_1,
562 HL.ADDRESS2 ladv_addr_line_2,
563 HL.ADDRESS3 ladv_addr_line_3,
564 HL.ADDRESS4 ladv_addr_line_4,
565 HL.CITY ladv_city
566 FROM
567 HZ_LOCATIONS HL,
568 IGS_AD_LOCVENUE_ADDR LA
569 WHERE
570 HL.LOCATION_ID = LA.LOCATION_ID
571 AND LA.SOURCE_TYPE = 'L'
572 AND LA.LOCATION_VENUE_CD = cp_location_cd
573 AND LA.IDENTIFYING_ADDRESS_FLAG = 'Y' ;
574
575 -- cursor for selection of the IGS_AD_LOCATION address when
576 -- only the location_cd and IGS_CO_ADDR_TYPE is supplied
577 -- skpandey, Bug#3687111, Changed definition of cursor c_loct to optimize query
578 CURSOR c_loct(
579 cp_location_cd VARCHAR2,
580 cp_addr_type VARCHAR2)IS
581 SELECT
582 LA.LOCATION_VENUE_CD ladv_location_cd,
583 HL.ADDRESS1 ladv_addr_line_1,
584 HL.ADDRESS2 ladv_addr_line_2,
585 HL.ADDRESS3 ladv_addr_line_3,
586 HL.ADDRESS4 ladv_addr_line_4,
587 HL.CITY ladv_city
588 FROM
589 HZ_LOCATIONS HL,
590 IGS_AD_LOCVENUE_ADDR LA ,
591 IGS_PE_LOCVENUE_USE PLU
592 WHERE
593 HL.LOCATION_ID = LA.LOCATION_ID
594 AND LA.LOCATION_VENUE_ADDR_ID = PLU.LOC_VENUE_ADDR_ID
595 AND LA.SOURCE_TYPE = 'L'
596 AND LA.LOCATION_VENUE_CD = cp_location_cd
597 AND PLU.SITE_USE_CODE = cp_addr_type;
598
599 -- Local Variables
600 v_name VARCHAR2(256) := NULL;
601 v_line_1 VARCHAR2(256) := NULL;
602 -- Local IGS_GE_EXCEPTIONS
603 e_name_error EXCEPTION; -- locationt name exception handler
604 e_addr_error EXCEPTION; -- IGS_AD_LOCATION address exception handler
605 BEGIN
606 -- test for open cursor, then loop and select the persons name
607 IF c_loc_name%ISOPEN THEN
608 CLOSE c_loc_name;
609 END IF;
610 FOR c_loc_rec IN c_loc_name(
611 p_location_cd) LOOP
612 v_name := c_loc_rec.loc_description;
613 -- Determin if p_addr_type is passed and open correct cursor
614 IF p_addr_type IS NULL THEN
615 FOR c_loc_rec IN c_loc(
616 p_location_cd)LOOP
617 v_line_1 := c_loc_rec.ladv_addr_line_1;
618 p_line_2 := c_loc_rec.ladv_addr_line_2;
619 p_line_3 := c_loc_rec.ladv_addr_line_3;
620 p_line_4 := c_loc_rec.ladv_addr_line_4;
621 p_line_5 := c_loc_rec.ladv_city;
622 END LOOP;
623 ELSE
624 FOR c_loct_rec IN c_loct(
625 p_location_cd,
626 p_addr_type)LOOP
627 v_line_1 := c_loct_rec.ladv_addr_line_1;
628 p_line_2 := c_loct_rec.ladv_addr_line_2;
629 p_line_3 := c_loct_rec.ladv_addr_line_3;
630 p_line_4 := c_loct_rec.ladv_addr_line_4;
631 p_line_5 := c_loct_rec.ladv_city;
632 END LOOP;
633 END IF;
634 END LOOP;
635 -- test if name has been selected
636 IF v_name IS NULL THEN
637 RAISE e_name_error;
638 ELSE
639 p_name := v_name;
640 END IF;
641 -- test if name has been selected
642 IF v_line_1 IS NULL THEN
643 RAISE e_addr_error;
644 ELSE
645 p_line_1 := v_line_1;
646 END IF;
647 RETURN TRUE;
648 EXCEPTION
649 WHEN e_name_error THEN
650 p_name := 'Location Code not found';
651 RETURN FALSE;
652 WHEN e_addr_error THEN
653 p_line_1 := 'No Address Record Found';
654 RETURN TRUE;
655 WHEN OTHERS THEN
656 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
657 IGS_GE_MSG_STACK.ADD;
658 App_Exception.Raise_Exception ;
659 END genp_get_loc_addr;
660 -------------------------------------------------------------------------------
661 -- Module: genp_get_inst_addr
662 -- Purpose: Function for returning formatted IGS_OR_INSTITUTION names and addresses
663 -- based on variations of the parameters passed
664 -- Notes:
665 -- p_phone_no is used to toggle the display of the phone number
666 -- Y populates the p_phone_line parameter with the phone number
667 -- N populates the p_phone_line parameter with a null value
668 -- Exception Handlers
669 -- e_name_error: returns false and populates the p_name variable with
670 -- 'IGS_OR_INSTITUTION not found'
671 -- e_addr_error: returns false and populates p_line_1 with
672 -- 'No address record found'
673 --
674 -- Module History
675 ----------------------------------------------------------------------
676 -- 04/03/1998 MSONTER Intial creation of Module
677 -- 05/03/1998 MSONTER Modified cursor c_inst_name to search
678 -- IGS_CO_ADDR_TYPE.IGS_CO_TYPE = 'Y'
679 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
680 -- 19/03/1998 MSONTER Moved to local function of genp_get_addr
681 -------------------------------------------------------------------------------
682 FUNCTION genp_get_inst_addr(
683 p_institution_cd VARCHAR2,
684 p_addr_type VARCHAR2,
685 p_phone_no VARCHAR2,
686 p_name OUT NOCOPY VARCHAR2,
687 p_line_1 OUT NOCOPY VARCHAR2,
688 p_line_2 OUT NOCOPY VARCHAR2,
689 p_line_3 OUT NOCOPY VARCHAR2,
690 p_line_4 OUT NOCOPY VARCHAR2,
691 p_line_5 OUT NOCOPY VARCHAR2,
692 p_phone_line OUT NOCOPY VARCHAR2)
693 RETURN BOOLEAN
694 AS
695 -- cursor for selection of the IGS_OR_INSTITUTION name
696 CURSOR c_inst_name (
697 cp_institution_cd VARCHAR2)IS
698 SELECT inst.name inst_name
699 FROM IGS_OR_INSTITUTION inst
700 WHERE inst.institution_cd = cp_institution_cd;
701 -- cursor for selection of the IGS_OR_INSTITUTION address when
702 -- only the loc_unit_cd is supplied
703 CURSOR c_ins(
704 cp_institution_cd VARCHAR2)IS
705 SELECT iadv.institution_cd iadv_institution_cd,
706 iadv.addr_type iadv_addr_type,
707 iadv.addr_line_1 iadv_addr_line_1,
708 iadv.addr_line_2 iadv_addr_line_2,
709 iadv.addr_line_3 iadv_addr_line_3,
710 iadv.addr_line_4 iadv_addr_line_4,
711 iadv.city iadv_city
712 FROM IGS_OR_INST_ADDR iadv
713 WHERE iadv.institution_cd = cp_institution_cd AND
714 iadv.correspondence_ind = 'Y';
715
716 -- cursor for selection of the IGS_OR_INSTITUTION address when
717 -- only the institution_cd and IGS_CO_ADDR_TYPE is supplied
718 CURSOR c_inst(
719 cp_institution_cd VARCHAR2,
720 cp_addr_type VARCHAR2)IS
721 SELECT iadv.institution_cd iadv_institution_cd,
722 iadv.addr_type iadv_addr_type,
723 iadv.addr_line_1 iadv_addr_line_1,
724 iadv.addr_line_2 iadv_addr_line_2,
725 iadv.addr_line_3 iadv_addr_line_3,
726 iadv.addr_line_4 iadv_addr_line_4,
727 iadv.city iadv_city
728 FROM IGS_OR_INST_ADDR iadv
729 WHERE iadv.institution_cd = cp_institution_cd AND
730 iadv.addr_type = cp_addr_type;
731
732 -- Local Variables
733 v_name VARCHAR2(256) := NULL;
734 v_line_1 VARCHAR2(256) := NULL;
735 -- Local IGS_GE_EXCEPTIONS
736 e_name_error EXCEPTION; -- institutiont name exception handler
737 e_addr_error EXCEPTION; -- IGS_OR_INSTITUTION address exception handler
738 BEGIN
739 -- test for open cursor, then loop and select the persons name
740 IF c_inst_name%ISOPEN THEN
741 CLOSE c_inst_name;
742 END IF;
743 FOR c_instit_rec IN c_inst_name(
744 p_institution_cd) LOOP
745 v_name := c_instit_rec.inst_name;
746 -- Determin if p_addr_type is passed and open correct cursor
747 IF p_addr_type IS NULL THEN
748 FOR c_ins_rec IN c_ins(
749 p_institution_cd) LOOP
750 v_line_1 := c_ins_rec.iadv_addr_line_1;
751 p_line_2 := c_ins_rec.iadv_addr_line_2;
752 p_line_3 := c_ins_rec.iadv_addr_line_3;
753 p_line_4 := c_ins_rec.iadv_addr_line_4;
754 p_line_5 := c_ins_rec.iadv_city;
755 END LOOP;
756 ELSE
757 FOR c_inst_rec IN c_inst(
758 p_institution_cd,
759 p_addr_type)LOOP
760 v_line_1 := c_inst_rec.iadv_addr_line_1;
761 p_line_2 := c_inst_rec.iadv_addr_line_2;
762 p_line_3 := c_inst_rec.iadv_addr_line_3;
763 p_line_4 := c_inst_rec.iadv_addr_line_4;
764 p_line_5 := c_inst_rec.iadv_city;
765 END LOOP;
766 END IF;
767 END LOOP;
768 -- test if name has been selected
769 IF v_name IS NULL THEN
770 RAISE e_name_error;
771 ELSE
772 p_name := v_name;
773 END IF;
774 -- test if name has been selected
775 IF v_line_1 IS NULL THEN
776 RAISE e_addr_error;
777 ELSE
778 p_line_1 := v_line_1;
779 END IF;
780 RETURN TRUE;
781 EXCEPTION
782 WHEN e_name_error THEN
783 p_name := 'institution Code not found';
784 RETURN FALSE;
785 WHEN e_addr_error THEN
786 p_line_1 := 'No Address Record Found';
787 RETURN TRUE;
788 WHEN OTHERS THEN
789 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
790 IGS_GE_MSG_STACK.ADD;
791 App_Exception.Raise_Exception ;
792 END genp_get_inst_addr;
793 BEGIN
794 IF (p_person_id IS NOT NULL AND
795 p_org_unit_cd IS NULL AND
796 p_institution_cd IS NULL AND
797 p_location_cd IS NULL) THEN
798 IF NOT genp_get_per_addr(
799 p_person_id,
800 p_addr_type,
801 p_phone_no,
802 p_name_style,
803 v_name,
804 v_line_1,
805 v_line_2,
806 v_line_3,
807 v_line_4,
808 v_line_5,
809 v_phone,
810 -- # 2382471 Added for Country ,State and Zip Code
811 v_state,
812 v_postal_code,
813 v_country_desc) THEN
814 RAISE e_addr;
815 END IF;
816 ELSIF (p_person_id IS NULL AND
817 p_org_unit_cd IS NOT NULL AND
818 p_institution_cd IS NULL AND
819 p_location_cd IS NULL) THEN
820 IF NOT genp_get_org_addr(
821 p_org_unit_cd,
822 p_addr_type,
823 p_phone_no,
824 v_name,
825 v_line_1,
826 v_line_2,
827 v_line_3,
828 v_line_4,
829 v_line_5,
830 v_phone) THEN
831 RAISE e_addr;
832 END IF;
833 ELSIF ( p_person_id IS NULL AND
834 p_org_unit_cd IS NULL AND
835 p_institution_cd IS NOT NULL AND
836 p_location_cd IS NULL) THEN
837 IF NOT genp_get_inst_addr(
838 p_institution_cd,
839 p_addr_type,
840 p_phone_no,
841 v_name,
842 v_line_1,
843 v_line_2,
844 v_line_3,
845 v_line_4,
846 v_line_5,
847 v_phone) THEN
848 RAISE e_addr;
849 END IF;
850 ELSIF ( p_person_id IS NULL AND
851 p_org_unit_cd IS NULL AND
852 p_institution_cd IS NULL AND
853 p_location_cd IS NOT NULL) THEN
854 IF NOT genp_get_loc_addr(
855 p_location_cd,
856 p_addr_type,
857 p_phone_no,
858 v_name,
859 v_line_1,
860 v_line_2,
861 v_line_3,
862 v_line_4,
863 v_line_5,
864 v_phone) THEN
865 RAISE e_addr;
866 END IF;
867 ELSE
868 RAISE e_addr;
869 END IF;
870 -- Assemble the address based on the variables passed
871 v_addr := v_name;
872 -- use p_phone_no to append phone number
873 IF p_phone_no = 'Y' THEN
874 IF v_phone IS NOT NULL THEN
875 v_addr := v_addr || ' ('||v_phone||')';
876 END IF;
877 END IF;
878 -- Use p_inc_addr to append address lnies that are not null
879 IF p_inc_addr = 'Y' THEN
880 IF v_line_1 IS NOT NULL THEN
881 v_addr := v_addr || fnd_global.local_chr(10) || v_line_1;
882 END IF;
883 IF v_line_2 IS NOT NULL THEN
884 v_addr := v_addr || fnd_global.local_chr(10) || v_line_2;
885 END IF;
886 IF v_line_3 IS NOT NULL THEN
887 v_addr := v_addr || fnd_global.local_chr(10) || v_line_3;
888 END IF;
889 IF v_line_4 IS NOT NULL THEN
890 v_addr := v_addr || fnd_global.local_chr(10) || v_line_4;
891 END IF;
892 IF v_line_5 IS NOT NULL THEN
893 v_addr := v_addr || fnd_global.local_chr(10) || v_line_5;
894 END IF;
895 -- # 2382471 Added for Country ,State and Zip Code
896 IF v_state IS NOT NULL THEN
897 v_addr := v_addr || fnd_global.local_chr(10) || v_state;
898 END IF;
899 IF v_country_desc IS NOT NULL THEN
900 v_addr := v_addr || fnd_global.local_chr(10) || v_country_desc;
901 END IF;
902 IF v_postal_code IS NOT NULL THEN
903 v_addr := v_addr || fnd_global.local_chr(10) || v_postal_code;
904 END IF;
905
906 END IF;
907 -- Test if v_addr is null, if so then raise exception
908 IF v_addr IS NULL THEN
909 RAISE e_addr;
910 END IF;
911 -- format string based on p_case_type
912 IF UPPER(p_case_type) = 'UPPER' THEN
913 v_addr := UPPER(v_addr);
914 ELSIF UPPER(p_case_type) = 'LOWER' THEN
915 v_addr := LOWER(v_addr);
916 ELSIF UPPER(p_case_type) = 'NORMAL' THEN
917 v_addr := INITCAP(v_addr);
918 ELSIF UPPER(p_case_type) = 'DEFAULT' THEN
919 NULL;
920 ELSE
921 RAISE e_addr;
922 END IF; -- IF UPPER(p_case_type)
923 RETURN v_addr;
924 EXCEPTION
925 WHEN e_addr THEN
926 IF v_addr IS NULL THEN
927 RETURN 'No Address record found';
928 END IF;
929 WHEN OTHERS THEN
930 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
931 IGS_GE_MSG_STACK.ADD;
932 App_Exception.Raise_Exception ;
933 RETURN NULL;
934 END genp_get_mail_addr;
935
936 FUNCTION genp_get_nxt_prsn_id(
937 p_person_id OUT NOCOPY NUMBER ,
938 p_message_name OUT NOCOPY VARCHAR2 )
939 RETURN BOOLEAN AS
940 gv_other_detail VARCHAR2(255);
941 BEGIN
942 -- Return next available IGS_PE_PERSON id value
943 DECLARE
944 v_seq_num_str VARCHAR(64);
945 v_chk_digit NUMBER(2);
946 v_new_id NUMBER(8);
947 v_check CHAR;
948 cst_max_attemp CONSTANT NUMBER := 5;
949 v_is_right_length BOOLEAN DEFAULT TRUE;
950 v_id_generated BOOLEAN DEFAULT FALSE;
951 CURSOR c_get_nxt_seq_num IS
952 SELECT IGS_PE_PERSON_PE_ID_S.nextval
953 FROM DUAL;
954 --skpandey, Bug#4937960: Changed c_chk_id_exists cursor definition to optimize query
955 CURSOR c_chk_id_exists (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
956 SELECT 'x'
957 FROM IGS_PE_PERSON_BASE_V
958 WHERE person_id = cp_person_id;
959 FUNCTION genpl_calc_chk_digit (p_seq_num NUMBER)
960 RETURN NUMBER
961 AS
962 v_chk_digit NUMBER(2);
963 v_seq_num_str VARCHAR2(7);
964 BEGIN
965 v_seq_num_str := TO_CHAR(p_seq_num);
966 v_chk_digit := 11 -
967 (((SUBSTR(v_seq_num_str, 1, 1) * 64) +
968 (SUBSTR(v_seq_num_str, 2, 1) * 32) +
969 (SUBSTR(v_seq_num_str, 3, 1) * 16) +
970 (SUBSTR(v_seq_num_str, 4, 1) * 8) +
971 (SUBSTR(v_seq_num_str, 5, 1) * 4) +
972 (SUBSTR(v_seq_num_str, 6, 1) * 2) +
973 (SUBSTR(v_seq_num_str, 7, 1) * 1)) MOD 11);
974 RETURN v_chk_digit;
975 END genpl_calc_chk_digit;
976 BEGIN
977 -- calculate the ID in 5 attempts
978 FOR v_try_cnt IN 1 .. cst_max_attemp LOOP
979 OPEN c_get_nxt_seq_num;
980 FETCH c_get_nxt_seq_num INTO v_seq_num_str;
981 IF (c_get_nxt_seq_num%NOTFOUND) THEN
982 CLOSE c_get_nxt_seq_num;
983 RAISE NO_DATA_FOUND;
984 END IF;
985 CLOSE c_get_nxt_seq_num;
986
987 -- calculate the check digit
988 v_chk_digit := genpl_calc_chk_digit(TO_NUMBER(v_seq_num_str));
989
990 v_new_id := TO_NUMBER(v_seq_num_str);
991 EXIT;
992
993 END LOOP;
994
995 -- check that id does not already exist in the IGS_PE_PERSON table
996 OPEN c_chk_id_exists(v_new_id);
997 FETCH c_chk_id_exists INTO v_check;
998 IF (c_chk_id_exists%FOUND) THEN
999 CLOSE c_chk_id_exists;
1000 p_message_name := 'IGS_GE_DUPLICATE_VALUE';
1001 RETURN FALSE;
1002 END IF;
1003 CLOSE c_chk_id_exists;
1004 -- New person_id is generated successfully
1005 p_message_name := null;
1006 p_person_id := v_new_id;
1007 RETURN TRUE;
1008 END;
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1012 IGS_GE_MSG_STACK.ADD;
1013 App_Exception.Raise_Exception ;
1014 END genp_get_nxt_prsn_id;
1015
1016
1017
1018 FUNCTION genp_get_pdv_name(
1019 p_person_id IN NUMBER ,
1020 p_field_name IN VARCHAR2 )
1021 RETURN VARCHAR2 AS
1022 /*
1023 WHO WHEN WHAT
1024 pkpatel 27-MAR-2003 Bug 2261717
1025 Removed the initial_last_name from igs_pe_person_v and write specific cursor to find the value.
1026 skpandey 13-JAN-2006 Bug#4937960: Changed c_person cursor definition to optimize query
1027 */
1028 -- This module returns the value of a field passed in as a parameter for a
1029 -- IGS_PE_PERSON.
1030 -- The use of this module will generally be in the order by clause for tables
1031 -- that retrieve person_id's but require them in an order, eg. surname
1032 -- order.
1033 -- Most common use will be the order by clause of a block on a form module.
1034
1035 v_ret_val VARCHAR2(255) DEFAULT NULL;
1036 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE) IS
1037 SELECT DECODE(
1038 UPPER(p_field_name),
1039 'SURNAME', p.PERSON_LAST_NAME ,
1040 'GIVEN_NAMES', P.PERSON_FIRST_NAME ,
1041 'PREFERRED_GIVEN_NAME', P.KNOWN_AS ,
1042 'CONTEXT_BLOCK_NAME', p.PERSON_LAST_NAME || ', ' || p.PERSON_TITLE || ' ' || NVL(p.KNOWN_AS,p.PERSON_FIRST_NAME),
1043 NULL)
1044 FROM hz_parties p
1045 WHERE p.party_id = cp_person_id;
1046
1047 CURSOR initial_last_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
1048 SELECT RTRIM(DECODE(person_last_name,null,'',DECODE(person_first_name,null,person_last_name,person_last_name
1049 || ', ' ) ) || NVL(person_first_name,'')|| ' '||person_middle_name,' ')
1050 FROM hz_parties
1051 WHERE party_id = cp_person_id;
1052
1053
1054 BEGIN
1055 IF p_field_name = 'INITIAL_LAST_NAME' THEN
1056
1057 OPEN initial_last_name_cur(p_person_id);
1058 FETCH initial_last_name_cur INTO v_ret_val;
1059 CLOSE initial_last_name_cur;
1060
1061 ELSE
1062
1063 OPEN c_person(p_person_id);
1064 FETCH c_person INTO v_ret_val;
1065 CLOSE c_person;
1066
1067 END IF;
1068
1069 RETURN v_ret_val;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1074 IGS_GE_MSG_STACK.ADD;
1075 App_Exception.Raise_Exception ;
1076 END genp_get_pdv_name;
1077
1078
1079 FUNCTION genp_get_person_name(
1080 p_person_id IN NUMBER ,
1081 p_surname OUT NOCOPY VARCHAR2 ,
1082 p_given_names OUT NOCOPY VARCHAR2 ,
1083 p_title OUT NOCOPY VARCHAR2 ,
1084 p_oracle_username OUT NOCOPY VARCHAR2 ,
1085 p_preferred_given_name OUT NOCOPY VARCHAR2 ,
1086 p_full_name OUT NOCOPY VARCHAR2 ,
1087 p_preferred_name OUT NOCOPY VARCHAR2 ,
1088 p_title_name OUT NOCOPY VARCHAR2 ,
1089 p_initial_name OUT NOCOPY VARCHAR2 ,
1090 p_message_name OUT NOCOPY VARCHAR2 )
1091
1092 /*---------------------------------------------------------------
1093 --Change History:
1094 --Who When What
1095 --ssawhney 4257183 igs_pe_person usage changed to igs_pe_person_base_v.
1096 -- return NULL/full_name for useless and obsolete fields like title_name,orc_user,initial_name etc.
1097 -------------------------------------------------------------------*/
1098 RETURN BOOLEAN AS
1099 BEGIN
1100 DECLARE
1101 v_person_details IGS_PE_PERSON_BASE_V%ROWTYPE;
1102 v_other_detail VARCHAR(255);
1103 CURSOR c_person_details IS
1104 SELECT *
1105 FROM IGS_PE_PERSON_BASE_V -- IGS_PE_API_ALT_PERS_API_ID_V
1106 WHERE person_id = p_person_id;
1107 BEGIN
1108 OPEN c_person_details;
1109 FETCH c_person_details INTO v_person_details;
1110 IF (c_person_details%NOTFOUND) THEN
1111 CLOSE c_person_details;
1112 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1113 RETURN FALSE;
1114 ELSE
1115 CLOSE c_person_details;
1116 p_surname := v_person_details.last_name ; --v_person_details.pe_surname;
1117 p_given_names := v_person_details.first_name ; --v_person_details.pe_given_names;
1118 p_title := v_person_details.title;
1119 p_oracle_username := null ; -- v_person_details.oracle_username; this should not be used
1120 p_preferred_given_name := v_person_details.known_as ; --preferred_given_name;
1121 p_full_name := v_person_details.full_name;
1122 p_preferred_name := v_person_details.full_name; --pe_preferred_name; --used only in tracking, hence changin
1123 p_title_name := v_person_details.full_name; --pe_title_name;
1124 p_initial_name :=v_person_details.full_name; --pe_initial_name;
1125 p_message_name := null;
1126 RETURN TRUE;
1127 END IF;
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1131 IGS_GE_MSG_STACK.ADD;
1132 App_Exception.Raise_Exception ;
1133 END;
1134 END genp_get_person_name;
1135
1136
1137 FUNCTION genp_get_prsn_email(
1138 p_person_id IN NUMBER ,
1139 p_email_addr OUT NOCOPY VARCHAR2 ,
1140 p_message_name OUT NOCOPY VARCHAR2 )
1141 RETURN BOOLEAN AS
1142 BEGIN
1143 DECLARE
1144 CURSOR c_email_addr IS
1145 SELECT pdv. EMAIL_ADDRESS email_addr
1146 FROM HZ_PARTIES pdv -- IGS_PE_PERSON_V pdv
1147 WHERE pdv.party_id = p_person_id;
1148 --ssawhney use hz_parties instead of person_v, as single record needs to be returned
1149 --which can anyway be used from hz_parties.
1150 v_other_detail VARCHAR2(255);
1151 v_email_addr IGS_PE_PERSON_V.email_addr%TYPE;
1152 BEGIN
1153 -- This module returns the selected IGS_PE_PERSON's
1154 -- email address
1155 -- select the IGS_PE_PERSON's details
1156 OPEN c_email_addr;
1157 FETCH c_email_addr INTO v_email_addr;
1158 -- if a record was found, return the
1159 -- email address selected
1160 IF (c_email_addr%FOUND) THEN
1161 CLOSE c_email_addr;
1162 -- set the IGS_PE_PERSON's email address to the
1163 -- value selected
1164 p_email_addr := v_email_addr;
1165 p_message_name := null ;
1166 RETURN TRUE;
1167 ELSE
1168 -- set the message number as this
1169 -- IGS_PE_PERSON doesn't exist
1170 CLOSE c_email_addr;
1171 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1172 RETURN FALSE;
1173 END IF;
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1177 IGS_GE_MSG_STACK.ADD;
1178 App_Exception.Raise_Exception ;
1179 END;
1180 END genp_get_prsn_email;
1181
1182 FUNCTION genp_get_prsn_names(
1183 p_person_id IN NUMBER ,
1184 p_surname OUT NOCOPY VARCHAR2 ,
1185 p_given_names OUT NOCOPY VARCHAR2 ,
1186 p_title OUT NOCOPY VARCHAR2 ,
1187 p_oracle_username OUT NOCOPY VARCHAR2 ,
1188 p_preferred_given_name OUT NOCOPY VARCHAR2 ,
1189 p_full_name OUT NOCOPY VARCHAR2 ,
1190 p_preferred_name OUT NOCOPY VARCHAR2 ,
1191 p_title_name OUT NOCOPY VARCHAR2 ,
1192 p_initial_name OUT NOCOPY VARCHAR2 ,
1193 p_context_block_name OUT NOCOPY VARCHAR2 ,
1194 p_message_name OUT NOCOPY VARCHAR2 )
1195 RETURN BOOLEAN AS
1196 /*
1197 WHO WHEN WHAT
1198 pkpatel 27-MAR-2003 Bug 2261717
1199 Filtered the query to be based only on IGS_PE_PERSON_V.
1200 Removed the initial_name and passed full_name for the OUT parameter.
1201 ssawhney 4257183 igs_pe_person usage changed to igs_pe_person_base_v.
1202 return NULL or exact view def for complex field derivations...avoid usage of person_v
1203 -------------------------------------------------------------------*/
1204
1205 TYPE r_person_details IS RECORD (
1206 surname IGS_PE_PERSON.surname%TYPE,
1207 given_names IGS_PE_PERSON.given_names%TYPE,
1208 title IGS_PE_PERSON.title%TYPE,
1209 oracle_username IGS_PE_PERSON.oracle_username%TYPE,
1210 preferred_given_name IGS_PE_PERSON.preferred_given_name%TYPE,
1211 full_name IGS_PE_PERSON_V.full_name%TYPE,
1212 preferred_name IGS_PE_PERSON_V.preferred_name%TYPE,
1213 title_name IGS_PE_PERSON_V.title_name%TYPE,
1214 context_block_name IGS_PE_PERSON_V.context_block_name%TYPE
1215 );
1216 v_person_details r_person_details;
1217 v_other_detail VARCHAR(255);
1218 CURSOR c_person_details IS
1219 SELECT p.last_name surname,
1220 p.first_name given_names,
1221 p.title,
1222 null oracle_username,
1223 p.known_as preferred_given_name,
1224 p.full_name,
1225 NVL(P.KNOWN_AS, SUBSTR (P.FIRST_NAME, 1, DECODE(INSTR(P.FIRST_NAME, ' '), 0, LENGTH(P.FIRST_NAME), (INSTR(P.FIRST_NAME, ' ')-1)))) || ' ' || P.LAST_NAME PREFERRED_NAME,
1226 null title_name,
1227 p.LAST_NAME || ', ' || p.TITLE || ' ' || NVL(p.KNOWN_AS,p.FIRST_NAME) CONTEXT_BLOCK_NAME
1228 FROM igs_pe_person_base_v p
1229 WHERE person_id = p_person_id;
1230 BEGIN
1231 OPEN c_person_details;
1232 FETCH c_person_details INTO v_person_details;
1233 IF (c_person_details%NOTFOUND) THEN
1234 CLOSE c_person_details;
1235 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1236 RETURN FALSE;
1237 ELSE
1238 CLOSE c_person_details;
1239 p_surname := v_person_details.surname;
1240 p_given_names := v_person_details.given_names;
1241 p_title := v_person_details.title;
1242 p_oracle_username := v_person_details.oracle_username;
1243 p_preferred_given_name := v_person_details.preferred_given_name;
1244 p_full_name := v_person_details.full_name;
1245 p_preferred_name := v_person_details.preferred_name;
1246 p_title_name := v_person_details.title_name;
1247 p_initial_name := v_person_details.full_name;
1248 p_context_block_name := v_person_details.context_block_name;
1249 p_message_name := null ;
1250 RETURN TRUE;
1251 END IF;
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1255 IGS_GE_MSG_STACK.ADD;
1256 App_Exception.Raise_Exception ;
1257
1258 END genp_get_prsn_names;
1259
1260
1261 END IGS_GE_GEN_002 ;
[Home] [Help]