SELECT
  ProductDelivery.caseID,
  ProductDelivery.certificationFrequency,
  ProductDelivery.freqOverrideInd,
  ProductDelivery.estDeliveryCost,
  ProductDelivery.productID,
  ProductDelivery.productProviderID,
  ProductDelivery.productType,
  ProductDelivery.providerLocationID,
  ProductDelivery.overrideMDRInd,
  CaseHeader.endDate,
  CaseHeader.ownerOrgObjectLinkID,
  CaseHeader.priorityCode,
  CaseHeader.startDate,
  CaseHeader.statusCode,
  CaseHeader.classificationCode,
  CaseHeader.comments,
  CaseHeader.expectedEndDate,
  CaseHeader.objectiveCode,
  CaseHeader.outcomeCode,
  CaseHeader.expectedOutcome,
  CaseHeader.actualOutcome,
  CaseHeader.concernRoleID,
  CaseHeader.receivedDate,
  Outcome.name,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType,
  Product.name,
  Product.liabilityReconciliationType,
  Product.estimateCostInd,
  ProductProvider.name,
  ProviderLocation.name
INTO
  :caseID,
  :certificationFrequency,
  :certificationFrequencyInd,
  :estimatedCost,
  :productID,
  :productProviderID,
  :productType,
  :locationID,
  :overrideMDRInd,
  :caseEndDate,
  :ownerOrgObjectLinkID,
  :casePriorityCode,
  :caseStartDate,
  :caseStatusCode,
  :classificationCode,
  :comments,
  :expectedEndDate,
  :objectiveCode,
  :outcomeCode,
  :expectedOutcome,
  :actualOutcome,
  :primaryClientID,
  :receivedDate,
  :outcomeName,
  :primaryClientName,
  :primaryClientType,
  :productName,
  :liabilityReassessmentType,
  :estimatedCostApplicable,
  :productProviderName,
  :locationName 
FROM
  ProductDelivery,
  ConcernRole,
  Product,
  ProductProvider,
  ProviderLocation,
  CaseHeader 
  
  LEFT OUTER JOIN
  
  Outcome 
  ON Outcome.outcomeID = CaseHeader.expectedOutcome 
WHERE ProductDelivery.caseID = :caseID 
  AND CaseHeader.caseID = ProductDelivery.caseID 
  AND ConcernRole.concernRoleID = CaseHeader.concernRoleID 
  AND Product.productID = ProductDelivery.productID 
  AND ProductProvider.concernRoleID = ProductDelivery.productProviderID 
  AND ProviderLocation.providerLocationID = ProductDelivery.providerLocationID