Operation Details
Description: Operation to return issue cases based on the optional search criteria entered. At least one search criteria must be entered (can enter all the three fields). Case Reference can be an Integrated Case or a Product Delivery case. In case of Integrated Case all the Issues that exists on the Integrated Cases are displayed. In the case of Product Delivery case all the Issues that are applied to Product Delivery are displayed. If all or a combination of the search criteria are entered then the result is narrowed down.
Sterotype: nsmulti
SQL 
SELECT
  IssueDelivery.caseID,
  IssueDelivery.issueType,
  IssueCaseHeader.caseReference,
  RelatedCaseHeader.caseID,
  RelatedCaseHeader.caseReference,
  ConcernRole.concernRoleID,
  ConcernRole.concernRoleName,
  ConcernRole.concernRoleType
INTO
  :issueDeliveryCaseID,
  :issueType,
  :issueCaseReference,
  :relatedCaseID,
  :relatedCaseReference,
  :concernRoleID,
  :concernRoleName,
  :concernRoleType 
FROM
  CaseHeader IssueCaseHeader,
  CaseHeader RelatedCaseHeader,
  ConcernRole,
  IssueDelivery 
WHERE 
  (
    :caseReference IS NULL 
    OR RelatedCaseHeader.caseReference = :caseReference 
    AND IssueDelivery.relatedCaseID = RelatedCaseHeader.caseID 
    AND IssueCaseHeader.caseID = IssueDelivery.caseID 
  ) 
  AND 
  (
    :issueReference IS NULL 
    OR IssueCaseHeader.caseReference = :issueReference 
    AND IssueDelivery.caseID = IssueCaseHeader.caseID 
    AND RelatedCaseHeader.caseID = IssueDelivery.relatedCaseID 
  ) 
  AND 
  (
    :concernRoleIDIsNull <>'0' 
    OR IssueCaseHeader.concernRoleID = :concernRoleID 
    AND IssueCaseHeader.caseTypeCode = 'CT10' 
    AND IssueDelivery.caseID = IssueCaseHeader.caseID 
    AND RelatedCaseHeader.caseID = IssueDelivery.relatedCaseID 
  ) 
  AND 
  (
    ConcernRole.concernRoleID = IssueCaseHeader.concernRoleID 
  )