/*This file contains scripts for database objects to accompany the book 
ASP  .NET Professional Projects
By Hersh Bhasin
To install
~~~~~~~~~~
Step1 : Create a MS SQL Server database with the name ASPNET
Step2 : Execute this file in the SQL Server Query Analyzer

*/


/****************************************************************
Create Database
*****************************************************************/

USE master
GO

IF DB_ID('ASPNET') IS NOT NULL BEGIN
    DROP DATABASE ASPNET
END
GO

CREATE DATABASE ASPNET
GO

USE ASPNET
GO


/*********************************************************
TABLE CREATION
**********************************************************/
CREATE TABLE groups
               (id char(3) NULL,
              code_display char(30) NOT NULL,
              code_value integer NOT NULL,
              code_category integer NULL ,
              type char(1) NOT NULL,
constraint groups_pk primary key(code_value) )
 
 

CREATE TABLE masters
               (
              code_value integer identity NOT NULL,
              code_display char(30) NOT NULL,
              code_category integer NOT NULL,
              type char(1) NOT NULL,
              closing money default 0  NULL,
              opening money default 0  NULL,
              
constraint masters_pk primary key(code_value)      )       




 
CREATE TABLE TblSelection
              (Selection varchar(50) NOT NULL  , 
	CONSTRAINT p
        PRIMARY KEY  NONCLUSTERED
       (Selection)) 

CREATE TABLE tr_header
               (id char(3) NOT NULL,
              date datetime NOT NULL,
              doc_no int not null,
              narr varchar(150) NULL,
              ref varchar(15) NULL,
            constraint trhdr_pk primary key(doc_no))
 

CREATE TABLE transactions
       (doc_no int NOT NULL,
       sr_no money NOT NULL,
       code_value int NOT NULL,
       dr_amount money default  0 NOT NULL,
       cr_amount money default  0 NOT NULL,
       posted_to int  null	,
     
constraint trans_pk primary key(doc_no,sr_no),

constraint masters_fk foreign key(code_value)
			references masters(code_value),

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

 
/************************************************
Insert Data
*************************************************/ 

insert into groups  
         ( code_value,   
           code_display,   
           code_category,   
           id, type )  
  values ( 1,   
           'Capital account',   
           null,   
           'R','L' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id, type )  
  values ( null,   
           2,   
           'Long term loans',   
           'R', 'L' )  
  
insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type)  
  values ( 3,   
           303,   
           'Bank od & limits',   
           'R','L' )  
    insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id, type )  
  values ( 2,   
           201,   
           'Secured loans',   
           'R','L' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id, type )  
  values ( 2,   
           202,   
           'Unsecured loans',   
           'R' ,'L')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type)  
  values ( null,   
           3,   
           'Current liabilities',   
           'R' ,'L')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type)  
  values ( 3,   
           300,   
           'Duties  taxes payable (bs)',   
           'R','L' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 3,   
           301,   
           'Provisions',   
           'R' ,'L')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 3,   
           302,   
           'Sundry creditors',   
           'R' ,'L')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           4,   
           'Fixed assets',   
           'R' ,'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           5,   
           'Investments',   
           'R' ,'A')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           6,   
           'Current assets',   
           'R', 'A' )  

insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 6,   
           601,   
           'Deposits (assets)',   
           'R', 'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 6,   
           602,   
           'Advances (assets)',   
           'R' , 'A' )  

  insert into groups  
         ( id , type,   
           code_display,   
           code_value,   
           code_category )  
  values ( 'R', 'A'   ,
           'Advances - excise a/cs',   
           60200,   
           602 )  
 


 insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 6,   
           603,   
           'Sundry debtors',   
           'R', 'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 6,   
           604,   
           'Cash in hand',   
           'R', 'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 6,   
           605,   
           'Bank accounts',   
           'R', 'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           7,   
           'Revenue accounts',   
           'R', 'M' )  

 insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 7,   
           700,   
           'Sales account',   
           'R' ,'I' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 7,   
           701,   
           'Purchase account',   
           'R' ,'E')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 7,   
           702,   
           'Income account',   
           'R', 'I' )  

 insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 7,   
           703,   
           'Duties and taxes paid (p/l)',   
           'R', 'E' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
values ( 7,   
           704,   
           'Expenditure account',   
           'R' , 'E' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 704,   
           70400,   
           'Expenses (direct)',   
           'R' , 'E' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( 704,   
           70401,   
           'Expenses (indirect)',   
           'R' ,'E')  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           8,   
           'Branch/divisions',   
           'R', 'A' )  
  insert into groups  
         ( code_category,   
           code_value,   
           code_display,   
           id , type )  
  values ( null,   
           9,   
           'Reserves and surplus',   
          'R' ,'L' )  


insert into masters   (  code_display, code_category,type)   
Values (  'Cash in Hand',604,'A')   

insert into masters   (  code_display, code_category,type)   
Values (  'Wells Fargo Checking',605,'A')

insert into masters   (  code_display, code_category,type)   
Values (  'Visa Card',605,'A')      

insert into masters   (  code_display, code_category,type)   
Values (  'Salary',70400,'E')

insert into masters   (  code_display, code_category,type)   
Values (  'Interest Income',702,'I')

insert into masters   (  code_display, code_category,type)   
Values (  'Rent',70401,'E')      
           
insert into masters   (  code_display, code_category,type)   
Values (  'Utilities',70400,'E')  

insert into masters   (  code_display, code_category,type)   
Values (  'Medical Expenses',70401,'E')   

go
/***************************************************
Stored Procedures
***************************************************/ 

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

create procedure p_masters


@code_value integer = null,
@code_display varchar(30),
@code_category integer = NULL ,
@type char(1)= NULL,
@opening money = 0 ,
@closing money =0,
@group_name varchar(30) = NULL 
as
/*********************************************************************************
This procedures creates or updates a new master record. If a null 
code_value is passed, a record is inserted else the record is updated.  You can 
pass either the code of the group or the descriptive name of the group.
Example passing the code of the group (604):
execute p_masters 1,' Petty Cash a/c' ,604, 'A',0,0 ,NULL 
Example passing the name of the group(Cash a/c):
p_masters 1,'Petty Cash a/c' ,604, 'A',0,0,'Cash a/c'
***********************************************************************************/


DECLARE @flag integer
DECLARE @oldType as char(1)
DECLARE @grCode_value integer
DECLARE @grType as char(1) 

IF isnull(@code_value,0) = 0
-----------If code value = 0 then INSERT  a new record ------------------
	BEGIN
		IF  Datalength(@group_name) > 1 
			Begin	
		        ---- If the group_name is provided look up group 
       			------details using   the descriptive name

			SELECT @grtype = type ,
			@grCode_value = code_value
			from Groups
			WHERE code_display = rtrim(@group_name)
			End
		Else
			---If a numeric code_value of the group is provided, 
      			---look up the group details using it

			Begin
			
			SELECT @grtype = type ,
			@grCode_value = code_value
			from Groups
			WHERE code_value = @code_category			

			End
	
	Insert into masters(code_category,code_display,type,opening,closing)
	Values(@grCode_value ,@code_display,@grtype,isnull(@opening,0),isnull(@closing,0))
		IF @@ERROR != 0
		begin 
	  	GOTO doerror
		END
	END

ELSE
	----------UPDATE a record if a code_value is passed ---------
	BEGIN
				
		
		
		
		Update masters
		Set code_category = @code_category,
		code_display = @code_display,
		--type = @type, don't allow update of type
		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
if exists (select * from sysobjects where id = object_id('p_trans') and sysstat & 0xf = 4)
	drop procedure p_trans
go  

create procedure p_trans

@date datetime ,
@ref varchar(30) = NULL,
@dr_amount money = 0,
@cr_amount money =0,
@posted_to integer,
@id char(3),
@doc_no integer = NULL,
@narr varchar(150) = NULL

as
/************************************************************
Author: Hersh Bhasin
This procedure creates or modifies a transaction record. 
Each transaction record will have a entry in tr_header and 
two records (a debit and a credit record) in the tranasaction table.
Usage:
To Insert a record:
call with a null doc_no to insert
example : exec p_trans @date="01/01/2001", @ref="test", 
@code_value = 1, @dr_amount = 10, @cr_amount=0, 
@posted_to = "Sales a/c" ,@id="RPT",@doc_no=Null
To modify a record:
call with an existing doc_no:
example : exec p_trans @date="01/01/2001", @ref="test", 
@code_value = 1, @dr_amount = 10, @cr_amount=0, 
@posted_to = "Sales a/c" ,@id="RPT",@doc_no=50
******************************************************************/

DECLARE @ll_doc  integer
DECLARE @ret integer
DECLARE @code_value integer
/*
Get the selected cash/bank account: 
The user makes a selection from selection.aspx 
and tblselection is updated with the code_value
*/
Select @code_value = selection from tblSelection


BEGIN TRANSACTION


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

