1555 lines
56 KiB
SQL
1555 lines
56 KiB
SQL
--
|
||
-- $Id$
|
||
--
|
||
-- Script containing customer specific configuration sql statements for kfst: Kien Facility Management Strijp-S
|
||
|
||
DEFINE thisfile = 'KFST.SQL'
|
||
DEFINE dbuser = '^KFST'
|
||
|
||
SET ECHO ON
|
||
SET DEFINE ON
|
||
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
|
||
WHENEVER SQLERROR EXIT;
|
||
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
|
||
WHENEVER SQLERROR CONTINUE;
|
||
SPOOL &fcltlogfile
|
||
SET DEFINE OFF
|
||
|
||
------ payload begin ------
|
||
|
||
|
||
CREATE OR REPLACE PACKAGE KFST
|
||
AS
|
||
PROCEDURE add_xml_row (p_bestand IN VARCHAR2,
|
||
p_regel IN VARCHAR2);
|
||
|
||
PROCEDURE add_xml_element (p_bestand IN VARCHAR2,
|
||
p_tag IN VARCHAR2,
|
||
p_value IN VARCHAR2);
|
||
|
||
PROCEDURE set_kenmerk(p_module IN VARCHAR2,
|
||
p_kenmerk_key IN NUMBER,
|
||
p_link_key IN NUMBER,
|
||
p_kenmerk_waarde IN VARCHAR2,
|
||
p_history IN NUMBER);
|
||
END;
|
||
/
|
||
|
||
CREATE OR REPLACE PACKAGE BODY KFST
|
||
AS
|
||
PROCEDURE add_xml_row (
|
||
p_bestand IN VARCHAR2,
|
||
p_regel IN VARCHAR2)
|
||
AS
|
||
v_index NUMBER;
|
||
BEGIN
|
||
SELECT COALESCE( MAX(fac_rapport_volgnr), 0) + 1
|
||
INTO v_index
|
||
FROM fac_rapport
|
||
WHERE fac_rapport_node = p_bestand;
|
||
|
||
INSERT INTO fac_rapport (
|
||
fac_rapport_node,
|
||
fac_rapport_volgnr,
|
||
fac_rapport_regel)
|
||
VALUES (p_bestand,
|
||
v_index,
|
||
p_regel);
|
||
END;
|
||
|
||
PROCEDURE add_xml_element (
|
||
p_bestand IN VARCHAR2,
|
||
p_tag IN VARCHAR2,
|
||
p_value IN VARCHAR2)
|
||
AS
|
||
BEGIN
|
||
kfst.add_xml_row(p_bestand,
|
||
'<' || p_tag || '>'
|
||
|| xml.char_to_html(p_value)
|
||
|| '</' || p_tag || '>');
|
||
END;
|
||
|
||
PROCEDURE set_kenmerk(p_module IN VARCHAR2,
|
||
p_kenmerk_key IN NUMBER,
|
||
p_link_key IN NUMBER,
|
||
p_kenmerk_waarde IN VARCHAR2,
|
||
p_history IN NUMBER)
|
||
AS
|
||
v_count NUMBER;
|
||
v_niveau VARCHAR2 (1);
|
||
BEGIN
|
||
CASE p_module
|
||
WHEN 'PRS'
|
||
THEN
|
||
-- niveau van prs_kenmerk ophalen, omdat deze ook in prs_kenmerklink wordt
|
||
-- opgeslagen (da's dus redundant..?)
|
||
SELECT prs_kenmerk_niveau
|
||
INTO v_niveau
|
||
FROM prs_kenmerk
|
||
WHERE prs_kenmerk_key = p_kenmerk_key;
|
||
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM prs_kenmerklink
|
||
WHERE prs_link_key = p_link_key
|
||
AND prs_kenmerk_key = p_kenmerk_key
|
||
AND prs_kenmerklink_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE prs_kenmerklink
|
||
WHERE prs_link_key = p_link_key
|
||
AND prs_kenmerk_key = p_kenmerk_key
|
||
AND prs_kenmerklink_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE prs_kenmerklink
|
||
SET prs_kenmerklink_waarde = p_kenmerk_waarde
|
||
WHERE prs_link_key = p_link_key
|
||
AND prs_kenmerk_key = p_kenmerk_key
|
||
AND prs_kenmerklink_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO prs_kenmerklink (prs_kenmerk_key,
|
||
prs_link_key,
|
||
prs_kenmerklink_niveau,
|
||
prs_kenmerklink_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
v_niveau,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
|
||
WHEN 'ALG'
|
||
THEN
|
||
-- niveau van prs_kenmerk ophalen, omdat deze ook in prs_kenmerklink wordt
|
||
-- opgeslagen (da's dus redundant..?)
|
||
SELECT alg_kenmerk_niveau
|
||
INTO v_niveau
|
||
FROM alg_kenmerk
|
||
WHERE alg_kenmerk_key = p_kenmerk_key;
|
||
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM alg_onrgoedkenmerk
|
||
WHERE alg_onrgoed_key = p_link_key
|
||
AND alg_kenmerk_key = p_kenmerk_key
|
||
AND alg_onrgoedkenmerk_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE alg_onrgoedkenmerk
|
||
WHERE alg_onrgoed_key = p_link_key
|
||
AND alg_kenmerk_key = p_kenmerk_key
|
||
AND alg_onrgoedkenmerk_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE alg_onrgoedkenmerk
|
||
SET alg_onrgoedkenmerk_waarde = p_kenmerk_waarde
|
||
WHERE alg_onrgoed_key = p_link_key
|
||
AND alg_kenmerk_key = p_kenmerk_key
|
||
AND alg_onrgoedkenmerk_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO alg_onrgoedkenmerk (alg_kenmerk_key,
|
||
alg_onrgoed_key,
|
||
alg_onrgoed_niveau,
|
||
alg_onrgoedkenmerk_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
v_niveau,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
|
||
WHEN 'MLD'
|
||
THEN
|
||
-- melding- of opdrachtkenmerk?
|
||
SELECT mld_kenmerk_niveau
|
||
INTO v_niveau
|
||
FROM mld_kenmerk
|
||
WHERE mld_kenmerk_key = p_kenmerk_key;
|
||
|
||
CASE v_niveau
|
||
WHEN 'O'
|
||
THEN
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM mld_kenmerkopdr
|
||
WHERE mld_opdr_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkopdr_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE mld_kenmerkopdr
|
||
WHERE mld_opdr_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkopdr_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE mld_kenmerkopdr
|
||
SET mld_kenmerkopdr_waarde = p_kenmerk_waarde
|
||
WHERE mld_opdr_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkopdr_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO mld_kenmerkopdr (mld_kenmerk_key,
|
||
mld_opdr_key,
|
||
mld_kenmerkopdr_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
WHEN 'S'
|
||
THEN
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM mld_kenmerkmelding
|
||
WHERE mld_melding_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkmelding_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE mld_kenmerkmelding
|
||
WHERE mld_melding_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkmelding_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE mld_kenmerkmelding
|
||
SET mld_kenmerkmelding_waarde = p_kenmerk_waarde
|
||
WHERE mld_melding_key = p_link_key
|
||
AND mld_kenmerk_key = p_kenmerk_key
|
||
AND mld_kenmerkmelding_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO mld_kenmerkmelding (mld_kenmerk_key,
|
||
mld_melding_key,
|
||
mld_kenmerkmelding_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
END CASE;
|
||
WHEN 'FIN'
|
||
THEN
|
||
-- niveau van prs_kenmerk ophalen, omdat deze ook in prs_kenmerklink wordt
|
||
-- opgeslagen (da's dus redundant..?)
|
||
SELECT fin_kenmerk_type
|
||
INTO v_niveau
|
||
FROM fin_kenmerk
|
||
WHERE fin_kenmerk_key = p_kenmerk_key;
|
||
|
||
|
||
IF v_niveau = 'F'
|
||
THEN
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM fin_kenmerkfactuur
|
||
WHERE fin_factuur_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactuur_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE fin_kenmerkfactuur
|
||
WHERE fin_factuur_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactuur_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE fin_kenmerkfactuur
|
||
SET fin_kenmerkfactuur_waarde = p_kenmerk_waarde
|
||
WHERE fin_factuur_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactuur_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO fin_kenmerkfactuur (fin_kenmerk_key,
|
||
fin_factuur_key,
|
||
fin_kenmerkfactuur_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
ELSE
|
||
-- Kenmerk bij factuurregel
|
||
SELECT COUNT ( * )
|
||
INTO v_count
|
||
FROM fin_kenmerkfactregel
|
||
WHERE fin_factuurregel_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactregel_verwijder IS NULL;
|
||
|
||
IF v_count = 1
|
||
THEN
|
||
IF p_kenmerk_waarde IS NULL
|
||
THEN
|
||
DELETE fin_kenmerkfactregel
|
||
WHERE fin_factuurregel_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactregel_verwijder IS NULL;
|
||
ELSE
|
||
UPDATE fin_kenmerkfactregel
|
||
SET fin_kenmerkfactregel_waarde = p_kenmerk_waarde
|
||
WHERE fin_factuurregel_key = p_link_key
|
||
AND fin_kenmerk_key = p_kenmerk_key
|
||
AND fin_kenmerkfactregel_verwijder IS NULL;
|
||
END IF;
|
||
ELSE
|
||
IF p_kenmerk_key IS NOT NULL AND p_kenmerk_waarde IS NOT NULL
|
||
THEN
|
||
INSERT INTO fin_kenmerkfactregel (fin_kenmerk_key,
|
||
fin_factuurregel_key,
|
||
fin_kenmerkfactregel_waarde)
|
||
VALUES (p_kenmerk_key,
|
||
p_link_key,
|
||
p_kenmerk_waarde);
|
||
END IF;
|
||
END IF;
|
||
|
||
END IF;
|
||
END CASE;
|
||
END;
|
||
|
||
END;
|
||
/
|
||
|
||
CREATE OR REPLACE PROCEDURE kfst_import_kostenstructuur (
|
||
p_import_key IN NUMBER
|
||
)
|
||
AS
|
||
CURSOR c_cursor
|
||
IS
|
||
SELECT *
|
||
FROM fac_imp_file
|
||
WHERE fac_import_key = p_import_key
|
||
ORDER BY fac_imp_file_index;
|
||
|
||
v_newline VARCHAR2 (1000);
|
||
v_aanduiding VARCHAR2 (1000); -- Import line
|
||
v_field VARCHAR2 (100); -- Import field
|
||
v_fielddelimitor VARCHAR2 (1); -- Field seperator
|
||
v_errormsg VARCHAR (200);
|
||
v_errorhint VARCHAR (200);
|
||
oracle_err_num NUMBER;
|
||
oracle_err_mes VARCHAR2 (200);
|
||
v_skipfield VARCHAR2 (100);
|
||
-- De importvelden
|
||
v_GrootboekNummer VARCHAR2(100);
|
||
v_GrootboekOms VARCHAR2(100);
|
||
v_HoofdstukNummer VARCHAR2(100);
|
||
v_HoofdstukOms VARCHAR2(100);
|
||
v_AccountNummer VARCHAR2(100);
|
||
v_AccountOms VARCHAR2(100);
|
||
header_found BOOLEAN;
|
||
|
||
BEGIN
|
||
-- Init
|
||
header_found := FALSE;
|
||
v_fielddelimitor := ';';
|
||
|
||
-- Clear my previous imported rows
|
||
DELETE FROM kfst_imp_kostenstructuur;
|
||
|
||
FOR rec1 IN c_cursor
|
||
LOOP
|
||
BEGIN
|
||
v_newline := rec1.fac_imp_file_line;
|
||
v_aanduiding := v_newline;
|
||
|
||
IF SUBSTR (v_newline, 1, 3) = '?'
|
||
THEN
|
||
-- EF BB BF aangetroffen
|
||
fac.imp_writelog (p_import_key,
|
||
'W',
|
||
'Byte Order Mark aangetroffen',
|
||
'Bestand heeft onbehandeld UTF-8 formaat.'
|
||
);
|
||
v_newline := SUBSTR (v_newline, 4);
|
||
END IF;
|
||
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_GrootboekNummer);
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_GrootboekOms);
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_HoofdstukNummer);
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_HoofdstukOms);
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_AccountNummer);
|
||
fac.imp_getfield (v_newline, v_fielddelimitor, v_AccountOms);
|
||
|
||
|
||
-- Skip until a valid header is found
|
||
IF UPPER(v_GrootboekNummer) = 'CODE'
|
||
AND UPPER(v_GrootboekOms) = 'OMSCHRIJVING GROOTBOEKREKENING'
|
||
AND UPPER(v_HoofdstukNummer) = 'HOOFDSTUKNUMMER'
|
||
AND UPPER(v_HoofdstukOms) = 'HOOFDSTUKNAAM'
|
||
AND UPPER(v_AccountNummer) = 'ACCOUNTNUMMER'
|
||
AND UPPER(v_AccountOms) = 'ACCOUNTOMSCHRIJVING'
|
||
THEN
|
||
-- Sla de kopregel over.
|
||
header_found := TRUE;
|
||
ELSIF (header_found AND v_GrootboekNummer IS NOT NULL)
|
||
THEN
|
||
INSERT INTO kfst_imp_kostenstructuur
|
||
(GrootboekNummer,
|
||
GrootboekOms,
|
||
HoofdstukNummer,
|
||
HoofdstukOms,
|
||
AccountNummer,
|
||
AccountOms)
|
||
VALUES (v_GrootboekNummer,
|
||
v_GrootboekOms,
|
||
v_HoofdstukNummer,
|
||
v_HoofdstukOms,
|
||
v_AccountNummer,
|
||
v_AccountOms);
|
||
END IF;
|
||
|
||
COMMIT;
|
||
END;
|
||
END LOOP;
|
||
|
||
IF NOT header_found
|
||
THEN
|
||
fac.imp_writelog (p_import_key,
|
||
'E',
|
||
'Geen geldige header aangetroffen',
|
||
'Bestand is geen geldig kostensoort importbestand.'
|
||
);
|
||
END IF;
|
||
EXCEPTION
|
||
WHEN OTHERS
|
||
THEN
|
||
oracle_err_num := SQLCODE;
|
||
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
|
||
v_errormsg := 'OTHERS (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
|
||
fac.imp_writelog (p_import_key, 'E', SUBSTR (v_errormsg, 1, 1000), v_aanduiding);
|
||
-- maak de importtabel leeg om te voorkomen dat er halve bestanden ingelezen worden.
|
||
DELETE FROM kfst_imp_kostenstructuur;
|
||
END;
|
||
/
|
||
|
||
|
||
-- Importfunctie om de kostensoort structuur in de eigentabellen te plaatsen. De structuur
|
||
-- is opgedeeld in 3 niveaus. Eerst het hoofdstuk dan het account en vervolgens het grootboek.
|
||
-- Uiteindelijk wordt het grootboeknummer gekoppeld aan de factuur via de kostensoort.
|
||
-- Eigen tabel waardes: 181 Hoofdstuk
|
||
-- 182 Account
|
||
-- 107 Grootboek / Kostencode
|
||
CREATE OR REPLACE PROCEDURE kfst_update_kostenstructuur (p_import_key IN NUMBER)
|
||
AS
|
||
|
||
CURSOR c
|
||
IS
|
||
SELECT *
|
||
FROM kfst_imp_kostenstructuur;
|
||
|
||
v_oldcount NUMBER;
|
||
v_newcount NUMBER;
|
||
v_kostensoort_key NUMBER;
|
||
v_kostensoortgrp_key NUMBER;
|
||
v_errormsg VARCHAR (1000);
|
||
v_error_hint VARCHAR (1000);
|
||
oracle_err_mes VARCHAR2 (150);
|
||
oracle_err_num VARCHAR2 (150);
|
||
BEGIN
|
||
|
||
SELECT COUNT (*)
|
||
INTO v_newcount
|
||
FROM kfst_imp_kostenstructuur;
|
||
|
||
IF v_newcount = 0 THEN
|
||
fac.imp_writelog (p_import_key,
|
||
'E',
|
||
'KFST kostenstructuur import bevat geen records',
|
||
'$Revision$'
|
||
);
|
||
RETURN;
|
||
END IF;
|
||
|
||
-- How many active records are now present?
|
||
SELECT COUNT (*)
|
||
INTO v_oldcount
|
||
FROM prs_kostensoort; -- grootboekrecords
|
||
|
||
FOR rec IN c
|
||
LOOP
|
||
BEGIN
|
||
BEGIN
|
||
-- Bestaat het account al in de kostensoortgrp tabel
|
||
SELECT prs_kostensoortgrp_key
|
||
INTO v_kostensoortgrp_key
|
||
FROM prs_kostensoortgrp
|
||
WHERE prs_kostensoortgrp_altcode = rec.AccountNummer;
|
||
|
||
-- Zo ja, dan is misschien de omschrijving aangepast.
|
||
UPDATE prs_kostensoortgrp
|
||
SET prs_kostensoortgrp_oms = rec.AccountOms
|
||
WHERE prs_kostensoortgrp_key = v_kostensoortgrp_key;
|
||
EXCEPTION WHEN NO_DATA_FOUND
|
||
THEN
|
||
INSERT INTO prs_kostensoortgrp (prs_kostensoortgrp_altcode, prs_kostensoortgrp_oms)
|
||
VALUES (rec.AccountNummer, rec.AccountOms)
|
||
RETURNING prs_kostensoortgrp_key INTO v_kostensoortgrp_key;
|
||
END;
|
||
|
||
BEGIN
|
||
-- Bestaat het grootboek al in de kostensoort tabel
|
||
SELECT prs_kostensoort_key
|
||
INTO v_kostensoort_key
|
||
FROM prs_kostensoort
|
||
WHERE prs_kostensoort_refcode = rec.GrootboekNummer;
|
||
|
||
-- Zo ja, dan is misschien de omschrijving aangepast.
|
||
UPDATE prs_kostensoort
|
||
SET prs_kostensoort_oms = rec.GrootboekNummer,
|
||
prs_kostensoort_opmerking = rec.GrootboekOms || ' H' || SUBSTR(rec.hoofdstuknummer, 1,1),
|
||
prs_kostensoortgrp_key = v_kostensoortgrp_key
|
||
WHERE prs_kostensoort_key = v_kostensoort_key;
|
||
EXCEPTION WHEN NO_DATA_FOUND
|
||
THEN
|
||
INSERT INTO prs_kostensoort (prs_kostensoort_refcode, prs_kostensoort_oms, prs_kostensoort_opmerking, prs_kostensoortgrp_key)
|
||
VALUES (rec.GrootboekNummer, rec.GrootboekNummer, rec.GrootboekOms || ' H' || SUBSTR(rec.hoofdstuknummer, 1,1), v_kostensoortgrp_key)
|
||
RETURNING prs_kostensoort_key INTO v_kostensoort_key;
|
||
END;
|
||
|
||
|
||
COMMIT;
|
||
EXCEPTION
|
||
WHEN OTHERS
|
||
THEN
|
||
v_error_hint := v_errormsg;
|
||
oracle_err_num := SQLCODE;
|
||
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
|
||
v_errormsg :=
|
||
v_errormsg
|
||
|| 'ORACLE (error '
|
||
|| oracle_err_num
|
||
|| '/'
|
||
|| oracle_err_mes
|
||
|| ')';
|
||
fac.imp_writelog (p_import_key,
|
||
'E',
|
||
v_errormsg,
|
||
v_error_hint
|
||
);
|
||
COMMIT; -- tbv logging
|
||
END;
|
||
END LOOP;
|
||
|
||
SELECT COUNT (*)
|
||
INTO v_newcount
|
||
FROM prs_kostensoort; -- grootboekrecords
|
||
|
||
fac.imp_writelog (p_import_key,
|
||
'S',
|
||
'Aantal kostensoorten vooraf: ' || TO_CHAR (v_oldcount) || ' nu: ' || TO_CHAR (v_newcount),
|
||
''
|
||
);
|
||
|
||
EXCEPTION
|
||
WHEN OTHERS
|
||
THEN
|
||
v_error_hint := v_errormsg;
|
||
oracle_err_num := SQLCODE;
|
||
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
|
||
v_errormsg :=
|
||
v_errormsg
|
||
|| 'ORACLE (error '
|
||
|| oracle_err_num
|
||
|| '/'
|
||
|| oracle_err_mes
|
||
|| ')';
|
||
fac.imp_writelog (p_import_key,
|
||
'E',
|
||
v_errormsg,
|
||
v_error_hint
|
||
);
|
||
COMMIT; -- tbv logging
|
||
END;
|
||
/
|
||
|
||
CREATE OR REPLACE VIEW kfst_v_factuur_gegevens
|
||
(
|
||
fin_factuur_key,
|
||
opdracht_id,
|
||
fin_factuur_datum,
|
||
fin_factuur_nr,
|
||
prs_bedrijf_key,
|
||
fin_factuurregel_omschrijving,
|
||
prs_kostensoort_refcode,
|
||
prs_kostensoort_oms,
|
||
fin_factuurregel_totaal,
|
||
fin_factuurregel_btw,
|
||
fin_btwtabelwaarde_code,
|
||
fin_btwtabelwaarde_key,
|
||
fin_factuurregel_nr,
|
||
fin_factuur_debiteur_nr,
|
||
fin_factuur_opmerking,
|
||
fin_factuur_boekmaand,
|
||
opdr_datum,
|
||
prs_kostenplaats_key,
|
||
mld_opdr_key,
|
||
cnt_contract_key,
|
||
bes_bestelopdr_key,
|
||
fin_factuur_statuses_key,
|
||
lev_kpn,
|
||
kpcode,
|
||
kdcode
|
||
)
|
||
AS
|
||
SELECT f.fin_factuur_key,
|
||
DECODE (
|
||
c.cnt_contract_key,
|
||
NULL, DECODE (
|
||
bo.bes_bestelopdr_key,
|
||
NULL, (SELECT sd.ins_srtdiscipline_prefix
|
||
|| m.mld_melding_key
|
||
|| '/'
|
||
|| o.mld_opdr_bedrijfopdr_volgnr
|
||
FROM mld_melding m,
|
||
mld_stdmelding std,
|
||
ins_tab_discipline d,
|
||
ins_srtdiscipline sd
|
||
WHERE m.mld_melding_key =
|
||
o.mld_melding_key
|
||
AND m.mld_stdmelding_key =
|
||
std.mld_stdmelding_key
|
||
AND std.mld_ins_discipline_key =
|
||
d.ins_discipline_key
|
||
AND d.ins_srtdiscipline_key =
|
||
sd.ins_srtdiscipline_key),
|
||
bo.bes_bestelopdr_id),
|
||
'C'
|
||
|| TO_CHAR (cnt_contract_nummer_intern)
|
||
|| DECODE (cnt_contract_versie,
|
||
NULL, NULL,
|
||
'.' || cnt_contract_versie))
|
||
opdracht_id,
|
||
f.fin_factuur_datum,
|
||
f.fin_factuur_nr,
|
||
COALESCE (b_c.prs_bedrijf_key,
|
||
COALESCE (b_o.prs_bedrijf_key, b_b.prs_bedrijf_key))
|
||
prs_bedrijf_key,
|
||
fr.fin_factuurregel_omschrijving,
|
||
k.prs_kostensoort_refcode,
|
||
k.prs_kostensoort_oms,
|
||
fr.fin_factuurregel_totaal,
|
||
ROUND (
|
||
fr.fin_factuurregel_totaal
|
||
* DECODE (btw.fin_btwtabelwaarde_verlegd,
|
||
1, 0,
|
||
btw.fin_btwtabelwaarde_perc / 100),
|
||
2)
|
||
fin_factuurregel_btw,
|
||
btw.fin_btwtabelwaarde_code,
|
||
btw.fin_btwtabelwaarde_key,
|
||
fr.fin_factuurregel_nr,
|
||
f.fin_factuur_debiteur_nr,
|
||
f.fin_factuur_opmerking,
|
||
f.fin_factuur_boekmaand,
|
||
COALESCE (
|
||
o.mld_opdr_datumbegin,
|
||
COALESCE (c.cnt_contract_looptijd_tot,
|
||
bo.bes_bestelopdr_datum)),
|
||
COALESCE (
|
||
o.prs_kostenplaats_key,
|
||
COALESCE (
|
||
c.prs_kostenplaats_key,
|
||
(SELECT DISTINCT b.prs_kostenplaats_key
|
||
FROM bes_bestelling b,
|
||
bes_bestelopdr_item boi,
|
||
bes_bestelling_item bi
|
||
WHERE bo.bes_bestelopdr_key = boi.bes_bestelopdr_key
|
||
AND boi.bes_bestelopdr_item_key =
|
||
bi.bes_bestelopdr_item_key
|
||
AND bi.bes_bestelling_key = b.bes_bestelling_key))),
|
||
o.mld_opdr_key,
|
||
c.cnt_contract_key,
|
||
bo.bes_bestelopdr_key,
|
||
( -- status van de factuur is ingevoerd als er facturen van dezelfde leverancier en nummer zijn
|
||
-- met status ingevoerd of incompleet
|
||
SELECT DECODE (COUNT (*), 0, f.fin_factuur_statuses_key, 2)
|
||
FROM fin_factuur f_t,
|
||
cnt_contract c_t,
|
||
bes_bestelopdr b_t,
|
||
mld_opdr o_t
|
||
WHERE f_t.fin_factuur_verwijder IS NULL
|
||
AND f_t.mld_opdr_key = o_t.mld_opdr_key(+)
|
||
AND f_t.cnt_contract_key = c_t.cnt_contract_key(+)
|
||
AND f_t.bes_bestelopdr_key = b_t.bes_bestelopdr_key(+)
|
||
AND COALESCE (
|
||
o_t.mld_uitvoerende_keys,
|
||
COALESCE (b_t.prs_bedrijf_key,
|
||
c_t.cnt_prs_bedrijf_key)) =
|
||
COALESCE (
|
||
o.mld_uitvoerende_keys,
|
||
COALESCE (bo.prs_bedrijf_key,
|
||
c.cnt_prs_bedrijf_key))
|
||
AND f.fin_factuur_nr = f_t.fin_factuur_nr
|
||
AND f_t.fin_factuur_statuses_key IN (2, 3)
|
||
AND f.fin_factuur_statuses_key = 6)
|
||
fin_factuur_statuses_key,
|
||
lk.prs_kostenplaats_nr
|
||
lev_kpn,
|
||
(SELECT alg_gebouw_code
|
||
FROM alg_gebouw g
|
||
WHERE g.alg_gebouw_key = COALESCE(cg.alg_gebouw_key, og.alg_gebouw_key)),
|
||
(SELECT alg_gebouw_code
|
||
FROM alg_gebouw g
|
||
WHERE g.alg_gebouw_key = COALESCE(cg.alg_gebouw_key, og.alg_gebouw_key))
|
||
FROM fin_factuur f,
|
||
fin_factuurregel fr,
|
||
fin_btwtabelwaarde btw,
|
||
mld_opdr o,
|
||
mld_melding m,
|
||
mld_typeopdr ot,
|
||
cnt_contract c,
|
||
bes_bestelopdr bo,
|
||
prs_bedrijf b_o,
|
||
prs_bedrijf b_c,
|
||
prs_bedrijf b_b,
|
||
prs_kostensoort k,
|
||
prs_kostensoortgrp kg,
|
||
prs_kostenplaats lk,
|
||
alg_v_allonrgoed_gegevens og,
|
||
(SELECT kfr.fin_factuurregel_key,
|
||
fac.safe_to_number (fin_kenmerkfactregel_waarde)
|
||
alg_gebouw_key
|
||
FROM fin_kenmerkfactregel kfr
|
||
WHERE fin_kenmerk_key = 81) cg
|
||
WHERE f.fin_factuur_key = fr.fin_factuur_key
|
||
AND fr.fin_btwtabelwaarde_key = btw.fin_btwtabelwaarde_key
|
||
AND f.mld_opdr_key = o.mld_opdr_key(+)
|
||
AND o.mld_typeopdr_key = ot.mld_typeopdr_key(+)
|
||
AND o.mld_melding_key = m.mld_melding_key(+)
|
||
AND f.cnt_contract_key = c.cnt_contract_key(+)
|
||
AND f.bes_bestelopdr_key = bo.bes_bestelopdr_key(+)
|
||
AND bo.prs_bedrijf_key = b_b.prs_bedrijf_key(+)
|
||
AND o.mld_uitvoerende_keys = b_o.prs_bedrijf_key(+)
|
||
AND c.cnt_prs_bedrijf_key = b_c.prs_bedrijf_key(+)
|
||
AND k.prs_kostensoort_key = COALESCE(fr.prs_kostensoort_key, f.prs_kostensoort_key)
|
||
AND k.prs_kostenplaats_key = lk.prs_kostenplaats_key(+)
|
||
AND k.prs_kostensoortgrp_key = kg.prs_kostensoortgrp_key(+)
|
||
AND fr.fin_factuurregel_key = cg.fin_factuurregel_key(+)
|
||
AND m.mld_alg_onroerendgoed_keys = og.alg_onroerendgoed_keys(+)
|
||
AND f.fin_factuur_verwijder IS NULL;
|
||
|
||
|
||
-- Klant rapport met extra velden omschrijving en alle notes van de factuur
|
||
CREATE OR REPLACE VIEW kfst_v_factuur_gegevens_notes
|
||
(
|
||
fin_factuur_key,
|
||
fin_factuur_datum,
|
||
prs_bedrijf_naam,
|
||
prs_leverancier_nr,
|
||
fin_factuur_nr,
|
||
discipline,
|
||
bron,
|
||
fin_factuur_opmerking,
|
||
contract_nr,
|
||
totaal,
|
||
fin_factuur_totaal,
|
||
fin_factuur_totaal_incl,
|
||
status,
|
||
fin_factuur_statuses_omschr,
|
||
kostenplaats,
|
||
boekstukNr,
|
||
dispuut,
|
||
notes
|
||
)
|
||
AS
|
||
SELECT fc.fin_factuur_key,
|
||
fc.fin_factuur_datum,
|
||
uitv.prs_bedrijf_naam,
|
||
uitv.prs_leverancier_nr,
|
||
fc.fin_factuur_nr,
|
||
co.discipline,
|
||
CASE fc.fin_factuur_bron
|
||
WHEN 0 THEN 'Handmatig'
|
||
WHEN 1 THEN 'Excel'
|
||
ELSE 'Mail'
|
||
END bron,
|
||
fc.fin_factuur_opmerking,
|
||
co.contract_nr,
|
||
co.totaal,
|
||
fc.fin_factuur_totaal,
|
||
fc.fin_factuur_totaal + fc.fin_factuur_totaal_btw,
|
||
co.status,
|
||
stat.fin_factuur_statuses_omschr,
|
||
(SELECT kost.prs_kostenplaats_nr
|
||
FROM prs_kostenplaats kost
|
||
WHERE kost.prs_kostenplaats_key = co.kost_key) kostenplaats,
|
||
(SELECT fkenmerk.fin_kenmerkfactuur_waarde
|
||
FROM fin_kenmerkfactuur fkenmerk
|
||
WHERE fkenmerk.fin_factuur_key = fc.fin_factuur_key
|
||
AND fkenmerk.fin_kenmerk_key IN (1)) boekstukNr,
|
||
(SELECT fkenmerk.fin_kenmerkfactuur_waarde
|
||
FROM fin_kenmerkfactuur fkenmerk
|
||
WHERE fkenmerk.fin_factuur_key = fc.fin_factuur_key
|
||
AND fkenmerk.fin_kenmerk_key IN (61)) dispuut, -- 41 op Test
|
||
(SELECT LISTAGG (
|
||
TO_CHAR (notes.fin_factuur_note_aanmaak, 'DD-MM-YYYY')
|
||
|| ':'
|
||
|| CHR (13)
|
||
|| CHR (10)
|
||
|| notes.fin_factuur_note_omschrijving,
|
||
CHR (13)|| CHR (10))
|
||
WITHIN GROUP (ORDER BY notes.fin_factuur_note_key)
|
||
FROM fin_factuur_note notes
|
||
WHERE notes.fin_factuur_key = fc.fin_factuur_key) notes
|
||
FROM (SELECT f.fin_factuur_key fin_key,
|
||
c.cnt_prs_bedrijf_key bedr_key,
|
||
dis.ins_discipline_omschrijving discipline,
|
||
'C' ||c.cnt_contract_key || '.' || c.cnt_contract_versie contract_nr,
|
||
NULL totaal,
|
||
c.prs_kostenplaats_key kost_key,
|
||
NULL status
|
||
FROM cnt_contract c, fin_factuur f, ins_tab_discipline dis
|
||
WHERE c.cnt_contract_key = f.cnt_contract_key
|
||
AND dis.ins_discipline_key = c.ins_discipline_key
|
||
UNION ALL
|
||
SELECT f.fin_factuur_key fin_key,
|
||
o.mld_uitvoerende_keys bedr_key,
|
||
'Opdracht ' || sm.mld_stdmelding_omschrijving discipline,
|
||
srt.ins_srtdiscipline_prefix || TO_CHAR (o.mld_melding_key) || '/' || o.mld_opdr_bedrijfopdr_volgnr opdr_nr,
|
||
o.mld_opdr_kosten totaal,
|
||
o.prs_kostenplaats_key kost_key,
|
||
stat.mld_statusopdr_omschrijving status
|
||
FROM mld_opdr o,
|
||
fin_factuur f,
|
||
mld_melding m,
|
||
mld_stdmelding sm,
|
||
mld_statusopdr stat,
|
||
ins_tab_discipline dis,
|
||
ins_srtdiscipline srt
|
||
WHERE o.mld_opdr_key = f.mld_opdr_key
|
||
AND m.mld_melding_key = o.mld_melding_key
|
||
AND m.mld_stdmelding_key = sm.mld_stdmelding_key
|
||
AND dis.ins_discipline_key= sm.mld_ins_discipline_key
|
||
AND srt.ins_srtdiscipline_key = dis.ins_srtdiscipline_key
|
||
AND stat.mld_statusopdr_key = o.mld_statusopdr_key) co,
|
||
fin_factuur fc,
|
||
prs_bedrijf uitv,
|
||
fin_factuur_statuses stat
|
||
WHERE fc.fin_factuur_key = co.fin_key
|
||
AND uitv.prs_bedrijf_key = co.bedr_key
|
||
AND stat.fin_factuur_statuses_key = fc.fin_factuur_statuses_key
|
||
AND fc.fin_factuur_verwijder IS NULL;
|
||
|
||
|
||
CREATE OR REPLACE VIEW kfst_v_bron_export_exact_xml
|
||
(
|
||
fin_factuur_key,
|
||
oms_kop,
|
||
oms_detail,
|
||
opdracht_id,
|
||
factuur_nr,
|
||
datum,
|
||
crdnr,
|
||
bedrag_txt,
|
||
bedrag,
|
||
bedrag_incl,
|
||
betaalref,
|
||
itemcode,
|
||
reknr,
|
||
kstplcode,
|
||
kstdrcode,
|
||
btw_code,
|
||
lev_kpn,
|
||
boekstuknr
|
||
)
|
||
AS
|
||
SELECT fin_factuur_key,
|
||
SUBSTR(b.prs_bedrijf_naam, 1,30) oms_kop,
|
||
REPLACE (SUBSTR (fin_factuurregel_omschrijving, 1, 60), ',', '')
|
||
oms_detail,
|
||
opdracht_id,
|
||
fin_factuur_nr,
|
||
TO_CHAR (fin_factuur_datum, 'yyyy-mm-dd') datum,
|
||
b.prs_leverancier_nr crdnr,
|
||
REPLACE (TO_CHAR (bedrag), ',', '.') bedrag_txt,
|
||
bedrag,
|
||
bedrag_incl,
|
||
fin_factuur_nr betaalref,
|
||
prs_kostensoort_oms itemcode,
|
||
prs_kostensoort_refcode reknr,
|
||
kpcode kstplcode,
|
||
kdcode kstdrcode,
|
||
fin_btwtabelwaarde_code btw_code,
|
||
lev_kpn,
|
||
(SELECT fin_kenmerkfactuur_waarde FROM fin_kenmerkfactuur kf WHERE fin_kenmerk_key = 1 AND kf.fin_factuur_key = fg.fin_factuur_key) boekstuknr
|
||
FROM ( SELECT g.fin_factuur_key,
|
||
opdracht_id,
|
||
fin_factuur_datum,
|
||
fin_factuur_nr,
|
||
prs_bedrijf_key,
|
||
fin_factuur_debiteur_nr,
|
||
fin_factuur_opmerking,
|
||
prs_kostensoort_refcode,
|
||
prs_kostensoort_oms,
|
||
fin_factuurregel_omschrijving,
|
||
SUM (fin_factuurregel_totaal) bedrag,
|
||
SUM (fin_factuurregel_totaal + fin_factuurregel_btw) bedrag_incl,
|
||
fin_btwtabelwaarde_code,
|
||
mld_opdr_key,
|
||
cnt_contract_key,
|
||
prs_kostenplaats_key,
|
||
lev_kpn,
|
||
kpcode,
|
||
kdcode
|
||
FROM kfst_v_factuur_gegevens g, kfst_exp_factuur e
|
||
WHERE fin_factuur_statuses_key = 6
|
||
AND g.fin_factuur_key = e.fin_factuur_key
|
||
GROUP BY g.fin_factuur_key,
|
||
opdracht_id,
|
||
fin_factuur_datum,
|
||
fin_factuur_nr,
|
||
prs_bedrijf_key,
|
||
fin_factuur_debiteur_nr,
|
||
fin_factuur_opmerking,
|
||
prs_kostensoort_refcode,
|
||
prs_kostensoort_oms,
|
||
fin_btwtabelwaarde_code,
|
||
fin_factuurregel_omschrijving,
|
||
mld_opdr_key,
|
||
cnt_contract_key,
|
||
prs_kostenplaats_key,
|
||
lev_kpn,
|
||
kpcode,
|
||
kdcode) fg,
|
||
prs_bedrijf b
|
||
WHERE fg.prs_bedrijf_key = b.prs_bedrijf_key;
|
||
|
||
|
||
CREATE OR REPLACE VIEW kfst_v_export_exact_t (
|
||
result,
|
||
result_order
|
||
)
|
||
AS
|
||
SELECT REPLACE(REPLACE( regelnummer
|
||
|| ','
|
||
|| dagb_type
|
||
|| ','
|
||
|| dagbknr
|
||
|| ','
|
||
|| periode
|
||
|| ','
|
||
|| bkjcode
|
||
|| ','
|
||
|| boekstuknr
|
||
|| ','
|
||
|| oms25
|
||
|| ','
|
||
|| datum
|
||
|| ','
|
||
|| empty9
|
||
|| ','
|
||
|| debnr
|
||
|| ','
|
||
|| crdnr
|
||
|| ','
|
||
|| empty12
|
||
|| ','
|
||
|| bedrag
|
||
|| ','
|
||
|| drbk_in_val
|
||
|| ','
|
||
|| valcode
|
||
|| ','
|
||
|| koers
|
||
|| ','
|
||
|| kredbep
|
||
|| ','
|
||
|| bdrkredbep
|
||
|| ','
|
||
|| vervdatfak
|
||
|| ','
|
||
|| vervdatkrd
|
||
|| ','
|
||
|| empty21
|
||
|| ','
|
||
|| empty22
|
||
|| ','
|
||
|| weeknummer
|
||
|| ','
|
||
|| betaalref
|
||
|| ','
|
||
|| betwijze
|
||
|| ','
|
||
|| grek_bdr
|
||
|| ','
|
||
|| empty27
|
||
|| ','
|
||
|| empty28
|
||
|| ','
|
||
|| empty29
|
||
|| ','
|
||
|| empty30
|
||
|| ','
|
||
|| empty31
|
||
|| ','
|
||
|| transsubtype
|
||
|| ','
|
||
|| empty33
|
||
|| ','
|
||
|| empty34
|
||
|| ','
|
||
|| empty35
|
||
|| ','
|
||
|| empty36
|
||
|| ','
|
||
|| empty37
|
||
|| ','
|
||
|| empty38
|
||
|| ','
|
||
|| projectnr
|
||
|| ','
|
||
|| empty40,CHR (13), ''), CHR (10), '<ret>'),
|
||
boekstuknr * 100 + regelnummer
|
||
FROM (
|
||
SELECT 0 regelnummer,
|
||
'I' dagb_type,
|
||
'61' dagbknr,
|
||
'' periode,
|
||
'' bkjcode,
|
||
boekstuknr,
|
||
oms_kop oms25,
|
||
TO_CHAR (TO_DATE(datum, 'yyyy-mm-dd'), 'ddmmyyyy') datum,
|
||
'' empty9,
|
||
'' debnr,
|
||
crdnr,
|
||
'' empty12,
|
||
REPLACE (TO_CHAR (bedrag), ',', '.') bedrag,
|
||
'' drbk_in_val,
|
||
'' valcode,
|
||
'' koers,
|
||
'' kredbep,
|
||
'' bdrkredbep,
|
||
'' vervdatfak,
|
||
'' vervdatkrd,
|
||
'' empty21,
|
||
'' empty22,
|
||
'' weeknummer,
|
||
betaalref,
|
||
'' betwijze,
|
||
'' grek_bdr,
|
||
'' empty27,
|
||
'' empty28,
|
||
'' empty29,
|
||
'' empty30,
|
||
'' empty31,
|
||
'' transsubtype,
|
||
'' empty33,
|
||
'' empty34,
|
||
'' empty35,
|
||
'' empty36,
|
||
'' empty37,
|
||
'' empty38,
|
||
'' projectnr,
|
||
'!' empty40
|
||
FROM (SELECT datum,
|
||
factuur_nr,
|
||
betaalref,
|
||
crdnr,
|
||
oms_kop,
|
||
boekstuknr,
|
||
SUM (bedrag_incl)
|
||
bedrag
|
||
FROM kfst_v_bron_export_exact_xml
|
||
GROUP BY datum,
|
||
factuur_nr,
|
||
betaalref,
|
||
crdnr,
|
||
oms_kop,
|
||
boekstuknr) fg
|
||
UNION ALL
|
||
SELECT 1 regelnummer,
|
||
'I' dagb_type,
|
||
'61' dagbknr,
|
||
'' periode,
|
||
'' bkjcode,
|
||
boekstuknr,
|
||
oms_detail,
|
||
TO_CHAR (TO_DATE(datum, 'yyyy-mm-dd'), 'ddmmyyyy') datum,
|
||
reknr,
|
||
'' debnr,
|
||
crdnr,
|
||
'' empty12,
|
||
REPLACE (TO_CHAR (bedrag_incl), ',', '.') bedrag,
|
||
opdracht_id drbk_in_val,
|
||
'' val_code,
|
||
'' koers,
|
||
'' kredbep,
|
||
'' bdrkredbep,
|
||
'' vervdatfak,
|
||
'' vervdatkrd,
|
||
btw_code,
|
||
REPLACE (TO_CHAR (bedrag_incl - bedrag), ',', '.') btw_bdr,
|
||
'' weeknummer,
|
||
'' empty24,
|
||
'' empty25,
|
||
'' empty26,
|
||
kstplcode,
|
||
kstdrcode,
|
||
'' empty29,
|
||
'' empty30,
|
||
'' empty31,
|
||
'' transsubtype,
|
||
'' empty33,
|
||
'' empty34,
|
||
'' emtpy35,
|
||
'' empty36,
|
||
'' empty37,
|
||
'' empty38,
|
||
'' projectnr,
|
||
'!' empty40
|
||
FROM kfst_v_bron_export_exact_xml fg
|
||
ORDER BY boekstuknr, regelnummer);
|
||
|
||
CREATE OR REPLACE VIEW kfst_v_export_exact (
|
||
result,
|
||
result_order
|
||
)
|
||
AS
|
||
SELECT fac_rapport_regel, fac_rapport_volgnr
|
||
FROM fac_rapport
|
||
WHERE fac_rapport_node = 'EXACT';
|
||
|
||
--
|
||
-- Oude procedure om XML formaat te gebruiken. Tegenwoordig werkt men met csv bestanden.
|
||
--
|
||
/*
|
||
CREATE OR REPLACE PROCEDURE kfst_select_exact (p_applname IN VARCHAR2,
|
||
p_applrun IN VARCHAR2
|
||
)
|
||
AS
|
||
CURSOR c
|
||
IS
|
||
SELECT crdnr, factuur_nr, MAX(datum) datum
|
||
FROM kfst_v_bron_export_exact_xml
|
||
GROUP BY crdnr, factuur_nr;
|
||
|
||
CURSOR c_sub (c_crdnr VARCHAR2, c_factuur_nr VARCHAR2)
|
||
IS
|
||
SELECT *
|
||
FROM kfst_v_bron_export_exact_xml
|
||
WHERE COALESCE(crdnr, 'CrdNr') = COALESCE(c_crdnr, 'CrdNr')
|
||
AND factuur_nr = c_factuur_nr;
|
||
|
||
v_order_count NUMBER;
|
||
v_amount_sum NUMBER(10,2);
|
||
v_amount_sum_incl NUMBER(10,2);
|
||
v_count NUMBER;
|
||
v_sub_count NUMBER;
|
||
v_bestand VARCHAR2(12);
|
||
v_boekstuknummer NUMBER;
|
||
v_crdnr PRS_BEDRIJF.PRS_LEVERANCIER_NR%TYPE;
|
||
v_factuur_nr FIN_FACTUUR.FIN_FACTUUR_NR%TYPE;
|
||
|
||
BEGIN
|
||
|
||
v_order_count := 0;
|
||
v_amount_sum := 0;
|
||
v_amount_sum_incl := 0;
|
||
v_count := 0;
|
||
v_bestand := SUBSTR(p_applname, 1, 12);
|
||
v_crdnr := 'Q';
|
||
v_factuur_nr := 'Q';
|
||
|
||
-- Eerst opruiming
|
||
DELETE FROM imp_log
|
||
WHERE imp_log_applicatie = p_applname;
|
||
|
||
DELETE fac_rapport
|
||
WHERE fac_rapport_node = v_bestand;
|
||
|
||
DELETE fin_kenmerkfactuur kf
|
||
WHERE fin_kenmerk_key = 1
|
||
AND fin_factuur_key IN (SELECT fin_factuur_key
|
||
FROM fin_factuur
|
||
WHERE fin_factuur_statuses_key <> 7);
|
||
|
||
-- Bewaar de factuur keys in een tijdelijke tabel zodat bij het verwerken (nieuwe export)
|
||
-- deze keys gebruikt kunnen worden.
|
||
DELETE kfst_exp_factuur;
|
||
|
||
INSERT INTO kfst_exp_factuur (fin_factuur_key)
|
||
( SELECT DISTINCT fin_factuur_key
|
||
FROM kfst_v_factuur_gegevens
|
||
WHERE fin_factuur_statuses_key = 6
|
||
AND fin_factuur_datum > SYSDATE - (5 * 365) -- Check op geldige factuurdatum:
|
||
AND fin_factuur_datum < SYSDATE + 365 -- facturen mogen niet ouder zijn dan 5 jaar of meer dan <20><>n jaar in de toekomst.
|
||
);
|
||
|
||
-- Bepaal het boekstuknummer (key 1) met het hoogste nummer van dit jaar
|
||
-- ## AAIT#36589
|
||
|
||
SELECT MAX(fin_kenmerkfactuur_waarde)
|
||
INTO v_boekstuknummer
|
||
FROM fin_kenmerkfactuur
|
||
WHERE fin_kenmerk_key = 1
|
||
AND SUBSTR(fin_kenmerkfactuur_waarde,1,2) = TO_CHAR(SYSDATE, 'YY');
|
||
|
||
IF v_boekstuknummer IS NULL
|
||
THEN
|
||
v_boekstuknummer := FAC.safe_to_number(TO_CHAR(SYSDATE, 'YY') || '610000');
|
||
END IF;
|
||
|
||
-- header
|
||
kfst.add_xml_row (v_bestand, '<?xml version="1.0" encoding="windows-1252"?>');
|
||
kfst.add_xml_row (v_bestand, '<eExact>');
|
||
kfst.add_xml_row (v_bestand, '<GLEntries>');
|
||
|
||
FOR rec IN c
|
||
LOOP
|
||
BEGIN
|
||
-- Als we een nieuwe leverancier/factuurnummer tegenkomen gaan we het boekstuknummer ophogen.
|
||
IF rec.crdnr <> v_crdnr OR rec.factuur_nr <> v_factuur_nr
|
||
THEN
|
||
v_boekstuknummer := v_boekstuknummer + 1;
|
||
v_crdnr := rec.crdnr;
|
||
v_factuur_nr := rec.factuur_nr;
|
||
END IF;
|
||
|
||
kfst.add_xml_row (v_bestand, '<GLEntry status="E" entry="' || xml.char_to_html(v_boekstuknummer) || '">');
|
||
kfst.add_xml_element (v_bestand, 'Description', rec.factuur_nr);
|
||
kfst.add_xml_element (v_bestand, 'Date', rec.datum);
|
||
kfst.add_xml_element (v_bestand, 'DocumentDate', TO_CHAR(SYSDATE, 'YYYY-MM-DD') );
|
||
kfst.add_xml_row (v_bestand, '<Journal type="I" code="61"/>');
|
||
v_sub_count := 1;
|
||
FOR rec1 IN c_sub (v_crdnr, v_factuur_nr)
|
||
LOOP
|
||
BEGIN
|
||
-- Eerst maar even een kenmerk voor het boestuknummer aanmaken
|
||
kfst.set_kenmerk('FIN', 1, rec1.fin_factuur_key, v_boekstuknummer, 0);
|
||
kfst.add_xml_row (v_bestand, '<FinEntryLine number="' || v_sub_count || '" subtype="T" type="N">');
|
||
kfst.add_xml_element (v_bestand, 'Date', rec1.datum);
|
||
kfst.add_xml_row (v_bestand, '<GLAccount code="' || xml.char_to_html(rec1.reknr) || '"/>');
|
||
kfst.add_xml_element (v_bestand, 'Description', rec1.oms25);
|
||
kfst.add_xml_row (v_bestand, '<Costcenter code="' || xml.char_to_html(rec1.kstplcode) || '"/>');
|
||
kfst.add_xml_row (v_bestand, '<Costunit code="' || xml.char_to_html(rec1.kstdrcode) || '"/>');
|
||
kfst.add_xml_row (v_bestand, '<Creditor number="' || xml.char_to_html(rec1.crdnr) || '"/>');
|
||
kfst.add_xml_row (v_bestand, '<Item code="' || xml.char_to_html(rec1.itemcode) || '"/>');
|
||
kfst.add_xml_row (v_bestand, '<Amount>');
|
||
kfst.add_xml_element (v_bestand, 'Debit', rec1.bedrag_txt);
|
||
kfst.add_xml_row (v_bestand, '<VAT code="' || xml.char_to_html(rec1.btw_code) || '"/>');
|
||
kfst.add_xml_row (v_bestand, '</Amount>');
|
||
kfst.add_xml_row (v_bestand, '<Payment>');
|
||
kfst.add_xml_element (v_bestand, 'Reference', rec1.betaalref);
|
||
kfst.add_xml_element (v_bestand, 'InvoiceNumber', rec1.fin_factuur_key);
|
||
kfst.add_xml_row (v_bestand, '</Payment>');
|
||
kfst.add_xml_row (v_bestand, '<FinReferences>');
|
||
kfst.add_xml_element (v_bestand, 'YourRef', rec1.betaalref);
|
||
kfst.add_xml_element (v_bestand, 'DocumentDate', TO_CHAR(SYSDATE, 'YYYY-MM-DD') );
|
||
kfst.add_xml_row (v_bestand, '</FinReferences>');
|
||
kfst.add_xml_row (v_bestand, '<FreeFields>');
|
||
kfst.add_xml_row (v_bestand, '<FreeTexts>');
|
||
kfst.add_xml_row (v_bestand, '<FreeText number="3">' || rec1.opdracht_id || '</FreeText>');
|
||
kfst.add_xml_row (v_bestand, '</FreeTexts>');
|
||
kfst.add_xml_row (v_bestand, '</FreeFields>');
|
||
kfst.add_xml_row (v_bestand, '</FinEntryLine>');
|
||
v_sub_count := v_sub_count + 1;
|
||
v_amount_sum := v_amount_sum + rec1.bedrag;
|
||
v_amount_sum_incl := v_amount_sum_incl + rec1.bedrag_incl;
|
||
END;
|
||
END LOOP;
|
||
IF v_sub_count = 1
|
||
THEN
|
||
fac.writelog (p_applname, 'E', 'Fout tijdens Exact export. Factuurnummer: ' || v_factuur_nr || ' Crdnr: ' || v_crdnr,'');
|
||
END IF;
|
||
kfst.add_xml_row (v_bestand, '</GLEntry>');
|
||
v_order_count := v_order_count + 1;
|
||
END;
|
||
END LOOP;
|
||
|
||
kfst.add_xml_row (v_bestand, '</GLEntries>');
|
||
kfst.add_xml_row (v_bestand, '</eExact>');
|
||
fac.writelog (p_applname, 'S', 'Exact export uitgevoerd. Aantal regels: ' || v_order_count || ' Totaal bedrag: ' || v_amount_sum || ' Totaal bedrag (incl): ' || v_amount_sum_incl, '');
|
||
|
||
END;
|
||
/
|
||
*/
|
||
CREATE OR REPLACE PROCEDURE kfst_select_exact (p_applname IN VARCHAR2,
|
||
p_applrun IN VARCHAR2
|
||
)
|
||
AS
|
||
|
||
CURSOR c
|
||
IS
|
||
SELECT crdnr, factuur_nr, MAX(datum) datum
|
||
FROM kfst_v_bron_export_exact_xml
|
||
GROUP BY crdnr, factuur_nr;
|
||
|
||
CURSOR c_sub (c_crdnr VARCHAR2, c_factuur_nr VARCHAR2)
|
||
IS
|
||
SELECT *
|
||
FROM kfst_v_bron_export_exact_xml
|
||
WHERE COALESCE(crdnr, 'CrdNr') = COALESCE(c_crdnr, 'CrdNr')
|
||
AND factuur_nr = c_factuur_nr;
|
||
|
||
CURSOR cv IS
|
||
SELECT result, result_order
|
||
FROM kfst_v_export_exact_t
|
||
ORDER BY result_order;
|
||
|
||
v_first NUMBER;
|
||
v_count NUMBER;
|
||
v_bestand VARCHAR2(12);
|
||
v_boekstuknummer NUMBER;
|
||
v_crdnr PRS_BEDRIJF.PRS_LEVERANCIER_NR%TYPE;
|
||
v_factuur_nr FIN_FACTUUR.FIN_FACTUUR_NR%TYPE;
|
||
|
||
v_log VARCHAR2(1000);
|
||
v_log_prev VARCHAR2 (1000);
|
||
v_errormsg VARCHAR (200);
|
||
v_errorhint VARCHAR (200);
|
||
oracle_err_num NUMBER;
|
||
oracle_err_mes VARCHAR2 (200);
|
||
|
||
BEGIN
|
||
v_count := 0;
|
||
v_bestand := SUBSTR(p_applname, 1, 12);
|
||
v_crdnr := 'Q';
|
||
v_factuur_nr := 'Q';
|
||
|
||
-- Eerst opruiming
|
||
DELETE FROM imp_log
|
||
WHERE imp_log_applicatie = p_applname;
|
||
|
||
DELETE fac_rapport
|
||
WHERE fac_rapport_node = v_bestand;
|
||
|
||
DELETE fin_kenmerkfactuur kf
|
||
WHERE fin_kenmerk_key = 1
|
||
AND fin_factuur_key IN (SELECT fin_factuur_key
|
||
FROM fin_factuur
|
||
WHERE fin_factuur_statuses_key <> 7);
|
||
|
||
-- Bewaar de factuur keys in een tijdelijke tabel zodat bij het verwerken (nieuwe export)
|
||
-- deze keys gebruikt kunnen worden.
|
||
DELETE kfst_exp_factuur;
|
||
|
||
INSERT INTO kfst_exp_factuur (fin_factuur_key)
|
||
( SELECT DISTINCT fin_factuur_key
|
||
FROM kfst_v_factuur_gegevens
|
||
WHERE fin_factuur_statuses_key = 6
|
||
AND fin_factuur_datum > SYSDATE - (5 * 365) -- Check op geldige factuurdatum:
|
||
AND fin_factuur_datum < SYSDATE + 365 -- facturen mogen niet ouder zijn dan 5 jaar of meer dan <20><>n jaar in de toekomst.
|
||
);
|
||
|
||
-- Bepaal het boekstuknummer (key 1) met het hoogste nummer van dit jaar
|
||
|
||
SELECT MAX(fin_kenmerkfactuur_waarde)
|
||
INTO v_boekstuknummer
|
||
FROM fin_kenmerkfactuur
|
||
WHERE fin_kenmerk_key = 1
|
||
AND SUBSTR(fin_kenmerkfactuur_waarde,1,2) = TO_CHAR(SYSDATE, 'YY');
|
||
|
||
IF v_boekstuknummer IS NULL
|
||
THEN
|
||
v_boekstuknummer := FAC.safe_to_number(TO_CHAR(SYSDATE, 'YY') || '610000');
|
||
END IF;
|
||
|
||
-- nu gaan we de boekstuknummer invullen
|
||
FOR rec IN c
|
||
LOOP
|
||
BEGIN
|
||
-- Als we een nieuwe leverancier/factuurnummer tegenkomen gaan we het boekstuknummer ophogen.
|
||
IF rec.crdnr <> v_crdnr OR rec.factuur_nr <> v_factuur_nr
|
||
THEN
|
||
v_boekstuknummer := v_boekstuknummer + 1;
|
||
v_crdnr := rec.crdnr;
|
||
v_factuur_nr := rec.factuur_nr;
|
||
END IF;
|
||
|
||
FOR rec1 IN c_sub (v_crdnr, v_factuur_nr)
|
||
LOOP
|
||
BEGIN
|
||
-- Boekstuknummer aanmaken
|
||
kfst.set_kenmerk('FIN', 1, rec1.fin_factuur_key, v_boekstuknummer, 0);
|
||
END;
|
||
END LOOP;
|
||
END;
|
||
END LOOP;
|
||
|
||
-- De view bevat voor alle subregels volgnummer 1. Deze gaan we nu omzetten naar echte volgnummers.
|
||
FOR rec IN cv
|
||
LOOP
|
||
BEGIN
|
||
v_first := substr(rec.result, 1,1);
|
||
IF v_first = 0 THEN
|
||
v_count := 0;
|
||
ELSE
|
||
v_count := v_count + 1;
|
||
END IF;
|
||
|
||
v_log_prev := v_log;
|
||
v_log := rec.result_order || ' | ' || rec.result_order;
|
||
INSERT INTO fac_rapport (
|
||
fac_rapport_regel,
|
||
fac_rapport_volgnr,
|
||
fac_rapport_node)
|
||
VALUES (
|
||
v_count || substr (rec.result, 2),
|
||
SUBSTR(rec.result_order, 1,2) || SUBSTR(rec.result_order, 5,4) || to_char(1000+v_count), -- forceer sortering bij count > 9, alleen jaar en volgnummer van het boekstuknummer zijn interessant.
|
||
v_bestand);
|
||
END;
|
||
END LOOP;
|
||
EXCEPTION
|
||
WHEN OTHERS
|
||
THEN
|
||
oracle_err_num := SQLCODE;
|
||
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
|
||
v_errormsg := 'OTHERS (error ' || oracle_err_num || '/' || oracle_err_mes || ')';
|
||
fac.writelog (p_applname, 'E', SUBSTR (v_errormsg, 1, 1000), '');
|
||
fac.writelog (p_applname, 'I', 'Log: ' || SUBSTR (v_log, 1, 990), '');
|
||
fac.writelog (p_applname, 'I', 'Log_prev: ' || SUBSTR (v_log_prev, 1, 990), '');
|
||
INSERT INTO fac_rapport (
|
||
fac_rapport_regel,
|
||
fac_rapport_volgnr,
|
||
fac_rapport_node)
|
||
VALUES (
|
||
'Fout: Export is afgebroken',
|
||
9999999999,
|
||
v_bestand);
|
||
-- Als er iets fout gegaan is willen we voorkomen dat de verwerk actie uitgevoerd wordt.
|
||
DELETE kfst_exp_factuur;
|
||
END;
|
||
/
|
||
|
||
CREATE OR REPLACE VIEW kfst_v_export_verwerk_exact (
|
||
result,
|
||
result_order
|
||
)
|
||
AS
|
||
SELECT 'Lijst met bevroren facturen:' fac_rapport_regel, 0 fac_rapport_volgnr
|
||
FROM DUAL
|
||
UNION ALL
|
||
SELECT fac_rapport_regel, fac_rapport_volgnr
|
||
FROM fac_rapport
|
||
WHERE fac_rapport_node = 'VW_EXACT';
|
||
|
||
-- Procedure om alle geexporteerde facturen naar status verwerkt te zetten.
|
||
CREATE OR REPLACE PROCEDURE kfst_select_verwerk_exact (
|
||
p_applname IN VARCHAR2,
|
||
p_applrun IN VARCHAR2
|
||
)
|
||
AS
|
||
CURSOR c_fact
|
||
IS
|
||
SELECT f.fin_factuur_key
|
||
FROM kfst_exp_factuur f
|
||
, kfst_v_factuur_gegevens fg
|
||
WHERE f.fin_factuur_key = fg.fin_factuur_key
|
||
AND fg.fin_factuur_statuses_key = 6;
|
||
|
||
v_errormsg VARCHAR (200);
|
||
oracle_err_num NUMBER;
|
||
oracle_err_mes VARCHAR2 (200);
|
||
v_aanvrager NUMBER;
|
||
v_bestelling_key NUMBER;
|
||
v_bestand VARCHAR2(12);
|
||
v_logdate DATE;
|
||
v_volgnr NUMBER;
|
||
BEGIN
|
||
v_errormsg := 'Geen akties';
|
||
|
||
-- Facilitor user
|
||
v_aanvrager := 4;
|
||
|
||
v_logdate := SYSDATE;
|
||
|
||
v_bestand := 'VW_EXACT';
|
||
v_volgnr := 1;
|
||
-- Eerst opruiming
|
||
DELETE fac_rapport
|
||
WHERE fac_rapport_node = v_bestand;
|
||
|
||
FOR rec1 IN c_fact
|
||
LOOP
|
||
BEGIN
|
||
UPDATE fin_factuur f
|
||
SET fin_factuur_statuses_key = 7
|
||
WHERE f.fin_factuur_key = rec1.fin_factuur_key;
|
||
|
||
fac.trackaction ('FINVER', rec1.fin_factuur_key, v_aanvrager, NULL, NULL);
|
||
|
||
INSERT INTO fac_rapport (fac_rapport_node,
|
||
fac_rapport_volgnr,
|
||
fac_rapport_regel)
|
||
VALUES (v_bestand,
|
||
v_volgnr,
|
||
rec1.fin_factuur_key);
|
||
|
||
v_volgnr := v_volgnr + 1;
|
||
END;
|
||
END LOOP;
|
||
DELETE kfst_exp_factuur;
|
||
END;
|
||
/
|
||
|
||
-- Notificatiejob om de administratie te informeren dat er facturen afgewezen zijn.
|
||
-- Let op dat je wel bij de notificatie de vlag mail aanzet.
|
||
-- Normaal worden factuurnotificaties niet verzonden omdat Facilitor niet weet wie
|
||
-- ze wil ontvangen.
|
||
CREATE OR REPLACE VIEW kfst_v_noti_fin_factuur
|
||
AS
|
||
SELECT NULL sender,
|
||
562 receiver, -- Administratie
|
||
NULL text, --Lcl.l('lcl_noti_FINFOK') || ': ' || f.fac_tracking_refkey
|
||
sn.fac_srtnotificatie_code code,
|
||
t.fac_tracking_refkey key,
|
||
NULL xkey
|
||
FROM fac_tracking t, fac_srtnotificatie sn
|
||
WHERE t.fac_srtnotificatie_key = sn.fac_srtnotificatie_key
|
||
AND fac_srtnotificatie_code IN ('FINFNO',
|
||
'FINGNO',
|
||
'FINFUN',
|
||
'FINGUN')
|
||
AND BITAND (fac_srtnotificatie_mode, 2) = 2
|
||
AND t.fac_tracking_datum BETWEEN (SELECT fac_notificatie_job_nextrun
|
||
- fac_notificatie_job_interval
|
||
/ 24
|
||
FROM fac_notificatie_job
|
||
WHERE LOWER (
|
||
fac_notificatie_job_view) =
|
||
'kfst_v_noti_fin_factuur')
|
||
AND (SELECT fac_notificatie_job_nextrun
|
||
FROM fac_notificatie_job
|
||
WHERE LOWER (
|
||
fac_notificatie_job_view) =
|
||
'kfst_v_noti_fin_factuur');
|
||
|
||
------ payload end ------
|
||
|
||
SET DEFINE OFF
|
||
BEGIN adm.systrackscriptId ('$Id$', 0); END;
|
||
/
|
||
|
||
COMMIT;
|
||
SET ECHO OFF
|
||
SPOOL OFF
|
||
SET DEFINE ON
|
||
PROMPT Logfile of this upgrade is: &fcltlogfile |