SELECT
  LEGALACTION.LEGALACTIONID,
  LEGALACTION.CASEID,
  LEGALACTION.STARTDATE,
  LEGALACTION.ENDDATE,
  LEGALACTION.STATUSCODE,
  ADMINLEGALACTION.LEGALACTIONNAMECODE
INTO
  :legalActionID,
  :legalActionCaseID,
  :startDate,
  :endDate,
  :statusCode,
  :name 
FROM
  LEGALACTION,
  ADMINLEGALACTION,
  CASEHEADER,
  CASERELATIONSHIP 
WHERE LEGALACTION.CONFIGLEGALACTIONID =ADMINLEGALACTION.LEGALACTIONID 
  AND CASEHEADER.CASEID =LEGALACTION.CASEID 
  AND LEGALACTION.CASEID NOT IN 
  ( 
  SELECT
    RELATEDCASEID 
  FROM
    CASERELATIONSHIP 
  WHERE CASEID = :caseID 
    AND STATUSCODE = :recordStatus 
  ) 
  AND LEGALACTION.CASEID NOT IN 
  ( 
  SELECT
    CASEID 
  FROM
    CASERELATIONSHIP 
  WHERE RELATEDCASEID = :caseID 
    AND STATUSCODE = :recordStatus 
  ) 
  AND LEGALACTION.CASEID <> :caseID 
  AND LEGALACTION.STATUSCODE <> :statusCode 
  AND CASERELATIONSHIP.RELATEDCASEID = 
  ( 
  SELECT
    CASERELATIONSHIP.RELATEDCASEID 
  FROM
    CASERELATIONSHIP 
  WHERE CASERELATIONSHIP.CASEID = :caseID 
    AND CASERELATIONSHIP.TYPECODE = :relationShipCode 
  ) 
  AND CASERELATIONSHIP.CASEID = CASEHEADER.CASEID