DELIMITER $$
CREATE PROCEDURE sp_report(
	in vint_report_type int(11),
	in vdt_start date,
	in vdt_end date,
	in vint_product_id int(11),
	in vint_party_id int(11),
	out vintReturn INT)
BEGIN
	DECLARE txt_order_name varchar (100);
	DECLARE txt_party_name varchar (100);
	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;
	if (vint_report_type ='1') then
		SELECT int_purchase_order_id, 
		dt_transaction, 
		concat(txt_formatted_number,'/',int_number) into  txt_order_name, 
		tbl_purchase_order.int_party_id, 
		tbl_parties.txt_name into txt_party_name, 
		tbl_purchase_order.int_product_id, 
		tbl_products.txt_product_name, 
		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
		FROM tbl_purchase_order
		LEFT JOIN tbl_parties ON (tbl_purchase_order.int_party_id=tbl_parties.int_party_id)
		LEFT JOIN tbl_products ON (tbl_purchase_order.int_product_id=tbl_products.int_product_id)
		LEFT JOIN tbl_organisation ON (tbl_purchase_order.int_organisation_id=tbl_organisation.int_organisation_id)
		LEFT JOIN tbl_users ON (tbl_purchase_order.int_user_id=tbl_users.int_user_id)
        where 
        (dt_transaction  BETWEEN vdt_start and vdt_end )and 
        (int_party_id=vint_party_id) and 
        (int_product_id=vint_product_id);
	end if;
	
	COMMIT;

END$$
DELIMITER ;