#ifdef CNT
/*
 *  $Revision$
 *  $Id$
 */

CREATE OR REPLACE PACKAGE cnt AS
  PROCEDURE setcontractstatus (pcontractkey IN NUMBER, pstatus IN NUMBER, puserkey IN NUMBER);
  FUNCTION getVerlengschemaEinddatum(startdatum IN DATE, cnt_key IN NUMBER, volgnr IN NUMBER) RETURN DATE;
  FUNCTION cnt_getTermijndatum (pdate IN DATE, ptermijn_key IN NUMBER, prichting IN NUMBER) RETURN DATE;
  FUNCTION termijnjaarfactor (ptermijn_key IN NUMBER) RETURN NUMBER;
  FUNCTION cnt_getOpzegdatum (pcnt_contract_key IN NUMBER) RETURN DATE;
  FUNCTION cnt_getRappeldatum (pcnt_contract_key IN NUMBER) RETURN DATE;
  FUNCTION cnt_contract_status (van IN DATE, rappel IN DATE, opzeg IN DATE, tot IN DATE) RETURN NUMBER;
  PROCEDURE autoverleng_contract;
  PROCEDURE autoinactiveer_contract;

  PROCEDURE splitscontract (p_key IN NUMBER, p_datum IN DATE, p_user IN NUMBER, p_amount NUMBER);
  PROCEDURE generatefactuurschema (pcontractkey IN NUMBER,ptermijnbedrag   IN NUMBER DEFAULT NULL);

  FUNCTION getnieuwsteversiekey(p_key IN NUMBER) RETURN NUMBER;
  FUNCTION sprintf (ps IN VARCHAR2 , p_contract_key IN NUMBER) RETURN VARCHAR2;
  PROCEDURE remove(p_contract_key IN NUMBER);

  PROCEDURE notifycontractbedrijven (pcntkey               NUMBER,
                                     ptrackingkey          NUMBER,
                                     pnotidelay            NUMBER DEFAULT NULL);

  PRAGMA RESTRICT_REFERENCES (cnt_contract_status, WNDS, WNPS);
END cnt;
/

CREATE OR REPLACE PACKAGE BODY cnt AS
    -- Geef contractkey, gewenste status en userkey, en de status wortdt
    -- gezet, waarbij zonodig (indien verandering) een trackrecord wordt gemaakt
    -- Als de status niet wijzigt, wordt ook geen trackrecord gemaakt
    -- ZIE schema StateDiagramContracten.vsd
   PROCEDURE setcontractstatus (pcontractkey IN NUMBER, pstatus IN NUMBER, puserkey IN NUMBER)
   AS
      currentstatus cnt_contract.cnt_contract_status%TYPE;
      newstatus cnt_contract.cnt_contract_status%TYPE;
      eventcode VARCHAR(7); -- fac_srtnotificatie.fac_srtnotificatie_code%TYPE is te klein voor '@CNTNEW'
   BEGIN
      SELECT   cnt_contract_status
        INTO   currentstatus
        FROM   cnt_contract
       WHERE   cnt_contract_key = pcontractkey;

      CASE pstatus
         WHEN 0                                                                                             -- Actief
         THEN
            IF currentstatus IS NULL
            THEN
               newstatus := pstatus;
               eventcode := 'CNTNEW'; -- direct ook notificeren
            ELSIF currentstatus = 2 OR currentstatus = 3         -- Het kan ook zijn dat er geen fiattering nodig is.
            THEN
               newstatus := pstatus;
               eventcode := 'CNTFIT';
               fac.notifytrackingbedrijven('CNTNEW', pcontractkey); -- Nu alsnog notificeren, we zijn helemaal klaar met fiatteren
            END IF;
         WHEN 1                                                                                             -- Gesloten
         THEN
            IF currentstatus = 0
            THEN
               newstatus := pstatus;
               eventcode := 'CNTCLO';
            END IF;
         WHEN 2                                                                                             -- Nieuw
         THEN
            IF currentstatus IS NULL
            THEN
               newstatus := pstatus;
               eventcode := '@CNTNEW'; -- nog niet notificeren
            ELSIF currentstatus = 3
            THEN
               newstatus := pstatus;
               eventcode := 'CNTREJ';
            END IF;
         WHEN 3                                                                                             -- Ter goedkeuring
         THEN
            IF currentstatus = 2 OR currentstatus IS NULL
            THEN
               newstatus := pstatus;
               eventcode := 'CNTCPT';
            END IF;
         ELSE
            -- Invalid statuschange or no change
            newstatus := NULL;
      END CASE;

      IF newstatus IS NOT NULL
      THEN
         -- vooralsnog lopen de notificaties 1-op-1 met de tracking
         -- noticode := eventcode;
         UPDATE   cnt_contract
            SET   cnt_contract_status = newstatus
          WHERE   cnt_contract_key = pcontractkey;

         -- We know that trackaction doesnt do tracking if eventcode is null
         fac.trackaction (eventcode, pcontractkey, puserkey, NULL, NULL);
      END IF;
   END;

   -- Bepaal de einddatum na x iteraties van het verlengschema vanaf een startdatum.
   FUNCTION getVerlengschemaEinddatum(startdatum IN DATE, cnt_key IN NUMBER, volgnr IN NUMBER) RETURN DATE
   IS
      CURSOR c_verleng
      IS
         SELECT cnt_verlengschema_volgnr
              , cnt_verlengschema_verlengtermijn
           FROM cnt_verlengschema
          WHERE cnt_verlengschema_volgnr <= volgnr
            AND cnt_contract_key = cnt_key;

      einddatum DATE := startdatum;
   BEGIN
      FOR c IN c_verleng
      LOOP
         einddatum := cnt.cnt_getTermijndatum(einddatum, c.cnt_verlengschema_verlengtermijn, 1);
      END LOOP;

      RETURN einddatum;
   END;

   -- Deze functie bepaalt de datum door het termijn (ptermijn_key) van de datum (pdate) in mindering te brengen.
   -- Indien de 29, 30 of 31 datum niet in de betreffende maand voorkomt wordt de eerste van de volgende maand genomen.
   -- prichting geeft aan of ten opzichte van pdate achteruit (-1) of vooruit (1, of anders) moet worden gerekend.
   FUNCTION cnt_getTermijndatum (pdate IN DATE, ptermijn_key IN NUMBER, prichting IN NUMBER)
      RETURN DATE
   IS
      ptermijn_type VARCHAR2(1);
      ptermijn_aantal NUMBER;
      lrichting NUMBER;
   BEGIN
      IF prichting = -1
      THEN
        lrichting :=  -1;
      ELSE
        lrichting := 1;
      END IF;

      -- Bepaal type en aantal periodes
      IF ptermijn_key IS NOT NULL
      THEN
        SELECT cnt_termijn_type,
               cnt_termijn_aantal
          INTO ptermijn_type,
               ptermijn_aantal
          FROM cnt_termijn
         WHERE cnt_termijn_key = ptermijn_key;
      ELSE
        -- Als ptermijn_key leeg/ongedefinieerd, return pdate (ofwel termijn dan impliciet 0 dagen)!
        RETURN pdate;
      END IF;

      IF ptermijn_type = 'D'
      THEN
        RETURN pdate + (lrichting * ptermijn_aantal);
      ELSIF ptermijn_type = 'W'
      THEN
        RETURN pdate + (lrichting * ptermijn_aantal * 7);
      ELSIF ptermijn_type = 'M'
      THEN
        RETURN fac.addMonths(pdate, lrichting * ptermijn_aantal);
      ELSE
        -- ptermijn_type = 'Y'
        RETURN fac.addMonths(pdate, lrichting * ptermijn_aantal * 12);
      END IF;
   END;

   -- Deze functie benadert de factor om een contracttermijn te normaliseren tot een (1) jaar.
   -- Als dat niet kan (ongeldige termijn) wordt nu -1 opgeleverd
   -- Vb: 4W -> 13, 2M ->6, 2J-> 0,5
   -- Kan geen rekening houden met schrikkeljaren en 53 weken en zo
   FUNCTION termijnjaarfactor (ptermijn_key IN NUMBER)
      RETURN NUMBER
   IS
      ptermijn_type     VARCHAR2 (1);
      ptermijn_aantal   NUMBER;
   BEGIN
      IF ptermijn_key IS NOT NULL
      THEN
         SELECT cnt_termijn_type, cnt_termijn_aantal
           INTO ptermijn_type, ptermijn_aantal
           FROM cnt_termijn
          WHERE cnt_termijn_key = ptermijn_key;
      ELSE
         -- Als ptermijn_key leeg/ongedefinieerd, return dan maar 1
         RETURN -1;
      END IF;

      IF ptermijn_aantal = 0
      THEN
         RETURN -1;  -- delen door null mag niet he
      END IF;

      IF ptermijn_type = 'D'
      THEN
         RETURN 365 / ptermijn_aantal;
      -- soms 366
      ELSIF ptermijn_type = 'W'
      THEN
         RETURN 52 / ptermijn_aantal;                                                     -- soms 53
      ELSIF ptermijn_type = 'M'
      THEN
         RETURN 12 / ptermijn_aantal;
      ELSE
         -- ptermijn_type = 'Y'
         RETURN 1 / ptermijn_aantal;
      END IF;
   END;

   FUNCTION cnt_getOpzegdatum (pcnt_contract_key IN NUMBER) RETURN DATE IS
       plooptijd_tot DATE;
       popzegtermijn NUMBER;
   BEGIN
      SELECT cnt_contract_looptijd_tot,
             cnt_contract_opzegtermijn
        INTO plooptijd_tot,
             popzegtermijn
        FROM cnt_contract
       WHERE cnt_contract_key = pcnt_contract_key;
       RETURN cnt_getTermijndatum (plooptijd_tot, popzegtermijn, -1);
   END;

   FUNCTION cnt_getRappeldatum (pcnt_contract_key IN NUMBER)
      RETURN DATE
   IS
      plooptijd_tot DATE;
      prappeltermijn NUMBER;
      popzegtermijn NUMBER;
   BEGIN
      SELECT cnt_contract_looptijd_tot,
             cnt_contract_rappeltermijn,
             cnt_contract_opzegtermijn
        INTO plooptijd_tot,
             prappeltermijn,
             popzegtermijn
        FROM cnt_contract
       WHERE cnt_contract_key = pcnt_contract_key;
       RETURN cnt_getTermijndatum (cnt_getTermijndatum (plooptijd_tot, popzegtermijn, -1), prappeltermijn, -1);
   END;

   FUNCTION cnt_contract_status (van IN DATE, rappel IN DATE, opzeg IN DATE, tot IN DATE)
      RETURN NUMBER
   AS
      varSysdate DATE;
   BEGIN
      varSysdate:=SYSDATE;

      IF varSysdate < van THEN
        RETURN 0;
      ELSIF varSysdate <= rappel THEN
        RETURN 1;
      ELSIF varSysdate <= opzeg THEN
        RETURN 2;
      ELSIF varSysdate <= tot THEN
        RETURN 3;
      END IF;

      RETURN 4;
   END;

   PROCEDURE autoverleng_contract
   IS
      CURSOR c_verleng IS
         SELECT c.cnt_contract_key
              , c.ins_discipline_key
              , c.cnt_contract_nummer_intern
              , c.cnt_contract_omschrijving
              , c.cnt_contract_looptijd_tot
              , c.cnt_contract_verleng_termijn
              , o.cnt_termijn_omschrijving  opzegtermijn
              , cnt.cnt_getTermijndatum (c.cnt_contract_looptijd_tot, c.cnt_contract_opzegtermijn, -1) dtopzeggen
              , v.cnt_termijn_omschrijving  verlengtermijn
              , cnt.cnt_getTermijndatum (c.cnt_contract_looptijd_tot, c.cnt_contract_verleng_termijn, 1) dtnieuw
           FROM cnt_contract c
              , cnt_disc_params p
              , cnt_termijn o
              , cnt_termijn v
          WHERE c.ins_discipline_key = p.cnt_ins_discipline_key
            AND c.cnt_contract_opzegtermijn = o.cnt_termijn_key
            AND c.cnt_contract_verleng_termijn = v.cnt_termijn_key
            AND c.cnt_contract_verwijder IS NULL
            AND BITAND(p.cnt_disc_params_opties,1) = 1  -- Dit contracttype mag automatisch verlengd worden
            AND BITAND(c.cnt_contract_verlenging,4) = 4 -- Dit contract mag automatisch verlengd worden
            AND TRUNC(SYSDATE) > cnt.cnt_getTermijndatum (c.cnt_contract_looptijd_tot, c.cnt_contract_opzegtermijn, -1)
            AND NOT EXISTS
                  ( SELECT 1
                      FROM cnt_contract cn
                     WHERE cn.cnt_contract_nummer_intern = c.cnt_contract_nummer_intern
                       AND cn.cnt_contract_versie > c.cnt_contract_versie
                       AND cn.cnt_contract_looptijd_van = (c.cnt_contract_looptijd_tot + 1)
                       AND cn.cnt_contract_verwijder IS NULL
                  );

      tracking                VARCHAR2(4000);
      this_verlengschema_key  cnt_verlengschema.cnt_verlengschema_key%TYPE;
      next_verlengschema_key  cnt_verlengschema.cnt_verlengschema_key%TYPE;
      next_termijn            cnt_termijn.cnt_termijn_key%TYPE;
      infinite_prolongate     NUMBER(1) := 0;
   BEGIN
      FOR cnt_verloopt IN c_verleng
      LOOP
         UPDATE cnt_contract
            SET cnt_contract_looptijd_tot = cnt_verloopt.dtnieuw
          WHERE cnt_contract_key = cnt_verloopt.cnt_contract_key;
         tracking := REPLACE(REPLACE(lcl.l('lcl_cnt_prolongation_tracking')
                                    , '{0}', cnt_verloopt.verlengtermijn
                                    )
                            , '{1}', TO_CHAR(cnt_verloopt.dtnieuw,'dd-mm-yyyy')
                            );
         fac.trackaction ('CNTUPD', cnt_verloopt.cnt_contract_key, NULL, NULL, tracking);
         -- Heeft dit automatisch verlengbaar contract een verlengschema?
         BEGIN
            SELECT this_schema_key
                 , next_schema_key
              INTO this_verlengschema_key
                 , next_verlengschema_key
              FROM ( SELECT cnt_verlengschema_key AS this_schema_key
                          , LEAD(cnt_verlengschema_key) OVER(ORDER BY cnt_verlengschema_volgnr) AS next_schema_key
                       FROM cnt_verlengschema
                      WHERE cnt_contract_key = cnt_verloopt.cnt_contract_key
                   )
             WHERE rownum = 1;
            -- Dan eerstvolgende verlengtermijn uit verlengschema naar het veld verlengtermijn in contract kopieren...
            IF next_verlengschema_key IS NOT NULL
            THEN
               SELECT cnt_verlengschema_verlengtermijn
                 INTO next_termijn
                 FROM cnt_verlengschema
                WHERE cnt_verlengschema_key = next_verlengschema_key;
            ELSE
               IF infinite_prolongate = 1
               THEN
                  next_termijn := cnt_verloopt.cnt_contract_verleng_termijn;
               ELSE
                  next_termijn := NULL;
               END IF;
            END IF;
            UPDATE cnt_contract
               SET cnt_contract_verleng_termijn = next_termijn
             WHERE cnt_contract_key = cnt_verloopt.cnt_contract_key;

            -- ...en deze verlengtermijn uit het verlengschema verwijderen.
            DELETE
              FROM cnt_verlengschema
             WHERE cnt_verlengschema_key = this_verlengschema_key;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
               -- Laat laatste verlengtermijn staan als er geen volgende in het schema gevonden kan worden.
         END;
      END LOOP;
   END;

   PROCEDURE autoinactiveer_contract
   IS
      l_prs_perslid_key prs_perslid.prs_perslid_key%TYPE;
      tracking          VARCHAR2(4000);

      CURSOR c_inactiveer IS
         SELECT c.cnt_contract_key
           FROM cnt_contract c
          WHERE c.cnt_contract_inactiveren = 1
            AND TRUNC(c.cnt_contract_looptijd_tot) < TRUNC(SYSDATE)
            AND NOT EXISTS (SELECT f.fin_factuur_key                       -- Contract mag geen openstaande facturen hebben.
                              FROM fin_factuur f
                             WHERE f.cnt_contract_key = c.cnt_contract_key
                              AND f.fin_factuur_statuses_key NOT IN (1,7)
                              AND f.fin_factuur_verwijder IS NULL);
   BEGIN
      BEGIN
         SELECT prs_perslid_key
           INTO l_prs_perslid_key
           FROM prs_perslid
          WHERE prs_perslid_oslogin = '_SYSTEEM' AND prs_perslid_verwijder IS NULL;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            fac.putsystemnotification ('Invalid configuration: missing _SYSTEEM user', 1);
      END;

      FOR cnt_inactiveer IN c_inactiveer
      LOOP
         cnt.setcontractstatus(cnt_inactiveer.cnt_contract_key, 1, l_prs_perslid_key); -- Zorgt ook voor tracking en daarmee notificatie.

         UPDATE cnt_contract
            SET cnt_contract_inactiveren = 0
          WHERE cnt_contract_key = cnt_inactiveer.cnt_contract_key;

         tracking := lcl.l('lcl_cnt_is_cntupd') || CHR(10) || lcl.l('lcl_cnt_auto_close') || ': ' || lcl.l('lcl_Yes') || lcl.l('lcl_trackto') || lcl.l('lcl_No');
         fac.trackaction('CNTUPD', cnt_inactiveer.cnt_contract_key, NULL, NULL, tracking);
      END LOOP;
   END;

   PROCEDURE splitscontract (p_key IN NUMBER, p_datum IN DATE, p_user IN NUMBER, p_amount NUMBER)
   IS
      begin1            DATE;
      eind1             DATE;
      versie1           cnt_contract.cnt_contract_versie%TYPE;
      newkey            NUMBER;
      nummer1           cnt_contract.cnt_contract_nummer_intern%TYPE;
      amount1           cnt_contract.cnt_contract_kosten%TYPE;
      approve_new       cnt_disc_params.cnt_disc_params_approve_new%TYPE;
      srtcontract_type  cnt_disc_params.cnt_srtcontract_type%TYPE;
      new_status        cnt_contract.cnt_contract_status%TYPE;
      tracking          VARCHAR2 (4000);
      splitsen          BOOLEAN := TRUE;
   BEGIN
      -- Splits het contract met p_key in twee delen: het oude dat eindigt op p_datum - 1,
      -- en een nieuwe kopie die begint op die p_datum
      -- p_amount is -optioneel- het bedrag dat bij het oude contract moeten blijven
      -- de rest komt bij de nieuwe. Als p_amount NULL is, eindigen beide contracten
      -- met het originele bedrag.
      IF p_key IS NULL
      THEN
         RETURN;
      END IF;

      BEGIN
         SELECT   c.cnt_contract_looptijd_van,
                  c.cnt_contract_looptijd_tot,
                  COALESCE (c.cnt_contract_versie, '0'),
                  cnt_contract_nummer_intern,
                  cnt_contract_kosten,
                  cdp.cnt_srtcontract_type,
                  cdp.cnt_disc_params_approve_new,
                  c.cnt_contract_status
           INTO   begin1, eind1, versie1, nummer1, amount1, srtcontract_type, approve_new, new_status
           FROM   cnt_contract c,
                  cnt_disc_params cdp
          WHERE   c.ins_discipline_key = cdp.cnt_ins_discipline_key
            AND   c.cnt_contract_key = p_key;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            RETURN;
      END;

      IF (begin1 < p_datum AND eind1 > p_datum)
      THEN
         -- Splitsen van contract.
         begin1 := p_datum;
         IF (fac.getsetting('cnt_contract_approval') = 1 AND srtcontract_type != 6) -- niet voor mantelcontracten
         THEN
            new_status := 2; -- altijd op nieuw(=concept)
         ELSE
            new_status := 0; -- direct actief
         END IF;
      ELSIF (eind1 < p_datum)
      THEN
         -- Verlengen van contract
         begin1 := eind1 + 1;
         eind1  := p_datum;
         splitsen := FALSE;
      ELSE
         RETURN;
      END IF;

      -- de datum is een zinvolle splitsdatum, alles in orde
      INSERT INTO cnt_contract (cnt_contract_nummer, cnt_contract_omschrijving, cnt_contract_looptijd_tot, cnt_contract_kosten,
                                cnt_contract_uurloon, cnt_prs_bedrijf_key, cnt_prs_afdeling_key, cnt_prs_perslid_key,
                                cnt_contract_verwijder, cnt_contract_nummer_intern, cnt_contract_korting, prs_contactpersoon_key,
                                cnt_contract_contact_persoon, ins_discipline_key, cnt_contract_document,
                                prs_perslid_key_eig, prs_perslid_key_beh, prs_afdeling_key_eig, cnt_contract_status,
                                cnt_contract_looptijd_van, prs_kostenplaats_key, cnt_contract_termijnkosten, prs_kostensoort_key,
                                cnt_contract_opmerking, cnt_contract_mantel_key, cnt_contract_opzegtermijn,
                                cnt_contract_rappeltermijn, cnt_contract_versie, prs_dienst_key, cnt_contract_termijntermijn
                               )
         SELECT   cnt_contract_nummer,
                  cnt_contract_omschrijving,
                  eind1,
                  amount1 - COALESCE(p_amount, 0),
                  cnt_contract_uurloon,
                  cnt_prs_bedrijf_key,
                  cnt_prs_afdeling_key,
                  cnt_prs_perslid_key,
                  NULL,
                  cnt_contract_nummer_intern,
                  cnt_contract_korting,
                  prs_contactpersoon_key,
                  cnt_contract_contact_persoon,
                  ins_discipline_key,
                  cnt_contract_document,
                  prs_perslid_key_eig,
                  prs_perslid_key_beh,
                  prs_afdeling_key_eig,
                  new_status,
                  begin1,
                  prs_kostenplaats_key,
                  cnt_contract_termijnkosten,
                  prs_kostensoort_key,
                  cnt_contract_opmerking,
                  cnt_contract_mantel_key,
                  cnt_contract_opzegtermijn,
                  cnt_contract_rappeltermijn,
                  TO_CHAR (versie1 + 1),
                  prs_dienst_key,
                  cnt_contract_termijntermijn
           FROM   cnt_contract
          WHERE   cnt_contract_key = p_key;

      --- ik wil de nieuwe key weten
      SELECT   cnt_contract_key
        INTO   newkey
        FROM   cnt_contract
       WHERE   cnt_contract_nummer_intern = nummer1
         AND   cnt_contract_versie = TO_CHAR (versie1 + 1)
         AND   cnt_contract_verwijder IS NULL;
      --- Een versieloos oud contract krijgt expliciet versie 0
      --- Looptijd_tot en kosten moteen alleen bij splitsen worden aangepast.
      IF splitsen
      THEN
         UPDATE   cnt_contract
            SET   cnt_contract_looptijd_tot = p_datum - 1,
                  cnt_contract_versie = versie1,
                  cnt_contract_kosten = COALESCE(p_amount, cnt_contract_kosten)
          WHERE   cnt_contract_key = p_key;
      ELSE
         UPDATE   cnt_contract
            SET   cnt_contract_versie = versie1
         WHERE    cnt_contract_key = p_key;
      END IF;

      tracking := lcl.l('lcl_cnt_is_cntupd') || CHR(10) || lcl.l('lcl_cnt_enddate') || ': ' || TO_CHAR(eind1, 'dd-mm-yyyy') || lcl.l('lcl_trackto') || TO_CHAR((p_datum - 1), 'dd-mm-yyyy');
      IF (p_amount IS NOT NULL AND p_amount <> amount1)
      THEN
         tracking := tracking || CHR(10) || lcl.l('lcl_cnt_contract_sum') || ': ' || amount1 || lcl.l('lcl_trackto') || p_amount;
      END IF;
      fac.trackaction ('CNTUPD', p_key, p_user, NULL, tracking);

      -- de basis is gelegd, nu de aanhang meekopieren
      -- flexkenmerken
      INSERT INTO cnt_kenmerkcontract (cnt_contract_key, cnt_kenmerk_key, cnt_kenmerkcontract_waarde
                                      )
         SELECT   newkey, k.cnt_kenmerk_key, k.cnt_kenmerkcontract_waarde
           FROM   cnt_kenmerkcontract k
          WHERE   k.cnt_contract_key = p_key AND k.cnt_kenmerkcontract_verwijder IS NULL;

      -- cnt_contract_dienst (blind kopieren)
      INSERT INTO cnt_contract_dienst (cnt_contract_key, cnt_contract_dienst_contactext, cnt_contract_dienst_contactint, cnt_contract_dienst_materiaal, cnt_contract_dienst_uitvoertd, cnt_contract_dienst_uren, cnt_contract_dienst_uurloon, mld_stdmelding_key, prs_bedrijf_key
                                      )
         SELECT   newkey,
                  cd.cnt_contract_dienst_contactext,
                  cd.cnt_contract_dienst_contactint,
                  cd.cnt_contract_dienst_materiaal,
                  cd.cnt_contract_dienst_uitvoertd,
                  cd.cnt_contract_dienst_uren,
                  cd.cnt_contract_dienst_uurloon,
                  cd.mld_stdmelding_key,
                  cd.prs_bedrijf_key
           FROM   cnt_contract_dienst cd
          WHERE   cd.cnt_contract_key = p_key;

      -- scope en zo
      -- cnt_contract_object
      INSERT INTO cnt_contract_object (cnt_contract_key, cnt_ins_srtdeel_key, cnt_ins_deel_key
                                      )
         SELECT   newkey, cob.cnt_ins_srtdeel_key, cob.cnt_ins_deel_key
           FROM   cnt_contract_object cob
          WHERE   cob.cnt_contract_key = p_key AND cob.cnt_contract_object_verwijder IS NULL;

      --cnt_contract_plaats
      INSERT INTO cnt_contract_plaats (cnt_contract_key, cnt_alg_plaats_code, cnt_alg_plaats_key, cnt_contract_plaats_gewicht
                                      )
         SELECT   newkey, cop.cnt_alg_plaats_code, cop.cnt_alg_plaats_key, cop.cnt_contract_plaats_gewicht
           FROM   cnt_contract_plaats cop
          WHERE   cop.cnt_contract_key = p_key AND cop.cnt_contract_plaats_verwijder IS NULL;

      IF p_amount IS NOT NULL AND amount1 <> 0 AND fac.getsetting('cnt_scopeverdeling') = 1
      THEN -- gewicht is dan telkens een bedrag en cnt_contract_kosten is het totaal daarvan
        UPDATE cnt_contract_plaats
           SET cnt_contract_plaats_gewicht = cnt_contract_plaats_gewicht / amount1 * p_amount
        WHERE cnt_contract_key = p_key;

        UPDATE cnt_contract_plaats
           SET cnt_contract_plaats_gewicht = cnt_contract_plaats_gewicht / amount1 * (amount1 - p_amount)
        WHERE cnt_contract_key = newkey;
      END IF;

      --cnt_factuurschema: splitst op dezelfde grensdatum
      UPDATE   cnt_factuurschema
         SET   cnt_contract_key = newkey
       WHERE   cnt_contract_key = p_key
         AND cnt_factuurschema_boekmaand >= TO_CHAR(p_datum, 'YYYY-MM');

      fac.trackaction ('CNTNEW', newkey, p_user, NULL, NULL);

   END splitscontract;

   -- Genereer een factuurschema met een gelijk bedrag per maand gedurende
   -- de looptijd van het contract
   -- Verondersteld dat er nog geen schema was.
   PROCEDURE generatefactuurschema (
      pcontractkey     IN NUMBER,
      ptermijnbedrag   IN NUMBER DEFAULT NULL)
   AS
      maanden          NUMBER (10);
      maand            NUMBER (10);
      ltermijnbedrag   cnt_factuurschema.cnt_factuurschema_bedrag%TYPE;
   BEGIN
      FOR cnt_rec
         IN (SELECT cnt_contract_kosten,
                    cnt_contract_looptijd_tot,
                    cnt_contract_looptijd_van
               FROM cnt_contract
              WHERE cnt_contract_key = pcontractkey)
      LOOP
         maanden :=
            1
            + TRUNC(MONTHS_BETWEEN (cnt_rec.cnt_contract_looptijd_tot,
                                    cnt_rec.cnt_contract_looptijd_van));

         ltermijnbedrag :=
            COALESCE (ptermijnbedrag, cnt_rec.cnt_contract_kosten / maanden);
         FOR maand IN 0 .. maanden - 1
         LOOP
            INSERT INTO cnt_factuurschema (cnt_contract_key,
                                           cnt_factuurschema_boekmaand,
                                           cnt_factuurschema_bedrag)
                VALUES (pcontractkey,
                        TO_CHAR (
                           ADD_MONTHS (cnt_rec.cnt_contract_looptijd_van, maand),
                           'YYYY-MM'),
                        ltermijnbedrag);
         END LOOP;
      END LOOP;
   END;

   -- Zoek de key van de hoogste revisie van het meegegeven contract
   -- Beseffende dat _versie een oplopende reeks is en hopende dat
   -- nummer_intern hetzelfde blijft over versies en vrij uniek is
   -- Voor wat extra zekerheid wordt bedrijf betrokken
   FUNCTION getnieuwsteversiekey (p_key IN NUMBER)
      RETURN NUMBER
   IS
      nummer1 cnt_contract.cnt_contract_nummer_intern%TYPE;
      versie1 cnt_contract.cnt_contract_versie%TYPE;
      bedrijf1 cnt_contract.cnt_prs_bedrijf_key%TYPE;
      maxversie NUMBER;
      resultkey NUMBER;
   BEGIN
      resultkey := p_key;                                                         -- mocht ik niks vinden, dan is dit em

      SELECT   cnt_contract_nummer_intern, cnt_contract_versie, cnt_prs_bedrijf_key
        INTO   nummer1, versie1, bedrijf1
        FROM   cnt_contract
       WHERE   cnt_contract_verwijder IS NULL AND cnt_contract_key = p_key;

      SELECT   MAX (fac.safe_to_number (cnt_contract_versie))
        INTO   maxversie
        FROM   cnt_contract
       WHERE       cnt_contract_nummer_intern = nummer1
               AND cnt_prs_bedrijf_key = bedrijf1
               AND cnt_contract_verwijder IS NULL;

      IF maxversie IS NOT NULL
      THEN
         SELECT   cnt_contract_key
           INTO   resultkey
           FROM   cnt_contract
          WHERE       cnt_contract_nummer_intern = nummer1
                  AND cnt_prs_bedrijf_key = bedrijf1
                  AND fac.safe_to_number (cnt_contract_versie) = maxversie
                  AND cnt_contract_verwijder IS NULL;
      END IF;

      RETURN resultkey;
   END;

   FUNCTION sprintf (ps IN VARCHAR2 , p_contract_key IN NUMBER )
      RETURN VARCHAR2
   IS
      s VARCHAR2 (2048 CHAR);
      varlev VARCHAR2 (255 CHAR); -- Kan nu ook een samenstelling zijn van prs_perslid gegevens.
      varomsch cnt_contract.cnt_contract_omschrijving%TYPE;
      varnummer cnt_contract.cnt_contract_nummer_intern%TYPE;
   BEGIN
      s :=  ps;
      -- We do support substitution of placeholders in the messages
      -- ##LEV##    = prs_bedrijf_naam
      -- ##OMSCH##  = cnt_contract_omschrijving
      -- ##KEY##    = cnt_contract_nummer_intern
      IF INSTR (s, '#') > 0
      THEN
         SELECT   COALESCE(b.prs_bedrijf_naam, p.prs_perslid_naam_full, a.prs_afdeling_omschrijving),
                  c.cnt_contract_omschrijving,
                  c.cnt_contract_nummer_intern
           INTO   varlev,
                  varomsch,
                  varnummer
           FROM   cnt_contract c,
                  prs_bedrijf b,
                  prs_v_perslid_fullnames_all p,
                  prs_afdeling a
          WHERE   c.cnt_prs_bedrijf_key = b.prs_bedrijf_key(+)
            AND   c.cnt_prs_perslid_key = p.prs_perslid_key(+)
            AND   c.cnt_prs_afdeling_key = a.prs_afdeling_key(+)
            AND   c.cnt_contract_key = p_contract_key;
         s :=
            REPLACE (REPLACE (REPLACE (s, '##LEV##', varlev), '##OMSCH##', varomsch),
                     '##KEY##',
                     varnummer
                    );
      END IF;
      RETURN s;
   END;

   PROCEDURE remove(p_contract_key IN NUMBER)
   IS
       CURSOR c_contract IS
           SELECT cnt_contract_key
             FROM cnt_contract
            WHERE cnt_contract_mantel_key = p_contract_key;

       CURSOR c_factuur IS
           SELECT fin_factuur_key
             FROM fin_factuur
            WHERE cnt_contract_key = p_contract_key;

       CURSOR c_bijlagen(p_refkey IN NUMBER) IS
           SELECT fac_bijlagen_key
             FROM fac_bijlagen b
                , cnt_contract r
            WHERE b.fac_bijlagen_refkey = r.cnt_contract_key
              AND b.fac_bijlagen_module = 'CNT'
              AND b.fac_bijlagen_refkey = p_refkey;

   BEGIN
       -- Verwijder alle subcontracten van dit contract.
       FOR ref_contract IN c_contract
       LOOP
           cnt.remove(ref_contract.cnt_contract_key);
       END LOOP;

       FOR ref_factuur IN c_factuur
       LOOP
           fin.remove(ref_factuur.fin_factuur_key);
       END LOOP;

       FOR ref_bijlagen IN c_bijlagen(p_contract_key)
       LOOP
           flx.deleteflexbijlage (ref_bijlagen.fac_bijlagen_key);
       END LOOP;

       FOR ref_mldopdr IN (SELECT mld_opdr_key
                             FROM mld_opdr
                            WHERE cnt_contract_key = p_contract_key
                           )
       LOOP
           -- Verwijder melding-opdrachten die aan dit contract zitten.
           mld.remove_opdr(ref_mldopdr.mld_opdr_key);
       END LOOP;

       DELETE FROM cnt_contract
        WHERE cnt_contract_key = p_contract_key;

       -- Van de volgende tabellen worden de records die naar deze p_contact_key
       -- verwijzen met ON DELETE CASCADE ook verwijderd:
       --  cnt_contract_note
       --  cnt_contract_object
       --  cnt_contract_plaats
       --  cnt_contract_dienst
       --  cnt_factuurschema
       --  cnt_kenmerkcontract
       -- In de volgende tabel wordt cnt_contract_key leeg gemaakt bij het verwijderen van p_contract_key:
       --  mld_opdr

       fac.remove_tracking('contract', p_contract_key);
       -- NB:
       -- Tabel bes_bestelopdr heeft ook een cnt_contract key, maar deze heeft geen referential integrity constraint.
       -- In geen enkele omgeving is cnt_contract_key in deze tabel ingevuld.
   END;

   PROCEDURE notifycontractbedrijven (pcntkey               NUMBER,
                                      ptrackingkey          NUMBER,
                                      pnotidelay            NUMBER DEFAULT NULL)
   AS
   BEGIN
      FOR bedrijfrec
         IN (SELECT b.prs_bedrijf_key,
                     l.alg_locatie_key,
                     CASE WHEN b.prs_bedrijf_key = c.cnt_prs_bedrijf_key THEN 'C' ELSE 'G' END bedrijfadres_type,
                     cnt_contract_nummer_intern,
                     c.ins_discipline_key,
                     COALESCE (l.alg_locatie_email, ins_discipline_email),
                     d.prs_bedrijf_key                                            persoon_bedrijf_key -- van de melder, niet de uitvoerende
               FROM prs_bedrijf         b,
                     cnt_contract        c,
                     ins_tab_discipline  d,
                     alg_locatie         l,
                     prs_perslid         p,
                     prs_v_afdeling      d,
                     (  SELECT cnt_contract_key,
                              DECODE (MIN (cp1.cnt_alg_plaats_code),
                                       'L', MIN (cp1.cnt_alg_plaats_key),
                                       MIN (alg_boom.alg_locatie_key))
                                    cnt_locatie_key
                           FROM cnt_contract_plaats cp1,
                              (SELECT alg_r.alg_ruimte_key,
                                       alg_r.alg_verdieping_key,
                                       alg_v.alg_gebouw_key,
                                       alg_g.alg_locatie_key,
                                       'R' alg_type
                                 FROM alg_ruimte alg_r, alg_verdieping alg_v, alg_gebouw alg_g
                                 WHERE     alg_r.alg_verdieping_key = alg_v.alg_verdieping_key
                                       AND alg_v.alg_gebouw_key = alg_g.alg_gebouw_key
                                 UNION
                                 SELECT TO_NUMBER (NULL),
                                       alg_v.alg_verdieping_key,
                                       alg_v.alg_gebouw_key,
                                       alg_g.alg_locatie_key,
                                       'V' alg_type
                                 FROM alg_verdieping alg_v, alg_gebouw alg_g
                                 WHERE alg_v.alg_gebouw_key = alg_g.alg_gebouw_key
                                 UNION
                                 SELECT TO_NUMBER (NULL),
                                       TO_NUMBER (NULL),
                                       alg_g.alg_gebouw_key,
                                       alg_g.alg_locatie_key,
                                       'G' alg_type
                                 FROM alg_gebouw alg_g) alg_boom
                        WHERE     cp1.cnt_alg_plaats_key =
                                    CASE cp1.cnt_alg_plaats_code
                                       WHEN 'G' THEN alg_boom.alg_gebouw_key(+)
                                       WHEN 'V' THEN alg_boom.alg_verdieping_key(+)
                                       WHEN 'R' THEN alg_boom.alg_ruimte_key(+)
                                    END
                              AND cp1.cnt_alg_plaats_code = alg_boom.alg_type(+)
                              AND cp1.cnt_contract_plaats_verwijder IS NULL
                     GROUP BY cnt_contract_key
                        HAVING COUNT (1) = 1) cp
               WHERE     c.cnt_contract_key = pcntkey
                     AND d.ins_discipline_key = c.ins_discipline_key
                     AND c.cnt_contract_key = cp.cnt_contract_key(+)
                     AND cp.cnt_locatie_key = l.alg_locatie_key(+)
                     AND d.prs_afdeling_key = p.prs_afdeling_key
                     AND p.prs_perslid_key = c.prs_perslid_key_beh
                     AND (   b.prs_bedrijf_key = c.cnt_prs_bedrijf_key  -- type 'C'
                           OR EXISTS
                                 (SELECT 1
                                    FROM prs_bedrijfadres ba
                                    WHERE ba.prs_bedrijf_key = b.prs_bedrijf_key
                                      AND ba.prs_bedrijfadres_startdatum <= SYSDATE
                                      AND ba.prs_bedrijfadres_type = 'G')))
      LOOP
         fac.notifybedrijf (pbedrijf_key          => bedrijfrec.prs_bedrijf_key,
                            pbedrijfadres_type    => bedrijfrec.bedrijfadres_type,
                            prefkey               => pcntkey,
                            ptrackingkey          => ptrackingkey,
                            preference            => bedrijfrec.cnt_contract_nummer_intern,
                            pdiscipline_key       => bedrijfrec.ins_discipline_key,
                            plocatie_key          => bedrijfrec.alg_locatie_key,
                            pbedrijf_key2         => bedrijfrec.persoon_bedrijf_key,
                            pnotidelay            => pnotidelay);
      END LOOP;
   END;

END cnt;
/

REGISTERRUN('$Id$')

#endif // CNT