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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE advance_licencing
(
	@type1 varchar(1),
	@adid numeric(28),
--itemcode
--import used for item code details 
--export is used for export number
	@itemcode varchar(20),
--expqty
--import used for allowed with 2% 
--export used for shipment_qty
	@expqty  numeric(20,8),
--actqty
--import used for actual_import
--export used for ae_id
	@actqty   numeric(20,8),
--dt
--import not using
--export used for shippment date
	@dt varchar(11)	
	
)AS
	declare	
	
	@aie_id numeric (20),
	@order_id  varchar(50),
	@balqty numeric(20,8),
	@old_Bal_Qty numeric(20,8),
	@old_Act_Qty numeric(20,8),
	@total_cap_Qty numeric (28,0),
	@new_bal_Qty numeric (28,0),
	@ship_qty numeric (28,0)
	if @type1='I'
begin
	set select @aie_id = ai_id from ad_item_det where ad_id=@adid and item_code=@itemcode
	if @aie_id is null
		begin
		
			insert into ad_item_det (ad_id,item_code,exp_qty,act_qty,bal_qty) values (@adid,@itemcode,@expqty,@actqty,@actqty)
			--return 0
		end 
		
	else		
		begin
							
			update ad_item_det set act_qty=@actqty where ai_id=@aie_id
			execute advance_licencing_export_qty_update  @adid 
		end
end
else
begin
	--select @aie_id = ae_id from ad_exp_det where ad_id=@adid and exp_no=@itemcode
	if @actqty=0	
		begin
			insert into ad_exp_det (ad_id,ship_qty,exp_no,ship_dt) values (@adid,@expqty,@itemcode,@dt)
			execute advance_licencing_export_qty_update  @adid 
		end
	else
		begin
		if @actqty=-1	
			begin
				insert into ad_exp_det (ad_id,ship_qty,exp_no,ship_dt) values (@adid,@expqty,@itemcode,@dt)
				execute advance_licencing_export_qty_update  @adid 
				execute  licencing_adjustment_closing  @adid 
				update ad_lic_pro set exp_close='Y',exp_close_dt=getdate()
				where ad_id=@adid
			end
		else
			begin
				update ad_exp_det set ship_qty=@expqty where ae_id=@actqty
				execute advance_licencing_export_qty_update @adid
			end
		end
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE advance_licencing_export_qty_update
(
	@adid numeric(28)	
)AS
	declare	
	@total_cap_Qty numeric (28,0),
	@new_bal_Qty numeric (28,0),
	@ship_qty numeric (28,0),
	@shiped_mat numeric(20,8),
	@aiid numeric(28),
	@itemcode varchar (50),
	@act_qty numeric(20,8)
	begin
		set select @ship_qty=sum(ship_qty) from ad_exp_det where ad_id=@adid
		
		set select @total_cap_Qty=total_cap_exp from ad_lic_pro where ad_id=@adid
		
		if @ship_qty IS NULL
			begin
				set @new_bal_Qty=@total_cap_Qty
				update ad_item_det set exported_qty=0,bal_qty=act_qty where ad_id=@adid
			end
		else
			set @new_bal_Qty=@total_cap_Qty - @ship_qty
		
		begin
			
				declare a Cursor for select ai_id,item_code,act_qty  from ad_item_det where ad_id=@adid
				open a
				fetch Next from a into @aiid,@itemcode,@act_qty
				while @@fetch_status=0
				begin
						set select @shiped_mat=((b.qty/p.batch_size)*@ship_qty*1.02) from 
						ad_lic_pro alp,inter_quarter iq,inter_master im,product_master p,bom b,item_mast i
						where alp.ad_id=@adid and iq.qtr_id=alp.qtr_id and iq.inter_id=im.inter_id 
						and im.p_id=p.p_id and p.prod_no=b.prod_no and b.item_type='R' 
						and b.item_code=@itemcode and i.item_type=b.item_type and i.imp_loc='I'
						if @shiped_mat	is not null
						update ad_item_det set exported_qty=@shiped_mat,bal_qty=@act_qty-@shiped_mat where ai_id=@aiid
					fetch Next from a into @aiid,@itemcode,@act_qty
				end 
				close a
			deallocate a
			--if @new_bal_qty > 0 
				update ad_lic_pro set bal_cap_exp=@new_bal_qty
				where ad_id=@adid
			--else
				--update ad_lic_pro set bal_cap_exp=@new_bal_qty,exp_close='Y',exp_close_dt=getdate()
				--where ad_id=@adid
		
		end
	end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE banner_mat_req
(
	@dat datetime
)AS
	declare	
	@o_l varchar(1),            		-----------		own / loan licence Product
	@banner_provided varchar(1),     -----------		Raw material Banner Provided
	@total_qty numeric(25,6),   	-----------		Total Order Qty for Month
	@p_id numeric(18),		-----------		Product Identity Number
	@prodno varchar(50),		-----------		Banner Provided Product Identity
	@batchSize numeric(18),		-----------		no of capsules in a batch
	@PackSize  numeric(18),	----------		no of capsules in a pack
	@pckcd varchar(50),		-----------		ordered Packing materials 
	@catid numeric (8),		-----------		Product Category
	@wipqty numeric(18),		-----------		WIP Quantity
	@fgqty numeric (18),		-----------		Quantity in Finished Good Stock
	@totrmqty numeric(18),		-----------		Total Qty to be Produced in this month of production (respect to RM Required)
	@totpmqty Numeric(18),		-----------		Total Qty to be Produced in this month of production (respect to PM Required)
	@RMFtion numeric (8,3),		-----------		Total Quantity as Per Batch for RM
	@PMFtion numeric (8,3) ,	-----------		Total Quantity as Per Batch for RM
	@RminBatch numeric(8),		-----------		Minimum Batches to be Manufactured (respect to RM Required)
	@PminBatch numeric(8),		-----------		Minimum Batches to be Manufactured (respect to PM Required)
	@RmPBatch numeric(8,3),	-----------		Partial Batches to be Manufactured (respect to RM Required)
	@PmPBatch numeric(8,3),	-----------		Partial Batches to be Manufactured (respect to PM Required)
	@RmRound numeric(8,3),	----------		Round off of Partial Batch From cat master (respect to RM Required)
	@PmRound numeric(8,3),	----------		Round off of Partial Batch From cat master (respect toPM Required)
	@totRMBatch numeric(18,3),	----------		Total Batches Qty for Asking RM
	@totPMBatch numeric(18,3),	----------		Total Batches Qty for Asking PM
	@totRMReg numeric(18,3),	----------		Total RM Quantity required
	@totPMReg numeric(18,3),	----------		Total PM Quantity required
	@banpro varchar(2),		----------		Material banner Provided
	@c_id numeric (28,0),	
	@c_name varchar (50),
	@o_qty_req numeric(25,6),  --- order quantity required material
	@c_number varchar(50),
	@plant_number varchar(50),
	@plant_id numeric(18),
	@plant_name varchar (50),
	@item_name varchar (50),		
	@val numeric(25,6),  ---no of batches ordered
	@qty numeric(25,6),  ---qty required per batch
	@um_des varchar (50),
	@item_type varchar(50),
	@item_code varchar(50),
	@op_bal numeric(25,6),
	@thisqty numeric(25,6),   ----quantity required from customer for this production month
	@inter numeric (18),---intermdiary product
	@chkdt datetime,
	@my numeric(1) ,
	@no numeric(1) ,
	@tot numeric(5)
begin
select distinct @chkdt=for_dt from ban_mat_req where for_dt = (select min(for_dt) from ban_mat_req)
if @chkdt=@dat 
	set @my=1
else
begin
		delete from ban_mat_req 
	
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,d.total_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from dom_order d,product_master p ,plant_master pl,customer_master cm
	where d.o_date=@dat
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
--------------select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
-------------Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--------------Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--------------Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)						
--------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
--------------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'			
			if @rmround is null
				set @rmround=1
--------------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
		
			if @totRMBatch > 0  
			begin
--------------select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type and banner_provided=@banpro					
						if @tot>0 
							update ban_mat_req   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des	 and banner_provided=@banpro		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO ban_mat_req([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
							end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
---------------------------- packing material for domestic orders
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,dod.act_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id,dod.pck_cd,pck.pack_size
	from dom_order d,dom_order_details dod,product_master p ,plant_master pl,customer_master cm,pack pck
	where d.o_date=@dat
	and dod.o_id=d.o_id
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	and dod.pck_cd=pck.pck_cd
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd,@packSize
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno and pck_cd=@pckcd
			if @fgqty is null
				set @fgqty=0
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totpmqty > 0  and @batchSize > 0 
			begin
			set @totPMBatch=@totpmqty/@batchSize
		
			if @totPMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totPMBatch* b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='P' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type and b.pck_cd=@pckcd
				open RV
				fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin
				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type  and banner_provided=@banpro	
							
						if @tot>0 			
							update ban_mat_req   set  [qty] =qty+@totPmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and banner_provided=@banpro	
		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO ban_mat_req ([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totPMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
							end						
					fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end			
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd,@PackSize
		end 
	close v
	deallocate v	
-------------------------------for international orders
	declare v Cursor for select  cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,
	qd.month_qty val,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from inter_master i,inter_quarter iq,quarter_details qd,product_master p,plant_master pl,customer_master cm
	where i.inter_id=iq.inter_id and i.p_id=p.p_id and qd.qtr_id=iq.qtr_id 
	and o_month=@dat
	and pl.plant_id=p.plant and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
-- select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
			
			
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)			
			
------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'
			
			if @rmround is null
				set @rmround=1
--------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
		
			if @totRMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type  and banner_provided=@banpro	
						if @tot>0 
							update ban_mat_req   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type  and banner_provided=@banpro	
						else
						begin
							select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
							and doc_date < @dat
							and cust_no=@c_number
							and location_id=@plant_number
							and item_type=@item_type
							and item_code=@item_code
							if @op_bal is  null				
								begin
								set @op_bal=0
								end
							INSERT INTO ban_mat_req([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
							VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
						end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------to  get data for next month--------------------------------------
----------------------------------------------------------------------------------------------------------------------------
set @dat=dateadd(m,1,@dat)
print @dat
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,d.total_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from dom_order d,product_master p ,plant_master pl,customer_master cm
	where d.o_date=@dat
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
-- select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)						
------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'			
			if @rmround is null
				set @rmround=1
--------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
		
			if @totRMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type and banner_provided=@banpro	and for_dt=@dat				
						if @tot>0 
							update ban_mat_req   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des	 and banner_provided=@banpro		and for_dt=@dat
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO ban_mat_req([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
							end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
---------------------------- packing material for domestic orders
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,dod.act_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id,dod.pck_cd,pck.pack_size
	from dom_order d,dom_order_details dod,product_master p ,plant_master pl,customer_master cm,pack pck
	where d.o_date=@dat
	and dod.o_id=d.o_id
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	and dod.pck_cd=pck.pck_cd
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd,@packSize
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno and pck_cd=@pckcd
			if @fgqty is null
				set @fgqty=0
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totpmqty > 0  and @batchSize > 0 
			begin
			set @totPMBatch=@totpmqty/@batchSize
		
			if @totPMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totPMBatch* b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='P' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type and b.pck_cd=@pckcd
				open RV
				fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin
				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type  and banner_provided=@banpro	and for_dt=@dat
							
						if @tot>0 			
							update ban_mat_req   set  [qty] =qty+@totPmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and banner_provided=@banpro	and for_dt=@dat
		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO ban_mat_req ([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totPMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
							end						
					fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end			
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd,@PackSize
		end 
	close v
	deallocate v	
-------------------------------for international orders
	declare v Cursor for select  cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,
	qd.month_qty val,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from inter_master i,inter_quarter iq,quarter_details qd,product_master p,plant_master pl,customer_master cm
	where i.inter_id=iq.inter_id and i.p_id=p.p_id and qd.qtr_id=iq.qtr_id 
	and o_month=@dat
	and pl.plant_id=p.plant and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
-- select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
			
			
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)			
			
------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'
			
			if @rmround is null
				set @rmround=1
--------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
		
			if @totRMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from ban_mat_req where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type  and banner_provided=@banpro	and for_dt=@dat
						if @tot>0 
							update ban_mat_req   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type  and banner_provided=@banpro	and for_dt=@dat
						else
						begin
							select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
							and doc_date < @dat
							and cust_no=@c_number
							and location_id=@plant_number
							and item_type=@item_type
							and item_code=@item_code
							if @op_bal is  null				
								begin
								set @op_bal=0
								end
							INSERT INTO ban_mat_req([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided,for_dt)
							VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro,@dat)
						end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
end
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE cd_insert 
(
@cid numeric(8),
@cdate varchar(50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
insert into cd_entry (c_id,c_date,amt,b_name,entry_by,entry_dt,loc_id)
 values
 (@cid ,@cdate ,@amt,@bname,@uid,getdate(),@loc)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE cd_update
(
@eid varchar (50),
@cid numeric(8),
@cdate varchar (50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
update cd_entry set c_date=@cdate,amt=@amt,b_name=@bname,edit_by=@uid,edit_dt=getdate(),loc_id=@loc
where e_id=@eid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE delivery_plan_monthly
(@pid numeric(28),
@ed numeric (18,8),
@pckcd varchar(28),
@qty1 numeric(28),
@val1 numeric(28,5),
@qty2 numeric(28),
@val2 numeric(28,5),
@qty3 numeric(28),
@val3 numeric(28,5),
@qty4 numeric(28),
@val4 numeric(28,5),
@tot numeric(28,5),
@dt datetime,
@rate numeric(28,3),
@uid numeric(8),
@loc numeric(8)
)
as
	declare	@indian_id	numeric (4)
		
select @indian_id =del_id from delivery_plan where pck_cd=@pckcd and del_dt=@dt
if @indian_id is not null
	begin
		
		update delivery_plan set ed=@ed,qty_1=@qty1,qty_2=@qty2,qty_3=@qty3,qty_4=@qty4,value_1=@val1,value_2=@val2,value_3=@val3,value_4=@val4,total_qty=@tot
,edit_by=@uid,edit_dt=getdate() ,loc_id=@loc
where del_id=@indian_id
				
	end
else
	begin
		insert into delivery_plan (p_id,ed,pck_cd,qty_1,qty_2,qty_3,qty_4,value_1,value_2,value_3,value_4,del_dt,total_qty,rate
,entry_by,entry_dt,loc_id
)
values (@pid,@ed,@pckcd,@qty1, @qty2 , @qty3 ,  @qty4, @val1, @val2, @val3 , @val4,@dt,@tot,@rate
,@uid ,getdate(),@loc
)
	end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dom_order_process
(
	@p_id numeric(28),
	@month varchar(20),
	@pck_cd  varchar(28),
	@pack_size   numeric(28),
	@qty   numeric(28),
	@a_unit  numeric(28),
	@a_unit1  numeric(28)		
	
)AS
	declare	
	@tot	varchar (50),
	@order_id  varchar(50),
	@o_id numeric(28),
	@new_qty numeric(28),
	@sample_qty numeric(28),
	@firmdate numeric(3)
	set @order_id=convert(varchar(5),@p_id) +  '-' +convert(varchar(2), month(@month))+ '-' +convert(varchar(4), year(@month))	
	select @tot = o_id from dom_order where order_no=@order_id
	if @tot is null
		begin
		
			insert into dom_order (order_no  ,p_id ,o_date) values (@order_id   ,@p_id,@month)
			insert into dom_order_details values (@@identity,@pck_cd,@pack_size,@qty,@qty,@qty,@qty,@a_unit,@a_unit1)
			return 0
		end 
		
	else
		begin
			select @o_id= d_o_id from dom_order_details where o_id=@tot and pck_cd=@pck_cd
			if @o_id is  null
			
				insert into dom_order_details values (@tot,@pck_cd,@pack_size,@qty,@qty,@qty,@qty,@a_unit,@a_unit1)
			else
			/*if month(@month)<>month(getdate())*/
				begin
				if month(@month)=month(dateadd(m,1,getdate()))
					begin
					select @firmdate = convert(numeric(3),parameter) from parameter where param_id=503
					if @firmdate is not null
						begin						
						if day(getdate()) <=@firmdate				
							update dom_order_details set e_15 =@qty,act_qty=@qty ,a_unit=@a_unit,a_unit1=@a_unit1
							where d_o_id=@o_id
					
						if day(getdate()) >@firmdate and day(getdate()) <=30
							update dom_order_details set e_30=@qty  ,act_qty=@qty,a_unit=@a_unit,a_unit1=@a_unit1
							where d_o_id=@o_id
						end
					else
						update dom_order_details set exp_qty=@qty,e_15 =@qty ,e_30=@qty  ,act_qty=@qty,a_unit=@a_unit,a_unit1=@a_unit1
						where d_o_id=@o_id
					end 
				else	
					update dom_order_details set exp_qty=@qty,e_15 =@qty ,e_30=@qty  ,act_qty=@qty,a_unit=@a_unit,a_unit1=@a_unit1
					where d_o_id=@o_id
				end
		end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE excise_insert 
(
@cid varchar (50),
@cno varchar (50),
@cdate varchar (50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
insert into excise_entry (c_id,c_no,c_date,amt,b_name,entry_by,entry_dt,loc_id)
 values
 (@cid ,@cno ,@cdate ,@amt,@bname,@uid,getdate(),@loc)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE excise_update
(
@eid varchar (50),
@cno varchar (50),
@cdate varchar (50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
update excise_entry set c_no=@cno,c_date=@cdate,amt=@amt,b_name=@bname,edit_by=@uid,edit_dt=getdate(),loc_id=@loc
where e_id=@eid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE fund_insert 
(
@cno varchar (50),
@cdate varchar(50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
insert into fund_entry (c_no,c_date,amt,b_name,entry_by,entry_dt,loc_id)
 values
 (@cno ,@cdate ,@amt,@bname,@uid,getdate(),@loc)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE fund_update
(
@eid varchar (50),
@cno varchar (50),
@cdate varchar (50),
@amt varchar (50),
@bname varchar (50),
@uid numeric (20),
@loc numeric(8)
) AS
update fund_entry set c_no=@cno,c_date=@cdate,amt=@amt,b_name=@bname,edit_by=@uid,edit_dt=getdate(),loc_id=@loc
where e_id=@eid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE inter_master1
(
@a numeric (4),
@b varchar (50),
@c varchar (50),
@d varchar (250),
@e varchar (50),
@f varchar (50),
@g numeric (20),
@h varchar (50),
@i varchar (50),
@j varchar (50),
@k varchar (50),
@l varchar (50),
@m varchar (50),
@n varchar (50),
@o varchar (50),
@p numeric (20),
@q varchar (50),
@r varchar (50),
@s varchar (50),
@t varchar (50),
@u numeric (20),
@v numeric (20),
@w varchar (50),
@x varchar (50),
@y varchar (50),
@z varchar (50),
@aa varchar (50),
@ab varchar (50),
@ac varchar (1500),
@ad numeric (20),
@ae varchar (50),
@af varchar (50),
@ag varchar (50),
@uid numeric (20),
@bulk varchar (50),
@color varchar (50),
@shape varchar (50),
@cons varchar (50),
@note varchar (150),
@final varchar (50),
@p6 varchar (50),
@p7 varchar (50),
@p10 varchar (50),
@p12 varchar (50),
@p13 varchar (50),
@p14 varchar (50)
)
AS
insert into inter_master (p_id,advice_ref,order_no,consinee_add,discharge_port,order_status, order_qty,tariff_code,
unit_pack,shipment_mode,container_req,latest_del_dt,stuffing_dt, stuffing_at,export_under,strip_size,printed_foil,pvc_pdvc,
printed_carton,inserts,shipper_qty,polybag_qty ,double_polybag ,shipper_quality ,shipper_label ,strapping ,stretch,stretch_type,remarks,
number_of_shipper,rawmaterial_status,shelf_life,licence_number ,entry_by,entry_dt,order_type,color,shape,consignee,note,final,lot,dim,bopp,pall,conf,tag)
values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m,@n,@o,@p,@q,@r,@s,@t,
@u,@v,@w,@x,@y,@z,@aa,@ab,@ac,@ad,@ae,@af,@ag,@uid,getdate(),@bulk,@color,@shape,@cons,@note,@final,@p6,@p7,@p10,@p12,@p13,@p14)
return @@identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE international_order
(
@iid numeric (20),
@first_date datetime,
@a1 numeric (20),
@a2 numeric (20),
@a3 numeric (20),
@uid numeric(28)
)
as
	declare	
		@q_id	numeric (28),
		@qq_id	numeric (28),
		@tot_qtr numeric (28),
		@s_date datetime,
		@m_date datetime,
		@l_date datetime
		
set @s_date=@first_date
set @m_date=dateadd(m,1,@first_date)
set @l_date=dateadd(m,2,@first_date)
set @tot_qtr=@a1+@a2+@a3
select @q_id =qtr_id from inter_quarter where inter_id=@iid and  start_dt=@first_date
if @q_id is not null
	begin
		
	update inter_quarter set inter_id =@iid,start_dt=@s_date , total_qty=@tot_qtr,edit_dt=getdate() ,edit_by=@uid where qtr_id=@q_id
	update quarter_details set month_qty=@a1,m_total_qty=@a1 where qtr_id=@q_id and o_month=@s_date				
	update quarter_details set month_qty=@a2,m_total_qty=@a2 where qtr_id=@q_id and o_month=@m_date
	update quarter_details set month_qty=@a3,m_total_qty=@a3  where qtr_id=@q_id and o_month=@l_date
	end
else
	begin
		insert into inter_quarter (inter_id ,start_dt , total_qty,entry_dt,entry_by ) values(@iid,@s_date,@tot_qtr,getdate(),@uid)
		set @qq_id=@@identity
		insert into quarter_details (qtr_id,o_month,month_qty,m_total_qty ) values (@qq_id,@s_date,@a1,@a1)
		insert into quarter_details (qtr_id,o_month,month_qty,m_total_qty ) values (@qq_id,@m_date,@a2,@a2)
		insert into quarter_details (qtr_id,o_month,month_qty,m_total_qty ) values (@qq_id,@l_date,@a3,@a3)
	end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE licencing_adjustment_closing
(
	
	@adid numeric(28)	
)AS
	declare	
	
	@aie_id numeric (20),
	@order_id  varchar(50),
	@balqty numeric(20,8),
	@old_Bal_Qty numeric(20,8),
	@old_Act_Qty numeric(20,8),
	@total_cap_Qty numeric (28,0),
	@new_bal_Qty numeric (28,0),
	@ship_qty numeric (28,0),
@newadid numeric(28),
@aeid numeric(28),
@itemcode varchar(50)
declare a Cursor for  select  item_code,bal_qty from ad_item_det where ad_id=@adid
				open a
				fetch Next from a into @itemcode,@balqty
				while @@fetch_status=0
				begin
					if @balqty<>0				
						if @balqty<0 
							begin
								set @balqty=-(@balqty)
								declare b Cursor for select aie.bal_qty,aie.ad_id from ad_item_det aie,ad_lic_pro alp,inter_quarter iq,inter_master im 
								where alp.exp_close='Y'
								and (alp.ad_close<>'Y' or alp.ad_close is null)
								and aie.ad_id=alp.ad_id
								and iq.qtr_id=alp.qtr_id 
								and iq.inter_id=im.inter_id
								and aie.item_code=@itemcode
								and bal_qty > 0 
							
								open b
								fetch Next from b into @old_Bal_qty,@newadid		
							
								while @@fetch_status=0
								begin
										if @balqty<>0
										begin		
											if @old_Bal_qty>@balqty
											begin
												set @new_bal_Qty=@old_bal_qty-@balqty
												update ad_item_det set bal_qty=@new_bal_qty where ad_id=@newadid and item_code=@itemcode
												update ad_item_det set bal_qty=0 where ad_id=@adid and item_code=@itemcode
												insert into licence_adjustment (donor,receiver,qty,item_code) values (@newadid,@adid,@balqty,@itemcode)
												set @balqty=0
											
											end 
										else
											if @old_bal_qty<@balqty						
											begin
						
												set @new_bal_Qty=@balqty-@old_bal_qty
												update ad_item_det set bal_qty=0 where ad_id=@newadid and item_code=@itemcode
												update ad_item_det set bal_qty=-@new_bal_qty where ad_id=@adid and item_code=@itemcode
												insert into licence_adjustment (donor,receiver,qty,item_code) values (@newadid,@adid,@old_bal_qty,@itemcode)
												set @balqty=@new_bal_Qty
						
											end
										else
											if @old_bal_qty=@balqty
						
												begin												
													update ad_item_det set bal_qty=0 where ad_id=@newadid and item_code=@itemcode
													update ad_item_det set bal_qty=0 where ad_id=@adid and item_code=@itemcode
													insert into licence_adjustment (donor,receiver,qty,item_code) values (@newadid,@adid,@balqty,@itemcode)
													set @balqty=0													
												end
										end
								fetch Next from b into @old_Bal_qty,@newadid
								end 
								close b
								deallocate b
						
							end
						else
							if @balqty>0 
							begin								
								declare c Cursor for select aie.bal_qty,aie.ad_id from ad_item_det aie,ad_lic_pro alp,inter_quarter iq,inter_master im 
								where alp.exp_close='Y'
								and (alp.ad_close<>'Y'or alp.ad_close is null)
								and aie.ad_id=alp.ad_id
								and iq.qtr_id=alp.qtr_id 
								and iq.inter_id=im.inter_id
								and aie.item_code=@itemcode
								and bal_qty<0
								open c
								fetch Next from c into @old_Bal_qty,@newadid								
								while @@fetch_status=0
								begin
									if @balqty>0
									begin
										set @old_Bal_qty=-@old_Bal_qty
										if @old_Bal_qty>@balqty
											begin
												set @new_bal_Qty=@balqty-@old_bal_qty
												update ad_item_det set bal_qty=@new_bal_qty where ad_id=@newadid and item_code=@itemcode
												update ad_item_det set bal_qty=0 where ad_id=@adid and item_code=@itemcode
												insert into licence_adjustment (donor,receiver,qty,item_code) values (@adid,@newadid,@balqty,@itemcode)
												set @balqty=0
											end 
										else
											if @old_bal_qty<@balqty
												begin
													set @new_bal_Qty=@old_bal_qty-@balqty
													update ad_item_det set bal_qty=0 where ad_id=@newadid and item_code=@itemcode
													update ad_item_det set bal_qty=@new_bal_qty where ad_id=@adid and item_code=@itemcode
													insert into licence_adjustment (donor,receiver,qty,item_code) values (@adid,@newadid,@old_bal_qty,@itemcode)
													set @balqty=@new_bal_Qty
												end
											else
												if @old_bal_qty=@balqty
													begin														
														update ad_item_det set bal_qty=0 where ad_id=@newadid and item_code=@itemcode
														update ad_item_det set bal_qty=0 where ad_id=@adid and item_code=@itemcode
														insert into licence_adjustment (donor,receiver,qty,item_code) values (@adid,@newadid,@balqty,@itemcode)
														set @balqty=0
													end
									end
									fetch Next from c into @old_Bal_qty,@newadid
									
								end
								close c
								deallocate c
							end
					fetch Next from a into @itemcode,@balqty
				end 
				close a
			deallocate a

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE mat_req
(
	@dat datetime
)AS
	declare	
	@c_id numeric (28,0),	
	@c_name varchar (50),
	@o_qty_req numeric(25,6),  --- order quantity required material
	@c_number varchar(50),
	@plant_number varchar(50),
	@plant_id numeric(18),
	@plant_name varchar (50),
	@item_name varchar (50),		
	@val numeric(25,6),  ---no of batches ordered
	@qty numeric(25,6),  ---qty required per batch
	@um_des varchar (50),
	@item_type varchar(50),
	@item_code varchar(50),
	@op_bal numeric(25,6),
	@thisqty numeric(25,6),   ----quantity required from customer for this production month
	@inter numeric (18),---intermdiary product
	@ban_pro varchar(10),
	@tot numeric(5)
begin
delete from material_requirement
		
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,im.item_name,
	(total_qty/batch_size) val,b.qty,im.um_des,b.item_type,b.item_code,p.intermdiary,b.banner_provided
	from dom_order d,product_master p ,bom b,item_mast im,plant_master pl,customer_master cm
	where d.o_date=@dat
	and p.p_id=d.p_id
	and b.prod_no=p.prod_no
	and im.item_code=b.item_code
	and im.item_type=b.item_type
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@item_name ,@val ,@qty ,@um_des ,@item_type ,@item_code ,@inter,@ban_pro
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
			set @o_qty_req = @val * @qty
			select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
			and doc_date < @dat
			and cust_no=@c_number
			and location_id=@plant_number
			and item_type=@item_type
			and item_code=@item_code
			if @op_bal is  null				
				begin
				set @op_bal=0
				end
			if @op_bal is not null
				begin
				
				
				select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type
					
				if @tot>0 
					update material_requirement   set  [qty] =qty+@o_qty_req where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des and  [banner_provided]=@ban_pro
				else
					INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
					VALUES(@c_id,@plant_id,@item_code, @item_type, @o_qty_req, @op_bal,@um_des, @item_name, @plant_name, @c_name,@ban_pro)
				end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@item_name ,@val ,@qty ,@um_des ,@item_type ,@item_code ,@inter,@ban_pro
		end 
	close v
	deallocate v	
	declare a Cursor for select  cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,im.item_name,
	(qd.month_qty/p.batch_size) val,b.qty,im.um_des,b.item_type,b.item_code,p.intermdiary,b.banner_provided
	from inter_master i,inter_quarter iq,quarter_details qd,product_master p,bom b,item_mast im,plant_master pl,customer_master cm
	where i.inter_id=iq.inter_id and i.p_id=p.p_id and qd.qtr_id=iq.qtr_id 
	and o_month=@dat
	and b.prod_no=p.prod_no and im.item_code=b.item_code and im.item_type=b.item_type and pl.plant_id=p.plant and p.c_id=cm.c_id
	open a
	fetch Next from a into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@item_name ,@val ,@qty ,@um_des ,@item_type ,@item_code ,@inter,@ban_pro
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
			set @o_qty_req = @val * @qty
			select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
			and doc_date < @dat
			and cust_no=@c_number
			and location_id=@plant_number
			and item_type=@item_type
			and item_code=@item_code
			if @op_bal is not null
				set @op_bal=@op_bal
			else
				begin
				set @op_bal=0
				end
			if @op_bal is not null
				begin
				
				
				select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type
					
				if @tot>0 
					update material_requirement   set  [qty] =qty+@o_qty_req where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des and banner_provided=@ban_pro
				else
					INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
					VALUES(@c_id,@plant_id,@item_code, @item_type, @o_qty_req, @op_bal,@um_des, @item_name, @plant_name, @c_name,@ban_pro)
				end
			fetch Next from a into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@item_name ,@val ,@qty ,@um_des ,@item_type ,@item_code ,@inter,@ban_pro
		end 
	close a
	deallocate a	
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE memo_insert 
(
@subject varchar(50),
@uid numeric(20),
@memo varchar(1500),
@dtime datetime 
)
 AS
insert into memo (subject,uid,memo,entry_dt) 
values (@subject,@uid,@memo,@dtime)
return @@identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE new_mat_req
(
	@dat datetime
)AS
	declare	
	@o_l varchar(1),            		-----------		own / loan licence Product
	@banner_provided varchar(1),     -----------		Raw material Banner Provided
	@total_qty numeric(25,6),   	-----------		Total Order Qty for Month
	@p_id numeric(18),		-----------		Product Identity Number
	@prodno varchar(50),		-----------		Banner Provided Product Identity
	@batchSize numeric(18),		-----------		no of capsules in a batch
	@pckcd varchar(50),		-----------		ordered Packing materials 
	@catid numeric (8),		-----------		Product Category
	@wipqty numeric(18),		-----------		WIP Quantity
	@fgqty numeric (18),		-----------		Quantity in Finished Good Stock
	@totrmqty numeric(18),		-----------		Total Qty to be Produced in this month of production (respect to RM Required)
	@totpmqty Numeric(18),		-----------		Total Qty to be Produced in this month of production (respect to PM Required)
	@RMFtion numeric (8,3),		-----------		Total Quantity as Per Batch for RM
	@PMFtion numeric (8,3) ,	-----------		Total Quantity as Per Batch for RM
	@RminBatch numeric(8),		-----------		Minimum Batches to be Manufactured (respect to RM Required)
	@PminBatch numeric(8),		-----------		Minimum Batches to be Manufactured (respect to PM Required)
	@RmPBatch numeric(8,3),	-----------		Partial Batches to be Manufactured (respect to RM Required)
	@PmPBatch numeric(8,3),	-----------		Partial Batches to be Manufactured (respect to PM Required)
	@RmRound numeric(8,3),	----------		Round off of Partial Batch From cat master (respect to RM Required)
	@PmRound numeric(8,3),	----------		Round off of Partial Batch From cat master (respect toPM Required)
	@totRMBatch numeric(18,3),	----------		Total Batches Qty for Asking RM
	@totPMBatch numeric(18,3),	----------		Total Batches Qty for Asking PM
	@totRMReg numeric(18,3),	----------		Total RM Quantity required
	@totPMReg numeric(18,3),	----------		Total PM Quantity required
	@banpro varchar(2),		----------		Material banner Provided
	@c_id numeric (28,0),	
	@c_name varchar (50),
	@o_qty_req numeric(25,6),  --- order quantity required material
	@c_number varchar(50),
	@plant_number varchar(50),
	@plant_id numeric(18),
	@plant_name varchar (50),
	@item_name varchar (50),		
	@val numeric(25,6),  ---no of batches ordered
	@qty numeric(25,6),  ---qty required per batch
	@um_des varchar (50),
	@item_type varchar(50),
	@item_code varchar(50),
	@op_bal numeric(25,6),
	@thisqty numeric(25,6),   ----quantity required from customer for this production month
	@inter numeric (18),---intermdiary product
	@tot numeric(5)
begin
delete from material_requirement
	
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,d.total_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from dom_order d,product_master p ,plant_master pl,customer_master cm
	where d.o_date=@dat
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
-- select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)						
------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'			
			if @rmround is null
				set @rmround=1
--------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
	print @totrmbatch		
			if @totRMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type and banner_provided=@banpro					
						if @tot>0 
							update material_requirement   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des	 and banner_provided=@banpro		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro)
							end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
/*
			if @totPmqty > 0 
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
			
			set @PMFtion = @totPMqty/@batchSize
			set @Pminbatch=Floor(@totPMqty/@batchSize)
------------Partial Batch :-production Qty % batch size
					
			set @PmpBatch=@PMFtion-@Pminbatch			
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			select @PMRound=round_off from cat_master where cat_id=@catid and from_slab<=@PmpBatch and to_slab >=@PmpBatch --and item_type='P'
--------total raw material to be asked for batches=total complete batches+round off from category
			
			set @totPMBatch=@PminBatch+@PMRound
			if @totPMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_master i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin
				
						select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type
							
						if @tot>0 
							update material_requirement   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des
		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro)
							end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
*/
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
	
	declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,dod.act_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id,dod.pck_cd
	from dom_order d,dom_order_details dod,product_master p ,plant_master pl,customer_master cm
	where d.o_date=@dat
	and dod.o_id=d.o_id
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno and pck_cd=@pckcd
			if @fgqty is null
				set @fgqty=0
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totpmqty > 0  and @batchSize > 0 
			begin
			set @totPMBatch=@totpmqty/@batchSize
	print @totrmbatch		
			if @totPMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totPMBatch* b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='P' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type and b.pck_cd=@pckcd
				open RV
				fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin
				
						select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type and  [um_des]=@um_des and banner_provided=@banpro	
							
						if @tot>0 
							update material_requirement   set  [qty] =qty+@totPmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des and banner_provided=@banpro	
		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totPMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro)
							end						
					fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end			
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd
		end 
	close v
	deallocate v	
-------------------------------for international orders
	declare v Cursor for select  cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,
	qd.month_qty val,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id
	from inter_master i,inter_quarter iq,quarter_details qd,product_master p,plant_master pl,customer_master cm
	where i.inter_id=iq.inter_id and i.p_id=p.p_id and qd.qtr_id=iq.qtr_id 
	and o_month=@dat
	and pl.plant_id=p.plant and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
-- select For WIP
			select @wipqty=qty from prodn where prod_no=@prodno and stage_no<5 and stage_no = (select max(stage_no) from prodn where prod_no=@prodno and stage_no<5)
			if @wipqty is null
				set @wipqty=0
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno
			if @fgqty is null
				set @fgqty=0
--Raw Material :- Production quantity =totalqty-(wip+FGTR)
			set @totrmqty=@total_qty - (@wipqty+@fgqty)
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totrmqty > 0  and @batchSize >0
			begin
--------------total Complete batches :- Floor(production qty/batch Size)
			
			
				set @RMFtion = @totRMqty/@batchSize
				set @Rminbatch=Floor(@totRMqty/@batchSize)			
			
------------Partial Batch :-production Qty % batch size
			set @RmpBatch=@RMFtion-@Rminbatch
----------select round off value from category master where product is selected and from_slab<='partial batch' and to_slab>='partial_slab'
			if @catid is not null		
			select @RMRound=round_off from cat_master where cat_id=@catid and from_slab<=@RmpBatch and to_slab >=@RmpBatch --and item_type='R'
			
			if @rmround is null
				set @rmround=1
--------total raw material to be asked for batches=total complete batches+round off from category
			set @totRMBatch=@RminBatch+@RMRound
	print @totrmbatch		
			if @totRMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totRMBatch*b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='R' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type
				open RV
				fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin				
						select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type and  [um_des]=@um_des and banner_provided=@banpro	
						if @tot>0 
							update material_requirement   set  [qty] =qty+@totrmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des and banner_provided=@banpro	
						else
						begin
							select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
							and doc_date < @dat
							and cust_no=@c_number
							and location_id=@plant_number
							and item_type=@item_type
							and item_code=@item_code
							if @op_bal is  null				
								begin
								set @op_bal=0
								end
							INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
							VALUES(@c_id,@plant_id,@item_code, @item_type, @totRMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro)
						end						
					fetch Next from RV into @item_code,@totRMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid
		end 
	close v
	deallocate v	
/* Interantional Packing Material
declare v Cursor for select cm.c_name,cm.c_id,cm.c_number,pl.plant_number,pl.plant_id,pl.plant_name,dod.act_qty,p.p_id,p.prod_no,p.batch_size,p.intermdiary,p.cat_id,dod.pck_cd
	from dom_order d,dom_order_details dod,product_master p ,plant_master pl,customer_master cm
	where d.o_date=@dat
	and dod.o_id=d.o_id
	and d.p_id=p.p_id
	and pl.plant_id=p.plant
	and p.c_id=cm.c_id
	open v
	fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd
	print @@fetch_status
		while @@fetch_status=0
		begin
			begin
				if @inter<>0 
				set @c_id=@inter
				select @c_number=c_number from customer_master where c_id=@c_id
				select @c_name=c_name from customer_master where c_id=@c_id
			end
---Select For FGTR
			select @fgqty=sum(qty) from trans where doc_type in ('FGOPN','FGTRF','DC') and prod_no=@prodno and pck_cd=@pckcd
			if @fgqty is null
				set @fgqty=0
--Packing Material:- Production qty=totalqty-FGTR
			set @totpmqty = @total_qty - @fgqty
			if @totpmqty > 0  and @batchSize > 0 
			begin
			set @totPMBatch=@totpmqty/@batchSize
	print @totrmbatch		
			if @totPMBatch > 0  
			begin
---select (totalbatch*qty) required material from bom where prod_no is selected 
			declare RV Cursor for select b.item_code,(@totPMBatch* b.qty) qty,b.banner_provided, b.item_type ,i.um_des,i.item_name
						from bom b,item_mast i where b.item_type='P' and b.prod_no=@prodno
						and b.item_code=i.item_code and b.item_type=i.item_type and b.pck_cd=@pckcd
				open RV
				fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					while @@fetch_status=0
						begin
				
						select @tot=count(*) from material_requirement where c_id=@c_id and plant_id=@plant_id and item_code=@item_code and item_type=@item_type
							
						if @tot>0 
							update material_requirement   set  [qty] =qty+@totPmqty where [c_id]=@c_id and  [plant_id]=@plant_id and  [item_code]=@item_code and [item_type]=@item_type and  [um_des]=@um_des
		
						else
							begin
								select @op_bal=sum(qty) from trans where doc_type in ('OP','GRN','ISS','SAN','SAM')
								and doc_date < @dat
								and cust_no=@c_number
								and location_id=@plant_number
								and item_type=@item_type
								and item_code=@item_code
								if @op_bal is  null				
									begin
									set @op_bal=0
									end
								INSERT INTO material_requirement([c_id], [plant_id], [item_code], [item_type], [qty], op_bal,[um_des], [item_name], [plant_name], [c_name],banner_provided)
								VALUES(@c_id,@plant_id,@item_code, @item_type, @totPMqty, @op_bal,@um_des, @item_name, @plant_name, @c_name,@banpro)
							end						
					fetch Next from RV into @item_code,@totPMQty,@banpro,@item_type,@um_des,@item_name
					end 
				close RV
				deallocate RV	
			end
			end			
		fetch Next from v into @c_name,@c_id ,@c_number ,@plant_number ,@plant_id ,@plant_name ,@total_qty ,@p_id ,@prodno,@batchSize,@inter,@catid,@pckcd
		end 
	close v
	deallocate v	
*/
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE  opreation_insert (
@partent_id numeric(28),
@child_id numeric(28),
@child_check varchar (1),
@child_link varchar(1500),
@child_text varchar(1500),
@child_desc varchar (1500),
@pa varchar (1500),
@ext varchar(15)
) as
declare @path varchar
insert into operation_binder(partent_id,child_id,child_check,child_link,child_text,child_desc) values (@partent_id,
@child_id,
@child_check,
@child_link,
@child_text,
@child_desc)
set @path=@pa +"/"+@@identity+@ext
update operation_binder set child_id=@@identity,child_link=@path where slno=@@identity
return @@identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE track_insert
( @CAT_ID int ,
@USER_ID numeric (20),
@rtype varchar (50),
@req_head varchar (150) 
,@dtime datetime ,
@ass_user numeric (9),
@rpt_text varchar (1500),
@id int output
)
 AS
insert into track_MASTER
 (CAT_ID,req_by,req_type,req_close,dept_close,req_head,req_date,
assign_to,assign_dt) VALUES 
(@CAT_ID ,
@USER_ID ,
@rtype,
 'N',
'N', 
@req_head ,
@dtime ,
@ass_user ,
@dtime )
select @id = @@identity
insert into track_report (track_id,report_txt,user_id,report_dt)
 values 
(@@identity,@rpt_text,@user_id,@dtime)
return @id

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE track_report_insert
(@trackid int,
@report_text varchar (1500),
@user_id numeric ,
@dtime datetime
)
 AS
insert into track_report (track_id,report_txt,user_id,report_dt) 
values 
(@trackid,@report_text,@user_id,@dtime)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE user_bin
(
@c_id numeric (20),
@uid numeric (20),
@subf varchar (1),
@add_f varchar (1),
@edit_f varchar (1),
@down varchar (1)
)
as
	declare	@b_u_id	numeric (4),
		@act numeric (10,2)
select @b_u_id = b_u_id from user_binder where uid=@uid and child_id=@c_id
if @b_u_id is not null
	begin
		
				update user_binder set subf=@subf,add_f=@add_f,edit_f=@edit_f,download=@down where uid=@uid and child_id=@c_id
				
			
	end
else
	begin
		insert into user_binder (uid,child_id,subf,add_f,edit_f,download) values (@uid,@c_id,@subf,@add_f,@edit_f,@down)
	end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE user_insert 
(
@a varchar (50),
@b varchar (50),
@c varchar (50),
@u varchar (50),
@r varchar (50),
@uid numeric (20),
@d numeric (20),
@e varchar (50),
@g varchar (50)
) AS
insert into user_master (name,username,password,user_type,point_of_contact,parent,status,login_access,email_id)
 values
 (@a ,@b ,@c ,@u,@r,@uid,@d ,@e,@g)
return @@identity

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO