/*
   2010. augusztus 17.10:34:11
   User: sa
   Server: 192.168.1.85,1137
   Database: aruker
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.szall_szlaf ADD
	BRUTTO_ERTEK dbo.egyseg_ar NULL
GO
COMMIT
go



update szall_szlaf set brutto_ertek=
round((select isnull(SUM(ertek*(100+afaertek)/100),0)
	from vszall_szlat where szall_szlaf.bizszam=vszall_szlat.bizszam),
		CASE penznem WHEN 'HUF' THEN 0 WHEN 'FT' THEN 0 ELSE 2 END)

go




/*
   2010. augusztus 17.10:35:57
   User: sa
   Server: 192.168.1.85,1137
   Database: aruker
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.szall_szlaf
	DROP CONSTRAINT FK_szall_szlaf_szallito
GO
COMMIT
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.szall_szlaf
	DROP CONSTRAINT FK_szall_szlaf_deviza
GO
COMMIT
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.szall_szlaf
	DROP CONSTRAINT FK_szall_szlaf_belepes
GO
COMMIT
go
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_szall_szlaf
	(
	BIZSZAM dbo.bizonylat_szam NOT NULL,
	KULSOSZLASZ varchar(50) NOT NULL,
	SZALLITO dbo.kulcs NOT NULL,
	SZALL_NEV dbo.nev NULL,
	TELJ_DATUM dbo.datum NOT NULL,
	SZLA_DATUM dbo.datum NOT NULL,
	FIZ_MODJA char(1) NOT NULL,
	FIZ_ESED dbo.datum NOT NULL,
	penznem dbo.penznem NOT NULL,
	kurzus dbo.kurzus NOT NULL,
	kedvezmeny dbo.kedvezmeny NOT NULL,
	afamentes bit NOT NULL,
	MEGJEGYZES dbo.megjegyzes NOT NULL,
	KICSODA dbo.kicsoda NOT NULL,
	MODOSITVA dbo.datum_ora NOT NULL,
	TOROLT char(1) NOT NULL,
	TORLO char(1) NOT NULL,
	TOR_SSZ dbo.bizonylat_szam NOT NULL,
	VISSZARU char(1) NOT NULL,
	BRUTTO_ERTEK dbo.egyseg_ar NOT NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.szall_szlaf)
	 EXEC('INSERT INTO dbo.Tmp_szall_szlaf (BIZSZAM, KULSOSZLASZ, SZALLITO, SZALL_NEV, TELJ_DATUM, SZLA_DATUM, FIZ_MODJA, FIZ_ESED, penznem, kurzus, kedvezmeny, afamentes, MEGJEGYZES, KICSODA, MODOSITVA, TOROLT, TORLO, TOR_SSZ, VISSZARU, BRUTTO_ERTEK)
		SELECT BIZSZAM, KULSOSZLASZ, SZALLITO, SZALL_NEV, TELJ_DATUM, SZLA_DATUM, FIZ_MODJA, FIZ_ESED, penznem, kurzus, kedvezmeny, afamentes, MEGJEGYZES, KICSODA, MODOSITVA, TOROLT, TORLO, TOR_SSZ, VISSZARU, BRUTTO_ERTEK FROM dbo.szall_szlaf WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.szall_szlat
	DROP CONSTRAINT FK_szall_szlat_szall_szlaf
GO
DROP TABLE dbo.szall_szlaf
GO
EXECUTE sp_rename N'dbo.Tmp_szall_szlaf', N'szall_szlaf', 'OBJECT' 
GO
ALTER TABLE dbo.szall_szlaf ADD CONSTRAINT
	PK_szall_szlaf PRIMARY KEY CLUSTERED 
	(
	BIZSZAM
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_szall_szlaf ON dbo.szall_szlaf
	(
	KULSOSZLASZ
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_szall_szlaf_1 ON dbo.szall_szlaf
	(
	SZALLITO
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.szall_szlaf ADD CONSTRAINT
	FK_szall_szlaf_belepes FOREIGN KEY
	(
	KICSODA
	) REFERENCES dbo.belepes
	(
	ROVIDNEV
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.szall_szlaf ADD CONSTRAINT
	FK_szall_szlaf_deviza FOREIGN KEY
	(
	penznem
	) REFERENCES dbo.deviza
	(
	penznem
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.szall_szlaf ADD CONSTRAINT
	FK_szall_szlaf_szallito FOREIGN KEY
	(
	SZALLITO
	) REFERENCES dbo.szallito
	(
	SZALL_KOD
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
go
BEGIN TRANSACTION
GO
ALTER TABLE dbo.szall_szlat ADD CONSTRAINT
	FK_szall_szlat_szall_szlaf FOREIGN KEY
	(
	BIZSZAM
	) REFERENCES dbo.szall_szlaf
	(
	BIZSZAM
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT