Operation Details
Description: Check thats a sanction record exists already, with the same sanction reason, sanction level, program and within the same sanction date range, for the household member.
Sterotype: ns
SQL 
SELECT
  COUNT(*)
INTO
  :numberOfRecords 
FROM
  Sanction 
WHERE 
  (
    Sanction.sanctionID <> :sanctionID 
    OR :sanctionIDIsNull <> '0' 
  ) 
  AND Sanction.concernRoleID = :concernRoleID 
  AND Sanction.statusCode = :statusCode 
  AND Sanction.sanctionReason = :sanctionReason 
  AND 
	(((:program <> 'SPR1') AND
	(
	Sanction.program = :program 
	OR Sanction.program = 'SPR1'
	)) OR 
        ((:program = 'SPR1') AND
	(
	Sanction.program IS NOT NULL
	)))
  AND 
  (
    
    (
      :sanctionEndDate >= Sanction.sanctionStartDate 
      OR :sanctionEndDate IS NULL 
    ) 
    AND 
    (
      :sanctionStartDate <= Sanction.sanctionEndDate 
      OR Sanction.sanctionEndDate IS NULL 
    ) 
  )