Oracle : ORA-01422 ค่าการดึงข้อมูลที่ตรงกันทั้งหมดมากกว่าจำนวนแถวที่ต้องการ

CREATE OR REPLACE PROCEDURE WMWHSE8.IRPC_TRNUPDATE_S_SP
(
  I_TRANSFERNUM   IN        VARCHAR2,
  I_TRANSFERLINE  IN        VARCHAR2,
  I_LPN           IN        VARCHAR2,
  O_STATUS        OUT       VARCHAR2,
  O_ERRDESC       OUT       VARCHAR2
)
IS

/*  ####################################################   */

V_PROCDESC           VARCHAR2(200):=' IRPC_TRNUPDATE_S_SP ';
V_RECCOUNT           NUMBER(2);

V_FROMSTORERKEY		VARCHAR2(15);
V_FROMSKU		      VARCHAR2(50);
V_FROMLOC		      VARCHAR2(10);
V_FROMLOT          VARCHAR2(10);
V_FROMID          VARCHAR2(18);
V_FROMQTY          NUMBER(22,5);
V_FROMPACKKEY      VARCHAR2(50);
V_FROMUOM          VARCHAR2(10);

V_FROMLOC2        VARCHAR2(10);
V_QTYONHAND       NUMBER(22,5);
V_QTYALLOCATED    NUMBER(22,5);
BEGIN
   
   
   SELECT 
   COUNT(1) 
   INTO V_RECCOUNT 
   FROM WMWHSE8.TRANSFER 
   WHERE TRANSFERKEY = I_TRANSFERNUM;
   IF V_RECCOUNT = 0 THEN 
     O_STATUS := 'E'; 
     O_ERRDESC := 'ERROR CANNOT FIND TRANSFER';
     RETURN;
   END IF;

  SELECT 
  COUNT(1) 
  INTO V_RECCOUNT 
  FROM WMWHSE8.TRANSFERDETAIL 
  WHERE TRANSFERKEY = I_TRANSFERNUM 
  AND TRANSFERLINENUMBER =  I_TRANSFERLINE 
  AND STATUS IN ('0','3');
  
  IF V_RECCOUNT = 0 THEN
     O_STATUS := 'E';
     O_ERRDESC := 'ERROR CANNOT FIND TRANSFER';
     RETURN;
   ELSE
    SELECT
    FROMSTORERKEY , 
    FROMSKU , 
    FROMLOC , 
    FROMLOT , 
    FROMID , 
    FROMQTY , 
    FROMPACKKEY , 
    FROMUOM
    INTO
    V_FROMSTORERKEY , 
    V_FROMSKU ,
    V_FROMLOC ,
    V_FROMLOT ,
    V_FROMID ,
    V_FROMQTY ,
    V_FROMPACKKEY ,
    V_FROMUOM        
      
    FROM WMWHSE8.TRANSFERDETAIL
    WHERE TRANSFERKEY = I_TRANSFERNUM
    AND TRANSFERLINENUMBER =  I_TRANSFERLINE
    AND STATUS IN ('0','3') ;
   END IF;
  
  
   IF V_FROMID = I_LPN THEN
     
       SELECT
       LOC ,
       QTY ,
       QTYALLOCATED
       INTO
       V_FROMLOC2,
       V_QTYONHAND,
       V_QTYALLOCATED
       FROM WMWHSE8.LOTXLOCXID
       WHERE ID = I_LPN
       AND SKU = V_FROMSKU
       AND STORERKEY = V_FROMSTORERKEY
       AND LOC = V_FROMLOC;
       
       IF V_QTYALLOCATED > 0 THEN
         O_STATUS := 'E'; 
         O_ERRDESC := 'ERROR LPN IS ALLOCATE';
         RETURN;     
       END IF;
       
       IF V_FROMQTY <> V_QTYONHAND THEN
         O_STATUS := 'E'; 
         O_ERRDESC := 'ERROR QTY TRANSFER  <> QTY LPN';
         RETURN;     
       END IF;
   
      UPDATE WMWHSE8.TRANSFER
      SET STATUS = 3, 
      EDITWHO = 'ADMINISTRATOR' ,
      EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
      WHERE TRANSFERKEY = I_TRANSFERNUM
      AND STATUS IN ('0','3');
       
      UPDATE WMWHSE8.TRANSFERDETAIL
      SET STATUS = 3, 
      EDITWHO = 'ADMINISTRATOR' ,
      EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
      WHERE TRANSFERKEY = I_TRANSFERNUM
      AND TRANSFERLINENUMBER =  I_TRANSFERLINE
      AND STATUS IN ('0','3');
      --COMMIT;
      --RETURN;
   END IF;

   SELECT 
   COUNT(1) 
   INTO V_RECCOUNT  
   FROM WMWHSE8.LOTXLOCXID 
   WHERE ID = I_LPN
   AND SKU = V_FROMSKU
   AND STORERKEY = V_FROMSTORERKEY
   AND LOC = V_FROMLOC;
   
   IF V_RECCOUNT <> 1 THEN 
     O_STATUS := 'E'; 
     O_ERRDESC := 'ERROR CANNOT FIND PALLET IN SYSTEM';
     ROLLBACK;
     RETURN;
   END IF;

   SELECT
   LOC ,
   QTY ,
   QTYALLOCATED
   INTO
   V_FROMLOC2,
   V_QTYONHAND,
   V_QTYALLOCATED
   FROM WMWHSE8.LOTXLOCXID
   WHERE ID = I_LPN
   AND SKU = V_FROMSKU
   AND STORERKEY = V_FROMSTORERKEY
   AND LOC = V_FROMLOC;
   
   IF V_QTYALLOCATED > 0 THEN
     O_STATUS := 'E'; 
     O_ERRDESC := 'ERROR LPN IS ALLOCATE';
     RETURN;     
   END IF;
   
   IF V_FROMQTY <> V_QTYONHAND THEN
     O_STATUS := 'E'; 
     O_ERRDESC := 'ERROR QTY TRANSFER  <> QTY LPN';
     RETURN;     
   END IF;
   
   
   IF V_FROMQTY = V_QTYONHAND THEN
     UPDATE WMWHSE8.TRANSFER
     SET STATUS = 3,
     EDITWHO = 'ADMINISTRATOR' ,
     EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
     WHERE TRANSFERKEY = I_TRANSFERNUM
     AND STATUS IN ('0','3');
   
     UPDATE WMWHSE8.TRANSFERDETAIL
     SET STATUS = 3,
         EDITWHO = 'ADMINISTRATOR' ,
         EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR,
         FROMID = I_LPN,
         TOID = I_LPN
     WHERE TRANSFERKEY = I_TRANSFERNUM
     AND TRANSFERLINENUMBER =  I_TRANSFERLINE
     AND STATUS IN ('0','3');
     
     O_STATUS := 'S';
     --COMMIT;
     RETURN;
   ELSE
     O_STATUS := 'E';
     O_ERRDESC := ' QTY TRANSFER > QTY ONHAND';
     ROLLBACK;
     RETURN;
   END IF;

   --O_ERRDESC := ' I_TRANSFERNUM : ' || I_TRANSFERNUM || ' I_TRANSFERLINE : ' || I_TRANSFERLINE || ' I_LPN : ' || I_LPN;  

   DBMS_OUTPUT.PUT_LINE('SUCCESS TRANSFER');
   
EXCEPTION
  WHEN OTHERS THEN
  O_STATUS := 'E';
  O_ERRDESC := TO_CHAR(SYSTIMESTAMP ,'YYYY/MM/DD HH24:MI:SS') || ' => ' ||  'ERPP ERROR ' || ' ' ||  V_PROCDESC || ' : ' || SUBSTR(TO_CHAR(SQLCODE) || ': ' || SQLERRM, 1, 2000);
  DBMS_OUTPUT.PUT_LINE(O_ERRDESC);
  ROLLBACK;
  RETURN;
END;
/