| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374 |
- SELECT [F].*
- , round([offen] * 0.19, 2) AS [Mwst_Betrag_berechnet]
- , [K].[Kunde_Name]
- , [K].[Kunde_Nachname]
- , [K].[Kunde_Vorname]
- , [K].[Kunde_Adresse_ID]
- , [K].[Kunde_Adresse]
- , [K].[Kunde_PLZ]
- , [K].[Kunde_Ort]
- , [K].[Kunde_Land]
- , [K].[Kunde_Telefon]
- , [K].[Kunde_Email]
- , [K].[Payment_Terms_Code]
- , [K].[Gen_Bus_Posting_Group]
- , [K].[Customer_Group_Code]
- , [K].[Customer_Group_Description]
- , isnull([FD].[Bearbeitet], 'N') AS [Bearbeitet]
- , isnull([FD].[Versicherung], 'N') AS [Versicherung]
- , isnull([FD].[Rechtsanwalt], 'N') AS [Rechtsanwalt]
- , isnull([FD].[Reklamation], 'N') AS [Reklamation]
- , isnull([FD].[Mahnen_aussetzen], 'N') AS [Mahnen_aussetzen]
- , isnull([FD].[Selbstbeteiligung], '') AS [Selbstbeteiligung]
- , isnull([FD].[Selbstbeteiligung_Betrag], 0.0) AS [Selbstbeteiligung_Betrag]
- , isnull([FD].[Mwst], '') AS [Mwst]
- , isnull([FD].[Mwst_Betrag], 0.0) AS [Mwst_Betrag]
- , isnull([FD].[Vers_Adresse_ID], '') AS [Vers_Adresse_ID]
- , isnull([FD].[Versicherung_Typ], '') AS [Versicherung_Typ]
- , isnull([FD].[Reklamation_Begruendung], '') AS [Reklamation_Begruendung]
- , isnull([FD].[Rechtsanwalt_Begruendung], '') AS [Rechtsanwalt_Begruendung]
- , isnull([FD].[Rechtsanwalt_Aktenzeichen], '') AS [Rechtsanwalt_Aktenzeichen]
- , isnull([FD].[Rechtsanwalt_Stand], '') AS [Rechtsanwalt_Stand]
- , isnull([FD].[Mahnen_Begruendung], '') AS [Mahnen_Begruendung]
- , isnull([FD].[Wiedervorlage], coalesce([FD].[Wiedervorlage], dateadd(DAY, 14, [F].[Invoice_Date]))) AS [Wiedervorlage]
- , [V].[Vers_Name]
- , [V].[Vers_Adresse]
- , [V].[Vers_PLZ]
- , [V].[Vers_Ort]
- , [V].[Vers_Telefon]
- , [V].[Vers_Telefax]
- , [V].[Vers_Web]
- , [V].[Vers_Email]
- , [FZ].[Fahrzeug_VIN]
- , [FZ].[Fahrzeug_Marke_Nr]
- , [FZ].[Fahrzeug_Modell_Nr]
- , [FZ].[Fahrzeug_Modell_Name]
- , [FZ].[Fahrzeug_Kategoriecode]
- , [FZ].[Fahrzeug_Kunde_Nr]
- , [FZ].[Fahrzeug_Kennzeichen]
- , [FZ].[Fahrzeug_Kmstand]
- , [FZ].[Fahrzeug_Antriebsart]
- , [FZ].[Fahrzeug_Erstzulassung]
- , [FZ].[Fahrzeug_Kaufdatum]
- , [FZ].[Fahrzeug_Vorbesitzer_Anzahl]
- , [FZ].[Fahrzeug_Vorbesitzer_Kunde_Nr]
- , iif(K.Kunde_Nr = K2.Kunde_Nr, 'N', 'J') as Fahrzeug_Leasing
- , [K2].[Kunde_Nr] AS [Leasing_Kunde_Nr]
- , [K2].[Kunde_Name] AS [Leasing_Kunde_Name]
- , [K2].[Kunde_Nachname] AS [Leasing_Kunde_Nachname]
- , [K2].[Kunde_Vorname] AS [Leasing_Kunde_Vorname]
- , [K2].[Kunde_Adresse_ID] AS [Leasing_Kunde_Adresse_ID]
- , [K2].[Kunde_Adresse] AS [Leasing_Kunde_Adresse]
- , [K2].[Kunde_PLZ] AS [Leasing_Kunde_PLZ]
- , [K2].[Kunde_Ort] AS [Leasing_Kunde_Ort]
- , [K2].[Kunde_Land] AS [Leasing_Kunde_Land]
- , [K2].[Kunde_Telefon] AS [Leasing_Kunde_Telefon]
- , [K2].[Kunde_Email] AS [Leasing_Kunde_Email]
- FROM [dbo].[Forderungen] [F]
- LEFT JOIN [dbo].[Kunden] [K] ON [F].[Kunde_Nr] = [K].[Kunde_Nr] AND [F].[Client_DB] = [K].[Client_DB]
- LEFT JOIN [dbo].[Fahrzeuge] FZ ON F.VIN = FZ.Fahrzeug_Nr AND [F].[Client_DB] = [FZ].[Client_DB]
- LEFT JOIN [dbo].[Kunden] [K2] ON [FZ].[Fahrzeug_Kunde_Nr] = [K2].[Kunde_Nr] AND [FZ].[Client_DB] = [K2].[Client_DB]
- LEFT JOIN [dbo].[Forderungen_Details] [FD] ON [F].[Client_DB] = [FD].[Client_DB] AND [F].[Document_No] = [FD].[Beleg_Nr]
- LEFT JOIN [dbo].[Versicherungen] [V] ON [FD].[Vers_Adresse_ID] = [V].[Vers_Adresse_ID]
- WHERE [F].[Client_DB] = {{ appsmith.URL.queryParams.Client_DB }}
- AND [F].[Document_No] = {{ appsmith.URL.queryParams.Document_No }}
|
