[Home] [Help]
PACKAGE BODY: APPS.GMF_LAYERS
Source
1 PACKAGE BODY GMF_LAYERS AS
2 /* $Header: GMFLAYRB.pls 120.11.12020000.2 2013/02/11 21:16:49 pvkanetk 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 | 10-MAR-09 Hari Luthra BUG#8299247 |
30 | If the yield or consumption is not in lots then avoid NO_DATA_FOUND |
31 | |
32 +==========================================================================+
33 */
34
35 /*
36 --+==========================================================================+
37 --| PROCEDURE NAME |
38 --| Create_Incoming_Layers |
39 --| |
40 --| TYPE |
41 --| Public |
42 --| |
43 --| USAGE |
44 --| Create_Incoming_Layers |
45 --| |
46 --| DESCRIPTION |
47 --| |
48 --| PARAMETERS |
49 --| |
50 --| RETURNS |
51 --| None |
52 --| |
53 --| HISTORY |
54 --| |
55 --+==========================================================================+
56 */
57 PROCEDURE Create_Incoming_Layers
58 ( p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
60 p_tran_rec IN GMF_LAYERS.trans_rec_type,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2,
64 p_Create_vib IN VARCHAR2 DEFAULT 'Y') IS
65
66 l_layer_id NUMBER;
67 l_layer_rec gmf_incoming_material_layers%ROWTYPE;
68 l_doc_qty NUMBER;
69 l_doc_um VARCHAR2(4);
70 l_req_count PLS_INTEGER;
71
72 l_api_name VARCHAR2(30) := 'Create_Incoming_Layers';
73
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 gme_debug.put_line ('processing batch: ' || p_tran_rec.transaction_source_id || ' item/org/lot/txnId/reverseId: ' ||
80 p_tran_rec.inventory_item_id ||'/'|| p_tran_rec.organization_id || '/' ||
81 p_tran_rec.lot_number ||'/'|| p_tran_rec.transaction_id ||'/'|| p_tran_rec.reverse_id ||
82 ' line_type: ' || p_tran_rec.line_type);
83 gme_debug.put_line ('pri Qty: ' || p_tran_rec.primary_quantity || ' ' || p_tran_rec.primary_uom ||
84 ' doc qty: ' || p_tran_rec.doc_qty || ' ' || p_tran_rec.doc_uom);
85 END IF;
86
87 -- Possible validations
88 -- Verify that the there is no other record with this trans_id
89 -- Validate that this is valid trans_id
90 -- Validate that it is for doc_type PROD and line_type -1 and 2
91 -- Insert the data into the layers table
92 IF (p_tran_rec.transaction_source_type_id <> 5) THEN
93 -- Bug 5586577. Return with Success.
94 -- x_return_status := FND_API.G_RET_STS_ERROR ;
95 x_return_status := FND_API.G_RET_STS_SUCCESS ;
96 --dbms_output.put_line ('Only PROD document allowed for incoming layers');
97 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
98 -- FND_MSG_PUB.Add;
99 RETURN;
100 END IF;
101
102 IF (p_tran_rec.line_type <> 1 ) THEN
103 x_return_status := FND_API.G_RET_STS_ERROR ;
104 --dbms_output.put_line ('Only Products allowed for incoming layers');
105 FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PRODUCT_TRANS');
106 FND_MSG_PUB.Add;
107 RETURN;
108 END IF;
109
110 -- If the doc_qty is not passed for the reversed layer, get it from the original layer
111 l_doc_qty := p_tran_rec.doc_qty;
112 l_doc_um := p_tran_rec.doc_uom;
113
114 IF (p_tran_rec.reverse_id IS NOT NULL and p_tran_rec.doc_qty IS NULL) THEN
115 BEGIN
116 /* PK Bug 8219507 removed mtln */
117 SELECT -l.layer_doc_qty, l.layer_doc_um
118 INTO l_doc_qty, l_doc_um
119 FROM gmf_incoming_material_layers l, mtl_material_transactions mmt
120 -- mtl_transaction_lot_numbers mtln
121 WHERE
122 mmt.transaction_id = p_tran_rec.reverse_id AND
123 -- mtln.transaction_id (+) = p_tran_rec.reverse_id AND
124 -- mtln.lot_number (+) = p_tran_rec.lot_number AND
125 l.mmt_transaction_id = mmt.transaction_id ; -- AND
126 -- l.lot_number(+) = mtln.lot_number ;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 x_return_status := FND_API.G_RET_STS_ERROR ;
130 --dbms_output.put_line ('Could not find the reversed layer');
131 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_REVERSED_LAYER');
132 FND_MSG_PUB.Add;
133 RETURN;
134 END;
135 END IF;
136
137 IF (l_doc_qty IS NULL ) THEN
138 IF g_debug <= gme_debug.g_log_procedure THEN
139 gme_debug.put_line ('No doc quantity specified for the layer');
140 END IF;
141 x_return_status := FND_API.G_RET_STS_ERROR ;
142 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_DOC_QTY_FOR_LAYER');
143 FND_MSG_PUB.Add;
144 RETURN;
145 END IF;
146
147
148
149 -- Create the incoming layer
150 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
151
152 IF g_debug <= gme_debug.g_log_statement THEN
153 gme_debug.put_line ('creating incoming layer: ' || l_layer_id);
154 END IF;
155
156 INSERT INTO gmf_incoming_material_layers(
157 layer_id,
158 mmt_transaction_id,
159 mmt_organization_id,
160 lot_number,
161 layer_doc_qty,
162 layer_doc_um,
163 layer_date,
164 pseudo_layer_id,
165 final_cost_ind,
166 gl_posted_ind,
167 created_by,
168 creation_date,
169 last_updated_by,
170 last_update_date,
171 last_update_login,
172 accounted_flag)
173 VALUES(
174 l_layer_id,
175 p_tran_rec.transaction_id,
176 p_tran_rec.organization_id,
177 p_tran_rec.lot_number,
178 l_doc_qty,
179 l_doc_um,
180 p_tran_rec.transaction_date,
181 NULL,
182 0,
183 0,
184 p_tran_rec.created_by,
185 sysdate,
186 p_tran_rec.last_updated_by,
187 sysdate,
188 p_tran_rec.last_update_login,
189 'N');
190
191
192 l_layer_rec.layer_id := l_layer_id;
193 l_layer_rec.mmt_transaction_id := p_tran_rec.transaction_id;
194 l_layer_rec.layer_doc_qty := l_doc_qty;
195 l_layer_rec.layer_doc_um := l_doc_um;
196 l_layer_rec.layer_date := p_tran_rec.transaction_date;
197 l_layer_rec.final_cost_ind := 0;
198 l_layer_rec.gl_posted_ind := 0;
199
200 -- If the batch is completed directly, we may not have the requirements.
201 -- In such case, make the call to create the requirements.
202
203 l_req_count := 0;
204 SELECT count(*)
205 INTO l_req_count
206 FROM gmf_batch_requirements
207 WHERE batch_id = p_tran_rec.transaction_source_id
208 AND delete_mark = 0;
209
210 IF l_req_count = 0 THEN
211 IF g_debug <= gme_debug.g_log_statement THEN
212 gme_debug.put_line ('creating batch requirements before creating VIB details');
213 END IF;
214 -- Create the requirements
215 GMF_VIB.Create_Batch_Requirements(
216 1.0,
217 FND_API.G_FALSE,
218 p_tran_rec.transaction_source_id,
219 x_return_status,
220 x_msg_count,
221 x_msg_data);
222 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
223 -- requirement creation failed.
224 RETURN;
225 END IF;
226 END IF;
227
228 IF g_debug <= gme_debug.g_log_statement THEN
229 gme_debug.put_line ('now calling Create_VIB_Details');
230 END IF;
231
232 -- Now generate the VIB details for this product transaction.
233
234 -- PK B 15888374 Added check on p_Create_vib before calling Create_VIB_Details
235
236 IF NVL(p_Create_vib, 'Y') = 'Y' THEN
237
238 GMF_VIB.Create_VIB_Details (
239 p_api_version,
240 p_init_msg_list,
241 p_tran_rec,
242 l_layer_rec,
243 x_return_status,
244 x_msg_count,
245 x_msg_data);
246
247 IF g_debug <= gme_debug.g_log_statement THEN
248 gme_debug.put_line ('done creating vib details. status/msg: ' || x_return_status ||'/'|| x_msg_data);
249 END IF;
250
251 END IF; -- PK B 15888374
252
253 IF g_debug <= gme_debug.g_log_procedure THEN
254 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
255 END IF;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 gme_debug.put_line ('Exiting api (thru when others) ' || g_pkg_name || '.' || l_api_name);
260 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
261 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
262 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
263 FND_MSG_PUB.Add;
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265 END;
266
267
268 /*
269 --+==========================================================================+
270 --| PROCEDURE NAME |
271 --| Create_Outgoing_Layers |
272 --| |
273 --| TYPE |
274 --| Public |
275 --| |
276 --| USAGE |
277 --| Create_Outgoing_Layers |
278 --| |
279 --| DESCRIPTION |
280 --| |
281 --| PARAMETERS |
282 --| |
283 --| RETURNS |
284 --| None |
285 --| |
286 --| HISTORY |
287 --| 10-MAR-09 Hari Luthra BUG#8299247 |
288 --| If the yield or consumption is not in lots then avoid NO_DATA_FOUND |
289 --| |
290 --+==========================================================================+
291 */
292 PROCEDURE Create_Outgoing_Layers
293 ( p_api_version IN NUMBER,
294 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
295 p_tran_rec IN GMF_LAYERS.TRANS_REC_TYPE,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2) IS
299
300 /* PK Bug 8219507 removed mtln */
301
302 CURSOR c_null_vib_rows IS
303 SELECT v.rowid, v.*, mmt.organization_id, l.layer_date
304 FROM gmf_batch_vib_details v,
305 gmf_batch_requirements r,
306 gmf_incoming_material_layers l,
307 mtl_material_transactions mmt
308 -- mtl_transaction_lot_numbers mtln
309 WHERE
310 r.batch_id = p_tran_rec.transaction_source_id AND
311 r.ing_material_detail_id = p_tran_rec.trx_source_line_id AND
312 r.delete_mark = 0 AND
313 v.requirement_id = r.requirement_id AND
314 v.consume_layer_id is NULL AND
315 v.finalize_ind = 0 AND
316 l.layer_id = v.prod_layer_id AND
317 l.final_cost_ind = 0 AND
318 l.mmt_transaction_id = mmt.transaction_id AND
319 -- mtln.transaction_id(+) = l.mmt_transaction_id AND
320 -- mtln.lot_number(+) = l.lot_number AND
321 -- mmt.source_line_id = -99 AND
322 mmt.opm_costed_flag IS NOT NULL AND
323 not exists (select 'x' from gme_transaction_pairs tp
324 where transaction_id1 = mmt.transaction_id and tp.pair_type = 1)
325 ORDER by v.prod_layer_id DESC;
326
327
328 l_layer_id NUMBER;
329 l_remaining_ib_doc_qty NUMBER;
330 l_rev_consume_ib_doc_qty NUMBER;
331 l_consume_ib_doc_qty NUMBER;
332 l_consume_ib_pri_qty NUMBER;
333 l_doc_qty NUMBER;
334 l_doc_um VARCHAR2(4);
335 l_delete_mark NUMBER;
336 l_period_count PLS_INTEGER;
337 l_rowid ROWID;
338
339 e_invalid_consumption EXCEPTION;
340
341 l_api_name VARCHAR2(30) := 'Create_Outgoing_Layers';
342
343 BEGIN
344 x_return_status := FND_API.G_RET_STS_SUCCESS ;
345
346 IF g_debug <= gme_debug.g_log_procedure THEN
347 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
348 gme_debug.put_line ('processing batch: ' || p_tran_rec.transaction_source_id || ' item/org/lot/txnId/reverseId: ' ||
349 p_tran_rec.inventory_item_id ||'/'|| p_tran_rec.organization_id || '/' ||
350 p_tran_rec.lot_number ||'/'|| p_tran_rec.transaction_id ||'/'|| p_tran_rec.reverse_id ||
351 ' line_type: ' || p_tran_rec.line_type);
352 gme_debug.put_line ('pri Qty: ' || p_tran_rec.primary_quantity || ' ' || p_tran_rec.primary_uom ||
353 ' doc qty: ' || p_tran_rec.doc_qty || ' ' || p_tran_rec.doc_uom);
354 END IF;
355
356 -- Possible validations
357 -- Verify that the there is no other record with this trans_id
358 -- Validate that this is valid trans_id
359 -- Validate that it is for doc_type PROD and line_type -1 and 2
360 -- Insert the data into the layers table
361 IF (p_tran_rec.transaction_source_type_id <> 5) THEN
362 -- Bug 5586577. Return with Success.
363 -- x_return_status := FND_API.G_RET_STS_ERROR ;
364 x_return_status := FND_API.G_RET_STS_SUCCESS ;
365 --dbms_output.put_line ('Only PROD document allowed for outgoing layers');
366 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
367 -- FND_MSG_PUB.Add;
368 RETURN;
369 END IF;
370 IF (p_tran_rec.line_type <> -1 and p_tran_rec.line_type <> 2) THEN
371 x_return_status := FND_API.G_RET_STS_ERROR ;
372 gme_debug.put_line ('Error-GMF: Only Ingredients and By-Products allowed for outgoing layers');
373 FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_ING_TRANS');
374 FND_MSG_PUB.Add;
375 RETURN;
376 END IF;
377
378 -- If the doc_qty is not passed for the reversed layer, get it from the original layer
379 l_doc_qty := p_tran_rec.doc_qty;
380 l_doc_um := p_tran_rec.doc_uom;
381
382 IF (p_tran_rec.reverse_id IS NOT NULL and p_tran_rec.doc_qty IS NULL) THEN
383 BEGIN
384 /* PK Bug 8219507 removed mtln */
385 SELECT -l.layer_doc_qty, l.layer_doc_um
386 INTO l_doc_qty, l_doc_um
387 FROM gmf_outgoing_material_layers l, mtl_material_transactions mmt
388 -- mtl_transaction_lot_numbers mtln
389 WHERE
390 mmt.transaction_id = p_tran_rec.reverse_id AND
391 -- mtln.transaction_id (+) = p_tran_rec.reverse_id AND
392 -- mtln.lot_number (+) = p_tran_rec.lot_number AND
393 l.mmt_transaction_id = mmt.transaction_id; -- AND
394 -- l.lot_number(+) = mtln.lot_number ;
395 EXCEPTION
396 WHEN NO_DATA_FOUND THEN
397 x_return_status := FND_API.G_RET_STS_ERROR ;
398 --dbms_output.put_line ('Could not find the reversed layer');
399 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_REVERSED_LAYER');
400 FND_MSG_PUB.Add;
401 RETURN;
402 END;
403 END IF;
404
405 IF (l_doc_qty IS NULL ) THEN
406 x_return_status := FND_API.G_RET_STS_ERROR ;
407 IF g_debug <= gme_debug.g_log_procedure THEN
408 gme_debug.put_line ('No doc quantity specified for the layer');
409 END IF;
410 FND_MESSAGE.SET_NAME('GMF', 'GMF_NO_DOC_QTY_FOR_LAYER');
411 FND_MSG_PUB.Add;
412 RETURN;
413 END IF;
414
415 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
416 l_remaining_ib_doc_qty := l_doc_qty;
417
418 -- Store ingredient consumption as positive quantities
419 -- IF (p_tran_rec.line_type = -1) THEN
420 -- l_remaining_ib_doc_qty := -l_remaining_ib_doc_qty;
421 -- END IF;
422
423 -- If this a reversed layer, do not comsume from it.
424 l_delete_mark := 0;
425 IF (p_tran_rec.reverse_id IS NOT NULL) THEN
426 -- If the reverse transaction is alrady used in
427 -- VIB details, get the quantity used in VIB details.
428 -- We need to leave that much qty to reverse those VIB details later.
429
430 IF g_debug <= gme_debug.g_log_statement THEN
431 gme_debug.put_line ('Reversal. get the quantity used in VIB details, if any.');
432 END IF;
433
434 BEGIN
435
436 /* HALUTHRA : Bug 8299247. Chaging l.lot_number = p_tran_rec.lot_number to
437 nvl(l.lot_number,'X')=nvl(p_tran_rec.lot_number,'X') */
438
439 SELECT -sum(nvl(consume_ib_doc_qty,0)), l.ROWID
440 INTO l_rev_consume_ib_doc_qty, l_rowid
441 FROM gmf_outgoing_material_layers l,
442 gmf_batch_vib_details v
443 WHERE l.mmt_transaction_id = p_tran_rec.reverse_id and
444 nvl(l.lot_number,'X')= nvl(p_tran_rec.lot_number,'X') and
445 l.layer_id = v.consume_layer_id (+)
446 GROUP BY l.ROWID;
447
448 IF g_debug <= gme_debug.g_log_statement THEN
449 gme_debug.put_line ('Reversal. quantity already used in VIB details is: ' ||
450 l_rev_consume_ib_doc_qty);
451 END IF;
452
453 l_remaining_ib_doc_qty := l_rev_consume_ib_doc_qty;
454 IF l_rev_consume_ib_doc_qty = 0 THEN
455 l_delete_mark := 1;
456 END IF;
457
458 UPDATE gmf_outgoing_material_layers
459 SET remaining_ib_doc_qty = 0,
460 delete_mark = l_delete_mark
461 WHERE
462 ROWID = l_rowid;
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 NULL;
466 END;
467 END IF;
468
469 IF g_debug <= gme_debug.g_log_statement THEN
470 gme_debug.put_line ('l_remaining_ib_doc_qty: ' || l_remaining_ib_doc_qty);
471 END IF;
472
473 IF l_remaining_ib_doc_qty > 0 THEN
474 -- If ingredients were consumed after the product was yielded, there are
475 -- VIB details pointing to the NULL consumption layers. If possible,
476 -- Update them to consume from this layer. This is done only if the product
477 -- Layer is not posted to subledgerand the cost is not frozen.
478
479 IF g_debug <= gme_debug.g_log_statement THEN
480 gme_debug.put_line ('getting null consumption layers from VIB details, if any');
481 END IF;
482
483 FOR n IN c_null_vib_rows LOOP
484
485 IF g_debug <= gme_debug.g_log_statement THEN
486 gme_debug.put_line ('found null consumption layers. prodLayer/ConsLayer/lineType: ' ||
487 n.prod_layer_id||'/'||n.consume_layer_id||'/'||n.line_type);
488 END IF;
489
490 BEGIN
491 IF l_remaining_ib_doc_qty = 0 THEN
492 RAISE e_invalid_consumption;
493 END IF;
494
495
496 SELECT COUNT(*)
497 INTO l_period_count
498 FROM gmf_organization_definitions god,
499 gmf_period_statuses gps,
500 gmf_fiscal_policies gfp,
501 cm_mthd_mst mthd
502 WHERE god.organization_id = n.organization_id
503 AND gfp.legal_entity_id = god.legal_entity_id
504 AND mthd.cost_type_id = gfp.cost_type_id
505 AND mthd.cost_type = 1 -- Actual costing method
506 AND mthd.prodcalc_type = 1 -- PMAC product cost
507 AND gps.legal_entity_id = gfp.legal_entity_id
508 AND gps.cost_type_id = gfp.cost_type_id
509 AND n.layer_date BETWEEN gps.start_date AND gps.end_date
510 AND p_tran_rec.transaction_date BETWEEN gps.start_date AND gps.end_date;
511
512 IF l_period_count = 0 THEN
513 RAISE e_invalid_consumption;
514 END IF;
515
516 -- If ingredient qty is more then what is needed.
517 IF n.consume_ib_doc_qty <= l_remaining_ib_doc_qty THEN
518
519 IF g_debug <= gme_debug.g_log_statement THEN
520 gme_debug.put_line ('If ingredient qty is more then what is needed.');
521 END IF;
522
523 UPDATE gmf_batch_vib_details
524 SET consume_layer_id = l_layer_id,
525 consume_layer_date = p_tran_rec.transaction_date
526 WHERE ROWID = n.rowid;
527
528 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - n.consume_ib_doc_qty;
529 ELSE
530 -- If ingredient qty is less then what is needed.
531 -- Split the row into 2.
532 -- Create another row with NULL layer for remaining quantity
533 -- First convert the consume quantity to primary UM.
534
535 IF g_debug <= gme_debug.g_log_statement THEN
536 gme_debug.put_line ('If ingredient qty is less then what is needed...split the row into 2');
537 END IF;
538
539 l_consume_ib_doc_qty := n.consume_ib_doc_qty - l_remaining_ib_doc_qty;
540
541 l_consume_ib_pri_qty :=
542 INV_CONVERT.INV_UM_CONVERT(
543 ITEM_ID => p_tran_rec.inventory_item_id
544 , PRECISION => 5
545 , ORGANIZATION_ID => p_tran_rec.organization_id
546 , LOT_NUMBER => p_tran_rec.lot_number
547 , FROM_QUANTITY => l_consume_ib_doc_qty
548 , FROM_UNIT => l_doc_um
549 , TO_UNIT => p_tran_rec.primary_uom
550 , FROM_NAME => NULL
551 , TO_NAME => NULL
552 );
553
554 INSERT INTO gmf_batch_vib_details(
555 prod_layer_id,
556 prod_layer_pri_qty,
557 consume_layer_id,
558 consume_layer_date,
559 line_type,
560 requirement_id,
561 finalize_ind,
562 consume_ib_doc_qty,
563 consume_ib_pri_qty,
564 created_by,
565 creation_date,
566 last_updated_by,
567 last_update_date,
568 last_update_login)
569 VALUES(
570 n.prod_layer_id,
571 n.prod_layer_pri_qty,
572 NULL,
573 n.consume_layer_date,
574 p_tran_rec.line_type, -- ???? inserting NULL
575 n.requirement_id,
576 0,
577 l_consume_ib_doc_qty,
578 l_consume_ib_pri_qty,
579 p_tran_rec.created_by,
580 sysdate,
581 p_tran_rec.last_updated_by,
582 sysdate,
583 p_tran_rec.last_update_login);
584
585 -- Consume the current ingredient quantity
586 l_consume_ib_pri_qty :=
587 INV_CONVERT.INV_UM_CONVERT(
588 ITEM_ID => p_tran_rec.inventory_item_id
589 , PRECISION => 5
590 , ORGANIZATION_ID => p_tran_rec.organization_id
591 , LOT_NUMBER => p_tran_rec.lot_number
592 , FROM_QUANTITY => l_remaining_ib_doc_qty
593 , FROM_UNIT => l_doc_um
594 , TO_UNIT => p_tran_rec.primary_uom
595 , FROM_NAME => NULL
596 , TO_NAME => NULL
597 );
598
599 UPDATE gmf_batch_vib_details
600 SET consume_layer_id = l_layer_id,
601 consume_ib_doc_qty = l_remaining_ib_doc_qty,
602 consume_ib_pri_qty = l_consume_ib_pri_qty,
603 consume_layer_date = p_tran_rec.transaction_date
604 WHERE ROWID = n.rowid;
605
606 l_remaining_ib_doc_qty := 0;
607 END IF;
608 EXCEPTION
609 WHEN e_invalid_consumption THEN
610 NULL; -- Skip to the next row
611 END;
612 END LOOP;
613
614 IF g_debug <= gme_debug.g_log_statement THEN
615 gme_debug.put_line ('done processing consumption layers from VIB details');
616 END IF;
617
618 END IF;
619
620 IF g_debug <= gme_debug.g_log_statement THEN
621 gme_debug.put_line ('creating new outgoing layers...');
622 END IF;
623
624 INSERT INTO gmf_outgoing_material_layers(
625 layer_id,
626 mmt_transaction_id,
627 mmt_organization_id,
628 lot_number,
629 layer_doc_qty,
630 layer_doc_um,
631 remaining_ib_doc_qty,
632 delete_mark,
633 created_by,
634 creation_date,
635 last_updated_by,
636 last_update_date,
637 last_update_login)
638 VALUES(
639 l_layer_id,
640 p_tran_rec.transaction_id,
641 p_tran_rec.organization_id,
642 p_tran_rec.lot_number,
643 l_doc_qty,
644 l_doc_um,
645 l_remaining_ib_doc_qty,
646 l_delete_mark,
647 p_tran_rec.created_by,
648 sysdate,
649 p_tran_rec.last_updated_by,
650 sysdate,
651 p_tran_rec.last_update_login);
652
653 IF g_debug <= gme_debug.g_log_statement THEN
654 gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
655 END IF;
656
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659
660 IF g_debug <= gme_debug.g_log_procedure THEN
661 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
662 END IF;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
667 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
668 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
669 FND_MSG_PUB.Add;
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
671 END;
672
673 /*
674 --+==========================================================================+
675 --| PROCEDURE NAME |
676 --| Create_Resource_Layers |
677 --| |
678 --| TYPE |
679 --| Public |
680 --| |
681 --| USAGE |
682 --| Create_Resource_Layers |
683 --| |
684 --| DESCRIPTION |
685 --| |
686 --| PARAMETERS |
687 --| |
688 --| RETURNS |
689 --| None |
690 --| |
691 --| HISTORY |
692 --| 10-Mar-09 HARI LUTHRA BUG 8299247 |
693 --| To avoid null return , including nvl and outer join in a query |
694 --| |
695 --+==========================================================================+
696 */
697 PROCEDURE Create_Resource_Layers
698 ( p_api_version IN NUMBER,
699 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
700 p_rsrc_rec IN gme_resource_txns%ROWTYPE,
701 p_doc_qty IN NUMBER,
702 p_doc_um IN VARCHAR2,
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_msg_count OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2) IS
706
707 CURSOR c_null_vib_rows IS
708 --SELECT v.rowid, v.*, t.organization_id, l.layer_date
709 -- VKINDURI Added hint as per bug # 13424903
710 SELECT /*+ LEADING(r) */ v.rowid, v.*, t.organization_id, l.layer_date
711 FROM gmf_batch_requirements r,
712 gmf_batch_vib_details v,
713 gmf_incoming_material_layers l,
714 mtl_material_transactions t
715 WHERE
716 r.batch_id = p_rsrc_rec.doc_id AND
717 r.batchstep_resource_id = p_rsrc_rec.line_id AND
718 r.delete_mark = 0 AND
719 v.requirement_id = r.requirement_id AND
720 v.consume_layer_id is NULL AND
721 l.layer_id = v.prod_layer_id AND
722 l.final_cost_ind = 0 AND
723 l.mmt_transaction_id = t.transaction_id AND
724 -- t.source_line_id = -99 AND commented out for Bug 8472152
725 t.opm_costed_flag IS NOT NULL
726 -- ORDER by v.consume_layer_id;
727 ORDER by v.prod_layer_id desc;
728
729 l_layer_id NUMBER;
730 l_remaining_ib_doc_qty NUMBER;
731 l_rev_consume_ib_doc_qty NUMBER;
732 l_consume_ib_doc_qty NUMBER;
733 l_consume_ib_pri_qty NUMBER;
734 l_delete_mark NUMBER;
735 l_period_count PLS_INTEGER;
736 l_rowid ROWID;
737
738 e_invalid_consumption EXCEPTION;
739
740 l_api_name VARCHAR2(40) := 'Create_Resource_Layers';
741
742 BEGIN
743 x_return_status := FND_API.G_RET_STS_SUCCESS ;
744
745
746 IF g_debug <= gme_debug.g_log_procedure THEN
747 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
748 gme_debug.put_line ('processing batch: ' || p_rsrc_rec.doc_id || ' lineID: ' || p_rsrc_rec.line_id ||
749 ' rsrc/org/txnId/reverseId: ' || p_rsrc_rec.resources ||'/'|| p_rsrc_rec.organization_id || '/' ||
750 p_rsrc_rec.poc_trans_id ||'/'|| p_rsrc_rec.reverse_id);
751 gme_debug.put_line ('transQty: ' || p_rsrc_rec.resource_usage || ' ' || p_rsrc_rec.trans_qty_um ||
752 ' doc qty: ' || p_doc_qty || ' ' || p_doc_um);
753 END IF;
754
755 -- Possible validations
756 -- Verify that the there is no other record with this trans_id
757 -- Validate that this is valid trans_id
758 -- Validate that it is for doc_type PROD and line_type 0
759 -- Insert the data into the layers table
760 IF (p_rsrc_rec.doc_type <> 'PROD') THEN
761 -- Bug 5586577. Return with Success.
762 -- x_return_status := FND_API.G_RET_STS_ERROR ;
763 x_return_status := FND_API.G_RET_STS_SUCCESS ;
764 --dbms_output.put_line ('Only PROD document allowed for outgoing layers');
765 -- FND_MESSAGE.SET_NAME('GMF', 'GMF_NON_PROD_TRANS');
766 -- FND_MSG_PUB.Add;
767 RETURN;
768 END IF;
769
770 SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
771 l_remaining_ib_doc_qty := p_doc_qty;
772
773 -- If this a reversed layer, do not comsume from it.
774 l_delete_mark := 0;
775 IF (p_rsrc_rec.reverse_id IS NOT NULL) THEN
776 -- If the reverse transaction is alrady used in
777 -- VIB details, get the quantity used in VIB details.
778 -- We need to leave that much qty to reverse those VIB details later.
779 BEGIN
780
781
782 /*HALUTHRA BUG 8299247 Adding NVL for the nvl(-sum(consume_ib_doc_qty),0) and outer join for l.layer_id = v.consume_layer_id(+) */
783
784 SELECT -sum(nvl(consume_ib_doc_qty,0)), l.ROWID
785 INTO l_rev_consume_ib_doc_qty, l_rowid
786 FROM gmf_resource_layers l,
787 gmf_batch_vib_details v
788 WHERE l.poc_trans_id = p_rsrc_rec.reverse_id and
789 l.layer_id = v.consume_layer_id(+)
790 GROUP BY l.ROWID;
791
792 l_remaining_ib_doc_qty := l_rev_consume_ib_doc_qty;
793
794 IF l_rev_consume_ib_doc_qty = 0 THEN
795 l_delete_mark := 1;
796 END IF;
797
798 UPDATE gmf_resource_layers
799 SET remaining_ib_doc_qty = 0,
800 delete_mark = l_delete_mark
801 WHERE
802 ROWID = l_rowid;
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 NULL;
806 END;
807 END IF;
808
809 -- ABS will handle reversal layers
810 IF l_remaining_ib_doc_qty > 0 THEN
811 -- If resource were consumed after the product was yielded, there are
812 -- VIB details pointing to the NULL consumption layers. If possible,
813 -- Update them to consume from this layer.
814 FOR n IN c_null_vib_rows LOOP
815 BEGIN
816 IF l_remaining_ib_doc_qty = 0 THEN
817 RAISE e_invalid_consumption;
818 END IF;
819
820 -- PK Bug 8472152 Commented next statement. Null vib records from past period should be updated as well
821
822 /* SELECT COUNT(*)
823 INTO l_period_count
824 FROM gmf_organization_definitions god,
825 gmf_period_statuses gps,
826 gmf_fiscal_policies gfp,
827 cm_mthd_mst mthd
828 WHERE god.organization_id = n.organization_id
829 AND gfp.legal_entity_id = god.legal_entity_id
830 AND mthd.cost_type_id = gfp.cost_type_id
831 AND mthd.cost_type = 1 -- Actual costing method
832 AND mthd.prodcalc_type = 1 -- PMAC product cost
833 AND gps.legal_entity_id = gfp.legal_entity_id
834 AND gps.cost_type_id = gfp.cost_type_id
835 AND n.layer_date BETWEEN gps.start_date AND gps.end_date
836 AND p_rsrc_rec.trans_date BETWEEN gps.start_date AND gps.end_date;
837
838 IF l_period_count = 0 THEN
839 RAISE e_invalid_consumption;
840 END IF; */
841
842 -- If resource usage is more then what is needed.
843 IF n.consume_ib_doc_qty <= l_remaining_ib_doc_qty THEN
844
845 UPDATE gmf_batch_vib_details
846 SET consume_layer_id = l_layer_id
847 WHERE ROWID = n.rowid;
848
849 l_remaining_ib_doc_qty := l_remaining_ib_doc_qty - n.consume_ib_doc_qty;
850 ELSE
851 -- If resource usage is less then what is needed.
852 -- Split the row into 2.
853 -- Create another row with NULL layer for remaining quantity
854 -- Create another row with NULL layer for remaining quantity
855 -- First convert the consume quantity to primary UM.
856
857 l_consume_ib_doc_qty := n.consume_ib_doc_qty - l_remaining_ib_doc_qty;
858
859 l_consume_ib_pri_qty :=
860 INV_CONVERT.INV_UM_CONVERT(
861 ITEM_ID => 0
862 , PRECISION => 5
863 , ORGANIZATION_ID => n.organization_id
864 , LOT_NUMBER => NULL
865 , FROM_QUANTITY => l_consume_ib_doc_qty
866 , FROM_UNIT => p_doc_um
867 , TO_UNIT => p_rsrc_rec.trans_qty_um
868 , FROM_NAME => NULL
869 , TO_NAME => NULL
870 );
871
872
873 INSERT INTO gmf_batch_vib_details(
874 prod_layer_id,
875 prod_layer_pri_qty,
876 consume_layer_id,
877 consume_layer_date,
878 line_type,
879 requirement_id,
880 finalize_ind,
881 consume_ib_doc_qty,
882 consume_ib_pri_qty,
883 created_by,
884 creation_date,
885 last_updated_by,
886 last_update_date,
887 last_update_login)
888 VALUES(
889 n.prod_layer_id,
890 n.prod_layer_pri_qty,
891 NULL,
892 n.consume_layer_date,
893 p_rsrc_rec.line_type, -- ???? inserting NULL
894 n.requirement_id,
895 0,
896 l_consume_ib_doc_qty,
897 l_consume_ib_pri_qty,
898 p_rsrc_rec.created_by,
899 sysdate,
900 p_rsrc_rec.last_updated_by,
901 sysdate,
902 p_rsrc_rec.last_update_login);
903
904 -- Consume the current ingredient quantity
905 l_consume_ib_pri_qty :=
906 INV_CONVERT.INV_UM_CONVERT(
907 ITEM_ID => 0
908 , PRECISION => 5
909 , ORGANIZATION_ID => n.organization_id
910 , LOT_NUMBER => NULL
911 , FROM_QUANTITY => l_remaining_ib_doc_qty
912 , FROM_UNIT => p_doc_um
913 , TO_UNIT => p_rsrc_rec.trans_qty_um
914 , FROM_NAME => NULL
915 , TO_NAME => NULL
916 );
917
918
919 UPDATE gmf_batch_vib_details
920 SET consume_layer_id = l_layer_id,
921 consume_ib_pri_qty = l_consume_ib_pri_qty,
922 consume_ib_doc_qty = l_remaining_ib_doc_qty
923 WHERE ROWID = n.rowid;
924
925 l_remaining_ib_doc_qty := 0;
926 END IF;
927 EXCEPTION
928 WHEN e_invalid_consumption THEN
929 NULL; -- Skip to the next row
930 END;
931 END LOOP;
932 END IF;
933
934 INSERT INTO gmf_resource_layers(
935 layer_id,
936 poc_trans_id,
937 layer_doc_qty,
938 layer_doc_um,
939 remaining_ib_doc_qty,
940 delete_mark,
941 created_by,
942 creation_date,
943 last_updated_by,
944 last_update_date,
945 last_update_login)
946 VALUES(
947 l_layer_id,
948 p_rsrc_rec.poc_trans_id,
949 p_doc_qty,
950 p_doc_um,
951 l_remaining_ib_doc_qty,
952 l_delete_mark,
953 p_rsrc_rec.created_by,
954 sysdate,
955 p_rsrc_rec.last_updated_by,
956 sysdate,
957 p_rsrc_rec.last_update_login);
958
959 x_return_status := FND_API.G_RET_STS_SUCCESS;
960
961 IF g_debug <= gme_debug.g_log_procedure THEN
962 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
963 END IF;
964
965 EXCEPTION
966 WHEN OTHERS THEN
967 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
968 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
969 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
970 FND_MSG_PUB.Add;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
972 RAISE;
973 END;
974
975 -- Begin Additions for relayering concurrent request.
976 /*
977 --+==========================================================================+
978 --| PROCEDURE NAME |
979 --| log_message |
980 --| |
981 --| TYPE |
982 --| Public |
983 --| |
984 --| USAGE |
985 --| log_message |
986 --| |
987 --| DESCRIPTION |
988 --| |
989 --| PARAMETERS |
990 --| |
991 --| RETURNS |
992 --| None |
993 --| |
994 --| HISTORY |
995 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
996 --+==========================================================================+
997 */
998
999 PROCEDURE log_message (
1000 p_table_name IN VARCHAR2,
1001 p_procedure_name IN VARCHAR2,
1002 p_parameters IN VARCHAR2,
1003 p_message IN VARCHAR2,
1004 p_error_type IN VARCHAR2
1005 ) IS
1006 PRAGMA autonomous_transaction;
1007 g_date_format VARCHAR2(100) := 'YYYY-MM-DD HH24:MI:SS';
1008 BEGIN
1009 INSERT INTO gme_temp_exceptions (
1010 table_name,
1011 procedure_name,
1012 parameters,
1013 message,
1014 error_type,
1015 script_date
1016 ) VALUES (
1017 p_table_name,
1018 p_procedure_name,
1019 p_parameters,
1020 TO_CHAR (SYSDATE, g_date_format) || ': ' || p_message,
1021 p_error_type,
1022 SYSDATE);
1023 COMMIT;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 -- fnd_file.put_line(fnd_file.log, 'SQLERRM = '||SQLERRM);
1027 RAISE;
1028 END log_message;
1029
1030 /*
1031 --+==========================================================================+
1032 --| PROCEDURE NAME |
1033 --| Delete_old_layers |
1034 --| |
1035 --| TYPE |
1036 --| Public |
1037 --| |
1038 --| USAGE |
1039 --| Delete_old_layers |
1040 --| |
1041 --| DESCRIPTION |
1042 --| |
1043 --| PARAMETERS |
1044 --| |
1045 --| RETURNS |
1046 --| None |
1047 --| |
1048 --| HISTORY |
1049 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
1050 --+==========================================================================+
1051 */
1052
1053 PROCEDURE Delete_old_layers (p_batch_id IN NUMBER) IS
1054 err_num NUMBER;
1055 err_msg VARCHAR2(100);
1056
1057 BEGIN
1058
1059 DELETE
1060 FROM gmf_layer_cost_details c
1061 WHERE
1062 c.layer_id IN
1063 (SELECT il.layer_id
1064 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1065 WHERE h.batch_id = p_batch_id
1066 AND h.batch_id = t.transaction_source_id
1067 AND t.transaction_source_type_id = 5
1068 AND il.mmt_transaction_id = t.transaction_id
1069 AND il.mmt_organization_id = t.organization_id
1070 );
1071
1072 /* Bug 13442362 13367279. This query does a full table scan of gmf_incoming_material_layers
1073 This is also not required since PSEUDO_LAYER record now has transaction_id of last yield.
1074 Thus next query is still deleting it using the index
1075
1076 DELETE
1077 FROM gmf_incoming_material_layers il
1078 WHERE il.PSEUDO_LAYER_ID IS NOT NULL
1079 AND EXISTS
1080 (SELECT 1
1081 FROM gme_batch_header h, mtl_material_transactions t,
1082 gmf_incoming_material_layers im
1083 WHERE h.batch_id = p_batch_id
1084 AND h.batch_id = t.transaction_source_id
1085 AND t.transaction_source_type_id = 5
1086 AND im.mmt_transaction_id = t.transaction_id
1087 AND im.mmt_organization_id = t.organization_id
1088 AND im.layer_id = il.PSEUDO_LAYER_ID
1089 );
1090
1091 Bug 13442362 13367279. End */
1092
1093
1094 DELETE
1095 FROM gmf_incoming_material_layers il
1096 WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
1097 (SELECT distinct t.organization_id, t.transaction_id
1098 FROM gme_batch_header h, mtl_material_transactions t
1099 WHERE h.batch_id = p_batch_id
1100 AND h.batch_id = t.transaction_source_id
1101 AND t.transaction_source_type_id = 5
1102 );
1103
1104 DELETE
1105 FROM gmf_outgoing_material_layers ol
1106 WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
1107 (SELECT distinct t.organization_id, t.transaction_id
1108 FROM gme_batch_header h, mtl_material_transactions t
1109 WHERE h.batch_id = p_batch_id
1110 AND h.batch_id = t.transaction_source_id
1111 AND t.transaction_source_type_id = 5
1112 );
1113
1114 DELETE
1115 FROM gmf_resource_layers il
1116 WHERE il.poc_trans_id IN
1117 (SELECT t.poc_trans_id
1118 FROM gme_batch_header h, gme_resource_txns t
1119 WHERE h.batch_id = p_batch_id
1120 AND h.batch_id = t.doc_id
1121 AND t.doc_type = 'PROD'
1122 );
1123
1124 DELETE
1125 FROM gmf_batch_vib_details bvd
1126 WHERE bvd.requirement_id IN
1127 (SELECT br.requirement_id
1128 FROM gmf_batch_requirements br, gme_batch_header h
1129 WHERE h.batch_id = p_batch_id
1130 AND h.batch_id = br.batch_id
1131 );
1132
1133 DELETE
1134 FROM gmf_batch_requirements br
1135 WHERE br.batch_id IN
1136 (SELECT batch_id
1137 FROM gme_batch_header
1138 WHERE batch_id = p_batch_id
1139 ) ;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 err_num := SQLCODE;
1144 err_msg := SUBSTRB(SQLERRM, 1, 100);
1145 GMF_LAYERS.log_message (
1146 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1147 p_procedure_name => 'Delete_old_layers',
1148 p_parameters => err_num,
1149 p_message => 'Error deleting Old layer data for batch_id = '||p_batch_id||' '||err_msg,
1150 p_error_type => 'I');
1151 fnd_file.put_line(fnd_file.log, to_char(SQLCODE)||': '||err_msg);
1152 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1153 fnd_file.put_line(fnd_file.log, ' Error Deleting Old layer data. ');
1154 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1155 RAISE;
1156
1157 END Delete_old_layers;
1158
1159 /*
1160 --+==========================================================================+
1161 --| PROCEDURE NAME |
1162 --| Delete_period_layers |
1163 --| |
1164 --| TYPE |
1165 --| Public |
1166 --| |
1167 --| USAGE |
1168 --| Delete_period_layers |
1169 --| |
1170 --| DESCRIPTION |
1171 --| |
1172 --| PARAMETERS |
1173 --| |
1174 --| RETURNS |
1175 --| None |
1176 --| |
1177 --| HISTORY |
1178 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
1179 --+==========================================================================+
1180 */
1181
1182 PROCEDURE Delete_period_layers (p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
1183 err_num NUMBER;
1184 err_msg VARCHAR2(100);
1185 l_count NUMBER;
1186 l_start_date DATE;
1187 l_end_date DATE;
1188 e_baddata EXCEPTION;
1189
1190 CURSOR cur_incoming_layers IS
1191 select count(*)
1192 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1193 WHERE h.batch_id = p_batch_id
1194 AND h.batch_id = t.transaction_source_id
1195 AND t.transaction_source_type_id = 5
1196 AND il.mmt_transaction_id = t.transaction_id
1197 AND il.mmt_organization_id = t.organization_id;
1198
1199 -- Cusror selects prior period consumptions for yield in current period
1200 CURSOR CUR_VIB_DETAILS IS
1201 SELECT CONSUME_LAYER_ID, CONSUME_IB_DOC_QTY, CONSUME_IB_PRI_QTY, LINE_TYPE
1202 FROM gmf_batch_vib_details bvd
1203 WHERE bvd.CONSUME_LAYER_DATE < l_start_date
1204 AND bvd.prod_layer_id IN
1205 (SELECT il.layer_id
1206 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1207 WHERE h.batch_id = p_batch_id
1208 AND h.batch_id = t.transaction_source_id
1209 AND t.transaction_source_type_id = 5
1210 AND il.mmt_transaction_id = t.transaction_id
1211 AND il.mmt_organization_id = t.organization_id
1212 AND il.layer_date >= l_start_date
1213 );
1214
1215 vib CUR_VIB_DETAILS%ROWTYPE;
1216
1217 BEGIN
1218
1219 select start_date, end_date INTO l_start_date, l_end_date
1220 from gmf_period_statuses
1221 where period_id = p_period_id;
1222
1223
1224 DELETE
1225 FROM gmf_layer_cost_details c
1226 WHERE c.layer_id IN
1227 (SELECT il.layer_id
1228 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1229 WHERE h.batch_id = p_batch_id
1230 AND h.batch_id = t.transaction_source_id
1231 AND t.transaction_source_type_id = 5
1232 AND il.mmt_transaction_id = t.transaction_id
1233 AND il.mmt_organization_id = t.organization_id
1234 AND t.transaction_date >= l_start_date
1235 );
1236
1237 -- Add code to addback quantities to old layers here.
1238 /* Pseudo code
1239 Select all VIB details to be deleted. Vib details has these columns of interest.
1240 PROD_LAYER_ID (These will be deleted.) CONSUME_LAYER_ID ( Quantity needs to be added to this layer)
1241 CONSUME_LAYER_DATE ( Add quantity if CONSUME_LAYER_DATE is in a past period.
1242 That is CONSUME_LAYER_DATE < (select start_date from gmf_period_statuses where period_id = p_period_id)
1243 CONSUME_IB_DOC_QTY, CONSUME_IB_PRI_QTY.
1244 Quanity CONSUME_IB_DOC_QTY needs to be added to REMAINING_IB_DOC_QTY of gmf_outgoing_material_layers or gmf_resource_layers.
1245 Note that CONSUME_LAYER_ID could either belong to gmf_outgoing_material_layers or gmf_resource_layers.
1246 LINE_TYPE will decide whether it is outgoing layer or resource layer.
1247
1248 */
1249 FOR vib IN CUR_VIB_DETAILS LOOP
1250
1251 IF vib.LINE_TYPE IN (-1, 2) THEN
1252
1253 Update gmf_outgoing_material_layers
1254 set REMAINING_IB_DOC_QTY = REMAINING_IB_DOC_QTY + vib.CONSUME_IB_DOC_QTY
1255 where layer_id = vib.CONSUME_LAYER_ID;
1256
1257 ELSIF vib.LINE_TYPE = 0 THEN
1258
1259 Update gmf_resource_layers
1260 set REMAINING_IB_DOC_QTY = REMAINING_IB_DOC_QTY + vib.CONSUME_IB_DOC_QTY
1261 where layer_id = vib.CONSUME_LAYER_ID;
1262
1263 ELSE
1264
1265 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1266 fnd_file.put_line(fnd_file.log, ' ERROR in ELSE condition deleting period layers. ');
1267 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1268 RAISE e_baddata;
1269
1270 END IF;
1271
1272 END LOOP;
1273
1274
1275 -- Code to add quantities from VIB details ends here
1276 DELETE
1277 FROM gmf_batch_vib_details bvd
1278 WHERE bvd.prod_layer_id IN
1279 (SELECT il.layer_id
1280 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
1281 WHERE h.batch_id = p_batch_id
1282 AND h.batch_id = t.transaction_source_id
1283 AND t.transaction_source_type_id = 5
1284 AND il.mmt_transaction_id = t.transaction_id
1285 AND il.mmt_organization_id = t.organization_id
1286 AND il.layer_date >= l_start_date
1287 );
1288
1289 DELETE
1290 FROM gmf_outgoing_material_layers ol
1291 WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
1292 (SELECT distinct t.organization_id, t.transaction_id
1293 FROM gme_batch_header h, mtl_material_transactions t
1294 WHERE h.batch_id = p_batch_id
1295 AND h.batch_id = t.transaction_source_id
1296 AND t.transaction_source_type_id = 5
1297 AND t.transaction_date >= l_start_date
1298 );
1299
1300 DELETE
1301 FROM gmf_resource_layers il
1302 WHERE il.poc_trans_id IN
1303 (SELECT t.poc_trans_id
1304 FROM gme_batch_header h, gme_resource_txns t
1305 WHERE h.batch_id = p_batch_id
1306 AND h.batch_id = t.doc_id
1307 AND t.doc_type = 'PROD'
1308 AND t.trans_date >= l_start_date
1309 );
1310
1311 DELETE
1312 FROM gmf_incoming_material_layers il
1313 WHERE il.layer_id IN
1314 (SELECT il1.layer_id
1315 FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il1
1316 WHERE h.batch_id = p_batch_id
1317 AND h.batch_id = t.transaction_source_id
1318 AND t.transaction_source_type_id = 5
1319 AND il1.mmt_transaction_id = t.transaction_id
1320 AND il1.mmt_organization_id = t.organization_id
1321 AND il1.layer_date >= l_start_date
1322 );
1323
1324
1325 -- delete conditionally if no prior incoming layer exists.Or delete requirements if running for first period?
1326
1327 OPEN cur_incoming_layers;
1328 FETCH cur_incoming_layers into l_count;
1329 Close cur_incoming_layers;
1330
1331 IF (l_count = 0) THEN
1332
1333 DELETE
1334 FROM gmf_batch_requirements br
1335 WHERE br.batch_id IN
1336 (SELECT batch_id
1337 FROM gme_batch_header
1338 WHERE batch_id = p_batch_id
1339 );
1340
1341 END IF;
1342
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 err_num := SQLCODE;
1346 err_msg := SUBSTRB(SQLERRM, 1, 100);
1347 GMF_LAYERS.log_message (
1348 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1349 p_procedure_name => 'Delete_period_layers',
1350 p_parameters => err_num,
1351 p_message => 'Error deleting Old layer data for batch_id = '||p_batch_id||' Period id '||p_period_id||
1352 ' '||err_msg,p_error_type => 'I');
1353 fnd_file.put_line(fnd_file.log, to_char(SQLCODE)||': '||err_msg);
1354 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1355 fnd_file.put_line(fnd_file.log, ' Error Deleting Old layer data, Please ROLLBACK. ');
1356 fnd_file.put_line(fnd_file.log, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1357 RAISE;
1358
1359 END Delete_period_layers;
1360
1361 /*
1362 --+==========================================================================+
1363 --| PROCEDURE NAME |
1364 --| Recreate_outgoing_layers |
1365 --| |
1366 --| TYPE |
1367 --| Public |
1368 --| |
1369 --| USAGE |
1370 --| Recreate_outgoing_layers |
1371 --| |
1372 --| DESCRIPTION |
1373 --| |
1374 --| PARAMETERS |
1375 --| |
1376 --| RETURNS |
1377 --| None |
1378 --| |
1379 --| HISTORY |
1380 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
1381 --+==========================================================================+
1382 */
1383
1384 PROCEDURE Recreate_outgoing_layers(p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
1385
1386 err_num NUMBER;
1387 err_msg VARCHAR2(100);
1388 g_mig_date DATE := SYSDATE;
1389 g_date_format VARCHAR2(100) := 'YYYY-MM-DD HH24:MI:SS';
1390 l_start_date DATE ;
1391 l_end_date DATE;
1392 l_batch_id NUMBER := p_batch_id;
1393 l_period_id NUMBER := p_period_id;
1394
1395 CURSOR c_mig_trans IS
1396 select *
1397 from
1398 (
1399 SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
1400 md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
1401 tp.transaction_id2 as reverse_id
1402 from mtl_material_transactions mmt,
1403 gme_transaction_pairs tp,
1404 gme_material_details md,
1405 gme_batch_header b
1406 where md.batch_id = b.batch_id
1407 and b.batch_id = l_batch_id
1408 and mmt.transaction_source_type_id = 5
1409 and mmt.transaction_source_id = b.batch_id
1410 and mmt.trx_source_line_id = md.material_detail_id
1411 and mmt.inventory_item_id = md.inventory_item_id
1412 and mmt.organization_id = md.organization_id
1413 and md.line_type IN (-1, 2)
1414 and tp.transaction_id1(+) = mmt.transaction_id
1415 AND mmt.transaction_date >= l_start_date
1416 AND mmt.transaction_date <= l_end_date
1417 and tp.pair_type(+) = 1
1418 )
1419 ORDER BY trans_date, line_type,
1420 DECODE (line_type,
1421 1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
1422 / DECODE (trans_qty, 0, 1, trans_qty)
1423 ),
1424 1, trans_id,
1425 DECODE (reverse_id,
1426 NULL, trans_id,
1427 reverse_id + .5
1428 )
1429 ),
1430 trans_id
1431 );
1432
1433
1434 mt mtl_material_transactions%ROWTYPE;
1435 rt gme_resource_txns%ROWTYPE;
1436 l_lot_number VARCHAR2(100);
1437
1438 x_return_status VARCHAR2(10);
1439 x_msg_count NUMBER;
1440 i NUMBER;
1441 x_msg_data VARCHAR2(1000);
1442 l_count PLS_INTEGER;
1443 l_il_count PLS_INTEGER := 0;
1444 l_rl_count PLS_INTEGER := 0;
1445 l_ol_count PLS_INTEGER := 0;
1446 l_doc_qty NUMBER;
1447 e_uom_conv_failure EXCEPTION;
1448
1449 l_trans_rec GMF_LAYERS.TRANS_REC_TYPE;
1450 BEGIN
1451 select start_date, end_date INTO l_start_date, l_end_date
1452 from gmf_period_statuses
1453 where period_id = p_period_id;
1454
1455 GMF_LAYERS.log_message (
1456 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1457 p_procedure_name => 'None',
1458 p_parameters => 'None',
1459 p_message => 'Started the migration Recreate_outgoing_layers for the batch',
1460 p_error_type => 'I');
1461
1462 GMF_LAYERS.log_message (
1463 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1464 p_procedure_name => 'None',
1465 p_parameters => 'None',
1466 p_message => 'Profile GMF_USE_VIB_FOR_ACOST = '||FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST'),
1467 p_error_type => 'I');
1468
1469 GMF_LAYERS.log_message (
1470 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1471 p_procedure_name => 'None',
1472 p_parameters => 'None',
1473 p_message => 'Profile GMF_USE_ITEM_STEP_DEPENDENCIES = '||FND_PROFILE.VALUE ('GMF_USE_ITEM_STEP_DEPENDENCIES'),
1474 p_error_type => 'I');
1475
1476
1477 FOR t IN c_mig_trans LOOP
1478 BEGIN
1479
1480 x_msg_count := 0;
1481 x_return_status := 0;
1482
1483 fnd_msg_pub.initialize;
1484
1485 FOR trans_rec in
1486 (
1487 SELECT
1488 mmt.transaction_id
1489 , mmt.transaction_source_type_id
1490 , mmt.transaction_action_id
1491 , mmt.transaction_type_id
1492 , mmt.inventory_item_id
1493 , mmt.organization_id
1494 , NULL as lot_number /* B9442981 */
1495 , mmt.transaction_date
1496 , mmt.primary_quantity as primary_quantity /* B9442981 Doc Qty */
1497 , msi.primary_uom_code
1498 , mmt.transaction_source_id -- batch_id
1499 , mmt.trx_source_line_id -- line_id
1500 , mmt.last_updated_by
1501 , mmt.created_by
1502 , mmt.last_update_login
1503 FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
1504 mtl_system_items_b msi
1505 WHERE
1506 mmt.ROWID = t.r_id
1507 -- AND mtln.transaction_id (+) = mmt.transaction_id
1508 AND msi.inventory_item_id = mmt.inventory_item_id
1509 AND msi.organization_id = mmt.organization_id
1510 )
1511 LOOP
1512
1513 l_trans_rec.transaction_id := trans_rec.transaction_id;
1514 l_trans_rec.transaction_source_type_id := trans_rec.transaction_source_type_id;
1515 l_trans_rec.transaction_action_id := trans_rec.transaction_action_id;
1516 l_trans_rec.transaction_type_id := trans_rec.transaction_type_id;
1517 l_trans_rec.inventory_item_id := trans_rec.inventory_item_id;
1518 l_trans_rec.organization_id := trans_rec.organization_id;
1519 l_trans_rec.lot_number := trans_rec.lot_number;
1520 l_trans_rec.transaction_date := trans_rec.transaction_date;
1521 l_trans_rec.primary_quantity := trans_rec.primary_quantity;
1522 l_trans_rec.primary_uom := trans_rec.primary_uom_code;
1523 l_trans_rec.doc_uom := t.doc_um;
1524 l_trans_rec.transaction_source_id := trans_rec.transaction_source_id;
1525 l_trans_rec.trx_source_line_id := trans_rec.trx_source_line_id;
1526 l_trans_rec.reverse_id := t.reverse_id;
1527 l_trans_rec.line_type := t.line_type;
1528 l_trans_rec.last_updated_by := trans_rec.last_updated_by;
1529 l_trans_rec.created_by := trans_rec.created_by;
1530 l_trans_rec.last_update_login := trans_rec.last_update_login;
1531
1532 SELECT count(*)
1533 INTO l_count
1534 FROM gmf_outgoing_material_layers
1535 WHERE
1536 mmt_transaction_id = trans_rec.transaction_id
1537 AND ((lot_number is not null and lot_number = trans_rec.lot_number)
1538 OR
1539 (lot_number is null))
1540 ;
1541
1542
1543 IF l_count = 0 THEN
1544 -- Convert transaction qty in the document UOM
1545
1546 BEGIN
1547
1548
1549 l_trans_rec.doc_qty :=
1550 INV_CONVERT.INV_UM_CONVERT(
1551 ITEM_ID => trans_rec.inventory_item_id
1552 , PRECISION => 5
1553 , ORGANIZATION_ID => trans_rec.organization_id
1554 , LOT_NUMBER => trans_rec.lot_number
1555 , FROM_QUANTITY => trans_rec.primary_quantity
1556 , FROM_UNIT => trans_rec.primary_uom_code
1557 , TO_UNIT => t.doc_um
1558 , FROM_NAME => NULL
1559 , TO_NAME => NULL
1560 );
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 -- fnd_file.put_line ('Error in UOM conversion');
1565 -- fnd_file.put_line ('From UOM = '||mt.trans_um||', To UOM = '||t.doc_um);
1566 GMF_LAYERS.log_message (
1567 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1568 p_procedure_name => 'None',
1569 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id)||
1570 ' From UOM = '||trans_rec.primary_uom_code||', To UOM = '||t.doc_um,
1571 p_message => 'UOM Conversion error',
1572 p_error_type => 'E');
1573 RAISE e_uom_conv_failure;
1574 END;
1575
1576 IF t.line_type = -1 THEN
1577 l_trans_rec.doc_qty := -l_trans_rec.doc_qty;
1578 END IF;
1579
1580 gmf_layers.Create_outgoing_Layers
1581 ( p_api_version => 1.0,
1582 p_init_msg_list => FND_API.G_FALSE,
1583 p_tran_rec => l_trans_rec,
1584 x_return_status => x_return_status,
1585 x_msg_count => x_msg_count,
1586 x_msg_data => x_msg_data);
1587
1588 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1589 --
1590 -- fnd_file.put_line ('Error creating outgoing layer : '||to_char (mt.trans_id));
1591 GMF_LAYERS.log_message (
1592 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1593 p_procedure_name => 'None',
1594 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id),
1595 p_message => 'Error creating outgoing layer',
1596 p_error_type => 'E');
1597 FOR i IN 1..fnd_msg_pub.count_msg
1598 LOOP
1599 GMF_LAYERS.log_message (
1600 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1601 p_procedure_name => 'None',
1602 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id),
1603 p_message => fnd_msg_pub.get_detail (i, NULL),
1604 p_error_type => 'E');
1605 END LOOP;
1606 ELSE
1607 l_ol_count := l_ol_count + 1;
1608 END IF;
1609 END IF;
1610 END LOOP;
1611 EXCEPTION
1612 WHEN e_uom_conv_failure THEN
1613 NULL;
1614 END;
1615 END LOOP;
1616
1617
1618 GMF_LAYERS.log_message (
1619 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1620 p_procedure_name => 'None',
1621 p_parameters => 'Incoming Layers = '||to_char(l_il_count)||
1622 ', Outgoing Layers = '||to_char(l_ol_count)||
1623 ', Resource Layers = '||to_char(l_rl_count),
1624 p_message => 'Completed the migration to Recreate_outgoing_layers for the batch',
1625 p_error_type => 'I');
1626
1627
1628
1629 END Recreate_outgoing_layers;
1630
1631 /*
1632 --+==========================================================================+
1633 --| PROCEDURE NAME |
1634 --| Recreate_resource_layers |
1635 --| |
1636 --| TYPE |
1637 --| Public |
1638 --| |
1639 --| USAGE |
1640 --| Recreate_resource_layers |
1641 --| |
1642 --| DESCRIPTION |
1643 --| |
1644 --| PARAMETERS |
1645 --| |
1646 --| RETURNS |
1647 --| None |
1648 --| |
1649 --| HISTORY |
1650 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
1651 --+==========================================================================+
1652 */
1653
1654 PROCEDURE Recreate_resource_layers(p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
1655
1656 err_num NUMBER;
1657 err_msg VARCHAR2(100);
1658 g_mig_date DATE := SYSDATE;
1659 g_date_format VARCHAR2(100) := 'YYYY-MM-DD HH24:MI:SS';
1660 l_start_date DATE ;
1661 l_end_date DATE;
1662 l_batch_id NUMBER := p_batch_id; -- Replace this batch-id with proper batch-id.
1663
1664 CURSOR c_mig_trans IS
1665 select *
1666 from
1667 ( SELECT rt.rowid r_id, rt.poc_trans_id as trans_id, rt.trans_date, rt.line_type as line_type, rt.trans_qty_um as doc_um, 'R' as type,
1668 rt.resource_usage as trans_qty, rt.reverse_id
1669 FROM gme_resource_txns rt, gme_batch_header b
1670 WHERE rt.doc_type = 'PROD'
1671 AND rt.doc_id = b.batch_id
1672 AND rt.completed_ind = 1
1673 AND rt.delete_mark = 0
1674 AND rt.doc_id = l_batch_id
1675 AND rt.trans_date >= l_start_date
1676 AND rt.trans_date <= l_end_date
1677 )
1678 ORDER BY trans_date, line_type,
1679 DECODE (line_type,
1680 1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
1681 / DECODE (trans_qty, 0, 1, trans_qty)
1682 ),
1683 1, trans_id,
1684 DECODE (reverse_id,
1685 NULL, trans_id,
1686 reverse_id + .5
1687 )
1688 ),
1689 trans_id
1690 );
1691
1692 mt mtl_material_transactions%ROWTYPE;
1693 rt gme_resource_txns%ROWTYPE;
1694 l_lot_number VARCHAR2(100);
1695
1696 x_return_status VARCHAR2(10);
1697 x_msg_count NUMBER;
1698 i NUMBER;
1699 x_msg_data VARCHAR2(1000);
1700 l_count PLS_INTEGER;
1701 l_il_count PLS_INTEGER := 0;
1702 l_rl_count PLS_INTEGER := 0;
1703 l_ol_count PLS_INTEGER := 0;
1704 l_doc_qty NUMBER;
1705 e_uom_conv_failure EXCEPTION;
1706
1707 l_trans_rec GMF_LAYERS.TRANS_REC_TYPE;
1708 BEGIN
1709
1710 select start_date, end_date INTO l_start_date, l_end_date
1711 from gmf_period_statuses
1712 where period_id = p_period_id;
1713
1714 GMF_LAYERS.log_message (
1715 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1716 p_procedure_name => 'None',
1717 p_parameters => 'None',
1718 p_message => 'Started the migration to Recreate_resource_layers for the batch',
1719 p_error_type => 'I');
1720
1721 GMF_LAYERS.log_message (
1722 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1723 p_procedure_name => 'None',
1724 p_parameters => 'None',
1725 p_message => 'Profile GMF_USE_VIB_FOR_ACOST = '||FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST'),
1726 p_error_type => 'I');
1727
1728 GMF_LAYERS.log_message (
1729 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1730 p_procedure_name => 'None',
1731 p_parameters => 'None',
1732 p_message => 'Profile GMF_USE_ITEM_STEP_DEPENDENCIES = '||FND_PROFILE.VALUE ('GMF_USE_ITEM_STEP_DEPENDENCIES'),
1733 p_error_type => 'I');
1734
1735
1736 FOR t IN c_mig_trans LOOP
1737 BEGIN
1738
1739 x_msg_count := 0;
1740 x_return_status := 0;
1741
1742 fnd_msg_pub.initialize;
1743
1744 SELECT * INTO rt
1745 FROM gme_resource_txns
1746 WHERE
1747 ROWID = t.r_id;
1748
1749 SELECT count(*)
1750 INTO l_count
1751 FROM gmf_resource_layers
1752 WHERE
1753 poc_trans_id = rt.poc_trans_id;
1754
1755 IF l_count = 0 THEN
1756 GMF_LAYERS.Create_Resource_Layers(
1757 1.0,
1758 FND_API.G_FALSE,
1759 rt,
1760 rt.resource_usage,
1761 rt.trans_qty_um,
1762 x_return_status,
1763 x_msg_count,
1764 x_msg_data);
1765
1766 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1767 -- fnd_file.put_line ('Error creating resource layer : '||to_char (rt.poc_trans_id));
1768 GMF_LAYERS.log_message (
1769 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1770 p_procedure_name => 'None',
1771 p_parameters => 'POC Trans ID = '||to_char(rt.poc_trans_id),
1772 p_message => 'Error creating resource layer',
1773 p_error_type => 'E');
1774 FOR i IN 1..fnd_msg_pub.count_msg LOOP
1775 GMF_LAYERS.log_message (
1776 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1777 p_procedure_name => 'None',
1778 p_parameters => 'POC Trans ID = '||to_char(rt.poc_trans_id),
1779 p_message => fnd_msg_pub.get_detail (i, NULL),
1780 p_error_type => 'E');
1781 END LOOP;
1782 ELSE
1783 l_rl_count := l_rl_count + 1;
1784 END IF;
1785 END IF;
1786
1787 EXCEPTION
1788 WHEN e_uom_conv_failure THEN
1789 NULL;
1790 END;
1791 END LOOP;
1792
1793 GMF_LAYERS.log_message (
1794 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1795 p_procedure_name => 'None',
1796 p_parameters => 'Incoming Layers = '||to_char(l_il_count)||
1797 ', Outgoing Layers = '||to_char(l_ol_count)||
1798 ', Resource Layers = '||to_char(l_rl_count),
1799 p_message => 'Completed the migration to Recreate_resource_layers for the batch',
1800 p_error_type => 'I');
1801
1802 END Recreate_resource_layers;
1803
1804 /*
1805 --+==========================================================================+
1806 --| PROCEDURE NAME |
1807 --| Recreate_incoming_layers |
1808 --| |
1809 --| TYPE |
1810 --| Public |
1811 --| |
1812 --| USAGE |
1813 --| Recreate_incoming_layers |
1814 --| |
1815 --| DESCRIPTION |
1816 --| |
1817 --| PARAMETERS |
1818 --| |
1819 --| RETURNS |
1820 --| None |
1821 --| |
1822 --| HISTORY |
1823 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
1824 --+==========================================================================+
1825 */
1826
1827 PROCEDURE Recreate_incoming_layers(p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
1828 err_num NUMBER;
1829 err_msg VARCHAR2(100);
1830 g_mig_date DATE := SYSDATE;
1831 g_date_format VARCHAR2(100) := 'YYYY-MM-DD HH24:MI:SS';
1832 l_start_date DATE ;
1833 l_end_date DATE ;
1834 l_batch_id NUMBER := p_batch_id; -- Replace this batch-id with proper batch-id.
1835 l_prev_mat_det_id NUMBER := 0; -- B 15888374
1836 l_use_vib VARCHAR2(1);
1837 l_use_step_dep VARCHAR2(1);
1838
1839 CURSOR c_mig_trans IS
1840 select *
1841 from
1842 (
1843 SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
1844 md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
1845 tp.transaction_id2 as reverse_id, mmt.trx_source_line_id AS mat_det_id
1846 from mtl_material_transactions mmt,
1847 gme_transaction_pairs tp,
1848 gme_material_details md,
1849 gme_batch_header b
1850 where md.batch_id = b.batch_id
1851 and b.batch_id = l_batch_id
1852 and mmt.transaction_source_type_id = 5
1853 and mmt.transaction_source_id = b.batch_id
1854 and mmt.trx_source_line_id = md.material_detail_id
1855 and mmt.inventory_item_id = md.inventory_item_id
1856 and mmt.organization_id = md.organization_id
1857 and md.line_type = 1
1858 and tp.transaction_id1(+) = mmt.transaction_id
1859 AND mmt.transaction_date >= l_start_date
1860 AND mmt.transaction_date <= l_end_date
1861 and tp.pair_type(+) = 1
1862 )
1863 ORDER BY mat_det_id, trans_date, line_type,
1864 DECODE (line_type,
1865 1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
1866 / DECODE (trans_qty, 0, 1, trans_qty)
1867 ),
1868 1, trans_id,
1869 DECODE (reverse_id,
1870 NULL, trans_id,
1871 reverse_id + .5
1872 )
1873 ),
1874 trans_id
1875 );
1876
1877
1878
1879 mt mtl_material_transactions%ROWTYPE;
1880 rt gme_resource_txns%ROWTYPE;
1881 l_lot_number VARCHAR2(100);
1882
1883 x_return_status VARCHAR2(10);
1884 x_msg_count NUMBER;
1885 i NUMBER;
1886 x_msg_data VARCHAR2(1000);
1887 l_count PLS_INTEGER;
1888 l_il_count PLS_INTEGER := 0;
1889 l_rl_count PLS_INTEGER := 0;
1890 l_ol_count PLS_INTEGER := 0;
1891 l_doc_qty NUMBER;
1892 e_uom_conv_failure EXCEPTION;
1893
1894 l_trans_rec GMF_LAYERS.TRANS_REC_TYPE;
1895 BEGIN
1896
1897 select start_date, end_date INTO l_start_date, l_end_date
1898 from gmf_period_statuses
1899 where period_id = p_period_id;
1900
1901 l_use_vib := FND_PROFILE.VALUE ('GMF_USE_VIB_FOR_ACOST');
1902 l_use_step_dep := FND_PROFILE.VALUE ('GMF_USE_ITEM_STEP_DEPENDENCIES');
1903
1904 GMF_LAYERS.log_message (
1905 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1906 p_procedure_name => 'None',
1907 p_parameters => 'None',
1908 p_message => 'Started the migration to Recreate_incoming_layers for the batch',
1909 p_error_type => 'I');
1910
1911 GMF_LAYERS.log_message (
1912 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1913 p_procedure_name => 'None',
1914 p_parameters => 'None',
1915 p_message => 'Profile GMF_USE_VIB_FOR_ACOST = '||l_use_vib,
1916 p_error_type => 'I');
1917
1918 GMF_LAYERS.log_message (
1919 p_table_name => 'GMF_BATCH_VIB_DETAILS',
1920 p_procedure_name => 'None',
1921 p_parameters => 'None',
1922 p_message => 'Profile GMF_USE_ITEM_STEP_DEPENDENCIES = '||l_use_step_dep,
1923 p_error_type => 'I');
1924
1925
1926 FOR t IN c_mig_trans LOOP
1927 BEGIN
1928
1929 x_msg_count := 0;
1930 x_return_status := 0;
1931
1932 fnd_msg_pub.initialize;
1933
1934 FOR trans_rec in
1935 (
1936 SELECT
1937 mmt.transaction_id
1938 , mmt.transaction_source_type_id
1939 , mmt.transaction_action_id
1940 , mmt.transaction_type_id
1941 , mmt.inventory_item_id
1942 , mmt.organization_id
1943 , NULL as lot_number /* B9442981 */
1944 , mmt.transaction_date
1945 , mmt.primary_quantity as primary_quantity /* B9442981 Doc Qty */
1946 , msi.primary_uom_code
1947 , mmt.transaction_source_id -- batch_id
1948 , mmt.trx_source_line_id -- line_id
1949 , mmt.last_updated_by
1950 , mmt.created_by
1951 , mmt.last_update_login
1952 FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
1953 mtl_system_items_b msi
1954 WHERE
1955 mmt.ROWID = t.r_id
1956 -- AND mtln.transaction_id (+) = mmt.transaction_id
1957 AND msi.inventory_item_id = mmt.inventory_item_id
1958 AND msi.organization_id = mmt.organization_id
1959 )
1960 LOOP
1961
1962 l_trans_rec.transaction_id := trans_rec.transaction_id;
1963 l_trans_rec.transaction_source_type_id := trans_rec.transaction_source_type_id;
1964 l_trans_rec.transaction_action_id := trans_rec.transaction_action_id;
1965 l_trans_rec.transaction_type_id := trans_rec.transaction_type_id;
1966 l_trans_rec.inventory_item_id := trans_rec.inventory_item_id;
1967 l_trans_rec.organization_id := trans_rec.organization_id;
1968 l_trans_rec.lot_number := trans_rec.lot_number;
1969 l_trans_rec.transaction_date := trans_rec.transaction_date;
1970 l_trans_rec.primary_quantity := trans_rec.primary_quantity;
1971 l_trans_rec.primary_uom := trans_rec.primary_uom_code;
1972 l_trans_rec.doc_uom := t.doc_um;
1973 l_trans_rec.transaction_source_id := trans_rec.transaction_source_id;
1974 l_trans_rec.trx_source_line_id := trans_rec.trx_source_line_id;
1975 l_trans_rec.reverse_id := t.reverse_id;
1976 l_trans_rec.line_type := t.line_type;
1977 l_trans_rec.last_updated_by := trans_rec.last_updated_by;
1978 l_trans_rec.created_by := trans_rec.created_by;
1979 l_trans_rec.last_update_login := trans_rec.last_update_login;
1980
1981 IF t.line_type = 1 THEN
1982 SELECT count(*)
1983 INTO l_count
1984 FROM gmf_incoming_material_layers
1985 WHERE
1986 mmt_transaction_id = trans_rec.transaction_id
1987 AND ((lot_number is not null and lot_number = trans_rec.lot_number)
1988 OR
1989 (lot_number is null))
1990 ;
1991 ELSE
1992 SELECT count(*)
1993 INTO l_count
1994 FROM gmf_outgoing_material_layers
1995 WHERE
1996 mmt_transaction_id = trans_rec.transaction_id
1997 AND ((lot_number is not null and lot_number = trans_rec.lot_number)
1998 OR
1999 (lot_number is null))
2000 ;
2001 END IF;
2002
2003
2004 IF l_count = 0 THEN
2005 -- Convert transaction qty in the document UOM
2006
2007 BEGIN
2008
2009 l_trans_rec.doc_qty :=
2010 INV_CONVERT.INV_UM_CONVERT(
2011 ITEM_ID => trans_rec.inventory_item_id
2012 , PRECISION => 5
2013 , ORGANIZATION_ID => trans_rec.organization_id
2014 , LOT_NUMBER => trans_rec.lot_number
2015 , FROM_QUANTITY => trans_rec.primary_quantity
2016 , FROM_UNIT => trans_rec.primary_uom_code
2017 , TO_UNIT => t.doc_um
2018 , FROM_NAME => NULL
2019 , TO_NAME => NULL
2020 );
2021
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 -- fnd_file.put_line ('Error in UOM conversion');
2025 -- fnd_file.put_line ('From UOM = '||mt.trans_um||', To UOM = '||t.doc_um);
2026 GMF_LAYERS.log_message (
2027 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2028 p_procedure_name => 'None',
2029 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id)||
2030 ' From UOM = '||trans_rec.primary_uom_code||', To UOM = '||t.doc_um,
2031 p_message => 'UOM Conversion error',
2032 p_error_type => 'E');
2033 RAISE e_uom_conv_failure;
2034 END;
2035
2036 IF (l_prev_mat_det_id = t.mat_det_id AND NVL(l_use_vib, 'N') = 'N' AND NVL(l_use_vib, 'N') = 'N') THEN
2037
2038 gmf_layers.Create_Incoming_Layers
2039 ( p_api_version => 1.0,
2040 p_init_msg_list => FND_API.G_FALSE,
2041 p_tran_rec => l_trans_rec,
2042 x_return_status => x_return_status,
2043 x_msg_count => x_msg_count,
2044 x_msg_data => x_msg_data,
2045 p_Create_vib => 'N'
2046 );
2047 ELSE
2048
2049 gmf_layers.Create_Incoming_Layers
2050 ( p_api_version => 1.0,
2051 p_init_msg_list => FND_API.G_FALSE,
2052 p_tran_rec => l_trans_rec,
2053 x_return_status => x_return_status,
2054 x_msg_count => x_msg_count,
2055 x_msg_data => x_msg_data,
2056 p_Create_vib => 'Y'
2057 );
2058
2059 END IF;
2060
2061 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2062 -- fnd_file.put_line ('Error creating incoming layer : '||to_char (mt.trans_id));
2063 GMF_LAYERS.log_message (
2064 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2065 p_procedure_name => 'None',
2066 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id),
2067 p_message => 'Error creating incoming layer',
2068 p_error_type => 'E');
2069 FOR i IN 1..fnd_msg_pub.count_msg LOOP
2070 GMF_LAYERS.log_message (
2071 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2072 p_procedure_name => 'None',
2073 p_parameters => 'Trans ID = '||to_char(trans_rec.transaction_id),
2074 p_message => fnd_msg_pub.get_detail (i, NULL),
2075 p_error_type => 'E');
2076 END LOOP;
2077 ELSE
2078 l_il_count := l_il_count + 1;
2079 END IF;
2080 END IF;
2081 END LOOP;
2082 EXCEPTION
2083 WHEN e_uom_conv_failure THEN
2084 NULL;
2085 END;
2086 l_prev_mat_det_id := t.mat_det_id; -- B 15888374
2087 END LOOP;
2088
2089 GMF_LAYERS.log_message (
2090 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2091 p_procedure_name => 'None',
2092 p_parameters => 'Incoming Layers = '||to_char(l_il_count)||
2093 ', Outgoing Layers = '||to_char(l_ol_count)||
2094 ', Resource Layers = '||to_char(l_rl_count),
2095 p_message => 'Completed the migration to Recreate_incoming_layers for the batch',
2096 p_error_type => 'I');
2097
2098 END Recreate_incoming_layers;
2099
2100 /*
2101 --+==========================================================================+
2102 --| PROCEDURE NAME |
2103 --| Finalize_batch |
2104 --| |
2105 --| TYPE |
2106 --| Public |
2107 --| |
2108 --| USAGE |
2109 --| Finalize_batch |
2110 --| |
2111 --| DESCRIPTION |
2112 --| |
2113 --| PARAMETERS |
2114 --| |
2115 --| RETURNS |
2116 --| None |
2117 --| |
2118 --| HISTORY |
2119 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
2120 --+==========================================================================+
2121 */
2122
2123 PROCEDURE Finalize_batch (p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
2124
2125 l_batch_id NUMBER := p_batch_id; -- Replace this batch-id with proper batch-id.
2126 x_return_status VARCHAR2(10);
2127 x_msg_count NUMBER;
2128 i NUMBER;
2129 x_msg_data VARCHAR2(1000);
2130 l_start_date DATE ;
2131 l_end_date DATE ;
2132
2133 CURSOR c_mig_closed_batches IS
2134 SELECT batch_id
2135 FROM gme_batch_header b
2136 WHERE b.batch_status = 4
2137 AND b.batch_id = l_batch_id
2138 AND b.batch_close_date >= l_start_date
2139 AND b.batch_close_date <= l_end_date;
2140 BEGIN
2141
2142 select start_date, end_date INTO l_start_date, l_end_date
2143 from gmf_period_statuses
2144 where period_id = p_period_id;
2145
2146 FOR cls IN c_mig_closed_batches LOOP
2147 BEGIN
2148 x_msg_count := 0;
2149 x_return_status := 0;
2150
2151 GMF_VIB.Finalize_VIB_Details(
2152 1.0,
2153 FND_API.G_FALSE,
2154 cls.batch_id,
2155 x_return_status,
2156 x_msg_count,
2157 x_msg_data);
2158
2159 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2160 GMF_LAYERS.log_message (
2161 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2162 p_procedure_name => 'None',
2163 p_parameters => 'Batch ID = '||to_char(cls.batch_id),
2164 p_message => 'Error creating finalization layers',
2165 p_error_type => 'E');
2166
2167 FOR i IN 1..fnd_msg_pub.count_msg LOOP
2168 GMF_LAYERS.log_message (
2169 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2170 p_procedure_name => 'None',
2171 p_parameters => 'Batch ID = '||to_char(cls.batch_id),
2172 p_message => fnd_msg_pub.get_detail (i, NULL),
2173 p_error_type => 'E');
2174 END LOOP;
2175 END IF;
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178 NULL;
2179 END;
2180 END LOOP;
2181 GMF_LAYERS.log_message (
2182 p_table_name => 'GMF_BATCH_VIB_DETAILS',
2183 p_procedure_name => 'None',
2184 p_parameters => 'Batch ID = '||to_char(p_batch_id),
2185 p_message => 'Layer Finalization completed',
2186 p_error_type => 'E');
2187
2188 END Finalize_batch;
2189
2190 /*
2191 --+==========================================================================+
2192 --| PROCEDURE NAME |
2193 --| Relayer |
2194 --| |
2195 --| TYPE |
2196 --| Public |
2197 --| |
2198 --| USAGE |
2199 --| Relayer |
2200 --| |
2201 --| DESCRIPTION |
2202 --| |
2203 --| PARAMETERS |
2204 --| |
2205 --| RETURNS |
2206 --| None |
2207 --| |
2208 --| HISTORY |
2209 --| Parag Kanetkar Bug 8523022 30-OCT-2009 Added Prodedure |
2210 --+==========================================================================+
2211 */
2212
2213 PROCEDURE Relayer( errbuf OUT NOCOPY VARCHAR2,
2214 retcode OUT NOCOPY VARCHAR2,
2215 p_legal_entity_id IN NUMBER,
2216 p_calendar_code IN VARCHAR2,
2217 p_period_code IN VARCHAR2,
2218 p_cost_type_id IN NUMBER,
2219 p_org_id IN NUMBER DEFAULT NULL,
2220 p_batch_id IN NUMBER DEFAULT NULL) IS
2221
2222 l_legal_entity_id NUMBER := p_legal_entity_id;
2223 l_period varchar2(10) := p_period_code;
2224 l_calendar varchar2(10) := p_calendar_code;
2225 l_cost_type NUMBER := p_cost_type_id;
2226 l_org_id NUMBER := p_org_id;
2227 l_batch_id NUMBER := p_batch_id;
2228 l_periodid Number;
2229 l_batchstatus NUMBER;
2230 l_startdate DATE;
2231 l_enddate DATE;
2232 l_count NUMBER;
2233 l_type NUMBER;
2234 l_posted_cnt NUMBER;
2235 l_ret_status BOOLEAN;
2236 l_debug_level NUMBER;
2237
2238 TYPE batch_rec_type IS RECORD
2239 ( batch_id NUMBER
2240 , batch_no VARCHAR2(32)
2241 , batch_status NUMBER
2242 , actual_start_date DATE
2243 , batch_close_date DATE
2244 );
2245
2246 TYPE batch_cursor_type IS REF CURSOR RETURN batch_rec_type;
2247
2248 rec batch_rec_type;
2249 cur_batches batch_cursor_type;
2250
2251 /* Bug 9417673 - GMF_LAYERS PACKAGE FAILS WITH ORA-1722 */
2252 CURSOR cur_get_periodid IS
2253 SELECT gps.period_id,
2254 gps.start_date,
2255 gps.end_date
2256 FROM gmf_period_statuses gps
2257 WHERE gps.cost_type_id = l_cost_type
2258 AND gps.calendar_code = l_calendar
2259 AND gps.period_code = l_period
2260 AND gps.legal_entity_id = l_legal_entity_id;
2261
2262 CURSOR Cur_cost_type IS
2263 SELECT cmm.cost_type
2264 FROM gmf_fiscal_policies gfp, cm_mthd_mst cmm
2265 WHERE gfp.cost_type_id = cmm.cost_type_id
2266 AND gfp.legal_entity_id = l_legal_entity_id
2267 AND cmm.cost_type <> 6
2268 UNION
2269 SELECT cmm2.cost_type
2270 FROM gmf_fiscal_policies gfp, cm_mthd_mst cmm1,cm_mthd_mst cmm2
2271 WHERE gfp.cost_type_id = cmm1.cost_type_id
2272 AND gfp.legal_entity_id = l_legal_entity_id
2273 AND cmm1.cost_type = 6
2274 AND cmm1.default_lot_cost_type_id = cmm2.cost_type_id;
2275
2276 /* Bug 13442362 13367279 additional change Not for this bug. We do not need to care about
2277 staging transfers and subinventory transfers for this validation as costing does not cost these */
2278
2279 CURSOR Cur_OPM_costed IS
2280 select Count(*) from mtl_material_transactions
2281 where transaction_date >= l_startdate
2282 and transaction_date <= l_enddate
2283 and transaction_action_id NOT IN (2, 28)
2284 and opm_costed_flag IS NULL
2285 and organization_id IN (SELECT organization_id
2286 FROM gmf_organization_definitions
2287 WHERE legal_entity_id = l_legal_entity_id);
2288
2289
2290
2291 BEGIN
2292
2293 fnd_file.put_line(fnd_file.log, ' ========================================================' );
2294 fnd_file.put_line(fnd_file.log, ' Starting Migration of layer data at '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2295 fnd_file.put_line(fnd_file.log, 'Request Parameters le id '||l_legal_entity_id||' Cost Calendar '||l_calendar||' Period '||
2296 l_period||' CostTypId ' ||l_cost_type||' Batch Org id '||l_org_id||' Batch id'||l_batch_id);
2297 fnd_file.put_line(fnd_file.log, ' ========================================================' );
2298
2299 l_debug_level := TO_NUMBER(FND_PROFILE.VALUE( 'GMF_CONC_DEBUG' )); -- B 10196137
2300
2301 OPEN cur_get_periodid;
2302 FETCH cur_get_periodid INTO l_periodid, l_startdate, l_enddate;
2303 CLOSE cur_get_periodid;
2304
2305 fnd_file.put_line(fnd_file.log, 'Period ID '||l_periodid||' Period Start Date '||to_char(l_startdate,'DD-MON-YYYY HH24:MI:SS')
2306 ||' Period End Date '||to_char(l_enddate,'DD-MON-YYYY HH24:MI:SS'));
2307
2308 IF l_periodid IS NULL THEN
2309 fnd_file.put_line(fnd_file.log, 'Period not found ');
2310 fnd_file.put_line
2311 (fnd_file.log,'Can not Continue. Returning without migrating data' );
2312
2313 l_ret_status := fnd_concurrent.set_completion_status('ERROR','Error Period Not Found.');
2314 RETURN;
2315 END IF;
2316
2317 /* write period validation logic here. If period passed in is already accounted then do nothing
2318 and return
2319
2320 Validation logic
2321 If for legal entity, and calendar , period passed in if
2322 a) fiscal policy cost method is actual costing OR
2323 fiscal policy cost method is lot actual costing with alternate cost method of actual costing
2324
2325 AND
2326
2327 b) if any mmt record for organizations belonging to le for date range of entire period has OPM costed flag as
2328 NULL
2329
2330 If a) AND b) is TRUE do nothing and Return.
2331
2332 */
2333
2334 -- Period Validation Logic Begins here
2335 OPEN Cur_cost_type;
2336 FETCH Cur_cost_type INTO l_type;
2337 CLOSE Cur_cost_type;
2338
2339 fnd_file.put_line
2340 (fnd_file.log,'Fiscal policy Cost Type (Alternate for Lot Costing) Actual=1 Standard=0 Is '||l_type );
2341
2342 IF (l_type = 1) THEN
2343
2344 OPEN Cur_OPM_costed;
2345 FETCH Cur_OPM_costed INTO l_posted_cnt;
2346 CLOSE Cur_OPM_costed;
2347
2348 fnd_file.put_line
2349 (fnd_file.log,'Posted Transaction Count '||l_posted_cnt );
2350
2351 IF l_posted_cnt > 0 THEN
2352
2353 fnd_file.put_line
2354 (fnd_file.log,'Cost type Or Default Lot cost type is Actual Costing and '||l_posted_cnt||' Transactions are already posted.' );
2355 fnd_file.put_line
2356 (fnd_file.log,'Can not Continue. Returning without migrating data' );
2357
2358 l_ret_status := fnd_concurrent.set_completion_status('ERROR','Errors found during processing.'||
2359 ' Please check the log file for details.');
2360
2361 RETURN;
2362
2363 END IF;
2364
2365 END IF;
2366
2367
2368 /* Now handle cases Based on parameters passed.
2369 a) If l_batch_id is not null then Open batch cursor for just batch_id and period passed in.
2370 b) If l_org_id is passed in and l_batch_id is NULL then Open batch cursor for all batches in org
2371 for the period.
2372 c) If l_org_id is NULL then Open batch cursor for all orgs of that legal entity for period.
2373
2374 */
2375
2376 IF l_batch_id IS NOT NULL THEN
2377
2378 fnd_file.put_line(fnd_file.log, 'Opening batch_id cursor case 1 for batch_id '||l_batch_id);
2379
2380 OPEN cur_batches FOR
2381 SELECT batch_id, batch_no, batch_status , asd, batch_close_date
2382 FROM (
2383 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2384 h.batch_close_date, h.organization_id
2385 FROM gme_batch_header h,
2386 mtl_material_transactions t
2387 WHERE h.batch_status in (2,3,4) -- B9441550
2388 AND h.actual_start_date <= l_enddate
2389 AND h.batch_id = t.transaction_source_id
2390 AND t.transaction_source_type_id = 5
2391 AND t.transaction_date >= l_startdate
2392 AND t.transaction_date <= l_enddate
2393 UNION
2394 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2395 h.batch_close_date, h.organization_id
2396 FROM gme_batch_header h,
2397 gme_resource_txns r
2398 WHERE h.batch_status in (2,3,4) -- B9441550
2399 AND h.actual_start_date <= l_enddate
2400 AND h.batch_id = r.doc_id
2401 AND r.trans_date >= l_startdate
2402 AND r.trans_date <= l_enddate
2403 AND r.completed_ind = 1
2404 AND r.delete_mark = 0
2405 UNION
2406 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2407 h.batch_close_date, h.organization_id
2408 FROM gme_batch_header h
2409 WHERE h.batch_status = 4
2410 AND h.actual_start_date <= l_enddate
2411 AND h.batch_close_date >= l_startdate
2412 AND h.batch_close_date <= l_enddate
2413 UNION -- B9441550
2414 SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
2415 h.batch_close_date, h.organization_id
2416 FROM gme_batch_header h
2417 WHERE h.batch_status = -1
2418 AND h.batch_id = l_batch_id
2419 ) batches
2420 WHERE batches.batch_id = l_batch_id
2421 AND batches.organization_id = l_org_id
2422 AND batches.organization_id IN
2423 (SELECT organization_id
2424 FROM gmf_organization_definitions
2425 WHERE legal_entity_id = l_legal_entity_id);
2426
2427
2428 ELSIF (l_batch_id IS NULL AND l_org_id IS NOT NULL) THEN
2429
2430 fnd_file.put_line(fnd_file.log, 'Opening org_id cursor case 2 for org_id '||l_org_id);
2431
2432 OPEN cur_batches FOR
2433 SELECT batch_id, batch_no, batch_status , asd, batch_close_date
2434 FROM (
2435 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2436 h.batch_close_date, h.organization_id
2437 FROM gme_batch_header h,
2438 mtl_material_transactions t
2439 WHERE h.batch_status in (2,3,4) -- B9441550
2440 AND h.actual_start_date <= l_enddate
2441 AND h.batch_id = t.transaction_source_id
2442 AND t.transaction_source_type_id = 5
2443 AND t.transaction_date >= l_startdate
2444 AND t.transaction_date <= l_enddate
2445 UNION
2446 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2447 h.batch_close_date, h.organization_id
2448 FROM gme_batch_header h,
2449 gme_resource_txns r
2450 WHERE h.batch_status in (2,3,4) -- B9441550
2451 AND h.actual_start_date <= l_enddate
2452 AND h.batch_id = r.doc_id
2453 AND r.trans_date >= l_startdate
2454 AND r.trans_date <= l_enddate
2455 AND r.completed_ind = 1
2456 AND r.delete_mark = 0
2457 UNION
2458 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2459 h.batch_close_date, h.organization_id
2460 FROM gme_batch_header h
2461 WHERE h.batch_status = 4
2462 AND h.actual_start_date <= l_enddate
2463 AND h.batch_close_date >= l_startdate
2464 AND h.batch_close_date <= l_enddate
2465 UNION -- B9441550
2466 SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
2467 h.batch_close_date, h.organization_id
2468 FROM gme_batch_header h
2469 WHERE h.batch_status = -1
2470 AND h.organization_id = l_org_id
2471 AND h.plan_start_date <= l_enddate
2472 AND h.plan_start_date >= l_startdate
2473 ) batches
2474 WHERE batches.organization_id = l_org_id
2475 AND batches.organization_id IN
2476 (SELECT organization_id
2477 FROM gmf_organization_definitions
2478 WHERE legal_entity_id = l_legal_entity_id);
2479
2480 ELSIF (l_batch_id IS NULL AND l_org_id IS NULL) THEN
2481
2482 fnd_file.put_line(fnd_file.log, 'Opening period cursor case 3 for start date '||to_char(l_startdate,'DD-MON-RRRR HH24:MI:SS')||' end date '||
2483 to_char(l_enddate,'DD-MON-RRRR HH24:MI:SS'));
2484
2485 OPEN cur_batches FOR
2486 SELECT batch_id, batch_no, batch_status , asd, batch_close_date
2487 FROM (
2488 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2489 h.batch_close_date, h.organization_id
2490 FROM gme_batch_header h,
2491 mtl_material_transactions t
2492 WHERE h.batch_status in (2,3,4) -- B9441550
2493 AND h.actual_start_date <= l_enddate
2494 AND h.batch_id = t.transaction_source_id
2495 AND t.transaction_source_type_id = 5
2496 AND t.transaction_date >= l_startdate
2497 AND t.transaction_date <= l_enddate
2498 UNION
2499 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2500 h.batch_close_date, h.organization_id
2501 FROM gme_batch_header h,
2502 gme_resource_txns r
2503 WHERE h.batch_status in (2,3,4) -- B9441550
2504 AND h.actual_start_date <= l_enddate
2505 AND h.batch_id = r.doc_id
2506 AND r.trans_date >= l_startdate
2507 AND r.trans_date <= l_enddate
2508 AND r.completed_ind = 1
2509 AND r.delete_mark = 0
2510 UNION
2511 SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
2512 h.batch_close_date, h.organization_id
2513 FROM gme_batch_header h
2514 WHERE h.batch_status = 4
2515 AND h.actual_start_date <= l_enddate
2516 AND h.batch_close_date >= l_startdate
2517 AND h.batch_close_date <= l_enddate
2518 UNION -- B9441550
2519 SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
2520 h.batch_close_date, h.organization_id
2521 FROM gme_batch_header h
2522 WHERE h.batch_status = -1
2523 AND h.plan_start_date <= l_enddate
2524 AND h.plan_start_date >= l_startdate
2525 ) batches
2526 WHERE batches.organization_id IN
2527 (SELECT organization_id
2528 FROM gmf_organization_definitions
2529 WHERE legal_entity_id = l_legal_entity_id);
2530
2531 END IF;
2532
2533
2534 FETCH cur_batches INTO rec;
2535 WHILE cur_batches%FOUND LOOP
2536
2537
2538 fnd_file.put_line(fnd_file.log, 'In Loop Migrating layer data for Batch Number '||rec.batch_no);
2539
2540 IF ((rec.actual_start_date >= l_startdate) AND (rec.actual_start_date <= l_enddate)) THEN
2541
2542 fnd_file.put_line(fnd_file.log, ' Deleting Old layers for Batch_id '|| rec.batch_id||' Batch No '||rec.batch_no);
2543 GMF_LAYERS.Delete_old_layers(rec.batch_id);
2544
2545 ELSE
2546
2547 fnd_file.put_line(fnd_file.log, ' Deleting Period layers for Batch_id '|| rec.batch_id||' Batch No '||rec.batch_no||' Period id '||l_periodid);
2548 GMF_LAYERS.Delete_period_layers(rec.batch_id, l_periodid);
2549
2550 END IF;
2551
2552 fnd_file.put_line(fnd_file.log, ' END Deleting Old layers for Batch_id '|| rec.batch_id||' Batch No '||rec.batch_no||' Period id '||l_periodid);
2553 -- Moved Declaration to top
2554 fnd_file.put_line(fnd_file.log, ' Rebuilding outgoing layers data for Batch_id '|| rec.batch_id||' Period id '||l_periodid);
2555
2556 GMF_LAYERS.Recreate_outgoing_layers(rec.batch_id,l_periodid);
2557
2558 fnd_file.put_line(fnd_file.log, ' Rebuilding Resource data for Batch_id '|| rec.batch_id||' Period id '||l_periodid);
2559
2560 GMF_LAYERS.Recreate_resource_layers(rec.batch_id,l_periodid);
2561
2562 fnd_file.put_line(fnd_file.log, ' Rebuilding incoming layers data for Batch_id '|| rec.batch_id||' Period id '||l_periodid);
2563
2564 GMF_LAYERS.Recreate_incoming_layers(rec.batch_id,l_periodid);
2565
2566 -- Or call if r=batch close date i in period being passed?
2567 IF ((rec.batch_status = 4) AND (rec.batch_close_date >= l_startdate) AND (rec.batch_close_date <= l_enddate)) THEN
2568
2569 fnd_file.put_line(fnd_file.log, ' Finalizing layers data for Close Batch_id '|| rec.batch_id);
2570
2571 GMF_LAYERS.Finalize_batch (rec.batch_id,l_periodid);
2572
2573 END IF;
2574
2575 -- Bug 10196137 Perform these queries and log messages only if debug level is 3.
2576
2577 IF l_debug_level > 2 THEN
2578
2579 fnd_file.put_line(fnd_file.log, ' After rebuild record count for Batch_id '|| rec.batch_id||' Batch No '||rec.batch_no);
2580
2581 SELECT count(*) INTO l_count
2582 FROM gmf_incoming_material_layers il
2583 WHERE il.PSEUDO_LAYER_ID IS NOT NULL
2584 AND EXISTS
2585 (SELECT 1
2586 FROM gme_batch_header h, mtl_material_transactions t,
2587 gmf_incoming_material_layers im
2588 WHERE h.batch_id = rec.batch_id
2589 AND h.batch_id = t.transaction_source_id
2590 AND t.transaction_source_type_id = 5
2591 AND im.mmt_transaction_id = t.transaction_id
2592 AND im.mmt_organization_id = t.organization_id
2593 AND im.layer_id = il.PSEUDO_LAYER_ID
2594 );
2595 fnd_file.put_line(fnd_file.log, ' Psuedo Incoming layers count = '|| l_count);
2596 SELECT count(*) INTO l_count
2597 FROM gmf_incoming_material_layers il
2598 WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
2599 (SELECT DISTINCT t.organization_id, t.transaction_id
2600 FROM gme_batch_header h, mtl_material_transactions t
2601 WHERE h.batch_id = rec.batch_id
2602 AND h.batch_id = t.transaction_source_id
2603 AND t.transaction_source_type_id = 5
2604 );
2605 fnd_file.put_line(fnd_file.log, ' Incoming layers count = '|| l_count);
2606 SELECT count(*) INTO l_count
2607 FROM gmf_outgoing_material_layers ol
2608 WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
2609 (SELECT DISTINCT t.organization_id, t.transaction_id
2610 FROM gme_batch_header h, mtl_material_transactions t
2611 WHERE h.batch_id = rec.batch_id
2612 AND h.batch_id = t.transaction_source_id
2613 AND t.transaction_source_type_id = 5
2614 );
2615 fnd_file.put_line(fnd_file.log, ' Outgoing layers count = '|| l_count);
2616 SELECT count(*) INTO l_count
2617 FROM gmf_batch_vib_details bvd
2618 WHERE bvd.requirement_id IN
2619 (SELECT br.requirement_id
2620 FROM gmf_batch_requirements br, gme_batch_header h
2621 WHERE h.batch_id = rec.batch_id
2622 AND h.batch_id = br.batch_id
2623 );
2624 fnd_file.put_line(fnd_file.log, ' Batch Vib details count = '|| l_count);
2625 SELECT count(*) INTO l_count
2626 FROM gmf_batch_requirements br
2627 WHERE br.batch_id IN
2628 (SELECT batch_id
2629 FROM gme_batch_header
2630 WHERE batch_id = rec.batch_id
2631 );
2632 fnd_file.put_line(fnd_file.log, ' Batch requirements count = '|| l_count);
2633
2634 END IF; -- Bug 10196137 Perform these queries and log messages only if debug level is 3
2635
2636 fnd_file.put_line(fnd_file.log, 'END rebuilding layers for Batch No = '|| rec.batch_no);
2637
2638 COMMIT;
2639 FETCH cur_batches INTO rec;
2640
2641 END LOOP;
2642
2643 fnd_file.put_line
2644 (fnd_file.log,'Layer Migration finished at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2645
2646 l_ret_status := fnd_concurrent.set_completion_status('NORMAL','Process completed successfully.');
2647
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 fnd_file.put_line(fnd_file.log, ' *****************************************************' );
2652 fnd_file.put_line(fnd_file.log, ' Error Running script for Batch_id '|| rec.batch_id);
2653 fnd_file.put_line(fnd_file.log, ' Please check table gme_temp_exceptions for details' );
2654 fnd_file.put_line(fnd_file.log, ' *****************************************************' );
2655 fnd_file.put_line(fnd_file.log,'ERROR: '||substr(sqlerrm,1,100) || ' While Remigrating layer data.');
2656 l_ret_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' While Remigrating layer data.' );
2657
2658 ROLLBACK;
2659
2660
2661 END Relayer;
2662
2663 -- END Additions for relayering concurrent request.
2664
2665 END GMF_LAYERS;