GMI Lot Master

Select
                i.item_no,
                i.item_desc1,
                l.lot_no,
                l.sublot_no,
                l.lot_desc,
                l.qc_grade,
                l.expaction_code,
                l.strength,
                l.inactive_ind,
                l.origination_type,
                l.expaction_date,
                l.lot_created,
                l.expire_date,
                l.retest_date
            From    ic_item_mst i,
                ic_lots_mst l
            Where   l.item_id =i.item_id
            and     l.lot_id  != 0
            and     i. item_no >= NVL(:from_item,'0')
            and     i.item_no <= NVL(:to_item,'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz')
            and l.delete_mark = decode(:select_criteria,'Active',0,'Deleted',1,'All',l.delete_mark,0)
            Order by  item_no asc ,lot_no  asc