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