[Home] [Help]
PACKAGE BODY: APPS.GME_GANTT_LOV_PKG
Source
1 PACKAGE BODY gme_gantt_lov_pkg AS
2 /* $Header: GMEGNTLB.pls 120.4 2006/07/10 16:01:01 svgonugu noship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_GANTT_LOV_PKG';
4
5 /**
6 * Select the appropriate batches and send them one by one to the gantt
7 */
8 PROCEDURE select_batches_lov (
9 p_input_field IN VARCHAR2
10 ,p_current_start_row IN INTEGER
11 ,p_num_row_to_get IN INTEGER
12 ,p_pending_status IN VARCHAR2
13 ,p_released_status IN VARCHAR2
14 ,p_certified_status IN VARCHAR2
15 ,p_organization_id IN INTEGER
16 ,p_batch_type IN INTEGER
17 ,p_fpo_type IN INTEGER
18 ,p_from_date IN DATE
19 ,p_to_date IN DATE
20 ,x_total_rows OUT NOCOPY NUMBER
21 ,x_batch_lov_tbl OUT NOCOPY batchlovdetailtabletype)
22 IS
23 CURSOR batch_lov_from_and_to_date
24 IS
25 SELECT DISTINCT b.batch_id, batch_no, b.organization_id
26 ,batch_status, itd.concatenated_segments
27 ,itd.description
28 FROM gme_batch_header b
29 ,gme_material_details d
30 ,mtl_system_items_vl itd
31 WHERE b.organization_id = p_organization_id
32 AND UPPER (batch_no) LIKE p_input_field
33 AND b.batch_id = d.batch_id
34 AND ( ( batch_type = p_batch_type
35 AND ( batch_status = p_pending_status
36 OR batch_status = p_released_status
37 OR batch_status = p_certified_status) )
38 OR (batch_type = p_fpo_type AND batch_status = 1) )
39 AND ( ( batch_status = 1
40 AND ( ( plan_start_date >= p_from_date
41 AND plan_start_date <= p_to_date)
42 OR ( plan_cmplt_date >= p_from_date
43 AND plan_start_date <= p_to_date) ) )
44 OR ( batch_status = 2
45 AND ( ( actual_start_date >= p_from_date
46 AND actual_start_date <= p_to_date)
47 OR ( plan_cmplt_date >= p_from_date
48 AND actual_start_date <= p_to_date) ) )
49 OR ( batch_status = 3
50 AND ( ( actual_start_date >= p_from_date
51 AND actual_start_date <= p_to_date)
52 OR ( actual_cmplt_date >= p_from_date
53 AND actual_start_date <= p_to_date) ) ) )
54 AND d.line_type = 1
55 AND d.inventory_item_id = itd.inventory_item_id
56 AND d.organization_id = itd.organization_id
57 ORDER BY 2;
58
59 CURSOR batch_lov_from_date
60 IS
61 SELECT DISTINCT b.batch_id, batch_no, b.organization_id
62 ,batch_status, itd.concatenated_segments
63 ,itd.description
64 FROM gme_batch_header b
65 ,gme_material_details d
66 ,mtl_system_items_vl itd
67 WHERE b.organization_id = p_organization_id
68 AND UPPER (batch_no) LIKE p_input_field
69 AND b.batch_id = d.batch_id
70 AND ( ( batch_type = p_batch_type
71 AND ( batch_status = p_pending_status
72 OR batch_status = p_released_status
73 OR batch_status = p_certified_status) )
74 OR (batch_type = p_fpo_type AND batch_status = 1) )
75 AND ( ( batch_status = 1
76 AND ( plan_start_date >= p_from_date
77 OR plan_cmplt_date >= p_from_date) )
78 OR ( batch_status = 2
79 AND ( actual_start_date >= p_from_date
80 OR plan_cmplt_date >= p_from_date) )
81 OR ( batch_status = 3
82 AND ( actual_start_date >= p_from_date
83 OR actual_cmplt_date >= p_from_date) ) )
84 AND d.line_type = 1
85 AND d.inventory_item_id = itd.inventory_item_id
86 AND d.organization_id = itd.organization_id
87 ORDER BY 2;
88
89 l_max_rows BINARY_INTEGER;
90 l_current_start_row BINARY_INTEGER;
91 l_temp_batch_lov_table batchlovdetailtabletype;
92 i BINARY_INTEGER := 1;
93 j BINARY_INTEGER := 0;
94 l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_BATCHES_LOV';
95 BEGIN
96 IF p_to_date IS NOT NULL THEN
97 OPEN batch_lov_from_and_to_date;
98
99 LOOP
100 FETCH batch_lov_from_and_to_date
101 INTO l_temp_batch_lov_table (i);
102
103 EXIT WHEN batch_lov_from_and_to_date%NOTFOUND;
104 i := i + 1;
105 END LOOP;
106
107 CLOSE batch_lov_from_and_to_date;
108 ELSE
109 OPEN batch_lov_from_date;
110
111 LOOP
112 FETCH batch_lov_from_date
113 INTO l_temp_batch_lov_table (i);
114
115 EXIT WHEN batch_lov_from_date%NOTFOUND;
116 i := i + 1;
117 END LOOP;
118
119 CLOSE batch_lov_from_date;
120 END IF;
121
122 x_total_rows := l_temp_batch_lov_table.COUNT;
123
124 IF x_total_rows > 0 THEN
125 l_max_rows := p_num_row_to_get + p_current_start_row;
126
127 IF l_max_rows > x_total_rows THEN
128 l_max_rows := x_total_rows;
129 END IF;
130
131 IF p_current_start_row = 0 THEN
132 l_current_start_row := 1;
133 ELSE
134 l_current_start_row := p_current_start_row;
135 END IF;
136
137 FOR i IN l_current_start_row .. l_max_rows LOOP
138 j := j + 1;
139 x_batch_lov_tbl (j).batch_no :=
140 l_temp_batch_lov_table (i).batch_no;
141 x_batch_lov_tbl (j).concatenated_segments := l_temp_batch_lov_table (i).concatenated_segments;
142 END LOOP;
143 END IF;
144 --Bug2804440
145 EXCEPTION
146 WHEN OTHERS THEN
147 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
148 --End Bug2804440
149 END select_batches_lov;
150
151 /**==========================================================================================
152 # Eddie Oumerretane 21-03-02 Bug 2187223 Select items directly from the ic_item_mst bable
153 #=========================================================================================*/
154 PROCEDURE select_items_lov (
155 p_input_field IN VARCHAR2
156 ,p_current_start_row IN INTEGER
157 ,p_num_row_to_get IN INTEGER
158 ,p_line_type IN INTEGER
159 ,p_organization_id IN INTEGER
160 ,x_total_rows OUT NOCOPY NUMBER
161 ,x_item_lov_tbl OUT NOCOPY itemlovdetailtabletype)
162 IS
163 l_line_type NUMBER;
164 l_add_event VARCHAR2 (100);
165 l_display_event VARCHAR2 (100);
166 l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_ITEMS_LOV';
167
168 /* Bug 2187223
169 CURSOR item_lov IS
170
171 SELECT DISTINCT
172 i.item_id,
173 i.item_no,
174 i.item_desc1
175 FROM
176 GME_BATCH_HEADER b,
177 GME_MATERIAL_DETAILS d,
178 ic_item_mst i
179 WHERE
180 organization_id = p_organization_id AND
181 b.batch_id = d.batch_id AND
182 d.line_type = p_line_type AND
183 d.item_id = i.item_id AND
184 UPPER(i.item_no) like p_input_field
185
186 ORDER BY 2;
187 */
188
189 --FPBug#4911946
190 sqlstmt VARCHAR2(1000);
191 TYPE t_genref IS REF CURSOR;
192 item_lov t_genref;
193
194 /*CURSOR item_lov
195 IS
196 SELECT i.inventory_item_id, i.concatenated_segments, i.description
197 FROM mtl_system_items_vl i
198 WHERE i.concatenated_segments LIKE p_input_field
199 ORDER BY 2; */
200
201 l_max_rows BINARY_INTEGER;
202 l_current_start_row BINARY_INTEGER;
203 l_temp_item_lov_table itemlovdetailtabletype;
204 i BINARY_INTEGER := 1;
205 j BINARY_INTEGER := 0;
206 gme_enabled_flag VARCHAR2(1) := 'Y';
207 serial_control_flag BINARY_INTEGER := 1;
208 BEGIN
209 --FPBug#4911946 Begin
210 sqlstmt := 'SELECT i.inventory_item_id, i.concatenated_segments, i.description '
211 ||' FROM mtl_system_items_vl i '
212 ||' WHERE i.concatenated_segments LIKE :p_input_field '
213 ||' AND organization_id = :org_id '
214 ||' AND process_execution_enabled_flag = :flag '
215 ||' AND serial_number_control_code = :serial_flag ' --Bug#5350221
216 ||' ORDER BY 2 ';
217 OPEN item_lov FOR sqlstmt
218 USING p_input_field, p_organization_id, gme_enabled_flag, serial_control_flag;
219 --FPBug#4911946 End
220 LOOP
221 FETCH item_lov
222 INTO l_temp_item_lov_table (i);
223
224 EXIT WHEN item_lov%NOTFOUND;
225 i := i + 1;
226 END LOOP;
227
228 CLOSE item_lov;
229
230 x_total_rows := l_temp_item_lov_table.COUNT;
231
232 IF x_total_rows > 0 THEN
233 l_max_rows := p_num_row_to_get + p_current_start_row;
234
235 IF l_max_rows > x_total_rows THEN
236 l_max_rows := x_total_rows;
237 END IF;
238
239 IF p_current_start_row = 0 THEN
240 l_current_start_row := 1;
241 ELSE
242 l_current_start_row := p_current_start_row;
243 END IF;
244
245 FOR i IN l_current_start_row .. l_max_rows LOOP
246 j := j + 1;
247 x_item_lov_tbl (j).concatenated_segments := l_temp_item_lov_table (i).concatenated_segments;
248 x_item_lov_tbl (j).description := l_temp_item_lov_table (i).description;
249 END LOOP;
250 END IF;
251 --Bug2804440
252 EXCEPTION
253 WHEN OTHERS THEN
254 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
255 --End Bug2804440
256 END select_items_lov;
257
258 /**
259 * Select the appropriate resources and send them one by one to the gantt
260 */
261 PROCEDURE select_resources_lov (
262 p_input_field IN VARCHAR2
263 ,p_current_start_row IN INTEGER
264 ,p_num_row_to_get IN INTEGER
265 ,p_organization_id IN INTEGER
266 ,x_total_rows OUT NOCOPY NUMBER
267 ,x_rsrc_lov_tbl OUT NOCOPY resourcelovdetailtabletype)
268 IS
269 CURSOR rsrc_lov
270 IS
271 SELECT DISTINCT m.resources, m.resource_desc
272 FROM cr_rsrc_mst m, cr_rsrc_dtl d
273 WHERE d.organization_id = p_organization_id
274 AND UPPER (d.resources) LIKE p_input_field
275 AND d.resources = m.resources
276 ORDER BY 1;
277
278 l_max_rows BINARY_INTEGER;
279 l_current_start_row BINARY_INTEGER;
280 l_temp_rsrc_lov_table resourcelovdetailtabletype;
281 i BINARY_INTEGER := 1;
282 j BINARY_INTEGER := 0;
283 l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_RESOURCES_LOV';
284 BEGIN
285 OPEN rsrc_lov;
286
287 LOOP
288 FETCH rsrc_lov
289 INTO l_temp_rsrc_lov_table (i);
290
291 EXIT WHEN rsrc_lov%NOTFOUND;
292 i := i + 1;
293 END LOOP;
294
295 CLOSE rsrc_lov;
296
297 x_total_rows := l_temp_rsrc_lov_table.COUNT;
298
299 IF x_total_rows > 0 THEN
300 l_max_rows := p_num_row_to_get + p_current_start_row;
301
302 IF l_max_rows > x_total_rows THEN
303 l_max_rows := x_total_rows;
304 END IF;
305
306 IF p_current_start_row = 0 THEN
307 l_current_start_row := 1;
308 ELSE
309 l_current_start_row := p_current_start_row;
310 END IF;
311
312 FOR i IN l_current_start_row .. l_max_rows LOOP
313 j := j + 1;
314 x_rsrc_lov_tbl (j).resources :=
315 l_temp_rsrc_lov_table (i).resources;
316 x_rsrc_lov_tbl (j).resource_desc :=
317 l_temp_rsrc_lov_table (i).resource_desc;
318 END LOOP;
319 END IF;
320 --Bug2804440
321 EXCEPTION
322 WHEN OTHERS THEN
323 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
324 --End Bug2804440
325 END select_resources_lov;
326
327 /**
328 * Select the vaild organizations for the current operator
329 */
330 PROCEDURE select_organizations_lov (
331 p_input_field IN VARCHAR2
332 ,p_current_start_row IN INTEGER
333 ,p_num_row_to_get IN INTEGER
334 ,p_user_id IN NUMBER
335 ,x_total_rows OUT NOCOPY NUMBER
336 ,x_orgn_lov_tbl OUT NOCOPY orgnlovdetailtabletype)
337 IS
338 CURSOR orgn_lov IS
339 SELECT m.organization_code, o.name
340 FROM mtl_parameters m, gmd_parameters_hdr gmd, gme_parameters gme,
341 hr_all_organization_units o --FPBug#4911946
342 WHERE m.organization_code LIKE p_input_field
343 AND gmd.organization_id = m.organization_id
344 AND gmd.plant_ind = 1
345 AND gme.organization_id = m.organization_id
346 AND o.organization_id = m.organization_id
347 ORDER BY 1;
348
349 l_max_rows BINARY_INTEGER;
350 l_current_start_row BINARY_INTEGER;
351 l_temp_orgn_lov_table orgnlovdetailtabletype;
352 i BINARY_INTEGER := 1;
353 j BINARY_INTEGER := 0;
354 l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_ORGANIZATIONS_LOV';
355 BEGIN
356 OPEN orgn_lov;
357
358 LOOP
359 FETCH orgn_lov
360 INTO l_temp_orgn_lov_table (i);
361
362 EXIT WHEN orgn_lov%NOTFOUND;
363 i := i + 1;
364 END LOOP;
365
366 CLOSE orgn_lov;
367
368 x_total_rows := l_temp_orgn_lov_table.COUNT;
369
370 IF x_total_rows > 0 THEN
371 l_max_rows := p_num_row_to_get + p_current_start_row;
372
373 IF l_max_rows > x_total_rows THEN
374 l_max_rows := x_total_rows;
375 END IF;
376
377 IF p_current_start_row = 0 THEN
378 l_current_start_row := 1;
379 ELSE
380 l_current_start_row := p_current_start_row;
381 END IF;
382
383 FOR i IN l_current_start_row .. l_max_rows LOOP
384 j := j + 1;
385 x_orgn_lov_tbl (j).organization_code :=
386 l_temp_orgn_lov_table (i).organization_code;
387 x_orgn_lov_tbl (j).organization_name :=
388 l_temp_orgn_lov_table (i).organization_name;
389 END LOOP;
390 END IF;
391 --Bug2804440
392 EXCEPTION
393 WHEN OTHERS THEN
394 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
395 --End Bug2804440
396 END select_organizations_lov;
397 END;