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