create.sql

聊天 聊天 聊天 聊天 聊天 聊天

IF isnull(@doc_no,0) = 0
	--INSERT--
	BEGIN
		--SafeGuard : Check if tranaction with same ref# exists. If so dont insert
		select @ret = count(*) from tr_header where ref = @ref
		if @ret > 0 
		BEGIN
		--raiserror (53000, 1,16)
		GOTO doerror
		END			

		Select @ll_doc =   isnull(max(doc_no),0)+1 from tr_header
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
	END

ELSE
	--UPDATE__
	BEGIN
		SELECT @ll_doc = @doc_no
		
		delete from transactions where doc_no = @doc_no
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
		delete from tr_header where doc_no = @doc_no
		IF @@ERROR != 0
		begin 
	 	GOTO doerror
		END
		


	END



BEGIN
		
	
	INSERT INTO tr_header ( id, date,ref,   doc_no ,narr) VALUES
	(@id, isnull(@date,getdate()),@ref,   @ll_doc, @narr)
		IF @@ERROR != 0
		begin 
		  GOTO doerror
		END

	INSERT INTO transactions ( doc_no, dr_amount, cr_amount, 
	code_value, sr_no,posted_to ) 
	VALUES 		
	( @ll_doc, isnull(@dr_amount,0), ISNULL(@cr_amount,0),
	 @code_value, 1 ,@posted_to)	
		IF @@ERROR != 0
		begin 
		  GOTO doerror
		END		

	INSERT INTO transactions ( doc_no, dr_amount, cr_amount, 
	code_value, sr_no, posted_to ) 
	VALUES 		
	( @ll_doc, ISNULL(@cr_amount,0),ISNULL(@dr_amount,0), 
	@posted_to, 2 ,@code_value)	
 	  
		IF @@ERROR != 0
		begin 
		  GOTO doerror
		END

END

COMMIT TRANSACTION	
SELECT 0
GOTO doreturn
doerror:

Rollback TRANSACTION
doreturn:
RETURN 0
SELECT -100 
go

/**********************************************************************************************
Triggers
***********************************************************************************************/

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


CREATE TRIGGER delete_mstr ON transactions for delete as
Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @mmonth char(3)
DECLARE @amount money
DECLARE @mstr_amount money

SELECT  * 
into #temp
from deleted

UPDATE Masters
SET Closing = isnull(Closing,0)-
(ISNULL(t.dr_amount,0)-ISNULL(t.cr_amount,0))
FROM masters m, #temp t
WHERE  m.code_value = t.code_value 
AND m.type in("A","E") 

UPDATE Masters
SET Closing = isnull(Closing,0)-
(ISNULL(t.cr_amount,0) - ISNULL(t.dr_amount,0))
FROM masters m, #temp t
WHERE  m.code_value = t.code_value 
AND m.type in("I","L") 

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



CREATE TRIGGER insert_mstr ON transactions for insert as


Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @amount money



SELECT @mtype = masters.type
FROM masters, inserted
WHERE ( masters.code_value = inserted.code_value    ) 

SELECT  * 
into #temp
from inserted

If @mtype = 'A' or @mtype = 'E'  
	BEGIN
		SELECT @amount = ISNULL(#temp.dr_amount,0) - ISNULL(#temp.cr_amount,0)
		FROM #temp		

	END
ELSE	
	BEGIN
		SELECT @amount =  ISNULL(#temp.cr_amount,0) - ISNULL(#temp.dr_amount,0)
		FROM #temp
	END

UPDATE MASTERS
SET closing = closing + @amount
FROM masters, #temp  WHERE ( masters.code_value = #temp.code_value    )

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



CREATE TRIGGER update_mstr ON transactions for update as


Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @amount money


SELECT @mtype = masters.type
FROM masters, inserted
WHERE ( masters.code_value = inserted.code_value    ) 

SELECT  * 
into #temp
from inserted

SELECT  * 
into #t2
from deleted

If @mtype = 'A' or @mtype = 'E'  
	BEGIN
		SELECT @amount = ISNULL(#temp.dr_amount,0) - ISNULL(#temp.cr_amount,0)
				 - ISNULL(#t2.dr_amount,0) + isnull(#t2.cr_amount ,0)
		FROM #temp, #t2
		WHERE #temp.code_value = #t2.code_value		

	END
ELSE	
	BEGIN
		SELECT @amount =  ISNULL(#temp.cr_amount,0) - ISNULL(#temp.dr_amount,0)
				  - ISNULL(#t2.cr_amount,0) + isnull(#t2.dr_amount ,0) 
		FROM #temp, #t2
		WHERE #temp.code_value = #t2.code_value		
	END

UPDATE Masters
SET Closing = Closing + @amount
FROM masters, #temp  WHERE ( masters.code_value = #temp.code_value    )

go

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
		INVENTORY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
CREATE TABLE stock_master
               ( code_value integer identity NOT NULL,
              code_display Varchar(30) NOT NULL,
              rate money  NULL,
              uom Varchar(10) NULL,
              closing money default 0  NULL,
              opening money default 0  NULL,
              
constraint stkmstr_pk  primary key(code_value) )
  

CREATE TABLE stock_detail
       (doc_no int NOT NULL,
       sr_no money NOT NULL,
       code_value int NOT NULL,
       qty_in money default 0 NOT NULL,
       qty_out money default 0  NOT NULL
 
constraint stkdtl_pk primary key(doc_no,sr_no),

constraint smaster_fk foreign key(code_value)
			references stock_master(code_value),

constraint shdr_fk foreign key(doc_no)
			references tr_header(doc_no)
			

)

/**************Triggers**********************************/

if exists (select * from sysobjects where id = object_id(N'[dbo].[delete_stk]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[delete_stk]
GO
CREATE TRIGGER delete_stk ON stock_detail for delete as
Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @bal money

SELECT  * 
into #temp
from deleted

UPDATE stock_master
SET Closing =Closing- 
(ISNULL(#temp.qty_in,0) - ISNULL(#temp.qty_out,0))
FROM stock_master, #temp
WHERE ( stock_master.code_value = #temp.code_value    )

go

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

CREATE TRIGGER insert_stk ON stock_detail for insert as
Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @bal money

SELECT  * 
into #temp
from inserted
 
	BEGIN
		SELECT @bal = ISNULL(#temp.qty_in,0) - ISNULL(#temp.qty_out,0)
		FROM #temp		

	END


UPDATE stock_master
SET closing = isnull(closing,0) + @bal
FROM stock_master, #temp  WHERE ( stock_master.code_value = #temp.code_value    )

go
---------------------------------------------------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[update_stk]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[update_stk]
GO
CREATE TRIGGER update_stk ON stock_detail for update as


Declare @sql varchar(200)
DECLARE  @mtype char(1)
DECLARE @bal money


SELECT  * 
into #temp
from inserted

SELECT  * 
into #t2
from deleted

 
	BEGIN
		SELECT @bal = ISNULL(#temp.qty_in,0) - ISNULL(#temp.qty_out,0)
			     -(ISNULL(#t2.qty_in,0) - ISNULL(#t2.qty_out,0)) 	
		From #temp, #t2
		Where #temp.code_value = #t2.code_value
			

	END


UPDATE stock_master
SET closing = isnull(closing,0) + @bal
FROM stock_master, #temp  WHERE ( stock_master.code_value = #temp.code_value    )

go

---********************Procedures*************************

if exists (select * from sysobjects where id = object_id('p_stock_trans') and sysstat & 0xf = 4)
	drop procedure p_stock_trans
go  

create procedure p_stock_trans
@date datetime ,
@ref varchar(30) = NULL,
@qty_in money = 0,
@qty_out money =0,
@id char(3),
@doc_no integer = NULL,
@narr varchar(150) = NULL,
@code_value integer
as
/* 
call with a null doc_no to insert, a valid doc_no to update
Example :
Execute p_stock_trans

@date =getdate() ,
@ref = test1,
@qty_in = 10,
@qty_out  =0,
@id ="STK",
@doc_no = NULL,
@narr = "Test Entry"
*/

DECLARE @ll_doc  integer
DECLARE @ret integer

BEGIN TRANSACTION
--To insert a new movement, pass doc_no =NULL to procedure--
IF isnull(@doc_no,0) = 0
	BEGIN
		--SafeGuard : Check if tranaction with same ref# exists. If so dont insert
		select @ret = count(*) from tr_header where ref = @ref
		if @ret > 0 
		BEGIN
		   GOTO doerror
		END			
		Select @ll_doc =   isnull(max(doc_no),0)+1 from tr_header
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
	END

ELSE
	---------To UPDATE pass the doc_no--------
	BEGIN
		SELECT @ll_doc = @doc_no
		delete from stock_detail where doc_no = @doc_no
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
		delete from tr_header where doc_no = @doc_no
		IF @@ERROR != 0
		begin 
	 	GOTO doerror
		END
	END

BEGIN
	INSERT INTO tr_header ( id, date,ref,   doc_no ,narr) VALUES
	(@id, isnull(@date,getdate()),@ref,   @ll_doc, @narr)
		IF @@ERROR != 0
		begin 
		  GOTO doerror
		END

	INSERT INTO stock_detail ( doc_no, qty_in, qty_out, code_value, sr_no ) VALUES 		
	( @ll_doc, isnull(@qty_in,0), ISNULL(@qty_out,0), @code_value, 1 )	
		IF @@ERROR != 0
		begin 
		  GOTO doerror
		END		
END
COMMIT TRANSACTION	
SELECT 0
GOTO doreturn

doerror:
Rollback TRANSACTION
doreturn:
RETURN 0
SELECT -100

go
-------*************Stock Master****************************

if exists (select * from sysobjects where id = object_id('p_stock_master') and sysstat & 0xf = 4)
	drop procedure p_stock_master
go  

create procedure p_stock_master

@code_value integer = null,
@code_display varchar(30),
@rate  money  = 0 ,
@uom char(10),
@opening money = 0,
@closing money =0

as
/*
This procedures creates or updates a new stovk master record. If a null code_value is passed, a record is inserted
else the record is updated. 
example:
Exec p_stock_master 
@code_value  = null,
@code_display = "Lux Soap",
@rate    = 2 ,
@uom ="pcs",
@opening  = 0,
@closing  =0 

*/

DECLARE @flag integer


IF isnull(@code_value,0) = 0
	--INSERT--
	BEGIN
	
	
	Insert into stock_master(code_display,rate,uom,opening,closing)
	Values(@code_display,@rate,@uom,isnull(@opening,0),isnull(@closing,0))
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
	END

ELSE
	--UPDATE__
	BEGIN
				
		
		
		
		Update stock_master
		Set code_display = @code_display,
		rate = @rate,
		uom = @uom,
		opening =@opening,
		closing =@closing
		Where code_value =@code_value
		
		
	     	IF @@ERROR != 0
	 	begin 
	  	GOTO doerror
		END


	END




SELECT 0
GOTO doreturn
doerror:

Return - 100
doreturn:
RETURN 0


GO
--************Some dummy stock data

delete from stock_master

Exec p_stock_master 
@code_value  = null,
@code_display = "Lux Soap",
@rate    = 8 ,
@uom ="doz",
@opening  = 0,
@closing  =0 
go

Exec p_stock_master 
@code_value  = null,
@code_display = "Johnson & Johnson Soap",
@rate    = 9.5 ,
@uom ="doz",
@opening  = 0,
@closing  =0 

go

Exec p_stock_master 
@code_value  = null,
@code_display = "Coconut Oil",
@rate    = 100 ,
@uom ="Ton",
@opening  = 0,
@closing  =0 
go