ERP 2.50:QA test plan 2.50/Modularity/Room material procedure

/********************PROCEDURE RMAT_ADD_MATERIAL***************/

CREATE OR REPLACE PROCEDURE RMAT_ADD_MATERIAL (pinstance_id IN VARCHAR2) 

AS

 -- Logistice
 v_ResultStr VARCHAR2(2000):=;
 v_Message VARCHAR2(2000):=;
 v_Record_ID VARCHAR2(32);
 -- Parameter
 TYPE RECORD IS REF CURSOR;
   Cur_Parameter RECORD;
   -- Record Info
   v_Bpartner VARCHAR2(32);
   v_Client VARCHAR2(32);
   v_Org VARCHAR2(32);
   v_Createdby VARCHAR2(32);
   v_Typematerial CHAR(1);
   v_SeqNo NUMBER(10);
   v_Rmat_Id VARCHAR2(32);
   v_NoOfLines NUMBER(10);
  
   -- Copy
   Cur_Copymaterial RECORD;
 BEGIN
   --  Update AD_PInstance
   DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ;
   v_ResultStr:='PInstanceNotFound';
   AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ;
 BEGIN --BODY
   -- Get Parameters
   v_ResultStr:='ReadingParameters';
   FOR Cur_Parameter IN
     (SELECT i.Record_ID,
       p.ParameterName,
       p.P_String,
       p.P_Number,
       p.P_Date
     FROM AD_PINSTANCE i
     LEFT JOIN AD_PINSTANCE_PARA p
       ON i.AD_PInstance_ID=p.AD_PInstance_ID
     WHERE i.AD_PInstance_ID=PInstance_ID
     ORDER BY p.SeqNo
     )
   LOOP
     v_Record_ID:=Cur_Parameter.Record_ID;
     IF(Cur_Parameter.ParameterName='MaterialType') THEN
             v_Typematerial:=Cur_Parameter.P_String;
             DBMS_OUTPUT.PUT_LINE('MaterialType=' || v_Typematerial) ;
     END IF;
   END LOOP; -- Get Parameter
   DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID) ;
   v_ResultStr:='Readingscheduleroom';
   SELECT AD_CLIENT_ID, AD_ORG_ID, CREATEDBY
   INTO v_client, v_org, v_Createdby
   FROM ROOMS_ROOMSCHEDULE
   WHERE ROOMS_ROOMSCHEDULE_ID=v_Record_ID;
   
   SELECT COALESCE(MAX(RMAT_REQUIRED.line), 10)
   INTO v_SeqNo
   FROM RMAT_REQUIRED
   WHERE RMAT_REQUIRED.RMAT_REQUIRED_ID=v_Record_ID;
   -- Copying records from RMAT_MATERIAL depending on Type material value
   v_ResultStr:='Copying';
   FOR Cur_Copymaterial IN
     (SELECT RMAT_MATERIAL_ID
     FROM RMAT_MATERIAL
     WHERE RMAT_MATERIAL.MATERIALTYPE=v_Typematerial
     AND RMAT_MATERIAL.AD_CLIENT_ID=v_Client;
     )
   LOOP
     -- Get next no
     SELECT GET_UUID() INTO v_Rmat_Id FROM DUAL;
     -- Insert
     INSERT
     INTO RMAT_REQUIRED
       ( RMAT_REQUIRED_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, LINE, ROOMS_ROOMSCHEDULE_ID, MATERIALUSED_ID)
       VALUES
       (
         v_Rmat_Id, v_client, v_org, 'Y',
         now(), v_Createdby, now(), v_Createdby,
         v_SeqNo, v_Record_ID, Cur_Copymaterial.RMAT_MATERIAL_ID
       )
       ;
     -- update translation
     --
     v_SeqNo:=v_SeqNo + 10;
     v_NoOfLines:=v_NoOfLines + 1;
   END LOOP;
   v_Message:='@CopiedMaterial@=' || v_NoOfLines ;
   --<<FINISH_PROCESS>>
   --  Update AD_PInstance
   DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
   AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 1, v_Message) ;
   RETURN;
 END; --BODY
EXCEPTION
WHEN OTHERS THEN
 v_ResultStr:= '@ERROR=' || SQLERRM;
 DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
 AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
 RETURN;
END RMAT_ADD_MATERIAL
;
/