SELECT
CaseHeader.caseReference,
CaseHeader.statuscode,
AppealRelationship.resolutioncode,
AppealRelationship.appealCaseID,
Appeal.deadlinedate,
Appeal.appealtypecode,
ConcernRole.concernrolename,
OrgObjectLink.orgObjectReference,
OrgObjectLink.orgObjectType,
OrgObjectLink.userName
INTO
:caseReference,
:status,
:resolution,
:appealCaseID,
:deadlinedate,
:appealCaseType,
:appelant,
:orgObjectReference,
:orgObjectType,
:userName
FROM
CaseHeader,
Appeal,
AppealRelationship,
ConcernRole,
OrgObjectLink
WHERE CaseHeader.OwnerOrgObjectLinkID =OrgObjectLink.OrgObjectLinkID
AND AppealRelationship.AppealCaseID =Appeal.CaseID
AND AppealRelationship.AppealCaseID =CaseHeader.CaseID
AND AppealRelationship.statuscode <> :appealStatusCode
AND ConcernRole.ConcernRoleID =CaseHeader.ConcernRoleID
AND AppealRelationship.CaseID IN
(
SELECT
ISSUEDELIVERY.CASEID
FROM
CASEHEADER,
ISSUEDELIVERY
WHERE CASEHEADER.CASEID = ISSUEDELIVERY.RELATEDCASEID
AND ISSUEDELIVERY.CASEID = :caseID
UNION
SELECT
PRODUCTDELIVERY.CASEID
FROM
PRODUCTDELIVERY
WHERE PRODUCTDELIVERY.CASEID = :caseID
UNION
SELECT
CASEHEADER.CASEID
FROM
CASEHEADER
WHERE CASEHEADER.INTEGRATEDCASEID IS NULL
AND CASEHEADER.CASEID = :caseID
)
ORDER BY DeadlineDate desc
|