1 PACKAGE BODY OM_REPORTS_MLS_LANG AS
2 /* $Header: OEXOEAMB.pls 120.0 2005/06/01 01:54:52 appldev noship $ */
3
4 FUNCTION GET_LANG RETURN VARCHAR2 IS
5 p_order_number_lo NUMBER := NULL;
6 p_order_number_hi NUMBER := NULL;
7 p_order_date_lo DATE := NULL ;
8 p_order_date_hi DATE := NULL ;
9 p_schedule_date_lo DATE := NULL ;
10 p_schedule_date_hi DATE := NULL ;
11 p_request_date_lo DATE := NULL ;
12 p_request_date_hi DATE := NULL ;
13 p_promise_date_lo DATE := NULL ;
14 p_promise_date_hi DATE := NULL ;
15 p_bill_to_customer_name_lo VARCHAR2(50) := NULL;
16 p_bill_to_customer_name_hi VARCHAR2(50) := NULL;
17 p_ship_to_customer_name_lo VARCHAR2(50) := NULL;
18 p_ship_to_customer_name_hi VARCHAR2(50) := NULL;
19 p_del_to_customer_name_lo VARCHAR2(50) := NULL;
20 p_del_to_customer_name_hi VARCHAR2(50) := NULL;
21 p_salesrep VARCHAR2(50) := NULL;
22 p_created_by VARCHAR2(50) := NULL;
23 p_open_flag VARCHAR2(1) := NULL;
24 p_booked_status VARCHAR2(50) := NULL;
25 p_order_type NUMBER := NULL;
26
27 ret_val NUMBER := NULL;
28 parm_number NUMBER ;
29 v_select_statement VARCHAR2(4000) ;
30 v_select_statement1 VARCHAR2(2000) ;
31 v_select_statement2 VARCHAR2(2000) ;
32 v_select_statement3 VARCHAR2(2000) ;
33 v_select_statement4 VARCHAR2(2000) ;
34 v_select_statement5 VARCHAR2(2000) ;
35 v_select_statement6 VARCHAR2(2000) ;
36 l_cursor_id INTEGER ;
37 lang_string VARCHAR2(240) := NULL;
38 l_lang VARCHAR2(30) ;
39 l_lang_str VARCHAR2(500) := NULL;
40 l_base_lang VARCHAR2(30) ;
41 l_dummy INTEGER ;
42
43 BEGIN
44
45 -- ORDER NUMBER (From)
46 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order Number (From)', parm_number
47 );
48
49 IF (ret_val = -1) THEN
50 p_order_number_lo := NULL;
51 ELSE
52 p_order_number_lo := TO_NUMBER(FND_REQUEST_INFO.GET_PARAMETER(parm_number))
53 ;
54
55 END IF;
56
57 -- ORDER NUMBER (To)
58 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order Number (To)', parm_number);
59
60 IF (ret_val = -1) THEN
61 p_order_number_hi := NULL;
62 ELSE
63 p_order_number_hi := TO_NUMBER(FND_REQUEST_INFO.GET_PARAMETER(parm_number))
64 ;
65
66 END IF;
67
68 -- ORDER DATE(From)
69 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order Date (From)', parm_number);
70
71
72 IF (ret_val = -1) THEN
73 p_order_date_lo := NULL;
74 ELSE
75 p_order_date_lo := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER
76 (parm_number));
77
78 END IF;
79
80 -- ORDER DATE To
81 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order Date (To)', parm_number)
82 ;
83
84 IF (ret_val = -1) THEN
85 p_order_date_hi := NULL;
86 ELSE
87 p_order_date_hi := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER
88 (parm_number));
89
90 END IF;
91
92
93 --SCHEDULE DATE(From)
94 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Schedule Date (From)', parm_number);
95
96
97 IF (ret_val = -1) THEN
98 p_schedule_date_lo := NULL;
99 ELSE
100 p_schedule_date_lo := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
101
102 END IF;
103
104 -- SCHEDULE DATE To
105 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Schedule Date (To)', parm_number)
106 ;
107
108 IF (ret_val = -1) THEN
109 p_schedule_date_hi := NULL;
110 ELSE
111 p_schedule_date_hi := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
112
113 END IF;
114
115 -- PROMISE DATE(From)
116 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Promise Date (From)', parm_number);
117
118
119 IF (ret_val = -1) THEN
120 p_promise_date_lo := NULL;
121 ELSE
122 p_promise_date_lo := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
123
124 END IF;
125
126 -- PROMISE DATE To
127 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Promise Date (To)', parm_number)
128 ;
129
130 IF (ret_val = -1) THEN
131 p_promise_date_hi := NULL;
132 ELSE
133 p_promise_date_hi := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
134
135 END IF;
136
137 --REQUEST DATE(From)
138 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Request Date (From)', parm_number);
139
140
141 IF (ret_val = -1) THEN
142 p_request_date_lo := NULL;
143 ELSE
144 p_request_date_lo := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
145
146 END IF;
147
148 -- REQUEST DATE To
149 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Request Date (To)', parm_number)
150 ;
151
152 IF (ret_val = -1) THEN
153 p_request_date_hi := NULL;
154 ELSE
155 p_request_date_hi := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(parm_number));
156
157 END IF;
158
159 -- BILL TO CUSTOMER NAME(From)
160 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Bill To Customer Name (From)', parm_number);
161
162 IF (ret_val = -1) THEN
163 p_bill_to_customer_name_lo := NULL;
164 ELSE
165 p_bill_to_customer_name_lo := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
166 END IF;
167
168 -- BILL TO CUSTOMER NAME To
169 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Bill To Customer Name (To)', parm_number);
170
171 IF (ret_val = -1) THEN
172 p_bill_to_customer_name_hi := NULL;
173 ELSE
174 p_bill_to_customer_name_hi := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
175 END IF;
176
177 -- SHIP TO CUSTOMER NAME(From)
178 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Ship To Customer Name (From)', parm_number);
179
180 IF (ret_val = -1) THEN
181 p_ship_to_customer_name_lo := NULL;
182 ELSE
183 p_ship_to_customer_name_lo := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
184 END IF;
185
186 -- SHIP TO CUSTOMER NAME To
187 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Ship To Customer Name (To)', parm_number);
188
189 IF (ret_val = -1) THEN
190 p_ship_to_customer_name_hi := NULL;
191 ELSE
192 p_ship_to_customer_name_hi := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
193 END IF;
194
195
196 -- Deliver TO CUSTOMER NAME(From)
197 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Deliver To Customer (From)', parm_number);
198
199 IF (ret_val = -1) THEN
200 p_del_to_customer_name_lo := NULL;
201 ELSE
202 p_del_to_customer_name_lo := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
203 END IF;
204
205 -- DELIVER TO CUSTOMER NAME To
206 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Deliver To Customer (To)', parm_number);
207
208 IF (ret_val = -1) THEN
209 p_del_to_customer_name_hi := NULL;
210 ELSE
211 p_del_to_customer_name_hi := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
212 END IF;
213
214
215 -- Order Type
216 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Order Type', parm_number);
217
218
219 IF (ret_val = -1) THEN
220 p_order_type := NULL;
221 ELSE
222 p_order_type := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
223
224 END IF;
225
226
227 -- Salesperson
228 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Salesperson', parm_number);
229
230
231 IF (ret_val = -1) THEN
232 p_salesrep := NULL;
233 ELSE
234 p_salesrep := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
235
236 END IF;
237
238 -- Open Orders
239 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Open Orders Only', parm_number);
240
241
242 IF (ret_val = -1) THEN
243 p_open_flag := NULL;
244 ELSE
245 p_open_flag := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
246
247 END IF;
248
249 -- Booked Status
250 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Booked Status', parm_number);
251
252
253 IF (ret_val = -1) THEN
254 p_booked_status := NULL;
255 ELSE
256 p_booked_status := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
257
258 END IF;
259
260 -- Created By
261 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Created By', parm_number);
262
263
264 IF (ret_val = -1) THEN
265 p_created_by := NULL;
266 ELSE
267 p_created_by := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
268
269 END IF;
270
271 --Get Base Language
272
273 SELECT language_code INTO l_base_lang FROM fnd_languages
274 WHERE installed_flag = 'B';
275
276 --Create a query string to get languages based on parameters.
277
278
279 v_select_statement1 :=
280 'select DISTINCT loc.language from oe_order_headers_all h, oe_order_lines_all l, hz_cust_accounts bill_cust, hz_cust_accounts ship_cust, hz_cust_accounts del_cust, hz_cust_site_uses bill_su, hz_parties bill_p, hz_parties ship_p, hz_parties del_p,';
281
282 v_select_statement2 := ' hz_cust_site_uses ship_su, hz_cust_site_uses del_su,hz_cust_acct_sites a, hz_cust_acct_sites bill_addr, hz_cust_acct_sites ship_addr, hz_cust_acct_sites del_addr, ra_salesreps sr, fnd_user u, hz_party_sites ps, hz_locations loc';
283
284 -- Changes are made as a part of bug # 4221221
285 -- Removed the conditions l.item_type_code(+) != ''INCLUDED'' and h.cancelled_flag is null
286 -- These conditions are not required here
287
288 v_select_statement3 :=
289 ' where h.header_id = l.header_id(+) and h.salesrep_id = sr.salesrep_id (+) and u.user_id = h.created_by';
290
291 /* ' where h.cancelled_flag is null and h.header_id = l.header_id(+) and l.item_type_code(+) != ''INCLUDED'' and h.salesrep_id = sr.salesrep_id (+) and u.user_id = h.created_by';*/
292
293 v_select_statement4 := ' and h.ship_to_org_id = ship_su.site_use_id(+) and ship_su.cust_acct_site_id = ship_addr.cust_acct_site_id(+) and ship_addr.cust_account_id = ship_cust.cust_account_id(+) ';
294
295 v_select_statement5 :=
296 'and h.invoice_to_org_id = bill_su.site_use_id(+) and bill_su.cust_acct_site_id = bill_addr.cust_acct_site_id(+) and bill_addr.cust_account_id = bill_cust.cust_account_id(+) and h.deliver_to_org_id = del_su.site_use_id(+) and ';
297
298 v_select_statement6 := ' del_su.cust_acct_site_id = del_addr.cust_acct_site_id(+) and del_addr.cust_account_id = del_cust.cust_account_id(+) and (h.invoice_to_org_id is not null or h.ship_to_org_id is not null) and a.cust_account_id = h.sold_to_org_id';
299 v_select_statement6 := v_select_statement6 || ' and bill_cust.party_id = bill_p.party_id and ship_cust.party_id = ship_p.party_id and del_cust.party_id = del_p.party_id and a.party_site_id = ps.party_site_id and ps.location_id = loc.location_id';
300
301 v_select_statement := v_select_statement1 || v_select_statement2 || v_select_statement3 || v_select_statement4 || v_select_statement5 || v_select_statement6;
302
303 IF p_order_number_lo IS NOT NULL OR p_order_number_hi IS NOT NULL THEN
304 IF p_order_number_lo IS NULL THEN
305 v_select_statement := v_select_statement||' AND h.order_number <= :p_order_number_hi';
306
307 ELSIF p_order_number_hi IS NULL THEN
308 v_select_statement := v_select_statement||' AND h.order_number >= :p_order_number_lo';
309
310 ELSE
311 v_select_statement := v_select_statement||' AND h.order_number BETWEEN :p_order_number_lo AND :p_order_number_hi';
312
313 END IF;
314 END IF;
315
316 IF p_order_date_lo IS NOT NULL OR p_order_date_hi IS NOT NULL THEN
317 IF p_order_date_lo IS NULL THEN
318 v_select_statement := v_select_statement||' AND h.ordered_date <= :p_order_date_hi';
319
320 ELSIF p_order_date_hi IS NULL THEN
321 v_select_statement := v_select_statement||' AND h.ordered_date >= :p_order_date_lo';
322
323 ELSE
324 v_select_statement := v_select_statement||' AND h.ordered_date BETWEEN :p_order_date_lo AND :p_order_date_hi';
325
326 END IF;
327 END IF;
328
329 IF p_schedule_date_lo IS NOT NULL OR p_schedule_date_hi IS NOT NULL THEN
330 IF p_schedule_date_lo IS NULL THEN
331 v_select_statement := v_select_statement||' AND l.schedule_ship_date <= :p_schedule_date_hi';
332
333 ELSIF p_schedule_date_hi IS NULL THEN
334 v_select_statement := v_select_statement||' AND l.schedule_ship_date >= :p_schedule_date_lo';
335
336 ELSE
337 v_select_statement := v_select_statement||' AND l.schedule_ship_date BETWEEN :p_schedule_date_lo AND :p_schedule_date_hi';
338
339 END IF;
340 END IF;
341
342 IF p_promise_date_lo IS NOT NULL OR p_promise_date_hi IS NOT NULL THEN
343 IF p_promise_date_lo IS NULL THEN
344 v_select_statement := v_select_statement||' AND l.promise_date <= :p_promise_date_hi';
345
346 ELSIF p_promise_date_hi IS NULL THEN
347 v_select_statement := v_select_statement||' AND l.promise_date >= :p_promise_date_lo';
348
349 ELSE
350 v_select_statement := v_select_statement||' AND l.promise_date BETWEEN :p_promise_date_lo AND :p_promise_date_hi';
351
352 END IF;
353 END IF;
354
355 IF p_request_date_lo IS NOT NULL OR p_request_date_hi IS NOT NULL THEN
356 IF p_request_date_lo IS NULL THEN
357 v_select_statement := v_select_statement||' AND l.request_date <= :p_request_date_hi';
358
359 ELSIF p_request_date_hi IS NULL THEN
360 v_select_statement := v_select_statement||' AND l.request_date >= :p_request_date_lo';
361
362 ELSE
363 v_select_statement := v_select_statement||' AND l.request_date BETWEEN :p_request_date_lo AND :p_request_date_hi';
364
365 END IF;
366 END IF;
367
368
369 IF p_bill_to_customer_name_lo IS NOT NULL OR p_bill_to_customer_name_hi IS NOT
370 NULL THEN
371
372 IF p_bill_to_customer_name_lo IS NULL THEN
373 v_select_statement := v_select_statement||' AND bill_p.party_name <= :p_bill_to_customer_name_hi';
374
375 ELSIF p_bill_to_customer_name_hi IS NULL THEN
376 v_select_statement := v_select_statement||' AND bill_p.party_name >= :p_bill_to_customer_name_lo';
377
378 ELSE
379 v_select_statement := v_select_statement||' AND bill_p.party_name BETWEEN :p_bill_to_customer_name_lo AND :p_bill_to_customer_name_hi';
380
381 END IF;
382 END IF;
383
384 IF p_ship_to_customer_name_lo IS NOT NULL OR p_ship_to_customer_name_hi IS NOT
385 NULL THEN
386 IF p_ship_to_customer_name_lo IS NULL THEN
387 v_select_statement := v_select_statement||' AND ship_p.party_name <= :p_ship_to_customer_name_hi';
388
389 ELSIF p_ship_to_customer_name_hi IS NULL THEN
390 v_select_statement := v_select_statement||' AND ship_p.party_name >= :p_ship_to_customer_name_lo';
391
392 ELSE
393 v_select_statement := v_select_statement||' AND ship_p.party_name BETWEEN :p_ship_to_customer_name_lo AND :p_ship_to_customer_name_hi';
394
395 END IF;
396 END IF;
397
398
399 IF p_del_to_customer_name_lo IS NOT NULL OR p_del_to_customer_name_hi IS NOT NULL THEN
400 IF p_del_to_customer_name_lo IS NULL THEN
404 v_select_statement := v_select_statement||' AND del_p.party_name >= :p_del_to_customer_name_lo';
401 v_select_statement := v_select_statement||' AND del_p.party_name <= :p_del_to_customer_name_hi';
402
403 ELSIF p_del_to_customer_name_hi IS NULL THEN
405
406 ELSE
407 v_select_statement := v_select_statement||' AND del_p.party_name BETWEEN :p_del_to_customer_name_lo AND :p_del_to_customer_name_hi';
408 END IF;
409 END IF;
410
411 IF p_order_type IS NOT NULL THEN
412 v_select_statement := v_select_statement||' AND h.order_type_id = :p_order_type';
413 END IF;
414
415 IF p_booked_status IS NOT NULL THEN
416 IF substr(upper(p_booked_status),1,1) = 'Y' THEN
417 v_select_statement := v_select_statement || ' AND h.booked_flag = ''Y'''; ELSE
418 v_select_statement := v_select_statement || ' AND h.booked_flag = ''N''';
419 END IF;
420 END IF;
421
422 IF p_open_flag IS NOT NULL THEN
423 IF substr(upper(p_open_flag),1,1) = 'Y' THEN
424 v_select_statement := v_select_statement || ' AND h.open_flag = ''Y'''; ELSE
425 v_select_statement := v_select_statement || ' AND h.open_flag = ''N''';
426 END IF;
427 END IF;
428
429 IF p_salesrep IS NOT NULL THEN
430 v_select_statement := v_select_statement||' AND sr.name = :p_salesrep';
431 END IF;
432
433 IF p_created_by IS NOT NULL THEN
434 v_select_statement := v_select_statement||' AND u.user_name = :p_created_by';
435 END IF;
436
437 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
438
439 DBMS_SQL.PARSE(l_cursor_id, v_select_statement, DBMS_SQL.V7);
440
441 IF p_order_number_lo IS NOT NULL THEN
442 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_order_number_lo', p_order_number_lo);
443 END IF;
444
445 IF p_order_number_hi IS NOT NULL THEN
446 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_order_number_hi', p_order_number_hi);
447 END IF;
448
449 IF p_bill_to_customer_name_lo IS NOT NULL THEN
450 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_bill_to_customer_name_lo',
451 p_bill_to_customer_name_lo);
452 END IF;
453
454 IF p_bill_to_customer_name_hi IS NOT NULL THEN
455 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_bill_to_customer_name_hi',
456 p_bill_to_customer_name_hi);
457 END IF;
458
459 IF p_ship_to_customer_name_lo IS NOT NULL THEN
460 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_ship_to_customer_name_lo',
461 p_ship_to_customer_name_lo);
462 END IF;
463
464 IF p_ship_to_customer_name_hi IS NOT NULL THEN
465 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_ship_to_customer_name_hi',
466 p_ship_to_customer_name_hi);
467 END IF;
468
469 IF p_del_to_customer_name_lo IS NOT NULL THEN
470 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_del_to_customer_name_lo',
471 p_del_to_customer_name_lo);
472 END IF;
473
474 IF p_del_to_customer_name_hi IS NOT NULL THEN
475 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_del_to_customer_name_hi',
476 p_del_to_customer_name_hi);
477 END IF;
478
479 IF p_order_date_lo IS NOT NULL THEN
480 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_order_date_lo',
481 p_order_date_lo);
482 END IF;
483
484 IF p_order_date_hi IS NOT NULL THEN
485 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_order_date_hi',
486 p_order_date_hi);
487 END IF;
488
489 IF p_schedule_date_lo IS NOT NULL THEN
490 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_schedule_date_lo',
491 p_schedule_date_lo);
492 END IF;
493
494 IF p_schedule_date_hi IS NOT NULL THEN
495 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_schedule_date_hi',
496 p_schedule_date_hi);
497 END IF;
498
499 IF p_request_date_lo IS NOT NULL THEN
500 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_request_date_lo',
501 p_request_date_lo);
502 END IF;
503
504 IF p_request_date_hi IS NOT NULL THEN
505 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_request_date_hi',
506 p_request_date_hi);
507 END IF;
508
509 IF p_promise_date_lo IS NOT NULL THEN
510 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_promise_date_lo',
511 p_promise_date_lo);
512 END IF;
513
514 IF p_promise_date_hi IS NOT NULL THEN
515 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_promise_date_hi',
516 p_promise_date_hi);
517 END IF;
518
519 IF p_order_type IS NOT NULL THEN
520 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_order_type',
521 p_order_type);
522 END IF;
523
524 IF p_salesrep IS NOT NULL THEN
525 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_salesrep',
526 p_salesrep);
527 END IF;
528
529 IF p_created_by IS NOT NULL THEN
530 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_created_by',
531 p_created_by);
532 END IF;
533
534 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_lang,30);
535
536 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
537
538 LOOP
539 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
540 EXIT;
541 END IF;
542 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_lang);
543
544 IF (l_lang IS NOT NULL) THEN
545 IF (l_lang_str IS NULL) THEN
546 l_lang_str := l_lang;
547 ELSE
548 l_lang_str := l_lang_str||','||l_lang;
549 END IF;
550 END IF;
551 /* IF (l_lang_str IS NULL) THEN
552 l_lang_str := l_base_lang;
553 ELSE
554 IF instr(l_lang_str, l_lang) = 0 THEN
555 l_lang_str := l_lang_str||','||l_base_lang;
556 END IF;
557 END IF;*/
558
559 END LOOP;
560
561 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
562 IF (l_lang_str IS NULL) THEN
563 l_lang_str := l_base_lang;
564 ELSE
565 IF instr(l_lang_str, l_lang) = 0 THEN
566 l_lang_str := l_lang_str||','||l_base_lang;
567 END IF;
568 END IF;
569 return (l_lang_str);
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
574 RAISE;
575 END GET_LANG;
576
577 END OM_REPORTS_MLS_LANG;
[Home] [Help]