1 PACKAGE BODY GMIVQTY AS
2 /* $Header: GMIVQTYB.pls 120.0 2005/05/25 15:57:23 appldev noship $ */
3 /* Body start of comments
4 +==========================================================================+
5 | Copyright (c) 1998 Oracle Corporation |
6 | Redwood Shores, CA, USA |
7 | All rights reserved. |
8 +==========================================================================+
9 | FILE NAME |
10 | GMIVQTYB.pls |
11 | |
12 | PACKAGE NAME |
13 | GMIVQTY |
14 | |
15 | DESCRIPTION |
16 | |
17 | CONTENTS |
18 | Validate_Inventory_Posting |
19 | |
20 | HISTORY |
21 | 25-FEB-1999 M.Godfrey Upgrade to R11 |
22 | 20/AUG/1999 H.Verdding Bug 951828 Change GMS package Calls to GMA |
23 | 27/OCT/1999 H.Verdding Bug 1042739 added l_qty_rec.orgn_code To |
24 | GMA_VALID_GRP.Validate_doc_no |
25 | 23/May/2000 P.J.Schofield Major rewrite |
26 | 01/Nov/2001 K.RajaSekhar Bug 1962677 Code is changed to copy the |
27 | journal_comment value in the procedure |
28 | Validate_Inventory_Posting. |
29 | 27/Dec/2001 J.DiIorio Bug#2117575 - 11.5.1I Changed type 3 move |
30 | to check to quantity for zero in addition |
31 | to check for null. |
32 | Changed validate_inventory_posting. |
33 | 04/15/2002 Venkat Ramana Bug#2317115 Modified the select statement |
34 | in the function co_orgn_whse_valid. |
35 | 05/06/2002 Sastry/Ravi BUG#2354190/2354168 Modified the code in |
36 | Procedure Validate_Inventory_Posting. |
37 | 30/Apr/2002 B.Ravishanker Bug#2340824 Modified code in the function |
38 | whse_locations_valid and |
39 | Validate_Inventory_Posting procedure to |
40 | display the correct error message when the |
41 | to_location is invalid (Marked for Purged).|
42 | 07/02/2002 Jalaj Srivastava Bug 2483656
43 | Modified to enable creation of journals |
44 | through inventory APIs
45 | 11/11/2002 Joe DiIorio Bug 2643440
46 | 11.5.1J - added nocopy. |
47 | 21/11/2002 Sastry Bug 2665243 Modified elsif condition |
48 | in procedure validate_inventory_posting. |
49 | 09/05/2003 Sastry BUG 2861715 Modified code in Procedure |
50 | Validate_Inventory_Posting. |
51 | 10/09/2003 James Bernard Bug 3127824 Modified code in |
52 | validate_inventory_posting so that user is |
53 | allowed to move qty from source whse even |
54 | when on hand qty is NULL.Also modifed code |
55 | so that negative qty not allowed message is|
56 | displayed when onhand qty is going negative|
57 | for IC$ALLOWNEGINV is zero. |
58 | 09/10/2003 James Bernard Bug 3171345 Added code to set lot status of|
59 | source whse to default lot status if the |
60 | destination whse also does not have any |
61 | lot status. If destination whse has a lot |
62 | status then the source whse will have lot |
63 | status of the destination whse. This is |
64 | is done if the onhand qty in source whse |
65 | is NULL while doing TRN transactions. |
66 +==========================================================================+
67 Body end of comments
68 */
69 /* Global variables */
70 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMIVQTY';
71 l_from_loct_ctl NUMBER;
72 l_to_loct_ctl NUMBER;
73 /* +=========================================================================+
74 | FUNCTION NAME |
75 | Check_unposted_jnl_lot_status |
76 | |
77 | TYPE |
78 | PRIVATE |
79 | |
80 | USAGE |
81 | Used to ascertain if any unposted journals exist for item / lot / |
82 | sublot / whse_code / location with a different lot status |
83 | |
84 | DESCRIPTION |
85 | This procedure checks for unposted journals for item / lot / sublot |
86 | / whse_code / location with differnet lot status |
87 | |
88 | PARAMETERS |
89 | p_item_id Surrogate key of item |
90 | p_lot_id Surrogate key of lot |
91 | p_whse_code Warehouse code |
92 | p_location Location |
93 | p_lot_status Lot status to be checked for |
94 | |
95 | RETURNS |
96 | BOOLEAN |
97 | |
98 | HISTORY |
99 | 01-OCT-1998 M.Godfrey Created |
100 +=========================================================================+
101 */
102 FUNCTION Check_unposted_jnl_lot_status
103 ( p_item_id IN ic_item_mst.item_id%TYPE
104 , p_lot_id IN ic_lots_mst.lot_id%TYPE
105 , p_whse_code IN ic_whse_mst.whse_code%TYPE
106 , p_location IN ic_loct_mst.location%TYPE
107 , p_lot_status IN ic_lots_sts.lot_status%TYPE
108 )
109 RETURN BOOLEAN
110 IS
111
112 CURSOR ic_journal IS
113 SELECT
114 count(*)
115 FROM
116 ic_adjs_jnl a, ic_jrnl_mst j
117 WHERE
118 a.item_id = p_item_id AND
119 a.lot_id = p_lot_id AND
120 a.whse_code = p_whse_code AND
121 a.location = p_location AND
122 a.journal_id = j.journal_id AND
123 j.posted_ind = 0 AND
124 j.delete_mark = 0 AND
125 p_lot_status <> a.lot_status;
126
127 l_rows_found NUMBER;
128
129 BEGIN
130
131 OPEN ic_journal;
132
133 FETCH ic_journal INTO l_rows_found;
134
135 IF (ic_journal%NOTFOUND)
136 THEN
137 l_rows_found :=0;
138 END IF;
139
140 CLOSE ic_journal;
141
142 IF l_rows_found > 0
143 THEN
144 RETURN TRUE;
145 ELSE
146 RETURN FALSE;
147 END IF;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 RAISE;
152
153 END Check_unposted_jnl_lot_status;
154
155 /* +=========================================================================+
156 | FUNCTION NAME |
157 | Check_unposted_jnl_qc_grade |
158 | |
159 | TYPE |
160 | PRIVATE |
161 | |
162 | USAGE |
163 | Used to ascertain if any unposted journals exist for item / lot / |
164 | sublot / whse_code / location with a different QC grade |
165 | |
166 | DESCRIPTION |
167 | This procedure checks for unposted journals for item / lot / sublot |
168 | / whse_code / location with differnet QC grade |
169 | |
170 | PARAMETERS |
171 | p_item_id Surrogate key of item |
172 | p_lot_id Surrogate key of lot |
173 | p_qc_grade QC grade to be checked for |
174 | |
175 | RETURNS |
176 | BOOLEAN |
177 | |
178 | HISTORY |
179 | 01-OCT-1998 M.Godfrey Created |
180 +=========================================================================+
181 */
182 FUNCTION Check_unposted_jnl_qc_grade
183 ( p_item_id IN ic_item_mst.item_id%TYPE
184 , p_lot_id IN ic_lots_mst.lot_id%TYPE
185 , p_qc_grade IN qc_grad_mst.qc_grade%TYPE
186 )
187 RETURN BOOLEAN
188 IS
189
190 CURSOR ic_journal IS
191 SELECT
192 count(*)
193 FROM
194 ic_adjs_jnl a, ic_jrnl_mst j
195 WHERE
196 a.item_id = p_item_id AND
197 a.lot_id = p_lot_id AND
198 a.journal_id = j.journal_id AND
199 j.posted_ind = 0 AND
200 j.delete_mark = 0 AND
201 p_qc_grade <> a.qc_grade;
202
203 l_rows_found NUMBER;
204
205 BEGIN
206
207 OPEN ic_journal;
208
209 FETCH ic_journal INTO l_rows_found;
210
211 IF (ic_journal%NOTFOUND)
212 THEN
213 l_rows_found :=0;
214 END IF;
215
216 CLOSE ic_journal;
217
218 IF l_rows_found > 0
219 THEN
220 RETURN TRUE;
221 ELSE
222 RETURN FALSE;
223 END IF;
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 RAISE;
228
229 END Check_unposted_jnl_qc_grade;
230
231
232 FUNCTION co_orgn_valid (p_qty_rec IN GMIGAPI.qty_rec_typ)
233 RETURN BOOLEAN
234 IS
235 l_count NUMBER;
236 BEGIN
237 SELECT 1
238 INTO l_count
239 FROM sy_orgn_mst co,
240 sy_orgn_mst org
241 WHERE co.orgn_code = p_qty_rec.co_code AND
242 co.delete_mark = 0 AND
243 org.orgn_code = p_qty_rec.orgn_code AND
244 org.co_code = p_qty_rec.co_code AND
245 org.delete_mark=0;
246
247 RETURN TRUE;
248
249 EXCEPTION
250 WHEN OTHERS
251 THEN
252 RETURN FALSE;
253 END;
254
255
256 FUNCTION co_orgn_whse_valid
257 ( p_qty_rec IN GMIGAPI.qty_rec_typ
258 , x_from_loct_ctl OUT NOCOPY NUMBER
259 , x_to_loct_ctl OUT NOCOPY NUMBER
260 )
261 RETURN BOOLEAN
262 IS
263 l_from_whse ic_whse_mst.whse_code%TYPE;
264 l_to_whse ic_whse_mst.whse_code%TYPE;
265 BEGIN
266 l_from_whse := p_qty_rec.from_whse_code;
267 /* **************************************************
268 Jalaj Srivastava Bug 2483656
269 added trans types 8 for move journal
270 ************************************************** */
271 IF p_qty_rec.trans_type IN (3,8)
272 THEN l_to_whse := p_qty_rec.to_whse_code;
273 ELSE l_to_whse := p_qty_rec.from_whse_code;
274 END IF;
275 -- BEGIN BUG#2317115 Venkata Ramana
276 -- No need to check for the delete_mark of company because
277 -- the company cannot be deleted without deleting the dependent Organizations.
278 SELECT fw.loct_ctl, tw.loct_ctl
279 INTO x_from_loct_ctl, x_to_loct_ctl
280 FROM ic_whse_mst fw,
281 ic_whse_mst tw,
282 sy_orgn_mst fo,
283 sy_orgn_mst toc
284 WHERE fw.whse_code = l_from_whse AND
285 tw.whse_code = l_to_whse AND
286 fo.orgn_code = fw.orgn_code AND
287 toc.orgn_code = tw.orgn_code AND
288 fw.delete_mark = 0 AND
289 tw.delete_mark = 0 AND
290 fo.delete_mark = 0 AND
291 toc.delete_mark = 0;
292 --END BUG#2317115
293 RETURN TRUE;
294
295 EXCEPTION
296
297 WHEN OTHERS
298 THEN
299 RETURN FALSE;
300 END;
301
302
303 FUNCTION whse_locations_valid
304 (p_qty_rec IN GMIGAPI.qty_rec_typ)
305 RETURN BOOLEAN
306 IS
307 l_from_whse ic_whse_mst.whse_code%TYPE;
308 l_to_whse ic_whse_mst.whse_code%TYPE;
309 l_to_location ic_loct_mst.location%TYPE;
310 l_from_location ic_loct_mst.location%TYPE;
311 l_count NUMBER;
312 BEGIN
313 l_from_whse := p_qty_rec.from_whse_code;
314 l_from_location := p_qty_rec.from_location;
315
316 /* **************************************************
317 Jalaj Srivastava Bug 2483656
318 added trans types 8,9 for journals
319 ************************************************** */
320 IF p_qty_rec.trans_type IN (3,4,8,9)
321 THEN
322 l_to_whse := p_qty_rec.to_whse_code;
323 l_to_location := p_qty_rec.to_location;
324 ELSE
325 l_to_whse := p_qty_rec.from_whse_code;
326 l_to_location := p_qty_rec.from_location;
327 END IF;
328
329 IF l_from_loct_ctl = 1
330 AND l_from_location <> GMIGUTL.IC$DEFAULT_LOCT
331 THEN
332 SELECT 1
333 INTO l_count
334 FROM ic_loct_mst
335 WHERE whse_code = l_from_whse
336 AND location = l_from_location
337 AND delete_mark = 0;
338 END IF;
339
340 IF l_to_loct_ctl = 1
341 AND l_to_location <> GMIGUTL.IC$DEFAULT_LOCT
342 THEN
343 SELECT 1
344 INTO l_count
345 FROM ic_loct_mst
346 WHERE whse_code = l_to_whse
347 AND location = l_to_location
348 AND delete_mark = 0;
349 END IF;
350
351 RETURN TRUE;
352
353 EXCEPTION
354
355 WHEN OTHERS
356 THEN
357 -- BEGIN BUG#2340824 Ravishanker B.
358 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCATION');
359 FND_MESSAGE.SET_TOKEN('ITEM_NO', P_qty_rec.item_no);
360 --IF there is some problem in the 1st Select statement
361 --the l_count is NULL and control gets into the IF part
362 IF l_count IS NULL THEN
363 FND_MESSAGE.SET_TOKEN('WHSE_CODE', P_qty_rec.from_whse_code);
364 FND_MESSAGE.SET_TOKEN('LOCATION',P_qty_rec.from_location);
365 --IF there is some problem in the 2nd Select statement
366 --the l_count shall be '1' and control gets into the ELSE part
367 ELSE
368 FND_MESSAGE.SET_TOKEN('WHSE_CODE', P_qty_rec.to_whse_code);
369 FND_MESSAGE.SET_TOKEN('LOCATION',P_qty_rec.to_location);
370 END IF;
371 FND_MSG_PUB.Add;
372 -- END BUG#2340824
373 RETURN FALSE;
374 END;
375
376
377 /* +=========================================================================+
378 | PROCEDURE NAME |
379 | Validate_Inventory_Posting |
380 | |
381 | TYPE |
382 | Public |
383 | |
384 | USAGE |
385 | Perform validation functions for inventory quantities posting |
386 | |
387 | DESCRIPTION |
388 | This procedure performs all the validation functions concerned with |
389 | inventory quantity postings. |
390 | |
391 | PARAMETERS |
392 | p_qty_rec Record datatype containing all inventory posting |
393 | data |
394 | x_item_id Surrogate key of the item |
395 | x_lot_id Surrogate key of the lot |
396 | x_old_lot_status Original lot status of item/lot/location |
397 | x_old_qc_grade Original QC grade of item/lot |
398 | x_trans_rec Record datatype containing all inventory posting |
399 | data |
400 | x_return_status 'S'-success, 'E'-error, 'U'-unexpected error |
401 | x_msg_count Count of messages in message list |
402 | x_msg_data Message data |
403 | |
404 | HISTORY |
405 | 01-OCT-1998 M.Godfrey Created |
406 | 16-AUG-1999 H.Verdding Added Fix For B965832 Part 2 |
407 | Prevent Transactions Against |
408 | Default Lot. |
409 | 17-AUG-1999 H.Verdding Added Fix For B959444 |
410 | Amended Deviation Logic |
411 | |
412 | 24-APR-2001 A. Mundhe Bug 1735824 - Validate user name, |
413 | co_code and orgn_code. |
414 | |
415 | 01/Nov/2001 K.RajaSekhar Bug 1962677 Code is changed to copy |
416 | the journal_comment value in to the |
417 | out-parameter from the in-parameter |
418 | record. |
419 | |
420 | 06-Feb-2002 A. Mundhe Bug 2206335 - Do not call the uom |
421 | conversion routine if the user is |
422 | trying to zero out the quantity or |
423 | move entire quantity. |
424 | |
425 | 18-Feb-2002 A. Mundhe Bug 2206335 - If the user is trying to|
426 | zero out the qty then do not run the |
427 | deviation logic as it is already |
428 | accounted for. |
429 | 06-May-2002 Sastry/Ravi BUG#2354190/2354168 - Modified code so|
430 | that if user sets the profile option |
431 | IC$MOVEDIFFSTAT for different values |
432 | then in all cases API works properly. |
433 | 30-Apr-2002 B Ravishanker Bug#2340824 - Changed the code to |
434 | display the correct message when |
435 | to_location is invalid |
436 | (Marked for Purged). |
437 | Jalaj Srivastava Bug 2635964 |
438 | For status/grade immediate transactions, we need to capture |
439 | the quantities also for storing in the database. Previously we |
440 | stored zero for these transactions. |
441 | Sastry Bug 2665343
442 | Modified elsif condition so that the error message cannot post to |
443 | future date does not occur when a validate trans_date in past is |
444 | passed. |
445 | |
446 | 19-Aug-2003 A. Mundhe Bug 2946031 - Display negative inv |
447 | message correctly when profile |
448 | IC$ALLOWNEGINV is zero and loct_onhand|
449 | is null. |
450 | Sastry 09/05/2003 BUG#2861715 |
451 | Added code to move/adjust the entire Onhand qty2 based on the |
452 | parameter move_entire_qty when entire primary qty is moved/adjusted|
453 | for dual2 items. |
454 | 10-Sep-2003 James Bernard Bug 3127824 Modified code so that |
455 | user is allowed to move qty even when |
456 | qty at source is NULL. Also modified |
457 | code to display negative inv |
458 | message correctly when profile |
459 | IC$ALLOWNEGINV is zero and loct_onhand|
460 | is null. |
461 | 09-OCT-2003 James Bernard Bug 3171345 Added code to set lot status |
462 | of source whse to default status if the |
463 | destination whse also does not have any |
464 | lot status. If destination whse has a lot |
465 | status then the source whse will have lot |
466 | status of the destination whse. This is |
467 | is done if the onhand qty in source whse |
468 | is NULL while doing TRN transactions. |
469 | 25-AUG-2004 Supriya Malluru Bug 3711032 Added code to initalize from and to location |
470 | controls to 0. |
471 +=========================================================================+
472 */
473 PROCEDURE Validate_Inventory_Posting
474 ( p_api_version IN NUMBER
475 , p_validation_level IN NUMBER
476 , p_qty_rec IN GMIGAPI.qty_rec_typ
477 , p_ic_item_mst_row IN ic_item_mst%ROWTYPE
478 , p_ic_item_cpg_row IN ic_item_cpg%ROWTYPE
479 , p_ic_lots_mst_row IN ic_lots_mst%ROWTYPE
480 , p_ic_lots_cpg_row IN ic_lots_cpg%ROWTYPE
481 , x_ic_jrnl_mst_row OUT NOCOPY ic_jrnl_mst%ROWTYPE
482 , x_ic_adjs_jnl_row1 OUT NOCOPY ic_adjs_jnl%ROWTYPE
483 , x_ic_adjs_jnl_row2 OUT NOCOPY ic_adjs_jnl%ROWTYPE
484 , x_return_status OUT NOCOPY VARCHAR2
485 , x_msg_count OUT NOCOPY NUMBER
486 , x_msg_data OUT NOCOPY VARCHAR2
487 )
488 IS
489 l_qty_rec GMIGAPI.qty_rec_typ;
490 l_ic_whse_mst_rec ic_whse_mst%ROWTYPE;
491 l_ic_loct_inv_row_from ic_loct_inv%ROWTYPE;
492 l_ic_loct_inv_row_to ic_loct_inv%ROWTYPE;
493 l_sy_reas_cds_row sy_reas_cds%ROWTYPE;
494 l_qc_grad_mst_row qc_grad_mst%ROWTYPE;
495 l_ic_lots_sts_row ic_lots_sts%ROWTYPE;
496 l_qty2 NUMBER;
497 l_onhand NUMBER;
498 l_onhand2 NUMBER;
499 l_trans_type NUMBER(2);
500 l_trans_code VARCHAR2(4);
501 l_original_qc_grade VARCHAR2(4);
502 l_original_lot_status VARCHAR2(4);
503 l_msg_count NUMBER;
504 l_msg_data VARCHAR2(2000);
505 l_return_status VARCHAR2(1);
506 l_return_val NUMBER;
507 l_neg_qty NUMBER :=0;
508 l_bad_location NUMBER := 0;
509 -- Bug 1735824
510 l_user_name fnd_user.user_name%TYPE;
511 l_user_id fnd_user.user_id%TYPE;
512
513 -- Bug 2206335
514 l_check_deviation NUMBER := 1;
515 l_ccid NUMBER;
516 l_errmsg VARCHAR2(4000);
517 l_other_lines_qty NUMBER := 0;
518 l_from_whse_co_code VARCHAR2(4);
519 CURSOR Cur_get_onhand_for_grade IS
520 SELECT sum(loct_onhand),sum(loct_onhand2)
521 FROM ic_loct_inv
522 WHERE item_id = p_ic_item_mst_row.item_id
523 AND lot_id = p_ic_lots_mst_row.lot_id;
524
525 --BEGIN BUG#3171345
526 CURSOR Cur_get_status IS
527 SELECT lot_status
528 FROM ic_item_mst
529 WHERE item_id = p_ic_item_mst_row.item_id;
530 --END BUG#3171345
531
532 BEGIN
533 /* Store inputs locally */
534 l_qty_rec := p_qty_rec;
535 l_trans_type := p_qty_rec.trans_type;
536 l_user_name := p_qty_rec.user_name;
537
538 -- Bug 1735824
539 -- Validate the user name,co_code and orgn_code.
540 GMA_GLOBAL_GRP.Get_who( p_user_name => l_user_name
541 , x_user_id => l_user_id
542 );
543
544 IF l_user_id = 0
545 THEN
546 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_USER_NAME');
547 FND_MESSAGE.SET_TOKEN('USER_NAME', l_user_name);
548 FND_MSG_PUB.Add;
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552 IF NOT GMA_VALID_GRP.Validate_co_code(p_qty_rec.co_code)
553 THEN
554 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_CO_CODE');
555 FND_MESSAGE.SET_TOKEN('CO_CODE',p_qty_rec.co_code);
556 FND_MSG_PUB.Add;
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559
560 IF NOT GMA_VALID_GRP.Validate_orgn_code(l_qty_rec.orgn_code)
561 THEN
562 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ORGN_CODE');
563 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_qty_rec.orgn_code);
564 FND_MSG_PUB.Add;
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 IF l_trans_type BETWEEN 1 AND 10
569 THEN
570 NULL;
571 ELSE
572 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_TRANS_TYPE');
573 FND_MESSAGE.SET_TOKEN('TRANS_TYPE',l_trans_type);
574 FND_MSG_PUB.Add;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 /* Check that transaction type is applicable to item. If */
579 /* it's not, complain, otherwise set up a few defaults. */
580 IF p_ic_item_mst_row.grade_ctl = 0 AND l_trans_type IN (5,10)
581 OR p_ic_item_mst_row.status_ctl = 0 AND l_trans_type IN (4,9)
582 THEN
583 FND_MESSAGE.SET_NAME('GMI','IC_API_INV_TRANS_TYPE_FOR_ITEM');
584 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
585 FND_MESSAGE.SET_TOKEN('TRANS_TYPE', l_trans_type);
586 FND_MSG_PUB.Add;
587 RAISE FND_API.G_EXC_ERROR;
588 ELSE
589 /* For STSI and GRDI default the uoms. For other types */
590 /* accept what we're given unless they're null, in which */
591 /* case default them. */
592 IF (l_trans_type IN (4,5,9,10))
593 THEN
594 l_qty_rec.item_um := p_ic_item_mst_row.item_um;
595 l_qty_rec.item_um2 := p_ic_item_mst_row.item_um2;
596 ELSE
597 l_qty_rec.item_um := nvl(l_qty_rec.item_um, p_ic_item_mst_row.item_um);
598 l_qty_rec.item_um2 := nvl(l_qty_rec.item_um2, p_ic_item_mst_row.item_um2);
599 END IF;
600
601 IF (l_trans_type < 6) THEN
602 l_qty_rec.journal_ind := 'N';
603 ELSE
604 l_qty_rec.journal_ind := 'Y';
605 END IF;
606 IF l_trans_type = 1
607 THEN
608 l_trans_code := 'CREI';
609 END IF;
610
611 IF l_trans_type = 2
612 THEN
613 l_trans_code := 'ADJI';
614 END IF;
615
616 IF l_trans_type = 3
617 THEN
618 l_trans_code := 'TRNI';
619 END IF;
620
621 IF l_trans_type = 4
622 THEN
623 l_trans_code := 'STSI';
624 END IF;
625
626 IF l_trans_type = 5
627 THEN
628 l_trans_code := 'GRDI';
629 END IF;
630 IF l_trans_type = 6 THEN
631 l_trans_code := 'CRER';
632 END IF;
633 IF l_trans_type = 7 THEN
634 l_trans_code := 'ADJR';
635 END IF;
636 IF l_trans_type = 8 THEN
637 l_trans_code := 'TRNR';
638 END IF;
639 IF l_trans_type = 9 THEN
640 l_trans_code := 'STSR';
641 END IF;
642 IF l_trans_type = 10 THEN
643 l_trans_code := 'GRDR';
644 END IF;
645
646 l_qty_rec.txn_type := substr(l_trans_code,1,3);
647 IF (l_qty_rec.txn_type = 'CRE') THEN
648 l_qty_rec.lot_status := p_ic_item_mst_row.lot_status;
649 l_qty_rec.qc_grade := p_ic_item_mst_row.qc_grade;
650 END IF;
651
652 IF (l_qty_rec.txn_type = 'STS') THEN
653 l_qty_rec.to_whse_code := l_qty_rec.from_whse_code;
654 l_qty_rec.to_location := l_qty_rec.from_location;
655 END IF;
656
657 IF (l_qty_rec.txn_type = 'GRD') THEN
658 l_qty_rec.from_whse_code := NULL;
659 l_qty_rec.from_location := NULL;
660 END IF;
661
662 END IF;
663
664
665 /* All transaction types need an item. Make sure we have */
666 /* one which can be used */
667 IF p_ic_item_mst_row.item_id = 0
668 OR p_ic_item_mst_row.delete_mark = 1
669 OR p_ic_item_mst_row.noninv_ind = 1
670 OR p_ic_item_mst_row.inactive_ind =1 AND GMIGUTL.IC$API_ALLOW_INACTIVE=0
671 THEN
672 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
673 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
674 FND_MSG_PUB.Add;
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678
679 /* If the item is lot controlled, all transaction types need */
680 /* a lot. If it isn't lot controlled we can ignore whatever */
681 /* we're given and use the default lot. It should also be */
682 /* borne in mind that sublot control does not necessarilly */
683 /* mean that we should have a sublot number. Also make sure */
684 /* that attempts to transact against the default lot are */
685 /* blocked. */
686
687 IF p_ic_item_mst_row.lot_ctl > 0
688 THEN
689 IF NVL(l_qty_rec.lot_no,GMIGUTL.IC$DEFAULT_LOT)=GMIGUTL.IC$DEFAULT_LOT
690 OR p_ic_lots_mst_row.delete_mark = 1
691 OR p_ic_lots_mst_row.inactive_ind = 1 AND GMIGUTL.IC$API_ALLOW_INACTIVE = 0
692 THEN
693 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_NO');
694 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
695 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
696 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
697 FND_MSG_PUB.Add;
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700 ELSE
701 l_qty_rec.lot_no := GMIGUTL.IC$DEFAULT_LOT;
702 l_qty_rec.sublot_no := NULL;
703 END IF;
704
705
706 /* This next bit is a bit more complicated. For all transaction */
707 /* types the company must own the organisation. For STSI and GRDI */
708 /* transactions, the validation stops there. For CREI, ADJI and */
709 /* TRNI we need to ensure that the warehouse(s) belong to the */
710 /* organisation(s) too. Whilst doing this last check, the loct_*/
711 /* _ctl flags will be retrieved for later use in location */
712 /* validation. If any ownership verification fails we cannot */
713 /* proceed. */
714 /* Initialize from and to location control variables*/
715
716 /* BUG 3711032 */
717 l_from_loct_ctl:=0;
718 l_to_loct_ctl:=0;
719
720 IF l_trans_type IN (4,5,9,10)
721 THEN
722 IF co_orgn_valid (l_qty_rec)
723 THEN
724 NULL;
725 ELSE
726 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ORGN_CODE');
727 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_qty_rec.orgn_code);
728 FND_MSG_PUB.Add;
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731 ELSE
732 IF co_orgn_whse_valid (l_qty_rec, l_from_loct_ctl, l_to_loct_ctl)
733 THEN
734 NULL;
735 ELSE
736 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ORGN_CODE');
737 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_qty_rec.orgn_code);
738 FND_MSG_PUB.Add;
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 END IF;
742
743
744 /* Location control: If the item and the warehouse(s) are both */
745 /* location controlled then we must block attempts to transact */
746 /* against the default location. If either of them is not loct */
747 /* controlled then we can only transact against the default. */
748 /* */
749 /* If one of them is location controlled (loct_ctl=1) and the */
750 /* other is non-validated location controlled (loct_ctl=2) then */
751 /* we can accept whatever location is specified. */
752 /* */
753 /* For trans_types of 1,2,4 there will only be 1 warehouse and */
754 /* location. For trans_type=3 (TRNI) there will be 2 warehouse/ */
755 /* location combinations and the combinations should not match. */
756
757 /* For grade changes we don't care about locations so */
758 /* even if we're given one, it is flattened. */
759
760 IF l_trans_type IN (5,10)
761 THEN
762 l_qty_rec.from_location := GMIGUTL.IC$DEFAULT_LOCT;
763 l_qty_rec.to_location := GMIGUTL.IC$DEFAULT_LOCT;
764 ELSE
765 IF NVL(l_qty_rec.from_location,GMIGUTL.IC$DEFAULT_LOCT)=GMIGUTL.IC$DEFAULT_LOCT
766 THEN
767 l_qty_rec.from_location := GMIGUTL.IC$DEFAULT_LOCT;
768 END IF;
769
770 IF NVL(l_qty_rec.to_location,GMIGUTL.IC$DEFAULT_LOCT)=GMIGUTL.IC$DEFAULT_LOCT
771 THEN
772 l_qty_rec.to_location := GMIGUTL.IC$DEFAULT_LOCT;
773 END IF;
774
775 l_bad_location := 0;
776
777 IF p_ic_item_mst_row.loct_ctl > 0
778 THEN
779 IF l_from_loct_ctl > 0 AND
780 l_qty_rec.from_location=GMIGUTL.IC$DEFAULT_LOCT
781 OR l_trans_type in (3,4,8,9) AND
782 l_to_loct_ctl>0 AND
783 l_qty_rec.to_location=GMIGUTL.IC$DEFAULT_LOCT
784 OR l_trans_type IN (3,8) AND
785 l_qty_rec.from_whse_code=l_qty_rec.to_whse_code AND
786 l_qty_rec.from_location=l_qty_rec.to_location
787 THEN l_bad_location := 1;
788 END IF;
789
790 IF l_bad_location = 1
791 THEN
792 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCATION');
793 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_qty_rec.item_no);
794 FND_MESSAGE.SET_TOKEN('WHSE_CODE', l_qty_rec.from_whse_code);
795 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
796 FND_MSG_PUB.Add;
797 RAISE FND_API.G_EXC_ERROR;
798 -- BEGIN BUG#2340824 Ravishanker B.
799 ELSE
800 IF whse_locations_valid (l_qty_rec) = FALSE THEN
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803 -- END BUG#2340824
804 END IF;
805 ELSE
806 l_qty_rec.from_location := GMIGUTL.IC$DEFAULT_LOCT;
807 l_qty_rec.to_location := GMIGUTL.IC$DEFAULT_LOCT;
808 END IF;
809 END IF;
810
811
812 /* If this is a grade change, ensure we have a different qc */
813 /* grade to the current one for the lot. For all other */
814 /* transactions we default it from the lot master. */
815
816 IF (l_trans_type IN (5,10))
817 THEN
818 IF l_qty_rec.qc_grade <> p_ic_lots_mst_row.qc_grade
819 THEN
820 IF GMIGUTL.v_qc_grade(l_qty_rec.qc_grade, l_qc_grad_mst_row)
821 THEN
822 /* Check for unposted journals with different QC grade */
823 IF Check_unposted_jnl_qc_grade
824 ( p_item_id => p_ic_item_mst_row.item_id
825 , p_lot_id => p_ic_lots_mst_row.lot_id
826 , p_qc_grade => l_qty_rec.qc_grade
827 )
828 THEN
829 FND_MESSAGE.SET_NAME('GMI','IC_API_UNPOSTED_JNL_QC_GRADE');
830 FND_MESSAGE.SET_TOKEN('QC_GRADE',l_qty_rec.qc_grade);
831 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
832 FND_MSG_PUB.Add;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835 ELSE
836 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_QC_GRADE');
837 FND_MESSAGE.SET_TOKEN('QC_GRADE',l_qty_rec.qc_grade);
838 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
839 FND_MSG_PUB.Add;
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842 ELSE
843 FND_MESSAGE.SET_NAME('GMI','IC_API_SAME_QC_GRADE');
844 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
845 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
846 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
847 FND_MSG_PUB.Add;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850 ELSE
851 l_qty_rec.qc_grade :=p_ic_lots_mst_row.qc_grade;
852 END IF;
853
854
855
856 /* For anything other than grade changes we must ensure that we */
857 /* are not attempting to post into a closed period */
858
859 /* Jalaj Srivastava Bug 1427922 13-OCT-2000 */
860 /* For status and grade txns we do not need trans date validations */
861 /* BEGIN BUG#1492002 Sastry */
862 /* Transaction date should be less than sysdate including the timestamp*/
863 IF (l_qty_rec.trans_type NOT IN (4,5,9,10))
864 THEN
865 l_return_val := GMICCAL.trans_date_validate
866 ( l_qty_rec.trans_date
867 , l_qty_rec.orgn_code
868 , l_qty_rec.from_whse_code
869 );
870 IF l_return_val <> 0
871 THEN
872 FND_MESSAGE.SET_NAME('GMI','IC_API_CANNOT_POST_CLOSED');
873 FND_MESSAGE.SET_TOKEN('ITEM_NO' , l_qty_rec.item_no);
874 FND_MESSAGE.SET_TOKEN('TRANS_DATE', l_qty_rec.trans_date);
875 FND_MSG_PUB.Add;
876 RAISE FND_API.G_EXC_ERROR;
877 -- Bug 2665243 Sastry removed to_char
878 ELSIF l_qty_rec.trans_date > SYSDATE
879 THEN
880 FND_MESSAGE.SET_NAME('GMI','IC_API_CANNOT_POST_FUTURE');
881 FND_MESSAGE.SET_TOKEN('ITEM_NO' , l_qty_rec.item_no);
882 FND_MESSAGE.SET_TOKEN('TRANS_DATE', l_qty_rec.trans_date);
883 FND_MSG_PUB.Add;
884 RAISE FND_API.G_EXC_ERROR;
885 END IF;
886 END IF;
887 /* END BUG#1492002 */
888 /* Validate Reason Code. All transaction types need a reason */
889 /* code. If this is a 'Quantity' transaction then the flags */
890 /* on the code must be checked against the quantity given. */
891 /* Note that there is no correlation between document */
892 /* types and reason codes. */
893
894 IF GMIGUTL.v_reason_code
895 ( p_reason_code => l_qty_rec.reason_code
896 , x_sy_reas_cds_row=> l_sy_reas_cds_row
897 )
898 THEN
899 IF (l_trans_type IN (1,2,3,6,7,8))
900 THEN
901 IF l_sy_reas_cds_row.reason_type = 1 AND
902 l_qty_rec.trans_qty < 0
903 THEN
904 FND_MESSAGE.SET_NAME('GMI','IC_API_DEC_NOT_ALLOWED');
905 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
906 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
907 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
908 FND_MSG_PUB.Add;
909 RAISE FND_API.G_EXC_ERROR;
910 ELSIF l_sy_reas_cds_row.reason_type = 2 AND
911 l_qty_rec.trans_qty > 0
912 THEN
913 FND_MESSAGE.SET_NAME('GMI','IC_API_INC_NOT_ALLOWED');
914 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
915 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
916 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
917 FND_MSG_PUB.Add;
918 RAISE FND_API.G_EXC_ERROR;
919 END IF;
920 END IF;
921 ELSE
922 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_REASON_CODE');
923 FND_MESSAGE.SET_TOKEN('REASON_CODE',l_qty_rec.reason_code);
924 FND_MSG_PUB.Add;
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927
928
929 /* 'Quantity' transactions must not have a zero quantity */
930 /* and transfers must be specified positively */
931
932 IF l_trans_type IN (3,8) AND l_qty_rec.trans_qty <= 0
933 THEN
934 FND_MESSAGE.SET_NAME('GMI','IC_API_MOVE_QTY_NOT_NEG');
935 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
936 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
937 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
938 FND_MSG_PUB.Add;
939 RAISE FND_API.G_EXC_ERROR;
940 ELSE
941 IF l_qty_rec.trans_qty =0
942 THEN
943 FND_MESSAGE.SET_NAME('GMI','IC_API_ZERO_QTY');
944 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
945 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
946 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
947 FND_MSG_PUB.Add;
948 RAISE FND_API.G_EXC_ERROR;
949 END IF;
950 END IF;
951
952
953 /* See if we have any stock balances. */
954 /* The presence or otherwise of stock drives the validation */
955 /* for lot_status and qc_grade appropriate to the transaction. */
956
957 IF l_trans_type in (1,2,4,6,7,9)
958 THEN
959 GMIGUTL.get_loct_inv
960 ( p_item_id => p_ic_item_mst_row.item_id
961 , p_whse_code =>l_qty_rec.from_whse_code
962 , p_lot_id =>p_ic_lots_mst_row.lot_id
963 , p_location =>l_qty_rec.from_location
964 , x_ic_loct_inv_row =>l_ic_loct_inv_row_from
965 );
966 /* *************************************************************
967 Jalaj Srivastava Bug 2635964
968 Need to populate only for status immediate transactions.
969 ************************************************************* */
970 IF (l_trans_type = 4) THEN
971 l_onhand := l_ic_loct_inv_row_from.loct_onhand;
972 l_onhand2 := l_ic_loct_inv_row_from.loct_onhand2;
973 END IF;
974 ELSIF l_trans_type IN (3,8)
975 THEN
976 GMIGUTL.get_loct_inv
977 ( p_item_id => p_ic_item_mst_row.item_id
978 , p_whse_code =>l_qty_rec.from_whse_code
979 , p_lot_id =>p_ic_lots_mst_row.lot_id
980 , p_location =>l_qty_rec.from_location
981 , x_ic_loct_inv_row =>l_ic_loct_inv_row_from
982 );
983 GMIGUTL.get_loct_inv
984 ( p_item_id => p_ic_item_mst_row.item_id
985 , p_whse_code =>l_qty_rec.to_whse_code
986 , p_lot_id =>p_ic_lots_mst_row.lot_id
987 , p_location =>l_qty_rec.to_location
988 , x_ic_loct_inv_row =>l_ic_loct_inv_row_to
989 );
990 ELSIF l_trans_type IN (5,10) THEN
991 /* *************************************************************
992 Jalaj Srivastava Bug 2635964
993 For grade transactions, there is no warehouse and location.
994 The grade change affects the lot at all warehouse/location.
995 To get onhand we need to sum up the onhands at all the
996 warehouse/locations where the lot exists.
997 Removed the code here which was seleting directly from
998 ic_loct_inv using GMIVDBL.ic_loct_inv_select which uses a
999 rownum? to restrict rows to 1.
1000 ************************************************************* */
1001 OPEN Cur_get_onhand_for_grade;
1002 FETCH Cur_get_onhand_for_grade INTO l_onhand,l_onhand2;
1003 IF (Cur_get_onhand_for_grade%NOTFOUND) THEN
1004 l_onhand := NULL;
1005 l_onhand2 := NULL;
1006 END IF;
1007 CLOSE Cur_get_onhand_for_grade;
1008
1009 END IF;
1010
1011 /* Hang onto original status and grade as we'll need them */
1012 /* when we set up the output journal rows */
1013
1014 l_original_qc_grade := p_ic_lots_mst_row.qc_grade;
1015 l_original_lot_status := l_ic_loct_inv_row_from.lot_status;
1016
1017 IF l_trans_type IN (1,6)
1018 THEN
1019 /* If inventory create there should be no stock at location. */
1020 IF l_ic_loct_inv_row_from.loct_onhand IS NOT NULL
1021 THEN
1022 FND_MESSAGE.SET_NAME('GMI','IC_API_LOCT_ONHAND_EXISTS');
1023 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1024 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1025 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1026 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_qty_rec.from_whse_code);
1027 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
1028 FND_MSG_PUB.Add;
1029 RAISE FND_API.G_EXC_ERROR;
1030 ELSE
1031 l_qty_rec.lot_status := p_ic_item_mst_row.lot_status;
1032 l_qty_rec.qc_grade := p_ic_item_mst_row.qc_grade;
1033 END IF;
1034 ELSIF l_trans_type IN (2,7)
1035 THEN
1036 /* If adjusting there might or might not be stock */
1037 IF l_ic_loct_inv_row_from.loct_onhand IS NULL
1038 THEN
1039 l_qty_rec.lot_status := p_ic_item_mst_row.lot_status;
1040 l_qty_rec.qc_grade := p_ic_item_mst_row.qc_grade;
1041 ELSE
1042 l_qty_rec.lot_status := l_ic_loct_inv_row_from.lot_status;
1043 l_qty_rec.qc_grade := p_ic_lots_mst_row.qc_grade;
1044 END IF;
1045 ELSIF l_trans_type IN (3,8)
1046 THEN
1047 /* If moving stock then there should be some at the source but */
1048 /* not necessarilly at the target. */
1049 /* BUG#3127824 James Bernard */
1050 /* Modified code so that user is able to move qty even if source whse has NULL qty */
1051 /*IF l_ic_loct_inv_row_from.loct_onhand IS NULL
1052 THEN
1053 FND_MESSAGE.SET_NAME('GMI','IC_API_NO_LOCT_ONHAND');
1054 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1055 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1056 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1057 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_qty_rec.from_whse_code);
1058 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
1059 FND_MSG_PUB.Add;
1060 RAISE FND_API.G_EXC_ERROR;
1061 ELSE*/
1062 /*==================================================
1063 27/Dec/2001 J.DiIorio Bug#2117575 - 11.5.1I
1064 ================================================*/
1065 --BEGIN BUG James Bernard3171345
1066 IF p_ic_item_mst_row.status_ctl = 1 AND l_ic_loct_inv_row_from.lot_status IS NULL THEN
1067 --Check first if there is any/zero qty in the to whse ie destination whse.
1068 IF l_ic_loct_inv_row_to.lot_status IS NULL THEN
1069 OPEN Cur_get_status;
1070 FETCH Cur_get_status INTO l_ic_loct_inv_row_from.lot_status;
1071 l_original_lot_status:=l_ic_loct_inv_row_from.lot_status;
1072 CLOSE Cur_get_status;
1073 ELSE
1074 --There is zero/any qty in destination whse ..assign that status to the source as well.
1075 l_ic_loct_inv_row_from.lot_status:=l_ic_loct_inv_row_to.lot_status;
1076 l_original_lot_status:=l_ic_loct_inv_row_to.lot_status;
1077 END IF;
1078 END IF;
1079 --END BUG#3171345
1080 -- BEGIN BUG#2354190/2354168 Sastry/Ravi
1081 -- Modified the code to handle different cases when profile
1082 -- option IC$MOVEDIFFSTAT is set to different values.
1083 IF GMIGUTL.IC$MOVEDIFFSTAT =0 THEN
1084 IF (l_ic_loct_inv_row_to.loct_onhand IS NULL)
1085 or (NVL(l_ic_loct_inv_row_from.lot_status,' ') = NVL(l_ic_loct_inv_row_to.lot_status,' ')) THEN
1086 l_qty_rec.lot_status := l_ic_loct_inv_row_from.lot_status;
1087 ELSE
1088 FND_MESSAGE.SET_NAME('GMI','IC_API_MOVE_STATUS_ERR');
1089 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1090 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1091 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1092 FND_MSG_PUB.Add;
1093 RAISE FND_API.G_EXC_ERROR;
1094 END IF;
1095 ELSIF GMIGUTL.IC$MOVEDIFFSTAT = 1 THEN
1096 IF (l_ic_loct_inv_row_to.loct_onhand IS NULL) THEN
1097 l_qty_rec.lot_status := l_ic_loct_inv_row_FROM.lot_status;
1098 ELSE
1099 l_qty_rec.lot_status := l_ic_loct_inv_row_TO.lot_status;
1100 END IF;
1101 ELSIF GMIGUTL.IC$MOVEDIFFSTAT =2 THEN
1102 IF (nvl(l_ic_loct_inv_row_to.loct_onhand,0) = 0)
1103 or (NVL(l_ic_loct_inv_row_from.lot_status,' ') = NVL(l_ic_loct_inv_row_to.lot_status,' ')) THEN
1104 l_qty_rec.lot_status := l_ic_loct_inv_row_from.lot_status;
1105 ELSE
1106 FND_MESSAGE.SET_NAME('GMI','IC_API_MOVE_STATUS_ERR');
1107 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1108 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1109 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1110 FND_MSG_PUB.Add;
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113 END IF;
1114 -- END IF; BUG#3127824 James Bernard
1115 -- END BUG#2354190/2354168
1116 ELSIF l_trans_type IN (4,9)
1117 THEN
1118 IF l_ic_loct_inv_row_from.loct_onhand IS NULL
1119 THEN
1120 FND_MESSAGE.SET_NAME('GMI','IC_API_NO_LOCT_ONHAND');
1121 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1122 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1123 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1124 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_qty_rec.from_whse_code);
1125 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
1126 FND_MSG_PUB.Add;
1127 RAISE FND_API.G_EXC_ERROR;
1128 ELSIF l_qty_rec.lot_status = l_ic_loct_inv_row_from.lot_status
1129 THEN
1130 FND_MESSAGE.SET_NAME('GMI','IC_API_SAME_LOT_STATUS');
1131 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1132 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1133 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1134 FND_MSG_PUB.Add;
1135 RAISE FND_API.G_EXC_ERROR;
1136 ELSIF l_qty_rec.lot_status = p_ic_item_mst_row.lot_status
1137 THEN
1138 NULL;
1139 ELSE
1140 IF GMIGUTL.v_lot_status (l_qty_rec.lot_status, l_ic_lots_sts_row)
1141 THEN
1142 NULL;
1143 ELSE
1144 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_STATUS');
1145 FND_MESSAGE.SET_TOKEN('LOT_STATUS',l_qty_rec.lot_status);
1146 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1147 FND_MSG_PUB.Add;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150 END If;
1151 /* Check for unposted journals with different lot status */
1152 IF Check_unposted_jnl_lot_status
1153 ( p_item_id => p_ic_item_mst_row.item_id
1154 , p_lot_id => p_ic_lots_mst_row.lot_id
1155 , p_whse_code => l_qty_rec.from_whse_code
1156 , p_location => l_qty_rec.from_location
1157 , p_lot_status => l_qty_rec.lot_status
1158 )
1159 THEN
1160 FND_MESSAGE.SET_NAME('GMI','IC_API_UNPOSTED_JNL_LOT_STATUS');
1161 FND_MESSAGE.SET_TOKEN('LOT_STATUS',l_qty_rec.lot_status);
1162 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1163 FND_MSG_PUB.Add;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166 ELSE
1167 /* For grade changes there should be a non-zero balance */
1168 IF NVL(l_onhand,0) = 0
1169 THEN
1170 FND_MESSAGE.SET_NAME('GMI','IC_API_NO_LOT_ONHAND');
1171 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1172 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1173 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1174 FND_MSG_PUB.Add;
1175 RAISE FND_API.G_EXC_ERROR;
1176 END IF;
1177 END IF;
1178
1179
1180 /* Before embarking on major number crunching, validate the */
1181 /* primary uom of the quantity passed in. */
1182 IF l_qty_rec.item_um <> p_ic_item_mst_row.item_um
1183 THEN
1184 IF NOT GMA_VALID_GRP.Validate_um(l_qty_rec.item_um)
1185 THEN
1186 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
1187 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1188 FND_MESSAGE.SET_TOKEN('UOM',l_qty_rec.item_um);
1189 FND_MSG_PUB.Add;
1190 RAISE FND_API.G_EXC_ERROR;
1191 END IF;
1192 END IF;
1193
1194
1195
1196 /* FROM HERE ON, NOTHING HAS CHANGED -- */
1197
1198 /* Handle Quantities */
1199 /* If primary Uom differs from item primary UoM then convert */
1200 /* transaction quantity */
1201 IF (l_qty_rec.item_um <> p_ic_item_mst_row.item_um) OR
1202 (l_qty_rec.item_um IS NULL)
1203 THEN
1204 /* If quantity to convert is negative then make positive for conversion */
1205 IF l_qty_rec.trans_qty < 0
1206 THEN
1207 l_neg_qty := 1;
1208 l_qty_rec.trans_qty := 0 - l_qty_rec.trans_qty;
1209 END IF;
1210 l_qty_rec.trans_qty :=GMICUOM.uom_conversion
1211 ( pitem_id =>p_ic_item_mst_row.item_id
1212 , plot_id =>p_ic_lots_mst_row.lot_id
1213 , pcur_qty =>l_qty_rec.trans_qty
1214 , pcur_uom =>l_qty_rec.item_um
1215 , pnew_uom =>p_ic_item_mst_row.item_um
1216 , patomic =>0
1217 );
1218 /* Negative quantity indicates UoM conversion failure */
1219 IF (l_qty_rec.trans_qty < 0)
1220 THEN
1221 FND_MESSAGE.SET_NAME('GMI','IC_API_UOM_CONVERSION_ERROR');
1222 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1223 FND_MESSAGE.SET_TOKEN('FROM_UOM',l_qty_rec.item_um);
1224 FND_MESSAGE.SET_TOKEN('TO_UOM',p_ic_item_mst_row.item_um);
1225 FND_MSG_PUB.Add;
1226 RAISE FND_API.G_EXC_ERROR;
1227 ELSE
1228 l_qty_rec.item_um :=p_ic_item_mst_row.item_um;
1229 /* Reverse quantity sign if reversed above */
1230 IF l_neg_qty = 1
1231 THEN
1232 l_neg_qty := 0;
1233 l_qty_rec.trans_qty := 0 - l_qty_rec.trans_qty;
1234
1235 END IF;
1236 END IF;
1237 END IF;
1238
1239 /* If dual unit of measure then convert to item secondary unit of measure */
1240 IF (p_ic_item_mst_row.dualum_ind > 0)
1241 THEN
1242 /* Bug 2206335 */
1243 /* Do not call the uom conversion routine if the user is trying to
1244 zero out the quantity or move entire quantity */
1245 IF ( (l_trans_type IN (2,7)) AND
1246 (l_qty_rec.trans_qty + l_ic_loct_inv_row_from.loct_onhand) = 0 )
1247 THEN
1248 -- BEGIN BUG#2861715 Sastry
1249 IF (p_ic_item_mst_row.dualum_ind = 2) THEN
1250 IF (UPPER(l_qty_rec.move_entire_qty) = 'Y') THEN
1251 l_qty2 := l_ic_loct_inv_row_from.loct_onhand2 * -1;
1252 l_qty_rec.trans_qty2 :=l_qty2;
1253 l_check_deviation := 0;
1254 ELSE
1255 l_qty2 := GMICUOM.uom_conversion
1256 ( pitem_id =>p_ic_item_mst_row.item_id
1257 , plot_id =>p_ic_lots_mst_row.lot_id
1258 , pcur_qty =>l_qty_rec.trans_qty
1259 , pcur_uom =>p_ic_item_mst_row.item_um
1260 , pnew_uom =>p_ic_item_mst_row.item_um2
1261 , patomic =>0
1262 );
1263 l_check_deviation := 1;
1264 END IF;
1265 ELSE
1266 l_qty2 := l_ic_loct_inv_row_from.loct_onhand2 * -1;
1267 l_qty_rec.trans_qty2 :=l_qty2;
1268 l_check_deviation := 0;
1269 END IF;
1270 -- END BUG#2861715
1271 ELSIF( (l_trans_type IN (3,8)) AND
1272 (l_qty_rec.trans_qty - l_ic_loct_inv_row_from.loct_onhand) = 0 )
1273 THEN
1274 -- BEGIN BUG#2861715 Sastry
1275 -- Added code to move the entire Onhand qty2 based on the newly added parameter
1276 IF (p_ic_item_mst_row.dualum_ind = 2) THEN
1277 IF (UPPER(l_qty_rec.move_entire_qty) = 'Y') THEN
1278 l_qty2 := l_ic_loct_inv_row_from.loct_onhand2;
1279 l_qty_rec.trans_qty2 :=l_qty2;
1280 l_check_deviation := 0;
1281 ELSE
1282 l_qty2 := GMICUOM.uom_conversion
1283 ( pitem_id =>p_ic_item_mst_row.item_id
1284 , plot_id =>p_ic_lots_mst_row.lot_id
1285 , pcur_qty =>l_qty_rec.trans_qty
1286 , pcur_uom =>p_ic_item_mst_row.item_um
1287 , pnew_uom =>p_ic_item_mst_row.item_um2
1288 , patomic =>0
1289 );
1290 l_check_deviation := 1;
1291 END IF;
1292 ELSE
1293 l_qty2 := l_ic_loct_inv_row_from.loct_onhand2;
1294 l_qty_rec.trans_qty2 :=l_qty2;
1295 l_check_deviation := 0;
1296 END IF;
1297 -- END BUG#2861715
1298 ELSE
1299 /* If quantity to convert is negative then make positive for conversion */
1300 IF l_qty_rec.trans_qty < 0
1301 THEN
1302 l_neg_qty := 1;
1303 l_qty_rec.trans_qty := 0 - l_qty_rec.trans_qty;
1304 END IF;
1305 l_qty2 :=GMICUOM.uom_conversion
1306 ( pitem_id =>p_ic_item_mst_row.item_id
1307 , plot_id =>p_ic_lots_mst_row.lot_id
1308 , pcur_qty =>l_qty_rec.trans_qty
1309 , pcur_uom =>p_ic_item_mst_row.item_um
1310 , pnew_uom =>p_ic_item_mst_row.item_um2
1311 , patomic =>0
1312 );
1313 /* Negative quantity indicates UoM conversion failure */
1314 IF (l_qty2 < 0)
1315 THEN
1316 FND_MESSAGE.SET_NAME('GMI','IC_API_UOM_CONVERSION_ERROR');
1317 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1318 FND_MESSAGE.SET_TOKEN('FROM_UOM',p_ic_item_mst_row.item_um);
1319 FND_MESSAGE.SET_TOKEN('TO_UOM',p_ic_item_mst_row.item_um2);
1320 FND_MSG_PUB.Add;
1321 RAISE FND_API.G_EXC_ERROR;
1322 END IF;
1323 /* Reverse quantity sign if reversed above */
1324 IF l_neg_qty = 1
1325 THEN
1326 l_neg_qty := 0;
1327 l_qty_rec.trans_qty := 0 - l_qty_rec.trans_qty;
1328 l_qty2 := 0 - l_qty2;
1329 END IF;
1330 END IF; /* 2206335 */
1331
1332 /* If fixed conversion then converted value is secondary qty */
1333 IF (p_ic_item_mst_row.dualum_ind = 1) OR
1334 (p_ic_item_mst_row.dualum_ind = 2 AND l_qty_rec.trans_qty2 = 0)
1335 THEN
1336 l_qty_rec.trans_qty2 :=l_qty2;
1337 l_qty_rec.item_um2 :=p_ic_item_mst_row.item_um2;
1338 ELSE
1339 /* If secondary Uom differs from item secondary UoM then convert */
1340 /* transaction quantity */
1341 IF (l_qty_rec.item_um2 <> p_ic_item_mst_row.item_um2)
1342
1343 THEN
1344 /* If quantity to convert is negative then make positive for conversion */
1345 IF l_qty_rec.trans_qty < 0
1346 THEN
1347 l_neg_qty := 1;
1348 l_qty_rec.trans_qty2 := 0 - l_qty_rec.trans_qty2;
1349 END IF;
1350 l_qty_rec.trans_qty2 :=GMICUOM.uom_conversion
1351 ( pitem_id =>p_ic_item_mst_row.item_id
1352 , plot_id =>p_ic_lots_mst_row.lot_id
1353 , pcur_qty =>l_qty_rec.trans_qty2
1354 , pcur_uom =>l_qty_rec.item_um2
1355 , pnew_uom =>p_ic_item_mst_row.item_um2
1356 , patomic =>0
1357 );
1358 /* Negative quantity indicates UoM conversion failure */
1359 IF (l_qty_rec.trans_qty2 < 0)
1360 THEN
1361 FND_MESSAGE.SET_NAME('GMI','IC_API_UOM_CONVERSION_ERROR');
1362 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1363 FND_MESSAGE.SET_TOKEN('FROM_UOM',l_qty_rec.item_um2);
1364 FND_MESSAGE.SET_TOKEN('TO_UOM',p_ic_item_mst_row.item_um2);
1365 FND_MSG_PUB.Add;
1366 RAISE FND_API.G_EXC_ERROR;
1367 ELSE
1368 l_qty_rec.item_um2 :=p_ic_item_mst_row.item_um2;
1369 /* Reverse quantity sign if reversed above */
1370 IF l_neg_qty = 1
1371 THEN
1372 l_neg_qty := 0;
1373 l_qty_rec.trans_qty2 := 0 - l_qty_rec.trans_qty2;
1374 END IF;
1375 END IF;
1376 --Jalaj Srivastava Bug 1554040
1377 ELSE
1378 IF (l_qty_rec.trans_qty2 IS NULL) THEN
1379 l_qty_rec.trans_qty2 :=l_qty2;
1380 END IF;
1381 END IF;
1382
1383 /* Check deviation */
1384 /* H.Verdding B959444 Amended Deviation Logic */
1385
1386 -- Bug 2206335
1387 -- If the user is trying to zero out the qty then do not
1388 -- run the deviation logic as it is already accounted for.
1389 IF (l_check_deviation <> 0) THEN
1390 IF (ABS(l_qty_rec.trans_qty2) >
1391 ABS(l_qty2) * (1 + p_ic_item_mst_row.deviation_hi)) OR
1392 (ABS(l_qty_rec.trans_qty2) <
1393 ABS(l_qty2) * (1 - p_ic_item_mst_row.deviation_lo))
1394 THEN
1395 FND_MESSAGE.SET_NAME('GMI','IC_API_QTY_TOLERANCE_ERROR');
1396 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1397 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1398 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1399 FND_MSG_PUB.Add;
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 END IF;
1403 END IF;
1404 ELSE
1405 /* LE fix */
1406 l_qty_rec.item_um2 := NULL;
1407 l_qty_rec.trans_qty2 := NULL;
1408 END IF;
1409 /* Check location inventory for becoming negative */
1410 IF (GMIGUTL.IC$ALLOWNEGINV = '0') THEN
1411 /* ******************************************************
1412 For ADJR/TRNR transactions check for other lines also
1413 ****************************************************** */
1414 IF ( (l_trans_type IN (7,8))
1415 AND ( (l_qty_rec.journal_no IS NOT NULL)
1416 OR ( (upper(l_qty_rec.journal_no) = 'PREVIOUS')
1417 AND (l_qty_rec.orgn_code = GMIGAPI.prev_orgn_code)
1418 )
1419 )
1420 ) THEN
1421
1422 SELECT nvl(sum(a.qty),0)
1423 INTO l_other_lines_qty
1424 FROM ic_jrnl_mst j, ic_adjs_jnl a
1425 WHERE j.orgn_code = l_qty_rec.orgn_code
1426 AND j.journal_no = nvl(l_qty_rec.journal_no,GMIGAPI.prev_journal_no)
1427 AND a.journal_id = j.journal_id
1428 AND a.line_type <> -1
1429 AND a.item_id = p_ic_item_mst_row.item_id
1430 AND a.lot_id = nvl(p_ic_lots_mst_row.lot_id,0)
1431 AND a.whse_code = l_qty_rec.from_whse_code
1432 AND a.location = nvl(l_qty_rec.from_location,GMIGUTL.IC$DEFAULT_LOCT);
1433 END IF;-- ( (l_trans_type IN (7,8))
1434 -- Bug 2946031
1435 -- Display negative qty not allowed message correctly if
1436 -- l_ic_loct_inv_row_from.loct_onhand is null.
1437 -- Bug 3127824 Added nvl function to l_ic_loct_inv_row_from.loct_onhand so that negative qty not
1438 -- allowed message is displayed properly when doing move immediate / Journal.
1439 IF ( ( (l_trans_type IN (1,2,6,7))
1440 AND ((nvl(l_ic_loct_inv_row_from.loct_onhand,0) + l_qty_rec.trans_qty + l_other_lines_qty) < 0)
1441 )
1442 OR
1443 ( (l_trans_type IN (3,8))
1444 AND ((nvl(l_ic_loct_inv_row_from.loct_onhand,0) - l_qty_rec.trans_qty - l_other_lines_qty) < 0)
1445 )
1446 ) THEN
1447 FND_MESSAGE.SET_NAME('GMI','IC_API_NEG_QTY_NOT_ALLOWED');
1448 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1449 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1450 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1451 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_qty_rec.from_whse_code);
1452 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
1453 FND_MSG_PUB.Add;
1454 RAISE FND_API.G_EXC_ERROR;
1455 END IF;
1456 END IF;
1457
1458 /* Check move quantity if item is lot-indivisble */
1459 IF (p_ic_item_mst_row.lot_indivisible = 1) AND
1460 (l_trans_type IN (3,8)) AND
1461 (l_ic_loct_inv_row_from.loct_onhand <> l_qty_rec.trans_qty)
1462 THEN
1463 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_INDIVISIBLE');
1464 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_qty_rec.item_no);
1465 FND_MESSAGE.SET_TOKEN('LOT_NO',l_qty_rec.lot_no);
1466 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_qty_rec.sublot_no);
1467 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_qty_rec.from_whse_code);
1468 FND_MESSAGE.SET_TOKEN('LOCATION',l_qty_rec.from_location);
1469 FND_MSG_PUB.Add;
1470 RAISE FND_API.G_EXC_ERROR;
1471 END IF;
1472 IF (l_qty_rec.txn_type IN ('GRD','STS','TRN')) THEN
1473 x_ic_adjs_jnl_row1.acctg_unit_id := NULL;
1474 x_ic_adjs_jnl_row1.acct_id := NULL;
1475 x_ic_adjs_jnl_row2.acctg_unit_id := NULL;
1476 x_ic_adjs_jnl_row2.acct_id := NULL;
1477 END IF;
1478 /* *********************************************************
1479 Jalaj Srivastava Bug 2483656
1480 Charge accounts are only available from 11i family pack
1481 I onwards and for Create/Adjust only.
1482 ********************************************************* */
1483 IF ( (l_qty_rec.txn_type IN ('CRE','ADJ'))
1484 AND (GMIPVER.get_opm_11i_family_pack >= 9)
1485 ) THEN
1486 IF ( (l_qty_rec.acctg_unit_no IS NULL)
1487 AND (l_qty_rec.acct_no IS NULL)
1488 ) THEN
1489 x_ic_adjs_jnl_row1.acctg_unit_id := NULL;
1490 x_ic_adjs_jnl_row1.acct_id := NULL;
1491 ELSIF ( (l_qty_rec.acctg_unit_no IS NULL)
1492 OR (l_qty_rec.acct_no IS NULL)
1493 ) THEN
1494 FND_MESSAGE.SET_NAME('GMI','GMI_API_CHARGE_ACCT');
1495 FND_MSG_PUB.Add;
1496 RAISE FND_API.G_EXC_ERROR;
1497 ELSE
1498 /* *********************************************************
1499 Jalaj Srivastava
1500 charge accounts are tied to the company of the warehouse
1501 ********************************************************** */
1502 SELECT co_code INTO l_from_whse_co_code
1503 FROM sy_orgn_mst
1504 WHERE orgn_code = (SELECT orgn_code
1505 FROM ic_whse_mst
1506 WHERE whse_code = l_qty_rec.from_whse_code);
1507 gmf_validate_account.get_accu_acct_ids
1508 ( p_co_code => l_from_whse_co_code
1509 ,p_acctg_unit_no => l_qty_rec.acctg_unit_no
1510 ,p_acct_no => l_qty_rec.acct_no
1511 ,p_create_acct => 'Y'
1512 ,x_acctg_unit_id => x_ic_adjs_jnl_row1.acctg_unit_id
1513 ,x_acct_id => x_ic_adjs_jnl_row1.acct_id
1514 ,x_ccid => l_ccid
1515 ,x_status => l_return_status
1516 ,x_errmsg => l_errmsg
1517 );
1518 IF (x_ic_adjs_jnl_row1.acctg_unit_id = -1) THEN
1519 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,substrb(l_errmsg,1,240));
1520 FND_MSG_PUB.Add;
1521 RAISE FND_API.G_EXC_ERROR;
1522 END IF;
1523 END IF;
1524 END IF; --IF (l_qty_rec.txn_type IN ('CRE','ADJ')) THEN
1525
1526
1527 /* If we've reached this far then all is OK. Create the journalling */
1528 /* rows ready to pass back to the calling API. */
1529
1530 x_ic_jrnl_mst_row.journal_id := NULL;
1531 x_ic_jrnl_mst_row.journal_no := NULL;
1532 --BEGIN BUG#1962677 K.RajaSekhar
1533 x_ic_jrnl_mst_row.journal_comment := NULL;
1534 x_ic_jrnl_mst_row.journal_comment := l_qty_rec.journal_comment;
1535 --END BUG#1962677
1536 x_ic_jrnl_mst_row.posting_id := 0;
1537 x_ic_jrnl_mst_row.print_cnt := 0;
1538 --Jalaj Srivastava Bug 2483656
1539 IF (l_qty_rec.journal_ind = 'N') THEN --Immediate txn
1540 x_ic_jrnl_mst_row.posted_ind := 1;
1541 ELSE
1542 x_ic_jrnl_mst_row.posted_ind := 0;
1543 END IF;
1544 x_ic_jrnl_mst_row.orgn_code := l_qty_rec.orgn_code;
1545 x_ic_jrnl_mst_row.creation_date := SYSDATE;
1546 x_ic_jrnl_mst_row.last_update_date := SYSDATE;
1547 -- Bug 1735824
1548 -- Use the user name passed to the API instead of
1549 -- defaulting to the setup user name.
1550 x_ic_jrnl_mst_row.created_by := l_user_id;
1551 x_ic_jrnl_mst_row.last_updated_by := l_user_id;
1552
1553 x_ic_jrnl_mst_row.delete_mark := 0;
1554 x_ic_jrnl_mst_row.text_code := NULL;
1555 x_ic_jrnl_mst_row.in_use := 0;
1556 x_ic_jrnl_mst_row.last_update_login := NULL;
1557 x_ic_jrnl_mst_row.program_application_id := NULL;
1558 x_ic_jrnl_mst_row.program_id := NULL;
1559 x_ic_jrnl_mst_row.program_update_date := NULL;
1560 x_ic_jrnl_mst_row.request_id := NULL;
1561 x_ic_jrnl_mst_row.attribute1 := UPPER(l_qty_rec.attribute1);
1562 x_ic_jrnl_mst_row.attribute2 := UPPER(l_qty_rec.attribute2);
1563 x_ic_jrnl_mst_row.attribute3 := UPPER(l_qty_rec.attribute3);
1564 x_ic_jrnl_mst_row.attribute4 := UPPER(l_qty_rec.attribute4);
1565 x_ic_jrnl_mst_row.attribute5 := UPPER(l_qty_rec.attribute5);
1566 x_ic_jrnl_mst_row.attribute6 := UPPER(l_qty_rec.attribute6);
1567 x_ic_jrnl_mst_row.attribute7 := UPPER(l_qty_rec.attribute7);
1568 x_ic_jrnl_mst_row.attribute8 := UPPER(l_qty_rec.attribute8);
1569 x_ic_jrnl_mst_row.attribute9 := UPPER(l_qty_rec.attribute9);
1570 x_ic_jrnl_mst_row.attribute10 := UPPER(l_qty_rec.attribute10);
1571 x_ic_jrnl_mst_row.attribute11 := UPPER(l_qty_rec.attribute11);
1572 x_ic_jrnl_mst_row.attribute12 := UPPER(l_qty_rec.attribute12);
1573 x_ic_jrnl_mst_row.attribute13 := UPPER(l_qty_rec.attribute13);
1574 x_ic_jrnl_mst_row.attribute14 := UPPER(l_qty_rec.attribute14);
1575 x_ic_jrnl_mst_row.attribute15 := UPPER(l_qty_rec.attribute15);
1576 x_ic_jrnl_mst_row.attribute16 := UPPER(l_qty_rec.attribute16);
1577 x_ic_jrnl_mst_row.attribute17 := UPPER(l_qty_rec.attribute17);
1578 x_ic_jrnl_mst_row.attribute18 := UPPER(l_qty_rec.attribute18);
1579 x_ic_jrnl_mst_row.attribute19 := UPPER(l_qty_rec.attribute19);
1580 x_ic_jrnl_mst_row.attribute20 := UPPER(l_qty_rec.attribute20);
1581 x_ic_jrnl_mst_row.attribute21 := UPPER(l_qty_rec.attribute21);
1582 x_ic_jrnl_mst_row.attribute22 := UPPER(l_qty_rec.attribute22);
1583 x_ic_jrnl_mst_row.attribute23 := UPPER(l_qty_rec.attribute23);
1584 x_ic_jrnl_mst_row.attribute24 := UPPER(l_qty_rec.attribute24);
1585 x_ic_jrnl_mst_row.attribute25 := UPPER(l_qty_rec.attribute25);
1586 x_ic_jrnl_mst_row.attribute26 := UPPER(l_qty_rec.attribute26);
1587 x_ic_jrnl_mst_row.attribute27 := UPPER(l_qty_rec.attribute27);
1588 x_ic_jrnl_mst_row.attribute28 := UPPER(l_qty_rec.attribute28);
1589 x_ic_jrnl_mst_row.attribute29 := UPPER(l_qty_rec.attribute29);
1590 x_ic_jrnl_mst_row.attribute30 := UPPER(l_qty_rec.attribute30);
1591 x_ic_jrnl_mst_row.attribute_category := UPPER(l_qty_rec.attribute_category);
1592
1593 x_ic_adjs_jnl_row1.trans_type := l_trans_code;
1594 x_ic_adjs_jnl_row1.trans_flag := 0;
1595 x_ic_adjs_jnl_row1.doc_id := NULL;
1596 x_ic_adjs_jnl_row1.journal_id := NULL;
1597 IF (l_qty_rec.journal_ind = 'N') THEN --Immediate txn
1598 x_ic_adjs_jnl_row1.completed_ind := 1;
1599 ELSE
1600 x_ic_adjs_jnl_row1.completed_ind := 0;
1601 END IF;
1602 x_ic_adjs_jnl_row1.whse_code := l_qty_rec.from_whse_code;
1603 x_ic_adjs_jnl_row1.reason_code := l_qty_rec.reason_code;
1604 x_ic_adjs_jnl_row1.doc_date := l_qty_rec.trans_date;
1605 x_ic_adjs_jnl_row1.item_id := p_ic_item_mst_row.item_id;
1606 x_ic_adjs_jnl_row1.item_um := l_qty_rec.item_um;
1607 x_ic_adjs_jnl_row1.item_um2 := l_qty_rec.item_um2;
1608 x_ic_adjs_jnl_row1.lot_id := p_ic_lots_mst_row.lot_id;
1609 x_ic_adjs_jnl_row1.location := l_qty_rec.from_location;
1610 IF l_qty_rec.txn_type IN ('CRE','ADJ') OR l_trans_code IN ('STSR','GRDR')
1611 THEN
1612 x_ic_adjs_jnl_row1.qty := NVL(l_qty_rec.trans_qty,0);
1613 x_ic_adjs_jnl_row1.qty2 := l_qty_rec.trans_qty2;
1614 ELSIF l_qty_rec.txn_type = 'TRN' THEN
1615 x_ic_adjs_jnl_row1.qty := NVL(-l_qty_rec.trans_qty,0);
1616 x_ic_adjs_jnl_row1.qty2 := -l_qty_rec.trans_qty2;
1617 /* *************************************************************
1618 Jalaj Srivastava Bug 2635964
1619 For grade transactions, there is no warehouse and location.
1620 The grade change affects the lot at all warehouse/location.
1621 To get onhand we need to sum up the onhands at all the
1622 warehouse/locations where the lot exists.
1623 ************************************************************* */
1624 ELSIF l_trans_code IN ('STSI','GRDI') THEN
1625 x_ic_adjs_jnl_row1.qty := -l_onhand;
1626 x_ic_adjs_jnl_row1.qty2 := -l_onhand2;
1627 END IF;
1628 x_ic_adjs_jnl_row1.line_id := NULL;
1629 x_ic_adjs_jnl_row1.co_code := l_qty_rec.co_code;
1630 x_ic_adjs_jnl_row1.orgn_code := l_qty_rec.orgn_code;
1631 x_ic_adjs_jnl_row1.no_inv := 0; /* NOT NULL on database */
1632 x_ic_adjs_jnl_row1.no_trans := NULL;
1633 x_ic_adjs_jnl_row1.creation_date := SYSDATE;
1634 x_ic_adjs_jnl_row1.last_update_date := SYSDATE;
1635 -- Bug 1735824
1636 -- Use the user name passed to the API instead of
1637 -- defaulting to the setup user name.
1638 x_ic_adjs_jnl_row1.created_by := l_user_id;
1639 x_ic_adjs_jnl_row1.last_updated_by := l_user_id;
1640 x_ic_adjs_jnl_row1.trans_cnt := 1;
1641 x_ic_adjs_jnl_row1.last_update_login := NULL;
1642 x_ic_adjs_jnl_row1.program_application_id := NULL;
1643 x_ic_adjs_jnl_row1.program_id := NULL;
1644 x_ic_adjs_jnl_row1.program_update_date := NULL;
1645 x_ic_adjs_jnl_row1.request_id := NULL;
1646 IF l_qty_rec.txn_type IN ('TRN','STS','GRD')
1647 THEN
1648 x_ic_adjs_jnl_row1.qc_grade := l_original_qc_grade;
1649 x_ic_adjs_jnl_row1.lot_status := l_original_lot_status;
1650 x_ic_adjs_jnl_row1.line_type := -1;
1651 x_ic_adjs_jnl_row2 := x_ic_adjs_jnl_row1;
1652 x_ic_adjs_jnl_row2.qc_grade := l_qty_rec.qc_grade;
1653 x_ic_adjs_jnl_row2.lot_status := l_qty_rec.lot_status;
1654 x_ic_adjs_jnl_row2.line_type := 1;
1655 x_ic_adjs_jnl_row2.location := l_qty_rec.to_location;
1656 x_ic_adjs_jnl_row2.whse_code := l_qty_rec.to_whse_code;
1657 /* *************************************************************
1658 Jalaj Srivastava Bug 2635964
1659 For grade transactions, there is no warehouse and location.
1660 The grade change affects the lot at all warehouse/location.
1661 To get onhand we need to sum up the onhands at all the
1662 warehouse/locations where the lot exists.
1663 ************************************************************* */
1664 x_ic_adjs_jnl_row2.qty := -x_ic_adjs_jnl_row1.qty;
1665 x_ic_adjs_jnl_row2.qty2 := -x_ic_adjs_jnl_row1.qty2;
1666 ELSE
1667 x_ic_adjs_jnl_row1.qc_grade := l_qty_rec.qc_grade;
1668 x_ic_adjs_jnl_row1.lot_status := l_qty_rec.lot_status;
1669 x_ic_adjs_jnl_row1.line_type := 0;
1670 END IF;
1671 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1672 , p_data => x_msg_data
1673 );
1674 x_return_status := FND_API.G_RET_STS_SUCCESS;
1675
1676 EXCEPTION
1677 WHEN FND_API.G_EXC_ERROR THEN
1678 x_return_status := FND_API.G_RET_STS_ERROR;
1679 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1680 , p_data => x_msg_data
1681 );
1682
1683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1685 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1686 , p_data => x_msg_data
1687 );
1688 WHEN OTHERS THEN
1689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1690 /* IF FND_MSG_PUB.check_msg_level */
1691 /* (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) */
1692 /* THEN */
1693
1694 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1695 , 'Validate_Inventory_posting'
1696 );
1697 /* END IF; */
1698 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
1699 , p_data => x_msg_data
1700 );
1701
1702 END Validate_Inventory_Posting;
1703
1704
1705 PROCEDURE Construct_Txn_Rec
1706 ( p_ic_adjs_jnl_row IN ic_adjs_jnl%ROWTYPE
1707 ,x_tran_rec OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
1708 )
1709 IS
1710 BEGIN
1711 x_tran_rec.item_id := p_ic_adjs_jnl_row.item_id;
1712 x_tran_rec.line_id := p_ic_adjs_jnl_row.line_id;
1713 x_tran_rec.co_code := p_ic_adjs_jnl_row.co_code;
1714 x_tran_rec.orgn_code := p_ic_adjs_jnl_row.orgn_code;
1715 x_tran_rec.whse_code := p_ic_adjs_jnl_row.whse_code;
1716 x_tran_rec.lot_id := p_ic_adjs_jnl_row.lot_id;
1717 x_tran_rec.location := p_ic_adjs_jnl_row.location;
1718 x_tran_rec.doc_id := p_ic_adjs_jnl_row.doc_id;
1719 x_tran_rec.doc_type := p_ic_adjs_jnl_row.trans_type;
1720 x_tran_rec.doc_line := p_ic_adjs_jnl_row.doc_line;
1721 x_tran_rec.line_type := p_ic_adjs_jnl_row.line_type;
1722 x_tran_rec.reason_code := p_ic_adjs_jnl_row.reason_code;
1723
1724 --Jalaj Srivastava Bug 1683162
1725 --trans date should be the date entered by the user and not the sysdate
1726
1727 x_tran_rec.trans_date := p_ic_adjs_jnl_row.doc_date;
1728 x_tran_rec.trans_qty := p_ic_adjs_jnl_row.qty;
1729 x_tran_rec.trans_qty2 := p_ic_adjs_jnl_row.qty2;
1730 x_tran_rec.qc_grade := p_ic_adjs_jnl_row.qc_grade;
1731 x_tran_rec.lot_no := NULL;
1732 x_tran_rec.sublot_no := NULL;
1733 x_tran_rec.lot_status := p_ic_adjs_jnl_row.lot_status;
1734 x_tran_rec.trans_stat := NULL;
1735 x_tran_rec.trans_um := p_ic_adjs_jnl_row.item_um;
1736 x_tran_rec.trans_um2 := p_ic_adjs_jnl_row.item_um2;
1737 x_tran_rec.staged_ind := NULL;
1738 x_tran_rec.event_id := NULL;
1739 x_tran_rec.text_code := NULL;
1740 x_tran_rec.user_id := p_ic_adjs_jnl_row.created_by;
1741 x_tran_rec.create_lot_index := NULL;
1742 x_tran_rec.non_inv := NULL;
1743 END construct_txn_rec;
1744
1745 END GMIVQTY;
[Home] [Help]