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