SELECT
appealrelationship.caseid,
ch2.caseReference,
ch2.statuscode,
count(appealrelationship.appealcaseid) numberOfAppeals,
ch2.startDate,
cool.orgObjectType,
cool.orgObjectReference,
cool.userName
INTO
:caseId,
:caseReference,
:status,
:numberOfAppeals,
:startDate,
:orgObjectType,
:orgObjectReference,
:userName
FROM
caseheader ch2,
appealrelationship,
OrgObjectLink cool
WHERE ch2.caseid IN
(
SELECT
ch1.caseid
FROM
caseHeader ch1,
OrgObjectLink cool1
WHERE ch1.ownerOrgObjectLinkID = cool1.orgobjectlinkid
AND cool1.orgobjectreference IN
(
SELECT
distinct orgunit.organisationunitid
FROM
organisationUnit orgunit
WHERE orgunit.organisationUnitID IN
(
SELECT
ou.organisationUnitID
FROM
organisationUnit ou,
orgunitpositionlink opl,
positionholderlink phl,
Position pos2
WHERE phl.username = :supervisorUsername
AND phl.positionid = opl.positionid
AND opl.positionid = pos2.positionid
AND opl.organisationUnitID = ou.organisationUnitID
AND pos2.leadPositionInd = '1'
AND opl.recordStatus <> :recordStatus
AND phl.recordStatus <> :recordStatus
UNION
SELECT
organisationunitid
FROM
Orgunitparentlink opar
WHERE opar.recordStatus <> :recordStatus
AND opar.parentorganisationunitid IN
(
SELECT
organisationunitid
FROM
orgunitpositionlink opl1,
positionholderlink phl1,
position pos3
WHERE phl1.positionid = opl1.positionid
AND opl1.positionid = pos3.positionid
AND pos3.leadPositionInd = '1'
AND phl1.username = :supervisorUsername
AND opl1.recordStatus <> :recordStatus
AND phl1.recordStatus <> :recordStatus
)
)
UNION
SELECT
distinct WorkQueue.workQueueID
FROM
WorkQueue,
WorkQueueSubscription
WHERE WorkQueue.workQueueID = WorkQueueSubscription.workQueueID
AND WorkQueueSubscription.userName IN
(
SELECT
users.username
FROM
position,
positionholderlink,
organisationunit,
orgunitpositionlink,
users
WHERE position.positionid = positionholderlink.positionid
AND orgunitpositionlink.positionid = positionholderlink.positionid
AND orgunitpositionlink.organisationunitid = organisationunit.organisationunitid
AND positionholderlink.username =users.username
AND positionholderlink.recordStatus <> :recordStatus
AND orgunitpositionlink.recordStatus <> :recordStatus
AND position.recordStatus <> :recordStatus
AND position.positionid IN
(
SELECT
orgunitpositionlink.positionid
FROM
orgunitpositionlink
WHERE orgunitpositionlink.recordStatus <> :recordStatus
AND orgunitpositionlink.organisationunitid IN
(
SELECT
opl.organisationunitid
FROM
orgunitpositionlink opl,
positionholderlink phl,
Position pos
WHERE pos.positionid = phl.positionid
AND phl.positionid = opl.positionid
AND pos.LeadPositionInd = '1'
AND phl.username = :supervisorUsername
AND phl.recordStatus <> :recordStatus
AND opl.recordStatus <> :recordStatus
UNION
SELECT
Orgunitparentlink.organisationunitid
FROM
Orgunitparentlink
WHERE Orgunitparentlink.recordStatus <> :recordStatus
AND Orgunitparentlink.parentorganisationunitid IN
(
SELECT
opl1.organisationunitid
FROM
orgunitpositionlink opl1,
positionholderlink phl1,
position pos1
WHERE pos1.positionid = phl1.positionid
AND pos1.leadpositionind = '1'
AND phl1.positionid = opl1.positionid
AND phl1.username = :supervisorUsername
AND opl1.recordStatus <> :recordStatus
AND phl1.recordStatus <> :recordStatus
)
)
UNION
SELECT
prl.positionid
FROM
PositionReportingLink prl,
PositionHolderLink phl2,
Position pos2
WHERE phl2.positionid = prl.reportsToID
AND phl2.positionid = pos2.positionid
AND pos2.leadpositionind = '1'
AND phl2.username = :supervisorUsername
AND phl2.recordStatus <> :recordStatus
AND prl.recordStatus <> :recordStatus
)
UNION
SELECT
cool.username
FROM
CaseUserRole cur,
orgobjectlink cool
WHERE cur.recordStatus <> :recordStatus
AND cool.username = :supervisorUsername
AND cur.orgobjectlinkid = cool.orgobjectlinkid
)
UNION
SELECT
distinct pos.positionid
FROM
orgunitpositionlink ouposlink,
position pos
WHERE ouposlink.positionID = pos.positionid
AND ouposlink.recordStatus <> :recordStatus
AND ouposlink.ORGANISATIONUNITID IN
(
SELECT
ou.organisationUnitID
FROM
organisationUnit ou,
orgunitpositionlink opl,
positionholderlink phl,
Position pos2
WHERE phl.username = :supervisorUsername
AND phl.positionid = opl.positionid
AND opl.positionid = pos2.positionid
AND pos2.leadPositionInd = '1'
AND opl.organisationUnitID = ou.organisationUnitID
AND opl.recordStatus <> :recordStatus
AND phl.recordStatus <> :recordStatus
UNION
SELECT
Orgunitparentlink.organisationunitid
FROM
Orgunitparentlink
WHERE Orgunitparentlink.recordStatus <> :recordStatus
AND Orgunitparentlink.parentorganisationunitid IN
(
SELECT
opl1.organisationunitid
FROM
orgunitpositionlink opl1,
positionholderlink phl1,
position pos1
WHERE pos1.positionid = phl1.positionid
AND pos1.leadpositionind = '1'
AND phl1.positionid = opl1.positionid
AND phl1.username = :supervisorUsername
AND opl1.recordStatus <> :recordStatus
AND phl1.recordStatus <> :recordStatus
)
)
UNION
SELECT
distinct prl.positionid
FROM
PositionReportingLink prl,
PositionHolderLink phl2,
Position pos2
WHERE phl2.positionid = prl.reportsToID
AND phl2.positionid = pos2.positionid
AND pos2.LeadPositionInd = '1'
AND phl2.username = :supervisorUsername
AND phl2.recordStatus <> :recordStatus
AND prl.recordStatus <> :recordStatus
)
UNION
SELECT
ch.caseid
FROM
caseheader ch,
caseuserrole cur,
orgobjectlink cool
WHERE ch.ownerOrgObjectLinkID = cur.orgobjectlinkid
AND cur.orgobjectlinkid = cool.orgobjectlinkid
AND cur.caseID = ch.caseID
AND cool.username IN
(
SELECT
users.username
FROM
position,
positionholderlink,
organisationunit,
orgunitpositionlink,
users
WHERE position.positionid = positionholderlink.positionid
AND orgunitpositionlink.positionid = positionholderlink.positionid
AND orgunitpositionlink.organisationunitid = organisationunit.organisationunitid
AND positionholderlink.username =users.username
AND positionholderlink.recordStatus <> :recordStatus
AND orgunitpositionlink.recordStatus <> :recordStatus
AND position.recordStatus <> :recordStatus
AND position.positionid IN
(
SELECT
orgunitpositionlink.positionid
FROM
orgunitpositionlink
WHERE orgunitpositionlink.recordStatus <> :recordStatus
AND orgunitpositionlink.organisationunitid IN
(
SELECT
opl.organisationunitid
FROM
orgunitpositionlink opl,
positionholderlink phl,
Position pos
WHERE pos.positionid = phl.positionid
AND phl.positionid = opl.positionid
AND pos.LeadPositionInd = '1'
AND phl.username = :supervisorUsername
AND phl.recordStatus <> :recordStatus
AND opl.recordStatus <> :recordStatus
UNION
SELECT
Orgunitparentlink.organisationunitid
FROM
Orgunitparentlink
WHERE Orgunitparentlink.recordStatus <> :recordStatus
AND Orgunitparentlink.parentorganisationunitid IN
(
SELECT
opl1.organisationunitid
FROM
orgunitpositionlink opl1,
positionholderlink phl1,
position pos1
WHERE pos1.positionid = phl1.positionid
AND pos1.leadpositionind = '1'
AND phl1.positionid = opl1.positionid
AND phl1.username = :supervisorUsername
AND opl1.recordStatus <> :recordStatus
AND phl1.recordStatus <> :recordStatus
)
)
UNION
SELECT
prl.positionid
FROM
PositionReportingLink prl,
PositionHolderLink phl2,
Position pos2
WHERE phl2.positionid = prl.reportsToID
AND phl2.positionid = pos2.positionid
AND pos2.LeadPositionInd = '1'
AND phl2.username = :supervisorUsername
AND phl2.recordStatus <> :recordStatus
AND prl.recordStatus <> :recordStatus
)
)
)
AND appealrelationship.statuscode <> :appealStatusCode
AND ch2.statusCode <> :caseStatusCode
AND ch2.ownerOrgObjectLinkID = cool.OrgObjectLinkID
AND appealrelationship.caseid = ch2.caseid
GROUP
by appealrelationship.caseid,
ch2.caseReference,
ch2.statuscode,
ch2.startDate,
cool.orgObjectType,
cool.orgObjectReference,
cool.userName
ORDER BY numberOfAppeals DESC
|
