DROP PROCEDURE IF EXISTS `sp_setChartAccount`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_setChartAccount`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	BLOCK1: BEGIN
		DECLARE DONE_BLOCK1 INT DEFAULT 0;
		DECLARE CompanyID INT;
		DECLARE CURSOR_CompanyID CURSOR FOR
			SELECT
				company.ID
			FROM
				company
			WHERE
				company.is_delete = 0;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE_BLOCK1 = 1;
		
		TRUNCATE TABLE comp_chart_of_acct;
		
		OPEN CURSOR_CompanyID;
		REPEAT
		FETCH CURSOR_CompanyID INTO CompanyID;
		
		INSERT INTO comp_chart_of_acct (
			companyID,
			acctGroupID,
			acctCode,
			acctName,
			acctDesc,
			createdBy
		)
		SELECT
			CompanyID,
			_ref_chart_of_acct.acctGroupID,
			_ref_chart_of_acct.acctCode,
			_ref_chart_of_acct.acctName,
			_ref_chart_of_acct.acctDescription,
			CompanyID
		FROM _ref_chart_of_acct
		WHERE _ref_chart_of_acct.businessTypeID = 1;

		UNTIL DONE_BLOCK1 END REPEAT;
		CLOSE CURSOR_CompanyID;
	END BLOCK1;
END

//