1 PACKAGE BODY WSH_EXTREPS_MLS_LANG AS
2 /* $Header: WSHMLSLB.pls 120.1.12000000.4 2007/06/14 07:00:06 jnpinto ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_EXTREPS_MLS_LANG';
6 --
7
8 /* This local function is created so that the call is common from
9 Function Get_Lang and Procedure Get_NLS_Lang
10 This function takes the input Parameter Record and the Concurrent
11 Program Name and returns the String of Languages for which the
12 Concurrent Program Needs to be run
13 */
14 FUNCTION GET_LANG_STRING (
15 p_prog_name IN VARCHAR2,
16 p_doc_param_info IN WSH_DOCUMENT_SETS.document_set_rec_type
17 )
18 RETURN VARCHAR2 IS
19
20 l_CursorID INTEGER;
21 v_SelectStmt VARCHAR2(3000);
22 l_lang VARCHAR2(30);
23 l_base_lang VARCHAR2(30);
24 l_dummy INTEGER;
25 l_lang_str VARCHAR2(500) := NULL;
26 v_FROM VARCHAR2(500); -- 4497301
27
28 --
29 l_debug_on BOOLEAN;
30 --
31 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LANG_STRING';
32 --
33
34 BEGIN
35
36 --
37 -- Debug Statements
38 --
39 --
40 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
41 --
42 IF l_debug_on IS NULL
43 THEN
44 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
45 END IF;
46 --
47 IF l_debug_on THEN
48 WSH_DEBUG_SV.push(l_module_name);
49 END IF;
50 --
51
52 -- Get base language
53 SELECT language_code INTO l_base_lang FROM fnd_languages
54 WHERE installed_flag = 'B';
55
56 -- 4497301 : creating the v_FROM depending on whether a Trip is there or not
57 -- 4248303 : Making use of hz_locations for language field.
58
59 IF ( (p_doc_param_info.p_trip_id IS NOT NULL) OR
60 (p_doc_param_info.p_trip_stop_id IS NOT NULL) OR
61 (p_doc_param_info.p_departure_date_low IS NOT NULL or
62 p_doc_param_info.p_departure_date_high IS NOT NULL) OR
63 (p_prog_name = 'WSHRDBOL' AND p_doc_param_info.p_delivery_leg_id is NOT NULL))
64 THEN
65 v_FROM := 'FROM hz_locations loc,' ||
66 ' wsh_new_deliveries wnd,' ||
67 ' wsh_delivery_legs wdl,' ||
68 ' wsh_delivery_assignments_v wda ' ||
69 ' WHERE wdl.delivery_id = wnd.delivery_id ';
70 ELSE
71 v_FROM := 'FROM hz_locations loc,' ||
72 ' wsh_new_deliveries wnd,' ||
73 ' wsh_delivery_assignments_v wda ' ||
74 ' WHERE 1 = 1 ';
75 END IF;
76
77 -- 4497301 : Commented the follwoing SELECT stmt.Re written the same below the commented code.
78 -- Create a query string to get languages based on the parameters
79 /* v_SelectStmt := 'SELECT DISTINCT hcas.language ' ||
80 'FROM hz_cust_acct_sites_all hcas,' ||
81 ' hz_party_sites hps,'||
82 ' wsh_new_deliveries wnd,' ||
83 ' wsh_delivery_legs wdl,' ||
84 ' wsh_delivery_assignments_v wda, ' ||
85 ' wsh_locations wl ' ||
86 'WHERE wdl.delivery_id(+) = wnd.delivery_id ' ||
87 'AND wnd.delivery_id = wda.delivery_id '||
88 'AND wl.wsh_location_id = wnd.ultimate_dropoff_location_id ' ||
89 'AND wl.location_source_code = ''HZ'' ' ||
90 'AND hps.location_id = wl.source_location_id ' ||
91 'AND hcas.party_site_id = hps.party_site_id ' ||
92 'AND nvl(wnd.SHIPMENT_DIRECTION , ''O'') IN (''O'', ''IO'') ' ; -- J inbound logistics jckwok*/
93
94 -- 4497301 : Create a query string to get languages based on the parameters
95 -- 4248303 : Making use of hz_locations.language.
96 v_SelectStmt := 'SELECT DISTINCT loc.language ' ||
97 v_FROM ||
98 'AND loc.location_id = wnd.ultimate_dropoff_location_id ' ||
99 'AND wnd.delivery_id = wda.delivery_id '||
100 'AND nvl(wnd.SHIPMENT_DIRECTION , ''O'') IN (''O'', ''IO'') ' ; -- J inbound logistics jckwok
101
102 IF p_doc_param_info.p_organization_id IS NOT NULL THEN
103 v_SelectStmt := v_SelectStmt|| ' AND wnd.organization_id = :p_organization_id ';
104 END IF;
105
106 IF p_prog_name <> 'WSHRDPAK' THEN
107 v_SelectStmt := v_SelectStmt||
108 ' AND wda.delivery_detail_id IS NOT NULL';
109 END IF;
110
111 -- add to where clause if other parameters are specified
112 IF p_doc_param_info.p_trip_id IS NOT NULL THEN
113 v_SelectStmt := v_SelectStmt||' AND wdl.delivery_leg_id in '||
114 '(select distinct delivery_leg_id from wsh_delivery_legs '||
115 'where pick_up_stop_id in (select stop_id from '||
116 'wsh_trip_stops where trip_id = :p_trip_id))';
117 END IF;
118
119 IF p_doc_param_info.p_trip_stop_id IS NOT NULL THEN
120 v_SelectStmt := v_SelectStmt||' AND wdl.pick_up_stop_id = :p_trip_stop_id';
121 END IF;
122
123 -- 5723547 : Removed Connect By clause in the Dynamic SQL
124 IF p_doc_param_info.p_delivery_id IS NOT NULL THEN
125 --Bug 6074735, Removed IF p_prog_name = 'WSHRDPAK' condition
126 v_SelectStmt := v_SelectStmt||' AND wnd.delivery_id = :p_delivery_id';
127 END IF;
128
129 IF p_doc_param_info.p_freight_carrier IS NOT NULL AND p_prog_name <> 'WSHRDPAK' THEN
130 -- bug 1562990: wsh_new_deliveries doesn't have freight_carrier_code
131 -- Since the value set WSH_SRS_FREIGHT_CARRIERS actually look at
132 -- Ship Method, using ship_method_code will fix this bug.
133 v_SelectStmt := v_SelectStmt||' AND wnd.ship_method_code = :p_freight_carrier';
134 END IF;
135
136 IF p_doc_param_info.p_departure_date_low IS NOT NULL OR p_doc_param_info.p_departure_date_high IS NOT NULL
137 THEN
138 IF p_doc_param_info.p_departure_date_low IS NULL THEN
139 v_SelectStmt := v_SelectStmt||' AND wdl.delivery_leg_id IN '||
140 '(select distinct delivery_leg_id from wsh_delivery_legs '||
141 'where pick_up_stop_id in (select stop_id from '||
142 'wsh_trip_stops where planned_departure_date '||
143 '<= :p_departure_date_high))'; -- bug 1566422
144 ELSIF p_doc_param_info.p_departure_date_high IS NULL THEN
145 v_SelectStmt := v_SelectStmt||' AND wdl.delivery_leg_id IN '||
146 '(select distinct delivery_leg_id from wsh_delivery_legs '||
147 'where pick_up_stop_id in (select stop_id from '||
148 'wsh_trip_stops where planned_departure_date '||
149 '>= :p_departure_date_low))'; -- bug 1566422
150 ELSE
151 v_SelectStmt := v_SelectStmt||' AND wdl.delivery_leg_id in '||
152 '(select distinct delivery_leg_id from wsh_delivery_legs '||
153 'where pick_up_stop_id in (select stop_id from '||
154 'wsh_trip_stops where planned_departure_date '||
155 'BETWEEN :p_departure_date_low AND :p_departure_date_high))'; -- bug 1566422
156 END IF;
157 END IF;
158
159 IF p_prog_name = 'WSHRDPAK' AND ( p_doc_param_info.p_delivery_date_low IS NOT NULL OR
160 p_doc_param_info.p_delivery_date_high IS NOT NULL ) THEN
161 IF p_doc_param_info.p_delivery_date_low IS NULL THEN
162 v_SelectStmt := v_SelectStmt||' AND nvl(wnd.confirm_date,sysdate) <= p_doc_param_info.p_delivery_date_high '; ELSIF p_doc_param_info.p_delivery_date_high IS NULL THEN
163 v_SelectStmt := v_SelectStmt||' AND nvl(wnd.confirm_date,sysdate) >= p_doc_param_info.p_delivery_date_low ';
164 ELSE
165 v_SelectStmt := v_SelectStmt||' AND nvl(wnd.confirm_date,sysdate) '||
166 'between :p_delivery_date_low AND :p_delivery_date_high ';
167 END IF;
168 END IF;
169
170 -- Bug: 1520197, Done only for Bill Of Lading Report (WSHRDBOL)
171 IF (p_prog_name = 'WSHRDBOL' AND p_doc_param_info.p_delivery_leg_id is NOT NULL) THEN
172 v_SelectStmt := v_SelectStmt||' AND wdl.delivery_leg_id = :p_delivery_leg_id';
173 END IF;
174
175 -- Open the cursor for processing
176 l_CursorID := DBMS_SQL.OPEN_CURSOR;
177
178 -- Parse the query
179 DBMS_SQL.PARSE(l_CursorID, v_SelectStmt, DBMS_SQL.V7);
180
181 -- Bind input variables
182 IF p_doc_param_info.p_organization_id IS NOT NULL THEN
183 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_organization_id',p_doc_param_info.p_organization_id);
184 END IF;
185 IF p_doc_param_info.p_trip_id IS NOT NULL THEN
186 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_trip_id',p_doc_param_info.p_trip_id);
187 END IF;
188 IF p_doc_param_info.p_trip_stop_id IS NOT NULL THEN
189 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_trip_stop_id',p_doc_param_info.p_trip_stop_id);
190 END IF;
191 IF p_doc_param_info.p_departure_date_low IS NOT NULL THEN
192 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_departure_date_low',p_doc_param_info.p_departure_date_low);
193 END IF;
194 IF p_doc_param_info.p_departure_date_high IS NOT NULL THEN
195 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_departure_date_high',p_doc_param_info.p_departure_date_high);
196 END IF;
197 IF p_doc_param_info.p_freight_carrier IS NOT NULL AND p_prog_name <> 'WSHRDPAK' THEN
198 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_freight_carrier',p_doc_param_info.p_freight_carrier);
199 END IF;
200 IF p_doc_param_info.p_delivery_id IS NOT NULL THEN
201 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_delivery_id',p_doc_param_info.p_delivery_id);
202 END IF;
203 IF p_prog_name = 'WSHRDPAK' THEN
204 IF p_doc_param_info.p_delivery_date_low IS NOT NULL THEN
205 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_delivery_date_low',p_doc_param_info.p_delivery_date_low);
206 END IF;
207 IF p_doc_param_info.p_delivery_date_high IS NOT NULL THEN
208 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_delivery_date_high',p_doc_param_info.p_delivery_date_high);
209 END IF;
210 END IF;
211 -- Bug: 1520197
212 IF ( p_prog_name = 'WSHRDBOL' AND p_doc_param_info.p_delivery_leg_id IS NOT NULL ) THEN
213 DBMS_SQL.BIND_VARIABLE(l_CursorID,':p_delivery_leg_id',p_doc_param_info.p_delivery_leg_id);
214 END IF;
215
216 -- Define the output variable
217 DBMS_SQL.DEFINE_COLUMN(l_CursorID,1,l_lang,30);
218 -- Execute the query
219 l_dummy := DBMS_SQL.EXECUTE(l_CursorID);
220
221 -- Create string of languages to be returned
222 LOOP
223 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
224 EXIT;
225 END IF;
226
227 -- Fetch language into variable
228 DBMS_SQL.COLUMN_VALUE(l_CursorID,1,l_lang);
229
230 IF (l_lang IS NOT NULL) THEN
231 IF (l_lang_str IS NULL) THEN
232 l_lang_str := l_lang;
233 ELSE
234 l_lang_str := l_lang_str||','||l_lang;
235 END IF;
236 ELSE
237 IF (l_lang_str IS NULL) THEN
238 -- Use base language if none is specified
239 l_lang_str := l_base_lang;
240 ELSE
241 -- Make sure base language is not already in string
242 IF instr(l_lang_str,l_base_lang) = 0 THEN
243 l_lang_str := l_lang_str||','||l_base_lang;
244 END IF;
245 END IF;
246 END IF;
247 END LOOP;
248
249 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
250
251 IF (l_lang_str IS NULL) THEN
252 -- Function must not return an empty string
253 l_lang_str := l_base_lang;
254 END IF;
255
256 --
257 -- Debug Statements
258 --
259 IF l_debug_on THEN
260 WSH_DEBUG_SV.pop(l_module_name);
261 END IF;
262 --
263
264 RETURN (l_lang_str);
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
269 --
270 -- Debug Statements
271 --
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
274 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
275 END IF;
276 --
277 RAISE;
278
279 END GET_LANG_STRING;
280
281
282 FUNCTION GET_LANG RETURN VARCHAR2 IS
283
284 l_doc_param_info WSH_DOCUMENT_SETS.document_set_rec_type;
285
286 ret_val NUMBER;
287 l_parm_num NUMBER;
288 l_lang_str VARCHAR2(500) := NULL;
289 l_prog_app_name VARCHAR2(30);
290 l_prog_name VARCHAR2(30);
291 --
292 l_debug_on BOOLEAN;
293 --
294 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LANG';
295 --
296 BEGIN
297 -- PROGRAM NAME
298 --
299 -- Debug Statements
300 --
301 --
302 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
303 --
304 IF l_debug_on IS NULL
305 THEN
306 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
307 END IF;
308 --
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.push(l_module_name);
311 END IF;
312 --
313 FND_REQUEST_INFO.GET_PROGRAM(l_prog_name, l_prog_app_name);
314
315 -- TRIP ID
316 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Trip Name', l_parm_num);
317 IF (ret_val = -1) THEN
318 l_doc_param_info.p_trip_id := NULL;
319 ELSE
320 l_doc_param_info.p_trip_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
321 END IF;
322
323 -- STOP ID
324 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Trip Stop', l_parm_num);
325 IF (ret_val = -1) THEN
326 l_doc_param_info.p_trip_stop_id := NULL;
327 ELSE
328 l_doc_param_info.p_trip_stop_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
329 END IF;
330
331 -- DEPARTURE DATE LOW
332 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Departure Date (Low)', l_parm_num);
333 IF (ret_val = -1) THEN
334 l_doc_param_info.p_departure_date_low := NULL;
335 ELSE
336 l_doc_param_info.p_departure_date_low := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
337 END IF;
338
339 -- DEPARTURE DATE HIGH
340 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Departure Date (High)', l_parm_num);
341 IF (ret_val = -1) THEN
342 l_doc_param_info.p_departure_date_high := NULL;
343 ELSE
344 l_doc_param_info.p_departure_date_high := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
345 END IF;
346
347 -- DELIVERY DATE LOW
348 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Delivery Date (Low)', l_parm_num);
349 IF (ret_val = -1) THEN
350 l_doc_param_info.p_delivery_date_low := NULL;
351 ELSE
352 l_doc_param_info.p_delivery_date_low := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num)); END IF;
353
354 -- DELIVERY DATE HIGH
355 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Delivery Date (High)', l_parm_num);
356 IF (ret_val = -1) THEN
357 l_doc_param_info.p_delivery_date_high := NULL;
358 ELSE
359 l_doc_param_info.p_delivery_date_high := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
360 END IF;
361
362 -- FREIGHT CARRIER
363 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Freight Carrier', l_parm_num);
364 IF (ret_val = -1) THEN
365 l_doc_param_info.p_freight_carrier := NULL;
366 ELSE
367 l_doc_param_info.p_freight_carrier := FND_REQUEST_INFO.GET_PARAMETER(l_parm_num);
368 END IF;
369
370 -- DELIVERY
371 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Delivery Name', l_parm_num);
372 IF (ret_val = -1) THEN
373 l_doc_param_info.p_delivery_id := NULL;
374 ELSE
375 l_doc_param_info.p_delivery_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
376 END IF;
377
378 -- ORGANIZATION
379 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Warehouse', l_parm_num);
380 IF (ret_val = -1) THEN
381 l_doc_param_info.p_organization_id := NULL;
382 ELSE
383 l_doc_param_info.p_organization_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
384 END IF;
385
386 -- DELIVERY LEG ID Bug: 1520197
387 ret_val := FND_REQUEST_INFO.GET_PARAM_NUMBER('Bill of Lading Number', l_parm_num);
388 IF (ret_val = -1) THEN
389 l_doc_param_info.p_delivery_leg_id := NULL;
390 ELSE
391 l_doc_param_info.p_delivery_leg_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(l_parm_num));
392 END IF;
393
394 -- Calling Get_Lang_String
395 IF l_debug_on THEN
396 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Get_Lang_String ');
397 END IF;
398
399 l_lang_str := Get_Lang_String ( p_prog_name => l_prog_name, p_doc_param_info => l_doc_param_info );
400
401 -- Debug Statements
402 --
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.pop(l_module_name);
405 END IF;
406 --
407 RETURN (l_lang_str);
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 --
412 -- Debug Statements
413 --
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
416 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
417 END IF;
418 --
419 RAISE;
420 END GET_LANG;
421
422
423 /* This procedure is called from WSH_DOCUMENT_SETS.Print_Document_Set to get the
424 NLS languages for which the concurrent report needs to be run, if it is
425 MLS Compliant (MLS function is defined for that report)
426 */
427
428 PROCEDURE GET_NLS_LANG (
429 p_prog_name IN VARCHAR2,
430 p_doc_param_info IN WSH_DOCUMENT_SETS.document_set_rec_type,
431 p_nls_comp IN VARCHAR2,
432 x_nls_lang OUT NOCOPY lang_tab_type,
433 x_return_status OUT NOCOPY VARCHAR2
434 )
435 IS
436
437 l_lang_str VARCHAR2(500) := NULL;
438
439 p_lcount NUMBER;
440 endloc NUMBER;
441 startloc NUMBER;
442
443 --
444 l_debug_on BOOLEAN;
445 --
446 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_NLS_LANG';
447 --
448 BEGIN
449 -- PROGRAM NAME
450 --
451 -- Debug Statements
452 --
453 --
454 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
455 --
456 IF l_debug_on IS NULL
457 THEN
458 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
459 END IF;
460 --
461 IF l_debug_on THEN
462 WSH_DEBUG_SV.push(l_module_name);
463 END IF;
464 --
465
466 x_return_status := fnd_api.g_ret_sts_success;
467
468 -- Call Get_Lang_String to get the Language String
469 IF l_debug_on THEN
470 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Get_Lang_String ');
471 END IF;
472
473 l_lang_str := Get_Lang_String ( p_prog_name => p_prog_name, p_doc_param_info => p_doc_param_info );
474
475 /* Parse p_lang_str to get nls_languages */
476 startloc := 1;
477 endloc := 1;
478 p_lcount := 0;
479
480 /* Parse p_lang_str to get nls_languages */
481 startloc := 1;
482 endloc := 1;
483 p_lcount := 0;
484
485 if (l_lang_str is null ) then
486 -- Return error
487 x_return_status := fnd_api.g_ret_sts_error;
488 IF l_debug_on THEN
489 WSH_DEBUG_SV.logmsg(l_module_name,'Get_Lang_String returns NULL');
490 END IF;
491 raise no_data_found ;
492 end if;
493
494 if ( l_lang_str is not null ) then
495 loop
496 endloc := instr( l_lang_str, ',', startloc );
497 p_lcount := p_lcount + 1;
498 if ( endloc = 0 ) then
499 x_nls_lang(p_lcount).lang_code := LTRIM(RTRIM( substr( l_lang_str, startloc,
500 length(l_lang_str) -
501 startloc + 1
502 )
503 )
504 );
505 exit;
506 else
507 x_nls_lang(p_lcount).lang_code := LTRIM(RTRIM( substr( l_lang_str, startloc,
508 endloc - startloc
509 )
510 )
511 );
512 end if;
513 startloc := endloc + 1;
514 end loop;
515 end if;
516
517 /* get nls_language and nls_territory for each language_code */
518 for i in 1..p_lcount loop
519 /* if program is nls_compliant then use the default territory from fnd_languages,
520 otherwise use user environment */
521 if ( p_nls_comp = 'Y' ) then
522 begin
523 select nls_language, nls_territory
524 into x_nls_lang(i).nls_language, x_nls_lang(i).nls_territory
525 from fnd_languages
526 where language_code = x_nls_lang(i).lang_code;
527 exception
528 when no_data_found then
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.logmsg(l_module_name,'No data found in fnd_languages for :'|| x_nls_lang(i).lang_code);
531 END IF;
532 raise ;
533 end;
534 else
535 /* use territory from the user environment which is parent_id's nls_territory */
536 begin
537 select nls_language
538 into x_nls_lang(i).nls_language
539 from fnd_languages
540 where language_code = x_nls_lang(i).lang_code;
541 exception
542 when no_data_found then
543 IF l_debug_on THEN
544 WSH_DEBUG_SV.logmsg(l_module_name,'No data found in fnd_languages for :'|| x_nls_lang(i).lang_code);
545 END IF;
546 raise ;
547 end;
548
549 x_nls_lang(i).nls_territory := fnd_request_info.get_territory;
550
551 end if;
552 end loop;
553
554 -- Debug Statements
555 --
556 IF l_debug_on THEN
557 WSH_DEBUG_SV.pop(l_module_name);
558 END IF;
559 --
560
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 x_return_status := fnd_api.g_ret_sts_error;
564 wsh_util_core.add_message(x_return_status);
565 -- Debug Statements
566 --
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured.');
569 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
570 END IF;
571 --
572
573 WHEN OTHERS THEN
574 wsh_util_core.default_handler('WSH_EXTREPS_MLS_LANG.GET_NLS_LANG');
575 x_return_status := fnd_api.g_ret_sts_unexp_error;
576 --
577 -- Debug Statements
578 --
579 IF l_debug_on THEN
580 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
581 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
582 END IF;
583 --
584 END GET_NLS_LANG;
585
586
587 END WSH_EXTREPS_MLS_LANG;
[Home] [Help]