SELECT tax_payer_no, spouse_no
FROM ind_spouse
GROUP BY tax_payer_no,spouse_no
HAVING ( COUNT(spouse_no) > 1);

delete from ind_spouse
   where (rowid, spouse_no)
    not in
     (select min(rowid), spouse_no from ind_spouse group by spouse_no);

/* CGCC$CHK_CONS_ON_VF_MOD */
/* Validate item against appropriate check constraints */

DECLARE
  v_separation_date  ind_spouse.separation_date%type;
  V_TAX_NO INDIVIDUAL.TAX_PAYER_NO%TYPE;
  v_record_no      number;
  alert_is ALERT := FIND_ALERT('CFG_ERROR');
  alert_button number; 
  min_age number;
  CURSOR C3_SPOUSE IS
   select marriage_end_date,TAX_PAYER_NO ,spouse_no 
      from ind_spouse
      where spouse_no = :ind_spou.spouse_no AND marriage_end_date IS  NULL;
   CURSOR C1_SPOUSE IS
   select marriage_end_date,TAX_PAYER_NO,spouse_no  
      from ind_spouse
      where tax_payer_no  = :ind.tax_payer_no AND marriage_end_date IS NOT NULL;
       

 
BEGIN
  SELECT spouse_min_age 
    into min_age
    FROM SIGTAS_RULES
    WHERE sigtas_rules_no = 1;
   IF :ind_spou.dsp_birth_date is not null THEN
    IF months_between( :IND_SPOU.MARRIAGE_DATE , :IND_SPOU.dsp_birth_date ) /12 <  min_age then
      IRD_MSG(' Marriage date is under minimum date for marriage  ','E',TRUE);    
    END IF;
   END IF;
   IF :IND_SPOU.SEPARATION_DATE IS NOT NULL 
       AND :IND_SPOU.MARRIAGE_DATE IS NOT NULL THEN
    IF :IND_SPOU.MARRIAGE_DATE > :IND_SPOU.SEPARATION_DATE THEN
       IRD_MSG(' Marriage date is ahead of separation date ','E',TRUE);
     END IF;
   END IF;

   VALIDATE_END_MARRIAGE_DATE;

 
  IF :system.record_status = 'INSERT'  or :system.record_status = 'CHANGED' then
    if :ind_spou.marriage_date > sysdate then
      SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,' Marriage date is ahead of current date  ');
      alert_button := SHOW_ALERT(alert_is);  
      raise form_trigger_failure;
   end if; 
 
  

  IF :SYSTEM.RECORD_STATUS = 'CHANGED'    THEN 
    FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
    
        IF  :IND_SPOU.MARRIAGE_DATE  <= IND_SPOU_TAB1.MARRIAGE_END_DATE  then
              --  AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO THEN
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;
  END IF;








    IF :SYSTEM.RECORD_STATUS = 'INSERT'    THEN 
     FOR IND_SPOU_TAB IN  C3_SPOUSE
       LOOP
          SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,'Spouse #  '|| to_char(ind_spou_tab.spouse_no) || ' Already Assigned To Tax Payer  # ' || to_char(ind_spou_tab.tax_payer_no)  );
          alert_button := SHOW_ALERT(alert_is);  
          raise form_trigger_failure;
       END LOOP;
    
      FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
          IF  :IND_SPOU.MARRIAGE_DATE  <= IND_SPOU_TAB1.MARRIAGE_END_DATE  then
           --   AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO THEN
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;
      END IF;
      IF :SYSTEM.RECORD_STATUS = 'INSERT'  
             THEN 
            FOR IND_SPOU_TAB1 IN C1_SPOUSE
       LOOP
          IF :IND_SPOU.MARRIAGE_DATE <= IND_SPOU_TAB1.MARRIAGE_END_DATE 
           AND :IND.TAX_PAYER_NO <> IND_SPOU_TAB1.TAX_PAYER_NO then
            IRD_MSG('Taxpayer already assigned spouse  #:  '|| to_char(ind_spou_tab1.spouse_NO) ||' in this period ','E',TRUE); 
          END IF;
       END LOOP;

      END IF;

  
      IF ( :ind_spou.marriage_date < :ind_spou.dsp_birth_date
             and :ind_spou.dsp_birth_date is not null ) then
           SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,' Marriage Date Exceeds Birth Date Of Spouse  ');
           alert_button := SHOW_ALERT(alert_is);  
           raise form_trigger_failure;
      END IF;      
  END IF;



/* CGUV$CHK_KEYS_ON_VAL_FLD */
/* Check item value against unique or primary key */
  CGUV$CHK_IND_SPOU_PK(
    :IND_SPOU.MARRIAGE_DATE    /* IN : Item value                    */
   ,:IND_SPOU.SPOUSE_NO        /* IN : Item value                    */
   ,:IND_SPOU.TAX_PAYER_NO     /* IN : Item value                    */
   ,TRUE                   );  /* IN : Is the trigger item level?    */
  IRD_MSG('Row exists already with same Tax Payer No,Spouse No,Marriage
Date', 'E', TRUE);
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN OTHERS THEN
    CGTE$OTHER_EXCEPTIONS;
END;

PROCEDURE VALIDATE_END_MARRIAGE_DATE IS
BEGIN
  
 
 
IF :ind_spou.MARRIAGE_END_DATE is not null then
  IF :ind_spou.MARRIAGE_END_DATE > sysdate then
      IRD_MSG(' Separation Date Exceeds Current Date  ','E',TRUE);
     
  END IF;  

 
    IF :IND_SPOU.SEPARATION_DATE IS NOT NULL THEN
       IF :IND_SPOU.MARRIAGE_END_DATE < :IND_SPOU.SEPARATION_DATE THEN
          IRD_MSG(' End date of marriage is earlier than separation date ','E',TRUE);
       END IF;
    END IF; 

   IF  :ind_spou.marriage_date is not null then   
     IF :ind_spou.MARRIAGE_END_DATE <  :ind_spou.marriage_date then
         IRD_MSG(' End date of marriage is earlier than marriage date ','E',TRUE);
     END IF; 
   END IF;

 END IF;
 END;