1 PACKAGE BODY gme_create_batch_pvt AS
2 /* $Header: GMEVCRBB.pls 120.33.12010000.2 2008/11/06 03:14:47 srpuri ship $ */
3 g_debug VARCHAR2 (5)
4 := NVL (fnd_profile.VALUE ('AFLOG_LEVEL'), 99);
5 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_CREATE_BATCH_PVT';
6
7 /***********************************************************/
8 /* Oracle Process Manufacturing Process Execution APIs */
9 /* */
10 /* File Name: GMEVCRBB.pls */
11 /* Contents: Private layer for batch creation API */
12 /* HISTORY */
13 /* SivakumarG 05-APR-2006 */
14 /* put some debug messages in edit text copy code */
15 /* sunitha bug # 5484529 selecting the sum of the plan */
16 /* quantity without converting it to the UOM that the user entered */
17 /* while creating the batch into l_temp_qty .Convert the */
18 /* p_batch_size(user entered product plan quantity */
19 /* to the Routing uom and then Compare it with l_temp_qty.*/
20 /* Kapil M. Bug# 5458674 */
21 /* Changes to Support LCF Batches for GMO */
22 /* Archana Mundhe Bug 5763818 Modified the code to use */
23 /* ERES constants that are added to gme_common_pvt instead*/
24 /* of using the hardcoded ERES event names such as */
25 /* 'oracle.apps.gme...' */
26 /* Swapna K Bug#6398619 calls to validate_wip_entity are changed */
27 /* for the manual doc ordering */
28
29 /* Please use this new format for comments going forward.
30
31 G. Muratore 12/26/2007 Bug 6665602 Back out fix for 5484529. This fix was
32 incorrect. The select fixed in 5484529 was no correct because it did not convert
33 all product lines to one common uom. Therefore the summation is invalid.
34 Bug 5484529 will ahve to be re-addressed in a different way.
35 Swapna K Bug#6738476 Added parameter,p_batch_header_rec
36 to the procedure call,gme_phantom_pvt.create_phantom
37 10-JAN-2008 Rajesh Patangya Bug # 6752637
38 MTQ Quantity should be calculated based on product in place of just copy from
39 the routing, This is required by PS engine, New Function UPDATE_STEP_MTQ added
40
41 G. Muratore 07/31/2008 Bug 7265006 Correct fix for 5512352.
42 This fix was incorrect as to where the new condition was used when trying to optimize shortage checking.
43 Moved g_no_phant_short_check to shortage check condition as it was originally intended
44 for shortage checking only. Unfortunately it stopped all the other logic for phantom batches.
45 Now invisible move orders and high level reservations will get created for phantom batches.
46
47 G. Muratore 08/18/2008 Bug 7284242
48 This fix was to correct the algorithm for deriving the scale factor when creating a batch by
49 'PRODUCT'. Prior to this fix the code did not treat fixed scale products differently from
50 linear scale products.
51 K. swapna 10/21/2008 Bug 7493614
52 Moved the call to wf_event.raise call to the end of the create_batch
53 procedure as the sample can be created at the end of the batch creation.
54 ********************************************************* */
55
56 /************************************************************
57 * *
58 * CONSTRUCT_BATCH_HEADER *
59 * *
60 ************************************************************/
61 FUNCTION construct_batch_header (
62 p_batch_header_rec IN gme_batch_header%ROWTYPE
63 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE)
64 RETURN BOOLEAN
65 IS
66 l_count NUMBER;
67 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTRUCT_BATCH_HEADER';
68 BEGIN
69 IF g_debug <= gme_debug.g_log_procedure THEN
70 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
71 || l_api_name);
72 END IF;
73
74 /* Create defaults for mandatory columns that have not been specified */
75 l_count := gme_common_pvt.g_error_count;
76 x_batch_header_rec := p_batch_header_rec;
77
78 IF x_batch_header_rec.organization_id IS NULL THEN
79 fnd_message.set_name ('INV', 'INV_INT_ORGCODE');
80 fnd_msg_pub.ADD;
81 END IF;
82
83 x_batch_header_rec.prod_id := NVL (x_batch_header_rec.prod_id, 0);
84 x_batch_header_rec.prod_sequence :=
85 NVL (x_batch_header_rec.prod_sequence, 0);
86 x_batch_header_rec.plan_start_date :=
87 NVL (x_batch_header_rec.plan_start_date, gme_common_pvt.g_timestamp);
88 x_batch_header_rec.plan_cmplt_date :=
89 NVL (x_batch_header_rec.plan_cmplt_date, gme_common_pvt.g_timestamp);
90 x_batch_header_rec.due_date :=
91 NVL (x_batch_header_rec.due_date, gme_common_pvt.g_timestamp);
92 x_batch_header_rec.batch_status := gme_common_pvt.G_BATCH_PENDING;
93 x_batch_header_rec.priority_value :=
94 NVL (x_batch_header_rec.priority_value, 0);
95 x_batch_header_rec.priority_code :=
96 NVL (x_batch_header_rec.priority_code, 0);
97 x_batch_header_rec.print_count := 0;
98 x_batch_header_rec.batch_close_date := NULL;
99 x_batch_header_rec.actual_cost_ind := 'N';
100 x_batch_header_rec.gl_posted_ind := 0;
101 x_batch_header_rec.delete_mark := 0;
102 x_batch_header_rec.automatic_step_calculation := 1;
103
104 IF l_count <> gme_common_pvt.g_error_count THEN
105 RETURN FALSE;
106 ELSE
107 RETURN TRUE;
108 END IF;
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF g_debug <= gme_debug.g_log_unexpected THEN
112 gme_debug.put_line ( 'When others exception in '
113 || g_pkg_name
114 || '.'
115 || l_api_name
116 || ' Error is '
117 || SQLERRM);
118 END IF;
119
120 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
121 RETURN FALSE;
122 END construct_batch_header;
123
124 PROCEDURE validate_wip_entity (p_organization_id IN NUMBER,
125 p_batch_no IN VARCHAR2,
126 x_return_status OUT NOCOPY VARCHAR2) IS
127 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_WIP_ENTITY';
128 l_temp NUMBER;
129 duplicate_wip_entity EXCEPTION;
130 CURSOR Cur_wip_entity IS
131 SELECT 1
132 FROM DUAL
133 WHERE EXISTS (SELECT 1
134 FROM wip_entities
135 WHERE organization_id = p_organization_id
136 AND wip_entity_name = p_batch_no);
137 BEGIN
138 x_return_status := fnd_api.g_ret_sts_success;
139 OPEN Cur_wip_entity;
140 FETCH Cur_wip_entity INTO l_temp;
141 IF (Cur_wip_entity%FOUND) THEN
142 CLOSE Cur_wip_entity;
143 RAISE duplicate_wip_entity;
144 END IF;
145 CLOSE Cur_wip_entity;
146 EXCEPTION
147 WHEN duplicate_wip_entity THEN
148 x_return_status := fnd_api.g_ret_sts_error;
149 gme_common_pvt.log_message('GME_WIP_ENTITY_EXISTS');
150 WHEN OTHERS THEN
151 IF g_debug <= gme_debug.g_log_unexpected THEN
152 gme_debug.put_line ( 'When others exception in '
153 || g_pkg_name
154 || '.'
155 || l_api_name
156 || ' Error is '
157 || SQLERRM);
158 END IF;
159 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
160 x_return_status := fnd_api.g_ret_sts_unexp_error;
161 END validate_wip_entity;
162
163 PROCEDURE create_batch (
164 p_validation_level IN NUMBER
165 := gme_common_pvt.g_max_errors
166 ,p_batch_header_rec IN gme_batch_header%ROWTYPE
167 ,p_batch_size IN NUMBER
168 ,p_batch_size_uom IN VARCHAR2
169 ,p_creation_mode IN VARCHAR2
170 ,p_ignore_qty_below_cap IN VARCHAR2
171 DEFAULT fnd_api.g_true
172 ,p_use_workday_cal IN VARCHAR2
173 ,p_contiguity_override IN VARCHAR2
174 ,p_is_phantom IN VARCHAR2 DEFAULT 'N'
175 ,p_use_least_cost_validity_rule IN VARCHAR2 := fnd_api.g_false
176 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
177 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
178 ,x_return_status OUT NOCOPY VARCHAR2)
179 IS
180 l_in_batch_header gme_batch_header%ROWTYPE;
181 l_formula_master fm_form_mst%ROWTYPE;
182 l_formula_material fm_matl_dtl%ROWTYPE;
183 l_recipe_validity_rule gmd_recipe_validity_rules%ROWTYPE;
184 l_item_master mtl_system_items_kfv%ROWTYPE;
185 l_gme_material_detail gme_material_details%ROWTYPE;
186 l_text_header gme_text_header%ROWTYPE;
187 l_text_string gme_text_table.text%TYPE;
188 l_material_details gme_common_pvt.material_details_tab;
189 p_material_details gme_common_pvt.material_details_tab;
190 l_in_material_details gme_common_pvt.material_details_tab;
191 l_material_details_in gme_material_details%ROWTYPE;
192 l_item_masters gme_common_pvt.item_masters_tab;
193 l_text_table gme_common_pvt.text_tab;
194 l_recipe gmd_recipes%ROWTYPE;
195 l_from_uom mtl_units_of_measure.uom_code%TYPE;
196 l_to_uom mtl_units_of_measure.uom_code%TYPE;
197 l_prim_item_um mtl_units_of_measure.uom_code%TYPE;
198 l_prim_prod_um mtl_units_of_measure.uom_code%TYPE;
199 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
200 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_BATCH';
201 l_uom VARCHAR2 (4);
202 l_return_status VARCHAR2 (1);
203 l_message_list VARCHAR2 (2000);
204 l_production_um VARCHAR2 (4);
205 l_primaries VARCHAR2 (15);
206 l_item_no VARCHAR2 (2000);
207 l_prefix VARCHAR2 (32);
208 l_enhanced_pi_ind VARCHAR2 (1);
209 l_dispense_required VARCHAR2 (1);
210 l_number_of_product_lines NUMBER;
211 l_number_of_byproduct_lines NUMBER;
212 l_number_of_ingredient_lines NUMBER;
213 l_number_of_formula_lines NUMBER;
214 l_number_of_text_lines NUMBER;
215 l_text_code NUMBER;
216 l_count NUMBER := 0;
217 l_message_count NUMBER;
218 l_doc_numbering NUMBER;
219 l_process_loss NUMBER;
220 l_return_code NUMBER;
221 l_total_input_qty NUMBER;
222 l_total_output_qty NUMBER;
223 l_total_output_qty_fixed NUMBER;
224 l_total_output_qty_b NUMBER;
225 l_total_output_qty_c NUMBER;
226 l_production_rule_qty NUMBER;
227 l_requested_qty NUMBER;
228 l_temp_qty NUMBER;
229 l_item_id NUMBER;
230 l_inv_trans_count NUMBER;
231 l_rsc_trans_count NUMBER;
232 l_prim_prod_qty NUMBER;
233 l_error_count NUMBER;
234 l_error_count_after NUMBER;
235 l_msg_data VARCHAR2(2000);
236 l_duration NUMBER;
237 l_cal_count NUMBER;
238 l_cont_ind NUMBER;
239 l_config_id NUMBER;
240 l_excp_tbl_ptr NUMBER; -- nsinghi bug#5200395
241 l_return BOOLEAN;
242 l_no_prod_rule_found BOOLEAN;
243 l_prim_prod_found BOOLEAN;
244 l_exception_material_tbl gme_common_pvt.exceptions_tab;
245 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
246 l_phantom_exc_material_tbl gme_common_pvt.exceptions_tab; -- nsinghi bug#5200395
247 l_phantom_exc_material_tbl_out gme_common_pvt.exceptions_tab; -- nsinghi bug#5200395
248 l_batch_header_rec gme_batch_header%ROWTYPE;
249 l_batchstep_rec gme_batch_steps%ROWTYPE;
250 x_batchstep_rec gme_batch_steps%ROWTYPE;
251 l_in_material_detail gme_material_details%ROWTYPE;
252 l_item_substituted BOOLEAN;
253 l_curr_item_substituted BOOLEAN;
254 l_ingred_req_date DATE;
255 material_dtl_fetch_failure EXCEPTION;
256
257 --FPBug#4351032
258 l_formula_tbl gmdfmval_pub.formula_detail_tbl;
259 l_new_item_rec mtl_system_items_kfv%ROWTYPE;
260 --5698727
261 l_in_batch_header1 gme_batch_header%ROWTYPE;
262 l_doc_timestamp VARCHAR2(50);
263 l_assignment_type NUMBER;
264 l_document_no gme_batch_header.batch_no%TYPE;
265 --5698727
266 invalid_item_substitute EXCEPTION;
267 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
268
269 CURSOR recipe_validity_rule_cursor (v_recipe_validity_rule_id NUMBER)
270 IS
271 SELECT *
272 FROM gmd_recipe_validity_rules
273 WHERE recipe_validity_rule_id = NVL (v_recipe_validity_rule_id, -1);
274
275 CURSOR recipe_cursor (v_recipe_id NUMBER)
276 IS
277 SELECT *
278 FROM gmd_recipes
279 WHERE recipe_id = v_recipe_id;
280
281 CURSOR formula_details_cursor (v_recipe_id NUMBER)
282 IS
283 SELECT a.*
284 FROM fm_matl_dtl a, gmd_recipes_b b
285 WHERE a.formula_id = b.formula_id AND b.recipe_id = v_recipe_id
286 ORDER BY line_no;
287
288 CURSOR fm_text_tbl_cursor (v_text_code NUMBER)
289 IS
290 SELECT *
291 FROM fm_text_tbl
292 WHERE text_code = NVL (v_text_code, -1)
293 ORDER BY line_no;
294 -- pawan kumar changed this cursor for bug 4917631
295 CURSOR item_master_cursor (v_inventory_item_id NUMBER, v_org_id NUMBER)
296 IS
297 SELECT inventory_item_id, concatenated_segments,
298 eng_item_flag, process_execution_enabled_flag
299 FROM mtl_system_items_kfv
300 WHERE inventory_item_id = v_inventory_item_id
301 AND organization_id = v_org_id;
302 -- pawan kumar added this cursor for bug 4917631
303 CURSOR item_master_cursor_sub (v_inventory_item_id NUMBER, v_org_id NUMBER)
304 IS
305 SELECT primary_uom_code, eng_item_flag, process_execution_enabled_flag
306 FROM mtl_system_items_kfv
307 WHERE inventory_item_id = v_inventory_item_id
308 AND organization_id = v_org_id;
309 CURSOR production_rules_cursor (
310 v_inventory_item_id NUMBER
311 ,v_org_id NUMBER)
312 IS
313 SELECT std_lot_size, primary_uom_code
314 FROM mtl_system_items_b
315 WHERE inventory_item_id = v_inventory_item_id
316 AND organization_id = v_org_id;
317
318 CURSOR cur_get_doc_ord (v_org_id NUMBER, v_batch_type NUMBER)
319 IS
320 SELECT DECODE (v_batch_type
321 ,10, fpo_doc_numbering
322 ,batch_doc_numbering) assignment_type
323 FROM gme_parameters
324 WHERE organization_id = v_org_id;
325
326 CURSOR cur_val_batch (
327 v_org_id NUMBER
328 ,v_batch_no VARCHAR2
329 ,v_batch_type VARCHAR2)
330 IS
331 SELECT 1
332 FROM DUAL
333 WHERE EXISTS (
334 SELECT 1
335 FROM gme_batch_header
336 WHERE batch_no = v_batch_no
337 AND organization_id = v_org_id
338 AND batch_type = v_batch_type);
339
340 CURSOR get_prim_prod (v_batch_id NUMBER, v_inventory_item_id NUMBER)
341 IS
342 SELECT plan_qty, dtl_um
343 FROM gme_material_details
344 WHERE batch_id = v_batch_id
345 AND inventory_item_id = v_inventory_item_id
346 AND line_type = gme_common_pvt.g_line_type_prod
347 ORDER BY line_no ASC;
348
349 -- Bug 6665602 Back out 5484529... Reinstate original cursor.
350 --sunitha bug # 5484529 selecting the sum of the plan quantity without converting
351 --it to the user entered UOM while creating the batch
352 CURSOR cur_batch_qty (v_batch_id NUMBER, v_inventory_item_id NUMBER)
353 IS
354 SELECT SUM (inv_convert.inv_um_convert (v_inventory_item_id
355 ,5
356 ,plan_qty
357 ,dtl_um
358 ,p_batch_size_uom
359 ,NULL
360 ,NULL) )
361 FROM gme_material_details
362 WHERE batch_id = v_batch_id
363 AND inventory_item_id = v_inventory_item_id
364 AND line_type = gme_common_pvt.g_line_type_prod;
365 /*CURSOR cur_batch_qty (v_batch_id NUMBER, v_inventory_item_id NUMBER)
366 IS
367 SELECT SUM (plan_qty )
368 FROM gme_material_details
369 WHERE batch_id = v_batch_id
370 AND inventory_item_id = v_inventory_item_id
371 AND line_type = gme_common_pvt.g_line_type_prod; */
372
373 CURSOR cur_item_no (v_inventory_item_id NUMBER, v_org_id NUMBER)
374 IS
375 SELECT concatenated_segments
376 FROM mtl_system_items_kfv
377 WHERE inventory_item_id = v_inventory_item_id
378 AND organization_id = v_org_id;
379
380 -- nsinghi bug#5674398 Added the Cursor
381 CURSOR c_batchsteps (p_material_detail_id IN NUMBER) IS
382 SELECT b.*
383 FROM gme_batch_step_items a, gme_batch_steps b
384 WHERE a.batchstep_id = b.batchstep_id AND a.material_detail_id = p_material_detail_id;
385
386 error_count_exceeded EXCEPTION;
387 expected_error EXCEPTION;
388 unexpected_error EXCEPTION;
389 scaling_failure EXCEPTION;
390 invalid_header_values EXCEPTION;
391 invalid_recipe EXCEPTION;
392 conversion_failure EXCEPTION;
393 steps_creation_failure EXCEPTION;
394 batch_header_fetch_failure EXCEPTION;
395 date_exceed_validity_rule EXCEPTION;
396 error_create_phantom EXCEPTION;
397 validation_failure EXCEPTION;
398 create_by_product_failure EXCEPTION;
399 gme_duplicate_batch EXCEPTION;
400 doc_num_is_not_passed EXCEPTION;
401 inventory_shortage EXCEPTION;
402 error_cont_period EXCEPTION;
403 error_non_contiguious EXCEPTION;
404 error_truncate_date EXCEPTION;
405 create_mo_hdr_err EXCEPTION;
406 create_mo_line_err EXCEPTION;
407 calc_mtl_req_date_err EXCEPTION;
408 gmo_instantiate_err EXCEPTION;
409 wip_entity_err EXCEPTION;
410
411 --FPBug#4395561
412 create_flex_failure EXCEPTION;
413 Fixed_process_loss_failure EXCEPTION;
414 -- Rajesh Patangya Bug # 6752637
415 update_step_mtq_failure EXCEPTION;
416 BEGIN
417 IF g_debug <= gme_debug.g_log_procedure THEN
418 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
419 || l_api_name);
420 END IF;
421 x_return_status := fnd_api.g_ret_sts_success;
422 -- Initialize output batch header
423 x_batch_header_rec := p_batch_header_rec;
424
425 IF ( p_creation_mode <> 'RECIPE'
426 AND NVL (p_batch_size, 0) = 0
427 AND p_is_phantom <> 'Y') THEN
428 gme_common_pvt.log_message ('GME_API_QTY_CANT_BE_ZERO');
429 RAISE expected_error;
430 END IF;
431
432 IF x_batch_header_rec.batch_type NOT IN (0, 10) THEN
433 gme_common_pvt.log_message ('GME_INVALID_BATCH_TYPE');
434 RAISE expected_error;
435 END IF;
436
437 OPEN cur_get_doc_ord (x_batch_header_rec.organization_id
438 ,x_batch_header_rec.batch_type);
439
440 FETCH cur_get_doc_ord
441 INTO l_doc_numbering;
442
443 CLOSE cur_get_doc_ord;
444 IF g_debug <= gme_debug.g_log_procedure THEN
445 gme_debug.put_line ('BEfore document numbering check' || g_pkg_name || '.'
446 || l_api_name);
447 END IF;
448 IF l_doc_numbering = 1 THEN
449 IF x_batch_header_rec.batch_no IS NULL THEN
450 RAISE doc_num_is_not_passed;
451 ELSE
452 OPEN cur_val_batch (x_batch_header_rec.organization_id
453 ,x_batch_header_rec.batch_no
454 ,x_batch_header_rec.batch_type);
455
456 FETCH cur_val_batch
457 INTO l_count;
458
459 CLOSE cur_val_batch;
460
461 IF l_count > 0 THEN
462 RAISE gme_duplicate_batch;
463 END IF;
464 END IF; /* x_batch_header_rec.batch_no IS NULL */
465 END IF; /* IF l_doc_numbering = 1 */
466 IF g_debug <= gme_debug.g_log_procedure THEN
467 gme_debug.put_line ('before laboratory check' || g_pkg_name || '.'
468 || l_api_name);
469 END IF;
470
471 IF (gme_common_pvt.g_lab_ind = 1 AND gme_common_pvt.g_plant_ind = 1) THEN
472 IF p_batch_header_rec.laboratory_ind = 1 THEN
473 x_batch_header_rec.laboratory_ind := 1;
474 x_batch_header_rec.update_inventory_ind :=
475 p_batch_header_rec.update_inventory_ind;
476 ELSE
477 x_batch_header_rec.laboratory_ind := 0;
478 x_batch_header_rec.update_inventory_ind := 'Y';
479 END IF;
480 ELSIF (gme_common_pvt.g_lab_ind = 1) THEN
481 x_batch_header_rec.laboratory_ind := 1;
482 x_batch_header_rec.update_inventory_ind :=
483 p_batch_header_rec.update_inventory_ind;
484 ELSIF (gme_common_pvt.g_plant_ind = 1) THEN
485 x_batch_header_rec.laboratory_ind := 0;
486 x_batch_header_rec.update_inventory_ind := 'Y';
487 END IF;
488 IF g_debug <= gme_debug.g_log_procedure THEN
489 gme_debug.put_line ('After laboratory check' || g_pkg_name || '.'
490 || l_api_name);
491 END IF;
492
493 -- Validate/fill in batch header columns
494 l_in_batch_header := x_batch_header_rec;
495 l_return :=
496 construct_batch_header (l_in_batch_header, x_batch_header_rec);
497
498 IF NOT l_return THEN
499 RAISE invalid_header_values;
500 END IF;
501 IF g_debug <= gme_debug.g_log_procedure THEN
502 gme_debug.put_line ('After contruct batch header' || g_pkg_name || '.'
503 || l_api_name);
504 END IF;
505
506 IF g_debug <= gme_debug.g_log_statement THEN
507 gme_debug.put_line ( g_pkg_name
508 || '.'
509 || l_api_name
510 || 'Starting Validity rule processing');
511 END IF;
512
513 /* If the batch is being created from LCF
514 then there is no pre-defined formula or
515 validity rule */
516 IF p_creation_mode <> 'LCF' THEN
517 -- Validate recipe validity rule
518 OPEN recipe_validity_rule_cursor
519 (x_batch_header_rec.recipe_validity_rule_id);
520
521 FETCH recipe_validity_rule_cursor
522 INTO l_recipe_validity_rule;
523
524 CLOSE recipe_validity_rule_cursor;
525
526 -- Load the recipe data.
527 OPEN recipe_cursor (l_recipe_validity_rule.recipe_id);
528
529 FETCH recipe_cursor
530 INTO l_recipe;
531
532 CLOSE recipe_cursor;
533
534 IF l_recipe.recipe_id IS NULL THEN
535 gme_common_pvt.log_message ('GME_API_INVALID_RULE');
536 RAISE expected_error;
537 END IF;
538
539 IF g_debug <= gme_debug.g_log_statement THEN
540 gme_debug.put_line ( g_pkg_name
541 || '.'
542 || l_api_name
543 || 'Calling gmd_recipe_data_pub.get_recipe_data');
544 END IF;
545
546 gmd_recipe_data_pub.get_recipe_data
547 (p_api_version => 1
548 ,p_init_msg_list => fnd_api.g_false
549 ,p_recipe_id => l_recipe.recipe_id
550 ,p_organization_id => x_batch_header_rec.organization_id
551 ,x_return_status => l_return_status
552 ,x_msg_count => l_message_count
553 ,x_msg_data => l_message_list
554 ,x_return_code => l_return_code
555 ,x_recipe_rout_tbl => gme_common_pvt.routings
556 ,x_recipe_rout_matl_tbl => gme_common_pvt.routing_materials
557 ,x_recipe_step_out => gme_common_pvt.steps
558 ,x_routing_depd_tbl => gme_common_pvt.step_dependencies
559 ,x_oprn_act_out => gme_common_pvt.activities
560 ,x_oprn_resc_rec => gme_common_pvt.resources
561 ,x_formula_header_rec => l_formula_master
562 ,x_formula_dtl_tbl => gme_common_pvt.materials
563 ,x_recp_resc_proc_param_tbl => gme_common_pvt.process_parameters);
564 IF g_debug <= gme_debug.g_log_statement THEN
565 gme_debug.put_line
566 ( g_pkg_name
567 || '.'
568 || l_api_name
569 || 'Return from gmd_recipe_data_pub.get_recipe_data '
570 || l_return_status);
571 END IF;
572
573 ELSE /* p_creation_mode = 'LCF' */
574 /* Setup following variables so that rest of the program goes through */
575 l_return_status := fnd_api.g_ret_sts_success;
576 l_recipe_validity_rule.inventory_item_id :=
577 gme_common_pvt.materials (1).inventory_item_id;
578 l_formula_master.text_code := NULL;
579 l_formula_master.scale_type := 0;
580 l_recipe.calculate_step_quantity := 0;
581 IF gme_common_pvt.routings.COUNT > 0 THEN
582 l_recipe.routing_id := gme_common_pvt.routings(1).routing_id;
583 END IF;
584 END IF; /* IF p_creation_mode <> 'LCF' THEN */
585
586 IF (g_debug = gme_debug.g_log_statement) THEN
587 gme_debug.put_line ( g_pkg_name
588 || ':Creation mode '
589 || p_creation_mode
590 || ' Batch size '
591 || p_batch_size
592 || ' '
593 || p_batch_size_uom);
594 gme_debug.put_line ('Return Status was ' || l_return_status);
595 gme_debug.put_line ('return_code = ' || TO_CHAR (l_return_code) );
596 gme_debug.put_line ( '# of Steps = '
597 || TO_CHAR (gme_common_pvt.steps.COUNT) );
598 gme_debug.put_line ( '# of Acts = '
599 || TO_CHAR (gme_common_pvt.activities.COUNT) );
600 gme_debug.put_line ( '# of Rsrc = '
601 || TO_CHAR (gme_common_pvt.resources.COUNT) );
602 gme_debug.put_line ( '# of Matl = '
603 || TO_CHAR
604 (gme_common_pvt.routing_materials.COUNT) );
605 gme_debug.put_line ( '# of Depd = '
606 || TO_CHAR
607 (gme_common_pvt.step_dependencies.COUNT) );
608 gme_debug.put_line ( '# of Lines = '
609 || TO_CHAR (gme_common_pvt.materials.COUNT) );
610 END IF;
611
612 IF l_return_status <> fnd_api.g_ret_sts_success THEN
613 IF l_return_code = 4 THEN
614 -- Recipe does not have a routing, not an error
615 NULL;
616 ELSIF l_return_code = 8 THEN
617 fnd_message.set_name ('GMD', 'GMD_CIRCULAR_DEPEN_DETECT');
618 fnd_msg_pub.ADD;
619 RAISE invalid_recipe;
620 ELSE
621 gme_common_pvt.log_message ('GME_BAD_RECIPE_RETRIEVAL');
622 RAISE invalid_recipe;
623 END IF;
624 END IF;
625
626 IF gme_common_pvt.routings.COUNT > 0 THEN
627 x_batch_header_rec.enforce_step_dependency :=
628 NVL (gme_common_pvt.routings (1).enforce_step_dependency, 0);
629 END IF;
630
631 IF p_creation_mode <> 'LCF' THEN
632 IF l_formula_master.formula_id IS NULL
633 OR l_formula_master.delete_mark = 1
634 OR l_formula_master.inactive_ind = 1 THEN
635 gme_common_pvt.log_message ('GME_API_INVALID_FORMULA');
636 RAISE expected_error;
637 END IF;
638 END IF; /* IF p_creation_mode <> 'LCF' */
639
640 -- Retrieve and validate formula details
641 l_number_of_formula_lines := 0;
642 l_total_input_qty := 0;
643 l_total_output_qty := 0;
644
645 IF g_debug <= gme_debug.g_log_statement THEN
646 gme_debug.put_line ( g_pkg_name
647 || '.'
648 || l_api_name
649 || 'Start material processing');
650 END IF;
651
652 FOR i IN 1 .. gme_common_pvt.materials.COUNT LOOP
653 l_formula_material := gme_common_pvt.materials (i);
654 -- pawan kumar changed this cursor for bug 4917631
655 OPEN item_master_cursor (l_formula_material.inventory_item_id
656 ,x_batch_header_rec.organization_id);
657 FETCH item_master_cursor
658 INTO l_item_master.inventory_item_id, l_item_master.concatenated_segments,
659 l_item_master.eng_item_flag, l_item_master.process_execution_enabled_flag;
660
661 CLOSE item_master_cursor;
662
663 IF l_item_master.inventory_item_id IS NULL
664 OR l_item_master.process_execution_enabled_flag = 'N'
665 OR ( l_item_master.eng_item_flag = 'Y'
666 AND x_batch_header_rec.laboratory_ind = 0) THEN
667 gme_common_pvt.log_message ('GME_API_INVALID_ITEM'
668 ,'ITEM_NO'
669 ,l_item_master.concatenated_segments);
670 RAISE error_count_exceeded;
671 ELSE
672 l_number_of_formula_lines := l_number_of_formula_lines + 1;
673 l_item_masters (l_number_of_formula_lines) := l_item_master;
674
675 IF l_formula_material.line_type > 0 THEN
676 IF l_formula_material.line_type =
677 gme_common_pvt.g_line_type_prod THEN
678 l_number_of_product_lines := l_number_of_product_lines + 1;
679
680
681
682 ELSE
683 l_number_of_byproduct_lines :=
684 l_number_of_byproduct_lines + 1;
685 END IF;
686 ELSE
687 l_number_of_ingredient_lines :=
688 l_number_of_ingredient_lines + 1;
689 END IF; /* l_formula_material.line_type > 0 */
690
691
692 -- Construct the material detail row
693
694 gme_common_pvt.construct_material_detail
695 (p_formula_detail_rec => l_formula_material
696 ,p_item_master_rec => l_item_master
697 ,p_batch_header_rec => x_batch_header_rec
698 ,x_material_detail_rec => l_gme_material_detail
699 ,x_return_status => l_return_status);
700
701 IF l_return_status = fnd_api.g_ret_sts_success THEN
702
703 l_material_details (l_number_of_formula_lines) :=
704 l_gme_material_detail;
705 l_material_details (l_number_of_formula_lines).original_qty :=
706 l_formula_material.qty;
707
708 IF (l_material_details (l_number_of_formula_lines).dtl_um <>
709 l_item_master.primary_uom_code) THEN
710 l_temp_qty :=
711 inv_convert.inv_um_convert
712 (l_material_details (l_number_of_formula_lines).inventory_item_id
713 ,5
714 ,l_material_details (l_number_of_formula_lines).original_qty
715 ,l_material_details (l_number_of_formula_lines).dtl_um
716 ,l_item_master.primary_uom_code
717 ,NULL
718 ,NULL);
719 ELSE
720 l_temp_qty :=
721 l_material_details (l_number_of_formula_lines).original_qty;
722 END IF;
723
724 l_material_details (l_number_of_formula_lines).original_primary_qty :=
725 l_temp_qty;
726 /* --Pawan changes for GMO
727 IF (l_material_details (l_number_of_formula_lines).line_type =
728 gme_common_pvt.g_line_type_ing )
729 AND (l_material_details (l_number_of_formula_lines).phantom_type = 0 ) THEN
730
731 GMO_DISPENSE_GRP.IS_DISPENSE_ITEM (p_api_version => 1.0,
732 p_init_msg_list => fnd_api.g_false ,
733 x_return_status => l_return_status ,
734 x_msg_count => l_message_count ,
735 x_msg_data => l_message_list ,
736 p_inventory_item_id => l_formula_material.inventory_item_id,
737 p_organization_id => x_batch_header_rec.organization_id,
738 p_recipe_id => l_recipe.recipe_id,
739 x_dispense_required => l_dispense_required,
740 x_dispense_config_id => l_config_id);
741
742 IF l_return_status = fnd_api.g_ret_sts_success THEN
743 l_material_details (l_number_of_formula_lines).dispense_ind := l_dispense_required ;
744 END IF;
745
746 END IF;
747 -- GMO changes end */
748 ELSE
749 RAISE error_count_exceeded;
750 END IF;
751 END IF; /* l_item_master.inventory_item_id IS NULL OR */
752 END LOOP; /* l_formula_material IN formula_details_cursor */
753
754 IF g_debug <= gme_debug.g_log_statement THEN
755 gme_debug.put_line ( g_pkg_name
756 || '.'
757 || l_api_name
758 || 'Finished material processing');
759 END IF;
760
761 IF l_number_of_formula_lines = 0
762 OR l_number_of_ingredient_lines = 0
763 OR l_number_of_product_lines = 0 THEN
764 gme_common_pvt.log_message ('GME_API_INVALID_FORMULA');
765 RAISE error_count_exceeded;
766 END IF;
767
768 -- We now need to sort out the scaling. If the formula is marked as scalable
769 -- we have to work out the output and input quantities. The columns on
770 -- the total_output_qty and total_input_qty on the formula header express
771 -- the totals in the YIELD_UOM of the system. However these are not always
772 -- useful and we have to work out other quantities on an as-required basis.
773 IF l_formula_master.scale_type = 1 THEN
774 -- The formula is scalable (at least at the header level anyway)
775 -- The first thing we need to do is find out the scale factor
776 IF p_creation_mode = 'OUTPUT' THEN
777 -- Batch is being created by Total Output.
778 -- Scale Factor is the requested batch size divided by the recipe
779 -- total output qty
780 l_total_output_qty := 0;
781 l_uom := p_batch_size_uom;
782 gmd_common_val.calculate_total_qty
783 (formula_id => l_formula_master.formula_id
784 ,x_product_qty => l_total_output_qty
785 ,x_ingredient_qty => l_total_input_qty
786 ,x_uom => l_uom
787 ,x_return_status => l_return_status
788 ,x_msg_count => l_message_count
789 ,x_msg_data => l_message_list);
790
791 /* If the above procedure is not able to calculate one of the 2 total qty's it returns status of Q. We need to
792 check if the total_output_qty is calculated correctly and not worry about if the total_input is notcalculated. */
793 IF (l_return_status <> fnd_api.g_ret_sts_success)
794 AND (l_return_status <> 'Q') THEN
795 RAISE expected_error;
796 END IF;
797
798 IF (l_total_output_qty IS NULL AND l_return_status = 'Q') THEN
799 fnd_message.set_name ('GMD', 'GMD_ERR_CALC_OUTPUT');
800 fnd_message.set_token ('UOM', l_uom);
801 fnd_msg_pub.ADD;
802 RAISE expected_error;
803 END IF;
804
805 gmd_validity_rules.get_output_ratio
806 (p_formula_id => l_formula_master.formula_id
807 ,p_batch_output => p_batch_size
808 ,p_formula_output => l_total_output_qty
809 ,p_yield_um => l_uom
810 ,x_return_status => l_return_status
811 ,x_output_ratio => gme_common_pvt.g_batch_scale_factor);
812
813 IF l_return_status <> fnd_api.g_ret_sts_success THEN
814 RAISE expected_error;
815 END IF;
816
817 l_primaries := 'OUTPUTS';
818 ELSIF p_creation_mode = 'PRODUCT' THEN
819 -- Batch is being created by Product and Quantity. Similar to above, but
820 -- the scale factor is the requested batch size divided by the quantity of
821 -- this item produced by the formula, so we need to calulate this first.
822 -- Although unlikely the specified product could occur multiple times
823 -- and be both a product and a byproduct.
824 l_total_output_qty := 0;
825
826 -- Bug 7284242 - Let's sum up the fixed products separately
827 l_total_output_qty_fixed := 0;
828 l_uom := p_batch_size_uom;
829
830 /* Let's convert formula product quantity in the requested UOM */
831 FOR loop_count IN 1 .. l_number_of_formula_lines LOOP
832 IF l_material_details (loop_count).line_type =
833 gme_common_pvt.g_line_type_prod
834 AND l_material_details (loop_count).inventory_item_id =
835 l_recipe_validity_rule.inventory_item_id THEN
836
837 -- bug#5373369 nsinghi rework. While determining the scale factor, give higher precision, otherwise when converting back to batch UOM, there is decimal dust issue and batch creation fails.
838 l_temp_qty :=
839 inv_convert.inv_um_convert
840 (l_material_details (loop_count).inventory_item_id
841 ,32 --5
842 ,l_material_details (loop_count).plan_qty
843 ,l_material_details (loop_count).dtl_um
844 ,l_uom
845 ,NULL
846 ,NULL);
847
848 IF l_temp_qty < 0 THEN
849 IF (g_debug = gme_debug.g_log_statement) THEN
850 gme_debug.put_line
851 ('Failed in UOM Conv from formula product UOM to requested UOM');
852 END IF;
853
854 l_item_id :=
855 l_material_details (loop_count).inventory_item_id;
856 l_from_uom := l_material_details (loop_count).dtl_um;
857 l_to_uom := l_uom;
858 RAISE conversion_failure;
859 ELSE
860 -- Bug 7284242 - Let's sum up the fixed products separately
861 IF (l_material_details (loop_count).scale_type = 1) THEN
862 l_total_output_qty := l_total_output_qty + l_temp_qty;
863 ELSE
864 l_total_output_qty_fixed := l_total_output_qty_fixed + l_temp_qty;
865 END IF;
866 END IF;
867 END IF;
868 /* l_material_details(loop_count).line_type IN (1, 2) AND */
869 END LOOP; /* loop_count IN 1 .. l_number_of_formula_lines */
870
871 l_primaries := 'OUTPUTS';
872
873 IF l_total_output_qty = 0 THEN
874 gme_common_pvt.log_message ('GME_API_PROD_QTY_CANT_ZERO');
875 RAISE expected_error;
876 END IF;
877
878 -- Bug 7284242 - This can only happen if the fixed product qty sum is greater
879 -- than the batch size being requested.
880 IF (p_batch_size - l_total_output_qty_fixed < 0) THEN
881 RAISE create_by_product_failure;
882 END IF;
883
884 -- Bug 7284242 - Let's subtract the fixed products total from the batch size to derive scale factor.
885 gme_common_pvt.g_batch_scale_factor :=
886 (p_batch_size - l_total_output_qty_fixed) / l_total_output_qty;
887
888 ELSIF p_creation_mode = 'RECIPE' THEN
889 -- Batch is being created by Recipe and Version
890 -- If there is a production rule in force we must find the batch size
891 -- allowable for this plant/product. The scale factor is then this size
892 -- divided by the formula's output of this product. If there isn't a
893 -- production rule then the formula as it stands is what we will
894 -- create the batch with, and scaling is thus not needed.
895 OPEN production_rules_cursor
896 (l_recipe_validity_rule.inventory_item_id
897 ,x_batch_header_rec.organization_id);
898
899 FETCH production_rules_cursor
900 INTO l_production_rule_qty, l_production_um;
901
902 IF production_rules_cursor%NOTFOUND THEN
903 l_primaries := 'OUTPUTS';
904 gme_common_pvt.g_batch_scale_factor := 1;
905
906 IF (g_debug = gme_debug.g_log_statement) THEN
907 gme_debug.put_line
908 ('CREATING BY RECIPE/VERSION WITHOUT SCALING');
909 END IF;
910 ELSE
911 l_total_output_qty := 0;
912
913 FOR loop_count IN 1 .. l_number_of_formula_lines LOOP
914 IF l_material_details (loop_count).line_type IN
915 (gme_common_pvt.g_line_type_prod
916 ,gme_common_pvt.g_line_type_byprod)
917 AND l_material_details (loop_count).inventory_item_id =
918 l_recipe_validity_rule.inventory_item_id THEN
919
920 -- bug#5373369 nsinghi rework. While determining the scale factor, give higher precision, otherwise when converting back to batch UOM, there is decimal dust issue and batch creation fails.
921
922 l_temp_qty :=
923 inv_convert.inv_um_convert
924 (l_material_details (loop_count).inventory_item_id
925 ,32 --5
926 ,l_material_details (loop_count).plan_qty
927 ,l_material_details (loop_count).dtl_um
928 ,l_production_um
929 ,NULL
930 ,NULL);
931
932 IF l_temp_qty < 0 THEN
933 IF (g_debug = gme_debug.g_log_statement) THEN
934 gme_debug.put_line
935 ('Failed in UOM Conv from formula product UOM to requested UOM');
936 END IF;
937
938 l_item_id :=
939 l_material_details (loop_count).inventory_item_id;
940 l_from_uom := l_material_details (loop_count).dtl_um;
941 l_to_uom := l_production_um;
942 RAISE conversion_failure;
943 ELSE
944 l_total_output_qty := l_total_output_qty + l_temp_qty;
945 END IF;
946 END IF;
947 /* l_material_details(loop_count).line_type IN (1, 2) AND */
948 END LOOP; /* loop_count IN 1 .. l_number_of_formula_lines */
949
950 IF l_total_output_qty = 0 THEN
951 gme_common_pvt.log_message ('GME_API_PROD_QTY_CANT_ZERO');
952 RAISE expected_error;
953 END IF;
954
955 gme_common_pvt.g_batch_scale_factor :=
956 l_production_rule_qty / l_total_output_qty;
957 END IF; /* IF production_rules_cursor%NOTFOUND */
958
959 CLOSE production_rules_cursor;
960
961 l_primaries := 'OUTPUTS';
962 ELSIF p_creation_mode = 'INPUT' THEN
963 -- Batch is being created by Total Input. The scale factor is the requested batch size divided by the formula's
964 -- total input. We should be changing all the ingredients to the batch size UOM
965 l_total_input_qty := 0;
966 l_uom := p_batch_size_uom;
967 gmd_common_val.calculate_total_qty
968 (formula_id => l_formula_master.formula_id
969 ,x_product_qty => l_total_output_qty
970 ,x_ingredient_qty => l_total_input_qty
971 ,x_uom => l_uom
972 ,x_return_status => l_return_status
973 ,x_msg_count => l_message_count
974 ,x_msg_data => l_message_list);
975
976 IF (l_return_status <> fnd_api.g_ret_sts_success)
977 AND (l_return_status <> 'Q') THEN
978 RAISE expected_error;
979 END IF;
980
981 IF (l_total_input_qty IS NULL AND l_return_status = 'Q') THEN
982 fnd_message.set_name ('GMD', 'GMD_ERR_CALC_OUTPUT');
983 fnd_message.set_token ('UOM', l_uom);
984 fnd_msg_pub.ADD;
985 RAISE expected_error;
986 END IF;
987
988 gmd_validity_rules.get_batchformula_ratio
989 (p_formula_id => l_formula_master.formula_id
990 ,p_batch_input => p_batch_size
991 ,p_formula_input => l_total_input_qty
992 ,p_yield_um => l_uom
993 ,x_return_status => l_return_status
994 ,x_batchformula_ratio => gme_common_pvt.g_batch_scale_factor);
995
996 IF l_return_status <> fnd_api.g_ret_sts_success THEN
997 RAISE expected_error;
998 END IF;
999
1000 l_primaries := 'INPUTS';
1001 ELSIF p_creation_mode = 'LCF' THEN
1002 -- No need to do anything, but this is a valid creation mode
1003 NULL;
1004 ELSE
1005 -- Batch is being created by means unknown
1006 gme_common_pvt.log_message ('GME_API_UNSUPPORTED_MODE'
1007 ,'MODE'
1008 ,p_creation_mode);
1009 RAISE expected_error;
1010 END IF;
1011
1012 IF gme_common_pvt.routings.COUNT <> 0 THEN
1013 l_total_output_qty_c := 0;
1014
1015 FOR loop_count IN 1 .. l_number_of_formula_lines LOOP
1016 IF l_material_details (loop_count).line_type IN
1017 (gme_common_pvt.g_line_type_prod
1018 ,gme_common_pvt.g_line_type_byprod) THEN
1019 IF l_material_details (loop_count).dtl_um =
1020 gme_common_pvt.routings (1).routing_uom THEN
1021 l_total_output_qty_c :=
1022 l_total_output_qty_c
1023 + l_material_details (loop_count).plan_qty;
1024 ELSE
1025 l_temp_qty :=
1026 inv_convert.inv_um_convert
1027 (l_material_details (loop_count).inventory_item_id
1028 ,5
1029 ,l_material_details (loop_count).plan_qty
1030 ,l_material_details (loop_count).dtl_um
1031 ,gme_common_pvt.routings (1).routing_uom
1032 ,NULL
1033 ,NULL);
1034
1035 IF l_temp_qty < 0 THEN
1036 IF (g_debug = gme_debug.g_log_statement) THEN
1037 gme_debug.put_line
1038 ('Failed in UOM Conv from formula product UOM to routing UOM');
1039 END IF;
1040
1041 l_item_id :=
1042 l_material_details (loop_count).inventory_item_id;
1043 l_from_uom := l_material_details (loop_count).dtl_um;
1044 l_to_uom := gme_common_pvt.routings (1).routing_uom;
1045 RAISE conversion_failure;
1046 ELSE
1047 l_total_output_qty_c :=
1048 l_total_output_qty_c + l_temp_qty;
1049 END IF;
1050 END IF; /* If formula detail UOM = routing UOM */
1051 END IF;
1052 /* l_material_details(loop_count).line_type IN (1, 2) */
1053 END LOOP; /* loop_count IN 1 .. l_number_of_formula_lines */
1054
1055 IF (g_debug = gme_debug.g_log_statement) THEN
1056 gme_debug.put_line ( 'tot_output before scaling is '
1057 || TO_CHAR (l_total_output_qty_c) );
1058 END IF;
1059 END IF; /* IF gme_common_pvt.routings.COUNT <> 0 */
1060
1061 IF gme_common_pvt.g_batch_scale_factor <> 1 THEN
1062 IF (g_debug = gme_debug.g_log_statement) THEN
1063 gme_debug.put_line
1064 ( 'scale factor is '
1065 || TO_CHAR
1066 (gme_common_pvt.g_batch_scale_factor) );
1067 END IF;
1068
1069 /* Following line is added, because the scale_batch is expecting the
1070 batch status to be 0. We will reset it to 1 as soon as scaling is done */
1071 x_batch_header_rec.batch_status := 0;
1072 /* l_over_allocations parameter is used to handle default lot going */
1073 /* negative cases, which would not be a case here as the default lot */
1074 /* is created after the scale is successfull */
1075 l_in_material_details := l_material_details;
1076 gme_scale_batch_pvt.scale_batch
1077 (p_batch_header_rec => x_batch_header_rec
1078 ,p_material_tbl => l_in_material_details
1079 ,p_scale_factor => gme_common_pvt.g_batch_scale_factor
1080 ,p_primaries => l_primaries
1081 ,p_qty_type => 1
1082 ,p_validity_rule_id => l_recipe_validity_rule.recipe_validity_rule_id
1083 ,p_enforce_vldt_check => fnd_api.g_true
1084 ,p_recalc_dates => fnd_api.g_false
1085 ,p_use_workday_cal => fnd_api.g_false
1086 ,p_contiguity_override => fnd_api.g_true
1087 ,x_material_tbl => l_material_details
1088 ,x_exception_material_tbl => l_exception_material_tbl
1089 ,x_batch_header_rec => l_in_batch_header
1090 ,
1091 /* Create batch does not need any values from here so dummy variable */
1092 x_return_status => x_return_status);
1093 x_batch_header_rec.batch_status := gme_common_pvt.g_batch_pending;
1094
1095 IF (g_debug = gme_debug.g_log_statement) THEN
1096 gme_debug.put_line ( 'return from scale_batch is: '
1097 || x_return_status);
1098 END IF;
1099
1100 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1101 RAISE scaling_failure;
1102 ELSE
1103 FOR loop_count IN 1 .. l_number_of_formula_lines LOOP
1104 l_material_details (loop_count).plan_qty :=
1105 ROUND (l_material_details (loop_count).plan_qty, 32);
1106 l_material_details (loop_count).original_qty :=
1107 ROUND (l_material_details (loop_count).original_qty, 32);
1108 /* This cursor just used to get primary_uom_code */
1109 OPEN production_rules_cursor(l_material_details (loop_count).inventory_item_id
1110 ,l_material_details (loop_count).organization_id);
1111 FETCH production_rules_cursor INTO l_production_rule_qty, l_production_um;
1112 CLOSE production_rules_cursor;
1113 IF (l_material_details (loop_count).dtl_um <> l_production_um) THEN
1114 l_temp_qty := inv_convert.inv_um_convert
1115 (l_material_details (loop_count).inventory_item_id
1116 ,5
1117 ,l_material_details (loop_count).original_qty
1118 ,l_material_details (loop_count).dtl_um
1119 ,l_production_um
1120 ,NULL
1121 ,NULL);
1122 ELSE
1123 l_temp_qty := l_material_details (loop_count).original_qty;
1124 END IF;
1125 l_material_details (loop_count).original_primary_qty := l_temp_qty;
1126 END LOOP;
1127 END IF;
1128
1129 -- Scaling worked OK, so we now need to caculate the total scaled
1130 -- formula output in the Routing UOM so that we can look up a few
1131 -- things in the process loss table.
1132 IF gme_common_pvt.routings.COUNT <> 0 THEN
1133 l_total_output_qty_b := 0;
1134
1135 FOR loop_count IN 1 .. l_number_of_formula_lines LOOP
1136 IF l_material_details (loop_count).line_type IN
1137 (gme_common_pvt.g_line_type_prod
1138 ,gme_common_pvt.g_line_type_byprod) THEN
1139 IF l_material_details (loop_count).dtl_um =
1140 gme_common_pvt.routings (1).routing_uom THEN
1141 l_total_output_qty_b :=
1142 l_total_output_qty_b
1143 + l_material_details (loop_count).plan_qty;
1144 ELSE
1145 l_temp_qty :=
1146 inv_convert.inv_um_convert
1147 (l_material_details (loop_count).inventory_item_id
1148 ,5
1149 ,l_material_details (loop_count).plan_qty
1150 ,l_material_details (loop_count).dtl_um
1151 ,gme_common_pvt.routings (1).routing_uom
1152 ,NULL
1153 ,NULL);
1154
1155 IF l_temp_qty < 0 THEN
1156 IF (g_debug = gme_debug.g_log_statement) THEN
1157 gme_debug.put_line
1158 ('Failed in UOM Conv from formula product UOM to routing UOM');
1159 END IF;
1160
1161 l_item_id :=
1162 l_material_details (loop_count).inventory_item_id;
1163 l_from_uom :=
1164 l_material_details (loop_count).dtl_um;
1165 l_to_uom := gme_common_pvt.routings (1).routing_uom;
1166 RAISE conversion_failure;
1167 ELSE
1168 l_total_output_qty_b :=
1169 l_total_output_qty_b + l_temp_qty;
1170 END IF; /* l_temp_qty > 0 */
1171 END IF; /* IF routing and formula uoms are same */
1172 END IF; /* If line type > 0 */
1173 END LOOP;
1174
1175 IF (g_debug = gme_debug.g_log_statement) THEN
1176 gme_debug.put_line ( 'Total Output after scaling is '
1177 || TO_CHAR (l_total_output_qty_b) );
1178 END IF;
1179 END IF; /* IF gme_common_pvt.routings.COUNT <> 0 */
1180 END IF; /* IF gme_common_pvt.g_batch_scale_factor <> 1 */
1181
1182 IF p_creation_mode <> 'LCF' THEN
1183 -- Batch has been scaled OK so we now need to determine and apply the process loss.
1184 l_process_loss :=
1185 gme_common_pvt.get_process_loss
1186 (p_validity_rule_id => l_recipe_validity_rule.recipe_validity_rule_id
1187 ,p_organization_id => x_batch_header_rec.organization_id
1188 ,p_total_output_qty_scaled => l_total_output_qty_b
1189 ,p_total_output_qty_pre_scale => l_total_output_qty_c);
1190
1191 IF (l_process_loss IS NULL) THEN
1192 RAISE expected_error;
1193 END IF;
1194
1195 -- Now that the process loss is known, we need to gross up the scalable
1196 -- ingredients (for creation by Total Output, Product or Recipe) or
1197 -- scale down the scalable products (for creation by Total Input).
1198 FOR l_row_count IN 1 .. l_number_of_formula_lines LOOP
1199 IF l_material_details (l_row_count).line_type < 0 THEN
1200 IF p_creation_mode IN ('PRODUCT', 'OUTPUT', 'RECIPE')
1201 AND l_material_details (l_row_count).scale_type = 1 THEN
1202 l_material_details (l_row_count).plan_qty :=
1203 l_material_details (l_row_count).plan_qty
1204 * 100
1205 / (100 - l_process_loss);
1206 END IF;
1207 ELSE /* l_material_details(l_row_count).line_type >= 0 */
1208 IF p_creation_mode = 'INPUT' THEN
1209 l_material_details (l_row_count).plan_qty :=
1210 l_material_details (l_row_count).plan_qty
1211 * (100 - l_process_loss)
1212 / 100;
1213 END IF;
1214 END IF; /* IF l_material_details(l_row_count).line_type < 0 */
1215
1216 l_material_details (l_row_count).original_qty :=
1217 l_material_details (l_row_count).plan_qty;
1218 END LOOP; /* l_row_count IN 1 .. l_number_of_formula_lines */
1219
1220 IF (g_debug = gme_debug.g_log_statement) THEN
1221 gme_debug.put_line ( 'Total Output after Process Loss is '
1222 || TO_CHAR (l_total_output_qty_b) );
1223 END IF;
1224 END IF; /* IF p_creation_mode <> 'LCF' THEN */
1225
1226 IF gme_common_pvt.routings.COUNT <> 0 THEN
1227 -- Accumulate the total output of the batch in the ROUTING UOM
1228 l_requested_qty := 0;
1229
1230 IF (l_total_output_qty_b IS NULL OR l_total_output_qty_b = 0)
1231 OR (p_creation_mode = 'INPUT' AND l_process_loss <> 0) THEN
1232 l_total_output_qty_b := 0;
1233
1234 FOR l_row_count IN 1 .. l_number_of_formula_lines LOOP
1235 IF l_material_details (l_row_count).line_type > 0 THEN
1236 IF l_material_details (l_row_count).dtl_um =
1237 gme_common_pvt.routings (1).routing_uom THEN
1238 l_total_output_qty_b :=
1239 l_total_output_qty_b
1240 + l_material_details (l_row_count).plan_qty;
1241 ELSE
1242 l_temp_qty :=
1243 inv_convert.inv_um_convert
1244 (l_material_details (l_row_count).inventory_item_id
1245 ,5
1246 ,l_material_details (l_row_count).plan_qty
1247 ,l_material_details (l_row_count).dtl_um
1248 ,gme_common_pvt.routings (1).routing_uom
1249 ,NULL
1250 ,NULL);
1251
1252 IF l_temp_qty < 0 THEN
1253 l_item_id :=
1254 l_material_details (l_row_count).inventory_item_id;
1255 l_from_uom :=
1256 l_material_details (l_row_count).dtl_um;
1257 l_to_uom :=
1258 gme_common_pvt.routings (1).routing_uom;
1259 RAISE conversion_failure;
1260 ELSE
1261 l_total_output_qty_b :=
1262 l_total_output_qty_b + l_temp_qty;
1263 END IF;
1264 END IF;
1265 END IF; -- IF l_material_details(l_row_count).line_type > 0
1266 END LOOP; -- l_row_count IN 1 .. l_number_of_formula_lines
1267 END IF; /* b_total_output_qty_b IS NULL */
1268
1269 IF l_total_output_qty_c = 0 THEN
1270 gme_common_pvt.log_message ('GME_API_PROD_QTY_CANT_ZERO');
1271 RAISE expected_error;
1272 END IF;
1273
1274 gme_common_pvt.g_routing_scale_factor :=
1275 l_total_output_qty_b / l_total_output_qty_c;
1276
1277 IF (g_debug = gme_debug.g_log_statement) THEN
1278 gme_debug.put_line ( 'Routing scale factor is '
1279 || gme_common_pvt.g_routing_scale_factor);
1280 END IF;
1281 END IF; /* IF gme_common_pvt.routings.COUNT <> 0 */
1282 ELSE /* IF l_formula_master.scale_type <> 1 */
1283 -- Formula cannot be scaled
1284 gme_common_pvt.g_batch_scale_factor := 1;
1285 gme_common_pvt.g_routing_scale_factor := 1;
1286 END IF;
1287
1288 -- Fixed Process Loss ME
1289
1290 IF (g_debug = gme_debug.g_log_statement) THEN
1291 gme_debug.put_line ('Before calling the Fixed Proccess Loss '
1292 || p_batch_header_rec.fixed_process_loss_applied);
1293 END IF;
1294
1295 IF(p_batch_header_rec.fixed_process_loss_applied = 'Y') THEN
1296 l_batch_header_rec := x_batch_header_rec;
1297 l_in_material_details := l_material_details;
1298 gme_fpl_pvt.apply_fixed_process_loss
1299 (p_batch_header_rec => l_batch_header_rec
1300 ,p_material_tbl => l_in_material_details
1301 ,p_organization_id => l_batch_header_rec.organization_id
1302 ,p_creation_mode => p_creation_mode
1303 ,p_called_from => 1
1304 ,x_batch_header_rec => x_batch_header_rec
1305 ,x_material_tbl => l_material_details
1306 ,x_return_status => x_return_status
1307 );
1308 IF (g_debug = gme_debug.g_log_statement) THEN
1309 gme_debug.put_line ('Return status after apply Fixed Proccess Loss '
1310 || x_return_status);
1311 END IF;
1312 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1313 RAISE Fixed_process_loss_failure;
1314 ELSE
1315 IF (g_debug = gme_debug.g_log_statement) THEN
1316 gme_debug.put_line ('Return from Apply Fixed Proccess Loss is ' || x_return_status);
1317 END IF;
1318 END IF;
1319 END IF;
1320
1321 -- Fixed Process Loss ME
1322 IF (g_debug = gme_debug.g_log_statement) THEN
1323 gme_debug.put_line ('After calling Apply Fixed Proccess Loss return is' || x_return_status);
1324 gme_debug.put_line ('fnd return is ' || fnd_api.g_ret_sts_success);
1325 END IF;
1326
1327 -- If we've found any errors, don't start the updates.
1328 -- IF gme_common_pvt.g_error_count > 0 THEN
1329 -- RAISE expected_error;
1330 -- END IF;
1331
1332 IF g_debug <= gme_debug.g_log_statement THEN
1333 gme_debug.put_line ( g_pkg_name
1334 || '.'
1335 || l_api_name
1336 || 'Start dbl pcrocessing');
1337 END IF;
1338
1339 -- Data construction complete so start the database phase
1340 x_batch_header_rec.formula_id := l_formula_master.formula_id;
1341 x_batch_header_rec.routing_id := l_recipe.routing_id;
1342 -- From here on all errors are fatal so the validation level is not checked.
1343 l_in_batch_header := x_batch_header_rec;
1344 /*5698727 rework Begin we use timestamp initially as its unique. later we update
1345 with actual batch_no
1346 gme_common_pvt.create_document_no (l_in_batch_header
1347 ,x_batch_header_rec);*/
1348 SELECT trim(TO_CHAR(systimestamp,'DD:MM:YYYY HH24:MI:SS:FF6')) INTO l_doc_timestamp FROM DUAL;
1349
1350 IF g_debug <= gme_debug.g_log_statement THEN
1351 gme_debug.put_line ( g_pkg_name
1352 || '.'
1353 || l_api_name
1354 || 'Initial Doc No(timestamp): '||l_doc_timestamp);
1355 END IF;
1356
1357 IF l_doc_numbering = gme_common_pvt.g_auto_doc_numbering THEN
1358 x_batch_header_rec.batch_no := l_doc_timestamp;
1359 END IF;
1360 --5698727 rework End
1361
1362 IF x_batch_header_rec.batch_no IS NULL THEN
1363 -- Report error that document number was invalid or could not be generated
1364 RAISE unexpected_error;
1365 ELSE
1366 IF (x_batch_header_rec.batch_type = 0) THEN
1367 l_prefix := FND_PROFILE.VALUE('GME_BATCH_PREFIX');
1368 ELSE
1369 l_prefix := FND_PROFILE.VALUE('GME_FPO_PREFIX');
1370 END IF;
1371 /*5698727 rework commented the following code. We will validate the wip entity
1372 when we are updating the wip_entities table later once the actual doc_no is available */
1373 /* Bug6398619 we have to validate the batch no for the manual doc ordering as
1374 the time stamp is not created for the manual ordering */
1375 IF l_doc_numbering <> gme_common_pvt.g_auto_doc_numbering THEN
1376 validate_wip_entity(p_organization_id => x_batch_header_rec.organization_id,
1377 p_batch_no => l_prefix||x_batch_header_rec.batch_no,
1378 x_return_status => l_return_status);
1379 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1380 RAISE wip_entity_err;
1381 END IF;
1382 END IF;
1383 END IF;
1384
1385
1386 l_number_of_text_lines := 0;
1387
1388 --Bug#5112133 add some debug messages for copy formula text GME Parameter
1389 IF (g_debug = gme_debug.g_log_statement) THEN
1390 gme_debug.put_line ('copy_formula_text_ind = '||gme_common_pvt.g_copy_formula_text_ind);
1391 END IF;
1392 -- If the formula has text and we have to copy it then generate
1393 -- the text header row first as database constraints impose this
1394 IF gme_common_pvt.g_copy_formula_text_ind = 1
1395 AND l_formula_master.text_code IS NOT NULL THEN
1396 l_return :=
1397 gme_text_dbl.insert_header_row (l_text_header, l_text_header);
1398
1399 IF l_return THEN
1400 x_batch_header_rec.text_code := l_text_header.text_code;
1401 ELSE
1402 --Bug#5112133
1403 IF (g_debug = gme_debug.g_log_statement) THEN
1404 gme_debug.put_line ('Error in inserting text code = '||l_formula_master.text_code);
1405 END IF;
1406 -- We could not insert the text header. Panic
1407 RAISE expected_error;
1408 END IF;
1409 END IF;
1410
1411 /* update automatic_step_calculation */
1412 x_batch_header_rec.automatic_step_calculation :=
1413 l_recipe.calculate_step_quantity;
1414
1415 IF l_recipe.routing_id IS NOT NULL THEN
1416 x_batch_header_rec.poc_ind := 'Y';
1417 ELSE
1418 x_batch_header_rec.poc_ind := 'N';
1419 END IF;
1420 -- Pawan Added code for enhanced_pi_ind--GMO
1421 IF p_creation_mode <> 'LCF' THEN
1422 gmd_recipe_fetch_pub.FETCH_ENHANCED_PI_IND (
1423 p_recipe_id => l_recipe.recipe_id
1424 ,p_recipe_validity_rule_id => l_recipe_validity_rule.recipe_validity_rule_id
1425 ,x_enhanced_pi_ind => l_enhanced_pi_ind
1426 ,x_return_status => l_return_status);
1427 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1428 RAISE expected_error;
1429 END IF;
1430 x_batch_header_rec.ENHANCED_PI_IND := l_enhanced_pi_ind ;
1431 ELSE
1432 -- Kapil Bug# 5458674. To Pass the PI Indicator for LCF Batches
1433 x_batch_header_rec.ENHANCED_PI_IND := p_batch_header_rec.enhanced_pi_ind;
1434 END IF; /* IF p_creation_mode <> 'LCF' */
1435
1436 --FPBug#4395561 Start
1437 /* call create flex procedure to insert the default values of the BACTH_FLEX
1438 DFF's segments if they are enabled */
1439 gme_validate_flex_fld_pvt.create_flex_batch_header(x_batch_header_rec,
1440 x_batch_header_rec,
1441 l_return_status);
1442 IF l_return_status <> FND_API.g_ret_sts_success THEN
1443 RAISE create_flex_failure;
1444 END IF;
1445 --FPBug#4395561 End
1446
1447 /* This is not a migrated batch, so let's indicate so. */
1448 x_batch_header_rec.migrated_batch_ind := 'N';
1449 l_return :=
1450 gme_batch_header_dbl.insert_row (x_batch_header_rec
1451 ,x_batch_header_rec);
1452
1453 -- If header was inserted OK, then insert the material details. As
1454 -- above, if we are copying text, generate and insert the text and codes first
1455 IF l_return = TRUE THEN
1456
1457 IF x_batch_header_rec.text_code IS NOT NULL THEN
1458 --Bug#5112133
1459 IF (g_debug = gme_debug.g_log_statement) THEN
1460 gme_debug.put_line ('x_batch_header_rec.text_code = '||x_batch_header_rec.text_code);
1461 gme_debug.put_line ('Inserting formula header text in batch header edit text');
1462 END IF;
1463 FOR l_fm_text_tbl_row IN
1464 fm_text_tbl_cursor (l_formula_master.text_code) LOOP
1465 l_number_of_text_lines := l_number_of_text_lines + 1;
1466 l_text_table (l_number_of_text_lines).text_code :=
1467 x_batch_header_rec.text_code;
1468 l_text_table (l_number_of_text_lines).line_no :=
1469 l_fm_text_tbl_row.line_no;
1470 l_text_table (l_number_of_text_lines).lang_code :=
1471 l_fm_text_tbl_row.lang_code;
1472 l_text_table (l_number_of_text_lines).paragraph_code :=
1473 l_fm_text_tbl_row.paragraph_code;
1474 l_text_table (l_number_of_text_lines).sub_paracode :=
1475 l_fm_text_tbl_row.sub_paracode;
1476
1477 IF l_fm_text_tbl_row.line_no = -1 THEN
1478 -- Start constructing key field for text code.
1479 l_text_table (l_number_of_text_lines).text :=
1480 'gme_batch_header'
1481 || '|'
1482 || TO_CHAR (x_batch_header_rec.batch_id)
1483 || '|';
1484 ELSE
1485 l_text_table (l_number_of_text_lines).text :=
1486 l_fm_text_tbl_row.text;
1487 END IF;
1488 END LOOP;
1489 END IF;
1490
1491 --Bug#5112133
1492 IF (g_debug = gme_debug.g_log_statement) THEN
1493 gme_debug.put_line ('l_number_of_formula_lines = '||l_number_of_formula_lines);
1494 END IF;
1495
1496 FOR l_row_count IN 1 .. l_number_of_formula_lines LOOP
1497 -- Create rows for details' text.
1498 IF (g_debug = gme_debug.g_log_statement) THEN
1499 gme_debug.put_line ('l text code is '||l_material_details (l_row_count).text_code);
1500 END IF;
1501 IF l_material_details (l_row_count).text_code IS NOT NULL
1502 AND gme_common_pvt.g_copy_formula_text_ind = 1 THEN
1503 l_text_code := l_material_details (l_row_count).text_code;
1504 l_return :=
1505 gme_text_dbl.insert_header_row (l_text_header
1506 ,l_text_header);
1507
1508 IF l_return THEN
1509 l_material_details (l_row_count).text_code :=
1510 l_text_header.text_code;
1511
1512 -- This fetches using the fm text data based on the fm text code.
1513 FOR l_fm_text_tbl_row IN fm_text_tbl_cursor (l_text_code) LOOP
1514 l_number_of_text_lines := l_number_of_text_lines + 1;
1515 l_text_table (l_number_of_text_lines).text_code :=
1516 l_text_header.text_code;
1517 l_text_table (l_number_of_text_lines).line_no :=
1518 l_fm_text_tbl_row.line_no;
1519 l_text_table (l_number_of_text_lines).lang_code :=
1520 l_fm_text_tbl_row.lang_code;
1521 l_text_table (l_number_of_text_lines).paragraph_code :=
1522 l_fm_text_tbl_row.paragraph_code;
1523 l_text_table (l_number_of_text_lines).sub_paracode :=
1524 l_fm_text_tbl_row.sub_paracode;
1525
1526 IF l_fm_text_tbl_row.line_no = -1 THEN
1527 -- Start constructing key field for text code.
1528 l_text_string :=
1529 'gme_material_details'
1530 || '|'
1531 || TO_CHAR
1532 (l_material_details (l_row_count).batch_id)
1533 || '|';
1534 l_text_string :=
1535 l_text_string
1536 || l_material_details (l_row_count).line_type
1537 || '|';
1538 l_text_string :=
1539 l_text_string
1540 || l_material_details (l_row_count).line_no;
1541 l_text_table (l_number_of_text_lines).text :=
1542 l_text_string;
1543 ELSE
1544 l_text_table (l_number_of_text_lines).text :=
1545 l_fm_text_tbl_row.text;
1546 END IF;
1547 END LOOP; -- text lines loop.
1548 ELSE
1549 -- We could not insert the text header. Panic
1550 --Bug#5112133
1551 IF (g_debug = gme_debug.g_log_statement) THEN
1552 gme_debug.put_line ('Error in inserting text header');
1553 END IF;
1554 RAISE expected_error;
1555 END IF;
1556 END IF;
1557
1558 l_material_details (l_row_count).batch_id :=
1559 x_batch_header_rec.batch_id;
1560
1561 --FPBug#4395561 Start
1562 l_return_status := NULL;
1563 gme_validate_flex_fld_pvt.create_flex_material_details(
1564 l_material_details (l_row_count),
1565 l_material_details (l_row_count),
1566 l_return_status);
1567 IF l_return_status <> FND_API.g_ret_sts_success THEN
1568 RAISE create_flex_failure;
1569 END IF;
1570 --FPBug#4395561 End
1571 --Pawan changes for GMO
1572 IF (l_material_details (l_row_count).line_type =
1573 gme_common_pvt.g_line_type_ing )
1574 AND (l_material_details (l_row_count).phantom_type = 0 )
1575 --Pawan Kumar add for bug 5365883
1576 -- Kapil M. Bug# 5458674. Reverted back the changes to support LCF Batches.
1577 -- AND (p_creation_mode <> 'LCF')
1578 THEN
1579
1580 GMO_DISPENSE_GRP.IS_DISPENSE_ITEM (p_api_version => 1.0,
1581 p_init_msg_list => fnd_api.g_false ,
1582 x_return_status => l_return_status ,
1583 x_msg_count => l_message_count ,
1584 x_msg_data => l_message_list ,
1585 p_inventory_item_id => l_material_details (l_row_count).inventory_item_id,
1586 p_organization_id => x_batch_header_rec.organization_id,
1587 p_recipe_id => l_recipe.recipe_id,
1588 x_dispense_required => l_dispense_required,
1589 x_dispense_config_id => l_config_id);
1590
1591 IF l_return_status IN ('S', 'W') THEN
1592 l_material_details (l_row_count).dispense_ind := l_dispense_required ;
1593 END IF;
1594
1595 END IF;
1596 -- GMO changes end
1597 IF (g_debug = gme_debug.g_log_statement) THEN
1598 gme_debug.put_line ('MATERTIAL lines to be INSERTED ' || l_row_count);
1599 END IF;
1600
1601 l_return :=
1602 gme_material_details_dbl.insert_row
1603 (l_material_details (l_row_count)
1604 ,l_material_details (l_row_count) );
1605
1606 IF (g_debug = gme_debug.g_log_statement) THEN
1607 gme_debug.put_line ('MATERTIAL_INSERTED');
1608 END IF;
1609
1610 IF NOT l_return THEN
1611 RAISE unexpected_error;
1612 END IF;
1613 -- Pawan Kumar added for bug 4947535
1614
1615 IF (l_config_id IS NOT NULL AND l_material_details(l_row_count).dispense_ind = 'Y') then
1616 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1617 gme_debug.put_line (g_pkg_name||'.'||l_api_name||'Instantiating material line id'
1618 || l_material_details (l_row_count).material_detail_id);
1619 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' config id ' || l_config_id);
1620 END IF;
1621 GMO_DISPENSE_GRP.INSTANTIATE_DISPENSE_SETUP
1622 (p_api_version => 1.0
1623 ,p_dispense_config_id => l_config_id
1624 ,p_entity_name => GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY
1625 ,p_entity_key => l_material_details(l_row_count).material_detail_id
1626 ,p_init_msg_list => FND_API.G_FALSE
1627 ,x_return_status => l_return_status
1628 ,x_msg_count => l_message_count
1629 ,x_msg_data => l_message_list);
1630 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1631
1632 RAISE unexpected_error;
1633 END IF;
1634 END IF;
1635
1636 END LOOP;
1637
1638 -- Insert Text detail for all the new text codes, which
1639 -- hold the data for all the detail lines and the batch header.
1640 FOR l_row_count IN 1 .. l_text_table.COUNT LOOP
1641 l_return :=
1642 gme_text_dbl.insert_text_row (l_text_table (l_row_count)
1643 ,l_text_table (l_row_count) );
1644
1645 IF NOT l_return THEN
1646 RAISE unexpected_error;
1647 END IF;
1648 END LOOP;
1649 ELSE
1650 RAISE unexpected_error;
1651 END IF;
1652
1653 -- Bath was created OK, so now try to create the steps (if needed)
1654 /*Restore the dates as supplied by user If user has suppled plan_start_date, it will be used to calculate other two.
1655 If user has supplied plan_cmplt_date and plan_start_date is null then plan_cmplt_date will be used to calculate other 2 dates; */
1656 x_batch_header_rec.plan_start_date := p_batch_header_rec.plan_start_date;
1657 x_batch_header_rec.plan_cmplt_date := p_batch_header_rec.plan_cmplt_date;
1658 x_batch_header_rec.due_date := p_batch_header_rec.due_date;
1659
1660 SELECT primary_uom_code
1661 INTO l_prim_item_um
1662 FROM mtl_system_items_b
1663 WHERE inventory_item_id = l_recipe_validity_rule.inventory_item_id
1664 AND organization_id = x_batch_header_rec.organization_id;
1665
1666 OPEN get_prim_prod (x_batch_header_rec.batch_id
1667 ,l_recipe_validity_rule.inventory_item_id);
1668
1669 FETCH get_prim_prod
1670 INTO l_prim_prod_qty, l_prim_prod_um;
1671
1672 IF get_prim_prod%FOUND THEN
1673 l_prim_prod_found := TRUE;
1674 ELSE
1675 l_prim_prod_found := FALSE;
1676 END IF;
1677
1678 CLOSE get_prim_prod;
1679
1680 IF g_debug <= gme_debug.g_log_statement THEN
1681 gme_debug.put_line ( g_pkg_name
1682 || '.'
1683 || l_api_name
1684 || 'Start step processing');
1685 END IF;
1686
1687 IF gme_common_pvt.routings.COUNT <> 0 THEN
1688 gme_create_step_pvt.create_batch_steps
1689 (p_recipe_rout_step_tbl => gme_common_pvt.steps
1690 ,p_recipe_rout_act_tbl => gme_common_pvt.activities
1691 ,p_recipe_rout_resc_tbl => gme_common_pvt.resources
1692 ,p_resc_parameters_tbl => gme_common_pvt.process_parameters
1693 ,p_recipe_rout_matl_tbl => gme_common_pvt.routing_materials
1694 ,p_routing_depd_tbl => gme_common_pvt.step_dependencies
1695 ,p_gme_batch_header_rec => x_batch_header_rec
1696 ,p_use_workday_cal => p_use_workday_cal
1697 ,p_contiguity_override => p_contiguity_override
1698 ,p_ignore_qty_below_cap => p_ignore_qty_below_cap
1699 ,x_return_status => x_return_status);
1700
1701 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1702 RAISE steps_creation_failure;
1703 END IF;
1704
1705 IF NOT (gme_batch_header_dbl.fetch_row (x_batch_header_rec
1706 ,x_batch_header_rec) ) THEN
1707 RAISE batch_header_fetch_failure;
1708 END IF;
1709 ELSE
1710 /* routing count = 0... there are no steps, so calculate the dates with prod rules if they exist... */
1711 IF l_prim_prod_found THEN
1712 l_temp_qty :=
1713 inv_convert.inv_um_convert
1714 (l_recipe_validity_rule.inventory_item_id
1715 ,5
1716 ,l_prim_prod_qty
1717 ,l_prim_prod_um
1718 ,l_prim_item_um
1719 ,NULL
1720 ,NULL);
1721
1722 IF l_temp_qty < 0 THEN
1723 l_item_id := l_recipe_validity_rule.inventory_item_id;
1724 l_from_uom := l_prim_prod_um;
1725 l_to_uom := l_prim_item_um;
1726 RAISE conversion_failure;
1727 END IF;
1728
1729 IF (gme_common_pvt.calc_date_from_prod_rule
1730 (p_organization_id => x_batch_header_rec.organization_id
1731 ,p_inventory_item_id => l_recipe_validity_rule.inventory_item_id
1732 ,p_item_qty => l_temp_qty
1733 ,p_start_date => x_batch_header_rec.plan_start_date
1734 ,p_cmplt_date => x_batch_header_rec.plan_cmplt_date
1735 ,x_start_date => x_batch_header_rec.plan_start_date
1736 ,x_cmplt_date => x_batch_header_rec.plan_cmplt_date) ) THEN
1737 l_no_prod_rule_found := FALSE;
1738 ELSE
1739 l_no_prod_rule_found := TRUE;
1740 END IF;
1741 ELSE
1742 -- prim prod was not found...
1743 l_no_prod_rule_found := TRUE;
1744 END IF;
1745
1746 IF l_no_prod_rule_found THEN
1747 IF x_batch_header_rec.plan_start_date IS NOT NULL
1748 AND x_batch_header_rec.plan_cmplt_date IS NULL THEN
1749 x_batch_header_rec.plan_cmplt_date :=
1750 x_batch_header_rec.plan_start_date;
1751 ELSIF x_batch_header_rec.plan_start_date IS NULL
1752 AND x_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
1753 x_batch_header_rec.plan_start_date :=
1754 x_batch_header_rec.plan_cmplt_date;
1755 ELSIF x_batch_header_rec.plan_start_date IS NULL
1756 AND x_batch_header_rec.plan_cmplt_date IS NULL THEN
1757 x_batch_header_rec.plan_start_date :=
1758 gme_common_pvt.g_timestamp;
1759 x_batch_header_rec.plan_cmplt_date :=
1760 gme_common_pvt.g_timestamp;
1761 END IF;
1762
1763 IF (g_debug = gme_debug.g_log_statement) THEN
1764 gme_debug.put_line
1765 ( 'production rule start_date '
1766 || TO_CHAR
1767 (x_batch_header_rec.plan_start_date
1768 ,'DD-MON-YYYY HH24:MI:SS') );
1769 gme_debug.put_line
1770 ( 'production rule end_date '
1771 || TO_CHAR
1772 (x_batch_header_rec.plan_cmplt_date
1773 ,'DD-MON-YYYY HH24:MI:SS') );
1774 END IF;
1775 ELSE
1776 IF p_use_workday_cal = fnd_api.g_true THEN
1777 gmd_recipe_fetch_pub.fetch_contiguous_ind
1778 (p_recipe_id => l_recipe_validity_rule.recipe_id
1779 ,p_orgn_id => p_batch_header_rec.organization_id
1780 ,p_recipe_validity_rule_id => l_recipe_validity_rule.recipe_validity_rule_id
1781 ,x_contiguous_ind => l_cont_ind
1782 ,x_return_status => l_return_status);
1783
1784 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1785 IF (g_debug = gme_debug.g_log_statement) THEN
1786 gme_debug.put_line
1787 ( 'GMD_RECIPE_FETCH_PUB.FETCH_CONTIGUOUS_IND returned error '
1788 || l_return_status);
1789 END IF;
1790
1791 l_cont_ind := 0;
1792 END IF;
1793
1794 IF (g_debug = gme_debug.g_log_statement) THEN
1795 gme_debug.put_line ( 'Calendar code found '
1796 || gme_common_pvt.g_calendar_code);
1797 gme_debug.put_line ('Cont ID found ' || l_cont_ind);
1798 END IF;
1799
1800 l_duration :=
1801 ( x_batch_header_rec.plan_cmplt_date
1802 - x_batch_header_rec.plan_start_date)
1803 * 24;
1804
1805 IF (g_debug = gme_debug.g_log_statement) THEN
1806 gme_debug.put_line ('l duration ' || l_duration);
1807 END IF;
1808
1809 IF p_batch_header_rec.plan_start_date IS NOT NULL
1810 OR ( p_batch_header_rec.plan_start_date IS NULL
1811 AND p_batch_header_rec.plan_cmplt_date IS NULL) THEN
1812 gmp_calendar_api.get_contiguous_periods
1813 (p_api_version => 1
1814 ,p_init_msg_list => FALSE
1815 ,p_start_date => NVL
1816 (p_batch_header_rec.plan_start_date
1817 ,x_batch_header_rec.plan_start_date)
1818 ,p_end_date => NULL
1819 ,p_calendar_code => gme_common_pvt.g_calendar_code
1820 ,p_duration => l_duration
1821 ,p_output_tbl => l_contig_period_tbl
1822 ,x_return_status => l_return_status);
1823
1824 IF (l_return_status <> x_return_status) THEN
1825 RAISE error_cont_period;
1826 END IF;
1827
1828 l_cal_count := l_contig_period_tbl.COUNT;
1829
1830 IF (g_debug = gme_debug.g_log_statement) THEN
1831 gme_debug.put_line ('l cal_count ' || l_cal_count);
1832 gme_debug.put_line ( 'p_contiguity_override '
1833 || p_contiguity_override);
1834 END IF;
1835
1836 IF l_cont_ind = 1
1837 AND p_contiguity_override = fnd_api.g_false THEN
1838 IF l_cal_count > 1 THEN
1839 RAISE error_non_contiguious;
1840 END IF;
1841 END IF;
1842
1843 x_batch_header_rec.plan_cmplt_date :=
1844 l_contig_period_tbl (l_cal_count).end_date;
1845 ELSE
1846 IF p_batch_header_rec.plan_cmplt_date IS NOT NULL THEN
1847 gmp_calendar_api.get_contiguous_periods
1848 (p_api_version => 1
1849 ,p_init_msg_list => FALSE
1850 ,p_start_date => NULL
1851 ,p_end_date => p_batch_header_rec.plan_cmplt_date
1852 ,p_calendar_code => gme_common_pvt.g_calendar_code
1853 ,p_duration => l_duration
1854 ,p_output_tbl => l_contig_period_tbl
1855 ,x_return_status => l_return_status);
1856
1857 IF (l_return_status <> x_return_status) THEN
1858 RAISE error_cont_period;
1859 END IF;
1860
1861 l_cal_count := l_contig_period_tbl.COUNT;
1862
1863 IF (g_debug = gme_debug.g_log_statement) THEN
1864 gme_debug.put_line ('l cal_count ' || l_cal_count);
1865 gme_debug.put_line ( 'p_contiguity_override '
1866 || p_contiguity_override);
1867 END IF;
1868
1869 IF l_cont_ind = 1
1870 AND p_contiguity_override = fnd_api.g_false THEN
1871 IF l_cal_count > 1 THEN
1872 RAISE error_non_contiguious;
1873 END IF;
1874 END IF;
1875
1876 x_batch_header_rec.plan_start_date :=
1877 l_contig_period_tbl (l_cal_count).start_date;
1878 END IF;
1879 END IF;
1880 END IF;
1881 END IF;
1882
1883 UPDATE gme_batch_header
1884 SET plan_start_date = x_batch_header_rec.plan_start_date
1885 ,plan_cmplt_date = x_batch_header_rec.plan_cmplt_date
1886 ,due_date =
1887 NVL (x_batch_header_rec.due_date
1888 ,x_batch_header_rec.plan_cmplt_date)
1889 WHERE batch_id = x_batch_header_rec.batch_id;
1890 END IF;
1891 --5698727
1892 l_in_batch_header1 := x_batch_header_rec ;
1893 gme_common_pvt.create_document_no (l_in_batch_header1
1894 ,x_batch_header_rec);
1895 --5698727 rework
1896 /*Bug#6398619 the validation here is required only for the automatic doc numbering */
1897 IF l_doc_numbering = gme_common_pvt.g_auto_doc_numbering THEN
1898 validate_wip_entity(p_organization_id => x_batch_header_rec.organization_id,
1899 p_batch_no => l_prefix||x_batch_header_rec.batch_no,
1900 x_return_status => l_return_status);
1901
1902 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1903 RAISE wip_entity_err;
1904 END IF;
1905
1906 /* update the batch_no with the actual value */
1907 UPDATE gme_batch_header
1908 SET batch_no = x_batch_header_rec.batch_no
1909 WHERE batch_id = x_batch_header_rec.batch_id;
1910
1911 /*5698727 rework update the wip_entities table with actual batch no*/
1912 UPDATE wip_entities
1913 SET wip_entity_name = l_prefix||x_batch_header_rec.batch_no
1914 WHERE organization_id = x_batch_header_rec.organization_id
1915 AND wip_entity_name = l_prefix||l_doc_timestamp;
1916 --5698727
1917 END IF;
1918
1919 IF (g_debug = gme_debug.g_log_statement) THEN
1920 gme_debug.put_line ( 'Calculated Start date is '
1921 || TO_CHAR (x_batch_header_rec.plan_start_date
1922 ,'DD-MON-YYYY HH24:MI:SS') );
1923 gme_debug.put_line ( 'Calculated Cmplt date is '
1924 || TO_CHAR (x_batch_header_rec.plan_cmplt_date
1925 ,'DD-MON-YYYY HH24:MI:SS') );
1926 END IF;
1927
1928 IF ( p_batch_header_rec.plan_start_date IS NOT NULL
1929 AND p_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
1930 -- IF routings exists and calculated date is greater than the date entered,
1931 -- then truncate all the dates to dates entered
1932 IF (gme_common_pvt.routings.COUNT <> 0) THEN
1933 IF (x_batch_header_rec.plan_cmplt_date >
1934 p_batch_header_rec.plan_cmplt_date) THEN
1935 x_batch_header_rec.plan_start_date :=
1936 p_batch_header_rec.plan_start_date;
1937 x_batch_header_rec.plan_cmplt_date :=
1938 p_batch_header_rec.plan_cmplt_date;
1939 gme_reschedule_batch_pvt.truncate_date
1940 (p_batch_header_rec => x_batch_header_rec
1941 ,p_date => 1
1942 ,p_batchstep_id => NULL
1943 ,x_return_status => l_return_status);
1944
1945 IF l_return_status <> x_return_status THEN
1946 RAISE error_truncate_date;
1947 END IF;
1948 -- Routings exists and calculated date is less than date entered,
1949 -- then updates the dates as entered by the user.
1950 ELSE
1951 UPDATE gme_batch_header
1952 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1953 ,due_date =
1954 NVL (x_batch_header_rec.due_date
1955 ,p_batch_header_rec.plan_cmplt_date)
1956 ,last_updated_by = gme_common_pvt.g_user_ident
1957 ,last_update_date = gme_common_pvt.g_timestamp
1958 ,last_update_login = gme_common_pvt.g_login_id
1959 WHERE batch_id = x_batch_header_rec.batch_id;
1960 END IF;
1961 END IF; -- End gme_common_pvt.routings.COUNT <> 0
1962
1963 UPDATE gme_batch_header
1964 SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
1965 ,due_date =
1966 NVL (x_batch_header_rec.due_date
1967 ,p_batch_header_rec.plan_cmplt_date)
1968 ,last_updated_by = gme_common_pvt.g_user_ident
1969 ,last_update_date = gme_common_pvt.g_timestamp
1970 ,last_update_login = gme_common_pvt.g_login_id
1971 WHERE batch_id = x_batch_header_rec.batch_id;
1972 END IF; -- End start and completion dates not null.
1973
1974 IF NOT (gme_batch_header_dbl.fetch_row (x_batch_header_rec
1975 ,x_batch_header_rec) ) THEN
1976 RAISE batch_header_fetch_failure;
1977 END IF;
1978
1979 IF g_debug <= gme_debug.g_log_statement THEN
1980 gme_debug.put_line ( g_pkg_name
1981 || '.'
1982 || l_api_name
1983 || 'Calculate mtl req date');
1984 END IF;
1985
1986 l_item_substituted := FALSE;
1987 FOR l_row_count IN 1 .. l_number_of_formula_lines LOOP
1988 gme_common_pvt.calc_mtl_req_date
1989 (p_batch_header_rec => x_batch_header_rec
1990 ,p_batchstep_rec => NULL
1991 ,p_mtl_dtl_rec => l_material_details (l_row_count)
1992 ,x_mtl_req_date => l_material_details (l_row_count).material_requirement_date
1993 ,x_return_status => l_return_status);
1994
1995 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1996 RAISE calc_mtl_req_date_err;
1997 ELSE
1998 --FPBug#4351032 Start
1999 /*Material requirement date is in our hands we need to find out whether particular ingredient
2000 is effective or not depending on substitution list defined
2001 */
2002 l_curr_item_substituted := FALSE;
2003 IF l_material_details (l_row_count).phantom_type = 0 AND
2004 l_material_details (l_row_count).line_type = -1 THEN
2005
2006 -- nsinghi bug#5674398
2007 -- Pass the ingredient substitution date rather than the material requirement date.
2008 -- At this point, steps table is not updated. Hence need to have similar logic here too
2009 -- as in gme_api_grp.get_ingr_sub_date.
2010
2011 IF gme_common_pvt.g_ingr_sub_date = 2 THEN
2012 l_ingred_req_date := l_material_details (l_row_count).material_requirement_date;
2013 ELSIF gme_common_pvt.g_ingr_sub_date = 1 THEN
2014 l_ingred_req_date := NVL(p_batch_header_rec.plan_start_date, x_batch_header_rec.plan_start_date);
2015 ELSIF gme_common_pvt.g_ingr_sub_date = 3 THEN
2016 l_ingred_req_date := NVL(p_batch_header_rec.plan_cmplt_date, x_batch_header_rec.plan_cmplt_date);
2017 END IF;
2018
2019 IF g_debug <= gme_debug.g_log_statement THEN
2020 gme_debug.put_line ( g_pkg_name||'.'|| l_api_name
2021 || ' l_ingred_req_date '||TO_CHAR(l_ingred_req_date, 'MON-DD-YYYY HH24:MI:SS'));
2022 END IF;
2023
2024 gmdfmval_pub.get_substitute_line_item (
2025 pformulaline_id => l_material_details (l_row_count).formulaline_id,
2026 pitem_id => l_material_details (l_row_count).inventory_item_id,
2027 pqty => l_material_details (l_row_count).plan_qty,
2028 puom => l_material_details (l_row_count).dtl_um,
2029 pscale_multiple => l_material_details (l_row_count).scale_multiple,
2030 -- nsinghi bug#5674398. Pass the ingredient substitution date rather than the material requirement date
2031 -- pdate => l_material_details (l_row_count).material_requirement_date,
2032 pdate => l_ingred_req_date,
2033 xformuladetail_tbl => l_formula_tbl);
2034
2035 IF l_material_details (l_row_count).inventory_item_id <> l_formula_tbl(1).inventory_item_id THEN
2036 /*get item primary product uom */
2037 -- pawan kumar changed this cursor for bug 4917631
2038 OPEN item_master_cursor_sub (l_formula_tbl(1).inventory_item_id,x_batch_header_rec.organization_id);
2039 FETCH item_master_cursor_sub INTO
2040 l_new_item_rec.primary_uom_code,l_new_item_rec.ENG_ITEM_FLAG,
2041 l_new_item_rec.PROCESS_EXECUTION_ENABLED_FLAG;
2042 CLOSE item_master_cursor_sub;
2043
2044 /* check whether item is process execution enabled and it's not experimental item incase of lab batches */
2045 IF l_new_item_rec.PROCESS_EXECUTION_ENABLED_FLAG <> 'Y' OR
2046 ( l_new_item_rec.ENG_ITEM_FLAG = 'Y' AND NVL(x_batch_header_rec.laboratory_ind,0) <> 1 ) THEN
2047 RAISE invalid_item_substitute;
2048 END IF;
2049
2050 IF (g_debug <= gme_debug.g_log_statement) THEN
2051 gme_debug.put_line('Calculated mtl req date: '||l_material_details (l_row_count).material_requirement_date);
2052 gme_debug.put_line('Initial Item ID: '||l_material_details (l_row_count).inventory_item_id);
2053 gme_debug.put_line('Initial Plan Qty: '||l_material_details (l_row_count).plan_qty);
2054 gme_debug.put_line('Initial Detail UOM: '||l_material_details (l_row_count).dtl_um);
2055 gme_debug.put_line('Initial Scale multiple: '||l_material_details (l_row_count).scale_multiple);
2056 END IF;
2057
2058 l_item_substituted := TRUE;
2059 l_curr_item_substituted := TRUE;
2060
2061 /*reassign new item values to material details */
2062 l_material_details (l_row_count).inventory_item_id := l_formula_tbl(1).inventory_item_id;
2063 l_material_details (l_row_count).plan_qty := l_formula_tbl(1).qty;
2064 l_material_details (l_row_count).original_qty := l_formula_tbl(1).qty;
2065 l_material_details (l_row_count).dtl_um := l_formula_tbl(1).detail_uom;
2066 l_material_details (l_row_count).scale_multiple := l_formula_tbl(1).scale_multiple;
2067
2068 IF (g_debug <= gme_debug.g_log_statement) THEN
2069 gme_debug.put_line('Substitued Item ID: '||l_formula_tbl(1).inventory_item_id);
2070 gme_debug.put_line('Substitued Plan Qty: '||l_formula_tbl(1).qty);
2071 gme_debug.put_line('Substituted Detail UOM: '||l_formula_tbl(1).detail_uom);
2072 gme_debug.put_line('Substituted Scale multiple: '||l_formula_tbl(1).scale_multiple);
2073 END IF;
2074
2075 /*if the new item uom is not the primary uom then calculate the primary quantity*/
2076 IF l_formula_tbl(1).detail_uom <> l_new_item_rec.primary_uom_code THEN
2077 l_temp_qty := inv_convert.inv_um_convert
2078 (item_id => l_material_details(l_row_count).inventory_item_id
2079 ,precision => 5
2080 ,from_quantity => l_material_details(l_row_count).plan_qty
2081 ,from_unit => l_material_details(l_row_count).dtl_um
2082 ,to_unit => l_new_item_rec.primary_uom_code
2083 ,from_name => NULL
2084 ,to_name => NULL);
2085 ELSE
2086 l_temp_qty := l_material_details(l_row_count).plan_qty;
2087 END IF;
2088
2089 IF (g_debug < gme_debug.g_log_statement) THEN
2090 gme_debug.put_line('Initial Original Primary Qty: '||l_material_details (l_row_count).original_primary_qty);
2091 gme_debug.put_line('Substituted Original Priamry Qty: '||l_temp_qty);
2092 END IF;
2093
2094 /* assign to original primary qty */
2095 l_material_details (l_row_count).original_primary_qty := l_temp_qty;
2096 ELSE
2097 l_curr_item_substituted := FALSE;
2098 END IF; /* item id check */
2099 END IF; /* phantom type and line type check */
2100 --FPBug#4351032 End
2101
2102 l_return :=
2103 gme_material_details_dbl.update_row
2104 (l_material_details (l_row_count) );
2105 /* FPBug#4351032 Updating original primary qty field manually
2106 as this update is not allowed in above dbl procedure */
2107
2108 UPDATE gme_material_details
2109 SET original_primary_qty = l_material_details (l_row_count).original_primary_qty
2110 WHERE material_detail_id = l_material_details (l_row_count).material_detail_id;
2111
2112 -- nsinghi bug#5674398
2113 -- if ASQC is on and current material line has been substitued, recalculate step qtys
2114 IF x_batch_header_rec.automatic_step_calculation = 1 AND l_curr_item_substituted THEN
2115 OPEN c_batchsteps (l_material_details(l_row_count).material_detail_id);
2116 FETCH c_batchsteps INTO l_batchstep_rec;
2117 CLOSE c_batchsteps;
2118
2119 IF (g_debug <= gme_debug.g_log_statement) THEN
2120 gme_debug.put_line ('Before updating step qty after the substitution');
2121 END IF;
2122 gme_update_step_qty_pvt.update_step_qty (p_batch_step_rec => l_batchstep_rec,
2123 x_message_count => l_message_count,
2124 x_message_list => l_message_list,
2125 x_return_status => x_return_status,
2126 x_batch_step_rec => x_batchstep_rec
2127 );
2128 IF (g_debug <= gme_debug.g_log_statement) THEN
2129 gme_debug.put_line ('After update step qty, return status is:' || x_return_status);
2130 END IF;
2131 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2132 -- We could not update the step qty
2133 RAISE unexpected_error;
2134 END IF;
2135 END IF;
2136 END IF;
2137 END LOOP;
2138
2139 -- nsinghi bug#5674398 START
2140 -- If atleast one item is substituted and the batch is ASQC batch
2141 -- then we will recalculate the start and end dates by calling reschedule batch
2142 IF NVL (x_batch_header_rec.update_inventory_ind, 'Y') = 'Y' AND l_item_substituted = TRUE THEN
2143 IF x_batch_header_rec.automatic_step_calculation = 1 THEN
2144 /* Assigning the user passed dates so that batch gets rescheduled to the passed dates*/
2145
2146 -- Restore the dates as supplied by user. If user does not provide either start or cmplt dt, use
2147 -- newly created batch's planned start date to re-schedule.
2148 IF p_batch_header_rec.plan_start_date IS NULL AND p_batch_header_rec.plan_cmplt_date IS NULL THEN
2149 x_batch_header_rec.plan_cmplt_date := p_batch_header_rec.plan_cmplt_date;
2150 ELSE
2151 x_batch_header_rec.plan_start_date := p_batch_header_rec.plan_start_date;
2152 x_batch_header_rec.plan_cmplt_date := p_batch_header_rec.plan_cmplt_date;
2153 END IF;
2154
2155 l_batch_header_rec := x_batch_header_rec;
2156 gme_reschedule_batch_pvt.reschedule_batch (
2157 p_batch_header_rec => l_batch_header_rec,
2158 p_use_workday_cal => p_use_workday_cal,
2159 p_contiguity_override => p_contiguity_override,
2160 x_batch_header_rec => x_batch_header_rec,
2161 x_return_status => x_return_status
2162 );
2163
2164 IF (g_debug <= gme_debug.g_log_statement) THEN
2165 gme_debug.put_line('Batch Planned Start Date '||TO_CHAR(x_batch_header_rec.plan_start_date,'MON-DD-YYYY HH24:MI:SS'));
2166 gme_debug.put_line('Batch Planned Completion Date '||TO_CHAR(x_batch_header_rec.plan_cmplt_date,'MON-DD-YYYY HH24:MI:SS'));
2167 END IF;
2168
2169 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2170 RAISE unexpected_error;
2171 END IF; /* Reschedule of the batch to the user passed dates */
2172
2173 /* nsinghi bug#5674398 Added the FETCH condition. Reschedule_batch will update the
2174 material_requirement_date, hence requery material detail records */
2175
2176 l_in_material_detail.batch_id := x_batch_header_rec.batch_id;
2177 IF NOT gme_material_details_dbl.fetch_tab(l_in_material_detail, l_material_details) THEN
2178 RAISE material_dtl_fetch_failure;
2179 END IF;
2180 UPDATE gme_batch_header
2181 SET due_date =
2182 x_batch_header_rec.plan_cmplt_date
2183 ,last_updated_by = gme_common_pvt.g_user_ident
2184 ,last_update_date = gme_common_pvt.g_timestamp
2185 ,last_update_login = gme_common_pvt.g_login_id
2186 WHERE batch_id = x_batch_header_rec.batch_id;
2187
2188 END IF; /* ASQC */
2189 END IF; /* Update inventory*/
2190 -- nsinghi bug#5674398 END
2191
2192 IF (g_debug = gme_debug.g_log_statement) THEN
2193 gme_debug.put_line ('p_creation_mode=' || p_creation_mode);
2194 gme_debug.put_line ('prim_prod_qty=' || l_prim_prod_qty);
2195 gme_debug.put_line ('prim_prod_um=' || l_prim_prod_um);
2196 gme_debug.put_line ('p_batch_size=' || p_batch_size);
2197 gme_debug.put_line ('p_batch_size_uom=' || p_batch_size_uom);
2198 gme_debug.put_line ( 'User passed Start date is '
2199 || TO_CHAR (p_batch_header_rec.plan_start_date
2200 ,'DD-MON-YYYY HH24:MI:SS') );
2201 gme_debug.put_line ( 'User passed Cmplt date is '
2202 || TO_CHAR (p_batch_header_rec.plan_cmplt_date
2203 ,'DD-MON-YYYY HH24:MI:SS') );
2204 END IF;
2205
2206 IF l_prim_prod_found AND (p_creation_mode = 'PRODUCT') THEN
2207 OPEN cur_batch_qty (x_batch_header_rec.batch_id
2208 ,l_recipe_validity_rule.inventory_item_id);
2209
2210 FETCH cur_batch_qty
2211 INTO l_temp_qty;
2212
2213 CLOSE cur_batch_qty;
2214
2215 IF l_temp_qty < 0 THEN
2216 l_item_id := l_recipe_validity_rule.inventory_item_id;
2217 l_from_uom := p_batch_size_uom;
2218 l_to_uom := l_prim_prod_um;
2219 RAISE conversion_failure;
2220 END IF;
2221
2222 IF (g_debug = gme_debug.g_log_statement) THEN
2223 gme_debug.put_line ('requested qty in dtl uom=' || l_temp_qty);
2224 gme_debug.put_line ('requested qty in dtl uom rounded=' || round(l_temp_qty));
2225 END IF;
2226
2227 --sunitha bug # 5484529 Convert the p_batch_size(user entered product plan quantity
2228 --to the Routing uom and then Compare it with the l_temp_qty.
2229
2230 -- Bug 6665602 Back out 5484529... Reinstate original condition.
2231 IF (ROUND (p_batch_size, 5) <> ROUND (l_temp_qty, 5) ) THEN
2232 /* IF (ROUND (inv_convert.inv_um_convert (l_recipe_validity_rule.inventory_item_id
2233 ,5
2234 ,p_batch_size
2235 ,p_batch_size_uom
2236 ,l_recipe_validity_rule.detail_uom
2237 ,NULL
2238 ,NULL), 5) <> ROUND (l_temp_qty, 5) ) THEN */
2239
2240 IF (g_debug = gme_debug.g_log_statement) THEN
2241 gme_debug.put_line
2242 ('Batch failed to be created for qty requested with creation by product qty');
2243 END IF;
2244
2245 RAISE create_by_product_failure;
2246 END IF;
2247 ELSIF NOT l_prim_prod_found THEN
2248 IF (g_debug = gme_debug.g_log_statement) THEN
2249 gme_debug.put_line ('I could not find the prim product');
2250 END IF;
2251 END IF;
2252
2253 IF (g_debug = gme_debug.g_log_statement) THEN
2254 gme_debug.put_line ('Checking dates against validity rule');
2255 END IF;
2256
2257 IF p_creation_mode <> 'LCF' THEN
2258 IF (NOT gme_common_pvt.check_validity_rule_dates
2259 (l_recipe_validity_rule.recipe_validity_rule_id
2260 ,x_batch_header_rec.plan_start_date
2261 ,x_batch_header_rec.plan_cmplt_date) ) THEN
2262 x_return_status := fnd_api.g_ret_sts_error;
2263 RAISE date_exceed_validity_rule;
2264 END IF;
2265 END IF; /* IF p_creation_mode <> 'LCF' THEN */
2266
2267 IF (g_debug = gme_debug.g_log_statement) THEN
2268 gme_debug.put_line ( 'BATCH CREATED, ID = '
2269 || TO_CHAR (x_batch_header_rec.batch_id) );
2270 END IF;
2271
2272 /* Update WIP entity table to put the primary product id */
2273 UPDATE wip_entities
2274 SET primary_item_id = l_recipe_validity_rule.inventory_item_id
2275 WHERE wip_entity_id = x_batch_header_rec.batch_id;
2276
2277 -- Rajesh Patangya Bug # 6752637
2278 /* Now its time to calculate the MTQ for steps based on the product */
2279 IF gme_common_pvt.routings.COUNT <> 0 THEN
2280 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
2281 gme_debug.put_line ( g_pkg_name
2282 || '.'
2283 || l_api_name
2284 || 'Calling Update_step_mtq');
2285 END IF;
2286
2287 IF NOT update_step_mtq (x_batch_header_rec.batch_id ) THEN
2288 RAISE update_step_mtq_failure;
2289 END IF;
2290 END IF;
2291
2292 IF g_debug <= gme_debug.g_log_statement THEN
2293 gme_debug.put_line ( g_pkg_name
2294 || '.'
2295 || l_api_name
2296 || 'Start phantom processing');
2297 END IF;
2298
2299 /* Now its time to create the phantom batch based on the setting of the phantom type - Automatic */
2300 l_error_count := fnd_msg_pub.count_msg;
2301
2302 FOR l_row_count IN 1 .. l_number_of_formula_lines LOOP
2303 /* If it is set as an automatic phantom */
2304 IF l_material_details (l_row_count).phantom_type = 1
2305 AND l_material_details (l_row_count).line_type = -1
2306 AND l_doc_numbering <> 1 THEN
2307 l_material_details_in := l_material_details (l_row_count);
2308
2309 -- nsinghi bug#5200395. Changed out var for x_exception_material_tbl to l_phantom_exc_material_tbl_out
2310 -- from x_exception_material_tbl
2311 /* Bug 5512352 If creating phantom from here no need to do shortage check since
2312 now get_batch_shortages will do it for phantom batches also */
2313 g_no_phant_short_check := 1;
2314 gme_phantom_pvt.create_phantom
2315 (p_material_detail_rec => l_material_details_in
2316 ,p_batch_header_rec => l_gme_batch_header --would be nul in this case
2317 ,x_material_detail_rec => l_material_details
2318 (l_row_count)
2319 ,p_validity_rule_id => NULL
2320 ,p_use_workday_cal => p_use_workday_cal
2321 ,p_contiguity_override => p_contiguity_override
2322 ,p_use_least_cost_validity_rule => p_use_least_cost_validity_rule
2323 ,x_exception_material_tbl => l_phantom_exc_material_tbl_out
2324 ,x_return_status => l_return_status);
2325 l_error_count_after := fnd_msg_pub.count_msg;
2326 g_no_phant_short_check := 0;
2327 -- nsinghi bug#5200395. This will add the exceptions generated by each phantom ingredient to l_phantom_exc_material_tbl.
2328 /* Bug 5512352 No need to add structure since phantoms are now adone along with main batch */
2329 /*
2330 IF (l_phantom_exc_material_tbl_out.COUNT) > 0 THEN
2331 l_excp_tbl_ptr := NVL(l_phantom_exc_material_tbl.LAST,0);
2332 FOR l_phntm_exc_cnt IN l_phantom_exc_material_tbl_out.FIRST..l_phantom_exc_material_tbl_out.LAST
2333 LOOP
2334 l_phantom_exc_material_tbl(l_excp_tbl_ptr + l_phntm_exc_cnt) := l_phantom_exc_material_tbl_out(l_phntm_exc_cnt);
2335 END LOOP;
2336 END IF;
2337 */
2338
2339 IF l_error_count < l_error_count_after THEN
2340 WHILE l_error_count_after > l_error_count LOOP
2341 fnd_msg_pub.delete_msg (p_msg_index => l_error_count_after);
2342 l_error_count_after := l_error_count_after - 1;
2343 END LOOP;
2344 END IF; /* l_error_count < l_error_count_after */
2345 END IF; /* l_material_details(l_row_count).phantom_type = 1 */
2346 END LOOP; /* l_row_count IN 1 .. l_number_of_formula_lines */
2347
2348
2349 -- Bug 7265006 - Moved g_no_phant_short_check to shortage check condition as it was originally intended
2350 -- for shortage checking only. Unfortunately it stopped all the other logic for phantom batches.
2351 -- Now invisible move orders and high level reservations will get created for phantom batches.
2352 IF x_batch_header_rec.batch_type = 0 AND NVL (x_batch_header_rec.update_inventory_ind, 'Y') = 'Y' THEN
2353 /* Check inventory shortages */
2354 IF (gme_common_pvt.g_check_shortages_ind = 1 AND g_no_phant_short_check = 0) THEN
2355 IF g_debug <= gme_debug.g_log_statement THEN
2356 gme_debug.put_line ( g_pkg_name || '.'|| l_api_name|| ' Calling shortages ');
2357 END IF;
2358 gme_common_pvt.get_batch_shortages
2359 (p_organization_id => x_batch_header_rec.organization_id
2360 ,p_batch_id => x_batch_header_rec.batch_id
2361 ,p_invoke_mode => 'O'
2362 ,p_tree_mode => gme_common_pvt.g_tree_reservation_mode
2363 ,x_return_status => l_return_status
2364 ,x_exception_tbl => x_exception_material_tbl);
2365 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2366 RAISE inventory_shortage;
2367 END IF;
2368 -- nsinghi Add the material exceptions from phantom batch to Parent Batch
2369 /*
2370 IF (l_phantom_exc_material_tbl.COUNT) > 0 THEN
2371 l_excp_tbl_ptr := NVL(x_exception_material_tbl.LAST,0);
2372 FOR l_phntm_exc_cnt IN l_phantom_exc_material_tbl.FIRST..l_phantom_exc_material_tbl.LAST
2373 LOOP
2374 x_exception_material_tbl(l_excp_tbl_ptr + l_phntm_exc_cnt) := l_phantom_exc_material_tbl(l_phntm_exc_cnt);
2375 END LOOP;
2376 END IF;
2377 */
2378 IF (x_exception_material_tbl.COUNT > 0) THEN
2379 x_return_status := gme_common_pvt.g_inv_short_err;
2380 END IF;
2381 END IF;
2382
2383 /* Create invisible move order */
2384 IF g_debug <= gme_debug.g_log_statement THEN
2385 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ' Creating Invisible Move Order ');
2386 END IF;
2387
2388 gme_move_orders_pvt.create_move_order_hdr
2389 (p_organization_id => x_batch_header_rec.organization_id
2390 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
2391 ,x_move_order_header_id => x_batch_header_rec.move_order_header_id
2392 ,x_return_status => l_return_status);
2393
2394 IF g_debug <= gme_debug.g_log_statement THEN
2395 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| 'Invisible Move Order is Created');
2396 END IF;
2397
2398 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2399 RAISE create_mo_hdr_err;
2400 ELSE
2401 IF g_debug <= gme_debug.g_log_statement THEN
2402 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ' Creating Invisible Move Order Lines');
2403 END IF;
2404
2405 l_in_material_details := l_material_details;
2406 gme_move_orders_pvt.create_move_order_lines
2407 (p_move_order_header_id => x_batch_header_rec.move_order_header_id
2408 ,p_move_order_type => gme_common_pvt.g_invis_move_order_type
2409 ,p_material_details_tbl => l_in_material_details
2410 ,x_material_details_tbl => l_material_details
2411 ,x_trolin_tbl => l_trolin_tbl
2412 ,x_return_status => l_return_status);
2413
2414 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2415 RAISE create_mo_line_err;
2416 END IF;
2417
2418 IF g_debug <= gme_debug.g_log_statement THEN
2419 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| 'Invisible Move Order Lines is Created');
2420 END IF;
2421 END IF;
2422
2423 /* Update batch header with move_order_header_id */
2424 UPDATE gme_batch_header
2425 SET move_order_header_id = x_batch_header_rec.move_order_header_id
2426 WHERE batch_id = x_batch_header_rec.batch_id;
2427
2428 /* Update material details table we have computed material requirement date and move_order_line_id */
2429 FOR i IN 1 .. l_material_details.COUNT LOOP
2430 l_return :=
2431 gme_material_details_dbl.update_row (l_material_details (i) );
2432 END LOOP;
2433
2434 /* Create High Level Reservations */
2435 IF (gme_common_pvt.g_create_high_level_resv_ind = 1) THEN
2436 IF g_debug <= gme_debug.g_log_statement THEN
2437 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ' Creating reservations for batch id '|| x_batch_header_rec.batch_id);
2438 END IF;
2439
2440 gme_reservations_pvt.create_batch_reservations
2441 (p_batch_id => x_batch_header_rec.batch_id
2442 ,p_timefence => gme_common_pvt.g_reservation_timefence
2443 ,x_return_status => l_return_status);
2444
2445 IF g_debug <= gme_debug.g_log_statement THEN
2446 gme_debug.put_line ( g_pkg_name
2447 || '.'
2448 || l_api_name
2449 || ' Reservations are created'
2450 || l_return_status);
2451 END IF;
2452 END IF;
2453
2454 /* Create Move Order */
2455 IF (gme_common_pvt.g_create_move_orders_ind = 1) THEN
2456 IF g_debug <= gme_debug.g_log_statement THEN
2457 gme_debug.put_line ( g_pkg_name
2458 || '.'
2459 || l_api_name
2460 || ' Creating Explicit Move Order ');
2461 END IF;
2462
2463 gme_move_orders_pvt.create_batch_move_order
2464 (p_batch_header_rec => x_batch_header_rec
2465 ,p_material_details_tbl => l_material_details
2466 ,x_return_status => l_return_status);
2467
2468 IF g_debug <= gme_debug.g_log_statement THEN
2469 gme_debug.put_line ( g_pkg_name
2470 || '.'
2471 || l_api_name
2472 || ' Explicit Move Order are created '
2473 || l_return_status);
2474 END IF;
2475 END IF;
2476 END IF;
2477 -- Pawan added code for populating the GMO
2478 IF x_batch_header_rec.enhanced_pi_ind = 'Y' THEN
2479 gmo_vbatch_grp.instantiate_advanced_pi
2480 (P_API_VERSION => 1.0,
2481 P_INIT_MSG_LIST => FND_API.G_FALSE,
2482 P_COMMIT => FND_API.G_FALSE,
2483 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2484 X_RETURN_STATUS => l_return_status,
2485 X_MSG_COUNT => l_message_count,
2486 X_MSG_DATA => l_msg_data,
2487 P_ENTITY_NAME => 'BATCH',
2488 P_ENTITY_KEY => x_batch_header_rec.batch_id) ;
2489 IF g_debug <= gme_debug.g_log_statement THEN
2490 gme_debug.put_line ( g_pkg_name
2491 || '.'
2492 || l_api_name
2493 || ' Calling instantiate_advanced_pi ');
2494 END IF;
2495 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2496 RAISE gmo_instantiate_err;
2497 END IF;
2498 END IF ;
2499 /*Bug#7493614 moved the workflow event call to the end of the batch
2500 create procedure */
2501 wf_event.RAISE (p_event_name => gme_common_pvt.G_BATCH_CREATED
2502 ,p_event_key => x_batch_header_rec.batch_id);
2503 IF g_debug <= gme_debug.g_log_procedure THEN
2504 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2505 END IF;
2506 EXCEPTION
2507 WHEN doc_num_is_not_passed THEN
2508 x_return_status := fnd_api.g_ret_sts_error;
2509 gme_common_pvt.log_message ('GME_DOC_NUM_NOT_PASSED');
2510 WHEN gme_duplicate_batch THEN
2511 x_return_status := fnd_api.g_ret_sts_error;
2512 --FPBug#4898641
2513 IF x_batch_header_rec.batch_type = 0 THEN
2514 gme_common_pvt.log_message ('GME_DUP_BATCH');
2515 ELSE
2516 gme_common_pvt.log_message ('GME_DUP_FPO');
2517 END IF;
2518 WHEN steps_creation_failure THEN
2519 NULL;
2520 WHEN error_create_phantom OR invalid_header_values OR invalid_recipe THEN
2521 x_return_status := l_return_status;
2522 --FPBug#4351032
2523 WHEN validation_failure OR expected_error OR scaling_failure OR date_exceed_validity_rule OR batch_header_fetch_failure OR error_count_exceeded OR expected_error OR unexpected_error OR invalid_item_substitute OR material_dtl_fetch_failure THEN
2524 x_return_status := fnd_api.g_ret_sts_error;
2525 WHEN error_cont_period THEN
2526 IF (g_debug = gme_debug.g_log_statement) THEN
2527 gme_debug.put_line ('Contiguity period ... _failed');
2528 END IF;
2529
2530 x_return_status := l_return_status;
2531 WHEN error_non_contiguious THEN
2532 IF (g_debug = gme_debug.g_log_statement) THEN
2533 gme_debug.put_line ('Contiguity period ... not found');
2534 END IF;
2535
2536 gme_common_pvt.log_message ('GME_NON_CONTIGUOUS_TIME');
2537 x_return_status := 'C';
2538 WHEN conversion_failure THEN
2539 IF l_item_no IS NULL THEN
2540 OPEN cur_item_no (l_item_id, x_batch_header_rec.organization_id);
2541
2542 FETCH cur_item_no
2543 INTO l_item_no;
2544
2545 CLOSE cur_item_no;
2546 END IF;
2547
2548 x_return_status := fnd_api.g_ret_sts_error;
2549 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
2550 fnd_message.set_token ('ITEM_NO', l_item_no);
2551 fnd_message.set_token ('FROM_UOM', l_from_uom);
2552 fnd_message.set_token ('TO_UOM', l_to_uom);
2553 fnd_msg_pub.ADD;
2554 WHEN Fixed_process_loss_failure THEN
2555 IF (g_debug = gme_debug.g_log_statement) THEN
2556 gme_debug.put_line ('Failed to apply FPL');
2557 END IF;
2558 WHEN create_by_product_failure THEN
2559 gme_common_pvt.log_message ('GME_API_CREATE_BY_PROD_FAIL');
2560 x_return_status := fnd_api.g_ret_sts_error;
2561
2562 IF (g_debug = gme_debug.g_log_statement) THEN
2563 gme_debug.put_line ('GME_API_CREATE_BY_PROD_FAIL');
2564 END IF;
2565 WHEN inventory_shortage OR create_mo_hdr_err OR create_mo_line_err OR
2566 calc_mtl_req_date_err OR gmo_instantiate_err OR wip_entity_err THEN
2567 x_return_status := l_return_status;
2568 WHEN error_truncate_date THEN
2569 IF (g_debug = gme_debug.g_log_statement) THEN
2570 gme_debug.put_line ('truncate date procedure error');
2571 END IF;
2572
2573 x_return_status := fnd_api.g_ret_sts_error;
2574 --FPBug#4395561
2575 WHEN create_flex_failure THEN
2576 x_return_status := l_return_status;
2577 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
2578 gme_debug.put_line ('Creating the default values of the DFF failure');
2579 END IF;
2580 -- Rajesh Patangya Bug # 6752637
2581 WHEN update_step_mtq_failure THEN
2582 x_return_status := fnd_api.g_ret_sts_error;
2583 IF (g_debug = gme_debug.g_log_statement) THEN
2584 gme_debug.put_line ('MTQ Calculation failure');
2585 END IF;
2586 WHEN OTHERS THEN
2587 IF g_debug <= gme_debug.g_log_unexpected THEN
2588 gme_debug.put_line ( 'When others exception in '
2589 || g_pkg_name
2590 || '.'
2591 || l_api_name
2592 || ' Error is '
2593 || SQLERRM);
2594 END IF;
2595
2596 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2597
2598 IF (g_debug = gme_debug.g_log_statement) THEN
2599 gme_debug.put_line (SQLERRM);
2600 END IF;
2601 x_return_status := fnd_api.g_ret_sts_unexp_error;
2602 END create_batch;
2603
2604 /************************************************************
2605 * 10-JAN-2008 Rajesh Patangya Bug # 6752637 *
2606 * UPDATE_STEP_MTQ *
2607 * MTQ Quantity should be calculated based on product *
2608 *************************************************************/
2609
2610 FUNCTION update_step_mtq (p_batch_id IN NUMBER)
2611 RETURN BOOLEAN IS
2612
2613 l_batch_steps gme_batch_steps%ROWTYPE;
2614 l_calculated_mtq gme_batch_steps.minimum_transfer_qty%TYPE;
2615 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_STEP_MTQ';
2616
2617 CURSOR step_mtq_cursor (x_batch_id gme_batch_header.batch_id%TYPE) IS
2618 SELECT *
2619 FROM gme_batch_steps
2620 WHERE batch_id = x_batch_id ;
2621
2622 CURSOR calculate_mtq_cursor (x_batch_id gme_batch_header.batch_id%TYPE,
2623 x_routingstep_id fm_rout_dtl.routingstep_id%TYPE) IS
2624
2625
2626 SELECT
2627 (frh.routing_qty * NVL(frd.minimum_transfer_qty,0)
2628 * DECODE(fmd.detail_uom, iim.primary_uom_code, fmd.qty,
2629 inv_convert.inv_um_convert
2630 (fmd.inventory_item_id,
2631 5,
2632 fmd.qty,
2633 fmd.detail_uom,
2634 iim.primary_uom_code,
2635 NULL,NULL)
2636 )
2637 )
2638 / (ffm.total_output_qty * frd.step_qty)
2639 FROM fm_form_mst ffm,
2640 fm_matl_dtl fmd,
2641 fm_rout_hdr frh,
2642 fm_rout_dtl frd,
2643 mtl_system_items iim,
2644 gme_batch_header gbh,
2645 gmd_recipe_validity_rules ffe
2646 WHERE gbh.batch_id = x_batch_id
2647 AND ffm.formula_id = gbh.formula_id
2648 AND ffm.formula_id = fmd.formula_id
2649 AND iim.organization_id = NVL(ffe.organization_id,iim.organization_id)
2650 AND fmd.inventory_item_id = ffe.inventory_item_id
2651 AND iim.inventory_item_id = fmd.inventory_item_id
2652 AND iim.organization_id = fmd.organization_id
2653 AND frh.routing_id = gbh.routing_id
2654 AND frh.routing_id = frd.routing_id
2655 AND frd.routingstep_id = l_batch_steps.routingstep_id
2656 AND ffe.recipe_validity_rule_id = gbh.recipe_validity_rule_id ;
2657
2658 BEGIN
2659 OPEN step_mtq_cursor (p_batch_id);
2660 LOOP
2661 FETCH step_mtq_cursor INTO l_batch_steps ;
2662 EXIT WHEN step_mtq_cursor%NOTFOUND;
2663
2664 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
2665 gme_debug.put_line (' MTQ = ' || NVL(l_batch_steps.minimum_transfer_qty,0)
2666 || ' Step_qty = ' || NVL(l_batch_steps.plan_step_qty,0)
2667 || ' Routingstep_id = ' || l_batch_steps.routingstep_id );
2668 END IF;
2669
2670 IF NVL(l_batch_steps.minimum_transfer_qty,0) > 0 AND
2671 NVL(l_batch_steps.plan_step_qty,0) > 0 THEN
2672
2673 OPEN calculate_mtq_cursor (p_batch_id, l_batch_steps.routingstep_id);
2674 FETCH calculate_mtq_cursor INTO l_calculated_mtq ;
2675 CLOSE calculate_mtq_cursor ;
2676
2677 UPDATE gme_batch_steps
2678 SET minimum_transfer_qty = l_calculated_mtq
2679 WHERE batch_id = l_batch_steps.batch_id
2680 AND routingstep_id = l_batch_steps.routingstep_id
2681 AND batchstep_id = l_batch_steps.batchstep_id ;
2682
2683 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
2684 gme_debug.put_line (' Update Calculated MTQ = ' || l_calculated_mtq );
2685 END IF;
2686
2687 END IF;
2688 END LOOP ;
2689 CLOSE step_mtq_cursor;
2690
2691 RETURN TRUE ;
2692
2693 EXCEPTION
2694 WHEN no_data_found THEN
2695 RETURN TRUE ;
2696 WHEN OTHERS THEN
2697 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2698 IF (NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT) THEN
2699 gme_debug.put_line (SQLERRM);
2700 END IF;
2701 RETURN FALSE ;
2702 END update_step_mtq ;
2703
2704 END gme_create_batch_pvt;
[Home] [Help]