DROP PROCEDURE sp_get_lookup_values;
CREATE PROCEDURE sp_get_lookup_values(IN lookup_code VARCHAR(50), IN dep_code VARCHAR(50), IN flag VARCHAR(50))
BEGIN
If flag = 'L' Then
	If dep_code = '0' OR dep_code = NULL Then
		Select 	lv.id, lv.lookup_id, lv.short_code, lv.description 
		from	lookups l,
				lookup_values lv
		where	l.id = lv.lookup_id
		AND		l.lookup_code = lookup_code
		AND		lv.is_active = 'Y';
	Else
		Select 	lv.id, lv.lookup_id, lv.short_code, lv.description 
		from	lookups l,
				lookup_values lv, lookup_values lvp
		where	lvp.short_code = dep_code
		AND		l.id = lv.lookup_id
		AND		lv.dep_parent_lookup_value_id = lvp.id
		AND		l.lookup_code = lookup_code
		AND		lvp.is_active = 'Y'
		AND		lv.is_active = 'Y';
	End If;
ELSE
	Select 	lv.id, lv.lookup_id, lv.short_code, lv.description 
	from	lookups l,
			lookup_values lv
	where	l.id = lv.lookup_id
	AND		lv.short_code = lookup_code
	AND		lv.is_active = 'Y';
End If;
END

