[Home] [Help]
PACKAGE BODY: APPS.GMF_LAYERS
Source
1 PACKAGE BODY GMF_LAYERS AS
2 /* $Header: GMFLAYRB.pls 120.1.12010000.1 2008/07/30 05:32:47 appldev ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'GMF_LAYERS';
5 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
6
7 /*
8 +==========================================================================+
9 | Copyright (c) 1998 Oracle Corporation |
10 | Redwood Shores, CA, USA |
11 | All rights reserved. |
12 +==========================================================================+
13 | FILE NAME |
14 | GMFLAYR.pls |
15 | |
16 | PACKAGE NAME |
17 | GMF_LAYERS |
18 | |
19 | DESCRIPTION |
20 | |
21 | CONTENTS |
22 | |
23 | HISTORY |
24 | 06-OCT-06 Venkat Chukkapalli BUG#5586577. |
25 | If doc type is not PROD set return status to SUCCESS to return |
26 | without an error in procedures: Create_Incoming_Layers, |
27 | Create_Outgoing_Layers and Create_Resource_Layers. |
28 +==========================================================================+
29 */
30
31 /*
32 --+==========================================================================+
33 --| PROCEDURE NAME |
34 --| Create_Incoming_Layers |
35 --| |
36 --| TYPE |
37 --| Public |
38 --| |
39 --| USAGE |
40 --| Create_Incoming_Layers |
41 --| |
42 --| DESCRIPTION |
43 --| |
44 --| PARAMETERS |
45 --| |
46 --| RETURNS |
47 --| None |
48 --| |
49 --| HISTORY |
50 --| |
51 --+==========================================================================+
52 */
53 PROCEDURE Create_Incoming_Layers
54 ( p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
56 p_tran_rec IN GMF_LAYERS.trans_rec_type,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2) IS
60
61 l_layer_id NUMBER;
62 l_layer_rec gmf_incoming_material_layers%ROWTYPE;
63 l_doc_qty NUMBER;
64 l_doc_um VARCHAR2(4);
65 l_req_count PLS_INTEGER;
66
67 l_api_name VARCHAR2(30) := 'Create_Incoming_Layers';
68
69 BEGIN
70 x_return_status := FND_API.G_RET_STS_SUCCESS ;
71
72 IF g_debug <= gme_debug.g_log_procedure THEN
73 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
74 gme_debug.put_line ('processing batch: ' || p_tran_rec.transaction_source_id || ' item/org/lot/txnId/reverseId: ' ||
75 p_tran_rec.inventory_item_id ||'/'|| p_tran_rec.organization_id || '/' ||
76 p_tran_rec.lot_number ||'/'|| p_tran_rec.transaction_id ||'/'|| p_tran_rec.reverse_id ||
77 ' line_type: ' || p_tran_rec.line_type);
78 gme_debug.put_line ('pri Qty: ' || p_tran_rec.primary_quantity || ' ' || p_tran_rec.primary_uom ||
79 ' doc qty: ' || p_tran_rec.doc_qty || ' ' || p_tran_rec.doc_uom);
80 END IF;
81
82 -- Possible validations
83 -- Verify that the there is no other record with this trans_id
84 -- Validate that this is valid trans_id
85 -- Validate that it is for doc_type PROD and line_type -1 and 2
86 -- Insert the data into the layers table
87 IF (p_tran_rec.transaction_source_type_id <> 5) THEN
88 -- Bug 5586577. Return with Success.
89 -- x_return_status := FND_API.G_RET_STS_ERROR ;
90 x_return_status := FND_API.G_RET_STS_SUCCESS ;
91 --dbms_output.put_line ('Only PROD document allowed for incoming layers');
92 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
93 -- FND_MSG_PUB.Add;
94 RETURN;
95 END IF;
96
97 IF (p_tran_rec.line_type <> 1 ) THEN
98 x_return_status := FND_API.G_RET_STS_ERROR ;
99 --dbms_output.put_line ('Only Products allowed for incoming layers');
100 FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PRODUCT_TRANS');
101 FND_MSG_PUB.Add;
102 RETURN;
103 END IF;
104
105 -- If the doc_qty is not passed for the reversed layer, get it from the original layer
106 l_doc_qty := p_tran_rec.doc_qty;
107 l_doc_um := p_tran_rec.doc_uom;
108
109 IF (p_tran_rec.reverse_id IS NOT NULL and p_tran_rec.doc_qty IS NULL) THEN
110 BEGIN
111 SELECT -l.layer_doc_qty, l.layer_doc_um
112 INTO l_doc_qty, l_doc_um
113 FROM gmf_incoming_material_layers l, mtl_material_transactions mmt,
114 mtl_transaction_lot_numbers mtln
115 WHERE
116 mmt.transaction_id = p_tran_rec.reverse_id AND
117 mtln.transaction_id (+) = p_tran_rec.reverse_id AND
118 mtln.lot_number (+) = p_tran_rec.lot_number AND
119 l.mmt_transaction_id = mmt.transaction_id AND
120 l.lot_number(+) = mtln.lot_number ;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 x_return_status := FND_API.G_RET_STS_ERROR ;
124 --dbms_output.put_line ('Could not find the reversed layer');
125 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_REVERSED_LAYER');
126 FND_MSG_PUB.Add;
127 RETURN;
128 END;
129 END IF;
130
131 IF (l_doc_qty IS NULL ) THEN
132 IF g_debug <= gme_debug.g_log_procedure THEN
133 gme_debug.put_line ('No doc quantity specified for the layer');
134 END IF;
135 x_return_status := FND_API.G_RET_STS_ERROR ;
136 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_DOC_QTY_FOR_LAYER');
137 FND_MSG_PUB.Add;
138 RETURN;
139 END IF;
140
141
142
143 -- Create the incoming layer
144 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
145
146 IF g_debug <= gme_debug.g_log_statement THEN
147 gme_debug.put_line ('creating incoming layer: ' || l_layer_id);
148 END IF;
149
150 INSERT INTO gmf_incoming_material_layers(
151 layer_id,
152 mmt_transaction_id,
153 mmt_organization_id,
154 lot_number,
155 layer_doc_qty,
156 layer_doc_um,
157 layer_date,
158 pseudo_layer_id,
159 final_cost_ind,
160 gl_posted_ind,
161 created_by,
162 creation_date,
163 last_updated_by,
164 last_update_date,
165 last_update_login,
166 accounted_flag)
167 VALUES(
168 l_layer_id,
169 p_tran_rec.transaction_id,
170 p_tran_rec.organization_id,
171 p_tran_rec.lot_number,
172 l_doc_qty,
173 l_doc_um,
174 p_tran_rec.transaction_date,
175 NULL,
176 0,
177 0,
178 p_tran_rec.created_by,
179 sysdate,
180 p_tran_rec.last_updated_by,
181 sysdate,
182 p_tran_rec.last_update_login,
183 'N');
184
185
186 l_layer_rec.layer_id := l_layer_id;
187 l_layer_rec.mmt_transaction_id := p_tran_rec.transaction_id;
188 l_layer_rec.layer_doc_qty := l_doc_qty;
189 l_layer_rec.layer_doc_um := l_doc_um;
190 l_layer_rec.layer_date := p_tran_rec.transaction_date;
191 l_layer_rec.final_cost_ind := 0;
192 l_layer_rec.gl_posted_ind := 0;
193
194 -- If the batch is completed directly, we may not have the requirements.
195 -- In such case, make the call to create the requirements.
196
197 l_req_count := 0;
198 SELECT count(*)
199 INTO l_req_count
200 FROM gmf_batch_requirements
201 WHERE batch_id = p_tran_rec.transaction_source_id
202 AND delete_mark = 0;
203
204 IF l_req_count = 0 THEN
205 IF g_debug <= gme_debug.g_log_statement THEN
206 gme_debug.put_line ('creating batch requirements before creating VIB details');
207 END IF;
208 -- Create the requirements
209 GMF_VIB.Create_Batch_Requirements(
210 1.0,
211 FND_API.G_FALSE,
212 p_tran_rec.transaction_source_id,
213 x_return_status,
214 x_msg_count,
215 x_msg_data);
216 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
217 -- requirement creation failed.
218 RETURN;
219 END IF;
220 END IF;
221
222 IF g_debug <= gme_debug.g_log_statement THEN
223 gme_debug.put_line ('now calling Create_VIB_Details');
224 END IF;
225
226 -- Now generate the VIB details for this product transaction.
227 GMF_VIB.Create_VIB_Details (
228 p_api_version,
229 p_init_msg_list,
230 p_tran_rec,
231 l_layer_rec,
232 x_return_status,
233 x_msg_count,
234 x_msg_data);
235
236 IF g_debug <= gme_debug.g_log_statement THEN
237 gme_debug.put_line ('done creating vib details. status/msg: ' || x_return_status ||'/'|| x_msg_data);
238 END IF;
239
240 IF g_debug <= gme_debug.g_log_procedure THEN
241 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
242 END IF;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
247 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
248 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
249 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
250 FND_MSG_PUB.Add;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
252 END;
253
254
255 /*
256 --+==========================================================================+
257 --| PROCEDURE NAME |
258 --| Create_Outgoing_Layers |
259 --| |
260 --| TYPE |
261 --| Public |
262 --| |
263 --| USAGE |
264 --| Create_Outgoing_Layers |
265 --| |
266 --| DESCRIPTION |
267 --| |
268 --| PARAMETERS |
269 --| |
270 --| RETURNS |
271 --| None |
272 --| |
273 --| HISTORY |
274 --| |
275 --+==========================================================================+
276 */
277 PROCEDURE Create_Outgoing_Layers
278 ( p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
280 p_tran_rec IN GMF_LAYERS.TRANS_REC_TYPE,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2) IS
284
285 CURSOR c_null_vib_rows IS
286 SELECT v.rowid, v.*, mmt.organization_id, l.layer_date
287 FROM gmf_batch_vib_details v,
288 gmf_batch_requirements r,
289 gmf_incoming_material_layers l,
290 mtl_material_transactions mmt,
291 mtl_transaction_lot_numbers mtln
292 WHERE
293 r.batch_id = p_tran_rec.transaction_source_id AND
294 r.ing_material_detail_id = p_tran_rec.trx_source_line_id AND
295 r.delete_mark = 0 AND
296 v.requirement_id = r.requirement_id AND
297 v.consume_layer_id is NULL AND
298 v.finalize_ind = 0 AND
299 l.layer_id = v.prod_layer_id AND
300 l.final_cost_ind = 0 AND
301 l.mmt_transaction_id = mmt.transaction_id AND
302 mtln.transaction_id(+) = l.mmt_transaction_id AND
303 mtln.lot_number(+) = l.lot_number AND
304 -- mmt.source_line_id = -99 AND
305 mmt.opm_costed_flag IS NOT NULL AND
306 not exists (select 'x' from gme_transaction_pairs tp
307 where transaction_id1 = mmt.transaction_id and tp.pair_type = 1)
308 ORDER by v.prod_layer_id DESC;
309
310
311 l_layer_id NUMBER;
312 l_remaining_ib_doc_qty NUMBER;
313 l_rev_consume_ib_doc_qty NUMBER;
314 l_consume_ib_doc_qty NUMBER;
315 l_consume_ib_pri_qty NUMBER;
316 l_doc_qty NUMBER;
317 l_doc_um VARCHAR2(4);
318 l_delete_mark NUMBER;
319 l_period_count PLS_INTEGER;
320 l_rowid ROWID;
321
322 e_invalid_consumption EXCEPTION;
323
324 l_api_name VARCHAR2(30) := 'Create_Outgoing_Layers';
325
326 BEGIN
327 x_return_status := FND_API.G_RET_STS_SUCCESS ;
328
329 IF g_debug <= gme_debug.g_log_procedure THEN
330 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
331 gme_debug.put_line ('processing batch: ' || p_tran_rec.transaction_source_id || ' item/org/lot/txnId/reverseId: ' ||
332 p_tran_rec.inventory_item_id ||'/'|| p_tran_rec.organization_id || '/' ||
333 p_tran_rec.lot_number ||'/'|| p_tran_rec.transaction_id ||'/'|| p_tran_rec.reverse_id ||
334 ' line_type: ' || p_tran_rec.line_type);
335 gme_debug.put_line ('pri Qty: ' || p_tran_rec.primary_quantity || ' ' || p_tran_rec.primary_uom ||
336 ' doc qty: ' || p_tran_rec.doc_qty || ' ' || p_tran_rec.doc_uom);
337 END IF;
338
339 -- Possible validations
340 -- Verify that the there is no other record with this trans_id
341 -- Validate that this is valid trans_id
342 -- Validate that it is for doc_type PROD and line_type -1 and 2
343 -- Insert the data into the layers table
344 IF (p_tran_rec.transaction_source_type_id <> 5) THEN
345 -- Bug 5586577. Return with Success.
346 -- x_return_status := FND_API.G_RET_STS_ERROR ;
347 x_return_status := FND_API.G_RET_STS_SUCCESS ;
348 --dbms_output.put_line ('Only PROD document allowed for outgoing layers');
349 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
350 -- FND_MSG_PUB.Add;
351 RETURN;
352 END IF;
353 IF (p_tran_rec.line_type <> -1 and p_tran_rec.line_type <> 2) THEN
354 x_return_status := FND_API.G_RET_STS_ERROR ;
355 gme_debug.put_line ('Error-GMF: Only Ingredients and By-Products allowed for outgoing layers');
356 FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_ING_TRANS');
357 FND_MSG_PUB.Add;
358 RETURN;
359 END IF;
360
361 -- If the doc_qty is not passed for the reversed layer, get it from the original layer
362 l_doc_qty := p_tran_rec.doc_qty;
363 l_doc_um := p_tran_rec.doc_uom;
364
365 IF (p_tran_rec.reverse_id IS NOT NULL and p_tran_rec.doc_qty IS NULL) THEN
366 BEGIN
367 SELECT -l.layer_doc_qty, l.layer_doc_um
368 INTO l_doc_qty, l_doc_um
369 FROM gmf_outgoing_material_layers l, mtl_material_transactions mmt,
370 mtl_transaction_lot_numbers mtln
371 WHERE
372 mmt.transaction_id = p_tran_rec.reverse_id AND
373 mtln.transaction_id (+) = p_tran_rec.reverse_id AND
374 mtln.lot_number (+) = p_tran_rec.lot_number AND
375 l.mmt_transaction_id = mmt.transaction_id AND
376 l.lot_number(+) = mtln.lot_number ;
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 x_return_status := FND_API.G_RET_STS_ERROR ;
380 --dbms_output.put_line ('Could not find the reversed layer');
381 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_REVERSED_LAYER');
382 FND_MSG_PUB.Add;
383 RETURN;
384 END;
385 END IF;
386
387 IF (l_doc_qty IS NULL ) THEN
388 x_return_status := FND_API.G_RET_STS_ERROR ;
389 IF g_debug <= gme_debug.g_log_procedure THEN
390 gme_debug.put_line ('No doc quantity specified for the layer');
391 END IF;
392 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_DOC_QTY_FOR_LAYER');
393 FND_MSG_PUB.Add;
394 RETURN;
395 END IF;
396
397 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
398 l_remaining_ib_doc_qty := l_doc_qty;
399
400 -- Store ingredient consumption as positive quantities
401 -- IF (p_tran_rec.line_type = -1) THEN
402 -- l_remaining_ib_doc_qty := -l_remaining_ib_doc_qty;
403 -- END IF;
404
405 -- If this a reversed layer, do not comsume from it.
406 l_delete_mark := 0;
407 IF (p_tran_rec.reverse_id IS NOT NULL) THEN
408 -- If the reverse transaction is alrady used in
409 -- VIB details, get the quantity used in VIB details.
410 -- We need to leave that much qty to reverse those VIB details later.
411
412 IF g_debug <= gme_debug.g_log_statement THEN
413 gme_debug.put_line ('Reversal. get the quantity used in VIB details, if any.');
414 END IF;
415
416 BEGIN
417 SELECT -sum(nvl(consume_ib_doc_qty,0)), l.ROWID
418 INTO l_rev_consume_ib_doc_qty, l_rowid
419 FROM gmf_outgoing_material_layers l,
420 gmf_batch_vib_details v
421 WHERE l.mmt_transaction_id = p_tran_rec.reverse_id and
422 l.lot_number = p_tran_rec.lot_number and
423 l.layer_id = v.consume_layer_id (+)
424 GROUP BY l.ROWID;
425
426
427 IF g_debug <= gme_debug.g_log_statement THEN
428 gme_debug.put_line ('Reversal. quantity already used in VIB details is: ' ||
429 l_rev_consume_ib_doc_qty);
430 END IF;
431
432 l_remaining_ib_doc_qty := l_rev_consume_ib_doc_qty;
433 IF l_rev_consume_ib_doc_qty = 0 THEN
434 l_delete_mark := 1;
435 END IF;
436
437 UPDATE gmf_outgoing_material_layers
438 SET remaining_ib_doc_qty = 0,
439 delete_mark = l_delete_mark
440 WHERE
441 ROWID = l_rowid;
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 NULL;
445 END;
446 END IF;
447
448 IF g_debug <= gme_debug.g_log_statement THEN
449 gme_debug.put_line ('l_remaining_ib_doc_qty: ' || l_remaining_ib_doc_qty);
450 END IF;
451
452 IF l_remaining_ib_doc_qty > 0 THEN
453 -- If ingredients were consumed after the product was yielded, there are
454 -- VIB details pointing to the NULL consumption layers. If possible,
455 -- Update them to consume from this layer. This is done only if the product
456 -- Layer is not posted to subledgerand the cost is not frozen.
457
458 IF g_debug <= gme_debug.g_log_statement THEN
459 gme_debug.put_line ('getting null consumption layers from VIB details, if any');
460 END IF;
461
462 FOR n IN c_null_vib_rows LOOP
463
464 IF g_debug <= gme_debug.g_log_statement THEN
465 gme_debug.put_line ('found null consumption layers. prodLayer/ConsLayer/lineType: ' ||
466 n.prod_layer_id||'/'||n.consume_layer_id||'/'||n.line_type);
467 END IF;
468
469 BEGIN
470 IF l_remaining_ib_doc_qty = 0 THEN
471 RAISE e_invalid_consumption;
472 END IF;
473
474
475 SELECT COUNT(*)
476 INTO l_period_count
477 FROM gmf_organization_definitions god,
478 gmf_period_statuses gps,
479 gmf_fiscal_policies gfp,
480 cm_mthd_mst mthd
481 WHERE god.organization_id = n.organization_id
482 AND gfp.legal_entity_id = god.legal_entity_id
483 AND mthd.cost_type_id = gfp.cost_type_id
484 AND mthd.cost_type = 1 -- Actual costing method
485 AND mthd.prodcalc_type = 1 -- PMAC product cost
486 AND gps.legal_entity_id = gfp.legal_entity_id
487 AND gps.cost_type_id = gfp.cost_type_id
488 AND n.layer_date BETWEEN gps.start_date AND gps.end_date
489 AND p_tran_rec.transaction_date BETWEEN gps.start_date AND gps.end_date;
490
491 IF l_period_count = 0 THEN
492 RAISE e_invalid_consumption;
493 END IF;
494
495 -- If ingredient qty is more then what is needed.
496 IF n.consume_ib_doc_qty <= l_remaining_ib_doc_qty THEN
497
498 IF g_debug <= gme_debug.g_log_statement THEN
499 gme_debug.put_line ('If ingredient qty is more then what is needed.');
500 END IF;
501
502 UPDATE gmf_batch_vib_details
503 SET consume_layer_id = l_layer_id,
504 consume_layer_date = p_tran_rec.transaction_date
505 WHERE ROWID = n.rowid;
506
507 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - n.consume_ib_doc_qty;
508 ELSE
509 -- If ingredient qty is less then what is needed.
510 -- Split the row into 2.
511 -- Create another row with NULL layer for remaining quantity
512 -- First convert the consume quantity to primary UM.
513
514 IF g_debug <= gme_debug.g_log_statement THEN
515 gme_debug.put_line ('If ingredient qty is less then what is needed...split the row into 2');
516 END IF;
517
518 l_consume_ib_doc_qty := n.consume_ib_doc_qty - l_remaining_ib_doc_qty;
519
520 l_consume_ib_pri_qty :=
521 INV_CONVERT.INV_UM_CONVERT(
522 ITEM_ID => p_tran_rec.inventory_item_id
523 , PRECISION => 5
524 , ORGANIZATION_ID => p_tran_rec.organization_id
525 , LOT_NUMBER => p_tran_rec.lot_number
526 , FROM_QUANTITY => l_consume_ib_doc_qty
527 , FROM_UNIT => l_doc_um
528 , TO_UNIT => p_tran_rec.primary_uom
529 , FROM_NAME => NULL
530 , TO_NAME => NULL
531 );
532
533 INSERT INTO gmf_batch_vib_details(
534 prod_layer_id,
535 prod_layer_pri_qty,
536 consume_layer_id,
537 consume_layer_date,
538 line_type,
539 requirement_id,
540 finalize_ind,
541 consume_ib_doc_qty,
542 consume_ib_pri_qty,
543 created_by,
544 creation_date,
545 last_updated_by,
546 last_update_date,
547 last_update_login)
548 VALUES(
549 n.prod_layer_id,
550 n.prod_layer_pri_qty,
551 NULL,
552 n.consume_layer_date,
553 p_tran_rec.line_type, -- ???? inserting NULL
554 n.requirement_id,
555 0,
556 l_consume_ib_doc_qty,
557 l_consume_ib_pri_qty,
558 p_tran_rec.created_by,
559 sysdate,
560 p_tran_rec.last_updated_by,
561 sysdate,
562 p_tran_rec.last_update_login);
563
564 -- Consume the current ingredient quantity
565 l_consume_ib_pri_qty :=
566 INV_CONVERT.INV_UM_CONVERT(
567 ITEM_ID => p_tran_rec.inventory_item_id
568 , PRECISION => 5
569 , ORGANIZATION_ID => p_tran_rec.organization_id
570 , LOT_NUMBER => p_tran_rec.lot_number
571 , FROM_QUANTITY => l_remaining_ib_doc_qty
572 , FROM_UNIT => l_doc_um
573 , TO_UNIT => p_tran_rec.primary_uom
574 , FROM_NAME => NULL
575 , TO_NAME => NULL
576 );
577
578 UPDATE gmf_batch_vib_details
579 SET consume_layer_id = l_layer_id,
580 consume_ib_doc_qty = l_remaining_ib_doc_qty,
581 consume_ib_pri_qty = l_consume_ib_pri_qty,
582 consume_layer_date = p_tran_rec.transaction_date
583 WHERE ROWID = n.rowid;
584
585 l_remaining_ib_doc_qty := 0;
586 END IF;
587 EXCEPTION
588 WHEN e_invalid_consumption THEN
589 NULL; -- Skip to the next row
590 END;
591 END LOOP;
592
593 IF g_debug <= gme_debug.g_log_statement THEN
594 gme_debug.put_line ('done processing consumption layers from VIB details');
595 END IF;
596
597 END IF;
598
599 IF g_debug <= gme_debug.g_log_statement THEN
600 gme_debug.put_line ('creating new outgoing layers...');
601 END IF;
602
603 INSERT INTO gmf_outgoing_material_layers(
604 layer_id,
605 mmt_transaction_id,
606 mmt_organization_id,
607 lot_number,
608 layer_doc_qty,
609 layer_doc_um,
610 remaining_ib_doc_qty,
611 delete_mark,
612 created_by,
613 creation_date,
614 last_updated_by,
615 last_update_date,
616 last_update_login)
617 VALUES(
618 l_layer_id,
619 p_tran_rec.transaction_id,
620 p_tran_rec.organization_id,
621 p_tran_rec.lot_number,
622 l_doc_qty,
623 l_doc_um,
624 l_remaining_ib_doc_qty,
625 l_delete_mark,
626 p_tran_rec.created_by,
627 sysdate,
628 p_tran_rec.last_updated_by,
629 sysdate,
630 p_tran_rec.last_update_login);
631
632 IF g_debug <= gme_debug.g_log_statement THEN
633 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
634 END IF;
635
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638
639 IF g_debug <= gme_debug.g_log_procedure THEN
640 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
641 END IF;
642
643 EXCEPTION
644 WHEN OTHERS THEN
645 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
646 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
647 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
648 FND_MSG_PUB.Add;
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
650 END;
651
652 /*
653 --+==========================================================================+
654 --| PROCEDURE NAME |
655 --| Create_Resource_Layers |
656 --| |
657 --| TYPE |
658 --| Public |
659 --| |
660 --| USAGE |
661 --| Create_Resource_Layers |
662 --| |
663 --| DESCRIPTION |
664 --| |
665 --| PARAMETERS |
666 --| |
667 --| RETURNS |
668 --| None |
669 --| |
670 --| HISTORY |
671 --| |
672 --+==========================================================================+
673 */
674 PROCEDURE Create_Resource_Layers
675 ( p_api_version IN NUMBER,
676 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
677 p_rsrc_rec IN gme_resource_txns%ROWTYPE,
678 p_doc_qty IN NUMBER,
679 p_doc_um IN VARCHAR2,
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_msg_count OUT NOCOPY NUMBER,
682 x_msg_data OUT NOCOPY VARCHAR2) IS
683
684 CURSOR c_null_vib_rows IS
685 SELECT v.rowid, v.*, t.organization_id, l.layer_date
686 FROM gmf_batch_vib_details v,
687 gmf_batch_requirements r,
688 gmf_incoming_material_layers l,
689 mtl_material_transactions t
690 WHERE
691 r.batch_id = p_rsrc_rec.doc_id AND
692 r.batchstep_resource_id = p_rsrc_rec.line_id AND
693 r.delete_mark = 0 AND
694 v.requirement_id = r.requirement_id AND
695 v.consume_layer_id is NULL AND
696 l.layer_id = v.prod_layer_id AND
697 l.final_cost_ind = 0 AND
698 l.mmt_transaction_id = t.transaction_id AND
699 t.source_line_id = -99 AND
700 t.opm_costed_flag IS NOT NULL
701 -- ORDER by v.consume_layer_id;
702 ORDER by v.prod_layer_id desc;
703
704 l_layer_id NUMBER;
705 l_remaining_ib_doc_qty NUMBER;
706 l_rev_consume_ib_doc_qty NUMBER;
707 l_consume_ib_doc_qty NUMBER;
708 l_consume_ib_pri_qty NUMBER;
709 l_delete_mark NUMBER;
710 l_period_count PLS_INTEGER;
711 l_rowid ROWID;
712
713 e_invalid_consumption EXCEPTION;
714
715 l_api_name VARCHAR2(40) := 'Create_Resource_Layers';
716
717 BEGIN
718 x_return_status := FND_API.G_RET_STS_SUCCESS ;
719
720
721 IF g_debug <= gme_debug.g_log_procedure THEN
722 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
723 gme_debug.put_line ('processing batch: ' || p_rsrc_rec.doc_id || ' lineID: ' || p_rsrc_rec.line_id ||
724 ' rsrc/org/txnId/reverseId: ' || p_rsrc_rec.resources ||'/'|| p_rsrc_rec.organization_id || '/' ||
725 p_rsrc_rec.poc_trans_id ||'/'|| p_rsrc_rec.reverse_id);
726 gme_debug.put_line ('transQty: ' || p_rsrc_rec.resource_usage || ' ' || p_rsrc_rec.trans_qty_um ||
727 ' doc qty: ' || p_doc_qty || ' ' || p_doc_um);
728 END IF;
729
730 -- Possible validations
731 -- Verify that the there is no other record with this trans_id
732 -- Validate that this is valid trans_id
733 -- Validate that it is for doc_type PROD and line_type 0
734 -- Insert the data into the layers table
735 IF (p_rsrc_rec.doc_type <> 'PROD') THEN
736 -- Bug 5586577. Return with Success.
737 -- x_return_status := FND_API.G_RET_STS_ERROR ;
738 x_return_status := FND_API.G_RET_STS_SUCCESS ;
739 --dbms_output.put_line ('Only PROD document allowed for outgoing layers');
740 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
741 -- FND_MSG_PUB.Add;
742 RETURN;
743 END IF;
744
745 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
746 l_remaining_ib_doc_qty := p_doc_qty;
747
748 -- If this a reversed layer, do not comsume from it.
749 l_delete_mark := 0;
750 IF (p_rsrc_rec.reverse_id IS NOT NULL) THEN
751 -- If the reverse transaction is alrady used in
752 -- VIB details, get the quantity used in VIB details.
753 -- We need to leave that much qty to reverse those VIB details later.
754 BEGIN
755 SELECT -sum(consume_ib_doc_qty), l.ROWID
756 INTO l_rev_consume_ib_doc_qty, l_rowid
757 FROM gmf_resource_layers l,
758 gmf_batch_vib_details v
759 WHERE l.poc_trans_id = p_rsrc_rec.reverse_id and
760 l.layer_id = v.consume_layer_id
761 GROUP BY l.ROWID;
762
763 l_remaining_ib_doc_qty := l_rev_consume_ib_doc_qty;
764
765 IF l_rev_consume_ib_doc_qty = 0 THEN
766 l_delete_mark := 1;
767 END IF;
768
769 UPDATE gmf_resource_layers
770 SET remaining_ib_doc_qty = 0,
771 delete_mark = l_delete_mark
772 WHERE
773 ROWID = l_rowid;
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 NULL;
777 END;
778 END IF;
779
780 -- ABS will handle reversal layers
781 IF l_remaining_ib_doc_qty > 0 THEN
782 -- If resource were consumed after the product was yielded, there are
783 -- VIB details pointing to the NULL consumption layers. If possible,
784 -- Update them to consume from this layer.
785 FOR n IN c_null_vib_rows LOOP
786 BEGIN
787 IF l_remaining_ib_doc_qty = 0 THEN
788 RAISE e_invalid_consumption;
789 END IF;
790
791
792 SELECT COUNT(*)
793 INTO l_period_count
794 FROM gmf_organization_definitions god,
795 gmf_period_statuses gps,
796 gmf_fiscal_policies gfp,
797 cm_mthd_mst mthd
798 WHERE god.organization_id = n.organization_id
799 AND gfp.legal_entity_id = god.legal_entity_id
800 AND mthd.cost_type_id = gfp.cost_type_id
801 AND mthd.cost_type = 1 -- Actual costing method
802 AND mthd.prodcalc_type = 1 -- PMAC product cost
803 AND gps.legal_entity_id = gfp.legal_entity_id
804 AND gps.cost_type_id = gfp.cost_type_id
805 AND n.layer_date BETWEEN gps.start_date AND gps.end_date
806 AND p_rsrc_rec.trans_date BETWEEN gps.start_date AND gps.end_date;
807
808 IF l_period_count = 0 THEN
809 RAISE e_invalid_consumption;
810 END IF;
811
812 -- If resource usage is more then what is needed.
813 IF n.consume_ib_doc_qty <= l_remaining_ib_doc_qty THEN
814
815 UPDATE gmf_batch_vib_details
816 SET consume_layer_id = l_layer_id
817 WHERE ROWID = n.rowid;
818
819 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - n.consume_ib_doc_qty;
820 ELSE
821 -- If resource usage is less then what is needed.
822 -- Split the row into 2.
823 -- Create another row with NULL layer for remaining quantity
824 -- Create another row with NULL layer for remaining quantity
825 -- First convert the consume quantity to primary UM.
826
827 l_consume_ib_doc_qty := n.consume_ib_doc_qty - l_remaining_ib_doc_qty;
828
829 l_consume_ib_pri_qty :=
830 INV_CONVERT.INV_UM_CONVERT(
831 ITEM_ID => 0
832 , PRECISION => 5
833 , ORGANIZATION_ID => n.organization_id
834 , LOT_NUMBER => NULL
835 , FROM_QUANTITY => l_consume_ib_doc_qty
836 , FROM_UNIT => p_doc_um
837 , TO_UNIT => p_rsrc_rec.trans_qty_um
838 , FROM_NAME => NULL
839 , TO_NAME => NULL
840 );
841
842
843 INSERT INTO gmf_batch_vib_details(
844 prod_layer_id,
845 prod_layer_pri_qty,
846 consume_layer_id,
847 consume_layer_date,
848 line_type,
849 requirement_id,
850 finalize_ind,
851 consume_ib_doc_qty,
852 consume_ib_pri_qty,
853 created_by,
854 creation_date,
855 last_updated_by,
856 last_update_date,
857 last_update_login)
858 VALUES(
859 n.prod_layer_id,
860 n.prod_layer_pri_qty,
861 NULL,
862 n.consume_layer_date,
863 p_rsrc_rec.line_type, -- ???? inserting NULL
864 n.requirement_id,
865 0,
866 l_consume_ib_doc_qty,
867 l_consume_ib_pri_qty,
868 p_rsrc_rec.created_by,
869 sysdate,
870 p_rsrc_rec.last_updated_by,
871 sysdate,
872 p_rsrc_rec.last_update_login);
873
874 -- Consume the current ingredient quantity
875 l_consume_ib_pri_qty :=
876 INV_CONVERT.INV_UM_CONVERT(
877 ITEM_ID => 0
878 , PRECISION => 5
879 , ORGANIZATION_ID => n.organization_id
880 , LOT_NUMBER => NULL
881 , FROM_QUANTITY => l_remaining_ib_doc_qty
882 , FROM_UNIT => p_doc_um
883 , TO_UNIT => p_rsrc_rec.trans_qty_um
884 , FROM_NAME => NULL
885 , TO_NAME => NULL
886 );
887
888
889 UPDATE gmf_batch_vib_details
890 SET consume_layer_id = l_layer_id,
891 consume_ib_pri_qty = l_consume_ib_pri_qty,
892 consume_ib_doc_qty = l_remaining_ib_doc_qty
893 WHERE ROWID = n.rowid;
894
895 l_remaining_ib_doc_qty := 0;
896 END IF;
897 EXCEPTION
898 WHEN e_invalid_consumption THEN
899 NULL; -- Skip to the next row
900 END;
901 END LOOP;
902 END IF;
903
904 INSERT INTO gmf_resource_layers(
905 layer_id,
906 poc_trans_id,
907 layer_doc_qty,
908 layer_doc_um,
909 remaining_ib_doc_qty,
910 delete_mark,
911 created_by,
912 creation_date,
913 last_updated_by,
914 last_update_date,
915 last_update_login)
916 VALUES(
917 l_layer_id,
918 p_rsrc_rec.poc_trans_id,
919 p_doc_qty,
920 p_doc_um,
921 l_remaining_ib_doc_qty,
922 l_delete_mark,
923 p_rsrc_rec.created_by,
924 sysdate,
925 p_rsrc_rec.last_updated_by,
926 sysdate,
927 p_rsrc_rec.last_update_login);
928
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 IF g_debug <= gme_debug.g_log_procedure THEN
932 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
933 END IF;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
938 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
939 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
940 FND_MSG_PUB.Add;
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
942 RAISE;
943 END;
944
945 END GMF_LAYERS;