SQL

CREATE OR REPLACE FUNCTION COMPARE_DATE(p_idSociete in VARCHAR2, dateTrait in DATE, dateComp in DATE)
RETURN number -- return 1 if dateTrait (or inferior) equal dateComp
IS
------------------------------------------------
-- $Revision: 1.7 $ - @version.fullname@
------------------------------------------------
/*---------------------------------------------------------------------------------------------------

   Function: COMPARE_DATE

     Compare if two date are equals or inferior for a selected portfolio company (ie WE and public holidays)

   Parameters:

     p_idsociete  - portfolio company identifier (mandatory)
     dateTrait    - date of traitment 
     datedomp     - comparison date 

   Returns:

      1 if datetrait is equal or inferior to datecomp, 0 otherwize  (NUMBER)
   
---------------------------------------------------------------------------------------------------*/

dateTraitLoop Date; -- date of traitment that can down in day in order to take saturday, sunday or a public holiday 
dateJNO Date; -- date of public holiday
i number := 0; -- increment
pCTX plog.log_ctx := plog.init( 'SID'||sys_context('USERENV', 'SID') );
BEGIN
  
  plog.setBeginSection(pCTX, 'COMPARE_DATE');
  -- for debug purpose remove comment at the beginning of the next line
  --plog.setLevel (pCTX, PLOG.LDEBUG);

-- Treatment of the current day
  IF (trunc(dateTrait, 'DD') = dateComp) THEN RETURN 1;

  ELSE
-- Treatment of saturday, sunday, or public holiday
    LOOP
       i := i + 1;
       dateTraitLoop := dateTrait - i;
       plog.debug(pCTX,' treatment date:  ' ||  dateTraitLoop);
       plog.debug(pCTX,'treatment date day:  ' ||  to_char(dateTraitLoop, 'DAY'));
       
       -- Do comparaison with saturday or sunday
       IF (
             (to_char(dateTraitLoop, 'DY') = 'SAT') OR  -- saturday found
           (to_char(dateTraitLoop, 'DY') = 'SUN')) THEN  -- sunday found

                   plog.debug(pCTX,'saturday, sunday, Comparaison between:  ' ||  dateTraitLoop);
            plog.debug(pCTX,'and:  ' ||  dateComp);
            IF (trunc(dateTraitLoop, 'DD') = dateComp) THEN RETURN 1; -- comparaison between saturday or sunday
            END IF;
            
       ELSE
       -- Do comparaison with public holiday
           select jno.DT_JNO into dateJNO from jno where to_char(jno.DT_JNO,'DD/MM/YYYY') = to_char(dateTraitLoop, 'DD/MM/YYYY') and jno.ID_SOCIETE = p_idSociete;
          IF (dateJNO is not null) THEN 
                  plog.debug(pCTX,'public holiday, Comparaison between:  ' ||  dateTraitLoop);
               plog.debug(pCTX,'and:  ' ||  dateComp);
               IF (trunc(dateJNO, 'DD') = dateComp) THEN RETURN 1; -- comparaison with a public holiday
               END IF;
          ELSE RETURN 0;
          END IF;
      END IF;
     END LOOP;
     END IF;

     plog.setendsection(pCTX, 'COMPARE_DATE');
   RETURN 0;
END COMPARE_DATE;
/