1 PACKAGE BODY IGS_GE_GEN_001 AS
2 /* $Header: IGSGE01B.pls 120.3 2006/01/25 09:11:03 skpandey noship $ */
3 /* change history
4 WHO WHEN WHAT
5 pkpatel 17-APR-2003 Bug 2261717
6 Modified the FUNCTION genp_get_per_addr
7 vrathi 03-JUN-2003 Bug 2940810
8 Modified procedure genp_get_audit. Added SQL bind variables.
9 asbala 29-DEC-2003 Bug 3330996. 10GCERT
10 gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID in GENP_DEL_LOG to disable OSS for R12.
11 */
12
13 FUNCTION GENP_CHK_COL_UPPER(
14 p_column_name VARCHAR2 ,
15 p_table_name VARCHAR2 )
16 RETURN BOOLEAN AS
17 gv_other_detail VARCHAR2(255);
18 BEGIN
19 DECLARE
20 cst_table CONSTANT user_objects.object_type%TYPE := 'TABLE';
21 cst_view CONSTANT user_objects.object_type%TYPE := 'VIEW';
22 v_object_type user_objects.object_type%TYPE;
23 v_col_comment user_col_comments.comments%TYPE;
24 v_table_name user_col_comments.table_name%TYPE;
25 v_column_name user_col_comments.column_name%TYPE;
26 v_search_condition VARCHAR2(10000);
27 v_full_stop_pos INTEGER;
28 CURSOR c_uo (
29 cp_object_name user_objects.object_name%TYPE) IS
30 SELECT uo.object_type
31 FROM user_objects uo
32 WHERE uo.object_name = cp_object_name AND
33 uo.object_type IN (cst_table, cst_view);
34 CURSOR c_ucc (
35 cp_view_name user_col_comments.table_name%TYPE,
36 cp_column_name user_col_comments.column_name%TYPE) IS
37 SELECT ucc.comments
38 FROM user_col_comments ucc
39 WHERE ucc.table_name = cp_view_name AND
40 ucc.column_name = cp_column_name;
41 CURSOR c_uc (
42 cp_table_name user_constraints.table_name%TYPE) IS
43 SELECT uc.search_condition
44 FROM user_constraints uc
45 WHERE uc.constraint_type = 'C' AND
46 uc.table_name = cp_table_name AND
47 uc.status = 'ENABLED' AND
48 uc.constraint_name like '%UCASE_CK%';
49 BEGIN
50 -- This routine checks if a table column is forced
51 -- to be upper case via a table constraint.
52 -- Check if table is a view. If the table is a view
53 -- need to determine the real table and column
54 OPEN c_uo (p_table_name);
55 FETCH c_uo INTO v_object_type;
56 IF (c_uo%NOTFOUND) THEN
57 CLOSE c_uo;
58 RETURN FALSE;
59 END IF;
60 CLOSE c_uo;
61 IF v_object_type = cst_table THEN
62 v_table_name := p_table_name;
63 v_column_name := p_column_name;
64 ELSE
65 -- Columns comments are being used to store the
66 -- real table.column for the view column.
67 OPEN c_ucc (
68 p_table_name,
69 p_column_name);
70 FETCH c_ucc INTO v_col_comment;
71 IF (c_ucc%NOTFOUND) THEN
72 CLOSE c_ucc;
73 RETURN FALSE;
74 END IF;
75 CLOSE c_ucc;
76 v_table_name := p_table_name;
77 v_column_name := p_column_name;
78 v_full_stop_pos := INSTR(v_col_comment, '.', 1, 1);
79 IF v_full_stop_pos = 0 THEN
80 RETURN FALSE;
81 END IF;
82 v_table_name := SUBSTR(v_col_comment, 1, v_full_stop_pos - 1);
83 v_column_name := SUBSTR(v_col_comment, v_full_stop_pos + 1);
84 END IF;
85 OPEN c_uc (v_table_name);
86 LOOP
87 FETCH c_uc INTO v_search_condition;
88 IF c_uc%NOTFOUND THEN
89 CLOSE c_uc;
90 EXIT;
91 END IF;
92 -- Check if the column is constained to uppercase.
93 -- Search for: column_name=UPPER(column_name)
94 -- Eg. SURNAME=UPPER(SURNAME)
95 -- Spaces and tabs are removed from the search string.
96 IF INSTR(UPPER(REPLACE(REPLACE(v_search_condition, fnd_global.local_chr(32)), fnd_global.local_chr(09))),
97 v_column_name || '=UPPER(' || v_column_name || ')') > 0 THEN
98 CLOSE c_uc;
99 RETURN TRUE;
100 END IF;
101 END LOOP;
102 -- All rows have been processed and no matches were found.
103 -- Column is not constrained to upper case.
104 RETURN FALSE;
105 EXCEPTION
106 WHEN OTHERS THEN
107 IF c_uo%ISOPEN THEN
108 CLOSE c_uo;
109 END IF;
110 IF c_ucc%ISOPEN THEN
111 CLOSE c_ucc;
112 END IF;
113 IF c_uc%ISOPEN THEN
114 CLOSE c_uc;
115 END IF;
116 RAISE;
117 END;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
121 END genp_chk_col_upper;
122
123
124 FUNCTION genp_clc_dt_diff(
125 p_start_dt IN DATE ,
126 p_end_dt IN DATE ,
127 p_message_name OUT NOCOPY VARCHAR2 )
128 RETURN NUMBER AS
129 v_other_detail VARCHAR2(255);
130 v_days NUMBER;
131 BEGIN
132 p_message_name := null;
133 IF (p_end_dt < p_start_dt) THEN
134 p_message_name := 'IGS_CA_ENDDT_LT_STARTDT';
135 RETURN 0;
136 END IF;
137 v_days := p_end_dt - p_start_dt;
138 RETURN v_days;
139 EXCEPTION
140 WHEN OTHERS THEN
141 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
142 -- App_Exception.Raise_Exception;
143 END genp_clc_dt_diff;
144
145
146 FUNCTION GENP_CLC_WEEK_END_DT(
147 p_date IN DATE ,
148 p_day_week_end IN VARCHAR2 DEFAULT 'FRIDAY')
149 RETURN DATE AS
150 gv_other_detail VARCHAR2(255);
151 BEGIN -- genp_clc_week_end_dt
152 -- This module will accept a date and return the week ending date.
153 -- The default for the end day of the week is FRIDAY.
154 DECLARE
155 BEGIN
156 -- If the date passed in is already the last day of the week, return it.
157 IF RTRIM(TO_CHAR(p_date, 'DAY')) = p_day_week_end THEN
158 RETURN p_date;
159 ELSE
160 -- otherwise calculate the date of the last day of the week.
161 RETURN NEXT_DAY(p_date, p_day_week_end);
162 END IF;
163 END;
164 EXCEPTION
165 WHEN OTHERS THEN
166 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
167 END genp_clc_week_end_dt;
168
169
170 PROCEDURE GENP_DEL_LOG(
171 errbuf out NOCOPY varchar2,
172 retcode out NOCOPY number ,
173 p_s_log_type IN VARCHAR2 ,
174 p_days_old IN NUMBER )
175 AS
176 gv_other_detail VARCHAR2(255);
177 e_resource_busy_exception EXCEPTION;
178 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
179 BEGIN -- genp_del_log
180 igs_ge_gen_003.set_org_id;
181 --Find and delete log records requiring cleanup.
182 -- Remove s_log and s_log_entry records older than a specified number of days.
183 retcode:=0;
184 DECLARE
185 CURSOR c_sl (
186 cp_s_log_type IGS_GE_S_LOG.s_log_type%TYPE,
187 cp_days_old_dt IGS_GE_S_LOG.creation_dt%TYPE) IS
188 SELECT creation_dt , rowid
189 FROM IGS_GE_S_LOG
190 WHERE s_log_type = cp_s_log_type AND
191 creation_dt < cp_days_old_dt
192 FOR UPDATE of s_log_type NOWAIT;
193 CURSOR c_sle (
194 cp_s_log_type IGS_GE_S_LOG_ENTRY.s_log_type%TYPE,
195 cp_creation_dt IGS_GE_S_LOG.creation_dt%TYPE) IS
196 SELECT creation_dt , rowid
197 FROM IGS_GE_S_LOG_ENTRY
198 WHERE s_log_type = cp_s_log_type AND
199 creation_dt = cp_creation_dt
200 FOR UPDATE of s_log_type NOWAIT;
201 v_record_found BOOLEAN := FALSE;
202 BEGIN
203 BEGIN -- inner block
204 SAVEPOINT sp_before_delete;
205 FOR v_sl_rec IN c_sl(
206 p_s_log_type,
207 SYSDATE - p_days_old)
208 LOOP
209 v_record_found := TRUE;
210 -- Clean up any log entries, they shouldn't exist
211 FOR v_sle_rec IN c_sle(
212 p_s_log_type,
213 v_sl_rec.creation_dt)
214 LOOP
215 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(x_rowid => v_sle_rec.rowid) ;
216 END LOOP;
217 -- Remove the current log record
218 IGS_GE_S_LOG_PKG.DELETE_ROW(x_rowid => v_sl_rec.rowid);
219 END LOOP;
220 EXCEPTION
221 WHEN e_resource_busy_exception THEN
222 RETCODE := 2 ;
223 ERRBUF := FND_MESSAGE.GET_STRING('IGS' , 'IGS_GE_RECORD_LOCKED');
224 END; -- inner block
225 IF v_record_found = FALSE THEN
226 ERRBUF := FND_MESSAGE.GET_STRING('IGS' , 'IGS_GE_NO_LOG_ENTRIES');
227 END IF;
228 RETURN;
229 END;
230 EXCEPTION
231 WHEN OTHERS THEN
232 /*RETCODE := 2 ;
233 ERRBUF := FND_MESSAGE.GET_STRING('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
234 commented for the exception handler related chnages*/
235 RETCODE := 2;
236 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
237 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
238 END genp_del_log;
239
240
241 FUNCTION GENP_DEL_NOTE(
242 p_reference_number IN NUMBER ,
243 p_message_name OUT NOCOPY VARCHAR2 )
244 RETURN BOOLEAN AS
245 gv_other_detail VARCHAR2(255);
246 BEGIN -- genp_del_note
247 -- Delete a note record.
248 DECLARE
249 e_resource_busy EXCEPTION;
250 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
251 CURSOR c_del_note IS
252 SELECT IGS_GE_NOTE.* , ROWID
253 FROM IGS_GE_NOTE
254 WHERE reference_number = p_reference_number
255 FOR UPDATE OF reference_number NOWAIT;
256 BEGIN
257 SAVEPOINT sp_before_delete;
258 p_message_name:= null ;
259 BEGIN
260 FOR v_note_rec IN c_del_note LOOP
261 IGS_GE_NOTE_PKG.DELETE_ROW(X_ROWID => V_NOTE_REC.ROWID );
262 END LOOP;
263 EXCEPTION
264 WHEN e_resource_busy THEN
265 ROLLBACK TO sp_before_delete;
266 p_message_name := 'IGS_GE_NOTE_RECORD_LOCKED';
267 RETURN FALSE;
268 END;
269 RETURN TRUE;
270 END;
271 EXCEPTION
272 WHEN OTHERS THEN
273 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
274 App_Exception.Raise_Exception ;
275 END genp_del_note;
276
277
278 FUNCTION genp_get_addr(
279 p_person_id NUMBER ,
280 p_org_unit_cd VARCHAR2 ,
281 p_institution_cd VARCHAR2 ,
282 p_location_cd VARCHAR2 ,
283 p_addr_type VARCHAR2 ,
284 p_case_type VARCHAR2 DEFAULT 'UPPER',
285 p_phone_no VARCHAR2 DEFAULT 'Y',
286 p_name_style VARCHAR2 DEFAULT 'CONTEXT',
287 p_inc_addr VARCHAR2 DEFAULT 'Y')
288 RETURN VARCHAR2 AS
289
290 v_line_1 VARCHAR2(256) := NULL; -- first line of address
291 v_line_2 VARCHAR2(256) := NULL; -- second line of address
292 v_line_3 VARCHAR2(256) := NULL; -- third line of address
293 v_line_4 VARCHAR2(256) := NULL; -- 4th line of address
294 v_line_5 VARCHAR2(256) := NULL; -- 5th line of address
295 v_addr VARCHAR2(2000) := NULL; -- final address variable
296 v_phone VARCHAR2(100) := NULL; -- placeholder for phone handling
297 v_name VARCHAR2(256) := NULL; -- person name placeholder
298 gv_other_detail VARCHAR2(1000) := NULL; -- global for error trapping
299 -- Local Exceptions
300 e_addr EXCEPTION; -- overall exception for trapping and handling errors
301 e_case_error EXCEPTION; -- case type error
302 --
303 -- Local Functions
304 -------------------------------------------------------------------------------
305 -- Module: genp_get_per_addr
306 -- Purpose: Function for returning formatted person names and addresses
307 -- based on variations of the parameters passed
308 -- Notes:
309 -- p_surname_first is a boolean to place the surname before the given name
310 -- TRUE formats in surname + , + title + given name
311 -- FALSE formats in title + given name + surname
312 --
313 -- p_phone_no is used to toggle the display of the phone number
314 -- Y populates the p_phone_line parameter with the phone number
315 -- N populates the p_phone_line parameter with a null value
316 -- Exception Handlers
317 -- e_name_error: returns false and populates the p_name variable with
318 -- 'Person name not found'
319 -- e_addr_error: returns false and populates p_line_1 with
320 -- 'No address record found'
321 --
322 -- Module History
323 -------------------------------------------------------------------------------
324 -- 03/03/1998 MSONTER Intial creation of Module
325 -- 05/03/1998 MSONTER Modified cursor c_pa to search
326 -- IGS_CO_ADDR_TYPE.correspondence_type = 'Y'
327 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
328 -- 19/03/1998 MSONTER Modified p_surname to p_name_style to use predefined
329 -- naming standards
330 -------------------------------------------------------------------------------
331 FUNCTION genp_get_per_addr(
332 p_per_id NUMBER,
333 p_adr_type VARCHAR2,
334 p_phone_num VARCHAR2,
335 p_name_style VARCHAR2,
336 p_name OUT NOCOPY VARCHAR2,
337 p_line_1 OUT NOCOPY VARCHAR2,
338 p_line_2 OUT NOCOPY VARCHAR2,
339 p_line_3 OUT NOCOPY VARCHAR2,
340 p_line_4 OUT NOCOPY VARCHAR2,
341 p_line_5 OUT NOCOPY VARCHAR2,
342 p_phone_line OUT NOCOPY VARCHAR2)
343 RETURN BOOLEAN
344 AS
345 /* change history
346 WHO WHEN WHAT
347 pkpatel 17-APR-2003 Bug 2261717
348 Removed selection of initial_last_name, initial_name from igs_pe_person_v.
349 Instead the direct logic to retrieve it from hz_parties was introduced.
350 skpandey 13-JAN-2006 Bug#4937960
351 Changed c_per_name cursor definition to optimize query
352 */
353 BEGIN
354 DECLARE
355 -- Local Cursors
356 -- cursor for selection of the person name in seperate parts to allow
357 -- construction based on the user preferences
358 CURSOR c_per_name (cp_person_id hz_parties.party_id%TYPE)IS
359 SELECT p.PERSON_TITLE per_title,
360 p.PERSON_LAST_NAME per_surname,
361 NVL(P.KNOWN_AS,p.PERSON_FIRST_NAME) per_first_name,
362 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 ,
363 P.PERSON_TITLE || ' ' || p.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME per_title_name ,
364 p.PERSON_LAST_NAME || ', ' || p.PERSON_TITLE || ' ' || NVL(p.KNOWN_AS,p.PERSON_FIRST_NAME) per_context_block_name
365 FROM hz_parties p
366 WHERE p.party_id = cp_person_id;
367
368
369 -- cursor for selection of the person address when
370 -- only the person_id is supplied
371 CURSOR c_pa(cp_person_id NUMBER)IS
372 SELECT padv.person_id padv_person_id,
373 padv.addr_type padv_addr_type,
374 padv.addr_line_1 padv_addr_line_1,
375 padv.addr_line_2 padv_addr_line_2,
376 padv.addr_line_3 padv_addr_line_3,
377 padv.addr_line_4 padv_addr_line_4,
378 padv.city padv_city
379 FROM IGS_PE_PERSON_ADDR_V padv
380 WHERE padv.person_id = cp_person_id AND
381 padv.correspondence_ind = 'Y';
382
383 -- cursor for selection of the person address when
384 -- only the person_id and addr_type is supplied
385 CURSOR c_pat(
386 cp_person_id NUMBER,
387 cp_addr_type VARCHAR2)IS
388 SELECT padv.person_id padv_person_id,
389 padv.addr_type padv_addr_type,
390 padv.addr_line_1 padv_addr_line_1,
391 padv.addr_line_2 padv_addr_line_2,
392 padv.addr_line_3 padv_addr_line_3,
393 padv.addr_line_4 padv_addr_line_4,
394 padv.city padv_city
395 FROM IGS_PE_PERSON_ADDR_V padv
396 WHERE padv.person_id = cp_person_id AND
397 padv.addr_type = cp_addr_type;
398
399 CURSOR initial_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
400 SELECT SUBSTR(igs_ge_gen_002.genp_get_initials(person_first_name), 1, 10) || ' ' || person_last_name
401 FROM hz_parties
402 WHERE party_id = cp_person_id;
403
404
405 CURSOR initial_last_name_cur(cp_person_id hz_parties.party_id%TYPE) IS
406 SELECT RTRIM(DECODE(person_last_name,null,'',DECODE(person_first_name,null,person_last_name,person_last_name
407 || ', ' ) ) || NVL(person_first_name,'')|| ' '||person_middle_name,' ')
408 FROM hz_parties
409 WHERE party_id = cp_person_id;
410
411
412 -- Local Variables
413 v_name VARCHAR2(256) := NULL;
414 v_line_1 VARCHAR2(256) := NULL;
415
416 e_name_error EXCEPTION; -- person name exception handler
417 e_addr_error EXCEPTION; -- person address exception handler
418 BEGIN
419 -- test for open cursor, then loop and select the persons name
420 IF (c_per_name%ISOPEN) THEN
421 CLOSE c_per_name;
422 END IF;
423
424 FOR c_per_rec IN c_per_name(p_per_id)LOOP
425 -- Determine if surname should be displayed first
426 IF p_name_style = 'PREFER' THEN
427
428 v_name := c_per_rec.per_title || ' ' || c_per_rec.per_preferred_name;
429
430 ELSIF p_name_style = 'TITLE' THEN
431 v_name := c_per_rec.per_title_name;
432 ELSIF p_name_style = 'INIT_F' THEN
433
434 OPEN initial_name_cur(p_per_id);
435 FETCH initial_name_cur INTO v_name;
436 CLOSE initial_name_cur;
437
438 ELSIF p_name_style = 'INIT_L' THEN
439
440 OPEN initial_last_name_cur(p_per_id);
441 FETCH initial_last_name_cur INTO v_name;
442 CLOSE initial_last_name_cur;
443
444 ELSIF p_name_style = 'CONTEXT' THEN
445 v_name := c_per_rec.per_context_block_name;
446 ELSIF p_name_style = 'SALUTAT' THEN
447
448 v_name := c_per_rec.per_title || ' ' || c_per_rec.per_surname;
449 ELSE
450 v_name := c_per_rec.per_context_block_name;
451 END IF; -- IF p_name_style
452
453 -- Determin if p_addr_type is passed and open correct cursor
454 IF p_adr_type IS NULL THEN
455 FOR c_pa_rec IN c_pa(p_per_id) LOOP
456 v_line_1 := c_pa_rec.padv_addr_line_1;
457 p_line_2 := c_pa_rec.padv_addr_line_2;
458 p_line_3 := c_pa_rec.padv_addr_line_3;
459 p_line_4 := c_pa_rec.padv_addr_line_4;
460 p_line_5 := c_pa_rec.padv_city;
461 END LOOP;
462 ELSE
463 FOR c_pat_rec IN c_pat(p_per_id, p_adr_type) LOOP
464 v_line_1 := c_pat_rec.padv_addr_line_1;
465 p_line_2 := c_pat_rec.padv_addr_line_2;
466 p_line_3 := c_pat_rec.padv_addr_line_3;
467 p_line_4 := c_pat_rec.padv_addr_line_4;
468 p_line_5 := c_pat_rec.padv_city;
469 END LOOP;
470 END IF;
471 END LOOP;
472 -- test if name has been selected
473 IF v_name IS NULL THEN
474 RAISE e_name_error;
475 ELSE
476 p_name := v_name;
477 END IF;
478 -- test if name has been selected
479 IF v_line_1 IS NULL THEN
480 RAISE e_addr_error;
481 ELSE
482 p_line_1 := v_line_1;
483 END IF;
484 RETURN TRUE;
485 EXCEPTION
486 WHEN e_name_error THEN
487 p_name := 'Person name not found';
488 RETURN FALSE;
489 WHEN e_addr_error THEN
490 p_line_1 := 'No Address Record Found';
491 RETURN TRUE;
492 WHEN OTHERS THEN
493 RAISE;
494 END;
495 EXCEPTION
496 WHEN OTHERS THEN
497 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
498 RETURN FALSE;
499 END genp_get_per_addr;
500
501 -------------------------------------------------------------------------------
502 -- Module: genp_get_org_addr
503 -- Purpose: Function for returning formatted org_unit names and addresses
504 -- based on variations of the parameters passed
505 -- Notes:
506 -- p_phone_no is used to toggle the display of the phone number
507 -- Y populates the p_phone_line parameter with the phone number
508 -- N populates the p_phone_line parameter with a null value
509 -- Exception Handlers
510 -- e_name_error: returns false and populates the p_name variable with
511 -- 'Org Unit not found'
512 -- e_addr_error: returns false and populates p_line_1 with
513 -- 'No address record found'
514 --
515 -- Module History
516 -------------------------------------------------------------------------------
517 -- 03/03/1998 MSONTER Intial creation of Module
518 -- 05/03/1998 MSONTER Modified cursor c_org_name to search
519 -- IGS_CO_ADDR_TYPE.correspondence_type = 'Y'
520 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
521 -------------------------------------------------------------------------------
522 FUNCTION genp_get_org_addr(
523 p_org_unit_cd VARCHAR2,
524 p_addr_type VARCHAR2,
525 p_phone_no VARCHAR2,
526 p_name OUT NOCOPY VARCHAR2,
527 p_line_1 OUT NOCOPY VARCHAR2,
528 p_line_2 OUT NOCOPY VARCHAR2,
529 p_line_3 OUT NOCOPY VARCHAR2,
530 p_line_4 OUT NOCOPY VARCHAR2,
531 p_line_5 OUT NOCOPY VARCHAR2,
532 p_phone_line OUT NOCOPY VARCHAR2)
533 RETURN BOOLEAN
534 AS
535 -- cursor for selection of the org_unit name
536 CURSOR c_org_name (
537 cp_org_unit_cd VARCHAR2)IS
538 SELECT ou.description ou_description
539 FROM IGS_OR_UNIT ou
540 WHERE ou.org_unit_cd = cp_org_unit_cd;
541 -- cursor for selection of the org_unit address when
542 -- only the org_unit_cd is supplied
543 CURSOR c_ou(
544 cp_org_unit_cd VARCHAR2)IS
545 SELECT oadv.org_unit_cd oadv_org_unit_cd,
546 oadv.addr_type oadv_addr_type,
547 oadv.addr_line_1 oadv_addr_line_1,
548 oadv.addr_line_2 oadv_addr_line_2,
549 oadv.addr_line_3 oadv_addr_line_3,
550 oadv.addr_line_4 oadv_addr_line_4,
551 oadv.city oadv_city
552 FROM IGS_OR_ADDR oadv
553 WHERE oadv.org_unit_cd = cp_org_unit_cd AND
554 oadv.correspondence_ind = 'Y';
555
556 -- cursor for selection of the org_unit address when
557 -- only the org_unit_cd and addr_type is supplied
558 CURSOR c_out(
559 cp_org_unit_cd VARCHAR2,
560 cp_addr_type VARCHAR2) IS
561 SELECT oadv.org_unit_cd oadv_org_unit_cd,
562 oadv.addr_type oadv_addr_type,
563 oadv.addr_line_1 oadv_addr_line_1,
564 oadv.addr_line_2 oadv_addr_line_2,
565 oadv.addr_line_3 oadv_addr_line_3,
566 oadv.addr_line_4 oadv_addr_line_4,
567 oadv.city oadv_city
568 FROM IGS_OR_ADDR oadv
569 WHERE oadv.org_unit_cd = cp_org_unit_cd AND
570 oadv.addr_type = cp_addr_type;
571
572 -- Local Variables
573 v_name VARCHAR2(256) := NULL;
574 v_line_1 VARCHAR2(256) := NULL;
575 -- Local Exceptions
576 e_name_error EXCEPTION; -- org_unit name exception handler
577 e_addr_error EXCEPTION; -- org_unit address exception handler
578 BEGIN
579 -- test for open cursor, then loop and select the persons name
580 IF c_org_name%ISOPEN THEN
581 CLOSE c_org_name;
582 END IF;
583 FOR c_org_rec IN c_org_name(
584 p_org_unit_cd) LOOP
585 v_name := c_org_rec.ou_description;
586 -- Determin if p_addr_type is passed and open correct cursor
587 IF p_addr_type IS NULL THEN
588 FOR c_ou_rec IN c_ou(
589 p_org_unit_cd) LOOP
590 v_line_1 := c_ou_rec.oadv_addr_line_1;
591 p_line_2 := c_ou_rec.oadv_addr_line_2;
592 p_line_3 := c_ou_rec.oadv_addr_line_3;
593 p_line_4 := c_ou_rec.oadv_addr_line_4;
594 p_line_5 := c_ou_rec.oadv_city;
595
596 END LOOP; -- FOR c_ou_rec IN c_ou(p_org_unit_cd)
597 ELSE
598 FOR c_out_rec IN c_out(
599 p_org_unit_cd,
600 p_addr_type) LOOP
601 v_line_1 := c_out_rec.oadv_addr_line_1;
602 p_line_2 := c_out_rec.oadv_addr_line_2;
603 p_line_3 := c_out_rec.oadv_addr_line_3;
604 p_line_4 := c_out_rec.oadv_addr_line_4;
605 p_line_5 := c_out_rec.oadv_city;
606 END LOOP; --
607 END IF;
608 END LOOP;
609 -- test if name has been selected
610 IF v_name IS NULL THEN
611 RAISE e_name_error;
612 ELSE
613 p_name := v_name;
614 END IF;
615 -- test if name has been selected
616 IF v_line_1 IS NULL THEN
617 RAISE e_addr_error;
618 ELSE
619 p_line_1 := v_line_1;
620 END IF;
621 RETURN TRUE;
622 EXCEPTION
623 WHEN e_name_error THEN
624 p_name := 'Org Unit not found';
625 RETURN FALSE;
626 WHEN e_addr_error THEN
627 p_line_1 := 'No Address Record Found';
628 RETURN TRUE;
629 WHEN OTHERS THEN
630 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
631 END genp_get_org_addr;
632 -------------------------------------------------------------------------------
633 -- Module: genp_get_loc_addr
634 -- Purpose: Function for returning formatted location names and addresses
635 -- based on variations of the parameters passed
636 -- Notes:
637 -- p_phone_no is used to toggle the display of the phone number
638 -- Y populates the p_phone_line parameter with the phone number
639 -- N populates the p_phone_line parameter with a null value
640 -- Exception Handlers
641 -- e_name_error: returns false and populates the p_name variable with
642 -- 'Location not found'
643 -- e_addr_error: returns false and populates p_line_1 with
644 -- 'No address record found'
645 -- Module History
646 -------------------------------------------------------------------------------
647 -- 04/03/1998 MSONTER Intial creation of Module
648 -- 05/03/1998 MSONTER Modified cursor c_loc_name to search
649 -- IGS_CO_ADDR_TYPE.correspondence_type = 'Y'
650 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
651 -------------------------------------------------------------------------------
652 FUNCTION genp_get_loc_addr(
653 p_location_cd VARCHAR2,
654 p_addr_type VARCHAR2,
655 p_phone_no VARCHAR2,
656 p_name OUT NOCOPY VARCHAR2,
657 p_line_1 OUT NOCOPY VARCHAR2,
658 p_line_2 OUT NOCOPY VARCHAR2,
659 p_line_3 OUT NOCOPY VARCHAR2,
660 p_line_4 OUT NOCOPY VARCHAR2,
661 p_line_5 OUT NOCOPY VARCHAR2,
662 p_phone_line OUT NOCOPY VARCHAR2)
663 RETURN BOOLEAN
664 AS
665 -- cursor for selection of the location name
666 CURSOR c_loc_name (
667 cp_location_cd VARCHAR2)IS
668 SELECT loc.description loc_description
669 FROM IGS_AD_LOCATION loc
670 WHERE loc.location_cd = cp_location_cd;
671 -- cursor for selection of the location address when
672 -- only the loc_unit_cd is supplied
673 --skpandey Bug#3687099, Changed definition of cursor c_loc to optimize query
674 CURSOR c_loc(
675 cp_location_cd VARCHAR2)IS
676 SELECT
677 LA.LOCATION_VENUE_CD ladv_location_cd,
678 HL.ADDRESS1 ladv_addr_line_1,
679 HL.ADDRESS2 ladv_addr_line_2,
680 HL.ADDRESS3 ladv_addr_line_3,
681 HL.ADDRESS4 ladv_addr_line_4,
682 HL.CITY ladv_city
683 FROM
684 HZ_LOCATIONS HL,
685 IGS_AD_LOCVENUE_ADDR LA
686 WHERE
687 HL.LOCATION_ID = LA.LOCATION_ID
688 AND LA.SOURCE_TYPE = 'L'
689 AND LA.LOCATION_VENUE_CD = cp_location_cd
690 AND LA.IDENTIFYING_ADDRESS_FLAG = 'Y' ;
691
692 -- cursor for selection of the location address when
693 -- only the location_cd and addr_type is supplied
694 --skpandey Bug#3687099, Changed definition of cursor c_loct to optimize query
695 CURSOR c_loct(
696 cp_location_cd VARCHAR2,
697 cp_addr_type VARCHAR2)IS
698 SELECT
699 LA.LOCATION_VENUE_CD ladv_location_cd,
700 HL.ADDRESS1 ladv_addr_line_1,
701 HL.ADDRESS2 ladv_addr_line_2,
702 HL.ADDRESS3 ladv_addr_line_3,
703 HL.ADDRESS4 ladv_addr_line_4,
704 HL.CITY ladv_city
705 FROM
706 HZ_LOCATIONS HL,
707 IGS_AD_LOCVENUE_ADDR LA ,
708 IGS_PE_LOCVENUE_USE PLU
709 WHERE
710 HL.LOCATION_ID = LA.LOCATION_ID
711 AND LA.LOCATION_VENUE_ADDR_ID = PLU.LOC_VENUE_ADDR_ID
712 AND LA.SOURCE_TYPE = 'L'
713 AND LA.LOCATION_VENUE_CD = cp_location_cd
714 AND PLU.SITE_USE_CODE = cp_addr_type;
715
716
717 -- Local Variables
718 v_name VARCHAR2(256) := NULL;
719 v_line_1 VARCHAR2(256) := NULL;
720 -- Local Exceptions
721 e_name_error EXCEPTION; -- locationt name exception handler
722 e_addr_error EXCEPTION; -- location address exception handler
723 BEGIN
724 -- test for open cursor, then loop and select the persons name
725 IF c_loc_name%ISOPEN THEN
726 CLOSE c_loc_name;
727 END IF;
728 FOR c_loc_rec IN c_loc_name(
729 p_location_cd) LOOP
730 v_name := c_loc_rec.loc_description;
731 -- Determin if p_addr_type is passed and open correct cursor
732 IF p_addr_type IS NULL THEN
733 FOR c_loc_rec IN c_loc(
734 p_location_cd)LOOP
735 v_line_1 := c_loc_rec.ladv_addr_line_1;
736 p_line_2 := c_loc_rec.ladv_addr_line_2;
737 p_line_3 := c_loc_rec.ladv_addr_line_3;
738 p_line_4 := c_loc_rec.ladv_addr_line_4;
739 p_line_5 := c_loc_rec.ladv_city;
740 END LOOP;
741 ELSE
742 FOR c_loct_rec IN c_loct(
743 p_location_cd,
744 p_addr_type)LOOP
745 v_line_1 := c_loct_rec.ladv_addr_line_1;
746 p_line_2 := c_loct_rec.ladv_addr_line_2;
747 p_line_3 := c_loct_rec.ladv_addr_line_3;
748 p_line_4 := c_loct_rec.ladv_addr_line_4;
749 p_line_5 := c_loct_rec.ladv_city;
750 END LOOP;
751 END IF;
752 END LOOP;
753 -- test if name has been selected
754 IF v_name IS NULL THEN
755 RAISE e_name_error;
756 ELSE
757 p_name := v_name;
758 END IF;
759 -- test if name has been selected
760 IF v_line_1 IS NULL THEN
761 RAISE e_addr_error;
762 ELSE
763 p_line_1 := v_line_1;
764 END IF;
765 RETURN TRUE;
766 EXCEPTION
767 WHEN e_name_error THEN
768 p_name := 'Location Code not found';
769 RETURN FALSE;
770 WHEN e_addr_error THEN
771 p_line_1 := 'No Address Record Found';
772 RETURN TRUE;
773 WHEN OTHERS THEN
774 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
775 END genp_get_loc_addr;
776 -------------------------------------------------------------------------------
777 -- Module: genp_get_inst_addr
778 -- Purpose: Function for returning formatted institution names and addresses
779 -- based on variations of the parameters passed
780 -- Notes:
781 -- p_phone_no is used to toggle the display of the phone number
782 -- Y populates the p_phone_line parameter with the phone number
783 -- N populates the p_phone_line parameter with a null value
784 -- Exception Handlers
785 -- e_name_error: returns false and populates the p_name variable with
786 -- 'Institution not found'
787 -- e_addr_error: returns false and populates p_line_1 with
788 -- 'No address record found'
789 --
790 -- Module History
791 ----------------------------------------------------------------------
792 -- 04/03/1998 MSONTER Intial creation of Module
793 -- 05/03/1998 MSONTER Modified cursor c_inst_name to search
794 -- IGS_CO_ADDR_TYPE.correspondence_type = 'Y'
795 -- 18/03/1998 YSWONG Modified code according to PLSQL-CODING standards
796 -- 19/03/1998 MSONTER Moved to local function of genp_get_addr
797 -------------------------------------------------------------------------------
798 FUNCTION genp_get_inst_addr(
799 p_institution_cd VARCHAR2,
800 p_addr_type VARCHAR2,
801 p_phone_no VARCHAR2,
802 p_name OUT NOCOPY VARCHAR2,
803 p_line_1 OUT NOCOPY VARCHAR2,
804 p_line_2 OUT NOCOPY VARCHAR2,
805 p_line_3 OUT NOCOPY VARCHAR2,
806 p_line_4 OUT NOCOPY VARCHAR2,
807 p_line_5 OUT NOCOPY VARCHAR2,
808 p_phone_line OUT NOCOPY VARCHAR2)
809 RETURN BOOLEAN
810 AS
811 -- cursor for selection of the institution name
812 CURSOR c_inst_name (
813 cp_institution_cd VARCHAR2)IS
814 SELECT inst.name inst_name
815 FROM IGS_OR_INSTITUTION inst
816 WHERE inst.institution_cd = cp_institution_cd;
817 -- cursor for selection of the IGS_OR_INSTITUTION address when
818 -- only the loc_unit_cd is supplied
819 CURSOR c_ins(
820 cp_institution_cd VARCHAR2)IS
821 SELECT iadv.institution_cd iadv_institution_cd,
822 iadv.addr_type iadv_addr_type,
823 iadv.addr_line_1 iadv_addr_line_1,
824 iadv.addr_line_2 iadv_addr_line_2,
825 iadv.addr_line_3 iadv_addr_line_3,
826 iadv.addr_line_4 iadv_addr_line_4,
827 iadv.city iadv_city
828 FROM IGS_OR_INST_ADDR iadv
829 WHERE iadv.institution_cd = cp_institution_cd AND
830 iadv.correspondence_ind = 'Y';
831 -- cursor for selection of the IGS_OR_INSTITUTION address when
832 -- only the institution_cd and IGS_CO_ADDR_TYPE is supplied
833 CURSOR c_inst(
834 cp_institution_cd VARCHAR2,
835 cp_addr_type VARCHAR2)IS
836 SELECT iadv.institution_cd iadv_institution_cd,
837 iadv.addr_type iadv_addr_type,
838 iadv.addr_line_1 iadv_addr_line_1,
839 iadv.addr_line_2 iadv_addr_line_2,
840 iadv.addr_line_3 iadv_addr_line_3,
841 iadv.addr_line_4 iadv_addr_line_4,
842 iadv.city iadv_city
843 FROM IGS_OR_INST_ADDR iadv
844 WHERE iadv.institution_cd = cp_institution_cd AND
845 iadv.addr_type = cp_addr_type;
846 -- Local Variables
847 v_name VARCHAR2(256) := NULL;
848 v_line_1 VARCHAR2(256) := NULL;
849 -- Local Exceptions
850 e_name_error EXCEPTION; -- institutiont name exception handler
851 e_addr_error EXCEPTION; -- IGS_OR_INSTITUTION address exception handler
852 BEGIN
853 -- test for open cursor, then loop and select the persons name
854 IF c_inst_name%ISOPEN THEN
855 CLOSE c_inst_name;
856 END IF;
857 FOR c_instit_rec IN c_inst_name(
858 p_institution_cd) LOOP
859 v_name := c_instit_rec.inst_name;
860 -- Determin if p_addr_type is passed and open correct cursor
861 IF p_addr_type IS NULL THEN
862 FOR c_ins_rec IN c_ins(
863 p_institution_cd) LOOP
864 v_line_1 := c_ins_rec.iadv_addr_line_1;
865 p_line_2 := c_ins_rec.iadv_addr_line_2;
866 p_line_3 := c_ins_rec.iadv_addr_line_3;
867 p_line_4 := c_ins_rec.iadv_addr_line_4;
868 p_line_5 := c_ins_rec.iadv_city;
869 END LOOP;
870 ELSE
871 FOR c_inst_rec IN c_inst(
872 p_institution_cd,
873 p_addr_type)LOOP
874 v_line_1 := c_inst_rec.iadv_addr_line_1;
875 p_line_2 := c_inst_rec.iadv_addr_line_2;
876 p_line_3 := c_inst_rec.iadv_addr_line_3;
877 p_line_4 := c_inst_rec.iadv_addr_line_4;
878 p_line_5 := c_inst_rec.iadv_city;
879 END LOOP;
880 END IF;
881 END LOOP;
882 -- test if name has been selected
883 IF v_name IS NULL THEN
884 RAISE e_name_error;
885 ELSE
886 p_name := v_name;
887 END IF;
888 -- test if name has been selected
889 IF v_line_1 IS NULL THEN
890 RAISE e_addr_error;
891 ELSE
892 p_line_1 := v_line_1;
893 END IF;
894 RETURN TRUE;
895 EXCEPTION
896 WHEN e_name_error THEN
897 p_name := 'Institution Code not found';
898 RETURN FALSE;
899 WHEN e_addr_error THEN
900 p_line_1 := 'No Address Record Found';
901 RETURN TRUE;
902 WHEN OTHERS THEN
903 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
904 END genp_get_inst_addr;
905 BEGIN
906 IF (p_person_id IS NOT NULL AND
907 p_org_unit_cd IS NULL AND
908 p_institution_cd IS NULL AND
909 p_location_cd IS NULL) THEN
910 IF NOT genp_get_per_addr(
911 p_person_id,
912 p_addr_type,
913 p_phone_no,
914 p_name_style,
915 v_name,
916 v_line_1,
917 v_line_2,
918 v_line_3,
919 v_line_4,
920 v_line_5,
921 v_phone) THEN
922 RAISE e_addr;
923 END IF;
924 ELSIF (p_person_id IS NULL AND
925 p_org_unit_cd IS NOT NULL AND
926 p_institution_cd IS NULL AND
927 p_location_cd IS NULL) THEN
928 IF NOT genp_get_org_addr(
929 p_org_unit_cd,
930 p_addr_type,
931 p_phone_no,
932 v_name,
933 v_line_1,
934 v_line_2,
935 v_line_3,
936 v_line_4,
937 v_line_5,
938 v_phone) THEN
939 RAISE e_addr;
940 END IF;
941 ELSIF ( p_person_id IS NULL AND
942 p_org_unit_cd IS NULL AND
943 p_institution_cd IS NOT NULL AND
944 p_location_cd IS NULL) THEN
945 IF NOT genp_get_inst_addr(
946 p_institution_cd,
947 p_addr_type,
948 p_phone_no,
949 v_name,
950 v_line_1,
951 v_line_2,
952 v_line_3,
953 v_line_4,
954 v_line_5,
955 v_phone) THEN
956 RAISE e_addr;
957 END IF;
958 ELSIF ( p_person_id IS NULL AND
959 p_org_unit_cd IS NULL AND
960 p_institution_cd IS NULL AND
961 p_location_cd IS NOT NULL) THEN
962 IF NOT genp_get_loc_addr(
963 p_location_cd,
964 p_addr_type,
965 p_phone_no,
966 v_name,
967 v_line_1,
968 v_line_2,
969 v_line_3,
970 v_line_4,
971 v_line_5,
972 v_phone) THEN
973 RAISE e_addr;
974 END IF;
975 ELSE
976 RAISE e_addr;
977 END IF;
978 -- Assemble the address based on the variables passed
979 v_addr := v_name;
980 -- use p_phone_no to append phone number
981 IF p_phone_no = 'Y' THEN
982 IF v_phone IS NOT NULL THEN
983 v_addr := v_addr || ' ('||v_phone||')';
984 END IF;
985 END IF;
986 -- Use p_inc_addr to append address lnies that are not null
987 IF p_inc_addr = 'Y' THEN
988 IF v_line_1 IS NOT NULL THEN
989 v_addr := v_addr || fnd_global.local_chr(10) || v_line_1;
990 END IF;
991 IF v_line_2 IS NOT NULL THEN
992 v_addr := v_addr || fnd_global.local_chr(10) || v_line_2;
993 END IF;
994 IF v_line_3 IS NOT NULL THEN
995 v_addr := v_addr || ' ' || v_line_3;
996 END IF;
997 IF v_line_4 IS NOT NULL THEN
998 v_addr := v_addr || ' ' || v_line_4;
999 END IF;
1000 IF v_line_5 IS NOT NULL THEN
1001 v_addr := v_addr || ' ' || v_line_5;
1002 END IF;
1003 END IF;
1004 -- Test if v_addr is null, if so then raise exception
1005 IF v_addr IS NULL THEN
1006 RAISE e_addr;
1007 END IF;
1008 -- format string based on p_case_type
1009 IF UPPER(p_case_type) = 'UPPER' THEN
1010 v_addr := UPPER(v_addr);
1011 ELSIF UPPER(p_case_type) = 'LOWER' THEN
1012 v_addr := LOWER(v_addr);
1013 ELSIF UPPER(p_case_type) = 'NORMAL' THEN
1014 v_addr := INITCAP(v_addr);
1015 ELSIF UPPER(p_case_type) = 'DEFAULT' THEN
1016 NULL;
1017 ELSE
1018 RAISE e_addr;
1019 END IF; -- IF UPPER(p_case_type)
1020 RETURN v_addr;
1021 EXCEPTION
1022 WHEN e_addr THEN
1023 IF v_addr IS NULL THEN
1024 RETURN 'No Address record found';
1025 END IF;
1026 WHEN OTHERS THEN
1027 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1028 RETURN NULL;
1029 END genp_get_addr;
1030
1031 --skpandey, Bug#4937960: Stubbed genp_get_appl_owner, nowhere used
1032 FUNCTION genp_get_appl_owner
1033 RETURN VARCHAR2 AS
1034 BEGIN
1035 RETURN NULL;
1036 END;
1037
1038
1039 PROCEDURE genp_get_audit(
1040 p_table_name IN VARCHAR2 ,
1041 p_rowid IN VARCHAR2 ,
1042 p_update_who OUT NOCOPY VARCHAR2 ,
1043 p_update_on OUT NOCOPY DATE )
1044 AS
1045 /* change history
1046 WHO WHEN WHAT
1047 vrathi 10-JUN-2003 BUG:2940810 Added SQL bind variable
1048 */
1049 v_cursor integer;
1050 v_no_rows integer;
1051 v_update_who varchar2(100);
1052 v_update_on date;
1053 v_select_string varchar2(255);
1054 BEGIN
1055 v_cursor := dbms_sql.open_cursor;
1056 v_select_string := 'SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE FROM '||p_table_name||' WHERE rowid=:row_id';
1057
1058 dbms_sql.parse(v_cursor, v_select_string, dbms_sql.native);
1059
1060 dbms_sql.bind_variable(v_cursor,'row_id',p_rowid);
1061
1062 dbms_sql.define_column(v_cursor,1,v_update_who,30);
1063 dbms_sql.define_column(v_cursor,2,v_update_on);
1064 v_no_rows := dbms_sql.execute_and_fetch(v_cursor, false);
1065 dbms_sql.column_value(v_cursor,1,v_update_who);
1066 dbms_sql.column_value(v_cursor,2,v_update_on);
1067 p_update_who := v_update_who;
1068 p_update_on := v_update_on;
1069 dbms_sql.close_cursor(v_cursor);
1070 END genp_get_audit;
1071
1072
1073 FUNCTION genp_get_cmp_cutoff(
1074 p_acad_cal_type IN VARCHAR2 ,
1075 p_acad_ci_sequence_number IN NUMBER )
1076 RETURN DATE AS
1077 gv_other_detail VARCHAR2(255);
1078 BEGIN -- genp_get_cmp_cutoff
1079 -- This module gets the IGS_PS_COURSE completion cutoff date.
1080 DECLARE
1081 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
1082 CURSOR c_daiv_sgcc (
1083 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
1084 cp_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1085 SELECT daiv.alias_val
1086 FROM IGS_CA_DA_INST_V daiv,
1087 IGS_GE_S_GEN_CAL_CON sgcc
1088 WHERE daiv.cal_type = cp_acad_cal_type AND
1089 daiv.ci_sequence_number = cp_acad_ci_sequence_number AND
1090 daiv.dt_alias = sgcc.crs_completion_cutoff_dt_alias
1091 ORDER BY daiv.alias_val ASC;
1092 BEGIN
1093 OPEN c_daiv_sgcc(
1094 p_acad_cal_type,
1095 p_acad_ci_sequence_number);
1096 FETCH c_daiv_sgcc INTO v_alias_val;
1097 IF c_daiv_sgcc%FOUND THEN
1098 CLOSE c_daiv_sgcc;
1099 RETURN v_alias_val;
1100 END IF;
1101 CLOSE c_daiv_sgcc;
1102 RETURN NULL;
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 IF (c_daiv_sgcc%ISOPEN) THEN
1106 CLOSE c_daiv_sgcc;
1107 END IF;
1108 RAISE;
1109 END;
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
1113 END genp_get_cmp_cutoff;
1114
1115 FUNCTION adm_get_name(
1116 x_person_id in NUMBER)
1117 RETURN VARCHAR2 AS
1118
1119 l_name VARCHAR2(450);
1120 CURSOR name IS
1121 -- change the reference from igs_pe_person_v to igs_pe_person_base_v --rghosh
1122 SELECT full_name FROM igs_pe_person_base_v
1123 WHERE person_id = x_person_id;
1124 BEGIN
1125 IF x_person_id IS NULL THEN
1126 return NULL;
1127 ELSE
1128 OPEN name;
1129 FETCH name INTO l_name;
1130 CLOSE name;
1131 RETURN l_name;
1132 END IF;
1133
1134 END adm_get_name;
1135
1136 FUNCTION adm_get_unit_title(
1137 x_person_id NUMBER,
1138 x_admission_appl_number NUMBER,
1139 x_nominated_course_cd VARCHAR2)
1140 RETURN VARCHAR2 AS
1141 l_title VARCHAR2(90);
1142 CURSOR title IS
1143 SELECT title FROM igs_ad_unit_sets_v
1144 WHERE person_id = x_person_id AND
1145 admission_appl_number = x_admission_appl_number AND
1146 nominated_course_cd = x_nominated_course_cd;
1147 BEGIN
1148 OPEN title;
1149 FETCH title INTO l_title;
1150 CLOSE title;
1151 RETURN l_title;
1152
1153 END adm_get_unit_title;
1154
1155 END IGS_GE_GEN_001 ;
[Home] [Help]