1 PACKAGE BODY GMD_SPREADSHEET_UPDATE AS
2 /* $Header: GMDSPUPB.pls 120.11 2006/09/19 15:10:12 kmotupal noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_SPREADSHEET_UPDATE';
4
5 /*##############################################################
6 # NAME
7 # lock_formula_hdr
8 # SYNOPSIS
9 # proc lock_formula_hdr
10 # DESCRIPTION
11 # This procedure is used to lock the formula header.
12 ###############################################################*/
13
14 PROCEDURE lock_formula_hdr (p_formula_id IN NUMBER, p_last_update_date IN DATE, X_return_status OUT NOCOPY VARCHAR2) IS
15 CURSOR Cur_lock_header IS
16 SELECT last_update_date
17 FROM fm_form_mst
18 WHERE formula_id = P_formula_id
19 FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
20 X_last_update_date DATE;
21 BEGIN
22 X_return_status := FND_API.g_ret_sts_success;
23 OPEN Cur_lock_header;
24 FETCH Cur_lock_header INTO X_last_update_date;
25 CLOSE Cur_lock_header;
26 IF X_last_update_date <> P_last_update_date THEN
27 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_CHANGED');
28 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'FM_FORM_MST');
29 FND_MSG_PUB.ADD;
30 X_return_status := FND_API.g_ret_sts_error;
31 END IF;
32 EXCEPTION
33 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
34 IF Cur_lock_header%ISOPEN THEN
35 CLOSE Cur_lock_header;
36 END IF;
37 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_LOCK');
38 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'FM_FORM_MST');
39 FND_MESSAGE.SET_TOKEN('RECORD', 'FORMULA_ID');
40 FND_MESSAGE.SET_TOKEN('KEY', P_formula_id);
41 FND_MSG_PUB.ADD;
42 X_return_status := FND_API.g_ret_sts_error;
43 WHEN OTHERS THEN
44 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Formula_Hdr');
45 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
46 END lock_formula_hdr;
47
48 /*##############################################################
49 # NAME
50 # lock_formula_dtl
51 # SYNOPSIS
52 # proc lock_formula_dtl
53 # DESCRIPTION
54 # This procedure is used to lock the formula details.
55 ###############################################################*/
56
57 PROCEDURE lock_formula_dtl (P_formulaline_id IN NUMBER, P_last_update_date IN DATE,
58 X_return_status OUT NOCOPY VARCHAR2) IS
59 CURSOR Cur_lock_details IS
60 SELECT last_update_date
61 FROM fm_matl_dtl
62 WHERE formulaline_id = P_formulaline_id
63 FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
64 X_last_update_date DATE;
65 BEGIN
66 X_return_status := FND_API.g_ret_sts_success;
67 OPEN Cur_lock_details;
68 FETCH Cur_lock_details INTO X_last_update_date;
69 CLOSE Cur_lock_details;
70 IF X_last_update_date <> P_last_update_date THEN
71 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_CHANGED');
72 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'FM_MATL_DTL');
73 FND_MSG_PUB.ADD;
74 X_return_status := FND_API.g_ret_sts_error;
75 END IF;
76 EXCEPTION
77 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
78 IF Cur_lock_details%ISOPEN THEN
79 CLOSE Cur_lock_details;
80 END IF;
81 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECORD_LOCK');
82 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'FM_MATL_DTL');
83 FND_MESSAGE.SET_TOKEN('RECORD', 'FORMULALINE_ID');
84 FND_MESSAGE.SET_TOKEN('KEY', P_formulaline_id);
85 FND_MSG_PUB.ADD;
86 X_return_status := FND_API.g_ret_sts_error;
87 WHEN OTHERS THEN
88 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Formula_Dtl');
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
90 END lock_formula_dtl;
91
92 /*##############################################################
93 # NAME
94 # lock_formula_record
95 # SYNOPSIS
96 # proc lock_formula_record
97 # DESCRIPTION
98 # This procedure is used to lock the formula.
99 ###############################################################*/
100
101 PROCEDURE lock_formula_record (P_formula_id IN NUMBER,X_return_status OUT NOCOPY VARCHAR2) IS
102 CURSOR Cur_get_hdr IS
103 SELECT last_update_date
104 FROM gmd_material_header_gtmp
105 WHERE formula_id = P_formula_id;
106
107 CURSOR Cur_get_dtl IS
108 SELECT formulaline_id, last_update_date
109 FROM gmd_material_details_gtmp
110 WHERE line_type <> 3
111 ORDER BY line_type, line_no;
112 l_last_update_date DATE;
113 l_return_status VARCHAR2(10);
114 error_lock EXCEPTION;
115 BEGIN
116 X_return_status := FND_API.g_ret_sts_success;
117 SAVEPOINT lock_formula_record;
118 OPEN Cur_get_hdr;
119 FETCH Cur_get_hdr INTO l_last_update_date;
120 CLOSE Cur_get_hdr;
121 lock_formula_hdr(P_formula_id => P_formula_id
122 ,P_last_update_date => l_last_update_date
123 ,X_return_status => l_return_status);
124 IF l_return_status <> x_return_status THEN
125 RAISE error_lock;
126 END IF;
127 FOR l_rec IN Cur_get_dtl LOOP
128 IF l_rec.formulaline_id IS NOT NULL THEN
129 lock_formula_dtl (P_formulaline_id => l_rec.formulaline_id
130 ,P_last_update_date => l_rec.last_update_date
131 ,X_return_status => l_return_status);
132 IF l_return_status <> x_return_status THEN
133 RAISE error_lock;
134 END IF;
135 END IF;
136 END LOOP;
137 EXCEPTION
138 WHEN error_lock THEN
139 X_return_status := l_return_status;
140 ROLLBACK TO SAVEPOINT lock_formula_record;
141 WHEN OTHERS THEN
142 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_formula_Record');
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 ROLLBACK TO SAVEPOINT lock_formula_record;
145 END lock_formula_record;
146
147 /*##############################################################
148 # NAME
149 # lock_batch_hdr
150 # SYNOPSIS
151 # proc lock_batch_hdr
152 # DESCRIPTION
153 # This procedure is used to lock the batch header.
154 ###############################################################*/
155
156 PROCEDURE lock_batch_hdr (P_batch_id IN NUMBER, P_last_update_date IN DATE, X_return_status OUT NOCOPY VARCHAR2) IS
157 CURSOR Cur_lock_header IS
158 SELECT last_update_date
159 FROM gme_batch_header
160 WHERE batch_id = P_batch_id
161 FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
162 X_last_update_date DATE;
163 BEGIN
164 X_return_status := FND_API.g_ret_sts_success;
165 OPEN Cur_lock_header;
166 FETCH Cur_lock_header INTO X_last_update_date;
167 CLOSE Cur_lock_header;
168 IF X_last_update_date <> P_last_update_date THEN
169 FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_CHANGED');
170 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'GME_BATCH_HEADER');
171 FND_MSG_PUB.ADD;
172 X_return_status := FND_API.g_ret_sts_error;
173 END IF;
174 EXCEPTION
175 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
176 IF Cur_lock_header%ISOPEN THEN
177 CLOSE Cur_lock_header;
178 END IF;
179 FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_LOCKED');
180 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'GME_BATCH_HEADER');
181 FND_MESSAGE.SET_TOKEN('RECORD', 'BATCH_ID');
182 FND_MESSAGE.SET_TOKEN('KEY', P_batch_id);
183 FND_MSG_PUB.ADD;
184 X_return_status := FND_API.g_ret_sts_error;
185 WHEN OTHERS THEN
186 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Hdr');
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 END lock_batch_hdr;
189
190 /*##############################################################
191 # NAME
192 # lock_batch_dtl
193 # SYNOPSIS
194 # proc lock_batch_dtl
195 # DESCRIPTION
196 # This procedure is used to lock the batch details.
197 ###############################################################*/
198
199 PROCEDURE lock_batch_dtl (P_material_detail_id IN NUMBER, P_last_update_date IN DATE,
200 X_return_status OUT NOCOPY VARCHAR2) IS
201 CURSOR Cur_lock_details IS
202 SELECT last_update_date
203 FROM gme_material_details
204 WHERE material_detail_id = P_material_detail_id
205 FOR UPDATE OF LAST_UPDATE_DATE NOWAIT;
206 X_last_update_date DATE;
207 BEGIN
208 X_return_status := FND_API.g_ret_sts_success;
209 OPEN Cur_lock_details;
210 FETCH Cur_lock_details INTO X_last_update_date;
211 CLOSE Cur_lock_details;
212
213 IF X_last_update_date <> P_last_update_date THEN
214 FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_CHANGED');
215 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'GME_MATERIAL_DETAILS');
216 FND_MSG_PUB.ADD;
217 X_return_status := FND_API.g_ret_sts_error;
218 END IF;
219 EXCEPTION
220 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
221 IF Cur_lock_details%ISOPEN THEN
222 CLOSE Cur_lock_details;
223 END IF;
224 FND_MESSAGE.SET_NAME('GME', 'GME_RECORD_LOCKED');
225 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'GME_MATERIAL_DETAILS');
226 FND_MESSAGE.SET_TOKEN('RECORD', 'MATERIAL_DETAIL_ID');
227 FND_MESSAGE.SET_TOKEN('KEY', P_material_detail_id);
228 FND_MSG_PUB.ADD;
229 X_return_status := FND_API.g_ret_sts_error;
230 WHEN OTHERS THEN
231 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Dtl');
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 END lock_batch_dtl;
234
235 /*##############################################################
236 # NAME
237 # lock_batch_record
238 # SYNOPSIS
239 # proc lock_batch_record
240 # DESCRIPTION
241 # This procedure is used to lock the batch.
242 ###############################################################*/
243
244 PROCEDURE lock_batch_record (P_batch_id IN NUMBER,
245 X_return_status OUT NOCOPY VARCHAR2) IS
246 CURSOR Cur_get_hdr IS
247 SELECT last_update_date
248 FROM gmd_material_header_gtmp
249 WHERE batch_id = P_batch_id;
250
251 CURSOR Cur_get_dtl IS
252 SELECT material_detail_id, last_update_date
253 FROM gmd_material_details_gtmp
254 WHERE line_type <> 3
255 ORDER BY line_type, line_no;
256 l_last_update_date DATE;
257 l_return_status VARCHAR2(10);
258 error_lock EXCEPTION;
259 BEGIN
260 X_return_status := FND_API.g_ret_sts_success;
261 SAVEPOINT lock_batch_record;
262 OPEN Cur_get_hdr;
263 FETCH Cur_get_hdr INTO l_last_update_date;
264 CLOSE Cur_get_hdr;
265 lock_batch_hdr(P_batch_id => P_batch_id
266 ,P_last_update_date => l_last_update_date
267 ,X_return_status => l_return_status);
268 IF l_return_status <> x_return_status THEN
269 RAISE error_lock;
270 END IF;
271 FOR l_rec IN Cur_get_dtl LOOP
272 IF l_rec.material_detail_id IS NOT NULL THEN
273 lock_batch_dtl (P_material_detail_id => l_rec.material_detail_id
274 ,P_last_update_date => l_rec.last_update_date
275 ,X_return_status => l_return_status);
276 IF l_return_status <> x_return_status THEN
277 RAISE error_lock;
278 END IF;
279 END IF;
280 END LOOP;
281 EXCEPTION
282 WHEN error_lock THEN
283 X_return_status := l_return_status;
284 ROLLBACK TO SAVEPOINT lock_batch_record;
285 WHEN OTHERS THEN
286 fnd_msg_pub.add_exc_msg ('GMD_SPREADSHEET_UPDATE', 'Lock_Batch_Record');
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 ROLLBACK TO SAVEPOINT lock_batch_record;
289 END lock_batch_record;
290
291
292 /*##############################################################
293 # NAME
294 # update_batch
295 # SYNOPSIS
296 # proc update_batch
297 # DESCRIPTION
298 # This procedure is used to update the batch.
299 # HISTORY
300 # 22-AUG-06 Kapil M Bug# 3927768
301 # Changed the IF condition to update lot for the items.
302 # 19-SEP-06 Kapil M Bug# 3927768
303 # Modified in deletion, updation and creation go product_pending_lots
304 ###############################################################*/
305
306 PROCEDURE update_batch (P_batch_id IN NUMBER, X_return_status OUT NOCOPY VARCHAR2) IS
307
308 CURSOR Cur_get_batch IS
309 SELECT *
310 FROM gme_batch_header
311 WHERE batch_id = P_batch_id;
312
313 CURSOR Cur_get_del_material IS
314 SELECT material_detail_id
315 FROM gme_material_details e
316 WHERE batch_id = P_batch_id
317 AND line_type <> 1
318 AND NOT EXISTS (SELECT 1
319 FROM gmd_material_details_gtmp g
320 WHERE line_type <> 3
321 AND g.material_detail_id = e.material_detail_id);
322
323 CURSOR Cur_get_material IS
324 SELECT *
325 FROM gmd_material_details_gtmp
326 WHERE line_type <> 3
327 ORDER BY line_type, line_no;
328
329 CURSOR Cur_get_detail_id(V_material_detail_id NUMBER) IS
330 SELECT 1
331 FROM gme_material_details
332 WHERE batch_id = P_batch_id
333 AND material_detail_id = V_material_detail_id;
334
335 -- Bug# 3927768 Kapil M
336 -- Changed the below three cursors
337 CURSOR Cur_get_lablot (V_material_detail_id NUMBER ) IS
338 SELECT *
339 FROM gmd_material_details_gtmp
340 WHERE line_type = 3
341 AND material_detail_id = V_material_detail_id;
342
343 -- Bug# 3927768 Kapil M
344 CURSOR Cur_get_lab_material (p_lot_id NUMBER) IS
345 SELECT quantity
346 FROM gme_pending_product_lots
347 WHERE batch_id = P_batch_id
348 AND pending_product_lot_id = p_lot_id;
349
350 -- Bug# 3927768 Kapil M
351 CURSOR Cur_get_del_lines (V_material_detail_id NUMBER) IS
352 SELECT material_detail_id, pending_product_lot_id
353 FROM gme_pending_product_lots e
354 WHERE batch_id = P_batch_id
355 AND material_detail_id = V_material_detail_id
356 AND NOT EXISTS (SELECT 1
357 FROM gmd_material_details_gtmp g
358 WHERE parent_line_id = V_material_detail_id
359 AND g.trans_id = e.pending_product_lot_id
360 AND line_type = 3);
361
362 CURSOR Cur_get_product_lot (V_material_detail_id NUMBER) IS
363 SELECT pending_product_lot_id
364 FROM gme_pending_product_lots
365 WHERE batch_id = P_batch_id
366 AND material_detail_id = V_material_detail_id;
367
368 CURSOR Cur_get_text IS
369 SELECT material_detail_id,text_code,orginal_text_code
370 FROM gmd_material_details_gtmp
371 WHERE text_code <> NVL(orginal_text_code,0);
372
373 l_batch_row GME_BATCH_HEADER%ROWTYPE;
374 l_batch_rec GME_BATCH_HEADER%ROWTYPE;
375 l_batch_step GME_BATCH_STEPS%ROWTYPE;
376 l_material_detail GME_MATERIAL_DETAILS%ROWTYPE;
377 P_material GME_MATERIAL_DETAILS%ROWTYPE;
378 X_material GME_MATERIAL_DETAILS%ROWTYPE;
379 l_material_det GME_MATERIAL_DETAILS%ROWTYPE;
380 l_material_out GME_MATERIAL_DETAILS%ROWTYPE;
381 l_material_rec GME_MATERIAL_DETAILS%ROWTYPE;
382 x_def_tran_row GME_INVENTORY_TXNS_GTMP%ROWTYPE;
383 x_material_row GME_MATERIAL_DETAILS%ROWTYPE;
384 l_pending_in_rec GME_PENDING_PRODUCT_LOTS%ROWTYPE;
385 l_pending_out_rec GME_PENDING_PRODUCT_LOTS%ROWTYPE;
386 l_labrec Cur_get_lablot%ROWTYPE;
387 type text_table is table of NUMBER(10) index by binary_integer;
388 x_text_tab text_table;
389 x_cnt NUMBER default 0;
390
391 l_material_count NUMBER(5);
392 l_resource_count NUMBER(5);
393 l_msg_index NUMBER(5);
394 l_default_release_type NUMBER(5);
395 l_text_code NUMBER(5);
396 l_temp NUMBER;
397 l_transacted VARCHAR2(240);
398 l_message_count NUMBER;
399 l_message_list VARCHAR2(2000);
400
401 l_return_status VARCHAR2(10);
402
403 lock_batch_err EXCEPTION;
404 error_setup EXCEPTION;
405 error_load_batch EXCEPTION;
406 update_alloc_err EXCEPTION;
407 insert_line_err EXCEPTION;
408 update_line_err EXCEPTION;
409 delete_line_err EXCEPTION;
410 BEGIN
411 /* Initialize return status */
412 X_return_status := FND_API.g_ret_sts_success;
413 /* Establish the savepoint */
414 SAVEPOINT update_batch;
415
416 /*Let us first check if their are any changes in the batch */
417 lock_batch_record (p_batch_id => p_batch_id
418 ,x_return_status => l_return_status);
419 IF l_return_status <> x_return_status THEN
420 RAISE lock_batch_err;
421 END IF;
422
423 /* Let us load the batch transactions into the temporary table */
424 OPEN Cur_get_batch;
425 FETCH Cur_get_batch INTO l_batch_row;
426 CLOSE Cur_get_batch;
427
428 /*Lets initialize the gme variables */
429 gme_common_pvt.set_timestamp;
430 IF NOT gme_common_pvt.setup(P_org_id => l_batch_row.organization_id) THEN
431 RAISE error_setup;
432 END IF; /* IF NOT gme_api_pub.setup_done */
433
434 gmd_api_grp.fetch_parm_values(P_orgn_id => l_batch_row.organization_id,
435 P_parm_name => 'FM$DEFAULT_RELEASE_TYPE',
436 P_parm_value => l_default_release_type,
437 X_return_status => X_return_status);
438
439 /* First let us delete all the lines which existed in the batch */
440 /* but was deleted from the spreadsheet */
441 FOR l_del_matl_rec IN Cur_get_del_material LOOP
442 l_material_detail.material_detail_id := l_del_matl_rec.material_detail_id;
443 l_material_detail.batch_id := P_batch_id;
444 gme_material_detail_pvt.delete_material_line (p_batch_header_rec => l_batch_row
445 ,p_material_detail_rec => l_material_detail
446 ,p_batch_step_rec => l_batch_step
447 ,x_transacted => l_transacted
448 ,x_return_status => l_return_status);
449 IF x_return_status <> l_return_status THEN
450 RAISE delete_line_err;
451 END IF;
452 END LOOP;
453
454 /*Let us fetch all the material lines for the batch */
455 FOR l_matl_rec IN Cur_get_material LOOP
456 /*If the line is not an existing record in the batch material_details */
457 OPEN Cur_get_detail_id(l_matl_rec.material_detail_id);
458 FETCH Cur_get_detail_id INTO l_temp;
459 IF(Cur_get_detail_id%NOTFOUND) THEN
460 --Bug3680011 is fixed.
461 l_material_detail.material_detail_id := NULL;
462 /* This implies that this is a new line we need to first insert the material line */
463 l_material_detail.batch_id := p_batch_id;
464 l_material_detail.line_no := l_matl_rec.line_no;
465 l_material_detail.line_type := l_matl_rec.line_type;
466 l_material_detail.inventory_item_id := l_matl_rec.inventory_item_id;
467 l_material_detail.organization_id := l_matl_rec.organization_id;
468 l_material_detail.dtl_um := l_matl_rec.detail_uom;
469 l_material_detail.revision := l_matl_rec.revision;
470 l_material_detail.text_code := l_matl_rec.text_code;
471 l_material_detail.phantom_type := 0;
472 l_material_detail.scale_type := 1;
473 l_material_detail.release_type := NVL(l_default_release_type,0);
474 l_material_detail.alloc_ind := 0;
475 l_material_detail.scrap_factor := 0;
476 l_material_detail.actual_qty := 0;
477 IF (l_batch_row.batch_status = 1) THEN
478 l_material_detail.plan_qty := l_matl_rec.qty;
479 ELSIF (l_batch_row.batch_status = 2) THEN
480 l_material_detail.wip_plan_qty := l_matl_rec.qty;
481 END IF;
482 gmd_debug.put_line(' Inserting line for batch:'||l_material_detail.batch_id||' Item:'||l_material_detail.inventory_item_id);
483 gme_material_detail_pvt.insert_material_line (p_batch_header_rec => l_batch_row
484 ,p_material_detail_rec => l_material_detail
485 ,p_batch_step_rec => l_batch_step
486 ,p_trans_id => NULL
487 ,x_transacted => l_transacted
488 ,x_return_status => l_return_status
489 ,x_material_detail_rec => l_material_out);
490 IF x_return_status <> l_return_status THEN
491 gmd_debug.put_line(' Insert material line error: Item:'||l_material_detail.inventory_item_id);
492 RAISE insert_line_err;
493 ELSE
494 l_matl_rec.material_detail_id := l_material_out.material_detail_id;
495 UPDATE gmd_material_details_gtmp
496 SET material_detail_id = l_matl_rec.material_detail_id,
497 parent_line_id = l_matl_rec.material_detail_id
498 WHERE parent_line_id = l_matl_rec.parent_line_id;
499 END IF;
500 ELSE
501 P_material.material_detail_id := l_matl_rec.material_detail_id;
502 IF (GME_MATERIAL_DETAILS_DBL.FETCH_ROW (P_material, X_material)) THEN
503 X_material.dtl_um := l_matl_rec.detail_uom;
504 IF (l_batch_row.batch_status = 1) THEN
505 X_material.plan_qty := l_matl_rec.qty;
506 ELSIF (l_batch_row.batch_status = 2) THEN
507 X_material.wip_plan_qty := l_matl_rec.qty;
508 END IF;
509 gme_material_detail_pvt.update_material_line (p_batch_header_rec => l_batch_row
510 ,p_material_detail_rec => X_material
511 ,p_stored_material_detail_rec => NULL
512 ,p_batch_step_rec => l_batch_step
513 ,p_scale_phantom => fnd_api.g_false
514 ,p_trans_id => NULL
515 ,x_transacted => l_transacted
516 ,x_return_status => l_return_status
517 ,x_material_detail_rec => l_material_out);
518 IF x_return_status <> l_return_status THEN
519 gmd_debug.put_line(' Insert material line error: Material id:'||l_material_detail.material_detail_id);
520 RAISE update_line_err;
521 END IF;
522 END IF;
523 END IF; /* IF(Cur_get_detail_id%NOTFOUND) THEN */
524 CLOSE Cur_get_detail_id;
525
526 IF (l_batch_row.update_inventory_ind = 'Y') THEN
527 update_allocation (P_plant_Id => l_batch_row.organization_id
528 ,P_batch_id => l_batch_row.batch_id
529 ,P_material_detail_id => l_matl_rec.material_detail_id
530 ,P_line_type => l_matl_rec.line_type
531 ,X_return_status => l_return_status);
532 IF l_return_status <> x_return_status THEN
533 RAISE update_alloc_err;
534 END IF;
535 ELSE
536 l_batch_rec.batch_id := P_batch_id;
537 l_material_rec.material_detail_id := l_matl_rec.material_detail_id;
538 FOR l_del_rec IN Cur_get_del_lines (l_matl_rec.material_detail_id) LOOP
539 -- Bug# 3927768 Kapil M
540 -- Those lots which are not found in gmd_material_details_gtmp should be deleted.
541 l_pending_in_rec.pending_product_lot_id := l_del_rec.pending_product_lot_id;
542
543 gme_api_pub.delete_pending_product_lot (p_api_version => 2.0,
544 x_message_count => l_message_count,
545 x_message_list => l_message_list,
546 x_return_status => l_return_status,
547 p_batch_header_rec => l_batch_rec,
548 p_org_code => NULL,
549 p_material_detail_rec => l_material_rec,
550 p_pending_product_lots_rec => l_pending_in_rec);
551 IF x_return_status <> l_return_status THEN
552 gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
553 'lot_number: '||l_pending_in_rec.lot_number);
554 RAISE update_line_err;
555 END IF;
556 END LOOP;
557
558 /* Let us load the lab lots into the temporary table */
559 FOR l_labrec IN Cur_get_lablot (l_matl_rec.material_detail_id)LOOP
560 -- Bug# 3927768 Kapil M
561 -- Added the item_id condition to update LOTs for specified items only.
562 IF (l_labrec.line_type = 3 AND l_labrec.INVENTORY_ITEM_ID = l_matl_rec.inventory_item_id) THEN
563 l_batch_rec.batch_id := P_batch_id;
564 l_material_rec.material_detail_id := l_matl_rec.material_detail_id;
565 l_pending_in_rec.lot_number := l_labrec.lot_number;
566 l_pending_in_rec.quantity := l_labrec.qty;
567 l_pending_in_rec.secondary_quantity := l_labrec.secondary_qty;
568 l_pending_in_rec.created_by := l_labrec.created_by;
569 l_pending_in_rec.creation_date := l_labrec.creation_date;
570 l_pending_in_rec.last_updated_by := l_labrec.last_updated_by;
571 l_pending_in_rec.last_update_date := l_labrec.last_update_date;
572 /* Let us check the lab batch lot is already existing if yes update the line */
573 -- Bug# 3927768 Kapil M
574 -- Records fetched based on the pending_lot_id
575 OPEN Cur_get_lab_material(l_labrec.trans_id);
576 FETCH Cur_get_lab_material INTO l_temp;
577 IF (Cur_get_lab_material%FOUND) THEN
578 -- Bug# 3927768 Kapil M
579 -- If Quantity in a lot has been changed then, update is performed.
580 IF l_labrec.qty <> l_temp THEN
581 l_pending_in_rec.pending_product_lot_id := l_labrec.trans_id;
582 gme_api_pub.update_pending_product_lot (p_api_version => 2.0,
583 x_message_count => l_message_count,
584 x_message_list => l_message_list,
585 x_return_status => l_return_status,
586 p_batch_header_rec => l_batch_rec,
587 p_org_code => NULL,
588 p_material_detail_rec => l_material_rec,
589 p_pending_product_lots_rec => l_pending_in_rec,
590 x_pending_product_lots_rec => l_pending_out_rec);
591 IF x_return_status <> l_return_status THEN
592 gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
593 'lot_number: '||l_pending_in_rec.lot_number);
594 RAISE update_line_err;
595 END IF;
596 END IF;
597 ELSE
598 /* Let us check the lab batch lot is already existing if not insert the new line */
599 l_pending_in_rec.pending_product_lot_id := NULL;
600 gme_api_pub.create_pending_product_lot (p_api_version => 2.0,
601 x_message_count => l_message_count,
602 x_message_list => l_message_list,
603 x_return_status => l_return_status,
604 p_batch_header_rec => l_batch_rec,
605 p_org_code => NULL,
606 p_material_detail_rec => l_material_rec,
607 p_pending_product_lots_rec => l_pending_in_rec,
608 x_pending_product_lots_rec => l_pending_out_rec);
609
610 IF x_return_status <> l_return_status THEN
611 gmd_debug.put_line(' Insert pending lot error: Material id:'||l_matl_rec.material_detail_id ||
612 'lot_number: '||l_pending_in_rec.lot_number);
613 RAISE update_line_err;
614 END IF;
615 END IF;
616 CLOSE Cur_get_lab_material;
617 END IF;
618 END LOOP;/*FOR l_labrec IN Cur_get_lablot*/
619 END IF;
620 END LOOP; /* FOR l_matl_rec IN Cur_get_material */
621
622 FOR l_rec IN Cur_get_text LOOP
623 IF l_rec.material_detail_id IS NOT NULL THEN
624 IF l_rec.text_code IS NOT NULL THEN
625 l_text_code := GMA_EDITTEXT_PKG.Copy_Text(l_rec.text_code,'FM_TEXT_TBL_TL','GME_TEXT_TABLE_TL');
626
627 UPDATE gme_material_details
628 SET text_code = l_text_code
629 WHERE material_detail_id = l_rec.material_detail_id;
630 END IF;
631 END IF;
632 IF l_rec.orginal_text_code IS NOT NULL AND
633 l_rec.text_code <> l_rec.orginal_text_code THEN
634 GMA_EDITTEXT_PKG.Delete_Text(l_rec.orginal_text_code,'GME_TEXT_TABLE_TL');
635 END IF;
636 END LOOP;
637
638 update gmd_material_details_gtmp a
639 set last_update_date = (select last_update_date
640 FROM gme_material_details
641 WHERE material_detail_id = a.material_detail_id);
642 EXCEPTION
643 WHEN error_setup THEN
644 ROLLBACK TO SAVEPOINT update_batch;
645 X_return_status := FND_API.g_ret_sts_error;
646 WHEN error_load_batch OR update_alloc_err OR insert_line_err OR update_line_err
647 OR delete_line_err OR lock_batch_err THEN
648 ROLLBACK TO SAVEPOINT update_batch;
649 X_return_status := l_return_status;
650 WHEN OTHERS THEN
651 ROLLBACK TO SAVEPOINT update_batch;
652 fnd_msg_pub.add_exc_msg (gmd_spreadsheet_update.g_pkg_name, 'Update_Batch');
653 x_return_status := FND_API.g_ret_sts_unexp_error;
654 END update_batch;
655
656 /*##############################################################
657 # NAME
658 # update_allocation
659 # SYNOPSIS
660 # proc update_allocation
661 # DESCRIPTION
662 # This procedure is used to update the allocations for the batch.
663 ###############################################################*/
664
665 PROCEDURE update_allocation (P_plant_id IN NUMBER,
666 P_batch_id IN NUMBER,
667 P_material_detail_id IN NUMBER,
668 P_line_type IN NUMBER,
669 X_return_status OUT NOCOPY VARCHAR2) IS
670
671 CURSOR Cur_get_del_lines (V_material_detail_id NUMBER) IS
672 SELECT *
673 FROM mtl_reservations e
674 WHERE demand_source_header_id = P_batch_id
675 AND demand_source_line_id = V_material_detail_id
676 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
677 AND NOT EXISTS (SELECT 1
678 FROM gmd_material_details_gtmp g
679 WHERE parent_line_id = V_material_detail_id
680 AND line_type = 3
681 AND g.reservation_id = e.reservation_id);
682
683 CURSOR Cur_get_lines (V_material_detail_id NUMBER) IS
684 SELECT *
685 FROM gmd_material_details_gtmp
686 WHERE parent_line_id = V_material_detail_id
687 AND line_type = 3
688 AND transaction_id IS NULL
689 ORDER BY line_no desc;
690
691 CURSOR Cur_get_line_revision (V_material_detail_id NUMBER) IS
692 SELECT revision,inventory_item_id
693 FROM gmd_material_details_gtmp
694 WHERE material_detail_id = V_material_detail_id;
695
696 CURSOR Cur_get_rev_control (V_item_id NUMBER) IS
697 SELECT revision_qty_control_code
698 FROM mtl_system_items_b
699 WHERE inventory_item_id = V_item_id
700 AND organization_id = P_plant_id;
701
702 CURSOR Cur_get_item_revision (V_item_id NUMBER) IS
703 SELECT revision
704 FROM mtl_item_revisions
705 WHERE inventory_item_id = V_item_id
706 AND organization_id = P_plant_id
707 ORDER BY CREATION_DATE DESC;
708
709 CURSOR Cur_get_qty (V_reservation_id NUMBER) IS
710 SELECT reservation_quantity
711 FROM mtl_reservations
712 WHERE reservation_id = V_reservation_id;
713
714 CURSOR Cur_req_date IS
715 SELECT material_requirement_date
716 FROM gme_material_details
717 WHERE material_detail_id = P_material_detail_id;
718
719 CURSOR Cur_get_new_qty IS
720 SELECT qty,transaction_id,lot_number
721 FROM gmd_material_details_gtmp
722 WHERE material_detail_id = P_material_detail_id
723 AND lot_number IS NOT NULL;
724
725 CURSOR Cur_get_old_qty (V_transaction_id NUMBER, V_lot_number VARCHAR2) IS
726 SELECT transaction_quantity
727 FROM mtl_transaction_lot_numbers
728 WHERE transaction_id = V_transaction_id
729 AND lot_number = V_lot_number;
730
731 l_material_out GME_MATERIAL_DETAILS%ROWTYPE;
732
733 l_return_status VARCHAR2(10);
734 l_reservation_qty NUMBER;
735 l_requirement_date DATE;
736 l_trans_qty NUMBER;
737 l_item_id NUMBER;
738 l_revision VARCHAR2(3);
739 l_rev_control NUMBER;
740
741 update_alloc_err EXCEPTION;
742 insert_alloc_err EXCEPTION;
743 delete_alloc_err EXCEPTION;
744 trans_update EXCEPTION;
745
746 BEGIN
747 /* Initialize return status */
748 X_return_status := FND_API.g_ret_sts_success;
749
750 /* Lets check wheter transaction qty is changed for any of the material lines */
751 /* if yes raise an error message */
752 FOR l_qty IN Cur_get_new_qty LOOP
753 OPEN Cur_get_old_qty(l_qty.transaction_id,l_qty.lot_number);
754 FETCH Cur_get_old_qty INTO l_trans_qty;
755 CLOSE Cur_get_old_qty;
756 IF l_qty.qty <> ABS(l_trans_qty) THEN
757 RAISE trans_update;
758 END IF;
759 END LOOP;
760
761 /* First let us delete all the reservations which existed in the batch */
762 /* but was deleted from the spreadsheet. */
763 IF P_line_type <> 1 THEN
764 FOR l_del_rec IN Cur_get_del_lines (P_material_detail_id) LOOP
765 gmd_debug.put_line(' Material:'||P_material_detail_id||' trans:'||l_del_rec.reservation_id||' reserv qty:'||l_del_rec.reservation_quantity||' Lot:'||l_del_rec.lot_number);
766 gme_reservations_pvt.delete_reservation(p_reservation_id => l_del_rec.reservation_id
767 ,x_return_status => l_return_status);
768 IF X_return_status <> l_return_status THEN
769 gmd_debug.put_line('Delete allocation error:'||l_del_rec.reservation_id);
770 RAISE delete_alloc_err;
771 END IF;
772 END LOOP;
773 END IF;
774
775 /* Then let us update/insert the reservation lines associated with the material line */
776 FOR l_rec IN Cur_get_lines (P_material_detail_id) LOOP
777 OPEN Cur_req_date;
778 FETCH Cur_req_date INTO l_requirement_date;
779 CLOSE Cur_req_date;
780
781 --Check if the item is revision control
782 OPEN Cur_get_rev_control (P_material_detail_id);
783 FETCH Cur_get_rev_control INTO l_rev_control;
784 CLOSE Cur_get_rev_control;
785
786 IF (l_rev_control = 2) THEN
787 /*Get the item and revision from material detail line*/
788 OPEN Cur_get_line_revision (P_material_detail_id);
789 FETCH Cur_get_line_revision INTO l_revision,l_item_id;
790 CLOSE Cur_get_line_revision;
791
792 /*If revision is not found for that material line then fetch the lastest revision
793 from the item revisions table */
794 IF (l_revision IS NULL) THEN
795 OPEN Cur_get_item_revision (l_item_id);
796 FETCH Cur_get_item_revision INTO l_revision;
797 CLOSE Cur_get_item_revision;
798 END IF;
799 ELSE
800 l_revision := NULL;
801 END IF;
802
803 l_material_out.inventory_item_id := l_rec.inventory_item_id;
804 l_material_out.organization_id := P_plant_id;
805 l_material_out.revision := l_revision;
806 l_material_out.batch_id := P_batch_id;
807 l_material_out.material_requirement_date := l_requirement_date;
808 l_material_out.material_detail_id := P_material_detail_id;
809 gmd_debug.put_line('Line:'||l_rec.line_no||' Material:'||p_material_detail_id||
810 ' Lot Number:'||l_rec.lot_number||' Qty:'||l_rec.qty||
811 ' Secondary:'||l_rec.secondary_qty);
812
813 /* Let us update the existing reservations */
814 IF l_rec.reservation_id IS NOT NULL THEN
815 OPEN Cur_get_qty (l_rec.reservation_id);
816 FETCH Cur_get_qty INTO l_reservation_qty;
817 CLOSE Cur_get_qty;
818 IF l_reservation_qty <> l_rec.qty THEN
819 gme_reservations_pvt.update_reservation (p_reservation_id => l_rec.reservation_id
820 ,p_revision => l_revision
821 ,p_subinventory => l_rec.subinventory_code
822 ,p_locator_id => l_rec.locator_id
823 ,p_lot_number => l_rec.lot_number
824 ,p_new_qty => l_rec.qty
825 ,p_new_sec_qty => ABS(l_rec.secondary_qty)
826 ,p_new_uom => l_rec.detail_uom
827 ,p_new_date => SYSDATE
828 ,x_return_status => l_return_status);
829 IF X_return_status <> l_return_status THEN
830 gmd_debug.put_line('Update allocation error:'||l_rec.reservation_id);
831 RAISE update_alloc_err;
832 END IF;
833 END IF;
834 ELSE
835 /* Let us create the new reservation material line */
836 gme_reservations_pvt.create_material_reservation (p_matl_dtl_rec => l_material_out
837 ,p_resv_qty => l_rec.qty
838 ,p_sec_resv_qty => ABS(l_rec.secondary_qty)
839 ,p_resv_um => l_rec.detail_uom
840 ,p_subinventory => l_rec.subinventory_code
841 ,p_locator_id => l_rec.locator_id
842 ,p_lot_number => l_rec.lot_number
843 ,x_return_status => l_return_status);
844 IF X_return_status <> l_return_status THEN
845 gmd_debug.put_line(' Insert alloc fail'||l_return_status);
846 RAISE insert_alloc_err;
847 END IF;
848 END IF; /* IF l_rec.reservation_id IS NOT NULL THEN */
849 END LOOP; /* FOR l_rec IN Cur_get_lines (l_matl_rec.material_detail_id) */
850
851 EXCEPTION
852 WHEN update_alloc_err OR insert_alloc_err OR delete_alloc_err THEN
853 X_return_status := l_return_status;
854 WHEN trans_update THEN
855 gmd_api_grp.log_message('GMD_QTY_NO_UPDATE');
856 X_return_status := FND_API.g_ret_sts_error;
857 WHEN OTHERS THEN
858 fnd_msg_pub.add_exc_msg (gmd_spreadsheet_update.g_pkg_name, 'Update_Allocation');
859 x_return_status := FND_API.g_ret_sts_unexp_error;
860 END update_allocation;
861
862 END GMD_SPREADSHEET_UPDATE;
[Home] [Help]