1 PACKAGE BODY gme_reschedule_step_pvt AS
2 /* $Header: GMEVRSSB.pls 120.13 2011/04/21 08:37:46 apmishra ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_RESCHEDULE_STEP_PVT';
5
6 /*================================================================================
7 Procedure
8 Reschedule Step
9 Description
10 This particular procedure call reschedules the batch steps.
11 Parameters
12 p_batch_step_rec The batch step row to identify the step.
13 p_reschedule_preceding Whether to reschedule preceding dependent steps
14 p_reschedule_succeeding Whether to reschedule succeeding dependent steps
15 p_source_step_id Since this is recursive procedure, we need to know,
16 Where it is coming from. So this really is a table
17 which holds all the steps rescheduled so far.
18 p_use_workday_cal Whether to use workday calendar or not.
19 T - Use it
20 F - Do not use it
21 p_contiguity_override Whether to override contiguity check and reschedule
22 step anyway.
23 T - Override it
24 F - DO not override it.
25 x_batch_step_rec The batch step returned.
26 x_return_status outcome of the API call
27 S - Success
28 E - Error
29 U - Unexpected error
30 HISTORY
31 12-APR-2010 G. Muratore Bug 11925400
32 Add step status check to the where clause for 2 cursors.
33 ================================================================================*/
34 PROCEDURE reschedule_step (
35 p_batch_step_rec IN gme_batch_steps%ROWTYPE
36 ,p_source_step_id_tbl IN step_tab
37 ,p_contiguity_override IN VARCHAR2
38 ,p_reschedule_preceding IN VARCHAR2
39 ,p_reschedule_succeeding IN VARCHAR2
40 ,p_use_workday_cal IN VARCHAR2
41 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
42 ,x_return_status OUT NOCOPY VARCHAR2)
43 IS
44 /* Buffers for database reads/writes */
45 l_api_name CONSTANT VARCHAR2 (30) := 'RESCHEDULE_STEP';
46 l_batch_header_rec gme_batch_header%ROWTYPE;
47 l_batch_header2_rec gme_batch_header%ROWTYPE;
48 l_batch_step_rec gme_batch_steps%ROWTYPE;
49 l_batch_step_m_rec gme_batch_steps%ROWTYPE;
50 l_material_detail_id_tbl gme_common_pvt.number_tab;
51 l_rel_type NUMBER;
52 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
53 l_loop_count_get_material NUMBER;
54 --Bug#5606089
55 l_batch_step2_rec gme_batch_steps%ROWTYPE;
56 x_batch_step2_rec gme_batch_steps%ROWTYPE;
57 TYPE l_line_type_tbl_typ IS TABLE OF gme_material_details.line_type%TYPE
58 INDEX BY BINARY_INTEGER;
59
60 l_line_type_tbl l_line_type_tbl_typ;
61 l_material_date DATE;
62 /* Exception definitions */
63 batch_step_fetch_error EXCEPTION;
64 no_dates_passed EXCEPTION;
65 no_date_change EXCEPTION;
66 invalid_step_status EXCEPTION;
67 batch_header_fetch_error EXCEPTION;
68 save_data_error EXCEPTION;
69 child_step_resch_error EXCEPTION;
70 parent_step_resch_error EXCEPTION;
71 prev_step_err EXCEPTION;
72 mtl_dt_chg_error EXCEPTION;
73 date_overlap_error EXCEPTION;
74 trun_date_error EXCEPTION;
75 cal_dates_error EXCEPTION;
76 step_start_date_low EXCEPTION;
77 error_cont_period EXCEPTION;
78 error_non_contiguious EXCEPTION;
79 date_exceed_validity_rule EXCEPTION;
80 invalid_schedule_status EXCEPTION;
81 clear_chg_dates_error EXCEPTION;
82 /* Local variables */
83 l_return_status VARCHAR2 (1);
84 l_max_end_date DATE;
85 l_min_start_date DATE;
86 l_change BOOLEAN := FALSE;
87 l_doc_type VARCHAR2 (4);
88 l_cal_count NUMBER;
89 l_duration NUMBER;
90 l_date DATE;
91
92 --FPBug#4585491
93 l_R_count NUMBER := 0;
94 l_M_count NUMBER := 0;
95 l_B_count NUMBER := 0;
96
97 CURSOR cur_get_max (v_batch_id NUMBER)
98 IS
99 SELECT MAX (plan_cmplt_date)
100 FROM gme_batch_steps
101 WHERE batch_id = v_batch_id;
102
103 CURSOR cur_get_min (v_batch_id NUMBER)
104 IS
105 SELECT MIN (plan_start_date)
106 FROM gme_batch_steps
107 WHERE batch_id = v_batch_id;
108
109 CURSOR cur_get_material (v_batch_id NUMBER)
110 IS
111 SELECT material_detail_id, line_type
112 FROM gme_material_details
113 WHERE batch_id = v_batch_id;
114
115 -- Bug 11925400 - Add step status check to the where clause.
116 CURSOR cur_get_prec_steps (v_batch_id NUMBER, v_batchstep_id NUMBER)
117 IS
118 SELECT s.batchstep_id, d.dep_type, d.standard_delay
119 ,s.step_status
120 FROM gme_batch_step_dependencies d, gme_batch_steps s
121 WHERE d.batchstep_id = v_batchstep_id
122 AND s.batchstep_id = d.dep_step_id
123 AND s.batch_id = v_batch_id
124 AND d.batch_id = s.batch_id
125 AND s.step_status in (1, 2)
126 ORDER BY s.plan_start_date;
127
128 -- Bug 11925400 - Add step status check to the where clause.
129 CURSOR cur_get_succ_steps (v_batch_id NUMBER, v_batchstep_id NUMBER)
130 IS
131 SELECT d.batchstep_id, d.dep_type, d.standard_delay, s.step_status
132 FROM gme_batch_step_dependencies d, gme_batch_steps s
133 WHERE d.batchstep_id = s.batchstep_id
134 AND d.dep_step_id = v_batchstep_id
135 AND s.batch_id = v_batch_id
136 AND d.batch_id = s.batch_id
137 AND s.step_status in (1, 2)
138 ORDER BY s.plan_cmplt_date;
139
140 CURSOR cur_get_dep_step_times (v_batchstep_id NUMBER)
141 IS
142 SELECT batchstep_id, plan_start_date, plan_cmplt_date
143 FROM gme_batch_steps
144 WHERE batchstep_id = v_batchstep_id;
145
146 CURSOR cur_get_max_date_from_prev (
147 v_batch_id NUMBER
148 ,v_batchstep_id NUMBER)
149 IS
150 SELECT MAX ( DECODE (d.dep_type
151 ,1, s.plan_start_date
152 ,0, s.plan_cmplt_date)
153 + d.standard_delay / 24) max_date
154 FROM gme_batch_step_dependencies d, gme_batch_steps s
155 WHERE d.batchstep_id = v_batchstep_id
156 AND s.batchstep_id = d.dep_step_id
157 AND s.batch_id = v_batch_id
158 AND d.batch_id = s.batch_id;
159
160 CURSOR cur_is_charge_associated (v_batch_id NUMBER, v_batchstep_id NUMBER)
161 IS
162 SELECT 1
163 FROM DUAL
164 WHERE EXISTS (
165 SELECT 1
166 FROM gme_batch_step_charges
167 WHERE batch_id = v_batch_id
168 AND batchstep_id = v_batchstep_id);
169
170 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
171 l_dep_step_rec cur_get_succ_steps%ROWTYPE;
172 l_max_date DATE;
173 l_found BOOLEAN;
174 l_source_step_id_tbl step_tab;
175 l_calendar_code VARCHAR2 (10);
176 BEGIN
177 IF (NVL (g_debug, 0) IN
178 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
179 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
180 || 'Entering');
181 END IF;
182
183 /* Set the return status to success initially */
184 x_return_status := fnd_api.g_ret_sts_success;
185
186 /* Ensure that either a start_date or end_date has been passed. */
187 IF ( p_batch_step_rec.plan_start_date IS NULL
188 AND p_batch_step_rec.plan_cmplt_date IS NULL) THEN
189 RAISE no_dates_passed;
190 END IF;
191
192 x_batch_step_rec := p_batch_step_rec;
193
194 /* The current Step Status must be Pending */
195 IF NOT (x_batch_step_rec.step_status in (1,2)) THEN
196 RAISE invalid_step_status;
197 END IF;
198
199 l_batch_header_rec.batch_id := x_batch_step_rec.batch_id;
200 l_calendar_code := gme_common_pvt.g_calendar_code;
201
202 /* Initialize local batch header */
203 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
204 ,l_batch_header_rec) ) THEN
205 RAISE batch_header_fetch_error;
206 END IF;
207
208 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
209 gme_debug.put_line ( 'trying to reschedule steps to '
210 || TO_CHAR (p_batch_step_rec.plan_start_date
211 ,'DD-MON-YYYY HH24:MI:SS') );
212 gme_debug.put_line ( 'trying to reschedule steps cmp to '
213 || TO_CHAR (p_batch_step_rec.plan_cmplt_date
214 ,'DD-MON-YYYY HH24:MI:SS') );
215 gme_debug.put_line ( 'Going to check previous step of '
216 || TO_CHAR (x_batch_step_rec.batchstep_no) );
217 gme_debug.put_line ( 'p_source_step_id_tbl '
218 || TO_CHAR (p_source_step_id_tbl.COUNT) );
219 END IF;
220
221 IF (l_batch_header_rec.batch_type = 0) THEN
222 l_doc_type := 'PROD';
223 ELSE
224 l_doc_type := 'FPO';
225 END IF;
226
227 IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
228 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
229 gme_debug.put_line ( 'deleting transactions for '
230 || x_batch_step_rec.batchstep_id);
231 END IF;
232
233 DELETE FROM gme_resource_txns
234 WHERE doc_type = l_doc_type
235 AND doc_id = x_batch_step_rec.batch_id
236 AND line_id IN (
237 SELECT batchstep_resource_id
238 FROM gme_batch_step_resources
239 WHERE batch_id = x_batch_step_rec.batch_id
240 AND batchstep_id = x_batch_step_rec.batchstep_id);
241
242 -- Navin Added as part of Reschedule Batch/Step Build.
243 DELETE FROM gme_resource_txns_gtmp
244 WHERE doc_type = l_doc_type
245 AND doc_id = x_batch_step_rec.batch_id
246 AND line_id IN (
247 SELECT batchstep_resource_id
248 FROM gme_batch_step_resources
249 WHERE batch_id = x_batch_step_rec.batch_id
250 AND batchstep_id = x_batch_step_rec.batchstep_id);
251 END IF;
252
253 gme_create_step_pvt.calc_dates
254 (p_gme_batch_header_rec => l_batch_header_rec
255 ,p_use_workday_cal => p_use_workday_cal
256 ,p_contiguity_override => p_contiguity_override
257 ,p_return_status => l_return_status
258 ,p_step_id => p_batch_step_rec.batchstep_id
259 ,p_plan_start_date => p_batch_step_rec.plan_start_date
260 ,p_plan_cmplt_date => p_batch_step_rec.plan_cmplt_date);
261
262 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
263 gme_debug.put_line ( 'Return status from calc_dates '
264 || l_return_status);
265 END IF;
266
267 IF l_return_status <> x_return_status THEN
268 RAISE cal_dates_error;
269 END IF;
270
271 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec
272 ,x_batch_step_rec) ) THEN
273 RAISE batch_step_fetch_error;
274 END IF;
275
276 /* System always recalculate plan completion date
277 based on plan start date, but If user has passed in both the
278 dates then we also need to honor whatever user had passed in,
279 that means there will be truncation or gap
280 */
281 IF NVL (p_batch_step_rec.plan_cmplt_date
282 ,x_batch_step_rec.plan_cmplt_date) <
283 x_batch_step_rec.plan_cmplt_date THEN
284 l_batch_header_rec.plan_cmplt_date :=
285 p_batch_step_rec.plan_cmplt_date;
286 gme_reschedule_batch_pvt.truncate_date
287 (p_batch_header_rec => l_batch_header_rec
288 ,p_batchstep_id => p_batch_step_rec.batchstep_id
289 ,p_date => 1
290 ,x_return_status => l_return_status);
291 x_batch_step_rec.plan_cmplt_date := p_batch_step_rec.plan_cmplt_date;
292
293 IF l_return_status <> x_return_status THEN
294 RAISE trun_date_error;
295 END IF;
296 ELSIF NVL (p_batch_step_rec.plan_cmplt_date
297 ,x_batch_step_rec.plan_cmplt_date) >
298 x_batch_step_rec.plan_cmplt_date THEN
299 x_batch_step_rec.plan_cmplt_date := p_batch_step_rec.plan_cmplt_date;
300
301 IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec) ) THEN
302 RAISE save_data_error;
303 END IF;
304 END IF;
305 --Bug#4543875 (port 4416699) Update the changed step due date in the procedure calc_dates with the original due date
306 UPDATE gme_batch_steps
307 SET due_date = p_batch_step_rec.due_date
308 ,last_updated_by = gme_common_pvt.g_user_ident
309 ,last_update_date = gme_common_pvt.g_timestamp
310 ,last_update_login = gme_common_pvt.g_login_id
311 WHERE batch_id = p_batch_step_rec.batch_id
312 AND batchstep_id = p_batch_step_rec.batchstep_id;
313 --Bug#4543875
314
315 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
316 gme_debug.put_line ( g_pkg_name
317 || '.'
318 || l_api_name
319 || ':'
320 || 'Calling Save_all_data');
321 END IF;
322
323 l_batch_step_m_rec := x_batch_step_rec;
324 save_all_data (p_batch_step_rec
325 ,p_use_workday_cal
326 ,p_contiguity_override
327 ,x_batch_step_rec.plan_start_date
328 ,x_batch_step_rec.plan_cmplt_date
329 ,l_return_status);
330
331 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
332 gme_debug.put_line ( 'Came back from save all data with status '
333 || l_return_status);
334 END IF;
335
336 IF l_return_status <> x_return_status THEN
337 RAISE save_data_error;
338 END IF;
339
340 /*******************************************************************************
341 Since this step has been rescheduled now let's reschedule dependent steps
342 p_source_step_id_tbl tells us which step we are coming from in the tree.
343 Let's assume that we have steps dependency set-up as
344 10 - 20 - 30 and 10 - 40 All Finish to start.
345 Now user is rescheduling step 20 and dependent steps are getting rescheduled
346 as well. The way logic works is we are going to call this very procedure
347 for all the succeding steps to 20 and all precceding steps to 20.
348 That means we call reschedule_step (30, p_source_step_is as 20),
349 then in reschedule of 30 we will call succeding steps to 30 there is NONE.
350 SO we look at precceding steps to 30 and there is 20, but we do not want to
351 reschedule 20 again, because that is where we are coming from.
352 This is where p_source_step_id_tbl is used. If you follow the logic it will
353 look like the following:
354 20
355 30
356 10
357 40
358 *******************************************************************************/
359 -- IF l_batch_header_rec.enforce_step_dependency = 1 THEN
360 /* Enforce step dependency on or scheduleing set to TRUE */
361 -- rescheduling a batch step in WIP status when one or more steps is in pending status raises an error.
362 FOR i IN 1 .. p_source_step_id_tbl.COUNT LOOP
363 l_source_step_id_tbl (i) := p_source_step_id_tbl (i);
364 END LOOP;
365
366 /* Call the reschedule step recursive routine for the children of the current step */
367 /* Reschedule succeding steps */
368 IF p_reschedule_succeeding = fnd_api.g_true OR
369 l_batch_header_rec.enforce_step_dependency = 1 THEN
370 OPEN cur_get_succ_steps (p_batch_step_rec.batch_id
371 ,p_batch_step_rec.batchstep_id);
372
373 FETCH cur_get_succ_steps
374 INTO l_dep_step_rec;
375
376 WHILE cur_get_succ_steps%FOUND LOOP
377 l_found := FALSE;
378
379 IF (p_source_step_id_tbl.COUNT > 0) THEN
380 FOR i IN 1 .. p_source_step_id_tbl.COUNT LOOP
381 IF l_dep_step_rec.batchstep_id =
382 p_source_step_id_tbl (i) THEN
383 l_found := TRUE;
384 EXIT WHEN l_found;
385 END IF;
386 END LOOP;
387 END IF;
388
389 IF l_found = FALSE
390 AND l_dep_step_rec.step_status <> gme_common_pvt.g_step_wip THEN
391 /* Continue only if the succeeding step is not the same as the step which initiated the reschedule and is not WIP */
392 l_batch_step_rec.batchstep_id :=
393 l_dep_step_rec.batchstep_id;
394 l_batch_step_rec.batch_id := p_batch_step_rec.batch_id;
395 -- Rework 4543875 Pawan Kumar added for fetching the data for succeding step
396 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step_rec
397 ,l_batch_step_rec) ) THEN
398 RAISE batch_step_fetch_error;
399 END IF;
400 l_max_date :=
401 gme_create_step_pvt.get_max_step_date
402 (p_use_workday_cal => p_use_workday_cal
403 ,p_calendar_code => l_calendar_code
404 ,p_batchstep_id => l_batch_step_rec.batchstep_id
405 ,p_batch_id => l_batch_step_m_rec.batch_id
406 ,p_batch_start_date => l_batch_step_m_rec.plan_start_date);
407
408 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
409 gme_debug.put_line
410 ( 'MAX date '
411 || TO_CHAR (l_max_date
412 ,'DD-MON-YYYY HH24:MI:SS')
413 || ' plan start '
414 || TO_CHAR
415 (x_batch_step_rec.plan_start_date
416 ,'DD-MON-YYYY HH24:MI:SS') );
417 END IF;
418
419 l_batch_step_rec.plan_start_date := l_max_date;
420 l_batch_step_rec.plan_cmplt_date := NULL;
421 l_source_step_id_tbl (l_source_step_id_tbl.COUNT + 1) :=
422 p_batch_step_rec.batchstep_id;
423
424 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
425 gme_debug.put_line
426 ( g_pkg_name
427 || '.'
428 || l_api_name
429 || ':'
430 || 'Calling Reschedule_step for batchstep_id : '
431 || p_batch_step_rec.batchstep_id);
432 END IF;
433
434 reschedule_step
435 (p_batch_step_rec => l_batch_step_rec
436 ,p_source_step_id_tbl => l_source_step_id_tbl
437 ,p_contiguity_override => p_contiguity_override
438 ,p_reschedule_preceding => p_reschedule_preceding
439 ,p_reschedule_succeeding => p_reschedule_succeeding
440 ,p_use_workday_cal => p_use_workday_cal
441 ,x_batch_step_rec => x_batch_step_rec
442 ,x_return_status => l_return_status);
443
444 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
445 gme_debug.put_line
446 ( 'Back from reschedule_succeeding_steps with status '
447 || l_return_status
448 || ' for step ID '
449 || p_batch_step_rec.batchstep_id);
450 END IF;
451
452 IF l_return_status <> x_return_status THEN
453 CLOSE cur_get_succ_steps;
454
455 RAISE child_step_resch_error;
456 END IF;
457 END IF; /* l_found = FALSE */
458
459 FETCH cur_get_succ_steps
460 INTO l_dep_step_rec;
461 END LOOP; /* WHILE cur_get_succ_steps%FOUND */
462
463 CLOSE cur_get_succ_steps;
464 END IF; /* p_reschedule_succeeding = FND_API.G_TRUE */
465
466 /* Reschedule preceding steps */
467 IF p_reschedule_preceding = fnd_api.g_true OR
468 l_batch_header_rec.enforce_step_dependency = 1 THEN
469 OPEN cur_get_prec_steps (p_batch_step_rec.batch_id
470 ,p_batch_step_rec.batchstep_id);
471
472 FETCH cur_get_prec_steps
473 INTO l_dep_step_rec;
474
475 WHILE cur_get_prec_steps%FOUND LOOP
476 l_found := FALSE;
477
478 IF (p_source_step_id_tbl.COUNT > 0) THEN
479 FOR i IN 1 .. p_source_step_id_tbl.COUNT LOOP
480 IF l_dep_step_rec.batchstep_id =
481 p_source_step_id_tbl (i) THEN
482 l_found := TRUE;
483 EXIT WHEN l_found;
484 END IF;
485 END LOOP;
486 END IF;
487
488 IF l_found = FALSE
489 AND l_dep_step_rec.step_status <> gme_common_pvt.g_step_wip THEN
490 /* Continue only if the preceeding step is not the same as the step which initiated the reschedule and is not WIP */
491 l_batch_step_rec.batchstep_id :=
492 l_dep_step_rec.batchstep_id;
493 l_batch_step_rec.batch_id := p_batch_step_rec.batch_id;
494 -- Rework 4543875 Pawan Kumar added for fetching the data for succeding step
495 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step_rec
496 ,l_batch_step_rec) ) THEN
497 RAISE batch_step_fetch_error;
498 END IF;
499 /* Standard Delay should always be divided by 24 if used in date calculations */
500 IF p_use_workday_cal = fnd_api.g_false THEN
501 IF l_dep_step_rec.dep_type = 0 THEN
502 l_batch_step_rec.plan_cmplt_date :=
503 l_batch_step_m_rec.plan_start_date
504 - l_dep_step_rec.standard_delay / 24;
505 l_batch_step_rec.plan_start_date := NULL;
506 ELSE
507 l_batch_step_rec.plan_start_date :=
508 l_batch_step_m_rec.plan_start_date
509 - l_dep_step_rec.standard_delay / 24;
510 l_batch_step_rec.plan_cmplt_date := NULL;
511 END IF;
512 ELSE
513 /* Use workday calendar */
514 /* Pass in the plan start date
515 of the current step as plan completion date to this procedure as we
516 want to find out the start or completion date of the preceeding step */
517 IF l_dep_step_rec.standard_delay >= 0 THEN
518 gmp_calendar_api.get_contiguous_periods
519 (p_api_version => 1
520 ,p_init_msg_list => TRUE
521 ,p_start_date => NULL
522 ,p_end_date => l_batch_step_m_rec.plan_start_date
523 ,p_calendar_code => l_calendar_code
524 ,p_duration => l_dep_step_rec.standard_delay
525 ,p_output_tbl => l_contig_period_tbl
526 ,x_return_status => l_return_status);
527 l_cal_count := l_contig_period_tbl.COUNT;
528 l_date := l_contig_period_tbl (l_cal_count).start_date;
529 ELSE /* Standard delay is negative */
530 gmp_calendar_api.get_contiguous_periods
531 (p_api_version => 1
532 ,p_init_msg_list => TRUE
533 ,p_start_date => l_batch_step_m_rec.plan_start_date
534 ,p_end_date => NULL
535 ,p_calendar_code => l_calendar_code
536 ,p_duration => ABS
537 (l_dep_step_rec.standard_delay)
538 ,p_output_tbl => l_contig_period_tbl
539 ,x_return_status => l_return_status);
540 l_cal_count := l_contig_period_tbl.COUNT;
541 l_date := l_contig_period_tbl (l_cal_count).end_date;
542 END IF; /* l_dep_step_rec.standard_delay >= 0 */
543
544 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
545 gme_debug.put_line
546 ( 'Called to get the cmplt date for step prior to '
547 || l_batch_step_m_rec.batchstep_id
548 || ' '
549 || TO_CHAR (l_batch_step_m_rec.plan_start_date
550 ,'DD-MON-YYYY HH24:MI:SS')
551 || ' with duration of '
552 || l_dep_step_rec.standard_delay
553 || ' Got back '
554 || TO_CHAR (l_date, 'DD-MON-YYYY HH24:MI:SS') );
555 END IF;
556
557 IF l_return_status <> fnd_api.g_ret_sts_success THEN
558 NULL;
559 ELSE
560 IF l_dep_step_rec.dep_type = 0 THEN
561 l_batch_step_rec.plan_cmplt_date := l_date;
562 l_batch_step_rec.plan_start_date := NULL;
563 ELSE
564 l_batch_step_rec.plan_start_date := l_date;
565 l_batch_step_rec.plan_cmplt_date := NULL;
566 END IF;
567 END IF;
568 /* l_return_status <> FND_API.G_RET_STS_SUCCESS */
569 END IF; /* p_use_workday_cal = 0 */
570
571 l_source_step_id_tbl (l_source_step_id_tbl.COUNT + 1) :=
572 p_batch_step_rec.batchstep_id;
573
574 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
575 gme_debug.put_line
576 ( 'Calling reschedule for batchstep_id '
577 || l_batch_step_rec.batchstep_id);
578 END IF;
579
580 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
581 gme_debug.put_line
582 ( g_pkg_name
583 || '.'
584 || l_api_name
585 || ':'
586 || 'Calling Reschedule_step for batchstep_id : '
587 || l_batch_step_rec.batchstep_id);
588 END IF;
589
590 reschedule_step
591 (p_batch_step_rec => l_batch_step_rec
592 ,p_source_step_id_tbl => l_source_step_id_tbl
593 ,p_contiguity_override => p_contiguity_override
594 ,p_reschedule_preceding => p_reschedule_preceding
595 ,p_reschedule_succeeding => p_reschedule_succeeding
596 ,p_use_workday_cal => p_use_workday_cal
597 ,x_batch_step_rec => x_batch_step_rec
598 ,x_return_status => l_return_status);
599
600 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
601 gme_debug.put_line
602 ( 'Back from reschedule source steps with status '
603 || l_return_status
604 || ' Steps IDs COUNT '
605 || l_source_step_id_tbl.COUNT
606 || ' Source Step ID '
607 || l_batch_step_rec.batchstep_id);
608 END IF;
609
610 IF l_return_status <> x_return_status THEN
611 CLOSE cur_get_prec_steps;
612
613 RAISE child_step_resch_error;
614 END IF;
615 END IF; /* l_found = FALSE */
616
617 FETCH cur_get_prec_steps
618 INTO l_dep_step_rec;
619 END LOOP; /* cur_get_prec_steps%FOUND */
620
621 CLOSE cur_get_prec_steps;
622 END IF; /* p_reschedule_preceding = FND_API.G_TRUE */
623 -- END IF; /* l_batch_header_rec.enforce_step_dependency = 1 OR */
624
625 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
626 gme_debug.put_line ( 'Done with succ as well as prec for '
627 || p_batch_step_rec.batchstep_id);
628 END IF;
629
630 IF p_source_step_id_tbl.COUNT = 0 THEN
631 /* Update the batch dates etc. only when this procedure is in for the
632 main step and not during the recursive calls */
633 OPEN cur_get_max (l_batch_header_rec.batch_id);
634
635 FETCH cur_get_max
636 INTO l_max_end_date;
637
638 IF l_max_end_date <> l_batch_header_rec.plan_cmplt_date THEN
639 l_batch_header_rec.plan_cmplt_date := l_max_end_date;
640 l_change := TRUE;
641 END IF;
642
643 CLOSE cur_get_max;
644
645 OPEN cur_get_min (l_batch_header_rec.batch_id);
646
647 FETCH cur_get_min
648 INTO l_min_start_date;
649
650 CLOSE cur_get_min;
651
652 IF l_batch_header_rec.batch_status = 1 THEN
653 IF l_min_start_date <> l_batch_header_rec.plan_start_date THEN
654 l_batch_header_rec.plan_start_date := l_min_start_date;
655 l_change := TRUE;
656 END IF;
657 END IF;
658
659 IF l_batch_header_rec.batch_status = 2 THEN
660 IF l_batch_header_rec.enforce_step_dependency = 1 THEN
661 IF l_min_start_date < l_batch_header_rec.plan_start_date THEN
662 RAISE step_start_date_low;
663 END IF;
664 ELSE /* l_batch_header_rec.enforce_step_dependency = 1 */
665 IF l_min_start_date < l_batch_header_rec.plan_start_date THEN
666 gme_reschedule_batch_pvt.truncate_date
667 (p_batch_header_rec => l_batch_header_rec
668 ,p_date => 0
669 ,x_return_status => l_return_status);
670
671 IF l_return_status <> x_return_status THEN
672 RAISE trun_date_error;
673 END IF;
674 END IF;
675 /* l_min_start_date < l_batch_header_rec.plan_start_date */
676 END IF; /* l_batch_header_rec.enforce_step_dependency = 1 */
677 END IF; /* l_batch_header_rec.batch_status = 2 */
678
679 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
680 gme_debug.put_line
681 ( 'New min/max Dates are '
682 || TO_CHAR (l_min_start_date
683 ,'DD-MON-YYYY HH24:MI:SS')
684 || ' and '
685 || TO_CHAR (l_max_end_date
686 ,'DD-MON-YYYY HH24:MI:SS')
687 || ' and '
688 || ' Batch dates are '
689 || TO_CHAR (l_batch_header_rec.plan_start_date
690 ,'DD-MON-YYYY HH24:MI:SS')
691 || ' and '
692 || TO_CHAR (l_batch_header_rec.plan_cmplt_date
693 ,'DD-MON-YYYY HH24:MI:SS') );
694 END IF;
695
696 /* This is to check the contiguity in case of step_start date is passed only*/
697 IF p_use_workday_cal = fnd_api.g_true
698 AND p_contiguity_override = fnd_api.g_false THEN
699 l_duration :=
700 ( l_batch_header_rec.plan_cmplt_date
701 - l_batch_header_rec.plan_start_date)
702 * 24;
703 gmp_calendar_api.get_contiguous_periods
704 (p_api_version => 1
705 ,p_init_msg_list => TRUE
706 ,p_start_date => l_batch_header_rec.plan_start_date
707 ,p_end_date => NULL
708 ,p_calendar_code => l_calendar_code
709 ,p_duration => l_duration
710 ,p_output_tbl => l_contig_period_tbl
711 ,x_return_status => l_return_status);
712
713 IF (l_return_status <> x_return_status) THEN
714 RAISE error_cont_period;
715 END IF;
716
717 l_cal_count := l_contig_period_tbl.COUNT;
718
719 IF l_cal_count > 1 THEN
720 RAISE error_non_contiguious;
721 END IF;
722 END IF;
723
724 /* Since we update the batch dates in the code else where
725 we want to fetch the row, so that the timestamps are most
726 current, which is what is used to make sure that someone
727 else has not updated this batch during processing of this
728 program execution. That is why l_batch_header_rec is introduced.*/
729 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
730 ,l_batch_header2_rec) ) THEN
731 RAISE batch_header_fetch_error;
732 END IF;
733
734 l_batch_header2_rec.plan_start_date :=
735 l_batch_header_rec.plan_start_date;
736 l_batch_header2_rec.plan_cmplt_date :=
737 l_batch_header_rec.plan_cmplt_date;
738
739 IF NOT (gme_batch_header_dbl.update_row (l_batch_header2_rec) ) THEN
740 RAISE save_data_error;
741 END IF;
742
743 --Bug#5365527 added the validity rule check for LCF Batches
744 IF l_batch_header2_rec.recipe_validity_rule_id IS NOT NULL THEN
745 -- Checking of batch dates with validity rules dates after teh reschedule
746 IF NOT gme_common_pvt.check_validity_rule_dates
747 (l_batch_header2_rec.recipe_validity_rule_id
748 ,l_batch_header2_rec.plan_start_date
749 ,l_batch_header2_rec.plan_cmplt_date) THEN
750 x_return_status := fnd_api.g_ret_sts_error;
751 RAISE date_exceed_validity_rule;
752 END IF;
753 ELSE
754 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
755 gme_debug.put_line ( g_pkg_name
756 || '.'
757 || l_api_name
758 || ':'
759 || 'Do not Check Validity Rule Dates as this is LCF batch');
760 END IF;
761 END IF; /* recipe_validity_rule_id IS NOT NULL */
762 END IF; /* p_source_step_id_tbl.COUNT = 0 */
763
764 /* Re-query output batch step row */
765 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec
766 ,x_batch_step_rec) ) THEN
767 RAISE batch_step_fetch_error;
768 END IF;
769
770 -- Checking of batch dates with validity rules dates after teh reschedule
771 IF l_change = TRUE THEN
772 /* Now we have to update the transaction dates of pending transactions */
773 /* for the material lines which are not of step release type */
774 OPEN cur_get_material (x_batch_step_rec.batch_id);
775
776 FETCH cur_get_material
777 BULK COLLECT INTO l_material_detail_id_tbl, l_line_type_tbl;
778
779 l_loop_count_get_material := cur_get_material%ROWCOUNT;
780
781 CLOSE cur_get_material;
782
783 FOR i IN 1 .. l_loop_count_get_material LOOP
784 -- stamp manual and incremental with step dates as well...
785 l_rel_type :=
786 gme_common_pvt.is_material_auto_release
787 (l_material_detail_id_tbl (i) );
788 --Bug#5606089 Start. Added the following code.
789 /* SELECT batchstep_id INTO l_batch_step2_rec.batchstep_id FROM GME_BATCH_STEP_ITEMS
790 WHERE material_detail_id = l_material_detail_id_tbl(i);
791
792 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step2_rec
793 ,x_batch_step2_rec) ) THEN
794 RAISE batch_step_fetch_error;
795 END IF;*/
796 --Bug#5606089 End.
797
798 -- Modified the if condition.
799 IF ( gme_common_pvt.is_material_assoc_to_step (l_material_detail_id_tbl (i) ) = TRUE
800 AND
801 l_rel_type IN
802 (gme_common_pvt.g_mtl_manual_release
803 ,gme_common_pvt.g_mtl_incremental_release
804 ,gme_common_pvt.g_mtl_autobystep_release)) THEN
805
806 -- pawan kumar start bug 5929323 -- moved the fetch of batchstep_id only when step is assoc
807
808 SELECT batchstep_id INTO l_batch_step2_rec.batchstep_id FROM GME_BATCH_STEP_ITEMS
809 WHERE material_detail_id = l_material_detail_id_tbl(i);
810
811 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_step2_rec
812 ,x_batch_step2_rec) ) THEN
813 RAISE batch_step_fetch_error;
814 END IF;
815 -- pawan kumar end bug 5929323
816
817
818 IF l_line_type_tbl (i) = gme_common_pvt.g_line_type_ing THEN
819 -- Update the material_required_date with the associated plan_start_Date;
820 --Bug#5606089
821 l_material_date := x_batch_step2_rec.plan_start_date;
822 --l_material_date := x_batch_step_rec.plan_start_date;
823 ELSE
824 -- Update the material required date with the associated plan cmplt Date;
825 --Bug#5606089
826 l_material_date := x_batch_step2_rec.plan_cmplt_date;
827 --l_material_date := x_batch_step_rec.plan_cmplt_date;
828 END IF;
829 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
830 gme_debug.put_line
831 ( 'Calling Material Date Change for batchstep_id : '
832 || x_batch_step2_rec.batchstep_id
833 || ' Material_detail_id : '
834 || l_material_detail_id_tbl (i)
835 || 'for date'||l_material_date );
836 END IF;
837 gme_common_pvt.material_date_change
838 (p_material_detail_id => l_material_detail_id_tbl
839 (i)
840 ,p_material_date => l_material_date
841 ,x_return_status => l_return_status);
842
843 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
844 gme_debug.put_line
845 ( 'Came back from material_date_change with status for step '
846 || l_return_status);
847 END IF;
848
849 --FPBug#4585491 commented out the following lines and added new checks
850 /*IF l_return_status <> x_return_status THEN
851 RAISE mtl_dt_chg_error;
852 END IF; */
853
854 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
855 RAISE mtl_dt_chg_error;
856 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
857 RAISE fnd_api.g_exc_unexpected_error;
858 END IF;
859
860 ELSE
861 gme_debug.put_line('3');
862 -- Navin Added as part of Reschedule Batch/Step Build.
863 IF l_line_type_tbl (i) = gme_common_pvt.g_line_type_ing THEN
864 -- Update the material_required_date with the associated plan_start_Date;
865 l_material_date := l_batch_header_rec.plan_start_date;
866 ELSE
867 -- Update the material required date with the associated plan cmplt Date;
868 l_material_date := l_batch_header_rec.plan_cmplt_date;
869 END IF;
870
871 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
872 gme_debug.put_line
873 ( 'Calling Material Date Change for batch_id : '
874 || l_batch_header_rec.batch_id
875 || ' Material_detail_id : '
876 || l_material_detail_id_tbl (i)
877 || 'for date'||l_material_date );
878 END IF;
879
880 gme_common_pvt.material_date_change
881 (p_material_detail_id => l_material_detail_id_tbl
882 (i)
883 ,p_material_date => l_material_date
884 ,x_return_status => l_return_status);
885
886 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
887 gme_debug.put_line
888 ( 'Came back from material_date_change with status '
889 || l_return_status);
890 END IF;
891 --FPBug#4585491 Begin
892 /*IF l_return_status <> x_return_status THEN
893 RAISE mtl_dt_chg_error;
894 END IF;*/
895 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
896 RAISE mtl_dt_chg_error;
897 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
898 RAISE fnd_api.g_exc_unexpected_error;
899 END IF;
900 END IF; /* If material is not associted to steps */
901 /*
902 The above material_date_change returns different status as described below
903 R: When reservations are deleted for a material line
904 M: When MO Allocations are deleted for a material line
905 B: When Both reservations and material lines are deleted for a material line
906 */
907 IF x_return_status = 'R' THEN
908 l_R_count := l_R_count + 1;
909 ELSIF x_return_status = 'M' THEN
910 l_M_count := l_M_count + 1;
911 ELSIF x_return_status = 'B' THEN
912 l_B_count := l_B_count + 1;
913 END IF;
914 --FPBug#4585491 End
915 END LOOP; /* FOR i IN 1..l_loop_count_get_material */
916 END IF; /* l_change = TRUE */
917
918 --FPBug#4585491 Begin
919 IF (l_B_count > 0) OR (l_R_count > 0 AND l_M_count > 0) THEN
920 --atleast for one material line MO allocations and reservations are deleted
921 gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
922 ELSIF l_R_count > 0 THEN
923 ----atleast for one material line reservations are deleted
924 gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
925 ELSIF l_M_count > 0 THEN
926 ----atleast for one material line MO allocations are deleted
927 gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
928 END IF;
929 x_return_status := fnd_api.g_ret_sts_success;
930 --FPBug#4585491 End
931
932 --Clearing the dates of the associated charges.
933 OPEN cur_is_charge_associated (l_batch_header_rec.batch_id
934 ,p_batch_step_rec.batchstep_id);
935
936 FETCH cur_is_charge_associated
937 INTO l_cur_is_charge_associated;
938
939 IF cur_is_charge_associated%FOUND THEN
940 CLOSE cur_is_charge_associated;
941
942 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
943 gme_debug.put_line ( g_pkg_name
944 || '.'
945 || l_api_name
946 || ':'
947 || 'Calling Clear charge dates for Batch_id:'
948 || l_batch_header_rec.batch_id
949 || ' Batchstep_id: '
950 || p_batch_step_rec.batchstep_id);
951 END IF;
952
953 gme_batch_step_chg_pvt.clear_charge_dates
954 (p_batch_id => l_batch_header_rec.batch_id
955 ,p_batchstep_id => p_batch_step_rec.batchstep_id
956 ,x_return_status => l_return_status);
957
958 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
959 gme_debug.put_line
960 ( 'Came back from Clear charge dates with status '
961 || l_return_status);
962 END IF;
963
964 IF l_return_status <> x_return_status THEN
965 RAISE clear_chg_dates_error;
966 END IF;
967 ELSE
968 CLOSE cur_is_charge_associated;
969 END IF;
970
971 --OM-GME integration - NOTIFY_CSR Action (Batch completion date may have changed)
972 /*Punit Kumar
973 gme_trans_engine_pvt.inform_om
974 ( p_action => 'NOTIFY_CSR'
975 , p_trans_id => NULL
976 , p_trans_id_reversed => NULL
977 , p_gme_batch_hdr => l_batch_header_rec
978 , p_gme_matl_dtl => NULL
979 );
980 */
981 IF (NVL (g_debug, 0) IN
982 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
983 gme_debug.put_line ( g_pkg_name
984 || '.'
985 || l_api_name
986 || ':'
987 || 'Exiting with '
988 || x_return_status);
989 END IF;
990 EXCEPTION
991 WHEN batch_step_fetch_error THEN
992 x_return_status := fnd_api.g_ret_sts_error;
993 gme_common_pvt.log_message ('GME_API_BATCH_STEP_FETCH_ERR');
994 WHEN no_dates_passed THEN
995 x_return_status := fnd_api.g_ret_sts_error;
996 gme_common_pvt.log_message ('GME_API_RESCH_STEP_NO_DATES');
997 WHEN no_date_change THEN
998 x_return_status := fnd_api.g_ret_sts_error;
999 gme_common_pvt.log_message ('GME_API_RESCH_STEP_NO_DATE_CHG');
1000 WHEN invalid_step_status THEN
1001 x_return_status := fnd_api.g_ret_sts_error;
1002 gme_common_pvt.log_message ('GME_API_INV_STEP_STAT_RESCH');
1003 WHEN invalid_schedule_status THEN
1004 x_return_status := fnd_api.g_ret_sts_error;
1005 gme_common_pvt.log_message ('GME_API_INV_STEP_RESCH');
1006 WHEN date_overlap_error THEN
1007 x_return_status := fnd_api.g_ret_sts_error;
1008 gme_common_pvt.log_message ('GME_STEP_OVERLAP_ERROR');
1009 WHEN batch_header_fetch_error THEN
1010 x_return_status := fnd_api.g_ret_sts_error;
1011 gme_common_pvt.log_message ('GME_API_BATCH_FETCH_ERROR');
1012 WHEN date_exceed_validity_rule THEN
1013 x_return_status := fnd_api.g_ret_sts_error;
1014 WHEN step_start_date_low THEN
1015 x_return_status := fnd_api.g_ret_sts_error;
1016 gme_common_pvt.log_message ('GME_ESD_PLAN_DATE');
1017 WHEN error_cont_period THEN
1018 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1019 gme_debug.put_line ('Contiguity period ... _failed');
1020 END IF;
1021
1022 x_return_status := l_return_status;
1023 WHEN error_non_contiguious THEN
1024 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1025 gme_debug.put_line ('Contiguity period ... not found');
1026 END IF;
1027
1028 gme_common_pvt.log_message ('GME_NON_CONTIGUOUS_TIME');
1029 x_return_status := 'C';
1030 WHEN save_data_error OR mtl_dt_chg_error THEN
1031 x_return_status := l_return_status;
1032 WHEN child_step_resch_error THEN
1033 x_return_status := l_return_status;
1034 WHEN cal_dates_error THEN
1035 x_return_status := l_return_status;
1036 WHEN parent_step_resch_error THEN
1037 x_return_status := l_return_status;
1038 WHEN prev_step_err THEN
1039 x_return_status := l_return_status;
1040 WHEN clear_chg_dates_error THEN
1041 x_return_status := fnd_api.g_ret_sts_error;
1042 WHEN OTHERS THEN
1043 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1044 gme_debug.put_line (l_api_name || ':OTHERS ' || SQLERRM);
1045 END IF;
1046
1047 x_return_status := fnd_api.g_ret_sts_unexp_error;
1048 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1049 END reschedule_step;
1050
1051 /**************************************************************************************
1052 Procedure
1053 save_all_data
1054 Description
1055 This particular procedure updates all necessary tables.
1056 Parameters
1057 p_batch_step_rec The batch step row to identify the step.
1058 p_diff Duration used to reschedule the start date with.
1059 p_diff_end Duration used to reschedule the end date with.
1060 x_return_status outcome of the API call
1061 S - Success
1062 E - Error
1063 U - Unexpected error
1064 HISTORY
1065 G.Kelly 22-Feb-2002 Bug - Rewrote the code.
1066 A Newbury 05-Aug-2003 B3045672 Modified cursor to include manual and incremental
1067 Pawan Kuamr 01-26-2004 For rework of bug 3010444
1068 ***************************************************************************************/
1069 PROCEDURE save_all_data (
1070 p_batch_step_rec IN gme_batch_steps%ROWTYPE
1071 ,p_use_workday_cal IN VARCHAR2
1072 ,p_contiguity_override IN VARCHAR2
1073 ,p_start_date IN DATE
1074 ,p_end_date IN DATE
1075 ,x_return_status OUT NOCOPY VARCHAR2)
1076 IS
1077 l_api_name CONSTANT VARCHAR2 (30) := 'SAVE_ALL_DATA';
1078 l_material_detail_id_tbl gme_common_pvt.number_tab;
1079 l_phantom_ids gme_common_pvt.number_tab;
1080 l_return_status VARCHAR2 (1);
1081 l_batch_header_rec gme_batch_header%ROWTYPE;
1082 l_in_batch_header_rec gme_batch_header%ROWTYPE;
1083 x_batch_header_rec gme_batch_header%ROWTYPE;
1084
1085 TYPE l_line_type_tbl_typ IS TABLE OF gme_material_details.line_type%TYPE
1086 INDEX BY BINARY_INTEGER;
1087
1088 l_line_type_tbl l_line_type_tbl_typ;
1089 l_material_date DATE;
1090 l_loop_count_get_material NUMBER;
1091 /* Exception definitions */
1092 resched_phant_error EXCEPTION;
1093 mtl_dt_chg_error EXCEPTION;
1094 invalid_batch EXCEPTION;
1095 invalid_prior_dates EXCEPTION;
1096
1097 --FPBug#4585491
1098 l_R_count NUMBER := 0;
1099 l_M_count NUMBER := 0;
1100 l_B_count NUMBER := 0;
1101
1102 CURSOR cur_get_material (v_batch_id NUMBER, v_batchstep_id NUMBER)
1103 IS
1104 SELECT material_detail_id, line_type
1105 FROM gme_material_details det
1106 WHERE batch_id = v_batch_id
1107 AND release_type IN (1, 2, 3)
1108 AND EXISTS (
1109 SELECT 1
1110 FROM gme_batch_step_items
1111 WHERE batch_id = v_batch_id
1112 AND batchstep_id = v_batchstep_id
1113 AND material_detail_id = det.material_detail_id);
1114 BEGIN
1115 IF (NVL (g_debug, 0) IN
1116 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
1117 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1118 || 'Entering');
1119 END IF;
1120
1121 /* Initialize return status to success */
1122 x_return_status := fnd_api.g_ret_sts_success;
1123 l_batch_header_rec.batch_id := p_batch_step_rec.batch_id;
1124
1125 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
1126 ,x_batch_header_rec) ) THEN
1127 RAISE invalid_batch;
1128 END IF;
1129
1130 OPEN cur_get_material (p_batch_step_rec.batch_id
1131 ,p_batch_step_rec.batchstep_id);
1132
1133 FETCH cur_get_material
1134 BULK COLLECT INTO l_material_detail_id_tbl, l_line_type_tbl;
1135
1136 l_loop_count_get_material := cur_get_material%ROWCOUNT;
1137
1138 CLOSE cur_get_material;
1139
1140 FOR i IN 1 .. l_loop_count_get_material LOOP
1141 -- Navin Added as part of Reschedule Batch/Step Build.
1142 IF l_line_type_tbl (i) = gme_common_pvt.g_line_type_ing THEN
1143 -- Update the material_required_date with the associated plan_start_Date;
1144 l_material_date := p_start_date;
1145 ELSE
1146 -- Update the material required date with the associated plan cmplt Date;
1147 l_material_date := p_end_date;
1148 END IF;
1149
1150 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
1151 gme_debug.put_line
1152 ( 'Calling Material Date Change for batch_id : '
1153 || l_batch_header_rec.batch_id
1154 || ' Material_detail_id : '
1155 || l_material_detail_id_tbl (i) );
1156 END IF;
1157
1158 gme_common_pvt.material_date_change
1159 (p_material_detail_id => l_material_detail_id_tbl
1160 (i)
1161 ,p_material_date => l_material_date
1162 ,x_return_status => l_return_status);
1163
1164 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1165 gme_debug.put_line
1166 ( 'Came back from Material Date Change with status '
1167 || l_return_status);
1168 END IF;
1169 --FPBug#4585491 Begin
1170 /*IF l_return_status <> x_return_status THEN
1171 RAISE mtl_dt_chg_error;
1172 END IF;*/
1173
1174 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1175 RAISE mtl_dt_chg_error;
1176 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1177 RAISE fnd_api.g_exc_unexpected_error;
1178 END IF;
1179
1180 /*
1181 The above material_date_change returns different status as described below
1182 R: When reservations are deleted for a material line
1183 M: When MO Allocations are deleted for a material line
1184 B: When Both reservations and material lines are deleted for a material line
1185 */
1186 IF x_return_status = 'R' THEN
1187 l_R_count := l_R_count + 1;
1188 ELSIF x_return_status = 'M' THEN
1189 l_M_count := l_M_count + 1;
1190 ELSIF x_return_status = 'B' THEN
1191 l_B_count := l_B_count + 1;
1192 END IF;
1193 --FPBug#4585491 End
1194 END LOOP; /* FOR i IN 1..l_loop_count_get_material */
1195
1196 --FPBug#4585491 Begin
1197 IF (l_B_count > 0) OR (l_R_count > 0 AND l_M_count > 0) THEN
1198 --atleast for one material line MO allocations and reservations are deleted
1199 gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
1200 ELSIF l_R_count > 0 THEN
1201 ----atleast for one material line reservations are deleted
1202 gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
1203 ELSIF l_M_count > 0 THEN
1204 ----atleast for one material line MO allocations are deleted
1205 gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
1206 END IF;
1207 x_return_status := fnd_api.g_ret_sts_success;
1208 --FPBug#4585491 End
1209
1210
1211 /* Now we have to reschedule the batch associated with the step */
1212 /* lines of release type step release */
1213 /* All means manual, incremental and auto by step... NOT AUTO */
1214 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1215 gme_debug.put_line ('Calling Fetch Step Phantoms.');
1216 END IF;
1217
1218 gme_phantom_pvt.fetch_step_phantoms
1219 (p_batch_id => p_batch_step_rec.batch_id
1220 ,p_batchstep_id => p_batch_step_rec.batchstep_id
1221 ,p_all_release_type_assoc => 1
1222 ,x_phantom_ids => l_phantom_ids
1223 ,x_return_status => l_return_status);
1224
1225 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1226 gme_debug.put_line
1227 ( 'Came back from Fetch Step Phantoms with status '
1228 || l_return_status);
1229 END IF;
1230
1231 IF l_return_status <> x_return_status THEN
1232 RAISE resched_phant_error;
1233 END IF;
1234
1235 FOR i IN 1 .. l_phantom_ids.COUNT LOOP
1236 l_batch_header_rec.batch_id := l_phantom_ids (i);
1237 -- Sending the completion date only as start date for the phantom batch
1238 l_batch_header_rec.plan_cmplt_date :=
1239 p_batch_step_rec.plan_start_date;
1240 l_in_batch_header_rec := l_batch_header_rec;
1241 gme_reschedule_batch_pvt.reschedule_batch
1242 (p_batch_header_rec => l_in_batch_header_rec
1243 ,p_use_workday_cal => p_use_workday_cal
1244 ,p_contiguity_override => p_contiguity_override
1245 ,x_batch_header_rec => l_batch_header_rec
1246 ,x_return_status => l_return_status);
1247
1248 IF l_return_status <> x_return_status THEN
1249 RAISE resched_phant_error;
1250 END IF;
1251 END LOOP; /* i IN 1 .. l_phantom_ids.COUNT */
1252
1253 IF (NVL (g_debug, 0) IN
1254 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
1255 gme_debug.put_line ( g_pkg_name
1256 || '.'
1257 || l_api_name
1258 || ':'
1259 || 'Exiting with '
1260 || x_return_status);
1261 END IF;
1262 EXCEPTION
1263 WHEN resched_phant_error OR mtl_dt_chg_error OR invalid_batch THEN
1264 x_return_status := l_return_status;
1265 WHEN invalid_prior_dates THEN
1266 x_return_status := fnd_api.g_ret_sts_error;
1267 WHEN OTHERS THEN
1268 x_return_status := fnd_api.g_ret_sts_unexp_error;
1269 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1270
1271 IF (NVL (g_debug, 0) > 0) THEN
1272 gme_debug.put_line ( g_pkg_name
1273 || '.'
1274 || l_api_name
1275 || ':'
1276 || ' OTHERS:'
1277 || SQLERRM);
1278 END IF;
1279 END save_all_data;
1280 END gme_reschedule_step_pvt;
[Home] [Help]