1 PACKAGE BODY gme_reschedule_batch_pvt AS
2 /* $Header: GMEVRSBB.pls 120.9 2006/09/28 17:20:56 pxkumar ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_RESCHEDULE_BATCH_PVT';
5
6 /***********************************************************************************
7 Procedure
8 reschedule_batch
9 Description
10 This particular procedure is used to reschedule the batch and the associated steps.
11 Parameters
12 p_batch_header_rec The batch header record.
13 x_batch_header_rec Output batch record.
14 p_use_workday_cal Whether to use workday calendar or not.
15 T - Use it
16 F - Do not use it
17 p_contiguity_override Whether to override contiguity check and reschedule
18 step anyway.
19 T - Override it
20 F - DO not override it.
21 x_return_status outcome of the API call
22 S - Success
23 E - Error
24 U - Unexpected error
25 ***********************************************************************************/
26 PROCEDURE reschedule_batch (
27 p_batch_header_rec IN gme_batch_header%ROWTYPE
28 ,p_use_workday_cal IN VARCHAR2
29 ,p_contiguity_override IN VARCHAR2
30 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
31 ,x_return_status OUT NOCOPY VARCHAR2)
32 IS
33 l_api_name CONSTANT VARCHAR2 (30) := 'RESCHEDULE_BATCH';
34
35 /* Collections for details etc */
36 TYPE l_line_type_tbl_typ IS TABLE OF gme_material_details.line_type%TYPE
37 INDEX BY BINARY_INTEGER;
38
39 l_line_type_tbl l_line_type_tbl_typ;
40 l_material_date DATE;
41 l_loop_count_get_material NUMBER;
42 l_material_detail_ids gme_common_pvt.number_tab;
43 l_material_detail_rec gme_material_details%ROWTYPE;
44 l_batch_header_rec gme_batch_header%ROWTYPE;
45 l_in_batch_header_rec gme_batch_header%ROWTYPE;
46 /* Local variables */
47 l_return_status VARCHAR2 (1);
48 l_rsrc_trans_count NUMBER := 0;
49 max_cmplt_date DATE;
50 min_start_date DATE;
51 l_plan_date DATE;
52 l_rel_type NUMBER;
53 l_doc_type VARCHAR2 (4);
54 l_prim_item_um VARCHAR2 (3);
55 l_prim_prod_qty NUMBER;
56 l_prim_prod_um VARCHAR2 (3);
57 l_no_prod_rule_found BOOLEAN;
58 l_prim_prod_found BOOLEAN;
59 l_recipe_validity_rule_rec gmd_recipe_validity_rules%ROWTYPE;
60 l_duration NUMBER;
61 l_cal_count NUMBER;
62 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
63 l_temp_qty NUMBER;
64 l_item_id NUMBER;
65 l_item_no VARCHAR2 (32);
66 l_from_uom VARCHAR2 (3);
67 l_to_uom VARCHAR2 (3);
68 l_date DATE;
69 l_start_date DATE;
70 l_cmplt_date DATE;
71 l_calendar_code VARCHAR2 (10);
72 l_contiguous_ind NUMBER;
73
74 CURSOR cur_get_status_type (v_validity_rule_id NUMBER)
75 IS
76 SELECT status_type
77 FROM gmd_status gs, gmd_recipe_validity_rules grvr
78 WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
79 AND status_code = grvr.validity_rule_status;
80
81 l_status_type gmd_status.status_type%TYPE;
82
83 CURSOR cur_get_valid_dates (v_validity_rule_id NUMBER)
84 IS
85 SELECT start_date, end_date
86 FROM gmd_recipe_validity_rules
87 WHERE recipe_validity_rule_id = v_validity_rule_id;
88
89 l_valid_dates_rec cur_get_valid_dates%ROWTYPE;
90
91 CURSOR cur_get_material (v_batch_id NUMBER)
92 IS
93 SELECT material_detail_id, line_type
94 FROM gme_material_details
95 WHERE batch_id = v_batch_id;
96
97 CURSOR cur_get_phant (v_batch_id NUMBER)
98 IS
99 SELECT phantom_id, m.material_detail_id, release_type, line_type
100 ,i.batchstep_id
101 FROM gme_material_details m, gme_batch_step_items i
102 WHERE m.batch_id = v_batch_id
103 AND NVL (phantom_id, 0) > 0
104 AND m.batch_id = i.batch_id(+)
105 AND m.material_detail_id = i.material_detail_id(+);
106
107 CURSOR cur_get_step_date (v_batch_id NUMBER, v_batchstep_id NUMBER)
108 IS
109 SELECT plan_start_date
110 FROM gme_batch_steps
111 WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
112
113 CURSOR cur_batch_step (
114 v_batch_id NUMBER
115 ,v_plan_cmplt_date DATE
116 ,l_diff NUMBER)
117 IS
118 SELECT batchstep_no, plan_cmplt_date
119 FROM gme_batch_steps
120 WHERE batch_id = v_batch_id
121 AND (plan_cmplt_date + l_diff) > v_plan_cmplt_date;
122
123 CURSOR recipe_validity_rule_cursor (
124 p_recipe_validity_rule_id gme_batch_header.recipe_validity_rule_id%TYPE)
125 IS
126 SELECT *
127 FROM gmd_recipe_validity_rules
128 WHERE recipe_validity_rule_id = NVL (p_recipe_validity_rule_id, -1);
129
130 CURSOR primary_prod_from_batch_cursor(v_batch_id IN NUMBER)
131 IS
132 SELECT primary_item_id
133 FROM wip_entities
134 WHERE wip_entity_id = v_batch_id;
135
136 CURSOR get_prim_prod (
137 v_batch_id gme_batch_header.batch_id%TYPE
138 ,v_item_id gme_material_details.inventory_item_id%TYPE)
139 IS
140 SELECT plan_qty, dtl_um
141 FROM gme_material_details
142 WHERE batch_id = v_batch_id
143 AND inventory_item_id = v_item_id
144 AND line_type = 1
145 ORDER BY line_no ASC;
146
147 CURSOR cur_item_no (
148 p_item_id gme_material_details.inventory_item_id%TYPE
149 ,p_organization_id gme_material_details.organization_id%TYPE)
150 IS
151 SELECT segment1
152 FROM mtl_system_items
153 WHERE organization_id = p_organization_id
154 AND inventory_item_id = p_item_id;
155
156 CURSOR cur_is_charge_associated (v_batch_id NUMBER)
157 IS
158 SELECT 1
159 FROM DUAL
160 WHERE EXISTS (SELECT 1
161 FROM gme_batch_step_charges
162 WHERE batch_id = v_batch_id);
163
164 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
165 /* Exceptions */
166 cal_dates_error EXCEPTION;
167 invalid_batch EXCEPTION;
168 null_dates EXCEPTION;
169 error_load_trans EXCEPTION;
170 mtl_dt_chg_error EXCEPTION;
171 resched_phant_fail EXCEPTION;
172 date_is_less_than_actual EXCEPTION;
173 cmplt_less_start EXCEPTION;
174 cannot_reschedule_start EXCEPTION;
175 batch_header_fetch_error EXCEPTION;
176 trun_date_error EXCEPTION;
177 date_exceed_validity_rule EXCEPTION;
178 error_cont_period EXCEPTION;
179 error_non_contiguious EXCEPTION;
180 conversion_failure EXCEPTION;
181 invalid_validity_rule EXCEPTION;
182 clear_chg_dates_error EXCEPTION;
183
184 --FPBug#4585491
185 l_R_count NUMBER := 0;
186 l_M_count NUMBER := 0;
187 l_B_count NUMBER := 0;
188 BEGIN
189 IF (NVL (g_debug, 0) IN
190 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
191 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
192 || 'Entering');
193 END IF;
194
195 /* Set the return status tosuccess initially */
196 x_return_status := fnd_api.g_ret_sts_success;
197
198 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
199 gme_debug.put_line ( 'Trying to reschedule to '
200 || TO_CHAR (p_batch_header_rec.plan_start_date
201 ,'MM/DD/YYYY HH24:MI:SS')
202 || ' and '
203 || TO_CHAR (p_batch_header_rec.plan_cmplt_date
204 ,'MM/DD/YYYY HH24:MI:SS') );
205 END IF;
206
207 /* Initialize out batch header record */
208 IF (p_batch_header_rec.plan_start_date IS NULL)
209 AND (p_batch_header_rec.plan_cmplt_date IS NULL) THEN
210 RAISE null_dates;
211 END IF;
212
213 IF p_batch_header_rec.plan_cmplt_date <
214 p_batch_header_rec.plan_start_date THEN
215 gme_common_pvt.log_message ('PM_PLAN_END_DATE_ERR');
216 RAISE cmplt_less_start;
217 END IF;
218
219 IF NOT (gme_batch_header_dbl.fetch_row (p_batch_header_rec
220 ,x_batch_header_rec) ) THEN
221 RAISE batch_header_fetch_error;
222 END IF;
223
224 /* Don't allow the Batch to be Rescheduled if the Batch Status is */
225 /* Cancelled or Closed or Certified */
226 IF (x_batch_header_rec.batch_status NOT IN (1, 2) ) THEN
227 RAISE invalid_batch;
228 END IF;
229
230 l_calendar_code := gme_common_pvt.g_calendar_code;
231
232 IF x_batch_header_rec.batch_status = 2 THEN /* Batch is already WIP */
233 IF p_batch_header_rec.plan_start_date <>
234 x_batch_header_rec.plan_start_date THEN
235 gme_common_pvt.log_message ('GME_CANT_RESCH_START');
236 RAISE cannot_reschedule_start;
237 END IF;
238
239 IF p_batch_header_rec.plan_cmplt_date <
240 x_batch_header_rec.actual_start_date THEN
241 -- Updated the DATE1 as plan_cmplt_date and DATE2 as actual_start_date
242 gme_common_pvt.log_message
243 ('GME_INVALID_DATE_RANGE'
244 ,'DATE1'
245 ,fnd_date.date_to_displaydt (p_batch_header_rec.plan_cmplt_date)
246 ,'DATE2'
247 ,fnd_date.date_to_displaydt
248 (x_batch_header_rec.actual_start_date) );
249 RAISE date_is_less_than_actual;
250 END IF;
251 END IF;
252 /* Check validity rule if not LCF batch */
253 IF x_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
254
255
256 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
257 gme_debug.put_line ( g_pkg_name
258 || '.'
259 || l_api_name
260 || ':'
261 || 'Calling Check Validity Rule Dates');
262 END IF;
263
264 /* Check wether the dates requested for rescheduling are within the ranges of the recipe validity rule */
265 IF NOT gme_common_pvt.check_validity_rule_dates
266 (x_batch_header_rec.recipe_validity_rule_id
267 ,p_batch_header_rec.plan_start_date
268 ,p_batch_header_rec.plan_cmplt_date
269 ,x_batch_header_rec) THEN
270 x_return_status := fnd_api.g_ret_sts_error;
271 RAISE date_exceed_validity_rule;
272 END IF;
273
274 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
275 gme_debug.put_line
276 ( 'Came back from Check Validity Rule Dates. Trying to reschedule From '
277 || TO_CHAR (x_batch_header_rec.plan_start_date
278 ,'MM/DD/YYYY HH24:MI:SS')
279 || ' and '
280 || TO_CHAR (x_batch_header_rec.plan_cmplt_date
281 ,'MM/DD/YYYY HH24:MI:SS') );
282 END IF;
283 ELSE /* x_batch_header_rec.recipe_validity_rule_id IS NULL */
284 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
285 gme_debug.put_line ( g_pkg_name
286 || '.'
287 || l_api_name
288 || ':'
289 || 'Do not Check Validity Rule Dates as this is LCF batch');
290 END IF;
291 END IF; /* IF x_batch_header_rec.recipe_validity_rule_id IS NOT NULL */
292
293 IF (x_batch_header_rec.batch_type = 0) THEN
294 l_doc_type := 'PROD';
295 ELSE
296 l_doc_type := 'FPO';
297 END IF;
298
299 IF (NVL (g_debug, 0) <= gme_debug.g_log_procedure) THEN
300 gme_debug.put_line ('Delete all the pending resource transactions');
301 END IF;
302
303 -- delete all the pending resource transactions
304 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
305 DELETE FROM gme_resource_txns
306 WHERE doc_id = x_batch_header_rec.batch_id
307 AND doc_type = l_doc_type
308 AND line_id IN (
309 SELECT batchstep_resource_id
310 FROM gme_batch_step_resources
311 WHERE batch_id = x_batch_header_rec.batch_id
312 AND batchstep_id IN (
313 SELECT batchstep_id
314 FROM gme_batch_steps
315 WHERE batch_id =
316 x_batch_header_rec.batch_id
317 AND step_status = 1) );
318 DELETE FROM gme_resource_txns_gtmp
319 WHERE doc_id = x_batch_header_rec.batch_id
320 AND doc_type = l_doc_type
321 AND line_id IN (
322 SELECT batchstep_resource_id
323 FROM gme_batch_step_resources
324 WHERE batch_id = x_batch_header_rec.batch_id
325 AND batchstep_id IN (
326 SELECT batchstep_id
327 FROM gme_batch_steps
328 WHERE batch_id =
329 x_batch_header_rec.batch_id
330 AND step_status = 1) );
331 END IF;
332
333 l_batch_header_rec := x_batch_header_rec;
334 l_batch_header_rec.plan_start_date := p_batch_header_rec.plan_start_date;
335 l_batch_header_rec.plan_cmplt_date := p_batch_header_rec.plan_cmplt_date;
336
337 /* Whenever the batch is created with the routing, the poc_ind is set to 'Y'
338 and set to 'N' when there is no routing. So the below code is replaced with
339 the check so that this code works fine for the migrated batches from old gme code */
340 IF l_batch_header_rec.poc_ind = 'Y' THEN
341 IF (x_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending) THEN
342 IF (p_batch_header_rec.plan_start_date IS NOT NULL)
343 AND (p_batch_header_rec.plan_cmplt_date IS NULL) THEN
344 gme_create_step_pvt.calc_dates
345 (p_gme_batch_header_rec => l_batch_header_rec
346 ,p_use_workday_cal => p_use_workday_cal
347 ,p_contiguity_override => p_contiguity_override
348 ,p_return_status => l_return_status);
349
350 IF l_return_status <> x_return_status THEN
351 RAISE cal_dates_error;
352 END IF;
353 ELSIF (p_batch_header_rec.plan_cmplt_date IS NOT NULL)
354 AND (p_batch_header_rec.plan_start_date IS NULL) THEN
355 gme_create_step_pvt.calc_dates
356 (p_gme_batch_header_rec => l_batch_header_rec
357 ,p_use_workday_cal => p_use_workday_cal
358 ,p_contiguity_override => p_contiguity_override
359 ,p_return_status => l_return_status);
360
361 IF l_return_status <> x_return_status THEN
362 RAISE cal_dates_error;
363 END IF;
364 END IF; /*plan_start_date not null*/
365
366 IF ( (p_batch_header_rec.plan_start_date IS NOT NULL)
367 AND (p_batch_header_rec.plan_cmplt_date IS NOT NULL) ) THEN
368 gme_create_step_pvt.calc_dates
369 (p_gme_batch_header_rec => l_batch_header_rec
370 ,p_use_workday_cal => p_use_workday_cal
371 ,p_contiguity_override => p_contiguity_override
372 ,p_return_status => l_return_status);
373
374 IF l_return_status <> x_return_status THEN
375 RAISE cal_dates_error;
376 END IF;
377 END IF; /* both date not null*/
378 END IF; /* batch_status = 1*/
379
380 IF (x_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip) THEN
381 IF (p_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
382 gme_create_step_pvt.calc_dates
383 (p_gme_batch_header_rec => p_batch_header_rec
384 ,p_use_workday_cal => p_use_workday_cal
385 ,p_contiguity_override => p_contiguity_override
386 ,p_return_status => l_return_status);
387
388 IF l_return_status <> x_return_status THEN
389 RAISE cal_dates_error;
390 END IF;
391 END IF; /*plan_cmpt_date is not null*/
392 END IF; /* batch_status = 2*/
393
394 IF NOT (gme_batch_header_dbl.fetch_row (p_batch_header_rec
395 ,x_batch_header_rec) ) THEN
396 RAISE batch_header_fetch_error;
397 END IF;
398 ELSE /* no routing */
399 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
400 gme_debug.put_line ('No Routing');
401 END IF;
402 /* GO to validity rule is not an LCF batch */
403 IF l_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
404 OPEN recipe_validity_rule_cursor
405 (l_batch_header_rec.recipe_validity_rule_id);
406
407 FETCH recipe_validity_rule_cursor
408 INTO l_recipe_validity_rule_rec;
409
410 CLOSE recipe_validity_rule_cursor;
411 ELSE /* LCF Batch */
412 OPEN primary_prod_from_batch_cursor
413 (l_batch_header_rec.batch_id);
414
415 FETCH primary_prod_from_batch_cursor
416 INTO l_recipe_validity_rule_rec.inventory_item_id;
417
418 CLOSE primary_prod_from_batch_cursor;
419 END IF; /* IF l_batch_header_rec.recipe_validity_rule_id IS NOT NULL */
420
421
422 SELECT primary_uom_code
423 INTO l_prim_item_um
424 FROM mtl_system_items
425 WHERE inventory_item_id =
426 l_recipe_validity_rule_rec.inventory_item_id
427 AND organization_id = l_batch_header_rec.organization_id;
428
429 OPEN get_prim_prod (x_batch_header_rec.batch_id
430 ,l_recipe_validity_rule_rec.inventory_item_id);
431
432 FETCH get_prim_prod
433 INTO l_prim_prod_qty, l_prim_prod_um;
434
435 IF get_prim_prod%FOUND THEN
436 l_prim_prod_found := TRUE;
437 ELSE
438 l_prim_prod_found := FALSE;
439 END IF;
440
441 CLOSE get_prim_prod;
442 -- Pawan changed for issues dound during UTE
443 IF l_prim_prod_found THEN
444 l_temp_qty :=
445 inv_convert.inv_um_convert
446 (item_id => l_recipe_validity_rule_rec.inventory_item_id
447 ,PRECISION => 5
448 ,from_quantity => l_prim_prod_qty
449 ,from_unit => l_prim_prod_um
450 ,to_unit => l_prim_item_um
451 ,from_name => NULL
452 ,to_name => NULL);
453
454 IF l_temp_qty = -99999 THEN
455 l_item_id := l_recipe_validity_rule_rec.inventory_item_id;
456 l_from_uom := l_prim_prod_um;
457 l_to_uom := l_prim_item_um;
458 RAISE conversion_failure;
459 END IF;
460
461 /* Pass plan_start_date to this procedure in case of a WIP batch */
462 IF x_batch_header_rec.batch_status = 2 THEN
463 l_date := NULL;
464 ELSE
465 l_date := p_batch_header_rec.plan_start_date;
466 END IF;
467
468 IF gme_common_pvt.calc_date_from_prod_rule
469 (p_organization_id => x_batch_header_rec.organization_id
470 ,p_inventory_item_id => l_recipe_validity_rule_rec.inventory_item_id
471 ,p_item_qty => l_temp_qty
472 ,p_start_date => l_date
473 ,p_cmplt_date => p_batch_header_rec.plan_cmplt_date
474 ,x_start_date => x_batch_header_rec.plan_start_date
475 ,x_cmplt_date => x_batch_header_rec.plan_cmplt_date) =
476 TRUE THEN
477 l_no_prod_rule_found := FALSE;
478
479 /* IF batch is WIP then disregard calculated plan_start_date */
480 IF x_batch_header_rec.batch_status =
481 gme_common_pvt.g_batch_wip THEN
482 x_batch_header_rec.plan_start_date :=
483 p_batch_header_rec.plan_start_date;
484 END IF;
485 ELSE
486 l_no_prod_rule_found := TRUE;
487 END IF;
488 ELSE
489 -- prim prod was not found...
490 l_no_prod_rule_found := TRUE;
491 END IF; /* l_prim_prod_found */
492
493 IF l_no_prod_rule_found THEN
494 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
495 gme_debug.put_line ('No Rules');
496 END IF;
497
498 IF p_batch_header_rec.plan_start_date IS NOT NULL
499 AND p_batch_header_rec.plan_cmplt_date IS NULL THEN
500 x_batch_header_rec.plan_cmplt_date :=
501 p_batch_header_rec.plan_start_date;
502 x_batch_header_rec.plan_start_date :=
503 p_batch_header_rec.plan_start_date;
504 ELSIF p_batch_header_rec.plan_start_date IS NULL
505 AND p_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
506 x_batch_header_rec.plan_start_date :=
507 p_batch_header_rec.plan_cmplt_date;
508 x_batch_header_rec.plan_cmplt_date :=
509 p_batch_header_rec.plan_cmplt_date;
510 ELSIF p_batch_header_rec.plan_start_date IS NULL
511 AND p_batch_header_rec.plan_cmplt_date IS NULL THEN
512 x_batch_header_rec.plan_start_date :=
513 gme_common_pvt.g_timestamp;
514 x_batch_header_rec.plan_cmplt_date :=
515 gme_common_pvt.g_timestamp;
516 ELSIF p_batch_header_rec.plan_start_date IS NOT NULL
517 AND p_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
518 x_batch_header_rec.plan_start_date :=
519 p_batch_header_rec.plan_start_date;
520 x_batch_header_rec.plan_cmplt_date :=
521 p_batch_header_rec.plan_cmplt_date;
522 END IF; /*plan_start_date NOT NULL*/
523
524 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
525 gme_debug.put_line
526 ( 'Production rule start_date '
527 || TO_CHAR (x_batch_header_rec.plan_start_date
528 ,'yyyy/mon/dd hh24:mi:ss')
529 || ' Production rule end_date '
530 || TO_CHAR (x_batch_header_rec.plan_cmplt_date
531 ,'yyyy/mon/dd hh24:mi:ss') );
532 END IF;
533 ELSE /* l_no_prod_found*/
534 IF p_use_workday_cal = fnd_api.g_true THEN
535 gmd_recipe_fetch_pub.fetch_contiguous_ind
536 (p_recipe_id => NULL
537 ,p_orgn_id => NULL
538 ,p_recipe_validity_rule_id => l_batch_header_rec.recipe_validity_rule_id
539 ,x_contiguous_ind => l_contiguous_ind
540 ,x_return_status => l_return_status);
541
542 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
543 gme_debug.put_line ( 'l_contiguous_ind found '
544 || l_contiguous_ind);
545 END IF;
546
547 l_duration :=
548 ( x_batch_header_rec.plan_cmplt_date
549 - x_batch_header_rec.plan_start_date)
550 * 24;
551
552 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
553 gme_debug.put_line ('l duration ' || l_duration);
554 END IF;
555
556 IF p_batch_header_rec.plan_start_date IS NOT NULL
557 OR ( p_batch_header_rec.plan_start_date IS NULL
558 AND p_batch_header_rec.plan_cmplt_date IS NULL) THEN
559 gmp_calendar_api.get_contiguous_periods
560 (p_api_version => 1
561 ,p_init_msg_list => TRUE
562 ,p_start_date => NVL
563 (p_batch_header_rec.plan_start_date
564 ,x_batch_header_rec.plan_start_date)
565 ,p_end_date => NULL
566 ,p_calendar_code => l_calendar_code
567 ,p_duration => l_duration
568 ,p_output_tbl => l_contig_period_tbl
569 ,x_return_status => l_return_status);
570
571 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
572 gme_debug.put_line
573 ( 'Came back from Get Contiguous Periods with status '
574 || l_return_status);
575 END IF;
576
577 IF (l_return_status <> x_return_status) THEN
578 RAISE error_cont_period;
579 END IF;
580
581 l_cal_count := l_contig_period_tbl.COUNT;
582
583 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
584 gme_debug.put_line ('l cal_count ' || l_cal_count);
585 gme_debug.put_line ( 'p_contiguity_override '
586 || p_contiguity_override);
587 END IF;
588
589 IF l_contiguous_ind = 1
590 AND p_contiguity_override = fnd_api.g_false
591 AND l_cal_count > 1 THEN
592 RAISE error_non_contiguious;
593 END IF;
594
595 x_batch_header_rec.plan_start_date :=
596 NVL (p_batch_header_rec.plan_start_date
597 ,x_batch_header_rec.plan_start_date);
598 x_batch_header_rec.plan_cmplt_date :=
599 l_contig_period_tbl (l_cal_count).end_date;
600
601 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
602 gme_debug.put_line
603 ( 'x_batch_header_rec.plan_cmplt_date '
604 || TO_CHAR
605 (x_batch_header_rec.plan_cmplt_date
606 ,'yyyy/mon/dd hh24:mi:ss') );
607 END IF;
608 ELSE /* p_batch_header_rec.plan_start_date IS NOT NULL */
609 IF p_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
610 gmp_calendar_api.get_contiguous_periods
611 (p_api_version => 1
612 ,p_init_msg_list => TRUE
613 ,p_start_date => NULL
614 ,p_end_date => p_batch_header_rec.plan_cmplt_date
615 ,p_calendar_code => l_calendar_code
616 ,p_duration => l_duration
617 ,p_output_tbl => l_contig_period_tbl
618 ,x_return_status => l_return_status);
619
620 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
621 gme_debug.put_line
622 ( 'Plan start date is null. Came back from Get Contiguous Periods with status '
623 || l_return_status);
624 END IF;
625
626 IF (l_return_status <> x_return_status) THEN
627 RAISE error_cont_period;
628 END IF;
629
630 l_cal_count := l_contig_period_tbl.COUNT;
631
632 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
633 gme_debug.put_line ('l cal_count ' || l_cal_count);
634 gme_debug.put_line ( 'p_contiguity_override '
635 || p_contiguity_override);
636 END IF;
637
638 IF l_contiguous_ind = 1
639 AND p_contiguity_override = fnd_api.g_false
640 AND l_cal_count > 1 THEN
641 RAISE error_non_contiguious;
642 END IF;
643
644 x_batch_header_rec.plan_cmplt_date :=
645 p_batch_header_rec.plan_cmplt_date;
646 x_batch_header_rec.plan_start_date :=
647 l_contig_period_tbl (l_cal_count).start_date;
648
649 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
650 gme_debug.put_line
651 ( 'x_batch_header_rec.plan_start_date '
652 || TO_CHAR
653 (x_batch_header_rec.plan_start_date
654 ,'yyyy/mon/dd hh24:mi:ss') );
655 END IF;
656 END IF; /*p_batch_header_rec_plan_cmplt_date is not null*/
657 END IF; /*p_batch_header_rec_plan_start_date is not null*/
658
659 IF ( p_batch_header_rec.plan_start_date IS NOT NULL
660 AND p_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
661 x_batch_header_rec.plan_cmplt_date :=
662 p_batch_header_rec.plan_cmplt_date;
663 END IF;
664 END IF; /* p_use_workday_cal = FND_API.G_TRUE */
665 END IF; /* l_no_prod_found*/
666
667 IF (NVL (g_debug, 0) IN
668 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
669 gme_debug.put_line ('Update gme_batch_header.');
670 END IF;
671
672 UPDATE gme_batch_header
673 SET plan_start_date = x_batch_header_rec.plan_start_date
674 ,plan_cmplt_date = x_batch_header_rec.plan_cmplt_date
675 ,due_date =
676 NVL (x_batch_header_rec.due_date
677 ,x_batch_header_rec.plan_cmplt_date)
678 ,last_updated_by = gme_common_pvt.g_user_ident
679 ,last_update_date = gme_common_pvt.g_timestamp
680 ,last_update_login = gme_common_pvt.g_login_id
681 WHERE batch_id = x_batch_header_rec.batch_id;
682 END IF;
683
684 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
685 gme_debug.put_line
686 ( ' After update gme_batch_header. Final plan_start_date '
687 || TO_CHAR (x_batch_header_rec.plan_start_date
688 ,'yyyy/mon/dd hh24:mi:ss')
689 || ' Final plan_cmplt_date '
690 || TO_CHAR (x_batch_header_rec.plan_cmplt_date
691 ,'yyyy/mon/dd hh24:mi:ss') );
692 END IF;
693
694 /* Whenever the batch is created with the routing, the poc_ind is set to 'Y'
695 and set to 'N' when there is no routing. So the below code is replaced with
696 the check so that this code works fine for the migrated batches from old gme code */
697 IF x_batch_header_rec.poc_ind = 'Y' THEN
698 SELECT MIN (plan_start_date)
699 INTO min_start_date
700 FROM gme_batch_steps
701 WHERE batch_id = p_batch_header_rec.batch_id;
702 ELSE
703 min_start_date := x_batch_header_rec.plan_start_date;
704 END IF;
705
706 IF (x_batch_header_rec.batch_status = 2) THEN
707 IF p_batch_header_rec.plan_start_date > min_start_date THEN
708 x_batch_header_rec.plan_start_date :=
709 p_batch_header_rec.plan_start_date;
710 truncate_date (p_batch_header_rec => x_batch_header_rec
711 ,p_date => 0
712 ,x_return_status => l_return_status);
713
714 IF l_return_status <> x_return_status THEN
715 RAISE trun_date_error;
716 END IF;
717 END IF; /* min_start_date*/
718 END IF;
719 /* batch_status = 2*/
720 /* Whenever the batch is created with the routing, the poc_ind is set to 'Y'
721 and set to 'N' when there is no routing. So the below code is replaced with
722 the check so that this code works fine for the migrated batches from old gme code */
723
724 IF x_batch_header_rec.poc_ind = 'Y' THEN
725 SELECT MAX (plan_cmplt_date)
726 INTO max_cmplt_date
727 FROM gme_batch_steps
728 WHERE batch_id = p_batch_header_rec.batch_id;
729 ELSE
730 max_cmplt_date := x_batch_header_rec.plan_cmplt_date;
731 END IF;
732
733 IF ( (x_batch_header_rec.plan_start_date IS NOT NULL)
734 AND (x_batch_header_rec.plan_cmplt_date IS NOT NULL) ) THEN
735 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
736 gme_debug.put_line ( 'max_date '
737 || TO_CHAR (max_cmplt_date
738 ,'yyyy/mon/dd hh24:mi:ss') );
739 gme_debug.put_line ( 'required batch end_date '
740 || TO_CHAR
741 (p_batch_header_rec.plan_cmplt_date
742 ,'yyyy/mon/dd hh24:mi:ss') );
743 END IF;
744
745 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
746 gme_debug.put_line ( 'p_plan_cmplt_date '
747 || TO_CHAR
748 (p_batch_header_rec.plan_cmplt_date
749 ,'yyyy/mon/dd hh24:mi:ss') );
750 gme_debug.put_line ( 'x_plan_cmplt_date '
751 || TO_CHAR
752 (x_batch_header_rec.plan_cmplt_date
753 ,'yyyy/mon/dd hh24:mi:ss') );
754 END IF;
755
756 IF p_batch_header_rec.plan_cmplt_date < max_cmplt_date THEN
757 x_batch_header_rec.plan_cmplt_date :=
758 p_batch_header_rec.plan_cmplt_date;
759 truncate_date (p_batch_header_rec => x_batch_header_rec
760 ,p_date => 1
761 ,x_return_status => l_return_status);
762
763 IF l_return_status <> x_return_status THEN
764 RAISE trun_date_error;
765 END IF;
766 ELSIF p_batch_header_rec.plan_cmplt_date >
767 x_batch_header_rec.plan_cmplt_date THEN
768 IF (NVL (g_debug, 0) IN
769 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
770 gme_debug.put_line
771 ('User supplied plan completion date is greater than what system calculated User wants to leave gaps.');
772 END IF;
773
774 UPDATE gme_batch_header
775 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
776 WHERE batch_id = x_batch_header_rec.batch_id;
777 END IF; /* max_cmplt_date */
778 END IF; /* dates not null */
779
780 IF NOT (gme_batch_header_dbl.fetch_row (p_batch_header_rec
781 ,x_batch_header_rec) ) THEN
782 RAISE batch_header_fetch_error;
783 END IF;
784
785 -- Checking of batch dates with validity rules dates after the reschedule
786 -- Pawan Kumar added code for bug 3088739
787 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
788 gme_debug.put_line ( 'affinal plan_start_date '
789 || TO_CHAR (x_batch_header_rec.plan_start_date
790 ,'yyyy/mon/dd hh24:mi:ss') );
791 gme_debug.put_line ( 'affinal plan_cmplt_date '
792 || TO_CHAR (x_batch_header_rec.plan_cmplt_date
793 ,'yyyy/mon/dd hh24:mi:ss') );
794 END IF;
795
796 /* If not LCF batch then check validity rule dates */
797 IF l_batch_header_rec.recipe_validity_rule_id IS NOT NULL THEN
798 IF NOT gme_common_pvt.check_validity_rule_dates
799 (l_batch_header_rec.recipe_validity_rule_id
800 ,x_batch_header_rec.plan_start_date
801 ,x_batch_header_rec.plan_cmplt_date
802 ,l_batch_header_rec) THEN
803 x_return_status := fnd_api.g_ret_sts_error;
804 RAISE date_exceed_validity_rule;
805 END IF;
806 END IF; /* IF l_batch_header_rec.recipe_validity_rule_id IS NOT NULL */
807
808 /* Load all the transactions and resources to the temporary table */
809 /* for the current batch if the update inventory ind is set for the batch */
810 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
811 IF (NVL (g_debug, 0) IN
812 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
813 gme_debug.put_line
814 ('Load all the transactions and resources to the temporary table.');
815 END IF;
816
817 gme_trans_engine_util.load_rsrc_trans
818 (p_batch_row => x_batch_header_rec
819 ,x_rsc_row_count => l_rsrc_trans_count
820 ,x_return_status => l_return_status);
821
822 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
823 gme_debug.put_line
824 ( 'Came back after loading all the transactions and resources to the temporary table with status '
825 || l_return_status);
826 END IF;
827
828 IF l_return_status <> x_return_status THEN
829 RAISE error_load_trans;
830 END IF;
831 END IF; /* IF x_batch_header_rec.update_inventory_ind = 'Y' */
832
833 FOR l_rec IN cur_get_phant (x_batch_header_rec.batch_id) LOOP
834 l_in_batch_header_rec.batch_id := l_rec.phantom_id;
835
836 IF l_rec.batchstep_id IS NOT NULL AND l_rec.release_type = 3 THEN
837 OPEN cur_get_step_date (x_batch_header_rec.batch_id
838 ,l_rec.batchstep_id);
839
840 FETCH cur_get_step_date
841 INTO l_plan_date;
842
843 CLOSE cur_get_step_date;
844
845 l_in_batch_header_rec.plan_cmplt_date := l_plan_date;
846 ELSE
847 l_in_batch_header_rec.plan_cmplt_date :=
848 x_batch_header_rec.plan_start_date;
849 END IF;
850
851 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
852 gme_debug.put_line
853 ( g_pkg_name
854 || '.'
855 || l_api_name
856 || ':'
857 || 'Calling Reschedule Batch for phantom batch_id: '
858 || l_in_batch_header_rec.batch_id);
859 END IF;
860
861 gme_reschedule_batch_pvt.reschedule_batch
862 (p_batch_header_rec => l_in_batch_header_rec
863 ,p_use_workday_cal => p_use_workday_cal
864 ,p_contiguity_override => p_contiguity_override
865 ,x_batch_header_rec => l_batch_header_rec
866 ,x_return_status => l_return_status);
867
868 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
869 gme_debug.put_line
870 ( 'Came back from Reschedule Batch with status '
871 || l_return_status);
872 END IF;
873
874 IF l_return_status <> x_return_status THEN
875 RAISE resched_phant_fail;
876 END IF;
877 END LOOP;
878
879 /* Now we have to update the transaction dates of pending transactions */
880 /* for the material lines which are not of step release type */
881 OPEN cur_get_material (x_batch_header_rec.batch_id);
882
883 FETCH cur_get_material
884 BULK COLLECT INTO l_material_detail_ids, l_line_type_tbl;
885
886 l_loop_count_get_material := cur_get_material%ROWCOUNT;
887
888 CLOSE cur_get_material;
889
890 FOR i IN 1 .. l_loop_count_get_material LOOP
891 l_material_detail_rec.material_detail_id :=
892 l_material_detail_ids (i);
893 -- Stamp manual and incremental with step dates also
894 -- Pawan Kumar bug 5499499 added code for step dates.
895 l_rel_type :=
896 gme_common_pvt.is_material_auto_release (l_material_detail_ids (i));
897
898 IF ( l_rel_type = 3 OR
899 ( gme_common_pvt.is_material_assoc_to_step(l_material_detail_ids(i)) = TRUE
900 AND l_rel_type IN (1, 2))
901 ) THEN
902 SELECT plan_start_date, plan_cmplt_date
903 INTO l_start_date, l_cmplt_date
904 FROM gme_batch_steps
905 WHERE batch_id = x_batch_header_rec.batch_id
906 AND batchstep_id = (SELECT batchstep_id
907 FROM gme_batch_step_items
908 WHERE batch_id = x_batch_header_rec.batch_id
909 AND material_detail_id = l_material_detail_ids(i));
910
911
912 ELSE /* NOT ASSOCIATED TO STEP */
913 l_start_date := x_batch_header_rec.plan_start_date;
914 l_cmplt_date := x_batch_header_rec.plan_cmplt_date;
915
916 END IF; /* IF l_rel_type = gme_common_pvt.g_mtl_autobystep_release */
917
918 -- Navin Added as part of Reschedule Batch/Step Build.
919 IF l_line_type_tbl (i) = gme_common_pvt.g_line_type_ing THEN
920 -- Update the material_required_date with the associated plan_start_Date;
921 l_material_date := l_start_date;
922 ELSE
923 -- Update the material required date with the associated plan cmplt Date;
924 l_material_date := l_cmplt_date;
925 END IF;
926
927 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
928 gme_debug.put_line
929 ( 'Calling Material Date Change for batch_id : '
930 || x_batch_header_rec.batch_id
931 || ' Material_detail_id : '
932 || l_material_detail_ids (i)
933 ||'date sent for change'
934 ||l_material_date
935 );
936 END IF;
937
938 gme_common_pvt.material_date_change
939 (p_material_detail_id => l_material_detail_ids
940 (i)
941 ,p_material_date => l_material_date
942 ,x_return_status => l_return_status);
943
944 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
945 gme_debug.put_line
946 ( 'Came back from material_date_change with status '
947 || l_return_status);
948 END IF;
949 --FPBug#4585491 Begin
950 /*IF l_return_status <> x_return_status THEN
951 RAISE mtl_dt_chg_error;
952 END IF; */
953
954 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
955 RAISE mtl_dt_chg_error;
956 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
957 RAISE fnd_api.g_exc_unexpected_error;
958 END IF;
959
960 /*
961 The above material_date_change returns different status as described below
962 R: When reservations are deleted for a material line
963 M: When MO Allocations are deleted for a material line
964 B: When Both reservations and material lines are deleted for a material line
965 */
966 IF x_return_status = 'R' THEN
967 l_R_count := l_R_count + 1;
968 ELSIF x_return_status = 'M' THEN
969 l_M_count := l_M_count + 1;
970 ELSIF x_return_status = 'B' THEN
971 l_B_count := l_B_count + 1;
972 END IF;
973 --FPBug#4585491 End
974
975 END LOOP; /* FOR i IN 1..l_loop_count_get_material */
976
977 --FPBug#4585491 Begin
978 IF (l_B_count > 0) OR (l_R_count > 0 AND l_M_count > 0) THEN
979 --atleast for one material line MO allocations and reservations are deleted
980 gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
981 ELSIF l_R_count > 0 THEN
982 ----atleast for one material line reservations are deleted
983 gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
984 ELSIF l_M_count > 0 THEN
985 ----atleast for one material line MO allocations are deleted
986 gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
987 END IF;
988 x_return_status := fnd_api.g_ret_sts_success;
989 --FPBug#4585491 End
990
991
992 --clearing the charge dates on batch reschedule;
993 OPEN cur_is_charge_associated (x_batch_header_rec.batch_id);
994
995 FETCH cur_is_charge_associated
996 INTO l_cur_is_charge_associated;
997
998 IF cur_is_charge_associated%FOUND THEN
999 CLOSE cur_is_charge_associated;
1000
1001 gme_batch_step_chg_pvt.clear_charge_dates
1002 (p_batch_id => x_batch_header_rec.batch_id
1003 ,x_return_status => l_return_status);
1004
1005 IF l_return_status <> x_return_status THEN
1006 RAISE clear_chg_dates_error;
1007 END IF;
1008 ELSE
1009 CLOSE cur_is_charge_associated;
1010 END IF;
1011 --Susruth Bug#5359091 Finite Scheduled indicator is set back to 0 once the batch is rescheduled. START
1012 IF x_batch_header_rec.FINITE_SCHEDULED_IND = 1 THEN
1013 UPDATE gme_batch_header
1014 SET FINITE_SCHEDULED_IND = 0
1015 WHERE batch_id = x_batch_header_rec.batch_id;
1016 x_batch_header_rec.FINITE_SCHEDULED_IND := 0;
1017 END IF;
1018
1019 -- Bug#5359091 end
1020 /* Update the row who columns */
1021 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
1022 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
1023 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
1024
1025 IF (NVL (g_debug, 0) IN
1026 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
1027 gme_debug.put_line ( g_pkg_name
1028 || '.'
1029 || l_api_name
1030 || ':'
1031 || 'Exiting with '
1032 || x_return_status);
1033 END IF;
1034 EXCEPTION
1035 WHEN invalid_batch THEN
1036 x_return_status := fnd_api.g_ret_sts_error;
1037 gme_common_pvt.log_message ('GME_API_INV_BATCH_RESCHED');
1038 WHEN null_dates THEN
1039 x_return_status := fnd_api.g_ret_sts_error;
1040 gme_common_pvt.log_message ('GME_API_RESCH_NO_DATES_PASSED');
1041 WHEN date_exceed_validity_rule THEN
1042 x_return_status := fnd_api.g_ret_sts_error;
1043 WHEN invalid_validity_rule THEN
1044 x_return_status := fnd_api.g_ret_sts_error;
1045 WHEN batch_header_fetch_error OR error_load_trans THEN
1046 x_return_status := fnd_api.g_ret_sts_error;
1047 WHEN resched_phant_fail OR mtl_dt_chg_error OR cal_dates_error THEN
1048 x_return_status := l_return_status;
1049 WHEN cmplt_less_start OR cannot_reschedule_start OR date_is_less_than_actual THEN
1050 x_return_status := fnd_api.g_ret_sts_error;
1051 WHEN error_cont_period THEN
1052 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1053 gme_debug.put_line ('Contiguity period ... _failed');
1054 END IF;
1055
1056 x_return_status := l_return_status;
1057 WHEN clear_chg_dates_error THEN
1058 x_return_status := fnd_api.g_ret_sts_error;
1059 WHEN error_non_contiguious THEN
1060 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1061 gme_debug.put_line ('Contiguity period ... not found');
1062 END IF;
1063
1064 gme_common_pvt.log_message ('GME_NON_CONTIGUOUS_TIME');
1065 x_return_status := 'C';
1066 WHEN conversion_failure THEN
1067 IF l_item_no IS NULL THEN
1068 OPEN cur_item_no (l_item_id, p_batch_header_rec.organization_id);
1069
1070 FETCH cur_item_no
1071 INTO l_item_no;
1072
1073 CLOSE cur_item_no;
1074 END IF;
1075
1076 x_return_status := fnd_api.g_ret_sts_error;
1077 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1078 fnd_message.set_token ('ITEM_NO', l_item_no);
1079 fnd_message.set_token ('FROM_UOM', l_from_uom);
1080 fnd_message.set_token ('TO_UOM', l_to_uom);
1081 fnd_msg_pub.ADD;
1082 WHEN OTHERS THEN
1083 x_return_status := fnd_api.g_ret_sts_unexp_error;
1084 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1085
1086 IF (NVL (g_debug, 0) > 0) THEN
1087 gme_debug.put_line ( g_pkg_name
1088 || '.'
1089 || l_api_name
1090 || ':'
1091 || ' OTHERS:'
1092 || SQLERRM);
1093 END IF;
1094 END reschedule_batch;
1095
1096 /***********************************************************************************
1097 Procedure
1098 truncate_date
1099 Description
1100 This particular procedure is used to update the pending transactions of the material detail
1101 line passed in to the trans date to the plan_start or completion dates of the batch/
1102 Parameters
1103
1104 P_batch_header Batch Header Row.
1105 p_date number possible values = 0 for start date and 1 for end date
1106 x_return_status outcome of the API call
1107 S - Success
1108 E - Error
1109 U - Unexpected error
1110 Revision History
1111 Rishi Varma bug # 3446787.
1112 Added the p_batchstep_id parameter for updating only the steps,actvities,
1113 resources,resource transactions.
1114 For this modified the sql query to include the condition
1115 "AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id)" for
1116 steps,activities,resources and resource transactions.
1117 ***********************************************************************************/
1118 PROCEDURE truncate_date (
1119 p_batch_header_rec IN gme_batch_header%ROWTYPE
1120 ,p_date IN NUMBER
1121 ,p_batchstep_id IN gme_batch_steps.batchstep_id%TYPE
1122 DEFAULT NULL
1123 ,x_return_status OUT NOCOPY VARCHAR2)
1124 IS
1125 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TRANSACTION';
1126 l_batch_id NUMBER := 0;
1127 l_doc_type VARCHAR2 (4);
1128 BEGIN
1129 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1130 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1131 || 'Entering');
1132 END IF;
1133
1134 /* Set the return status to success initially */
1135 x_return_status := fnd_api.g_ret_sts_success;
1136 l_batch_id := p_batch_header_rec.batch_id;
1137
1138 IF (p_batch_header_rec.batch_type = 0) THEN
1139 l_doc_type := 'PROD';
1140 ELSE
1141 l_doc_type := 'FPO';
1142 END IF;
1143
1144 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1145 gme_debug.put_line ('batch_id=' || l_batch_id);
1146 END IF;
1147
1148 IF p_date = 0 THEN
1149 --update batch start date
1150 IF (p_batchstep_id IS NULL) THEN
1151 UPDATE gme_batch_header
1152 SET plan_start_date = p_batch_header_rec.plan_start_date
1153 ,last_updated_by = gme_common_pvt.g_user_ident
1154 ,last_update_date = gme_common_pvt.g_timestamp
1155 ,last_update_login = gme_common_pvt.g_login_id
1156 WHERE batch_id = l_batch_id;
1157
1158 --update batch end date
1159 UPDATE gme_batch_header
1160 SET plan_cmplt_date = p_batch_header_rec.plan_start_date
1161 ,last_updated_by = gme_common_pvt.g_user_ident
1162 ,last_update_date = gme_common_pvt.g_timestamp
1163 ,last_update_login = gme_common_pvt.g_login_id
1164 WHERE batch_id = l_batch_id
1165 AND plan_cmplt_date < p_batch_header_rec.plan_start_date;
1166
1167 --update batch due date
1168 UPDATE gme_batch_header
1169 SET due_date = p_batch_header_rec.plan_start_date
1170 ,last_updated_by = gme_common_pvt.g_user_ident
1171 ,last_update_date = gme_common_pvt.g_timestamp
1172 ,last_update_login = gme_common_pvt.g_login_id
1173 WHERE batch_id = l_batch_id
1174 AND due_date < p_batch_header_rec.plan_start_date;
1175 END IF;
1176
1177 -- update batch steps start date
1178 UPDATE gme_batch_steps
1179 SET plan_start_date = p_batch_header_rec.plan_start_date
1180 ,last_updated_by = gme_common_pvt.g_user_ident
1181 ,last_update_date = gme_common_pvt.g_timestamp
1182 ,last_update_login = gme_common_pvt.g_login_id
1183 WHERE batch_id = l_batch_id
1184 AND plan_start_date < p_batch_header_rec.plan_start_date
1185 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1186
1187 -- update batch steps end date
1188 UPDATE gme_batch_steps
1189 SET plan_cmplt_date = p_batch_header_rec.plan_start_date
1190 ,last_updated_by = gme_common_pvt.g_user_ident
1191 ,last_update_date = gme_common_pvt.g_timestamp
1192 ,last_update_login = gme_common_pvt.g_login_id
1193 WHERE batch_id = l_batch_id
1194 AND plan_cmplt_date < p_batch_header_rec.plan_start_date
1195 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1196
1197 -- update batch steps due date
1198 UPDATE gme_batch_steps
1199 SET due_date = p_batch_header_rec.plan_start_date
1200 ,last_updated_by = gme_common_pvt.g_user_ident
1201 ,last_update_date = gme_common_pvt.g_timestamp
1202 ,last_update_login = gme_common_pvt.g_login_id
1203 WHERE batch_id = l_batch_id
1204 AND due_date < p_batch_header_rec.plan_start_date
1205 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1206
1207 -- update batch activity start date
1208 UPDATE gme_batch_step_activities
1209 SET plan_start_date = p_batch_header_rec.plan_start_date
1210 ,last_updated_by = gme_common_pvt.g_user_ident
1211 ,last_update_date = gme_common_pvt.g_timestamp
1212 ,last_update_login = gme_common_pvt.g_login_id
1213 WHERE batch_id = l_batch_id
1214 AND plan_start_date < p_batch_header_rec.plan_start_date
1215 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1216
1217 -- update batch activity end date
1218 UPDATE gme_batch_step_activities
1219 SET plan_cmplt_date = p_batch_header_rec.plan_start_date
1220 ,last_updated_by = gme_common_pvt.g_user_ident
1221 ,last_update_date = gme_common_pvt.g_timestamp
1222 ,last_update_login = gme_common_pvt.g_login_id
1223 WHERE batch_id = l_batch_id
1224 AND plan_cmplt_date < p_batch_header_rec.plan_start_date
1225 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1226
1227 -- update batch resources start date
1228 UPDATE gme_batch_step_resources
1229 SET plan_start_date = p_batch_header_rec.plan_start_date
1230 ,last_updated_by = gme_common_pvt.g_user_ident
1231 ,last_update_date = gme_common_pvt.g_timestamp
1232 ,last_update_login = gme_common_pvt.g_login_id
1233 WHERE batch_id = l_batch_id
1234 AND plan_start_date < p_batch_header_rec.plan_start_date
1235 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1236
1237 -- update batch resources end date
1238 UPDATE gme_batch_step_resources
1239 SET plan_cmplt_date = p_batch_header_rec.plan_start_date
1240 ,last_updated_by = gme_common_pvt.g_user_ident
1241 ,last_update_date = gme_common_pvt.g_timestamp
1242 ,last_update_login = gme_common_pvt.g_login_id
1243 WHERE batch_id = l_batch_id
1244 AND plan_cmplt_date < p_batch_header_rec.plan_start_date
1245 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1246
1247 -- update batch resources txns start date
1248 UPDATE gme_resource_txns
1249 SET start_date = p_batch_header_rec.plan_start_date
1250 ,last_updated_by = gme_common_pvt.g_user_ident
1251 ,last_update_date = gme_common_pvt.g_timestamp
1252 ,last_update_login = gme_common_pvt.g_login_id
1253 WHERE doc_id = l_batch_id
1254 AND doc_type = l_doc_type
1255 AND start_date < p_batch_header_rec.plan_start_date;
1256
1257 -- update batch resources txns end date
1258 UPDATE gme_resource_txns
1259 SET end_date = p_batch_header_rec.plan_start_date
1260 ,last_updated_by = gme_common_pvt.g_user_ident
1261 ,last_update_date = gme_common_pvt.g_timestamp
1262 ,last_update_login = gme_common_pvt.g_login_id
1263 WHERE doc_id = l_batch_id
1264 AND doc_type = l_doc_type
1265 AND end_date < p_batch_header_rec.plan_start_date;
1266
1267 -- update batch resources txns trans date
1268 UPDATE gme_resource_txns
1269 SET trans_date = p_batch_header_rec.plan_start_date
1270 ,last_updated_by = gme_common_pvt.g_user_ident
1271 ,last_update_date = gme_common_pvt.g_timestamp
1272 ,last_update_login = gme_common_pvt.g_login_id
1273 WHERE doc_id = l_batch_id
1274 AND doc_type = l_doc_type
1275 AND trans_date < p_batch_header_rec.plan_start_date;
1276 ELSE
1277 --update batch start date
1278 IF (p_batchstep_id IS NULL) THEN
1279 UPDATE gme_batch_header
1280 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1281 ,last_updated_by = gme_common_pvt.g_user_ident
1282 ,last_update_date = gme_common_pvt.g_timestamp
1283 ,last_update_login = gme_common_pvt.g_login_id
1284 WHERE batch_id = l_batch_id;
1285
1286 --update batch end date
1287 UPDATE gme_batch_header
1288 SET plan_start_date = p_batch_header_rec.plan_cmplt_date
1289 ,last_updated_by = gme_common_pvt.g_user_ident
1290 ,last_update_date = gme_common_pvt.g_timestamp
1291 ,last_update_login = gme_common_pvt.g_login_id
1292 WHERE batch_id = l_batch_id
1293 AND plan_start_date > p_batch_header_rec.plan_cmplt_date;
1294
1295 --update batch due date
1296 -- Bug 4416538( front bug of 4200964)
1297 -- The due date which was getting updated with the planned
1298 -- completion date is changed to be updated with the same due date.
1299 UPDATE gme_batch_header
1300 SET due_date = p_batch_header_rec.due_date
1301 ,last_updated_by = gme_common_pvt.g_user_ident
1302 ,last_update_date = gme_common_pvt.g_timestamp
1303 ,last_update_login = gme_common_pvt.g_login_id
1304 WHERE batch_id = l_batch_id
1305 AND due_date > p_batch_header_rec.plan_cmplt_date;
1306 END IF;
1307
1308 -- update batch steps start date
1309 UPDATE gme_batch_steps
1310 SET plan_start_date = p_batch_header_rec.plan_cmplt_date
1311 ,last_updated_by = gme_common_pvt.g_user_ident
1312 ,last_update_date = gme_common_pvt.g_timestamp
1313 ,last_update_login = gme_common_pvt.g_login_id
1314 WHERE batch_id = l_batch_id
1315 AND plan_start_date > p_batch_header_rec.plan_cmplt_date
1316 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1317
1318 -- update batch steps end date
1319 UPDATE gme_batch_steps
1320 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1321 ,last_updated_by = gme_common_pvt.g_user_ident
1322 ,last_update_date = gme_common_pvt.g_timestamp
1323 ,last_update_login = gme_common_pvt.g_login_id
1324 WHERE batch_id = l_batch_id
1325 AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
1326 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1327
1328 -- update batch steps due date
1329 UPDATE gme_batch_steps
1330 SET due_date = p_batch_header_rec.plan_cmplt_date
1331 ,last_updated_by = gme_common_pvt.g_user_ident
1332 ,last_update_date = gme_common_pvt.g_timestamp
1333 ,last_update_login = gme_common_pvt.g_login_id
1334 WHERE batch_id = l_batch_id
1335 AND due_date > p_batch_header_rec.plan_cmplt_date
1336 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1337
1338 -- update batch activity start date
1339 UPDATE gme_batch_step_activities
1340 SET plan_start_date = p_batch_header_rec.plan_cmplt_date
1341 ,last_updated_by = gme_common_pvt.g_user_ident
1342 ,last_update_date = gme_common_pvt.g_timestamp
1343 ,last_update_login = gme_common_pvt.g_login_id
1344 WHERE batch_id = l_batch_id
1345 AND plan_start_date > p_batch_header_rec.plan_cmplt_date
1346 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1347
1348 -- update batch activity end date
1349 UPDATE gme_batch_step_activities
1350 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1351 ,last_updated_by = gme_common_pvt.g_user_ident
1352 ,last_update_date = gme_common_pvt.g_timestamp
1353 ,last_update_login = gme_common_pvt.g_login_id
1354 WHERE batch_id = l_batch_id
1355 AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
1356 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1357
1358 -- update batch resources start date
1359 UPDATE gme_batch_step_resources
1360 SET plan_start_date = p_batch_header_rec.plan_cmplt_date
1361 ,last_updated_by = gme_common_pvt.g_user_ident
1362 ,last_update_date = gme_common_pvt.g_timestamp
1363 ,last_update_login = gme_common_pvt.g_login_id
1364 WHERE batch_id = l_batch_id
1365 AND plan_start_date > p_batch_header_rec.plan_cmplt_date
1366 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1367
1368 -- update batch resources end date
1369 UPDATE gme_batch_step_resources
1370 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1371 ,last_updated_by = gme_common_pvt.g_user_ident
1372 ,last_update_date = gme_common_pvt.g_timestamp
1373 ,last_update_login = gme_common_pvt.g_login_id
1374 WHERE batch_id = l_batch_id
1375 AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
1376 AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
1377
1378 -- update batch resources txns start date
1379 UPDATE gme_resource_txns
1380 SET start_date = p_batch_header_rec.plan_cmplt_date
1381 ,last_updated_by = gme_common_pvt.g_user_ident
1382 ,last_update_date = gme_common_pvt.g_timestamp
1383 ,last_update_login = gme_common_pvt.g_login_id
1384 WHERE doc_id = l_batch_id
1385 AND doc_type = l_doc_type
1386 AND start_date > p_batch_header_rec.plan_cmplt_date;
1387
1388 -- update batch resources txns end date
1389 UPDATE gme_resource_txns
1390 SET end_date = p_batch_header_rec.plan_cmplt_date
1391 ,last_updated_by = gme_common_pvt.g_user_ident
1392 ,last_update_date = gme_common_pvt.g_timestamp
1393 ,last_update_login = gme_common_pvt.g_login_id
1394 WHERE doc_id = l_batch_id
1395 AND doc_type = l_doc_type
1396 AND end_date > p_batch_header_rec.plan_cmplt_date;
1397
1398 -- update batch resources txns trans date
1399 UPDATE gme_resource_txns
1400 SET trans_date = p_batch_header_rec.plan_cmplt_date
1401 ,last_updated_by = gme_common_pvt.g_user_ident
1402 ,last_update_date = gme_common_pvt.g_timestamp
1403 ,last_update_login = gme_common_pvt.g_login_id
1404 WHERE doc_id = l_batch_id
1405 AND doc_type = l_doc_type
1406 AND trans_date > p_batch_header_rec.plan_cmplt_date;
1407 END IF;
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 x_return_status := fnd_api.g_ret_sts_unexp_error;
1411 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1412
1413 IF (NVL (g_debug, 0) > 0) THEN
1414 gme_debug.put_line ( g_pkg_name
1415 || '.'
1416 || l_api_name
1417 || ':'
1418 || ' OTHERS:'
1419 || SQLERRM);
1420 END IF;
1421 END truncate_date;
1422 END gme_reschedule_batch_pvt;
[Home] [Help]