1 PACKAGE BODY GMIALLOC AS
2 -- $Header: gmialocb.pls 115.6 2004/03/18 21:46:39 jsrivast noship $
3
4 /* ========================================================
5 This package is only for the use of Inventory module
6 and will be used for validating/updating the pending
7 transactions which will be drawing from the inventory when
8 there is a mass move immediate or move immediate.
9 Jalaj Srivastava Bug 3282770
10 Modified signatures of procedures update_pending_allocations
11 and CHECK_ALLOC_QTY.
12 Added procedure VALIDATE_MOVEALLOC_FORMASSMOVE.
13 ======================================================== */
14
15
16 /* ==================================================================
17 Procedure: update_pending_allocations
18
19 Description: This procedure is used for updating the pending
20 transactions in the ic_tran_pnd table which will
21 be drawing from the inventory when there is a
22 mass move immediate or move immediate.
23 Pending txn are those txns where the delete_mark
24 is 0 and completed_ind is 0 and the txn is not the
25 default txn. This procedure updates only those txns
26 where trans_qty is negative.
27 For lot controlled items default txn have a lot_id
28 0 and location as default location.
29
30 History Jalaj Srivastava Bug 2024229
31 1. we are concerned only with lot controlled items
32 2. we never update OM txns
33 3. if we cannot move allocations then we error out (done
34 from the form)
35 Jalaj Srivastava Bug 2519568
36 ic_summ_inv is now a view.
37 Removed all updates to ic_summ_inv from this procedure.
38 Jalaj Srivastava Bug 3282770
39 Modified signature to follow api standards.
40 Added proper error handling.
41 Reorganized the logic/code.
42 Allow OMSO allocations to be moved.
43 ================================================================== */
44 /* Global variables */
45 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIALLOC';
46
47 procedure update_pending_allocations
48 ( p_api_version IN NUMBER
49 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
50 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
51 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
52 ,x_return_status OUT NOCOPY VARCHAR2
53 ,x_msg_count OUT NOCOPY NUMBER
54 ,x_msg_data OUT NOCOPY VARCHAR2
55 ,pdoc_id IN NUMBER
56 ,pto_whse_code IN VARCHAR2
57 ,pto_location IN VARCHAR2
58 )
59 AS
60 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PENDING_ALLOCATIONS' ;
61 l_api_version CONSTANT NUMBER := 1.0 ;
62
63 /*===============Variable Declarations==================================*/
64 Cursor Cur_get_trans_cmp is
65 select whse_code, location,lot_id,item_id
66 from ic_tran_cmp
67 where doc_type = 'TRNI'
68 and doc_id = pdoc_id
69 and line_type = -1
70 and lot_id > 0;
71
75 from ic_tran_pnd
72 Cursor Cur_get_trans_pnd(pcur_get_trans_cmp Cur_get_trans_cmp%ROWTYPE) is
73 select trans_id,item_id,lot_id,whse_code,location,qc_grade,trans_qty,
74 trans_qty2,doc_type,doc_id,line_id
76 where item_id = pcur_get_trans_cmp.item_id
77 and lot_id = pcur_get_trans_cmp.lot_id
78 and whse_code = pcur_get_trans_cmp.whse_code
79 and location = pcur_get_trans_cmp.location
80 and doc_type NOT IN ('PROD','OPSO','OMSO')
81 and delete_mark = 0
82 and completed_ind = 0
83 and trans_qty < 0
84 UNION ALL
85 select trans_id,pnd.item_id,lot_id,whse_code,location,qc_grade,trans_qty,
86 trans_qty2,doc_type,doc_id,pnd.line_id
87 from ic_tran_pnd pnd , pm_matl_dtl matl
88 where pnd.item_id = pcur_get_trans_cmp.item_id
89 and pnd.lot_id = pcur_get_trans_cmp.lot_id
90 and pnd.whse_code = pcur_get_trans_cmp.whse_code
91 and pnd.location = pcur_get_trans_cmp.location
92 and pnd.doc_type = 'PROD'
93 and pnd.delete_mark = 0
94 and pnd.completed_ind = 0
95 and pnd.trans_qty < 0
96 and matl.batch_id = pnd.doc_id
97 and matl.line_id = pnd.line_id
98 and matl.phantom_id IS NULL
99 UNION ALL
100 select trans_id,pnd.item_id,lot_id,whse_code,location,qc_grade,trans_qty,
101 trans_qty2,doc_type,doc_id,pnd.line_id
102 from ic_tran_pnd pnd, op_ordr_dtl ordr, op_hold_cds hold
103 where pnd.item_id = pcur_get_trans_cmp.item_id
104 and pnd.lot_id = pcur_get_trans_cmp.lot_id
105 and pnd.whse_code = pcur_get_trans_cmp.whse_code
106 and pnd.location = pcur_get_trans_cmp.location
107 and pnd.doc_type = 'OPSO'
108 and pnd.delete_mark = 0
109 and pnd.completed_ind = 0
110 and pnd.trans_qty < 0
111 and ordr.line_id = pnd.line_id
112 and ordr.order_id = pnd.doc_id
113 and ordr.delete_mark = 0
114 and hold.holdreas_code = ordr.holdreas_code
115 and hold.invcommit_ind = 0
116 and hold.delete_mark = 0
117 and ( (not exists (select 1
118 from op_cust_itm
119 where cust_id = ordr.shipcust_id
120 and item_id = pcur_get_trans_cmp.item_id
121 and whse_restrictions = 1
122 and delete_mark = 0
123 )
124 )
125 OR (exists (select 1
126 from op_cust_itm
127 where cust_id = ordr.shipcust_id
128 and item_id = pcur_get_trans_cmp.item_id
129 and whse_restrictions = 1
130 and whse_code = pto_whse_code
131 and delete_mark = 0
132 )
133 )
134 )
135 UNION ALL
136 select trans_id,item_id,lot_id,whse_code,location,qc_grade,trans_qty,
137 trans_qty2,doc_type,doc_id,line_id
138 from ic_tran_pnd
139 where item_id = pcur_get_trans_cmp.item_id
140 and lot_id = pcur_get_trans_cmp.lot_id
141 and whse_code = pcur_get_trans_cmp.whse_code
142 and location = pcur_get_trans_cmp.location
143 and doc_type ='OMSO'
144 and delete_mark = 0
145 and completed_ind = 0
146 and trans_qty < 0
147 and staged_ind = 0
148 and whse_code = pto_whse_code;
149
150 orderid NUMBER;
151 UPDATE_OP_ORDR_DTL VARCHAR2(1);
152 l_user_id NUMBER := FND_GLOBAL.user_id;
153
154 /*====================End of Variable Declarations==============================*/
155 BEGIN
156 IF FND_API.to_boolean(p_init_msg_list) THEN
157 FND_MSG_PUB.Initialize;
158 END IF;
159 SAVEPOINT update_pending_allocations;
160 -- Standard call to check for call compatibility.
161 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
162 p_api_version ,
163 l_api_name ,
164 G_PKG_NAME ) THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167 x_return_status :=FND_API.G_RET_STS_SUCCESS;
168
169 --start of update of pending allocations
170 --first loop to get the move txns
171 FOR Cur_get_trans_cmp_rec in Cur_get_trans_cmp LOOP
172 --second loop for matching record from ic_tran_pnd which need to be updated
173 FOR Cur_get_trans_pnd_rec in Cur_get_trans_pnd(Cur_get_trans_cmp_rec) LOOP
174 --{
175 IF (Cur_get_trans_pnd_rec.doc_type = 'OPSO') THEN
176 --{
177 IF (Cur_get_trans_cmp_rec.whse_code <> pto_whse_code) THEN
178 --{
179 BEGIN
180 select order_id
181 into orderid
182 from op_ordr_hdr
183 where order_id = Cur_get_trans_pnd_rec.doc_id
184 and delete_mark = 0
185 FOR UPDATE NOWAIT;
186
187 UPDATE_OP_ORDR_DTL := 'Y';
188 EXCEPTION
189 WHEN OTHERS THEN
190 UPDATE_OP_ORDR_DTL := 'N';
191 END;--}
192 END IF;--}
193 END IF;--}
194 --{
195 IF ( (Cur_get_trans_pnd_rec.doc_type <> 'OPSO')
196 OR ( (Cur_get_trans_pnd_rec.doc_type = 'OPSO')
197 AND ( (Cur_get_trans_cmp_rec.whse_code = pto_whse_code)
198 OR (UPDATE_OP_ORDR_DTL = 'Y')
202
199 )
200 )
201 ) THEN
203 UPDATE IC_TRAN_PND
204 SET WHSE_CODE = pto_whse_code,
205 LOCATION = pto_location,
206 LAST_UPDATED_BY = l_user_id,
207 LAST_UPDATE_DATE = SYSDATE
208 WHERE trans_id = Cur_get_trans_pnd_rec.trans_id;
209 END IF;--}
210 --{
211 IF ( (Cur_get_trans_pnd_rec.doc_type = 'OPSO')
212 AND (Cur_get_trans_cmp_rec.whse_code <> pto_whse_code)
213 AND (UPDATE_OP_ORDR_DTL = 'Y')
214 ) THEN
215
216 UPDATE OP_ORDR_DTL
217 SET FROM_WHSE = pto_whse_code,
218 LAST_UPDATED_BY = l_user_id,
219 LAST_UPDATE_DATE = SYSDATE
220 WHERE line_id = Cur_get_trans_pnd_rec.line_id
221 and order_id = Cur_get_trans_pnd_rec.doc_id
222 and delete_mark = 0;
223
224 END IF;--}
225 --{
226 IF ( (Cur_get_trans_pnd_rec.doc_type = 'XFER')
227 AND (Cur_get_trans_cmp_rec.whse_code <> pto_whse_code)
228 ) THEN
229
230 UPDATE IC_XFER_MST
231 SET FROM_WAREHOUSE = pto_whse_code,
232 FROM_LOCATION = pto_location,
233 LAST_UPDATED_BY = l_user_id,
234 LAST_UPDATE_DATE = SYSDATE
235 WHERE transfer_id = Cur_get_trans_pnd_rec.doc_id;
236 END IF;--}
237
238 END LOOP;
239
240 END LOOP;
241
242 IF FND_API.to_boolean(p_commit) THEN
243 COMMIT WORK;
244 END IF;
245 FND_MSG_PUB.Count_AND_GET
246 (p_count => x_msg_count, p_data => x_msg_data);
247
248 EXCEPTION
249 WHEN FND_API.G_EXC_ERROR THEN
250 ROLLBACK to update_pending_allocations;
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 FND_MSG_PUB.Count_AND_GET
253 (p_count => x_msg_count, p_data => x_msg_data);
254
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256 ROLLBACK to update_pending_allocations;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 FND_MSG_PUB.Count_AND_GET
259 (p_count => x_msg_count, p_data => x_msg_data);
260
261 WHEN OTHERS THEN
262 ROLLBACK to update_pending_allocations;
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 IF (SQLCODE IS NOT NULL) THEN
265 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
266 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
267 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
268 FND_MSG_PUB.Add;
269 END IF;
270 FND_MSG_PUB.Count_AND_GET
271 (p_count => x_msg_count, p_data => x_msg_data);
272
273
274 END UPDATE_PENDING_ALLOCATIONS;
275
276 /* =======================================================================
277 Procedure: check_alloc_qty
278
279 Description: This procedure is used for validating
280 moving allocations for move immediate.
281 Pending txn are those txns where the delete_mark
282 is 0 and completed_ind is 0 and the txn is not the
283 default txn.
284 For lot controlled items default txn have a lot_id
285 0 and location as default location.
286
287 This procedure considers only actual onhand qty, move quantity
288 and the total allocations qty( where trans_qty <0). It
289 does not look at pending transactions which are going to
290 add to the inventory.
291
292 If move qty <= (onhand qty - total allocations qty) then
293 returns 0
294 no need to update allocations
295
296 If move qty > (onhand qty - total allocations qty) and
297 move qty >= total allocations qty
298 returns 1
299 allocations need to be updated
300
301 If move qty > (onhand qty - total allocations qty) and
302 move qty < total allocations qty
303 returns -1
304 allocations need to be but cannot be updated
305
306 History Jalaj Srivastava Bug 2024229
307 1. we are concerned only with lot controlled items
308 2. we never update OM txns
309 3. if we cannot move allocations then we error out (done
310 from the form)
311 Jalaj Srivastava Bug 3282770
312 Modified signature to follow api standards.
313 Added proper error handling.
314 Reorganized the logic/code.
315 If OMSO allocations exist then moving allcoations not allowed
316 if the move is to a different warehouse.
317 If any pick confirmed OMSO allocations exist then moving
318 allcoations not allowed
319 ======================================================================== */
320
321
322 PROCEDURE CHECK_ALLOC_QTY
323 ( p_api_version IN NUMBER
324 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
325 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
326 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
327 ,x_return_status OUT NOCOPY VARCHAR2
328 ,x_msg_count OUT NOCOPY NUMBER
329 ,x_msg_data OUT NOCOPY VARCHAR2
330 ,pfrom_whse_code IN VARCHAR2
331 ,pfrom_location IN VARCHAR2
335 ,pto_whse_code IN VARCHAR2
332 ,plot_id IN NUMBER
333 ,pitem_id IN NUMBER
334 ,pmove_qty IN NUMBER
336 ,x_move_allocations OUT NOCOPY VARCHAR2
337 )
338 AS
339
340 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_ALLOC_QTY' ;
341 l_api_version CONSTANT NUMBER := 1.0 ;
342 onhandqty NUMBER := 0;
343 allocqty NUMBER := 0;
344 tempqty NUMBER := 0;
345 OMSO_txn_count NUMBER := 0;
346 OMSO_pick_confirmed_txn_count NUMBER := 0;
347
348 BEGIN
349 IF FND_API.to_boolean(p_init_msg_list) THEN
350 FND_MSG_PUB.Initialize;
351 END IF;
352 -- Standard call to check for call compatibility.
353 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
354 p_api_version ,
355 l_api_name ,
356 G_PKG_NAME ) THEN
357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
358 END IF;
359 x_return_status :=FND_API.G_RET_STS_SUCCESS;
360
361 /* Get onhand */
362 select NVL(sum(nvl(loct_onhand,0)),0)
363 into onhandqty
364 from ic_loct_inv
365 where item_id = pitem_id
366 and lot_id = plot_id
367 and whse_code = pfrom_whse_code
368 and location = pfrom_location;
369
370 /* Get allocated quantity for transactions other than PROD and OPSO */
371 select nvl(abs(sum(nvl(trans_qty,0))),0)
372 into tempqty
373 from ic_tran_pnd
374 where item_id = pitem_id
375 and lot_id = plot_id
376 and whse_code = pfrom_whse_code
377 and location = pfrom_location
378 and doc_type NOT IN ('PROD','OPSO')
379 and delete_mark = 0
380 and completed_ind = 0
381 and trans_qty < 0;
382
383 allocqty :=tempqty;
384
385 /* Get allocated quantity for PROD transactions */
386 select nvl(abs(sum(nvl(trans_qty,0))),0)
387 into tempqty
388 from ic_tran_pnd itp, pm_matl_dtl pmd
389 where itp.item_id = pitem_id
390 and itp.lot_id = plot_id
391 and itp.whse_code = pfrom_whse_code
392 and itp.location = pfrom_location
393 and itp.doc_type ='PROD'
394 and itp.delete_mark = 0
395 and itp.completed_ind = 0
396 and itp.trans_qty < 0
397 and pmd.batch_id = itp.doc_id
398 and pmd.line_id = itp.line_id
399 and pmd.phantom_id IS NULL;
400
401 allocqty := allocqty + tempqty;
402
403 /* Get allocated quantity for OPSO transactions */
404 select nvl(abs(sum(nvl(trans_qty,0))),0)
405 into tempqty
406 from ic_tran_pnd itp, op_ordr_dtl ood, op_hold_cds ohc
407 where itp.item_id = pitem_id
408 and itp.lot_id = plot_id
409 and itp.whse_code = pfrom_whse_code
410 and itp.location = pfrom_location
411 and itp.doc_type ='OPSO'
412 and itp.delete_mark = 0
413 and itp.completed_ind = 0
414 and itp.trans_qty < 0
415 and ood.line_id = itp.line_id
416 and ood.order_id = itp.doc_id
417 and ood.delete_mark = 0
418 and ohc.holdreas_code = ood.holdreas_code
419 and ohc.invcommit_ind = 0
420 and ohc.delete_mark = 0;
421
422 allocqty := allocqty + tempqty;
423
424 --{
425 IF ( (allocqty > 0) and (pmove_qty >(onhandqty - allocqty)) ) THEN
426 --{
427 IF (pmove_qty >= allocqty) THEN
428
429 select count(1)
430 into OMSO_txn_count
431 from ic_tran_pnd pnd
432 where pnd.item_id = pitem_id
433 and pnd.lot_id = plot_id
434 and pnd.whse_code = pfrom_whse_code
435 and pnd.location = pfrom_location
436 and pnd.doc_type ='OMSO'
437 and pnd.delete_mark = 0
438 and pnd.completed_ind = 0
439 and pnd.trans_qty < 0;
440
441 --{
442 IF (OMSO_txn_count > 0) THEN
443 /* *****************************************************************
444 check if OMSO txns exist
445 if they exist and the move is to a different warehouse then
446 we cannot move allocations
447 ***************************************************************** */
448 --{
449 IF (pfrom_whse_code <> pto_whse_code) THEN
450
451 FND_MESSAGE.SET_NAME('GMI','GMI_MOVE_TO_DIFF_WHSE');
452 FND_MSG_PUB.Add;
453 RAISE FND_API.G_EXC_ERROR;
454 END IF;--}
455 /* *****************************************************************
456 check if OMSO txns exist
457 if they exist and any OMSO allocation is pick confirmed then the
458 allocations cannot be moved.
459 ***************************************************************** */
460 select count(1)
461 into OMSO_pick_confirmed_txn_count
462 from ic_tran_pnd pnd
463 where pnd.item_id = pitem_id
464 and pnd.lot_id = plot_id
465 and pnd.whse_code = pfrom_whse_code
466 and pnd.location = pfrom_location
467 and pnd.doc_type ='OMSO'
468 and pnd.delete_mark = 0
469 and pnd.completed_ind = 0
470 and pnd.trans_qty < 0
471 and pnd.staged_ind = 1;
472 --{
476 RAISE FND_API.G_EXC_ERROR;
473 IF (OMSO_pick_confirmed_txn_count > 0) THEN
474 FND_MESSAGE.SET_NAME('GMI','GMI_PICK_CNFRMD_ALLOC_EXISTS');
475 FND_MSG_PUB.Add;
477 END IF;--}
478
479 END IF;--}
480 --allocations can be moved.
481 x_move_allocations := 'Y';
482 ELSE
483 FND_MESSAGE.SET_NAME('GMI','GMI_UNABLE_TO_MOVE_ALLOCATIONS');
484 FND_MSG_PUB.Add;
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;--}
487 ELSE
488 --no need to move allocations
489 x_move_allocations := 'N'; /* move qty is less than or equal to unallocated qty */
490 END IF;--}
491
492 FND_MSG_PUB.Count_AND_GET
493 (p_count => x_msg_count, p_data => x_msg_data);
494
495 EXCEPTION
496 WHEN FND_API.G_EXC_ERROR THEN
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 FND_MSG_PUB.Count_AND_GET
499 (p_count => x_msg_count, p_data => x_msg_data);
500
501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 FND_MSG_PUB.Count_AND_GET
504 (p_count => x_msg_count, p_data => x_msg_data);
505
506 WHEN OTHERS THEN
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 IF (SQLCODE IS NOT NULL) THEN
509 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
510 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
511 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
512 FND_MSG_PUB.Add;
513 END IF;
514 FND_MSG_PUB.Count_AND_GET
515 (p_count => x_msg_count, p_data => x_msg_data);
516
517 END CHECK_ALLOC_QTY;
518
519 /* =======================================================================
520 Procedure: VALIDATE_MOVEALLOC_FORMASSMOVE
521
522 Description: This procedure is used to validate moving allocations
523 when there is a mass move immediate.
524 Allocations are those txns where the delete_mark
525 is 0 and completed_ind is 0 and the txn is not the
526 default txn.
527 For lot controlled items default txn have a lot_id
528 0 and location as default location.
529 This procedure considers only actual onhand qty, move quantity
530 and the total allocations qty( where trans_qty <0). It
531 does not look at pending transactions which are going to
532 add to the inventory.
533
534 If move qty <= (onhand qty - total allocations qty) then
535 returns 0
536 no need to update allocations
537
538 If move qty > (onhand qty - total allocations qty) and
539 move qty >= total allocations qty
540 returns 1
541 allocations need to be updated
542
543 If move qty > (onhand qty - total allocations qty) and
544 move qty < total allocations qty
545 returns -1
546 allocations need to be but cannot be updated
547
548 Jalaj Srivastava Bug 3282770
549 Added this procedure.
550 If OMSO allocations exist then moving allcoations not allowed
551 if the move is to a different warehouse.
552 If any pick confirmed OMSO allocations exist then moving
553 allcoations not allowed
554 ======================================================================== */
555
556
557 PROCEDURE VALIDATE_MOVEALLOC_FORMASSMOVE
558 ( p_api_version IN NUMBER
559 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
560 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
561 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
562 ,x_return_status OUT NOCOPY VARCHAR2
563 ,x_msg_count OUT NOCOPY NUMBER
564 ,x_msg_data OUT NOCOPY VARCHAR2
565 ,pfrom_whse_code IN VARCHAR2
566 ,pto_whse_code IN VARCHAR2
567 ,pjournal_id IN NUMBER
568 )
569 AS
570
571 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MOVEALLOC_FORMASSMOVE' ;
572 l_api_version CONSTANT NUMBER := 1.0 ;
573 OMSO_txn_count NUMBER := 0;
574 OMSO_pick_confirmed_txn_count NUMBER := 0;
575
576 BEGIN
577 IF FND_API.to_boolean(p_init_msg_list) THEN
578 FND_MSG_PUB.Initialize;
579 END IF;
580 -- Standard call to check for call compatibility.
581 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
582 p_api_version ,
583 l_api_name ,
584 G_PKG_NAME ) THEN
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587 x_return_status :=FND_API.G_RET_STS_SUCCESS;
588
589 SELECT count(1)
590 INTO OMSO_txn_count
591 FROM ic_tran_pnd pnd, ic_adjs_jnl jnl
592 WHERE jnl.journal_id = pjournal_id
593 AND jnl.line_type = -1
594 AND jnl.lot_id > 0
595 AND pnd.item_id = jnl.item_id
596 AND pnd.lot_id = jnl.lot_id
597 AND pnd.whse_code = jnl.whse_code
598 AND pnd.location = jnl.location
599 AND pnd.doc_type = 'OMSO'
600 AND pnd.delete_mark = 0
601 AND pnd.completed_ind = 0
602 AND pnd.trans_qty < 0;
603
604 --{
605 IF (OMSO_txn_count > 0) THEN
606 /* *****************************************************************
607 check if OMSO txns exist
608 if they exist and the move is to a different warehouse then
609 we cannot move allocations
610 ***************************************************************** */
611 --{
612 IF (pfrom_whse_code <> pto_whse_code) THEN
613
614 FND_MESSAGE.SET_NAME('GMI','GMI_MOVE_TO_DIFF_WHSE');
615 FND_MSG_PUB.Add;
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;--}
618 /* *****************************************************************
619 check if OMSO txns exist
620 if they exist and any OMSO allocation is pick confirmed then the
621 allocations cannot be moved.
622 ***************************************************************** */
623 select count(1)
624 into OMSO_pick_confirmed_txn_count
625 FROM ic_tran_pnd pnd, ic_adjs_jnl jnl
626 WHERE jnl.journal_id = pjournal_id
627 AND jnl.line_type = -1
628 AND jnl.lot_id > 0
629 AND pnd.item_id = jnl.item_id
630 AND pnd.lot_id = jnl.lot_id
631 AND pnd.whse_code = jnl.whse_code
632 AND pnd.location = jnl.location
633 AND pnd.doc_type = 'OMSO'
634 AND pnd.delete_mark = 0
635 AND pnd.completed_ind = 0
636 AND pnd.trans_qty < 0
637 AND pnd.staged_ind = 1;
638 --{
639 IF (OMSO_pick_confirmed_txn_count > 0) THEN
640 FND_MESSAGE.SET_NAME('GMI','GMI_PICK_CNFRMD_ALLOC_EXISTS');
641 FND_MSG_PUB.Add;
642 RAISE FND_API.G_EXC_ERROR;
643 END IF;--}
644
645 END IF;--}
646
647 FND_MSG_PUB.Count_AND_GET
648 (p_count => x_msg_count, p_data => x_msg_data);
649
650 EXCEPTION
651 WHEN FND_API.G_EXC_ERROR THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 FND_MSG_PUB.Count_AND_GET
654 (p_count => x_msg_count, p_data => x_msg_data);
655
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 FND_MSG_PUB.Count_AND_GET
659 (p_count => x_msg_count, p_data => x_msg_data);
660
661 WHEN OTHERS THEN
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 IF (SQLCODE IS NOT NULL) THEN
664 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
665 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
666 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
667 FND_MSG_PUB.Add;
668 END IF;
669 FND_MSG_PUB.Count_AND_GET
670 (p_count => x_msg_count, p_data => x_msg_data);
671
672 END VALIDATE_MOVEALLOC_FORMASSMOVE;
673
674 END GMIALLOC;
675
[Home] [Help]