1 PACKAGE BODY IGI_GEN_VERT AS
2 --- $Header: igigccbb.pls 120.8.12010000.2 2008/08/04 13:02:29 sasukuma ship $
3
4
5 --sdixit 28 jul 2003 MOAC changes START
6 FUNCTION is_req_installed
7 (p_option_name VARCHAR2
8 ,p_org_id NUMBER) RETURN VARCHAR2
9 IS
10 CURSOR Get_installed_option
11 is
12 SELECT status_flag
13 FROM igi_gcc_installed_options
14 WHERE option_name = upper(p_option_name)
15 AND org_id = p_org_id;
16
17 --Two queries seperated for bug3855184:Start
18 CURSOR Get_gl_fa_installed_option is
19 SELECT status_flag
20 FROM igi_gcc_gl_fa_inst_ops
21 WHERE option_name = upper(p_option_name) ;
22 --Two queries seperated for bug3855184:End
23
24 v_installed_flag varchar2(1);
25
26 BEGIN
27
28 OPEN get_installed_option ;
29 FETCH get_installed_option into v_installed_flag;
30 IF get_installed_option%NOTFOUND then --Bug3855184
31 OPEN Get_gl_fa_installed_option; --Bug3855184
32 FETCH Get_gl_fa_installed_option into v_installed_flag; --Bug3855184
33 CLOSE Get_gl_fa_installed_option; --Bug3855184
34 END IF; --Bug3855184
35 CLOSE get_installed_option ;
36
37 IF v_installed_flag = 'Y' THEN
38 RETURN 'Y';
39 ELSE
40 RETURN 'N';
41 END IF;
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN 'N';
46 END is_req_installed;
47 --sdixit MOAC chenges END
48
49 FUNCTION is_req_installed
50 (p_option_name VARCHAR2) RETURN BOOLEAN
51 is
52
53
54 CURSOR get_installed_option is
55 (SELECT status_flag
56 FROM IGI_gcc_installed_options
57 WHERE option_name = upper(p_option_name) );
58
59 --Two queries seperated for bug3855184:Start
60 CURSOR Get_gl_fa_installed_option is
61 (SELECT status_flag
62 FROM IGI_gcc_gl_fa_inst_ops
63 WHERE option_name = upper(p_option_name) ) ;
64 --Two queries seperated for bug3855184:End
65
66 v_installed_flag varchar2(1);
67
68 BEGIN
69
70 OPEN get_installed_option ;
71 FETCH get_installed_option into v_installed_flag;
72 IF get_installed_option%NOTFOUND then --Bug3855184
73 OPEN Get_gl_fa_installed_option; --Bug3855184
74 FETCH Get_gl_fa_installed_option into v_installed_flag; --Bug3855184
75 CLOSE Get_gl_fa_installed_option; --Bug3855184
76 END IF; --Bug3855184
77 CLOSE get_installed_option ;
78
79
80 IF v_installed_flag = 'Y' THEN
81 RETURN TRUE;
82 ELSE
83 RETURN FALSE;
84 END IF;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88
89 RETURN FALSE;
90
91 END is_req_installed;
92
93
94 /* ===================================================================
95 This Procedure is used to record debug information
96 ===================================================================
97 */
98 PROCEDURE DEBUG
99 ( p_module IN VARCHAR2
100 , p_module_variable IN VARCHAR2
101 , p_variable_value IN VARCHAR2
102 , P_message IN VARCHAR2
103 )
104 is
105 begin
106
107 NULL;
108 /* ********************************************
109 UNCOMMENT OUT NOCOPY THIS LINE ONCE THE TABLE IS DEFINED
110
111 ***************************************************** *
112 insert into IGI_debug_all ( MODULE
113 , MODULE_VARIABLE
114 , VARIABLE_VALUE
115 , MODULE_MESSAGE
116 , DEBUG_SEQUENCE)
117 select p_module
118 , p_module_variable
119 , p_variable_value
120 , P_message
121 , IGI_debug_s.nextval
122 from dual;
123 commit;
124
125 ********************************************************* */
126 EXCEPTION
127 when others then null;
128 end;
129
130 /* ===================================================================
131 This Procedure returns entries STATUS_FLAG and ERROR_NUM from
132 IGI_INSTALLED_OPTIONS for a given OPTION_NAME
133 ===================================================================
134 */
135
136 PROCEDURE get_option_status
137 ( p_option_name IN VARCHAR2
138 , p_status_flag OUT NOCOPY VARCHAR2
139 , p_error_num OUT NOCOPY NUMBER
140 )
141 IS
142
143 CURSOR stat_check IS
144 (SELECT status_flag
145 FROM IGI_gcc_installed_options
146 WHERE option_name = upper(p_option_name ));
147
148 --Two queries seperated for bug3855184:Start
149 CURSOR gl_fa_stat_check is
150 (SELECT status_flag
151 FROM IGI_gcc_gl_fa_inst_ops
152 WHERE option_name = upper(p_option_name )) ;
153 --Two queries seperated for bug3855184:End
154
155 BEGIN
156 p_error_num := 0;
157 p_status_flag := 'N';
158
159 OPEN stat_check ;
160 FETCH stat_check into p_status_flag;
161 IF stat_check%NOTFOUND then --Bug3855184
162 OPEN gl_fa_stat_check; --Bug3855184
163 FETCH gl_fa_stat_check into p_status_flag; --Bug3855184
164 CLOSE gl_fa_stat_check; --Bug3855184
165 END IF; --Bug3855184
166 CLOSE stat_check;
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN p_status_flag := 'N';
170 p_error_num := 1 ;
171 WHEN TOO_MANY_ROWS THEN p_status_flag := 'N';
172 p_error_num := 1 ;
173 WHEN OTHERS THEN p_status_flag := 'N';
174 p_error_num := 1 ;
175 END; -- Of get_option_status
176
177
178
179
180 FUNCTION GET_LOOKUP_MEANING (l_lookup_type VARCHAR2
181 ) RETURN VARCHAR2 IS
182
183 l_return_meaning VARCHAR2(240);
184 BEGIN
185
186 SELECT meaning into l_return_meaning
187 from IGI_LOOKUPS
188 WHERE LOOKUP_TYPE = l_lookup_type;
189
190 return (l_return_meaning);
191 EXCEPTION
192 WHEN OTHERS THEN
193 return (l_lookup_type);
194 END; -- Of GET_LOOKUP_MEANING
195
196
197
198 PROCEDURE IGI_EFC_CHECK_OPTIONS
199 (p_sob NUMBER
200 ,p_efc1 IN OUT NOCOPY VARCHAR2
201 ) IS
202 -- 29-MAR-00 EGARRETT
203 -- Removed the payment_funds_check_flag as it no longer exists
204 -- in table (part of EFC II).
205 CURSOR c_get_efc_options IS
206 SELECT mult_funding_budgets_flag
207 FROM psa_efc_options
208 WHERE set_of_books_id = p_sob;
209
210 x_mfb VARCHAR2(1);
211
212 BEGIN
213 OPEN c_get_efc_options;
214 FETCH c_get_efc_options INTO x_mfb;
215 IF c_get_efc_options%NOTFOUND THEN
216 RAISE NO_DATA_FOUND;
217 END IF;
218 IF c_get_efc_options%ISOPEN THEN
219 CLOSE c_get_efc_options;
220 END IF;
221
222 p_efc1 := x_mfb;
223
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 IF c_get_efc_options%ISOPEN THEN
227 CLOSE c_get_efc_options;
228 END IF;
229 p_efc1 := 'N';
230
231 --null;
232
233 WHEN OTHERS THEN
234 fnd_message.set_name('IGI','IGI_EFC_CHECK_OPTIONS');
235 app_exception.raise_exception;
236 END;
237
238 --M Thompson 23-Dec-1998 Add HUL functions START
239
240 FUNCTION get_ap_sob_id RETURN NUMBER IS
241 sErrMsg VARCHAR2(2000);
242 p_sob_id NUMBER;
243 BEGIN
244 SELECT set_of_books_id
245 INTO p_sob_id
246 FROM ap_system_parameters;
247 RETURN (p_sob_id);
248 EXCEPTION
249 WHEN OTHERS THEN sErrMsg := SQLERRM;
250 raise_application_error(-20000, sErrMsg);
251
252 END;
253
254 FUNCTION get_ar_sob_id RETURN NUMBER IS
255 sErrMsg VARCHAR2(2000);
256 p_sob_id NUMBER;
257 BEGIN
258 SELECT set_of_books_id
259 INTO p_sob_id
260 FROM ar_system_parameters;
261 RETURN (p_sob_id);
262 EXCEPTION
263 WHEN OTHERS THEN sErrMsg := SQLERRM;
264 raise_application_error(-20000, sErrMsg);
265 END;
266
267
268 FUNCTION get_po_sob_id RETURN NUMBER IS
269 sErrMsg VARCHAR2(2000);
270 p_sob_id NUMBER;
271 BEGIN
272 SELECT set_of_books_id
273 INTO p_sob_id
274 FROM financials_system_parameters;
275 RETURN (p_sob_id);
276 EXCEPTION
277 WHEN OTHERS THEN sErrMsg := SQLERRM;
278 raise_application_error(-20000, sErrMsg);
279 END;
280
281 --M Thompson 23-Dec-1998 Add HUL functions END
282
283
284 /* ===================================================================
285 This Function checks for at least one igi option installed
286 It is being used by IGIUTIL2, to see if it will need to process.
287 Any DB hits from IGILUTIL2 are being moved here to package.
288 ===================================================================
289 */
290
291 FUNCTION igiInstalled RETURN BOOLEAN IS
292
293 l_ErrMsg VARCHAR2(2000);
294 l_dummy VARCHAR2(1);
295
296 CURSOR c_igi_install is
297 SELECT 'x'
298 FROM igi_gcc_gl_fa_inst_ops
299 WHERE status_flag = 'Y';
300
301 BEGIN
302
303 OPEN c_igi_install;
304 FETCH c_igi_install INTO l_dummy;
305 IF c_igi_install%FOUND THEN
306 CLOSE c_igi_install;
307 RETURN TRUE;
308 END IF;
309 CLOSE c_igi_install;
310 RETURN FALSE;
311
312
313 EXCEPTION
314 WHEN OTHERS THEN l_errMsg := SQLERRM;
315 raise_application_error(-20000, l_errMsg);
316
317 END igiInstalled;
318
319
320 /* ===================================================================
321 This Function saves the IGI installed options within global variables
322 This is unavoidable is to prevent continious accesses to the DB.
323 This is being used by IGILUTIL2
324 ===================================================================
325 */
326
327 FUNCTION cacheProductOptions RETURN BOOLEAN IS
328
329 l_ErrMsg VARCHAR2(2000);
330
331 CURSOR c_install_option is
332 (SELECT option_name
333 FROM IGI_gcc_installed_options
334 WHERE status_flag = 'Y')
335 UNION
336 (SELECT option_name
337 FROM IGI_gcc_gl_fa_inst_ops
338 WHERE status_flag = 'Y');
339
340 BEGIN
341
342 IGI_IGILUTIL2_CBC := FALSE;
343 IGI_IGILUTIL2_CC := FALSE;
344 IGI_IGILUTIL2_CIS := FALSE;
345 IGI_IGILUTIL2_DOS := FALSE;
346 IGI_IGILUTIL2_EXP := FALSE;
347 IGI_IGILUTIL2_IAC := FALSE;
348 IGI_IGILUTIL2_MHC := FALSE;
349 IGI_IGILUTIL2_SIA := FALSE;
350 IGI_IGILUTIL2_STP := FALSE;
351
352 FOR install_option_rec IN c_install_option LOOP
353
354 IF upper(install_option_rec.option_name) = 'CBC' THEN
355 IGI_IGILUTIL2_CBC := TRUE;
356 ELSIF upper(install_option_rec.option_name) = 'CC' THEN
357 IGI_IGILUTIL2_CC := TRUE;
358 ELSIF upper(install_option_rec.option_name) = 'CIS' THEN
359 IGI_IGILUTIL2_CIS := TRUE;
360 ELSIF upper(install_option_rec.option_name) = 'DOS' THEN
361 IGI_IGILUTIL2_DOS := TRUE;
362 ELSIF upper(install_option_rec.option_name) = 'EXP' THEN
363 IGI_IGILUTIL2_EXP := TRUE;
364 ELSIF upper(install_option_rec.option_name) = 'IAC' THEN
365 IGI_IGILUTIL2_IAC := TRUE;
366 ELSIF upper(install_option_rec.option_name) = 'MHC' THEN
367 IGI_IGILUTIL2_MHC := TRUE;
368 ELSIF upper(install_option_rec.option_name) = 'SIA' THEN
369 IGI_IGILUTIL2_SIA := TRUE;
370 ELSIF upper(install_option_rec.option_name) = 'STP' THEN
371 IGI_IGILUTIL2_STP := TRUE;
372 ELSE
373 null;
374 END IF;
375
376 END LOOP;
377
378 IF IGI_IGILUTIL2_CBC OR IGI_IGILUTIL2_CC OR IGI_IGILUTIL2_CIS OR
379 IGI_IGILUTIL2_DOS OR IGI_IGILUTIL2_EXP OR IGI_IGILUTIL2_IAC OR
380 IGI_IGILUTIL2_MHC OR IGI_IGILUTIL2_SIA OR IGI_IGILUTIL2_STP
381 THEN
382 RETURN TRUE;
383 ELSE
384 RETURN FALSE;
385 END IF;
386
387
388 EXCEPTION
389 WHEN OTHERS THEN l_errMsg := SQLERRM;
390 raise_application_error(-20000, l_errMsg);
391
392 END cacheProductOptions;
393
394
395
396 /* ===================================================================
397 This Function gets the global variables from the package spec.
398 Apparently Forms and libraries cannot access global variables
399 in a stored package directly, you have to go by function or procedure
400 ===================================================================
401 */
402
403 FUNCTION productEnabled ( prod IN VARCHAR2 ) RETURN BOOLEAN IS
404
405 l_ErrMsg VARCHAR2(2000);
406
407 BEGIN
408
409 IF upper(prod) = 'CBC' THEN
410 IF IGI_IGILUTIL2_CBC THEN
411 RETURN TRUE;
412 END IF;
413 ELSIF upper(prod) = 'CC' THEN
414 IF IGI_IGILUTIL2_CC THEN
415 RETURN TRUE;
416 END IF;
417 ELSIF upper(prod) = 'CIS' THEN
418 IF IGI_IGILUTIL2_CIS THEN
419 RETURN TRUE;
420 END IF;
421 ELSIF upper(prod) = 'DOS' THEN
422 IF IGI_IGILUTIL2_DOS THEN
423 RETURN TRUE;
424 END IF;
425 ELSIF upper(prod) = 'EXP' THEN
426 IF IGI_IGILUTIL2_EXP THEN
427 RETURN TRUE;
428 END IF;
429 ELSIF upper(prod) = 'IAC' THEN
430 IF IGI_IGILUTIL2_IAC THEN
431 RETURN TRUE;
432 END IF;
433 ELSIF upper(prod) = 'MHC' THEN
434 IF IGI_IGILUTIL2_MHC THEN
435 RETURN TRUE;
436 END IF;
437 ELSIF upper(prod) = 'SIA' THEN
438 IF IGI_IGILUTIL2_SIA THEN
439 RETURN TRUE;
440 END IF;
441 ELSIF upper(prod) = 'STP' THEN
442 IF IGI_IGILUTIL2_STP THEN
443 RETURN TRUE;
444 END IF;
445 ELSE
446 RETURN FALSE;
447 END IF;
448
449 RETURN FALSE;
450
451
452 EXCEPTION
453 WHEN OTHERS THEN l_errMsg := SQLERRM;
454 raise_application_error(-20000, l_errMsg);
455
456 END productEnabled;
457
458 /* ============================================================== */
459 END;