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,
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:
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:
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);
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);
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);
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);
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:
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:
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);
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;
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;
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);
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;
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;
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;
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;
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
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;
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;
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;
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