[Home] [Help]
PACKAGE BODY: APPS.GMF_VIB
Source
1 PACKAGE BODY GMF_VIB AS
2 /* $Header: GMFVIBB.pls 120.2.12010000.2 2008/10/21 20:53:14 rpatangy ship $ */
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
448 CURSOR c_batch_products (c_batch_id NUMBER) IS
449 SELECT m.material_detail_id, m.inventory_item_id as item_id, m.organization_id,
450 decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) prod_plan_qty,
451 m.cost_alloc, s.batchstep_id
452 FROM gme_material_details m, gme_batch_step_items s
453 WHERE m.batch_id = c_batch_id AND
454 m.line_type = 1 AND
455 m.material_detail_id = s.material_detail_id and
456 decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 and
457 nvl(m.cost_alloc,0) <> 0;
458
459 CURSOR c_step_dependencies (c_batch_id NUMBER, c_batchstep_id NUMBER) IS
460 SELECT dep_step_id
461 FROM GME_BATCH_STEP_DEPENDENCIES
462 WHERE
463 batch_id = c_batch_id
464 START WITH batchstep_id = c_batchstep_id
465 CONNECT BY PRIOR dep_step_id = batchstep_id
466 UNION
467 SELECT c_batchstep_id FROM DUAL;
468
469 CURSOR c_total_prod_alloc (c_batch_id NUMBER) IS
470 SELECT ing_material_detail_id, batchstep_resource_id,
471 SUM(derived_cost_alloc) total_prod_alloc
472 FROM gmf_batch_requirements_gtmp
473 WHERE batch_id = c_batch_id
474 GROUP BY ing_material_detail_id, batchstep_resource_id;
475
476
477 l_batch_status gme_batch_header.batch_status%TYPE;
478 l_use_item_step_dep VARCHAR2(30);
479 l_vib_profile_value VARCHAR2(30);
480 l_api_name VARCHAR2(30) := 'Create_Temp_Batch_Requirements';
481 BEGIN
482 x_return_status := FND_API.G_RET_STS_SUCCESS ;
483
484 IF g_debug <= gme_debug.g_log_procedure THEN
485 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
486 END IF;
487
488 -- Validate batch_id
489 BEGIN
490 SELECT batch_status
491 INTO l_batch_status
492 FROM gme_batch_header
493 WHERE batch_id = p_batch_id;
494
495 /* Bug 5491419. Added status 4.
496 IF l_batch_status not in (2, 3) THEN
497 */
498 IF l_batch_status not in (2, 3, 4) THEN
499 -- x_return_status := FND_API.G_RET_STS_ERROR ;
500 dbms_output.put_line ('Batch is not in WIP/Cert/Close Status');
501 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_NOT_WIP');
502 -- FND_MSG_PUB.Add;
503 x_return_status := FND_API.G_RET_STS_SUCCESS ;
504 RETURN;
505 END IF;
506
507 /* Bug 6125370 - Start: Validate product lines*/
508 l_count := 0;
509 SELECT count(*)
510 INTO l_count
511 FROM gme_material_details m
512 WHERE m.batch_id = p_batch_id AND
513 m.line_type = 1 AND
514 decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 AND
515 nvl(m.cost_alloc,0) <> 0;
516
517 IF l_count = 0 THEN
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 FND_MESSAGE.SET_NAME('GMF', 'GMF_INVALID_BATCH');
520 FND_MSG_PUB.Add;
521 RETURN;
522 END IF;
523 /* Bug 6125370 - End */
524 EXCEPTION
525 WHEN OTHERS THEN
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
527 dbms_output.put_line ('Invalid Batch ID');
528 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
529 FND_MSG_PUB.Add;
530 RAISE;
531 END;
532
533 l_use_item_step_dep := fnd_profile.value ('GMF_USE_ITEM_STEP_DEPENDENCIES');
534 IF (l_use_item_step_dep IS NULL) THEN
535 l_use_item_step_dep := 'N';
536 END IF;
537
538 l_vib_profile_value := fnd_profile.value ('GMF_USE_VIB_FOR_ACOST');
539 IF (l_vib_profile_value IS NULL) THEN
540 l_vib_profile_value := 'N';
541 END IF;
542
543 gme_debug.put_line ('profiles. step_dep: ' || l_use_item_step_dep || ' vib: ' || l_vib_profile_value);
544
545 -- Delete the temp table first
546 DELETE from gmf_batch_requirements_gtmp;
547
548 -- Get all products and step association
549 IF g_debug <= gme_debug.g_log_statement THEN
550 gme_debug.put_line ('Get all products and step association');
551 END IF;
552
553 IF l_use_item_step_dep = 'Y' THEN
554 FOR p IN c_batch_products(p_batch_id)
555 LOOP
556 -- Get all dependant steps for the product step
557 FOR ds IN c_step_dependencies (p_batch_id, p.batchstep_id)
558 LOOP
559 -- insert records into the batch requirements table
560 INSERT INTO gmf_batch_requirements_gtmp(
561 vib_id,
562 batch_id,
563 product_item_id,
564 prod_material_detail_id,
565 ingredient_item_id,
566 ing_material_detail_id,
567 resources,
568 batchstep_resource_id,
569 derived_cost_alloc,
570 required_doc_qty,
571 delete_mark,
572 created_by,
573 creation_date,
574 last_updated_by,
575 last_update_date,
576 last_update_login,
577 requirement_id,
578 organization_id,
579 vib_profile_value)
580 SELECT
581 NULL,
582 p_batch_id,
583 p.item_id,
584 p.material_detail_id,
585 m.inventory_item_id,
586 m.material_detail_id,
587 NULL,
588 NULL,
589 p.cost_alloc,
590 p.cost_alloc * ( decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) /
591 p.prod_plan_qty),
592 0,
593 -1,
594 sysdate,
595 -1,
596 sysdate,
597 NULL,
598 gmf_vib_id_s.nextval,
599 p.organization_id,
600 l_vib_profile_value
601 FROM gme_batch_step_items s,
602 gme_material_details m
603 WHERE batchstep_id = ds.dep_step_id AND
604 s.material_detail_id = m.material_detail_id AND
605 m.line_type <> 1;
606
607 INSERT INTO gmf_batch_requirements_gtmp(
608 vib_id,
609 batch_id,
610 product_item_id,
611 prod_material_detail_id,
612 ingredient_item_id,
613 ing_material_detail_id,
614 resources,
615 batchstep_resource_id,
616 derived_cost_alloc,
617 required_doc_qty,
618 delete_mark,
619 created_by,
620 creation_date,
621 last_updated_by,
622 last_update_date,
623 last_update_login,
624 requirement_id,
625 organization_id,
626 vib_profile_value)
627 SELECT
628 NULL,
629 p_batch_id,
630 p.item_id,
631 p.material_detail_id,
632 NULL,
633 NULL,
634 m.resources,
635 m.batchstep_resource_id,
636 p.cost_alloc,
637 p.cost_alloc * ( nvl(m.plan_rsrc_usage,0) / p.prod_plan_qty),
638 0,
639 -1,
640 sysdate,
641 -1,
642 sysdate,
643 NULL,
644 gmf_vib_id_s.nextval,
645 p.organization_id,
646 l_vib_profile_value
647 FROM gme_batch_step_resources m
648 WHERE batchstep_id = ds.dep_step_id;
649
650 END LOOP;
651 END LOOP;
652 END IF;
653
654 -- Now insert any remaining ing/res which was not used for any product
655 IF g_debug <= gme_debug.g_log_statement THEN
656 gme_debug.put_line ('Now insert any remaining ingredients which was not used for any product...');
657 END IF;
658
659 INSERT INTO gmf_batch_requirements_gtmp(
660 vib_id,
661 batch_id,
662 product_item_id,
663 prod_material_detail_id,
664 ingredient_item_id,
665 ing_material_detail_id,
666 resources,
667 batchstep_resource_id,
668 derived_cost_alloc,
669 required_doc_qty,
670 delete_mark,
671 created_by,
672 creation_date,
673 last_updated_by,
674 last_update_date,
675 last_update_login,
676 requirement_id,
677 organization_id,
678 vib_profile_value)
679 SELECT
680 NULL,
681 p_batch_id,
682 p.inventory_item_id,
683 p.material_detail_id,
684 i.inventory_item_id,
685 i.material_detail_id,
686 NULL,
687 NULL,
688 p.cost_alloc,
689 p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
690 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
691 0,
692 -1,
693 sysdate,
694 -1,
695 sysdate,
696 NULL,
697 gmf_vib_id_s.nextval,
698 p.organization_id,
699 l_vib_profile_value
700 FROM gme_material_details p, gme_material_details i
701 WHERE
702 p.batch_id = p_batch_id AND
703 i.batch_id = p_batch_id AND
704 p.line_type = 1 AND
705 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
706 nvl(p.cost_alloc,0) <> 0 AND
707 i.line_type <> 1 AND
708 i.material_detail_id NOT IN (
709 SELECT nvl(ing_material_detail_id, -99)
710 FROM gmf_batch_requirements_gtmp f
711 WHERE
712 batch_id = p_batch_id );
713
714 IF g_debug <= gme_debug.g_log_statement THEN
715 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
716 gme_debug.put_line ('Now insert any remaining resources which was not used for any product...');
717 END IF;
718
719
720 INSERT INTO gmf_batch_requirements_gtmp(
721 vib_id,
722 batch_id,
723 product_item_id,
724 prod_material_detail_id,
725 ingredient_item_id,
726 ing_material_detail_id,
727 resources,
728 batchstep_resource_id,
729 derived_cost_alloc,
730 required_doc_qty,
731 delete_mark,
732 created_by,
733 creation_date,
734 last_updated_by,
735 last_update_date,
736 last_update_login,
737 requirement_id,
738 organization_id,
739 vib_profile_value)
740 SELECT
741 NULL,
742 p_batch_id,
743 p.inventory_item_id,
744 p.material_detail_id,
745 NULL,
746 NULL,
747 r.resources,
748 r.batchstep_resource_id,
749 p.cost_alloc,
750 p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
751 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
752 0,
753 -1,
754 sysdate,
755 -1,
756 sysdate,
757 NULL,
758 gmf_vib_id_s.nextval,
759 p.organization_id,
760 l_vib_profile_value
761 FROM gme_material_details p, gme_batch_step_resources r
762 WHERE
763 p.batch_id = p_batch_id AND
764 r.batch_id = p_batch_id AND
765 p.line_type = 1 AND
766 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
767 nvl(p.cost_alloc,0) <> 0 AND
768 r.batchstep_resource_id NOT IN (
769 SELECT nvl(batchstep_resource_id, -99)
770 FROM gmf_batch_requirements_gtmp f
771 WHERE
772 batch_id = p_batch_id );
773
774 IF g_debug <= gme_debug.g_log_statement THEN
775 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
776 END IF;
777
778 -- Now insert any product that may have been missed out
779 IF g_debug <= gme_debug.g_log_statement THEN
780 gme_debug.put_line ('Now insert any product that may have been missed out...');
781 END IF;
782
783 INSERT INTO gmf_batch_requirements_gtmp(
784 vib_id,
785 batch_id,
786 product_item_id,
787 prod_material_detail_id,
788 ingredient_item_id,
789 ing_material_detail_id,
790 resources,
791 batchstep_resource_id,
792 derived_cost_alloc,
793 required_doc_qty,
794 delete_mark,
795 created_by,
796 creation_date,
797 last_updated_by,
798 last_update_date,
799 last_update_login,
800 requirement_id,
801 organization_id,
802 vib_profile_value)
803 SELECT
804 NULL,
805 p_batch_id,
806 p.inventory_item_id,
807 p.material_detail_id,
808 i.inventory_item_id,
809 i.material_detail_id,
810 NULL,
811 NULL,
812 p.cost_alloc,
813 p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
814 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
815 0,
816 -1,
817 sysdate,
818 -1,
819 sysdate,
820 NULL,
821 gmf_vib_id_s.nextval,
822 p.organization_id,
823 l_vib_profile_value
824 FROM gme_material_details p, gme_material_details i
825 WHERE
826 p.batch_id = p_batch_id AND
827 i.batch_id = p_batch_id AND
828 p.line_type = 1 AND
829 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
830 nvl(p.cost_alloc,0) <> 0 AND
831 i.line_type <> 1 AND
832 p.material_detail_id NOT IN (
833 SELECT prod_material_detail_id
834 FROM gmf_batch_requirements_gtmp f
835 WHERE
836 batch_id = p_batch_id );
837 IF g_debug <= gme_debug.g_log_statement THEN
838 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
839 END IF;
840
841 IF g_debug <= gme_debug.g_log_statement THEN
842 gme_debug.put_line ('inserting remaining resources');
843 END IF;
844 INSERT INTO gmf_batch_requirements_gtmp(
845 vib_id,
846 batch_id,
847 product_item_id,
848 prod_material_detail_id,
849 ingredient_item_id,
850 ing_material_detail_id,
851 resources,
852 batchstep_resource_id,
853 derived_cost_alloc,
854 required_doc_qty,
855 delete_mark,
856 created_by,
857 creation_date,
858 last_updated_by,
859 last_update_date,
860 last_update_login,
861 requirement_id,
862 organization_id,
863 vib_profile_value)
864 SELECT
865 NULL,
866 p_batch_id,
867 p.inventory_item_id,
868 p.material_detail_id,
869 NULL,
870 NULL,
871 r.resources,
872 r.batchstep_resource_id,
873 p.cost_alloc,
874 p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
875 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
876 0,
877 -1,
878 sysdate,
879 -1,
880 sysdate,
881 NULL,
882 gmf_vib_id_s.nextval,
883 p.organization_id,
884 l_vib_profile_value
885 FROM gme_material_details p, gme_batch_step_resources r
886 WHERE
887 p.batch_id = p_batch_id AND
888 r.batch_id = p_batch_id AND
889 p.line_type = 1 AND
890 decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
891 nvl(p.cost_alloc,0) <> 0 AND
892 p.material_detail_id NOT IN (
893 SELECT prod_material_detail_id
894 FROM gmf_batch_requirements_gtmp f
895 WHERE
896 batch_id = p_batch_id );
897
898 IF g_debug <= gme_debug.g_log_statement THEN
899 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
900 END IF;
901
902
903 -- Now update the derived cost alloc and required doc qty
904 IF g_debug <= gme_debug.g_log_statement THEN
905 gme_debug.put_line ('Now updating the derived cost alloc and required doc qty ...');
906 END IF;
907
908 FOR i IN c_total_prod_alloc(p_batch_id) LOOP
909 BEGIN
910 UPDATE gmf_batch_requirements_gtmp
911 SET derived_cost_alloc = derived_cost_alloc/i.total_prod_alloc,
912 required_doc_qty = required_doc_qty/i.total_prod_alloc
913 WHERE
914 batch_id = p_batch_id AND
915 nvl(ing_material_detail_id, -1) = nvl(i.ing_material_detail_id,-1) AND
916 nvl(batchstep_resource_id, -1) = nvl(i.batchstep_resource_id,-1) AND
917 delete_mark = 0;
918 dbms_output.put_line( sql%rowcount || ' rows inserted');
919 EXCEPTION
920 WHEN OTHERS THEN
921 dbms_output.put_line ('Error updating batch requirements');
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
923 RAISE;
924 END;
925 END LOOP;
926
927 IF g_debug <= gme_debug.g_log_statement THEN
928 gme_debug.put_line ('Done updating the derived cost alloc and required doc qty ...');
929 END IF;
930
931 x_return_status := FND_API.G_RET_STS_SUCCESS ;
932
933 IF g_debug <= gme_debug.g_log_procedure THEN
934 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
935 END IF;
936
937 EXCEPTION
938 WHEN OTHERS THEN
939 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
940 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
941 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
942 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
943 FND_MSG_PUB.Add;
944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
945 End Create_Temp_Batch_Requirements;
946
947
948 /*
949 --+==========================================================================+
950 --| PROCEDURE NAME |
951 --| Create_VIB_Details |
952 --| |
953 --| TYPE |
954 --| Public |
955 --| |
956 --| USAGE |
957 --| Create_VIB_Details |
958 --| |
959 --| DESCRIPTION |
960 --| |
961 --| PARAMETERS |
962 --| |
963 --| RETURNS |
964 --| None |
965 --| |
966 --| HISTORY |
967 --| rseshadr 01-May-2006 Bug 5190115 - e_invalid_rsrc_reversal was raised |
968 --| but the catch block did not handle this exception |
969 --| |
970 --| umoogala 21-Oct-2006 bug 5607069 - |
971 --| Issue 1) Material and Resources reversals layers were not getting |
972 --| consumed. |
973 --| Fixed code to allocate reversals only if its original |
974 --| layer is consumed by this product, while creating VIB dtls.|
975 --| Issue 2) Material and Resources reversals layers were not getting |
976 --| properly apportioned in the finalization layers. |
977 --| Fix is same as above: |
978 --| Fixed code to allocate reversals only if its original |
979 --| layer is consumed by this product, while creating VIB dtls.|
980 --| pmarada 22-Aug-2007 Bug 6312166. Currently we are not inserting records |
981 --| in VIB when reverse the batch. as part of this fix we are |
982 --| going to insert records in VIB for reversal of bacth |
983 --+==========================================================================+
984 */
985 PROCEDURE Create_VIB_Details
986 ( p_api_version IN NUMBER,
987 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
988 p_tran_rec IN GMF_LAYERS.trans_rec_type,
989 p_layer_rec IN gmf_incoming_material_layers%ROWTYPE,
990 x_return_status OUT NOCOPY VARCHAR2,
991 x_msg_count OUT NOCOPY NUMBER,
992 x_msg_data OUT NOCOPY VARCHAR2
993 ) IS
994
995 CURSOR c_batch_req IS
996 SELECT *
997 FROM gmf_batch_requirements
998 WHERE
999 batch_id = p_tran_rec.transaction_source_id AND
1000 prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1001 delete_mark = 0;
1002
1003 CURSOR c_orig_mtl_vib IS
1004 SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
1005 v.consume_ib_pri_qty,
1006 decode (tp.transaction_id2, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
1007 decode (tp.transaction_id2, NULL, v.consume_layer_date, t2.transaction_date) c_trans_date,
1008 decode (tp.transaction_id2, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
1009 decode (tp.transaction_id2, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
1010 decode (tp.transaction_id2, NULL, 'N', 'Y') c_rev_layer,
1011 decode (tp.transaction_id2, NULL, ol.ROWID, ol2.ROWID) c_rowid
1012 FROM gmf_batch_vib_details v,
1013 gmf_incoming_material_layers il,
1014 gmf_outgoing_material_layers ol,
1015 mtl_material_transactions t,
1016 gme_transaction_pairs tp,
1017 gmf_outgoing_material_layers ol2,
1018 mtl_material_transactions t2
1019 WHERE
1020 il.mmt_transaction_id = p_tran_rec.reverse_id AND -- incoming layer of reversed prod yield
1021 -- Bug 6312166. il.mmt_transaction_id = p_tran_rec.transaction_id AND -- incoming layer of reversed prod yield
1022 nvl(il.lot_number, 'x') = nvl(p_tran_rec.lot_number, 'x') AND
1023 -- Bug 6312166. il.lot_number (+) = p_tran_rec.lot_number AND
1024 v.prod_layer_id = il.layer_id AND -- VIB details of above reversed layer
1025 v.line_type <> 0 AND -- material only
1026 ol.layer_id (+) = v.consume_layer_id AND -- getting consumption layer for above reversed prod yield
1027 t.transaction_id (+) = ol.mmt_transaction_id AND -- getting txn for above consumption layer
1028 --
1029 -- below 4 lines, get the above ingredient reversals, if any
1030 --
1031 tp.transaction_id1 (+) = t.transaction_id AND
1032 tp.pair_type(+) = 1 AND
1033 ol2.mmt_transaction_id (+) = tp.transaction_id2 AND
1034 t2.transaction_id (+) = ol2.mmt_transaction_id
1035 ;
1036
1037
1038 CURSOR c_orig_rsrc_vib IS
1039 SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
1040 v.consume_ib_pri_qty, decode (t.reverse_id, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
1041 decode (t.reverse_id, NULL, v.consume_layer_date, t2.trans_date) c_trans_date,
1042 decode (t.reverse_id, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
1043 decode (t.reverse_id, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
1044 decode (t.reverse_id, NULL, 'N', 'Y') c_rev_layer,
1045 decode (t.reverse_id, NULL, ol.ROWID, ol2.ROWID) c_rowid
1046 FROM gmf_batch_vib_details v,
1047 gmf_incoming_material_layers il,
1048 gmf_resource_layers ol,
1049 gme_resource_txns t,
1050 gmf_resource_layers ol2,
1051 gme_resource_txns t2
1052 WHERE
1053 il.mmt_transaction_id = p_tran_rec.reverse_id AND
1054 nvl(il.lot_number, '@@@') = nvl(p_tran_rec.lot_number, '@@@') AND
1055 v.prod_layer_id = il.layer_id AND
1056 v.line_type = 0 AND -- resource only
1057 v.consume_layer_id = ol.layer_id (+) AND
1058 ol.poc_trans_id = t.poc_trans_id (+) AND
1059 t.reverse_id = ol2.poc_trans_id (+) AND
1060 ol2.poc_trans_id = t2.poc_trans_id (+);
1061
1062
1063 CURSOR c_ing_layers (p_ing_material_detail_id NUMBER) IS
1064 SELECT mmt.inventory_item_id, mmt.organization_id, /* mtln.lot_number, */ mmt.primary_quantity, msi.primary_uom_code,
1065 mmt.transaction_date, md.line_type, tp.transaction_id2 as reverse_id, l.ROWID, l.*
1066 FROM gmf_outgoing_material_layers l,
1067 mtl_material_transactions mmt,
1068 mtl_transaction_lot_numbers mtln,
1069 mtl_system_items_b msi,
1070 gme_material_details md,
1071 gme_transaction_pairs tp
1072 WHERE
1073 mmt.transaction_source_type_id = 5 AND
1074 mmt.transaction_source_id = p_tran_rec.transaction_source_id AND
1075 mmt.trx_source_line_id = p_ing_material_detail_id AND
1076 mtln.transaction_id(+) = mmt.transaction_id AND
1077 l.mmt_transaction_id = mmt.transaction_id AND
1078 nvl(l.lot_number, '@@@') = nvl(mtln.lot_number, '@@@') AND
1079 l.delete_mark = 0 AND
1080 l.remaining_ib_doc_qty <> 0 AND
1081 msi.inventory_item_id = mmt.inventory_item_id AND
1082 msi.organization_id = mmt.organization_id AND
1083 md.material_detail_id = p_ing_material_detail_id AND
1084 tp.transaction_id1(+) = mmt.transaction_id AND
1085 tp.pair_type(+) = 1
1086 ORDER BY mmt.transaction_date;
1087
1088
1089 CURSOR c_rsrc_layers (p_batchstep_resource_id NUMBER) IS
1090 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.*
1091 FROM gmf_resource_layers l, gme_resource_txns p
1092 WHERE
1093 p.doc_type = 'PROD' AND
1094 p.doc_id = p_tran_rec.transaction_source_id AND
1095 p.line_id = p_batchstep_resource_id AND
1096 p.completed_ind = 1 AND
1097 p.delete_mark = 0 AND
1098 l.poc_trans_id = p.poc_trans_id and
1099 l.delete_mark = 0 and
1100 l.remaining_ib_doc_qty <> 0
1101 ORDER BY p.trans_date;
1102
1103 l_required_ib_doc_qty NUMBER;
1104 l_remaining_ib_doc_qty NUMBER;
1105 l_consume_ib_doc_qty NUMBER;
1106 l_consume_ib_pri_qty NUMBER;
1107 l_rev_consume_ib_doc_qty NUMBER;
1108 l_cur_consume_ib_doc_qty NUMBER;
1109 l_prev_consume_ib_doc_qty NUMBER;
1110 l_doc_um VARCHAR2(4);
1111 l_line_type PLS_INTEGER;
1112 l_item_um VARCHAR2(4);
1113 l_use_vib VARCHAR2(30);
1114 l_count PLS_INTEGER;
1115 l_orig_layer_consumption_qty NUMBER; -- Bug 5607069
1116
1117 e_vib_complete EXCEPTION;
1118 e_invalid_consumption EXCEPTION;
1119 e_invalid_mtl_reversal EXCEPTION;
1120 e_invalid_rsrc_reversal EXCEPTION;
1121 e_rsrc_invalid_consumption EXCEPTION;
1122
1123 l_api_name VARCHAR2(30) := 'Create_VIB_Details';
1124
1125 BEGIN
1126 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1127
1128 IF g_debug <= gme_debug.g_log_procedure THEN
1129 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
1130 END IF;
1131
1132 -- Validate that the VIB details do not exist already
1133 SELECT count(*)
1134 INTO l_count
1135 FROM gmf_batch_vib_details
1136 WHERE
1137 prod_layer_id = p_layer_rec.layer_id;
1138
1139 IF l_count > 0 THEN
1140 x_return_status := FND_API.G_RET_STS_ERROR ;
1141 x_msg_data := 'VIB Details already exist';
1142 dbms_output.put_line ('VIB Details already exist');
1143 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_VIB_EXIST');
1144 FND_MSG_PUB.Add;
1145 RETURN;
1146 END IF;
1147
1148 l_use_vib := FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST');
1149 IF l_use_vib IS NULL THEN
1150 l_use_vib := 'N';
1151 END IF;
1152
1153 gme_debug.put_line ('Profile...Use VIB = '||l_use_vib);
1154
1155
1156 -- If this is a product reversal, reverse the VIB layers.
1157 IF p_tran_rec.primary_quantity < 0 and p_tran_rec.reverse_id IS NOT NULL THEN
1158
1159 IF g_debug <= gme_debug.g_log_procedure THEN
1160 gme_debug.put_line ('product reversal, reverse the VIB layers');
1161 END IF;
1162
1163 IF g_debug <= gme_debug.g_log_procedure THEN
1164 gme_debug.put_line ('now reversing material vib layers');
1165 END IF;
1166
1167 FOR v IN c_orig_mtl_vib LOOP
1168 BEGIN
1169 -- For No VIB, the ingredient reversals may already have been consumed
1170 -- by a previous prod yield. In that case, do not reverse anymore.:w
1171 IF l_use_vib = 'N' and v.c_rev_layer = 'Y' and
1172 v.layer_doc_qty <> v.remaining_ib_doc_qty
1173 THEN
1174
1175 SELECT count (1)
1176 INTO l_count
1177 FROM gmf_batch_vib_details vib,
1178 gmf_batch_requirements r
1179 WHERE
1180 r.batch_id = p_tran_rec.transaction_source_id AND
1181 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1182 vib.requirement_id = r.requirement_id AND
1183 vib.consume_layer_id = v.c_layer_id;
1184
1185 IF l_count > 0 THEN
1186 IF g_debug <= gme_debug.g_log_statement THEN
1187 gme_debug.put_line ('No VIB, the ingredient reversals may already have ' ||
1188 'been consumed by a previous prod yield. do not reverse anymore.');
1189 END IF;
1190
1191 RAISE e_invalid_mtl_reversal;
1192 END IF;
1193 END IF;
1194
1195 -- Insert VIB reversals
1196 INSERT INTO gmf_batch_vib_details(
1197 prod_layer_id,
1198 prod_layer_pri_qty,
1199 consume_layer_id,
1200 consume_layer_date,
1201 line_type,
1202 vib_id,
1203 finalize_ind,
1204 consume_ib_doc_qty,
1205 consume_ib_pri_qty,
1206 created_by,
1207 creation_date,
1208 last_updated_by,
1209 last_update_date,
1210 last_update_login,
1211 requirement_id)
1212 VALUES(
1213 p_layer_rec.layer_id,
1214 p_tran_rec.primary_quantity,
1215 v.c_layer_id,
1216 v.c_trans_date,
1217 v.line_type,
1218 NULL,
1219 0,
1220 -v.consume_ib_doc_qty,
1221 -v.consume_ib_pri_qty,
1222 p_tran_rec.created_by,
1223 sysdate,
1224 p_tran_rec.last_updated_by,
1225 sysdate,
1226 p_tran_rec.last_update_login,
1227 v.requirement_id);
1228
1229 UPDATE gmf_outgoing_material_layers
1230 SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
1231 WHERE
1232 ROWID = v.c_rowid;
1233
1234 EXCEPTION
1235 WHEN e_invalid_mtl_reversal THEN
1236 NULL; -- Skip to next row
1237 END;
1238 END LOOP;
1239
1240 IF g_debug <= gme_debug.g_log_procedure THEN
1241 gme_debug.put_line ('now reversing resource vib layers');
1242 END IF;
1243
1244 FOR v IN c_orig_rsrc_vib LOOP
1245 BEGIN
1246 -- For No VIB, the resource reversals may already have been consumed
1247 -- by a previous prod yield. In that case, do not reverse anymore.:w
1248 IF l_use_vib = 'N' and v.c_rev_layer = 'Y' and
1249 v.layer_doc_qty <> v.remaining_ib_doc_qty
1250 THEN
1251
1252 SELECT count (1)
1253 INTO l_count
1254 FROM gmf_batch_vib_details vib,
1255 gmf_batch_requirements r
1256 WHERE
1257 r.batch_id = p_tran_rec.transaction_source_id AND
1258 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1259 vib.requirement_id = r.requirement_id AND
1260 vib.consume_layer_id = v.c_layer_id;
1261
1262 IF l_count > 0 THEN
1263 IF g_debug <= gme_debug.g_log_statement THEN
1264 gme_debug.put_line ('No VIB, the resource reversals may already have ' ||
1265 'been consumed by a previous prod yield. do not reverse anymore.');
1266 END IF;
1267
1268 RAISE e_invalid_rsrc_reversal;
1269 END IF;
1270 END IF;
1271
1272 -- Insert VIB reversals
1273 INSERT INTO gmf_batch_vib_details(
1274 prod_layer_id,
1275 prod_layer_pri_qty,
1276 consume_layer_id,
1277 consume_layer_date,
1278 line_type,
1279 vib_id,
1280 finalize_ind,
1281 consume_ib_doc_qty,
1282 consume_ib_pri_qty,
1283 created_by,
1284 creation_date,
1285 last_updated_by,
1286 last_update_date,
1287 last_update_login,
1288 requirement_id)
1289 VALUES(
1290 p_layer_rec.layer_id,
1291 p_tran_rec.primary_quantity,
1292 v.c_layer_id,
1293 v.c_trans_date,
1294 v.line_type,
1295 NULL,
1296 0,
1297 -v.consume_ib_doc_qty,
1298 -v.consume_ib_pri_qty,
1299 p_tran_rec.created_by,
1300 sysdate,
1301 p_tran_rec.last_updated_by,
1302 sysdate,
1303 p_tran_rec.last_update_login,
1304 v.requirement_id);
1305
1306 UPDATE gmf_resource_layers
1307 SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
1308 WHERE
1309 ROWID = v.c_rowid;
1310
1311 EXCEPTION
1312 WHEN e_invalid_rsrc_reversal THEN
1313 NULL; -- Skip to next row
1314 END;
1315 END LOOP;
1316 RETURN; -- Done with the reversal
1317 END IF;
1318
1319 -- For regular yields follow the following logic.
1320 IF g_debug <= gme_debug.g_log_procedure THEN
1321 gme_debug.put_line ('regular product yield');
1322 END IF;
1323
1324
1325 -- Go through the batch requirement rows for this product and insert
1326 -- the VIB details in the vib table.
1327 FOR req IN c_batch_req LOOP
1328 BEGIN
1329 l_required_ib_doc_qty := p_layer_rec.layer_doc_qty * req.required_doc_qty;
1330
1331 -- If the VIB details are for ingredient or by-product
1332 IF req.ing_material_detail_id IS NOT NULL THEN
1333 -- select ingredient layers that can be consumed for the
1334 -- IB qty
1335
1336 IF g_debug <= gme_debug.g_log_statement THEN
1337 gme_debug.put_line ('processing ingredient or by-product to create vib details');
1338 END IF;
1339
1340 FOR ing in c_ing_layers(req.ing_material_detail_id) LOOP
1341 BEGIN
1342
1343 IF g_debug <= gme_debug.g_log_statement THEN
1344 gme_debug.put_line ('consuming ing/byProd layer. matl_dtl_id: ' || req.ing_material_detail_id ||
1345 ' consume layer_id: ' ||ing.layer_id );
1346 END IF;
1347
1348 IF l_use_vib = 'Y' THEN
1349 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty;
1350
1351 IF l_required_ib_doc_qty = 0 THEN
1352 RAISE e_vib_complete;
1353 END IF;
1354
1355 IF l_remaining_ib_doc_qty <= 0 THEN
1356 RAISE e_invalid_consumption;
1357 END IF;
1358
1359 IF l_remaining_ib_doc_qty >= l_required_ib_doc_qty THEN
1360 l_consume_ib_doc_qty := l_required_ib_doc_qty;
1361 ELSE
1362 l_consume_ib_doc_qty := l_remaining_ib_doc_qty;
1363 END IF;
1364
1365 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - l_consume_ib_doc_qty;
1366 l_required_ib_doc_qty := l_required_ib_doc_qty - l_consume_ib_doc_qty;
1367
1368 ELSE
1369 IF ing.remaining_ib_doc_qty = 0 THEN
1370 RAISE e_invalid_consumption;
1371 END IF;
1372
1373 l_consume_ib_doc_qty := ing.layer_doc_qty * req.derived_cost_alloc;
1374 -- Get the quantity already consumed from this player for the product.
1375 -- If the entire quantity is remaining, no need to check.
1376 IF ing.layer_doc_qty <> ing.remaining_ib_doc_qty THEN
1377 SELECT nvl(sum (consume_ib_doc_qty), 0)
1378 INTO l_prev_consume_ib_doc_qty
1379 FROM gmf_batch_vib_details v,
1380 gmf_batch_requirements r
1381 WHERE
1382 r.batch_id = p_tran_rec.transaction_source_id AND
1383 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1384 v.requirement_id = r.requirement_id AND
1385 v.consume_layer_id = ing.layer_id;
1386
1387 --
1388 -- Bug 5607069: If this is the reversal layer, then see whether original
1389 -- reversed layerd is consumed by this product or not.
1390 -- If consumed, then consume from this reversal layer to nullify the effect of it.
1391 -- If not consumed, then don't consume from this reversal layer.
1392 --
1393 IF ing.reverse_id IS NOT NULL
1394 THEN
1395 SELECT nvl(sum (consume_ib_doc_qty), 0)
1396 INTO l_orig_layer_consumption_qty
1397 FROM gmf_outgoing_material_layers ol,
1398 gmf_batch_vib_details v,
1399 gmf_batch_requirements r
1400 WHERE
1401 ol.mmt_transaction_id = ing.reverse_id AND
1402 v.consume_layer_id = ol.layer_id AND
1403 r.batch_id = p_tran_rec.transaction_source_id AND
1404 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1405 v.requirement_id = r.requirement_id ;
1406
1407 IF l_orig_layer_consumption_qty = 0
1408 THEN
1409 -- Do not consume from this reversal layers, as its original
1410 -- layer is not consumed by this product.
1411 RAISE e_rsrc_invalid_consumption;
1412 END IF;
1413
1414 END IF;
1415 -- End bug 5607069
1416
1417 l_consume_ib_doc_qty := l_consume_ib_doc_qty - l_prev_consume_ib_doc_qty;
1418 IF ABS(l_consume_ib_doc_qty) > ABS(ing.remaining_ib_doc_qty) THEN
1419 l_consume_ib_doc_qty := ing.remaining_ib_doc_qty;
1420 END IF;
1421
1422 IF l_consume_ib_doc_qty = 0 THEN
1423 -- Previous consumption have already consumed this products share
1424 RAISE e_invalid_consumption;
1425 END IF;
1426 END IF;
1427
1428 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty - l_consume_ib_doc_qty;
1429 END IF;
1430
1431 -- Convert the l_consume_ib_doc_qty to primary UOM
1432 l_consume_ib_pri_qty :=
1433 INV_CONVERT.INV_UM_CONVERT(
1434 ITEM_ID => ing.inventory_item_id
1435 , PRECISION => 5
1436 , ORGANIZATION_ID => ing.organization_id
1437 , LOT_NUMBER => ing.lot_number
1438 , FROM_QUANTITY => l_consume_ib_doc_qty
1439 , FROM_UNIT => ing.layer_doc_um
1440 , TO_UNIT => ing.primary_uom_code
1441 , FROM_NAME => NULL
1442 , TO_NAME => NULL
1443 );
1444
1445 INSERT INTO gmf_batch_vib_details(
1446 prod_layer_id,
1447 prod_layer_pri_qty,
1448 consume_layer_id,
1449 consume_layer_date,
1450 line_type,
1451 vib_id,
1452 finalize_ind,
1453 consume_ib_doc_qty,
1454 consume_ib_pri_qty,
1455 created_by,
1456 creation_date,
1457 last_updated_by,
1458 last_update_date,
1459 last_update_login,
1460 requirement_id)
1461 VALUES(
1462 p_layer_rec.layer_id,
1463 p_tran_rec.primary_quantity,
1464 ing.layer_id,
1465 ing.transaction_date,
1466 ing.line_type,
1467 NULL,
1468 0,
1469 l_consume_ib_doc_qty,
1470 l_consume_ib_pri_qty,
1471 p_tran_rec.created_by,
1472 sysdate,
1473 p_tran_rec.last_updated_by,
1474 sysdate,
1475 p_tran_rec.last_update_login,
1476 req.requirement_id);
1477
1478 UPDATE gmf_outgoing_material_layers
1479 SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
1480 WHERE
1481 ROWID = ing.ROWID;
1482 EXCEPTION
1483 WHEN e_invalid_consumption THEN
1484 NULL; -- Skip to next row
1485 END;
1486 END LOOP;
1487 END IF;
1488
1489 -- IF the VIB details are for a resource
1490 IF req.batchstep_resource_id IS NOT NULL THEN
1491 -- select ingredient layers that can be consumed for the
1492 -- IB qty
1493 IF g_debug <= gme_debug.g_log_statement THEN
1494 gme_debug.put_line ('processing ingredient or by-product to create vib details');
1495 END IF;
1496
1497 FOR rsrc IN c_rsrc_layers(req.batchstep_resource_id) LOOP
1498 BEGIN
1499 IF l_use_vib = 'Y' THEN
1500 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty;
1501
1502 IF l_required_ib_doc_qty = 0 THEN
1503 RAISE e_vib_complete;
1504 END IF;
1505
1506 -- insert a row in the VIB detail table
1507 IF l_remaining_ib_doc_qty = 0 THEN
1508 RAISE e_rsrc_invalid_consumption;
1509 END IF;
1510
1511 -- If both required and remaining are -ve, they work in reverse fashion
1512 IF l_remaining_ib_doc_qty >= l_required_ib_doc_qty THEN
1513 l_consume_ib_doc_qty := l_required_ib_doc_qty;
1514 ELSE
1515 l_consume_ib_doc_qty := l_remaining_ib_doc_qty;
1516 END IF;
1517 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - l_consume_ib_doc_qty;
1518 l_required_ib_doc_qty := l_required_ib_doc_qty - l_consume_ib_doc_qty;
1519 ELSE
1520 IF rsrc.remaining_ib_doc_qty = 0 THEN
1521 RAISE e_rsrc_invalid_consumption;
1522 END IF;
1523
1524 l_consume_ib_doc_qty := rsrc.layer_doc_qty * req.derived_cost_alloc;
1525 -- Get the quantity already consumed from this player for the product.
1526 -- If the entire quantity is remaining, no need to check.
1527 IF rsrc.layer_doc_qty <> rsrc.remaining_ib_doc_qty THEN
1528 SELECT nvl(sum (consume_ib_doc_qty), 0)
1529 INTO l_prev_consume_ib_doc_qty
1530 FROM gmf_batch_vib_details v,
1531 gmf_batch_requirements r
1532 WHERE
1533 r.batch_id = p_tran_rec.transaction_source_id AND
1534 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1535 v.requirement_id = r.requirement_id AND
1536 v.consume_layer_id = rsrc.layer_id;
1537
1538 --
1539 -- Bug 5607069: If this is the reversal layer, then see whether original
1540 -- reversed layerd is consumed by this product or not.
1541 -- If consumed, then consume from this reversal layer to nullify the effect of it.
1542 -- If not consumed, then don't consume from this reversal layer.
1543 --
1544 IF rsrc.reverse_id IS NOT NULL
1545 THEN
1546 SELECT nvl(sum (consume_ib_doc_qty), 0)
1547 INTO l_orig_layer_consumption_qty
1548 FROM gmf_resource_layers rl,
1549 gmf_batch_vib_details v,
1550 gmf_batch_requirements r
1551 WHERE
1552 rl.poc_trans_id = rsrc.reverse_id AND
1553 v.consume_layer_id = rl.layer_id AND
1554 r.batch_id = p_tran_rec.transaction_source_id AND
1555 r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
1556 v.requirement_id = r.requirement_id ;
1557
1558 IF l_orig_layer_consumption_qty = 0
1559 THEN
1560 -- Do not consume from this reversal layers, as its original
1561 -- layer is not consumed by this product.
1562 RAISE e_rsrc_invalid_consumption;
1563 END IF;
1564
1565 END IF;
1566 -- End bug 5607069
1567
1568
1569 l_consume_ib_doc_qty := l_consume_ib_doc_qty - l_prev_consume_ib_doc_qty;
1570 --
1571 -- Bug 5607069: synching material and resource layer code
1572 --
1573 IF ABS(l_consume_ib_doc_qty) > ABS(rsrc.remaining_ib_doc_qty) THEN
1574 l_consume_ib_doc_qty := rsrc.remaining_ib_doc_qty;
1575 END IF;
1576 -- End bug 5607069
1577
1578 --
1579 -- Bug 5607069: synching material and resource layer code
1580 --
1581 -- IF l_consume_ib_doc_qty <= 0 THEN
1582 --
1583 IF l_consume_ib_doc_qty = 0 THEN
1584 -- Previous consumption have already consumed this products share
1585 RAISE e_rsrc_invalid_consumption;
1586 END IF;
1587 END IF;
1588
1589 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty - l_consume_ib_doc_qty;
1590 END IF;
1591
1592 -- Convert the l_consume_ib_doc_qty to primary UOM
1593 l_consume_ib_pri_qty :=
1594 INV_CONVERT.INV_UM_CONVERT(
1595 ITEM_ID => 0
1596 , PRECISION => 5
1597 , ORGANIZATION_ID => rsrc.organization_id
1598 , LOT_NUMBER => NULL
1599 , FROM_QUANTITY => l_consume_ib_doc_qty
1600 , FROM_UNIT => rsrc.layer_doc_um
1601 , TO_UNIT => rsrc.trans_um
1602 , FROM_NAME => NULL
1603 , TO_NAME => NULL
1604 );
1605
1606
1607 INSERT INTO gmf_batch_vib_details(
1608 prod_layer_id,
1609 prod_layer_pri_qty,
1610 consume_layer_id,
1611 consume_layer_date,
1612 line_type,
1613 vib_id,
1614 finalize_ind,
1615 consume_ib_doc_qty,
1616 consume_ib_pri_qty,
1617 created_by,
1618 creation_date,
1619 last_updated_by,
1620 last_update_date,
1621 last_update_login,
1622 requirement_id)
1623 VALUES(
1624 p_layer_rec.layer_id,
1625 p_tran_rec.primary_quantity,
1626 rsrc.layer_id,
1627 rsrc.trans_date,
1628 rsrc.line_type,
1629 NULL,
1630 0,
1631 l_consume_ib_doc_qty,
1632 l_consume_ib_pri_qty,
1633 p_tran_rec.created_by,
1634 sysdate,
1635 p_tran_rec.last_updated_by,
1636 sysdate,
1637 p_tran_rec.last_update_login,
1638 req.requirement_id );
1639
1640 UPDATE gmf_resource_layers
1641 SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
1642 WHERE
1643 ROWID = rsrc.ROWID;
1644 EXCEPTION
1645 WHEN e_rsrc_invalid_consumption THEN
1646 NULL; -- Skip to next row
1647 END;
1648 END LOOP;
1649
1650 END IF;
1651
1652 -- There is still some IB quantity not consumed, insert a NULL layer consumption.
1653 IF l_use_vib = 'Y' and l_required_ib_doc_qty <> 0 THEN
1654
1655 IF g_debug <= gme_debug.g_log_statement THEN
1656 gme_debug.put_line ('inserting NULL consumption layer for matl_dtl_id: ' || req.ing_material_detail_id);
1657 END IF;
1658
1659 l_consume_ib_pri_qty := 0;
1660 IF req.ing_material_detail_id IS NOT NULL THEN
1661
1662 SELECT m.dtl_um, m.line_type, i.primary_uom_code
1663 INTO l_doc_um, l_line_type, l_item_um
1664 FROM gme_material_details m, mtl_system_items_b i
1665 WHERE
1666 m.batch_id = req.batch_id AND
1667 m.material_detail_id = req.ing_material_detail_id AND
1668 i.inventory_item_id = m.inventory_item_id AND
1669 i.organization_id = m.organization_id;
1670
1671 l_consume_ib_pri_qty :=
1672 INV_CONVERT.INV_UM_CONVERT(
1673 ITEM_ID => req.ingredient_item_id
1674 , PRECISION => 5
1675 , ORGANIZATION_ID => req.organization_id
1676 , LOT_NUMBER => NULL
1677 , FROM_QUANTITY => l_required_ib_doc_qty
1678 , FROM_UNIT => l_doc_um
1679 , TO_UNIT => l_item_um
1680 , FROM_NAME => NULL
1681 , TO_NAME => NULL
1682 );
1683
1684 ELSE
1685 SELECT m.usage_uom, 0, r.std_usage_um
1686 INTO l_doc_um, l_line_type, l_item_um
1687 FROM gme_batch_step_resources m, cr_rsrc_mst_b r
1688 WHERE
1689 m.batch_id = req.batch_id AND
1690 m.batchstep_resource_id = req.batchstep_resource_id AND
1691 m.resources = r.resources;
1692
1693 l_consume_ib_pri_qty :=
1694 INV_CONVERT.INV_UM_CONVERT(
1695 ITEM_ID => 0
1696 , PRECISION => 5
1697 , ORGANIZATION_ID => req.organization_id
1698 , LOT_NUMBER => NULL
1699 , FROM_QUANTITY => l_required_ib_doc_qty
1700 , FROM_UNIT => l_doc_um
1701 , TO_UNIT => l_item_um
1702 , FROM_NAME => NULL
1703 , TO_NAME => NULL
1704 );
1705
1706
1707 END IF;
1708
1709 INSERT INTO gmf_batch_vib_details(
1710 prod_layer_id,
1711 prod_layer_pri_qty,
1712 consume_layer_id,
1713 consume_layer_date,
1714 line_type,
1715 vib_id,
1716 finalize_ind,
1717 consume_ib_doc_qty,
1718 consume_ib_pri_qty,
1719 created_by,
1720 creation_date,
1721 last_updated_by,
1722 last_update_date,
1723 last_update_login,
1724 requirement_id)
1725 VALUES(
1726 p_layer_rec.layer_id,
1727 p_tran_rec.primary_quantity,
1728 NULL,
1729 p_tran_rec.transaction_date,
1730 l_line_type,
1731 NULL,
1732 0,
1733 l_required_ib_doc_qty,
1734 l_consume_ib_pri_qty,
1735 p_tran_rec.created_by,
1736 sysdate,
1737 p_tran_rec.last_updated_by,
1738 sysdate,
1739 p_tran_rec.last_update_login,
1740 req.requirement_id);
1741 END IF;
1742 EXCEPTION
1743 WHEN e_vib_complete THEN
1744 NULL;
1745 END;
1746 END LOOP; -- c_batch_req
1747
1748 x_return_status := FND_API.G_RET_STS_SUCCESS;
1749
1750 IF g_debug <= gme_debug.g_log_procedure THEN
1751 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1752 END IF;
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
1757 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
1758 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1759 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
1760 FND_MSG_PUB.Add;
1761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1762 END;
1763
1764 /*
1765 --+==========================================================================+
1766 --| PROCEDURE NAME |
1767 --| Finalize_VIB_Details |
1768 --| |
1769 --| TYPE |
1770 --| Public |
1771 --| |
1772 --| USAGE |
1773 --| Finalize_VIB_Details |
1774 --| |
1775 --| DESCRIPTION |
1776 --| |
1777 --| PARAMETERS |
1778 --| |
1779 --| RETURNS |
1780 --| None |
1781 --| |
1782 --| HISTORY |
1783 --| |
1784 --| umoogala 21-Oct-2006 bug 5607069 - |
1785 --| Issue 1) Material and Resources reversals layers were not getting |
1786 --| consumed. |
1787 --| Fixed code to allocate reversals only if its original |
1788 --| layer is consumed by this product, while creating VIB dtls.|
1789 --| Issue 2) Material and Resources reversals layers were not getting |
1790 --| properly apportioned in the finalization layers. |
1791 --| Fix is same as above: |
1792 --| Fixed code to allocate reversals only if its original |
1793 --| layer is consumed by this product, while creating VIB dtls.|
1794 --+==========================================================================+
1795 */
1796 PROCEDURE Finalize_VIB_Details
1797 ( p_api_version IN NUMBER,
1798 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1799 p_batch_id IN NUMBER,
1800 x_return_status OUT NOCOPY VARCHAR2,
1801 x_msg_count OUT NOCOPY NUMBER,
1802 x_msg_data OUT NOCOPY VARCHAR2) IS
1803
1804 CURSOR c_batch_req IS
1805 SELECT *
1806 FROM gmf_batch_requirements
1807 WHERE
1808 batch_id = p_batch_id AND
1809 delete_mark = 0
1810 ORDER BY prod_material_detail_id;
1811
1812 CURSOR c_null_consume_layers IS
1813 SELECT v.*, l.layer_doc_qty, l.layer_doc_um, l.mmt_transaction_id, l.lot_number, l.mmt_organization_id
1814 FROM gmf_batch_vib_details v,
1815 gmf_batch_requirements r,
1816 gmf_incoming_material_layers l
1817 WHERE
1818 r.batch_id = p_batch_id AND
1819 v.requirement_id = r.requirement_id AND
1820 l.layer_id = v.prod_layer_id AND
1821 v.finalize_ind = 0 AND
1822 v.consume_layer_id IS NULL
1823 ORDER BY v.prod_layer_id
1824 ;
1825
1826 CURSOR c_last_prod_yield (p_prod_material_detail_id NUMBER) IS
1827 SELECT l.*, t.primary_quantity,
1828 t.inventory_item_id -- Bug 5607069
1829 FROM gmf_incoming_material_layers l,
1830 mtl_material_transactions t,
1831 mtl_transaction_lot_numbers mtln
1832 WHERE
1833 t.trx_source_line_id = p_prod_material_detail_id AND
1834 t.transaction_source_id = p_batch_id AND
1835 t.transaction_source_type_id = 5 AND
1836 mtln.transaction_id(+) = t.transaction_id AND
1837 l.mmt_transaction_id = t.transaction_id AND
1838 l.pseudo_layer_id IS NULL AND
1839 nvl(l.lot_number, '@@@') = nvl(mtln.lot_number, '@@@') AND
1840 not exists (select 'x' from gme_transaction_pairs tp
1841 where transaction_id1 = t.transaction_id and tp.pair_type = 1)
1842 ORDER BY l.creation_date DESC;
1843
1844 CURSOR c_remaining_ing_layers (p_ing_material_detail_id NUMBER) IS
1845 SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.inventory_item_id, t.transaction_date,
1846 mtln.lot_number, md.line_type, t.primary_quantity, msi.primary_uom_code, l.ROWID, tp.transaction_id2 as reverse_id,
1847 l.layer_doc_qty
1848 FROM gmf_outgoing_material_layers l, mtl_material_transactions t,
1849 mtl_transaction_lot_numbers mtln, gme_material_details md,
1850 mtl_system_items_b msi, gme_transaction_pairs tp
1851 WHERE
1852 t.trx_source_line_id = p_ing_material_detail_id AND
1853 t.transaction_source_id = p_batch_id AND
1854 t.transaction_source_type_id = 5 AND
1855 mtln.transaction_id(+) = t.transaction_id AND
1856 l.mmt_transaction_id = t.transaction_id AND
1857 l.remaining_ib_doc_qty <> 0 AND
1858 l.delete_mark = 0 AND
1859 nvl(l.lot_number, '@@@') = nvl(mtln.lot_number, '@@@') AND
1860 md.material_detail_id = p_ing_material_detail_id AND
1861 msi.inventory_item_id = t.inventory_item_id AND
1862 msi.organization_id = t.organization_id AND
1863 tp.transaction_id1(+) = t.transaction_id
1864 ;
1865
1866 CURSOR c_remaining_rsrc_layers (p_batchstep_resource_id NUMBER) IS
1867 SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.line_type, t.trans_date,
1868 t.resource_usage, t.trans_qty_um trans_um, l.ROWID, t.reverse_id, l.layer_doc_qty
1869 FROM gmf_resource_layers l,
1870 gme_resource_txns t
1871 WHERE
1872 l.poc_trans_id = t.poc_trans_id AND
1873 t.line_id = p_batchstep_resource_id AND
1874 l.remaining_ib_doc_qty <> 0 and
1875 l.delete_mark = 0;
1876
1877 CURSOR c_finalize_layer_consumption IS
1878 SELECT v.consume_layer_id, v.line_type, sum(v.consume_ib_doc_qty) consume_ib_doc_qty
1879 FROM
1880 gmf_batch_vib_details v,
1881 gmf_batch_requirements r
1882 WHERE
1883 r.batch_id = p_batch_id AND
1884 v.requirement_id = r.requirement_id AND
1885 v.finalize_ind = 1 AND
1886 v.consume_layer_id IS NOT NULL
1887 GROUP BY v.consume_layer_id, v.line_type;
1888
1889 l_batch_status gme_batch_header.batch_status%TYPE;
1890 prev_prod_material_detail_id NUMBER;
1891 l_total_cost_alloc NUMBER;
1892 l_pseudo_prod_layer_id NUMBER;
1893 l_last_prod_layer c_last_prod_yield%ROWTYPE;
1894 l_batch_close_date DATE;
1895 l_consume_ib_doc_qty NUMBER;
1896 l_consume_ib_pri_qty NUMBER;
1897 l_remaining_ib_doc_qty NUMBER;
1898 l_user_id NUMBER;
1899 l_count PLS_INTEGER;
1900 --
1901 -- Bug 5607069: Following 4 variables added
1902 l_use_vib VARCHAR2(30);
1903 l_orig_layer_consumption_qty NUMBER;
1904 e_invalid_consumption EXCEPTION;
1905 l_prev_consume_ib_doc_qty NUMBER;
1906
1907 l_api_name VARCHAR2(30) := 'Finalize_VIB_Details';
1908
1909 l_prev_prod_layer_id NUMBER;
1910
1911 BEGIN
1912 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1913
1914 IF g_debug <= gme_debug.g_log_procedure THEN
1915 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
1916 END IF;
1917
1918 -- Validate batch_id
1919 BEGIN
1920 SELECT batch_status, last_updated_by, batch_close_date
1921 INTO l_batch_status, l_user_id, l_batch_close_date
1922 FROM gme_batch_header
1923 WHERE batch_id = p_batch_id;
1924
1925 IF l_batch_status <> 4 THEN
1926 x_return_status := FND_API.G_RET_STS_ERROR ;
1927 --dbms_output.put_line ('Batch is not in CLOSE Status');
1928 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_NOT_CLOSE');
1929 FND_MSG_PUB.Add;
1930 RETURN;
1931 END IF;
1932 EXCEPTION
1933 WHEN OTHERS THEN
1934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1935 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
1936 FND_MSG_PUB.Add;
1937 RAISE;
1938 END;
1939
1940 -- Check if he VIB details already exist for this batch
1941 BEGIN
1942 SELECT count(*)
1943 INTO l_count
1944 FROM gmf_batch_vib_details v,
1945 gmf_batch_requirements r
1946 WHERE
1947 r.batch_id = p_batch_id AND
1948 r.requirement_id = v.requirement_id and
1949 v.finalize_ind = 1;
1950
1951 IF l_count > 0 THEN
1952 x_return_status := FND_API.G_RET_STS_ERROR ;
1953 --dbms_output.put_line ('VIB details already exist for this batch');
1954 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_FINAL_VIB_EXIST');
1955 FND_MSG_PUB.Add;
1956 RETURN;
1957 END IF;
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
1962 FND_MSG_PUB.Add;
1963 RAISE;
1964 END;
1965
1966
1967 l_use_vib := FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST');
1968 IF l_use_vib IS NULL THEN
1969 l_use_vib := 'N';
1970 END IF;
1971
1972 gme_debug.put_line ('Use VIB = ' || l_use_vib );
1973 IF g_debug <= gme_debug.g_log_statement THEN
1974 gme_debug.put_line ('reversing out all NULL consumptions layers, if any');
1975 END IF;
1976
1977
1978 -- reverse out all NULL consumption layers
1979 FOR n IN c_null_consume_layers LOOP
1980
1981 IF g_debug <= gme_debug.g_log_statement THEN
1982 gme_debug.put_line ('NULL consumption layer found for prod layer/txn/lot: ' ||
1983 n.prod_layer_id ||'/'||n.mmt_transaction_id||'/'||n.lot_number ||
1984 ' requirement_id for NULL consumption layer: ' || n.requirement_id);
1985 END IF;
1986
1987 BEGIN
1988
1989 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
1990 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
1991
1992
1993 INSERT INTO gmf_incoming_material_layers(
1994 layer_id,
1995 mmt_transaction_id,
1996 mmt_organization_id,
1997 lot_number,
1998 layer_doc_qty,
1999 layer_doc_um,
2000 layer_date,
2001 pseudo_layer_id,
2002 final_cost_ind,
2003 gl_posted_ind,
2004 created_by,
2005 creation_date,
2006 last_updated_by,
2007 last_update_date,
2008 last_update_login,
2009 accounted_flag)
2010 VALUES(
2011 l_pseudo_prod_layer_id,
2012 n.mmt_transaction_id,
2013 n.mmt_organization_id,
2014 n.lot_number,
2015 n.layer_doc_qty,
2016 n.layer_doc_um,
2017 l_batch_close_date,
2018 n.prod_layer_id,
2019 0,
2020 0,
2021 l_user_id,
2022 sysdate,
2023 l_user_id,
2024 sysdate,
2025 NULL,
2026 'N');
2027
2028 -- Create a VIB layer for the pseudo product layer reversing the original VIB row
2029 INSERT INTO gmf_batch_vib_details(
2030 prod_layer_id,
2031 prod_layer_pri_qty,
2032 consume_layer_id,
2033 consume_layer_date,
2034 line_type,
2035 vib_id,
2036 finalize_ind,
2037 consume_ib_doc_qty,
2038 consume_ib_pri_qty,
2039 created_by,
2040 creation_date,
2041 last_updated_by,
2042 last_update_date,
2043 last_update_login,
2044 requirement_id)
2045 VALUES(
2046 l_pseudo_prod_layer_id,
2047 n.prod_layer_pri_qty,
2048 NULL,
2049 n.consume_layer_date,
2050 n.line_type,
2051 NULL,
2052 1,
2053 -n.consume_ib_doc_qty,
2054 -n.consume_ib_pri_qty,
2055 l_user_id,
2056 sysdate,
2057 l_user_id,
2058 sysdate,
2059 NULL,
2060 n.requirement_id);
2061
2062
2063 END;
2064 END LOOP;
2065
2066 -- going thru the requirement details
2067
2068 IF g_debug <= gme_debug.g_log_procedure THEN
2069 gme_debug.put_line ('looping thru all the requirements to create finalization layers');
2070 END IF;
2071
2072 prev_prod_material_detail_id := -99;
2073 FOR req IN c_batch_req LOOP
2074 BEGIN
2075
2076 IF g_debug <= gme_debug.g_log_statement THEN
2077 gme_debug.put_line ('Processing prod_material_detail_id: ' || req.prod_material_detail_id ||
2078 ' ing_material_detail_id/batchstep_resource_id: ' || req.ing_material_detail_id||
2079 '/'||req.batchstep_resource_id);
2080 END IF;
2081
2082 -- Get the last yield for the product
2083 IF (prev_prod_material_detail_id <> req.prod_material_detail_id) THEN
2084 l_last_prod_layer.layer_id := NULL;
2085 OPEN c_last_prod_yield(req.prod_material_detail_id);
2086 FETCH c_last_prod_yield INTO l_last_prod_layer;
2087 CLOSE c_last_prod_yield;
2088 prev_prod_material_detail_id := req.prod_material_detail_id;
2089 l_pseudo_prod_layer_id := NULL;
2090 END IF;
2091
2092 -- Get any remaining ingredients layers for this requirement row.
2093 IF (l_last_prod_layer.layer_id IS NOT NULL AND req.ing_material_detail_id IS NOT NULL) THEN
2094
2095 FOR ing IN c_remaining_ing_layers (req.ing_material_detail_id) LOOP
2096 BEGIN
2097
2098 IF g_debug <= gme_debug.g_log_statement THEN
2099 gme_debug.put_line ('processing remaining ingredients layers...');
2100 END IF;
2101
2102 --
2103 -- Bug 5607069: When using Actuals:
2104 -- before consuming remaining qty, see whether it is already
2105 -- got consumed from this layer or not. If yes, then don't consume again
2106 --
2107 IF l_use_vib = 'N'
2108 THEN
2109 SELECT nvl(sum (consume_ib_doc_qty), 0)
2110 INTO l_prev_consume_ib_doc_qty
2111 FROM gmf_batch_vib_details v,
2112 gmf_incoming_material_layers il,
2113 mtl_material_transactions mmt
2114 WHERE
2115 v.requirement_id = req.requirement_id AND
2116 v.consume_layer_id = ing.layer_id AND
2117 il.layer_id = v.prod_layer_id AND
2118 mmt.transaction_id = il.mmt_transaction_id AND
2119 mmt.transaction_source_type_id = 5 AND
2120 mmt.inventory_item_id = req.product_item_id AND
2121 mmt.organization_id = req.organization_id
2122 ;
2123
2124 IF (l_prev_consume_ib_doc_qty <> 0)
2125 THEN
2126 -- Do not consume from this layer, as it is already
2127 -- consumed
2128 RAISE e_invalid_consumption ;
2129 END IF;
2130
2131
2132 --
2133 -- Bug 5607069: If this is the reversal layer, then see whether original
2134 -- reversed layer is consumed by this product or not.
2135 -- If consumed, then consume from this reversal layer to nullify the effect of it.
2136 -- If not consumed, then don't consume from this reversal layer.
2137 --
2138
2139 IF ing.reverse_id IS NOT NULL
2140 THEN
2141 SELECT nvl(sum (consume_ib_doc_qty), 0)
2142 INTO l_orig_layer_consumption_qty
2143 FROM gmf_outgoing_material_layers ol,
2144 gmf_batch_vib_details v,
2145 gmf_incoming_material_layers il,
2146 mtl_material_transactions mmt
2147 WHERE
2148 ol.mmt_transaction_id = ing.reverse_id AND
2149 ol.lot_number = ing.lot_number AND
2150 v.consume_layer_id = ol.layer_id AND
2151 v.requirement_id = req.requirement_id AND
2152 il.layer_id = v.prod_layer_id AND
2153 mmt.transaction_id = il.mmt_transaction_id AND
2154 mmt.transaction_source_type_id = 5 AND
2155 mmt.inventory_item_id = req.product_item_id AND
2156 mmt.organization_id = req.organization_id
2157 ;
2158
2159
2160 IF (l_orig_layer_consumption_qty = 0)
2161 THEN
2162 -- Do not consume from this reversal layers, as its original
2163 -- layer is not consumed by this product.
2164 RAISE e_invalid_consumption ;
2165 END IF;
2166
2167 END IF;
2168
2169 END IF;
2170 -- End bug 5607069
2171
2172 IF l_pseudo_prod_layer_id IS NULL THEN
2173 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
2174 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
2175
2176 INSERT INTO gmf_incoming_material_layers(
2177 layer_id,
2178 mmt_transaction_id,
2179 mmt_organization_id,
2180 lot_number,
2181 layer_doc_qty,
2182 layer_doc_um,
2183 layer_date,
2184 pseudo_layer_id,
2185 final_cost_ind,
2186 gl_posted_ind,
2187 created_by,
2188 creation_date,
2189 last_updated_by,
2190 last_update_date,
2191 last_update_login,
2192 accounted_flag)
2193 VALUES(
2194 l_pseudo_prod_layer_id,
2195 l_last_prod_layer.mmt_transaction_id,
2196 l_last_prod_layer.mmt_organization_id,
2197 l_last_prod_layer.lot_number,
2198 l_last_prod_layer.layer_doc_qty,
2199 l_last_prod_layer.layer_doc_um,
2200 l_batch_close_date,
2201 l_last_prod_layer.layer_id,
2202 0,
2203 0,
2204 l_user_id,
2205 sysdate,
2206 l_user_id,
2207 sysdate,
2208 NULL,
2209 'N');
2210 END IF;
2211
2212 --
2213 -- Bug 5607069
2214 --
2215 IF l_use_vib = 'N'
2216 THEN
2217 l_consume_ib_doc_qty := ing.layer_doc_qty * req.derived_cost_alloc;
2218 ELSE
2219 l_consume_ib_doc_qty := ing.remaining_ib_doc_qty * req.derived_cost_alloc;
2220 END IF;
2221 -- Bug 5607069
2222
2223 l_consume_ib_pri_qty :=
2224 INV_CONVERT.INV_UM_CONVERT(
2225 ITEM_ID => ing.inventory_item_id
2226 , PRECISION => 5
2227 , ORGANIZATION_ID => req.organization_id
2228 , LOT_NUMBER => NULL
2229 , FROM_QUANTITY => l_consume_ib_doc_qty
2230 , FROM_UNIT => ing.layer_doc_um
2231 , TO_UNIT => ing.primary_uom_code
2232 , FROM_NAME => NULL
2233 , TO_NAME => NULL
2234 );
2235
2236
2237 l_remaining_ib_doc_qty := ing.remaining_ib_doc_qty - l_consume_ib_doc_qty;
2238
2239 INSERT INTO gmf_batch_vib_details(
2240 prod_layer_id,
2241 prod_layer_pri_qty,
2242 consume_layer_id,
2243 consume_layer_date,
2244 line_type,
2245 vib_id,
2246 finalize_ind,
2247 consume_ib_doc_qty,
2248 consume_ib_pri_qty,
2249 created_by,
2250 creation_date,
2251 last_updated_by,
2252 last_update_date,
2253 last_update_login,
2254 requirement_id)
2255 VALUES(
2256 l_pseudo_prod_layer_id,
2257 l_last_prod_layer.primary_quantity,
2258 ing.layer_id,
2259 ing.transaction_date,
2260 ing.line_type,
2261 NULL,
2262 1,
2263 l_consume_ib_doc_qty,
2264 l_consume_ib_pri_qty,
2265 l_user_id,
2266 sysdate,
2267 l_user_id,
2268 sysdate,
2269 NULL,
2270 req.requirement_id);
2271
2272
2273 --
2274 -- Bug 5607069: Added exception block.
2275 --
2276 EXCEPTION
2277 WHEN e_invalid_consumption THEN
2278 NULL; -- Skip to next row
2279 END;
2280 END LOOP;
2281 END IF;
2282
2283 --
2284 -- Now doing for resources
2285 --
2286 IF (l_last_prod_layer.layer_id IS NOT NULL AND req.batchstep_resource_id IS NOT NULL) THEN
2287 FOR rsrc IN c_remaining_rsrc_layers (req.batchstep_resource_id) LOOP
2288 BEGIN
2289
2290 IF g_debug <= gme_debug.g_log_statement THEN
2291 gme_debug.put_line ('processing remaining resource layers...');
2292 END IF;
2293
2294
2295 --
2296 -- Bug 5607069: When using Actuals:
2297 -- before consuming remaining qty, see whether it is already
2298 -- got consumed from this layer or not. If yes, then don't consume again
2299 --
2300 IF l_use_vib = 'N'
2301 THEN
2302 SELECT nvl(sum (consume_ib_doc_qty), 0)
2303 INTO l_prev_consume_ib_doc_qty
2304 FROM gmf_batch_vib_details v,
2305 gmf_incoming_material_layers il,
2306 mtl_material_transactions mmt
2307 WHERE
2308 v.requirement_id = req.requirement_id AND
2309 v.consume_layer_id = rsrc.layer_id AND
2310 il.layer_id = v.prod_layer_id AND
2311 mmt.transaction_id = il.mmt_transaction_id AND
2312 mmt.inventory_item_id = req.product_item_id AND
2313 mmt.organization_id = req.organization_id AND
2314 mmt.transaction_source_type_id = 5
2315 ;
2316
2317 IF (l_prev_consume_ib_doc_qty <> 0)
2318 THEN
2319 -- Do not consume from this layer, as it is already
2320 -- consumed
2321 RAISE e_invalid_consumption ;
2322 END IF;
2323
2324
2325 --
2326 -- Bug 5607069: If this is the reversal layer, then see whether original
2327 -- reversed layer is consumed by this product or not.
2328 -- If consumed, then consume from this reversal layer to nullify the effect of it.
2329 -- If not consumed, then don't consume from this reversal layer.
2330 --
2331 IF rsrc.reverse_id IS NOT NULL
2332 THEN
2333 SELECT nvl(sum (consume_ib_doc_qty), 0)
2334 INTO l_orig_layer_consumption_qty
2335 FROM gmf_resource_layers rl,
2336 gmf_batch_vib_details v,
2337 gmf_incoming_material_layers il,
2338 mtl_material_transactions mmt
2339 WHERE
2340 rl.poc_trans_id = rsrc.reverse_id AND
2341 v.consume_layer_id = rl.layer_id AND
2342 v.requirement_id = req.requirement_id AND
2343 il.layer_id = v.prod_layer_id AND
2344 mmt.transaction_id = il.mmt_transaction_id AND
2345 mmt.inventory_item_id = req.product_item_id AND
2346 mmt.organization_id = req.organization_id AND
2347 mmt.transaction_source_type_id = 5
2348 ;
2349
2350 IF (l_orig_layer_consumption_qty = 0)
2351 THEN
2352 -- Do not consume from this reversal layers, as its original
2353 -- layer is not consumed by this product.
2354 RAISE e_invalid_consumption ;
2355 END IF;
2356
2357 END IF;
2358
2359 END IF;
2360 -- End bug 5607069
2361
2362 IF l_pseudo_prod_layer_id IS NULL THEN
2363 -- Create a pseudo product layer in the gmf_incoming_material_layers table.
2364 -- Bug 6887598 mmt_organization_id should not be NULL
2365 SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
2366 INSERT INTO gmf_incoming_material_layers(
2367 layer_id,
2368 mmt_transaction_id,
2369 mmt_organization_id, -- B6887598
2370 lot_number,
2371 layer_doc_qty,
2372 layer_doc_um,
2373 layer_date,
2374 pseudo_layer_id,
2375 final_cost_ind,
2376 gl_posted_ind,
2377 created_by,
2378 creation_date,
2379 last_updated_by,
2380 last_update_date,
2381 last_update_login,
2382 accounted_flag)
2383 VALUES(
2384 l_pseudo_prod_layer_id,
2385 l_last_prod_layer.mmt_transaction_id,
2386 l_last_prod_layer.mmt_organization_id, -- B6887598
2387 l_last_prod_layer.lot_number,
2388 l_last_prod_layer.layer_doc_qty,
2389 l_last_prod_layer.layer_doc_um,
2390 l_batch_close_date,
2391 l_last_prod_layer.layer_id,
2392 0,
2393 0,
2394 l_user_id,
2395 sysdate,
2396 l_user_id,
2397 sysdate,
2398 NULL,
2399 'N');
2400 END IF;
2401 --
2402 -- Bug 5607069
2403 --
2404 IF l_use_vib = 'N'
2405 THEN
2406 l_consume_ib_doc_qty := rsrc.layer_doc_qty * req.derived_cost_alloc;
2407 ELSE
2408 l_consume_ib_doc_qty := rsrc.remaining_ib_doc_qty * req.derived_cost_alloc;
2409 END IF;
2410
2411 l_consume_ib_pri_qty :=
2412 INV_CONVERT.INV_UM_CONVERT(
2413 ITEM_ID => 0
2414 , PRECISION => 5
2415 , ORGANIZATION_ID => req.organization_id
2416 , LOT_NUMBER => NULL
2417 , FROM_QUANTITY => l_consume_ib_doc_qty
2418 , FROM_UNIT => rsrc.layer_doc_um
2419 , TO_UNIT => rsrc.trans_um
2420 , FROM_NAME => NULL
2421 , TO_NAME => NULL
2422 );
2423
2424
2425 l_remaining_ib_doc_qty := rsrc.remaining_ib_doc_qty - l_consume_ib_doc_qty;
2426 INSERT INTO gmf_batch_vib_details(
2427 prod_layer_id,
2428 prod_layer_pri_qty,
2429 consume_layer_id,
2430 consume_layer_date,
2431 line_type,
2432 vib_id,
2433 finalize_ind,
2434 consume_ib_doc_qty,
2435 consume_ib_pri_qty,
2436 created_by,
2437 creation_date,
2438 last_updated_by,
2439 last_update_date,
2440 last_update_login,
2441 requirement_id)
2442 VALUES(
2443 l_pseudo_prod_layer_id,
2444 l_last_prod_layer.primary_quantity,
2445 rsrc.layer_id,
2446 rsrc.trans_date,
2447 rsrc.line_type,
2448 NULL,
2449 1,
2450 l_consume_ib_doc_qty,
2451 l_consume_ib_pri_qty,
2452 l_user_id,
2453 sysdate,
2454 l_user_id,
2455 sysdate,
2456 NULL,
2457 req.requirement_id);
2458
2459 --
2460 -- Bug 5607069: Added exception block.
2461 --
2462 EXCEPTION
2463 WHEN e_invalid_consumption THEN
2464 NULL; -- Skip to next row
2465 END;
2466 END LOOP;
2467 END IF;
2468 END;
2469 END LOOP;
2470
2471 -- Now update the remaining_ib_doc_qty based upon the finalized layers.
2472 IF g_debug <= gme_debug.g_log_statement THEN
2473 gme_debug.put_line ('Now update the remaining_ib_doc_qty based upon the finalized layers');
2474 END IF;
2475
2476 FOR c IN c_finalize_layer_consumption LOOP
2477 BEGIN
2478 IF c.line_type = 0 THEN
2479 UPDATE gmf_resource_layers
2480 SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
2481 WHERE
2482 layer_id = c.consume_layer_id;
2483 ELSE
2484 UPDATE gmf_outgoing_material_layers
2485 SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
2486 WHERE
2487 layer_id = c.consume_layer_id;
2488 END IF;
2489 END;
2490 END LOOP;
2491
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493
2494 IF g_debug <= gme_debug.g_log_procedure THEN
2495 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2496 END IF;
2497
2498 EXCEPTION
2499 WHEN OTHERS THEN
2500 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
2501 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
2502 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
2503 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
2504 FND_MSG_PUB.Add;
2505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2506 END;
2507
2508 /*
2509 --+==========================================================================+
2510 --| PROCEDURE NAME |
2511 --| Revert_Finalization |
2512 --| |
2513 --| TYPE |
2514 --| Public |
2515 --| |
2516 --| USAGE |
2517 --| Revert_Finalization |
2518 --| |
2519 --| DESCRIPTION |
2520 --| |
2521 --| PARAMETERS |
2522 --| |
2523 --| RETURNS |
2524 --| None |
2525 --| |
2526 --| HISTORY |
2527 --| |
2528 --+==========================================================================+
2529 */
2530 PROCEDURE Revert_Finalization
2531 ( p_api_version IN NUMBER,
2532 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2533 p_batch_id IN NUMBER,
2534 x_return_status OUT NOCOPY VARCHAR2,
2535 x_msg_count OUT NOCOPY NUMBER,
2536 x_msg_data OUT NOCOPY VARCHAR2) IS
2537
2538 CURSOR c_finalize_rows IS
2539 SELECT v.ROWID, v.consume_ib_doc_qty, v.consume_layer_id, v.line_type
2540 FROM gmf_batch_vib_details v, gmf_batch_requirements r
2541 WHERE
2542 r.batch_id = p_batch_id and
2543 r.requirement_id = v.requirement_id and
2544 v.finalize_ind = 1 and
2545 v.consume_layer_id IS NOT NULL;
2546
2547 l_batch_status PLS_INTEGER;
2548 l_api_name VARCHAR2(30) := 'Revert_Finalization';
2549 BEGIN
2550
2551 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2552
2553 IF g_debug <= gme_debug.g_log_procedure THEN
2554 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
2555 END IF;
2556
2557 -- Validate batch_id
2558 BEGIN
2559 SELECT batch_status
2560 INTO l_batch_status
2561 FROM gme_batch_header
2562 WHERE batch_id = p_batch_id;
2563
2564 IF l_batch_status = 4 THEN
2565 x_return_status := FND_API.G_RET_STS_ERROR ;
2566 --dbms_output.put_line ('Cannot revert finalization of a closed batch ');
2567 FND_MESSAGE.SET_NAME('GMF', 'GMF_BATCH_CLOSED');
2568 FND_MSG_PUB.Add;
2569 RETURN;
2570 END IF;
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2574 FND_MESSAGE.SET_NAME('GMF', 'G_RET_STS_UNEXP_ERROR');
2575 FND_MSG_PUB.Add;
2576 RAISE;
2577 END;
2578
2579 -- Delete all rows from gmf_incoming_material_layers which are used in
2580 -- gmf_batch_vib_details with finalize_ind = 1
2581 IF g_debug <= gme_debug.g_log_statement THEN
2582 gme_debug.put_line ('deleting pseudo layers...');
2583 END IF;
2584
2585 DELETE from gmf_incoming_material_layers
2586 WHERE
2587 pseudo_layer_id IS NOT NULL AND
2588 layer_id in (
2589 SELECT prod_layer_id
2590 FROM gmf_batch_vib_details v,
2591 gmf_batch_requirements r
2592 WHERE
2593 r.batch_id = p_batch_id AND
2594 r.requirement_id = v.requirement_id AND
2595 v.finalize_ind = 1);
2596
2597 IF g_debug <= gme_debug.g_log_statement THEN
2598 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
2599 END IF;
2600
2601
2602 -- Delete all rows from the gmf_batch_vib_details with finalize_ind = 1
2603 -- and the comsume_layer_id is NULL.
2604 IF g_debug <= gme_debug.g_log_statement THEN
2605 gme_debug.put_line ('now deleting NULL finalized consumption layers');
2606 END IF;
2607
2608 DELETE from gmf_batch_vib_details
2609 WHERE
2610 finalize_ind = 1 and
2611 consume_layer_id IS NULL and
2612 requirement_id in (
2613 SELECT requirement_id
2614 FROM gmf_batch_requirements
2615 WHERE
2616 Batch_id = p_batch_id);
2617
2618 IF g_debug <= gme_debug.g_log_statement THEN
2619 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
2620 END IF;
2621
2622 IF g_debug <= gme_debug.g_log_statement THEN
2623 gme_debug.put_line ('now deleting regular finalized consumption layers. Also, updating remaining qty in outgoing layers table.');
2624 END IF;
2625
2626 FOR f IN c_finalize_rows LOOP
2627 BEGIN
2628 -- Update the layers material, resource remaining_doc_qty
2629 IF f.line_type = 0 THEN
2630 UPDATE gmf_resource_layers
2631 SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
2632 WHERE
2633 layer_id = f.consume_layer_id;
2634 ELSE
2635 UPDATE gmf_outgoing_material_layers
2636 SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
2637 WHERE
2638 layer_id = f.consume_layer_id;
2639 END IF;
2640
2641 -- Delete the row from the gmf_batch_vib_detail table.
2642 DELETE from gmf_batch_vib_details
2643 WHERE
2644 ROWID = f.ROWID;
2645
2646 END;
2647 END LOOP;
2648
2649 x_return_status := FND_API.G_RET_STS_SUCCESS;
2650
2651 IF g_debug <= gme_debug.g_log_procedure THEN
2652 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2653 END IF;
2654
2655 EXCEPTION
2656 WHEN OTHERS THEN
2657 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
2658 FND_MESSAGE.SET_NAME('GMI','GMF_SQL_ERROR');
2659 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
2660 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
2661 FND_MSG_PUB.Add;
2662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2663 END;
2664
2665 /*
2666 PROCEDURE allocate_ingredients
2667 (
2668 p_ac_proc_id IN NUMBER,
2669 x_return_status OUT NOCOPY VARCHAR2,
2670 x_msg_count OUT NOCOPY NUMBER,
2671 x_msg_data OUT NOCOPY VARCHAR2
2672 )
2673 IS
2674
2675 CURSOR get_latest_yield (
2676 p_co_code VARCHAR2,
2677 p_start_date DATE,
2678 p_end_date DATE
2679 )
2680 IS
2681 SELECT layer_id, trans_id, layer_doc_qty, layer_doc_um, layer_date, pseudo_layer_id,
2682 final_cost_ind, gl_posted_ind
2683 FROM (
2684 SELECT
2685 il.layer_id, il.trans_id, il.layer_doc_qty, il.layer_doc_um, il.layer_date, il.pseudo_layer_id,
2686 il.final_cost_ind, il.gl_posted_ind,
2687 hdr.batch_id,
2688 RANK() OVER(partition by hdr.batch_id ORDER BY hdr.batch_id, il.layer_date desc, il.layer_id desc) layer_rank
2689 FROM
2690 gme_batch_header hdr,
2691 sy_orgn_mst orgn,
2692 gmf_incoming_material_layers il,
2693 ic_tran_pnd pnd
2694 WHERE
2695 il.layer_date >= p_start_date
2696 AND il.layer_date <= p_end_date
2697 AND il.trans_id IS NOT NULL
2698 AND pnd.trans_id = il.trans_id
2699 AND orgn.co_code = p_co_code
2700 AND hdr.plant_code = orgn.orgn_code
2701 AND hdr.batch_id = pnd.doc_id
2702 AND hdr.batch_status <> 4
2703 ) a
2704 WHERE a.layer_rank = 1
2705 and a.layer_id = 9
2706 ORDER BY batch_id, layer_date desc
2707 ;
2708
2709 l_co_code cm_cldr_hdr.co_code%TYPE;
2710 l_start_date DATE;
2711 l_end_date DATE;
2712
2713 l_layer_rec gmf_incoming_material_layers%ROWTYPE;
2714 l_trans_rec ic_tran_pnd%ROWTYPE;
2715
2716 BEGIN
2717
2718 x_return_status := FND_API.G_RET_STS_SUCCESS ; --xxxremove
2719
2720 SELECT hdr.co_code, dtl.start_date, dtl.end_date
2721 INTO l_co_code, l_start_date, l_end_date
2722 FROM cm_acpr_ctl acpr, cm_cldr_dtl dtl, cm_cldr_hdr hdr
2723 WHERE acpr.acproc_id = p_ac_proc_id
2724 AND hdr.calendar_code = acpr.calendar_code
2725 AND hdr.cost_mthd_code = acpr.cost_mthd_code
2726 AND hdr.calendar_code = dtl.calendar_code
2727 AND dtl.period_code = acpr.period_code
2728 ;
2729
2730 OPEN get_latest_yield(l_co_code, l_start_date, l_end_date);
2731 LOOP
2732 FETCH get_latest_yield
2733 INTO l_layer_rec.layer_id, l_layer_rec.trans_id,
2734 l_layer_rec.layer_doc_qty, l_layer_rec.layer_doc_um,
2735 l_layer_rec.layer_date, l_layer_rec.pseudo_layer_id,
2736 l_layer_rec.final_cost_ind, l_layer_rec.gl_posted_ind;
2737
2738 EXIT WHEN get_latest_yield%NOTFOUND;
2739
2740 SELECT pnd.*
2741 INTO l_trans_rec
2742 FROM mtl_material_transactions pnd
2743 WHERE transaction_id = l_layer_rec.trans_id
2744 ;
2745
2746 dbms_output.put_line('processing layer_id: ' || l_layer_rec.layer_id);
2747
2748
2749 -- Now generate the VIB details for this product transaction.
2750 GMF_VIB.Create_VIB_Details (
2751 1.0,
2752 FND_API.G_TRUE,
2753 l_trans_rec,
2754 l_layer_rec,
2755 x_return_status,
2756 x_msg_count,
2757 x_msg_data,
2758 3
2759 );
2760
2761 END LOOP;
2762
2763 CLOSE get_latest_yield;
2764
2765 END allocate_ingredients;
2766 */
2767
2768 END GMF_VIB;