1 PACKAGE BODY WSH_REPORTS_MLS_LANG AS
2 /* $Header: WSHLANGB.pls 115.6 99/07/16 08:19:11 porting sh $ */
3
4 FUNCTION GET_LANG RETURN VARCHAR2 IS
5 l_CursorID INTEGER;
6 v_SelectStmt VARCHAR2(800);
7 v_departure_id NUMBER;
8 v_departure_date_lo DATE;
9 v_departure_date_hi DATE;
10 v_freight_carrier VARCHAR2(25);
11 v_delivery_id NUMBER;
12 v_warehouse_id NUMBER;
13 l_lang VARCHAR2(30);
14 l_base_lang VARCHAR2(30);
15 l_dummy INTEGER;
16 l_lang_str VARCHAR2(500);
17 BEGIN
18 -- The external docs have parameters which are in the same positions
19 -- Therefore, fnd_request_info.get_parameter is used to get the parameter
20 -- values based on positions without using IF statements to check for
21 -- the program short name first
22 v_departure_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(1));
23 v_departure_date_lo := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(2));
24 v_departure_date_hi := FND_DATE.CANONICAL_TO_DATE(FND_REQUEST_INFO.GET_PARAMETER(3));
25 v_freight_carrier := FND_REQUEST_INFO.GET_PARAMETER(4);
26 v_delivery_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(5));
27 v_warehouse_id := to_number(FND_REQUEST_INFO.GET_PARAMETER(8));
28
29 -- Get base language
30 SELECT language_code INTO l_base_lang FROM fnd_languages
31 WHERE installed_flag = 'B';
32
33 -- Create a query string to get languages based on the parameters
34 v_SelectStmt := 'SELECT DISTINCT a.language
35 FROM ra_addresses a,
36 wsh_departures dp,
37 wsh_deliveries dl,
38 ra_site_uses_all su
39 WHERE a.address_id = su.address_id
40 AND su.site_use_id = dl.ultimate_ship_to_id
41 AND dl.actual_departure_id = dp.departure_id
42 AND dp.status_code = ''CL''
43 AND dp.organization_id = :v_warehouse_id';
44
45 -- add to where clause if other parameters are specified
46 IF v_departure_id IS NOT NULL THEN
47 v_SelectStmt := v_SelectStmt||' AND dp.departure_id = :v_departure_id';
48 END IF;
49
50 IF v_delivery_id IS NOT NULL THEN
51 v_SelectStmt := v_SelectStmt||' AND dl.delivery_id = :v_delivery_id';
52 END IF;
53
54 IF v_freight_carrier IS NOT NULL THEN
55 v_SelectStmt := v_SelectStmt||' AND dp.freight_carrier_code = :v_freight_carrier';
56 END IF;
57
58 IF v_departure_date_lo IS NOT NULL OR v_departure_date_hi IS NOT NULL
59 THEN
60 IF v_departure_date_lo IS NULL THEN
61 v_SelectStmt := v_SelectStmt||' AND trunc(dp.actual_departure_date) <= :v_departure_date_hi';
62 ELSIF v_departure_date_hi IS NULL THEN
63 v_SelectStmt := v_SelectStmt||' AND trunc(dp.actual_departure_date) >= :v_departure_date_lo';
64 ELSE
65 v_SelectStmt := v_SelectStmt||' AND trunc(dp.actual_departure_date) BETWEEN :v_departure_date_lo AND :v_departure_date_hi';
66 END IF;
67 END IF;
68
69 -- Open the cursor for processing
70 l_CursorID := DBMS_SQL.OPEN_CURSOR;
71
72 -- Parse the query
73 DBMS_SQL.PARSE(l_CursorID, v_SelectStmt, DBMS_SQL.V7);
74
75 -- Bind input variables
76 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_warehouse_id',v_warehouse_id);
77 IF v_departure_id IS NOT NULL THEN
78 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_departure_id',v_departure_id);
79 END IF;
80 IF v_departure_date_lo IS NOT NULL THEN
81 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_departure_date_lo',v_departure_date_lo);
82 END IF;
83 IF v_departure_date_hi IS NOT NULL THEN
84 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_departure_date_hi',v_departure_date_hi);
85 END IF;
86 IF v_freight_carrier IS NOT NULL THEN
87 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_freight_carrier',v_freight_carrier);
88 END IF;
89 IF v_delivery_id IS NOT NULL THEN
90 DBMS_SQL.BIND_VARIABLE(l_CursorID,':v_delivery_id',v_delivery_id);
91 END IF;
92
93 -- Define the output variable
94 DBMS_SQL.DEFINE_COLUMN(l_CursorID,1,l_lang,30);
95
96 -- Execute the query
97 l_dummy := DBMS_SQL.EXECUTE(l_CursorID);
98
99 -- Create string of languages to be returned
100 LOOP
101 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
102 EXIT;
103 END IF;
104
105 -- Fetch language into variable
106 DBMS_SQL.COLUMN_VALUE(l_CursorID,1,l_lang);
107
108 IF (l_lang IS NOT NULL) THEN
109 IF (l_lang_str IS NULL) THEN
110 l_lang_str := l_lang;
111 ELSE
112 l_lang_str := l_lang_str||','||l_lang;
113 END IF;
114 ELSE
115 IF (l_lang_str IS NULL) THEN
116 -- Use base language if none is specified
117 l_lang_str := l_base_lang;
118 ELSE
119 -- Make sure base language is not already in string
120 IF instr(l_lang_str,l_base_lang) = 0 THEN
121 l_lang_str := l_lang_str||','||l_base_lang;
122 END IF;
123 END IF;
124 END IF;
125 END LOOP;
126
127 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
128
129 IF (l_lang_str IS NULL) THEN
130 -- Function must not return an empty string
131 l_lang_str := l_base_lang;
132 END IF;
133
134 RETURN (l_lang_str);
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
139 RAISE;
140 END GET_LANG;
141 END WSH_REPORTS_MLS_LANG;
[Home] [Help]