DELIMITER $$
CREATE PROCEDURE sp_insert_financial_years(
	in vdt_start datetime,
	in vdt_end datetime,
	in vysn_current int(11),
	in vint_reactor_count int(11),
	in vint_coulumn_count int(11),
	in vint_drum_count int(11),
	in vint_purchase_count int(11),
	in vint_sale_count int(11),
	in vint_batch_no_count int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_financial_year_id),0)+1) into int_next_id FROM tbl_financial_years;
	INSERT INTO tbl_financial_years(
	int_financial_year_id, dt_start, dt_end, ysn_current, int_reactor_count, int_coulumn_count, int_drum_count, int_purchase_count, int_sale_count, int_batch_no_count)
	 VALUES (
	int_next_id, vdt_start, vdt_end, vysn_current, vint_reactor_count, vint_coulumn_count, vint_drum_count, vint_purchase_count, vint_sale_count, vint_batch_no_count);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_financial_years(
	in vint_financial_year_id int(11),
	in vdt_start datetime,
	in vdt_end datetime,
	in vysn_current int(11),
	in vint_reactor_count int(11),
	in vint_coulumn_count int(11),
	in vint_drum_count int(11),
	in vint_purchase_count int(11),
	in vint_sale_count int(11),
	in vint_batch_no_count int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_financial_years set
		dt_start=vdt_start, dt_end=vdt_end, ysn_current=vysn_current, int_reactor_count=vint_reactor_count, int_coulumn_count=vint_coulumn_count, int_drum_count=vint_drum_count, int_purchase_count=vint_purchase_count, int_sale_count=vint_sale_count, int_batch_no_count= vint_batch_no_count
	WHERE int_financial_year_id=vint_financial_year_id;
	COMMIT;
	SET vintReturn = vint_financial_year_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_financial_years(
	in vint_financial_year_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		DELETE FROM tbl_financial_years WHERE int_financial_year_id=vint_financial_year_id;
		SET vintReturn = vint_financial_year_id;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_organisation(
	in vtxt_name varchar(100),
	in vtxt_short_name varchar(25),
	in vtxt_contact_person varchar(60),
	in vtxt_address varchar(200),
	in vtxt_mobile varchar(25),
	in vtxt_mobile_whatsapp varchar(25),
	in vtxt_email varchar(100),
	in vtxt_gstn varchar(50),
	in vtxt_pan varchar(25),
	in vblb_logo mediumblob,
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_organisation_id),0)+1) into int_next_id FROM tbl_organisation;
	INSERT INTO tbl_organisation(
	int_organisation_id, txt_name, txt_short_name, txt_contact_person, txt_address, txt_mobile, txt_mobile_whatsapp, txt_email, txt_gstn, txt_pan, blb_logo)
	 VALUES (
	int_next_id, vtxt_name, vtxt_short_name, vtxt_contact_person, vtxt_address, vtxt_mobile, vtxt_mobile_whatsapp, vtxt_email, vtxt_gstn, vtxt_pan, vblb_logo);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_organisation(
	in vint_organisation_id int(11),
	in vtxt_name varchar(100),
	in vtxt_short_name varchar(25),
	in vtxt_contact_person varchar(60),
	in vtxt_address varchar(200),
	in vtxt_mobile varchar(25),
	in vtxt_mobile_whatsapp varchar(25),
	in vtxt_email varchar(100),
	in vtxt_gstn varchar(50),
	in vtxt_pan varchar(25),
	in vblb_logo mediumblob,
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_organisation set
		txt_name=vtxt_name, txt_short_name=vtxt_short_name, txt_contact_person=vtxt_contact_person, txt_address=vtxt_address, txt_mobile=vtxt_mobile, txt_mobile_whatsapp=vtxt_mobile_whatsapp, txt_email=vtxt_email, txt_gstn=vtxt_gstn, txt_pan=vtxt_pan, blb_logo= vblb_logo
	WHERE int_organisation_id=vint_organisation_id;
	COMMIT;
	SET vintReturn = vint_organisation_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_organisation(
	in vint_organisation_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_organisation_id) AS int_count FROM tbl_employee WHERE int_organisation_id=vint_organisation_id)
		 UNION ALL
		(Select count(int_organisation_id) AS int_count FROM tbl_employee WHERE int_organisation_id=vint_organisation_id)
		 UNION ALL
		(Select count(int_organisation_id) AS int_count FROM tbl_production WHERE int_organisation_id=vint_organisation_id)
		 UNION ALL
		(Select count(int_organisation_id) AS int_count FROM tbl_purchase_order WHERE int_organisation_id=vint_organisation_id)
		 UNION ALL
		(Select count(int_organisation_id) AS int_count FROM tbl_sales_order WHERE int_organisation_id=vint_organisation_id)
		 UNION ALL
		(Select count(int_organisation_id) AS int_count FROM tbl_stock_movement WHERE int_organisation_id=vint_organisation_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_organisation WHERE int_organisation_id=vint_organisation_id;
			SET vintReturn = vint_organisation_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_parties(
	in vtxt_name varchar(100),
	in vtxt_short_name varchar(25),
	in vtxt_contact_person varchar(60),
	in vtxt_address varchar(200),
	in vtxt_city varchar(25),
	in vtxt_mobile varchar(25),
	in vtxt_mobile_whatsapp varchar(25),
	in vtxt_email varchar(100),
	in vtxt_gstn varchar(50),
	in vtxt_pan varchar(25),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_party_id),0)+1) into int_next_id FROM tbl_parties;
	INSERT INTO tbl_parties(
	int_party_id, txt_name, txt_short_name, txt_contact_person, txt_address, txt_city, txt_mobile, txt_mobile_whatsapp, txt_email, txt_gstn, txt_pan, int_user_id)
	 VALUES (
	int_next_id, vtxt_name, vtxt_short_name, vtxt_contact_person, vtxt_address, vtxt_city, vtxt_mobile, vtxt_mobile_whatsapp, vtxt_email, vtxt_gstn, vtxt_pan, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_parties(
	in vint_party_id int(11),
	in vtxt_name varchar(100),
	in vtxt_short_name varchar(25),
	in vtxt_contact_person varchar(60),
	in vtxt_address varchar(200),
	in vtxt_city varchar(25),
	in vtxt_mobile varchar(25),
	in vtxt_mobile_whatsapp varchar(25),
	in vtxt_email varchar(100),
	in vtxt_gstn varchar(50),
	in vtxt_pan varchar(25),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_parties set
		txt_name=vtxt_name, txt_short_name=vtxt_short_name, txt_contact_person=vtxt_contact_person, txt_address=vtxt_address, txt_city=vtxt_city, txt_mobile=vtxt_mobile, txt_mobile_whatsapp=vtxt_mobile_whatsapp, txt_email=vtxt_email, txt_gstn=vtxt_gstn, txt_pan=vtxt_pan, int_user_id= vint_user_id
	WHERE int_party_id=vint_party_id;
	COMMIT;
	SET vintReturn = vint_party_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_parties(
	in vint_party_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_agent_party_id) AS int_count FROM tbl_purchase_order WHERE int_agent_party_id=vint_party_id)
		 UNION ALL
		(Select count(int_party_id) AS int_count FROM tbl_purchase_order WHERE int_party_id=vint_party_id)
		 UNION ALL
		(Select count(int_agent_party_id) AS int_count FROM tbl_sales_order WHERE int_agent_party_id=vint_party_id)
		 UNION ALL
		(Select count(int_party_id) AS int_count FROM tbl_sales_order WHERE int_party_id=vint_party_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_parties WHERE int_party_id=vint_party_id;
			SET vintReturn = vint_party_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_production(
	in vint_production_unit_id int(11),
	in vtxt_batch_no varchar(25),
	in vdt_start datetime,
	in vdt_end datetime,
	in vint_organisation_id int(11),
	in vtxt_remarks varchar(250),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_production_id),0)+1) into int_next_id FROM tbl_production;
	INSERT INTO tbl_production(
	int_production_id, int_production_unit_id, txt_batch_no, dt_start, dt_end, int_organisation_id, txt_remarks, int_user_id)
	 VALUES (
	int_next_id, vint_production_unit_id, vtxt_batch_no, vdt_start, vdt_end, vint_organisation_id, vtxt_remarks, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_production(
	in vint_production_id int(11),
	in vint_production_unit_id int(11),
	in vtxt_batch_no varchar(25),
	in vdt_start datetime,
	in vdt_end datetime,
	in vint_organisation_id int(11),
	in vtxt_remarks varchar(250),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_production set
		int_production_unit_id=vint_production_unit_id, txt_batch_no=vtxt_batch_no, dt_start=vdt_start, dt_end=vdt_end, int_organisation_id=vint_organisation_id, txt_remarks=vtxt_remarks, int_user_id= vint_user_id
	WHERE int_production_id=vint_production_id;
	COMMIT;
	SET vintReturn = vint_production_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_production(
	in vint_production_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_source_production_id) AS int_count FROM tbl_stock_movement WHERE int_source_production_id=vint_production_id)
		 UNION ALL
		(Select count(int_target_production_id) AS int_count FROM tbl_stock_movement WHERE int_target_production_id=vint_production_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_production WHERE int_production_id=vint_production_id;
			SET vintReturn = vint_production_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_production_units(
	in vtxt_production_unit varchar(100),
	in vint_production_unit_type int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_production_unit_id),0)+1) into int_next_id FROM tbl_production_units;
	INSERT INTO tbl_production_units(
	int_production_unit_id, txt_production_unit, int_production_unit_type, int_user_id)
	 VALUES (
	int_next_id, vtxt_production_unit, vint_production_unit_type, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_production_units(
	in vint_production_unit_id int(11),
	in vtxt_production_unit varchar(100),
	in vint_production_unit_type int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_production_units set
		txt_production_unit=vtxt_production_unit, int_production_unit_type=vint_production_unit_type, int_user_id= vint_user_id
	WHERE int_production_unit_id=vint_production_unit_id;
	COMMIT;
	SET vintReturn = vint_production_unit_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_production_units(
	in vint_production_unit_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_production_unit_id) AS int_count FROM tbl_production WHERE int_production_unit_id=vint_production_unit_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_production_units WHERE int_production_unit_id=vint_production_unit_id;
			SET vintReturn = vint_production_unit_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_products(
	in vtxt_product_name varchar(100),
	in vtxt_hsn_code varchar(25),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_product_id),0)+1) into int_next_id FROM tbl_products;
	INSERT INTO tbl_products(
	int_product_id, txt_product_name, txt_hsn_code, int_user_id)
	 VALUES (
	int_next_id, vtxt_product_name, vtxt_hsn_code, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_products(
	in vint_product_id int(11),
	in vtxt_product_name varchar(100),
	in vtxt_hsn_code varchar(25),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_products set
		txt_product_name=vtxt_product_name, txt_hsn_code=vtxt_hsn_code, int_user_id= vint_user_id
	WHERE int_product_id=vint_product_id;
	COMMIT;
	SET vintReturn = vint_product_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_products(
	in vint_product_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_product_id) AS int_count FROM tbl_purchase_order WHERE int_product_id=vint_product_id)
		 UNION ALL
		(Select count(int_product_id) AS int_count FROM tbl_sales_order WHERE int_product_id=vint_product_id)
		 UNION ALL
		(Select count(int_product_id) AS int_count FROM tbl_stock_movement WHERE int_product_id=vint_product_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_products WHERE int_product_id=vint_product_id;
			SET vintReturn = vint_product_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_purchase_order(
	in vdt_transaction date,
	in vint_number int(11),
	in vtxt_formatted_number varchar(25),
	in vint_party_id int(11),
	in vint_product_id int(11),
	in vint_agent_party_id int(11),
	in vtxt_party_document_no varchar(100),
	in vdbl_rate decimal(15,2),
	in vdbl_total_gross_weight decimal(15,3),
	in vdbl_total_tare_weight decimal(15,3),
	in vdbl_total_net_weight decimal(15,3),
	in vdbl_total_product_value decimal(15,2),
	in vdbl_total_freight_value decimal(15,2),
	in vdbl_total_tax_value decimal(15,2),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_purchase_order_id),0)+1) into int_next_id FROM tbl_purchase_order;
	if (vint_number is null) then
		SELECT (ifnull(MAX(int_number),0)+1) into vint_number FROM tbl_purchase_order;
	end if;
	SELECT YEAR(DATE_SUB(vdt_transaction,INTERVAL -3 MONTH)) into int_fy;
	Select concat( int_fy, '-',right((int_fy+1) ,2))  into vtxt_formatted_number;
	INSERT INTO tbl_purchase_order(
	int_purchase_order_id, dt_transaction, int_number, txt_formatted_number, int_party_id, int_product_id, int_agent_party_id, txt_party_document_no, dbl_rate, dbl_total_gross_weight, dbl_total_tare_weight, dbl_total_net_weight, dbl_total_product_value, dbl_total_freight_value, dbl_total_tax_value, txt_remarks, int_organisation_id, int_user_id)
	 VALUES (
	int_next_id, vdt_transaction, vint_number, vtxt_formatted_number, vint_party_id, vint_product_id, vint_agent_party_id, vtxt_party_document_no, vdbl_rate, vdbl_total_gross_weight, vdbl_total_tare_weight, vdbl_total_net_weight, vdbl_total_product_value, vdbl_total_freight_value, vdbl_total_tax_value, vtxt_remarks, vint_organisation_id, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_purchase_order(
	in vint_purchase_order_id int(11),
	in vdt_transaction date,
	in vint_number int(11),
	in vtxt_formatted_number varchar(25),
	in vint_party_id int(11),
	in vint_product_id int(11),
	in vint_agent_party_id int(11),
	in vtxt_party_document_no varchar(100),
	in vdbl_rate decimal(15,2),
	in vdbl_total_gross_weight decimal(15,3),
	in vdbl_total_tare_weight decimal(15,3),
	in vdbl_total_net_weight decimal(15,3),
	in vdbl_total_product_value decimal(15,2),
	in vdbl_total_freight_value decimal(15,2),
	in vdbl_total_tax_value decimal(15,2),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_purchase_order set
		dt_transaction=vdt_transaction,  int_party_id=vint_party_id, int_product_id=vint_product_id, int_agent_party_id=vint_agent_party_id, txt_party_document_no=vtxt_party_document_no, dbl_rate=vdbl_rate, dbl_total_gross_weight=vdbl_total_gross_weight, dbl_total_tare_weight=vdbl_total_tare_weight, dbl_total_net_weight=vdbl_total_net_weight, dbl_total_product_value=vdbl_total_product_value, dbl_total_freight_value=vdbl_total_freight_value, dbl_total_tax_value=vdbl_total_tax_value, txt_remarks=vtxt_remarks, int_organisation_id=vint_organisation_id, int_user_id= vint_user_id
	WHERE int_purchase_order_id=vint_purchase_order_id;
	COMMIT;
	SET vintReturn = vint_purchase_order_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_purchase_order(
	in vint_purchase_order_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_purchase_order_id) AS int_count FROM tbl_purchase_order_detail WHERE int_purchase_order_id=vint_purchase_order_id)
		 UNION ALL
		(Select count(int_purchase_order_id) AS int_count FROM tbl_purchase_order_detail WHERE int_purchase_order_id=vint_purchase_order_id)
		 UNION ALL
		(Select count(int_purchase_order_id) AS int_count FROM tbl_purchase_order_detail WHERE int_purchase_order_id=vint_purchase_order_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_purchase_order WHERE int_purchase_order_id=vint_purchase_order_id;
			SET vintReturn = vint_purchase_order_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_purchase_order_detail(
	in vint_purchase_order_id int(11),
	in vdbl_gross_weight decimal(15,3),
	in vdbl_tare_weight decimal(15,3),
	in vdbl_net_weight decimal(15,3),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_purchase_order_detail_id),0)+1) into int_next_id FROM tbl_purchase_order_detail;
	INSERT INTO tbl_purchase_order_detail(
	int_purchase_order_detail_id, int_purchase_order_id, dbl_gross_weight, dbl_tare_weight, dbl_net_weight, int_user_id)
	 VALUES (
	int_next_id, vint_purchase_order_id, vdbl_gross_weight, vdbl_tare_weight, vdbl_net_weight, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_purchase_order_detail(
	in vint_purchase_order_detail_id int(11),
	in vint_purchase_order_id int(11),
	in vdbl_gross_weight decimal(15,3),
	in vdbl_tare_weight decimal(15,3),
	in vdbl_net_weight decimal(15,3),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_purchase_order_detail set
		int_purchase_order_id=vint_purchase_order_id, dbl_gross_weight=vdbl_gross_weight, dbl_tare_weight=vdbl_tare_weight, dbl_net_weight=vdbl_net_weight, int_user_id= vint_user_id
	WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id;
	COMMIT;
	SET vintReturn = vint_purchase_order_detail_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_purchase_order_detail(
	in vint_purchase_order_detail_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_purchase_order_detail_id) AS int_count FROM tbl_purchase_order_tax WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id)
		 UNION ALL
		(Select count(int_purchase_order_detail_id) AS int_count FROM tbl_purchase_receive_detail WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id)
		 UNION ALL
		(Select count(int_purchase_order_detail_id) AS int_count FROM tbl_purchase_order_tax WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id)
		 UNION ALL
		(Select count(int_purchase_order_detail_id) AS int_count FROM tbl_purchase_receive_detail WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id)
		 UNION ALL
		(Select count(int_source_purchase_detail_id) AS int_count FROM tbl_stock_movement WHERE int_source_purchase_detail_id=vint_purchase_order_detail_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_purchase_order_detail WHERE int_purchase_order_detail_id=vint_purchase_order_detail_id;
			SET vintReturn = vint_purchase_order_detail_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_sales_order(
	in vdt_transaction date,
	in vint_number int(11),
	in vtxt_formatted_number varchar(25),
	in vint_party_id int(11),
	in vint_product_id int(11),
	in vint_agent_party_id int(11),
	in vdbl_rate decimal(15,2),
	in vdbl_total_weight decimal(15,3),
	in vdbl_total_product_value decimal(15,2),
	in vdbl_total_freight_value decimal(15,2),
	in vdbl_total_tax_value decimal(15,2),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_sales_order_id),0)+1) into int_next_id FROM tbl_sales_order;
	if (vint_number is null) then
		SELECT (ifnull(MAX(int_number),0)+1) into vint_number FROM tbl_sales_order;
	end if;
	SELECT YEAR(DATE_SUB(vdt_transaction,INTERVAL -3 MONTH)) into int_fy;
	Select concat( int_fy, '-',right((int_fy+1) ,2))  into vtxt_formatted_number;
	INSERT INTO tbl_sales_order(
	int_sales_order_id, dt_transaction, int_number, txt_formatted_number, int_party_id, int_product_id, int_agent_party_id, dbl_rate, dbl_total_weight, dbl_total_product_value, dbl_total_freight_value, dbl_total_tax_value, txt_remarks, int_organisation_id, int_user_id)
	 VALUES (
	int_next_id, vdt_transaction, vint_number, vtxt_formatted_number, vint_party_id, vint_product_id, vint_agent_party_id, vdbl_rate, vdbl_total_weight, vdbl_total_product_value, vdbl_total_freight_value, vdbl_total_tax_value, vtxt_remarks, vint_organisation_id, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_sales_order(
	in vint_sales_order_id int(11),
	in vdt_transaction date,
	in vint_number int(11),
	in vtxt_formatted_number varchar(25),
	in vint_party_id int(11),
	in vint_product_id int(11),
	in vint_agent_party_id int(11),
	in vdbl_rate decimal(15,2),
	in vdbl_total_weight decimal(15,3),
	in vdbl_total_product_value decimal(15,2),
	in vdbl_total_freight_value decimal(15,2),
	in vdbl_total_tax_value decimal(15,2),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_sales_order set
		dt_transaction=vdt_transaction,  int_party_id=vint_party_id, int_product_id=vint_product_id, int_agent_party_id=vint_agent_party_id, dbl_rate=vdbl_rate, dbl_total_weight=vdbl_total_weight, dbl_total_product_value=vdbl_total_product_value, dbl_total_freight_value=vdbl_total_freight_value, dbl_total_tax_value=vdbl_total_tax_value, txt_remarks=vtxt_remarks, int_organisation_id=vint_organisation_id, int_user_id= vint_user_id
	WHERE int_sales_order_id=vint_sales_order_id;
	COMMIT;
	SET vintReturn = vint_sales_order_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_sales_order(
	in vint_sales_order_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_sales_order_id) AS int_count FROM tbl_purchase_order WHERE int_sales_order_id=vint_sales_order_id)
		 UNION ALL
		(Select count(int_sales_order_id) AS int_count FROM tbl_sales_order_detail WHERE int_sales_order_id=vint_sales_order_id)
		 UNION ALL
		(Select count(int_sales_order_id) AS int_count FROM tbl_purchase_order WHERE int_sales_order_id=vint_sales_order_id)
		 UNION ALL
		(Select count(int_sales_order_id) AS int_count FROM tbl_sales_order_detail WHERE int_sales_order_id=vint_sales_order_id)
		 UNION ALL
		(Select count(int_target_sales_order_id) AS int_count FROM tbl_stock_movement WHERE int_target_sales_order_id=vint_sales_order_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_sales_order WHERE int_sales_order_id=vint_sales_order_id;
			SET vintReturn = vint_sales_order_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_stock_movement(
	in vdt_transaction date,
	in vint_product_id int(11),
	in vdbl_quantity decimal(15,3),
	in vint_source_purchase_id int(11),
	in vint_source_storage_unit_id int(11),
	in vint_source_production_id int(11),
	in vtxt_source_drum_tag varchar(25),
	in vint_opening_id int(11),
	in vint_target_sales_order_id int(11),
	in vint_target_storage_unit_id int(11),
	in vint_target_production_id int(11),
	in vtxt_target_drum_tag varchar(25),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_stock_movement_id),0)+1) into int_next_id FROM tbl_stock_movement;
	INSERT INTO tbl_stock_movement(
	int_stock_movement_id, dt_transaction, int_product_id, dbl_quantity, int_source_purchase_id, int_source_storage_unit_id, int_source_production_id, txt_source_drum_tag, int_opening_id, int_target_sales_order_id, int_target_storage_unit_id, int_target_production_id, txt_target_drum_tag, txt_remarks, int_organisation_id, int_user_id)
	 VALUES (
	int_next_id, vdt_transaction, vint_product_id, vdbl_quantity, vint_source_purchase_id, vint_source_storage_unit_id, vint_source_production_id, vtxt_source_drum_tag, vint_opening_id, vint_target_sales_order_id, vint_target_storage_unit_id, vint_target_production_id, vtxt_target_drum_tag, vtxt_remarks, vint_organisation_id, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_stock_movement(
	in vint_stock_movement_id int(11),
	in vdt_transaction date,
	in vint_product_id int(11),
	in vdbl_quantity decimal(15,3),
	in vint_source_purchase_id int(11),
	in vint_source_storage_unit_id int(11),
	in vint_source_production_id int(11),
	in vtxt_source_drum_tag varchar(25),
	in vint_opening_id int(11),
	in vint_target_sales_order_id int(11),
	in vint_target_storage_unit_id int(11),
	in vint_target_production_id int(11),
	in vtxt_target_drum_tag varchar(25),
	in vtxt_remarks varchar(250),
	in vint_organisation_id int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_stock_movement set
		dt_transaction=vdt_transaction, int_product_id=vint_product_id, dbl_quantity=vdbl_quantity, int_source_purchase_id=vint_source_purchase_id, int_source_storage_unit_id=vint_source_storage_unit_id, int_source_production_id=vint_source_production_id, txt_source_drum_tag=vtxt_source_drum_tag, int_opening_id=vint_opening_id, int_target_sales_order_id=vint_target_sales_order_id, int_target_storage_unit_id=vint_target_storage_unit_id, int_target_production_id=vint_target_production_id, txt_target_drum_tag=vtxt_target_drum_tag, txt_remarks=vtxt_remarks, int_organisation_id=vint_organisation_id, int_user_id= vint_user_id
	WHERE int_stock_movement_id=vint_stock_movement_id;
	COMMIT;
	SET vintReturn = vint_stock_movement_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_stock_movement(
	in vint_stock_movement_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		DELETE FROM tbl_stock_movement WHERE int_stock_movement_id=vint_stock_movement_id;
		SET vintReturn = vint_stock_movement_id;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_insert_storage_units(
	in vtxt_storage varchar(100),
	in vint_storage_capacity int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_storage_unit_id),0)+1) into int_next_id FROM tbl_storage_units;
	INSERT INTO tbl_storage_units(
	int_storage_unit_id, txt_storage, int_storage_capacity, int_user_id)
	 VALUES (
	int_next_id, vtxt_storage, vint_storage_capacity, vint_user_id);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_storage_units(
	in vint_storage_unit_id int(11),
	in vtxt_storage varchar(100),
	in vint_storage_capacity int(11),
	in vint_user_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_storage_units set
		txt_storage=vtxt_storage, int_storage_capacity=vint_storage_capacity, int_user_id= vint_user_id
	WHERE int_storage_unit_id=vint_storage_unit_id;
	COMMIT;
	SET vintReturn = vint_storage_unit_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_storage_units(
	in vint_storage_unit_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_source_storage_unit_id) AS int_count FROM tbl_stock_movement WHERE int_source_storage_unit_id=vint_storage_unit_id)
		 UNION ALL
		(Select count(int_target_storage_unit_id) AS int_count FROM tbl_stock_movement WHERE int_target_storage_unit_id=vint_storage_unit_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_storage_units WHERE int_storage_unit_id=vint_storage_unit_id;
			SET vintReturn = vint_storage_unit_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_insert_users(
	in vtxt_name varchar(100),
	in vtxt_password varchar(100),
	in vdb_role_type smallint(6),
	in vallowed_login int(11),
	in vdt_created datetime,
	in vdt_modified datetime,
	in vby_user int(11),
	out vintReturn INT)
BEGIN
	DECLARE int_next_id int;
	DECLARE int_fy int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	SELECT (ifnull(MAX(int_user_id),0)+1) into int_next_id FROM tbl_users;
	INSERT INTO tbl_users(
	int_user_id, txt_name, txt_password, db_role_type, allowed_login, dt_created, dt_modified, by_user)
	 VALUES (
	int_next_id, vtxt_name, vtxt_password, vdb_role_type, vallowed_login, vdt_created, vdt_modified, vby_user);
	COMMIT;
SET vintReturn = int_next_id;
SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_update_users(
	in vint_user_id int(11),
	in vtxt_name varchar(100),
	in vtxt_password varchar(100),
	in vdb_role_type smallint(6),
	in vallowed_login int(11),
	in vdt_created datetime,
	in vdt_modified datetime,
	in vby_user int(11),
	out vintReturn INT)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
	UPDATE tbl_users set
		txt_name=vtxt_name, txt_password=vtxt_password, db_role_type=vdb_role_type, allowed_login=vallowed_login, dt_created=vdt_created, dt_modified=vdt_modified, by_user= vby_user
	WHERE int_user_id=vint_user_id;
	COMMIT;
	SET vintReturn = vint_user_id;
	SELECT vintReturn;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_delete_users(
	in vint_user_id INT,
	out vintReturn INT)
BEGIN
	DECLARE int_ref_count int;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	DECLARE EXIT HANDLER FOR SQLWARNING
	BEGIN
		SET vintReturn = 0;
		SHOW ERRORS;
		ROLLBACK;
	END;
	START TRANSACTION;
		SELECT SUM(int_count) INTO int_ref_count FROM (
		(Select count(int_user_id) AS int_count FROM tbl_parties WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_production WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_production_units WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_products WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_purchase_order WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_purchase_order_detail WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_sales_order WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_stock_movement WHERE int_user_id=vint_user_id)
		 UNION ALL
		(Select count(int_user_id) AS int_count FROM tbl_storage_units WHERE int_user_id=vint_user_id)
		 ) qry; 
		IF(int_ref_count> 0) THEN 
			SET vintReturn = 0;
		ELSE 
			DELETE FROM tbl_users WHERE int_user_id=vint_user_id;
			SET vintReturn = vint_user_id;
		END IF;
	COMMIT;
	SELECT vintReturn;
END$$
DELIMITER ;
