Error at function keyword for German clients

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

Error at function keyword for German clients

Author

Topic

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-11 : 00:51:36

I have created script file for exporting user defined functions from SQL Server database and exectued at MSDE 2000 sql server version at our clients place. Our clients are using German language operating system.And they are getting error when they execute this script file at command prompt saying that " false syntax near 'function' keyword. But in our sql server environment it is working fine.please help me why this error is occuring.create function syntax is correctly given.

I will be thankful for immediate help.
Regards
Malini
Hyderabad.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-11 : 02:30:42

The language settings of the operating system shouldn't really make a difference to the script. I've seen some pretty wierd bugs in generated SQL Scripts with non-visual characters, though. It is possible you might have a character somewhere that is interpreted differently on a German codepage. Could you post the script that is having problems?

Owais

Where there's a will, I want to be in it.

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-11 : 03:38:04

Hello Owais,
Thanks for immediate reply. As per your suggestion i am sending you the script for your verification. Please go through it give me the solution. The following is the script :
=========================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_AktEinzelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_AktEinzelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_KundeEinzelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_KundeEinzelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_KundeSammelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_KundeSammelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Mietvertrag11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_Mietvertrag11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Mietvertrag12]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_Mietvertrag12]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_ProjEinzelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_ProjEinzelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_ProjSammelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_ProjSammelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Rechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_Rechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Uebersicht01]') and xtype in (N'FN', N'IF', N'TF'))
drop Funktion [dbo].[qry_Uebersicht01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_AktRechnungsempfaenger01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_AktRechnungsempfaenger01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_AktRechnungsempfaenger02]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_AktRechnungsempfaenger02]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_AktVeranstalter01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_AktVeranstalter01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Mietvertrag01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_Mietvertrag01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_ProjRechnungsempfaenger01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_ProjRechnungsempfaenger01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_ProjVeranstalter01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_ProjVeranstalter01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_Rechnung01]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_Rechnung01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_TodayDate]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_TodayDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_c2akt21]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[qry_c2akt21]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE view qry_c2akt21 as
select akt.aktid as ID, aktAdr.aktAdr as aktAddress, aktAdr.aktCID as CID
from Akt left JOIN AktAdr ON Akt.aktID = AktAdr.AktID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_AktRechnungsempfaenger01 AS
SELECT DISTINCT
TOP 100 PERCENT Akt.aktProject, Akt.aktID, AktAdr.AktAdr, AktAdr.AktCID,
Contact_1.CAttn AS AR_CATTN, Contact_1.CFirstName AS AR_CFIRSTNAME,
Contact_1.CLastName AS AR_CLASTNAME, Location.Street AS AR_CSTREET,
Location.PoBox AS AR_CPOBOX, Location.PoBox_ZIP AS AR_CPOBOX_ZIP,
Location.Country AS AR_CCOUNTRY, Location.ZIP AS AR_CZIP, Location.City AS AR_CCITY,
Address.ANR AS AR_AANR, Address.Name1 AS AR_ANAME1, Address.Name2 AS AR_ANAME2,
Address.Street AS AR_ASTREET, Address.PoBox AS AR_APOBOX,
Address.PBCode AS AR_APBCODE, Address.Country AS AR_ACOUNTRY,
Address.PostCode AS AR_APOSTCODE, Address.City AS AR_ACITY, Location.DefaultLocation,
Akt.aktType
FROM ((((AktAdr RIGHT JOIN
Akt ON AktAdr.AktID = Akt.aktID) LEFT JOIN
Contact ON (AktAdr.AktCID = Contact.CID) AND (AktAdr.AktAdr = Contact.CANR)) LEFT JOIN
Address ON Contact.CANR = Address.ANR) LEFT JOIN
Contact AS Contact_1 ON AktAdr.AktCID = Contact_1.CID) LEFT JOIN
Location ON Contact_1.CID = Location.CID
WHERE (AktAdr.AktCID <> 0) AND (Location.DefaultLocation LIKE CASE WHEN [address].[anr] IS NULL
THEN '-1' ELSE '0' END) AND Akt.aktType = 21
ORDER BY Akt.aktID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_AktRechnungsempfaenger02 AS
SELECT DISTINCT
TOP 100 PERCENT Akt.aktID, CASE AktAdr.AktAdr WHEN '0' THEN cast(Contact.Cid AS varchar(15))
ELSE Address.ANR END AS AR_ID,
CASE AktAdr.AktAdr WHEN '0' THEN Contact.CLastName + ' ' + Contact.CFirstName ELSE Address.Name1
END AS AR_NAME1,
--CASE AktAdr.AktAdr WHEN '0' THEN '0' ELSE CAttn + ' ' + CFirstName + ' ' + CLastName END AS AR_NAME2,
CAttn + ' ' + CFirstName + ' ' + CLastName AS AR_NAME2,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CPOBOX ELSE Address.PoBox END AS AR_POBOX,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CPOBOX_ZIP ELSE Address.PBCode END AS AR_PBCODE,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CSTREET ELSE Address.Street END AS AR_STREET,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CZIP ELSE Address.PostCode END AS AR_POSTCODE,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CCOUNTRY ELSE Address.Country END AS AR_COUNTRY,
CASE AktAdr.AktAdr WHEN '0' THEN AR_CCITY ELSE Address.City END AS AR_CITY, Akt.aktProject,
qry_AktRechnungsempfaenger01.DefaultLocation
FROM
(((Akt INNER JOIN AktAdr ON Akt.aktID = AktAdr.AktID) LEFT JOIN
qry_AktRechnungsempfaenger01 ON AktAdr.AktID = qry_AktRechnungsempfaenger01.aktID)
LEFT JOIN Address ON AktAdr.AktAdr = Address.ANR)
LEFT JOIN Contact ON AktAdr.AktCID = Contact.CID

WHERE (((qry_AktRechnungsempfaenger01.AktCID) = AktAdr.AktCID OR
(qry_AktRechnungsempfaenger01.AktCID) IS NULL))
-- AND AKT.AKTID IN (3258,3259)
AND (CAttn + ' ' + CFirstName + ' ' + CLastName) IS NOT NULL
ORDER BY AR_NAME1

-- WHERE (((qry_AktRechnungsempfaenger01.AktCID)=[aktadr].[aktcid] Or (qry_AktRechnungsempfaenger01.AktCID) Is Null))
--ORDER BY IIf(aktadr.aktadr="0",contact.clastname & " " & contact.cfirstname,address.name1);

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_AktVeranstalter01 AS
SELECT DISTINCT
qry_C2Akt21.ID, CASE WHEN address.anr IS NULL THEN CAST(contact.cid as Varchar(15)) ELSE address.anr END AS AV_ID,
CASE WHEN address.anr IS NULL THEN contact.clastname + ' ' + contact.cfirstname ELSE address.name1 END AS AV_NAME1,
CASE WHEN address.anr IS NULL THEN '' ELSE contact.cattn + ' ' + contact.cfirstname + ' ' + contact.clastname END AS AV_NAME2,
CASE WHEN address.anr IS NULL THEN location.pobox ELSE address.pobox END AS AV_POBOX,
CASE WHEN address.anr IS NULL THEN location.pobox_zip ELSE address.pbcode END AS AV_PBCODE,
CASE WHEN address.anr IS NULL THEN location.street ELSE address.street END AS AV_STREET,
CASE WHEN address.anr IS NULL THEN location.country ELSE address.country END AS AV_COUNTRY,
CASE WHEN address.anr IS NULL THEN location.zip ELSE address.postcode END AS AV_POSTCODE,
CASE WHEN address.anr IS NULL THEN location.city ELSE address.city END AS AV_CITY,
CASE WHEN address.anr IS NULL THEN contact.cmobile ELSE address.mainphone END AS AV_PHONE1,
CASE WHEN address.anr IS NULL THEN '' ELSE contact.cmobile END AS AV_PHONE2,
CASE WHEN contact.cspecialaddr = 1 THEN CASE WHEN location.defaultlocation = 0 THEN 0 ELSE 1 END
ELSE 1 END AS OK
FROM
(qry_C2Akt21 left JOIN Address ON qry_C2Akt21.aktaddress = Address.ANR)
left JOIN (Contact LEFT JOIN Location ON Contact.CID = Location.CID)
ON qry_C2Akt21.CID = Contact.CID
WHERE
(CASE WHEN contact.cspecialaddr = 1 THEN CASE WHEN location.defaultlocation = 0 THEN 0
ELSE 1 END ELSE 1 END) = 1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_ProjRechnungsempfaenger01 AS
SELECT DISTINCT CProject.ID,
CASE WHEN [address].[anr] IS NULL THEN cast(Contact.Cid as varchar(15)) ELSE [address].[anr] END AS PR_ID,
--IIf(IsNull([address].[anr]),[contact].[cid],[address].[anr]) AS PR_ID,
CASE WHEN [address].[anr] IS NULL THEN [contact].[cattn] ELSE [address].[name1] END AS PR_NAME1,
--IIf(IsNull([address].[anr]),[contact].[cattn],[address].[name1]) AS PR_NAME1,
CASE WHEN [address].[anr] IS NULL THEN [contact].[clastname] + ' ' + [contact].[cfirstname] ELSE [contact].[cattn] + ' ' + [contact].[cfirstname] + ' ' + [contact].[clastname] END AS PR_NAME2,
--IIf(IsNull([address].[anr]),[contact].[clastname] & " " & [contact].[cfirstname],[contact].[cattn] & " " & [contact].[cfirstname] & " " & [contact].[clastname]) AS PR_NAME2,
CASE WHEN [address].[anr] IS NULL THEN [location].[pobox] ELSE [address].[pobox] END AS PR_POBOX,
--IIf(IsNull([address].[anr]),[location].[pobox],[address].[pobox]) AS PR_POBOX,
CASE WHEN [address].[anr] IS NULL THEN [location].[pobox_zip] ELSE [address].[pbcode] END AS PR_PBCODE,
--IIf(IsNull([address].[anr]),[location].[pobox_zip],[address].[pbcode]) AS PR_PBCODE,
CASE WHEN [address].[anr] IS NULL THEN [location].[street] ELSE [address].[street] END AS PR_STREET,
--IIf(IsNull([address].[anr]),[location].[street],[address].[street]) AS PR_STREET,
CASE WHEN [address].[anr] IS NULL THEN [location].[country] ELSE [address].[country] END AS PR_COUNTRY,
--IIf(IsNull([address].[anr]),[location].[country],[address].[country]) AS PR_COUNTRY,
CASE WHEN [address].[anr] IS NULL THEN [location].[zip] ELSE [address].[postcode] END AS PR_POSTCODE,
--IIf(IsNull([address].[anr]),[location].[zip],[address].[postcode]) AS PR_POSTCODE,
CASE WHEN [address].[anr] IS NULL THEN [location].[city] ELSE [address].[city] END AS PR_CITY,
--IIf(IsNull([address].[anr]),[location].[city],[address].[city]) AS PR_CITY,
Location.DefaultLocation AS DEF, Contact.CspecialAddr,
CASE WHEN [contact].[cspecialaddr]=1 THEN --true (1)
CASE WHEN [location].[defaultlocation]=0 THEN 0 ELSE 1 END
ELSE 1 END AS OK
--IIf([contact].[cspecialaddr]=True,IIf([location].[defaultlocation]=0,0,1),1) AS OK
FROM
((CProject LEFT JOIN Address ON CProject.src1 = Address.ANR) LEFT JOIN Contact ON CProject.src2 = Contact.CID) LEFT JOIN Location ON Contact.CID = Location.CID
WHERE
(CASE WHEN [contact].[cspecialaddr]=1 THEN --true (1)
CASE WHEN [location].[defaultlocation]=0 THEN 0 ELSE 1 END ELSE 1 END)=1
--(((IIf([contact].[cspecialaddr]=True,IIf([location].[defaultlocation]=0,0,1),1))=1));

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_ProjVeranstalter01 AS
SELECT
CProject.ID,
CASE WHEN [address].[anr] IS NULL THEN cast(Contact.Cid as varchar(15)) ELSE [address].[anr] END AS PV_ID,
--IIf(IsNull([address].[anr]),[contact].[cid],[address].[anr]) AS PV_ID,
CASE WHEN [address].[anr] IS NULL THEN [contact].[cattn] ELSE [address].[name1] END AS PV_NAME1, --(IsNull([address].[anr]),[contact].[cattn],[address].[name1]) AS PV_NAME1,
CASE WHEN [address].[anr] IS NULL THEN [contact].[clastname] + ' ' + [contact].[cfirstname] ELSE[contact].[cattn] + ' ' + [contact].[cfirstname] + ' ' + [contact].[clastname] END AS PV_NAME2,
--(IsNull([address].[anr]),[contact].[clastname] & " " & [contact].[cfirstname],[contact].[cattn] & " " & [contact].[cfirstname] & " " & [contact].[clastname]) AS PV_NAME2,
CASE WHEN [address].[anr] IS NULL THEN [location].[pobox] ELSE [address].[pobox] END AS PV_POBOX,
--(IsNull([address].[anr]),[location].[pobox],[address].[pobox]) AS PV_POBOX,
CASE WHEN [address].[anr] IS NULL THEN [location].[pobox_zip] ELSE [address].[pbcode] END AS PV_PBCODE, --(IsNull([address].[anr]),[location].[pobox_zip],[address].[pbcode]) AS PV_PBCODE,
CASE WHEN [address].[anr] IS NULL THEN [location].[street] ELSE [address].[street] END AS PV_STREET, --(IsNull([address].[anr]),[location].[street],[address].[street]) AS PV_STREET,
CASE WHEN [address].[anr] IS NULL THEN [location].[country] ELSE [address].[country] END AS PV_COUNTRY, --(IsNull([address].[anr]),[location].[country],[address].[country]) AS PV_COUNTRY,
CASE WHEN [address].[anr] IS NULL THEN [location].[zip] ELSE [address].[postcode] END AS PV_POSTCODE, --(IsNull([address].[anr]),[location].[zip],[address].[postcode]) AS PV_POSTCODE,
CASE WHEN [address].[anr] IS NULL THEN [location].[city] ELSE [address].[city] END AS PV_CITY, --(IsNull([address].[anr]),[location].[city],[address].[city]) AS PV_CITY,
CASE WHEN [address].[anr] IS NULL THEN [contact].[cmobile] ELSE [address].[mainphone] END AS PV_PHONE1, --(IsNull([address].[anr]),[contact].[cmobile],[address].[mainphone]) AS PV_PHONE1,
CASE WHEN [address].[anr] IS NULL THEN '' ELSE [contact].[cmobile] END AS PV_PHONE2 --(IsNull([address].[anr]),"",[contact].[cmobile]) AS PV_PHONE2
FROM
(CProject LEFT JOIN (Contact LEFT JOIN Location ON Contact.CID = Location.CID) ON CProject.src4 = Contact.CID) LEFT JOIN Address ON CProject.src3 = Address.ANR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_Mietvertrag01 AS
SELECT DISTINCT TOP 100 PERCENT
Project.PID, Project.PName, Project.Active, qry_ProjRechnungsempfaenger01.PR_NAME1, qry_ProjRechnungsempfaenger01.PR_NAME2, qry_ProjRechnungsempfaenger01.PR_POBOX, qry_ProjRechnungsempfaenger01.PR_PBCODE, qry_ProjRechnungsempfaenger01.PR_STREET,
qry_ProjRechnungsempfaenger01.PR_COUNTRY, qry_ProjRechnungsempfaenger01.PR_POSTCODE, qry_ProjRechnungsempfaenger01.PR_CITY, qry_ProjVeranstalter01.PV_NAME1, qry_ProjVeranstalter01.PV_NAME2, qry_ProjVeranstalter01.PV_POBOX,
qry_ProjVeranstalter01.PV_PBCODE, qry_ProjVeranstalter01.PV_STREET, qry_ProjVeranstalter01.PV_COUNTRY, qry_ProjVeranstalter01.PV_POSTCODE, qry_ProjVeranstalter01.PV_CITY, RessReserv.AktID, Akt.aktSComment, AktRecurr.duration, Akt.aktStatus,
qry_AktRechnungsempfaenger02.AR_NAME1, qry_AktRechnungsempfaenger02.AR_NAME2, qry_AktRechnungsempfaenger02.AR_POBOX, qry_AktRechnungsempfaenger02.AR_PBCODE, qry_AktRechnungsempfaenger02.AR_STREET,
qry_AktRechnungsempfaenger02.AR_POSTCODE, qry_AktRechnungsempfaenger02.AR_CITY, qry_AktRechnungsempfaenger02.AR_COUNTRY, Ress.RID, Ress.RName, Ress.RType, KCPreise.src3, RessReserv.ResSComment, RessReserv.ResStartDate, RessReserv.ResStartTime, RessReserv.ResEndDate, RessReserv.ResEndTime,
CASE WHEN IsNumeric([kcgpreise].[num1])=1 THEN [kcgpreise].[num1] ELSE [kcpreise].[num1] END AS PREIS, --IIf(IsNumeric([kcgpreise].[num1]),[kcgpreise].[num1],[kcpreise].[num1]) AS PREIS,
CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END AS START_PREIS, --IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]) AS START_PREIS,
CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END AS END_PREIS, --IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]) AS END_PREIS,
CASE WHEN IsNumeric(kcmwst_1.num1)=1 THEN [kcmwst].[num1] ELSE 0 END AS MWST_SATZ, --IIf(IsNumeric(kcmwst_1.num1),kcmwst_1.num1,IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0)) AS MWST_SATZ,
CASE WHEN ISDATE(kcmwst_1.dat1)=1 THEN kcmwst_1.dat1 ELSE [kcmwst].[dat1] END AS START_MWST, --IIf(IsDate(kcmwst_1.dat1),kcmwst_1.dat1,[kcmwst].[dat1]) AS START_MWST,
CASE WHEN ISDATE(kcmwst_1.dat2)=1 THEN kcmwst_1.dat2 ELSE [kcmwst].[dat2] END AS END_MWST, --IIf(IsDate(kcmwst_1.dat2),kcmwst_1.dat2,[kcmwst].[dat2]) AS END_MWST,
CASE WHEN IsNumeric(kcmwst_1.opt1)=1 THEN kcmwst_1.opt1
ELSE
CASE WHEN IsNumeric([kcmwst].[opt1])=1 THEN [kcmwst].[opt1] ELSE 2 END
END AS MWST_OPT, --IIf(IsNumeric(kcmwst_1.opt1),kcmwst_1.opt1,IIf(IsNumeric([kcmwst].[opt1]),[kcmwst].[opt1],2)) AS MWST_OPT,
CProject.txt1, CProject.txt2, CProject.txt3, CProject.chk3, CProject.chk1, CProject.num1, CProject.dat1, CProject.dat2, CProject.chk2, CProject.num2, CProject.dat3
FROM
((((((((((RessReserv INNER JOIN Ress ON RessReserv.RID = Ress.RID) LEFT JOIN Akt ON RessReserv.AktID = Akt.aktID) LEFT JOIN AktRecurr ON Akt.aktID = AktRecurr.AktID) LEFT JOIN Project ON Akt.aktProject = Project.PID)
LEFT JOIN CProject ON Project.PID = CProject.ID) LEFT JOIN AktUserZord ON Akt.aktID = AktUserZord.AKTID) LEFT JOIN (KCGPreise LEFT JOIN KCMWST AS KCMWST_1 ON KCGPreise.opt1 = KCMWST_1.opt1) ON Ress.RID = KCGPreise.src1)
LEFT JOIN (KCPreise LEFT JOIN KCMWST ON KCPreise.opt1 = KCMWST.opt1) ON Ress.RID = KCPreise.src1) LEFT JOIN qry_AktRechnungsempfaenger02 ON Akt.aktID = qry_AktRechnungsempfaenger02.aktID)
LEFT JOIN qry_ProjRechnungsempfaenger01 ON CProject.ID = qry_ProjRechnungsempfaenger01.ID) LEFT JOIN qry_ProjVeranstalter01 ON CProject.ID = qry_ProjVeranstalter01.ID
WHERE (
((CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END)<=convert(datetime,GETDATE(),104)) AND --((IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]))<=GETDATE()) AND
((CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END)>=convert(datetime,GETDATE(),104)) AND --((IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]))>=GETDATE()) AND
((CASE WHEN IsDate([kcmwst_1].[dat1])=1 THEN [kcmwst_1].[dat1] ELSE [kcmwst].[dat1] END)<=convert(datetime,GETDATE(),104)) AND --((IIf(IsDate([kcmwst_1].[dat1]),[kcmwst_1].[dat1],[kcmwst].[dat1]))<=GETDATE()) AND
((CASE WHEN IsDate([kcmwst_1].[dat2])=1 THEN [kcmwst_1].[dat2] ELSE [kcmwst].[dat2] END)>=convert(datetime,GETDATE(),104)) AND --((IIf(IsDate([kcmwst_1].[dat2]),[kcmwst_1].[dat2],[kcmwst].[dat2]))>=GETDATE())) OR
((CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END)<=convert(datetime,GETDATE(),104)) AND--(((IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]))<=GETDATE()) AND
((CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END)>=convert(datetime,GETDATE(),104)) AND --((IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]))>=GETDATE()) AND
((CASE WHEN IsDate([kcmwst_1].[dat1])=1 THEN [kcmwst_1].[dat1] ELSE [kcmwst].[dat1] END) Is Null) AND --((IIf(IsDate([kcmwst_1].[dat1]),[kcmwst_1].[dat1],[kcmwst].[dat1])) Is Null) AND
((CASE WHEN IsDate([kcmwst_1].[dat2])=1 THEN [kcmwst_1].[dat2] ELSE [kcmwst].[dat2] END) Is Null)) --((IIf(IsDate([kcmwst_1].[dat2]),[kcmwst_1].[dat2],[kcmwst].[dat2])) Is Null))
ORDER BY Project.PID, Ress.RType DESC , RessReserv.ResStartDate, RessReserv.ResStartTime

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_Rechnung01 AS
SELECT DISTINCT TOP 100 PERCENT
Project.PID, Project.PName, Project.Active, qry_ProjRechnungsempfaenger01.PR_ID, qry_ProjRechnungsempfaenger01.PR_NAME1, qry_ProjRechnungsempfaenger01.PR_NAME2,
qry_ProjRechnungsempfaenger01.PR_POBOX, qry_ProjRechnungsempfaenger01.PR_PBCODE, qry_ProjRechnungsempfaenger01.PR_STREET, qry_ProjRechnungsempfaenger01.PR_COUNTRY,
qry_ProjRechnungsempfaenger01.PR_POSTCODE, qry_ProjRechnungsempfaenger01.PR_CITY, qry_ProjVeranstalter01.PV_ID, qry_ProjVeranstalter01.PV_NAME1, qry_ProjVeranstalter01.PV_NAME2,
qry_ProjVeranstalter01.PV_POBOX, qry_ProjVeranstalter01.PV_PBCODE, qry_ProjVeranstalter01.PV_STREET, qry_ProjVeranstalter01.PV_COUNTRY, qry_ProjVeranstalter01.PV_POSTCODE,
qry_ProjVeranstalter01.PV_CITY, RessReserv.AktID, Akt.aktSComment, AktRecurr.duration, Akt.aktStatus, Akt.aktType,Akt.AktStartDate, qry_AktRechnungsempfaenger02.AR_ID, qry_AktRechnungsempfaenger02.AR_NAME1,
qry_AktRechnungsempfaenger02.AR_NAME2, qry_AktRechnungsempfaenger02.AR_POBOX, qry_AktRechnungsempfaenger02.AR_PBCODE, qry_AktRechnungsempfaenger02.AR_STREET,
qry_AktRechnungsempfaenger02.AR_POSTCODE, qry_AktRechnungsempfaenger02.AR_CITY, qry_AktRechnungsempfaenger02.AR_COUNTRY, Ress.RID, Ress.RName, Ress.RType, KCPreise.src3,
RessReserv.ResSComment, RessReserv.ResStartDate, RessReserv.ResStartTime, RessReserv.ResEndDate, RessReserv.ResEndTime,
CASE WHEN IsNumeric([kcgpreise].[num1])=1 THEN [kcgpreise].[num1] ELSE [kcpreise].[num1] END AS PREIS, --IIf(IsNumeric([kcgpreise].[num1]),[kcgpreise].[num1],[kcpreise].[num1]) AS PREIS,
CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END AS START_PREIS, --IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]) AS START_PREIS,
CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END AS END_PREIS,
--IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]) AS END_PREIS,
CASE WHEN IsNumeric(kcmwst_1.num1)=1 THEN kcmwst_1.num1
ELSE CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END
END AS MWST_SATZ,
--IIf(IsNumeric(kcmwst_1.num1),kcmwst_1.num1,IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0)) AS MWST_SATZ,
CASE WHEN IsDate(kcmwst_1.dat1)=1 THEN kcmwst_1.dat1 ELSE [kcmwst].[dat1] END AS START_MWST,
--IIf(IsDate(kcmwst_1.dat1),kcmwst_1.dat1,[kcmwst].[dat1]) AS START_MWST,
CASE WHEN IsDate(kcmwst_1.dat2)=1 THEN kcmwst_1.dat2 ELSE [kcmwst].[dat2] END AS END_MWST,
--IIf(IsDate(kcmwst_1.dat2),kcmwst_1.dat2,[kcmwst].[dat2]) AS END_MWST,
CASE WHEN IsNumeric(kcmwst_1.opt1)=1 THEN kcmwst_1.opt1
ELSE CASE WHEN IsNumeric(kcmwst.opt1)=1 THEN [kcmwst].[opt1] ELSE 2 END
END AS MWST_OPT,
--IIf(IsNumeric(kcmwst_1.opt1),kcmwst_1.opt1,IIf(IsNumeric([kcmwst].[opt1]),[kcmwst].[opt1],2)) AS MWST_OPT,
CProject.txt1, CProject.txt2, CProject.txt3, CProject.chk3, CProject.chk1, CProject.num1, CProject.dat1, CProject.dat2, CProject.chk2, CProject.num2, CProject.dat3
FROM
((((((((((RessReserv INNER JOIN Ress ON RessReserv.RID = Ress.RID) LEFT JOIN Akt ON RessReserv.AktID = Akt.aktID) LEFT JOIN AktRecurr ON Akt.aktID = AktRecurr.AktID)
LEFT JOIN Project ON Akt.aktProject = Project.PID) LEFT JOIN CProject ON Project.PID = CProject.ID)
LEFT JOIN AktUserZord ON Akt.aktID = AktUserZord.AKTID)
LEFT JOIN (KCGPreise LEFT JOIN KCMWST AS KCMWST_1 ON KCGPreise.opt1 = KCMWST_1.opt1) ON Ress.RID = KCGPreise.src1)
LEFT JOIN (KCPreise LEFT JOIN KCMWST ON KCPreise.opt1 = KCMWST.opt1) ON Ress.RID = KCPreise.src1)
LEFT JOIN qry_AktRechnungsempfaenger02 ON Akt.aktID = qry_AktRechnungsempfaenger02.aktID)
LEFT JOIN qry_ProjRechnungsempfaenger01 ON CProject.ID = qry_ProjRechnungsempfaenger01.ID)
LEFT JOIN qry_ProjVeranstalter01 ON CProject.ID= qry_ProjVeranstalter01.ID
WHERE
(((CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END)<=[ressreserv].[resstartdate]) AND
((CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END)>=[ressreserv].[resenddate]))
ORDER BY Project.PID, Ress.RType DESC , RessReserv.ResStartDate, RessReserv.ResStartTime
/*
(((CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END)<=[ressreserv].[resstartdate]) AND
--((IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]))<=[ressreserv].[resstartdate]) AND
((CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END)>=[ressreserv].[resenddate]) AND
--((IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]))>=[ressreserv].[resenddate]) AND
((CASE WHEN IsDate([kcmwst_1].[dat1])=1 THEN [kcmwst_1].[dat1] ELSE [kcmwst].[dat1] END)<=[ressreserv].[resstartdate]) AND
--((IIf(IsDate([kcmwst_1].[dat1]),[kcmwst_1].[dat1],[kcmwst].[dat1]))<=[ressreserv].[resstartdate]) AND
((CASE WHEN IsDate([kcmwst_1].[dat2])=1 THEN [kcmwst_1].[dat2] ELSE [kcmwst].[dat2] END)>=[ressreserv].[resenddate]))
OR
--((IIf(IsDate([kcmwst_1].[dat2]),[kcmwst_1].[dat2],[kcmwst].[dat2]))>=[ressreserv].[resenddate])) OR
(((CASE WHEN IsDate([kcgpreise].[dat1])=1 THEN [kcgpreise].[dat1] ELSE [kcpreise].[dat1] END)<=[ressreserv].[resstartdate]) AND
--(((IIf(IsDate([kcgpreise].[dat1]),[kcgpreise].[dat1],[kcpreise].[dat1]))<=[ressreserv].[resstartdate]) AND
((CASE WHEN IsDate([kcgpreise].[dat2])=1 THEN [kcgpreise].[dat2] ELSE [kcpreise].[dat2] END)>=[ressreserv].[resenddate]) AND
--((IIf(IsDate([kcgpreise].[dat2]),[kcgpreise].[dat2],[kcpreise].[dat2]))>=[ressreserv].[resenddate]) AND
((CASE WHEN IsDate([kcmwst_1].[dat1])=1 THEN [kcmwst_1].[dat1] ELSE [kcmwst].[dat1] END) Is Null) AND
--((IIf(IsDate([kcmwst_1].[dat1]),[kcmwst_1].[dat1],[kcmwst].[dat1])) Is Null) AND
((CASE WHEN IsDate([kcmwst_1].[dat2])=1 THEN [kcmwst_1].[dat2] ELSE [kcmwst].[dat2] END) Is Null))
-- AND (qry_AktRechnungsempfaenger02.AR_NAME2 IS NOT NULL) -- MODIFIED BY KK
--((IIf(IsDate([kcmwst_1].[dat2]),[kcmwst_1].[dat2],[kcmwst].[dat2])) Is Null))
*/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW qry_TodayDate
as
select convert(datetime,getdate(),104) as curDate

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--sp_helptext qry_KundeSammelrechnung11

CREATE Funktion qry_AktEinzelrechnung11(@Forms_F_Auswahl_Rechnung01_Kom varchar(255), @Forms_F_Auswahl_Rechnung01_Opt varchar(255))
RETURNS TABLE AS RETURN (
SELECT DISTINCT
qry_AktRechnungsempfaenger02.AR_ID, qry_AktRechnungsempfaenger02.AR_NAME1,
qry_AktRechnungsempfaenger02.aktProject, Akt.aktSComment, Akt.aktID, Akt.aktStatus, Akt.aktType, CAkt21.chk1,
CAkt21.cmb1, CAkt21.opt1, Project.Active
FROM
((Akt INNER JOIN
CAkt21 ON [Akt].[aktID] = [CAkt21].[ID]) INNER JOIN
qry_AktRechnungsempfaenger02 ON [Akt].[aktID] = [qry_AktRechnungsempfaenger02].[aktID]) LEFT JOIN
Project ON [Akt].[aktProject] = [Project].[PID]
WHERE
(((qry_AktRechnungsempfaenger02.AR_ID) LIKE @Forms_F_Auswahl_Rechnung01_Kom) AND
((qry_AktRechnungsempfaenger02.aktProject) LIKE @Forms_F_Auswahl_Rechnung01_Kom) AND ((Akt.aktStatus) = 0)
AND ((Akt.aktType) = 21) AND ((CAkt21.chk1) = '0') AND ((CAkt21.cmb1)
LIKE CASE @Forms_F_Auswahl_Rechnung01_Opt WHEN 0 THEN '%' ELSE '0' END) AND ((CAkt21.opt1) = 1) AND
((Project.Active) = 1)))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_KundeEinzelrechnung11
(@Forms_F_Auswahl_Rechnung01_Opt varchar(255))
RETURNS TABLE AS RETURN (

SELECT DISTINCT
TOP 100 PERCENT
qry_AktRechnungsempfaenger02.AR_ID
AS KUNDE_ID,
qry_AktRechnungsempfaenger02.AR_NAME1
AS KUNDE_NAME,
qry_AktRechnungsempfaenger02.AR_CITY
AS KUNDE_ORT,
Akt.aktStatus,
Akt.aktType,
CAkt21.chk1,
CAkt21.cmb1,
CAkt21.opt1,
Project.Active
FROM ((CAkt21 INNER JOIN
Akt ON
[CAkt21].[ID] = [Akt].[aktID])
LEFT JOIN
qry_AktRechnungsempfaenger02
ON
[Akt].[aktID] = [qry_AktRechnungsempfaenger02].[aktID])
LEFT JOIN
Project ON
[Akt].[aktProject] = [Project].[PID]
WHERE (((Akt.aktStatus) = 0) AND
((Akt.aktType) = 21) AND
((CAkt21.chk1) = '0') AND
((CAkt21.cmb1) LIKE CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt
= 0 THEN '%' ELSE '0' END) AND
((CAkt21.opt1) = 1) AND ((Project.Active) = 1)))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_KundeSammelrechnung11
(@Forms_F_Auswahl_Rechnung01_Opt varchar (255))
RETURNS TABLE
AS RETURN (
SELECT DISTINCT top 100 percent
CASE WHEN address.anr IS NULL THEN CAST(contact.cid as Varchar(15)) ELSE address.anr END AS KUNDE_ID, --IIf(IsNull(address.anr),contact.cid,address.anr) AS KUNDE_ID,
CASE WHEN address.anr IS NULL THEN contact.clastname + ' ' + contact.cfirstname ELSE address.name1 END AS KUNDE_NAME, --IIf(IsNull(address.anr),contact.clastname & " " & contact.cfirstname,address.name1) AS KUNDE_NAME,
CASE WHEN address.anr IS NULL THEN location.city ELSE address.city END AS KUNDE_ORT, --IIf(IsNull(address.anr),location.city,address.city) AS KUNDE_ORT,
CASE WHEN address.anr IS NULL THEN location.DefaultLocation ELSE 1 END AS DEF, --IIf(IsNull(address.anr),location.DefaultLocation,-1) AS DEF,
Akt.aktStatus, Akt.aktType, CAkt21.chk1, CAkt21.cmb1, CAkt21.opt1, Project.Active, CProject.chk3
FROM
((CAkt21 INNER JOIN (Akt INNER JOIN ((Project INNER JOIN CProject ON Project.PID=CProject.ID)
LEFT JOIN Address ON CProject.src1=Address.ANR) ON Akt.aktProject=Project.PID) ON CAkt21.ID=Akt.aktID)
LEFT JOIN Contact ON CProject.src2=Contact.CID) LEFT JOIN Location ON Contact.CID=Location.CID
WHERE
(
((CASE WHEN address.anr IS NULL THEN CAST(contact.cid as Varchar(15)) ELSE address.anr END) IS NOT NULL) AND --((IIf(IsNull(address.anr),contact.cid,address.anr)) Is Not Null) And
((CASE WHEN address.anr IS NULL THEN location.DefaultLocation ELSE -1 END)=-1) AND --((IIf(IsNull(address.anr),location.DefaultLocation,-1))=-1) And
((Akt.aktStatus)=0) And ((Akt.aktType)=21) And ((CAkt21.chk1)='0') And
((CAkt21.cmb1) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt =0 THEN '%' ELSE '0' END) AND --IIf(Forms!F_Auswahl_Rechnung01!Optionsrahmen1=0,"*",'0')) And
((CAkt21.opt1)=0) And ((Project.Active)=1) And ((CProject.chk3)='0'))
ORDER BY CASE WHEN address.anr IS NULL THEN contact.clastname + ' ' + contact.cfirstname ELSE address.name1 END --IIf(IsNull(address.anr),contact.clastname & " " & contact.cfirstname,address.name1))
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--SELECT * FROM qry_Mietvertrag11(0,0,0,656)
--SELECT * FROM qry_Rechnung01
--SELECT * FROM qry_AktVeranstalter01
CREATE Funktion qry_Mietvertrag11 (
@Forms_F_Auswahl_Rechnung01_Opt varchar (255),
@Forms_F_Auswahl_Rechnung01_Opt1 varchar (255),
@Forms_F_Auswahl_Rechnung01_EndAkt varchar (255),
@Forms_F_Auswahl_Rechnung01_EndPrj varchar (255)
)
RETURNS TABLE
AS
RETURN (
SELECT DISTINCT TOP 100 PERCENT
[qry_Rechnung01].[PID], [qry_Rechnung01].[PName], [qry_Rechnung01].[Active],
[qry_Rechnung01].[PR_ID], [qry_Rechnung01].[PR_NAME1], [qry_Rechnung01].[PR_NAME2], [qry_Rechnung01].[PR_POBOX],
[qry_Rechnung01].[PR_PBCODE], [qry_Rechnung01].[PR_STREET], [qry_Rechnung01].[PR_COUNTRY], [qry_Rechnung01].[PR_POSTCODE],
[qry_Rechnung01].[PR_CITY], [qry_Rechnung01].[PV_ID], [qry_Rechnung01].[PV_NAME1], [qry_Rechnung01].[PV_NAME2],
[qry_Rechnung01].[PV_POBOX], [qry_Rechnung01].[PV_PBCODE], [qry_Rechnung01].[PV_STREET], [qry_Rechnung01].[PV_COUNTRY],
[qry_Rechnung01].[PV_POSTCODE], [qry_Rechnung01].[PV_CITY], [qry_Rechnung01].[AktID],
CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END AS AKT,
CASE WHEN [cakt21].[cmb1]=0 THEN 'definitiv'
WHEN [cakt21].[cmb1]=1 THEN 'provisorisch'
WHEN [cakt21].[cmb1]=2 THEN '1. Option'
ELSE '2. Option'
END AS Status,
[qry_Rechnung01].[aktSComment], [qry_Rechnung01].[duration], [qry_Rechnung01].[aktStatus], [qry_Rechnung01].[aktType], [qry_Rechnung01].[AR_ID],
[qry_Rechnung01].[AR_NAME1], [qry_Rechnung01].[AR_NAME2], [qry_Rechnung01].[AR_POBOX], [qry_Rechnung01].[AR_PBCODE], [qry_Rechnung01].[AR_STREET],
[qry_Rechnung01].[AR_POSTCODE], [qry_Rechnung01].[AR_CITY], [qry_Rechnung01].[AR_COUNTRY], [qry_AktVeranstalter01].[AV_ID], [qry_AktVeranstalter01].[AV_NAME1],
[qry_AktVeranstalter01].[AV_NAME2], [qry_AktVeranstalter01].[AV_POBOX], [qry_AktVeranstalter01].[AV_PBCODE], [qry_AktVeranstalter01].[AV_STREET], [qry_AktVeranstalter01].[AV_COUNTRY],
[qry_AktVeranstalter01].[AV_POSTCODE], [qry_AktVeranstalter01].[AV_CITY], [qry_Rechnung01].[RID], [qry_Rechnung01].[RName], [CAkt21].[src1], [CAkt21].[chk1] as caktchk1, [CAkt21].[opt1] as caktopt1,
[qry_Rechnung01].[RType] as RType, [qry_Rechnung01].[ResSComment], [qry_Rechnung01].[ResStartDate], [qry_Rechnung01].[ResStartTime], [qry_Rechnung01].[ResEndDate], [qry_Rechnung01].[ResEndTime],
[qry_Rechnung01].[START_PREIS], [qry_Rechnung01].[END_PREIS], [qry_Rechnung01].[PREIS] as preis, [qry_Rechnung01].[START_MWST], [qry_Rechnung01].[END_MWST],
[qry_Rechnung01].[MWST_SATZ] as MWST_SATZ, [qry_Rechnung01].[MWST_OPT], [PREIS]*[MWST_SATZ] AS MWST_BETRAG,
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_SATZ,
--IIf([rtype]=1,IIf([cakt21].[chk6]='1',0,[mwst_satz]),[mwst_satz]) AS S_MWST_SATZ,
CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END AS FAKTOR_GERAET,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis]*[CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END AS S_PREIS,
-- ELSE preis * FAKTOR_GERAET END AS S_PREIS,
--IIf([Rtype]=1,IIf([cakt21].[chk6]='1',0,[preis]*[faktor_saal]),[preis]*[faktor_geraet]) AS S_PREIS,
-- S_PREIS *S_MWST_SATZ AS S_MWST_BETRAG,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis]*[CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END *
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_BETRAG,
[CAkt21].[chk6], [CAkt21].[num1] as caktNum1,
[CAkt21].[num3] AS FAKTOR_SAAL,
[KCMWST].[dat1], [KCMWST].[dat2] as kcdat2, [KCMWST].[opt1], [CAkt21].[chk2],
--IIf(@Forms_F_Auswahl_Rechnung01_Opt =0,[qry_Rechnung01]![AktID],@Forms_F_Auswahl_Rechnung01_EndAkt) AS AKT,
CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END AS I_MWST_SATZ,
--IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0) AS I_MWST_SATZ,
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN cakt21.num1 * CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END ELSE 0 END AS I_MWST_BETRAG,
--IIf([chk6]='1',[cakt21].[num1]*[I_MWST_SATZ],0) AS I_MWST_BETRAG,
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN [cakt21].[num1] ELSE 0 END AS I_PREIS,
--IIf([cakt21].[chk6]='1',[cakt21].[num1],0) AS I_PREIS,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num4] ELSE 0 END AS GAR_PRODUKT1,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num4],0) AS GAR_PRODUKT1,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num2] ELSE 0 END AS GAR_PRODUKT2,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num2],0) AS GAR_PRODUKT2,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
ELSE [cakt21].[num2] END AS GAR_BETRAG,
--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,[Cakt21].[num4]*[Cakt21].[num2],[cakt21].[num2]))) AS GAR_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END AS GAR_BETRAG0,
--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1),[cakt21].[num2]/(kcmwst_1.num1+1)))) AS GAR_BETRAG0,
case when isnumeric(CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END)=1 then kcmwst_1.num1 else 0 END AS GAR_MWST_SATZ,
-- CASE WHEN [GAR_BETRAG0]=0 THEN 0
-- WHEN IsNumeric(GAR_BETRAG0)=1 THEN kcmwst_1.num1
-- ELSE 0 END AS GAR_MWST_SATZ,
--IIf([GAR_BETRAG0]=0,0,IIf(IsNumeric([GAR_BETRAG0]),kcmwst_1.num1,0)) AS GAR_MWST_SATZ,
KCMWST_1.dat1 as kc1dat1, KCMWST_1.dat2 as kc1dat2,
-- [GAR_BETRAG]-[GAR_BETRAG0] AS GAR_MWST_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
ELSE [cakt21].[num2] END - CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END AS GAR_MWST_BETRAG,
CASE WHEN [Cakt21].[cmb2]=0 THEN 'Direktinkasso'
WHEN [Cakt21].[cmb2]=1 THEN 'pro Eintritt'
WHEN [Cakt21].[cmb2]=2 THEN 'nach Aufwand'
WHEN [Cakt21].[cmb2]=3 THEN 'Mehraufwand'
WHEN [Cakt21].[cmb2]=4 THEN 'unbewacht'
WHEN [Cakt21].[cmb2]=5 THEN 'bei schlechtem Wetter bewacht nach Aufwan'
ELSE 'nach Vereinbarung' END AS GAR_TEXT,
--IIf([Cakt21].[cmb2]=0,'Direktinkasso',IIf([CAkt21].[cmb2]=1,'pro Eintritt',IIf([CAkt21].[cmb2]=2,'nach Aufwand',IIf([CAkt21].[cmb2]=3,'Mehraufwand','unbewacht')))) AS GAR_TEXT,
[qry_Rechnung01].[txt1], [qry_Rechnung01].[txt2], [qry_Rechnung01].[txt3],
[qry_Rechnung01].[chk3], [qry_Rechnung01].[chk1], [qry_Rechnung01].[num1],
[qry_Rechnung01].[dat1] as Rdat1, --modified by Sisira
[qry_Rechnung01].[dat2],
CASE WHEN [qry_Rechnung01].[chk1]='1' OR [qry_Rechnung01].[chk1]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat2])=1 THEN [qry_Rechnung01].[num1] ELSE 0 END
ELSE 0 END AS AKONTO,
--IIf([qry_Rechnung01].[chk1]='1',IIf(IsDate([qry_Rechnung01].[dat2]),[qry_Rechnung01].[num1],0),0) AS AKONTO,
[qry_Rechnung01].[chk2] AS QRYCHK2, [qry_Rechnung01].[num2] AS QRYNUM2, [qry_Rechnung01].[dat3] AS QRYDAT3,
CASE WHEN [qry_Rechnung01].[chk2]='1' OR [qry_Rechnung01].[chk2]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat3])=1 THEN [qry_Rechnung01].[num2] ELSE 0 END
ELSE 0 END AS BEITRAG,
--IIf([qry_Rechnung01].[chk2]='1',IIf(IsDate([qry_Rechnung01].[dat3]),[qry_Rechnung01].[num2],0),0) AS BEITRAG,
/*
CASE WHEN [cakt27].[chk5]=0 THEN convert(float,txt1) ELSE 0 END AS Item1,
CASE WHEN [cakt27].[chk3]=0 THEN convert(float,txt1) ELSE 0 END AS Item2,
CASE WHEN [cakt27].[chk4]=0 THEN convert(float,txt1) ELSE 0 END AS Item3,
CASE WHEN [cakt27].[chk7]=0 THEN convert(float,txt1) ELSE 0 END AS Item4,
CASE WHEN [cakt27].[chk8]=0 THEN convert(float,txt1) ELSE 0 END AS Item5,
CASE WHEN [cakt27].[chk9]=0 THEN convert(float,txt1) ELSE 0 END AS Item6,
CASE WHEN [cakt27].[chk10]=0 THEN convert(float,txt1) ELSE 0 END AS Item7,
CASE WHEN [cakt27].[chk11]=0 THEN convert(float,txt1) ELSE 0 END AS Item8,
CASE WHEN [cakt27].[chk12]=0 THEN convert(float,txt1) ELSE 0 END AS Item9,
CASE WHEN [cakt27].[chk13]=0 THEN convert(float,txt1) ELSE 0 END AS Item10,
*/
[CAkt21].[num2], [CAkt21].[cmb1]
FROM
((((((CAkt21 INNER JOIN qry_Rechnung01 ON [CAkt21].[ID]=[qry_Rechnung01].[AktID])
LEFT JOIN KCMWST ON [CAkt21].[opt2]=[KCMWST].[opt1])
LEFT JOIN qry_C2Akt21 ON [qry_Rechnung01].[AktID]=[qry_C2Akt21].[ID])
LEFT JOIN AktRecurr ON [AktRecurr].[AktID]=[CAkt21].[ID]) -- MODIFIED BY KK
LEFT JOIN tbl_hlp_0 ON [CAkt21].[chk1]=[tbl_hlp_0].[chk1])
LEFT JOIN KCMWST AS KCMWST_1 ON [tbl_hlp_0].[opt1]=KCMWST_1.opt1)
LEFT JOIN qry_AktVeranstalter01 ON [qry_C2Akt21].[ID]=[qry_AktVeranstalter01].[ID]
WHERE
(([qry_Rechnung01].[PID])=@Forms_F_Auswahl_Rechnung01_EndPrj)
And (([qry_Rechnung01].[Active])=1) And (([qry_Rechnung01].[aktStatus])=0)
And (([qry_Rechnung01].[aktType])=21) And (([CAkt21].[opt1])=@Forms_F_Auswahl_Rechnung01_Opt )
And (([CAkt21].[chk1])='0')
And (([KCMWST].[dat1])<=[qry_Rechnung01].[resstartdate] Or ([KCMWST].[dat1]) Is Null)
And (([KCMWST].[dat2])>=[qry_Rechnung01].[resenddate] Or ([KCMWST].[dat2]) Is Null)
And ((KCMWST_1.dat1)<=(select curDate from qry_TodayDate))
And ((KCMWST_1.dat2)>=(select curDate from qry_TodayDate))
And (([qry_Rechnung01].[chk3])=0)
And (([CAkt21].[cmb1]) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt1=1 THEN '0' ELSE '%' END)--IIf(@Forms_F_Auswahl_Rechnung01_Opt =1,'0','*'))
And (([qry_Rechnung01].[src3])=[cakt21].[src1] Or ([qry_Rechnung01].[src3]) Is Null)
And ((CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END)=[qry_Rechnung01].[AktID])
ORDER BY [qry_Rechnung01].[AktID] DESC,[qry_Rechnung01].[RType]
UNION
SELECT DISTINCT TOP 100 PERCENT
[qry_Rechnung01].[PID], [qry_Rechnung01].[PName], [qry_Rechnung01].[Active],
[qry_Rechnung01].[PR_ID], [qry_Rechnung01].[PR_NAME1], [qry_Rechnung01].[PR_NAME2], [qry_Rechnung01].[PR_POBOX],
[qry_Rechnung01].[PR_PBCODE], [qry_Rechnung01].[PR_STREET], [qry_Rechnung01].[PR_COUNTRY], [qry_Rechnung01].[PR_POSTCODE],
[qry_Rechnung01].[PR_CITY], [qry_Rechnung01].[PV_ID], [qry_Rechnung01].[PV_NAME1], [qry_Rechnung01].[PV_NAME2],
[qry_Rechnung01].[PV_POBOX], [qry_Rechnung01].[PV_PBCODE], [qry_Rechnung01].[PV_STREET], [qry_Rechnung01].[PV_COUNTRY],
[qry_Rechnung01].[PV_POSTCODE], [qry_Rechnung01].[PV_CITY], [qry_Rechnung01].[AktID],
CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END AS AKT,
CASE WHEN [cakt27].[cmb1]=0 THEN 'definitiv'
WHEN [cakt27].[cmb1]=1 THEN 'provisorisch'
WHEN [cakt27].[cmb1]=2 THEN '1. Option'
ELSE '2. Option'
END AS Status,
[qry_Rechnung01].[aktSComment], [qry_Rechnung01].[duration], [qry_Rechnung01].[aktStatus], [qry_Rechnung01].[aktType], [qry_Rechnung01].[AR_ID],
[qry_Rechnung01].[AR_NAME1], [qry_Rechnung01].[AR_NAME2], [qry_Rechnung01].[AR_POBOX], [qry_Rechnung01].[AR_PBCODE], [qry_Rechnung01].[AR_STREET],
[qry_Rechnung01].[AR_POSTCODE], [qry_Rechnung01].[AR_CITY], [qry_Rechnung01].[AR_COUNTRY], [qry_AktVeranstalter01].[AV_ID], [qry_AktVeranstalter01].[AV_NAME1],
[qry_AktVeranstalter01].[AV_NAME2], [qry_AktVeranstalter01].[AV_POBOX], [qry_AktVeranstalter01].[AV_PBCODE], [qry_AktVeranstalter01].[AV_STREET], [qry_AktVeranstalter01].[AV_COUNTRY],
[qry_AktVeranstalter01].[AV_POSTCODE], [qry_AktVeranstalter01].[AV_CITY], [qry_Rechnung01].[RID], [qry_Rechnung01].[RName], [cakt27].[src1], [cakt27].[chk1] as caktchk1, [cakt27].[opt1] as caktopt1,
[qry_Rechnung01].[RType] as RType, [qry_Rechnung01].[ResSComment], [qry_Rechnung01].[ResStartDate], [qry_Rechnung01].[ResStartTime], [qry_Rechnung01].[ResEndDate], [qry_Rechnung01].[ResEndTime],
[qry_Rechnung01].[START_PREIS], [qry_Rechnung01].[END_PREIS], [qry_Rechnung01].[PREIS] as preis, [qry_Rechnung01].[START_MWST], [qry_Rechnung01].[END_MWST],
[qry_Rechnung01].[MWST_SATZ] as MWST_SATZ, [qry_Rechnung01].[MWST_OPT], [PREIS]*[MWST_SATZ] AS MWST_BETRAG,
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_SATZ,
--IIf([rtype]=1,IIf([cakt27].[chk6]='1',0,[mwst_satz]),[mwst_satz]) AS S_MWST_SATZ,
CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END AS FAKTOR_GERAET,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN 0 ELSE [preis]*[cakt27].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END AS S_PREIS,
-- ELSE preis * FAKTOR_GERAET END AS S_PREIS,
--IIf([Rtype]=1,IIf([cakt27].[chk6]='1',0,[preis]*[faktor_saal]),[preis]*[faktor_geraet]) AS S_PREIS,
-- S_PREIS *S_MWST_SATZ AS S_MWST_BETRAG,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN 0 ELSE [preis]*[cakt27].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END *
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_BETRAG,
[cakt27].[chk6], [cakt27].[num1] as caktNum1,
[cakt27].[num3] AS FAKTOR_SAAL,
[KCMWST].[dat1], [KCMWST].[dat2] as kcdat2, [KCMWST].[opt1], [cakt27].[chk2],
--IIf(@Forms_F_Auswahl_Rechnung01_Opt =0,[qry_Rechnung01]![AktID],@Forms_F_Auswahl_Rechnung01_EndAkt) AS AKT,
CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END AS I_MWST_SATZ,

--IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0) AS I_MWST_SATZ,
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN cakt27.num1 * CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END ELSE 0 END AS I_MWST_BETRAG,
--IIf([chk6]='1',[cakt27].[num1]*[I_MWST_SATZ],0) AS I_MWST_BETRAG,
CASE WHEN [cakt27].[chk6]='1' OR [cakt27].[chk6]='-1' THEN [cakt27].[num1] ELSE 0 END AS I_PREIS,
--IIf([cakt27].[chk6]='1',[cakt27].[num1],0) AS I_PREIS,
CASE WHEN [cakt27].[cmb2]=1 THEN [cakt27].[num4] ELSE 0 END AS GAR_PRODUKT1,
--IIf([cakt27].[cmb2]=1,[cakt27].[num4],0) AS GAR_PRODUKT1,
CASE WHEN [cakt27].[cmb2]=1 THEN [cakt27].[num2] ELSE 0 END AS GAR_PRODUKT2,
--IIf([cakt27].[cmb2]=1,[cakt27].[num2],0) AS GAR_PRODUKT2,
CASE WHEN [cakt27].[cmb2]=4 THEN 0
WHEN [cakt27].[cmb2]=0 THEN 0
WHEN [cakt27].[cmb2]=1 THEN [cakt27].[num4]*[cakt27].[num2]
ELSE [cakt27].[num2] END AS GAR_BETRAG,
--IIf([cakt27].[cmb2]=4,0,IIf([cakt27].[cmb2]=0,0,IIf([cakt27].[cmb2]=1,[cakt27].[num4]*[cakt27].[num2],[cakt27].[num2]))) AS GAR_BETRAG,
CASE WHEN [cakt27].[cmb2]=4 THEN 0
WHEN [cakt27].[cmb2]=0 THEN 0
WHEN [cakt27].[cmb2]=1 THEN ([cakt27].[num4]*[cakt27].[num2])/(kcmwst_1.num1+1)
ELSE [cakt27].[num2]/kcmwst_1.num1+1 END AS GAR_BETRAG0,
--IIf([cakt27].[cmb2]=4,0,IIf([cakt27].[cmb2]=0,0,IIf([cakt27].[cmb2]=1,([cakt27].[num4]*[cakt27].[num2])/(kcmwst_1.num1+1),[cakt27].[num2]/(kcmwst_1.num1+1)))) AS GAR_BETRAG0,
case when isnumeric(CASE WHEN [cakt27].[cmb2]=4 THEN 0
WHEN [cakt27].[cmb2]=0 THEN 0
WHEN [cakt27].[cmb2]=1 THEN ([cakt27].[num4]*[cakt27].[num2])/(kcmwst_1.num1+1)
ELSE [cakt27].[num2]/kcmwst_1.num1+1 END)=1 then kcmwst_1.num1 else 0 END AS GAR_MWST_SATZ,
-- CASE WHEN [GAR_BETRAG0]=0 THEN 0
-- WHEN IsNumeric(GAR_BETRAG0)=1 THEN kcmwst_1.num1
-- ELSE 0 END AS GAR_MWST_SATZ,
--IIf([GAR_BETRAG0]=0,0,IIf(IsNumeric([GAR_BETRAG0]),kcmwst_1.num1,0)) AS GAR_MWST_SATZ,
KCMWST_1.dat1 as kc1dat1, KCMWST_1.dat2 as kc1dat2,
-- [GAR_BETRAG]-[GAR_BETRAG0] AS GAR_MWST_BETRAG,
CASE WHEN [cakt27].[cmb2]=4 THEN 0
WHEN [cakt27].[cmb2]=0 THEN 0
WHEN [cakt27].[cmb2]=1 THEN [cakt27].[num4]*[cakt27].[num2]
ELSE [cakt27].[num2] END - CASE WHEN [cakt27].[cmb2]=4 THEN 0
WHEN [cakt27].[cmb2]=0 THEN 0
WHEN [cakt27].[cmb2]=1 THEN ([cakt27].[num4]*[cakt27].[num2])/(kcmwst_1.num1+1)
ELSE [cakt27].[num2]/kcmwst_1.num1+1 END AS GAR_MWST_BETRAG,
CASE WHEN [cakt27].[cmb2]=0 THEN 'Direktinkasso'
WHEN [cakt27].[cmb2]=1 THEN 'pro Eintritt'
WHEN [cakt27].[cmb2]=2 THEN 'nach Aufwand'
WHEN [cakt27].[cmb2]=3 THEN 'Mehraufwand'
WHEN [cakt27].[cmb2]=4 THEN 'unbewacht'
WHEN [cakt27].[cmb2]=5 THEN 'bei schlechtem Wetter bewacht nach Aufwan'
ELSE 'nach Vereinbarung' END AS GAR_TEXT,
--IIf([cakt27].[cmb2]=0,'Direktinkasso',IIf([cakt27].[cmb2]=1,'pro Eintritt',IIf([cakt27].[cmb2]=2,'nach Aufwand',IIf([cakt27].[cmb2]=3,'Mehraufwand','unbewacht')))) AS GAR_TEXT,
[qry_Rechnung01].[txt1], [qry_Rechnung01].[txt2], [qry_Rechnung01].[txt3],
[qry_Rechnung01].[chk3], [qry_Rechnung01].[chk1], [qry_Rechnung01].[num1],
[qry_Rechnung01].[dat1] as Rdat1, --modified by Sisira
[qry_Rechnung01].[dat2],
CASE WHEN [qry_Rechnung01].[chk1]='1' OR [qry_Rechnung01].[chk1]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat2])=1 THEN [qry_Rechnung01].[num1] ELSE 0 END

ELSE 0 END AS AKONTO,
--IIf([qry_Rechnung01].[chk1]='1',IIf(IsDate([qry_Rechnung01].[dat2]),[qry_Rechnung01].[num1],0),0) AS AKONTO,
[qry_Rechnung01].[chk2] AS QRYCHK2, [qry_Rechnung01].[num2] AS QRYNUM2, [qry_Rechnung01].[dat3] AS QRYDAT3,
CASE WHEN [qry_Rechnung01].[chk2]='1' OR [qry_Rechnung01].[chk2]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat3])=1 THEN [qry_Rechnung01].[num2] ELSE 0 END
ELSE 0 END AS BEITRAG,
--IIf([qry_Rechnung01].[chk2]='1',IIf(IsDate([qry_Rechnung01].[dat3]),[qry_Rechnung01].[num2],0),0) AS BEITRAG,
[cakt27].[num2], [cakt27].[cmb1]
FROM
((((((cakt27 INNER JOIN qry_Rechnung01 ON [cakt27].[ID]=[qry_Rechnung01].[AktID])
LEFT JOIN KCMWST ON [cakt27].[opt2]=[KCMWST].[opt1])
LEFT JOIN qry_C2Akt21 ON [qry_Rechnung01].[AktID]=[qry_C2Akt21].[ID])
LEFT JOIN AktRecurr ON [AktRecurr].[AktID]=[cakt27].[ID]) -- MODIFIED BY KK
LEFT JOIN tbl_hlp_0 ON [cakt27].[chk1]=[tbl_hlp_0].[chk1])
LEFT JOIN KCMWST AS KCMWST_1 ON [tbl_hlp_0].[opt1]=KCMWST_1.opt1)
LEFT JOIN qry_AktVeranstalter01 ON [qry_C2Akt21].[ID]=[qry_AktVeranstalter01].[ID]
WHERE
(([qry_Rechnung01].[PID])=@Forms_F_Auswahl_Rechnung01_EndPrj)
And (([qry_Rechnung01].[Active])=1) And (([qry_Rechnung01].[aktStatus])=0)
And (([qry_Rechnung01].[aktType])=27) And (([cakt27].[opt1])=@Forms_F_Auswahl_Rechnung01_Opt )
And (([cakt27].[chk1])='0')
And (([KCMWST].[dat1])<=[qry_Rechnung01].[resstartdate] Or ([KCMWST].[dat1]) Is Null)
And (([KCMWST].[dat2])>=[qry_Rechnung01].[resenddate] Or ([KCMWST].[dat2]) Is Null)
And ((KCMWST_1.dat1)<=(select curDate from qry_TodayDate))
And ((KCMWST_1.dat2)>=(select curDate from qry_TodayDate))
And (([qry_Rechnung01].[chk3])=0)
And (([cakt27].[cmb1]) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt1=1 THEN '0' ELSE '%' END)--IIf(@Forms_F_Auswahl_Rechnung01_Opt =1,'0','*'))
And (([qry_Rechnung01].[src3])=[cakt27].[src1] Or ([qry_Rechnung01].[src3]) Is Null)
And ((CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END)=[qry_Rechnung01].[AktID])
ORDER BY [qry_Rechnung01].[AktID] DESC,[qry_Rechnung01].[RType])

--SELECT * FROM qry_Mietvertrag11(0,0,0,656)
--select curDate from qry_TodayDate

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Funktion qry_Mietvertrag12 (
@Forms_F_Auswahl_Rechnung01_Opt varchar (255),
@Forms_F_Auswahl_Rechnung01_Opt1 varchar (255),
@Forms_F_Auswahl_Rechnung01_EndAkt varchar (255),
@Forms_F_Auswahl_Rechnung01_EndPrj varchar (255)
)
RETURNS TABLE
AS
RETURN (
SELECT DISTINCT TOP 100 PERCENT
[qry_Rechnung01].[PID], [qry_Rechnung01].[PName], [qry_Rechnung01].[Active],
[qry_Rechnung01].[PR_ID], [qry_Rechnung01].[PR_NAME1], [qry_Rechnung01].[PR_NAME2], [qry_Rechnung01].[PR_POBOX],
[qry_Rechnung01].[PR_PBCODE], [qry_Rechnung01].[PR_STREET], [qry_Rechnung01].[PR_COUNTRY], [qry_Rechnung01].[PR_POSTCODE],
[qry_Rechnung01].[PR_CITY], [qry_Rechnung01].[PV_ID], [qry_Rechnung01].[PV_NAME1], [qry_Rechnung01].[PV_NAME2],
[qry_Rechnung01].[PV_POBOX], [qry_Rechnung01].[PV_PBCODE], [qry_Rechnung01].[PV_STREET], [qry_Rechnung01].[PV_COUNTRY],
[qry_Rechnung01].[PV_POSTCODE], [qry_Rechnung01].[PV_CITY], [qry_Rechnung01].[AktID],
CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END AS AKT,
CASE WHEN [cakt21].[cmb1]=0 THEN 'definitiv'
WHEN [cakt21].[cmb1]=1 THEN 'provisorisch'
WHEN [cakt21].[cmb1]=2 THEN '1. Option'
ELSE '2. Option'
END AS Status,
[qry_Rechnung01].[aktSComment], [qry_Rechnung01].[duration], [qry_Rechnung01].[aktStatus], [qry_Rechnung01].[aktType], [qry_Rechnung01].[AR_ID],
[qry_Rechnung01].[AR_NAME1], [qry_Rechnung01].[AR_NAME2], [qry_Rechnung01].[AR_POBOX], [qry_Rechnung01].[AR_PBCODE], [qry_Rechnung01].[AR_STREET],
[qry_Rechnung01].[AR_POSTCODE], [qry_Rechnung01].[AR_CITY], [qry_Rechnung01].[AR_COUNTRY], [qry_AktVeranstalter01].[AV_ID], [qry_AktVeranstalter01].[AV_NAME1],
[qry_AktVeranstalter01].[AV_NAME2], [qry_AktVeranstalter01].[AV_POBOX], [qry_AktVeranstalter01].[AV_PBCODE], [qry_AktVeranstalter01].[AV_STREET], [qry_AktVeranstalter01].[AV_COUNTRY],
[qry_AktVeranstalter01].[AV_POSTCODE], [qry_AktVeranstalter01].[AV_CITY], [qry_Rechnung01].[RID], [qry_Rechnung01].[RName], [CAkt21].[src1], [CAkt21].[chk1] as caktchk1, [CAkt21].[opt1] as caktopt1,
[qry_Rechnung01].[RType] as RType, [qry_Rechnung01].[ResSComment], [qry_Rechnung01].[ResStartDate], [qry_Rechnung01].[ResStartTime], [qry_Rechnung01].[ResEndDate], [qry_Rechnung01].[ResEndTime],
[qry_Rechnung01].[START_PREIS], [qry_Rechnung01].[END_PREIS], [qry_Rechnung01].[PREIS] as preis, [qry_Rechnung01].[START_MWST], [qry_Rechnung01].[END_MWST],
[qry_Rechnung01].[MWST_SATZ] as MWST_SATZ, [qry_Rechnung01].[MWST_OPT], [PREIS]*[MWST_SATZ] AS MWST_BETRAG,
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_SATZ,
--IIf([rtype]=1,IIf([cakt21].[chk6]='1',0,[mwst_satz]),[mwst_satz]) AS S_MWST_SATZ,
CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END AS FAKTOR_GERAET,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis]*[CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END AS S_PREIS,
-- ELSE preis * FAKTOR_GERAET END AS S_PREIS,
--IIf([Rtype]=1,IIf([cakt21].[chk6]='1',0,[preis]*[faktor_saal]),[preis]*[faktor_geraet]) AS S_PREIS,
-- S_PREIS *S_MWST_SATZ AS S_MWST_BETRAG,
CASE WHEN [Rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis]*[CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric(aktrecurr.duration)=1 THEN aktrecurr.duration ELSE 1 END END *
CASE WHEN [rtype]=1 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_BETRAG,
[CAkt21].[chk6], [CAkt21].[num1] as caktNum1,
[CAkt21].[num3] AS FAKTOR_SAAL,
[KCMWST].[dat1], [KCMWST].[dat2] as kcdat2, [KCMWST].[opt1], [CAkt21].[chk2],
--IIf(@Forms_F_Auswahl_Rechnung01_Opt =0,[qry_Rechnung01]![AktID],@Forms_F_Auswahl_Rechnung01_EndAkt) AS AKT,
CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END AS I_MWST_SATZ,
--IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0) AS I_MWST_SATZ,
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN cakt21.num1 * CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END ELSE 0 END AS I_MWST_BETRAG,
--IIf([chk6]='1',[cakt21].[num1]*[I_MWST_SATZ],0) AS I_MWST_BETRAG,
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN [cakt21].[num1] ELSE 0 END AS I_PREIS,
--IIf([cakt21].[chk6]='1',[cakt21].[num1],0) AS I_PREIS,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num4] ELSE 0 END AS GAR_PRODUKT1,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num4],0) AS GAR_PRODUKT1,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num2] ELSE 0 END AS GAR_PRODUKT2,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num2],0) AS GAR_PRODUKT2,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
ELSE [cakt21].[num2] END AS GAR_BETRAG,
--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,[Cakt21].[num4]*[Cakt21].[num2],[cakt21].[num2]))) AS GAR_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END AS GAR_BETRAG0,
--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1),[cakt21].[num2]/(kcmwst_1.num1+1)))) AS GAR_BETRAG0,
case when isnumeric(CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END)=1 then kcmwst_1.num1 else 0 END AS GAR_MWST_SATZ,
-- CASE WHEN [GAR_BETRAG0]=0 THEN 0
-- WHEN IsNumeric(GAR_BETRAG0)=1 THEN kcmwst_1.num1
-- ELSE 0 END AS GAR_MWST_SATZ,
--IIf([GAR_BETRAG0]=0,0,IIf(IsNumeric([GAR_BETRAG0]),kcmwst_1.num1,0)) AS GAR_MWST_SATZ,
KCMWST_1.dat1 as kc1dat1, KCMWST_1.dat2 as kc1dat2,
-- [GAR_BETRAG]-[GAR_BETRAG0] AS GAR_MWST_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
ELSE [cakt21].[num2] END - CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/kcmwst_1.num1+1 END AS GAR_MWST_BETRAG,
CASE WHEN [Cakt21].[cmb2]=0 THEN 'Direktinkasso'
WHEN [Cakt21].[cmb2]=1 THEN 'pro Eintritt'
WHEN [Cakt21].[cmb2]=2 THEN 'nach Aufwand'
WHEN [Cakt21].[cmb2]=3 THEN 'Mehraufwand'
WHEN [Cakt21].[cmb2]=4 THEN 'unbewacht'
WHEN [Cakt21].[cmb2]=5 THEN 'bei schlechtem Wetter bewacht nach Aufwan'
ELSE 'nach Vereinbarung' END AS GAR_TEXT,
--IIf([Cakt21].[cmb2]=0,'Direktinkasso',IIf([CAkt21].[cmb2]=1,'pro Eintritt',IIf([CAkt21].[cmb2]=2,'nach Aufwand',IIf([CAkt21].[cmb2]=3,'Mehraufwand','unbewacht')))) AS GAR_TEXT,
[qry_Rechnung01].[txt1], [qry_Rechnung01].[txt2], [qry_Rechnung01].[txt3],
[qry_Rechnung01].[chk3], [qry_Rechnung01].[chk1], [qry_Rechnung01].[num1],
[qry_Rechnung01].[dat1] as Rdat1, --modified by Sisira
[qry_Rechnung01].[dat2],
CASE WHEN [qry_Rechnung01].[chk1]='1' OR [qry_Rechnung01].[chk1]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat2])=1 THEN [qry_Rechnung01].[num1] ELSE 0 END
ELSE 0 END AS AKONTO,
--IIf([qry_Rechnung01].[chk1]='1',IIf(IsDate([qry_Rechnung01].[dat2]),[qry_Rechnung01].[num1],0),0) AS AKONTO,
[qry_Rechnung01].[chk2] AS QRYCHK2, [qry_Rechnung01].[num2] AS QRYNUM2, [qry_Rechnung01].[dat3] AS QRYDAT3,
CASE WHEN [qry_Rechnung01].[chk2]='1' OR [qry_Rechnung01].[chk2]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat3])=1 THEN [qry_Rechnung01].[num2] ELSE 0 END
ELSE 0 END AS BEITRAG,
--IIf([qry_Rechnung01].[chk2]='1',IIf(IsDate([qry_Rechnung01].[dat3]),[qry_Rechnung01].[num2],0),0) AS BEITRAG,
/*
CASE WHEN [cakt27].[chk5]=0 THEN convert(float,txt1) ELSE 0 END AS Item1,
CASE WHEN [cakt27].[chk3]=0 THEN convert(float,txt1) ELSE 0 END AS Item2,
CASE WHEN [cakt27].[chk4]=0 THEN convert(float,txt1) ELSE 0 END AS Item3,
CASE WHEN [cakt27].[chk7]=0 THEN convert(float,txt1) ELSE 0 END AS Item4,
CASE WHEN [cakt27].[chk8]=0 THEN convert(float,txt1) ELSE 0 END AS Item5,
CASE WHEN [cakt27].[chk9]=0 THEN convert(float,txt1) ELSE 0 END AS Item6,
CASE WHEN [cakt27].[chk10]=0 THEN convert(float,txt1) ELSE 0 END AS Item7,
CASE WHEN [cakt27].[chk11]=0 THEN convert(float,txt1) ELSE 0 END AS Item8,
CASE WHEN [cakt27].[chk12]=0 THEN convert(float,txt1) ELSE 0 END AS Item9,
CASE WHEN [cakt27].[chk13]=0 THEN convert(float,txt1) ELSE 0 END AS Item10,
*/
[CAkt21].[num2], [CAkt21].[cmb1]
FROM
((((((CAkt21 INNER JOIN qry_Rechnung01 ON [CAkt21].[ID]=[qry_Rechnung01].[AktID])
LEFT JOIN KCMWST ON [CAkt21].[opt2]=[KCMWST].[opt1])
LEFT JOIN qry_C2Akt21 ON [qry_Rechnung01].[AktID]=[qry_C2Akt21].[ID])
LEFT JOIN AktRecurr ON [AktRecurr].[AktID]=[CAkt21].[ID]) -- MODIFIED BY KK
LEFT JOIN tbl_hlp_0 ON [CAkt21].[chk1]=[tbl_hlp_0].[chk1])
LEFT JOIN KCMWST AS KCMWST_1 ON [tbl_hlp_0].[opt1]=KCMWST_1.opt1)
LEFT JOIN qry_AktVeranstalter01 ON [qry_C2Akt21].[ID]=[qry_AktVeranstalter01].[ID]
WHERE
(([qry_Rechnung01].[PID])=@Forms_F_Auswahl_Rechnung01_EndPrj)
And (([qry_Rechnung01].[Active])=1) And (([qry_Rechnung01].[aktStatus])=0)
And (([qry_Rechnung01].[aktType])=21) And (([CAkt21].[opt1])=@Forms_F_Auswahl_Rechnung01_Opt )
And (([CAkt21].[chk1])='0')
And (([KCMWST].[dat1])<=[qry_Rechnung01].[resstartdate] Or ([KCMWST].[dat1]) Is Null)
And (([KCMWST].[dat2])>=[qry_Rechnung01].[resenddate] Or ([KCMWST].[dat2]) Is Null)
And ((KCMWST_1.dat1)<=(select curDate from qry_TodayDate))
And ((KCMWST_1.dat2)>=(select curDate from qry_TodayDate))
And (([qry_Rechnung01].[chk3])=0)
And (([CAkt21].[cmb1]) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt1=1 THEN '0' ELSE '%' END)--IIf(@Forms_F_Auswahl_Rechnung01_Opt =1,'0','*'))
And (([qry_Rechnung01].[src3])=[cakt21].[src1] Or ([qry_Rechnung01].[src3]) Is Null)
And ((CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END)=[qry_Rechnung01].[AktID])
ORDER BY [qry_Rechnung01].[AktID] DESC,[qry_Rechnung01].[RType])

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_ProjEinzelrechnung11 (
@Forms_F_Auswahl_Rechnung01_Kom varchar (255),
@Forms_F_Auswahl_Rechnung01_Opt varchar (255)
)
RETURNS TABLE
AS RETURN (

SELECT DISTINCT top 100 percent
[qry_ProjRechnungsempfaenger01].[ID], [Project].[PName], [qry_AktRechnungsempfaenger02].[AR_ID], [qry_AktRechnungsempfaenger02].[AR_NAME1], [qry_AktRechnungsempfaenger02].[AR_CITY],
[Akt].[aktStatus], [CAkt21].[chk1], [CAkt21].[cmb1], [CAkt21].[opt1], [Project].[Active]
FROM
(qry_ProjRechnungsempfaenger01 RIGHT JOIN (((Akt LEFT JOIN Project ON [Akt].[aktProject]=[Project].[PID]) LEFT JOIN CProject ON [Project].[PID]=[CProject].[ID])
INNER JOIN CAkt21 ON [Akt].[aktID]=[CAkt21].[ID]) ON [qry_ProjRechnungsempfaenger01].[ID]=[CProject].[ID]) LEFT JOIN qry_AktRechnungsempfaenger02 ON [Akt].[aktID]=[qry_AktRechnungsempfaenger02].[aktID]
WHERE
((([qry_AktRechnungsempfaenger02].[AR_ID]) Like @Forms_F_Auswahl_Rechnung01_Kom) And (([Akt].[aktStatus])=0) And (([CAkt21].[chk1])='0') And
(([CAkt21].[cmb1]) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN '%' ELSE '0' END) And (([CAkt21].[opt1])=1) And (([Project].[Active])=1) And (([Akt].[aktType])=21)) ORDER BY [Project].[PName])

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_ProjSammelrechnung11 (
@Forms_F_Auswahl_Rechnung01_Kom varchar (255),
@Forms_F_Auswahl_Rechnung01_Opt varchar (255)
)
RETURNS TABLE
AS RETURN (
SELECT DISTINCT top 100 percent
Project.PID, Project.PName, Address.ANR, Contact.CID,
CASE WHEN address.anr IS NULL THEN CAST(contact.cid as Varchar(15)) ELSE address.anr END AS KUNDE_ID,
--IIf(IsNull(address.anr),contact.cid,address.anr) AS KUNDE_ID,
Akt.aktStatus, Akt.aktType, CAkt21.chk1, CAkt21.cmb1, CAkt21.opt1, Project.Active, CProject.chk3
FROM
(((((((Akt LEFT JOIN AktAdr ON Akt.aktID=AktAdr.AktID) LEFT JOIN Project ON Akt.aktProject=Project.PID)
LEFT JOIN CProject ON Project.PID=CProject.ID) LEFT JOIN Address AS Address_1 ON AktAdr.AktAdr=Address_1.ANR)
LEFT JOIN Contact AS Contact_1 ON Address_1.ANR=Contact_1.CANR) LEFT JOIN Address ON CProject.src1=Address.ANR)
INNER JOIN CAkt21 ON Akt.aktID=CAkt21.ID) LEFT JOIN Contact ON CProject.src2=Contact.CID
WHERE
(((CASE WHEN address.anr IS NULL THEN CAST(contact.cid as Varchar(15)) ELSE address.anr END) Like @Forms_F_Auswahl_Rechnung01_Kom) And ((Akt.aktStatus)=0) And ((Akt.aktType)=21) And ((CAkt21.chk1)='0') And
((CAkt21.cmb1) Like CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN '%' ELSE '0' END) And ((CAkt21.opt1)=0) And ((Project.Active)=1) And ((CProject.chk3)='0'))
ORDER BY Project.PName)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_Rechnung11 (
@Forms_F_Auswahl_Rechnung01_Opt varchar (255),
@Forms_F_Auswahl_Rechnung01_EndPrj varchar (255),
@Forms_F_Auswahl_Rechnung01_EndAkt varchar (255)
)
RETURNS TABLE
AS RETURN (
SELECt distinct
-- TOP 100 PERCENT
[qry_Rechnung01].[PID], [qry_Rechnung01].[PName], [qry_Rechnung01].[Active], [qry_Rechnung01].[PR_ID], [qry_Rechnung01].[PR_NAME1], [qry_Rechnung01].[PR_NAME2],
[qry_Rechnung01].[PR_POBOX], [qry_Rechnung01].[PR_PBCODE], [qry_Rechnung01].[PR_STREET], [qry_Rechnung01].[PR_COUNTRY], [qry_Rechnung01].[PR_POSTCODE],
[qry_Rechnung01].[PR_CITY], [qry_Rechnung01].[PV_ID], [qry_Rechnung01].[PV_NAME1], [qry_Rechnung01].[PV_NAME2], [qry_Rechnung01].[PV_POBOX], [qry_Rechnung01].[PV_PBCODE],
[qry_Rechnung01].[PV_STREET], [qry_Rechnung01].[PV_COUNTRY], [qry_Rechnung01].[PV_POSTCODE], [qry_Rechnung01].[PV_CITY], [qry_Rechnung01].[AktID],
CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt=0 THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END AS AKT,
--IIf(@Forms_F_Auswahl_Rechnung01_Opt=0,[qry_Rechnung01]![AktID],@Forms_F_Auswahl_Rechnung01_EndAkt) AS AKT,
CASE WHEN [cakt21].[cmb1]=0 THEN 'definitiv'
WHEN [cakt21].[cmb1]=1 THEN 'provisorisch'
WHEN [cakt21].[cmb1]=2 THEN '1. Option'
WHEN [cakt21].[cmb1]=3 THEN '2. Option'
ELSE 'definitiv' END AS Status,
--IIf([cakt21].[cmb1]=0,"definitiv",IIf([cakt21].[cmb1]=1,"provisorisch",IIf([cakt21].[cmb1]=2,"1. Option",IIf([cakt21].[cmb1]=3,"2. Option","definitiv")))) AS Status,
[qry_Rechnung01].[aktSComment], [qry_Rechnung01].[duration], [qry_Rechnung01].[aktStatus],
[qry_Rechnung01].[aktType], [qry_Rechnung01].[AR_ID], [qry_Rechnung01].[AR_NAME1], [qry_Rechnung01].[AR_NAME2],
[qry_Rechnung01].[AR_POBOX], [qry_Rechnung01].[AR_PBCODE], [qry_Rechnung01].[AR_STREET], [qry_Rechnung01].[AR_POSTCODE],
[qry_Rechnung01].[AR_CITY], [qry_Rechnung01].[AR_COUNTRY], [qry_AktVeranstalter01].[AV_ID], [qry_AktVeranstalter01].[AV_NAME1],
[qry_AktVeranstalter01].[AV_NAME2], [qry_AktVeranstalter01].[AV_POBOX], [qry_AktVeranstalter01].[AV_PBCODE],
[qry_AktVeranstalter01].[AV_STREET], [qry_AktVeranstalter01].[AV_COUNTRY], [qry_AktVeranstalter01].[AV_POSTCODE],
[qry_AktVeranstalter01].[AV_CITY], [qry_Rechnung01].[RID], [qry_Rechnung01].[RName], [CAkt21].[opt1], [CAkt21].[src1],
[CAkt21].[chk1], [qry_Rechnung01].[RType], [qry_Rechnung01].[ResSComment], [qry_Rechnung01].[ResStartDate],
[qry_Rechnung01].[ResStartTime], [qry_Rechnung01].[ResEndDate], [qry_Rechnung01].[ResEndTime], [qry_Rechnung01].[START_PREIS],
[qry_Rechnung01].[END_PREIS], [qry_Rechnung01].[PREIS], [qry_Rechnung01].[START_MWST], [qry_Rechnung01].[END_MWST],
[qry_Rechnung01].[MWST_SATZ], [qry_Rechnung01].[MWST_OPT], [PREIS]*[MWST_SATZ] AS MWST_BETRAG,
[CAkt21].[num3] AS FAKTOR_SAAL,
CASE WHEN [rtype]!=3 THEN
CASE WHEN [cakt21].[chk6]='1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_SATZ,
--IIf(Not [rtype]=3,IIf([cakt21].[chk6]='1',0,[mwst_satz]),[mwst_satz]) AS S_MWST_SATZ,
CASE WHEN IsNumeric([aktrecurr].[duration])=1 THEN [aktrecurr].[duration] ELSE 1 END AS FAKTOR_GERAET,
--IIf(IsNumeric([aktrecurr].[duration]),[aktrecurr].[duration],1) AS FAKTOR_GERAET,
CASE WHEN [rtype]!=3 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis] * [CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric([aktrecurr].[duration])=1 THEN [aktrecurr].[duration] ELSE 1 END END AS S_PREIS,
--IIf((Not [Rtype]=3),IIf([cakt21].[chk6]='1',0,[preis]*[faktor_saal]),[preis]*[faktor_geraet]) AS S_PREIS,
[KCMWST].[num1] AS KCNUM1, [KCMWST].[dat1] AS KCDAT1, [KCMWST].[dat2] AS KCDAT2, [KCMWST].[opt1] AS KCOPT1,
-- [S_PREIS]*[S_MWST_SATZ] AS S_MWST_BETRAG,
CASE WHEN [rtype]!=3 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [preis] * [CAkt21].[num3] END
ELSE preis * CASE WHEN IsNumeric([aktrecurr].[duration])=1 THEN [aktrecurr].[duration] ELSE 1 END END *
CASE WHEN [rtype]!=3 THEN
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN 0 ELSE [mwst_satz] END
ELSE [mwst_satz] END AS S_MWST_BETRAG,
[CAkt21].[chk6], [CAkt21].[num1], [CAkt21].[txt1],[CAkt21].[tim1],
CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END AS I_MWST_SATZ,
--IIf(IsNumeric([kcmwst].[num1]),[kcmwst].[num1],0) AS I_MWST_SATZ,
CASE WHEN [chk6]='1' OR [chk6]='-1' THEN [cakt21].[num1] * CASE WHEN IsNumeric([kcmwst].[num1])=1 THEN [kcmwst].[num1] ELSE 0 END ELSE 0 END AS I_MWST_BETRAG,
--IIf([chk6]='1',[cakt21].[num1]*[I_MWST_SATZ],0) AS I_MWST_BETRAG,
CASE WHEN [cakt21].[chk6]='1' OR [cakt21].[chk6]='-1' THEN [cakt21].[num1] ELSE 0 END AS I_PREIS,
--IIf([cakt21].[chk6]='1',[cakt21].[num1],0) AS I_PREIS,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num4] ELSE 0 END AS GAR_PRODUKT1,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num4],0) AS GAR_PRODUKT1,
CASE WHEN [CAkt21].[cmb2]=1 THEN [Cakt21].[num2] ELSE 0 END AS GAR_PRODUKT2,
--IIf([CAkt21].[cmb2]=1,[Cakt21].[num2],0) AS GAR_PRODUKT2,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
WHEN [cakt21].[cmb2]=4 THEN 0
ELSE [cakt21].[num2] END AS GAR_BETRAG,
--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,[Cakt21].[num4]*[Cakt21].[num2],[cakt21].[num2]))) AS GAR_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/(kcmwst_1.num1+1) END AS GAR_BETRAG0,

--IIf([cakt21].[cmb2]=4,0,IIf([Cakt21].[cmb2]=0,0,IIf([CAkt21].[cmb2]=1,([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1),[cakt21].[num2]/(kcmwst_1.num1+1)))) AS GAR_BETRAG0,
-- CASE WHEN [GAR_BETRAG0]=0 THEN 0
-- ELSE CASE WHEN IsNumeric([GAR_BETRAG0])=1 THEN kcmwst_1.num1 ELSE 0 END
-- END AS GAR_MWST_SATZ,
CASE WHEN ISNUMERIC(CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/(kcmwst_1.num1+1) END)=1 THEN kcmwst_1.num1 ELSE 0 END AS GAR_MWST_SATZ,
--IIf([GAR_BETRAG0]=0,0,IIf(IsNumeric([GAR_BETRAG0]),kcmwst_1.num1,0)) AS GAR_MWST_SATZ,
KCMWST_1.dat1, KCMWST_1.dat2,
-- [GAR_BETRAG]-[GAR_BETRAG0] AS GAR_MWST_BETRAG,
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN [Cakt21].[num4]*[Cakt21].[num2]
WHEN [cakt21].[cmb2]=4 THEN 0
ELSE [cakt21].[num2] END -
CASE WHEN [cakt21].[cmb2]=4 THEN 0
WHEN [cakt21].[cmb2]=0 THEN 0
WHEN [cakt21].[cmb2]=1 THEN ([Cakt21].[num4]*[Cakt21].[num2])/(kcmwst_1.num1+1)
ELSE [cakt21].[num2]/(kcmwst_1.num1+1) END AS GAR_MWST_BETRAG,
CASE WHEN [qry_rechnung01].[akttype]=21 THEN
CASE WHEN [Cakt21].[cmb2]=0 THEN 'Direktinkasso'
WHEN [Cakt21].[cmb2]=1 THEN 'pro Eintritt'
WHEN [Cakt21].[cmb2]=2 THEN 'nach Aufwand'
WHEN [Cakt21].[cmb2]=3 THEN 'Mehraufwand'
WHEN [Cakt21].[cmb2]=4 THEN 'unbewacht'
WHEN [Cakt21].[cmb2]=5 THEN 'bei schlechtem Wetter bewacht nach Aufwan'
ELSE 'nach Vereinbarung' END
ELSE '' END AS GAR_TEXT,
--IIf([qry_rechnung01].[akttype]=21,IIf([Cakt21].[cmb2]=0,"Direktinkasso",IIf([CAkt21].[cmb2]=1,"pro Eintritt",IIf([CAkt21].[cmb2]=2,"nach Aufwand",IIf([CAkt21].[cmb2]=3,"Mehraufwand","unbewacht")))),"") AS GAR_TEXT,
[qry_Rechnung01].[txt1] AS QRYTXT1, [qry_Rechnung01].[txt2] AS QRYTXT2, [qry_Rechnung01].[txt3] AS QRYTXT3, [qry_Rechnung01].[chk3] AS QRYCHK3,
[qry_Rechnung01].[chk1] AS QRYCHK1, [qry_Rechnung01].[num1] AS QRYNUM1,
[qry_Rechnung01].[dat1] AS QRYDAT1,
[qry_Rechnung01].[dat2] AS QRYDAT2,
CASE WHEN [qry_Rechnung01].[chk1]='1' OR [qry_Rechnung01].[chk1]='-1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat2])=1 THEN [qry_Rechnung01].[num1] ELSE 0 END
ELSE 0 END AS AKONTO,
--IIf([qry_Rechnung01].[chk1]='1',IIf(IsDate([qry_Rechnung01].[dat2]),[qry_Rechnung01].[num1],0),0) AS AKONTO, [qry_Rechnung01].[chk2], [qry_Rechnung01].[num2], [qry_Rechnung01].[dat3],
CASE WHEN [qry_Rechnung01].[chk2]='1' THEN
CASE WHEN IsDate([qry_Rechnung01].[dat3])=1 THEN [qry_Rechnung01].[num2] ELSE 0 END
ELSE 0 END AS BEITRAG, [qry_Rechnung01].[dat3] AS QRYDAT3,
--IIf([qry_Rechnung01].[chk2]='1',IIf(IsDate([qry_Rechnung01].[dat3]),[qry_Rechnung01].[num2],0),0) AS BEITRAG,
-- for additional data ( 10 fields)
isnull(CAkt21.chk3,0) as chk3, isnull(CAkt21.chk4,0) as chk4, isnull(CAkt21.chk5,0) as chk5, isnull(CAkt21.chk7,0) as chk7, isnull(CAkt21.chk8,0) as chk8, isnull(CAkt21.chk9,0) as chk9, isnull(CAkt21.chk10,0) as chk10, isnull(CAkt21.chk11,0) as chk11,
isnull(CAkt21.chk12,0) as chk12, isnull(CAkt21.chk13,0) as chk13, isnull(CAkt21.num5,0) as num5, isnull(CAkt21.num6,0) as num6, isnull(CAkt21.num7,0) as num7, isnull(CAkt21.num8,0) as num8, isnull(CAkt21.num9,0) as num9,
isnull(CAkt21.num10,0) as num10, isnull(CAkt21.num11,0) as num11, isnull(CAkt21.num12,0) as num12, isnull(CAkt21.num13,0) as num13, isnull(CAkt21.num14,0) as num14, isnull(CAkt21.num15,0) as num15, isnull(CAkt21.num16,0) as num16,
isnull(CAkt21.num17,0) as num17, isnull(CAkt21.num18,0) as num18, isnull(CAkt21.num19,0) as num19, isnull(CAkt21.num20,0) as num20, isnull(CAkt21.num21,0) as num21, isnull(CAkt21.num22,0) as num22, isnull(CAkt21.num23,0) as num23,
isnull(CAkt21.num24,0) as num24, CAkt21.src2, CAkt21.src3, CAkt21.src4, CAkt21.src5, CAkt21.src6, CAkt21.src7, CAkt21.src8,
CAkt21.src9, CAkt21.src10, CAkt21.src11, CAkt21.txt2, CAkt21.txt3, CAkt21.txt4, CAkt21.txt5, CAkt21.txt6,
CAkt21.txt7, CAkt21.txt8, CAkt21.txt9, CAkt21.txt10, CAkt21.txt11,
[CAkt21].[num2], [CAkt21].[cmb1]
FROM
((((((CAkt21 RIGHT JOIN qry_Rechnung01 ON [CAkt21].[ID]=[qry_Rechnung01].[AktID])
LEFT JOIN KCMWST ON [CAkt21].[opt2]=[KCMWST].[opt1])
LEFT JOIN C2Akt21 ON [qry_Rechnung01].[AktID]=[C2Akt21].[ID])
LEFT JOIN AktRecurr ON [AktRecurr].[AktID]=[C2Akt21].[ID]) -- MODIFIED BY KK
LEFT JOIN tbl_hlp_0 ON [CAkt21].[chk1]=[tbl_hlp_0].[chk1])
LEFT JOIN KCMWST AS KCMWST_1 ON [tbl_hlp_0].[opt1]=KCMWST_1.opt1)
LEFT JOIN qry_AktVeranstalter01 ON [C2Akt21].[ID]=[qry_AktVeranstalter01].[ID]
WHERE
((([qry_Rechnung01].[PID])=@Forms_F_Auswahl_Rechnung01_EndPrj) And (([qry_Rechnung01].[Active])=1) And
(([qry_Rechnung01].[aktStatus])=0) And (([qry_Rechnung01].[aktType])=21) And
(([CAkt21].[opt1])=@Forms_F_Auswahl_Rechnung01_Opt Or ([CAkt21].[opt1]) Is Null) And
(([CAkt21].[chk1])='0' Or ([CAkt21].[chk1]) Is Null) And
(([KCMWST].[dat1])<=[qry_Rechnung01].[resstartdate] Or ([KCMWST].[dat1]) Is Null) And
(([KCMWST].[dat2])>=[qry_Rechnung01].[resenddate] Or ([KCMWST].[dat2]) Is Null) And
((KCMWST_1.dat1)<=[qry_Rechnung01].[resstartdate] Or (KCMWST_1.dat1) Is Null) And
((KCMWST_1.dat2)>=[qry_Rechnung01].[resenddate] Or (KCMWST_1.dat2) Is Null) And
(([qry_Rechnung01].[chk3])='0') And (([CAkt21].[cmb1])=0 Or ([CAkt21].[cmb1]) Is Null) And
(([qry_Rechnung01].[src3])=[cakt21].[src1] Or ([qry_Rechnung01].[src3]) Is Null) And
((CASE WHEN @Forms_F_Auswahl_Rechnung01_Opt='0' THEN [qry_Rechnung01].[AktID] ELSE @Forms_F_Auswahl_Rechnung01_EndAkt END)=[qry_Rechnung01].[AktID])
--((IIf(@Forms_F_Auswahl_Rechnung01_Opt=0,[qry_Rechnung01]![AktID],@Forms_F_Auswahl_Rechnung01_EndAkt))=[qry_rechnung01.aktid]))
))
-- ORDER BY [qry_Rechnung01].[RType])

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Funktion qry_Uebersicht01 (
@Forms_F_Auswahl_Uebersicht01_datVon varchar (255),
@Forms_F_Auswahl_Uebersicht01_datBis varchar (255)
)
RETURNS TABLE
AS RETURN (
SELECT DISTINCT TOP 100 PERCENT
[Ress].[RID], [Ress].[RName], [CAkt21].[src1], [RessReserv].[ResStartDate], [RessReserv].[ResStartTime],
[RessReserv].[ResEndDate], [RessReserv].[ResEndTime], [RessReserv].[ResWholeDay], [Akt].[aktID], [Akt].[aktSComment],
[AktUserZord].[UserName], [Akt].[aktName], [Akt].[aktStatus], [Akt].[aktType], [qry_ProjVeranstalter01].[PV_NAME1],
[qry_ProjVeranstalter01].[PV_NAME2], [qry_ProjVeranstalter01].[PV_PHONE1], [qry_ProjVeranstalter01].[PV_PHONE2],
[qry_AktVeranstalter01].[AV_NAME1], [qry_AktVeranstalter01].[AV_NAME2], [qry_AktVeranstalter01].[AV_PHONE1], [qry_AktVeranstalter01].[AV_PHONE2],
CASE WHEN [Cakt21].[cmb2]=0 THEN 'Direktinkasso'
WHEN [Cakt21].[cmb2]=1 THEN 'pro Eintritt'
WHEN [Cakt21].[cmb2]=2 THEN 'nach Aufwand'
WHEN [Cakt21].[cmb2]=3 THEN 'Mehraufwand'
WHEN [Cakt21].[cmb2]=4 THEN 'unbewacht'
WHEN [Cakt21].[cmb2]=5 THEN 'bei schlechtem Wetter bewacht nach Aufwan'
ELSE 'nach Vereinbarung' END AS GAR,
--IIf([cakt21].[cmb2]=0,"Direktinkasso",IIf([cakt21].[cmb2]=1,"pro Eintritt",IIf([cakt21].[cmb2]=2,"nach Aufwand",IIf([cakt21].[cmb2]=3,"Mehraufwand",IIf([cakt21].[cmb2]=5,"bei schlechtem Wetter bewacht nach Aufwand","unbewacht"))))) AS GAR,
CASE WHEN [cakt21].[cmb1]=0 THEN 'definitiv'
WHEN [cakt21].[cmb1]=1 THEN 'provisorisch'
WHEN [cakt21].[cmb1]=2 THEN '1. Option'
ELSE '2. Option' END AS Status,
--IIf([cakt21].[cmb1]=0,"definitiv",IIf([cakt21].[cmb1]=1,"provisorisch",IIf([cakt21].[cmb1]=2,"1. Option","2. Option"))) AS Status,
[CAkt21].[num4], [Project].[PID], [Project].[PName], [CProject].[txt3]
FROM
qry_AktVeranstalter01 RIGHT JOIN ((((((((Ress INNER JOIN RessReserv ON [Ress].[RID]=[RessReserv].[RID])
INNER JOIN Akt ON [RessReserv].[AktID]=[Akt].[aktID])
LEFT JOIN Project ON [Akt].[aktProject]=[Project].[PID])
LEFT JOIN CProject ON [Project].[PID]=[CProject].[ID])
INNER JOIN CAkt21 ON [Akt].[aktID]=[CAkt21].[ID])
LEFT JOIN C2Akt21 ON [Akt].[aktID]=[C2Akt21].[ID])
LEFT JOIN AktUserZord ON [Akt].[aktID]=[AktUserZord].[AKTID])
LEFT JOIN qry_ProjVeranstalter01 ON [CProject].[ID]=[qry_ProjVeranstalter01].[ID]) ON [qry_AktVeranstalter01].[ID]=[C2Akt21].[ID]
WHERE
((([RessReserv].[ResStartDate])<=@Forms_F_Auswahl_Uebersicht01_datBis) And
(([RessReserv].[ResEndDate])>=@Forms_F_Auswahl_Uebersicht01_datVon) And
(([Akt].[aktStatus])=0))
ORDER BY [Ress].[RName], [RessReserv].[ResStartDate], [RessReserv].[ResStartTime])

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AktTypeChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[AktTypeChange]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE trigger AktTypeChange on dbo.AKT
FOR UPDATE
AS

DECLARE
@aktid int,
@aktType int,
@ID int,
@aktName varchar(20),
@cakt27id int

SELECT @aktid=aktid,@aktType=AktType FROM inserted

IF UPDATE(aktType)
BEGIN
if @akttype=21
begin
SELECT @cakt27id=id from Cakt27 where ID=@aktid
if @@rowcount>0
begin
Insert into Cakt21(ID,chk1,chk2,chk3,num1,num2,num3,num4,num5,num6,num7,num8,
num9,num10,num11,num12,num13,num14,num15,num16,num17,num18,num19,num20,num21,num22,num23,num24,
opt1,opt2,src1,src2,src3,src4,src5,src6,tim1,txt1,txt2,txt3,txt4,txt5,txt6,txt7,txt8,
txt9,InsertRepId,UpdateRepId,chk4,chk5,src7,chk6,chk7,chk8,chk9,chk10,chk11,chk12,chk13,
cmb1,cmb2,cmb3,src8,src9,src10,src11,txt10,txt11)
Select ID,chk1,chk2,chk3,num1,num2,num3,num4,num5,num6,num7,num8,
num9,num10,num11,num12,num13,num14,num15,num16,num17,num18,num19,num20,num21,num22,num23,num24,
opt1,opt2,src1,src2,src3,src4,src5,src6,tim1,txt1,txt2,txt3,txt4,txt5,txt6,txt7,txt8,
txt9,InsertRepId,UpdateRepId,chk4,chk5,src7,chk6,chk7,chk8,chk9,chk10,chk11,chk12,chk13,
cmb1,cmb2,cmb3,src8,src9,src10,src11,txt10,txt11 from Cakt27 where ID=@cakt27id
end
end
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

=========================

Thanks & Regards
Malini.

quote:
Originally posted by mohdowais

The language settings of the operating system shouldn't really make a difference to the script. I've seen some pretty wierd bugs in generated SQL Scripts with non-visual characters, though. It is possible you might have a character somewhere that is interpreted differently on a German codepage. Could you post the script that is having problems?

Owais

Where there's a will, I want to be in it.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-11 : 03:52:00

Ouch...be careful what you wish for.
You still haven't mentioned which line gives you the error, and probably a better description of the error.

Owais

Where there's a will, I want to be in it.

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-11 : 04:10:20

Hi Owais,
I have already mentioned that the error occured as "false syntax near 'function' keyword' for every function which i have written script. If suppose there are 16 functions, then the error occurs for 16 times.

Thanks & Regards
Malini

quote:
Originally posted by mohdowais

Ouch...be careful what you wish for.
You still haven't mentioned which line gives you the error, and probably a better description of the error.

Owais

Where there's a will, I want to be in it.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-11 : 04:20:21

Ok, I can see the problem now, but I can't imagine how it got there:


drop Funktion [dbo].[qry_AktEinzelrechnung11] ...
CREATE Funktion qry_Mietvertrag12 ...

How did a CREATE Function become a CREATE Funktion?

Do this: open this script file in notepad, hit Ctrl+H (Find and replace), and replace all occurances of Funktion with Function. That should fix this problem. But I can't really understand how this would work in ANY locale, german or otherwise. Funktion is not a recognized keyword in SQL Server.

Owais

Where there's a will, I want to be in it.

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-11 : 04:34:58

Hi Owais,
Oh ! i am sorry , actually i have given as 'Function' for the first time. But it does not work in our client's place. So i thought if i change into German language it may work so i have changed this as 'Funktion' (in German language). But even it is giving error.
So for both it is giving same error. Still i have problem after changing 'funktion' to 'function'

I will be greatful if this problem is solved.

Thanks & Regards
Malini.

quote:
Originally posted by mohdowais

Ok, I can see the problem now, but I can't imagine how it got there:

drop Funktion [dbo].[qry_AktEinzelrechnung11] ...
CREATE Funktion qry_Mietvertrag12 ...

How did a CREATE Function become a CREATE Funktion?

Do this: open this script file in notepad, hit Ctrl+H (Find and replace), and replace all occurances of Funktion with Function. That should fix this problem. But I can't really understand how this would work in ANY locale, german or otherwise. Funktion is not a recognized keyword in SQL Server.

Owais

Where there's a will, I want to be in it.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-11 : 06:04:00

Is the database in v7 compatability mode? This won't allow functions.
It shouldn't give that error but......

What happens if they run a create function statement on it's own - maybe it's nothing to do with the functions but something that's happened earlier.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 12:20:13

Yes this sounds like a compatibility mode problem. To verify what compatibility mode you are in, right click on your db in Enterprise Manager and go to properties. Then go to Options tab. The information is at the bottom. Let us know what you find. It needs to say 80 in order to create functions in that database.

Tara

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-11 : 22:23:29

Hi Tara,
Good morning. Thanks for your reply. But as i said my client in German is using MSDE 2000 sql server version which does not have User Interface. He cannot see Enterprise Manager screen. What ever is to be done must be done through command prompt. So how i should tell him to check compatiable version in Enterprise manager.
That is the main problem.
Please give me suggession for MSDE 2000 version.

Thanks & Regards
Malini.

quote:
Originally posted by tduggan

Yes this sounds like a compatibility mode problem. To verify what compatibility mode you are in, right click on your db in Enterprise Manager and go to properties. Then go to Options tab. The information is at the bottom. Let us know what you find. It needs to say 80 in order to create functions in that database.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-11 : 23:59:23

run "sp_helpdb" in Query Analyzer or osql or whatever. Look at the last column "compatibility_level", if it is 65 or 70, you're in trouble, if it's 80, we're in trouble.

Owais

Where there's a will, I want to be in it.

Maliniksh
Starting Member

8 Posts

Posted - 2003-11-12 : 23:55:32

Hi Owais,
Thank you for good suggession. But still the problem exists.
I have done the following steps :
I told my german client to execute the following command at dos prompt.

C:\>osql /U sa /P /S servername /i KCB_SQLFunction.sql

The following code i added to script file KCB_SQLFunction.sql script file : (script file starts with the following code and ends with creating functions )

USE LatestCons2k
go
EXEC sp_dbcmptlevel 'LatestCons2k', 80
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_AktEinzelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Function [dbo].[qry_AktEinzelrechnung11]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qry_KundeEinzelrechnung11]') and xtype in (N'FN', N'IF', N'TF'))
drop Function [dbo].[qry_KundeEinzelrechnung11]
GO
.
.
.
.

When my client exectues the above command it gave the following error :
1> 2> 1> 2> Msg 15416, Level 16, State 1, Server DCNOTE, Procedure
sp_dbcmptlevel, LIne 90
syntax : sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values for the data base compatibility degree are 60.65 or 70
error at 'Function'

Can you hepl me find out the above error.

Thanks & Regards
Malini

===================================

quote:
Originally posted by mohdowais

run "sp_helpdb" in Query Analyzer or osql or whatever. Look at the last column "compatibility_level", if it is 65 or 70, you're in trouble, if it's 80, we're in trouble.

Owais

Where there's a will, I want to be in it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 12:12:28

Well according to your error, your client is not using SQL Server 2000. They are using SQL Server 7.0. They'll need to upgrade to 2000 in order to be able to use functions. They can set their compatibility mode to 70 in 2000 if they think that their app isn't compatibility with 2000.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources