# SQL CONL

WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON

SELECT 'Requirement 89526: Requirement 86810: [PERF] SCRIPT PART - CONL store 3 lines instead of 1 "motifs de recours" screen' "Comment" FROM DUAL;

set serveroutput on
exec globals_pkg.GV_Run_My_Trigger := FALSE;

drop table conl_temp;
create table conl_temp as select * from conl where structure_id = 17336;
alter table conl_temp MODIFY  CONL_LABEL varchar2(180); 

drop table translated_conl_temp;
create table translated_conl_temp as select * from translated_conl where structure_id = 17336;
alter table translated_conl_temp MODIFY  LABEL varchar2(180); 

declare
    v_structure_id PLS_INTEGER := 17336; -- :STRUCTURE_ID;
    i PLS_INTEGER :=0;
    v_updated PLS_INTEGER :=0;
    v_tc_updated PLS_INTEGER :=0;
begin
    dbms_output.put_line('Begin with ' || v_structure_id);
    for c in (select  conl.id,conl.cont_id,conl.conl_label_num, tc.id as TCID from conl join translated_CONL tc on tc.conl_id = conl.id and tc.structure_id = conl.structure_id where conl.structure_id=v_structure_id and conl.cont_id is not null and conl.conl_label_num in (1,5,8,9,18,21) )
    loop
        DECLARE 
            v_first_label_to_add  VARCHAR2(180) := null;
            v_second_label_to_add VARCHAR2(180) := null;
			v_first_trans_label_to_add  VARCHAR2(180) := null;
            v_second_trans_label_to_add VARCHAR2(180) := null;
        begin
            i:=i+1;
            -- here we look for complementatry labels if any and update TRANSLATED CONL records as well
            -- TO DO manage translated CONT and cleanup ( deletion of no more used records )
            -- FIRST
            begin
                select CONL_LABEL into v_first_label_to_add from CONL where CONL_LABEL is not null and 
                    conl.cont_id=c.cont_id and 
					conl.structure_id = v_structure_id and
                    CONL_LABEL_NUM = decode(c.CONL_LABEL_NUM,
                        1,2,
                        5,6,
                        8,30,
                        9,10,
                        18,19,
                        21,22,null);
						
						if c.TCID is not null then
							select LABEL into v_first_trans_label_to_add from TRANSLATED_CONL
							join CONL on conl.structure_id = TRANSLATED_CONL.structure_id and conl.id = TRANSLATED_CONL.CONL_ID
							where CONL_LABEL is not null and 
							conl.cont_id = c.cont_id and 
							conl.structure_id = v_structure_id and
							conl.CONL_LABEL_NUM = decode(c.CONL_LABEL_NUM,
								1,2,
								5,6,
								8,30,
								9,10,
								18,19,
								21,22,null); 
						end if;
                begin
                    select CONL_LABEL into v_second_label_to_add from CONL where CONL_LABEL is not null and 
                        conl.cont_id=c.cont_id and 
                        CONL_LABEL_NUM = decode(c.CONL_LABEL_NUM,
                            1,3,
                            5,7,
                            8,null,
                            9,null,
                            18,20,
                            21,null,null);
							
					if c.TCID is not null then
							select LABEL into v_second_trans_label_to_add from TRANSLATED_CONL
							join CONL on conl.structure_id = TRANSLATED_CONL.structure_id and conl.id = TRANSLATED_CONL.CONL_ID
							where CONL_LABEL is not null and 
							conl.cont_id = c.cont_id and 
							conl.structure_id = v_structure_id and
							conl.CONL_LABEL_NUM = decode(c.CONL_LABEL_NUM,
							  1,3,
								5,7,
								8,null,
								9,null,
								18,20,
								21,null,null);
						end if;
							
                EXCEPTION WHEN No_Data_Found THEN NULL; 
                END;
            EXCEPTION WHEN No_Data_Found THEN NULL; 
            END;
            if v_first_label_to_add is not null then
                update conl_temp set CONL_LABEL=CONL_LABEL||v_first_label_to_add || v_second_label_to_add,
                technical_version = technical_version + 1
                where id = c.id
				and structure_id = v_structure_id;
                v_updated := v_updated+1;
            end if;            
			
			if c.TCID is not null then
				update translated_conl_TEMP set LABEL=LABEL||v_first_trans_label_to_add || v_second_trans_label_to_add,
                technical_version = technical_version + 1
                where id= c.TCID
				and structure_id = v_structure_id;
				v_tc_updated := v_tc_updated + 1;
			end if;
        END;
    end loop;
    dbms_output.put_line('End with ' || v_structure_id || ' processed ' || i || ' CONL records, updated ' || v_updated || ' CONL records, and ' || v_tc_updated || ' TRANSLATED_CONL records.');
    commit;
end;
/