297: p_resultout OUT NOCOPY VARCHAR2
298: )
299: IS
300: l_count_lots_mst NUMBER := 0;
301: l_sum_loct_onhand ic_loct_inv.loct_onhand%TYPE := 0 ;
302: l_sum_loct_onhand2 ic_loct_inv.loct_onhand2%TYPE := 0 ;
303:
304: l_lot_id ic_lots_mst.lot_id%TYPE := TO_NUMBER(LTRIM(p_itemkey, 'EX')) ;
305: l_lot_no ic_lots_mst.lot_no%TYPE ;
298: )
299: IS
300: l_count_lots_mst NUMBER := 0;
301: l_sum_loct_onhand ic_loct_inv.loct_onhand%TYPE := 0 ;
302: l_sum_loct_onhand2 ic_loct_inv.loct_onhand2%TYPE := 0 ;
303:
304: l_lot_id ic_lots_mst.lot_id%TYPE := TO_NUMBER(LTRIM(p_itemkey, 'EX')) ;
305: l_lot_no ic_lots_mst.lot_no%TYPE ;
306: l_sublot_no ic_lots_mst.sublot_no%TYPE ;
332:
333: /* find out quantity we have on hand */
334: SELECT nvl(SUM(loct_onhand),0) , nvl(SUM(loct_onhand2),0)
335: INTO l_sum_loct_onhand , l_sum_loct_onhand2
336: FROM ic_loct_inv
337: WHERE lot_id = l_lot_id
338: AND delete_mark = 0 ;
339:
340: IF (
337: WHERE lot_id = l_lot_id
338: AND delete_mark = 0 ;
339:
340: IF (
341: /* no rows found in ic_loct_inv
342: quantities total zero */
343: ( l_sum_loct_onhand + l_sum_loct_onhand2 ) = 0
344: ) THEN
345: p_resultout := 'COMPLETE:ZERO' ;