[Home] [Help]
PACKAGE BODY: APPS.GME_GANTT_PKG
Source
1 PACKAGE BODY gme_gantt_pkg AS
2 /* $Header: GMEGNTB.pls 120.8.12010000.1 2008/07/25 10:28:37 appldev ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_GANTT_PKG';
4 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
5 g_bulk_fetch_limit NUMBER := 45;
6 g_product_no VARCHAR2 (32);
7 g_ingredient_no VARCHAR2 (32);
8 g_org_code VARCHAR2(3);
9 g_organization_id NUMBER (10);
10 g_mfg_calendar_code VARCHAR2 (10);
11 g_calendar_desc VARCHAR2 (240);
12 g_calendar_start DATE;
13 g_calendar_end DATE;
14
15 CURSOR get_batches_from_date (
16 p_organization_id NUMBER
17 ,p_batch_no VARCHAR2
18 ,p_to_batch_no VARCHAR2 --Bug#4867640
19 ,p_batch_type NUMBER
20 ,p_pending_status NUMBER
21 ,p_released_status NUMBER
22 ,p_certified_status NUMBER
23 ,p_fpo_type NUMBER
24 ,p_from_date DATE
25 ,p_resource VARCHAR2
26 ,p_prim_rsrc_ind NUMBER
27 ,p_sec_rsrc_ind NUMBER
28 ,p_aux_rsrc_ind NUMBER)
29 IS
30 SELECT b.batch_id, b.batch_no, batch_status, batch_type
31 ,
32 --Bug3315440
33 DECODE (batch_status
34 ,1, date_to_clientdt (plan_start_date)
35 ,date_to_clientdt (actual_start_date) ) start_date
36 ,DECODE (batch_status
37 ,3, date_to_clientdt (actual_cmplt_date)
38 ,date_to_clientdt (plan_cmplt_date) ) end_date
39 ,date_to_clientdt (plan_start_date)
40 ,date_to_clientdt (plan_cmplt_date)
41 ,DECODE (date_to_clientdt (actual_start_date)
42 ,NULL, date_to_clientdt (plan_start_date)
43 ,date_to_clientdt (actual_start_date) ) actual_start
44 ,DECODE (date_to_clientdt (actual_cmplt_date)
45 ,NULL, date_to_clientdt (plan_cmplt_date)
46 ,date_to_clientdt (actual_cmplt_date) ) actual_end
47 ,NVL(b.formula_id,0), NVL (routing_id, 0)
48 ,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
49 ,
50 --Bug#2479773 Swapna Kommineni 02-FEB-2005
51 NVL (finite_scheduled_ind, 0),
52 NVL(parentline_id,0) --Bug#5550337
53 FROM gme_batch_header b
54 WHERE organization_id = p_organization_id
55 --Bug#4867640 check for batch_no within the given range
56 AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
57 (lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
58 lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
59 )
60 AND ( ( batch_type = p_batch_type
61 AND ( batch_status = p_pending_status
62 OR batch_status = p_released_status
63 OR batch_status = p_certified_status) )
64 OR (batch_type = p_fpo_type AND batch_status = 1) )
65 AND ( ( batch_status = 1
66 AND ( plan_start_date >= p_from_date
67 OR plan_cmplt_date >= p_from_date) )
68 OR ( batch_status = 2
69 AND ( actual_start_date >= p_from_date
70 OR plan_cmplt_date >= p_from_date) )
71 OR ( batch_status = 3
72 AND ( actual_start_date >= p_from_date
73 OR actual_cmplt_date >= p_from_date) ) )
74 AND ( ( p_resource = '%'
75 AND p_prim_rsrc_ind = 1
76 AND p_aux_rsrc_ind = 2
77 AND p_sec_rsrc_ind = 0)
78 OR (EXISTS (
79 SELECT 1
80 FROM gme_batch_step_resources d2
81 WHERE d2.batch_id = b.batch_id
82 AND d2.resources LIKE p_resource
83 AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
84 OR d2.prim_rsrc_ind = p_aux_rsrc_ind
85 OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
86 ORDER BY 5;
87
88 CURSOR get_batches_from_to_date (
89 p_organization_id NUMBER
90 ,p_batch_no VARCHAR2
91 ,p_to_batch_no VARCHAR2 --Bug#4867640
92 ,p_batch_type NUMBER
93 ,p_pending_status NUMBER
94 ,p_released_status NUMBER
95 ,p_certified_status NUMBER
96 ,p_fpo_type NUMBER
97 ,p_from_date DATE
98 ,p_to_date DATE
99 ,p_resource VARCHAR2
100 ,p_prim_rsrc_ind NUMBER
101 ,p_sec_rsrc_ind NUMBER
102 ,p_aux_rsrc_ind NUMBER)
103 IS
104 SELECT b.batch_id, batch_no, batch_status, batch_type
105 ,
106 --Bug3315440
107 DECODE (batch_status
108 ,1, date_to_clientdt (plan_start_date)
109 ,date_to_clientdt (actual_start_date) ) start_date
110 ,DECODE (batch_status
111 ,3, date_to_clientdt (actual_cmplt_date)
112 ,date_to_clientdt (plan_cmplt_date) ) end_date
113 ,date_to_clientdt (plan_start_date)
114 ,date_to_clientdt (plan_cmplt_date)
115 ,DECODE (date_to_clientdt (actual_start_date)
116 ,NULL, date_to_clientdt (plan_start_date)
117 ,date_to_clientdt (actual_start_date) ) actual_start
118 ,DECODE (date_to_clientdt (actual_cmplt_date)
119 ,NULL, date_to_clientdt (plan_cmplt_date)
120 ,date_to_clientdt (actual_cmplt_date) ) actual_end
121 ,NVL(formula_id,0), NVL (routing_id, 0)
122 ,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
123 ,
124 --Bug#2479773 Swapna Kommineni 02-FEB-2005
125 NVL (finite_scheduled_ind, 0),
126 NVL(parentline_id,0) --Bug#5550337
127 FROM gme_batch_header b
128 WHERE organization_id = p_organization_id
129 AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
130 (lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
131 lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
132 )
133 AND ( ( batch_type = p_batch_type
134 AND ( batch_status = p_pending_status
135 OR batch_status = p_released_status
136 OR batch_status = p_certified_status) )
137 OR (batch_type = p_fpo_type AND batch_status = 1) )
138 AND ( ( batch_status = 1
139 AND ( ( plan_start_date >= p_from_date
140 AND plan_start_date <= p_to_date)
141 OR ( plan_cmplt_date >= p_from_date
142 AND plan_start_date <= p_to_date) ) )
143 OR ( batch_status = 2
144 AND ( ( actual_start_date >= p_from_date
145 AND actual_start_date <= p_to_date)
146 OR ( plan_cmplt_date >= p_from_date
147 AND actual_start_date <= p_to_date) ) )
148 OR ( batch_status = 3
149 AND ( ( actual_start_date >= p_from_date
150 AND actual_start_date <= p_to_date)
151 OR ( actual_cmplt_date >= p_from_date
152 AND actual_start_date <= p_to_date) ) ) )
153 AND ( ( p_resource = '%'
154 AND p_prim_rsrc_ind = 1
155 AND p_aux_rsrc_ind = 2
156 AND p_sec_rsrc_ind = 0)
157 OR (EXISTS (
158 SELECT 1
159 FROM gme_batch_step_resources d2
160 WHERE d2.batch_id = b.batch_id
161 AND d2.resources LIKE p_resource
162 AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
163 OR d2.prim_rsrc_ind = p_aux_rsrc_ind
164 OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
165 ORDER BY 5;
166
167 /* Moved these cursors to global space, as these are used in
168 multiple procedures */
169 CURSOR get_product_info (p_batch_id NUMBER)
170 IS
171 SELECT i.inventory_item_id, i.concatenated_segments, i.description
172 FROM mtl_system_items_kfv i
173 ,gme_batch_header b
174 ,gmd_recipe_validity_rules v
175 WHERE b.batch_id = p_batch_id
176 AND b.recipe_validity_rule_id = v.recipe_validity_rule_id
177 AND v.inventory_item_id = i.inventory_item_id
178 AND b.organization_id = i.organization_id;
179 /* This cursor is defined for the LCF batch */
180 CURSOR get_product_info_lcf (p_batch_id NUMBER)
181 IS
182 SELECT i.inventory_item_id, i.concatenated_segments, i.description
183 FROM mtl_system_items_kfv i
184 ,gme_batch_header b
185 ,gme_material_details m
186 WHERE b.batch_id = p_batch_id
187 AND b.batch_id = m.batch_id
188 AND m.organization_id = i.organization_id
189 AND m.inventory_item_id = i.inventory_item_id
190 AND m.line_type = 1
191 AND m.line_no = 1;
192
193 CURSOR get_product_qty (p_batch_id NUMBER, p_inventory_item_id NUMBER)
194 IS
195 SELECT SUM (plan_qty), NVL (SUM (actual_qty), 0), dtl_um
196 FROM gme_material_details
197 WHERE batch_id = p_batch_id
198 AND line_type = 1
199 AND inventory_item_id = p_inventory_item_id
200 GROUP BY dtl_um;
201
202
203 /*======================================================================
204 # Return the given string padded on the right with blank characters.
205 #======================================================================*/
206 FUNCTION r_pad (p_str VARCHAR2, p_display_length INTEGER)
207 RETURN VARCHAR2
208 IS
209 l_actual_length INTEGER;
210 l_api_name CONSTANT VARCHAR2 (30) := 'R_PAD';
211 BEGIN
212 IF p_str IS NULL THEN
213 RETURN RPAD (' ', p_display_length);
214 END IF;
215
216 l_actual_length := LENGTH (RPAD (p_str, p_display_length) );
217
218 IF (l_actual_length < p_display_length) THEN
219 l_actual_length :=
220 p_display_length
221 + (p_display_length - l_actual_length);
222 END IF;
223
224 RETURN RPAD (p_str, l_actual_length);
225 --Bug2804440
226 EXCEPTION
227 WHEN OTHERS THEN
228 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
229 --End Bug2804440
230 END r_pad;
231
232 PROCEDURE init_session (
233 p_organization_id IN NUMBER
234 ,p_org_code IN VARCHAR2
235 ,p_batch_no IN VARCHAR2
236 ,p_to_batch_no IN VARCHAR2 --Bug#4867640
237 ,p_from_date IN DATE
238 ,p_to_date IN DATE
239 ,p_resource IN VARCHAR2
240 ,p_product_no IN VARCHAR2
241 ,p_ingredient_no IN VARCHAR2
242 ,p_prim_rsrc_ind IN INTEGER
243 ,p_sec_rsrc_ind IN INTEGER
244 ,p_aux_rsrc_ind IN INTEGER
245 ,p_batch_type IN INTEGER
246 ,p_fpo_type IN INTEGER
247 ,p_released_status IN INTEGER
248 ,p_pending_status IN INTEGER
249 ,p_certified_status IN INTEGER)
250 IS
251 l_api_name CONSTANT VARCHAR2 (30) := 'INIT_SESSION';
252 BEGIN
253 close_cursors;
254
255 IF p_to_date IS NULL THEN
256 OPEN get_batches_from_date (p_organization_id
257 ,p_batch_no
258 ,p_to_batch_no --Bug#4867640
259 ,p_batch_type
260 ,p_pending_status
261 ,p_released_status
262 ,p_certified_status
263 ,p_fpo_type
264 ,p_from_date
265 ,p_resource
266 ,p_prim_rsrc_ind
267 ,p_sec_rsrc_ind
268 ,p_aux_rsrc_ind);
269 ELSE
270 OPEN get_batches_from_to_date (p_organization_id
271 ,p_batch_no
272 ,p_to_batch_no --Bug#4867640
273 ,p_batch_type
274 ,p_pending_status
275 ,p_released_status
276 ,p_certified_status
277 ,p_fpo_type
278 ,p_from_date
279 ,p_to_date
280 ,p_resource
281 ,p_prim_rsrc_ind
282 ,p_sec_rsrc_ind
283 ,p_aux_rsrc_ind);
284 END IF;
285
286 g_product_no := p_product_no;
287 g_ingredient_no := p_ingredient_no;
288 g_organization_id := p_organization_id;
289 g_org_code := p_org_code;
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
293 gme_debug.put_line ('Exception occurs = ' || SQLERRM);
294 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
295 END IF;
296 END init_session;
297
298 /*======================================================================
299 # Eddie Oumerretane 10-APR-02 Bug # 2313926. Fix the test that ends the
300 # the retrieval of batches/FPOs when the pre-determined
301 # number of documents are loaded (Get_Next_Batch_Set).
302 #======================================================================*/
303 PROCEDURE get_next_batch_set (
304 x_nb_batches OUT NOCOPY NUMBER
305 ,x_start_of_first_batch OUT NOCOPY DATE
306 ,x_batch_table OUT NOCOPY gantttableofbatch)
307 IS
308 l_product_type NUMBER (1) := 1;
309 l_ingredient_type NUMBER (1) := -1;
310 batch_id batchidtab;
311 batch_no batchnotab;
312 batch_status batchstatustab;
313 batch_type batchtypetab;
314 start_date batchdatetab;
315 end_date batchdatetab;
316 plan_start_date batchdatetab;
317 plan_cmplt_date batchdatetab;
318 actual_start batchdatetab;
319 actual_end batchdatetab;
320 formula_id batchidtab;
321 routing_id batchidtab;
322 item_no itemnotab;
323 item_um batchumtab;
324 plan_qty batchqtytab;
325 actual_qty batchqtytab;
326 enforce_step_dep batchtypetab;
327 --Bug3746919 bsatpute
328 terminated_ind batchtypetab;
329 --Bug#2479773 Swapna Kommineni 02-FEB-2005
330 finite_scheduled_ind batchtypetab;
331 parentline_id batchidtab; --Bug#5550337
332 l_nb_record BINARY_INTEGER := 0;
333 l_record_ind BINARY_INTEGER := 1;
334 l_batch_id_str VARCHAR2 (32);
335 l_parent_line_id_str VARCHAR2 (32); --Bug#5550337
336 l_formula_id_str VARCHAR2 (32);
337 l_routing_id_str VARCHAR2 (32);
338 --l_batch_status_str VARCHAR2(32);
339 --l_batch_type_str VARCHAR2(32);
340 --l_enforce_step_dep_str VARCHAR2(32);
341 l_batch_no_str VARCHAR2 (128);
342 l_batch_record VARCHAR2 (700);
343 l_batch_record_tbl VARCHAR2 (32000);
344 l_continue BOOLEAN;
345 l_inventory_item_id NUMBER (15);
346 l_item_no VARCHAR2 (128);
347 l_item_desc VARCHAR2 (280);
348 l_item_um VARCHAR2 (16);
349 l_plan_qty NUMBER;
350 l_actual_qty NUMBER;
351 l_plan_qty_str VARCHAR2 (40);
352 l_actual_qty_str VARCHAR2 (40);
353 l_api_name CONSTANT VARCHAR2 (30) := 'GET_NEXT_BATCH_SET';
354
355 /**
356 * Check whether the given batch consumes or produce the given item based on the line type.
357 */
358 FUNCTION filter_batch_item (
359 p_batch_id NUMBER
360 ,p_item_no VARCHAR2
361 ,p_line_type NUMBER)
362 RETURN BOOLEAN
363 IS
364 CURSOR get_ingredient
365 IS
366 SELECT COUNT (*)
367 FROM mtl_system_items_kfv i, gme_material_details m
368 WHERE m.batch_id = p_batch_id
369 AND m.inventory_item_id = i.inventory_item_id
370 AND m.line_type = -1
371 AND m.organization_id = i.organization_id
372 AND i.concatenated_segments LIKE p_item_no;
373
374 CURSOR get_product
375 IS
376 SELECT COUNT (*)
377 FROM mtl_system_items_kfv i, gme_material_details m
378 WHERE m.batch_id = p_batch_id
379 AND m.inventory_item_id = i.inventory_item_id
380 AND m.organization_id = i.organization_id
381 AND m.line_type = 1
382 AND i.concatenated_segments LIKE p_item_no;
383
384 l_dummy NUMBER;
385 l_return BOOLEAN;
386 l_api_name CONSTANT VARCHAR2 (30) := 'FILTER_BATCH_ITEM';
387 BEGIN
388 l_return := TRUE;
389 l_dummy := 0;
390
391 IF p_line_type = 1 THEN
392 OPEN get_product;
393
394 FETCH get_product
395 INTO l_dummy;
396
397 IF (get_product%NOTFOUND OR l_dummy = 0) THEN
398 l_return := FALSE;
399 END IF;
400
401 CLOSE get_product;
402 ELSE
403 OPEN get_ingredient;
404
405 FETCH get_ingredient
406 INTO l_dummy;
407
408 IF (get_ingredient%NOTFOUND OR l_dummy = 0) THEN
409 l_return := FALSE;
410 END IF;
411
412 CLOSE get_ingredient;
413 END IF;
414
415 RETURN l_return;
416 --Bug2804440
417 EXCEPTION
418 WHEN OTHERS THEN
419 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
420 --End Bug2804440
421 END filter_batch_item;
422
423 /**
424 * Sub-program used to populate the batch detail table.
425 */
426 FUNCTION filter_batch (p_batch_id NUMBER)
427 RETURN BOOLEAN
428 IS
429 l_is_batch_valid BOOLEAN;
430 l_api_name CONSTANT VARCHAR2 (30) := 'FILTER_BATCH';
431 BEGIN
432 l_is_batch_valid := TRUE;
433
434 IF g_ingredient_no <> '%' THEN
435 -- Check whether this batch consumes the specified ingredient
436 l_is_batch_valid :=
437 filter_batch_item (p_batch_id
438 ,g_ingredient_no
439 ,l_ingredient_type);
440 END IF;
441
442 IF g_product_no <> '%' AND l_is_batch_valid THEN
443 -- Check whether this batch produces the specified product
444 l_is_batch_valid :=
445 filter_batch_item (p_batch_id, g_product_no, l_product_type);
446 END IF;
447
448 RETURN l_is_batch_valid;
449 --Bug2804440
450 EXCEPTION
451 WHEN OTHERS THEN
452 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
453 --End Bug2804440
454 END filter_batch;
455 BEGIN
456 x_nb_batches := 0;
457 x_start_of_first_batch := NULL;
458
459 /* Bug: 6375968 Initialized the variables */
460 l_plan_qty := 0;
461 l_actual_qty := 0;
462 l_item_um := ' ';
463 l_inventory_item_id := 0;
464 l_item_no := ' ';
465
466
467 -- To date has not been entered in the Filter window
468 IF get_batches_from_date%ISOPEN THEN
469 l_continue := TRUE;
470
471 WHILE (l_continue) LOOP
472 IF batch_id.EXISTS (1) THEN
473 batch_id.DELETE;
474 END IF;
475
476 FETCH get_batches_from_date
477 BULK COLLECT INTO batch_id, batch_no, batch_status, batch_type
478 ,start_date, end_date, plan_start_date, plan_cmplt_date
479 ,actual_start, actual_end, formula_id, routing_id
480 ,enforce_step_dep,
481 --bug3746919 bsatpute
482 terminated_ind,
483 --Bug#2479773 Swapna Kommineni 02-FEB-2005
484 finite_scheduled_ind, parentline_id LIMIT g_bulk_fetch_limit;
485
486 IF batch_id.EXISTS (1) AND batch_id.COUNT > 0 THEN
487 FOR e IN 1 .. batch_id.COUNT LOOP
488 --- Eddie Oumerretane 07-MAR-03 Bug # 2831884.
489 --- Removed tests on start/end dates below in order to allow WIP batches
490 --- with plan end date < actual start date to display in the tree.
491 IF filter_batch (batch_id (e) ) THEN
492 OPEN get_product_info (batch_id (e) );
493
494 FETCH get_product_info
495 INTO l_inventory_item_id, l_item_no, l_item_desc;
496
497 IF get_product_info%NOTFOUND THEN
498 /* This must be an LCF batch */
499 OPEN get_product_info_lcf (batch_id (e) );
500
501 FETCH get_product_info_lcf
502 INTO l_inventory_item_id, l_item_no, l_item_desc;
503
504 CLOSE get_product_info_lcf;
505 END IF;
506
507 CLOSE get_product_info;
508
509 OPEN get_product_qty (batch_id (e)
510 ,l_inventory_item_id);
511
512 FETCH get_product_qty
513 INTO l_plan_qty, l_actual_qty, l_item_um;
514
515 CLOSE get_product_qty;
516
517
518 l_plan_qty_str := ROUND (l_plan_qty, 2);
519 l_actual_qty_str := ROUND (l_actual_qty, 2);
520 l_batch_id_str := batch_id (e);
521 l_formula_id_str := formula_id (e);
522 l_routing_id_str := routing_id (e);
523 --l_batch_type_str := batch_type(e);
524 l_batch_no_str := batch_no (e);
525 l_parent_line_id_str := parentline_id (e); --Bug#5550337
526 -- l_batch_status_str := batch_status(e);
527 -- l_enforce_step_dep_str := enforce_step_dep(e);
528 l_batch_record :=
529 LPAD (l_batch_id_str, 32, '0')
530 || r_pad (l_batch_no_str, 32)
531 || LPAD (batch_type (e), 5, '0')
532 || R_PAD (g_org_code, 32)
533 || LPAD (batch_status (e), 5, '0')
534 || LPAD (TO_CHAR (plan_start_date (e)
535 ,'YYYYMMDDHH24MISS')
536 ,32
537 ,'0')
538 || LPAD (TO_CHAR (plan_cmplt_date (e)
539 ,'YYYYMMDDHH24MISS')
540 ,32
541 ,'0')
542 || LPAD (TO_CHAR (actual_start (e)
543 ,'YYYYMMDDHH24MISS')
544 ,32
545 ,'0')
546 || LPAD (TO_CHAR (actual_end (e), 'YYYYMMDDHH24MISS')
547 ,32
548 ,'0')
549 || LPAD (TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
550 ,32
551 ,'0')
552 || LPAD (TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
553 ,32
554 ,'0')
555 || LPAD (l_formula_id_str, 32, '0')
556 || LPAD (l_routing_id_str, 32, '0')
557 || r_pad (l_item_no, 100)
558 || LPAD (l_actual_qty_str, 40, '0')
559 || LPAD (l_plan_qty_str, 40, '0')
560 || r_pad (l_item_um, 4)
561 || LPAD (enforce_step_dep (e), 5, '0')
562 --Bug3746919 bsatpute
563 || LPAD (terminated_ind (e), 5, '0')
564 || LPAD (finite_scheduled_ind (e), 5, '0')
565 || LPAD (l_parent_line_id_str, 32, '0'); --Bug#5550337
566
567 IF l_nb_record = 0 THEN
568 IF x_start_of_first_batch IS NULL THEN
569 x_start_of_first_batch := start_date (e);
570 END IF;
571
572 l_batch_record_tbl := l_batch_record;
573 l_nb_record := l_nb_record + 1;
574 ELSE
575 l_batch_record_tbl :=
576 l_batch_record_tbl || l_batch_record;
577 l_nb_record := l_nb_record + 1;
578
579 IF l_nb_record >= 40 THEN
580 x_batch_table (l_record_ind) := l_batch_record_tbl;
581 l_record_ind := l_record_ind + 1;
582 l_nb_record := 0;
583 END IF;
584 END IF;
585
586 x_nb_batches := x_nb_batches + 1;
587 END IF;
588 END LOOP;
589 END IF;
590
591 IF NOT batch_id.EXISTS (1)
592 OR ( batch_id.COUNT <= x_nb_batches
593 OR batch_id.COUNT < g_bulk_fetch_limit) THEN
594 -- All selected batches meet the filter criteria or this was the last
595 -- set of batches. In this case, send the batches to the gantt.
596 -- Otherwise, continue fetching ...
597 l_continue := FALSE;
598 END IF;
599 END LOOP;
600
601 IF l_nb_record > 0 THEN
602 x_batch_table (l_record_ind) := l_batch_record_tbl;
603 END IF;
604 -- To date has been entered in the Filter window
605 ELSIF get_batches_from_to_date%ISOPEN THEN
606 l_continue := TRUE;
607
608 WHILE (l_continue) LOOP
609 IF batch_id.EXISTS (1) THEN
610 batch_id.DELETE;
611 END IF;
612
613 FETCH get_batches_from_to_date
614 BULK COLLECT INTO batch_id, batch_no, batch_status, batch_type
615 ,start_date, end_date, plan_start_date, plan_cmplt_date
616 ,actual_start, actual_end, formula_id, routing_id
617 ,enforce_step_dep,
618 --bug3746919 bsatpute
619 terminated_ind,
620 --Bug#2479773 Swapna Kommineni 02-FEB-2005
621 finite_scheduled_ind, parentline_id LIMIT g_bulk_fetch_limit;
622
623 --Bug3746919
624 IF batch_id.EXISTS (1) AND batch_id.COUNT > 0 THEN
625 FOR e IN 1 .. batch_id.COUNT LOOP
626 IF filter_batch (batch_id (e) ) THEN
627 OPEN get_product_info (batch_id (e) );
628
629 FETCH get_product_info
630 INTO l_inventory_item_id, l_item_no, l_item_desc;
631
632 IF get_product_info%NOTFOUND THEN
633 FETCH get_product_info_lcf
634 INTO l_inventory_item_id, l_item_no, l_item_desc;
635
636 CLOSE get_product_info_lcf;
637 END IF;
638
639 CLOSE get_product_info;
640
641 OPEN get_product_qty (batch_id (e)
642 ,l_inventory_item_id);
643
644 FETCH get_product_qty
645 INTO l_plan_qty, l_actual_qty, l_item_um;
646
647 CLOSE get_product_qty;
648
649 l_plan_qty_str := ROUND (l_plan_qty, 2);
650 l_actual_qty_str := ROUND (l_actual_qty, 2);
651 l_batch_id_str := batch_id (e);
652 l_formula_id_str := formula_id (e);
653 l_routing_id_str := routing_id (e);
654 --l_batch_type_str := batch_type(e);
655 l_batch_no_str := batch_no (e);
656 l_parent_line_id_str := parentline_id (e); --Bug#5550337
657 --l_batch_status_str := batch_status(e);
658 --l_enforce_step_dep_str := enforce_step_dep(e);
659 l_batch_record :=
660 LPAD (l_batch_id_str, 32, '0')
661 || r_pad (l_batch_no_str, 32)
662 || LPAD (batch_type (e), 5, '0')
663 || LPAD (g_organization_id, 32, '0')
664 || LPAD (batch_status (e), 5, '0')
665 || LPAD (TO_CHAR (plan_start_date (e)
666 ,'YYYYMMDDHH24MISS')
667 ,32
668 ,'0')
669 || LPAD (TO_CHAR (plan_cmplt_date (e)
670 ,'YYYYMMDDHH24MISS')
671 ,32
672 ,'0')
673 || LPAD (TO_CHAR (actual_start (e)
674 ,'YYYYMMDDHH24MISS')
675 ,32
676 ,'0')
677 || LPAD (TO_CHAR (actual_end (e), 'YYYYMMDDHH24MISS')
678 ,32
679 ,'0')
680 || LPAD (TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
681 ,32
682 ,'0')
683 || LPAD (TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
684 ,32
685 ,'0')
686 || LPAD (l_formula_id_str, 32, '0')
687 || LPAD (l_routing_id_str, 32, '0')
688 || r_pad (l_item_no, 100)
689 || LPAD (l_actual_qty_str, 40, '0')
690 || LPAD (l_plan_qty_str, 40, '0')
691 || r_pad (l_item_um, 4)
692 || LPAD (enforce_step_dep (e), 5, '0')
693 --bug3746919 bsatpute
694 || LPAD (terminated_ind (e), 5, '0')
695 || LPAD (finite_scheduled_ind (e), 5, '0')
696 || LPAD (l_parent_line_id_str, 32, '0'); --Bug#5550337
697
698 IF l_nb_record = 0 THEN
699 IF x_start_of_first_batch IS NULL THEN
700 x_start_of_first_batch := start_date (e);
701 END IF;
702
703 l_batch_record_tbl := l_batch_record;
704 l_nb_record := l_nb_record + 1;
705 ELSE
706 l_batch_record_tbl :=
707 l_batch_record_tbl || l_batch_record;
708 l_nb_record := l_nb_record + 1;
709
710 IF l_nb_record >= 40 THEN
711 x_batch_table (l_record_ind) := l_batch_record_tbl;
712 l_record_ind := l_record_ind + 1;
713 l_nb_record := 0;
714 END IF;
715 END IF;
716
717 x_nb_batches := x_nb_batches + 1;
718 END IF;
719 END LOOP;
720 END IF;
721
722 IF NOT batch_id.EXISTS (1)
723 OR ( batch_id.COUNT <= x_nb_batches
724 OR batch_id.COUNT < g_bulk_fetch_limit) THEN
725 -- All selected batches meet the filter criteria or this was the last
726 -- set of batches. In this case, send the batches to the gantt.
727 -- Otherwise, continue fetching ...
728 l_continue := FALSE;
729 END IF;
730 END LOOP;
731
732 IF l_nb_record > 0 THEN
733 x_batch_table (l_record_ind) := l_batch_record_tbl;
734 END IF;
735 END IF;
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
739 gme_debug.put_line ('no data found ');
740 END IF;
741 --Bug2804440
742 WHEN OTHERS THEN
743 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
744 --End Bug2804440
745 close_cursors;
746 END get_next_batch_set;
747
748 /*======================================================================
749 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
750 # dates when retrieving operations/activities and resources.
751 # Bharati Satpute Bug3746919. Replaced the NVL insted of DECODE
752 # in the get_operation_steps cursor.
753 #======================================================================*/
754 PROCEDURE get_operations (
755 p_batch_id IN NUMBER
756 ,p_resource IN VARCHAR2
757 ,p_prim_rsrc_ind IN NUMBER
758 ,p_sec_rsrc_ind IN NUMBER
759 ,p_aux_rsrc_ind IN NUMBER
760 ,x_nb_operation OUT NOCOPY NUMBER
761 ,x_operations_table OUT NOCOPY gantttableofoperation)
762 IS
763 start_date batchdatetab;
764 batchstep_no batchstepnotab;
765 step_status batchstatustab;
766 plan_step_qty batchqtytab;
767 actual_step_qty batchqtytab;
768 plan_start_date batchdatetab;
769 actual_start_date batchdatetab;
770 expct_cmplt_date batchdatetab;
771 actual_cmplt_date batchdatetab;
772 step_close_date batchdatetab;
773 oprn_id batchidtab;
774 oprn_no batchnotab;
775 oprn_vers batchverstab;
776 oprn_desc batchdesctab;
777 oper_class batchclasstab;
778 process_qty_um batchumtab;
779 end_date batchdatetab;
780 --bsatpute bug3746919
781 terminated_ind batchtypetab;
782
783 CURSOR get_operation_steps
784 IS
785 SELECT DISTINCT
786 --Bug3746919 bsatpute
787 DECODE
788 (step_status
789 ,1, date_to_clientdt (r.plan_start_date)
790 ,5, date_to_clientdt (b.actual_start_date)
791 ,date_to_clientdt (r.actual_start_date) )
792 start_date
793 --NVL(date_to_clientDT(r.actual_start_date),date_to_clientDT(r.plan_start_date)) start_date
794 ,r.batchstep_no, r.step_status, r.plan_step_qty
795 ,DECODE (r.actual_step_qty
796 ,NULL, 0
797 ,r.actual_step_qty) actual_step_qty
798 ,date_to_clientdt (r.plan_start_date)
799 ,NVL (date_to_clientdt (r.actual_start_date)
800 ,date_to_clientdt (r.plan_start_date) )
801 ,date_to_clientdt (r.plan_cmplt_date)
802 ,NVL (date_to_clientdt (r.actual_cmplt_date)
803 ,date_to_clientdt (r.plan_cmplt_date) )
804 ,NVL (date_to_clientdt (r.step_close_date)
805 ,date_to_clientdt (SYSDATE) )
806 ,o.oprn_id, o.oprn_no, NVL (o.oprn_vers, 0)
807 ,o.oprn_desc
808 ,DECODE (o.oprn_class
809 ,NULL, '?'
810 ,o.oprn_class) oper_class
811 ,o.process_qty_um
812 ,DECODE
813 (step_status
814 ,3, date_to_clientdt (r.actual_cmplt_date)
815 ,date_to_clientdt (r.plan_cmplt_date) ) end_date
816 --bug3746919 bsatpute
817 ,NVL (r.terminated_ind, 0)
818 FROM gme_batch_steps r
819 ,gme_batch_step_resources d
820 ,gmd_operations o
821 ,gme_batch_header b
822 WHERE r.batch_id = p_batch_id
823 AND r.batch_id = d.batch_id
824 AND r.batch_id = b.batch_id
825 AND r.oprn_id = o.oprn_id
826 AND r.batchstep_id = d.batchstep_id
827 AND d.resources LIKE p_resource
828 AND ( d.prim_rsrc_ind = p_prim_rsrc_ind
829 OR d.prim_rsrc_ind = p_aux_rsrc_ind
830 OR d.prim_rsrc_ind = p_sec_rsrc_ind)
831 ORDER BY 1;
832
833 CURSOR get_oper_class_desc (p_oper_class VARCHAR2)
834 IS
835 SELECT oprn_class_desc
836 FROM gmd_operation_class
837 WHERE oprn_class = p_oper_class;
838
839 l_nb_record BINARY_INTEGER := 0;
840 l_record_ind BINARY_INTEGER := 1;
841 l_batch_id_str VARCHAR2 (10);
842 l_plan_qty_str VARCHAR2 (40);
843 l_actual_qty_str VARCHAR2 (40);
844 l_batchstep_no_str VARCHAR2 (10);
845 l_step_status_str VARCHAR2 (3);
846 l_oprn_id_str VARCHAR2 (10);
847 l_oprn_no_str VARCHAR2 (64);
848 ---l_oprn_vers_str VARCHAR2(5);
849 l_oprn_desc_str VARCHAR2 (160);
850 l_class_str VARCHAR2 (16);
851 l_class_desc_str VARCHAR2 (160);
852 l_oper_record VARCHAR2 (737);
853 l_oper_record_tbl VARCHAR2 (32000);
854 l_api_name CONSTANT VARCHAR2 (30) := 'GET_OPERATIONS';
855 BEGIN
856 x_nb_operation := 0;
857
858 OPEN get_operation_steps;
859
860 IF oprn_id.EXISTS (1) THEN
861 oprn_id.DELETE;
862 END IF;
863
864 FETCH get_operation_steps
865 BULK COLLECT INTO start_date, batchstep_no, step_status, plan_step_qty
866 ,actual_step_qty, plan_start_date, actual_start_date
867 ,expct_cmplt_date, actual_cmplt_date, step_close_date, oprn_id
868 ,oprn_no, oprn_vers, oprn_desc, oper_class, process_qty_um
869 ,end_date,
870 --bug3746919 bsatpute
871 terminated_ind LIMIT g_bulk_fetch_limit;
872
873 IF oprn_id.EXISTS (1) AND oprn_id.COUNT > 0 THEN
874 FOR e IN 1 .. oprn_id.COUNT LOOP
875 l_class_desc_str := '?';
876
877 IF (oper_class (e) <> '?') THEN
878 OPEN get_oper_class_desc (oper_class (e) );
879
880 FETCH get_oper_class_desc
881 INTO l_class_desc_str;
882
883 CLOSE get_oper_class_desc;
884 END IF;
885
886 l_batch_id_str := p_batch_id;
887 l_oprn_id_str := oprn_id (e);
888 l_oprn_no_str := oprn_no (e);
889 --- l_oprn_vers_str := oprn_vers(e);
890 l_oprn_desc_str := oprn_desc (e);
891 l_class_str := oper_class (e);
892 l_batchstep_no_str := batchstep_no (e);
893 l_step_status_str := step_status (e);
894 l_plan_qty_str := ROUND (plan_step_qty (e), 3);
895 l_actual_qty_str := ROUND (actual_step_qty (e), 3);
896 l_oper_record :=
897 LPAD (l_batch_id_str, 10, '0')
898 || LPAD (l_oprn_id_str, 10, '0')
899 || r_pad (l_oprn_no_str, 16)
900 || r_pad (l_oprn_desc_str, 40)
901 || r_pad (l_class_str, 4)
902 || r_pad (l_class_desc_str, 40)
903 || LPAD (l_batchstep_no_str, 10, '0')
904 || LPAD (l_step_status_str, 3, '0')
905 || LPAD (l_plan_qty_str, 40, '0')
906 || LPAD (l_actual_qty_str, 40, '0')
907 || r_pad (process_qty_um (e), 4)
908 || TO_CHAR (plan_start_date (e), 'YYYYMMDDHH24MISS')
909 || TO_CHAR (actual_start_date (e), 'YYYYMMDDHH24MISS')
910 || TO_CHAR (expct_cmplt_date (e), 'YYYYMMDDHH24MISS')
911 || TO_CHAR (actual_cmplt_date (e), 'YYYYMMDDHH24MISS')
912 || TO_CHAR (step_close_date (e), 'YYYYMMDDHH24MISS')
913 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
914 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
915 || LPAD (oprn_vers (e), 5, '0')
916 --bsatpute bug3746919
917 || LPAD (terminated_ind (e), 5, '0');
918
919 IF l_nb_record = 0 THEN
920 l_oper_record_tbl := l_oper_record;
921 l_nb_record := l_nb_record + 1;
922 ELSE
923 l_oper_record_tbl := l_oper_record_tbl || l_oper_record;
924 l_nb_record := l_nb_record + 1;
925
926 IF l_nb_record >= 45 THEN
927 x_operations_table (l_record_ind) := l_oper_record_tbl;
928 l_record_ind := l_record_ind + 1;
929 l_nb_record := 0;
930 END IF;
931 END IF;
932
933 x_nb_operation := x_nb_operation + 1;
934 END LOOP;
935
936 IF l_nb_record > 0 THEN
937 x_operations_table (l_record_ind) := l_oper_record_tbl;
938 END IF;
939 END IF;
940
941 CLOSE get_operation_steps;
942 --Bug2804440
943 EXCEPTION
944 WHEN OTHERS THEN
945 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
946 --End Bug2804440
947 END get_operations;
948
949 /*======================================================================
950 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
951 # dates when retrieving operations/activities and resources.
952 #======================================================================*/
953 PROCEDURE get_activities (
954 p_batch_id IN NUMBER
955 ,p_batchstep_no IN NUMBER
956 ,p_resource IN VARCHAR2
957 ,p_prim_rsrc_ind IN NUMBER
958 ,p_sec_rsrc_ind IN NUMBER
959 ,p_aux_rsrc_ind IN NUMBER
960 ,x_nb_activity OUT NOCOPY NUMBER
961 ,x_activity_table OUT NOCOPY gantttableofactivity)
962 IS
963 activity activitycodetab;
964 activity_desc batchdesctab;
965 cost_analysis_code costancodetab;
966 cost_analysis_desc batchdesctab;
967 start_date batchdatetab;
968 end_date batchdatetab;
969
970 CURSOR get_activities
971 IS
972 SELECT a.activity, a.activity_desc, a.cost_analysis_code
973 ,c.cost_analysis_desc
974 ,MIN
975 (DECODE (r.step_status
976 ,1, date_to_clientdt (o.plan_start_date)
977 ,5, date_to_clientdt (b.actual_start_date)
978 ,date_to_clientdt (o.actual_start_date) ) )
979 start_date
980 ,MAX
981 (DECODE (r.step_status
982 ,3, date_to_clientdt (o.actual_cmplt_date)
983 ,date_to_clientdt (o.plan_cmplt_date) ) )
984 end_date
985 FROM gme_batch_step_resources o
986 ,gme_batch_steps r
987 ,gme_batch_step_activities s
988 ,gmd_activities a
989 ,gme_batch_header b
990 ,cm_alys_mst c
991 WHERE o.batch_id = p_batch_id
992 AND r.batch_id = o.batch_id
993 AND s.batch_id = r.batch_id
994 AND b.batch_id = r.batch_id
995 AND s.delete_mark = 0
996 AND o.batchstep_activity_id = s.batchstep_activity_id
997 AND r.batchstep_no = p_batchstep_no
998 AND o.batchstep_id = r.batchstep_id
999 AND s.activity = a.activity
1000 AND c.cost_analysis_code = a.cost_analysis_code
1001 AND o.resources LIKE p_resource
1002 AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
1003 OR o.prim_rsrc_ind = p_aux_rsrc_ind
1004 OR o.prim_rsrc_ind = p_sec_rsrc_ind)
1005 GROUP BY a.activity
1006 ,a.activity_desc
1007 ,a.cost_analysis_code
1008 ,c.cost_analysis_desc
1009 ORDER BY 5;
1010
1011 l_nb_record BINARY_INTEGER := 0;
1012 l_record_ind BINARY_INTEGER := 1;
1013 l_batch_id_str VARCHAR2 (10);
1014 l_batchstep_no_str VARCHAR2 (10);
1015 l_activity_str VARCHAR2 (64);
1016 l_activity_desc_str VARCHAR2 (160);
1017 l_cost_ana_code_str VARCHAR2 (16);
1018 l_cost_ana_desc_str VARCHAR2 (160);
1019 l_activity_record VARCHAR2 (600);
1020 l_activity_record_tbl VARCHAR2 (32000);
1021 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ACTIVITIES';
1022 BEGIN
1023 x_nb_activity := 0;
1024
1025 OPEN get_activities;
1026
1027 IF activity.EXISTS (1) THEN
1028 activity.DELETE;
1029 END IF;
1030
1031 FETCH get_activities
1032 BULK COLLECT INTO activity, activity_desc, cost_analysis_code
1033 ,cost_analysis_desc, start_date, end_date;
1034
1035 IF activity.EXISTS (1) AND activity.COUNT > 0 THEN
1036 FOR e IN 1 .. activity.COUNT LOOP
1037 l_batch_id_str := p_batch_id;
1038 l_batchstep_no_str := p_batchstep_no;
1039 l_activity_str := activity (e);
1040 l_activity_desc_str := activity_desc (e);
1041 l_cost_ana_code_str := cost_analysis_code (e);
1042 l_cost_ana_desc_str := cost_analysis_desc (e);
1043 l_activity_record :=
1044 LPAD (l_batch_id_str, 10, '0')
1045 || LPAD (l_batchstep_no_str, 10, '0')
1046 || r_pad (l_activity_str, 16)
1047 || r_pad (l_activity_desc_str, 40)
1048 || r_pad (l_cost_ana_code_str, 4)
1049 || r_pad (l_cost_ana_desc_str, 40)
1050 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1051 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS');
1052
1053 IF l_nb_record = 0 THEN
1054 l_activity_record_tbl := l_activity_record;
1055 l_nb_record := l_nb_record + 1;
1056 ELSE
1057 l_activity_record_tbl :=
1058 l_activity_record_tbl || l_activity_record;
1059 l_nb_record := l_nb_record + 1;
1060
1061 IF l_nb_record >= 50 THEN
1062 x_activity_table (l_record_ind) := l_activity_record_tbl;
1063 l_record_ind := l_record_ind + 1;
1064 l_nb_record := 0;
1065 END IF;
1066 END IF;
1067
1068 x_nb_activity := x_nb_activity + 1;
1069 END LOOP;
1070
1071 IF l_nb_record > 0 THEN
1072 x_activity_table (l_record_ind) := l_activity_record_tbl;
1073 END IF;
1074 END IF;
1075
1076 CLOSE get_activities;
1077 --Bug2804440
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1081 --End Bug2804440
1082 END get_activities;
1083
1084 /*======================================================================
1085 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
1086 # dates when retrieving operations/activities and resources.
1087 #======================================================================*/
1088 PROCEDURE get_resources (
1089 p_batch_id IN NUMBER
1090 ,p_batchstep_no IN NUMBER
1091 ,p_activity IN VARCHAR2
1092 ,p_resource IN VARCHAR2
1093 ,p_prim_rsrc_ind IN NUMBER
1094 ,p_sec_rsrc_ind IN NUMBER
1095 ,p_aux_rsrc_ind IN NUMBER
1096 ,x_nb_resource OUT NOCOPY NUMBER
1097 ,x_resource_table OUT NOCOPY gantttableofresource)
1098 IS
1099 resources batchresourcestab;
1100 resource_desc batchdesctab;
1101 prim_rsrc_ind batchintindtab;
1102 scale_type batchtypetab;
1103 plan_rsrc_count batchqtytab;
1104 actual_res_count batchqtytab;
1105 plan_rsrc_qty batchqtytab;
1106 actual_res_qty batchqtytab;
1107 plan_rsrc_usage batchqtytab;
1108 actual_res_usage batchqtytab;
1109 std_usage_um batchumtab;
1110 offset_interval batchqtytab;
1111 start_date batchdatetab;
1112 end_date batchdatetab;
1113 plan_start_date batchdatetab;
1114 plan_cmplt_date batchdatetab;
1115 actual_start_date batchdatetab;
1116 actual_cmplt_date batchdatetab;
1117 process_qty_um batchumtab;
1118 step_status batchstatustab;
1119
1120 CURSOR get_resources
1121 IS
1122 SELECT o.resources, c.resource_desc, o.prim_rsrc_ind
1123 ,o.scale_type, NVL (o.plan_rsrc_count, 0)
1124 ,DECODE (o.actual_rsrc_count
1125 ,NULL, 0
1126 ,o.actual_rsrc_count) actual_res_count
1127 ,NVL (o.plan_rsrc_qty, 0)
1128 ,DECODE (o.actual_rsrc_qty
1129 ,NULL, 0
1130 ,o.actual_rsrc_qty) actual_res_qty
1131 ,NVL (o.plan_rsrc_usage, 0)
1132 ,DECODE (o.actual_rsrc_usage
1133 ,NULL, 0
1134 ,o.actual_rsrc_usage) actual_res_usage
1135 ,NVL (c.std_usage_um, ' '), NVL (o.offset_interval, 0)
1136 --Swapna K Bug#2479773 17-MAR-2005 Rework
1137 /* Null actual start date is sent as planned start date */
1138 ,DECODE
1139 (r.step_status
1140 ,1, date_to_clientdt (o.plan_start_date)
1141 ,5, date_to_clientdt (b.actual_start_date)
1142 ,NVL (date_to_clientdt (o.actual_start_date)
1143 ,date_to_clientdt (o.plan_start_date) ) )
1144 start_date
1145 ,DECODE (r.step_status
1146 ,3, date_to_clientdt (o.actual_cmplt_date)
1147 ,date_to_clientdt (o.plan_cmplt_date) ) end_date
1148 ,date_to_clientdt (o.plan_start_date)
1149 ,date_to_clientdt (o.plan_cmplt_date)
1150 ,NVL (date_to_clientdt (o.actual_start_date)
1151 ,date_to_clientdt (o.plan_start_date) )
1152 ,NVL (date_to_clientdt (o.actual_cmplt_date)
1153 ,date_to_clientdt (o.plan_cmplt_date) )
1154 ,NVL (f.process_qty_um, ' '), NVL (r.step_status, 0)
1155 FROM gme_batch_steps r
1156 ,gme_batch_step_activities a
1157 ,gme_batch_step_resources o
1158 ,gmd_operations f
1159 ,gme_batch_header b
1160 ,cr_rsrc_mst c
1161 WHERE r.batch_id = p_batch_id
1162 AND b.batch_id = r.batch_id
1163 AND r.batchstep_no = p_batchstep_no
1164 AND a.batchstep_id = r.batchstep_id
1165 AND a.delete_mark = 0
1166 AND o.batchstep_id = a.batchstep_id
1167 AND o.batchstep_activity_id = a.batchstep_activity_id
1168 AND a.activity = p_activity
1169 AND f.oprn_id = r.oprn_id
1170 AND r.batch_id = o.batch_id
1171 AND o.resources = c.resources
1172 AND o.resources LIKE p_resource
1173 AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
1174 OR o.prim_rsrc_ind = p_aux_rsrc_ind
1175 OR o.prim_rsrc_ind = p_sec_rsrc_ind)
1176 ORDER BY 13;
1177
1178 l_nb_record BINARY_INTEGER := 0;
1179 l_record_ind BINARY_INTEGER := 1;
1180 l_batch_id_str VARCHAR2 (10);
1181 l_batchstep_no_str VARCHAR2 (10);
1182 l_resources_str VARCHAR2 (64);
1183 l_resource_desc_str VARCHAR2 (160);
1184 l_prim_rsrc_ind_str VARCHAR2 (2);
1185 l_scale_type_str VARCHAR2 (2);
1186 l_plan_rsrc_count_str VARCHAR2 (40);
1187 l_actual_res_count_str VARCHAR2 (40);
1188 l_plan_rsrc_qty_str VARCHAR2 (40);
1189 l_actual_rsrc_qty_str VARCHAR2 (40);
1190 l_plan_rsrc_usage_str VARCHAR2 (40);
1191 l_actual_rsrc_usage_str VARCHAR2 (40);
1192 l_std_usage_um_str VARCHAR2 (16);
1193 l_offset_interval_str VARCHAR2 (40);
1194 l_process_qty_um_str VARCHAR2 (16);
1195 l_step_status_str VARCHAR2 (3);
1196 l_resource_record VARCHAR2 (792);
1197 l_resource_record_tbl VARCHAR2 (32000);
1198 l_api_name CONSTANT VARCHAR2 (30) := 'GET_RESOURCES';
1199 BEGIN
1200 x_nb_resource := 0;
1201
1202 OPEN get_resources;
1203
1204 IF resources.EXISTS (1) THEN
1205 resources.DELETE;
1206 END IF;
1207
1208 FETCH get_resources
1209 BULK COLLECT INTO resources, resource_desc, prim_rsrc_ind, scale_type
1210 ,plan_rsrc_count, actual_res_count, plan_rsrc_qty, actual_res_qty
1211 ,plan_rsrc_usage, actual_res_usage, std_usage_um, offset_interval
1212 ,start_date, end_date, plan_start_date, plan_cmplt_date
1213 ,actual_start_date, actual_cmplt_date, process_qty_um
1214 ,step_status;
1215
1216 IF resources.EXISTS (1) AND resources.COUNT > 0 THEN
1217 FOR e IN 1 .. resources.COUNT LOOP
1218 l_batch_id_str := p_batch_id;
1219 l_batchstep_no_str := p_batchstep_no;
1220 l_resources_str := resources (e);
1221 l_resource_desc_str := resource_desc (e);
1222 l_prim_rsrc_ind_str := prim_rsrc_ind (e);
1223 l_scale_type_str := scale_type (e);
1224 l_plan_rsrc_count_str := plan_rsrc_count (e);
1225 l_actual_res_count_str := actual_res_count (e);
1226 l_plan_rsrc_qty_str := ROUND (plan_rsrc_qty (e), 3);
1227 l_actual_rsrc_qty_str := ROUND (actual_res_qty (e), 3);
1228 l_plan_rsrc_usage_str := ROUND (plan_rsrc_usage (e), 3);
1229 l_actual_rsrc_usage_str := ROUND (actual_res_usage (e), 3);
1230 l_std_usage_um_str := std_usage_um (e);
1231 l_offset_interval_str := offset_interval (e);
1232 l_step_status_str := step_status (e);
1233 l_process_qty_um_str := process_qty_um (e);
1234 l_resource_record :=
1235 LPAD (l_batch_id_str, 10, '0')
1236 || LPAD (l_batchstep_no_str, 10, '0')
1237 || r_pad (l_resources_str, 16)
1238 || r_pad (l_resource_desc_str, 40)
1239 || LPAD (l_prim_rsrc_ind_str, 2, '0')
1240 || LPAD (l_scale_type_str, 2, '0')
1241 || LPAD (l_plan_rsrc_count_str, 40, 0)
1242 || LPAD (l_actual_res_count_str, 40, 0)
1243 || LPAD (l_plan_rsrc_qty_str, 40, 0)
1244 || LPAD (l_actual_rsrc_qty_str, 40, 0)
1245 || LPAD (l_plan_rsrc_usage_str, 40, 0)
1246 || LPAD (l_actual_rsrc_usage_str, 40, 0)
1247 || r_pad (l_std_usage_um_str, 4)
1248 || LPAD (l_offset_interval_str, 40, 0)
1249 || LPAD (l_step_status_str, 3, '0')
1250 || r_pad (l_process_qty_um_str, 4)
1251 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1252 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
1253 || TO_CHAR (plan_start_date (e), 'YYYYMMDDHH24MISS')
1254 || TO_CHAR (plan_cmplt_date (e), 'YYYYMMDDHH24MISS')
1255 || TO_CHAR (actual_start_date (e), 'YYYYMMDDHH24MISS')
1256 || TO_CHAR (actual_cmplt_date (e), 'YYYYMMDDHH24MISS');
1257
1258 IF l_nb_record = 0 THEN
1259 l_resource_record_tbl := l_resource_record;
1260 l_nb_record := l_nb_record + 1;
1261 ELSE
1262 l_resource_record_tbl :=
1263 l_resource_record_tbl || l_resource_record;
1264 l_nb_record := l_nb_record + 1;
1265
1266 IF l_nb_record >= 40 THEN
1267 x_resource_table (l_record_ind) := l_resource_record_tbl;
1268 l_record_ind := l_record_ind + 1;
1269 l_nb_record := 0;
1270 END IF;
1271 END IF;
1272
1273 x_nb_resource := x_nb_resource + 1;
1274 END LOOP;
1275
1276 IF l_nb_record > 0 THEN
1277 x_resource_table (l_record_ind) := l_resource_record_tbl;
1278 END IF;
1279 END IF;
1280
1281 CLOSE get_resources;
1282 --Bug2804440
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1286 --End Bug2804440
1287 END get_resources;
1288
1289 /*======================================================================
1290 # Eddie Oumerretane 21-MAR-02 Bug # 2189705. Retrieve the UOM from
1291 # the GME_MATERIAL_DETAILS in Get_Batch_Properties
1292 #======================================================================*/
1293 PROCEDURE get_batch_properties (
1294 p_batch_id IN NUMBER
1295 ,x_batch_properties_str OUT NOCOPY VARCHAR2)
1296 IS
1297 CURSOR get_ids (p_batch_id NUMBER)
1298 IS
1299 SELECT b.formula_id, b.routing_id, b.recipe_validity_rule_id
1300 ,v.recipe_id
1301 FROM gme_batch_header b, gmd_recipe_validity_rules v
1302 WHERE b.batch_id = p_batch_id
1303 AND b.recipe_validity_rule_id = v.recipe_validity_rule_id(+);
1304
1305 CURSOR get_routing_info (p_routing_id NUMBER)
1306 IS
1307 SELECT routing_no, routing_vers, routing_desc
1308 FROM gmd_routings
1309 WHERE routing_id = p_routing_id;
1310
1311 CURSOR get_formula_info (p_formula_id NUMBER)
1312 IS
1313 SELECT formula_no, formula_vers, formula_desc1
1314 FROM fm_form_mst
1315 WHERE formula_id = p_formula_id;
1316
1317 CURSOR get_recipe_info (p_recipe_id NUMBER)
1318 IS
1319 SELECT recipe_no, recipe_version, recipe_description
1320 FROM gmd_recipes
1321 WHERE recipe_id = p_recipe_id;
1322
1323 l_routing_id NUMBER (10);
1324 l_formula_id NUMBER (10);
1325 l_recipe_id NUMBER (15);
1326 l_recipe_vr_id NUMBER (15);
1327 l_inventory_item_id NUMBER (15);
1328 l_routing_no VARCHAR2 (128) := '?';
1329 l_routing_vers NUMBER (5) := 0;
1330 l_formula_no VARCHAR2 (128) := '?';
1331 l_recipe_no VARCHAR2 (128) := '?';
1332 l_formula_vers NUMBER (5) := 0;
1333 l_recipe_vers NUMBER (5) := 0;
1334 l_item_no VARCHAR2 (128) := '?';
1335 l_item_desc VARCHAR2 (280) := '?';
1336 l_recipe_desc VARCHAR2 (280) := '?';
1337 l_formula_desc VARCHAR2 (280) := '?';
1338 l_routing_desc VARCHAR2 (160) := '?';
1339 l_item_um VARCHAR2 (16) := '?';
1340 l_plan_qty NUMBER := 0;
1341 l_actual_qty NUMBER := 0;
1342 l_routing_vers_str VARCHAR2 (5);
1343 l_formula_vers_str VARCHAR2 (5);
1344 l_plan_qty_str VARCHAR2 (40);
1345 l_actual_qty_str VARCHAR2 (40);
1346 l_api_name CONSTANT VARCHAR2 (30) := 'GET_BATCH_PROPERTIES';
1347 BEGIN
1348 OPEN get_ids (p_batch_id);
1349
1350 FETCH get_ids
1351 INTO l_formula_id, l_routing_id, l_recipe_vr_id, l_recipe_id;
1352
1353 CLOSE get_ids;
1354
1355 IF (l_recipe_id IS NOT NULL AND l_recipe_id > 0) THEN
1356 OPEN get_recipe_info (l_recipe_id);
1357
1358 FETCH get_recipe_info
1359 INTO l_recipe_no, l_recipe_vers, l_recipe_desc;
1360
1361 CLOSE get_recipe_info;
1362 END IF;
1363
1364 IF (l_routing_id IS NOT NULL AND l_routing_id > 0) THEN
1365 OPEN get_routing_info (l_routing_id);
1366
1367 FETCH get_routing_info
1368 INTO l_routing_no, l_routing_vers, l_routing_desc;
1369
1370 CLOSE get_routing_info;
1371 END IF;
1372
1373 IF (l_formula_id IS NOT NULL AND l_formula_id > 0) THEN
1374 OPEN get_formula_info (l_formula_id);
1375
1376 FETCH get_formula_info
1377 INTO l_formula_no, l_formula_vers, l_formula_desc;
1378
1379 CLOSE get_formula_info;
1380 END IF;
1381
1382 OPEN get_product_info (p_batch_id);
1383
1384 FETCH get_product_info
1385 INTO l_inventory_item_id, l_item_no, l_item_desc;
1386
1387 IF get_product_info%NOTFOUND THEN
1388 /* This must be an LCF batch */
1389 OPEN get_product_info_lcf (p_batch_id);
1390
1391 FETCH get_product_info_lcf
1392 INTO l_inventory_item_id, l_item_no, l_item_desc;
1393
1394 CLOSE get_product_info_lcf;
1395 END IF;
1396
1397 CLOSE get_product_info;
1398
1399 OPEN get_product_qty (p_batch_id, l_inventory_item_id);
1400
1401 FETCH get_product_qty
1402 INTO l_plan_qty, l_actual_qty, l_item_um;
1403
1404 CLOSE get_product_qty;
1405
1406 l_routing_vers_str := l_routing_vers;
1407 l_formula_vers_str := l_formula_vers;
1408 l_plan_qty_str := ROUND (l_plan_qty, 3);
1409 l_actual_qty_str := ROUND (l_actual_qty, 3);
1410 x_batch_properties_str :=
1411 r_pad (l_item_no, 32)
1412 || r_pad (l_item_desc, 70)
1413 || LPAD (l_plan_qty_str, 40, '0')
1414 || LPAD (l_actual_qty_str, 40, '0')
1415 || r_pad (l_item_um, 4)
1416 || r_pad (l_formula_no, 32)
1417 || LPAD (l_formula_vers, 5, '0')
1418 || r_pad (l_routing_no, 32)
1419 || LPAD (l_routing_vers, 5, '0')
1420 || r_pad (l_recipe_no, 32)
1421 || LPAD (l_recipe_vers, 5, '0')
1422 || r_pad (l_recipe_desc, 70)
1423 || r_pad (l_formula_desc, 70)
1424 || r_pad (l_routing_desc, 40);
1425 --Bug2804440
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1429 --End Bug2804440
1430 END get_batch_properties;
1431
1432 PROCEDURE close_cursors
1433 IS
1434 l_api_name CONSTANT VARCHAR2 (30) := 'CLOSE_CURSORS';
1435 BEGIN
1436 IF get_batches_from_date%ISOPEN THEN
1437 CLOSE get_batches_from_date;
1438 END IF;
1439
1440 IF get_batches_from_to_date%ISOPEN THEN
1441 CLOSE get_batches_from_to_date;
1442 END IF;
1443 --Bug2804440
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1447 --End Bug2804440
1448 END close_cursors;
1449
1450 /*======================================================================
1451 # Retrieve Shop calendar data for the given organization
1452 #======================================================================*/
1453 FUNCTION get_mfg_calendar (p_organization_id IN NUMBER)
1454 RETURN BOOLEAN
1455 IS
1456 CURSOR get_mfg_cal_code
1457 IS
1458 SELECT p.calendar_code, c.description
1459 FROM mtl_parameters p, bom_calendars c
1460 WHERE p.organization_id = p_organization_id
1461 AND c.calendar_code = p.calendar_code;
1462 BEGIN
1463 OPEN get_mfg_cal_code;
1464
1465 FETCH get_mfg_cal_code
1466 INTO g_mfg_calendar_code, g_calendar_desc;
1467
1468 IF (get_mfg_cal_code%NOTFOUND OR g_mfg_calendar_code IS NULL) THEN
1469 CLOSE get_mfg_cal_code;
1470
1471 RETURN FALSE;
1472 ELSE
1473 CLOSE get_mfg_cal_code;
1474
1475 RETURN TRUE;
1476 END IF;
1477 END get_mfg_calendar;
1478
1479 /*======================================================================
1480 # Retrieve Shop calendar assigned to the given organization
1481 #======================================================================*/
1482 PROCEDURE fetch_shop_calendar (
1483 p_organization_id IN NUMBER
1484 ,p_date IN DATE
1485 ,x_calendar_no OUT NOCOPY VARCHAR2
1486 ,x_calendar_desc OUT NOCOPY VARCHAR2
1487 ,x_calendar_start OUT NOCOPY VARCHAR2
1488 ,x_calendar_end OUT NOCOPY VARCHAR2
1489 ,x_calendar_assigned OUT NOCOPY VARCHAR2
1490 ,x_return_code OUT NOCOPY VARCHAR2
1491 ,x_error_msg OUT NOCOPY VARCHAR2)
1492 IS
1493 -- Get Calendar Date Range
1494 CURSOR get_calendar_date_range (p_calendar_code VARCHAR2)
1495 IS
1496 SELECT MIN (date_to_clientdt (calendar_start_date) )
1497 ,MAX (date_to_clientdt (calendar_end_date) )
1498 FROM bom_calendars
1499 WHERE calendar_code = p_calendar_code;
1500
1501 l_api_name CONSTANT VARCHAR2 (30) := 'Fetch_Shop_Calendar';
1502 inv_calendar_sel EXCEPTION;
1503 no_calendar_assigned EXCEPTION;
1504 BEGIN
1505 x_return_code := 'S';
1506 x_error_msg := ' ';
1507
1508 IF NOT get_mfg_calendar (p_organization_id) THEN
1509 RAISE no_calendar_assigned;
1510 ELSE
1511 x_calendar_assigned := 'Y';
1512
1513 OPEN get_calendar_date_range (g_mfg_calendar_code);
1514
1515 FETCH get_calendar_date_range
1516 INTO g_calendar_start, g_calendar_end;
1517
1518 CLOSE get_calendar_date_range;
1519 END IF;
1520
1521 IF (p_date IS NOT NULL)
1522 AND (p_date < g_calendar_start OR p_date > g_calendar_end) THEN
1523 RAISE inv_calendar_sel;
1524 END IF;
1525
1526 x_calendar_start := TO_CHAR (g_calendar_start, 'YYYYMMDDHH24MISS');
1527 x_calendar_end := TO_CHAR (g_calendar_end, 'YYYYMMDDHH24MISS');
1528 x_calendar_no := r_pad (g_mfg_calendar_code, 16);
1529 x_calendar_desc := r_pad (g_calendar_desc, 40);
1530 EXCEPTION
1531 WHEN inv_calendar_sel THEN
1532 fnd_message.set_name ('GME', 'GME_DATE_OUTSIDE_SHOP_CALENDAR');
1533 --Bug3315440
1534 --FND_MESSAGE.SET_TOKEN('SEL_DATE', TO_CHAR(p_date,'DD-MON-YYYY'));
1535 --FND_MESSAGE.SET_TOKEN('CAL_BEGIN_DATE', TO_CHAR(g_calendar_start,'DD-MON-YYYY'));
1536 --FND_MESSAGE.SET_TOKEN('CAL_END_DATE', TO_CHAR(g_calendar_end,'DD-MON-YYYY'));
1537 --Bug4068469: removed trunc from the set_token call
1538 --FND_MESSAGE.SET_TOKEN('SEL_DATE', trunc(fnd_date.date_to_displayDT(p_date),'DD-MON-YYYY'));
1539 fnd_message.set_token ('SEL_DATE'
1540 ,fnd_date.date_to_displaydt (p_date) );
1541 fnd_message.set_token ('CAL_BEGIN_DATE'
1542 ,fnd_date.date_to_displaydt (g_calendar_start) );
1543 fnd_message.set_token ('CAL_END_DATE'
1544 ,fnd_date.date_to_displaydt (g_calendar_end) );
1545 x_return_code := 'F';
1546 x_error_msg := r_pad (fnd_message.get, 1000);
1547 WHEN no_calendar_assigned THEN
1548 x_calendar_assigned := 'N';
1549 fnd_message.set_name ('GMP', 'GME_NO_CAL_ASSIGNED_TO_PLANT');
1550 x_return_code := 'F';
1551 x_error_msg := r_pad (fnd_message.get, 1000);
1552 WHEN OTHERS THEN
1553 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1554 END fetch_shop_calendar;
1555
1556 /*======================================================================
1557 # Retrieve working and non working days for a given date range
1558 #======================================================================*/
1559 PROCEDURE fetch_work_non_work_days (
1560 p_organization_id IN NUMBER
1561 ,p_from_date IN DATE
1562 ,p_to_date IN DATE
1563 ,x_calendar_no OUT NOCOPY VARCHAR2
1564 ,x_calendar_desc OUT NOCOPY VARCHAR2
1565 ,x_calendar_start OUT NOCOPY VARCHAR2
1566 ,x_calendar_end OUT NOCOPY VARCHAR2
1567 ,x_rec_count OUT NOCOPY NUMBER
1568 ,x_shop_cal_tbl OUT NOCOPY shopcalendartabletype
1569 ,x_calendar_assigned OUT NOCOPY VARCHAR2
1570 ,x_return_code OUT NOCOPY VARCHAR2
1571 ,x_error_msg OUT NOCOPY VARCHAR2)
1572 IS
1573 l_output_tbl gmp_calendar_api.date_tbl;
1574 l_nb_record BINARY_INTEGER := 0;
1575 l_record_ind BINARY_INTEGER := 1;
1576 l_cal_rec VARCHAR2 (15);
1577 l_cal_rec_tbl VARCHAR2 (32000);
1578 l_api_name CONSTANT VARCHAR2 (30) := 'Fetch_Work_Non_Work_Days';
1579 l_max_records CONSTANT NUMBER := 2100;
1580 inv_calendar_sel EXCEPTION;
1581 fetch_cal_error EXCEPTION;
1582 no_cal_assigned_to_org EXCEPTION;
1583 BEGIN
1584 x_rec_count := 0;
1585 x_return_code := 'S';
1586 x_error_msg := ' ';
1587 x_calendar_assigned := 'Y';
1588 --- Get the calendar associated to the given organization
1589 fetch_shop_calendar (p_organization_id => p_organization_id
1590 ,p_date => NULL
1591 ,x_calendar_no => x_calendar_no
1592 ,x_calendar_desc => x_calendar_desc
1593 ,x_calendar_start => x_calendar_start
1594 ,x_calendar_end => x_calendar_end
1595 ,x_calendar_assigned => x_calendar_assigned
1596 ,x_return_code => x_return_code
1597 ,x_error_msg => x_error_msg);
1598
1599 IF x_return_code <> 'S' THEN
1600 IF x_calendar_assigned = 'N' THEN
1601 RAISE no_cal_assigned_to_org;
1602 ELSE
1603 RAISE inv_calendar_sel;
1604 END IF;
1605 END IF;
1606
1607 gmp_calendar_api.get_all_dates (p_api_version => 1
1608 ,p_init_msg_list => FALSE
1609 ,p_calendar_code => g_mfg_calendar_code
1610 ,p_start_date => p_from_date
1611 ,p_end_date => p_to_date
1612 ,p_output_tbl => l_output_tbl
1613 ,x_return_status => x_return_code);
1614
1615 IF x_return_code <> 'S' THEN
1616 RAISE fetch_cal_error;
1617 END IF;
1618
1619 IF l_output_tbl.COUNT > 0 THEN
1620 x_rec_count := l_output_tbl.COUNT;
1621
1622 FOR e IN 1 .. l_output_tbl.COUNT LOOP
1623 l_cal_rec :=
1624 TO_CHAR (l_output_tbl (e).cal_date, 'YYYYMMDDHH24MISS')
1625 || l_output_tbl (e).is_workday;
1626
1627 IF l_nb_record = 0 THEN
1628 l_cal_rec_tbl := l_cal_rec;
1629 l_nb_record := l_nb_record + 1;
1630 ELSE
1631 l_cal_rec_tbl := l_cal_rec_tbl || l_cal_rec;
1632 l_nb_record := l_nb_record + 1;
1633
1634 IF l_nb_record >= l_max_records THEN
1635 x_shop_cal_tbl (l_record_ind) := l_cal_rec_tbl;
1636 l_record_ind := l_record_ind + 1;
1637 l_nb_record := 0;
1638 END IF;
1639 END IF;
1640 END LOOP;
1641
1642 IF l_nb_record > 0 THEN
1643 x_shop_cal_tbl (l_record_ind) := l_cal_rec_tbl;
1644 END IF;
1645 END IF;
1646 EXCEPTION
1647 WHEN inv_calendar_sel THEN
1648 fnd_message.set_name ('GMD', 'GMD_UNEXPECTED_ERROR');
1649 fnd_message.set_token ('ERROR', SQLERRM);
1650 x_return_code := 'F';
1651 x_error_msg := fnd_message.get;
1652 WHEN fetch_cal_error THEN
1653 x_error_msg := r_pad (fnd_message.get, 1000);
1654 WHEN no_cal_assigned_to_org THEN
1655 x_return_code := 'F';
1656 WHEN OTHERS THEN
1657 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1658 END fetch_work_non_work_days;
1659
1660 /*======================================================================
1661 # Check whether batch/FPO/Step can be re-scheduled based on shop calendar.
1662 # Returns:
1663 # S : Batch/Step can be rescheduled
1664 # C : Prompt the user to reschedule without using shop calendar
1665 # P : Prompt the user to reschedule ignoring contiguity constraints
1666 # F : Batch/Step cannot be reschedule
1667 #======================================================================*/
1668 PROCEDURE validate_reschedule_event (
1669 p_batch_id IN NUMBER
1670 ,p_organization_id IN NUMBER
1671 ,p_primary_prod_no IN VARCHAR2
1672 ,p_start_date IN DATE
1673 ,p_end_date IN DATE
1674 ,p_entity_type IN VARCHAR2
1675 ,x_return_code OUT NOCOPY VARCHAR2
1676 ,x_error_msg OUT NOCOPY VARCHAR2)
1677 IS
1678 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Reschedule_Event';
1679 l_is_working_day BOOLEAN;
1680 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
1681 l_batch_duration NUMBER;
1682 l_contiguity_ind NUMBER;
1683 l_batch_no VARCHAR2 (32);
1684 non_work_time EXCEPTION;
1685 reschedule_error EXCEPTION;
1686 no_calendar_assigned EXCEPTION;
1687 BEGIN
1688 l_is_working_day := TRUE;
1689 x_return_code := 'S';
1690 x_error_msg := ' ';
1691
1692 IF g_mfg_calendar_code IS NULL THEN
1693 IF NOT get_mfg_calendar (p_organization_id) THEN
1694 RAISE no_calendar_assigned;
1695 END IF;
1696 END IF;
1697
1698 IF p_start_date IS NOT NULL THEN
1699 l_is_working_day :=
1700 gmp_calendar_api.is_working_daytime
1701 (p_api_version => 1
1702 ,p_init_msg_list => TRUE
1703 ,p_calendar_code => g_mfg_calendar_code
1704 ,p_date => p_start_date
1705 ,p_ind => 0
1706 , -- Start date
1707 x_return_status => x_return_code);
1708
1709 /* For now let's treat this condition as non-working day
1710 So the folowing code is commented. If the API returns
1711 Error or if the date is not working time, give same error
1712 IF x_return_code <> FND_API.G_RET_STS_SUCCESS THEN
1713 RAISE RESCHEDULE_ERROR;
1714 END IF; */
1715
1716 --- Warn the user that start date is a non working time
1717 IF NOT l_is_working_day THEN
1718 fnd_message.set_name ('GME', 'GME_SHOP_NON_WKG');
1719 --Bug3315440
1720 -- FND_MESSAGE.SET_TOKEN('PDATE', TO_CHAR(p_start_date,'DD-MON-YYYY HH24 MI SS'));
1721 --Bug4068469: removed trunc from the set_token call
1722 fnd_message.set_token ('PDATE'
1723 ,fnd_date.date_to_displaydt (p_start_date) );
1724 RAISE non_work_time;
1725 END IF;
1726 END IF;
1727
1728 IF p_end_date IS NOT NULL THEN
1729 l_is_working_day :=
1730 gmp_calendar_api.is_working_daytime
1731 (p_api_version => 1
1732 ,p_init_msg_list => TRUE
1733 ,p_calendar_code => g_mfg_calendar_code
1734 ,p_date => p_end_date
1735 ,p_ind => 1
1736 , -- End date
1737 x_return_status => x_return_code);
1738
1739 /* For now let's treat this condition as non-working day
1740 So the folowing code is commented. If the API returns
1741 Error or if the date is not working time, give same error
1742 IF x_return_code <> FND_API.G_RET_STS_SUCCESS THEN
1743 RAISE RESCHEDULE_ERROR;
1744 END IF; */
1745
1746 --- Warn the user that end date is a non working time
1747 IF NOT l_is_working_day THEN
1748 fnd_message.set_name ('GME', 'GME_SHOP_NON_WKG');
1749 --Bug3315440
1750 -- FND_MESSAGE.SET_TOKEN('PDATE', TO_CHAR(p_end_date,'DD-MON-YYYY HH24 MI SS'));
1751 --Bug4068469: removed trunc from the set_token call
1752 fnd_message.set_token ('PDATE'
1753 ,fnd_date.date_to_displaydt (p_end_date) );
1754 RAISE non_work_time;
1755 END IF;
1756 END IF;
1757 EXCEPTION
1758 WHEN non_work_time THEN
1759 x_return_code := 'C';
1760 x_error_msg := r_pad (fnd_message.get, 1000);
1761 WHEN reschedule_error THEN
1762 x_return_code := 'F';
1763 x_error_msg := r_pad (fnd_message.get, 1000);
1764 WHEN no_calendar_assigned THEN
1765 fnd_message.set_name ('GMP', 'GME_NO_CAL_ASSIGNED_TO_PLANT');
1766 x_return_code := 'F';
1767 x_error_msg := r_pad (fnd_message.get, 1000);
1768 WHEN OTHERS THEN
1769 --Bug4068469: set x_return_code and the x_error_msg
1770 x_return_code := 'U';
1771 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1772 x_error_msg := r_pad (fnd_message.get, 1000);
1773 END validate_reschedule_event;
1774
1775 /*======================================================================
1776 # For timezone changes.
1777 # Returns:
1778 # date : date in the client timezone
1779 # HISTORY
1780 # Bharati Satpute Bug3315440 21-JAN-2004
1781 #======================================================================*/
1782 FUNCTION date_to_clientdt (dateval DATE)
1783 RETURN DATE
1784 IS
1785 t_dateval DATE;
1786 tz_code VARCHAR2 (50);
1787 l_api_name CONSTANT VARCHAR2 (30) := 'date_to_clientDT';
1788 BEGIN
1789 t_dateval := dateval;
1790
1791 IF fnd_date.timezones_enabled THEN
1792 tz_code := fnd_date.client_timezone_code;
1793
1794 IF tz_code <> fnd_date.server_timezone_code THEN
1795 t_dateval :=
1796 fnd_timezones_pvt.adjust_datetime
1797 (dateval
1798 ,fnd_date.server_timezone_code
1799 ,fnd_date.client_timezone_code);
1800 END IF;
1801 END IF;
1802
1803 RETURN (t_dateval);
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1807 END date_to_clientdt;
1808 END;