USE [cloudERP]
GO
/****** Object:  StoredProcedure [db_mm].[pr_savegrn]    Script Date: 15/01/2020 17:26:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [db_mm].[pr_savegrn] 
    @statementtype nvarchar(50),
	@grnheader [db_mm].[hdr_grn_tvp] readonly,
	@grndetail [db_mm].[dtl_grn_tvp] readonly,
	@grnbatchwise [db_mm].[grnbatchwise_tvp] readonly,
	@grnlevies [db_mm].[dtl_grn_levies_tvp] readonly,
	@userid int=1
AS

BEGIN 
SET QUOTED_IDENTIFIER OFF;
	SET ANSI_NULLS ON; 
	SET NOCOUNT ON;
		declare @grnid int, @rowcnt int,@suplid int,@itemid int,@mainid int
		declare @grnnumber int
	BEGIN TRANSACTION
	If (@statementtype='Insert')
		Begin
	set @grnnumber = (SELECT ISNULL(MAX(grnnumber), 0) + 1 FROM db_mm.hdr_grn)
		
	
		insert into [db_mm].[hdr_grn] (grndate,grnnumber,gatenoteid,storeid,suplid,poid,billamount,totaltaxamount,billdiscount,billroundoff,billno,billdate,chalanno,chalandate,gateentryno,gateentrydate,
		purchaseaccount,grno,grdate,drcrnoteno,billpassamount,boid,coid,remarks,isactive,createdby,createdon,modifiedby,modifiedon,fyid,[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount],[grncategoryid]) 
			Select grndate,@grnnumber,[gatenoteid],[storeid]
				,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[boid],[coid],[remarks],[isactive],[createdby],[createdon],[modifiedby],[modifiedon],[fyid],[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount],[grncategoryid]  from @grnheader
				IF @@ERROR <> 0
				BEGIN
				-- Rollback the transaction
				ROLLBACK
				-- Raise an error and return
				RAISERROR ('Error while adding grn header data.', 16, 1)
				RETURN
				END
				SELECT @grnid= SCOPE_IDENTITY()
				IF @@ERROR <> 0
				BEGIN
				-- Rollback the transaction
				ROLLBACK
				-- Raise an error and return
				RAISERROR ('Error while getting indent id.', 16, 1)
				RETURN
				END
				update [db_mm].[hdr_grn] set status ='E' where grnid = @grnid
				
			   insert into [db_mm].[dtl_grn] (grnid,itemid,quantitybilled,quantityreceived,quantityrejected,quantityshortage,
				quantitysurplus,quantitybroken,itemrate,itemdiscount,ratediff,itemtax,brandid,hsncode,arnumber,qtyissued,receiptuom,issueuom,batchno,expirydate,mfgdate,orderid,[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty] )
				select @grnid,[itemid],[quantitybilled],[quantityreceived],[quantityrejected],(quantitybilled-quantityreceived),[quantitysurplus],
				[quantitybroken],[itemrate],[itemdiscount],[ratediff],[itemtax],[brandid],[hsncode],[arnumber],[qtyissued],[receiptuom],[issueuom],[batchno],[expirydate],[mfgdate],[orderid],[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty] 
				from @grndetail


				if   exists(select 1 from @grnbatchwise) 
				begin
				insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,arnumber,batchno,expirydate,mfgdate,orderid,[sampleqty],[supplierretestdate])
				select @grnid,[itemid],[quantity],[brandid],[arnumber],[batchno],[expirydate],[mfgdate],[orderid],[sampleqty],[supplierretestdate] from @grnbatchwise
				  insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,orderid,[sampleqty])
				select @grnid,[itemid],[quantityreceived],[brandid],[orderid],[sampleqty] from @grndetail where itemid  not in (select itemid from @grnbatchwise)
				end 
				ELSE
				begin
                insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,orderid,[sampleqty])
				select @grnid,[itemid],[quantityreceived],[brandid],[orderid],[sampleqty] from @grndetail
				end

				----Insert Levies data
	             insert into [db_mm].[dtl_grn_levies] (grnid, levyid, quantity, rate, uom, uomid, amount, hsncode, gstid, 
	             igstamt, cgstamt, sgstamt, igstrate, cgstrate, sgstrate, createdby, createdon)
	             select @grnid, levyid, quantity, rate, uom, uomid, amount, hsncode, gstid, igstamt, cgstamt, sgstamt, igstrate,
	             cgstrate, sgstrate, @userid, getdate()	from @grnlevies
	 	         IF @@error <> 0
		         BEGIN
			       -- Rollback the transaction
			      ROLLBACK
			-- Raise an error and return
			     RAISERROR ('Error while adding sop invoice levies data.', 16, 1)
			     RETURN
		         END

				---update atrectquantity in podetail
				update db_mm.dtl_purchaseorder  set atrectquantity=isnull(atrectquantity,0)+[quantityreceived] from @grndetail dt where db_mm.dtl_purchaseorder.orderid=dt.orderid
				and db_mm.dtl_purchaseorder.itemid=dt.itemid and db_mm.dtl_purchaseorder.brandid=dt.brandid 

				end 
	    else
		Begin -- begin of update
			select top 1 @grnid = grnid from @grnheader

			   select  [grndate],[grnnumber],[gatenoteid],[storeid]
				,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[suplname],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],
				[customduty],[otherdiscount],[jobworkamount],[grncategoryid]  from @grnheader
				Except
			   select [grndate],[grnnumber],[gatenoteid],[storeid]
				,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[suplname],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount],[grncategoryid]
			   from db_mm.hdr_grn where grnid =@grnid
			

			SELECT @rowcnt = @@ROWCOUNT
			-- data needs to update if query returns a row  
			IF @rowcnt > 0
				BEGIN -- begin of row count
					-- insert records in bkup and checking @@ERROR 
				--	insert into CloudERPbklog.db_mm.hdr_GRN ([grnid],[grndate],[grnnumber],[gatenoteid],[storeid]
				--,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				--,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[boid],[coid],[remarks],[isactive],[modifiedby],[fyid],[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount]) 
				--	SELECT [grnid],[grndate],[grnnumber],[gatenoteid],[storeid]
				--,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				--,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[boid],[coid],[remarks],[isactive],@userid,[fyid],[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount]  from db_mm.hdr_grn where grnid = @grnid

					IF @@ERROR <> 0
					 BEGIN
						-- Rollback the transaction
						ROLLBACK
						-- Raise an error and return
						RAISERROR ('Error while updating log enteries.', 16, 1)
						RETURN
					 END
					-- updating existing records from supplier table and checking @@ERROR 
					UPDATE db_mm.hdr_grn   set 
					         [grndate] = a.[grndate],
                                [storeid] =a.[storeid],
                             [suplid] =a.[suplid],
                             [poid] =a.[poid],
                             [billamount] =a.[billamount],
                             [totaltaxamount] =a.[totaltaxamount],
                             [billdiscount]=a.[billdiscount],
                             [billroundoff]=a.[billroundoff],
                             [billno]=a.[billno],
                             [billdate]=a.[billdate],
                             [chalanno]=a.[chalanno],
                             [chalandate] =a.[chalandate],
                             [gateentryno] =a.[gateentryno],
                             [gateentrydate] =a.[gateentrydate],
                             [purchaseaccount]=a.[purchaseaccount],
                             [grno] =a.[grno],
                             [grdate] =a.[grdate],
                             [drcrnoteno] =a.[drcrnoteno],
                             [billpassamount]=a.[billpassamount],
                             [boid] =a.[boid],
                             [coid] =a.[coid],
                             [remarks] =a.[remarks],
                             [isactive]=a.[isactive],
                             [modifiedby]=a.[modifiedby],
							 [suplname]=a.[suplname],
							[supladdress1]=a.[supladdress1],
							[supladdress2]=a.[supladdress2],
							[suplcity]=a.[suplcity],
							[suplstate]=a.[suplstate],
							[suplpincode]=a.[suplpincode],
							[suplphoneno]=a.[suplphoneno],
							[igstamount]=a.[igstamount],
							[cgstamount]=a.[cgstamount],
							[sgstamount]=a.[sgstamount],
							[freightandoctroi]=a.[freightandoctroi],
							[othercharges]=a.[othercharges],
							[customduty]=a.[customduty],
							[otherdiscount]=a.[otherdiscount],
							[jobworkamount]=a.[jobworkamount],
							[grncategoryid]=a.[grncategoryid]
					FROM   @grnheader a
					WHERE db_mm.hdr_grn.grnid= a.grnid 
		
					-- Rollback the transaction if there were any errors
					IF @@ERROR <> 0
						BEGIN
							-- Rollback the transaction
							ROLLBACK
							-- Raise an error and return
							RAISERROR ('Error while updating existing grn record.', 16, 1)
							RETURN
						END
				END -- end of row count
			Else -- if record count of except statement is 0,check for supplier entry in log table, if record count is 0, insert record for the first time in log table 
				Begin
					if not exists(select grnid from CloudERPbklog.db_mm.hdr_grn where grnid = @grnid)
						begin
							-- insert data in INDENT HEADER table and checking @@ERROR 
							insert into CloudERPbklog.db_mm.hdr_grn([grnid],[grndate],[grnnumber],[gatenoteid],[storeid]
				,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[boid],[coid],[remarks],[isactive],[modifiedby],[fyid],[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount] )
			    Select [grnid],[grndate],[grnnumber],[gatenoteid],[storeid]
				,[suplid],[poid],[billamount],[totaltaxamount],[billdiscount],[billroundoff],[billno],[billdate],[chalanno],[chalandate],[gateentryno],[gateentrydate]
				,[purchaseaccount],[grno],[grdate],[drcrnoteno],[billpassamount],[boid],[coid],[remarks],[isactive],@userid,[fyid],[suplname],[supladdress1],[supladdress2],[suplcity],[suplstate],[suplpincode],[suplphoneno],[igstamount],[cgstamount],[sgstamount],[freightandoctroi],[othercharges],[customduty],[otherdiscount],[jobworkamount]  from db_mm.hdr_grn where grnid = @grnid
							-- Rollback the transaction if there were any errors
							IF @@ERROR <> 0
								BEGIN
								-- Rollback the transaction
								ROLLBACK
								-- Raise an error and return
								RAISERROR ('Error while adding grn data.', 16, 1)
								RETURN
								END
										end
				End
			
			SELECT itemid,quantitybilled,quantityreceived,quantityrejected,quantityshortage,
			quantitysurplus,quantitybroken,itemrate,itemdiscount,ratediff,itemtax,brandid,hsncode,arnumber,qtyissued,receiptuom,issueuom,batchno,expirydate,mfgdate,orderid,[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty] 
			from [db_mm].[dtl_grn] where [grnid] =@grnid
			EXCEPT select itemid,quantitybilled,quantityreceived,quantityrejected,quantitshortage,
			quantitysurplus,quantitybroken,itemrate,itemdiscount,ratediff,itemtax,brandid,hsncode,arnumber,qtyissued,receiptuom,issueuom,batchno,expirydate,mfgdate,orderid,[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty]   from @grndetail		
			
			--EXEC sp_executesql @sql

			SELECT @rowcnt = @@ROWCOUNT
			
			IF @rowcnt > 0
				BEGIN 
				insert into CloudERPbklog.db_mm.dtl_grn (grnid,itemid,quantitybilled,quantityreceived,quantityrejected,quantitshortage,
				quantitysurplus,quantitybroken,itemrate,itemdiscount,ratediff,itemtax,brandid,hsncode,arnumber,qtyissued,receiptuom,issueuom,batchno,expirydate,mfgdate,orderid,[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty] ) 
					Select  @grnid,itemid,quantitybilled,quantityreceived,quantityrejected,quantityshortage,
				quantitysurplus,quantitybroken,itemrate,itemdiscount,ratediff,itemtax,brandid,hsncode,arnumber,qtyissued,receiptuom,issueuom,batchno,expirydate,mfgdate,orderid,[gstid],[igstamount],[cgstamount],[sgstamount],[sampleqty]  from db_mm.dtl_grn where [grnid] = @grnid

			
					-- Rollback the transaction if there were any errors
					IF @@ERROR <> 0
					 BEGIN
						-- Rollback the transaction
						ROLLBACK
						-- Raise an error and return
						RAISERROR ('Error while updating log enteries.', 16, 1)
						RETURN
					 END
					 update db_mm.dtl_purchaseorder  set atrectquantity=isnull(atrectquantity,0)-[quantityreceived]+(select quantityreceived from @grndetail gd where db_mm.dtl_purchaseorder.itemid=gd.itemid and db_mm.dtl_purchaseorder.brandid=gd.brandid )   from @grndetail dt where db_mm.dtl_purchaseorder.orderid=dt.orderid
				     and db_mm.dtl_purchaseorder.itemid=dt.itemid and db_mm.dtl_purchaseorder.brandid=dt.brandid 
					 delete from db_mm.dtl_grn where [grnid] = @grnid

                
				insert into db_mm.dtl_grn(grnid,itemid,quantitybilled,quantityreceived,quantityrejected,quantityshortage,
				quantitysurplus,quantitybroken,itemrate,itemdiscount,brandid,hsncode,arnumber,qtyissued,receiptuom,orderid,[gstid],[igstamount],[cgstamount],
				[sgstamount],[sampleqty] )
					Select @grnid,itemid,quantitybilled,quantityreceived,quantityrejected,quantitshortage,
				quantitysurplus,quantitybroken,itemrate,itemdiscount,brandid,hsncode,arnumber,qtyissued,receiptuom,orderid,[gstid],[igstamount],[cgstamount],
				[sgstamount],[sampleqty]  from @grndetail
					-- Rollback the transaction if there were any errors
					IF @@ERROR <> 0
						BEGIN
							-- Rollback the transaction
							ROLLBACK
							-- Raise an error and return
							RAISERROR ('Error while updating existing grn detail record.', 16, 1)
							RETURN
						END
						
				IF @@ERROR <> 0
						BEGIN
							-- Rollback the transaction
							ROLLBACK
							-- Raise an error and return
							RAISERROR ('Error while updating existing grn detail record.', 16, 1)
							RETURN
						END
				END
				
				delete from [db_mm].[grnbatchwise] where grnid=@grnid
				
				if   exists(select 1 from @grnbatchwise) 
				begin
				insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,arnumber,batchno,expirydate,mfgdate,orderid,[sampleqty])
				select @grnid,[itemid],[quantity],[brandid],[arnumber],[batchno],[expirydate],[mfgdate],[orderid],[sampleqty] from @grnbatchwise
				  insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,orderid,[sampleqty])
				select @grnid,[itemid],[quantityreceived],[brandid],[orderid],[sampleqty] from @grndetail where itemid  not in (select itemid from @grnbatchwise)
				end 
				ELSE
				begin
                insert into [db_mm].[grnbatchwise] (grnid,itemid,quantity,brandid,orderid,[sampleqty])
				select @grnid,[itemid],[quantityreceived],[brandid],[orderid],[sampleqty] from @grndetail
				end -- end of row count

				delete from [db_mm].[dtl_grn_levies] where grnid=@grnid
	   insert into [db_mm].[dtl_grn_levies] (grnid, levyid, quantity, rate, uom, uomid, amount, hsncode, gstid, 
	igstamt, cgstamt, sgstamt, igstrate, cgstrate, sgstrate, createdby, createdon)
	select @grnid, levyid, quantity, rate, uom, uomid, amount, hsncode, gstid, igstamt, cgstamt, sgstamt, igstrate,
	 cgstrate, sgstrate, @userid, getdate()	from @grnlevies
			end
								commit
								
END



