[Home] [Help]
PACKAGE BODY: APPS.GME_CONVERT_FPO_PVT
Source
1 PACKAGE BODY gme_convert_fpo_pvt AS
2 /* $Header: GMEVCFPB.pls 120.7 2006/02/14 07:05:18 lgao noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_API_CONVERT_FPO';
5
6 --*************************************************************
7 --*************************************************************
8 PROCEDURE VALIDATION (
9 p_batch_header IN gme_batch_header%ROWTYPE
10 ,p_batch_size IN NUMBER
11 ,p_batch_size_uom IN VARCHAR2
12 ,p_num_batches IN NUMBER
13 ,p_validity_rule_id IN NUMBER
14 ,p_leadtime IN NUMBER
15 ,p_batch_offset IN NUMBER
16 ,p_offset_type IN NUMBER
17 ,p_plan_start_date IN gme_batch_header.plan_start_date%TYPE
18 ,p_plan_cmplt_date IN gme_batch_header.plan_cmplt_date%TYPE
19 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
20 ,x_return_status OUT NOCOPY VARCHAR2)
21 IS
22 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATION';
23 /* Exception definitions */
24 no_null_dates EXCEPTION;
25 no_populate_both_dates EXCEPTION;
26 invalid_batch_status EXCEPTION;
27 plan_qty_zero_err EXCEPTION;
28 invalid_item_item_um EXCEPTION;
29 no_batches EXCEPTION;
30 neg_validity_rule_id EXCEPTION;
31 leadtime_err EXCEPTION;
32 batch_offset_err EXCEPTION;
33 offset_type_err EXCEPTION;
34 BEGIN
35 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
36 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
37 || l_api_name);
38 END IF;
39
40 IF (p_plan_start_date IS NULL AND p_plan_cmplt_date IS NULL) THEN
41 RAISE no_null_dates;
42 ELSIF (p_plan_start_date IS NOT NULL AND p_plan_cmplt_date IS NOT NULL) THEN
43 RAISE no_populate_both_dates;
44 END IF;
45
46 --Shikha Nagar Bug 3607420
47 -- Allow planned dates to be in past. In form a warning is issued in such cases.
48 IF (p_plan_start_date IS NOT NULL AND p_plan_cmplt_date IS NULL) THEN
49 x_pregen_fpo_row.schedule_method := 'FORWARD';
50 ELSIF (p_plan_cmplt_date IS NOT NULL AND p_plan_start_date IS NULL) THEN
51 x_pregen_fpo_row.schedule_method := 'BACKWARD';
52 END IF;
53
54 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
55 gme_debug.put_line (g_pkg_name ||
56 '.' ||
57 l_api_name ||
58 ' schedule method is '||
59 x_pregen_fpo_row.schedule_method);
60 END IF;
61 IF (p_batch_header.batch_status <> 1) THEN
62 RAISE invalid_batch_status;
63 END IF;
64
65 -- Pawan Kumar ADDED code for bug 2398719
66 IF (p_batch_header.batch_type = 0) THEN
67 RAISE invalid_batch_status;
68 END IF;
69
70 IF (p_batch_size IS NULL OR p_batch_size <= 0) THEN
71 RAISE plan_qty_zero_err;
72 END IF;
73
74 IF (p_batch_size_uom IS NULL) THEN
75 RAISE invalid_item_item_um;
76 END IF;
77
78 IF (p_num_batches IS NULL OR p_num_batches <= 0) THEN
79 RAISE no_batches;
80 END IF;
81
82 IF (p_validity_rule_id < 0) THEN
83 RAISE neg_validity_rule_id;
84 END IF;
85
86 IF (p_leadtime < 0) THEN
87 RAISE leadtime_err;
88 END IF;
89
90 IF (p_batch_offset < 0) THEN
91 RAISE batch_offset_err;
92 END IF;
93
94 IF (p_offset_type NOT IN (0, 1) ) THEN
95 RAISE offset_type_err;
96 END IF;
97
98 --load parameters into structure.
99 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
100 gme_debug.put_line ('assigning all values to x_pregen_fpo_row');
101 END IF;
102
103 x_return_status := fnd_api.g_ret_sts_success;
104 x_pregen_fpo_row.qty_per_batch := p_batch_size;
105 x_pregen_fpo_row.batch_size_uom := p_batch_size_uom;
106 x_pregen_fpo_row.num_batches := p_num_batches;
107 x_pregen_fpo_row.validity_rule_id := p_validity_rule_id;
108
109 IF p_leadtime IS NULL THEN
110 x_pregen_fpo_row.leadtime := 0;
111 ELSE
112 x_pregen_fpo_row.leadtime := p_leadtime;
113 END IF;
114
115 IF p_batch_offset IS NULL THEN
116 x_pregen_fpo_row.batch_offset := 0;
117 ELSE
118 x_pregen_fpo_row.batch_offset := p_batch_offset;
119 END IF;
120
121 x_pregen_fpo_row.offset_type := p_offset_type;
122 x_pregen_fpo_row.plan_start_date := p_plan_start_date;
123 x_pregen_fpo_row.plan_cmplt_date := p_plan_cmplt_date;
124
125 IF (x_pregen_fpo_row.sum_eff_qty = 0) THEN
126 x_pregen_fpo_row.sum_eff_qty :=
127 x_pregen_fpo_row.num_batches * x_pregen_fpo_row.qty_per_batch;
128 END IF;
129
130 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
131 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
132 END IF;
133 EXCEPTION
134 WHEN no_null_dates THEN
135 gme_common_pvt.log_message ('GME_NO_NULL_DATES');
136 x_return_status := fnd_api.g_ret_sts_error;
137 WHEN no_populate_both_dates THEN
138 gme_common_pvt.log_message ('GME_NO_POPULATE_BOTH_DATES');
139 x_return_status := fnd_api.g_ret_sts_error;
140 WHEN invalid_batch_status THEN
141 gme_common_pvt.log_message ('GME_INV_FPO_STATUS');
142 x_return_status := fnd_api.g_ret_sts_error;
143 WHEN plan_qty_zero_err THEN
144 gme_common_pvt.log_message ('PM_GT_ZERO_ERR');
145 x_return_status := fnd_api.g_ret_sts_error;
146 WHEN invalid_item_item_um THEN
147 gme_common_pvt.log_message ('GME_INVALID_ITEM_OR_ITEM_UM');
148 x_return_status := fnd_api.g_ret_sts_error;
149 WHEN no_batches THEN
150 gme_common_pvt.log_message ('PM_INV_NO_BATCHES');
151 x_return_status := fnd_api.g_ret_sts_error;
152 WHEN neg_validity_rule_id THEN
153 gme_common_pvt.log_message ('GME_NO_NEG_VALIDITY_RULE_ID');
154 x_return_status := fnd_api.g_ret_sts_error;
155 WHEN leadtime_err THEN
156 gme_common_pvt.log_message ('PM_LESSTHAN_ZERO_ERR');
157 x_return_status := fnd_api.g_ret_sts_error;
158 WHEN batch_offset_err THEN
159 gme_common_pvt.log_message ('PM_LESSTHAN_ZERO_ERR');
160 x_return_status := fnd_api.g_ret_sts_error;
161 WHEN offset_type_err THEN
162 gme_common_pvt.log_message ('PM_OFFSET_TYPE_ERR');
163 x_return_status := fnd_api.g_ret_sts_error;
164 WHEN OTHERS THEN
165 x_return_status := fnd_api.g_ret_sts_unexp_error;
166 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO', 'validation');
167 END VALIDATION;
168
169 --*************************************************************
170
171 --*************************************************************
172 PROCEDURE retrieve_fpo_data (
173 p_fpo_header_row IN gme_batch_header%ROWTYPE
174 ,x_fpo_header_row OUT NOCOPY gme_batch_header%ROWTYPE
175 ,p_pregen_fpo_row IN pregen_fpo_row
176 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
177 ,x_prim_prod_row OUT NOCOPY gme_material_details%ROWTYPE
178 ,x_validity_rule_row OUT NOCOPY validity_rule_row
179 ,x_fpo_material_details_tab OUT NOCOPY fpo_material_details_tab
180 ,x_return_status OUT NOCOPY VARCHAR2)
181 IS
182 l_api_name CONSTANT VARCHAR2 (30) := 'RETRIEVE_FPO_DATA';
183 l_cur_dtl PLS_INTEGER;
184 l_pregen_fpo_row pregen_fpo_row;
185 l_fpo_material_details_tab fpo_material_details_tab;
186 l_return BOOLEAN;
187 --Begin Bug 2924803 Anil
188 l_prod_row gme_material_details%ROWTYPE;
189 l_plan_qty gme_material_details.plan_qty%TYPE;
190
191 --End Bug 2924803 Anil
192 CURSOR get_fpo_material_details (v_batch_id NUMBER)
193 IS
194 SELECT *
195 FROM gme_material_details
196 WHERE batch_id = v_batch_id;
197
198 l_fpo_material_details_row get_fpo_material_details%ROWTYPE;
199
200 CURSOR get_prim_prod_row (v_batch_id NUMBER)
201 IS
202 SELECT *
203 FROM gme_material_details
204 WHERE batch_id = v_batch_id
205 AND line_type = 1
206 AND inventory_item_id =
207 (SELECT inventory_item_id
208 FROM gmd_recipe_validity_rules
209 WHERE recipe_validity_rule_id =
210 (SELECT recipe_validity_rule_id
211 FROM gme_batch_header
212 WHERE batch_id = v_batch_id) );
213
214 /* GME CONV
215 CURSOR get_production_rules (v_plant_code VARCHAR2, v_item_id NUMBER) IS
216 SELECT *
217 FROM ic_plnt_inv
218 WHERE orgn_code = v_plant_code AND
219 item_id = v_item_id AND
220 delete_mark = 0;
221 GME CONV Added following 2 cursors */
222 --Susruth D. Bug#4917636 Start
223 CURSOR item_master_cursor (v_inventory_item_id NUMBER, v_org_id NUMBER)
224 IS
225 SELECT concatenated_segments,description
226 FROM mtl_system_items_kfv
227 WHERE inventory_item_id = v_inventory_item_id
228 AND organization_id = v_org_id;
229 /*SELECT *
230 FROM mtl_system_items_kfv
231 WHERE inventory_item_id = v_inventory_item_id
232 AND organization_id = v_org_id;*/
233 --Susruth D. Bug#4917636 End.
234 l_item_row item_master_cursor%ROWTYPE;
235
236 CURSOR get_production_rules (v_inventory_item_id NUMBER, v_org_id NUMBER)
237 IS
238 SELECT std_lot_size, primary_uom_code, fixed_lead_time
239 ,variable_lead_time
240 FROM mtl_system_items_b
241 WHERE inventory_item_id = v_inventory_item_id
242 AND organization_id = v_org_id;
243
244 l_get_production_rules get_production_rules%ROWTYPE;
245 /* Exception definitions */
246 no_fetch_material_details EXCEPTION;
247 batch_fetch_error EXCEPTION;
248 BEGIN
249 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
250 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
251 || l_api_name);
252 END IF;
253 x_return_status := fnd_api.g_ret_sts_success;
254 l_pregen_fpo_row := p_pregen_fpo_row;
255 --Retrieve fpo_header into local fpo_header_rec structure.
256 l_return :=
257 gme_batch_header_dbl.fetch_row (p_fpo_header_row, x_fpo_header_row);
258
259 -- exception added
260 IF NOT l_return THEN
261 RAISE batch_fetch_error;
262 END IF;
263 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
264 gme_debug.put_line (g_pkg_name ||
265 '.' ||
266 l_api_name ||
267 ' batch header fetch_row returns success ');
268 END IF;
269
270 --Retrieve fpo_material_details into local fpo_material_details structure.
271 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
272 gme_debug.put_line (g_pkg_name ||
273 '.' ||
274 l_api_name ||
275 ' Retrieve material details for batch_id '||
276 x_fpo_header_row.batch_id);
277 END IF;
278 l_cur_dtl := 0;
279
280 OPEN get_fpo_material_details (x_fpo_header_row.batch_id);
281
282 FETCH get_fpo_material_details
283 INTO l_fpo_material_details_row;
284
285 IF get_fpo_material_details%FOUND THEN
286 WHILE get_fpo_material_details%FOUND LOOP
287 l_cur_dtl := l_cur_dtl + 1;
288 l_fpo_material_details_tab (l_cur_dtl) :=
289 l_fpo_material_details_row;
290 x_fpo_material_details_tab (l_cur_dtl) :=
291 l_fpo_material_details_tab (l_cur_dtl);
292
293 FETCH get_fpo_material_details
294 INTO l_fpo_material_details_row;
295 END LOOP;
296 ELSE
297 CLOSE get_fpo_material_details;
298
299 RAISE no_fetch_material_details;
300 END IF;
301
302 CLOSE get_fpo_material_details;
303
304 -- Call get_effective_prim_prod api for line_id of the fpo's prim_prod
305 --Bogus primary_product line for now......get UK api
306 OPEN get_prim_prod_row (p_fpo_header_row.batch_id);
307
308 FETCH get_prim_prod_row
309 INTO x_prim_prod_row;
310
311 --Begin Bug 2924803 Anil
312 LOOP
313 FETCH get_prim_prod_row
314 INTO l_prod_row;
315
316 EXIT WHEN get_prim_prod_row%NOTFOUND;
317
318 IF x_prim_prod_row.dtl_um = l_prod_row.dtl_um THEN
319 l_plan_qty := l_prod_row.plan_qty;
320 ELSE
321 gmicuom.icuomcv (x_prim_prod_row.inventory_item_id
322 ,0
323 ,l_prod_row.plan_qty
324 ,l_prod_row.dtl_um
325 ,x_prim_prod_row.dtl_um
326 ,l_plan_qty);
327 END IF;
328
329 x_prim_prod_row.plan_qty := x_prim_prod_row.plan_qty + l_plan_qty;
330 END LOOP;
331
332 --End Bug 2924803 Anil
333 CLOSE get_prim_prod_row;
334
335 --Get prim prod item info
336 OPEN item_master_cursor (x_prim_prod_row.inventory_item_id
337 ,p_fpo_header_row.organization_id);
338
339 FETCH item_master_cursor
340 INTO l_item_row;
341
342 CLOSE item_master_cursor;
343
344 /* GME CONV Added above lines
345 SELECT *
346 INTO l_item_row
347 FROM ic_item_mst
348 WHERE ic_item_mst.item_id = x_prim_prod_row.inventory_item_id;
349 */ --Assign primary product details to validity_rule structure
350 x_validity_rule_row.organization_id := p_fpo_header_row.organization_id;
351 x_validity_rule_row.prim_prod_item_id :=
352 x_prim_prod_row.inventory_item_id;
353 x_validity_rule_row.prim_prod_item_no :=
354 l_item_row.concatenated_segments;
355 x_validity_rule_row.prim_prod_item_desc1 := l_item_row.description;
356 x_validity_rule_row.prim_prod_item_um := x_prim_prod_row.dtl_um;
357 x_validity_rule_row.prim_prod_effective_qty := x_prim_prod_row.plan_qty;
358
359 --GET PRODUCTION RULES
360 OPEN get_production_rules (x_fpo_header_row.organization_id
361 ,x_prim_prod_row.inventory_item_id);
362
363 FETCH get_production_rules
364 INTO l_get_production_rules;
365
366 IF get_production_rules%FOUND THEN
367 l_pregen_fpo_row.std_qty := l_get_production_rules.std_lot_size;
368 l_pregen_fpo_row.fixed_leadtime :=
369 l_get_production_rules.fixed_lead_time;
370 l_pregen_fpo_row.variable_leadtime :=
371 l_get_production_rules.variable_lead_time;
372 l_pregen_fpo_row.rules_found := 1;
373 ELSE
374 l_pregen_fpo_row.rules_found := 0;
375 END IF;
376
377 l_pregen_fpo_row.batch_size_uom := x_prim_prod_row.dtl_um;
378
379 CLOSE get_production_rules;
380
381 x_pregen_fpo_row := l_pregen_fpo_row;
382 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
383 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
384 END IF;
385 EXCEPTION
386 WHEN no_fetch_material_details THEN
387 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
388 gme_debug.put_line ('no fetch material_details');
389 END IF;
390
391 gme_common_pvt.log_message ('PM_NO_MATL_DTL');
392 x_return_status := fnd_api.g_ret_sts_error;
393 WHEN batch_fetch_error THEN
394 x_return_status := fnd_api.g_ret_sts_error;
395 WHEN OTHERS THEN
396 x_return_status := fnd_api.g_ret_sts_unexp_error;
397 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO', 'retrieve_fpo_data');
398 END retrieve_fpo_data;
399
400 --*************************************************************
401
402 --*************************************************************
403 PROCEDURE calculate_leadtime (
404 p_pregen_fpo_row IN pregen_fpo_row
405 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
406 ,x_return_status OUT NOCOPY VARCHAR2)
407 IS
408 l_api_name CONSTANT VARCHAR2 (30) := 'CALCULATE_LEADTIME';
409 BEGIN
410 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
411 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
412 || l_api_name);
413 END IF;
414
415 x_pregen_fpo_row := p_pregen_fpo_row;
416 x_return_status := fnd_api.g_ret_sts_success;
417
418 IF ( p_pregen_fpo_row.variable_leadtime = 0
419 OR p_pregen_fpo_row.std_qty = 0) THEN
420 x_pregen_fpo_row.leadtime := p_pregen_fpo_row.fixed_leadtime;
421 ELSE
422 x_pregen_fpo_row.leadtime :=
423 p_pregen_fpo_row.fixed_leadtime
424 + ( p_pregen_fpo_row.variable_leadtime
425 * p_pregen_fpo_row.qty_per_batch
426 / p_pregen_fpo_row.std_qty);
427 END IF;
428 --Bug2804440
429 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
430 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
431 END IF;
432 EXCEPTION
433 WHEN OTHERS THEN
434 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
435 x_return_status := fnd_api.g_ret_sts_unexp_error;
436 --End Bug2804440
437 END calculate_leadtime;
438
439 --*************************************************************
440 --*****************************************************************
441 PROCEDURE generate_pre_batch_header_recs (
442 p_fpo_header_row IN gme_batch_header%ROWTYPE
443 ,p_prim_prod_row IN gme_material_details%ROWTYPE
444 ,p_pregen_fpo_row IN pregen_fpo_row
445 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
446 ,x_return_status OUT NOCOPY VARCHAR2)
447 IS
448 l_api_name CONSTANT VARCHAR2 (30) := 'GENERATE_PRE_BATCH_HEADER_RECS';
449 x_prev_plan_start_date gme_batch_header.plan_start_date%TYPE;
450 x_prev_plan_cmplt_date gme_batch_header.plan_cmplt_date%TYPE;
451 x_batch_leadtime_days NUMBER;
452 x_batch_offset_days NUMBER;
453 x_neg_batch_leadtime_days NUMBER;
454 x_neg_batch_offset_days NUMBER;
455 l_fpo_header_row gme_batch_header%ROWTYPE;
456 l_prim_prod_row gme_material_details%ROWTYPE;
457 l_pregen_fpo_row pregen_fpo_row;
458 l_generated_pre_batch_tab generated_pre_batch_tab;
459 /* Exception definitions */
460 generate_pre_batch_err EXCEPTION;
461 BEGIN
462 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
463 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
464 || l_api_name);
465 END IF;
466
467 l_fpo_header_row := p_fpo_header_row;
468 l_prim_prod_row := p_prim_prod_row;
469 l_pregen_fpo_row := p_pregen_fpo_row;
470
471 --Convert variables expressed in hours to days.
472 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
473 gme_debug.put_line ( 'l_pregen_fpo_row.leadtime = '
474 || TO_CHAR (l_pregen_fpo_row.leadtime) );
475 END IF;
476
477 x_batch_leadtime_days := l_pregen_fpo_row.leadtime / 24;
478
479 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
480 gme_debug.put_line ( 'x_batch_leadtime_days = '
481 || TO_CHAR (x_batch_leadtime_days) );
482 gme_debug.put_line ( 'l_pregen_fpo_row.batch_offset = '
483 || TO_CHAR (l_pregen_fpo_row.batch_offset) );
484 END IF;
485
486 x_batch_offset_days := l_pregen_fpo_row.batch_offset / 24;
487
488 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
489 gme_debug.put_line ( 'x_batch_offset_days = '
490 || TO_CHAR (x_batch_offset_days) );
491 END IF;
492
493 --Negative values of above.
494 x_neg_batch_leadtime_days := x_batch_leadtime_days * -1;
495
496 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
497 gme_debug.put_line ( 'x_neg_batch_leadtime_days = '
498 || TO_CHAR (x_neg_batch_leadtime_days) );
499 END IF;
500
501 x_neg_batch_offset_days := x_batch_offset_days * -1;
502
503 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
504 gme_debug.put_line ( 'x_neg_batch_offset_days = '
505 || TO_CHAR (x_neg_batch_offset_days) );
506 gme_debug.put_line ( 'l_pregen_fpo_row.schedule_method = '
507 || (l_pregen_fpo_row.schedule_method) );
508 END IF;
509 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
510 gme_debug.put_line (g_pkg_name ||
511 '.' ||
512 l_api_name ||
513 ' Begin looping here for count of '||
514 l_pregen_fpo_row.num_batches);
515 END IF;
516
517 FOR i IN 1 .. l_pregen_fpo_row.num_batches LOOP
518 -- KYH GME CONV BEGIN
519 -- l_generated_pre_batch_tab (i).plant_code :=
520 -- l_fpo_header_row.plant_code;
521 l_generated_pre_batch_tab (i).organization_id :=
522 l_fpo_header_row.organization_id;
523 -- KYH GME CONV END
524 l_generated_pre_batch_tab (i).batch_type := 0;
525 l_generated_pre_batch_tab (i).fpo_id := l_fpo_header_row.batch_id;
526
527 -- Bug 3258483 Mohit Kapoor
528 /* Added the assignment of wip_whse_code so that correct
529 wip_whse_code is saved to batch */
530 -- l_generated_pre_batch_tab (i).wip_whse_code := l_fpo_header_row.wip_whse_code;
531 IF (i = 1) THEN
532 --first batch
533 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
534 --First batch/Scheduling FORWARD
535 l_generated_pre_batch_tab (i).plan_start_date :=
536 l_pregen_fpo_row.plan_start_date;
537 l_generated_pre_batch_tab (i).plan_cmplt_date :=
538 l_pregen_fpo_row.plan_start_date + x_batch_leadtime_days;
539 ELSE
540 --First Batch/Scheduling BACKWARD
541 l_generated_pre_batch_tab (i).plan_cmplt_date :=
542 l_pregen_fpo_row.plan_cmplt_date;
543 l_generated_pre_batch_tab (i).plan_start_date :=
544 l_pregen_fpo_row.plan_cmplt_date
545 + x_neg_batch_leadtime_days;
546 END IF;
547
548 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
549 gme_debug.put_line
550 ( 'l_generated_pre_batch_tab(i).plan_start_date = '
551 || TO_CHAR
552 (l_generated_pre_batch_tab (i).plan_start_date
553 ,'MM/DD/YYYY hh24:mi:ss') );
554 gme_debug.put_line
555 ( 'l_generated_pre_batch_tab(i).plan_cmplt_date = '
556 || TO_CHAR
557 (l_generated_pre_batch_tab (i).plan_cmplt_date
558 ,'MM/DD/YYYY hh24:mi:ss') );
559 END IF;
560 ELSE
561 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
562 --Not First Batch; Scheduling FORWARD
563 IF (l_pregen_fpo_row.offset_type = 0) THEN
564 --Start-to-Start
565 x_prev_plan_start_date :=
566 l_generated_pre_batch_tab (i - 1).plan_start_date;
567
568 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
569 gme_debug.put_line ( 'x_prev_plan_start_date = '
570 || TO_CHAR (x_prev_plan_start_date
571 ,'MM/DD/YYYY hh24:mi:ss') );
572 END IF;
573
574 l_generated_pre_batch_tab (i).plan_start_date :=
575 x_prev_plan_start_date;
576
577 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
578 gme_debug.put_line
579 ( 'l_generated_pre_batch_tab(i).plan_start_date = '
580 || TO_CHAR
581 (l_generated_pre_batch_tab (i).plan_start_date
582 ,'MM/DD/YYYY hh24:mi:ss') );
583 END IF;
584 ELSE
585 --End-to-Start
586 x_prev_plan_cmplt_date :=
587 l_generated_pre_batch_tab (i - 1).plan_cmplt_date;
588 l_generated_pre_batch_tab (i).plan_start_date :=
589 x_prev_plan_cmplt_date;
590 END IF;
591
592 l_generated_pre_batch_tab (i).plan_start_date :=
593 l_generated_pre_batch_tab (i).plan_start_date
594 + x_batch_offset_days;
595
596 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
597 gme_debug.put_line
598 ( 'W/OFFSET l_generated_pre_batch_tab(i).plan_start_date = '
599 || TO_CHAR
600 (l_generated_pre_batch_tab (i).plan_start_date
601 ,'MM/DD/YYYY hh24:mi:ss ') );
602 END IF;
603
604 l_generated_pre_batch_tab (i).plan_cmplt_date :=
605 l_generated_pre_batch_tab (i).plan_start_date
606 + x_batch_leadtime_days;
607
608 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
609 gme_debug.put_line
610 ( 'W/LEADTIME l_generated_pre_batch_tab(i).plan_cmplt_date = '
611 || TO_CHAR
612 (l_generated_pre_batch_tab (i).plan_cmplt_date
613 ,'MM/DD/YYYY hh24:mi:ss') );
614 END IF;
615 ELSIF (l_pregen_fpo_row.schedule_method = 'BACKWARD') THEN
616 --Not First Batch; Scheduling BACKWARD
617 IF (l_pregen_fpo_row.offset_type = 0) THEN
618 --Start-to-Start
619 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
620 gme_debug.put_line ('BACKWARD scheduling start-to-start');
621 END IF;
622
623 x_prev_plan_start_date :=
624 l_generated_pre_batch_tab (i - 1).plan_start_date;
625
626 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
627 gme_debug.put_line ( 'x_prev_plan_start_date = '
628 || TO_CHAR (x_prev_plan_start_date
629 ,'MM/DD/YYYY hh24:mi:ss') );
630 END IF;
631
632 l_generated_pre_batch_tab (i).plan_start_date :=
633 x_prev_plan_start_date + x_neg_batch_offset_days;
634
635 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
636 gme_debug.put_line
637 ( 'W/NEG OFFSET l_generated_pre_batch_tab(i).plan_start_date = '
638 || TO_CHAR
639 (l_generated_pre_batch_tab (i).plan_start_date
640 ,'MM/DD/YYYY hh24:mi:ss') );
641 END IF;
642
643 l_generated_pre_batch_tab (i).plan_cmplt_date :=
644 l_generated_pre_batch_tab (i).plan_start_date
645 + x_batch_leadtime_days;
646
647 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
648 gme_debug.put_line
649 ( 'W/LEADTIME l_generated_pre_batch_tab(i).plan_cmplt_date = '
650 || TO_CHAR
651 (l_generated_pre_batch_tab (i).plan_cmplt_date
652 ,'MM/DD/YYYY hh24:mi:ss') );
653 END IF;
654 ELSE
655 --End-to-Start
656 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
657 gme_debug.put_line ('BACKWARD scheduling end-to-start');
658 END IF;
659
660 x_prev_plan_start_date :=
661 l_generated_pre_batch_tab (i - 1).plan_start_date;
662
663 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
664 gme_debug.put_line ( 'x_prev_plan_start_date = '
665 || TO_CHAR (x_prev_plan_start_date
666 ,'MM/DD/YYYY hh24:mi:ss') );
667 END IF;
668
669 l_generated_pre_batch_tab (i).plan_cmplt_date :=
670 x_prev_plan_start_date + x_neg_batch_offset_days;
671
672 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
673 gme_debug.put_line
674 ( 'W/NEG OFFSET l_generated_pre_batch_tab(i).plan_cmplt_date = '
675 || TO_CHAR
676 (l_generated_pre_batch_tab (i).plan_cmplt_date
677 ,'MM/DD/YYYY hh24:mi:ss') );
678 END IF;
679
680 l_generated_pre_batch_tab (i).plan_start_date :=
681 l_generated_pre_batch_tab (i).plan_cmplt_date
682 + x_neg_batch_leadtime_days;
683
684 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
685 gme_debug.put_line
686 ( 'W/NEG LEADTIME l_generated_pre_batch_tab(i).plan_start_date = '
687 || TO_CHAR
688 (l_generated_pre_batch_tab (i).plan_start_date
689 ,'MM/DD/YYYY hh24:mi:ss') );
690 END IF;
691 END IF;
692 END IF;
693 END IF;
694
695 l_generated_pre_batch_tab (i).due_date :=
696 l_generated_pre_batch_tab (i).plan_cmplt_date;
697
698 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
699 gme_debug.put_line
700 ( 'l_generated_pre_batch_tab(i).due_date= '
701 || TO_CHAR
702 (l_generated_pre_batch_tab (i).due_date
703 ,'MM/DD/YYYY hh24:mi:ss') );
704 END IF;
705
706 x_generated_pre_batch_tab (i) := l_generated_pre_batch_tab (i);
707 x_return_status := fnd_api.g_ret_sts_success;
708 END LOOP;
709
710 IF x_return_status <> fnd_api.g_ret_sts_success THEN
711 RAISE generate_pre_batch_err;
712 END IF;
713
714 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
715 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
716 END IF;
717 EXCEPTION
718 WHEN generate_pre_batch_err THEN
719 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
720 gme_debug.put_line
721 ('problem generateing the pre_batch-header recs');
722 END IF;
723
724 gme_common_pvt.log_message ('GEN_PRE_BATCH_HEADER_ERR');
725 x_return_status := fnd_api.g_ret_sts_error;
726 WHEN OTHERS THEN
727 x_return_status := fnd_api.g_ret_sts_unexp_error;
728 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
729 ,'generate_pre_batch_header_recs');
730 END generate_pre_batch_header_recs;
731
732 --****************************************************************
733 --****************************************************************
734 PROCEDURE convert_fpo_to_batch (
735 p_generated_pre_batch_tab IN generated_pre_batch_tab
736 ,p_recipe_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
737 ,p_pregen_fpo_row IN pregen_fpo_row
738 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
739 ,x_return_status OUT NOCOPY VARCHAR2
740 ,x_arr_rtn_sts OUT NOCOPY return_array_sts
741 ,p_process_row IN NUMBER DEFAULT 0
742 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
743 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
744 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
745 ,p_fpo_validity_rule_id IN NUMBER --BUG#3185748 Sastry
746 )
747 IS
748 l_api_name CONSTANT VARCHAR2 (30) := 'CONVERT_FPO_TO_BATCH';
749 dummy NUMBER;
750 l_generated_pre_batch_tab generated_pre_batch_tab;
751 l_pre_batch_row gme_batch_header%ROWTYPE;
752 l_in_pre_batch_row gme_batch_header%ROWTYPE;
753 l_recipe_validity_rule_tab gme_common_pvt.recipe_validity_rule_tab;
754 l_pregen_fpo_row pregen_fpo_row;
755 l_exception_material_tbl gme_common_pvt.exceptions_tab;
756 l_return_status VARCHAR2 (1);
757 l_api_version NUMBER;
758 l_validation_level NUMBER := 100;
759 l_batch_size gme_material_details.plan_qty%TYPE;
760 l_batch_uom gme_material_details.dtl_um%TYPE;
761 l_creation_mode VARCHAR2 (10);
762 l_om_gme_batch_rec gml_batch_om_util.batch_line_rec;
763 -- Bug 3185748 Added variables, validation_failure and cursor get_validity_rule .
764 l_inventory_item_id NUMBER; -- KYH GME CONV
765 l_item_um VARCHAR2 (3);
766 /* Exception definitions */
767 batch_creation_failure EXCEPTION;
768 validation_failure EXCEPTION;
769 --Swapna Kommineni bug#3565971 13/08/2004 Added variables to store the start and end dates
770 --calculated for the previous batch.
771 l_prev_start_date DATE;
772 l_prev_cmplt_date DATE;
773 l_msg_count NUMBER;
774 l_msg_data VARCHAR2(300);
775
776
777 /* Cusror definitions */
778 --KYH GME Convergence - replace ic_item_mst with mtl_system_items
779 --Remember that organization_id can be null in gmd_recipe_validity_rules
780 Cursor get_validity_rule (v_validity_rule_id NUMBER,v_organization_id NUMBER) IS
781 SELECT v.inventory_item_id, i.primary_uom_code
782 FROM gmd_recipe_validity_rules v, mtl_system_items i
783 WHERE recipe_validity_rule_id = v_validity_rule_id
784 AND i.organization_id = v_organization_id
785 AND v.inventory_item_id = i.inventory_item_id;
786 BEGIN
787 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
788 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
789 || l_api_name);
790 END IF;
791 x_return_status := fnd_api.g_ret_sts_success;
792 dummy := 0;
793 l_validation_level := gme_common_pvt.g_max_errors;
794 l_api_version := 2.0;
795 l_generated_pre_batch_tab := p_generated_pre_batch_tab;
796 l_recipe_validity_rule_tab := p_recipe_validity_rule_tab;
797 l_pregen_fpo_row := p_pregen_fpo_row;
798 l_batch_size := l_pregen_fpo_row.qty_per_batch;
799 -- change for item_uom for bug 2398719
800 l_batch_uom := l_pregen_fpo_row.batch_size_uom;
801 --Swapna Kommineni bug#3565971 13/08/2004 Initialization of variables to NULL
802 l_prev_start_date := NULL;
803 l_prev_cmplt_date := NULL;
804 l_creation_mode := 'PRODUCT';
805
806 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
807 gme_debug.put_line (g_pkg_name ||
808 '.' ||
809 l_api_name ||
810 'Batch UOM is '||
811 l_batch_uom);
812 END IF;
813
814 --Populate generated_pre_batch_tab with validity_rule.
815 IF (p_process_row = 0) THEN
816 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
817 gme_debug.put_line (g_pkg_name ||
818 '.' ||
819 l_api_name ||
820 ' p_process_row is set to 0 ');
821 END IF;
822 FOR i IN 1 .. p_generated_pre_batch_tab.COUNT LOOP
823 -- Bug 3185748 Added Begin .. End
824 BEGIN
825 gme_common_pvt.g_error_count := 0;
826 l_generated_pre_batch_tab (i).recipe_validity_rule_id :=
827 l_recipe_validity_rule_tab (i).recipe_validity_rule_id;
828 -- Populate header row with values of generated_pre_batch_tab row.
829 l_pre_batch_row := l_generated_pre_batch_tab (i);
830 -- Create batches from the generate_pre_batch_tab.
831 -- Pawan Kumar changed the call to private layer from the public layer for creat_batch
832 l_in_pre_batch_row := l_pre_batch_row;
833 --BEGIN BUG#3185748 Sastry
834 SAVEPOINT create_batch;
835
836 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
837 gme_debug.put_line (g_pkg_name ||
838 '.' ||
839 l_api_name ||
840 ' Retrieve validity_rule for id '||
841 p_fpo_validity_rule_id||
842 ' organization_id is '||
843 l_in_pre_batch_row.organization_id);
844 END IF;
845 OPEN get_validity_rule (p_fpo_validity_rule_id,l_in_pre_batch_row.organization_id);
846
847 FETCH Get_validity_rule INTO l_inventory_item_id, l_item_um; -- KYH GME CONV
848
849 IF get_validity_rule%NOTFOUND THEN
850 gme_common_pvt.log_message
851 ('GME_VALIDITY_RULE_RETRIEVE_ERR');
852
853 CLOSE get_validity_rule;
854
855 RAISE validation_failure;
856 END IF;
857
858 CLOSE get_validity_rule;
859
860 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
861 gme_debug.put_line (g_pkg_name ||
862 '.' ||
863 l_api_name ||
864 ' Invoke validate validity rule ');
865 END IF;
866 IF NOT gme_common_pvt.validate_validity_rule
867 (p_validity_rule_id => l_in_pre_batch_row.recipe_validity_rule_id
868 ,p_organization_id => l_in_pre_batch_row.organization_id
869 ,p_prim_product_id => l_inventory_item_id
870 ,p_qty => l_batch_size
871 ,p_uom => l_item_um
872 ,p_object_type => 'P'
873 ,p_start_date => l_in_pre_batch_row.plan_start_date
874 ,p_cmplt_date => l_in_pre_batch_row.plan_cmplt_date
875 ,p_creation_mode => 'PRODUCT') THEN
876 RAISE validation_failure;
877 END IF;
878
879 --END BUG#3185748
880 --Rishi Varma bug#3460631 22/03/04
881 --assigned null to the due date as the due date should be
882 --defaulted to the planned completion date.
883 l_in_pre_batch_row.due_date := NULL;
884
885 --Swapna Kommineni bug#3565971 13/08/2004 Start
886 /*This will recalculate the start and end dates for the second batch onwards depending on
887 the calculated completed or start dates of the first batch only in the case leadtime is
888 not given.In the case leadtime is given the correct dates will be calculated from
889 generate_pre_batch_header_recs*/
890 IF ( l_prev_cmplt_date IS NOT NULL
891 AND l_prev_start_date IS NOT NULL
892 AND l_pregen_fpo_row.leadtime = 0
893 AND l_pregen_fpo_row.offset_type = 1) THEN
894 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
895 l_in_pre_batch_row.plan_start_date :=
896 l_prev_cmplt_date
897 + NVL (l_pregen_fpo_row.batch_offset, 0) / 24;
898 l_in_pre_batch_row.plan_cmplt_date := NULL;
899 ELSIF (l_pregen_fpo_row.schedule_method = 'BACKWARD') THEN
900 l_in_pre_batch_row.plan_cmplt_date :=
901 l_prev_start_date
902 - NVL (l_pregen_fpo_row.batch_offset, 0) / 24;
903 l_in_pre_batch_row.plan_start_date := NULL;
904 END IF;
905 END IF;
906
907 --Swapna Kommineni bug#3565971 13/08/2004 End
908 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
909 gme_debug.put_line (g_pkg_name ||
910 '.' ||
911 l_api_name ||
912 ' about to invoke create_batch ');
913 END IF;
914 gme_create_batch_pvt.create_batch
915 (p_validation_level => l_validation_level
916 ,x_return_status => l_return_status
917 ,p_batch_header_rec => l_in_pre_batch_row
918 ,x_batch_header_rec => l_pre_batch_row
919 ,p_batch_size => l_batch_size
920 ,p_batch_size_uom => l_batch_uom
921 ,p_creation_mode => l_creation_mode
922 ,p_ignore_qty_below_cap => 'T'
923 ,p_use_workday_cal => p_use_shop_cal
924 ,p_contiguity_override => p_contiguity_override
925 ,x_exception_material_tbl => l_exception_material_tbl);
926
927 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
928 gme_debug.put_line (g_pkg_name ||
929 '.' ||
930 l_api_name ||
931 ' create_batch returns status of '||
932 l_return_status );
933 END IF;
934 IF l_return_status <> fnd_api.g_ret_sts_success THEN
935 x_arr_rtn_sts (i).return_status := l_return_status;
936
937 -- Pawan Kumar ADDED code for bug 2398719
938 IF l_return_status NOT IN (gme_common_pvt.g_inv_short_err) THEN
939 RAISE batch_creation_failure;
940 ELSE
941 FOR j IN 1 .. l_exception_material_tbl.COUNT LOOP
942 x_exception_material_tbl (i) :=
943 l_exception_material_tbl (j);
944 END LOOP;
945 END IF;
946 END IF; /* l_return_status <> FND_API.G_RET_STS_SUCCESS */
947
948 --Swapna Kommineni bug#3565971 13/08/2004 Assigning the planned start and end dates of
949 --batch that are calculated from the create_batch api
950 l_prev_start_date := l_pre_batch_row.plan_start_date;
951 l_prev_cmplt_date := l_pre_batch_row.plan_cmplt_date;
952 x_generated_pre_batch_tab (i) := l_pre_batch_row;
953 -- B3140274 OM-GME integration - CONVERT for convert FPO to batch
954 -- B3194346 OM-GME integration - call to central routine
955
956 /* GME_TRANS_ENGINE_PVT.inform_OM
957 ( p_action => 'CONVERT'
958 , p_trans_id => NULL
959 , p_trans_id_reversed => NULL
960 , p_gme_batch_hdr => l_pre_batch_row
961 , p_gme_matl_dtl => NULL
962 ); */
963 GME_SUPPLY_RES_PVT.create_reservation_from_FPO
964 ( P_FPO_batch_id => l_pre_batch_row.fpo_id
965 , P_New_batch_id => l_pre_batch_row.batch_id
966 , X_return_status => x_return_status
967 , X_msg_count => l_msg_count
968 , X_msg_data => l_msg_data
969 );
970
971 --BEGIN BUG#3185748 Sastry
972 EXCEPTION
973 WHEN batch_creation_failure OR validation_failure THEN
974 x_return_status := fnd_api.g_ret_sts_error;
975 ROLLBACK TO SAVEPOINT create_batch;
976
977 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
978 gme_debug.put_line ('batch creation_fail');
979 END IF;
980 END;
981 --END BUG#3185748
982 END LOOP;
983 ELSE /* IF (p_process_row = 0) */
984 l_generated_pre_batch_tab (p_process_row).recipe_validity_rule_id :=
985 l_recipe_validity_rule_tab (p_process_row).recipe_validity_rule_id;
986 -- Populate header row with values of generated_pre_batch_tab row.
987 l_pre_batch_row := l_generated_pre_batch_tab (p_process_row);
988 -- Create batches from the generate_pre_batch_tab.
989 -- Pawan Kumar changed the call to private layer from public API
990 l_in_pre_batch_row := l_pre_batch_row;
991 gme_create_batch_pvt.create_batch
992 (p_validation_level => l_validation_level
993 ,x_return_status => l_return_status
994 ,p_batch_header_rec => l_in_pre_batch_row
995 ,x_batch_header_rec => l_pre_batch_row
996 ,p_batch_size => l_batch_size
997 ,p_batch_size_uom => l_batch_uom
998 ,p_creation_mode => l_creation_mode
999 ,p_ignore_qty_below_cap => 'T'
1000 ,p_use_workday_cal => p_use_shop_cal
1001 ,p_contiguity_override => p_contiguity_override
1002 ,x_exception_material_tbl => l_exception_material_tbl);
1003
1004 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1005 gme_debug.put_line (g_pkg_name ||
1006 '.' ||
1007 l_api_name ||
1008 ' call to create_batch returns status of '||
1009 l_return_status||
1010 ' current return status '||
1011 x_return_status);
1012 END IF;
1013 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1014 x_arr_rtn_sts (p_process_row).return_status := l_return_status;
1015
1016 IF l_return_status NOT IN (gme_common_pvt.g_inv_short_err) THEN
1017 RAISE batch_creation_failure;
1018 END IF;
1019 END IF;
1020
1021 x_generated_pre_batch_tab (p_process_row) := l_pre_batch_row;
1022 -- B3140274 OM-GME integration - CONVERT for convert FPO to batch
1023 -- B3194346 OM-GME integration - call to central routine
1024
1025 /* GME_TRANS_ENGINE_PVT.inform_OM
1026 ( p_action => 'CONVERT'
1027 , p_trans_id => NULL
1028 , p_trans_id_reversed => NULL
1029 , p_gme_batch_hdr => l_pre_batch_row
1030 , p_gme_matl_dtl => NULL
1031 ); */
1032 GME_SUPPLY_RES_PVT.create_reservation_from_FPO
1033 ( P_FPO_batch_id => l_pre_batch_row.fpo_id
1034 , P_New_batch_id => l_pre_batch_row.batch_id
1035 , X_return_status => x_return_status
1036 , X_msg_count => l_msg_count
1037 , X_msg_data => l_msg_data
1038 );
1039
1040 END IF; /* IF (p_process_row = 0) */
1041 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1042 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name ||' with status '||x_return_status);
1043 END IF;
1044 EXCEPTION
1045 WHEN batch_creation_failure THEN
1046 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1047 gme_debug.put_line ('batch creation_fail');
1048 END IF;
1049
1050 x_return_status := fnd_api.g_ret_sts_error;
1051
1052 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1053 gme_debug.put_line ('x_return_status = ' || x_return_status);
1054 END IF;
1055 WHEN OTHERS THEN
1056 x_return_status := fnd_api.g_ret_sts_unexp_error;
1057 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
1058 ,'CONVERT_FPO_TO_BATCH');
1059 END convert_fpo_to_batch;
1060
1061 --*********************************************************
1062 --*********************************************************
1063 PROCEDURE update_original_fpo (
1064 p_fpo_header_row IN gme_batch_header%ROWTYPE
1065 ,p_prim_prod_row IN gme_material_details%ROWTYPE
1066 ,p_pregen_fpo_row IN pregen_fpo_row
1067 ,p_fpo_material_details_tab IN fpo_material_details_tab
1068 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
1069 ,x_fpo_header_row OUT NOCOPY gme_batch_header%ROWTYPE
1070 ,x_return_status OUT NOCOPY VARCHAR2)
1071 IS
1072 CURSOR get_phantom_fpo (v_batch_id NUMBER)
1073 IS
1074 SELECT phantom_id
1075 FROM gme_material_details
1076 WHERE batch_id = v_batch_id AND phantom_id IS NOT NULL;
1077
1078 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ORIGINAL_FPO';
1079 l_fpo_header_row gme_batch_header%ROWTYPE;
1080 l_prim_prod_row gme_material_details%ROWTYPE;
1081 l_pregen_fpo_row pregen_fpo_row;
1082 l_fpo_material_details_tab fpo_material_details_tab;
1083 l_over_allocations gme_common_pvt.exceptions_tab;
1084 l_tran_row gme_inventory_txns_gtmp%ROWTYPE;
1085 -- GME CONV l_tran_tab gme_common_pvt.transactions_tab;
1086 l_batch_header gme_batch_header%ROWTYPE;
1087 x_batch_header gme_batch_header%ROWTYPE;
1088 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
1089 l_resource_tab gme_common_pvt.resource_transactions_tab;
1090 -- l_batch_type ic_tran_pnd.doc_type%TYPE;
1091 l_scale_factor NUMBER;
1092 l_primaries VARCHAR2 (8);
1093 l_batch_status gme_batch_header.batch_status%TYPE;
1094 l_mat_row_count NUMBER;
1095 l_rsc_row_count NUMBER;
1096 l_return_status VARCHAR2 (1);
1097 l_return BOOLEAN;
1098 l_exception_material_tbl gme_common_pvt.exceptions_tab;
1099 /*Exceptions */
1100 load_rsrc_trans_err EXCEPTION;
1101 gme_fetch_all_trans_err EXCEPTION;
1102 update_row_err EXCEPTION;
1103 delete_pending_trans_err EXCEPTION;
1104 scale_batch_err EXCEPTION;
1105 update_pending_trans EXCEPTION;
1106 cancel_batch_err EXCEPTION;
1107 fetch_batch_err EXCEPTION;
1108 BEGIN
1109 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1110 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1111 || l_api_name);
1112 END IF;
1113 x_return_status := fnd_api.g_ret_sts_success;
1114
1115 l_fpo_header_row := p_fpo_header_row;
1116 l_batch_status := l_fpo_header_row.batch_status;
1117 l_fpo_material_details_tab := p_fpo_material_details_tab;
1118 l_primaries := 'OUTPUTS';
1119 l_prim_prod_row := p_prim_prod_row;
1120 l_pregen_fpo_row := p_pregen_fpo_row;
1121 -- Working with the Pending Transactions of the original FPO.
1122 --
1123 -- Load pending transactions for FPO from ic_tran_pnd to
1124 -- gme temp table. Will have to work
1125 -- with pending transactions.
1126 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_fpo_header_row
1127 ,x_rsc_row_count => l_rsc_row_count
1128 ,x_return_status => l_return_status);
1129
1130 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1131 RAISE load_rsrc_trans_err;
1132 END IF;
1133
1134 --Fetch all Pending transactions for original FPO
1135 --into local table.
1136 /* GME CONV
1137 l_tran_row.doc_id := l_fpo_header_row.batch_id;
1138 l_tran_row.doc_type := 'FPO';
1139 gme_trans_engine_pvt.fetch_all_trans (
1140 l_tran_row,
1141 l_tran_tab,
1142 l_return_status
1143 );
1144
1145 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1146 RAISE gme_fetch_all_trans_err;
1147 END IF;
1148 GME CONV */
1149
1150 -- Determine whether sum of effective qtys inserted is
1151 -- less than the original FPO quantity.
1152 -- If less than the original FPO qty
1153 -- scale the original FPO and all pending transactions to the
1154 -- difference.
1155 -- If greater than or equal to the original FPO qty, set FPO status
1156 -- to converted and delete pending transactions.
1157 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1158 gme_debug.put_line ( 'l_prim_prod_row.plan_qty = '
1159 || TO_CHAR (l_prim_prod_row.plan_qty) );
1160 gme_debug.put_line ( 'l_pregen_fpo_row.sum_eff_qty = '
1161 || TO_CHAR (l_pregen_fpo_row.sum_eff_qty) );
1162 END IF;
1163
1164 IF (l_prim_prod_row.plan_qty <= l_pregen_fpo_row.sum_eff_qty) THEN
1165 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1166 gme_debug.put_line
1167 ('in IF.gonna update header and delete pending trans');
1168 END IF;
1169
1170 --Update FPO status to CONVERTED
1171 l_fpo_header_row.batch_status := -3;
1172 l_return := gme_batch_header_dbl.update_row (l_fpo_header_row);
1173
1174 IF NOT l_return THEN
1175 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1176 gme_debug.put_line ('l_return from update_row = FALSE');
1177 END IF;
1178
1179 RAISE update_row_err;
1180 ELSE
1181 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1182 gme_debug.put_line ('l_return from update_row = TRUE');
1183 END IF;
1184 END IF;
1185
1186 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1187 gme_debug.put_line ( 'l_fpo_header_row.batch_status = '
1188 || TO_CHAR (l_fpo_header_row.batch_status) );
1189 gme_debug.put_line ( 'l_fpo_header_row.batch_id = '
1190 || TO_CHAR (l_fpo_header_row.batch_id) );
1191 gme_debug.put_line ( 'l_fpo_header_row.batch_type = '
1192 || TO_CHAR (l_fpo_header_row.batch_type) );
1193 END IF;
1194
1195 --FPBug#4941012 set plan qty to zero for parent FPO
1196 UPDATE gme_material_details
1197 SET plan_qty = 0
1198 WHERE batch_id = l_fpo_header_row.batch_id;
1199
1200 --Gonna delete the pending transactions associated with orig FPO
1201 /* GME CONV
1202
1203 FOR i IN 1 .. l_tran_tab.COUNT
1204 LOOP
1205 l_tran_row := l_tran_tab (i);
1206 l_tran_row.alloc_qty := 0;
1207 l_tran_row.trans_qty := 0;
1208 l_tran_row.trans_qty2 := 0;
1209 gme_trans_engine_pvt.update_pending_trans (
1210 l_tran_row,
1211 l_return_status
1212 );
1213
1214 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1215 RAISE update_pending_trans;
1216 END IF;
1217 END LOOP;
1218 GME CONV */
1219
1220 --Fetch all resource transactions for original FPO
1221 --into local table.
1222 l_resource_txns.doc_id := l_fpo_header_row.batch_id;
1223 gme_resource_engine_pvt.fetch_active_resources
1224 (p_resource_rec => l_resource_txns
1225 ,x_resource_tbl => l_resource_tab
1226 ,x_return_status => l_return_status);
1227
1228 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1229 RAISE gme_fetch_all_trans_err;
1230 END IF; /* IF l_return_status <> x_return_status */
1231
1232 FOR i IN 1 .. l_resource_tab.COUNT LOOP
1233 l_resource_txns := l_resource_tab (i);
1234 /* Bug 2376315 - Thomas Daniel */
1235 /* Commented the following code for updating the resource usage to zero and added */
1236 /* code to delete the resource transactions */
1237
1238 /*l_resource_txns.resource_usage := 0;
1239 GME_RESOURCE_ENGINE_PVT.update_resource_trans (p_tran_row => l_resource_txns
1240 ,x_return_status => l_return_status);*/
1241 gme_resource_engine_pvt.delete_resource_trans
1242 (p_tran_rec => l_resource_txns
1243 ,x_return_status => l_return_status);
1244
1245 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1246 RAISE update_row_err;
1247 END IF;
1248 END LOOP; /* FOR i IN 1..l_resource_tab.COUNT */
1249
1250 -- Add code for canel phantom fpo
1251 -- pk_fpo('in fpo-before cancel'||l_fpo_header_row.batch_id);
1252 FOR l_rec IN get_phantom_fpo (l_fpo_header_row.batch_id) LOOP
1253 l_batch_header.batch_id := l_rec.phantom_id;
1254
1255 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1256 gme_debug.put_line ( 'phantom to cancel = '
1257 || TO_CHAR (l_batch_header.batch_id) );
1258 END IF;
1259
1260 --FPBug#4941012
1261 IF NOT gme_batch_header_dbl.fetch_row(l_batch_header,
1262 l_batch_header) THEN
1263 RAISE fetch_batch_err;
1264 END IF;
1265
1266 gme_cancel_batch_pvt.cancel_batch
1267 (p_batch_header_rec => l_batch_header
1268 ,x_batch_header_rec => x_batch_header
1269 ,x_return_status => x_return_status);
1270
1271 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1272 RAISE cancel_batch_err;
1273 END IF;
1274 END LOOP;
1275 ELSE
1276 --Scale FPO material details line
1277 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1278 gme_debug.put_line ('gonna do some scaling');
1279 END IF;
1280
1281 l_scale_factor :=
1282 (l_prim_prod_row.plan_qty - l_pregen_fpo_row.sum_eff_qty)
1283 / l_prim_prod_row.plan_qty;
1284
1285 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1286 gme_debug.put_line ('l_scale_factor = ' || l_scale_factor);
1287 END IF;
1288
1289 --p_qty_type is default 1 for the plan qty only
1290 /* l_over_allocations parameter is used to handle default lot going */
1291 /* negative cases, which would not be a case here as their are no */
1292 /* actual allocations against the FPO */
1293 gme_scale_batch_pvt.scale_batch
1294 (p_batch_header_rec => l_fpo_header_row
1295 ,p_scale_factor => l_scale_factor
1296 ,p_primaries => l_primaries
1297 ,p_qty_type => 1
1298 ,p_validity_rule_id => NULL
1299 ,p_enforce_vldt_check => 'F'
1300 ,p_use_workday_cal => 'F'
1301 ,p_contiguity_override => 'T'
1302 ,p_recalc_dates => 'T'
1303 ,x_return_status => l_return_status
1304 ,x_batch_header_rec => x_fpo_header_row
1305 ,x_exception_material_tbl => l_exception_material_tbl);
1306
1307 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1308 RAISE scale_batch_err;
1309 END IF;
1310 END IF;
1311
1312 x_fpo_header_row := l_fpo_header_row;
1313 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1314 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1315 END IF;
1316 EXCEPTION
1317 WHEN load_rsrc_trans_err THEN
1318 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1319 gme_debug.put_line ('load_rsrc_trans_err_fail');
1320 END IF;
1321
1322 x_return_status := fnd_api.g_ret_sts_error;
1323 WHEN cancel_batch_err THEN
1324 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1325 gme_debug.put_line ('Cancel_FPO_PHANTOM_fail');
1326 END IF;
1327
1328 x_return_status := fnd_api.g_ret_sts_error;
1329 WHEN gme_fetch_all_trans_err THEN
1330 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1331 gme_debug.put_line ('fetch_alltrans_err_fail');
1332 END IF;
1333
1334 x_return_status := fnd_api.g_ret_sts_error;
1335 --FPBug#4941012
1336 WHEN fetch_batch_err THEN
1337 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1338 gme_debug.put_line ('fetch batch header fail');
1339 END IF;
1340
1341 x_return_status := fnd_api.g_ret_sts_error;
1342 WHEN update_row_err THEN
1343 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1344 gme_debug.put_line ('update_row _fail');
1345 END IF;
1346
1347 x_return_status := fnd_api.g_ret_sts_error;
1348 WHEN delete_pending_trans_err THEN
1349 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1350 gme_debug.put_line ('delete_pending_trans _fail');
1351 END IF;
1352
1353 x_return_status := fnd_api.g_ret_sts_error;
1354 WHEN scale_batch_err THEN
1355 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1356 gme_debug.put_line ('scale_batch _fail');
1357 END IF;
1358
1359 x_return_status := fnd_api.g_ret_sts_error;
1360 WHEN update_pending_trans THEN
1361 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1362 gme_debug.put_line ('update_pending_trans _fail');
1363 END IF;
1364
1365 x_return_status := fnd_api.g_ret_sts_error;
1366 WHEN OTHERS THEN
1367 x_return_status := fnd_api.g_ret_sts_unexp_error;
1368 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
1369 ,'UPDATE_ORIGINAL_FPO');
1370 END update_original_fpo;
1371
1372 --*********************************************************
1373 PROCEDURE convert_fpo_main (
1374 p_batch_header IN gme_batch_header%ROWTYPE
1375 ,p_batch_size IN NUMBER
1376 ,p_num_batches IN NUMBER
1377 ,p_validity_rule_id IN NUMBER
1378 ,p_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
1379 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
1380 ,p_leadtime IN NUMBER
1381 ,p_batch_offset IN NUMBER
1382 ,p_offset_type IN NUMBER
1383 ,
1384 -- p_schedule_method IN VARCHAR2,
1385 p_plan_start_date IN gme_batch_header.plan_start_date%TYPE
1386 ,p_plan_cmplt_date IN gme_batch_header.plan_cmplt_date%TYPE
1387 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
1388 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
1389 ,x_return_status OUT NOCOPY VARCHAR2
1390 ,x_batch_header OUT NOCOPY gme_batch_header%ROWTYPE
1391 ,p_use_for_all IN VARCHAR2 := fnd_api.g_true)
1392 IS
1393 l_api_name CONSTANT VARCHAR2 (30) := 'CONVERT_FPO_MAIN';
1394 /* Cursor definitions */
1395 --KYH GME Convergence - replace ic_item_mst with mtl_system_items
1396 Cursor get_validity_rule (v_validity_rule_id NUMBER, v_organization_id NUMBER) IS
1397 SELECT v.inventory_item_id, i.primary_uom_code
1398 FROM gmd_recipe_validity_rules v, mtl_system_items i
1399 WHERE recipe_validity_rule_id = v_validity_rule_id
1400 AND i.organization_id = v_organization_id
1401 AND v.inventory_item_id = i.inventory_item_id;
1402
1403 /* Local variables */
1404 l_inventory_item_id NUMBER; -- KYH GME CONV
1405 l_fpo_header_row gme_batch_header%ROWTYPE;
1406 l_in_fpo_header_row gme_batch_header%ROWTYPE;
1407 l_batch_header gme_batch_header%ROWTYPE;
1408 l_prim_prod_row gme_material_details%ROWTYPE;
1409 l_fpo_material_details_tab fpo_material_details_tab;
1410 l_validity_rule_row validity_rule_row;
1411 l_pregen_fpo_row pregen_fpo_row;
1412 l_in_pregen_fpo_row pregen_fpo_row;
1413 x_pregen_fpo_row pregen_fpo_row;
1414 l_generated_pre_batch_tab generated_pre_batch_tab;
1415 l_in_generated_pre_batch_tab generated_pre_batch_tab;
1416 l_recipe_validity_rule_tab gme_common_pvt.recipe_validity_rule_tab;
1417 l_return_status VARCHAR2 (1);
1418 l_item_um VARCHAR2 (4);
1419 l_arr_rtn_sts return_array_sts;
1420 l_batch_range VARCHAR2 (2000);
1421 l_exception_material_tbl gme_common_pvt.exceptions_tab;
1422 /* Exception definitions */
1423 validation_failure EXCEPTION;
1424 fpo_retrieval_failure EXCEPTION;
1425 batch_generation_failure EXCEPTION;
1426 insufficient_validity_rules EXCEPTION;
1427 create_batch_failure EXCEPTION;
1428 update_original_fpo_failure EXCEPTION;
1429 batch_header_fetch_error EXCEPTION;
1430 BEGIN
1431 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1432 gme_debug.log_initialize ('ConvertFPO');
1433 END IF;
1434
1435 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1436 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1437 || l_api_name);
1438 END IF;
1439
1440 x_return_status := fnd_api.g_ret_sts_success;
1441 -- Pawan Kumar ADDED code for bug 2398719
1442 l_batch_header.batch_id := p_batch_header.batch_id;
1443 /* Bug2403042: Added following code to identify the
1444 FPO based on batch_no, and plant_code */
1445 l_batch_header.batch_no := p_batch_header.batch_no;
1446 l_batch_header.organization_id := p_batch_header.organization_id; -- KYH GME CONV
1447 l_batch_header.batch_id := p_batch_header.batch_id; -- KYH GME CONV
1448 l_batch_header.batch_type := 10;
1449
1450 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1451 gme_debug.put_line (g_pkg_name ||
1452 '.' ||
1453 l_api_name ||
1454 ' Retrieve batch header for batch_id '||
1455 l_batch_header.batch_id);
1456 END IF;
1457 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
1458 RAISE batch_header_fetch_error;
1459 END IF;
1460
1461 /* Get the validity rule for the existing FPO */
1462 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1463 gme_debug.put_line (g_pkg_name ||
1464 '.' ||
1465 l_api_name ||
1466 ' Retrieve validity rule for rule_id '||
1467 l_batch_header.recipe_validity_rule_id);
1468 END IF;
1469 OPEN get_validity_rule (l_batch_header.recipe_validity_rule_id,l_batch_header.organization_id);
1470
1471 FETCH get_validity_rule
1472 INTO l_inventory_item_id, l_item_um;
1473
1474 IF get_validity_rule%NOTFOUND THEN
1475 gme_common_pvt.log_message ('GME_VALIDITY_RULE_RETRIEVE_ERR');
1476
1477 CLOSE get_validity_rule;
1478
1479 RAISE validation_failure;
1480 END IF;
1481
1482 CLOSE get_validity_rule;
1483
1484 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1485 gme_debug.put_line (g_pkg_name ||
1486 '.' ||
1487 l_api_name ||
1488 ' Invoke Validation ');
1489 END IF;
1490
1491 VALIDATION (p_batch_header => l_batch_header
1492 ,
1493 -- x_batch_header => x_batch_header,
1494 p_batch_size => p_batch_size
1495 ,p_batch_size_uom => l_item_um
1496 ,p_num_batches => p_num_batches
1497 ,p_validity_rule_id => p_validity_rule_id
1498 ,p_leadtime => p_leadtime
1499 ,p_batch_offset => p_batch_offset
1500 ,p_offset_type => p_offset_type
1501 ,
1502 -- p_schedule_method => p_schedule_method,
1503 p_plan_start_date => p_plan_start_date
1504 ,p_plan_cmplt_date => p_plan_cmplt_date
1505 ,x_pregen_fpo_row => x_pregen_fpo_row
1506 ,x_return_status => x_return_status);
1507
1508 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1509 gme_debug.put_line (g_pkg_name ||
1510 '.' ||
1511 l_api_name ||
1512 ' VALIDATION procedure returns '||
1513 x_return_status);
1514 END IF;
1515
1516 l_pregen_fpo_row := x_pregen_fpo_row;
1517 l_fpo_header_row := l_batch_header;
1518
1519 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1520 RAISE validation_failure;
1521 END IF;
1522
1523 l_in_fpo_header_row := l_fpo_header_row;
1524 l_in_pregen_fpo_row := l_pregen_fpo_row;
1525 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1526 gme_debug.put_line (g_pkg_name ||
1527 '.' ||
1528 l_api_name ||
1529 ' About to INVOKE retrieve_fpo_data ');
1530 END IF;
1531 retrieve_fpo_data
1532 (p_fpo_header_row => l_in_fpo_header_row
1533 ,x_fpo_header_row => l_fpo_header_row
1534 ,p_pregen_fpo_row => l_in_pregen_fpo_row
1535 ,x_pregen_fpo_row => l_pregen_fpo_row
1536 ,x_prim_prod_row => l_prim_prod_row
1537 ,x_validity_rule_row => l_validity_rule_row
1538 ,x_fpo_material_details_tab => l_fpo_material_details_tab
1539 ,x_return_status => x_return_status);
1540
1541 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1542 gme_debug.put_line (g_pkg_name ||
1543 '.' ||
1544 l_api_name ||
1545 ' retrieve_fpo_data returns status of '||
1546 x_return_status);
1547 END IF;
1548
1549 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1550 RAISE fpo_retrieval_failure;
1551 END IF;
1552
1553 l_item_um := l_prim_prod_row.dtl_um;
1554
1555 IF p_use_for_all = fnd_api.g_true THEN
1556 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1557 gme_debug.put_line (g_pkg_name ||
1558 '.' ||
1559 l_api_name ||
1560 ' p_use_for_all is TRUE ');
1561 END IF;
1562 IF p_validity_rule_id IS NULL OR p_validity_rule_id = 0 THEN
1563 RAISE insufficient_validity_rules;
1564 -- Bug 3185748 Removed the code to validate the validity rule if
1565 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1566 END IF; /* p_validity_rule_id IS NULL OR */
1567
1568 FOR i IN 1 .. p_num_batches LOOP
1569 l_recipe_validity_rule_tab (i).recipe_validity_rule_id :=
1570 p_validity_rule_id;
1571 END LOOP;
1572 ELSE /* IF p_use_for_all */
1573 IF p_num_batches = 1 THEN
1574 IF p_validity_rule_id IS NULL THEN
1575 RAISE insufficient_validity_rules;
1576 -- Bug 3185748 Removed the code to validate the validity rule if
1577 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1578 END IF;
1579
1580 l_recipe_validity_rule_tab (1).recipe_validity_rule_id :=
1581 p_validity_rule_id;
1582 ELSE /* IF p_num_batches = 1 */
1583 IF p_validity_rule_tab.COUNT <> p_num_batches THEN
1584 RAISE insufficient_validity_rules;
1585 -- Bug 3185748 Removed the code to validate the validity rule if
1586 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1587 END IF; /* IF p_validity_rule_tab.COUNT <> p_num_batches */
1588
1589 l_recipe_validity_rule_tab := p_validity_rule_tab;
1590 END IF; /* IF p_num_batches = 1 */
1591 END IF; /* IF p_use_for_all */
1592
1593 --Swapna Kommineni bug#3565971 13/08/2004 commented the code so that passed lead time
1594 -- does not get overwritten.
1595 /* IF (l_pregen_fpo_row.rules_found = 1) THEN
1596 l_in_pregen_fpo_row := l_pregen_fpo_row;
1597 calculate_leadtime (
1598 p_pregen_fpo_row => l_in_pregen_fpo_row,
1599 x_pregen_fpo_row => l_pregen_fpo_row,
1600 x_return_status => l_return_status
1601 );
1602 END IF; */
1603 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1604 gme_debug.put_line (g_pkg_name ||
1605 '.' ||
1606 l_api_name ||
1607 ' Retrieve batch header for batch_id '||
1608 l_batch_header.batch_id);
1609 END IF;
1610 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1611 gme_debug.put_line (g_pkg_name ||
1612 '.' ||
1613 l_api_name ||
1614 ' Invoke generate_pre_batch_header_recs');
1615 END IF;
1616 generate_pre_batch_header_recs
1617 (p_fpo_header_row => l_fpo_header_row
1618 ,p_prim_prod_row => l_prim_prod_row
1619 ,p_pregen_fpo_row => l_pregen_fpo_row
1620 ,x_generated_pre_batch_tab => l_generated_pre_batch_tab
1621 ,x_return_status => x_return_status);
1622
1623 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1624 gme_debug.put_line (g_pkg_name ||
1625 '.' ||
1626 l_api_name ||
1627 ' generate_pre_batch_header_recs returns '||
1628 x_return_status );
1629 END IF;
1630
1631 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1632 RAISE batch_generation_failure;
1633 END IF;
1634
1635 --Swapna Kommineni bug#3565971 13/08/2004 Start
1636 FOR i IN 1 .. l_generated_pre_batch_tab.COUNT LOOP
1637 -- IF (p_leadtime is NULL) and (i=1) THEN
1638
1639 --Swapna Kommineni reopened bug#3565971 07/09/2004
1640 /* Only when the leadtime is not passed then the start and enddates calculated
1641 from generate_pre_batch_header_recs will be changed */
1642 IF (p_leadtime IS NULL) THEN
1643 IF (i = 1) THEN
1644 /* For the first batch if the leadtime is not passed then plan start and end dates are
1645 passed to the create_batch api as the given dates. so that api will*/
1646 l_generated_pre_batch_tab (i).plan_start_date :=
1647 p_plan_start_date;
1648 l_generated_pre_batch_tab (i).plan_cmplt_date :=
1649 p_plan_cmplt_date;
1650 ELSE
1651 IF (p_plan_cmplt_date IS NULL) THEN
1652 l_generated_pre_batch_tab (i).plan_cmplt_date :=
1653 p_plan_cmplt_date;
1654 ELSIF (p_plan_start_date IS NULL) THEN
1655 l_generated_pre_batch_tab (i).plan_start_date :=
1656 p_plan_start_date;
1657 END IF;
1658 END IF;
1659 END IF;
1660 END LOOP;
1661
1662 --Swapna Kommineni bug#3565971 13/08/2004 End
1663 l_in_generated_pre_batch_tab := l_generated_pre_batch_tab;
1664 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1665 gme_debug.put_line (g_pkg_name ||
1666 '.' ||
1667 l_api_name ||
1668 ' Invoke convert_fpo_to_batch ');
1669 END IF;
1670 convert_fpo_to_batch
1671 (p_generated_pre_batch_tab => l_in_generated_pre_batch_tab
1672 ,p_recipe_validity_rule_tab => l_recipe_validity_rule_tab
1673 ,p_pregen_fpo_row => l_pregen_fpo_row
1674 ,x_generated_pre_batch_tab => l_generated_pre_batch_tab
1675 ,p_use_shop_cal => p_use_shop_cal
1676 ,p_contiguity_override => p_contiguity_override
1677 ,x_return_status => x_return_status
1678 ,x_arr_rtn_sts => l_arr_rtn_sts
1679 ,x_exception_material_tbl => l_exception_material_tbl
1680 ,p_fpo_validity_rule_id => l_batch_header.recipe_validity_rule_id
1681 -- Bug 3185748
1682 );
1683 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1684 gme_debug.put_line (g_pkg_name ||
1685 '.' ||
1686 l_api_name ||
1687 ' convert_fpo_to_batch returns '||
1688 x_return_status);
1689 END IF;
1690
1691 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1692 RAISE create_batch_failure;
1693 END IF;
1694
1695 --BEGIN BUG#3185748 Sastry
1696 -- Call update fpo only if some batches were created.
1697 IF l_generated_pre_batch_tab.COUNT > 0 THEN
1698 l_pregen_fpo_row.num_batches := l_generated_pre_batch_tab.COUNT;
1699 l_pregen_fpo_row.sum_eff_qty :=
1700 l_generated_pre_batch_tab.COUNT * p_batch_size;
1701 update_original_fpo
1702 (p_fpo_header_row => l_fpo_header_row
1703 ,p_prim_prod_row => l_prim_prod_row
1704 ,p_pregen_fpo_row => l_pregen_fpo_row
1705 ,p_fpo_material_details_tab => l_fpo_material_details_tab
1706 ,p_enforce_vldt_check => p_enforce_vldt_check
1707 ,x_fpo_header_row => x_batch_header
1708 ,x_return_status => x_return_status);
1709
1710 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1711 gme_debug.put_line ( 'in private convert_fpo-after update'
1712 || x_return_status);
1713 END IF;
1714
1715 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1716 RAISE update_original_fpo_failure;
1717 END IF;
1718
1719 -- Bug 3185748 Use l_generated_pre_batch_tab.FIRST and l_generated_pre_batch_tab.LAST
1720 -- to get the batch range as all the batches might have not been created.
1721 l_batch_range :=
1722 l_generated_pre_batch_tab (l_generated_pre_batch_tab.FIRST).batch_no;
1723
1724 IF l_generated_pre_batch_tab.COUNT > 1 THEN
1725 l_batch_range :=
1726 l_batch_range
1727 || ' - '
1728 || l_generated_pre_batch_tab (l_generated_pre_batch_tab.LAST).batch_no;
1729 END IF;
1730
1731 -- Bug 3185748 Display proper message
1732 IF p_num_batches = l_generated_pre_batch_tab.COUNT THEN
1733 gme_common_pvt.log_message ('GME_FPO_TO_BATCHES_CREATED'
1734 ,'BATCHRANGE'
1735 ,l_batch_range);
1736 ELSE
1737 gme_common_pvt.log_message ('GME_NOT_ALL_BATCHES_CREATED'
1738 ,'BATCHRANGE'
1739 ,l_batch_range);
1740 END IF;
1741 END IF;
1742 --END BUG#3185748 Sastry
1743 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1744 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1745 END IF;
1746 EXCEPTION
1747 WHEN validation_failure THEN
1748 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1749 gme_debug.put_line ('in validiation fail exception');
1750 END IF;
1751
1752 x_return_status := fnd_api.g_ret_sts_error;
1753 WHEN fpo_retrieval_failure THEN
1754 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1755 gme_debug.put_line ('in retrieval fail exception');
1756 END IF;
1757
1758 x_return_status := fnd_api.g_ret_sts_error;
1759 WHEN batch_generation_failure THEN
1760 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1761 gme_debug.put_line ('in batch_generation fail exception');
1762 END IF;
1763
1764 x_return_status := fnd_api.g_ret_sts_error;
1765 WHEN insufficient_validity_rules THEN
1766 x_return_status := fnd_api.g_ret_sts_error;
1767 gme_common_pvt.log_message ('GME_INSUF_VAL_RULE');
1768 WHEN create_batch_failure THEN
1769 x_return_status := fnd_api.g_ret_sts_error;
1770 WHEN update_original_fpo_failure OR batch_header_fetch_error THEN
1771 x_return_status := fnd_api.g_ret_sts_error;
1772 WHEN OTHERS THEN
1773 x_return_status := fnd_api.g_ret_sts_unexp_error;
1774 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO', 'CONVERT_FPO_MAIN');
1775 END convert_fpo_main;
1776 --****************************************************************
1777 END gme_convert_fpo_pvt;