--
-- $Id$
--
-- Correctie op huurlijst: het huurarchief is wel juist aangemaakt maar er is geen exportbestand aangemaakt
--
DEFINE thisfile = 'NYBU#60032.SQL'
DEFINE dbuser   = '^NYBU'

SET ECHO ON
SET DEFINE ON
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
COLUMN fcltcusterr NEW_VALUE fcltcusterr NOPRINT;
WHENEVER SQLERROR CONTINUE;
SELECT adm.getscriptspoolfile('&thisfile') AS fcltlogfile FROM DUAL;
SPOOL &fcltlogfile
WHENEVER SQLERROR EXIT;
SELECT adm.checkscriptcust('&dbuser') AS fcltcusterr FROM DUAL;
WHENEVER SQLERROR CONTINUE;
PROMPT &fcltcusterr
SET DEFINE OFF

------ payload begin ------
 
-- alle foutief aangemaakte huurregels verwijderen 
DELETE nybu_huur_archief a
 WHERE a.datum_export >= TO_DATE ('31-10-2019', 'dd-mm-yyyy')
 AND PERIODE <> 201911;
 
 
 -- omdat alle vinkjes al verwijderd zijn door een foutieve definitieve export kunnen we niet alles opnieuw doen
 -- de data in het archief is goed dus alleen die opnieuw klaarzetten voor een export
CREATE OR REPLACE PROCEDURE nybu_select_correctie_huur (p_applname   IN VARCHAR2,
                                                   p_applrun    IN VARCHAR2)
AS
   v_aanduiding     VARCHAR2 (200);
   v_errorhint      VARCHAR2 (1000);
   v_errormsg       VARCHAR2 (1000);
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);

   -- Individuele facturen voor debiteuren; ook voor sommatieregel met totaalbedrag
   -- Bedragen zijn incl BTW
   CURSOR c_deb
   IS
        SELECT deb_nr,
               achternaam,
               SUM (bedrag) bedrag,
               betreft,
               periode
          FROM nybu_huur_archief
         WHERE     naar_coda IS NULL
               AND deb_nr NOT LIKE 'P%'
               AND deb_nr != 'salaris'
      GROUP BY deb_nr,
               achternaam,
               betreft,
               periode
      ORDER BY deb_nr;

   -- Individuele facturen per contract voor fin_verkoopfactuur
   -- Bedragen zijn incl BTW
   CURSOR c_deb_cnt
   IS
        SELECT deb_nr,
               achternaam,
               SUM (bedrag) bedrag,
               betreft,
               cnt_contract_nr,
               periode
          FROM nybu_huur_archief
         WHERE     naar_coda IS NULL
               AND deb_nr NOT LIKE 'P%'
               AND deb_nr != 'salaris'
      GROUP BY deb_nr,
               achternaam,
               betreft,
               cnt_contract_nr,
               periode
      ORDER BY deb_nr;

   -- Factuurregels voor deze factuur
   CURSOR regels (in_deb_nr VARCHAR2)
   IS
        SELECT *
          FROM nybu_huur_archief
         WHERE deb_nr = in_deb_nr AND naar_coda IS NULL
      ORDER BY datum_export, omschrijving, bedrag;

   -- Individuele facturen voor interne kostenplaatsen; ook voor sommatieregel met totaalbedrag
   -- Bedragen zijn INCLUSIEF BTW (NYBU#31139)
   CURSOR c_kpn
   IS
        SELECT deb_nr,
               achternaam,
               COALESCE (TRIM (SUBSTR (kg.prs_kostenplaatsgrp_oms, 1, 7)),
                         'ONB')
                  kpn_grp,
               kpn_code_afd,
               kpn_code_proj,
               SUM (bedrag) bedrag,
               betreft
          FROM nybu_huur_archief ha,
               prs_v_aanwezigkostenplaats ak,
               prs_kostenplaatsgrp kg
         WHERE     naar_coda IS NULL
               AND deb_nr LIKE 'P%'
               AND ak.prs_kostenplaats_nr = deb_nr
               AND kg.prs_kostenplaatsgrp_key(+) = ak.prs_kostenplaatsgrp_key
      GROUP BY deb_nr,
               achternaam,
               prs_kostenplaatsgrp_oms,
               kpn_code_afd,
               kpn_code_proj,
               betreft
      ORDER BY deb_nr, betreft;

   CURSOR c_salaris
   IS
        SELECT COALESCE (prs_perslid_nr, prs_perslid_email) personeelsnr,
               achternaam,
               kpn_code_afd,
               kpn_code_proj,
               bedrag,
               btw_perc,
               omschrijving
          FROM nybu_huur_archief b, prs_perslid p
         WHERE     naar_coda IS NULL
               AND deb_nr = 'salaris'
               AND p.prs_perslid_key = b.prs_perslid_key
      ORDER BY achternaam, omschrijving;

   v_teller         NUMBER;
   v_rap_count      NUMBER;
   v_run            DATE;
   v_lastPeriode    VARCHAR (6);
   v_periode        VARCHAR (6);
   v_periodeNaam    VARCHAR2 (20);
   v_last_period    VARCHAR (6);
BEGIN
   v_teller := 0;
   v_rap_count := 0;

   v_aanduiding := 'Init';
   v_errormsg := 'Periode bepalen';


      DELETE imp_log
       WHERE imp_log_applicatie = p_applname;

      DELETE fac_rapport
       WHERE fac_rapport_node = p_applname;

      -- Genereer sommatie- en specificatieregels voor debiteuren tbv CODA export
      FOR rec IN c_deb
      LOOP
         v_aanduiding :=
            v_teller || ': ' || rec.deb_nr || '/' || rec.achternaam;
         v_errormsg := 'Sommatieregel aanmaken';
         v_teller := v_teller + 1;
         v_rap_count := v_rap_count + 1;

         -- Sommatieregel
         INSERT INTO fac_rapport (fac_rapport_node,
                                  fac_rapport_volgnr,
                                  fac_rapport_regel,
                                  fac_rapport_soort)
             VALUES (p_applname,
                     v_rap_count,
                        v_teller
                     || ';'
                     || '10;'
                     || '13000;'
                     || REPLACE (rec.deb_nr, 'CON', 'DC')
                     || ';'
                     || ';'
                     || TRIM(TO_CHAR (ABS (rec.bedrag),
                                      '99999999D99',
                                      'NLS_NUMERIC_CHARACTERS=,.'))
                     || ';'
                     || CASE
                           WHEN rec.bedrag < 0 THEN 'C;'
                           WHEN rec.bedrag >= 0 THEN 'D;'
                        END
                     || ';'
                     || rec.betreft
                     || ' '
                     || v_periodeNaam
                     || ' '
                     || rec.achternaam
                     || ';'
                     || 'Summary;'
                     || 'K91200;'
                     || 'P991210',
                     0);


         -- Specificatieregels
         FOR reg IN regels (rec.deb_nr)
         LOOP
            v_errormsg := 'Specificatieregel aanmaken';
            v_rap_count := v_rap_count + 1;

            INSERT INTO fac_rapport (fac_rapport_node,
                                     fac_rapport_volgnr,
                                     fac_rapport_regel,
                                     fac_rapport_soort)
                VALUES (p_applname,
                        v_rap_count,
                           v_teller
                        || ';'
                        || '12;'
                        || reg.prs_kostensoort_refcode
                        || ';'
                        || reg.kpn_code_afd
                        || ';'
                        || reg.kpn_code_proj
                        || ';'
                        || TRIM(TO_CHAR (ABS (reg.bedrag),
                                         '99999999D99',
                                         'NLS_NUMERIC_CHARACTERS=,.'))
                        || ';'
                        || CASE
                              WHEN reg.bedrag < 0 THEN 'D;'
                              WHEN reg.bedrag >= 0 THEN 'C;'
                           END
                        || reg.btw_code
                        || ';'
                        || reg.omschrijving
                        || ' #'
                        || reg.kamer
                        || ';'
                        || 'Analysis;'
                        || ';'
                        || '',
                        0);

         END LOOP;
      END LOOP;

      -- Scheidingsregel
      v_errormsg := 'Scheidingsregel aanmaken';
      v_rap_count := v_rap_count + 1;

      INSERT INTO fac_rapport (fac_rapport_node,
                               fac_rapport_volgnr,
                               fac_rapport_regel,
                               fac_rapport_soort)
          VALUES (p_applname,
                  v_rap_count,
                  'INTERNE KOSTENPLAATSEN;;;;;;;;;;;',
                  0);

      v_teller := 0;

      -- Genereer sommatie- en specificatieregels voor interne kostenplaatsen
      FOR rec IN c_kpn
      LOOP
         v_aanduiding :=
            v_teller || ': ' || rec.deb_nr || '/' || rec.achternaam;
         v_errormsg := 'Sommatieregel KPN aanmaken';
         v_teller := v_teller + 1;
         v_rap_count := v_rap_count + 1;

         -- Sommatieregel
         INSERT INTO fac_rapport (fac_rapport_node,
                                  fac_rapport_volgnr,
                                  fac_rapport_regel,
                                  fac_rapport_soort)
             VALUES (p_applname,
                     v_rap_count,
                        v_teller
                     || ';'
                     || ';'
                     || '56000;'
                     || rec.kpn_grp
                     || ';'
                     || rec.deb_nr
                     || ';'
                     || TRIM(TO_CHAR (ABS (rec.bedrag),
                                      '99999999D99',
                                      'NLS_NUMERIC_CHARACTERS=,.'))
                     || ';'
                     || CASE
                           WHEN rec.bedrag < 0 THEN 'C;'
                           WHEN rec.bedrag >= 0 THEN 'D;'
                        END
                     || ';'
                     || rec.betreft
                     || ' '
                     || v_periodeNaam
                     || ' '
                     || rec.achternaam
                     || ';'
                     || ';'
                     || ';'
                     || '',
                     0);

         -- Tegenboeking
         v_errormsg := 'Tegenboeking aanmaken';
         v_rap_count := v_rap_count + 1;

         INSERT INTO fac_rapport (fac_rapport_node,
                                  fac_rapport_volgnr,
                                  fac_rapport_regel,
                                  fac_rapport_soort)
             VALUES (p_applname,
                     v_rap_count,
                        v_teller
                     || ';'
                     || ';'
                     || '76000;'
                     || rec.kpn_code_afd
                     || ';'
                     || rec.kpn_code_proj
                     || ';'
                     || TRIM(TO_CHAR (ABS (rec.bedrag),
                                      '99999999D99',
                                      'NLS_NUMERIC_CHARACTERS=,.'))
                     || ';'
                     || CASE
                           WHEN rec.bedrag < 0 THEN 'D;'
                           WHEN rec.bedrag >= 0 THEN 'C;'
                        END
                     || ';'
                     || rec.betreft
                     || ' '
                     || v_periodeNaam
                     || ' '
                     || rec.achternaam
                     || ';'
                     || ';'
                     || ';'
                     || '',
                     0);
      END LOOP;

      -- Scheidingsregel
      v_errormsg := 'Scheidingsregel aanmaken';
      v_rap_count := v_rap_count + 1;

      INSERT INTO fac_rapport (fac_rapport_node,
                               fac_rapport_volgnr,
                               fac_rapport_regel,
                               fac_rapport_soort)
          VALUES (p_applname,
                  v_rap_count,
                  'SALARISADMINISTRATIE;;;;;;;;;;;',
                  0);

      v_teller := 0;

      -- Genereer specificatieregels voor salarisadministratie
      FOR rec IN c_salaris
      LOOP
         v_aanduiding :=
            v_teller || ': ' || rec.personeelsnr || '/' || rec.achternaam;
         v_errormsg := 'Sommatieregel salarisadministratie aanmaken';
         v_teller := v_teller + 1;
         v_rap_count := v_rap_count + 1;

         -- Sommatieregel
         INSERT INTO fac_rapport (fac_rapport_node,
                                  fac_rapport_volgnr,
                                  fac_rapport_regel,
                                  fac_rapport_soort)
             VALUES (p_applname,
                     v_rap_count,
                        v_teller
                     || ';'
                     || rec.personeelsnr
                     || ';'
                     || rec.achternaam
                     || ';'
                     || rec.kpn_code_afd
                     || ';'
                     || rec.kpn_code_proj
                     || ';'
                     || TRIM(TO_CHAR (ABS (rec.bedrag),
                                      '99999999D99',
                                      'NLS_NUMERIC_CHARACTERS=,.'))
                     || ';'
                     || rec.btw_perc
                     || ';'
                     || rec.omschrijving
                     || ';'
                     || ';'
                     || ';'
                     || ';'
                     || '',
                     0);
      END LOOP;


   COMMIT;

END nybu_select_correctie_huur;
/

CREATE OR REPLACE VIEW NYBU_V_EXPORT_CORRECTIE_HUUR
(
    RESULT,
    RESULT_ORDER
)
AS
      SELECT fac_rapport_regel result, fac_rapport_volgnr result_order
        FROM fac_rapport
       WHERE fac_rapport_node = 'HUUR_CODA'
    ORDER BY fac_rapport_volgnr;

CREATE OR REPLACE PROCEDURE nybu_export_correctie_huur (p_applname   IN VARCHAR2,
                                                   p_applrun    IN VARCHAR2,
                                                   p_filedir    IN VARCHAR2,
                                                   p_filename   IN VARCHAR2)
AS
   v_aanduiding     VARCHAR2 (200);
   v_errorhint      VARCHAR2 (1000);
   v_errormsg       VARCHAR2 (1000);
   oracle_err_num   NUMBER;
   oracle_err_mes   VARCHAR2 (200);

   v_run            DATE;
   v_count          NUMBER;
BEGIN
   v_run := TO_DATE (p_applrun, 'YYYY-MM-DD HH24:MI:SS');

   v_aanduiding := 'Init';
   v_errormsg := 'Init';

   -- Alleen als er iets geëxporteerd is!
   SELECT COUNT ( * )
     INTO v_count
     FROM fac_rapport
    WHERE fac_rapport_node = p_applname;

   IF (v_count > 0)
   THEN
      v_errormsg := 'Exportdatum zetten';

      -- Exportdatum van alle regels van de laatste export zetten
      UPDATE nybu_huur_archief
         SET naar_coda = v_run
       WHERE naar_coda IS NULL;
   END IF;

   fac.writelog (p_applname,
                 'S',
                 'Factuurregels geëxporteerd',
                 v_run);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      oracle_err_num := SQLCODE;
      oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
      v_errormsg :=
            v_errorhint
         || ' ORACLE (error '
         || oracle_err_num
         || '/'
         || oracle_err_mes
         || ')';
      fac.writelog (p_applname,
                    'E',
                    v_aanduiding || v_errormsg,
                    'Onbekende fout!');
      COMMIT;
END nybu_export_correctie_huur;
/

------ payload end   ------

SET DEFINE OFF
BEGIN adm.systrackscriptId ('$Id$', 1); END;
/

COMMIT;
SET ECHO OFF
SPOOL OFF
SET DEFINE ON
PROMPT Logfile of this upgrade is: &fcltlogfile