SELECT
Appeal.caseID,
caseReference,
appealTypeCode,
concernRoleName,
participantRoleID,
CaseParticipantRole.typeCode,
concernRoleType,
CaseHeader.registrationDate,
CaseHeader.statusCode
INTO
:caseID,
:caseReference,
:appealType,
:participantName,
:participantRoleID,
:participantType,
:concernRoleType,
:creationDate,
:status
FROM
Appeal,
CaseHeader,
CaseParticipantRole,
ConcernRole,
Hearing
WHERE CaseHeader.caseID = Appeal.caseID
AND CaseParticipantRole.caseID = Appeal.caseID
AND ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID
AND CaseParticipantRole.typeCode in ( :appellantParticipantRoleType, :respondentParticipantRoleType)
AND
(
:searchByAppealType = '0'
OR appealTypeCode = :appealType
)
AND
(
:searchByStatus = '0'
OR CaseHeader.statusCode = :status
)
AND
(
:searchByCreationDate = '0'
OR CaseHeader.registrationDate = :creationDate
)
AND
(
:searchByAppellant = '0'
OR
(
participantRoleID = :appellantID
AND CaseParticipantRole.typeCode = :appellantParticipantRoleType
)
)
AND
(
:searchByRespondent = '0'
OR
(
participantRoleID = :respondentID
AND CaseParticipantRole.typeCode = :respondentParticipantRoleType
)
)
AND
(
:searchByAppellantType = '0'
)
AND
(
Hearing.caseID = Appeal.caseID
AND
(
scheduledDateTime >= :fromHearingDateTime
AND scheduledDateTime < :toHearingDateTime
)
)
ORDER BY caseReference,
CaseParticipantRole.typeCode
|