1 PACKAGE BODY IGS_GE_GEN_004 AS
2 /* $Header: IGSGE04B.pls 115.14 2003/11/25 13:22:25 asbala ship $ */
3
4 ------------------------------------------------------------------
5 --
6 -- Bug ID : 1938728
7 -- who when what
8 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
9 -- smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS
10 -- removed .
11 -- sjadhav 16-aug-2001 removed hardcoded reference to
12 -- apps schema in cursor c_user_name
13 -- msrinivi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_bus_day
14 -- removed .
15 -- asbala 23-JUL-2003 Changed IGS_lookup_view to igs_lookup_values in CURSOR c_lookup_meaning
16 --asbala 23-JUL-03 Bug No:2667343 populating x_meaning from lookup_type = Report_Type and lookup_code = A
17 -- instead of hard coded string 'All'.
18 ------------------------------------------------------------------
19
20 FUNCTION GENP_GET_WHO_NAME(p_last_updated_by IN NUMBER)
21 RETURN VARCHAR2 IS
22 CURSOR c_user_name is
23 SELECT user_name
24 FROM fnd_user
25 WHERE user_id=p_last_updated_by;
26 x_user_name VARCHAR2(100);
27 begin
28 OPEN c_user_name;
29 FETCH c_user_name INTO x_user_name;
30 IF c_user_name%FOUND then
31 RETURN x_user_name;
32 ELSE
33 RETURN NULL;
34 END IF;
35 EXCEPTION
36 WHEN OTHERS THEN
37 RETURN FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
38 RAISE;
39 END GENP_GET_WHO_NAME;
40
41 FUNCTION GENP_GET_LOOKUP(
42 p_lookup_type IN VARCHAR2,
43 p_lookup_code IN VARCHAR2)
44 RETURN VARCHAR2 IS
45 --asbala 23-JUL-03 Changed igs_lookups_view to igs_lookup_values
46 CURSOR c_lookup_meaning(cp_lookup_type VARCHAR2,
47 cp_lookup_code VARCHAR2) IS
48 SELECT meaning
49 FROM IGS_LOOKUP_VALUES
50 WHERE lookup_type=cp_lookup_type AND
51 lookup_code=cp_lookup_code;
52 x_meaning VARCHAR2(80);
53 BEGIN
54 --asbala 23-JUL-03 Bug No:2667343 populating x_meaning from lookup_type = Report_Type and lookup_code = A
55 -- instead of hard coded string 'All'.
56 IF (p_lookup_code = '%' OR p_lookup_code IS NULL) THEN
57 OPEN c_lookup_meaning('REPORT_TYPE','A');
58 FETCH c_lookup_meaning INTO x_meaning;
59 CLOSE c_lookup_meaning;
60 RETURN x_meaning;
61 END IF;
62 OPEN c_lookup_meaning(p_lookup_type,p_lookup_code);
63 FETCH c_lookup_meaning INTO x_meaning;
64 IF c_lookup_meaning%FOUND THEN
65 CLOSE c_lookup_meaning;
66 RETURN x_meaning;
67 END IF;
68 CLOSE c_lookup_meaning;
69 RETURN NULL;
70 EXCEPTION
71 WHEN OTHERS THEN
72 RETURN FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
73 RAISE;
74 END GENP_GET_LOOKUP;
75
76 FUNCTION GENP_UPD_ST_LGC_DEL(
77 p_person_id IN NUMBER ,
78 p_s_student_todo_type IN VARCHAR2 ,
79 p_sequence_number IN NUMBER ,
80 p_message_name OUT NOCOPY VARCHAR2 )
81 RETURN BOOLEAN AS
82 e_resource_busy_exception EXCEPTION;
83 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54 );
84 gv_other_details VARCHAR2(255);
85 BEGIN
86 DECLARE
87 v_st_record IGS_PE_STD_TODO%ROWTYPE;
88 CURSOR c_lock_st_records IS
89 SELECT *
90 FROM IGS_PE_STD_TODO
91 WHERE person_id = p_person_id AND
92 s_student_todo_type = p_s_student_todo_type AND
93 sequence_number = p_sequence_number
94 FOR UPDATE
95 NOWAIT;
96 CURSOR SI_PE_TODO_CUR IS
97 SELECT IGS_PE_STD_TODO.* , ROWID
98 FROM IGS_PE_STD_TODO
99 WHERE person_id = p_person_id AND
100 s_student_todo_type = p_s_student_todo_type AND
101 sequence_number = p_sequence_number;
102
103
104 BEGIN
105 -- Update the IGS_PE_STD_TODO table with the NOWAIT option.
106 OPEN c_lock_st_records;
107 FETCH c_lock_st_records INTO v_st_record;
108 CLOSE c_lock_st_records;
109 FOR SI_RE_REC IN SI_PE_TODO_CUR LOOP
110 IGS_PE_STD_TODO_PKG.UPDATE_ROW(
111 x_rowid => SI_RE_REC.ROWID ,
112 X_PERSON_ID => SI_RE_REC.PERSON_ID,
113 X_S_STUDENT_TODO_TYPE => SI_RE_REC.S_STUDENT_TODO_TYPE,
114 X_SEQUENCE_NUMBER => SI_RE_REC.SEQUENCE_NUMBER,
115 X_TODO_DT => SI_RE_REC.TODO_DT,
116 X_LOGICAL_DELETE_DT => SYSDATE ,
117 X_MODE=> 'R'
118 );
119 END LOOP;
120 p_message_name := null ;
121 RETURN TRUE;
122 END;
123 EXCEPTION
124 WHEN e_resource_busy_exception THEN
125 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
126 IGS_GE_MSG_STACK.ADD;
127 RETURN FALSE;
128 WHEN OTHERS THEN
129 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception ;
132 END genp_upd_st_lgc_del;
133
134
135 FUNCTION GENP_VAL_ADT_CRSP(
136 p_addr_type FND_LOOKUP_VALUES.lookup_code%type ,
137 p_crsp_ind IGS_PE_HZ_LOCATIONS.correspondence%TYPE )
138 RETURN VARCHAR2 AS
139 -- This modules is used to test if the passed address type
140 -- and correspondence indicator exists in the system. This is
141 -- only used for validation in the Inquiry System.
142 v_other_detail VARCHAR2(255) := NULL;
143 v_found VARCHAR2(1) := NULL;
144 CURSOR c_adt( cp_addr_type IGS_CO_ADDR_TYPE.addr_type%TYPE,
145 cp_crsp_ind IGS_PE_PERSON_ADDR.correspondence_ind%TYPE)
146 iS
147 SELECT
148 'x'
149 FROM IGS_CO_ADDR_TYPE adt
150 WHERE adt.addr_type = cp_addr_type;
151
152 BEGIN
153 IF c_adt%ISOPEN
154 THEN
155 CLOSE c_adt;
156 END IF;
157 OPEN c_adt( p_addr_type, p_crsp_ind);
158 FETCH c_adt INTO v_found;
159 IF c_adt%NOTFOUND
160 THEN
161 CLOSE c_adt;
162 RETURN 'N';
163 ELSE
164 CLOSE c_adt;
165 RETURN 'Y';
166 END IF;
167 EXCEPTION
168 WHEN OTHERS THEN
169 IF c_adt%ISOPEN
170 THEN
171 CLOSE c_adt;
172 END IF;
173 RETURN 'N';
174 END genp_val_adt_crsp;
175
176 FUNCTION JBSP_GET_DT_PICTURE(
177 p_char_dt IN VARCHAR2 ,
178 p_dt_picture OUT NOCOPY VARCHAR2 )
179 RETURN BOOLEAN AS
180 BEGIN
181 DECLARE
182 v_return_dt DATE := NULL;
183 v_char_dt VARCHAR2(40);
184 BEGIN
185 -- This function accepts a date string, determines what format
186 -- should be used to convert the string to a date and returns
187 -- the date picture and true if a valid date picture found.
188 v_char_dt := SUBSTR(REPLACE(p_char_dt, '''', ''), 1, 40);
189 IF v_char_dt IS NULL THEN
190 p_dt_picture := ' ';
191 RETURN FALSE;
192 END IF;
193 v_return_dt := TO_DATE(v_char_dt, 'DD/MM/YY');
194 p_dt_picture := 'DD/MM/YY';
195 RETURN TRUE;
196 EXCEPTION WHEN OTHERS THEN
197 BEGIN
198 v_return_dt := TO_DATE(v_char_dt, 'DD/MM/YYYY');
199 p_dt_picture := 'DD/MM/YYYY';
200 RETURN TRUE;
201 EXCEPTION WHEN OTHERS THEN
202 BEGIN
203 v_return_dt := TO_DATE(v_char_dt, 'DD-MM-YY');
204 p_dt_picture := 'DD-MM-YY';
205 RETURN TRUE;
206 EXCEPTION WHEN OTHERS THEN
207 BEGIN
208 v_return_dt := TO_DATE(v_char_dt, 'DD-MM-YYYY');
209 p_dt_picture := 'DD-MM-YYYY';
210 RETURN TRUE;
211 EXCEPTION WHEN OTHERS THEN
212 BEGIN
213 v_return_dt := TO_DATE(v_char_dt, 'DD-MM-YY HH24:MI:SS');
214 p_dt_picture := 'DD-MM-YY HH24:MI:SS';
215 RETURN TRUE;
216 EXCEPTION WHEN OTHERS THEN
217 BEGIN
218 v_return_dt := TO_DATE(v_char_dt, 'DD-MM-YYYY HH24:MI:SS');
219 p_dt_picture := 'DD-MM-YYYY HH24:MI:SS';
220 RETURN TRUE;
221 EXCEPTION WHEN OTHERS THEN
222 BEGIN
223 v_return_dt := TO_DATE(v_char_dt, 'DD/MM/YY HH24:MI:SS');
224 p_dt_picture := 'DD/MM/YY HH24:MI:SS';
225 RETURN TRUE;
226 EXCEPTION WHEN OTHERS THEN
227 BEGIN
228 v_return_dt := TO_DATE(v_char_dt, 'DD/MM/YYYY HH24:MI:SS');
229 p_dt_picture := 'DD/MM/YYYY HH24:MI:SS';
230 RETURN TRUE;
231 EXCEPTION WHEN OTHERS THEN
232 p_dt_picture := ' ';
233 RETURN FALSE;
234 END;
235 END;
236 END;
237 END;
238 END;
239 END;
240 END;
241 END;
242 END jbsp_get_dt_picture;
243
244 FUNCTION get_day (
245 p_day_short_name IN VARCHAR2
246 ) RETURN VARCHAR2 IS
247 CURSOR cur_day (cp_day_short_name IN VARCHAR2) IS
248 SELECT meaning
249 FROM fnd_lookups
250 WHERE lookup_type = 'DAY_NAME'
251 AND lookup_code = cp_day_short_name;
252 l_day fnd_lookups.meaning%TYPE;
253 BEGIN
254 OPEN cur_day (p_day_short_name);
255 FETCH cur_day INTO l_day;
256 CLOSE cur_day;
257 RETURN (l_day);
258 END get_day;
259
260 -- Created msrinivi
261 -- 28 Feb, 03
262 -- Returns P_QUERY_STR is > 0
263 -- else returns 0. Used when we want
264 -- to display zero for -ve numbers
265
266 FUNCTION get_positive_num(
267 P_NUMBER IN NUMBER
268 ) RETURN VARCHAR2 IS
269 l varchar2(2000) ;
270 BEGIN
271 IF P_NUMBER IS NULL THEN
272 RETURN P_NUMBER ;
273 END IF;
274
275 SELECT TO_CHAR(decode(sign(to_number(P_NUMBER )),-1,0,to_number(P_NUMBER))) into l from dual;
276 RETURN l;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 RETURN l;
281 END;
282
283 FUNCTION get_unit_set_title (p_unit_set_cd VARCHAR2) RETURN VARCHAR2 IS
284
285 CURSOR c_title (cp_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE) IS
286 SELECT us_out.abbreviation, us_out.short_title, us_out.title
287 FROM igs_en_unit_set us_out
288 WHERE us_out.unit_set_cd = cp_unit_set_cd
289 AND us_out.version_number = (SELECT version_number
290 FROM igs_en_unit_set us_in ,
291 igs_en_unit_set_stat uss
292 WHERE uss.unit_set_status = us_in.unit_set_status
293 AND us_in.unit_set_cd = us_out.unit_set_cd
294 AND uss.s_unit_set_status = 'ACTIVE'
295 AND rownum = 1);
296 rec_title c_title%ROWTYPE;
297
298 CURSOR c_setup IS
299 SELECT wif_unit_set_title
300 FROM igs_da_setup
301 WHERE s_control_num = 1;
302 l_wif_unit_set_title igs_da_setup.wif_unit_set_title%TYPE;
303 BEGIN
304 -- get unit set title
305 OPEN c_title (p_unit_set_cd);
306 FETCH c_title INTO rec_title;
307 CLOSE c_title;
308 -- get the setup
309 OPEN c_setup;
310 FETCH c_setup INTO l_wif_unit_set_title;
311 CLOSE c_setup;
312
313 IF l_wif_unit_set_title='ABBR' THEN
314 RETURN rec_title.abbreviation;
315 ELSIF l_wif_unit_set_title='STIL' THEN
316 RETURN rec_title.short_title;
317 ELSE
318 RETURN rec_title.title;
319 END IF;
320
321 END get_unit_set_title;
322
323 END IGS_GE_GEN_004 ;
[Home] [Help]