[Home] [Help]
PACKAGE BODY: APPS.GME_CONVERT_FPO_PVT
Source
1 PACKAGE BODY gme_convert_fpo_pvt AS
2 /* $Header: GMEVCFPB.pls 120.8.12020000.4 2012/12/12 10:26:54 apmishra ship $ */
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 -- Bug 15915373
322 -- Replacing the call to the deprecated gmicuom.icuomcv API
323 -- with inv_convert.INV_UM_CONVERT
324 --
325 /*
326 gmicuom.icuomcv (x_prim_prod_row.inventory_item_id
327 ,0
328 ,l_prod_row.plan_qty
329 ,l_prod_row.dtl_um
330 ,x_prim_prod_row.dtl_um
331 ,l_plan_qty);*/
332
333 l_plan_qty := inv_convert.INV_UM_CONVERT(x_prim_prod_row.inventory_item_id,
334 32,
335 l_prod_row.plan_qty,
336 l_prod_row.dtl_um,
337 x_prim_prod_row.dtl_um,
338 NULL,
339 NULL);
340
341 END IF;
342
343 x_prim_prod_row.plan_qty := x_prim_prod_row.plan_qty + l_plan_qty;
344 END LOOP;
345
346 --End Bug 2924803 Anil
347 CLOSE get_prim_prod_row;
348
349 --Get prim prod item info
350 OPEN item_master_cursor (x_prim_prod_row.inventory_item_id
351 ,p_fpo_header_row.organization_id);
352
353 FETCH item_master_cursor
354 INTO l_item_row;
355
356 CLOSE item_master_cursor;
357
358 /* GME CONV Added above lines
359 SELECT *
360 INTO l_item_row
361 FROM ic_item_mst
362 WHERE ic_item_mst.item_id = x_prim_prod_row.inventory_item_id;
363 */ --Assign primary product details to validity_rule structure
364 x_validity_rule_row.organization_id := p_fpo_header_row.organization_id;
365 x_validity_rule_row.prim_prod_item_id :=
366 x_prim_prod_row.inventory_item_id;
367 x_validity_rule_row.prim_prod_item_no :=
368 l_item_row.concatenated_segments;
369 x_validity_rule_row.prim_prod_item_desc1 := l_item_row.description;
370 x_validity_rule_row.prim_prod_item_um := x_prim_prod_row.dtl_um;
371 x_validity_rule_row.prim_prod_effective_qty := x_prim_prod_row.plan_qty;
372
373 --GET PRODUCTION RULES
374 OPEN get_production_rules (x_fpo_header_row.organization_id
375 ,x_prim_prod_row.inventory_item_id);
376
377 FETCH get_production_rules
378 INTO l_get_production_rules;
379
380 IF get_production_rules%FOUND THEN
381 l_pregen_fpo_row.std_qty := l_get_production_rules.std_lot_size;
382 l_pregen_fpo_row.fixed_leadtime :=
383 l_get_production_rules.fixed_lead_time;
384 l_pregen_fpo_row.variable_leadtime :=
385 l_get_production_rules.variable_lead_time;
386 l_pregen_fpo_row.rules_found := 1;
387 ELSE
388 l_pregen_fpo_row.rules_found := 0;
389 END IF;
390
391 l_pregen_fpo_row.batch_size_uom := x_prim_prod_row.dtl_um;
392
393 CLOSE get_production_rules;
394
395 x_pregen_fpo_row := l_pregen_fpo_row;
396 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
397 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
398 END IF;
399 EXCEPTION
400 WHEN no_fetch_material_details THEN
401 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
402 gme_debug.put_line ('no fetch material_details');
403 END IF;
404
405 gme_common_pvt.log_message ('PM_NO_MATL_DTL');
406 x_return_status := fnd_api.g_ret_sts_error;
407 WHEN batch_fetch_error THEN
408 x_return_status := fnd_api.g_ret_sts_error;
409 WHEN OTHERS THEN
410 gme_debug.put_line ('Exception in api ' || g_pkg_name || '.' || l_api_name);
411 x_return_status := fnd_api.g_ret_sts_unexp_error;
412 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO', 'retrieve_fpo_data');
413 END retrieve_fpo_data;
414
415 --*************************************************************
416
417 --*************************************************************
418 PROCEDURE calculate_leadtime (
419 p_pregen_fpo_row IN pregen_fpo_row
420 ,x_pregen_fpo_row OUT NOCOPY pregen_fpo_row
421 ,x_return_status OUT NOCOPY VARCHAR2)
422 IS
423 l_api_name CONSTANT VARCHAR2 (30) := 'CALCULATE_LEADTIME';
424 BEGIN
425 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
426 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
427 || l_api_name);
428 END IF;
429
430 x_pregen_fpo_row := p_pregen_fpo_row;
431 x_return_status := fnd_api.g_ret_sts_success;
432
433 IF ( p_pregen_fpo_row.variable_leadtime = 0
434 OR p_pregen_fpo_row.std_qty = 0) THEN
435 x_pregen_fpo_row.leadtime := p_pregen_fpo_row.fixed_leadtime;
436 ELSE
437 x_pregen_fpo_row.leadtime :=
438 p_pregen_fpo_row.fixed_leadtime
439 + ( p_pregen_fpo_row.variable_leadtime
440 * p_pregen_fpo_row.qty_per_batch
441 / p_pregen_fpo_row.std_qty);
442 END IF;
443 --Bug2804440
444 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
445 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
446 END IF;
447 EXCEPTION
448 WHEN OTHERS THEN
449 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
450 x_return_status := fnd_api.g_ret_sts_unexp_error;
451 --End Bug2804440
452 END calculate_leadtime;
453
454 --*************************************************************
455 --*****************************************************************
456 PROCEDURE generate_pre_batch_header_recs (
457 p_fpo_header_row IN gme_batch_header%ROWTYPE
458 ,p_prim_prod_row IN gme_material_details%ROWTYPE
459 ,p_pregen_fpo_row IN pregen_fpo_row
460 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
461 ,x_return_status OUT NOCOPY VARCHAR2)
462 IS
463 l_api_name CONSTANT VARCHAR2 (30) := 'GENERATE_PRE_BATCH_HEADER_RECS';
464 x_prev_plan_start_date gme_batch_header.plan_start_date%TYPE;
465 x_prev_plan_cmplt_date gme_batch_header.plan_cmplt_date%TYPE;
466 x_batch_leadtime_days NUMBER;
467 x_batch_offset_days NUMBER;
468 x_neg_batch_leadtime_days NUMBER;
469 x_neg_batch_offset_days NUMBER;
470 l_fpo_header_row gme_batch_header%ROWTYPE;
471 l_prim_prod_row gme_material_details%ROWTYPE;
472 l_pregen_fpo_row pregen_fpo_row;
473 l_generated_pre_batch_tab generated_pre_batch_tab;
474 /* Exception definitions */
475 generate_pre_batch_err EXCEPTION;
476 BEGIN
477 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
478 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
479 || l_api_name);
480 END IF;
481
482 l_fpo_header_row := p_fpo_header_row;
483 l_prim_prod_row := p_prim_prod_row;
484 l_pregen_fpo_row := p_pregen_fpo_row;
485
486 --Convert variables expressed in hours to days.
487 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
488 gme_debug.put_line ( 'l_pregen_fpo_row.leadtime = '
489 || TO_CHAR (l_pregen_fpo_row.leadtime) );
490 END IF;
491
492 x_batch_leadtime_days := l_pregen_fpo_row.leadtime / 24;
493
494 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
495 gme_debug.put_line ( 'x_batch_leadtime_days = '
496 || TO_CHAR (x_batch_leadtime_days) );
497 gme_debug.put_line ( 'l_pregen_fpo_row.batch_offset = '
498 || TO_CHAR (l_pregen_fpo_row.batch_offset) );
499 END IF;
500
501 x_batch_offset_days := l_pregen_fpo_row.batch_offset / 24;
502
503 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
504 gme_debug.put_line ( 'x_batch_offset_days = '
505 || TO_CHAR (x_batch_offset_days) );
506 END IF;
507
508 --Negative values of above.
509 x_neg_batch_leadtime_days := x_batch_leadtime_days * -1;
510
511 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
512 gme_debug.put_line ( 'x_neg_batch_leadtime_days = '
513 || TO_CHAR (x_neg_batch_leadtime_days) );
514 END IF;
515
516 x_neg_batch_offset_days := x_batch_offset_days * -1;
517
518 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
519 gme_debug.put_line ( 'x_neg_batch_offset_days = '
520 || TO_CHAR (x_neg_batch_offset_days) );
521 gme_debug.put_line ( 'l_pregen_fpo_row.schedule_method = '
522 || (l_pregen_fpo_row.schedule_method) );
523 END IF;
524 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
525 gme_debug.put_line (g_pkg_name ||
526 '.' ||
527 l_api_name ||
528 ' Begin looping here for count of '||
529 l_pregen_fpo_row.num_batches);
530 END IF;
531
532 FOR i IN 1 .. l_pregen_fpo_row.num_batches LOOP
533 -- KYH GME CONV BEGIN
534 -- l_generated_pre_batch_tab (i).plant_code :=
535 -- l_fpo_header_row.plant_code;
536
537 -- Bug 13696836 - Initialize firmed ind also.
538 l_generated_pre_batch_tab (i).firmed_ind :=
539 l_fpo_header_row.firmed_ind;
540
541 l_generated_pre_batch_tab (i).organization_id :=
542 l_fpo_header_row.organization_id;
543 -- KYH GME CONV END
544 l_generated_pre_batch_tab (i).batch_type := 0;
545 l_generated_pre_batch_tab (i).fpo_id := l_fpo_header_row.batch_id;
546
547 -- Bug 13396879 - copy flex field data.
548 l_generated_pre_batch_tab (i).attribute_category := l_fpo_header_row.attribute_category;
549
550 l_generated_pre_batch_tab (i).attribute1 := l_fpo_header_row.attribute1;
551 l_generated_pre_batch_tab (i).attribute2 := l_fpo_header_row.attribute2;
552 l_generated_pre_batch_tab (i).attribute3 := l_fpo_header_row.attribute3;
553 l_generated_pre_batch_tab (i).attribute4 := l_fpo_header_row.attribute4;
554 l_generated_pre_batch_tab (i).attribute5 := l_fpo_header_row.attribute5;
555 l_generated_pre_batch_tab (i).attribute6 := l_fpo_header_row.attribute6;
556 l_generated_pre_batch_tab (i).attribute7 := l_fpo_header_row.attribute7;
557 l_generated_pre_batch_tab (i).attribute8 := l_fpo_header_row.attribute8;
558 l_generated_pre_batch_tab (i).attribute9 := l_fpo_header_row.attribute9;
559 l_generated_pre_batch_tab (i).attribute10 := l_fpo_header_row.attribute10;
560
561 l_generated_pre_batch_tab (i).attribute11 := l_fpo_header_row.attribute11;
562 l_generated_pre_batch_tab (i).attribute12 := l_fpo_header_row.attribute12;
563 l_generated_pre_batch_tab (i).attribute13 := l_fpo_header_row.attribute13;
564 l_generated_pre_batch_tab (i).attribute14 := l_fpo_header_row.attribute14;
565 l_generated_pre_batch_tab (i).attribute15 := l_fpo_header_row.attribute15;
566 l_generated_pre_batch_tab (i).attribute16 := l_fpo_header_row.attribute16;
567 l_generated_pre_batch_tab (i).attribute17 := l_fpo_header_row.attribute17;
568 l_generated_pre_batch_tab (i).attribute18 := l_fpo_header_row.attribute18;
569 l_generated_pre_batch_tab (i).attribute19 := l_fpo_header_row.attribute19;
570 l_generated_pre_batch_tab (i).attribute20 := l_fpo_header_row.attribute20;
571
572 l_generated_pre_batch_tab (i).attribute21 := l_fpo_header_row.attribute21;
573 l_generated_pre_batch_tab (i).attribute22 := l_fpo_header_row.attribute22;
574 l_generated_pre_batch_tab (i).attribute23 := l_fpo_header_row.attribute23;
575 l_generated_pre_batch_tab (i).attribute24 := l_fpo_header_row.attribute24;
576 l_generated_pre_batch_tab (i).attribute25 := l_fpo_header_row.attribute25;
577 l_generated_pre_batch_tab (i).attribute26 := l_fpo_header_row.attribute26;
578 l_generated_pre_batch_tab (i).attribute27 := l_fpo_header_row.attribute27;
579 l_generated_pre_batch_tab (i).attribute28 := l_fpo_header_row.attribute28;
580 l_generated_pre_batch_tab (i).attribute29 := l_fpo_header_row.attribute29;
581 l_generated_pre_batch_tab (i).attribute30 := l_fpo_header_row.attribute30;
582
583 l_generated_pre_batch_tab (i).attribute31 := l_fpo_header_row.attribute31;
584 l_generated_pre_batch_tab (i).attribute32 := l_fpo_header_row.attribute32;
585 l_generated_pre_batch_tab (i).attribute33 := l_fpo_header_row.attribute33;
586 l_generated_pre_batch_tab (i).attribute34 := l_fpo_header_row.attribute34;
587 l_generated_pre_batch_tab (i).attribute35 := l_fpo_header_row.attribute35;
588 l_generated_pre_batch_tab (i).attribute36 := l_fpo_header_row.attribute36;
589 l_generated_pre_batch_tab (i).attribute37 := l_fpo_header_row.attribute37;
590 l_generated_pre_batch_tab (i).attribute38 := l_fpo_header_row.attribute38;
591 l_generated_pre_batch_tab (i).attribute39 := l_fpo_header_row.attribute39;
592 l_generated_pre_batch_tab (i).attribute40 := l_fpo_header_row.attribute40;
593
594 -- Bug 3258483 Mohit Kapoor
595 /* Added the assignment of wip_whse_code so that correct wip_whse_code is saved to batch */
596 -- l_generated_pre_batch_tab (i).wip_whse_code := l_fpo_header_row.wip_whse_code;
597 IF (i = 1) THEN
598 --first batch
599 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
600 --First batch/Scheduling FORWARD
601 l_generated_pre_batch_tab (i).plan_start_date :=
602 l_pregen_fpo_row.plan_start_date;
603 l_generated_pre_batch_tab (i).plan_cmplt_date :=
604 l_pregen_fpo_row.plan_start_date + x_batch_leadtime_days;
605 ELSE
606 --First Batch/Scheduling BACKWARD
607 l_generated_pre_batch_tab (i).plan_cmplt_date :=
608 l_pregen_fpo_row.plan_cmplt_date;
609 l_generated_pre_batch_tab (i).plan_start_date :=
610 l_pregen_fpo_row.plan_cmplt_date
611 + x_neg_batch_leadtime_days;
612 END IF;
613
614 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
615 gme_debug.put_line
616 ( 'l_generated_pre_batch_tab(i).plan_start_date = '
617 || TO_CHAR
618 (l_generated_pre_batch_tab (i).plan_start_date
619 ,'MM/DD/YYYY hh24:mi:ss') );
620 gme_debug.put_line
621 ( 'l_generated_pre_batch_tab(i).plan_cmplt_date = '
622 || TO_CHAR
623 (l_generated_pre_batch_tab (i).plan_cmplt_date
624 ,'MM/DD/YYYY hh24:mi:ss') );
625 END IF;
626 ELSE
627 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
628 --Not First Batch; Scheduling FORWARD
629 IF (l_pregen_fpo_row.offset_type = 0) THEN
630 --Start-to-Start
631 x_prev_plan_start_date :=
632 l_generated_pre_batch_tab (i - 1).plan_start_date;
633
634 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
635 gme_debug.put_line ( 'x_prev_plan_start_date = '
636 || TO_CHAR (x_prev_plan_start_date
637 ,'MM/DD/YYYY hh24:mi:ss') );
638 END IF;
639
640 l_generated_pre_batch_tab (i).plan_start_date :=
641 x_prev_plan_start_date;
642
643 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
644 gme_debug.put_line
645 ( 'l_generated_pre_batch_tab(i).plan_start_date = '
646 || TO_CHAR
647 (l_generated_pre_batch_tab (i).plan_start_date
648 ,'MM/DD/YYYY hh24:mi:ss') );
649 END IF;
650 ELSE
651 --End-to-Start
652 x_prev_plan_cmplt_date :=
653 l_generated_pre_batch_tab (i - 1).plan_cmplt_date;
654 l_generated_pre_batch_tab (i).plan_start_date :=
655 x_prev_plan_cmplt_date;
656 END IF;
657
658 l_generated_pre_batch_tab (i).plan_start_date :=
659 l_generated_pre_batch_tab (i).plan_start_date
660 + x_batch_offset_days;
661
662 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
663 gme_debug.put_line
664 ( 'W/OFFSET l_generated_pre_batch_tab(i).plan_start_date = '
665 || TO_CHAR
666 (l_generated_pre_batch_tab (i).plan_start_date
667 ,'MM/DD/YYYY hh24:mi:ss ') );
668 END IF;
669
670 l_generated_pre_batch_tab (i).plan_cmplt_date :=
671 l_generated_pre_batch_tab (i).plan_start_date
672 + x_batch_leadtime_days;
673
674 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
675 gme_debug.put_line
676 ( 'W/LEADTIME l_generated_pre_batch_tab(i).plan_cmplt_date = '
677 || TO_CHAR
678 (l_generated_pre_batch_tab (i).plan_cmplt_date
679 ,'MM/DD/YYYY hh24:mi:ss') );
680 END IF;
681 ELSIF (l_pregen_fpo_row.schedule_method = 'BACKWARD') THEN
682 --Not First Batch; Scheduling BACKWARD
683 IF (l_pregen_fpo_row.offset_type = 0) THEN
684 --Start-to-Start
685 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
686 gme_debug.put_line ('BACKWARD scheduling start-to-start');
687 END IF;
688
689 x_prev_plan_start_date :=
690 l_generated_pre_batch_tab (i - 1).plan_start_date;
691
692 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
693 gme_debug.put_line ( 'x_prev_plan_start_date = '
694 || TO_CHAR (x_prev_plan_start_date
695 ,'MM/DD/YYYY hh24:mi:ss') );
696 END IF;
697
698 l_generated_pre_batch_tab (i).plan_start_date :=
699 x_prev_plan_start_date + x_neg_batch_offset_days;
700
701 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
702 gme_debug.put_line
703 ( 'W/NEG OFFSET l_generated_pre_batch_tab(i).plan_start_date = '
704 || TO_CHAR
705 (l_generated_pre_batch_tab (i).plan_start_date
706 ,'MM/DD/YYYY hh24:mi:ss') );
707 END IF;
708
709 l_generated_pre_batch_tab (i).plan_cmplt_date :=
710 l_generated_pre_batch_tab (i).plan_start_date
711 + x_batch_leadtime_days;
712
713 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
714 gme_debug.put_line
715 ( 'W/LEADTIME l_generated_pre_batch_tab(i).plan_cmplt_date = '
716 || TO_CHAR
717 (l_generated_pre_batch_tab (i).plan_cmplt_date
718 ,'MM/DD/YYYY hh24:mi:ss') );
719 END IF;
720 ELSE
721 --End-to-Start
722 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
723 gme_debug.put_line ('BACKWARD scheduling end-to-start');
724 END IF;
725
726 x_prev_plan_start_date :=
727 l_generated_pre_batch_tab (i - 1).plan_start_date;
728
729 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
730 gme_debug.put_line ( 'x_prev_plan_start_date = '
731 || TO_CHAR (x_prev_plan_start_date
732 ,'MM/DD/YYYY hh24:mi:ss') );
733 END IF;
734
735 l_generated_pre_batch_tab (i).plan_cmplt_date :=
736 x_prev_plan_start_date + x_neg_batch_offset_days;
737
738 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
739 gme_debug.put_line
740 ( 'W/NEG OFFSET l_generated_pre_batch_tab(i).plan_cmplt_date = '
741 || TO_CHAR
742 (l_generated_pre_batch_tab (i).plan_cmplt_date
743 ,'MM/DD/YYYY hh24:mi:ss') );
744 END IF;
745
746 l_generated_pre_batch_tab (i).plan_start_date :=
747 l_generated_pre_batch_tab (i).plan_cmplt_date
748 + x_neg_batch_leadtime_days;
749
750 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
751 gme_debug.put_line
752 ( 'W/NEG LEADTIME l_generated_pre_batch_tab(i).plan_start_date = '
753 || TO_CHAR
754 (l_generated_pre_batch_tab (i).plan_start_date
755 ,'MM/DD/YYYY hh24:mi:ss') );
756 END IF;
757 END IF;
758 END IF;
759 END IF;
760
761 l_generated_pre_batch_tab (i).due_date :=
762 l_generated_pre_batch_tab (i).plan_cmplt_date;
763
764 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
765 gme_debug.put_line
766 ( 'l_generated_pre_batch_tab(i).due_date= '
767 || TO_CHAR
768 (l_generated_pre_batch_tab (i).due_date
769 ,'MM/DD/YYYY hh24:mi:ss') );
770 END IF;
771
772 x_generated_pre_batch_tab (i) := l_generated_pre_batch_tab (i);
773 x_return_status := fnd_api.g_ret_sts_success;
774 END LOOP;
775
776 IF x_return_status <> fnd_api.g_ret_sts_success THEN
777 RAISE generate_pre_batch_err;
778 END IF;
779
780 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
781 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
782 END IF;
783 EXCEPTION
784 WHEN generate_pre_batch_err THEN
785 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
786 gme_debug.put_line
787 ('problem generateing the pre_batch-header recs');
788 END IF;
789
790 gme_common_pvt.log_message ('GEN_PRE_BATCH_HEADER_ERR');
791 x_return_status := fnd_api.g_ret_sts_error;
792 WHEN OTHERS THEN
793 x_return_status := fnd_api.g_ret_sts_unexp_error;
794 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
795 ,'generate_pre_batch_header_recs');
796 END generate_pre_batch_header_recs;
797
798 --****************************************************************
799 --****************************************************************
800 PROCEDURE convert_fpo_to_batch (
801 p_generated_pre_batch_tab IN generated_pre_batch_tab
802 ,p_recipe_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
803 ,p_pregen_fpo_row IN pregen_fpo_row
804 ,x_generated_pre_batch_tab OUT NOCOPY generated_pre_batch_tab
805 ,x_return_status OUT NOCOPY VARCHAR2
806 ,x_arr_rtn_sts OUT NOCOPY return_array_sts
807 ,p_process_row IN NUMBER DEFAULT 0
808 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
809 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
810 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
811 ,p_fpo_validity_rule_id IN NUMBER --BUG#3185748 Sastry
812 )
813 IS
814 l_api_name CONSTANT VARCHAR2 (30) := 'CONVERT_FPO_TO_BATCH';
815 dummy NUMBER;
816 l_generated_pre_batch_tab generated_pre_batch_tab;
817 l_pre_batch_row gme_batch_header%ROWTYPE;
818 l_in_pre_batch_row gme_batch_header%ROWTYPE;
819 l_recipe_validity_rule_tab gme_common_pvt.recipe_validity_rule_tab;
820 l_pregen_fpo_row pregen_fpo_row;
821 l_exception_material_tbl gme_common_pvt.exceptions_tab;
822 l_return_status VARCHAR2 (1);
823 l_api_version NUMBER;
824 l_validation_level NUMBER := 100;
825 l_batch_size gme_material_details.plan_qty%TYPE;
826 l_batch_uom gme_material_details.dtl_um%TYPE;
827 l_creation_mode VARCHAR2 (10);
828 l_om_gme_batch_rec gml_batch_om_util.batch_line_rec;
829 -- Bug 3185748 Added variables, validation_failure and cursor get_validity_rule .
830 l_inventory_item_id NUMBER; -- KYH GME CONV
831 l_item_um VARCHAR2 (3);
832 /* Exception definitions */
833 batch_creation_failure EXCEPTION;
834 validation_failure EXCEPTION;
835 --Swapna Kommineni bug#3565971 13/08/2004 Added variables to store the start and end dates
836 --calculated for the previous batch.
837 l_prev_start_date DATE;
838 l_prev_cmplt_date DATE;
839 l_msg_count NUMBER;
840 l_msg_data VARCHAR2(300);
841
842
843 /* Cusror definitions */
844 --KYH GME Convergence - replace ic_item_mst with mtl_system_items
845 --Remember that organization_id can be null in gmd_recipe_validity_rules
846 Cursor get_validity_rule (v_validity_rule_id NUMBER,v_organization_id NUMBER) IS
847 SELECT v.inventory_item_id, i.primary_uom_code
848 FROM gmd_recipe_validity_rules v, mtl_system_items i
849 WHERE recipe_validity_rule_id = v_validity_rule_id
850 AND i.organization_id = v_organization_id
851 AND v.inventory_item_id = i.inventory_item_id;
852 BEGIN
853 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
854 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
855 || l_api_name);
856 END IF;
857 x_return_status := fnd_api.g_ret_sts_success;
858 dummy := 0;
859 l_validation_level := gme_common_pvt.g_max_errors;
860 l_api_version := 2.0;
861 l_generated_pre_batch_tab := p_generated_pre_batch_tab;
862 l_recipe_validity_rule_tab := p_recipe_validity_rule_tab;
863 l_pregen_fpo_row := p_pregen_fpo_row;
864 l_batch_size := l_pregen_fpo_row.qty_per_batch;
865 -- change for item_uom for bug 2398719
866 l_batch_uom := l_pregen_fpo_row.batch_size_uom;
867 --Swapna Kommineni bug#3565971 13/08/2004 Initialization of variables to NULL
868 l_prev_start_date := NULL;
869 l_prev_cmplt_date := NULL;
870 l_creation_mode := 'PRODUCT';
871
872 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
873 gme_debug.put_line (g_pkg_name ||
874 '.' ||
875 l_api_name ||
876 'Batch UOM is '||
877 l_batch_uom);
878 END IF;
879
880 --Populate generated_pre_batch_tab with validity_rule.
881 IF (p_process_row = 0) THEN
882 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
883 gme_debug.put_line (g_pkg_name ||
884 '.' ||
885 l_api_name ||
886 ' p_process_row is set to 0 ');
887 END IF;
888 FOR i IN 1 .. p_generated_pre_batch_tab.COUNT LOOP
889 -- Bug 3185748 Added Begin .. End
890 BEGIN
891 gme_common_pvt.g_error_count := 0;
892 l_generated_pre_batch_tab (i).recipe_validity_rule_id :=
893 l_recipe_validity_rule_tab (i).recipe_validity_rule_id;
894 -- Populate header row with values of generated_pre_batch_tab row.
895 l_pre_batch_row := l_generated_pre_batch_tab (i);
896 -- Create batches from the generate_pre_batch_tab.
897 -- Pawan Kumar changed the call to private layer from the public layer for creat_batch
898 l_in_pre_batch_row := l_pre_batch_row;
899 --BEGIN BUG#3185748 Sastry
900 SAVEPOINT create_batch;
901
902 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
903 gme_debug.put_line (g_pkg_name ||
904 '.' ||
905 l_api_name ||
906 ' Retrieve validity_rule for id '||
907 p_fpo_validity_rule_id||
908 ' organization_id is '||
909 l_in_pre_batch_row.organization_id);
910 END IF;
911 OPEN get_validity_rule (p_fpo_validity_rule_id,l_in_pre_batch_row.organization_id);
912
913 FETCH Get_validity_rule INTO l_inventory_item_id, l_item_um; -- KYH GME CONV
914
915 IF get_validity_rule%NOTFOUND THEN
916 gme_common_pvt.log_message
917 ('GME_VALIDITY_RULE_RETRIEVE_ERR');
918
919 CLOSE get_validity_rule;
920
921 RAISE validation_failure;
922 END IF;
923
924 CLOSE get_validity_rule;
925
926 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
927 gme_debug.put_line (g_pkg_name ||
928 '.' ||
929 l_api_name ||
930 ' Invoke validate validity rule ');
931 END IF;
932 IF NOT gme_common_pvt.validate_validity_rule
933 (p_validity_rule_id => l_in_pre_batch_row.recipe_validity_rule_id
934 ,p_organization_id => l_in_pre_batch_row.organization_id
935 ,p_prim_product_id => l_inventory_item_id
936 ,p_qty => l_batch_size
937 ,p_uom => l_item_um
938 ,p_object_type => 'P'
939 ,p_start_date => l_in_pre_batch_row.plan_start_date
940 ,p_cmplt_date => l_in_pre_batch_row.plan_cmplt_date
941 ,p_creation_mode => 'PRODUCT') THEN
942 RAISE validation_failure;
943 END IF;
944
945 --END BUG#3185748
946 --Rishi Varma bug#3460631 22/03/04
947 --assigned null to the due date as the due date should be
948 --defaulted to the planned completion date.
949 l_in_pre_batch_row.due_date := NULL;
950
951 --Swapna Kommineni bug#3565971 13/08/2004 Start
952 /*This will recalculate the start and end dates for the second batch onwards depending on
953 the calculated completed or start dates of the first batch only in the case leadtime is
954 not given.In the case leadtime is given the correct dates will be calculated from
955 generate_pre_batch_header_recs*/
956 IF ( l_prev_cmplt_date IS NOT NULL
957 AND l_prev_start_date IS NOT NULL
958 AND l_pregen_fpo_row.leadtime = 0
959 AND l_pregen_fpo_row.offset_type = 1) THEN
960 IF (l_pregen_fpo_row.schedule_method = 'FORWARD') THEN
961 l_in_pre_batch_row.plan_start_date :=
962 l_prev_cmplt_date
963 + NVL (l_pregen_fpo_row.batch_offset, 0) / 24;
964 l_in_pre_batch_row.plan_cmplt_date := NULL;
965 ELSIF (l_pregen_fpo_row.schedule_method = 'BACKWARD') THEN
966 l_in_pre_batch_row.plan_cmplt_date :=
967 l_prev_start_date
968 - NVL (l_pregen_fpo_row.batch_offset, 0) / 24;
969 l_in_pre_batch_row.plan_start_date := NULL;
970 END IF;
971 END IF;
972
973 --Swapna Kommineni bug#3565971 13/08/2004 End
974 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
975 gme_debug.put_line (g_pkg_name ||
976 '.' ||
977 l_api_name ||
978 ' about to invoke create_batch ');
979 END IF;
980 gme_create_batch_pvt.create_batch
981 (p_validation_level => l_validation_level
982 ,x_return_status => l_return_status
983 ,p_batch_header_rec => l_in_pre_batch_row
984 ,x_batch_header_rec => l_pre_batch_row
985 ,p_batch_size => l_batch_size
986 ,p_batch_size_uom => l_batch_uom
987 ,p_creation_mode => l_creation_mode
988 ,p_ignore_qty_below_cap => 'T'
989 ,p_use_workday_cal => p_use_shop_cal
990 ,p_contiguity_override => p_contiguity_override
991 ,x_exception_material_tbl => l_exception_material_tbl);
992
993 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
994 gme_debug.put_line (g_pkg_name ||
995 '.' ||
996 l_api_name ||
997 ' create_batch returns status of '||
998 l_return_status );
999 END IF;
1000 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1001 x_arr_rtn_sts (i).return_status := l_return_status;
1002
1003 -- Pawan Kumar ADDED code for bug 2398719
1004 IF l_return_status NOT IN (gme_common_pvt.g_inv_short_err) THEN
1005 RAISE batch_creation_failure;
1006 ELSE
1007 FOR j IN 1 .. l_exception_material_tbl.COUNT LOOP
1008 x_exception_material_tbl (i) :=
1009 l_exception_material_tbl (j);
1010 END LOOP;
1011 END IF;
1012 END IF; /* l_return_status <> FND_API.G_RET_STS_SUCCESS */
1013
1014 --Swapna Kommineni bug#3565971 13/08/2004 Assigning the planned start and end dates of
1015 --batch that are calculated from the create_batch api
1016 l_prev_start_date := l_pre_batch_row.plan_start_date;
1017 l_prev_cmplt_date := l_pre_batch_row.plan_cmplt_date;
1018 x_generated_pre_batch_tab (i) := l_pre_batch_row;
1019 -- B3140274 OM-GME integration - CONVERT for convert FPO to batch
1020 -- B3194346 OM-GME integration - call to central routine
1021
1022 /* GME_TRANS_ENGINE_PVT.inform_OM
1023 ( p_action => 'CONVERT'
1024 , p_trans_id => NULL
1025 , p_trans_id_reversed => NULL
1026 , p_gme_batch_hdr => l_pre_batch_row
1027 , p_gme_matl_dtl => NULL
1028 ); */
1029 GME_SUPPLY_RES_PVT.create_reservation_from_FPO
1030 ( P_FPO_batch_id => l_pre_batch_row.fpo_id
1031 , P_New_batch_id => l_pre_batch_row.batch_id
1032 , X_return_status => x_return_status
1033 , X_msg_count => l_msg_count
1034 , X_msg_data => l_msg_data
1035 );
1036
1037 --BEGIN BUG#3185748 Sastry
1038 EXCEPTION
1039 WHEN batch_creation_failure OR validation_failure THEN
1040 x_return_status := fnd_api.g_ret_sts_error;
1041 ROLLBACK TO SAVEPOINT create_batch;
1042
1043 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1044 gme_debug.put_line ('batch creation_fail');
1045 END IF;
1046 END;
1047 --END BUG#3185748
1048 END LOOP;
1049 ELSE /* IF (p_process_row = 0) */
1050 l_generated_pre_batch_tab (p_process_row).recipe_validity_rule_id :=
1051 l_recipe_validity_rule_tab (p_process_row).recipe_validity_rule_id;
1052 -- Populate header row with values of generated_pre_batch_tab row.
1053 l_pre_batch_row := l_generated_pre_batch_tab (p_process_row);
1054 -- Create batches from the generate_pre_batch_tab.
1055 -- Pawan Kumar changed the call to private layer from public API
1056 l_in_pre_batch_row := l_pre_batch_row;
1057 gme_create_batch_pvt.create_batch
1058 (p_validation_level => l_validation_level
1059 ,x_return_status => l_return_status
1060 ,p_batch_header_rec => l_in_pre_batch_row
1061 ,x_batch_header_rec => l_pre_batch_row
1062 ,p_batch_size => l_batch_size
1063 ,p_batch_size_uom => l_batch_uom
1064 ,p_creation_mode => l_creation_mode
1065 ,p_ignore_qty_below_cap => 'T'
1066 ,p_use_workday_cal => p_use_shop_cal
1067 ,p_contiguity_override => p_contiguity_override
1068 ,x_exception_material_tbl => l_exception_material_tbl);
1069
1070 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1071 gme_debug.put_line (g_pkg_name ||
1072 '.' ||
1073 l_api_name ||
1074 ' call to create_batch returns status of '||
1075 l_return_status||
1076 ' current return status '||
1077 x_return_status);
1078 END IF;
1079 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1080 x_arr_rtn_sts (p_process_row).return_status := l_return_status;
1081
1082 IF l_return_status NOT IN (gme_common_pvt.g_inv_short_err) THEN
1083 RAISE batch_creation_failure;
1084 END IF;
1085 END IF;
1086
1087 x_generated_pre_batch_tab (p_process_row) := l_pre_batch_row;
1088 -- B3140274 OM-GME integration - CONVERT for convert FPO to batch
1089 -- B3194346 OM-GME integration - call to central routine
1090
1091 /* GME_TRANS_ENGINE_PVT.inform_OM
1092 ( p_action => 'CONVERT'
1093 , p_trans_id => NULL
1094 , p_trans_id_reversed => NULL
1095 , p_gme_batch_hdr => l_pre_batch_row
1096 , p_gme_matl_dtl => NULL
1097 ); */
1098 GME_SUPPLY_RES_PVT.create_reservation_from_FPO
1099 ( P_FPO_batch_id => l_pre_batch_row.fpo_id
1100 , P_New_batch_id => l_pre_batch_row.batch_id
1101 , X_return_status => x_return_status
1102 , X_msg_count => l_msg_count
1103 , X_msg_data => l_msg_data
1104 );
1105
1106 END IF; /* IF (p_process_row = 0) */
1107 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1108 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name ||' with status '||x_return_status);
1109 END IF;
1110 EXCEPTION
1111 WHEN batch_creation_failure THEN
1112 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1113 gme_debug.put_line ('batch creation_fail');
1114 END IF;
1115
1116 x_return_status := fnd_api.g_ret_sts_error;
1117
1118 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1119 gme_debug.put_line ('x_return_status = ' || x_return_status);
1120 END IF;
1121 WHEN OTHERS THEN
1122 x_return_status := fnd_api.g_ret_sts_unexp_error;
1123 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
1124 ,'CONVERT_FPO_TO_BATCH');
1125 END convert_fpo_to_batch;
1126
1127 --*********************************************************
1128 --*********************************************************
1129 PROCEDURE update_original_fpo (
1130 p_fpo_header_row IN gme_batch_header%ROWTYPE
1131 ,p_prim_prod_row IN gme_material_details%ROWTYPE
1132 ,p_pregen_fpo_row IN pregen_fpo_row
1133 ,p_fpo_material_details_tab IN fpo_material_details_tab
1134 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
1135 ,x_fpo_header_row OUT NOCOPY gme_batch_header%ROWTYPE
1136 ,x_return_status OUT NOCOPY VARCHAR2)
1137 IS
1138 CURSOR get_phantom_fpo (v_batch_id NUMBER)
1139 IS
1140 SELECT phantom_id
1141 FROM gme_material_details
1142 WHERE batch_id = v_batch_id AND phantom_id IS NOT NULL;
1143
1144 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ORIGINAL_FPO';
1145 l_fpo_header_row gme_batch_header%ROWTYPE;
1146 l_prim_prod_row gme_material_details%ROWTYPE;
1147 l_pregen_fpo_row pregen_fpo_row;
1148 l_fpo_material_details_tab fpo_material_details_tab;
1149 l_over_allocations gme_common_pvt.exceptions_tab;
1150 l_tran_row gme_inventory_txns_gtmp%ROWTYPE;
1151 -- GME CONV l_tran_tab gme_common_pvt.transactions_tab;
1152 l_batch_header gme_batch_header%ROWTYPE;
1153 x_batch_header gme_batch_header%ROWTYPE;
1154 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
1155 l_resource_tab gme_common_pvt.resource_transactions_tab;
1156 -- l_batch_type ic_tran_pnd.doc_type%TYPE;
1157 l_scale_factor NUMBER;
1158 l_primaries VARCHAR2 (8);
1159 l_batch_status gme_batch_header.batch_status%TYPE;
1160 l_mat_row_count NUMBER;
1161 l_rsc_row_count NUMBER;
1162 l_return_status VARCHAR2 (1);
1163 l_return BOOLEAN;
1164 l_exception_material_tbl gme_common_pvt.exceptions_tab;
1165
1166
1167 /*Exceptions */
1168 load_rsrc_trans_err EXCEPTION;
1169 gme_fetch_all_trans_err EXCEPTION;
1170 update_row_err EXCEPTION;
1171 delete_pending_trans_err EXCEPTION;
1172 scale_batch_err EXCEPTION;
1173 update_pending_trans EXCEPTION;
1174 cancel_batch_err EXCEPTION;
1175 fetch_batch_err EXCEPTION;
1176 -- Bug 14348159
1177 reschedule_batch_fail EXCEPTION;
1178
1179 BEGIN
1180 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1181 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1182 || l_api_name);
1183 END IF;
1184 x_return_status := fnd_api.g_ret_sts_success;
1185
1186 l_fpo_header_row := p_fpo_header_row;
1187 l_batch_status := l_fpo_header_row.batch_status;
1188 l_fpo_material_details_tab := p_fpo_material_details_tab;
1189 l_primaries := 'OUTPUTS';
1190 l_prim_prod_row := p_prim_prod_row;
1191 l_pregen_fpo_row := p_pregen_fpo_row;
1192 -- Working with the Pending Transactions of the original FPO.
1193 --
1194 -- Load pending transactions for FPO from ic_tran_pnd to
1195 -- gme temp table. Will have to work
1196 -- with pending transactions.
1197 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_fpo_header_row
1198 ,x_rsc_row_count => l_rsc_row_count
1199 ,x_return_status => l_return_status);
1200
1201 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1202 RAISE load_rsrc_trans_err;
1203 END IF;
1204
1205 --Fetch all Pending transactions for original FPO
1206 --into local table.
1207 /* GME CONV
1208 l_tran_row.doc_id := l_fpo_header_row.batch_id;
1209 l_tran_row.doc_type := 'FPO';
1210 gme_trans_engine_pvt.fetch_all_trans (
1211 l_tran_row,
1212 l_tran_tab,
1213 l_return_status
1214 );
1215
1216 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1217 RAISE gme_fetch_all_trans_err;
1218 END IF;
1219 GME CONV */
1220
1221 -- Determine whether sum of effective qtys inserted is
1222 -- less than the original FPO quantity.
1223 -- If less than the original FPO qty
1224 -- scale the original FPO and all pending transactions to the
1225 -- difference.
1226 -- If greater than or equal to the original FPO qty, set FPO status
1227 -- to converted and delete pending transactions.
1228 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1229 gme_debug.put_line ( 'l_prim_prod_row.plan_qty = '
1230 || TO_CHAR (l_prim_prod_row.plan_qty) );
1231 gme_debug.put_line ( 'l_pregen_fpo_row.sum_eff_qty = '
1232 || TO_CHAR (l_pregen_fpo_row.sum_eff_qty) );
1233 END IF;
1234
1235 IF (l_prim_prod_row.plan_qty <= l_pregen_fpo_row.sum_eff_qty) THEN
1236 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1237 gme_debug.put_line
1238 ('in IF.gonna update header and delete pending trans');
1239 END IF;
1240
1241 --Update FPO status to CONVERTED
1242 l_fpo_header_row.batch_status := -3;
1243 l_return := gme_batch_header_dbl.update_row (l_fpo_header_row);
1244
1245 IF NOT l_return THEN
1246 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1247 gme_debug.put_line ('l_return from update_row = FALSE');
1248 END IF;
1249
1250 RAISE update_row_err;
1251 ELSE
1252 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1253 gme_debug.put_line ('l_return from update_row = TRUE');
1254 END IF;
1255 END IF;
1256
1257 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1258 gme_debug.put_line ( 'l_fpo_header_row.batch_status = '
1259 || TO_CHAR (l_fpo_header_row.batch_status) );
1260 gme_debug.put_line ( 'l_fpo_header_row.batch_id = '
1261 || TO_CHAR (l_fpo_header_row.batch_id) );
1262 gme_debug.put_line ( 'l_fpo_header_row.batch_type = '
1263 || TO_CHAR (l_fpo_header_row.batch_type) );
1264 END IF;
1265
1266 --FPBug#4941012 set plan qty to zero for parent FPO
1267 UPDATE gme_material_details
1268 SET plan_qty = 0
1269 WHERE batch_id = l_fpo_header_row.batch_id;
1270
1271 --Gonna delete the pending transactions associated with orig FPO
1272 /* GME CONV
1273
1274 FOR i IN 1 .. l_tran_tab.COUNT
1275 LOOP
1276 l_tran_row := l_tran_tab (i);
1277 l_tran_row.alloc_qty := 0;
1278 l_tran_row.trans_qty := 0;
1279 l_tran_row.trans_qty2 := 0;
1280 gme_trans_engine_pvt.update_pending_trans (
1281 l_tran_row,
1282 l_return_status
1283 );
1284
1285 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1286 RAISE update_pending_trans;
1287 END IF;
1288 END LOOP;
1289 GME CONV */
1290
1291 --Fetch all resource transactions for original FPO
1292 --into local table.
1293 l_resource_txns.doc_id := l_fpo_header_row.batch_id;
1294 gme_resource_engine_pvt.fetch_active_resources
1295 (p_resource_rec => l_resource_txns
1296 ,x_resource_tbl => l_resource_tab
1297 ,x_return_status => l_return_status);
1298
1299 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1300 RAISE gme_fetch_all_trans_err;
1301 END IF; /* IF l_return_status <> x_return_status */
1302
1303 FOR i IN 1 .. l_resource_tab.COUNT LOOP
1304 l_resource_txns := l_resource_tab (i);
1305 /* Bug 2376315 - Thomas Daniel */
1306 /* Commented the following code for updating the resource usage to zero and added */
1307 /* code to delete the resource transactions */
1308
1309 /*l_resource_txns.resource_usage := 0;
1310 GME_RESOURCE_ENGINE_PVT.update_resource_trans (p_tran_row => l_resource_txns
1311 ,x_return_status => l_return_status);*/
1312 gme_resource_engine_pvt.delete_resource_trans
1313 (p_tran_rec => l_resource_txns
1314 ,x_return_status => l_return_status);
1315
1316 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1317 RAISE update_row_err;
1318 END IF;
1319 END LOOP; /* FOR i IN 1..l_resource_tab.COUNT */
1320
1321 -- Add code for canel phantom fpo
1322 -- pk_fpo('in fpo-before cancel'||l_fpo_header_row.batch_id);
1323 FOR l_rec IN get_phantom_fpo (l_fpo_header_row.batch_id) LOOP
1324 l_batch_header.batch_id := l_rec.phantom_id;
1325
1326 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1327 gme_debug.put_line ( 'phantom to cancel = '
1328 || TO_CHAR (l_batch_header.batch_id) );
1329 END IF;
1330
1331 --FPBug#4941012
1332 IF NOT gme_batch_header_dbl.fetch_row(l_batch_header,
1333 l_batch_header) THEN
1334 RAISE fetch_batch_err;
1335 END IF;
1336
1337 gme_cancel_batch_pvt.cancel_batch
1338 (p_batch_header_rec => l_batch_header
1339 ,x_batch_header_rec => x_batch_header
1340 ,x_return_status => x_return_status);
1341
1342 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1343 RAISE cancel_batch_err;
1344 END IF;
1345 END LOOP;
1346 ELSE
1347 --Scale FPO material details line
1348 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1349 gme_debug.put_line ('gonna do some scaling');
1350 END IF;
1351
1352 l_scale_factor :=
1353 (l_prim_prod_row.plan_qty - l_pregen_fpo_row.sum_eff_qty)
1354 / l_prim_prod_row.plan_qty;
1355
1356 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1357 gme_debug.put_line ('l_scale_factor = ' || l_scale_factor);
1358 END IF;
1359
1360 --p_qty_type is default 1 for the plan qty only
1361 /* l_over_allocations parameter is used to handle default lot going */
1362 /* negative cases, which would not be a case here as their are no */
1363 /* actual allocations against the FPO */
1364 gme_scale_batch_pvt.scale_batch
1365 (p_batch_header_rec => l_fpo_header_row
1366 ,p_scale_factor => l_scale_factor
1367 ,p_primaries => l_primaries
1368 ,p_qty_type => 1
1369 ,p_validity_rule_id => NULL
1370 ,p_enforce_vldt_check => 'F'
1371 ,p_use_workday_cal => 'F'
1372 ,p_contiguity_override => 'T'
1373 -- ,p_recalc_dates => 'T' -- 14348159 - reschedule here instead
1374 ,p_recalc_dates => 'F'
1375 ,x_return_status => l_return_status
1376 ,x_batch_header_rec => x_fpo_header_row
1377 ,x_exception_material_tbl => l_exception_material_tbl);
1378
1379 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1380 RAISE scale_batch_err;
1381 END IF;
1382
1383 -- Bug 14348159
1384 -- Call reschedule_batch for plan_cmplt_date for the fpo.
1385 l_fpo_header_row := x_fpo_header_row ;
1386 l_fpo_header_row.plan_start_date := NULL ;
1387
1388 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1389 gme_debug.put_line('calling reschedule_batch'||l_fpo_header_row.batch_id);
1390 END IF;
1391
1392 IF l_fpo_header_row.parentline_id IS NULL THEN
1393
1394 gme_reschedule_batch_pvt.reschedule_batch
1395 (p_batch_header_rec => l_fpo_header_row
1396 ,p_use_workday_cal => fnd_api.g_false
1397 ,p_contiguity_override => fnd_api.g_true
1398 ,x_batch_header_rec => x_fpo_header_row
1399 ,x_return_status => l_return_status);
1400
1401 -- Check return status and raise error if needed.
1402 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1403 RAISE reschedule_batch_fail;
1404 END IF;
1405
1406 l_fpo_header_row := x_fpo_header_row ;
1407
1408 END IF; /* parentline_id IS NOT NULL THEN */
1409 END IF;
1410
1411 x_fpo_header_row := l_fpo_header_row;
1412 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1413 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1414 END IF;
1415 EXCEPTION
1416 WHEN load_rsrc_trans_err THEN
1417 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1418 gme_debug.put_line ('load_rsrc_trans_err_fail');
1419 END IF;
1420
1421 x_return_status := fnd_api.g_ret_sts_error;
1422 -- Bug 14348159
1423 WHEN reschedule_batch_fail THEN
1424 x_return_status := 'C';
1425
1426 WHEN cancel_batch_err THEN
1427 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1428 gme_debug.put_line ('Cancel_FPO_PHANTOM_fail');
1429 END IF;
1430
1431 x_return_status := fnd_api.g_ret_sts_error;
1432 WHEN gme_fetch_all_trans_err THEN
1433 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1434 gme_debug.put_line ('fetch_alltrans_err_fail');
1435 END IF;
1436
1437 x_return_status := fnd_api.g_ret_sts_error;
1438 --FPBug#4941012
1439 WHEN fetch_batch_err THEN
1440 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1441 gme_debug.put_line ('fetch batch header fail');
1442 END IF;
1443
1444 x_return_status := fnd_api.g_ret_sts_error;
1445 WHEN update_row_err THEN
1446 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1447 gme_debug.put_line ('update_row _fail');
1448 END IF;
1449
1450 x_return_status := fnd_api.g_ret_sts_error;
1451 WHEN delete_pending_trans_err THEN
1452 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1453 gme_debug.put_line ('delete_pending_trans _fail');
1454 END IF;
1455
1456 x_return_status := fnd_api.g_ret_sts_error;
1457 WHEN scale_batch_err THEN
1458 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1459 gme_debug.put_line ('scale_batch _fail');
1460 END IF;
1461
1462 x_return_status := fnd_api.g_ret_sts_error;
1463 WHEN update_pending_trans THEN
1464 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1465 gme_debug.put_line ('update_pending_trans _fail');
1466 END IF;
1467
1468 x_return_status := fnd_api.g_ret_sts_error;
1469 WHEN OTHERS THEN
1470 x_return_status := fnd_api.g_ret_sts_unexp_error;
1471 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO'
1472 ,'UPDATE_ORIGINAL_FPO');
1473 END update_original_fpo;
1474
1475 --*********************************************************
1476 PROCEDURE convert_fpo_main (
1477 p_batch_header IN gme_batch_header%ROWTYPE
1478 ,p_batch_size IN NUMBER
1479 ,p_num_batches IN NUMBER
1480 ,p_validity_rule_id IN NUMBER
1481 ,p_validity_rule_tab IN gme_common_pvt.recipe_validity_rule_tab
1482 ,p_enforce_vldt_check IN VARCHAR2 := fnd_api.g_true
1483 ,p_leadtime IN NUMBER
1484 ,p_batch_offset IN NUMBER
1485 ,p_offset_type IN NUMBER
1486 ,
1487 -- p_schedule_method IN VARCHAR2,
1488 p_plan_start_date IN gme_batch_header.plan_start_date%TYPE
1489 ,p_plan_cmplt_date IN gme_batch_header.plan_cmplt_date%TYPE
1490 ,p_use_shop_cal IN VARCHAR2 := fnd_api.g_false
1491 ,p_contiguity_override IN VARCHAR2 := fnd_api.g_true
1492 ,x_return_status OUT NOCOPY VARCHAR2
1493 ,x_batch_header OUT NOCOPY gme_batch_header%ROWTYPE
1494 ,p_use_for_all IN VARCHAR2 := fnd_api.g_true)
1495 IS
1496 l_api_name CONSTANT VARCHAR2 (30) := 'CONVERT_FPO_MAIN';
1497 /* Cursor definitions */
1498 --KYH GME Convergence - replace ic_item_mst with mtl_system_items
1499 Cursor get_validity_rule (v_validity_rule_id NUMBER, v_organization_id NUMBER) IS
1500 SELECT v.inventory_item_id, i.primary_uom_code
1501 FROM gmd_recipe_validity_rules v, mtl_system_items i
1502 WHERE recipe_validity_rule_id = v_validity_rule_id
1503 AND i.organization_id = v_organization_id
1504 AND v.inventory_item_id = i.inventory_item_id;
1505
1506 /* Local variables */
1507 l_inventory_item_id NUMBER; -- KYH GME CONV
1508 l_fpo_header_row gme_batch_header%ROWTYPE;
1509 l_in_fpo_header_row gme_batch_header%ROWTYPE;
1510 l_batch_header gme_batch_header%ROWTYPE;
1511 l_prim_prod_row gme_material_details%ROWTYPE;
1512 l_fpo_material_details_tab fpo_material_details_tab;
1513 l_validity_rule_row validity_rule_row;
1514 l_pregen_fpo_row pregen_fpo_row;
1515 l_in_pregen_fpo_row pregen_fpo_row;
1516 x_pregen_fpo_row pregen_fpo_row;
1517 l_generated_pre_batch_tab generated_pre_batch_tab;
1518 l_in_generated_pre_batch_tab generated_pre_batch_tab;
1519 l_recipe_validity_rule_tab gme_common_pvt.recipe_validity_rule_tab;
1520 l_return_status VARCHAR2 (1);
1521 l_item_um VARCHAR2 (4);
1522 l_arr_rtn_sts return_array_sts;
1523 l_batch_range VARCHAR2 (2000);
1524 l_exception_material_tbl gme_common_pvt.exceptions_tab;
1525 /* Exception definitions */
1526 validation_failure EXCEPTION;
1527 fpo_retrieval_failure EXCEPTION;
1528 batch_generation_failure EXCEPTION;
1529 insufficient_validity_rules EXCEPTION;
1530 create_batch_failure EXCEPTION;
1531 update_original_fpo_failure EXCEPTION;
1532 batch_header_fetch_error EXCEPTION;
1533 BEGIN
1534 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1535 gme_debug.log_initialize ('ConvertFPO');
1536 END IF;
1537
1538 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1539 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1540 || l_api_name);
1541 END IF;
1542
1543 x_return_status := fnd_api.g_ret_sts_success;
1544 -- Pawan Kumar ADDED code for bug 2398719
1545 l_batch_header.batch_id := p_batch_header.batch_id;
1546 /* Bug2403042: Added following code to identify the
1547 FPO based on batch_no, and plant_code */
1548 l_batch_header.batch_no := p_batch_header.batch_no;
1549 l_batch_header.organization_id := p_batch_header.organization_id; -- KYH GME CONV
1550 l_batch_header.batch_id := p_batch_header.batch_id; -- KYH GME CONV
1551 l_batch_header.batch_type := 10;
1552
1553 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1554 gme_debug.put_line (g_pkg_name ||
1555 '.' ||
1556 l_api_name ||
1557 ' Retrieve batch header for batch_id '||
1558 l_batch_header.batch_id);
1559 END IF;
1560 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
1561 RAISE batch_header_fetch_error;
1562 END IF;
1563
1564 /* Get the validity rule for the existing FPO */
1565 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1566 gme_debug.put_line (g_pkg_name ||
1567 '.' ||
1568 l_api_name ||
1569 ' Retrieve validity rule for rule_id '||
1570 l_batch_header.recipe_validity_rule_id);
1571 END IF;
1572 OPEN get_validity_rule (l_batch_header.recipe_validity_rule_id,l_batch_header.organization_id);
1573
1574 FETCH get_validity_rule
1575 INTO l_inventory_item_id, l_item_um;
1576
1577 IF get_validity_rule%NOTFOUND THEN
1578 gme_common_pvt.log_message ('GME_VALIDITY_RULE_RETRIEVE_ERR');
1579
1580 CLOSE get_validity_rule;
1581
1582 RAISE validation_failure;
1583 END IF;
1584
1585 CLOSE get_validity_rule;
1586
1587 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1588 gme_debug.put_line (g_pkg_name ||
1589 '.' ||
1590 l_api_name ||
1591 ' Invoke Validation ');
1592 END IF;
1593
1594 VALIDATION (p_batch_header => l_batch_header
1595 ,
1596 -- x_batch_header => x_batch_header,
1597 p_batch_size => p_batch_size
1598 ,p_batch_size_uom => l_item_um
1599 ,p_num_batches => p_num_batches
1600 ,p_validity_rule_id => p_validity_rule_id
1601 ,p_leadtime => p_leadtime
1602 ,p_batch_offset => p_batch_offset
1603 ,p_offset_type => p_offset_type
1604 ,
1605 -- p_schedule_method => p_schedule_method,
1606 p_plan_start_date => p_plan_start_date
1607 ,p_plan_cmplt_date => p_plan_cmplt_date
1608 ,x_pregen_fpo_row => x_pregen_fpo_row
1609 ,x_return_status => x_return_status);
1610
1611 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1612 gme_debug.put_line (g_pkg_name ||
1613 '.' ||
1614 l_api_name ||
1615 ' VALIDATION procedure returns '||
1616 x_return_status);
1617 END IF;
1618
1619 l_pregen_fpo_row := x_pregen_fpo_row;
1620 l_fpo_header_row := l_batch_header;
1621
1622 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1623 RAISE validation_failure;
1624 END IF;
1625
1626 l_in_fpo_header_row := l_fpo_header_row;
1627 l_in_pregen_fpo_row := l_pregen_fpo_row;
1628 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1629 gme_debug.put_line (g_pkg_name ||
1630 '.' ||
1631 l_api_name ||
1632 ' About to INVOKE retrieve_fpo_data ');
1633 END IF;
1634 retrieve_fpo_data
1635 (p_fpo_header_row => l_in_fpo_header_row
1636 ,x_fpo_header_row => l_fpo_header_row
1637 ,p_pregen_fpo_row => l_in_pregen_fpo_row
1638 ,x_pregen_fpo_row => l_pregen_fpo_row
1639 ,x_prim_prod_row => l_prim_prod_row
1640 ,x_validity_rule_row => l_validity_rule_row
1641 ,x_fpo_material_details_tab => l_fpo_material_details_tab
1642 ,x_return_status => x_return_status);
1643
1644 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1645 gme_debug.put_line (g_pkg_name ||
1646 '.' ||
1647 l_api_name ||
1648 ' retrieve_fpo_data returns status of '||
1649 x_return_status);
1650 END IF;
1651
1652 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1653 RAISE fpo_retrieval_failure;
1654 END IF;
1655
1656 l_item_um := l_prim_prod_row.dtl_um;
1657
1658 IF p_use_for_all = fnd_api.g_true THEN
1659 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1660 gme_debug.put_line (g_pkg_name ||
1661 '.' ||
1662 l_api_name ||
1663 ' p_use_for_all is TRUE ');
1664 END IF;
1665 IF p_validity_rule_id IS NULL OR p_validity_rule_id = 0 THEN
1666 RAISE insufficient_validity_rules;
1667 -- Bug 3185748 Removed the code to validate the validity rule if
1668 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1669 END IF; /* p_validity_rule_id IS NULL OR */
1670
1671 FOR i IN 1 .. p_num_batches LOOP
1672 l_recipe_validity_rule_tab (i).recipe_validity_rule_id :=
1673 p_validity_rule_id;
1674 END LOOP;
1675 ELSE /* IF p_use_for_all */
1676 IF p_num_batches = 1 THEN
1677 IF p_validity_rule_id IS NULL THEN
1678 RAISE insufficient_validity_rules;
1679 -- Bug 3185748 Removed the code to validate the validity rule if
1680 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1681 END IF;
1682
1683 l_recipe_validity_rule_tab (1).recipe_validity_rule_id :=
1684 p_validity_rule_id;
1685 ELSE /* IF p_num_batches = 1 */
1686 IF p_validity_rule_tab.COUNT <> p_num_batches THEN
1687 RAISE insufficient_validity_rules;
1688 -- Bug 3185748 Removed the code to validate the validity rule if
1689 -- p_validity_rule_id is not null. The validation is done before creating the batch.
1690 END IF; /* IF p_validity_rule_tab.COUNT <> p_num_batches */
1691
1692 l_recipe_validity_rule_tab := p_validity_rule_tab;
1693 END IF; /* IF p_num_batches = 1 */
1694 END IF; /* IF p_use_for_all */
1695
1696 --Swapna Kommineni bug#3565971 13/08/2004 commented the code so that passed lead time
1697 -- does not get overwritten.
1698 /* IF (l_pregen_fpo_row.rules_found = 1) THEN
1699 l_in_pregen_fpo_row := l_pregen_fpo_row;
1700 calculate_leadtime (
1701 p_pregen_fpo_row => l_in_pregen_fpo_row,
1702 x_pregen_fpo_row => l_pregen_fpo_row,
1703 x_return_status => l_return_status
1704 );
1705 END IF; */
1706 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1707 gme_debug.put_line (g_pkg_name ||
1708 '.' ||
1709 l_api_name ||
1710 ' Retrieve batch header for batch_id '||
1711 l_batch_header.batch_id);
1712 END IF;
1713 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1714 gme_debug.put_line (g_pkg_name ||
1715 '.' ||
1716 l_api_name ||
1717 ' Invoke generate_pre_batch_header_recs');
1718 END IF;
1719 generate_pre_batch_header_recs
1720 (p_fpo_header_row => l_fpo_header_row
1721 ,p_prim_prod_row => l_prim_prod_row
1722 ,p_pregen_fpo_row => l_pregen_fpo_row
1723 ,x_generated_pre_batch_tab => l_generated_pre_batch_tab
1724 ,x_return_status => x_return_status);
1725
1726 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1727 gme_debug.put_line (g_pkg_name ||
1728 '.' ||
1729 l_api_name ||
1730 ' generate_pre_batch_header_recs returns '||
1731 x_return_status );
1732 END IF;
1733
1734 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1735 RAISE batch_generation_failure;
1736 END IF;
1737
1738 --Swapna Kommineni bug#3565971 13/08/2004 Start
1739 FOR i IN 1 .. l_generated_pre_batch_tab.COUNT LOOP
1740 -- IF (p_leadtime is NULL) and (i=1) THEN
1741
1742 --Swapna Kommineni reopened bug#3565971 07/09/2004
1743 /* Only when the leadtime is not passed then the start and enddates calculated
1744 from generate_pre_batch_header_recs will be changed */
1745 IF (p_leadtime IS NULL) THEN
1746 IF (i = 1) THEN
1747 /* For the first batch if the leadtime is not passed then plan start and end dates are
1748 passed to the create_batch api as the given dates. so that api will*/
1749 l_generated_pre_batch_tab (i).plan_start_date :=
1750 p_plan_start_date;
1751 l_generated_pre_batch_tab (i).plan_cmplt_date :=
1752 p_plan_cmplt_date;
1753 ELSE
1754 IF (p_plan_cmplt_date IS NULL) THEN
1755 l_generated_pre_batch_tab (i).plan_cmplt_date :=
1756 p_plan_cmplt_date;
1757 ELSIF (p_plan_start_date IS NULL) THEN
1758 l_generated_pre_batch_tab (i).plan_start_date :=
1759 p_plan_start_date;
1760 END IF;
1761 END IF;
1762 END IF;
1763 END LOOP;
1764
1765 --Swapna Kommineni bug#3565971 13/08/2004 End
1766 l_in_generated_pre_batch_tab := l_generated_pre_batch_tab;
1767 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1768 gme_debug.put_line (g_pkg_name ||
1769 '.' ||
1770 l_api_name ||
1771 ' Invoke convert_fpo_to_batch ');
1772 END IF;
1773 convert_fpo_to_batch
1774 (p_generated_pre_batch_tab => l_in_generated_pre_batch_tab
1775 ,p_recipe_validity_rule_tab => l_recipe_validity_rule_tab
1776 ,p_pregen_fpo_row => l_pregen_fpo_row
1777 ,x_generated_pre_batch_tab => l_generated_pre_batch_tab
1778 ,p_use_shop_cal => p_use_shop_cal
1779 ,p_contiguity_override => p_contiguity_override
1780 ,x_return_status => x_return_status
1781 ,x_arr_rtn_sts => l_arr_rtn_sts
1782 ,x_exception_material_tbl => l_exception_material_tbl
1783 ,p_fpo_validity_rule_id => l_batch_header.recipe_validity_rule_id
1784 -- Bug 3185748
1785 );
1786 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1787 gme_debug.put_line (g_pkg_name ||
1788 '.' ||
1789 l_api_name ||
1790 ' convert_fpo_to_batch returns '||
1791 x_return_status);
1792 END IF;
1793
1794 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1795 RAISE create_batch_failure;
1796 END IF;
1797
1798 --BEGIN BUG#3185748 Sastry
1799 -- Call update fpo only if some batches were created.
1800 IF l_generated_pre_batch_tab.COUNT > 0 THEN
1801 l_pregen_fpo_row.num_batches := l_generated_pre_batch_tab.COUNT;
1802 l_pregen_fpo_row.sum_eff_qty :=
1803 l_generated_pre_batch_tab.COUNT * p_batch_size;
1804 update_original_fpo
1805 (p_fpo_header_row => l_fpo_header_row
1806 ,p_prim_prod_row => l_prim_prod_row
1807 ,p_pregen_fpo_row => l_pregen_fpo_row
1808 ,p_fpo_material_details_tab => l_fpo_material_details_tab
1809 ,p_enforce_vldt_check => p_enforce_vldt_check
1810 ,x_fpo_header_row => x_batch_header
1811 ,x_return_status => x_return_status);
1812
1813 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1814 gme_debug.put_line ( 'in private convert_fpo-after update'
1815 || x_return_status);
1816 END IF;
1817
1818 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1819 RAISE update_original_fpo_failure;
1820 END IF;
1821
1822 -- Bug 3185748 Use l_generated_pre_batch_tab.FIRST and l_generated_pre_batch_tab.LAST
1823 -- to get the batch range as all the batches might have not been created.
1824 l_batch_range :=
1825 l_generated_pre_batch_tab (l_generated_pre_batch_tab.FIRST).batch_no;
1826
1827 IF l_generated_pre_batch_tab.COUNT > 1 THEN
1828 l_batch_range :=
1829 l_batch_range
1830 || ' - '
1831 || l_generated_pre_batch_tab (l_generated_pre_batch_tab.LAST).batch_no;
1832 END IF;
1833
1834 -- Bug 3185748 Display proper message
1835 IF p_num_batches = l_generated_pre_batch_tab.COUNT THEN
1836 gme_common_pvt.log_message ('GME_FPO_TO_BATCHES_CREATED'
1837 ,'BATCHRANGE'
1838 ,l_batch_range);
1839 ELSE
1840 gme_common_pvt.log_message ('GME_NOT_ALL_BATCHES_CREATED'
1841 ,'BATCHRANGE'
1842 ,l_batch_range);
1843 END IF;
1844 END IF;
1845 --END BUG#3185748 Sastry
1846 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1847 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1848 END IF;
1849 EXCEPTION
1850 WHEN validation_failure THEN
1851 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1852 gme_debug.put_line ('in validiation fail exception');
1853 END IF;
1854
1855 x_return_status := fnd_api.g_ret_sts_error;
1856 WHEN fpo_retrieval_failure THEN
1857 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1858 gme_debug.put_line ('in retrieval fail exception');
1859 END IF;
1860
1861 x_return_status := fnd_api.g_ret_sts_error;
1862 WHEN batch_generation_failure THEN
1863 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1864 gme_debug.put_line ('in batch_generation fail exception');
1865 END IF;
1866
1867 x_return_status := fnd_api.g_ret_sts_error;
1868 WHEN insufficient_validity_rules THEN
1869 x_return_status := fnd_api.g_ret_sts_error;
1870 gme_common_pvt.log_message ('GME_INSUF_VAL_RULE');
1871 WHEN create_batch_failure THEN
1872 x_return_status := fnd_api.g_ret_sts_error;
1873 WHEN update_original_fpo_failure OR batch_header_fetch_error THEN
1874 x_return_status := fnd_api.g_ret_sts_error;
1875 WHEN OTHERS THEN
1876 x_return_status := fnd_api.g_ret_sts_unexp_error;
1877 fnd_msg_pub.add_exc_msg ('GME_API_CONVERT_FPO', 'CONVERT_FPO_MAIN');
1878 END convert_fpo_main;
1879 --****************************************************************
1880 END gme_convert_fpo_pvt;