SELECT
  CaseHeader.statusCode,
  CaseHeader.caseReference,
  InvestigationDelivery.investigationType,
  InvestigationDelivery.investigationSubtype,
  CaseHeader.registrationDate,
  CaseHeader.concernRoleID,
  ConcernRole.concernRoleName,
  ConcernRole.primaryAlternateID, 
  ConcernRole.primaryPhoneNumberID, 
  EmailAddress.emailAddress,
  Address.addressData,
  OrgObjectLink.orgObjectLinkID,
  Resolution.resolution,
  Resolution.creationDate,
  InitialContactLog.startDateTime,
  InitialContactLog.contactLogType,
  LatestContactLog.startDateTime,
  LatestContactLog.contactLogType
INTO
  :caseStatus,
  :caseReference,
  :investigationType,
  :investigationSubtype,
  :registrationDate,
  :concernRoleID,
  :concernRoleName,
  :clientPrimaryAlternateID,
  :clientPhoneNumberID,
  :clientEmailAddress,
  :clientAddressData,
  :orgObjectLinkID,
  :resolution,
  :resolutionCreationDate,
  :initialContactStartDateTime,
  :initialContactType,
  :latestContactStartDateTime,
  :latestContactType
FROM
  CaseHeader,
  ConcernRole
  LEFT OUTER JOIN EmailAddress 
    ON ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID
  LEFT OUTER JOIN Address 
    ON ConcernRole.primaryAddressID = Address.addressID,
  Provider,
  OrgObjectLink,
  InvestigationDelivery 
  LEFT OUTER JOIN
  (SELECT
    ResolutionConfiguration.resolution,
    Resolution.creationDate,
    Resolution.caseID
  FROM
    Resolution,
    ResolutionConfiguration
  WHERE Resolution.resolutionConfigurationID = ResolutionConfiguration.resolutionConfigurationID
    AND Resolution.recordStatus = :recordStatus) Resolution ON Resolution.caseID = InvestigationDelivery.caseID   
  LEFT OUTER JOIN
  (SELECT
  ContactLog.contactLogType,
  ContactLogLink.linkID,
  UniqueContactLog.startDateTime
FROM
  ContactLogLink,
  ContactLog INNER JOIN
(SELECT
    ContactLog.startDateTime startDateTime,
    MIN(ContactLog.contactLogID) contactLogID
  FROM
    ContactLog,
    ContactLogLink
  WHERE ContactLog.contactLogID = ContactLogLink.contactLogID
    AND ContactLog.recordStatus = :recordStatus
    AND ContactLogLink.recordStatus = :recordStatus
    AND ContactLog.startDateTime = ( 
      SELECT MIN(startDateTime) 
      FROM ContactLog, ContactLogLink
      WHERE ContactLogLink.linkID = :caseID
        AND ContactLogLink.recordStatus = :recordStatus
        AND ContactLog.recordStatus = :recordStatus)
  GROUP BY ContactLog.startDateTime) UniqueContactLog ON ContactLog.contactLogID = UniqueContactLog.contactLogID
  WHERE ContactLog.contactLogID = ContactLogLink.contactLogID
    AND ContactLog.recordStatus = :recordStatus
    AND ContactLogLink.recordStatus = :recordStatus) InitialContactLog ON InitialContactLog.linkID = InvestigationDelivery.caseID   
  LEFT OUTER JOIN
  (SELECT
  ContactLog.contactLogType,
  ContactLogLink.linkID,
  UniqueContactLog.startDateTime
FROM
  ContactLogLink,
  ContactLog INNER JOIN
(SELECT
    ContactLog.startDateTime startDateTime,
    MAX(ContactLog.contactLogID) contactLogID
  FROM
    ContactLog,
    ContactLogLink
  WHERE ContactLog.contactLogID = ContactLogLink.contactLogID
    AND ContactLog.recordStatus = :recordStatus
    AND ContactLogLink.recordStatus = :recordStatus
    AND ContactLog.startDateTime = ( 
      SELECT MAX(startDateTime) 
      FROM ContactLog, ContactLogLink
      WHERE ContactLogLink.linkID = :caseID
        AND ContactLogLink.recordStatus = :recordStatus
        AND ContactLog.recordStatus = :recordStatus)
  GROUP BY ContactLog.startDateTime) UniqueContactLog ON ContactLog.contactLogID = UniqueContactLog.contactLogID
  WHERE ContactLog.contactLogID = ContactLogLink.contactLogID
    AND ContactLog.recordStatus = :recordStatus
    AND ContactLogLink.recordStatus = :recordStatus) LatestContactLog ON LatestContactLog.linkID = InvestigationDelivery.caseID   
WHERE InvestigationDelivery.caseID = :caseID
  AND InvestigationDelivery.caseID = CaseHeader.caseID
  AND OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID
  AND provider.providerConcernRoleID = ConcernRole.concernRoleID