[Home] [Help]
PACKAGE BODY: APPS.GMF_VIB
Source
1 PACKAGE BODY GMF_VIB AS
2 /* $Header: GMFVIBB.pls 120.13 2011/12/08 17:11:35 pvkanetk noship $ */
3
4 g_pkg_name VARCHAR2(30) := 'GMF_VIB';
5 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
6
7 --+==========================================================================+
8 --| Global Comments
9 --+==========================================================================+
10 --| HISTORY |
11 --| jgogna - created |
12 --| rseshadr 01-May-2006 bug 5190115 - handle nulls in cost_alloc for |
13 --| products. Fixed typo in resource reversal exception bloc. |
14 --| |
15 --| vchukkap 21-Sep-2006 bug 5491419 - batch requirements are not getting|
16 --| created for closed batches during migration. Only creating for |
17 --| batches with status: wip or completed (2 and 3). Included status 4. |
18 --| |
19 --| umoogala 21-Oct-2006 bug 5607069 - |
20 --| Issue 1) Material and Resources reversals layers were not getting |
21 --| consumed. |
22 --| Fixed code to allocate reversals only if its original |
23 --| layer is consumed by this product, while creating VIB dtls.|
24 --| Issue 2) Material and Resources reversals layers were not getting |
25 --| properly apportioned in the finalization layers. |
26 --| Fix is same as above: |
27 --| Fixed code to allocate reversals only if its original |
28 --| layer is consumed by this product, while creating VIB dtls.|
29 --| Pramod B.H 25-Aug-2008 Bug 6125370 - While releasing a batch, additional|
30 --| check is performed to verify if the batch contains at least one |
31 --| product line with both non-zero planned qty and non-zero cost |
32 --| allocation factor. If no such product line exists then the process |
33 --| will raise an error and will stop the batch release activity.
34 --+==========================================================================+
35
36 /*
37 --+==========================================================================+
38 --| PROCEDURE NAME |
39 --| Create_Batch_Requirements |
40 --| |
41 --| TYPE |
42 --| Public |
43 --| |
44 --| USAGE |
45 --| Create_Batch_Requirement |
46 --| |
47 --| DESCRIPTION |
48 --| |
49 --| PARAMETERS |
50 --| |
51 --| RETURNS |
52 --| None |
53 --| |
54 --| HISTORY |
55 --| jgogna - created |
56 --| rseshadr 01-May-2006 bug 5190115 - handle nulls in cost_alloc for |
57 --| products. Fixed typo in resource reversal exception bloc. |
58 --| |
59 --| vchukkap 21-Sep-2006 bug 5491419 - batch requirements are not getting|
60 --| created for closed batches during migration. Only creating for |
61 --| batches with status: wip or completed (2 and 3). Included status 4. |
62 --+==========================================================================+
63 */
64 PROCEDURE Create_Batch_Requirements
65 ( p_api_version IN NUMBER,
66 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
67 p_batch_id IN NUMBER,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2) IS
71
72 l_count PLS_INTEGER;
73 l_api_name VARCHAR2(30) := 'Create_Batch_Requirements';
74 BEGIN
75 x_return_status := FND_API.G_RET_STS_SUCCESS ;
76
77 IF g_debug <= gme_debug.g_log_procedure THEN
78 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
79 END IF;
80
81 -- Check if the Batch requirements already exist
82 BEGIN
83 l_count := 0;
84 SELECT count(*)
85 INTO l_count
86 FROM gmf_batch_requirements
87 WHERE batch_id = p_batch_id AND
88 delete_mark = 0;
89
90 IF l_count > 0 THEN
91 dbms_output.put_line ('Batch requirement already exist for the batch');
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_REQ_EXISTS');
94 FND_MSG_PUB.Add;
95 RETURN;
96 END IF;
97 END;
98
99 IF g_debug <= gme_debug.g_log_procedure THEN
100 gme_debug.put_line ('calling Create_Temp_Batch_Requirements');
101 END IF;
102
103 Create_Temp_Batch_Requirements (
104 p_api_version,
105 p_init_msg_list,
106 p_batch_id,
107 x_return_status,
108 x_msg_count,
109 x_msg_data);
110
111 IF g_debug <= gme_debug.g_log_procedure THEN
112 gme_debug.put_line ('after Create_Temp_Batch_Requirements status/msg: ' || x_return_status ||'/'||x_msg_data);
113 END IF;
114
115
116 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
117
118 -- Copy the temp table to the actual table
119 IF g_debug <= gme_debug.g_log_statement THEN
120 gme_debug.put_line ('inserting into req table from gtmp table');
121 END IF;
122
123 INSERT INTO gmf_batch_requirements(
124 vib_id,
125 batch_id,
126 product_item_id,
127 prod_material_detail_id,
128 ingredient_item_id,
129 ing_material_detail_id,
130 resources,
131 batchstep_resource_id,
132 derived_cost_alloc,
133 required_doc_qty,
134 delete_mark,
135 created_by,
136 creation_date,
137 last_updated_by,
138 last_update_date,
139 last_update_login,
140 requirement_id,
141 organization_id,
142 vib_profile_value)
143 SELECT
144 NULL,
145 batch_id,
146 product_item_id,
147 prod_material_detail_id,
148 ingredient_item_id,
149 ing_material_detail_id,
150 resources,
151 batchstep_resource_id,
152 derived_cost_alloc,
153 required_doc_qty,
154 delete_mark,
155 created_by,
156 creation_date,
157 last_updated_by,
158 last_update_date,
159 last_update_login,
160 requirement_id,
161 organization_id,
162 vib_profile_value
163 FROM gmf_batch_requirements_gtmp
164 WHERE batch_id = p_batch_id;
165
166 IF g_debug <= gme_debug.g_log_statement THEN
167 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
168 END IF;
169 END IF;
170
171 IF g_debug <= gme_debug.g_log_procedure THEN
172 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
173 END IF;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
178 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
179 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
180 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
181 FND_MSG_PUB.Add;
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
183 END Create_Batch_Requirements;
184
185 /*
186 --+==========================================================================+
187 --| PROCEDURE NAME |
188 --| Update_Batch_Requirements |
189 --| |
190 --| TYPE |
191 --| Public |
192 --| |
193 --| USAGE |
194 --| Update_Batch_Requirements |
195 --| |
196 --| DESCRIPTION |
197 --| |
198 --| PARAMETERS |
199 --| |
200 --| RETURNS |
201 --| None |
202 --| |
203 --| HISTORY |
204 --| |
205 --+==========================================================================+
206 */
207 PROCEDURE Update_Batch_Requirements
208 ( p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
210 p_batch_id IN NUMBER,
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2) IS
214
215 l_count PLS_INTEGER;
216 l_api_name VARCHAR2(30) := 'Update_Batch_Requirements';
217 BEGIN
218
219 x_return_status := FND_API.G_RET_STS_SUCCESS ;
220
221 IF g_debug <= gme_debug.g_log_procedure THEN
222 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
223 END IF;
224
225 IF g_debug <= gme_debug.g_log_procedure THEN
226 gme_debug.put_line ('calling Create_Temp_Batch_Requirements');
227 END IF;
228
229 Create_Temp_Batch_Requirements (
230 p_api_version,
231 p_init_msg_list,
232 p_batch_id,
233 x_return_status,
234 x_msg_count,
235 x_msg_data);
236
237 IF g_debug <= gme_debug.g_log_procedure THEN
238 gme_debug.put_line ('after Create_Temp_Batch_Requirements status/msg: ' || x_return_status ||'/'||x_msg_data);
239 END IF;
240
241 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
242
243 UPDATE gmf_batch_requirements
244 SET delete_mark = 1
245 WHERE
246 batch_id = p_batch_id;
247
248
249 IF g_debug <= gme_debug.g_log_statement THEN
250 gme_debug.put_line ('inserting into req table from gtmp');
251 END IF;
252
253 INSERT INTO gmf_batch_requirements(
254 vib_id,
255 batch_id,
256 product_item_id,
257 prod_material_detail_id,
258 ingredient_item_id,
259 ing_material_detail_id,
260 resources,
261 batchstep_resource_id,
262 derived_cost_alloc,
263 required_doc_qty,
264 delete_mark,
265 created_by,
266 creation_date,
267 last_updated_by,
268 last_update_date,
269 last_update_login,
270 requirement_id,
271 organization_id,
272 vib_profile_value)
273 SELECT
274 NULL,
275 batch_id,
276 product_item_id,
277 prod_material_detail_id,
278 ingredient_item_id,
279 ing_material_detail_id,
280 resources,
281 batchstep_resource_id,
282 derived_cost_alloc,
283 required_doc_qty,
284 delete_mark,
285 created_by,
286 creation_date,
287 last_updated_by,
288 last_update_date,
289 last_update_login,
290 requirement_id,
291 organization_id,
292 vib_profile_value
293 FROM gmf_batch_requirements_gtmp;
294
295 IF g_debug <= gme_debug.g_log_statement THEN
296 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
297 END IF;
298 END IF;
299
300 IF g_debug <= gme_debug.g_log_procedure THEN
301 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
302 END IF;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
307 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
308 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
309 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
310 FND_MSG_PUB.Add;
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
312 END Update_Batch_Requirements;
313
314 /*
315 --+==========================================================================+
316 --| PROCEDURE NAME |
317 --| Delete_Batch_Requirements |
318 --| |
319 --| TYPE |
320 --| Public |
321 --| |
322 --| USAGE |
323 --| Delete_Batch_Requirements |
324 --| |
325 --| DESCRIPTION |
326 --| |
327 --| PARAMETERS |
328 --| |
329 --| RETURNS |
330 --| None |
331 --| |
332 --| HISTORY |
333 --| |
334 --+==========================================================================+
335 */
336 PROCEDURE Delete_Batch_Requirements
337 ( p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
339 p_batch_id IN NUMBER,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2) IS
343
344 l_batch_status gme_batch_header.batch_status%TYPE;
345 l_api_name VARCHAR2(30) := 'Delete_Batch_Requirements';
346 BEGIN
347 x_return_status := FND_API.G_RET_STS_SUCCESS ;
348
349 IF g_debug <= gme_debug.g_log_procedure THEN
350 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
351 END IF;
352
353 -- Validate batch_id
354 BEGIN
355 SELECT batch_status
356 INTO l_batch_status
357 FROM gme_batch_header
358 WHERE batch_id = p_batch_id;
359
360 IF l_batch_status <> 1 THEN
361 x_return_status := FND_API.G_RET_STS_ERROR ;
362 dbms_output.put_line ('Batch is not in PENDING Status');
363 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_NOT_PENDING');
364 FND_MSG_PUB.Add;
365 RETURN;
366 END IF;
367 EXCEPTION
368 WHEN OTHERS THEN
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
370 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
371 FND_MSG_PUB.Add;
372 RAISE;
373 END;
374
375 IF g_debug <= gme_debug.g_log_statement THEN
376 gme_debug.put_line ('deleting batch reqs');
377 END IF;
378
379 UPDATE gmf_batch_requirements
380 SET delete_mark = 1
381 WHERE
382 batch_id = p_batch_id AND
383 delete_mark = 0;
384
385 IF g_debug <= gme_debug.g_log_statement THEN
386 gme_debug.put_line (sql%ROWCOUNT || ' rows deleted');
387 END IF;
388
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 IF g_debug <= gme_debug.g_log_procedure THEN
392 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
393 END IF;
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
398 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
399 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
400 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
401 FND_MSG_PUB.Add;
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
403 END;
404
405 /*
406 --+==========================================================================+
407 --| PROCEDURE NAME |
408 --| Create_Temp_Batch_Requirements |
409 --| |
410 --| TYPE |
411 --| Public |
412 --| |
413 --| USAGE |
414 --| Create_Temp_Batch_Requirements |
415 --| |
416 --| DESCRIPTION |
417 --| |
418 --| PARAMETERS |
419 --| |
420 --| RETURNS |
421 --| None |
422 --| |
423 --| HISTORY |
424 --| rseshadr 01-May-2006 Bug 5190115 - use nvl for cost_alloc as some prod |
425 --| rows can have null alloc value |
426 --| |
427 --| vchukkap 21-Sep-2006 bug 5491419 - batch requirements are not getting|
428 --| created for closed batches during migration. Only creating for |
429 --| batches with status: wip or completed (2 and 3). Included status 4. |
430 --| |
431 --| Pramod B.H 25-Aug-2008 Bug 6125370 - While releasing a batch, additional|
432 --| check is performed to verify if the batch contains at least one |
433 --| product line with both non-zero planned qty and non-zero cost |
434 --| allocation factor. If no such product line exists then the process |
435 --| will raise an error and will stop the batch release activity. |
436 --+==========================================================================+
437 */
438 PROCEDURE Create_Temp_Batch_Requirements
439 ( p_api_version IN NUMBER,
440 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
441 p_batch_id IN NUMBER,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2) IS
445
446 l_count PLS_INTEGER; /* Bug 6125370. */
447 l_gmf_asg_cons_yld_step_lvl VARCHAR2(30); /* Bug 8531915 */
448
449 CURSOR c_batch_products (c_batch_id NUMBER) IS
450 SELECT m.material_detail_id, m.inventory_item_id as item_id, m.organization_id,
451 decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) prod_plan_qty,
452 m.cost_alloc, s.batchstep_id
453 FROM gme_material_details m, gme_batch_step_items s
454 WHERE m.batch_id = c_batch_id AND
455 m.line_type = 1 AND
456 m.material_detail_id = s.material_detail_id and
457 decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 and
458 nvl(m.cost_alloc,0) <> 0;
459
460 CURSOR c_step_dependencies (c_batch_id NUMBER, c_batchstep_id NUMBER) IS
461 SELECT dep_step_id
462 FROM GME_BATCH_STEP_DEPENDENCIES
463 WHERE batch_id = c_batch_id
464 AND l_gmf_asg_cons_yld_step_lvl = 'N'
465 START WITH batchstep_id = c_batchstep_id
466 CONNECT BY PRIOR dep_step_id = batchstep_id
467 UNION
468 SELECT c_batchstep_id dep_step_id FROM DUAL;
469
470 CURSOR c_total_prod_alloc (c_batch_id NUMBER) IS
471 SELECT ing_material_detail_id, batchstep_resource_id,
472 SUM(derived_cost_alloc) total_prod_alloc
473 FROM gmf_batch_requirements_gtmp
474 WHERE batch_id = c_batch_id
475 GROUP BY ing_material_detail_id, batchstep_resource_id;
476
477
478 l_batch_status gme_batch_header.batch_status%TYPE;
479 l_use_item_step_dep VARCHAR2(30);
480 l_vib_profile_value VARCHAR2(30);
481 l_api_name VARCHAR2(30) := 'Create_Temp_Batch_Requirements';
482 BEGIN
483 x_return_status := FND_API.G_RET_STS_SUCCESS ;
484
485 IF g_debug <= gme_debug.g_log_procedure THEN
486 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
487 END IF;
488
489 -- Validate batch_id
490 BEGIN
491 SELECT batch_status
492 INTO l_batch_status
493 FROM gme_batch_header
494 WHERE batch_id = p_batch_id;
495
496 /* Bug 5491419. Added status 4.
497 IF l_batch_status not in (2, 3) THEN
498 */
499 /* Bug 9441550 . Added status -1. */
500 IF l_batch_status not in (-1,2, 3, 4) THEN
501 -- x_return_status := FND_API.G_RET_STS_ERROR ;
502 dbms_output.put_line ('Batch is not in WIP/Cert/Close Status');
503 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_NOT_WIP');
504 -- FND_MSG_PUB.Add;
505 x_return_status := FND_API.G_RET_STS_SUCCESS ;
506 RETURN;
507 END IF;
508
509 /* Bug 6125370 - Start: Validate product lines*/
510 l_count := 0;
511 SELECT count(*)
512 INTO l_count
513 FROM gme_material_details m
514 WHERE m.batch_id = p_batch_id AND
515 m.line_type = 1 AND
516 decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 AND
517 nvl(m.cost_alloc,0) <> 0;
518
519 IF l_count = 0 THEN
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 FND_MESSAGE.SET_NAME('GMF', 'GMF_INVALID_BATCH');
522 FND_MSG_PUB.Add;
523 RETURN;
524 END IF;
525 /* Bug 6125370 - End */
526 EXCEPTION
527 WHEN OTHERS THEN
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
529 dbms_output.put_line ('Invalid Batch ID');
530 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
531 FND_MSG_PUB.Add;
532 RAISE;
533 END;
534
535 l_use_item_step_dep := fnd_profile.value ('GMF_USE_ITEM_STEP_DEPENDENCIES');
536 IF (l_use_item_step_dep IS NULL) THEN
537 l_use_item_step_dep := 'N';
538 END IF;
539
540 l_vib_profile_value := fnd_profile.value ('GMF_USE_VIB_FOR_ACOST');
541 IF (l_vib_profile_value IS NULL) THEN
542 l_vib_profile_value := 'N';
543 END IF;
544
545 gme_debug.put_line ('profiles. step_dep: ' || l_use_item_step_dep || ' vib: ' || l_vib_profile_value);
546
547 -- Delete the temp table first
548 DELETE from gmf_batch_requirements_gtmp;
549
550 -- Get all products and step association
551 IF g_debug <= gme_debug.g_log_statement THEN
552 gme_debug.put_line ('Get all products and step association');
553 END IF;
554
555 IF l_use_item_step_dep = 'Y' THEN
556
557 -- Bug 8531915 Use profile GMF_ASG_CONS_YLD_STEP_LVL
558 l_gmf_asg_cons_yld_step_lvl := fnd_profile.value ('GMF_ASG_CONS_YLD_STEP_LVL');
559 IF (l_gmf_asg_cons_yld_step_lvl IS NULL) THEN
560 l_gmf_asg_cons_yld_step_lvl := 'N';
561 END IF;
562
563 FOR p IN c_batch_products(p_batch_id)
564 LOOP
565 -- Get all dependant steps for the product step
566 FOR ds IN c_step_dependencies (p_batch_id, p.batchstep_id)
567 LOOP
568 -- insert records into the batch requirements table
569 INSERT INTO gmf_batch_requirements_gtmp(
570 vib_id,
571 batch_id,
572 product_item_id,
573 prod_material_detail_id,
574 ingredient_item_id,
575 ing_material_detail_id,
576 resources,
577 batchstep_resource_id,
578 derived_cost_alloc,
579 required_doc_qty,
580 delete_mark,
581 created_by,
582 creation_date,
583 last_updated_by,
584 last_update_date,
585 last_update_login,
586 requirement_id,
587 organization_id,
588 vib_profile_value)
589 SELECT
590 NULL,
591 p_batch_id,
592 p.item_id,
593 p.material_detail_id,
594 m.inventory_item_id,
595 m.material_detail_id,
596 NULL,
597 NULL,
598 p.cost_alloc,
599 p.cost_alloc * ( decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) /
600 p.prod_plan_qty),
601 0,
602 -1,
603 sysdate,
604 -1,
605 sysdate,
606 NULL,
607 gmf_vib_id_s.nextval,
608 p.organization_id,
609 l_vib_profile_value
610 FROM gme_batch_step_items s,
611 gme_material_details m
612 WHERE batchstep_id = ds.dep_step_id AND
613 s.material_detail_id = m.material_detail_id AND
614 m.line_type <> 1;
615
616 INSERT INTO gmf_batch_requirements_gtmp(
617 vib_id,
618 batch_id,
619 product_item_id,
620 prod_material_detail_id,
621 ingredient_item_id,
622 ing_material_detail_id,
623 resources,
624 batchstep_resource_id,
625 derived_cost_alloc,
626 required_doc_qty,
627 delete_mark,
628 created_by,
629 creation_date,
630 last_updated_by,
631 last_update_date,
632 last_update_login,
633 requirement_id,
634 organization_id,
635 vib_profile_value)
636 SELECT
637 NULL,
638 p_batch_id,
639 p.item_id,
640 p.material_detail_id,
641 NULL,
642 NULL,
643 m.resources,
644 m.batchstep_resource_id,
645 p.cost_alloc,
646 p.cost_alloc * ( nvl(m.plan_rsrc_usage,0) / p.prod_plan_qty),
647 0,
648 -1,
649 sysdate,
650 -1,
651 sysdate,
652 NULL,
653 gmf_vib_id_s.nextval,
654 p.organization_id,
655 l_vib_profile_value
656 FROM gme_batch_step_resources m
657 WHERE batchstep_id = ds.dep_step_id;
658
659 END LOOP;
660 END LOOP;
661 END IF;
662
663 -- Now insert any remaining ing/res which was not used for any product
664 IF g_debug <= gme_debug.g_log_statement THEN
665 gme_debug.put_line ('Now insert any remaining ingredients which was not used for any product...');
666 END IF;
667
668 INSERT INTO gmf_batch_requirements_gtmp(
669 vib_id,
670 batch_id,
671 product_item_id,
672 prod_material_detail_id,
673 ingredient_item_id,
674 ing_material_detail_id,
675 resources,
676 batchstep_resource_id,
677 derived_cost_alloc,
678 required_doc_qty,
679 delete_mark,
680 created_by,
681 creation_date,
682 last_updated_by,
683 last_update_date,
684 last_update_login,
685 requirement_id,
686 organization_id,
687 vib_profile_value)
688 SELECT
689 NULL,
690 p_batch_id,
691 p.inventory_item_id,
692 p.material_detail_id,
693 i.inventory_item_id,
694 i.material_detail_id,
695 NULL,
696 NULL,
697 p.cost_alloc,
698 p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
699 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
700 0,
701 -1,
702 sysdate,
703 -1,
704 sysdate,
705 NULL,
706 gmf_vib_id_s.nextval,
707 p.organization_id,
708 l_vib_profile_value
709 FROM gme_material_details p, gme_material_details i
710 WHERE
711 p.batch_id = p_batch_id AND
712 i.batch_id = p_batch_id AND
713 p.line_type = 1 AND
714 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
715 nvl(p.cost_alloc,0) <> 0 AND
716 i.line_type <> 1 AND
717 i.material_detail_id NOT IN (
718 SELECT nvl(ing_material_detail_id, -99)
719 FROM gmf_batch_requirements_gtmp f
720 WHERE
721 batch_id = p_batch_id );
722
723 IF g_debug <= gme_debug.g_log_statement THEN
724 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
725 gme_debug.put_line ('Now insert any remaining resources which was not used for any product...');
726 END IF;
727
728 /* Bug 13442362 13367279 Added gme_batch_steps and joins so that index on gme_batch_step_resources is used */
729
730 INSERT INTO gmf_batch_requirements_gtmp(
731 vib_id,
732 batch_id,
733 product_item_id,
734 prod_material_detail_id,
735 ingredient_item_id,
736 ing_material_detail_id,
737 resources,
738 batchstep_resource_id,
739 derived_cost_alloc,
740 required_doc_qty,
741 delete_mark,
742 created_by,
743 creation_date,
744 last_updated_by,
745 last_update_date,
746 last_update_login,
747 requirement_id,
748 organization_id,
749 vib_profile_value)
750 SELECT
751 NULL,
752 p_batch_id,
753 p.inventory_item_id,
754 p.material_detail_id,
755 NULL,
756 NULL,
757 r.resources,
758 r.batchstep_resource_id,
759 p.cost_alloc,
760 p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
761 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
762 0,
763 -1,
764 sysdate,
765 -1,
766 sysdate,
767 NULL,
768 gmf_vib_id_s.nextval,
769 p.organization_id,
770 l_vib_profile_value
771 FROM gme_material_details p, gme_batch_step_resources r, gme_batch_steps s
772 WHERE
773 p.batch_id = p_batch_id AND
774 s.batch_id = p_batch_id AND
775 s.batchstep_id = r.batchstep_id AND
776 p.line_type = 1 AND
777 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
778 nvl(p.cost_alloc,0) <> 0 AND
779 r.batchstep_resource_id NOT IN (
780 SELECT nvl(batchstep_resource_id, -99)
781 FROM gmf_batch_requirements_gtmp f
782 WHERE
783 batch_id = p_batch_id );
784
785 IF g_debug <= gme_debug.g_log_statement THEN
786 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
787 END IF;
788
789 -- Now insert any product that may have been missed out
790 IF g_debug <= gme_debug.g_log_statement THEN
791 gme_debug.put_line ('Now insert any product that may have been missed out...');
792 END IF;
793
794 INSERT INTO gmf_batch_requirements_gtmp(
795 vib_id,
796 batch_id,
797 product_item_id,
798 prod_material_detail_id,
799 ingredient_item_id,
800 ing_material_detail_id,
801 resources,
802 batchstep_resource_id,
803 derived_cost_alloc,
804 required_doc_qty,
805 delete_mark,
806 created_by,
807 creation_date,
808 last_updated_by,
809 last_update_date,
810 last_update_login,
811 requirement_id,
812 organization_id,
813 vib_profile_value)
814 SELECT
815 NULL,
816 p_batch_id,
817 p.inventory_item_id,
818 p.material_detail_id,
819 i.inventory_item_id,
820 i.material_detail_id,
821 NULL,
822 NULL,
823 p.cost_alloc,
824 p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
825 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
826 0,
827 -1,
828 sysdate,
829 -1,
830 sysdate,
831 NULL,
832 gmf_vib_id_s.nextval,
833 p.organization_id,
834 l_vib_profile_value
835 FROM gme_material_details p, gme_material_details i
836 WHERE
837 p.batch_id = p_batch_id AND
838 i.batch_id = p_batch_id AND
839 p.line_type = 1 AND
840 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
841 nvl(p.cost_alloc,0) <> 0 AND
842 i.line_type <> 1 AND
843 p.material_detail_id NOT IN (
844 SELECT prod_material_detail_id
845 FROM gmf_batch_requirements_gtmp f
846 WHERE
847 batch_id = p_batch_id );
848 IF g_debug <= gme_debug.g_log_statement THEN
849 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
850 END IF;
851
852 IF g_debug <= gme_debug.g_log_statement THEN
853 gme_debug.put_line ('inserting remaining resources');
854 END IF;
855
856 /* Bug 13442362 13367279 Added gme_batch_steps and joins so that index on gme_batch_step_resources is used */
857
858 INSERT INTO gmf_batch_requirements_gtmp(
859 vib_id,
860 batch_id,
861 product_item_id,
862 prod_material_detail_id,
863 ingredient_item_id,
864 ing_material_detail_id,
865 resources,
866 batchstep_resource_id,
867 derived_cost_alloc,
868 required_doc_qty,
869 delete_mark,
870 created_by,
871 creation_date,
872 last_updated_by,
873 last_update_date,
874 last_update_login,
875 requirement_id,
876 organization_id,
877 vib_profile_value)
878 SELECT
879 NULL,
880 p_batch_id,
881 p.inventory_item_id,
882 p.material_detail_id,
883 NULL,
884 NULL,
885 r.resources,
886 r.batchstep_resource_id,
887 p.cost_alloc,
888 p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
889 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
890 0,
891 -1,
892 sysdate,
893 -1,
894 sysdate,
895 NULL,
896 gmf_vib_id_s.nextval,
897 p.organization_id,
898 l_vib_profile_value
899 FROM gme_material_details p, gme_batch_step_resources r, gme_batch_steps s
900 WHERE
901 p.batch_id = p_batch_id AND
902 s.batch_id = p_batch_id AND
903 s.batchstep_id = r.batchstep_id AND
904 p.line_type = 1 AND
905 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
906 nvl(p.cost_alloc,0) <> 0 AND
907 p.material_detail_id NOT IN (
908 SELECT prod_material_detail_id
909 FROM gmf_batch_requirements_gtmp f
910 WHERE
911 batch_id = p_batch_id );
912
913 IF g_debug <= gme_debug.g_log_statement THEN
914 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
915 END IF;
916
917
918 -- Now update the derived cost alloc and required doc qty
919 IF g_debug <= gme_debug.g_log_statement THEN
920 gme_debug.put_line ('Now updating the derived cost alloc and required doc qty ...');
921 END IF;
922
923 FOR i IN c_total_prod_alloc(p_batch_id) LOOP
924 BEGIN
925 UPDATE gmf_batch_requirements_gtmp
926 SET derived_cost_alloc = derived_cost_alloc/i.total_prod_alloc,
927 required_doc_qty = required_doc_qty/i.total_prod_alloc
928 WHERE
929 batch_id = p_batch_id AND
930 nvl(ing_material_detail_id, -1) = nvl(i.ing_material_detail_id,-1) AND
931 nvl(batchstep_resource_id, -1) = nvl(i.batchstep_resource_id,-1) AND
932 delete_mark = 0;
933 dbms_output.put_line( sql%rowcount || ' rows inserted');
934 EXCEPTION
935 WHEN OTHERS THEN
936 dbms_output.put_line ('Error updating batch requirements');
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
938 RAISE;
939 END;
940 END LOOP;
941
942 IF g_debug <= gme_debug.g_log_statement THEN
943 gme_debug.put_line ('Done updating the derived cost alloc and required doc qty ...');
944 END IF;
945
946 x_return_status := FND_API.G_RET_STS_SUCCESS ;
947
948 IF g_debug <= gme_debug.g_log_procedure THEN
949 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
950 END IF;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
955 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
956 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
957 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
958 FND_MSG_PUB.Add;
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
960 End Create_Temp_Batch_Requirements;
961
962
963 /*
964 --+==========================================================================+
965 --| PROCEDURE NAME |
966 --| Create_VIB_Details |
967 --| |
968 --| TYPE |
969 --| Public |
970 --| |
971 --| USAGE |
972 --| Create_VIB_Details |
973 --| |
974 --| DESCRIPTION |
975 --| |
976 --| PARAMETERS |
977 --| |
978 --| RETURNS |
979 --| None |
980 --| |
981 --| HISTORY |
982 --| rseshadr 01-May-2006 Bug 5190115 - e_invalid_rsrc_reversal was raised |
983 --| but the catch block did not handle this exception |
984 --| |
985 --| umoogala 21-Oct-2006 bug 5607069 - |
986 --| Issue 1) Material and Resources reversals layers were not getting |
987 --| consumed. |
988 --| Fixed code to allocate reversals only if its original |
989 --| layer is consumed by this product, while creating VIB dtls.|
990 --| Issue 2) Material and Resources reversals layers were not getting |
991 --| properly apportioned in the finalization layers. |
992 --| Fix is same as above: |
993 --| Fixed code to allocate reversals only if its original |
994 --| layer is consumed by this product, while creating VIB dtls.|
995 --| pmarada 22-Aug-2007 Bug 6312166. Currently we are not inserting records |
996 --| in VIB when reverse the batch. as part of this fix we are |
997 --| going to insert records in VIB for reversal of bacth |
998 --+==========================================================================+
999 */
1000 PROCEDURE Create_VIB_Details
1001 ( p_api_version IN NUMBER,
1002 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1003 p_tran_rec IN GMF_LAYERS.trans_rec_type,
1004 p_layer_rec IN gmf_incoming_material_layers%ROWTYPE,
1005 x_return_status OUT NOCOPY VARCHAR2,
1006 x_msg_count OUT NOCOPY NUMBER,
1007 x_msg_data OUT NOCOPY VARCHAR2
1008 ) IS
1009
1010 CURSOR c_batch_req IS
1011 SELECT *
1012 FROM gmf_batch_requirements
1013 WHERE
1014 batch_id = p_tran_rec.transaction_source_id AND
1015 prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1016 delete_mark = 0;
1017
1018 CURSOR c_orig_mtl_vib IS
1019 SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
1020 v.consume_ib_pri_qty,
1021 decode (tp.transaction_id2, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
1022 decode (tp.transaction_id2, NULL, v.consume_layer_date, t2.transaction_date) c_trans_date,
1023 decode (tp.transaction_id2, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
1024 decode (tp.transaction_id2, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
1025 decode (tp.transaction_id2, NULL, 'N', 'Y') c_rev_layer,
1026 decode (tp.transaction_id2, NULL, ol.ROWID, ol2.ROWID) c_rowid
1027 FROM gmf_batch_vib_details v,
1028 gmf_incoming_material_layers il,
1029 gmf_outgoing_material_layers ol,
1030 mtl_material_transactions t,
1031 gme_transaction_pairs tp,
1032 gmf_outgoing_material_layers ol2,
1033 mtl_material_transactions t2
1034 WHERE
1035 il.mmt_transaction_id = p_tran_rec.reverse_id AND -- incoming layer of reversed prod yield
1036 -- Bug 6312166. il.mmt_transaction_id = p_tran_rec.transaction_id AND -- incoming layer of reversed prod yield
1037 nvl(il.lot_number, 'x') = nvl(p_tran_rec.lot_number, 'x') AND
1038 -- Bug 6312166. il.lot_number (+) = p_tran_rec.lot_number AND
1039 v.prod_layer_id = il.layer_id AND -- VIB details of above reversed layer
1040 v.line_type <> 0 AND -- material only
1041 ol.layer_id (+) = v.consume_layer_id AND -- getting consumption layer for above reversed prod yield
1042 t.transaction_id (+) = ol.mmt_transaction_id AND -- getting txn for above consumption layer
1043 --
1044 -- below 4 lines, get the above ingredient reversals, if any
1045 --
1046 tp.transaction_id1 (+) = t.transaction_id AND
1047 tp.pair_type(+) = 1 AND
1048 ol2.mmt_transaction_id (+) = tp.transaction_id2 AND
1049 t2.transaction_id (+) = ol2.mmt_transaction_id
1050 ;
1051
1052
1053 CURSOR c_orig_rsrc_vib IS
1054 SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
1055 v.consume_ib_pri_qty, decode (t.reverse_id, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
1056 decode (t.reverse_id, NULL, v.consume_layer_date, t2.trans_date) c_trans_date,
1057 decode (t.reverse_id, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
1058 decode (t.reverse_id, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
1059 decode (t.reverse_id, NULL, 'N', 'Y') c_rev_layer,
1060 decode (t.reverse_id, NULL, ol.ROWID, ol2.ROWID) c_rowid
1061 FROM gmf_batch_vib_details v,
1062 gmf_incoming_material_layers il,
1063 gmf_resource_layers ol,
1064 gme_resource_txns t,
1065 gmf_resource_layers ol2,
1066 gme_resource_txns t2
1067 WHERE
1068 il.mmt_transaction_id = p_tran_rec.reverse_id AND
1069 nvl(il.lot_number, '@@@') = nvl(p_tran_rec.lot_number, '@@@') AND
1070 v.prod_layer_id = il.layer_id AND
1071 v.line_type = 0 AND -- resource only
1072 v.consume_layer_id = ol.layer_id (+) AND
1073 ol.poc_trans_id = t.poc_trans_id (+) AND
1074 t.reverse_id = ol2.poc_trans_id (+) AND
1075 ol2.poc_trans_id = t2.poc_trans_id (+);
1076
1077 /* Bug 8219507 removed mtln from query */
1078
1079 CURSOR c_ing_layers (p_ing_material_detail_id NUMBER) IS
1080 SELECT mmt.inventory_item_id, mmt.organization_id, /* mtln.lot_number, */ mmt.primary_quantity, msi.primary_uom_code,
1081 mmt.transaction_date, md.line_type, tp.transaction_id2 as reverse_id, l.ROWID, l.*
1082 FROM gmf_outgoing_material_layers l,
1083 mtl_material_transactions mmt,
1084 mtl_system_items_b msi,
1085 gme_material_details md,
1086 gme_transaction_pairs tp
1087 WHERE
1088 mmt.transaction_source_type_id = 5 AND
1089 mmt.transaction_source_id = p_tran_rec.transaction_source_id AND
1090 mmt.trx_source_line_id = p_ing_material_detail_id AND
1091 l.mmt_transaction_id = mmt.transaction_id AND
1092 l.delete_mark = 0 AND
1093 l.remaining_ib_doc_qty <> 0 AND
1094 msi.inventory_item_id = mmt.inventory_item_id AND
1095 msi.organization_id = mmt.organization_id AND
1096 md.material_detail_id = p_ing_material_detail_id AND
1097 tp.transaction_id1(+) = mmt.transaction_id AND
1098 tp.pair_type(+) = 1
1099 ORDER BY mmt.transaction_date;
1100
1101 CURSOR c_ing_layers_cnt (p_ing_material_detail_id NUMBER) IS -- Bug 8472152 Added cursor
1102 SELECT count(*)
1103 FROM gmf_outgoing_material_layers l,
1104 mtl_material_transactions mmt,
1105 mtl_system_items_b msi,
1106 gme_material_details md,
1107 gme_transaction_pairs tp
1108 WHERE
1109 mmt.transaction_source_type_id = 5 AND
1110 mmt.transaction_source_id = p_tran_rec.transaction_source_id AND
1111 mmt.trx_source_line_id = p_ing_material_detail_id AND
1112 l.mmt_transaction_id = mmt.transaction_id AND
1113 l.delete_mark = 0 AND
1114 l.remaining_ib_doc_qty <> 0 AND
1115 msi.inventory_item_id = mmt.inventory_item_id AND
1116 msi.organization_id = mmt.organization_id AND
1117 md.material_detail_id = p_ing_material_detail_id AND
1118 tp.transaction_id1(+) = mmt.transaction_id AND
1119 tp.pair_type(+) = 1
1120 ORDER BY mmt.transaction_date;
1121
1122
1123 CURSOR c_rsrc_layers (p_batchstep_resource_id NUMBER) IS
1124 SELECT p.resource_usage, p.trans_qty_um as trans_um, p.trans_date, p.line_type, p.reverse_id, p.organization_id, l.ROWID, l.*
1125 FROM gmf_resource_layers l, gme_resource_txns p
1126 WHERE
1127 p.doc_type = 'PROD' AND
1128 p.doc_id = p_tran_rec.transaction_source_id AND
1129 p.line_id = p_batchstep_resource_id AND
1130 p.completed_ind = 1 AND
1131 p.delete_mark = 0 AND
1132 l.poc_trans_id = p.poc_trans_id and
1133 l.delete_mark = 0 and
1134 l.remaining_ib_doc_qty <> 0
1135 ORDER BY p.trans_date;
1136
1137 l_required_ib_doc_qty NUMBER;
1138 l_remaining_ib_doc_qty NUMBER;
1139 l_consume_ib_doc_qty NUMBER;
1140 l_consume_ib_pri_qty NUMBER;
1141 l_rev_consume_ib_doc_qty NUMBER;
1142 l_cur_consume_ib_doc_qty NUMBER;
1143 l_prev_consume_ib_doc_qty NUMBER;
1144 l_doc_um VARCHAR2(4);
1145 l_line_type PLS_INTEGER;
1146 l_item_um VARCHAR2(4);
1147 l_use_vib VARCHAR2(30);
1148 l_count PLS_INTEGER;
1149 l_orig_layer_consumption_qty NUMBER; -- Bug 5607069
1150 l_ing_count NUMBER; -- Bug 8472152
1151 l_curr_cnt NUMBER; -- Bug 8472152
1152
1153
1154 e_vib_complete EXCEPTION;
1155 e_invalid_consumption EXCEPTION;
1156 e_invalid_mtl_reversal EXCEPTION;
1157 e_invalid_rsrc_reversal EXCEPTION;
1158 e_rsrc_invalid_consumption EXCEPTION;
1159
1160 l_api_name VARCHAR2(30) := 'Create_VIB_Details';
1161
1162 BEGIN
1163 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1164
1165 IF g_debug <= gme_debug.g_log_procedure THEN
1166 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
1167 END IF;
1168
1169 -- Validate that the VIB details do not exist already
1170 SELECT count(*)
1171 INTO l_count
1172 FROM gmf_batch_vib_details
1173 WHERE
1174 prod_layer_id = p_layer_rec.layer_id;
1175
1176 IF l_count > 0 THEN
1177 x_return_status := FND_API.G_RET_STS_ERROR ;
1178 x_msg_data := 'VIB Details already exist';
1179 dbms_output.put_line ('VIB Details already exist');
1180 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_VIB_EXIST');
1181 FND_MSG_PUB.Add;
1182 RETURN;
1183 END IF;
1184
1185 l_use_vib := FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST');
1186 IF l_use_vib IS NULL THEN
1187 l_use_vib := 'N';
1188 END IF;
1189
1190 gme_debug.put_line ('Profile...Use VIB = '||l_use_vib);
1191
1192
1193 -- If this is a product reversal, reverse the VIB layers.
1194 IF p_tran_rec.primary_quantity < 0 and p_tran_rec.reverse_id IS NOT NULL THEN
1195
1196 IF g_debug <= gme_debug.g_log_procedure THEN
1197 gme_debug.put_line ('product reversal, reverse the VIB layers');
1198 END IF;
1199
1200 IF g_debug <= gme_debug.g_log_procedure THEN
1201 gme_debug.put_line ('now reversing material vib layers');
1202 END IF;
1203
1204 FOR v IN c_orig_mtl_vib LOOP
1205 BEGIN
1206 -- For No VIB, the ingredient reversals may already have been consumed
1207 -- by a previous prod yield. In that case, do not reverse anymore.:w
1208 IF l_use_vib = 'N' and v.c_rev_layer = 'Y' and
1209 v.layer_doc_qty <> v.remaining_ib_doc_qty
1210 THEN
1211
1212 SELECT count (1)
1213 INTO l_count
1214 FROM gmf_batch_vib_details vib,
1215 gmf_batch_requirements r
1216 WHERE
1217 r.batch_id = p_tran_rec.transaction_source_id AND
1218 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1219 vib.requirement_id = r.requirement_id AND
1220 vib.consume_layer_id = v.c_layer_id;
1221
1222 IF l_count > 0 THEN
1223 IF g_debug <= gme_debug.g_log_statement THEN
1224 gme_debug.put_line ('No VIB, the ingredient reversals may already have ' ||
1225 'been consumed by a previous prod yield. do not reverse anymore.');
1226 END IF;
1227
1228 RAISE e_invalid_mtl_reversal;
1229 END IF;
1230 END IF;
1231
1232 -- Insert VIB reversals
1233 INSERT INTO gmf_batch_vib_details(
1234 prod_layer_id,
1235 prod_layer_pri_qty,
1236 consume_layer_id,
1237 consume_layer_date,
1238 line_type,
1239 vib_id,
1240 finalize_ind,
1241 consume_ib_doc_qty,
1242 consume_ib_pri_qty,
1243 created_by,
1244 creation_date,
1245 last_updated_by,
1246 last_update_date,
1247 last_update_login,
1248 requirement_id)
1249 VALUES(
1250 p_layer_rec.layer_id,
1251 p_tran_rec.primary_quantity,
1252 v.c_layer_id,
1253 v.c_trans_date,
1254 v.line_type,
1255 NULL,
1256 0,
1257 -v.consume_ib_doc_qty,
1258 -v.consume_ib_pri_qty,
1259 p_tran_rec.created_by,
1260 sysdate,
1261 p_tran_rec.last_updated_by,
1262 sysdate,
1263 p_tran_rec.last_update_login,
1264 v.requirement_id);
1265
1266 UPDATE gmf_outgoing_material_layers
1267 SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
1268 WHERE
1269 ROWID = v.c_rowid;
1270
1271 EXCEPTION
1272 WHEN e_invalid_mtl_reversal THEN
1273 NULL; -- Skip to next row
1274 END;
1275 END LOOP;
1276
1277 IF g_debug <= gme_debug.g_log_procedure THEN
1278 gme_debug.put_line ('now reversing resource vib layers');
1279 END IF;
1280
1281 FOR v IN c_orig_rsrc_vib LOOP
1282 BEGIN
1283 -- For No VIB, the resource reversals may already have been consumed
1284 -- by a previous prod yield. In that case, do not reverse anymore.:w
1285 IF l_use_vib = 'N' and v.c_rev_layer = 'Y' and
1286 v.layer_doc_qty <> v.remaining_ib_doc_qty
1287 THEN
1288
1289 SELECT count (1)
1290 INTO l_count
1291 FROM gmf_batch_vib_details vib,
1292 gmf_batch_requirements r
1293 WHERE
1294 r.batch_id = p_tran_rec.transaction_source_id AND
1295 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1296 vib.requirement_id = r.requirement_id AND
1297 vib.consume_layer_id = v.c_layer_id;
1298
1299 IF l_count > 0 THEN
1300 IF g_debug <= gme_debug.g_log_statement THEN
1301 gme_debug.put_line ('No VIB, the resource reversals may already have ' ||
1302 'been consumed by a previous prod yield. do not reverse anymore.');
1303 END IF;
1304
1305 RAISE e_invalid_rsrc_reversal;
1306 END IF;
1307 END IF;
1308
1309 -- Insert VIB reversals
1310 INSERT INTO gmf_batch_vib_details(
1311 prod_layer_id,
1312 prod_layer_pri_qty,
1313 consume_layer_id,
1314 consume_layer_date,
1315 line_type,
1316 vib_id,
1317 finalize_ind,
1318 consume_ib_doc_qty,
1319 consume_ib_pri_qty,
1320 created_by,
1321 creation_date,
1322 last_updated_by,
1323 last_update_date,
1324 last_update_login,
1325 requirement_id)
1326 VALUES(
1327 p_layer_rec.layer_id,
1328 p_tran_rec.primary_quantity,
1329 v.c_layer_id,
1330 v.c_trans_date,
1331 v.line_type,
1332 NULL,
1333 0,
1334 -v.consume_ib_doc_qty,
1335 -v.consume_ib_pri_qty,
1336 p_tran_rec.created_by,
1337 sysdate,
1338 p_tran_rec.last_updated_by,
1339 sysdate,
1340 p_tran_rec.last_update_login,
1341 v.requirement_id);
1342
1343 UPDATE gmf_resource_layers
1344 SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
1345 WHERE
1346 ROWID = v.c_rowid;
1347
1348 EXCEPTION
1349 WHEN e_invalid_rsrc_reversal THEN
1350 NULL; -- Skip to next row
1351 END;
1352 END LOOP;
1353 RETURN; -- Done with the reversal
1354 END IF;
1355
1356 -- For regular yields follow the following logic.
1357 IF g_debug <= gme_debug.g_log_procedure THEN
1358 gme_debug.put_line ('regular product yield');
1359 END IF;
1360
1361
1362 -- Go through the batch requirement rows for this product and insert
1363 -- the VIB details in the vib table.
1364 FOR req IN c_batch_req LOOP
1365 BEGIN
1366 l_required_ib_doc_qty := p_layer_rec.layer_doc_qty * req.required_doc_qty;
1367
1368 -- If the VIB details are for ingredient or by-product
1369 IF req.ing_material_detail_id IS NOT NULL THEN
1370 -- select ingredient layers that can be consumed for the
1371 -- IB qty
1372
1373 IF g_debug <= gme_debug.g_log_statement THEN
1374 gme_debug.put_line ('processing ingredient or by-product to create vib details');
1375 END IF;
1376
1377 -- Loop for all layers of an ingredient PK Bug 8472152
1378
1379 OPEN c_ing_layers_cnt(req.ing_material_detail_id);
1380 FETCH c_ing_layers_cnt INTO l_ing_count;
1381 CLOSE c_ing_layers_cnt;
1382
1383 l_curr_cnt := 0;
1384
1385 FOR ing in c_ing_layers(req.ing_material_detail_id) LOOP
1386 BEGIN
1387 l_curr_cnt := l_curr_cnt + 1; -- Bug 8472152
1388 IF g_debug <= gme_debug.g_log_statement THEN
1389 gme_debug.put_line ('consuming ing/byProd layer. matl_dtl_id: ' || req.ing_material_detail_id ||
1390 ' consume layer_id: ' ||ing.layer_id );
1391 END IF;
1392
1393 IF l_use_vib = 'Y' THEN
1394 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty;
1395
1396 IF l_required_ib_doc_qty = 0 THEN
1397 RAISE e_vib_complete;
1398 END IF;
1399 -- Bug 8472152 Remaining quantity could be negative No exception
1400 /* IF l_remaining_ib_doc_qty <= 0 THEN
1401 RAISE e_invalid_consumption;
1402 END IF;
1403
1404 IF l_remaining_ib_doc_qty >= l_required_ib_doc_qty THEN
1405 l_consume_ib_doc_qty := l_required_ib_doc_qty;
1406 ELSE
1407 l_consume_ib_doc_qty := l_remaining_ib_doc_qty;
1408 END IF;
1409
1410 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - l_consume_ib_doc_qty;
1411 l_required_ib_doc_qty := l_required_ib_doc_qty - l_consume_ib_doc_qty; */
1412
1413 -- Bug 8472152 modified Code should do following
1414 -- 1) Available for record > required then use required
1415 -- 2) Available for record < required and more records present then use what is available.
1416 -- 3) If last record use all that is required.
1417
1418 IF ((l_remaining_ib_doc_qty >= l_required_ib_doc_qty) OR (l_ing_count = l_curr_cnt)) THEN
1419 l_consume_ib_doc_qty := l_required_ib_doc_qty;
1420 ELSE
1421 l_consume_ib_doc_qty := l_remaining_ib_doc_qty;
1422 END IF;
1423
1424 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - l_consume_ib_doc_qty;
1425 l_required_ib_doc_qty := l_required_ib_doc_qty - l_consume_ib_doc_qty;
1426
1427
1428 ELSE
1429 IF ing.remaining_ib_doc_qty = 0 THEN
1430 RAISE e_invalid_consumption;
1431 END IF;
1432
1433 l_consume_ib_doc_qty := ing.layer_doc_qty * req.derived_cost_alloc;
1434 -- Get the quantity already consumed from this player for the product.
1435 -- If the entire quantity is remaining, no need to check.
1436 IF ing.layer_doc_qty <> ing.remaining_ib_doc_qty THEN
1437 SELECT nvl(sum (consume_ib_doc_qty), 0)
1438 INTO l_prev_consume_ib_doc_qty
1439 FROM gmf_batch_vib_details v,
1440 gmf_batch_requirements r
1441 WHERE
1442 r.batch_id = p_tran_rec.transaction_source_id AND
1443 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1444 v.requirement_id = r.requirement_id AND
1445 v.consume_layer_id = ing.layer_id;
1446
1447 --
1448 -- Bug 5607069: If this is the reversal layer, then see whether original
1449 -- reversed layerd is consumed by this product or not.
1450 -- If consumed, then consume from this reversal layer to nullify the effect of it.
1451 -- If not consumed, then don't consume from this reversal layer.
1452 --
1453 IF ing.reverse_id IS NOT NULL
1454 THEN
1455 SELECT nvl(sum (consume_ib_doc_qty), 0)
1456 INTO l_orig_layer_consumption_qty
1457 FROM gmf_outgoing_material_layers ol,
1458 gmf_batch_vib_details v,
1459 gmf_batch_requirements r
1460 WHERE
1461 ol.mmt_transaction_id = ing.reverse_id AND
1462 v.consume_layer_id = ol.layer_id AND
1463 r.batch_id = p_tran_rec.transaction_source_id AND
1464 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1465 v.requirement_id = r.requirement_id ;
1466
1467 IF l_orig_layer_consumption_qty = 0
1468 THEN
1469 -- Do not consume from this reversal layers, as its original
1470 -- layer is not consumed by this product.
1471 RAISE e_rsrc_invalid_consumption;
1472 END IF;
1473
1474 END IF;
1475 -- End bug 5607069
1476
1477 l_consume_ib_doc_qty := l_consume_ib_doc_qty - l_prev_consume_ib_doc_qty;
1478 IF ABS(l_consume_ib_doc_qty) > ABS(ing.remaining_ib_doc_qty) THEN
1479 l_consume_ib_doc_qty := ing.remaining_ib_doc_qty;
1480 END IF;
1481
1482 IF l_consume_ib_doc_qty = 0 THEN
1483 -- Previous consumption have already consumed this products share
1484 RAISE e_invalid_consumption;
1485 END IF;
1486 END IF;
1487
1488 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty - l_consume_ib_doc_qty;
1489 END IF;
1490
1491 -- Convert the l_consume_ib_doc_qty to primary UOM
1492 l_consume_ib_pri_qty :=
1493 INV_CONVERT.INV_UM_CONVERT(
1494 ITEM_ID => ing.inventory_item_id
1495 , PRECISION => 5
1496 , ORGANIZATION_ID => ing.organization_id
1497 , LOT_NUMBER => ing.lot_number
1498 , FROM_QUANTITY => l_consume_ib_doc_qty
1499 , FROM_UNIT => ing.layer_doc_um
1500 , TO_UNIT => ing.primary_uom_code
1501 , FROM_NAME => NULL
1502 , TO_NAME => NULL
1503 );
1504
1505 INSERT INTO gmf_batch_vib_details(
1506 prod_layer_id,
1507 prod_layer_pri_qty,
1508 consume_layer_id,
1509 consume_layer_date,
1510 line_type,
1511 vib_id,
1512 finalize_ind,
1513 consume_ib_doc_qty,
1514 consume_ib_pri_qty,
1515 created_by,
1516 creation_date,
1517 last_updated_by,
1518 last_update_date,
1519 last_update_login,
1520 requirement_id)
1521 VALUES(
1522 p_layer_rec.layer_id,
1523 p_tran_rec.primary_quantity,
1524 ing.layer_id,
1525 ing.transaction_date,
1526 ing.line_type,
1527 NULL,
1528 0,
1529 l_consume_ib_doc_qty,
1530 l_consume_ib_pri_qty,
1531 p_tran_rec.created_by,
1532 sysdate,
1533 p_tran_rec.last_updated_by,
1534 sysdate,
1535 p_tran_rec.last_update_login,
1536 req.requirement_id);
1537
1538 UPDATE gmf_outgoing_material_layers
1539 SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
1540 WHERE
1541 ROWID = ing.ROWID;
1542 EXCEPTION
1543 WHEN e_invalid_consumption THEN
1544 NULL; -- Skip to next row
1545 END;
1546 END LOOP;
1547 END IF;
1548
1549 -- IF the VIB details are for a resource
1550 IF req.batchstep_resource_id IS NOT NULL THEN
1551 -- select ingredient layers that can be consumed for the
1552 -- IB qty
1553 IF g_debug <= gme_debug.g_log_statement THEN
1554 gme_debug.put_line ('processing ingredient or by-product to create vib details');
1555 END IF;
1556
1557 FOR rsrc IN c_rsrc_layers(req.batchstep_resource_id) LOOP
1558 BEGIN
1559 IF l_use_vib = 'Y' THEN
1560 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty;
1561
1562 IF l_required_ib_doc_qty = 0 THEN
1563 RAISE e_vib_complete;
1564 END IF;
1565
1566 -- insert a row in the VIB detail table
1567 IF l_remaining_ib_doc_qty = 0 THEN
1568 RAISE e_rsrc_invalid_consumption;
1569 END IF;
1570
1571 -- If both required and remaining are -ve, they work in reverse fashion
1572 IF l_remaining_ib_doc_qty >= l_required_ib_doc_qty THEN
1573 l_consume_ib_doc_qty := l_required_ib_doc_qty;
1574 ELSE
1575 l_consume_ib_doc_qty := l_remaining_ib_doc_qty;
1576 END IF;
1577 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - l_consume_ib_doc_qty;
1578 l_required_ib_doc_qty := l_required_ib_doc_qty - l_consume_ib_doc_qty;
1579 ELSE
1580 IF rsrc.remaining_ib_doc_qty = 0 THEN
1581 RAISE e_rsrc_invalid_consumption;
1582 END IF;
1583
1584 l_consume_ib_doc_qty := rsrc.layer_doc_qty * req.derived_cost_alloc;
1585 -- Get the quantity already consumed from this player for the product.
1586 -- If the entire quantity is remaining, no need to check.
1587 IF rsrc.layer_doc_qty <> rsrc.remaining_ib_doc_qty THEN
1588 SELECT nvl(sum (consume_ib_doc_qty), 0)
1589 INTO l_prev_consume_ib_doc_qty
1590 FROM gmf_batch_vib_details v,
1591 gmf_batch_requirements r
1592 WHERE
1593 r.batch_id = p_tran_rec.transaction_source_id AND
1594 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1595 v.requirement_id = r.requirement_id AND
1596 v.consume_layer_id = rsrc.layer_id;
1597
1598 --
1599 -- Bug 5607069: If this is the reversal layer, then see whether original
1600 -- reversed layerd is consumed by this product or not.
1601 -- If consumed, then consume from this reversal layer to nullify the effect of it.
1602 -- If not consumed, then don't consume from this reversal layer.
1603 --
1604 IF rsrc.reverse_id IS NOT NULL
1605 THEN
1606 SELECT nvl(sum (consume_ib_doc_qty), 0)
1607 INTO l_orig_layer_consumption_qty
1608 FROM gmf_resource_layers rl,
1609 gmf_batch_vib_details v,
1610 gmf_batch_requirements r
1611 WHERE
1612 rl.poc_trans_id = rsrc.reverse_id AND
1613 v.consume_layer_id = rl.layer_id AND
1614 r.batch_id = p_tran_rec.transaction_source_id AND
1615 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1616 v.requirement_id = r.requirement_id ;
1617
1618 IF l_orig_layer_consumption_qty = 0
1619 THEN
1620 -- Do not consume from this reversal layers, as its original
1621 -- layer is not consumed by this product.
1622 RAISE e_rsrc_invalid_consumption;
1623 END IF;
1624
1625 END IF;
1626 -- End bug 5607069
1627
1628
1629 l_consume_ib_doc_qty := l_consume_ib_doc_qty - l_prev_consume_ib_doc_qty;
1630 --
1631 -- Bug 5607069: synching material and resource layer code
1632 --
1633 IF ABS(l_consume_ib_doc_qty) > ABS(rsrc.remaining_ib_doc_qty) THEN
1634 l_consume_ib_doc_qty := rsrc.remaining_ib_doc_qty;
1635 END IF;
1636 -- End bug 5607069
1637
1638 --
1639 -- Bug 5607069: synching material and resource layer code
1640 --
1641 -- IF l_consume_ib_doc_qty <= 0 THEN
1642 --
1643 IF l_consume_ib_doc_qty = 0 THEN
1644 -- Previous consumption have already consumed this products share
1645 RAISE e_rsrc_invalid_consumption;
1646 END IF;
1647 END IF;
1648
1649 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty - l_consume_ib_doc_qty;
1650 END IF;
1651
1652 -- Convert the l_consume_ib_doc_qty to primary UOM
1653 l_consume_ib_pri_qty :=
1654 INV_CONVERT.INV_UM_CONVERT(
1655 ITEM_ID => 0
1656 , PRECISION => 5
1657 , ORGANIZATION_ID => rsrc.organization_id
1658 , LOT_NUMBER => NULL
1659 , FROM_QUANTITY => l_consume_ib_doc_qty
1660 , FROM_UNIT => rsrc.layer_doc_um
1661 , TO_UNIT => rsrc.trans_um
1662 , FROM_NAME => NULL
1663 , TO_NAME => NULL
1664 );
1665
1666
1667 INSERT INTO gmf_batch_vib_details(
1668 prod_layer_id,
1669 prod_layer_pri_qty,
1670 consume_layer_id,
1671 consume_layer_date,
1672 line_type,
1673 vib_id,
1674 finalize_ind,
1675 consume_ib_doc_qty,
1676 consume_ib_pri_qty,
1677 created_by,
1678 creation_date,
1679 last_updated_by,
1680 last_update_date,
1681 last_update_login,
1682 requirement_id)
1683 VALUES(
1684 p_layer_rec.layer_id,
1685 p_tran_rec.primary_quantity,
1686 rsrc.layer_id,
1687 rsrc.trans_date,
1688 rsrc.line_type,
1689 NULL,
1690 0,
1691 l_consume_ib_doc_qty,
1692 l_consume_ib_pri_qty,
1693 p_tran_rec.created_by,
1694 sysdate,
1695 p_tran_rec.last_updated_by,
1696 sysdate,
1697 p_tran_rec.last_update_login,
1698 req.requirement_id );
1699
1700 UPDATE gmf_resource_layers
1701 SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
1702 WHERE
1703 ROWID = rsrc.ROWID;
1704 EXCEPTION
1705 WHEN e_rsrc_invalid_consumption THEN
1706 NULL; -- Skip to next row
1707 END;
1708 END LOOP;
1709
1710 END IF;
1711
1712 -- There is still some IB quantity not consumed, insert a NULL layer consumption.
1713 IF l_use_vib = 'Y' and l_required_ib_doc_qty <> 0 THEN
1714
1715 IF g_debug <= gme_debug.g_log_statement THEN
1716 gme_debug.put_line ('inserting NULL consumption layer for matl_dtl_id: ' || req.ing_material_detail_id);
1717 END IF;
1718
1719 l_consume_ib_pri_qty := 0;
1720 IF req.ing_material_detail_id IS NOT NULL THEN
1721
1722 SELECT m.dtl_um, m.line_type, i.primary_uom_code
1723 INTO l_doc_um, l_line_type, l_item_um
1724 FROM gme_material_details m, mtl_system_items_b i
1725 WHERE
1726 m.batch_id = req.batch_id AND
1727 m.material_detail_id = req.ing_material_detail_id AND
1728 i.inventory_item_id = m.inventory_item_id AND
1729 i.organization_id = m.organization_id;
1730
1731 l_consume_ib_pri_qty :=
1732 INV_CONVERT.INV_UM_CONVERT(
1733 ITEM_ID => req.ingredient_item_id
1734 , PRECISION => 5
1735 , ORGANIZATION_ID => req.organization_id
1736 , LOT_NUMBER => NULL
1737 , FROM_QUANTITY => l_required_ib_doc_qty
1738 , FROM_UNIT => l_doc_um
1739 , TO_UNIT => l_item_um
1740 , FROM_NAME => NULL
1741 , TO_NAME => NULL
1742 );
1743
1744 ELSE
1745 SELECT m.usage_um, 0, r.std_usage_uom -- Bug 8472152 changed from usage_uom, std_usage_uom
1746 INTO l_doc_um, l_line_type, l_item_um
1747 FROM gme_batch_step_resources m, cr_rsrc_mst_b r
1748 WHERE
1749 m.batch_id = req.batch_id AND
1750 m.batchstep_resource_id = req.batchstep_resource_id AND
1751 m.resources = r.resources;
1752
1753 l_consume_ib_pri_qty :=
1754 INV_CONVERT.INV_UM_CONVERT(
1755 ITEM_ID => 0
1756 , PRECISION => 5
1757 , ORGANIZATION_ID => req.organization_id
1758 , LOT_NUMBER => NULL
1759 , FROM_QUANTITY => l_required_ib_doc_qty
1760 , FROM_UNIT => l_doc_um
1761 , TO_UNIT => l_item_um
1762 , FROM_NAME => NULL
1763 , TO_NAME => NULL
1764 );
1765
1766
1767 END IF;
1768
1769 INSERT INTO gmf_batch_vib_details(
1770 prod_layer_id,
1771 prod_layer_pri_qty,
1772 consume_layer_id,
1773 consume_layer_date,
1774 line_type,
1775 vib_id,
1776 finalize_ind,
1777 consume_ib_doc_qty,
1778 consume_ib_pri_qty,
1779 created_by,
1780 creation_date,
1781 last_updated_by,
1782 last_update_date,
1783 last_update_login,
1784 requirement_id)
1785 VALUES(
1786 p_layer_rec.layer_id,
1787 p_tran_rec.primary_quantity,
1788 NULL,
1789 p_tran_rec.transaction_date,
1790 l_line_type,
1791 NULL,
1792 0,
1793 l_required_ib_doc_qty,
1794 l_consume_ib_pri_qty,
1795 p_tran_rec.created_by,
1796 sysdate,
1797 p_tran_rec.last_updated_by,
1798 sysdate,
1799 p_tran_rec.last_update_login,
1800 req.requirement_id);
1801 END IF;
1802 EXCEPTION
1803 WHEN e_vib_complete THEN
1804 NULL;
1805 END;
1806 END LOOP; -- c_batch_req
1807
1808 x_return_status := FND_API.G_RET_STS_SUCCESS;
1809
1810 IF g_debug <= gme_debug.g_log_procedure THEN
1811 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1812 END IF;
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
1817 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
1818 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1819 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
1820 FND_MSG_PUB.Add;
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1822 END;
1823
1824 /*
1825 --+==========================================================================+
1826 --| PROCEDURE NAME |
1827 --| Finalize_VIB_Details |
1828 --| |
1829 --| TYPE |
1830 --| Public |
1831 --| |
1832 --| USAGE |
1833 --| Finalize_VIB_Details |
1834 --| |
1835 --| DESCRIPTION |
1836 --| |
1837 --| PARAMETERS |
1838 --| |
1839 --| RETURNS |
1840 --| None |
1841 --| |
1842 --| HISTORY |
1843 --| |
1844 --| umoogala 21-Oct-2006 bug 5607069 - |
1845 --| Issue 1) Material and Resources reversals layers were not getting |
1846 --| consumed. |
1847 --| Fixed code to allocate reversals only if its original |
1848 --| layer is consumed by this product, while creating VIB dtls.|
1849 --| Issue 2) Material and Resources reversals layers were not getting |
1850 --| properly apportioned in the finalization layers. |
1851 --| Fix is same as above: |
1852 --| Fixed code to allocate reversals only if its original |
1853 --| layer is consumed by this product, while creating VIB dtls.|
1854 --+==========================================================================+
1855 */
1856 PROCEDURE Finalize_VIB_Details
1857 ( p_api_version IN NUMBER,
1858 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1859 p_batch_id IN NUMBER,
1860 x_return_status OUT NOCOPY VARCHAR2,
1861 x_msg_count OUT NOCOPY NUMBER,
1862 x_msg_data OUT NOCOPY VARCHAR2) IS
1863
1864 CURSOR c_batch_req IS
1865 SELECT *
1866 FROM gmf_batch_requirements
1867 WHERE
1868 batch_id = p_batch_id AND
1869 delete_mark = 0
1870 ORDER BY prod_material_detail_id;
1871
1872 CURSOR c_null_consume_layers IS
1873 SELECT v.*, l.layer_doc_qty, l.layer_doc_um, l.mmt_transaction_id, l.lot_number, l.mmt_organization_id
1874 FROM gmf_batch_vib_details v,
1875 gmf_batch_requirements r,
1876 gmf_incoming_material_layers l
1877 WHERE
1878 r.batch_id = p_batch_id AND
1879 v.requirement_id = r.requirement_id AND
1880 l.layer_id = v.prod_layer_id AND
1881 v.finalize_ind = 0 AND
1882 v.consume_layer_id IS NULL
1883 ORDER BY v.prod_layer_id
1884 ;
1885
1886 /* Bug 8219507 removed mtln from query */
1887
1888 CURSOR c_last_prod_yield (p_prod_material_detail_id NUMBER) IS
1889 SELECT l.*, t.primary_quantity,
1890 t.inventory_item_id -- Bug 5607069
1891 FROM gmf_incoming_material_layers l,
1892 mtl_material_transactions t
1893 WHERE
1894 t.trx_source_line_id = p_prod_material_detail_id AND
1895 t.transaction_source_id = p_batch_id AND
1896 t.transaction_source_type_id = 5 AND
1897 l.mmt_transaction_id = t.transaction_id AND
1898 l.pseudo_layer_id IS NULL AND
1899 not exists (select 'x' from gme_transaction_pairs tp
1900 where transaction_id1 = t.transaction_id and tp.pair_type = 1)
1901 ORDER BY l.creation_date DESC;
1902
1903 /* Bug 8219507 removed mtln from query */
1904
1905 CURSOR c_remaining_ing_layers (p_ing_material_detail_id NUMBER) IS
1906 SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.inventory_item_id, t.transaction_date,
1907 NULL lot_number, md.line_type, t.primary_quantity, msi.primary_uom_code, l.ROWID, tp.transaction_id2 as reverse_id,
1908 l.layer_doc_qty
1909 FROM gmf_outgoing_material_layers l, mtl_material_transactions t,
1910 gme_material_details md,
1911 mtl_system_items_b msi, gme_transaction_pairs tp
1912 WHERE
1913 t.trx_source_line_id = p_ing_material_detail_id AND
1914 t.transaction_source_id = p_batch_id AND
1915 t.transaction_source_type_id = 5 AND
1916 l.mmt_transaction_id = t.transaction_id AND
1917 l.remaining_ib_doc_qty <> 0 AND
1918 l.delete_mark = 0 AND
1919 md.material_detail_id = p_ing_material_detail_id AND
1920 msi.inventory_item_id = t.inventory_item_id AND
1921 msi.organization_id = t.organization_id AND
1922 tp.transaction_id1(+) = t.transaction_id
1923 ;
1924
1925 /* Bug 13442362 13367279 Added t.doc_type = 'PROD' AND t.doc_id = p_batch_id so that index on gme_resource_txns is used */
1926
1927 CURSOR c_remaining_rsrc_layers (p_batchstep_resource_id NUMBER) IS
1928 SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.line_type, t.trans_date,
1929 t.resource_usage, t.trans_qty_um trans_um, l.ROWID, t.reverse_id, l.layer_doc_qty
1930 FROM gmf_resource_layers l,
1931 gme_resource_txns t
1932 WHERE
1933 t.doc_type = 'PROD' AND
1934 t.doc_id = p_batch_id AND
1935 l.poc_trans_id = t.poc_trans_id AND
1936 t.line_id = p_batchstep_resource_id AND
1937 l.remaining_ib_doc_qty <> 0 and
1938 l.delete_mark = 0;
1939
1940 CURSOR c_finalize_layer_consumption IS
1941 SELECT v.consume_layer_id, v.line_type, sum(v.consume_ib_doc_qty) consume_ib_doc_qty
1942 FROM
1943 gmf_batch_vib_details v,
1944 gmf_batch_requirements r
1945 WHERE
1946 r.batch_id = p_batch_id AND
1947 v.requirement_id = r.requirement_id AND
1948 v.finalize_ind = 1 AND
1949 v.consume_layer_id IS NOT NULL
1950 GROUP BY v.consume_layer_id, v.line_type;
1951
1952 l_batch_status gme_batch_header.batch_status%TYPE;
1953 prev_prod_material_detail_id NUMBER;
1954 l_total_cost_alloc NUMBER;
1955 l_pseudo_prod_layer_id NUMBER;
1956 l_last_prod_layer c_last_prod_yield%ROWTYPE;
1957 l_batch_close_date DATE;
1958 l_consume_ib_doc_qty NUMBER;
1959 l_consume_ib_pri_qty NUMBER;
1960 l_remaining_ib_doc_qty NUMBER;
1961 l_user_id NUMBER;
1962 l_count PLS_INTEGER;
1963 --
1964 -- Bug 5607069: Following 4 variables added
1965 l_use_vib VARCHAR2(30);
1966 l_orig_layer_consumption_qty NUMBER;
1967 e_invalid_consumption EXCEPTION;
1968 l_prev_consume_ib_doc_qty NUMBER;
1969
1970 l_api_name VARCHAR2(30) := 'Finalize_VIB_Details';
1971
1972 l_prev_prod_layer_id NUMBER;
1973
1974 BEGIN
1975 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1976
1977 IF g_debug <= gme_debug.g_log_procedure THEN
1978 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
1979 END IF;
1980
1981 -- Validate batch_id
1982 BEGIN
1983 SELECT batch_status, last_updated_by, batch_close_date
1984 INTO l_batch_status, l_user_id, l_batch_close_date
1985 FROM gme_batch_header
1986 WHERE batch_id = p_batch_id;
1987
1988 IF l_batch_status <> 4 THEN
1989 x_return_status := FND_API.G_RET_STS_ERROR ;
1990 --dbms_output.put_line ('Batch is not in CLOSE Status');
1991 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_NOT_CLOSE');
1992 FND_MSG_PUB.Add;
1993 RETURN;
1994 END IF;
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1998 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
1999 FND_MSG_PUB.Add;
2000 RAISE;
2001 END;
2002
2003 -- Check if he VIB details already exist for this batch
2004 BEGIN
2005 SELECT count(*)
2006 INTO l_count
2007 FROM gmf_batch_vib_details v,
2008 gmf_batch_requirements r
2009 WHERE
2010 r.batch_id = p_batch_id AND
2011 r.requirement_id = v.requirement_id and
2012 v.finalize_ind = 1;
2013
2014 IF l_count > 0 THEN
2015 x_return_status := FND_API.G_RET_STS_ERROR ;
2016 --dbms_output.put_line ('VIB details already exist for this batch');
2017 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_FINAL_VIB_EXIST');
2018 FND_MSG_PUB.Add;
2019 RETURN;
2020 END IF;
2021 EXCEPTION
2022 WHEN OTHERS THEN
2023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2024 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
2025 FND_MSG_PUB.Add;
2026 RAISE;
2027 END;
2028
2029
2030 l_use_vib := FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST');
2031 IF l_use_vib IS NULL THEN
2032 l_use_vib := 'N';
2033 END IF;
2034
2035 gme_debug.put_line ('Use VIB = ' || l_use_vib );
2036 IF g_debug <= gme_debug.g_log_statement THEN
2037 gme_debug.put_line ('reversing out all NULL consumptions layers, if any');
2038 END IF;
2039
2040
2041 -- reverse out all NULL consumption layers
2042 FOR n IN c_null_consume_layers LOOP
2043
2044 IF g_debug <= gme_debug.g_log_statement THEN
2045 gme_debug.put_line ('NULL consumption layer found for prod layer/txn/lot: ' ||
2046 n.prod_layer_id ||'/'||n.mmt_transaction_id||'/'||n.lot_number ||
2047 ' requirement_id for NULL consumption layer: ' || n.requirement_id);
2048 END IF;
2049
2050 BEGIN
2051
2052 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
2053 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
2054
2055
2056 INSERT INTO gmf_incoming_material_layers(
2057 layer_id,
2058 mmt_transaction_id,
2059 mmt_organization_id,
2060 lot_number,
2061 layer_doc_qty,
2062 layer_doc_um,
2063 layer_date,
2064 pseudo_layer_id,
2065 final_cost_ind,
2066 gl_posted_ind,
2067 created_by,
2068 creation_date,
2069 last_updated_by,
2070 last_update_date,
2071 last_update_login,
2072 accounted_flag)
2073 VALUES(
2074 l_pseudo_prod_layer_id,
2075 n.mmt_transaction_id,
2076 n.mmt_organization_id,
2077 n.lot_number,
2078 n.layer_doc_qty,
2079 n.layer_doc_um,
2080 l_batch_close_date,
2081 n.prod_layer_id,
2082 0,
2083 0,
2084 l_user_id,
2085 sysdate,
2086 l_user_id,
2087 sysdate,
2088 NULL,
2089 'N');
2090
2091 -- Create a VIB layer for the pseudo product layer reversing the original VIB row
2092 INSERT INTO gmf_batch_vib_details(
2093 prod_layer_id,
2094 prod_layer_pri_qty,
2095 consume_layer_id,
2096 consume_layer_date,
2097 line_type,
2098 vib_id,
2099 finalize_ind,
2100 consume_ib_doc_qty,
2101 consume_ib_pri_qty,
2102 created_by,
2103 creation_date,
2104 last_updated_by,
2105 last_update_date,
2106 last_update_login,
2107 requirement_id)
2108 VALUES(
2109 l_pseudo_prod_layer_id,
2110 n.prod_layer_pri_qty,
2111 NULL,
2112 n.consume_layer_date,
2113 n.line_type,
2114 NULL,
2115 1,
2116 -n.consume_ib_doc_qty,
2117 -n.consume_ib_pri_qty,
2118 l_user_id,
2119 sysdate,
2120 l_user_id,
2121 sysdate,
2122 NULL,
2123 n.requirement_id);
2124
2125
2126 END;
2127 END LOOP;
2128
2129 -- going thru the requirement details
2130
2131 IF g_debug <= gme_debug.g_log_procedure THEN
2132 gme_debug.put_line ('looping thru all the requirements to create finalization layers');
2133 END IF;
2134
2135 prev_prod_material_detail_id := -99;
2136 FOR req IN c_batch_req LOOP
2137 BEGIN
2138
2139 IF g_debug <= gme_debug.g_log_statement THEN
2140 gme_debug.put_line ('Processing prod_material_detail_id: ' || req.prod_material_detail_id ||
2141 ' ing_material_detail_id/batchstep_resource_id: ' || req.ing_material_detail_id||
2142 '/'||req.batchstep_resource_id);
2143 END IF;
2144
2145 -- Get the last yield for the product
2146 IF (prev_prod_material_detail_id <> req.prod_material_detail_id) THEN
2147 l_last_prod_layer.layer_id := NULL;
2148 OPEN c_last_prod_yield(req.prod_material_detail_id);
2149 FETCH c_last_prod_yield INTO l_last_prod_layer;
2150 CLOSE c_last_prod_yield;
2151 prev_prod_material_detail_id := req.prod_material_detail_id;
2152 l_pseudo_prod_layer_id := NULL;
2153 END IF;
2154
2155 -- Get any remaining ingredients layers for this requirement row.
2156 IF (l_last_prod_layer.layer_id IS NOT NULL AND req.ing_material_detail_id IS NOT NULL) THEN
2157
2158 FOR ing IN c_remaining_ing_layers (req.ing_material_detail_id) LOOP
2159 BEGIN
2160
2161 IF g_debug <= gme_debug.g_log_statement THEN
2162 gme_debug.put_line ('processing remaining ingredients layers...');
2163 END IF;
2164
2165 --
2166 -- Bug 5607069: When using Actuals:
2167 -- before consuming remaining qty, see whether it is already
2168 -- got consumed from this layer or not. If yes, then don't consume again
2169 --
2170 IF l_use_vib = 'N'
2171 THEN
2172 SELECT nvl(sum (consume_ib_doc_qty), 0)
2173 INTO l_prev_consume_ib_doc_qty
2174 FROM gmf_batch_vib_details v,
2175 gmf_incoming_material_layers il,
2176 mtl_material_transactions mmt
2177 WHERE
2178 v.requirement_id = req.requirement_id AND
2179 v.consume_layer_id = ing.layer_id AND
2180 il.layer_id = v.prod_layer_id AND
2181 mmt.transaction_id = il.mmt_transaction_id AND
2182 mmt.transaction_source_type_id = 5 AND
2183 mmt.inventory_item_id = req.product_item_id AND
2184 mmt.organization_id = req.organization_id
2185 ;
2186
2187 IF (l_prev_consume_ib_doc_qty <> 0)
2188 THEN
2189 -- Do not consume from this layer, as it is already
2190 -- consumed
2191 RAISE e_invalid_consumption ;
2192 END IF;
2193
2194
2195 --
2196 -- Bug 5607069: If this is the reversal layer, then see whether original
2197 -- reversed layer is consumed by this product or not.
2198 -- If consumed, then consume from this reversal layer to nullify the effect of it.
2199 -- If not consumed, then don't consume from this reversal layer.
2200 --
2201
2202 IF ing.reverse_id IS NOT NULL
2203 THEN
2204 SELECT nvl(sum (consume_ib_doc_qty), 0)
2205 INTO l_orig_layer_consumption_qty
2206 FROM gmf_outgoing_material_layers ol,
2207 gmf_batch_vib_details v,
2208 gmf_incoming_material_layers il,
2209 mtl_material_transactions mmt
2210 WHERE
2211 ol.mmt_transaction_id = ing.reverse_id AND
2212 ol.lot_number = ing.lot_number AND
2213 v.consume_layer_id = ol.layer_id AND
2214 v.requirement_id = req.requirement_id AND
2215 il.layer_id = v.prod_layer_id AND
2216 mmt.transaction_id = il.mmt_transaction_id AND
2217 mmt.transaction_source_type_id = 5 AND
2218 mmt.inventory_item_id = req.product_item_id AND
2219 mmt.organization_id = req.organization_id
2220 ;
2221
2222
2223 IF (l_orig_layer_consumption_qty = 0)
2224 THEN
2225 -- Do not consume from this reversal layers, as its original
2226 -- layer is not consumed by this product.
2227 RAISE e_invalid_consumption ;
2228 END IF;
2229
2230 END IF;
2231
2232 END IF;
2233 -- End bug 5607069
2234
2235 IF l_pseudo_prod_layer_id IS NULL THEN
2236 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
2237 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
2238
2239 INSERT INTO gmf_incoming_material_layers(
2240 layer_id,
2241 mmt_transaction_id,
2242 mmt_organization_id,
2243 lot_number,
2244 layer_doc_qty,
2245 layer_doc_um,
2246 layer_date,
2247 pseudo_layer_id,
2248 final_cost_ind,
2249 gl_posted_ind,
2250 created_by,
2251 creation_date,
2252 last_updated_by,
2253 last_update_date,
2254 last_update_login,
2255 accounted_flag)
2256 VALUES(
2257 l_pseudo_prod_layer_id,
2258 l_last_prod_layer.mmt_transaction_id,
2259 l_last_prod_layer.mmt_organization_id,
2260 l_last_prod_layer.lot_number,
2261 l_last_prod_layer.layer_doc_qty,
2262 l_last_prod_layer.layer_doc_um,
2263 l_batch_close_date,
2264 l_last_prod_layer.layer_id,
2265 0,
2266 0,
2267 l_user_id,
2268 sysdate,
2269 l_user_id,
2270 sysdate,
2271 NULL,
2272 'N');
2273 END IF;
2274
2275 --
2276 -- Bug 5607069
2277 --
2278 IF l_use_vib = 'N'
2279 THEN
2280 l_consume_ib_doc_qty := ing.layer_doc_qty * req.derived_cost_alloc;
2281 ELSE
2282 l_consume_ib_doc_qty := ing.remaining_ib_doc_qty * req.derived_cost_alloc;
2283 END IF;
2284 -- Bug 5607069
2285
2286 l_consume_ib_pri_qty :=
2287 INV_CONVERT.INV_UM_CONVERT(
2288 ITEM_ID => ing.inventory_item_id
2289 , PRECISION => 5
2290 , ORGANIZATION_ID => req.organization_id
2291 , LOT_NUMBER => NULL
2292 , FROM_QUANTITY => l_consume_ib_doc_qty
2293 , FROM_UNIT => ing.layer_doc_um
2294 , TO_UNIT => ing.primary_uom_code
2295 , FROM_NAME => NULL
2296 , TO_NAME => NULL
2297 );
2298
2299
2300 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty - l_consume_ib_doc_qty;
2301
2302 INSERT INTO gmf_batch_vib_details(
2303 prod_layer_id,
2304 prod_layer_pri_qty,
2305 consume_layer_id,
2306 consume_layer_date,
2307 line_type,
2308 vib_id,
2309 finalize_ind,
2310 consume_ib_doc_qty,
2311 consume_ib_pri_qty,
2312 created_by,
2313 creation_date,
2314 last_updated_by,
2315 last_update_date,
2316 last_update_login,
2317 requirement_id)
2318 VALUES(
2319 l_pseudo_prod_layer_id,
2320 l_last_prod_layer.primary_quantity,
2321 ing.layer_id,
2322 ing.transaction_date,
2323 ing.line_type,
2324 NULL,
2325 1,
2326 l_consume_ib_doc_qty,
2327 l_consume_ib_pri_qty,
2328 l_user_id,
2329 sysdate,
2330 l_user_id,
2331 sysdate,
2332 NULL,
2333 req.requirement_id);
2334
2335
2336 --
2337 -- Bug 5607069: Added exception block.
2338 --
2339 EXCEPTION
2340 WHEN e_invalid_consumption THEN
2341 NULL; -- Skip to next row
2342 END;
2343 END LOOP;
2344 END IF;
2345
2346 --
2347 -- Now doing for resources
2348 --
2349 IF (l_last_prod_layer.layer_id IS NOT NULL AND req.batchstep_resource_id IS NOT NULL) THEN
2350 FOR rsrc IN c_remaining_rsrc_layers (req.batchstep_resource_id) LOOP
2351 BEGIN
2352
2353 IF g_debug <= gme_debug.g_log_statement THEN
2354 gme_debug.put_line ('processing remaining resource layers...');
2355 END IF;
2356
2357
2358 --
2359 -- Bug 5607069: When using Actuals:
2360 -- before consuming remaining qty, see whether it is already
2361 -- got consumed from this layer or not. If yes, then don't consume again
2362 --
2363 IF l_use_vib = 'N'
2364 THEN
2365 SELECT nvl(sum (consume_ib_doc_qty), 0)
2366 INTO l_prev_consume_ib_doc_qty
2367 FROM gmf_batch_vib_details v,
2368 gmf_incoming_material_layers il,
2369 mtl_material_transactions mmt
2370 WHERE
2371 v.requirement_id = req.requirement_id AND
2372 v.consume_layer_id = rsrc.layer_id AND
2373 il.layer_id = v.prod_layer_id AND
2374 mmt.transaction_id = il.mmt_transaction_id AND
2375 mmt.inventory_item_id = req.product_item_id AND
2376 mmt.organization_id = req.organization_id AND
2377 mmt.transaction_source_type_id = 5
2378 ;
2379
2380 IF (l_prev_consume_ib_doc_qty <> 0)
2381 THEN
2382 -- Do not consume from this layer, as it is already
2383 -- consumed
2384 RAISE e_invalid_consumption ;
2385 END IF;
2386
2387
2388 --
2389 -- Bug 5607069: If this is the reversal layer, then see whether original
2390 -- reversed layer is consumed by this product or not.
2391 -- If consumed, then consume from this reversal layer to nullify the effect of it.
2392 -- If not consumed, then don't consume from this reversal layer.
2393 --
2394 IF rsrc.reverse_id IS NOT NULL
2395 THEN
2396 SELECT nvl(sum (consume_ib_doc_qty), 0)
2397 INTO l_orig_layer_consumption_qty
2398 FROM gmf_resource_layers rl,
2399 gmf_batch_vib_details v,
2400 gmf_incoming_material_layers il,
2401 mtl_material_transactions mmt
2402 WHERE
2403 rl.poc_trans_id = rsrc.reverse_id AND
2404 v.consume_layer_id = rl.layer_id AND
2405 v.requirement_id = req.requirement_id AND
2406 il.layer_id = v.prod_layer_id AND
2407 mmt.transaction_id = il.mmt_transaction_id AND
2408 mmt.inventory_item_id = req.product_item_id AND
2409 mmt.organization_id = req.organization_id AND
2410 mmt.transaction_source_type_id = 5
2411 ;
2412
2413 IF (l_orig_layer_consumption_qty = 0)
2414 THEN
2415 -- Do not consume from this reversal layers, as its original
2416 -- layer is not consumed by this product.
2417 RAISE e_invalid_consumption ;
2418 END IF;
2419
2420 END IF;
2421
2422 END IF;
2423 -- End bug 5607069
2424
2425 IF l_pseudo_prod_layer_id IS NULL THEN
2426 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
2427 -- Bug 6887598 mmt_organization_id should not be NULL
2428 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
2429 INSERT INTO gmf_incoming_material_layers(
2430 layer_id,
2431 mmt_transaction_id,
2432 mmt_organization_id, -- B6887598
2433 lot_number,
2434 layer_doc_qty,
2435 layer_doc_um,
2436 layer_date,
2437 pseudo_layer_id,
2438 final_cost_ind,
2439 gl_posted_ind,
2440 created_by,
2441 creation_date,
2442 last_updated_by,
2443 last_update_date,
2444 last_update_login,
2445 accounted_flag)
2446 VALUES(
2447 l_pseudo_prod_layer_id,
2448 l_last_prod_layer.mmt_transaction_id,
2449 l_last_prod_layer.mmt_organization_id, -- B6887598
2450 l_last_prod_layer.lot_number,
2451 l_last_prod_layer.layer_doc_qty,
2452 l_last_prod_layer.layer_doc_um,
2453 l_batch_close_date,
2454 l_last_prod_layer.layer_id,
2455 0,
2456 0,
2457 l_user_id,
2458 sysdate,
2459 l_user_id,
2460 sysdate,
2461 NULL,
2462 'N');
2463 END IF;
2464 --
2465 -- Bug 5607069
2466 --
2467 IF l_use_vib = 'N'
2468 THEN
2469 l_consume_ib_doc_qty := rsrc.layer_doc_qty * req.derived_cost_alloc;
2470 ELSE
2471 l_consume_ib_doc_qty := rsrc.remaining_ib_doc_qty * req.derived_cost_alloc;
2472 END IF;
2473
2474 l_consume_ib_pri_qty :=
2475 INV_CONVERT.INV_UM_CONVERT(
2476 ITEM_ID => 0
2477 , PRECISION => 5
2478 , ORGANIZATION_ID => req.organization_id
2479 , LOT_NUMBER => NULL
2480 , FROM_QUANTITY => l_consume_ib_doc_qty
2481 , FROM_UNIT => rsrc.layer_doc_um
2482 , TO_UNIT => rsrc.trans_um
2483 , FROM_NAME => NULL
2484 , TO_NAME => NULL
2485 );
2486
2487
2488 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty - l_consume_ib_doc_qty;
2489 INSERT INTO gmf_batch_vib_details(
2490 prod_layer_id,
2491 prod_layer_pri_qty,
2492 consume_layer_id,
2493 consume_layer_date,
2494 line_type,
2495 vib_id,
2496 finalize_ind,
2497 consume_ib_doc_qty,
2498 consume_ib_pri_qty,
2499 created_by,
2500 creation_date,
2501 last_updated_by,
2502 last_update_date,
2503 last_update_login,
2504 requirement_id)
2505 VALUES(
2506 l_pseudo_prod_layer_id,
2507 l_last_prod_layer.primary_quantity,
2508 rsrc.layer_id,
2509 rsrc.trans_date,
2510 rsrc.line_type,
2511 NULL,
2512 1,
2513 l_consume_ib_doc_qty,
2514 l_consume_ib_pri_qty,
2515 l_user_id,
2516 sysdate,
2517 l_user_id,
2518 sysdate,
2519 NULL,
2520 req.requirement_id);
2521
2522 --
2523 -- Bug 5607069: Added exception block.
2524 --
2525 EXCEPTION
2526 WHEN e_invalid_consumption THEN
2527 NULL; -- Skip to next row
2528 END;
2529 END LOOP;
2530 END IF;
2531 END;
2532 END LOOP;
2533
2534 -- Now update the remaining_ib_doc_qty based upon the finalized layers.
2535 IF g_debug <= gme_debug.g_log_statement THEN
2536 gme_debug.put_line ('Now update the remaining_ib_doc_qty based upon the finalized layers');
2537 END IF;
2538
2539 FOR c IN c_finalize_layer_consumption LOOP
2540 BEGIN
2541 IF c.line_type = 0 THEN
2542 UPDATE gmf_resource_layers
2543 SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
2544 WHERE
2545 layer_id = c.consume_layer_id;
2546 ELSE
2547 UPDATE gmf_outgoing_material_layers
2548 SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
2549 WHERE
2550 layer_id = c.consume_layer_id;
2551 END IF;
2552 END;
2553 END LOOP;
2554
2555 x_return_status := FND_API.G_RET_STS_SUCCESS;
2556
2557 IF g_debug <= gme_debug.g_log_procedure THEN
2558 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2559 END IF;
2560
2561 EXCEPTION
2562 WHEN OTHERS THEN
2563 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
2564 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
2565 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
2566 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
2567 FND_MSG_PUB.Add;
2568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2569 END;
2570
2571 /*
2572 --+==========================================================================+
2573 --| PROCEDURE NAME |
2574 --| Revert_Finalization |
2575 --| |
2576 --| TYPE |
2577 --| Public |
2578 --| |
2579 --| USAGE |
2580 --| Revert_Finalization |
2581 --| |
2582 --| DESCRIPTION |
2583 --| |
2584 --| PARAMETERS |
2585 --| |
2586 --| RETURNS |
2587 --| None |
2588 --| |
2589 --| HISTORY |
2590 --| Parag Kanetkar Bug 11839588 12-May-2011 |
2591 --| Delete batch Close events if they exist since batch is reopened. |
2592 --| Bug 12982011. When deleting batch close events, Create accounting |
2593 --| May not have been run. Eliminate xla_ae_headers when deleting event. |
2594 --| |
2595 --+==========================================================================+
2596 */
2597 PROCEDURE Revert_Finalization
2598 ( p_api_version IN NUMBER,
2599 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2600 p_batch_id IN NUMBER,
2601 x_return_status OUT NOCOPY VARCHAR2,
2602 x_msg_count OUT NOCOPY NUMBER,
2603 x_msg_data OUT NOCOPY VARCHAR2) IS
2604
2605 CURSOR c_finalize_rows IS
2606 SELECT v.ROWID, v.consume_ib_doc_qty, v.consume_layer_id, v.line_type
2607 FROM gmf_batch_vib_details v, gmf_batch_requirements r
2608 WHERE
2609 r.batch_id = p_batch_id and
2610 r.requirement_id = v.requirement_id and
2611 v.finalize_ind = 1 and
2612 v.consume_layer_id IS NOT NULL;
2613
2614 -- Bug 11839588
2615 CURSOR check_posted_close_events IS
2616 SELECT count(*) FROM gmf_xla_extract_headers
2617 WHERE entity_code = 'PRODUCTION'
2618 AND event_class_code = 'BATCH_CLOSE'
2619 AND transaction_id = p_batch_id
2620 AND accounted_flag IS NULL;
2621 -- Bug 11839588 12982011 13099547 get ledger_id as well.
2622 CURSOR check_batch_close_events IS
2623 SELECT event_id, ledger_id FROM gmf_xla_extract_headers
2624 WHERE entity_code = 'PRODUCTION'
2625 AND event_class_code = 'BATCH_CLOSE'
2626 AND transaction_id = p_batch_id;
2627
2628 l_close_event_count NUMBER := 0;
2629 l_posted_count NUMBER := 0;
2630
2631 l_batch_status PLS_INTEGER;
2632 l_api_name VARCHAR2(30) := 'Revert_Finalization';
2633 BEGIN
2634
2635 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2636
2637 IF g_debug <= gme_debug.g_log_procedure THEN
2638 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2639 END IF;
2640
2641 -- Validate batch_id
2642 BEGIN
2643 SELECT batch_status
2644 INTO l_batch_status
2645 FROM gme_batch_header
2646 WHERE batch_id = p_batch_id;
2647
2648 IF l_batch_status = 4 THEN
2649 x_return_status := FND_API.G_RET_STS_ERROR ;
2650 --dbms_output.put_line ('Cannot revert finalization of a closed batch ');
2651 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_CLOSED');
2652 FND_MSG_PUB.Add;
2653 RETURN;
2654 END IF;
2655 -- Bug 11839588
2656 OPEN check_posted_close_events;
2657 FETCH check_posted_close_events INTO l_posted_count;
2658 CLOSE check_posted_close_events;
2659
2660 IF (l_posted_count >= 1) THEN
2661
2662 GMF_LAYERS.log_message (
2663 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2664 p_procedure_name => 'Revert_Finalization',
2665 p_parameters => p_batch_id,
2666 p_message => l_posted_count||' Posted events exist. Batch Can not be Reopened.',
2667 p_error_type => 'E');
2668
2669 x_return_status := FND_API.G_RET_STS_ERROR ;
2670 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_CLOSED');
2671 FND_MSG_PUB.Add;
2672 RETURN;
2673 END IF;
2674
2675
2676 EXCEPTION
2677 WHEN OTHERS THEN
2678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2679 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
2680 FND_MSG_PUB.Add;
2681 RAISE;
2682 END;
2683 -- Parag Kanetkar Bug 11839588
2684 -- Delete batch Close events if they exist since batch is reopened.
2685 -- PK Bug 12982011 eliminate xla_ae_headers. Create accounting may not have been run.
2686 -- or may be run for multiple ledgers. Bug 13099547
2687 -- Use l_event.ledger_id selected in check_batch_close_events Cursor
2688
2689 FOR l_event IN check_batch_close_events LOOP
2690
2691 l_close_event_count := l_close_event_count + 1;
2692
2693 INSERT INTO xla_events_int_gt
2694 (entity_id
2695 ,application_id
2696 ,ledger_id
2697 ,entity_code
2698 ,event_status_code
2699 ,event_id
2700 )
2701 SELECT
2702 xe.entity_id
2703 ,xe.application_id
2704 ,l_event.ledger_id
2705 ,'PRODUCTION'
2706 ,xe.event_status_code
2707 ,xe.event_id
2708 FROM xla_events xe
2709 WHERE xe.application_id = 555
2710 AND xe.event_id = l_event.event_id
2711 AND xe.event_type_code = 'CLOS';
2712
2713 IF g_debug <= gme_debug.g_log_statement THEN
2714 gme_debug.put_line ('Found Batch close event_id = '||l_event.event_id || ' Ledger_id= '||l_event.ledger_id);
2715 gme_debug.put_line ('deleting batch close events extract lines ');
2716 END IF;
2717
2718 GMF_LAYERS.log_message (
2719 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2720 p_procedure_name => 'Revert_Finalization',
2721 p_parameters => p_batch_id,
2722 p_message => ' Deleting event and extract data for Batch Close Event '||l_event.event_id,
2723 p_error_type => 'E');
2724
2725 DELETE FROM gmf_xla_extract_lines
2726 WHERE Header_id = (SELECT header_id FROM gmf_xla_extract_headers
2727 WHERE entity_code = 'PRODUCTION'
2728 AND event_class_code = 'BATCH_CLOSE'
2729 AND transaction_id = p_batch_id
2730 AND event_id = l_event.event_id);
2731
2732 IF g_debug <= gme_debug.g_log_statement THEN
2733 gme_debug.put_line ('deleting batch close events extract header ');
2734 END IF;
2735
2736 DELETE FROM gmf_xla_extract_headers
2737 WHERE entity_code = 'PRODUCTION'
2738 AND event_class_code = 'BATCH_CLOSE'
2739 AND transaction_id = p_batch_id
2740 AND event_id = l_event.event_id;
2741
2742 END LOOP;
2743
2744 IF (l_close_event_count >= 1) THEN
2745
2746 IF g_debug <= gme_debug.g_log_statement THEN
2747 gme_debug.put_line ('deleting batch close events');
2748 END IF;
2749 xla_events_pkg.delete_bulk_events( p_application_id => 555);
2750 delete from xla_events_int_gt ;
2751 END IF;
2752
2753 -- End Bug 11839588
2754 -- Delete all rows from gmf_incoming_material_layers which are used in
2755 -- gmf_batch_vib_details with finalize_ind = 1
2756 IF g_debug <= gme_debug.g_log_statement THEN
2757 gme_debug.put_line ('deleting pseudo layers...');
2758 END IF;
2759
2760 DELETE from gmf_incoming_material_layers
2761 WHERE
2762 pseudo_layer_id IS NOT NULL AND
2763 layer_id in (
2764 SELECT prod_layer_id
2765 FROM gmf_batch_vib_details v,
2766 gmf_batch_requirements r
2767 WHERE
2768 r.batch_id = p_batch_id AND
2769 r.requirement_id = v.requirement_id AND
2770 v.finalize_ind = 1);
2771
2772 IF g_debug <= gme_debug.g_log_statement THEN
2773 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
2774 END IF;
2775
2776
2777 -- Delete all rows from the gmf_batch_vib_details with finalize_ind = 1
2778 -- and the comsume_layer_id is NULL.
2779 IF g_debug <= gme_debug.g_log_statement THEN
2780 gme_debug.put_line ('now deleting NULL finalized consumption layers');
2781 END IF;
2782
2783 DELETE from gmf_batch_vib_details
2784 WHERE
2785 finalize_ind = 1 and
2786 consume_layer_id IS NULL and
2787 requirement_id in (
2788 SELECT requirement_id
2789 FROM gmf_batch_requirements
2790 WHERE
2791 Batch_id = p_batch_id);
2792
2793 IF g_debug <= gme_debug.g_log_statement THEN
2794 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
2795 END IF;
2796
2797 IF g_debug <= gme_debug.g_log_statement THEN
2798 gme_debug.put_line ('now deleting regular finalized consumption layers. Also, updating remaining qty in outgoing layers table.');
2799 END IF;
2800
2801 FOR f IN c_finalize_rows LOOP
2802 BEGIN
2803 -- Update the layers material, resource remaining_doc_qty
2804 IF f.line_type = 0 THEN
2805 UPDATE gmf_resource_layers
2806 SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
2807 WHERE
2808 layer_id = f.consume_layer_id;
2809 ELSE
2810 UPDATE gmf_outgoing_material_layers
2811 SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
2812 WHERE
2813 layer_id = f.consume_layer_id;
2814 END IF;
2815
2816 -- Delete the row from the gmf_batch_vib_detail table.
2817 DELETE from gmf_batch_vib_details
2818 WHERE
2819 ROWID = f.ROWID;
2820
2821 END;
2822 END LOOP;
2823
2824 x_return_status := FND_API.G_RET_STS_SUCCESS;
2825
2826 IF g_debug <= gme_debug.g_log_procedure THEN
2827 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2828 END IF;
2829
2830 EXCEPTION
2831 WHEN OTHERS THEN
2832 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
2833 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
2834 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
2835 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
2836 FND_MSG_PUB.Add;
2837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2838 END;
2839
2840 /*
2841 PROCEDURE allocate_ingredients
2842 (
2843 p_ac_proc_id IN NUMBER,
2844 x_return_status OUT NOCOPY VARCHAR2,
2845 x_msg_count OUT NOCOPY NUMBER,
2846 x_msg_data OUT NOCOPY VARCHAR2
2847 )
2848 IS
2849
2850 CURSOR get_latest_yield (
2851 p_co_code VARCHAR2,
2852 p_start_date DATE,
2853 p_end_date DATE
2854 )
2855 IS
2856 SELECT layer_id, trans_id, layer_doc_qty, layer_doc_um, layer_date, pseudo_layer_id,
2857 final_cost_ind, gl_posted_ind
2858 FROM (
2859 SELECT
2860 il.layer_id, il.trans_id, il.layer_doc_qty, il.layer_doc_um, il.layer_date, il.pseudo_layer_id,
2861 il.final_cost_ind, il.gl_posted_ind,
2862 hdr.batch_id,
2863 RANK() OVER(partition by hdr.batch_id ORDER BY hdr.batch_id, il.layer_date desc, il.layer_id desc) layer_rank
2864 FROM
2865 gme_batch_header hdr,
2866 sy_orgn_mst orgn,
2867 gmf_incoming_material_layers il,
2868 ic_tran_pnd pnd
2869 WHERE
2870 il.layer_date >= p_start_date
2871 AND il.layer_date <= p_end_date
2872 AND il.trans_id IS NOT NULL
2873 AND pnd.trans_id = il.trans_id
2874 AND orgn.co_code = p_co_code
2875 AND hdr.plant_code = orgn.orgn_code
2876 AND hdr.batch_id = pnd.doc_id
2877 AND hdr.batch_status <> 4
2878 ) a
2879 WHERE a.layer_rank = 1
2880 and a.layer_id = 9
2881 ORDER BY batch_id, layer_date desc
2882 ;
2883
2884 l_co_code cm_cldr_hdr.co_code%TYPE;
2885 l_start_date DATE;
2886 l_end_date DATE;
2887
2888 l_layer_rec gmf_incoming_material_layers%ROWTYPE;
2889 l_trans_rec ic_tran_pnd%ROWTYPE;
2890
2891 BEGIN
2892
2893 x_return_status := FND_API.G_RET_STS_SUCCESS ; --xxxremove
2894
2895 SELECT hdr.co_code, dtl.start_date, dtl.end_date
2896 INTO l_co_code, l_start_date, l_end_date
2897 FROM cm_acpr_ctl acpr, cm_cldr_dtl dtl, cm_cldr_hdr hdr
2898 WHERE acpr.acproc_id = p_ac_proc_id
2899 AND hdr.calendar_code = acpr.calendar_code
2900 AND hdr.cost_mthd_code = acpr.cost_mthd_code
2901 AND hdr.calendar_code = dtl.calendar_code
2902 AND dtl.period_code = acpr.period_code
2903 ;
2904
2905 OPEN get_latest_yield(l_co_code, l_start_date, l_end_date);
2906 LOOP
2907 FETCH get_latest_yield
2908 INTO l_layer_rec.layer_id, l_layer_rec.trans_id,
2909 l_layer_rec.layer_doc_qty, l_layer_rec.layer_doc_um,
2910 l_layer_rec.layer_date, l_layer_rec.pseudo_layer_id,
2911 l_layer_rec.final_cost_ind, l_layer_rec.gl_posted_ind;
2912
2913 EXIT WHEN get_latest_yield%NOTFOUND;
2914
2915 SELECT pnd.*
2916 INTO l_trans_rec
2917 FROM mtl_material_transactions pnd
2918 WHERE transaction_id = l_layer_rec.trans_id
2919 ;
2920
2921 dbms_output.put_line('processing layer_id: ' || l_layer_rec.layer_id);
2922
2923
2924 -- Now generate the VIB details for this product transaction.
2925 GMF_VIB.Create_VIB_Details (
2926 1.0,
2927 FND_API.G_TRUE,
2928 l_trans_rec,
2929 l_layer_rec,
2930 x_return_status,
2931 x_msg_count,
2932 x_msg_data,
2933 3
2934 );
2935
2936 END LOOP;
2937
2938 CLOSE get_latest_yield;
2939
2940 END allocate_ingredients;
2941 */
2942
2943 END GMF_VIB;