Operation Details
Description: For a nominee who is not the default nominee on a case, returns a count of assigned objectives for which 'live' financial components do not exist.
Sterotype: ns
SQL 
SELECT
  COUNT(DISTINCT(CaseNomineeObjective.caseNomineeID))
INTO
  :numberOfRecords 
FROM
  CaseNomineeObjective 
WHERE CaseNomineeObjective.caseNomineeID = 
  ( 
  SELECT
    DISTINCT(caseNomineeID) 
  FROM
    CaseNomineeObjective 
  WHERE CaseNomineeObjective.caseNomineeID = :caseNomineeID 
    AND CaseNomineeObjective.statusCode = :recordStatus 
  ) 
  AND 
  (
    CaseNomineeObjective.caseNomineeID NOT IN 
    ( 
    SELECT
      caseNomineeID 
    FROM
      FinancialComponent 
    WHERE FinancialComponent.caseNomineeID = CaseNomineeObjective.caseNomineeID 
    ) 
    OR CaseNomineeObjective.caseNomineeID IN 
    ( 
    SELECT
      FinancialComponent.caseNomineeID 
    FROM
      FinancialComponent 
    WHERE FinancialComponent.caseNomineeID = CaseNomineeObjective.caseNomineeID 
      AND FinancialComponent.statusCode = :statusCode 
    ) 
  )