-- Customer specific once-script UWVA#25567.
--
-- (c) 2013 SG|facilitor bv
-- $Revision$
-- $Id$
--
-- Support: +31 53 4800700
SET ECHO ON
SPOOL xuwva#25567.lst
SET DEFINE OFF

/* Formatted on 31-1-2013 11:54:30 (QP5 v5.115.810.9015) */
UPDATE   cnt_kenmerkcontract
   SET   cnt_kenmerkcontract_waarde = '10-01-2013'
 WHERE   cnt_kenmerk_key = 700 AND cnt_kenmerkcontract_verwijder IS NULL
         AND cnt_contract_key IN
                  (SELECT   DISTINCT c.cnt_contract_key
                     FROM   cnt_v_aanwezigcontract c,
                            (SELECT   *
                               FROM   cnt_kenmerkcontract
                              WHERE   cnt_kenmerk_key = 700 -- 'Laatste cXML-update' (hidden)
                                      AND cnt_kenmerkcontract_verwijder IS NULL)
                            kc,
                            fac_tracking t,
                            fac_srtnotificatie sn,
                            prs_bedrijf b
                    WHERE       c.ins_discipline_key = 360     -- Abonnementen
                            AND c.cnt_contract_status = 0    -- NIET Gesloten!
                            AND c.cnt_contract_key = kc.cnt_contract_key(+) -- Al verzonden?
                            AND c.cnt_contract_key = t.fac_tracking_refkey
                            AND t.fac_srtnotificatie_key =
                                  sn.fac_srtnotificatie_key
                            AND sn.fac_srtnotificatie_code IN
                                     ('CUST17', 'CUST18', 'CUST19') -- Verlengd/stopgezet/adres gewijzigd
                            AND t.fac_tracking_datum >
                                  TO_DATE ('10-01-2013', 'dd-mm-yyyy')
                            AND c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
                            AND b.prs_bedrijf_order_adres IS NOT NULL
                   UNION
                   SELECT   DISTINCT c.cnt_contract_key
                     FROM   cnt_v_aanwezigcontract c,
                            (SELECT   *
                               FROM   cnt_kenmerkcontract
                              WHERE   cnt_kenmerk_key = 700 -- 'Laatste cXML-update' (hidden)
                                      AND cnt_kenmerkcontract_verwijder IS NULL)
                            kc,
                            fac_tracking t,
                            prs_bedrijf b
                    WHERE   c.ins_discipline_key = 360         -- Abonnementen
                            AND c.cnt_contract_status = 1         -- Gesloten!
                            AND NOT EXISTS -- Beschouw alleen laatste versie van contracten
                                  (SELECT   1
                                     FROM   cnt_v_aanwezigcontract
                                    WHERE   cnt_contract_nummer_intern =
                                               c.cnt_contract_nummer_intern
                                            AND cnt_contract_versie >
                                                  c.cnt_contract_versie)
                            AND c.cnt_contract_key = kc.cnt_contract_key(+) -- Al verzonden?
                            AND c.cnt_contract_key = t.fac_tracking_refkey
                            AND t.fac_srtnotificatie_key = 139     -- 'CNTUPD'
                            AND UPPER (t.fac_tracking_oms) LIKE '%GESLOTEN%'
                            AND t.fac_tracking_datum >
                                  TO_DATE ('10-01-2013', 'dd-mm-yyyy')
                            AND c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
                            AND b.prs_bedrijf_order_adres IS NOT NULL);

COMMIT;

/* Formatted on 31-1-2013 11:54:30 (QP5 v5.115.810.9015) */
UPDATE   cnt_kenmerkcontract
   SET   cnt_kenmerkcontract_wijzig =
            TO_DATE ('10-01-2013 03:00', 'dd-mm-yyyy hh24:mi')
 WHERE   cnt_kenmerk_key = 700 AND cnt_kenmerkcontract_verwijder IS NULL
         AND cnt_contract_key IN
                  (SELECT   DISTINCT c.cnt_contract_key
                     FROM   cnt_v_aanwezigcontract c,
                            (SELECT   *
                               FROM   cnt_kenmerkcontract
                              WHERE   cnt_kenmerk_key = 700 -- 'Laatste cXML-update' (hidden)
                                      AND cnt_kenmerkcontract_verwijder IS NULL)
                            kc,
                            fac_tracking t,
                            fac_srtnotificatie sn,
                            prs_bedrijf b
                    WHERE       c.ins_discipline_key = 360     -- Abonnementen
                            AND c.cnt_contract_status = 0    -- NIET Gesloten!
                            AND c.cnt_contract_key = kc.cnt_contract_key(+) -- Al verzonden?
                            AND c.cnt_contract_key = t.fac_tracking_refkey
                            AND t.fac_srtnotificatie_key =
                                  sn.fac_srtnotificatie_key
                            AND sn.fac_srtnotificatie_code IN
                                     ('CUST17', 'CUST18', 'CUST19') -- Verlengd/stopgezet/adres gewijzigd
                            AND t.fac_tracking_datum >
                                  TO_DATE ('10-01-2013', 'dd-mm-yyyy')
                            AND c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
                            AND b.prs_bedrijf_order_adres IS NOT NULL
                   UNION
                   SELECT   DISTINCT c.cnt_contract_key
                     FROM   cnt_v_aanwezigcontract c,
                            (SELECT   *
                               FROM   cnt_kenmerkcontract
                              WHERE   cnt_kenmerk_key = 700 -- 'Laatste cXML-update' (hidden)
                                      AND cnt_kenmerkcontract_verwijder IS NULL)
                            kc,
                            fac_tracking t,
                            prs_bedrijf b
                    WHERE   c.ins_discipline_key = 360         -- Abonnementen
                            AND c.cnt_contract_status = 1         -- Gesloten!
                            AND NOT EXISTS -- Beschouw alleen laatste versie van contracten
                                  (SELECT   1
                                     FROM   cnt_v_aanwezigcontract
                                    WHERE   cnt_contract_nummer_intern =
                                               c.cnt_contract_nummer_intern
                                            AND cnt_contract_versie >
                                                  c.cnt_contract_versie)
                            AND c.cnt_contract_key = kc.cnt_contract_key(+) -- Al verzonden?
                            AND c.cnt_contract_key = t.fac_tracking_refkey
                            AND t.fac_srtnotificatie_key = 139     -- 'CNTUPD'
                            AND UPPER (t.fac_tracking_oms) LIKE '%GESLOTEN%'
                            AND t.fac_tracking_datum >
                                  TO_DATE ('10-01-2013', 'dd-mm-yyyy')
                            AND c.cnt_prs_bedrijf_key = b.prs_bedrijf_key
                            AND b.prs_bedrijf_order_adres IS NOT NULL);

COMMIT;

/* Formatted on 31-1-2013 14:18:08 (QP5 v5.115.810.9015) */
UPDATE   bes_bestelopdr
   SET   bes_bestelopdr_status = 3
 WHERE       prs_bedrijf_key = 62860
         AND bes_bestelopdr_status IN (4, 5, 6, 7)
         AND bes_bestelopdr_datum > TO_DATE ('10-01-2013', 'dd-mm-yyyy');

COMMIT;

BEGIN adm.systrackscript('$Workfile: uwva#25567.sql $', '$Revision$', 1); END;
/
COMMIT;
SPOOL OFF