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 : 03:46:24
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.
The following is the script file content:
=========================
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.
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site