You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1032 lines
34 KiB
1032 lines
34 KiB
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Rechnung'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Rechnung'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Rechnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Rechnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Mahnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Mahnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_abgeschlossene_Behandlungen'
|
|
GO
|
|
|
|
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_abgeschlossene_Behandlungen'
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Zahnarzt] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Zahnarzt]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Versicherung] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Versicherung]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Spalten] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Spalten]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Rechnung] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Rechnung]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Recall] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Recall]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_RacallPatient] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_RacallPatient]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Offene_Rechnungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Offene_Rechnungen]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Offene_Mahnungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Offene_Mahnungen]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_ohne_Datum] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Leistungen_ohne_Datum]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_mit_Datum] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Leistungen_mit_Datum]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_Dentotar] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Leistungen_Dentotar]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Krankenkasse] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Krankenkasse]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_IV] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_IV]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Hausarzt] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Hausarzt]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_GesVertreter] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_GesVertreter]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Garant] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Garant]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Fuersorge] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Fuersorge]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Firma] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Firma]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Behandlungsuebersicht] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Behandlungsuebersicht]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Behandler] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Behandler]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Arbeitgeber] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[View_Arbeitgeber]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[view_abgeschlossene_Behandlungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
DROP VIEW [dbo].[view_abgeschlossene_Behandlungen]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[view_abgeschlossene_Behandlungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[view_abgeschlossene_Behandlungen]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.Behandlu.Nrbehandlung, dbo.get_name(dbo.Behandlu.Nrpatient) AS Patient, ISNULL(dbo.get_name(dbo.Behandlu.Nrgarant), '') AS Garant, dbo.Behandlu.Behandlungsbeginn,
|
|
dbo.Behandlu.Behandlungsende, dbo.Behandlu.Nrestyp, dbo.Behandlu.Anzahlraten AS Raten
|
|
FROM dbo.Behandlu INNER JOIN
|
|
dbo.PRIVAT ON dbo.Behandlu.Nrpatient = dbo.PRIVAT.NRPRIVAT
|
|
WHERE (dbo.Behandlu.Status = 2) AND (dbo.Behandlu.aktiv = 1) AND (dbo.Behandlu.RgStellung <= GETDATE() OR
|
|
dbo.Behandlu.RgStellung IS NULL)
|
|
ORDER BY dbo.Behandlu.Nrbehandlung
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Arbeitgeber] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Arbeitgeber]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
WHERE (AG = 1)
|
|
ORDER BY NAME1
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Behandler] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Behandler]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRPRIVAT, NAME + ' ' + VORNAME + ', ' + PLZ + ' ' + ORT AS Concat_Name
|
|
FROM dbo.privat
|
|
WHERE (BEHANDLER = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Behandlungsuebersicht] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Behandlungsuebersicht]
|
|
AS
|
|
SELECT dbo.behandlu.Nrbehandlung, dbo.statusbh.Bezeichnung, dbo.behandlu.aktiv, dbo.privat.NAME AS Behandler, dbo.behandlu.Behandlungsbeginn, dbo.behandlu.Behandlungsende, dbo.behandlu.Rabatt,
|
|
dbo.behandlu.Total, dbo.behandlu.Taxpunktwert, dbo.behandlu.Nrpatient, dbo.behandlu.Status
|
|
FROM dbo.behandlu INNER JOIN
|
|
dbo.statusbh ON dbo.behandlu.Status = dbo.statusbh.Nreintrag LEFT OUTER JOIN
|
|
dbo.privat ON dbo.behandlu.Nrbehandler = dbo.privat.NRPRIVAT
|
|
WHERE (dbo.behandlu.aktiv = 1)
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Firma] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Firma]
|
|
AS
|
|
SELECT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Fuersorge] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Fuersorge]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
WHERE (FS = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Garant] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Garant]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRPRIVAT, NAME + ' ' + VORNAME + ', ' + PLZ + ' ' + ORT AS Concat_Name
|
|
FROM dbo.privat
|
|
WHERE (GARANT = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_GesVertreter] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_GesVertreter]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRPRIVAT, NAME + ' ' + VORNAME + ', ' + PLZ + ' ' + ORT AS Concat_Name
|
|
FROM dbo.privat
|
|
WHERE (GESVERTRETER = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Hausarzt] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Hausarzt]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRPRIVAT, NAME + ' ' + VORNAME + ', ' + PLZ + ' ' + ORT AS Concat_Name
|
|
FROM dbo.privat
|
|
WHERE (HAUSARZT = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_IV] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_IV]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
WHERE (IV = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Krankenkasse] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Krankenkasse]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
WHERE (KK = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_Dentotar] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Leistungen_Dentotar]
|
|
AS
|
|
SELECT dbo.LEISTUNG.Datum, CASE WHEN dbo.dentotar.leistungnrneu <> '' THEN 222 ELSE 0 END AS Tarif, CASE WHEN len(dbo.LEISTUNG.SSO_Nummer)
|
|
< 5 THEN 'P.' + dbo.leistung.sso_nummer ELSE dbo.leistung.sso_nummer END AS Tarifziffer, dbo.LEISTUNG.Bezd AS Tarifziffer_Beschreibung, 0 AS Bezugsziffer, 1 AS SI, 0 AS St, dbo.LEISTUNG.Menge AS Anzahl,
|
|
CASE WHEN absolut = 1 THEN frankenprotaxpunkt ELSE dbo.LEISTUNG.Taxpunkte END AS TPAL_Preis, dbo.Behandlu.Nrbehandlung, 1 AS TPW_AL, '' AS TP_TL, '' AS f_TL, '' AS TPW_TL, 1 AS A, 1 AS V, 1 AS P,
|
|
CASE WHEN len(leistungnrneu) > 6 OR
|
|
len(dbo.leistung.sso_nummer) < 5 THEN 1 ELSE 0 END AS M, dbo.LEISTUNG.Total AS Betrag
|
|
FROM dbo.Behandlu INNER JOIN
|
|
dbo.LEISTUNG ON dbo.Behandlu.Nrbehandlung = dbo.LEISTUNG.Nrbehandlung LEFT OUTER JOIN
|
|
dbo.DentoTar ON dbo.LEISTUNG.SSO_Nummer = dbo.DentoTar.LeistungNrNeu
|
|
WHERE (dbo.LEISTUNG.aktiv = 1)
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_mit_Datum] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Leistungen_mit_Datum]
|
|
AS
|
|
SELECT 1 AS Typ, dbo.LEISTUNG.Datum, dbo.LEISTUNG.SSO_Nummer, dbo.LEISTUNG.Bezd, dbo.LEISTUNG.Taxpunkte, SUM(dbo.LEISTUNG.Menge) AS Expr2, dbo.LEISTUNG.Frankenprotaxpunkt, CONVERT(int, dbo.LEISTUNG.Absolut)
|
|
AS Absolut, SUM(dbo.LEISTUNG.Total) AS Leistungstotal, dbo.Behandlu.Nrbehandlung
|
|
FROM dbo.Behandlu INNER JOIN
|
|
dbo.LEISTUNG ON dbo.Behandlu.Nrbehandlung = dbo.LEISTUNG.Nrbehandlung
|
|
WHERE (dbo.LEISTUNG.aktiv = 1)
|
|
GROUP BY dbo.LEISTUNG.SSO_Nummer, dbo.LEISTUNG.Taxpunkte, dbo.LEISTUNG.Bezd, CONVERT(int, dbo.LEISTUNG.Absolut), dbo.LEISTUNG.Frankenprotaxpunkt, dbo.LEISTUNG.Datum, dbo.Behandlu.Nrbehandlung
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Leistungen_ohne_Datum] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Leistungen_ohne_Datum]
|
|
AS
|
|
SELECT 1 AS Typ, dbo.LEISTUNG.SSO_Nummer, dbo.LEISTUNG.Bezd, dbo.LEISTUNG.Taxpunkte, SUM(dbo.LEISTUNG.Menge) AS Expr2, dbo.LEISTUNG.Frankenprotaxpunkt, CONVERT(int, dbo.LEISTUNG.Absolut) AS Absolut,
|
|
SUM(dbo.LEISTUNG.Total) AS Leistungstotal, dbo.Behandlu.Nrbehandlung
|
|
FROM dbo.Behandlu INNER JOIN
|
|
dbo.LEISTUNG ON dbo.Behandlu.Nrbehandlung = dbo.LEISTUNG.Nrbehandlung
|
|
WHERE (dbo.LEISTUNG.aktiv = 1)
|
|
GROUP BY dbo.LEISTUNG.SSO_Nummer, dbo.LEISTUNG.Taxpunkte, dbo.LEISTUNG.Bezd, CONVERT(int, dbo.LEISTUNG.Absolut), dbo.LEISTUNG.Frankenprotaxpunkt, dbo.Behandlu.Nrbehandlung
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Offene_Mahnungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Offene_Mahnungen]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.DEBITOR.Nreintrag, dbo.Faktura.Nrfaktura, dbo.DEBITOR.Faelligam, dbo.DEBITOR.Betrag, dbo.DEBITOR.Status, dbo.get_adresse_direkt(dbo.DEBITOR.Nrpatient, 1) AS Patient,
|
|
ISNULL(dbo.get_adresse_direkt(dbo.DEBITOR.Nrdebitor, 1), '') AS Debitor, dbo.DEBITOR.Nrpatient, dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) AS Offener_Betrag, dbo.DEBITOR.Nrdebitor, dbo.Faktura.Mahndatum1,
|
|
dbo.Faktura.Mahnfaelligkeit1, dbo.Faktura.Mahngebuehr1, dbo.Faktura.Mahndatum2, dbo.Faktura.Mahnfaelligkeit2, dbo.Faktura.Mahngebuehr2, dbo.Faktura.Mahndatum3, dbo.Faktura.Mahnfaelligkeit3,
|
|
dbo.Faktura.Mahngebuehr3, dbo.Faktura.erstellt_am, dbo.Faktura.MAHNSTOP, CASE WHEN mahndatum1 IS NULL AND dbo.faktura.faelligam < getdate() THEN 1 WHEN mahndatum2 IS NULL AND mahnfaelligkeit1 < getdate()
|
|
THEN 2 WHEN mahndatum3 IS NULL AND mahnfaelligkeit2 < getdate() THEN 3 WHEN mahndatum3 IS NOT NULL THEN 4 END AS Mahnstufe
|
|
FROM dbo.DEBITOR INNER JOIN
|
|
dbo.Faktura ON dbo.DEBITOR.Nrfaktura = dbo.Faktura.Nrfaktura
|
|
WHERE (dbo.DEBITOR.Status <> 9) AND (dbo.DEBITOR.Betrag <> 0) AND (dbo.DEBITOR.Betrag > 0.04) AND (dbo.DEBITOR.aktiv = 1) AND (dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) > 0) AND (dbo.Faktura.Status <> 9)
|
|
ORDER BY dbo.DEBITOR.Faelligam DESC
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Offene_Rechnungen] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Offene_Rechnungen]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.DEBITOR.Nreintrag, dbo.Faktura.Nrfaktura, dbo.DEBITOR.Faelligam, dbo.DEBITOR.Betrag, dbo.DEBITOR.Status, dbo.get_adresse_direkt(dbo.DEBITOR.Nrpatient, 1) AS Patient,
|
|
ISNULL(dbo.get_adresse_direkt(dbo.DEBITOR.Nrdebitor, 1), '') AS Debitor, dbo.DEBITOR.Nrpatient, dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) AS Offener_Betrag, dbo.DEBITOR.Nrdebitor, dbo.Faktura.Mahndatum1,
|
|
dbo.Faktura.Mahnfaelligkeit1, dbo.Faktura.Mahngebuehr1, dbo.Faktura.Mahndatum2, dbo.Faktura.Mahnfaelligkeit2, dbo.Faktura.Mahngebuehr2, dbo.Faktura.Mahndatum3, dbo.Faktura.Mahnfaelligkeit3,
|
|
dbo.Faktura.Mahngebuehr3, dbo.Faktura.erstellt_am
|
|
FROM dbo.DEBITOR INNER JOIN
|
|
dbo.Faktura ON dbo.DEBITOR.Nrfaktura = dbo.Faktura.Nrfaktura
|
|
WHERE (dbo.DEBITOR.Status <> 9) AND (dbo.DEBITOR.Betrag <> 0 AND dbo.DEBITOR.Betrag > 0.04) AND (dbo.DEBITOR.aktiv = 1) AND (dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) > 0) AND (dbo.Faktura.Status <> 9)
|
|
ORDER BY dbo.DEBITOR.Faelligam DESC
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_RacallPatient] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_RacallPatient]
|
|
AS
|
|
SELECT dbo.recall.Nrrecall, dbo.recall.Nrprivat, dbo.recall.Mandant, dbo.recall.Nrbehandler, dbo.recall.Zazrecall, dbo.recall.Datum, dbo.recall.Zeitvon, dbo.recall.Zeitbis,
|
|
dbo.recall.Bemerkung, dbo.recall.Gedruckt, dbo.recall.erstellt_am, dbo.recall.mutiert_am, dbo.recall.mutierer, dbo.recall.aktiv,
|
|
dbo.privat.NAME + ' ' + dbo.privat.VORNAME + ', ' + dbo.privat.PLZ + ' ' + dbo.privat.ORT AS Expr1
|
|
FROM dbo.recall LEFT OUTER JOIN
|
|
dbo.privat ON dbo.recall.Nrbehandler = dbo.privat.NRPRIVAT
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Recall] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Recall]
|
|
AS
|
|
SELECT dbo.recall.Nrrecall, dbo.recall.Nrprivat, dbo.recall.Mandant, dbo.recall.Nrbehandler, dbo.recall.Zazrecall, dbo.recall.Datum, dbo.recall.Zeitvon, dbo.recall.Zeitbis,
|
|
dbo.recall.Bemerkung, dbo.recall.Gedruckt, dbo.recall.erstellt_am, dbo.recall.mutiert_am, dbo.recall.mutierer, dbo.recall.aktiv,
|
|
dbo.privat.NAME + ' ' + dbo.privat.VORNAME + ', ' + dbo.privat.PLZ + ' ' + dbo.privat.ORT AS Concat_Name
|
|
FROM dbo.recall LEFT OUTER JOIN
|
|
dbo.privat ON dbo.recall.Nrbehandler = dbo.privat.NRPRIVAT
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Rechnung] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Rechnung]
|
|
AS
|
|
SELECT dbo.Behandlu.Nrbehandlung, dbo.Behandlu.Behandlungsbeginn, dbo.Behandlu.Behandlungsende, dbo.PRIVAT.NAME + ' ' + dbo.PRIVAT.VORNAME AS Patient, privat_1.NAME + ' ' + privat_1.VORNAME AS Behandler,
|
|
dbo.Behandlu.Taxpunktwert, dbo.Behandlu.Rabatt, dbo.Behandlu.Total - dbo.Get_Totale(5, dbo.Behandlu.Nrbehandlung) AS Total, dbo.Behandlu.Rechnungsbemerkung, dbo.Behandlu.Ratenzuschlag,
|
|
dbo.Behandlu.Andrucken_Taxpunkte, dbo.Behandlu.Anzahlung, dbo.LEISTUNG.Nrleistung, dbo.LEISTUNG.Datum, dbo.LEISTUNG.SSO_Nummer, dbo.LEISTUNG.Taxpunkte, dbo.LEISTUNG.Menge,
|
|
dbo.LEISTUNG.Frankenprotaxpunkt, dbo.LEISTUNG.Absolut, dbo.LEISTUNG.Total AS Leistungstotal, dbo.LEISTUNG.Bezd, dbo.get_adresszeile(1, dbo.Behandlu.Nrbehandlung) AS AdressZeile1, dbo.get_adresszeile(2,
|
|
dbo.Behandlu.Nrbehandlung) AS AdressZeile2, dbo.get_adresszeile(3, dbo.Behandlu.Nrbehandlung) AS AdressZeile3, dbo.get_adresszeile(4, dbo.Behandlu.Nrbehandlung) AS AdressZeile4, dbo.get_adresszeile(5,
|
|
dbo.Behandlu.Nrbehandlung) AS AdressZeile5, dbo.Get_Totale(1, dbo.Behandlu.Nrbehandlung) AS Total_SSO, dbo.Get_Totale(2, dbo.Behandlu.Nrbehandlung) AS Total_Medikamente, dbo.Get_Totale(3,
|
|
dbo.Behandlu.Nrbehandlung) AS Total_Labor, dbo.Get_Totale(4, dbo.Behandlu.Nrbehandlung) AS RabattTotal, dbo.Get_Totale(5, dbo.Behandlu.Nrbehandlung) AS Vorauszahlung, dbo.get_totalblock(1,
|
|
dbo.Behandlu.Nrbehandlung) AS TotalTextblock, dbo.get_totalblock(2, dbo.Behandlu.Nrbehandlung) AS TotalNumBlock, dbo.Behandlu.empfaenger
|
|
FROM dbo.Behandlu INNER JOIN
|
|
dbo.PRIVAT ON dbo.Behandlu.Nrpatient = dbo.PRIVAT.NRPRIVAT INNER JOIN
|
|
dbo.PRIVAT AS privat_1 ON dbo.Behandlu.Nrbehandler = privat_1.NRPRIVAT INNER JOIN
|
|
dbo.LEISTUNG ON dbo.Behandlu.Nrbehandlung = dbo.LEISTUNG.Nrbehandlung
|
|
WHERE (dbo.LEISTUNG.aktiv = 1)
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Spalten] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Spalten]
|
|
AS
|
|
SELECT SpalteNr, Tabelle, Tabellenspalte, Spalte, Breite, Reihenfolge, TipText, Nummer_Format, Erstellt_am, Mutiert_am, Mutierer, aktiv, readonly, AlsHacken
|
|
FROM dbo.spalten
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Versicherung] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Versicherung]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRFIRMA, NAME1 AS Concat_Name
|
|
FROM dbo.firma
|
|
WHERE (VS = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Zahnarzt] Script Date: 08.02.2019 09:31:15 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[View_Zahnarzt]
|
|
AS
|
|
SELECT TOP (100) PERCENT NRPRIVAT, NAME + ' ' + VORNAME + ', ' + PLZ + ' ' + ORT AS Concat_Name
|
|
FROM dbo.privat
|
|
WHERE (ZAHNARZT = 1)
|
|
ORDER BY Concat_Name
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[41] 4[41] 2[3] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "Behandlu"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 275
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 29
|
|
End
|
|
Begin Table = "PRIVAT"
|
|
Begin Extent =
|
|
Top = 138
|
|
Left = 38
|
|
Bottom = 268
|
|
Right = 239
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_abgeschlossene_Behandlungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_abgeschlossene_Behandlungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "DEBITOR"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 205
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "Faktura"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 243
|
|
Bottom = 266
|
|
Right = 452
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 15
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 3585
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Mahnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Mahnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "DEBITOR"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 205
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "Faktura"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 243
|
|
Bottom = 301
|
|
Right = 452
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 29
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 1440
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Rechnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Offene_Rechnungen'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
|
|
Begin DesignProperties =
|
|
Begin PaneConfigurations =
|
|
Begin PaneConfiguration = 0
|
|
NumPanes = 4
|
|
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
|
|
End
|
|
Begin PaneConfiguration = 1
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 4 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 2
|
|
NumPanes = 3
|
|
Configuration = "(H (1 [50] 2 [25] 3))"
|
|
End
|
|
Begin PaneConfiguration = 3
|
|
NumPanes = 3
|
|
Configuration = "(H (4 [30] 2 [40] 3))"
|
|
End
|
|
Begin PaneConfiguration = 4
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [56] 3))"
|
|
End
|
|
Begin PaneConfiguration = 5
|
|
NumPanes = 2
|
|
Configuration = "(H (2 [66] 3))"
|
|
End
|
|
Begin PaneConfiguration = 6
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [50] 3))"
|
|
End
|
|
Begin PaneConfiguration = 7
|
|
NumPanes = 1
|
|
Configuration = "(V (3))"
|
|
End
|
|
Begin PaneConfiguration = 8
|
|
NumPanes = 3
|
|
Configuration = "(H (1[56] 4[18] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 9
|
|
NumPanes = 2
|
|
Configuration = "(H (1 [75] 4))"
|
|
End
|
|
Begin PaneConfiguration = 10
|
|
NumPanes = 2
|
|
Configuration = "(H (1[66] 2) )"
|
|
End
|
|
Begin PaneConfiguration = 11
|
|
NumPanes = 2
|
|
Configuration = "(H (4 [60] 2))"
|
|
End
|
|
Begin PaneConfiguration = 12
|
|
NumPanes = 1
|
|
Configuration = "(H (1) )"
|
|
End
|
|
Begin PaneConfiguration = 13
|
|
NumPanes = 1
|
|
Configuration = "(V (4))"
|
|
End
|
|
Begin PaneConfiguration = 14
|
|
NumPanes = 1
|
|
Configuration = "(V (2))"
|
|
End
|
|
ActivePaneConfig = 0
|
|
End
|
|
Begin DiagramPane =
|
|
Begin Origin =
|
|
Top = 0
|
|
Left = 0
|
|
End
|
|
Begin Tables =
|
|
Begin Table = "Behandlu"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 275
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 42
|
|
End
|
|
Begin Table = "PRIVAT"
|
|
Begin Extent =
|
|
Top = 138
|
|
Left = 38
|
|
Bottom = 268
|
|
Right = 239
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "privat_1"
|
|
Begin Extent =
|
|
Top = 270
|
|
Left = 38
|
|
Bottom = 400
|
|
Right = 239
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "LEISTUNG"
|
|
Begin Extent =
|
|
Top = 402
|
|
Left = 38
|
|
Bottom = 532
|
|
Right = 233
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 11
|
|
Column = 3390
|
|
Alias = 900
|
|
Table = 1170
|
|
Output = 720
|
|
Append = 1400
|
|
NewValue = 1170
|
|
SortType = 1350
|
|
SortOrder = 1410
|
|
GroupBy = 1350
|
|
Filter = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
Or = 1350
|
|
End
|
|
End
|
|
End
|
|
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Rechnung'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_Rechnung'
|
|
GO
|
|
|
|
|
|
****** Object: View [dbo].[View_Offene_Mahnungen] Script Date: 18.02.2019 09:14:06 ******/
|
|
DROP VIEW [dbo].[View_Offene_Mahnungen]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[View_Offene_Mahnungen] Script Date: 18.02.2019 09:14:06 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE VIEW [dbo].[View_Offene_Mahnungen]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.DEBITOR.Nreintrag, dbo.Faktura.Nrfaktura, dbo.DEBITOR.Faelligam, dbo.DEBITOR.Betrag, dbo.DEBITOR.Status, dbo.get_adresse_direkt(dbo.DEBITOR.Nrpatient, 1) AS Patient,
|
|
ISNULL(dbo.get_adresse_direkt(dbo.DEBITOR.Nrdebitor, 1), '') AS Debitor, dbo.DEBITOR.Nrpatient, dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) AS Offener_Betrag, dbo.DEBITOR.Nrdebitor, dbo.Faktura.Mahndatum1,
|
|
dbo.Faktura.Mahnfaelligkeit1, dbo.Faktura.Mahngebuehr1, dbo.Faktura.Mahndatum2, dbo.Faktura.Mahnfaelligkeit2, dbo.Faktura.Mahngebuehr2, dbo.Faktura.Mahndatum3, dbo.Faktura.Mahnfaelligkeit3,
|
|
dbo.Faktura.Mahngebuehr3, dbo.Faktura.erstellt_am, dbo.Faktura.MAHNSTOP, CASE WHEN mahndatum1 IS NULL AND dbo.faktura.faelligam < getdate() THEN 1 WHEN mahndatum2 IS NULL AND mahnfaelligkeit1 < getdate()
|
|
THEN 2 WHEN mahndatum3 IS NULL AND mahnfaelligkeit2 < getdate() THEN 3 WHEN mahndatum3 IS NOT NULL THEN 4 END AS Mahnstufe
|
|
FROM dbo.DEBITOR INNER JOIN
|
|
dbo.Faktura ON dbo.DEBITOR.Nrfaktura = dbo.Faktura.Nrfaktura
|
|
WHERE (dbo.DEBITOR.Status <> 9) AND (dbo.DEBITOR.Betrag <> 0) AND (dbo.DEBITOR.Betrag > 0.04) AND (dbo.DEBITOR.aktiv = 1) AND (dbo.get_offener_betrag(dbo.Faktura.Nrfaktura) > 0) AND (dbo.Faktura.Status <> 9)
|
|
ORDER BY dbo.DEBITOR.Faelligam DESC
|
|
GO |