[Home] [Help]
PACKAGE BODY: APPS.GME_GANTT_RSRC_PKG
Source
1 PACKAGE BODY gme_gantt_rsrc_pkg AS
2 /* $Header: GMEGNTRB.pls 120.3.12000000.2 2007/01/26 22:57:02 snene ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_GANTT_RSRC_PKG';
4
5 /*======================================================================
6 # Return the given string padded on the right with blank characters.
7 #======================================================================*/
8 FUNCTION r_pad (p_str VARCHAR2, p_display_length INTEGER)
9 RETURN VARCHAR2
10 IS
11 l_actual_length INTEGER;
12 l_api_name CONSTANT VARCHAR2 (30) := 'R_PAD';
13 BEGIN
14 IF p_str IS NULL THEN
15 RETURN RPAD (' ', p_display_length);
16 END IF;
17
18 l_actual_length := LENGTH (RPAD (p_str, p_display_length) );
19
20 IF (l_actual_length < p_display_length) THEN
21 l_actual_length :=
22 p_display_length
23 + (p_display_length - l_actual_length);
24 END IF;
25
26 RETURN RPAD (p_str, l_actual_length);
27 --Bug2804440
28 EXCEPTION
29 WHEN OTHERS THEN
30 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
31 --End Bug2804440
32 END r_pad;
33
34 /**
35 * Select the available resources for the current organization and send them one by
36 * one to the gantt
37 */
38 PROCEDURE get_available_plant_resources (
39 p_organization_id IN NUMBER
40 ,x_nb_resources OUT NOCOPY NUMBER
41 ,x_plant_rsrc_tbl OUT NOCOPY plantresourcedetailtabletype)
42 IS
43 CURSOR get_plant_resources
44 IS
45 SELECT DISTINCT r.resources, r.resource_desc
46 FROM cr_rsrc_mst r, cr_rsrc_dtl d
47 WHERE d.organization_id = p_organization_id
48 AND d.delete_mark = 0
49 AND r.resources = d.resources
50 AND r.delete_mark = 0
51 ORDER BY 1;
52
53 resources rescode;
54 resource_desc resdesc;
55 l_nb_record BINARY_INTEGER := 0;
56 l_record_ind BINARY_INTEGER := 1;
57 l_resources_str VARCHAR2 (64);
58 l_resource_desc_str VARCHAR2 (160);
59 l_rsrc_record VARCHAR2 (224);
60 l_rsrc_record_tbl VARCHAR2 (32000);
61 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Available_Plant_Resources';
62 BEGIN
63 x_nb_resources := 0;
64
65 OPEN get_plant_resources;
66
67 IF resources.EXISTS (1) THEN
68 resources.DELETE;
69 END IF;
70
71 FETCH get_plant_resources
72 BULK COLLECT INTO resources, resource_desc;
73
74 IF resources.EXISTS (1) AND resources.COUNT > 0 THEN
75 FOR e IN 1 .. resources.COUNT LOOP
76 l_resources_str := resources (e);
77 l_resource_desc_str := resource_desc (e);
78 l_rsrc_record :=
79 r_pad (l_resources_str, 16)
80 || r_pad (l_resource_desc_str, 40);
81
82 IF l_nb_record = 0 THEN
83 l_rsrc_record_tbl := l_rsrc_record;
84 l_nb_record := l_nb_record + 1;
85 ELSE
86 l_rsrc_record_tbl := l_rsrc_record_tbl || l_rsrc_record;
87 l_nb_record := l_nb_record + 1;
88
89 IF l_nb_record >= 14 THEN
90 x_plant_rsrc_tbl (l_record_ind) := l_rsrc_record_tbl;
91 l_record_ind := l_record_ind + 1;
92 l_nb_record := 0;
93 END IF;
94 END IF;
95
96 x_nb_resources := x_nb_resources + 1;
97 END LOOP;
98
99 IF l_nb_record > 0 THEN
100 x_plant_rsrc_tbl (l_record_ind) := l_rsrc_record_tbl;
101 END IF;
102 END IF;
103
104 CLOSE get_plant_resources;
105 --Bug2804440
106 EXCEPTION
107 WHEN OTHERS THEN
108 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
109 --End Bug2804440
110 END get_available_plant_resources;
111
112 /**
113 * Fetch pending and WIP batches that consume the selected resource at the selected time.
114 */
115 PROCEDURE get_reschedule_batch_list (
116 p_organization_id IN NUMBER
117 ,p_resource IN VARCHAR2
118 ,p_from_date IN DATE
119 ,p_to_date IN DATE
120 ,x_nb_batches OUT NOCOPY NUMBER
121 ,x_resch_batch_tbl OUT NOCOPY reschbatchdetailtabletype)
122 IS
123 CURSOR get_product_info (p_batch_id NUMBER)
124 IS
125 SELECT i.inventory_item_id, i.concatenated_segments
126 FROM mtl_system_items_kfv i --Bug#5550337 changed from ic_item_mst
127 ,gme_batch_header b
128 ,gmd_recipe_validity_rules v
129 WHERE b.batch_id = p_batch_id
130 AND b.recipe_validity_rule_id = v.recipe_validity_rule_id
131 AND v.inventory_item_id = i.inventory_item_id;
132
133 batch_id batchid;
134 batch_no batchno;
135 batch_type batchtype;
136 batch_status batchstatus;
137 start_date batchdate;
138 plan_cmplt_date batchdate;
139 enforce_step_dep enforcestepdep;
140 l_nb_record BINARY_INTEGER := 0;
141 l_record_ind BINARY_INTEGER := 1;
142 l_item_no VARCHAR (128);
143 l_item_id NUMBER;
144 l_batch_id_str VARCHAR2 (10);
145 l_batch_status_str VARCHAR2 (3);
146 l_batch_type_str VARCHAR2 (3);
147 l_enforce_step_dep_str VARCHAR2 (3);
148 l_batch_no_str VARCHAR2 (128);
149 l_batch_record VARCHAR2 (303);
150 l_batch_record_tbl VARCHAR2 (32000);
151 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Reschedule_Batch_List';
152
153 -- Get all batches that are consuming the resource within the time period selected
154 -- by the user.
155 CURSOR get_batches_to_resch
156 IS
157 SELECT b.batch_id, b.batch_no, b.batch_type, b.batch_status
158 /*Bug#5350537 We have to show planned start date in reschedule window irrespective of batch status
159 commented the following code and seletcing plan start date directly */
160 ,b.plan_start_date
161 /*,DECODE (b.batch_status
162 ,1, b.plan_start_date
163 ,b.actual_start_date) start_date */
164 ,b.plan_cmplt_date, NVL (b.enforce_step_dependency, 0)
165 FROM gme_batch_header b
166 WHERE b.organization_id = p_organization_id
167 AND b.parentline_id IS NULL --Bug#5550337 not showing phantom batches as we cant reschedule
168 AND b.batch_status IN (1, 2)
169 AND ( ( b.plan_start_date >= p_from_date
170 AND b.plan_start_date <= p_to_date)
171 OR ( b.plan_cmplt_date >= p_from_date
172 AND b.plan_start_date <= p_to_date) )
173 AND EXISTS (
174 SELECT 1
175 FROM gme_batch_steps r, gme_batch_step_resources o
176 WHERE o.batch_id = b.batch_id
177 AND r.batch_id = o.batch_id
178 AND o.batchstep_id = r.batchstep_id
179 AND r.step_status IN (1, 2)
180 AND r.delete_mark = 0
181 AND o.resources = p_resource
182 AND ( ( o.plan_start_date >= p_from_date
183 AND o.plan_start_date <= p_to_date)
184 OR ( o.plan_cmplt_date >= p_from_date
185 AND o.plan_start_date <= p_to_date) ) )
186 ORDER BY 4;
187 BEGIN
188 x_nb_batches := 0;
189
190 OPEN get_batches_to_resch;
191
192 IF batch_id.EXISTS (1) THEN
193 batch_id.DELETE;
194 END IF;
195
196 FETCH get_batches_to_resch
197 BULK COLLECT INTO batch_id, batch_no, batch_type, batch_status
198 ,start_date, plan_cmplt_date, enforce_step_dep;
199
200 IF batch_id.EXISTS (1) AND batch_id.COUNT > 0 THEN
201 FOR e IN 1 .. batch_id.COUNT LOOP
202 IF start_date (e) IS NOT NULL THEN
203 l_batch_id_str := batch_id (e);
204 l_batch_no_str := batch_no (e);
205 l_batch_type_str := batch_type (e);
206 l_batch_status_str := batch_status (e);
207 l_enforce_step_dep_str := enforce_step_dep (e);
208
209 OPEN get_product_info (batch_id (e) );
210
211 FETCH get_product_info
212 INTO l_item_id, l_item_no;
213
214 CLOSE get_product_info;
215
216 l_batch_record :=
217 LPAD (l_batch_id_str, 10, '0')
218 || r_pad (l_batch_no_str, 32)
219 || LPAD (l_batch_type_str, 3, '0')
220 || LPAD (l_batch_status_str, 3, '0')
221 || LPAD (TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
222 ,14
223 ,'0')
224 || LPAD (TO_CHAR (plan_cmplt_date (e), 'YYYYMMDDHH24MISS')
225 ,14
226 ,'0')
227 || r_pad (l_item_no, 32)
228 || r_pad (l_enforce_step_dep_str, 3);
229
230 IF l_nb_record = 0 THEN
231 l_batch_record_tbl := l_batch_record;
232 l_nb_record := l_nb_record + 1;
233 ELSE
234 l_batch_record_tbl := l_batch_record_tbl || l_batch_record;
235 l_nb_record := l_nb_record + 1;
236
237 IF l_nb_record >= 100 THEN
238 x_resch_batch_tbl (l_record_ind) := l_batch_record_tbl;
239 l_record_ind := l_record_ind + 1;
240 l_nb_record := 0;
241 END IF;
242 END IF;
243
244 x_nb_batches := x_nb_batches + 1;
245 END IF;
246 END LOOP;
247
248 IF l_nb_record > 0 THEN
249 x_resch_batch_tbl (l_record_ind) := l_batch_record_tbl;
250 END IF;
251 END IF;
252
253 CLOSE get_batches_to_resch;
254 --Bug2804440
255 EXCEPTION
256 WHEN OTHERS THEN
257 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
258 --End Bug2804440
259 END get_reschedule_batch_list;
260
261 /**
262 * Retrieve the resource load data (qty available and scheduled usage)
263 *
264 * 25FEB02 Eddie Oumerretane
265 * Bug # 1919745 Implemented the new resource load summary
266 * table GME_BATCH_STEP_RSRC_SUMMARY. Now resource load data
267 * is retrieved from this new table, replacing the
268 * GME_BATCH_STEP_RESOURCES table.
269 * 10OCT02 Eddie Oumerretane.
270 * Bug # 2565952 Replaced table GME_BATCH_STEP_RSRC_SUMMARY
271 * with GME_RESOURCE_TXNS_SUMMARY table.
272 */
273 PROCEDURE fetch_resource_load (
274 p_resource_code IN OUT NOCOPY VARCHAR2
275 ,p_organization_id IN NUMBER
276 ,p_from_date IN DATE
277 ,p_to_date IN DATE
278 ,x_resource_desc OUT NOCOPY VARCHAR2
279 ,x_resource_uom OUT NOCOPY VARCHAR2
280 ,x_nb_load_interval OUT NOCOPY NUMBER
281 ,x_nb_avail_interval OUT NOCOPY NUMBER
282 ,x_rsrc_avail_tbl OUT NOCOPY resourceloadtabletype
283 ,x_rsrc_load_tbl OUT NOCOPY resourceloadtabletype)
284 IS
285 from_date resdate;
286 end_DATE resdate;
287 rsrc_count rescount;
288 l_rsrc_avail_tbl gmp_rsrc_avl_pkg.cal_tab2;
289 l_resource_id NUMBER (10);
290
291 -- Get the total scheduled usage for the resource within the current organization
292 CURSOR rsrc_load
293 IS
294 SELECT start_date from_date, end_date end_DATE
295 ,SUM (required_units) rsrc_count
296 FROM gme_resource_txns_summary
297 WHERE resource_id = l_resource_id
298 AND start_date >= p_from_date
299 AND start_date <= p_to_date
300 GROUP BY start_date, end_date
301 UNION
302 SELECT start_date from_date, end_date end_DATE
303 ,SUM (required_units) rsrc_count
304 FROM gme_resource_txns_summary
305 WHERE resource_id = l_resource_id
306 AND end_date >= p_from_date
307 AND start_date <= p_to_date
308 GROUP BY start_date, end_date
309 ORDER BY 1;
310
311 -- Get resource information
312 CURSOR get_rsrc_info
313 IS
314 SELECT d.resource_id, m.std_usage_um, m.resource_desc
315 FROM cr_rsrc_mst m, cr_rsrc_dtl d
316 WHERE d.organization_id = p_organization_id
317 AND d.resources = p_resource_code
318 AND d.resources = m.resources;
319
320 l_new_rsrc_ind VARCHAR2 (1);
321 l_start_date DATE;
322 l_end_date DATE;
323 l_assigned_qty NUMBER;
324 l_available NUMBER;
325 l_calendar_id NUMBER (10);
326 l_flag VARCHAR2 (1);
327 l_api_version NUMBER := 1;
328 l_init_msg_list VARCHAR2 (1);
329 l_return_status VARCHAR2 (2);
330 l_msg_count NUMBER (10);
331 l_msg_data VARCHAR2 (2000);
332 l_return_code VARCHAR2 (1);
333 l_rsrc_count_str VARCHAR2 (10);
334 l_resource_desc VARCHAR2 (160);
335 l_resource_um VARCHAR2 (16);
336 l_resource_code VARCHAR2 (64);
337 l_record_ind BINARY_INTEGER := 1;
338 l_nb_record BINARY_INTEGER := 0;
339 l_rsrc_load_rec VARCHAR2 (42);
340 l_rsrc_load_rec_tbl VARCHAR2 (32000);
341 l_api_name CONSTANT VARCHAR2 (30) := 'fetch_resource_load';
342 BEGIN
343 x_nb_load_interval := 0;
344 x_nb_avail_interval := 0;
345
346 -- Get resource details
347 OPEN get_rsrc_info;
348
349 FETCH get_rsrc_info
350 INTO l_resource_id, l_resource_um, l_resource_desc;
351
352 IF (get_rsrc_info%FOUND) THEN
353 CLOSE get_rsrc_info;
354
355 x_resource_uom := r_pad (l_resource_um, 4);
356 x_resource_desc := r_pad (l_resource_desc, 40);
357 l_resource_code := p_resource_code;
358 p_resource_code := r_pad (l_resource_code, 16);
359
360 OPEN rsrc_load;
361
362 IF from_date.EXISTS (1) THEN
363 from_date.DELETE;
364 END IF;
365
366 FETCH rsrc_load
367 BULK COLLECT INTO from_date, end_DATE, rsrc_count;
368
369 IF from_date.EXISTS (1) AND from_date.COUNT > 0 THEN
370 FOR e IN 1 .. from_date.COUNT LOOP
371 l_rsrc_count_str := rsrc_count (e);
372 l_rsrc_load_rec :=
373 TO_CHAR (from_date (e), 'YYYYMMDDHH24MISS')
374 || TO_CHAR (end_DATE (e), 'YYYYMMDDHH24MISS')
375 || LPAD (l_rsrc_count_str, 10, '0');
376
377 IF l_nb_record = 0 THEN
378 l_rsrc_load_rec_tbl := l_rsrc_load_rec;
379 l_nb_record := l_nb_record + 1;
380 ELSE
381 l_rsrc_load_rec_tbl :=
382 l_rsrc_load_rec_tbl || l_rsrc_load_rec;
383 l_nb_record := l_nb_record + 1;
384
385 IF l_nb_record >= 840 THEN
386 x_rsrc_load_tbl (l_record_ind) := l_rsrc_load_rec_tbl;
387 l_record_ind := l_record_ind + 1;
388 l_nb_record := 0;
389 END IF;
390 END IF;
391
392 x_nb_load_interval := x_nb_load_interval + 1;
393 END LOOP;
394
395 IF l_nb_record > 0 THEN
396 x_rsrc_load_tbl (l_record_ind) := l_rsrc_load_rec_tbl;
397 END IF;
398 END IF;
399
400 CLOSE rsrc_load;
401
402 l_rsrc_load_rec := '';
403 l_rsrc_load_rec_tbl := '';
404 l_record_ind := 1;
405 l_nb_record := 0;
406 -- Retrieve the available intervals along with the number of resources available
407 gmp_rsrc_avl_pkg.rsrc_avl (p_api_version => l_api_version
408 ,p_init_msg_list => l_init_msg_list
409 ,p_resource_id => l_resource_id
410 ,p_from_date => p_from_date
411 ,p_to_date => p_to_date
412 ,x_return_status => l_return_status
413 ,x_msg_count => l_msg_count
414 ,x_msg_data => l_msg_data
415 ,x_return_code => l_return_code
416 ,p_rec => l_rsrc_avail_tbl
417 ,p_flag => l_flag);
418
419 FOR i IN 1 .. l_rsrc_avail_tbl.COUNT LOOP
420 l_rsrc_count_str := l_rsrc_avail_tbl (i).out_resource_count;
421 l_rsrc_load_rec :=
422 TO_CHAR (l_rsrc_avail_tbl (i).out_cal_from_date
423 ,'YYYYMMDDHH24MISS')
424 || TO_CHAR (l_rsrc_avail_tbl (i).out_cal_to_date
425 ,'YYYYMMDDHH24MISS')
426 || LPAD (l_rsrc_count_str, 10, '0');
427
428 IF l_nb_record = 0 THEN
429 l_rsrc_load_rec_tbl := l_rsrc_load_rec;
430 l_nb_record := l_nb_record + 1;
431 ELSE
432 l_rsrc_load_rec_tbl := l_rsrc_load_rec_tbl || l_rsrc_load_rec;
433 l_nb_record := l_nb_record + 1;
434
435 IF l_nb_record >= 840 THEN
436 x_rsrc_avail_tbl (l_record_ind) := l_rsrc_load_rec_tbl;
437 l_record_ind := l_record_ind + 1;
438 l_nb_record := 0;
439 END IF;
440 END IF;
441
442 x_nb_avail_interval := x_nb_avail_interval + 1;
443 END LOOP;
444
445 IF l_nb_record > 0 THEN
446 x_rsrc_avail_tbl (l_record_ind) := l_rsrc_load_rec_tbl;
447 END IF;
448 ELSE
449 CLOSE get_rsrc_info;
450 END IF;
451 --Bug2804440
452 EXCEPTION
453 WHEN OTHERS THEN
454 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
455 --End Bug2804440
456 END fetch_resource_load;
457 END;