hi pankaj,
I am not sure if this is the right way to declare substr function inside criteria(I myself is a fan of MYSQL and this query is not supported there ..).
but i just want to comment on your query
add(Restrictions.between("substr(MPIM_PART_NO_FRM,0,5)", fromPart.substring(0, 5), fromPart.substring(0, 5)))
.add(Restrictions.between("substr(MPIM_PART_NO_TO,0,5)", toPart.substring(0, 5), toPart.substring(0, 5)));
is it the right way you have declared your criteria. Please check.
because with this query, even if there is no error, will always return null.
Regards,
Hemant.