se/SE/se-lib/Route/FixCrmProcesInitIdx/fill__CRM_PROCES_idx_TEMP.sql

DROP TABLE IF EXISTS `CRM_PROCES_idx_TEMP`;
-- Mysql BUG: "Can't reopen table" -- CREATE TEMPORARY TABLE `CRM_PROCES_idx_TEMP` (
-- You cannot refer to a TEMPORARY table more than once in the same query.
CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx_TEMP` (
	`ID_PROCES` int(11) NOT NULL
	, `PARENT_ID` int(11) NOT NULL DEFAULT '0'
	, `TYPE` varchar(32) NOT NULL DEFAULT ''
	, `idx_PROCES_INIT_ID` int(11) NOT NULL DEFAULT '0'
	, `idx_MAIN_PROCES_INIT_ID` int(11) NULL DEFAULT '0'
	, `idx_PROCES_WITH_GROUPS_ID` int(11) NULL DEFAULT NULL
--	, `idx_PATH` text NOT NULL
	, KEY `ID_PROCES` ( `ID_PROCES` )
	, KEY `PARENT_ID` ( `PARENT_ID` )
	, KEY `idx_PROCES_INIT_ID` ( `idx_PROCES_INIT_ID` )
	, KEY `idx_MAIN_PROCES_INIT_ID` ( `idx_MAIN_PROCES_INIT_ID` )
	, KEY `idx_PROCES_WITH_GROUPS_ID` ( `idx_PROCES_WITH_GROUPS_ID` )
	, UNIQUE KEY `uniq__idx_PROCES_WITH_GROUPS_ID` ( `ID_PROCES`, `idx_PROCES_WITH_GROUPS_ID` )
)

-- I.1--truncate-table
truncate table `CRM_PROCES_idx_TEMP`;

-- I.2--fill-CRM_PROCES_idx_TEMP
insert into `CRM_PROCES_idx_TEMP` ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID` )
	select p.`ID` as `ID_PROCES`
		, p.`TYPE` as `TYPE`
		, p.`PARENT_ID` as `PARENT_ID`
		, IF( p.`TYPE` = 'PROCES_INIT', p.`ID`, 0 ) as `idx_PROCES_INIT_ID`
		, IF( p.`TYPE` = 'PROCES_INIT', p.`ID`, 0 ) as `idx_MAIN_PROCES_INIT_ID`
	from `CRM_PROCES` p
	where p.`A_STATUS` in ( 'NORMAL', 'WAITING' )
;