[Home] [Help]
PACKAGE BODY: APPS.INV_MATERIAL_STATUS_PKG
Source
1 PACKAGE BODY INV_MATERIAL_STATUS_PKG as
2 /* $Header: INVMSPVB.pls 120.18.12020000.3 2012/09/11 10:36:34 rkatoori ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MATERIAL_STATUS_PKG';
5 -- BEGIN SCHANDRU INVERES
6 g_eres_enabled VARCHAR2(3) := NVL(fnd_profile.VALUE('EDR_ERES_ENABLED'), 'N');
7 -- END SCHANDRU INVERES
8 FUNCTION status_assigned(p_status_id IN NUMBER) return Boolean
9 IS
10 count_assigned number := 0;
11 BEGIN
12 -- Check subinventories
13 select 1
14 into count_assigned
15 from dual
16 where exists (select 1
17 from mtl_secondary_inventories
18 where status_id = p_status_id);
19
20 if count_assigned >0 then
21 return TRUE;
22 end if;
23
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 BEGIN
27 -- Check locator
28 select 1
29 into count_assigned
30 from dual
31 where exists (select 1
32 from mtl_item_locations
33 where status_id = p_status_id);
34
35 if count_assigned >0 then
36 return TRUE;
37 end if;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 BEGIN
41 -- Check lot
42 select 1
43 into count_assigned
44 from dual
45 where exists (select 1
46 from mtl_lot_numbers
47 where status_id = p_status_id);
48
49 if count_assigned >0 then
50 return TRUE;
51 end if;
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 BEGIN
55 -- Check serial /* Added first_rows hint for bug 14125147 */
56 select 1
57 into count_assigned
58 from dual
59 where exists (select /*+ first_rows(1) */ 1
60 from mtl_serial_numbers
61 where status_id = p_status_id);
62
63 if count_assigned >0 then
64 return TRUE;
65 end if;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 BEGIN
69 -- Check Onhand -- Bug 6842219
70 select 1
71 into count_assigned
72 from dual
73 where exists (select 1
74 from mtl_onhand_quantities_detail moqd, mtl_parameters mp
75 where moqd.organization_id = mp.organization_id
76 and mp.default_status_id is not null
77 and nvl(moqd.status_id, -9999) = p_status_id
78 and rownum = 1); -- Do we need to add rownum as the query is inside 'exists'.
79
80 if count_assigned >0 then
81 return TRUE;
82 end if;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 return FALSE;
86 END;
87 END;
88 END;
89 END;
90 return FALSE;
91 END;
92
93 Function get_default_locator_status(
94 p_organization_id IN NUMBER,
95 p_sub_code IN VARCHAR2
96 ) return NUMBER IS
97 l_status_id NUMBER;
98 BEGIN
99 SELECT default_loc_status_id
100 INTO l_status_id
101 FROM MTL_SECONDARY_INVENTORIES
102 WHERE organization_id = p_organization_id
103 AND secondary_inventory_name = p_sub_code;
104 return l_status_id;
105 exception
106 when others then
107 return NULL;
108 END get_default_locator_status;
109
110 -- Procedure Initialize_status_rec
111 -- Description
112 -- convert missing value in the input record
113 -- to null or proper default value .
114
115 PROCEDURE Initialize_status_rec(px_status_rec
116 IN OUT NOCOPY INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type )
117 IS
118 BEGIN
119 if px_status_rec.organization_id = fnd_api.g_miss_num THEN
120 px_status_rec.organization_id := NULL;
121 end if;
122 if px_status_rec.inventory_item_id = fnd_api.g_miss_num THEN
123 px_status_rec.inventory_item_id := NULL;
124 end if;
125 if px_status_rec.lot_number = fnd_api.g_miss_char then
126 px_status_rec.lot_number := NULL;
127 end if;
128 if px_status_rec.serial_number = fnd_api.g_miss_char then
129 px_status_rec.serial_number := NULL;
130 end if;
131 if px_status_rec.to_serial_number = fnd_api.g_miss_char then
132 px_status_rec.to_serial_number := NULL;
133 end if;
134 if px_status_rec.update_method = fnd_api.g_miss_num then
135 px_status_rec.update_method := NULL;
136 end if;
137 if px_status_rec.status_id = fnd_api.g_miss_num then
138 px_status_rec.status_id := NULL;
139 end if;
140 if px_status_rec.zone_code = fnd_api.g_miss_char then
141 px_status_rec.zone_code := NULL;
142 end if;
143 if px_status_rec.locator_id = fnd_api.g_miss_num then
144 px_status_rec.locator_id := NULL;
145 end if;
146 if px_status_rec.created_by = fnd_api.g_miss_num then
147 px_status_rec.created_by := FND_GLOBAL.USER_ID;
148 end if;
149 if px_status_rec.last_updated_by = fnd_api.g_miss_num then
150 px_status_rec.last_updated_by := FND_GLOBAL.USER_ID;
151 end if;
152 -- always default the creation date and update date to sysdate
153 -- since we only need to insert this record and never need to
154 -- change it, bug 1912638
155 px_status_rec.creation_date := SYSDATE;
156 px_status_rec.last_update_date := SYSDATE;
157 if px_status_rec.last_update_login = fnd_api.g_miss_num then
158 px_status_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
159 end if;
160
161 if px_status_rec.program_application_id = fnd_api.g_miss_num then
162 px_status_rec.program_application_id := NULL;
163 end if;
164
165 if px_status_rec.program_id = fnd_api.g_miss_num then
166 px_status_rec.program_id := NULL;
167 end if;
168
169 if px_status_rec.attribute_category = fnd_api.g_miss_char then
170 px_status_rec.attribute_category := NULL;
171 end if;
172 if px_status_rec.attribute1 = fnd_api.g_miss_char then
173 px_status_rec.attribute1 := NULL;
174 end if;
175 if px_status_rec.attribute2 = fnd_api.g_miss_char then
176 px_status_rec.attribute2 := NULL;
177 end if;
178 if px_status_rec.attribute3 = fnd_api.g_miss_char then
179 px_status_rec.attribute3 := NULL;
180 end if;
181 if px_status_rec.attribute4 = fnd_api.g_miss_char then
182 px_status_rec.attribute4 := NULL;
183 end if;
184 if px_status_rec.attribute5 = fnd_api.g_miss_char then
185 px_status_rec.attribute5 := NULL;
186 end if;
187 if px_status_rec.attribute6 = fnd_api.g_miss_char then
188 px_status_rec.attribute6 := NULL;
189 end if;
190 if px_status_rec.attribute7 = fnd_api.g_miss_char then
191 px_status_rec.attribute7 := NULL;
192 end if;
193 if px_status_rec.attribute8 = fnd_api.g_miss_char then
194 px_status_rec.attribute8 := NULL;
195 end if;
196 if px_status_rec.attribute9 = fnd_api.g_miss_char then
197 px_status_rec.attribute9 := NULL;
198 end if;
199 if px_status_rec.attribute10 = fnd_api.g_miss_char then
200 px_status_rec.attribute10 := NULL;
201 end if;
202 if px_status_rec.attribute11 = fnd_api.g_miss_char then
203 px_status_rec.attribute11 := NULL;
204 end if;
205 if px_status_rec.attribute12 = fnd_api.g_miss_char then
206 px_status_rec.attribute12 := NULL;
207 end if;
208 if px_status_rec.attribute13 = fnd_api.g_miss_char then
209 px_status_rec.attribute13 := NULL;
210 end if;
211 if px_status_rec.attribute14 = fnd_api.g_miss_char then
212 px_status_rec.attribute14 := NULL;
213 end if;
214 if px_status_rec.attribute15 = fnd_api.g_miss_char then
215 px_status_rec.attribute15 := NULL;
216 end if;
217 if px_status_rec.update_reason_id = fnd_api.g_miss_num then
218 px_status_rec.update_reason_id := NULL;
219 end if;
220
221 if px_status_rec.initial_status_flag = fnd_api.g_miss_char then
222 px_status_rec.initial_status_flag := NULL;
223 end if;
224 if px_status_rec.from_mobile_apps_flag = fnd_api.g_miss_char then
225 px_status_rec.from_mobile_apps_flag := NULL;
226 end if;
227 --BUG 7306729 Quantities should be changed to NULL when the value is g_miss_num
228 if px_status_rec.PRIMARY_ONHAND = fnd_api.g_miss_num then
229 px_status_rec.PRIMARY_ONHAND := NULL;
230 end if;
231
232 if px_status_rec.SECONDARY_ONHAND = fnd_api.g_miss_num then
233 px_status_rec.SECONDARY_ONHAND := NULL;
234 end if;
235 --End of 7306729
236 -- Bug# 1695432 added initial_status_flag,from_mobile_apps_flag columns
237
238 if px_status_rec.GROUP_ID = fnd_api.g_miss_num then
239 px_status_rec.GROUP_ID := NULL; --Bug#11826279
240 end if;
241
242 if px_status_rec.lpn_id = fnd_api.g_miss_num then
243 px_status_rec.lpn_id := NULL; --Bug#11826279
244 end if;
245
246 if px_status_rec.pending_status = fnd_api.g_miss_num then
247 px_status_rec.pending_status := 0; --ERES Deferred
248 end if;
249
250
251 END Initialize_status_rec;
252
253 PROCEDURE Insert_status_history(p_status_rec
254 IN INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type )
255 IS
256 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
257 l_msg_count NUMBER;
258 l_msg_data VARCHAR2(2000);
259 l_label_status varchar2(300);
260 l_return_status varchar2(1);
261 l_to_serial_number varchar2(30):= NULL;
262
263 cursor cur_serial_number is
264 SELECT serial_number
265 FROM MTL_SERIAL_NUMBERS
266 WHERE current_organization_id = p_status_rec.organization_id
267 AND inventory_item_id = p_status_rec.inventory_item_id
268 AND serial_number > p_status_rec.serial_number
269 AND serial_number <= p_status_rec.to_serial_number;
270 l_status_update_id NUMBER := NULL; -- SCHANDRU INVERES
271 -- g_eres_enabled varchar2(1):= 'Y';
272 l_multiple_app NUMBER := 0;
273 l_status_group_id NUMBER := 0;
274
275
276 BEGIN
277 l_multiple_app := NVL(FND_PROFILE.VALUE('INV_ERES_MULTIPLE_SIGNATURE'),0);--ERES Deferred
278 --BEGIN SCHANDRU INVERES
279 Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
280 Into l_status_update_id
281 From dual;
282 -- END SCHANDRU INVERES
283 l_status_rec := p_status_rec;
284
285 --ERES Deferred
286 IF ((l_multiple_app <> 2) AND (l_status_rec.group_id = -999)) THEN
287 Select mtl_onhand_status_group_s.NEXTVAL
288 Into l_status_group_id FROM dual;
289 l_status_rec.group_id := l_status_group_id;
290 END IF;
291 --ERES Deferred
292 INV_MATERIAL_STATUS_PKG.Initialize_status_rec(l_status_rec);
293
294 INSERT INTO MTL_MATERIAL_STATUS_HISTORY
295 (
296 STATUS_UPDATE_ID
297 ,ORGANIZATION_ID
298 ,INVENTORY_ITEM_ID
299 ,LOT_NUMBER
300 ,SERIAL_NUMBER
301 ,UPDATE_METHOD
302 ,STATUS_ID
303 ,ZONE_CODE
304 ,LOCATOR_ID
305 ,LPN_ID ---- Added for # 6633612
306 ,CREATION_DATE
307 ,CREATED_BY
308 ,LAST_UPDATED_BY
309 ,LAST_UPDATE_DATE
310 ,LAST_UPDATE_LOGIN
311 ,PROGRAM_APPLICATION_ID
312 ,PROGRAM_ID
313 ,ATTRIBUTE_CATEGORY
314 ,ATTRIBUTE1
315 ,ATTRIBUTE2
316 ,ATTRIBUTE3
317 ,ATTRIBUTE4
318 ,ATTRIBUTE5
319 ,ATTRIBUTE6
320 ,ATTRIBUTE7
321 ,ATTRIBUTE8
322 ,ATTRIBUTE9
323 ,ATTRIBUTE10
324 ,ATTRIBUTE11
325 ,ATTRIBUTE12
326 ,ATTRIBUTE13
327 ,ATTRIBUTE14
328 ,ATTRIBUTE15
329 ,UPDATE_REASON_ID
330 ,INITIAL_STATUS_FLAG
331 ,FROM_MOBILE_APPS_FLAG
332 -- NSRIVAST, INVCONV , Start
333 ,GRADE_CODE
334 ,PRIMARY_ONHAND
335 ,SECONDARY_ONHAND
336 -- NSRIVAST, INVCONV , End
337 ,GROUP_ID --Bug#11826279
338 ,pending_status --ERES Deferred
339 )
340 VALUES (
341 -- BEGIN SCHANDRU INVERES
342 --MTL_MATERIAL_STATUS_HISTORY_S.nextval
343 l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table.
344 -- END SCHANDRU INVERES
345 l_status_rec.ORGANIZATION_ID
346 ,l_status_rec.INVENTORY_ITEM_ID
347 ,l_status_rec.LOT_NUMBER
348 ,l_status_rec.SERIAL_NUMBER
349 ,l_status_rec.UPDATE_METHOD
350 ,l_status_rec.STATUS_ID
351 ,l_status_rec.ZONE_CODE
352 ,l_status_rec.LOCATOR_ID
353 ,l_status_rec.LPN_ID ---- Added for # 6633612
354 ,l_status_rec.CREATION_DATE
355 ,l_status_rec.CREATED_BY
356 ,l_status_rec.LAST_UPDATED_BY
357 ,l_status_rec.LAST_UPDATE_DATE
358 ,l_status_rec.LAST_UPDATE_LOGIN
359 ,l_status_rec.PROGRAM_APPLICATION_ID
360 ,l_status_rec.PROGRAM_ID
361 ,l_status_rec.ATTRIBUTE_CATEGORY
362 ,l_status_rec.ATTRIBUTE1
363 ,l_status_rec.ATTRIBUTE2
364 ,l_status_rec.ATTRIBUTE3
365 ,l_status_rec.ATTRIBUTE4
366 ,l_status_rec.ATTRIBUTE5
367 ,l_status_rec.ATTRIBUTE6
368 ,l_status_rec.ATTRIBUTE7
369 ,l_status_rec.ATTRIBUTE8
370 ,l_status_rec.ATTRIBUTE9
371 ,l_status_rec.ATTRIBUTE10
372 ,l_status_rec.ATTRIBUTE11
373 ,l_status_rec.ATTRIBUTE12
374 ,l_status_rec.ATTRIBUTE13
375 ,l_status_rec.ATTRIBUTE14
376 ,l_status_rec.ATTRIBUTE15
377 ,l_status_rec.UPDATE_REASON_ID
378 ,l_status_rec.INITIAL_STATUS_FLAG
379 ,l_status_rec.FROM_MOBILE_APPS_FLAG
380 -- NSRIVAST, INVCONV , Start
381 ,l_status_rec.GRADE_CODE
382 ,l_status_rec.PRIMARY_ONHAND
383 ,l_status_rec.SECONDARY_ONHAND
384 -- NSRIVAST, INVCONV , End
385 ,l_status_rec.GROUP_ID --Bug#11826279
386 ,l_status_rec.pending_status --ERES Deferred
387 );
388
389
390
391 -- BEGIN SCHANDRU INVERES
392 --Bug#11826279
393 IF g_eres_enabled <> 'N' THEN
394 IF (l_status_rec.group_id IS NULL) THEN
395 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
396 grade_update_id) values (l_status_update_id, NULL);
397 ELSE
398 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
399 grade_update_id) values (l_status_rec.group_id, NULL);
400 END IF;
401 END IF;
402 -- END SCHANDRU INVERES
403
404
405
406 --Bug# 1695432 added INITIAL_STATUS_FLAG,FROM_MOBILE_APPS_FLAG col
407
408 if p_status_rec.to_serial_number is not null and
409 p_status_rec.serial_number <> p_status_rec.to_serial_number then
410 l_to_serial_number := p_status_rec.to_serial_number;
411 FOR cc IN cur_serial_number LOOP
412 -- BEGIN SCHANDRU INVERES
413 Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
414 Into l_status_update_id
415 From dual;
416
417 -- END SCHANDRU INVERES
418 INSERT INTO MTL_MATERIAL_STATUS_HISTORY
419 (
420 STATUS_UPDATE_ID
421 ,ORGANIZATION_ID
422 ,INVENTORY_ITEM_ID
423 ,LOT_NUMBER
424 ,SERIAL_NUMBER
425 ,UPDATE_METHOD
426 ,STATUS_ID
427 ,ZONE_CODE
428 ,LOCATOR_ID
429 ,LPN_ID ---- Added for # 6633612
430 ,CREATION_DATE
431 ,CREATED_BY
432 ,LAST_UPDATED_BY
433 ,LAST_UPDATE_DATE
434 ,LAST_UPDATE_LOGIN
435 ,PROGRAM_APPLICATION_ID
436 ,PROGRAM_ID
437 ,ATTRIBUTE_CATEGORY
438 ,ATTRIBUTE1
439 ,ATTRIBUTE2
440 ,ATTRIBUTE3
441 ,ATTRIBUTE4
442 ,ATTRIBUTE5
443 ,ATTRIBUTE6
444 ,ATTRIBUTE7
445 ,ATTRIBUTE8
446 ,ATTRIBUTE9
447 ,ATTRIBUTE10
448 ,ATTRIBUTE11
449 ,ATTRIBUTE12
450 ,ATTRIBUTE13
451 ,ATTRIBUTE14
452 ,ATTRIBUTE15
453 ,UPDATE_REASON_ID
454 ,INITIAL_STATUS_FLAG
455 ,FROM_MOBILE_APPS_FLAG
456 -- NSRIVAST, INVCONV , Start
457 ,GRADE_CODE
458 ,PRIMARY_ONHAND
459 ,SECONDARY_ONHAND
460 -- NSRIVAST, INVCONV , End
461 )
462 VALUES (
463 --BEGIN SCHANDRU INVERES
464 --MTL_MATERIAL_STATUS_HISTORY_S.nextval
465 l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table
466 -- END SCHANDRU INVERES
467 l_status_rec.ORGANIZATION_ID
468 ,l_status_rec.INVENTORY_ITEM_ID
469 ,l_status_rec.LOT_NUMBER
470 ,cc.serial_number
471 ,l_status_rec.UPDATE_METHOD
472 ,l_status_rec.STATUS_ID
473 ,l_status_rec.ZONE_CODE
474 ,l_status_rec.LOCATOR_ID
475 ,l_status_rec.LPN_ID -- Added for # 6633612
476 ,l_status_rec.CREATION_DATE
477 ,l_status_rec.CREATED_BY
478 ,l_status_rec.LAST_UPDATED_BY
479 ,l_status_rec.LAST_UPDATE_DATE
480 ,l_status_rec.LAST_UPDATE_LOGIN
481 ,l_status_rec.PROGRAM_APPLICATION_ID
482 ,l_status_rec.PROGRAM_ID
483 ,l_status_rec.ATTRIBUTE_CATEGORY
484 ,l_status_rec.ATTRIBUTE1
485 ,l_status_rec.ATTRIBUTE2
486 ,l_status_rec.ATTRIBUTE3
487 ,l_status_rec.ATTRIBUTE4
488 ,l_status_rec.ATTRIBUTE5
489 ,l_status_rec.ATTRIBUTE6
490 ,l_status_rec.ATTRIBUTE7
491 ,l_status_rec.ATTRIBUTE8
492 ,l_status_rec.ATTRIBUTE9
493 ,l_status_rec.ATTRIBUTE10
494 ,l_status_rec.ATTRIBUTE11
495 ,l_status_rec.ATTRIBUTE12
496 ,l_status_rec.ATTRIBUTE13
497 ,l_status_rec.ATTRIBUTE14
498 ,l_status_rec.ATTRIBUTE15
499 ,l_status_rec.UPDATE_REASON_ID
500 ,l_status_rec.INITIAL_STATUS_FLAG
501 ,l_status_rec.FROM_MOBILE_APPS_FLAG
502 -- NSRIVAST, INVCONV , Start
503 ,l_status_rec.GRADE_CODE
504 ,l_status_rec.PRIMARY_ONHAND
505 ,l_status_rec.SECONDARY_ONHAND
506 -- NSRIVAST, INVCONV , End
507 );
508 --BEGIN SCHANDRU INVERES
509 IF g_eres_enabled <> 'N' THEN
510 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
511 grade_update_id) values (l_status_update_id, NULL);
512 END IF;
513 -- END SCHANDRU INVERES
514
515
516 --Bug# 1695432 added INITIAL_STATUS_FLAG,FROM_MOBILE_APPS_FLAG col
517
518 END LOOP;
519 end if;
520
521 -- call print_label to print the label
522 /* inv_label.print_label(
523 x_return_status => l_return_status,
524 x_msg_count => l_msg_count,
525 x_msg_data => l_msg_data,
526 x_label_status => l_label_status,
527 p_api_version => 1.0,
528 p_print_mode => 2,
529 p_business_flow_code => 10,
530 p_input_param_rec => l_input_param_rec); */
531 -- changed to call INV_LABEL.PRINT_LABEL_MANUAL_WRAP to pass serial range
532 INV_LABEL.PRINT_LABEL_MANUAL_WRAP(
533 x_return_status => l_return_status,
534 x_msg_count => l_msg_count,
535 x_msg_data => l_msg_data,
536 x_label_status => l_label_status,
537 p_business_flow_code => 10,
538 p_organization_id => l_status_rec.organization_id,
539 p_subinventory_code => l_status_rec.zone_code,
540 p_locator_id => l_status_rec.locator_id,
541 p_inventory_item_id => l_status_rec.inventory_item_id,
542 p_lot_number => l_status_rec.lot_number,
543 p_fm_serial_number => l_status_rec.serial_number,
544 p_to_serial_number => l_to_serial_number);
545
546 IF l_return_status <> fnd_api.g_ret_sts_success THEN
547 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL');
548 FND_MSG_PUB.ADD;
549 END IF;
550
551
552 EXCEPTION
553
554 WHEN FND_API.G_EXC_ERROR THEN
555
556 Raise FND_API.G_EXC_ERROR;
557
558 WHEN OTHERS THEN
559
560 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 FND_MSG_PUB.Add_Exc_Msg
563 ( G_PKG_NAME
564 , 'Insert_Status_history'
565 );
566 END IF;
567
568
569 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
570
571 END Insert_status_history;
572
573 /* bug 11806801 */
574 FUNCTION validate_mtstatus2(
575 p_old_status_id mtl_material_statuses.status_id%TYPE,
576 p_new_status_id mtl_material_statuses.status_id%TYPE ,
577 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
578 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
579 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
580 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
581 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE
582 )RETURN CHAR AS
583
584 l_return_status BOOLEAN;
585
586 BEGIN
587 l_return_status := validate_mtstatus(
588 p_old_status_id,
589 p_new_status_id,
590 p_subinventory_code,
591 p_locator_id,
592 p_organization_id,
593 p_inventory_item_id,
594 p_lot_number);
595
596 if (l_return_status) then
597 RETURN 'Y';
598 else
599 RETURN 'N';
600 end if;
601
602 EXCEPTION
603 when others then
604 return 'Y';
605 END;
606
607
608 FUNCTION validate_mtstatus(
609 p_old_status_id mtl_material_statuses.status_id%TYPE,
610 p_new_status_id mtl_material_statuses.status_id%TYPE ,
611 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
612 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
613 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
614 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE
615 )RETURN BOOLEAN AS
616
617 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE := NULL;
618 l_return_status BOOLEAN;
619
620 BEGIN
621
622 inv_trx_util_pub.TRACE('inside non-overloaded validate_mtstatus ', 'INV_MATERIAL_STATUS_PKG', 14);
623
624 l_return_status := validate_mtstatus(
625 p_old_status_id,
626 p_new_status_id,
627 p_subinventory_code,
628 p_locator_id,
629 p_organization_id,
630 p_inventory_item_id,
631 p_lot_number);
632
633 if (l_return_status) then
634 inv_trx_util_pub.TRACE('validate_mtstatus: returning true', 'INV_MATERIAL_STATUS_PKG', 14);
635 else
636 inv_trx_util_pub.TRACE('validate_mtstatus: returning false', 'INV_MATERIAL_STATUS_PKG', 14);
637 end if;
638
639 return l_return_status;
640
641 EXCEPTION
642 when others then
643 return TRUE;
644 END;
645
646
647 /* Bug 6837479 */
648 FUNCTION validate_mtstatus(
649 p_old_status_id mtl_material_statuses.status_id%TYPE,
650 p_new_status_id mtl_material_statuses.status_id%TYPE ,
651 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
652 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
653 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
654 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
655 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE,
656 p_lpn_id mtl_onhand_quantities_detail.lpn_id%TYPE DEFAULT NULL -- Bug 14240066
657 )RETURN BOOLEAN AS
658
659 l_return_status BOOLEAN;
660 l_dummy_param NUMBER := 1;
661
662 BEGIN
663
664 inv_trx_util_pub.TRACE('inside 1st overloaded validate_mtstatus ', 'INV_MATERIAL_STATUS_PKG', 14);
665
666 l_return_status := validate_mtstatus(
667 p_old_status_id,
668 p_new_status_id,
669 p_subinventory_code,
670 p_locator_id,
671 p_organization_id,
672 p_inventory_item_id,
673 p_lot_number,
674 l_dummy_param,
675 p_lpn_id); -- Bug 14240066
676
677 if (l_return_status) then
678 inv_trx_util_pub.TRACE('1st validate_mtstatus: returning true', 'INV_MATERIAL_STATUS_PKG', 14);
679 else
680 inv_trx_util_pub.TRACE('1st validate_mtstatus: returning false', 'INV_MATERIAL_STATUS_PKG', 14);
681 end if;
682
683 if (NOT l_return_status) then
684 -- ER Change: Calling the hook
685 inv_trx_util_pub.TRACE('validate_mtstatus: Calling the hook', 'INV_MATERIAL_STATUS_PKG', 14);
686 inv_material_status_hook.validate_rsv_matstatus(p_old_status_id,
687 p_new_status_id,
688 p_subinventory_code,
689 p_locator_id,
690 p_organization_id,
691 p_inventory_item_id,
692 p_lot_number,
693 l_return_status);
694
695 if (l_return_status) then
696 inv_trx_util_pub.TRACE('Hook returned true', 'INV_MATERIAL_STATUS_PKG', 14);
697 else
698 inv_trx_util_pub.TRACE('Hook returned false', 'INV_MATERIAL_STATUS_PKG', 14);
699 end if;
700
701 end if;
702
703 return l_return_status;
704
705 EXCEPTION
706 when others then
707 inv_trx_util_pub.TRACE('Exception was raised', 'INV_MATERIAL_STATUS_PKG', 14);
708 return TRUE;
709 END;
710
711 /* Bug 6837479: Modified the function to properly check for existing
712 * reservations
713 */
714 --INVCONV kkillams
715 FUNCTION validate_mtstatus(
716 p_old_status_id mtl_material_statuses.status_id%TYPE,
717 p_new_status_id mtl_material_statuses.status_id%TYPE ,
718 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
719 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
720 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
721 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
722 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE, /* bug 6837479 */
723 p_dummy_param NUMBER,
724 p_lpn_id mtl_onhand_quantities_detail.lpn_id%TYPE DEFAULT NULL -- Bug 14240066
725 )RETURN BOOLEAN AS
726 CURSOR cur_mt_status (cp_old_status_id mtl_material_statuses.status_code%TYPE,
727 cp_new_status_id mtl_material_statuses.status_code%TYPE) IS
728 SELECT 1 FROM mtl_material_statuses mts1,
729 mtl_material_statuses mts2
730 WHERE cp_old_status_id <> cp_new_status_id
731 AND mts1.status_id = cp_old_status_id
732 AND mts1.reservable_type = 1
733 AND mts2.status_id = cp_new_status_id
734 AND mts2.reservable_type <> mts1.reservable_type;
735
736 CURSOR c_subinv_items(cp_organization_id mtl_onhand_quantities_detail.organization_id%TYPE,
737 cp_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
738 cp_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE) IS
739 SELECT 1 FROM mtl_onhand_quantities_detail moq
740 WHERE organization_id = cp_organization_id
741 AND subinventory_code = cp_subinventory_code
742 AND EXISTS
743 (SELECT 1
744 FROM mtl_reservations mr
745 WHERE mr.inventory_item_id = moq.inventory_item_id
746 AND mr.organization_id = moq.organization_id
747 /* Bug 8674685
748 AND ( (mr.inventory_item_id = cp_inventory_item_id )
749 OR cp_inventory_item_id IS NULL
750 )
751 */
752 AND ( (mr.subinventory_code = cp_subinventory_code )
753 OR mr.subinventory_code IS NULL
754 )
755 )
756 AND ROWNUM = 1;
757
758 CURSOR c_locator_items(cp_organization_id mtl_onhand_quantities_detail.organization_id%TYPE,
759 cp_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
760 cp_locator_id mtl_onhand_quantities_detail.locator_id%TYPE) IS
761 SELECT 1 FROM mtl_onhand_quantities_detail moq
762 WHERE organization_id = cp_organization_id
763 AND locator_id = cp_locator_id
764 AND EXISTS
765 (SELECT 1
766 FROM mtl_reservations mr
767 WHERE mr.inventory_item_id = moq.inventory_item_id
768 AND mr.organization_id = moq.organization_id
769 /* Bug 8674685
770 AND ( (mr.inventory_item_id = cp_inventory_item_id )
771 OR cp_inventory_item_id IS NULL
772 )
773 */
774 AND ( (mr.locator_id = cp_locator_id )
775 OR ( (mr.locator_id IS NULL
776 AND mr.subinventory_code = moq.subinventory_code
777 )
778 OR mr.subinventory_code IS NULL
779 )
780 )
781 )
782 AND ROWNUM = 1;
783
784 CURSOR c_lot_items( cp_organization_id mtl_reservations.organization_id%TYPE,
785 cp_inventory_item_id mtl_reservations. inventory_item_id %TYPE,
786 cp_lot_number mtl_onhand_quantities_detail.lot_number%TYPE) IS
787 SELECT 1 FROM mtl_onhand_quantities_detail moq
788 WHERE organization_id = cp_organization_id
789 AND (inventory_item_id = cp_inventory_item_id OR cp_inventory_item_id IS NULL)
790 AND lot_number = cp_lot_number
791 AND EXISTS
792 (SELECT 1
793 FROM mtl_reservations mr
794 WHERE mr.inventory_item_id = moq.inventory_item_id
795 AND mr.organization_id = moq.organization_id
796 AND ( (mr.inventory_item_id = cp_inventory_item_id )
797 OR cp_inventory_item_id IS NULL
798 )
799 AND ( (mr.lot_number = cp_lot_number )
800 OR mr.lot_number IS NULL
801 )
802 )
803 AND ROWNUM = 1;
804
805 CURSOR c_items_reserv( cp_organization_id mtl_reservations.organization_id%TYPE,
806 cp_inventory_item_id mtl_reservations. inventory_item_id%TYPE) IS
807 SELECT 1 FROM mtl_reservations
808 WHERE inventory_item_id = cp_inventory_item_id
809 AND organization_id = cp_organization_id
810 AND ROWNUM = 1;
811
812 /*Added new cursor for Bug 12430080 */
813 CURSOR c_onhand_items( cp_organization_id mtl_onhand_quantities_detail.organization_id%TYPE,
814 cp_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
815 cp_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
816 cp_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
817 cp_lot_number mtl_onhand_quantities_detail.lot_number%TYPE,
818 cp_lpn_id mtl_onhand_quantities_detail.lpn_id%TYPE) IS -- Bug 14240066
819
820 SELECT 1 FROM mtl_onhand_quantities_detail moq
821 WHERE organization_id = cp_organization_id
822 AND (inventory_item_id = cp_inventory_item_id OR cp_inventory_item_id IS NULL)
823 AND (subinventory_code = cp_subinventory_code OR cp_subinventory_code IS NULL)
824 AND (locator_id = cp_locator_id OR cp_locator_id IS NULL)
825 AND (lot_number = cp_lot_number or cp_lot_number is null)
826 AND (lpn_id = cp_lpn_id OR cp_lpn_id IS NULL) -- Bug 14240066
827 AND EXISTS
828 (SELECT 1
829 FROM mtl_reservations mr
830 WHERE mr.inventory_item_id = moq.inventory_item_id
831 AND mr.organization_id = moq.organization_id
832 AND ( (mr.inventory_item_id = cp_inventory_item_id )
833 OR cp_inventory_item_id IS NULL
834 )
835 AND ( ( ( mr.subinventory_code = cp_subinventory_code ) OR
836 mr.subinventory_code IS NULL ) OR cp_subinventory_code IS NULL
837 )
838 AND ( ( ( mr.locator_id = cp_locator_id ) OR
839 mr.locator_id IS NULL ) OR cp_locator_id IS NULL
840 )
841 AND ( ( ( mr.lot_number = cp_lot_number ) OR
842 mr.lot_number IS NULL ) OR cp_lot_number IS NULL
843 )
844 AND ( ( ( mr.lpn_id = cp_lpn_id ) OR -- Bug 14240066
845 mr.lpn_id IS NULL ) OR cp_lpn_id IS NULL
846 )
847 )
848 AND ROWNUM = 1;
849
850
851 l_dummy NUMBER;
852 l_default_status_id NUMBER; /*bug 12430080 */
853
854 BEGIN
855
856 inv_trx_util_pub.TRACE('inside 2nd overloaded validate_mtstatus ', 'INV_MATERIAL_STATUS_PKG', 14);
857 inv_trx_util_pub.TRACE('validate_mtstatus: old status id: '||p_old_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
858 inv_trx_util_pub.TRACE('validate_mtstatus: new status id: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
859 inv_trx_util_pub.TRACE('validate_mtstatus: organization id: '||p_organization_id, 'INV_MATERIAL_STATUS_PKG', 14);
860 inv_trx_util_pub.TRACE('validate_mtstatus: inventory item id: '||p_inventory_item_id, 'INV_MATERIAL_STATUS_PKG', 14);
861 inv_trx_util_pub.TRACE('validate_mtstatus: subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
862 inv_trx_util_pub.TRACE('validate_mtstatus: locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
863 inv_trx_util_pub.TRACE('validate_mtstatus: lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
864 inv_trx_util_pub.TRACE('validate_mtstatus: dummy parameter: '||p_dummy_param, 'INV_MATERIAL_STATUS_PKG', 14);
865 inv_trx_util_pub.TRACE('validate_mtstatus: lpn_id: '||p_lpn_id, 'INV_MATERIAL_STATUS_PKG', 14); -- Bug 14240066
866
867 OPEN cur_mt_status(p_old_status_id,p_new_status_id);
868 FETCH cur_mt_status INTO l_dummy;
869 IF cur_mt_status%NOTFOUND THEN
870 CLOSE cur_mt_status;
871
872 inv_trx_util_pub.TRACE('validate_mtstatus: New status also allows reservation: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
873
874 RETURN TRUE;
875 END IF; --cur_mt_status
876 CLOSE cur_mt_status;
877
878 inv_trx_util_pub.TRACE('validate_mtstatus: New status does not allow reservation: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
879
880 /* Validating the organization material status for bug 1243008 */
881 SELECT default_status_id INTO l_default_status_id
882 FROM mtl_parameters
883 WHERE organization_id = p_organization_id;
884
885 IF l_default_status_id IS NOT NULL THEN
886 inv_trx_util_pub.TRACE('validate_mtstatus: Organization is onhand status enabled:', 'INV_MATERIAL_STATUS_PKG', 14);
887
888 OPEN c_onhand_items(p_organization_id,p_inventory_item_id,p_subinventory_code,p_locator_id,p_lot_number,p_lpn_id); -- Bug 14240066
889 FETCH c_onhand_items INTO l_dummy;
890 IF c_onhand_items%FOUND THEN
891 CLOSE c_onhand_items;
892
893 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the Onhand row: ', 'INV_MATERIAL_STATUS_PKG', 14);
894
895 RETURN FALSE;
896 END IF;
897 CLOSE c_onhand_items;
898 Else
899 inv_trx_util_pub.TRACE('validate_mtstatus: Organization is Non onhand status enabled:', 'INV_MATERIAL_STATUS_PKG', 14);
900
901 IF (p_lot_number IS NOT NULL ) THEN
902 inv_trx_util_pub.TRACE('validate_mtstatus: lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
903 OPEN c_lot_items(p_organization_id,p_inventory_item_id,p_lot_number);
904 FETCH c_lot_items INTO l_dummy;
905 IF c_lot_items%FOUND THEN
906 CLOSE c_lot_items;
907
908 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
909
910 RETURN FALSE;
911 END IF;
912 CLOSE c_lot_items;
913
914 ELSIF ( p_locator_id IS NOT NULL) THEN
915 inv_trx_util_pub.TRACE('validate_mtstatus: locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
916 OPEN c_locator_items(p_organization_id,p_inventory_item_id,p_locator_id);
917 FETCH c_locator_items INTO l_dummy;
918 IF c_locator_items%FOUND THEN
919 CLOSE c_locator_items;
920
921 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
922
923 RETURN FALSE;
924 END IF;
925 CLOSE c_locator_items;
926
927 --If api is called from subinventory/locator form.
928 ELSIF (P_subinventory_code IS NOT NULL ) THEN
929 inv_trx_util_pub.TRACE('validate_mtstatus: subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
930 -- Bug 6829224: Passing item_id to the cursor
931 OPEN c_subinv_items(p_organization_id,p_inventory_item_id,p_subinventory_code);
932 FETCH c_subinv_items INTO l_dummy;
933 IF c_subinv_items%FOUND THEN
934 CLOSE c_subinv_items;
935
936 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
937
938 RETURN FALSE;
939 END IF;
940 CLOSE c_subinv_items;
941
942 ELSE
943 inv_trx_util_pub.TRACE('validate_mtstatus: checking reservatios only for the item', 'INV_MATERIAL_STATUS_PKG', 14);
944 OPEN c_items_reserv(p_organization_id,p_inventory_item_id);
945 FETCH c_items_reserv INTO l_dummy;
946 IF c_items_reserv%FOUND THEN
947 CLOSE c_items_reserv;
948
949 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the item: '||p_inventory_item_id, 'INV_MATERIAL_STATUS_PKG', 14);
950
951 RETURN FALSE;
952 END IF; --c_items_reserv
953 CLOSE c_items_reserv;
954 END IF; --P_subinventory_code IS NOT NULL
955 END IF; --l_default_status_id IS NOT NULL
956
957 inv_trx_util_pub.TRACE('2nd overloaded validate_mtstatus: returning true', 'INV_MATERIAL_STATUS_PKG', 14);
958
959 RETURN TRUE;
960
961 EXCEPTION
962 when others then
963 return TRUE;
964
965 END validate_mtstatus;
966
967 PROCEDURE SET_MS_FLAGS(
968 p_status_id MTL_MATERIAL_STATUSES.STATUS_ID%TYPE
969 ,p_org_id MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID%TYPE
970 ,p_inventory_item_id MTL_LOT_NUMBERS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
971 ,p_secondary_inventory_name MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE DEFAULT NULL
972 ,p_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE DEFAULT NULL
973 ,p_inventory_location_id MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE DEFAULT NULL
974 ,p_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE DEFAULT NULL
975 ) AS
976 CURSOR cur_ms IS SELECT inventory_atp_code
977 ,reservable_type
978 ,availability_type FROM mtl_material_statuses
979 WHERE status_id = p_status_id;
980 rec_ms cur_ms%ROWTYPE;
981 BEGIN
982 OPEN cur_ms;
983 FETCH cur_ms INTO rec_ms;
984 CLOSE cur_ms;
985 IF p_lot_number IS NOT NULL THEN
986 UPDATE mtl_lot_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
987 availability_type =rec_ms.reservable_type,
988 reservable_type =rec_ms.availability_type
989 WHERE organization_id = p_org_id
990 AND lot_number = p_lot_number
991 AND inventory_item_id = p_inventory_item_id;
992 ELSIF p_serial_number IS NOT NULL THEN
993
994 /* Bug#4560805 The columns inventory_atp_code,availability_type and reservable_type are not a part of
995 mtl_serial_numbers. Hence commenting the UPDATE statement */
996
997 /*
998 UPDATE mtl_serial_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
999 availability_type =rec_ms.reservable_type,
1000 reservable_type =rec_ms.availability_type
1001 WHERE current_organization_id = p_org_id
1002 AND serial_number = p_serial_number
1003 AND inventory_item_id = p_inventory_item_id;
1004 */
1005 NULL;
1006 ELSIF p_inventory_location_id IS NOT NULL THEN
1007 UPDATE MTL_ITEM_LOCATIONS SET inventory_atp_code =rec_ms.inventory_atp_code,
1008 availability_type =rec_ms.reservable_type,
1009 reservable_type =rec_ms.availability_type
1010 WHERE organization_id = p_org_id
1011 AND inventory_location_id = p_inventory_location_id;
1012 ELSIF p_secondary_inventory_name IS NOT NULL THEN
1013 UPDATE mtl_secondary_inventories SET inventory_atp_code =rec_ms.inventory_atp_code,
1014 availability_type =rec_ms.reservable_type,
1015 reservable_type =rec_ms.availability_type
1016 WHERE organization_id = p_org_id
1017 AND secondary_inventory_name =p_secondary_inventory_name;
1018 END IF;
1019 END SET_MS_FLAGS;
1020 --END INVCONV kkillams
1021
1022 END INV_MATERIAL_STATUS_PKG;