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