Hi Team,
I have created following function to fetch Current Hold Reason code for an Onhand Item but It returns Old Hold reason since I have mentioned mtl_material_status_history table.
Please provide query to return current Hold reason code.
Thanks In advance.
FUNCTION XXNUC_INV_TAG_HOLD_REASON (p_inventory_item_id in NUMBER,
p_organization_id in NUMBER,
p_lpn_id in NUMBER,
p_lot_number in VARCHAR2,
p_locator_id in NUMBER)
RETURN VARCHAR2
AS
l_stat_update_id mtl_material_status_history.status_update_id%type;
l_reason_code mtl_transaction_reasons.description%type;
BEGIN
SELECT MAX(mmsh.status_update_id)
INTO l_stat_update_id
FROM mtl_material_status_history mmsh
WHERE mmsh.inventory_item_id =p_inventory_item_id
AND mmsh.organization_id = p_organization_id
AND mmsh.lpn_id = p_lpn_id
AND mmsh.lot_number = p_lot_number
AND mmsh.locator_id = p_locator_id
AND mmsh.status_id = 40;
IF l_stat_update_id IS NOT NULL THEN
BEGIN
SELECT description
INTO l_reason_code
FROM mtl_material_status_history mmsh1,
mtl_transaction_reasons mtr
WHERE mmsh1.update_reason_id = mtr.reason_id
AND mmsh1.status_update_id = l_stat_update_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
raise_application_error (-20005, 'Too Many Rows Returned for Status Update ID: '||l_stat_update_id);
RETURN NULL;
END;
RETURN l_reason_code;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END XXNUC_INV_TAG_HOLD_REASON;
END XXNUC_INV_ENDECA_PKG;