ERP 2.50:QA test plan 2.50/Modularity/Room material procedure (PostgreSQL)

CREATE OR REPLACE FUNCTION rmat_add_material(pinstance_id character varying)  RETURNS character varying AS
$BODY$
DECLARE
-- Logistice
v_ResultStr VARCHAR(2000);
v_Message VARCHAR(2000);
v_Record_ID VARCHAR(32);
-- Parameter

Cur_Parameter RECORD;
-- Record Info
v_Bpartner VARCHAR(32);
v_Client VARCHAR(32);
v_Org VARCHAR(32);
v_Createdby VARCHAR(32);
v_Typematerial VARCHAR(1);
v_SeqNo NUMERIC(10);
v_Rmat_Id VARCHAR(32);
v_NoOfLines NUMERIC(10);

-- Copy
Cur_Copymaterial RECORD;
BEGIN
--  Update AD_PInstance
--DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ;
v_ResultStr:='PInstanceNotFound';
PERFORM 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) ;
 PERFORM 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) ;
PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
RETURN ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;