[Home] [Help]
PACKAGE BODY: APPS.INV_MATERIAL_STATUS_PKG
Source
1 PACKAGE BODY INV_MATERIAL_STATUS_PKG as
2 /* $Header: INVMSPVB.pls 120.9.12010000.2 2008/11/12 06:37:50 ksivasa 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
56 select 1
57 into count_assigned
58 from dual
59 where exists (select 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
237 -- Bug# 1695432 added initial_status_flag,from_mobile_apps_flag columns
238
239
240 END Initialize_status_rec;
241
242 PROCEDURE Insert_status_history(p_status_rec
243 IN INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type )
244 IS
245 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
246 l_msg_count NUMBER;
247 l_msg_data VARCHAR2(2000);
248 l_label_status varchar2(300);
249 l_return_status varchar2(1);
250 l_to_serial_number varchar2(30):= NULL;
251
252 cursor cur_serial_number is
253 SELECT serial_number
254 FROM MTL_SERIAL_NUMBERS
255 WHERE current_organization_id = p_status_rec.organization_id
256 AND inventory_item_id = p_status_rec.inventory_item_id
257 AND serial_number > p_status_rec.serial_number
258 AND serial_number <= p_status_rec.to_serial_number;
259 l_status_update_id NUMBER := NULL; -- SCHANDRU INVERES
260 -- g_eres_enabled varchar2(1):= 'Y';
261
262 BEGIN
263 --BEGIN SCHANDRU INVERES
264 Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
265 Into l_status_update_id
266 From dual;
267 -- END SCHANDRU INVERES
268 l_status_rec := p_status_rec;
269 INV_MATERIAL_STATUS_PKG.Initialize_status_rec(l_status_rec);
270
271 INSERT INTO MTL_MATERIAL_STATUS_HISTORY
272 (
273 STATUS_UPDATE_ID
274 ,ORGANIZATION_ID
275 ,INVENTORY_ITEM_ID
276 ,LOT_NUMBER
277 ,SERIAL_NUMBER
278 ,UPDATE_METHOD
279 ,STATUS_ID
280 ,ZONE_CODE
281 ,LOCATOR_ID
282 ,LPN_ID ---- Added for # 6633612
283 ,CREATION_DATE
284 ,CREATED_BY
285 ,LAST_UPDATED_BY
286 ,LAST_UPDATE_DATE
287 ,LAST_UPDATE_LOGIN
288 ,PROGRAM_APPLICATION_ID
289 ,PROGRAM_ID
290 ,ATTRIBUTE_CATEGORY
291 ,ATTRIBUTE1
292 ,ATTRIBUTE2
293 ,ATTRIBUTE3
294 ,ATTRIBUTE4
295 ,ATTRIBUTE5
296 ,ATTRIBUTE6
297 ,ATTRIBUTE7
298 ,ATTRIBUTE8
299 ,ATTRIBUTE9
300 ,ATTRIBUTE10
301 ,ATTRIBUTE11
302 ,ATTRIBUTE12
303 ,ATTRIBUTE13
304 ,ATTRIBUTE14
305 ,ATTRIBUTE15
306 ,UPDATE_REASON_ID
307 ,INITIAL_STATUS_FLAG
308 ,FROM_MOBILE_APPS_FLAG
309 -- NSRIVAST, INVCONV , Start
310 ,GRADE_CODE
311 ,PRIMARY_ONHAND
312 ,SECONDARY_ONHAND
313 -- NSRIVAST, INVCONV , End
314 )
315 VALUES (
316 -- BEGIN SCHANDRU INVERES
317 --MTL_MATERIAL_STATUS_HISTORY_S.nextval
318 l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table.
319 -- END SCHANDRU INVERES
320 l_status_rec.ORGANIZATION_ID
321 ,l_status_rec.INVENTORY_ITEM_ID
322 ,l_status_rec.LOT_NUMBER
323 ,l_status_rec.SERIAL_NUMBER
324 ,l_status_rec.UPDATE_METHOD
325 ,l_status_rec.STATUS_ID
326 ,l_status_rec.ZONE_CODE
327 ,l_status_rec.LOCATOR_ID
328 ,l_status_rec.LPN_ID ---- Added for # 6633612
329 ,l_status_rec.CREATION_DATE
330 ,l_status_rec.CREATED_BY
331 ,l_status_rec.LAST_UPDATED_BY
332 ,l_status_rec.LAST_UPDATE_DATE
333 ,l_status_rec.LAST_UPDATE_LOGIN
334 ,l_status_rec.PROGRAM_APPLICATION_ID
335 ,l_status_rec.PROGRAM_ID
336 ,l_status_rec.ATTRIBUTE_CATEGORY
337 ,l_status_rec.ATTRIBUTE1
338 ,l_status_rec.ATTRIBUTE2
339 ,l_status_rec.ATTRIBUTE3
340 ,l_status_rec.ATTRIBUTE4
341 ,l_status_rec.ATTRIBUTE5
342 ,l_status_rec.ATTRIBUTE6
343 ,l_status_rec.ATTRIBUTE7
344 ,l_status_rec.ATTRIBUTE8
345 ,l_status_rec.ATTRIBUTE9
346 ,l_status_rec.ATTRIBUTE10
347 ,l_status_rec.ATTRIBUTE11
348 ,l_status_rec.ATTRIBUTE12
349 ,l_status_rec.ATTRIBUTE13
350 ,l_status_rec.ATTRIBUTE14
351 ,l_status_rec.ATTRIBUTE15
352 ,l_status_rec.UPDATE_REASON_ID
353 ,l_status_rec.INITIAL_STATUS_FLAG
354 ,l_status_rec.FROM_MOBILE_APPS_FLAG
355 -- NSRIVAST, INVCONV , Start
356 ,l_status_rec.GRADE_CODE
357 ,l_status_rec.PRIMARY_ONHAND
358 ,l_status_rec.SECONDARY_ONHAND
359 -- NSRIVAST, INVCONV , End
360 );
361
362
363 -- BEGIN SCHANDRU INVERES
364 IF g_eres_enabled <> 'N' THEN
365 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
366 grade_update_id) values (l_status_update_id, NULL);
367 END IF;
368 -- END SCHANDRU INVERES
369
370
371 --Bug# 1695432 added INITIAL_STATUS_FLAG,FROM_MOBILE_APPS_FLAG col
372
373 if p_status_rec.to_serial_number is not null and
374 p_status_rec.serial_number <> p_status_rec.to_serial_number then
375 l_to_serial_number := p_status_rec.to_serial_number;
376 FOR cc IN cur_serial_number LOOP
377 -- BEGIN SCHANDRU INVERES
378 Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
379 Into l_status_update_id
380 From dual;
381
382 -- END SCHANDRU INVERES
383 INSERT INTO MTL_MATERIAL_STATUS_HISTORY
384 (
385 STATUS_UPDATE_ID
386 ,ORGANIZATION_ID
387 ,INVENTORY_ITEM_ID
388 ,LOT_NUMBER
389 ,SERIAL_NUMBER
390 ,UPDATE_METHOD
391 ,STATUS_ID
392 ,ZONE_CODE
393 ,LOCATOR_ID
394 ,LPN_ID ---- Added for # 6633612
395 ,CREATION_DATE
396 ,CREATED_BY
397 ,LAST_UPDATED_BY
398 ,LAST_UPDATE_DATE
399 ,LAST_UPDATE_LOGIN
400 ,PROGRAM_APPLICATION_ID
401 ,PROGRAM_ID
402 ,ATTRIBUTE_CATEGORY
403 ,ATTRIBUTE1
404 ,ATTRIBUTE2
405 ,ATTRIBUTE3
406 ,ATTRIBUTE4
407 ,ATTRIBUTE5
408 ,ATTRIBUTE6
409 ,ATTRIBUTE7
410 ,ATTRIBUTE8
411 ,ATTRIBUTE9
412 ,ATTRIBUTE10
413 ,ATTRIBUTE11
414 ,ATTRIBUTE12
415 ,ATTRIBUTE13
416 ,ATTRIBUTE14
417 ,ATTRIBUTE15
418 ,UPDATE_REASON_ID
419 ,INITIAL_STATUS_FLAG
420 ,FROM_MOBILE_APPS_FLAG
421 -- NSRIVAST, INVCONV , Start
422 ,GRADE_CODE
423 ,PRIMARY_ONHAND
424 ,SECONDARY_ONHAND
425 -- NSRIVAST, INVCONV , End
426 )
427 VALUES (
428 --BEGIN SCHANDRU INVERES
429 --MTL_MATERIAL_STATUS_HISTORY_S.nextval
430 l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table
431 -- END SCHANDRU INVERES
432 l_status_rec.ORGANIZATION_ID
433 ,l_status_rec.INVENTORY_ITEM_ID
434 ,l_status_rec.LOT_NUMBER
435 ,cc.serial_number
436 ,l_status_rec.UPDATE_METHOD
437 ,l_status_rec.STATUS_ID
438 ,l_status_rec.ZONE_CODE
439 ,l_status_rec.LOCATOR_ID
443 ,l_status_rec.LAST_UPDATED_BY
440 ,l_status_rec.LPN_ID -- Added for # 6633612
441 ,l_status_rec.CREATION_DATE
442 ,l_status_rec.CREATED_BY
444 ,l_status_rec.LAST_UPDATE_DATE
445 ,l_status_rec.LAST_UPDATE_LOGIN
446 ,l_status_rec.PROGRAM_APPLICATION_ID
447 ,l_status_rec.PROGRAM_ID
448 ,l_status_rec.ATTRIBUTE_CATEGORY
449 ,l_status_rec.ATTRIBUTE1
450 ,l_status_rec.ATTRIBUTE2
451 ,l_status_rec.ATTRIBUTE3
452 ,l_status_rec.ATTRIBUTE4
453 ,l_status_rec.ATTRIBUTE5
454 ,l_status_rec.ATTRIBUTE6
455 ,l_status_rec.ATTRIBUTE7
456 ,l_status_rec.ATTRIBUTE8
457 ,l_status_rec.ATTRIBUTE9
458 ,l_status_rec.ATTRIBUTE10
459 ,l_status_rec.ATTRIBUTE11
460 ,l_status_rec.ATTRIBUTE12
461 ,l_status_rec.ATTRIBUTE13
462 ,l_status_rec.ATTRIBUTE14
463 ,l_status_rec.ATTRIBUTE15
464 ,l_status_rec.UPDATE_REASON_ID
465 ,l_status_rec.INITIAL_STATUS_FLAG
466 ,l_status_rec.FROM_MOBILE_APPS_FLAG
467 -- NSRIVAST, INVCONV , Start
468 ,l_status_rec.GRADE_CODE
469 ,l_status_rec.PRIMARY_ONHAND
470 ,l_status_rec.SECONDARY_ONHAND
471 -- NSRIVAST, INVCONV , End
472 );
473 --BEGIN SCHANDRU INVERES
474 IF g_eres_enabled <> 'N' THEN
475 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
476 grade_update_id) values (l_status_update_id, NULL);
477 END IF;
478 -- END SCHANDRU INVERES
479
480
481 --Bug# 1695432 added INITIAL_STATUS_FLAG,FROM_MOBILE_APPS_FLAG col
482
483 END LOOP;
484 end if;
485
486 -- call print_label to print the label
487 /* inv_label.print_label(
488 x_return_status => l_return_status,
489 x_msg_count => l_msg_count,
490 x_msg_data => l_msg_data,
491 x_label_status => l_label_status,
492 p_api_version => 1.0,
493 p_print_mode => 2,
494 p_business_flow_code => 10,
495 p_input_param_rec => l_input_param_rec); */
496 -- changed to call INV_LABEL.PRINT_LABEL_MANUAL_WRAP to pass serial range
497 INV_LABEL.PRINT_LABEL_MANUAL_WRAP(
498 x_return_status => l_return_status,
499 x_msg_count => l_msg_count,
500 x_msg_data => l_msg_data,
501 x_label_status => l_label_status,
502 p_business_flow_code => 10,
503 p_organization_id => l_status_rec.organization_id,
504 p_subinventory_code => l_status_rec.zone_code,
505 p_locator_id => l_status_rec.locator_id,
506 p_inventory_item_id => l_status_rec.inventory_item_id,
507 p_lot_number => l_status_rec.lot_number,
508 p_fm_serial_number => l_status_rec.serial_number,
509 p_to_serial_number => l_to_serial_number);
510
511 IF l_return_status <> fnd_api.g_ret_sts_success THEN
512 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL');
513 FND_MSG_PUB.ADD;
514 END IF;
515
516
517 EXCEPTION
518
519 WHEN FND_API.G_EXC_ERROR THEN
520
521 Raise FND_API.G_EXC_ERROR;
522
523 WHEN OTHERS THEN
524
525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
526 THEN
527 FND_MSG_PUB.Add_Exc_Msg
528 ( G_PKG_NAME
529 , 'Insert_Status_history'
530 );
531 END IF;
532
533
534 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
535
536 END Insert_status_history;
537
538 FUNCTION validate_mtstatus(
539 p_old_status_id mtl_material_statuses.status_id%TYPE,
540 p_new_status_id mtl_material_statuses.status_id%TYPE ,
541 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
542 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
543 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
544 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE
545 )RETURN BOOLEAN AS
546
547 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE := NULL;
548 l_return_status BOOLEAN;
549
550 BEGIN
551
552 inv_trx_util_pub.TRACE('inside non-overloaded validate_mtstatus ', 'INV_MATERIAL_STATUS_PKG', 14);
553
554 l_return_status := validate_mtstatus(
555 p_old_status_id,
556 p_new_status_id,
557 p_subinventory_code,
558 p_locator_id,
559 p_organization_id,
560 p_inventory_item_id,
561 p_lot_number);
562
563 if (l_return_status) then
564 inv_trx_util_pub.TRACE('validate_mtstatus: returning true', 'INV_MATERIAL_STATUS_PKG', 14);
565 else
566 inv_trx_util_pub.TRACE('validate_mtstatus: returning false', 'INV_MATERIAL_STATUS_PKG', 14);
567 end if;
568
569 return l_return_status;
570
571 EXCEPTION
575
572 when others then
573 return TRUE;
574 END;
576 /* Bug 6866429: Modified the function to properly check for existing
577 * reservations
578 */
579 --INVCONV kkillams
580 FUNCTION validate_mtstatus(
581 p_old_status_id mtl_material_statuses.status_id%TYPE,
582 p_new_status_id mtl_material_statuses.status_id%TYPE ,
583 p_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE,
584 p_locator_id mtl_onhand_quantities_detail.locator_id%TYPE,
585 p_organization_id mtl_secondary_inventories.organization_id%TYPE,
586 p_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
587 p_lot_number mtl_onhand_quantities_detail.lot_number%TYPE /* bug 6866429 */
588 )RETURN BOOLEAN AS
589 CURSOR cur_mt_status (cp_old_status_id mtl_material_statuses.status_code%TYPE,
590 cp_new_status_id mtl_material_statuses.status_code%TYPE) IS
591 SELECT 1 FROM mtl_material_statuses mts1,
592 mtl_material_statuses mts2
593 WHERE cp_old_status_id <> cp_new_status_id
594 AND mts1.status_id = cp_old_status_id
595 AND mts1.reservable_type = 1
596 AND mts2.status_id = cp_new_status_id
597 AND mts2.reservable_type <> mts1.reservable_type;
598
599 -- Bug 6829224: Modified the query such that for Orgs which track material status at onhand level
600 -- it checks for existing reservations for the given item.
601 CURSOR c_subinv_items(cp_organization_id mtl_onhand_quantities_detail.organization_id%TYPE,
602 cp_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
603 cp_subinventory_code mtl_onhand_quantities_detail.subinventory_code%TYPE) IS
604 SELECT 1 FROM mtl_onhand_quantities_detail moq
605 WHERE organization_id = cp_organization_id
606 AND subinventory_code = cp_subinventory_code
607 AND EXISTS
608 (SELECT 1
609 FROM mtl_reservations mr
610 WHERE mr.inventory_item_id = moq.inventory_item_id
611 AND mr.organization_id = moq.organization_id
612 AND ( (mr.inventory_item_id = cp_inventory_item_id )
613 OR cp_inventory_item_id IS NULL
614 )
615 AND ( (mr.subinventory_code = cp_subinventory_code )
616 OR mr.subinventory_code IS NULL
617 )
618 )
619 AND ROWNUM = 1;
620
621 CURSOR c_locator_items(cp_organization_id mtl_onhand_quantities_detail.organization_id%TYPE,
622 cp_inventory_item_id mtl_onhand_quantities_detail.inventory_item_id%TYPE,
623 cp_locator_id mtl_onhand_quantities_detail.locator_id%TYPE) IS
624 SELECT 1 FROM mtl_onhand_quantities_detail moq
625 WHERE organization_id = cp_organization_id
626 AND locator_id = cp_locator_id
627 AND EXISTS
628 (SELECT 1
629 FROM mtl_reservations mr
630 WHERE mr.inventory_item_id = moq.inventory_item_id
631 AND mr.organization_id = moq.organization_id
632 AND ( (mr.inventory_item_id = cp_inventory_item_id )
633 OR cp_inventory_item_id IS NULL
634 )
635 AND ( (mr.locator_id = cp_locator_id )
636 OR ( (mr.locator_id IS NULL
637 AND mr.subinventory_code = moq.subinventory_code
638 )
639 OR mr.subinventory_code IS NULL
640 )
641 )
642 )
643 AND ROWNUM = 1;
644
645 CURSOR c_lot_items( cp_organization_id mtl_reservations.organization_id%TYPE,
646 cp_inventory_item_id mtl_reservations. inventory_item_id %TYPE,
647 cp_lot_number mtl_onhand_quantities_detail.lot_number%TYPE) IS
648 SELECT 1 FROM mtl_onhand_quantities_detail moq
649 WHERE organization_id = cp_organization_id
653 (SELECT 1
650 AND (inventory_item_id = cp_inventory_item_id OR cp_inventory_item_id IS NULL)
651 AND lot_number = cp_lot_number
652 AND EXISTS
654 FROM mtl_reservations mr
655 WHERE mr.inventory_item_id = moq.inventory_item_id
656 AND mr.organization_id = moq.organization_id
657 AND ( (mr.inventory_item_id = cp_inventory_item_id )
658 OR cp_inventory_item_id IS NULL
659 )
660 AND ( (mr.lot_number = cp_lot_number )
661 OR mr.lot_number IS NULL
662 )
663 )
664 AND ROWNUM = 1;
665
666 CURSOR c_items_reserv( cp_organization_id mtl_reservations.organization_id%TYPE,
667 cp_inventory_item_id mtl_reservations. inventory_item_id%TYPE) IS
668 SELECT 1 FROM mtl_reservations
669 WHERE inventory_item_id = cp_inventory_item_id
670 AND organization_id = cp_organization_id
671 AND ROWNUM = 1;
672
673 l_dummy NUMBER;
674 BEGIN
675
676 inv_trx_util_pub.TRACE('inside overloaded validate_mtstatus ', 'INV_MATERIAL_STATUS_PKG', 14);
677 inv_trx_util_pub.TRACE('validate_mtstatus: old status id: '||p_old_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
678 inv_trx_util_pub.TRACE('validate_mtstatus: new status id: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
679 inv_trx_util_pub.TRACE('validate_mtstatus: organization id: '||p_organization_id, 'INV_MATERIAL_STATUS_PKG', 14);
680 inv_trx_util_pub.TRACE('validate_mtstatus: inventory item id: '||p_inventory_item_id, 'INV_MATERIAL_STATUS_PKG', 14);
681 inv_trx_util_pub.TRACE('validate_mtstatus: subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
682 inv_trx_util_pub.TRACE('validate_mtstatus: locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
683 inv_trx_util_pub.TRACE('validate_mtstatus: lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
684
685
686 OPEN cur_mt_status(p_old_status_id,p_new_status_id);
687 FETCH cur_mt_status INTO l_dummy;
688 IF cur_mt_status%NOTFOUND THEN
689 CLOSE cur_mt_status;
690
691 inv_trx_util_pub.TRACE('validate_mtstatus: New status also allows reservation: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
692
693 RETURN TRUE;
694 END IF; --cur_mt_status
695 CLOSE cur_mt_status;
696
697 inv_trx_util_pub.TRACE('validate_mtstatus: New status does not allow reservation: '||p_new_status_id, 'INV_MATERIAL_STATUS_PKG', 14);
698
699 IF (p_lot_number IS NOT NULL ) THEN
700 inv_trx_util_pub.TRACE('validate_mtstatus: lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
701 OPEN c_lot_items(p_organization_id,p_inventory_item_id,p_lot_number);
702 FETCH c_lot_items INTO l_dummy;
703 IF c_lot_items%FOUND THEN
704 CLOSE c_lot_items;
705
706 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the lot number: '||p_lot_number, 'INV_MATERIAL_STATUS_PKG', 14);
707
708 RETURN FALSE;
709 END IF;
710 CLOSE c_lot_items;
711
712 ELSIF ( p_locator_id IS NOT NULL) THEN
713 inv_trx_util_pub.TRACE('validate_mtstatus: locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
714 OPEN c_locator_items(p_organization_id,p_inventory_item_id,p_locator_id);
715 FETCH c_locator_items INTO l_dummy;
716 IF c_locator_items%FOUND THEN
717 CLOSE c_locator_items;
718
719 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for locator id: '||p_locator_id, 'INV_MATERIAL_STATUS_PKG', 14);
720
721 RETURN FALSE;
722 END IF;
723 CLOSE c_locator_items;
724
725 --If api is called from subinventory/locator form.
726 ELSIF (P_subinventory_code IS NOT NULL ) THEN
727 inv_trx_util_pub.TRACE('validate_mtstatus: subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
728 -- Bug 6829224: Passing item_id to the cursor
729 OPEN c_subinv_items(p_organization_id,p_inventory_item_id,p_subinventory_code);
730 FETCH c_subinv_items INTO l_dummy;
731 IF c_subinv_items%FOUND THEN
732 CLOSE c_subinv_items;
733
734 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the subinventory: '||p_subinventory_code, 'INV_MATERIAL_STATUS_PKG', 14);
735
736 RETURN FALSE;
737 END IF;
738 CLOSE c_subinv_items;
739
740 ELSE
741 inv_trx_util_pub.TRACE('validate_mtstatus: checking reservatios only for the item', 'INV_MATERIAL_STATUS_PKG', 14);
742 OPEN c_items_reserv(p_organization_id,p_inventory_item_id);
743 FETCH c_items_reserv INTO l_dummy;
744 IF c_items_reserv%FOUND THEN
745 CLOSE c_items_reserv;
746
747 inv_trx_util_pub.TRACE('validate_mtstatus: reservations exist for the item: '||p_inventory_item_id, 'INV_MATERIAL_STATUS_PKG', 14);
748
749 RETURN FALSE;
750 END IF; --c_items_reserv
751 CLOSE c_items_reserv;
752 END IF; --P_subinventory_code IS NOT NULL
753
754 inv_trx_util_pub.TRACE('validate_mtstatus: returning true', 'INV_MATERIAL_STATUS_PKG', 14);
755
756 RETURN TRUE;
757
758 EXCEPTION
759 when others then
760 return TRUE;
761
762 END validate_mtstatus;
763
764 PROCEDURE SET_MS_FLAGS(
765 p_status_id MTL_MATERIAL_STATUSES.STATUS_ID%TYPE
766 ,p_org_id MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID%TYPE
767 ,p_inventory_item_id MTL_LOT_NUMBERS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
768 ,p_secondary_inventory_name MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE DEFAULT NULL
769 ,p_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE DEFAULT NULL
770 ,p_inventory_location_id MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID%TYPE DEFAULT NULL
771 ,p_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE DEFAULT NULL
772 ) AS
773 CURSOR cur_ms IS SELECT inventory_atp_code
774 ,reservable_type
775 ,availability_type FROM mtl_material_statuses
776 WHERE status_id = p_status_id;
777 rec_ms cur_ms%ROWTYPE;
778 BEGIN
779 OPEN cur_ms;
780 FETCH cur_ms INTO rec_ms;
781 CLOSE cur_ms;
782 IF p_lot_number IS NOT NULL THEN
783 UPDATE mtl_lot_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
784 availability_type =rec_ms.reservable_type,
785 reservable_type =rec_ms.availability_type
786 WHERE organization_id = p_org_id
787 AND lot_number = p_lot_number
788 AND inventory_item_id = p_inventory_item_id;
789 ELSIF p_serial_number IS NOT NULL THEN
790
791 /* Bug#4560805 The columns inventory_atp_code,availability_type and reservable_type are not a part of
792 mtl_serial_numbers. Hence commenting the UPDATE statement */
793
794 /*
795 UPDATE mtl_serial_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
796 availability_type =rec_ms.reservable_type,
797 reservable_type =rec_ms.availability_type
798 WHERE current_organization_id = p_org_id
799 AND serial_number = p_serial_number
800 AND inventory_item_id = p_inventory_item_id;
801 */
802 NULL;
803 ELSIF p_inventory_location_id IS NOT NULL THEN
804 UPDATE MTL_ITEM_LOCATIONS SET inventory_atp_code =rec_ms.inventory_atp_code,
805 availability_type =rec_ms.reservable_type,
806 reservable_type =rec_ms.availability_type
807 WHERE organization_id = p_org_id
808 AND inventory_location_id = p_inventory_location_id;
809 ELSIF p_secondary_inventory_name IS NOT NULL THEN
810 UPDATE mtl_secondary_inventories SET inventory_atp_code =rec_ms.inventory_atp_code,
811 availability_type =rec_ms.reservable_type,
812 reservable_type =rec_ms.availability_type
813 WHERE organization_id = p_org_id
814 AND secondary_inventory_name =p_secondary_inventory_name;
815 END IF;
816 END SET_MS_FLAGS;
817 --END INVCONV kkillams
818
819 END INV_MATERIAL_STATUS_PKG;