Editing ray

Title / Description

Code
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; /

Author

Highlight as C C++ CSS Clojure Delphi ERb Groovy (beta) HAML HTML JSON Java JavaScript PHP Plain text Python Ruby SQL XML YAML diff code