Jeden Monat benötigt Finanzen eine Reihe von Auswertungen. Diese gehen als Excel-Tabellen an Almut bzw. deren Vertretung.
BCAS schick üblicherweise zwischen dem 21. und 23. jeden Monats die Daten an den Service. Die Daten sind dann auf WEBDATA abgelegt. Alle relevanten Tabellen beginnen mit 'BCAS'.
Bevor die Auswertungen erstellt werden, muss der Status bei jedem Vertrag geprüft und ggf. aktualisiert werden. Dies passiert durch den Aufruf einer Stored Procedure:
EXEC BCAS_IO.BCAS_STATECHANGE();
Anschliessend können die Reports abgerufen werden. Das Ergebnis wird jeweils in einer Excel-Datei gespeichert; der Dateiname steht im Kommentar über dem jeweiligen Statement (MM_YYYY austauschen gegen Monat / Jahr)
-- Exportdatei: BCAS_POLICYOVERVIEW_MM_YYYY
SELECT * FROM BCAS_POLICYOVERVIEW ORDER BY UEBERMITTELT_AM, POLICYNUMBER;
-- Exportdatei: BCAS_PAYMENTOVERVIEW_MM_YYYY
SELECT * FROM BCAS_PAYMENTOVERVIEW ORDER BY UEBERMITTELT_AM, POLICYNUMBER;
-- Exportdatei: BCAS_PAYMENTOVERVIEW_BY_MONTH_MM_YYYY
SELECT * FROM BCAS_PAYMENTOVERVIEW_BY_MONTH;
-- Exportdatei: BCAS_POLICYOVERVIEW_MONTHLY_MM_YYYY
SELECT * FROM BCAS_POLICYOVERVIEW_MONTHLY bpm;
-- Exportdatei: Monatsübersicht_YYYY_MM
--Monatsübersicht: + Commission SK
SELECT
TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM') AS Monat,
SUM(bp."value") AS "Gebuchte Beiträge",
0 AS "Rücklaufleistung",
0 AS "Todesfallleistung",
SUM(ROUND(bp.IC_BCAS / 12, 0)) AS "Provision BCAS",
SUM(ROUND(bp.IC_SK / 12, 0)) AS "Provision SK",
SUM(ROUND((bp.IC_SK) / 12, 0)) +ROUND(SUM(bp."Diff"), 0) AS "Provision SK2",
-- ROUND(SUM(bp."Diff"), 0) AS "Diff",
ROUND(SUM(bp.RE / 12), 0) AS "RV-Beiträge",
0 AS "RV-Leistungen",
ROUND(SUM(bp.RE) / 12 * 2.5 / 100, 0) AS "RV-Provisionen",
ROUND(SUM(ROUND(bp.C, 0) / 12), 0) AS "Costs of Insurance"
FROM BCAS_PAYMENTOVERVIEW bp
GROUP BY TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM')--, BP1.STATE
ORDER BY TO_CHAR(bp.UEBERMITTELT_AM, 'YYYY-MM')--, BP1.STATE
;
Als weiteren Report gibt es eine Statusübersicht. Diese wird monatlich fortgeschrieben. Man führt also das nachfolgende SQL-Statement aus, kopiert den Report des Vormonats und fügt die Daten des neuen Monats hinzu:
-- Juli 2014
WITH SEL_WERTE AS (SELECT TO_DATE('31.07.2014', 'DD.MM.YYYY') AS SEL_DATE FROM DUAL)
SELECT BS.ID AS STATEID, BS."name" AS STATE, COUNT(SEL.ID) AS ANZAHL, NVL(SUM(SEL2.PAYMENT), 0) AS PAYMENT, NVL(SUM(SEL.SUMASSURED), 0) AS SUMASSURED
FROM BCAS_STATE BS
LEFT OUTER JOIN
(
SELECT BP.ID, BCAS_IO.BCAS_IP_STATE_BY_DATE(BI.ID, (SELECT SEL_DATE FROM SEL_WERTE)) AS STATEID, BI.SUMASSURED
FROM BCAS_TRANSFER BT
INNER JOIN BCAS_POLICY BP ON BT.ID = BP.TRANSFERID
INNER JOIN BCAS_INSURANCEPRODUCT BI ON BP.ID = BI.POLICYID
WHERE BT.CREATINGDATE <= (SELECT SEL_DATE FROM SEL_WERTE)
) SEL ON BS.ID = SEL.STATEID
LEFT OUTER JOIN
(
SELECT BP.POLICYID, SUM(BP."value") AS PAYMENT
FROM BCAS_PAYMENT BP
WHERE BP.BOOKINGDATE <= (SELECT SEL_DATE FROM SEL_WERTE)
GROUP BY BP.POLICYID
) SEL2 ON SEL.ID = SEL2.POLICYID
GROUP BY BS.ID, BS."name"
ORDER BY BS.ID;
Wichtig ist es natürlich, den Monat bzw. das Datum in Zeile 1 anzupassen. Es ist immer das Datem des letzten Tages des Monats einzutragen.
Auswertung Tschechien.sql (12,46 kb)