
CREATE OR REPLACE TYPE numset_t IS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION generate_series(x NUMBER,y NUMBER) 
RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN x..y LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
/


CREATE OR REPLACE VIEW cfdat_config_command_list AS
	SELECT	cmd,dest,addr,data,
		cfg_id,
   		cfdat_config_scripts.seq AS scr_seq,
		cfdat_command.seq AS cmd_seq
        FROM cfdat_config_scripts,cfdat_command
        WHERE cfdat_config_scripts.script_id=cfdat_command.script_id;



CREATE OR REPLACE VIEW mcm_latest_loc AS
	SELECT mcm_id,rob_id,rob_pos,latest_mcm_loctime FROM (
	SELECT mcm_id,rob_id,rob_pos,mcm_loctime AS latest_mcm_loctime FROM mcm_loc WHERE (rob_id,rob_pos,mcm_loctime) IN (SELECT rob_id,rob_pos,MAX(mcm_loctime) FROM mcm_loc WHERE mcm_id IN 
		(SELECT mcm_id FROM mcm)
	GROUP BY rob_id,rob_pos) ORDER BY latest_mcm_loctime DESC);

CREATE OR REPLACE VIEW rob_latest_loc AS
	SELECT rob_id,roc_type,roc_serial,roc_pos,latest_rob_loctime FROM (
	SELECT rob_id,roc_type,roc_serial,roc_pos,rob_loctime AS latest_rob_loctime FROM rob_loc WHERE (roc_type,roc_serial,roc_pos,rob_loctime) IN (SELECT roc_type,roc_serial,roc_pos,MAX(rob_loctime) FROM rob_loc WHERE rob_id IN 
		(SELECT rob_id FROM rob)
	GROUP BY roc_type,roc_serial,roc_pos) ORDER BY latest_rob_loctime DESC);

CREATE OR REPLACE VIEW roc_latest_loc AS
	   SELECT roc_type,roc_serial,dcs_id,sm_id,sm_stack,sm_layer,latest_roc_loctime FROM (
	   SELECT roc_type,roc_serial,dcs_id,sm_id,sm_stack,sm_layer,roc_loctime AS latest_roc_loctime FROM roc_loc WHERE (sm_id,sm_stack,sm_layer,roc_loctime) IN (( 
   SELECT sm_id,sm_stack,sm_layer,MAX(roc_loctime)  FROM roc_loc WHERE (roc_type,roc_serial) IN 
		(SELECT roc_type,roc_serial FROM roc)
	GROUP BY sm_id,sm_stack,sm_layer)) ORDER BY latest_roc_loctime);

CREATE OR REPLACE VIEW sm_latest_loc AS
	SELECT sm_id,sm_slot,latest_sm_loctime FROM (SELECT sm_id,sm_slot,sm_loctime AS latest_sm_loctime FROM sm_loc WHERE (sm_slot,sm_loctime) IN (SELECT sm_slot,MAX(sm_loctime) FROM sm_loc WHERE (sm_id) IN 	
		(SELECT sm_id FROM sm) 	GROUP BY sm_slot) ORDER by latest_sm_loctime DESC);

CREATE OR REPLACE VIEW ori_latest_loc AS
	SELECT ori_id,roc_type,roc_serial,side,latest_ori_loctime FROM (
	SELECT ori_id,roc_type,roc_serial,side,ori_loctime AS latest_ori_loctime FROM ori_loc WHERE (roc_type,roc_serial,side,ori_loctime) IN (SELECT roc_type,roc_serial,side,MAX(ori_loctime) FROM ori_loc WHERE ori_id IN 
		(SELECT ori_id FROM ori)
	GROUP BY roc_type,roc_serial,side) ORDER BY latest_ori_loctime DESC);






CREATE OR REPLACE VIEW fee AS
        SELECT  'trd-fee_' || to_char(sm_slot,'FM09')
	|| '_' || sm_stack || '_' || sm_layer AS servername,
	sm_slot AS sm,
	sm_stack AS stack,
	sm_layer AS layer
	FROM roc,roc_latest_loc,sm_latest_loc
	WHERE dcs_id IS NOT NULL;


CREATE OR REPLACE VIEW feeservices AS
	SELECT servername || '_STATE' AS service, 'int' AS type FROM fee
	UNION ALL
	SELECT servername || '_T_ENV' AS service, 'float' AS type  FROM fee
	UNION ALL
	SELECT servername || '_T_MCM_' ||  to_char(COLUMN_VALUE,'FM09') AS service, 'float' AS type  
	FROM fee,TABLE(generate_series(0,15))
	WHERE stack!=2
	UNION ALL
	SELECT servername || '_T_MCM_' ||  to_char(COLUMN_VALUE,'FM09') AS service, 'float' AS type  
	FROM fee,TABLE(generate_series(0,11))
	WHERE stack=2
	UNION ALL
	SELECT servername || '_V_' || vname, 'float' AS type  
	FROM fee,(SELECT 'D3V3' AS vname FROM DUAL UNION 
		  SELECT 'A3V3' AS vname FROM DUAL UNION 
		  SELECT 'D1V8' AS vname FROM DUAL UNION 
		  SELECT 'A1V8' AS vname FROM DUAL UNION 
		  SELECT 'GND'  AS vname FROM DUAL UNION 
	          SELECT 'AGND' AS vname FROM DUAL )
	WHERE (stack <= 1 AND (sm <= 11 OR  sm >= 16)) OR
	      (stack >= 3 AND (sm >= 12 AND sm <= 15));




CREATE OR REPLACE VIEW rob_verbose AS
	SELECT 	rob_latest_loc.rob_id AS rob_id, 
		roc_latest_loc.roc_type AS roc_type, roc_latest_loc.roc_serial AS roc_serial, roc_pos,
		sm_latest_loc.sm_id AS sm_id, sm_slot, sm_stack, sm_layer,
		100*sm_slot + 10*sm_stack + sm_layer AS sm_s_l
	FROM rob_latest_loc,roc_latest_loc,sm_latest_loc
	WHERE rob_latest_loc.roc_type=roc_latest_loc.roc_type AND rob_latest_loc.roc_serial=roc_latest_loc.roc_serial AND roc_latest_loc.sm_id=sm_latest_loc.sm_id;



CREATE OR REPLACE VIEW ori_verbose AS
	SELECT 	ori_id,	
		roc_latest_loc.roc_type AS roc_type, roc_latest_loc.roc_serial AS roc_serial, side,
		sm_latest_loc.sm_id AS sm_id, sm_slot, sm_stack, sm_layer,
		100*sm_slot + 10*sm_stack + sm_layer AS sm_s_l
	FROM ori_latest_loc,roc_latest_loc,sm_latest_loc
	WHERE ori_latest_loc.roc_type=roc_latest_loc.roc_type AND ori_latest_loc.roc_serial=roc_latest_loc.roc_serial AND roc_latest_loc.sm_id=sm_latest_loc.sm_id;





CREATE OR REPLACE VIEW err_mcm_verbose AS
	SELECT	rob_verbose.rob_id, 
		sm_slot AS sm, sm_stack AS stack, sm_layer AS layer, 
		roc_pos AS rob, rob_pos AS mcm, status,
		begin_time, 
		( SELECT MIN(begin_time) AS end_time
		  FROM cferr_mcm err1
		  WHERE err1.rob_id=err2.rob_id 
		    AND err1.rob_pos=err2.rob_pos
		    AND err1.begin_time>err2.begin_time
		) AS end_time
	FROM rob_verbose, cferr_mcm err2
	WHERE rob_verbose.rob_id = err2.rob_id
	ORDER BY rob_id, rob_pos, begin_time;


CREATE OR REPLACE VIEW err_adc_verbose AS
	SELECT	rob_verbose.rob_id, 
		sm_slot AS sm, sm_stack AS stack, sm_layer AS layer, 
		roc_pos AS rob, rob_pos AS mcm, adcmask,
		begin_time, 
		( SELECT MIN(begin_time) AS end_time
		  FROM cferr_adc err1
		  WHERE err1.rob_id=err2.rob_id 
		    AND err1.rob_pos=err2.rob_pos
		    AND err1.begin_time>err2.begin_time
		) AS end_time
	FROM rob_verbose, cferr_adc err2
	WHERE rob_verbose.rob_id = err2.rob_id
	ORDER BY rob_id, rob_pos, begin_time;


CREATE OR REPLACE VIEW err_ni_verbose AS
	SELECT	rob_verbose.rob_id, 
		sm_slot AS sm, sm_stack AS stack, sm_layer AS layer, 
		roc_pos AS rob, rob_port AS port, nimask,
		begin_time, 
		( SELECT MIN(begin_time) AS end_time
		  FROM cferr_ni err1
		  WHERE err1.rob_id=err2.rob_id 
		    AND err1.rob_port=err2.rob_port
		    AND err1.begin_time>err2.begin_time
		) AS end_time
	FROM rob_verbose, cferr_ni err2
	WHERE rob_verbose.rob_id = err2.rob_id
	ORDER BY rob_id, rob_port, begin_time;

