[Home] [Help]
PACKAGE BODY: APPS.GME_REROUTE_BATCH_PVT
Source
1 PACKAGE BODY gme_reroute_batch_pvt AS
2 /* $Header: GMEVRRBB.pls 120.10 2006/08/18 15:51:27 svgonugu noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_REROUTE_BATCH_PVT';
5
6 /*===========================================================================================
7 Procedure
8 reroute_batch
9 Description
10 This particular procedure is used to reroute the batch to a different validity rule.
11 Parameters
12 p_batch_header The batch header record.
13 p_validity_rule_id Validity rule id.
14 x_batch_header Out NOCOPY record for batch header
15 x_return_status outcome of the API call
16 S - Success
17 E - Error
18 U - Unexpected error
19 C - No continous periods found
20 History
21 Sunitha Ch. Bug 5353941 seperated the check whether the material is assciated with step or not
22 and if it is not assciated then added a select statement to retrieve the planed start date of the
23 parent batch into l_phantom_batch_header_rec.plan_cmplt_date
24 Susruth Bug#5359091 Finite Scheduled indicator is set back to 0 once the batch is rerouted.
25 Sunitha Ch. bug 5353941 REWORK Check the release type of material also alog
26 with association to step.If release type is automatic then call rescedule batch of the phantom batch
27 Sunitha ch Bug#5391396 included the code to rescedule_step of the child batch when the product
28 is associated to step and its release type is autobystep.
29 =============================================================================================*/
30 PROCEDURE reroute_batch (
31 p_batch_header_rec IN gme_batch_header%ROWTYPE
32 ,p_validity_rule_id IN NUMBER
33 ,p_use_workday_cal IN VARCHAR2
34 DEFAULT fnd_api.g_false
35 ,p_contiguity_override IN VARCHAR2
36 DEFAULT fnd_api.g_false
37 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
38 ,x_return_status OUT NOCOPY VARCHAR2)
39 IS
40 l_api_name CONSTANT VARCHAR2 (30) := 'REROUTE_BATCH';
41 l_formula_master fm_form_mst%ROWTYPE;
42 CURSOR cur_get_recipe (v_validity_rule_id IN NUMBER)
43 IS
44 SELECT r.recipe_id, r.routing_id, calculate_step_quantity,v.inventory_item_id
45 FROM gmd_recipe_validity_rules v, gmd_recipes r
46 WHERE recipe_validity_rule_id = v_validity_rule_id
47 AND r.recipe_id = v.recipe_id;
48
49 CURSOR cur_get_max_step_date
50 IS
51 SELECT MAX (plan_cmplt_date)
52 FROM gme_batch_steps
53 WHERE batch_id = p_batch_header_rec.batch_id;
54
55 CURSOR cur_get_material (v_batch_id NUMBER)
56 IS
57 SELECT material_detail_id, phantom_id
58 FROM gme_material_details
59 WHERE batch_id = v_batch_id;
60
61 CURSOR get_prim_prod (v_batch_id NUMBER, v_inventory_item_id NUMBER)
62 IS
63 SELECT plan_qty, dtl_um
64 FROM gme_material_details
65 WHERE batch_id = v_batch_id
66 AND inventory_item_id = v_inventory_item_id
67 AND line_type = gme_common_pvt.g_line_type_prod
68 ORDER BY line_no ASC;
69
70 l_material_detail_ids_tab gme_common_pvt.number_tab;
71 l_material_detail_rec gme_material_details%ROWTYPE;
72 l_return_code NUMBER;
73 l_recipe_id NUMBER;
74 l_temp_qty NUMBER DEFAULT 0;
75 l_message_count NUMBER;
76 l_message_list VARCHAR2 (1000);
77 l_max_step_date DATE;
78 l_process_parameters_tab gmd_recipe_fetch_pub.recp_resc_proc_param_tbl;
79 l_gme_material_details gme_material_details%ROWTYPE;
80 l_material_details gme_common_pvt.material_details_tab;
81 l_total_output_qty_b NUMBER;
82 l_total_output_qty_c NUMBER;
83 l_phantom_ids_tab gme_common_pvt.number_tab;
84 l_phantom_associated_to_step BOOLEAN;
85 l_phantom_batch_header_rec gme_batch_header%ROWTYPE;
86 l_phantom_batch_header_rec_out gme_batch_header%ROWTYPE;
87 no_continous_periods EXCEPTION;
88 l_no_steps BOOLEAN := FALSE;
89 l_inventory_item_id pls_integer;
90 l_prim_item_um mtl_units_of_measure.uom_code%TYPE;
91 l_prim_prod_um mtl_units_of_measure.uom_code%TYPE;
92 l_prim_prod_qty NUMBER;
93 l_prim_prod_found BOOLEAN :=FALSE;
94 l_plan_start_date DATE;
95 l_plan_cmplt_date DATE;
96 l_no_prod_rule_found BOOLEAN;
97 l_W_ReturnedAtLeastOnce BOOLEAN := FALSE;
98 l_phantom_batch_step_rec gme_batch_steps%ROWTYPE;
99 l_step_tbl gme_reschedule_step_pvt.step_tab;
100 x_batch_step_rec gme_batch_steps%ROWTYPE;
101 error_dbl EXCEPTION;
102 l_proc VARCHAR2(100);
103 --FPBug#4585491
104 l_R_count NUMBER := 0;
105 l_M_count NUMBER := 0;
106 l_B_count NUMBER := 0;
107 BEGIN
108 IF (NVL (g_debug, 0) IN
109 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
110 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
111 || 'Entering');
112 END IF;
113
114 /* Set the return status to success initially */
115 x_return_status := fnd_api.g_ret_sts_success;
116
117 /* Get the Batch header */
118 IF (NOT gme_common_pvt.get_batch_header
119 (p_batch_header_rec => p_batch_header_rec
120 ,p_org_code => NULL
121 ,p_batch_type => NULL
122 ,x_batch_header_rec => x_batch_header_rec) ) THEN
123 RAISE fnd_api.g_exc_error;
124 END IF;
125
126 /* Validation for the new validity rule */
127 gme_reroute_batch_pvt.validate_validity_id
128 (p_batch_header_rec => x_batch_header_rec
129 ,p_validity_rule_id => p_validity_rule_id
130 ,x_return_status => x_return_status);
131
132 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
133 RAISE fnd_api.g_exc_error;
134 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
135 RAISE fnd_api.g_exc_unexpected_error;
136 END IF;
137
138 /* Now we have to delete the existing poc data */
139 gme_reroute_batch_pvt.delete_all_steps (x_batch_header_rec.batch_id
140 ,x_return_status);
141
142 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
143 RAISE fnd_api.g_exc_error;
144 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
145 RAISE fnd_api.g_exc_unexpected_error;
146 END IF;
147
148 /* Get the recipe id for the validity rule */
149 OPEN cur_get_recipe (p_validity_rule_id);
150
151 FETCH cur_get_recipe
152 INTO l_recipe_id, x_batch_header_rec.routing_id
153 ,x_batch_header_rec.automatic_step_calculation
154 ,l_inventory_item_id;
155 CLOSE cur_get_recipe;
156
157 x_batch_header_rec.recipe_validity_rule_id := p_validity_rule_id;
158
159 /* Populate the routing information from the recipe */
160 --{
161 IF x_batch_header_rec.routing_id IS NOT NULL THEN
162 x_batch_header_rec.poc_ind := 'Y';
163 gmd_recipe_data_pub.get_recipe_data
164 (p_api_version => 1
165 ,p_init_msg_list => fnd_api.g_false
166 ,p_recipe_id => l_recipe_id
167 ,p_organization_id => NULL
168 ,x_return_status => x_return_status
169 ,x_msg_count => l_message_count
170 ,x_msg_data => l_message_list
171 ,x_return_code => l_return_code
172 ,x_recipe_rout_tbl => gme_common_pvt.routings
173 ,x_recipe_rout_matl_tbl => gme_common_pvt.routing_materials
174 ,x_recipe_step_out => gme_common_pvt.steps
175 ,x_routing_depd_tbl => gme_common_pvt.step_dependencies
176 ,x_oprn_act_out => gme_common_pvt.activities
177 ,x_oprn_resc_rec => gme_common_pvt.resources
178 ,x_recp_resc_proc_param_tbl => l_process_parameters_tab
179 ,x_formula_header_rec => l_formula_master
180 ,x_formula_dtl_tbl => gme_common_pvt.materials);
181
182 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
183 RAISE fnd_api.g_exc_error;
184 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
185 RAISE fnd_api.g_exc_unexpected_error;
186 END IF;
187
188 -- Added code for Enforce Step Dependency
189 IF gme_common_pvt.routings.COUNT > 0 THEN
190 x_batch_header_rec.enforce_step_dependency :=
191 NVL (gme_common_pvt.routings (1).enforce_step_dependency, 0);
192 END IF;
193 --{
194 IF gme_common_pvt.routings.COUNT <> 0 THEN
195 /* Formula quantities calculation */
196 l_total_output_qty_c := 0;
197
198 FOR i IN 1 .. gme_common_pvt.materials.COUNT LOOP
199 --{
200 IF gme_common_pvt.materials (i).line_type IN (1, 2) THEN
201 --{
202 IF gme_common_pvt.materials (i).detail_uom =
203 gme_common_pvt.routings (1).routing_uom THEN
204 l_total_output_qty_c :=
205 l_total_output_qty_c
206 + gme_common_pvt.materials (i).qty;
207 ELSE
208 l_temp_qty :=
209 inv_convert.inv_um_convert
210 (item_id => gme_common_pvt.materials (i).inventory_item_id
211 ,PRECISION => gme_common_pvt.g_precision
212 ,from_quantity => gme_common_pvt.materials (i).qty
213 ,from_unit => gme_common_pvt.materials (i).detail_uom
214 ,to_unit => gme_common_pvt.routings (1).routing_uom
215 ,from_name => NULL
216 ,to_name => NULL);
217
218 IF (l_temp_qty = -99999) THEN
219 gme_common_pvt.log_message
220 (p_message_code => 'INV_UOM_CONVERSION_ERROR'
221 ,p_token1_name => 'uom1'
222 ,p_token1_value => gme_common_pvt.materials
223 (i).detail_uom
224 ,p_token2_name => 'uom2'
225 ,p_token2_value => gme_common_pvt.routings (1).routing_uom
226 ,p_token3_name => 'module'
227 ,p_token3_value => 'GME_REROUTE_BATCH_PVT.validate_validity_id'
228 ,p_product_code => 'INV');
229 RAISE fnd_api.g_exc_error;
230 ELSE
231 l_total_output_qty_c :=
232 l_total_output_qty_c + l_temp_qty;
233 END IF;
234 END IF;--} /* If formula detail UOM = routing UOM */
235 END IF;--} /* l_material_details (i).line_type IN (1, 2) */
236 END LOOP;
237
238 IF l_total_output_qty_c = 0 THEN
239 gme_common_pvt.log_message ('GME_API_PROD_QTY_CANT_ZERO');
240 RAISE fnd_api.g_exc_error;
241 END IF;
242
243 /* Batch Quantities calculation */
244 l_gme_material_details.batch_id := x_batch_header_rec.batch_id;
245 --{
246 IF gme_material_details_dbl.fetch_tab
247 (p_material_detail => l_gme_material_details
248 ,x_material_detail => l_material_details) THEN
249 l_total_output_qty_b := 0;
250
251 FOR l_row_count IN 1 .. l_material_details.COUNT LOOP
252 --{
253 IF l_material_details (l_row_count).line_type > 0 THEN
254 --{
255 IF l_material_details (l_row_count).dtl_um =
256 gme_common_pvt.routings (1).routing_uom THEN
257 l_total_output_qty_b :=
258 l_total_output_qty_b
259 + l_material_details (l_row_count).plan_qty;
260 ELSE
261 l_temp_qty :=
262 inv_convert.inv_um_convert
263 (item_id => l_material_details
264 (l_row_count).inventory_item_id
265 ,PRECISION => gme_common_pvt.g_precision
266 ,from_quantity => l_material_details
267 (l_row_count).plan_qty
268 ,from_unit => l_material_details
269 (l_row_count).dtl_um
270 ,to_unit => gme_common_pvt.routings
271 (1).routing_uom
272 ,from_name => NULL
273 ,to_name => NULL);
274
275 IF (l_temp_qty = -99999) THEN
276 gme_common_pvt.log_message
277 (p_message_code => 'INV_UOM_CONVERSION_ERROR'
278 ,p_token1_name => 'uom1'
279 ,p_token1_value => l_material_details
280 (l_row_count).dtl_um
281 ,p_token2_name => 'uom2'
282 ,p_token2_value => gme_common_pvt.routings
283 (1).routing_uom
284 ,p_token3_name => 'module'
285 ,p_token3_value => 'GME_REROUTE_BATCH_PVT.validate_validity_id'
286 ,p_product_code => 'INV');
287 RAISE fnd_api.g_exc_error;
288 ELSE
289 l_total_output_qty_b :=
290 l_total_output_qty_b + l_temp_qty;
291 END IF;
292 END IF;--}
293 END IF; --} -- IF l_material_details (l_row_count).line_type > 0
294 END LOOP; -- l_row_count IN 1 .. l_material_details.Count
295
296 IF l_total_output_qty_b = 0 THEN
297 gme_common_pvt.log_message ('GME_API_PROD_QTY_CANT_ZERO');
298 RAISE fnd_api.g_exc_error;
299 END IF;
300 END IF;--}
301
302 gme_common_pvt.g_routing_scale_factor :=
303 l_total_output_qty_b / l_total_output_qty_c;
304
305 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
306 gme_debug.put_line ( 'Routing scale factor is '
307 || gme_common_pvt.g_routing_scale_factor);
308 END IF;
309 ELSE
310 gme_common_pvt.g_routing_scale_factor := 1;
311 END IF;--}
312
313 /* We have all the data needed to create steps, so lets call */
314 /* the create step API */
315 gme_create_step_pvt.create_batch_steps
316 (p_recipe_rout_step_tbl => gme_common_pvt.steps
317 ,p_recipe_rout_act_tbl => gme_common_pvt.activities
318 ,p_recipe_rout_resc_tbl => gme_common_pvt.resources
319 ,p_resc_parameters_tbl => l_process_parameters_tab
320 ,p_recipe_rout_matl_tbl => gme_common_pvt.routing_materials
321 ,p_routing_depd_tbl => gme_common_pvt.step_dependencies
322 ,p_gme_batch_header_rec => x_batch_header_rec
323 ,p_use_workday_cal => p_use_workday_cal
324 ,p_contiguity_override => p_contiguity_override
325 ,x_return_status => x_return_status);
326
327 IF (x_return_status = 'C') THEN
328 RAISE no_continous_periods;
329 ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
330 RAISE fnd_api.g_exc_error;
331 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
332 RAISE fnd_api.g_exc_unexpected_error;
333 END IF;
334
335 --create batch steps updates the batch header
336 IF (gme_common_pvt.steps.COUNT > 0) THEN
337 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
338 ELSE
339 l_no_steps := TRUE;
340 END IF;
341
342 OPEN cur_get_max_step_date;
343
344 FETCH cur_get_max_step_date
345 INTO l_max_step_date;
346
347 CLOSE cur_get_max_step_date;
348
349 IF l_max_step_date <> x_batch_header_rec.plan_cmplt_date THEN
350 x_batch_header_rec.plan_cmplt_date := l_max_step_date;
351 END IF;
352 ELSE
353 x_batch_header_rec.poc_ind := 'N';
354 l_no_steps := TRUE;
355 END IF;--}
356
357 --{ /* IF x_batch_header_rec.routing_id IS NOT NULL */
358 IF (l_no_steps) THEN
359 SELECT primary_uom_code
360 INTO l_prim_item_um
361 FROM mtl_system_items_b
362 WHERE inventory_item_id = l_inventory_item_id
363 AND organization_id = x_batch_header_rec.organization_id;
364
365 OPEN get_prim_prod (x_batch_header_rec.batch_id
366 ,l_inventory_item_id);
367
368 FETCH get_prim_prod INTO l_prim_prod_qty, l_prim_prod_um;
369
370 IF get_prim_prod%FOUND THEN
371 l_prim_prod_found := TRUE;
372 ELSE
373 l_prim_prod_found := FALSE;
374 END IF;
375
376 CLOSE get_prim_prod;
377 --{
378 IF l_prim_prod_found THEN
379
380 l_temp_qty :=
381 inv_convert.inv_um_convert
382 (item_id => l_inventory_item_id
383 ,PRECISION => gme_common_pvt.g_precision
384 ,from_quantity => l_prim_prod_qty
385 ,from_unit => l_prim_prod_um
386 ,to_unit => l_prim_item_um
387 ,from_name => NULL
388 ,to_name => NULL);
389
390 IF (l_temp_qty = -99999) THEN
391 gme_common_pvt.log_message
392 (p_message_code => 'INV_UOM_CONVERSION_ERROR'
393 ,p_token1_name => 'uom1'
394 ,p_token1_value => l_prim_prod_um
395 ,p_token2_name => 'uom2'
396 ,p_token2_value => l_prim_item_um
397 ,p_token3_name => 'module'
398 ,p_token3_value => 'GME_REROUTE_BATCH_PVT.reroute_batch'
399 ,p_product_code => 'INV');
400 RAISE fnd_api.g_exc_error;
401 END IF;
402
403 IF (gme_common_pvt.calc_date_from_prod_rule
404 (p_organization_id => x_batch_header_rec.organization_id
405 ,p_inventory_item_id => l_inventory_item_id
406 ,p_item_qty => l_temp_qty
407 ,p_start_date => x_batch_header_rec.plan_start_date
408 ,p_cmplt_date => x_batch_header_rec.plan_cmplt_date
409 ,x_start_date => l_plan_start_date
410 ,x_cmplt_date => l_plan_cmplt_date) ) THEN
411 l_no_prod_rule_found := FALSE;
412 x_batch_header_rec.plan_start_date := l_plan_start_date;
413 x_batch_header_rec.plan_cmplt_date := l_plan_cmplt_date;
414 ELSE
415 l_no_prod_rule_found := TRUE;
416 END IF;
417 ELSE
418 -- prim prod was not found...
419 l_no_prod_rule_found := TRUE;
420
421 END IF;--}
422 --{
423 IF l_no_prod_rule_found THEN
424 IF x_batch_header_rec.plan_start_date IS NOT NULL THEN
425 --FPBug#4585491 if no production rule is found plan_completion_date will get default to start date
426 --AND x_batch_header_rec.plan_cmplt_date IS NULL THEN
427 x_batch_header_rec.plan_cmplt_date :=
428 x_batch_header_rec.plan_start_date;
429 ELSIF x_batch_header_rec.plan_start_date IS NULL
430 AND x_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
431 x_batch_header_rec.plan_start_date :=
432 x_batch_header_rec.plan_cmplt_date;
433 ELSIF x_batch_header_rec.plan_start_date IS NULL
434 AND x_batch_header_rec.plan_cmplt_date IS NULL THEN
435 x_batch_header_rec.plan_start_date :=
436 gme_common_pvt.g_timestamp;
437 x_batch_header_rec.plan_cmplt_date :=
438 gme_common_pvt.g_timestamp;
439 END IF;
440
441 IF (g_debug = gme_debug.g_log_statement) THEN
442 gme_debug.put_line
443 ( 'production rule start_date '
444 || TO_CHAR
445 (x_batch_header_rec.plan_start_date
446 ,'DD-MON-YYYY HH24:MI:SS') );
447 gme_debug.put_line
448 ( 'production rule end_date '
449 || TO_CHAR
450 (x_batch_header_rec.plan_cmplt_date
451 ,'DD-MON-YYYY HH24:MI:SS') );
452 END IF;
453 END IF;--}
454 END IF;--}
455
456
457 OPEN cur_get_material (x_batch_header_rec.batch_id);
458
459 FETCH cur_get_material
460 BULK COLLECT INTO l_material_detail_ids_tab, l_phantom_ids_tab;
461
462 CLOSE cur_get_material;
463
464 FOR i IN 1 .. l_material_detail_ids_tab.COUNT LOOP
465 l_material_detail_rec.material_detail_id :=
466 l_material_detail_ids_tab (i);
467 l_phantom_associated_to_step := FALSE;
468 gme_common_pvt.material_date_change
469 (p_material_detail_id => l_material_detail_rec.material_detail_id
470 ,p_material_date => NULL
471 ,x_return_status => x_return_status);
472 IF (g_debug = gme_debug.g_log_statement) THEN
473 gme_debug.put_line
474 ( 'after call to gme_common_pvt.material_date_change
475 material_detail_id is='
476 ||to_char(l_material_detail_ids_tab (i))
477 ||' return status = '||x_return_status);
478 END IF;
479
480 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
481 RAISE fnd_api.g_exc_error;
482 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
483 RAISE fnd_api.g_exc_unexpected_error;
484 END IF;
485 /*Sunitha Ch Bug 5353941 seperated the check whether the material is assciated with step or not and
486 if it is not assciated then added a select statement to retrieve the planed start date of the parent batch into l_phantom_batch_header_rec.plan_cmplt_date*/
487
488 IF ( l_phantom_ids_tab (i) IS NOT NULL ) THEN
489 /*Sunitha ch Bug#5391396 included the code to rescedule_step of the child batch when the product
490 is associated to step and its release type is autobystep.*/
491 IF NOT gme_material_details_dbl.fetch_row(l_material_detail_rec, l_material_detail_rec) THEN
492 l_proc := 'gme_material_details_dbl.fetch_row';
493 RAISE error_dbl;
494 END IF;
495 -- IF(l_material_detail_rec.material_requirement_date <> l_db_mtl_dtl_rec.material_requirement_date ) THEN
496 l_phantom_batch_header_rec.batch_id:= l_phantom_ids_tab (i);
497 IF(gme_common_pvt.is_material_auto_release(l_material_detail_rec.phantom_line_id)=3 AND
498 gme_common_pvt.is_material_assoc_to_step
499 (l_material_detail_rec.phantom_line_id )) THEN
500 l_phantom_associated_to_step := TRUE;
501 l_phantom_batch_step_rec.batch_id:= l_phantom_ids_tab (i);
502 SELECT batchstep_id INTO l_phantom_batch_step_rec.batchstep_id
503 FROM gme_batch_step_items
504 WHERE batch_id = l_phantom_ids_tab (i)
505 AND material_detail_id = l_material_detail_rec.phantom_line_id;
506 IF NOT gme_batch_steps_dbl.fetch_row(l_phantom_batch_step_rec, l_phantom_batch_step_rec) THEN
507 l_proc := 'gme_batch_steps_dbl.fetch_row';
508 RAISE error_dbl;
509 END IF;
510 l_phantom_batch_step_rec.plan_cmplt_date:=l_material_detail_rec.material_requirement_date;
511 l_phantom_batch_step_rec.plan_start_date:=NULL;
512 gme_reschedule_step_pvt.reschedule_step
513 (p_batch_step_rec => l_phantom_batch_step_rec
514 ,p_source_step_id_tbl => l_step_tbl
515 ,p_contiguity_override => fnd_api.g_true
516 ,p_reschedule_preceding => fnd_api.g_true
517 ,p_reschedule_succeeding => fnd_api.g_true
518 ,p_use_workday_cal => fnd_api.g_false
519 ,x_batch_step_rec => x_batch_step_rec
520 ,x_return_status => x_return_status);
521 ELSE
522 gme_debug.put_line('sc l_phantom_batch_header_rec.batch_id is '||l_phantom_batch_header_rec.batch_id);
523 l_phantom_batch_header_rec.plan_cmplt_date:=l_material_detail_rec.material_requirement_date;
524 gme_debug.put_line('sc l_phantom_batch_header_rec.plan_cmplt_date is '||to_char(l_phantom_batch_step_rec.plan_cmplt_date, 'DD-MON-YY HH24:MI:SS'));
525 gme_reschedule_batch_pvt.reschedule_batch
526 (p_batch_header_rec => l_phantom_batch_header_rec
527 ,p_use_workday_cal => fnd_api.g_false
528 ,p_contiguity_override => fnd_api.g_true
529 ,x_batch_header_rec => l_phantom_batch_header_rec_out
530 ,x_return_status => x_return_status);
531 gme_debug.put_line('sc l_phantom_batch_header_rec_out.plan_cmplt_date is '||to_char(l_phantom_batch_header_rec_out.plan_cmplt_date, 'DD-MON-YY HH24:MI:SS'));
532 gme_debug.put_line('sc l_phantom_batch_header_rec_out.batch_id is '||l_phantom_batch_header_rec_out.batch_id);
533 END IF;
534 END IF;
535
536 --FPBug#4585491 Begin
537 --commented the following code
538 /*--this is neccessary so that correct status of W gets send to the form.
539 IF (x_return_status = 'W' AND (NOT l_W_ReturnedAtLeastOnce)) THEN
540 l_W_ReturnedAtLeastOnce := TRUE;
541 END IF;*/
542
543 /*
544 The above material_date_change returns different status as described below
545 R: When reservations are deleted for a material line
546 M: When MO Allocations are deleted for a material line
547 B: When Both reservations and material lines are deleted for a material line
548 */
549 IF x_return_status = 'R' THEN
550 l_R_count := l_R_count + 1;
551 ELSIF x_return_status = 'M' THEN
552 l_M_count := l_M_count + 1;
553 ELSIF x_return_status = 'B' THEN
554 l_B_count := l_B_count + 1;
555 END IF;
556 --FPBug#4585491 End
557 END LOOP; /* FOR i IN 1..l_material_detail_ids_tab.COUNT */
558
559 --FPBug#4585491 Begin
560 --commented the following code
561 /*IF (l_W_ReturnedAtLeastOnce) AND (x_return_status = fnd_api.g_ret_sts_success) THEN
562 x_return_status := 'W';
563 END IF; */
564
565 /* If any of the reservations are MO allocations deleted then respective message
566 will be put on the stack but return status will be 'S' to form or API */
567 IF (l_B_count > 0) OR (l_R_count > 0 AND l_M_count > 0) THEN
568 --atleast for one material line MO allocations and reservations are deleted
569 gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
570 --x_return_status := 'W';
571 ELSIF l_R_count > 0 THEN
572 ----atleast for one material line reservations are deleted
573 gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
574 --x_return_status := 'W';
575 ELSIF l_M_count > 0 THEN
576 ----atleast for one material line MO allocations are deleted
577 gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
578 --x_return_status := 'W';
579 END IF;
580 x_return_status := fnd_api.g_ret_sts_success;
581 --FPBug#4585491 End
582 --Susruth Bug#5359091 Finite Scheduled indicator is set back to 0 once the batch is rerouted. start.
583 IF x_batch_header_rec.FINITE_SCHEDULED_IND = 1 THEN
584 x_batch_header_rec.FINITE_SCHEDULED_IND := 0;
585 END IF;
586 -- Bug#5359091 end.
587 IF NOT gme_batch_header_dbl.update_row (x_batch_header_rec) THEN
588 RAISE fnd_api.g_exc_error;
589 END IF;
590
591 IF (NVL (g_debug, 0) IN
592 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
593 gme_debug.put_line ( g_pkg_name
594 || '.'
595 || l_api_name
596 || ':'
597 || 'Exiting with '
598 || x_return_status);
599 END IF;
600 EXCEPTION
601 WHEN no_continous_periods THEN
602 RETURN;
603 WHEN fnd_api.g_exc_error THEN
604 x_return_status := fnd_api.g_ret_sts_error;
605 WHEN fnd_api.g_exc_unexpected_error THEN
606 x_return_status := fnd_api.g_ret_sts_unexp_error;
607
608 IF (NVL (g_debug, 0) > 0) THEN
609 gme_debug.put_line ( g_pkg_name
610 || '.'
611 || l_api_name
612 || ':'
613 || 'UNEXPECTED:'
614 || SQLERRM);
615 END IF;
616 WHEN error_dbl THEN
617 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
618 x_return_status := FND_API.g_ret_sts_unexp_error;
619
620 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
621 gme_debug.put_line (g_pkg_name||'.'||l_api_name|| ': '
622 || l_proc|| ' unexpected error: '|| SQLERRM);
623 END IF;
624 WHEN OTHERS THEN
625 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
626 x_return_status := fnd_api.g_ret_sts_unexp_error;
627
628 IF (NVL (g_debug, 0) > 0) THEN
629 gme_debug.put_line ( g_pkg_name
630 || '.'
631 || l_api_name
632 || ':'
633 || 'OTHERS:'
634 || SQLERRM);
635 END IF;
636 END reroute_batch;
637
638 /*===========================================================================================
639 Procedure
640 delete_all_steps
641 Description
642 This particular procedure is used to delete all the steps in the batch.
643 Parameters
644 p_batch_id Batch ID
645 x_return_status outcome of the API call
646 S - Success
647 E - Error
648 U - Unexpected error
649 =============================================================================================*/
650 PROCEDURE delete_all_steps (
651 p_batch_id IN NUMBER
652 ,x_return_status OUT NOCOPY VARCHAR2)
653 IS
654 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ALL_STEPS';
655 BEGIN
656 IF (NVL (g_debug, 0) IN
657 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
658 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
659 || 'Entering');
660 END IF;
661
662 /* Set the return status to success initially */
663 x_return_status := fnd_api.g_ret_sts_success;
664
665 DELETE FROM gme_process_parameters
666 WHERE batch_id = p_batch_id;
667
668 DELETE FROM gme_resource_txns
669 WHERE doc_id = p_batch_id;
670
671 DELETE FROM gme_resource_txns_gtmp
672 WHERE doc_id = p_batch_id;
673
674 DELETE FROM gme_batch_step_resources
675 WHERE batch_id = p_batch_id;
676
677 DELETE FROM gme_batch_step_items
678 WHERE batch_id = p_batch_id;
679
680 DELETE FROM gme_batch_step_dependencies
681 WHERE batch_id = p_batch_id;
682
683 DELETE FROM gme_batch_step_charges
684 WHERE batch_id = p_batch_id;
685
686 DELETE FROM gme_batch_step_activities
687 WHERE batch_id = p_batch_id;
688
689 DELETE FROM gme_batch_steps
690 WHERE batch_id = p_batch_id;
691
692 IF (NVL (g_debug, 0) IN
693 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
694 gme_debug.put_line ( g_pkg_name
695 || '.'
696 || l_api_name
697 || ':'
698 || 'Exiting with '
699 || x_return_status);
700 END IF;
701 EXCEPTION
702 WHEN fnd_api.g_exc_error THEN
703 x_return_status := fnd_api.g_ret_sts_error;
704 WHEN fnd_api.g_exc_unexpected_error THEN
705 x_return_status := fnd_api.g_ret_sts_unexp_error;
706
707 IF (NVL (g_debug, 0) > 0) THEN
708 gme_debug.put_line ( g_pkg_name
709 || '.'
710 || l_api_name
711 || ':'
712 || 'UNEXPECTED:'
713 || SQLERRM);
714 END IF;
715 WHEN OTHERS THEN
716 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
717 x_return_status := fnd_api.g_ret_sts_unexp_error;
718
719 IF (NVL (g_debug, 0) > 0) THEN
720 gme_debug.put_line ( g_pkg_name
721 || '.'
722 || l_api_name
723 || ':'
724 || 'OTHERS:'
725 || SQLERRM);
726 END IF;
727 END delete_all_steps;
728
729 PROCEDURE validate_validity_id_from_pub (
730 p_batch_header_rec IN gme_batch_header%ROWTYPE
731 ,p_validity_rule_id IN NUMBER
732 ,x_return_status OUT NOCOPY VARCHAR2)
733 IS
734 l_api_name CONSTANT VARCHAR2 (30) := 'validate_validity_id_from_pub';
735
736 CURSOR cur_get_validity_rule_details (v_validity_rule_id IN NUMBER)
737 IS
738 SELECT gr.formula_id, grvr.inventory_item_id
739 FROM gmd_recipe_validity_rules grvr, gmd_recipes gr
740 WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
741 AND gr.recipe_id = grvr.recipe_id;
742
743 CURSOR cur_get_material_details (
744 v_batch_id IN NUMBER
745 ,v_inventory_item_id IN NUMBER)
746 IS
747 SELECT plan_qty, dtl_um
748 FROM gme_material_details
749 WHERE batch_id = v_batch_id
750 AND line_type = 1
751 AND inventory_item_id = v_inventory_item_id;
752
753 l_formula_id NUMBER;
754 l_inventory_item_id NUMBER;
755 l_plan_qty NUMBER;
756 l_dtl_um gme_material_details.dtl_um%TYPE;
757 BEGIN
758 IF (NVL (g_debug, 0) IN
759 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
760 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
761 || 'Entering');
762 END IF;
763
764 /* Set the return status to success initially */
765 x_return_status := fnd_api.g_ret_sts_success;
766
767 /* Don't allow the Batch to be Rerouted if the Batch Status is not pending */
768 IF (p_batch_header_rec.batch_status <> 1) THEN
769 gme_common_pvt.log_message ('GME_API_INVALID_BATCH_REROUTE');
770 RAISE fnd_api.g_exc_error;
771 END IF;
772
773 /* LCF Batches may not have a validity rule
774 We do not want to allow reroute for these batches */
775 IF (p_batch_header_rec.recipe_validity_rule_id IS NULL) THEN
776 --Bug#5439736 replaced the message
777 gme_common_pvt.log_message ('GME_REROUTE_NOT_FOR_LCF');
778 RAISE fnd_api.g_exc_error;
779 END IF;
780
781 /* New validity rule should be different from the existing */
782 IF p_batch_header_rec.recipe_validity_rule_id = p_validity_rule_id THEN
783 gme_common_pvt.log_message ('GME_API_SAME_VALIDITY_RULE');
784 RAISE fnd_api.g_exc_error;
785 END IF;
786
787 OPEN cur_get_validity_rule_details (p_validity_rule_id);
788
789 FETCH cur_get_validity_rule_details
790 INTO l_formula_id, l_inventory_item_id;
791
792 IF (cur_get_validity_rule_details%NOTFOUND) THEN
793 CLOSE cur_get_validity_rule_details;
794
795 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
796 RAISE fnd_api.g_exc_error;
797 END IF;
798
799 CLOSE cur_get_validity_rule_details;
800
801 /* ????? */
802 OPEN cur_get_material_details (p_batch_header_rec.batch_id
803 ,l_inventory_item_id);
804
805 FETCH cur_get_material_details
806 INTO l_plan_qty, l_dtl_um;
807
808 IF (cur_get_material_details%NOTFOUND) THEN
809 CLOSE cur_get_material_details;
810 --Bug#5439736 replaced the message
811 gme_common_pvt.log_message ('GME_REROUTE_NO_PRIM_PROD');
812 RAISE fnd_api.g_exc_error;
813 END IF;
814
815 CLOSE cur_get_material_details;
816
817 /* whether the new validity rule belongs to the same formula as the batch */
818 IF (p_batch_header_rec.formula_id <> l_formula_id) THEN
819 gme_common_pvt.log_message ('GME_API_VALIDITY_DIFF_FORM');
820 RAISE fnd_api.g_exc_error;
821 END IF;
822
823 IF (NVL (g_debug, 0) IN
824 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
825 gme_debug.put_line ( g_pkg_name
826 || '.'
827 || l_api_name
828 || ':'
829 || 'Exiting with '
830 || x_return_status);
831 END IF;
832 EXCEPTION
833 WHEN fnd_api.g_exc_error THEN
834 x_return_status := fnd_api.g_ret_sts_error;
835 WHEN fnd_api.g_exc_unexpected_error THEN
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837
838 IF (NVL (g_debug, 0) > 0) THEN
839 gme_debug.put_line ( g_pkg_name
840 || '.'
841 || l_api_name
842 || ':'
843 || 'UNEXPECTED:'
844 || SQLERRM);
845 END IF;
846 WHEN OTHERS THEN
847 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
848 x_return_status := fnd_api.g_ret_sts_unexp_error;
849
850 IF (NVL (g_debug, 0) > 0) THEN
851 gme_debug.put_line ( g_pkg_name
852 || '.'
853 || l_api_name
854 || ':'
855 || 'OTHERS:'
856 || SQLERRM);
857 END IF;
858 END validate_validity_id_from_pub;
859
860 /*===========================================================================================
861 Procedure
862 validate_validity_id
863 Description
864 This particular procedure is used to validate the validity rule id passed in.
865 Parameters
866 p_validity_rule_id The validity_rule
867
868 =============================================================================================*/
869 PROCEDURE validate_validity_id (
870 p_batch_header_rec IN gme_batch_header%ROWTYPE
871 ,p_validity_rule_id IN NUMBER
872 ,x_return_status OUT NOCOPY VARCHAR2)
873 IS
874 l_api_name CONSTANT VARCHAR2 (30) := 'validate_validity_id';
875
876 CURSOR cur_validate_validity (
877 v_batch_type IN NUMBER
878 ,v_validity_rule_id IN NUMBER
879 ,v_qty IN NUMBER
880 ,v_laboratory_ind IN NUMBER)
881 IS
882 SELECT COUNT (1)
883 FROM gmd_recipe_validity_rules v
884 WHERE recipe_validity_rule_id = v_validity_rule_id
885 AND ( (validity_rule_status BETWEEN 700 AND 799)
886 OR (validity_rule_status BETWEEN 900 AND 999)
887 OR ( v_laboratory_ind = 1
888 AND validity_rule_status BETWEEN 400 AND 699) )
889 AND ( (v_batch_type = 0 AND recipe_use = 0)
890 OR (v_batch_type = 10 AND recipe_use IN (0, 1) ) )
891 AND delete_mark = 0
892 AND inv_min_qty <= v_qty
893 AND inv_max_qty >= v_qty;
894
895 CURSOR cur_prod_details (
896 v_batch_id IN NUMBER
897 ,v_validity_rule_id IN NUMBER)
898 IS
899 SELECT d.inventory_item_id, d.organization_id, d.plan_qty
900 ,d.dtl_um line_um, msib.primary_uom_code item_um
901 FROM gme_material_details d
902 ,mtl_system_items_b msib
903 ,gmd_recipe_validity_rules v
904 WHERE d.batch_id = v_batch_id
905 AND d.line_type = 1
906 AND msib.inventory_item_id = d.inventory_item_id
907 AND msib.organization_id = d.organization_id
908 AND v.recipe_validity_rule_id = v_validity_rule_id
909 AND d.inventory_item_id = v.inventory_item_id;
910
911 l_prod_qty NUMBER;
912 l_conv_qty NUMBER;
913 l_count PLS_INTEGER;
914 BEGIN
915 IF (NVL (g_debug, 0) IN
916 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
917 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
918 || 'Entering');
919 END IF;
920
921 /* Set the return status to success initially */
922 x_return_status := fnd_api.g_ret_sts_success;
923
924 /* determine prod qty and passing */
925 /* item_id, qty and uom to pass to cursor validate_validity */
926 FOR get_rec IN cur_prod_details (p_batch_header_rec.batch_id
927 ,p_validity_rule_id) LOOP
928 IF (get_rec.line_um = get_rec.item_um) THEN
929 l_prod_qty := NVL (l_prod_qty, 0) + get_rec.plan_qty;
930 ELSE
931 l_conv_qty :=
932 inv_convert.inv_um_convert
933 (item_id => get_rec.inventory_item_id
934 ,PRECISION => gme_common_pvt.g_precision
935 ,from_quantity => get_rec.plan_qty
936 ,from_unit => get_rec.line_um
937 ,to_unit => get_rec.item_um
938 ,from_name => NULL
939 ,to_name => NULL);
940
941 IF (l_conv_qty = -99999) THEN
942 gme_common_pvt.log_message
943 (p_message_code => 'INV_UOM_CONVERSION_ERROR'
944 ,p_token1_name => 'uom1'
945 ,p_token1_value => get_rec.line_um
946 ,p_token2_name => 'uom2'
947 ,p_token2_value => get_rec.item_um
948 ,p_token3_name => 'module'
949 ,p_token3_value => 'GME_REROUTE_BATCH_PVT.validate_validity_id'
950 ,p_product_code => 'INV');
951 RAISE fnd_api.g_exc_error;
952 ELSE
953 l_prod_qty := NVL (l_prod_qty, 0) + l_conv_qty;
954 END IF;
955 END IF;
956 END LOOP;
957
958 OPEN cur_validate_validity (p_batch_header_rec.batch_type
959 ,p_validity_rule_id
960 ,l_prod_qty
961 ,p_batch_header_rec.laboratory_ind);
962
963 FETCH cur_validate_validity
964 INTO l_count;
965
966 CLOSE cur_validate_validity;
967
968 IF (l_count = 0) THEN
969 gme_common_pvt.log_message ('GME_API_INVALID_VALIDITY');
970 RAISE fnd_api.g_exc_error;
971 END IF;
972
973 IF (NVL (g_debug, 0) IN
974 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
975 gme_debug.put_line ( g_pkg_name
976 || '.'
977 || l_api_name
978 || ':'
979 || 'Exiting with '
980 || x_return_status);
981 END IF;
982 EXCEPTION
983 WHEN fnd_api.g_exc_error THEN
984 x_return_status := fnd_api.g_ret_sts_error;
985 WHEN fnd_api.g_exc_unexpected_error THEN
986 x_return_status := fnd_api.g_ret_sts_unexp_error;
987
988 IF (NVL (g_debug, 0) > 0) THEN
989 gme_debug.put_line ( g_pkg_name
990 || '.'
991 || l_api_name
992 || ':'
993 || 'UNEXPECTED:'
994 || SQLERRM);
995 END IF;
996 WHEN OTHERS THEN
997 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
998 x_return_status := fnd_api.g_ret_sts_unexp_error;
999
1000 IF (NVL (g_debug, 0) > 0) THEN
1001 gme_debug.put_line ( g_pkg_name
1002 || '.'
1003 || l_api_name
1004 || ':'
1005 || 'OTHERS:'
1006 || SQLERRM);
1007 END IF;
1008 END validate_validity_id;
1009 END gme_reroute_batch_pvt;