Customer/onces/UWVA/UWVA#34519_IMP_CNT.SQL

SET ECHO ON
SPOOL uwva#34519_IMP_CNT.lst
SET DEFINE OFF

-- AUTO'S
--- Alle geïmporteerde auto's moeten als eigenaar Facilitair bedrijf Inkoop krijgen 
--- Afdelingsnaam=420601 Afdelingskey=1153
UPDATE ins_deel o
   SET ins_alg_ruimte_type = 'A', ins_alg_ruimte_key = 1153
 WHERE O.INS_SRTDEEL_KEY = 258658;

--- Verwijder de koppeltekens uit de kentekens van de auto's
UPDATE INS_DEEL O
   SET INS_DEEL_OMSCHRIJVING = REPLACE (INS_DEEL_OMSCHRIJVING, '-'),
       INS_DEEL_UPPER = REPLACE (INS_DEEL_UPPER, '-')
 WHERE     O.INS_SRTDEEL_KEY = 258658
       AND INS_DEEL_OMSCHRIJVING LIKE '%-%'
       AND INS_DEEL_UPPER LIKE '%-%';

-- Als de vervaldatum van de auto in het verleden ligt, dan moet deze worden toegevoegd aan het object auto 
-- Waarde ins_kenmerk_waarde14 uit FAC_IMP_INS moet naar ins_deel_vervaldatum uit INS_DEEL
update ins_deel O
set O.INS_DEEL_VERVALDATUM = (select  fac.safe_to_date(K.INS_KENMERKDEEL_WAARDE, 'DD-MM-YYYY') from ins_kenmerkdeel k
where K.INS_KENMERK_KEY = 475
and fac.safe_to_date(K.INS_KENMERKDEEL_WAARDE, 'DD-MM-YYYY') <= SYSDATE 
and K.INS_DEEL_KEY=O.INS_DEEL_KEY)
where O.INS_SRTDEEL_KEY=258658;

COMMIT;

-- GEBRUIKERSOVEREENKOMSTEN
-- Na het importeren van gebruikersovereenkomsten moet de opzegtermijn op <geen> gezet worden en de rappeltermijn op 3  maanden gezet worden.
UPDATE CNT_CONTRACT C
   SET C.CNT_CONTRACT_OPZEGTERMIJN = 1, C.CNT_CONTRACT_RAPPELTERMIJN = 5
 WHERE c.INS_DISCIPLINE_KEY = 426;
 
--  Het geïmporteerde personeelsnummer uit de gebruikersovereenkomst moet worden aangevuld met I- en voorloopnullen
UPDATE cnt_kenmerkcontract k
   SET k.cnt_kenmerkcontract_waarde =
          CONCAT ('I-', LPAD (k.cnt_kenmerkcontract_waarde, 6, '0'))
 WHERE cnt_kenmerkcontract_key IN
             (SELECT K.CNT_KENMERKCONTRACT_key
                FROM cnt_contract c, cnt_kenmerkcontract k, cnt_kenmerk m
               WHERE     c.ins_discipline_key = 426
                     AND C.CNT_CONTRACT_KEY = K.CNT_CONTRACT_KEY
                     AND K.CNT_KENMERK_KEY = M.CNT_KENMERK_KEY
                     AND M.CNT_SRTKENMERK_KEY = 652);

 --- In het testbestand zitten contracten met ongeldige persnummers, deze moeten uitgesloten worden van de update
 -- Alle andere gebruikerssovereenkomsten kunnen aan het juiste perslid gekoppeld worden
UPDATE cnt_contract c
   SET CNT_PRS_BEDRIJF_KEY = NULL,
       cnt_prs_perslid_key =
          (SELECT p.prs_perslid_key
             FROM cnt_kenmerkcontract k, cnt_kenmerk m, prs_perslid p
            WHERE     c.ins_discipline_key = 426
                  AND C.CNT_CONTRACT_KEY = K.CNT_CONTRACT_KEY
                  AND K.CNT_KENMERK_KEY = M.CNT_KENMERK_KEY
                  AND M.CNT_SRTKENMERK_KEY = 652
                  AND p.prs_perslid_verwijder IS NULL
                  AND P.PRS_PERSLID_NR = K.CNT_KENMERKCONTRACT_WAARDE)
 WHERE c.ins_discipline_key = 426
       AND c.CNT_contract_key NOT IN (
       SELECT C.CNT_CONTRACT_KEY
  FROM cnt_contract c
 WHERE cnt_contract_key NOT IN
             (SELECT c.cnt_contract_key
                FROM cnt_kenmerkcontract k,
                     cnt_kenmerk m,
                     prs_perslid p,
                     cnt_contract c
               WHERE     c.ins_discipline_key = 426
                     AND C.CNT_CONTRACT_KEY = K.CNT_CONTRACT_KEY
                     AND K.CNT_KENMERK_KEY = M.CNT_KENMERK_KEY
                     AND M.CNT_SRTKENMERK_KEY = 652
                     and p.prs_perslid_verwijder is null
                     AND P.PRS_PERSLID_NR = K.CNT_KENMERKCONTRACT_WAARDE)
       AND c.ins_discipline_key = 426   
       );

-- Vervolgens kunnen de juiste auto's aan de gebruikersovereenkomsten gekoppeld worden obv het kenteken (zonder koppeltekens)
INSERT INTO cnt_contract_object (CNT_CONTRACT_KEY, CNT_INS_SRTDEEL_KEY, CNT_INS_DEEL_KEY)
   SELECT C.CNT_CONTRACT_KEY, O.INS_SRTDEEL_KEY, o.INS_DEEL_KEY
     FROM CNT_CONTRACT c, INS_DEEL o
    WHERE     C.CNT_CONTRACT_OMSCHRIJVING = O.INS_DEEL_OMSCHRIJVING
          AND C.INS_DISCIPLINE_KEY = 426
          AND O.INS_SRTDEEL_KEY = 258658;

-- Het contractnummer en de contractomschrijving van de berijdersovereenkomst moet uit het kenteken, /  en het persnr van de berijder bestaan
UPDATE CNT_CONTRACT C
SET c.cnt_contract_nummer_intern=(SELECT CONCAT(C.CNT_CONTRACT_OMSCHRIJVING, '/')
                                                FROM PRS_PERSlid P WHERE C.CNT_PRS_PERSLID_KEY=P.PRS_PERSLID_KEY)
WHERE c.INS_DISCIPLINE_KEY = 426;

UPDATE CNT_CONTRACT C
SET c.cnt_contract_nummer_intern=(SELECT CONCAT(C.CNT_CONTRACT_NUMMER_INTERN, P.prs_perslid_NR)
                                                FROM PRS_PERSlid P WHERE C.CNT_PRS_PERSLID_KEY=P.PRS_PERSLID_KEY)
WHERE c.INS_DISCIPLINE_KEY = 426;

UPDATE CNT_CONTRACT C
SET c.cnt_contract_omschrijving=CONCAT('GO ', C.CNT_CONTRACT_NUMMER_INTERN)
WHERE c.INS_DISCIPLINE_KEY = 426;

COMMIT;

-- LEASECONTRACTEN
-- Na het importeren van lease contracten moet de opzegtermijn op <geen> gezet worden en de rappeltermijn op 3  maanden gezet worden.
UPDATE CNT_CONTRACT C
   SET C.CNT_CONTRACT_OPZEGTERMIJN = 1, C.CNT_CONTRACT_RAPPELTERMIJN = 5
 WHERE c.INS_DISCIPLINE_KEY = 425;

-- De koppeltekens in de kentekens bij de leasecontracten moet verwijderd worden
UPDATE CNT_CONTRACT C
   SET CNT_CONTRACT_NUMMER_INTERN = REPLACE (CNT_CONTRACT_NUMMER_INTERN, '-')
 WHERE c.INS_DISCIPLINE_KEY = 425 AND c.CNT_CONTRACT_NUMMER_INTERN LIKE '%-%';
 
UPDATE CNT_CONTRACT C
   SET C.CNT_CONTRACT_OMSCHRIJVING = REPLACE (C.CNT_CONTRACT_OMSCHRIJVING, '-')
 WHERE C.INS_DISCIPLINE_KEY = 425;

-- Vervolgens kan o.b.v. het kenteken de juiste auto aan het leasecontract gekoppeld worden
INSERT INTO cnt_contract_object (CNT_CONTRACT_KEY, CNT_INS_SRTDEEL_KEY, CNT_INS_DEEL_KEY)
   SELECT C.CNT_CONTRACT_KEY, O.INS_SRTDEEL_KEY, o.INS_DEEL_KEY
     FROM CNT_CONTRACT c, INS_DEEL o
    WHERE     C.CNT_CONTRACT_NUMMER_INTERN = O.INS_DEEL_OMSCHRIJVING
          AND C.INS_DISCIPLINE_KEY = 425
          AND O.INS_SRTDEEL_KEY = 258658;

COMMIT;

BEGIN adm.systrackscriptId('$Id$', 0); END;
/
COMMIT;
SPOOL OFF;