CREATE OR REPLACE FUNCTION  AMT_P_MIGRGUID
(
  IN I_IDSESSION INT
)
 RETURNS int AS
$body$
DECLARE
  L_ErrorCode int;
  L_CharBlock int:=0;  
  L_RowCount int;
  L_FindGUIDLevel int;
  L_LoopGUIDLevel int;
  L_MaxGUIDTechnicalLevel int; 
BEGIN
  L_ErrorCode := 0;
  L_ROWCOUNT := 0;
  
  /* search for existing case of a need to migration else return 
          skip only if all Jobs are at max level 
             or filter by project type 
       */
  select cast(OPTION_VALUE as int) into L_MaxGUIDTechnicalLevel 
    from SYS_SITE_OPTIONS 
   where OPTION_NAME = 'AMT_MAXGUIDTECHLEVEL';

       
   /* detect if job need to be processed for migration of GUID : 
             - no job of same type with a GuidLevel lower that the max 
         */   
  if (exists (select 1 
                from JobAnalysisProperties ajp join AnaJob aj 
                                                 on (ajp.IdJob = aj.IdJob)
               where ajp.IdJob = I_IDSESSION
                 and ajp.GuidLevel = L_MaxGUIDTechnicalLevel
                 and not exists (select 1 from AnaJob aj2 join JobAnalysisProperties ajp2 on (aj2.IdJob = ajp2.IdJob)
                                 where ajp2.GuidLevel <  L_MaxGUIDTechnicalLevel
                                 and aj2.JobTyp = aj.JobTyp )

              ))
  then
    return 0;
  end if;

  select droptemporarytable('TMP_PropMigGuidList') into L_ErrorCode;  
  
  create local temporary table TMP_PropMigGuidList
  (
    IdPropGuid int not null,
    GUIDLevel int
  );
  
  /* getting property from category */ 
  insert into TMP_PropMigGuidList (IdPropGuid, GUIDLevel)
  select prop.IdProp, cattr.IntVal  from CatCat cc join PropCat prop 
                                      on (prop.IdCat = cc.IdCat)
                                    join CatAttr cattr 
                                      on (cattr.IdCat = cc.IdCat)
  where 
        cc.IdCatParent = 140698
    and cattr.AttrNam ='technicalLevel';
  
  select droptemporarytable('TMP_PrevVersionGuid') into L_ErrorCode;  
  create local temporary table TMP_PrevVersionGuid 
  (
    GUIDLevel int not null,
    OBJECT_ID int not null,
    NAME_ID varchar(1015) not null,
    SHORT_NAME_ID varchar(600) null,
    PROPERTY_TYPE_ID int not null, 
    MATCHING_ID int null
  );

  /* cleanup prev guid property for object with corresponding existing guid in object 
          This is for merge case :  2 prev guid give only 1 guid ( abap)              */
             
  delete from IN_CHAR_PROPERTIES 
   using IN_OBJECTS io join Objects o on (io.NAME_ID = o.IdNam and io.SHORT_NAME_ID = o.IdShortNam)
   where IN_CHAR_PROPERTIES.OBJECT_ID = io.OBJECT_ID 
     and IN_CHAR_PROPERTIES.PROPERTY_TYPE_ID in (select IdPropGuid from TMP_PropMigGuidList)
     and io.SESSION_ID = I_IDSESSION
     and IN_CHAR_PROPERTIES.SESSION_ID = I_IDSESSION;
                
  /* insert first part of GUID (long&short) exists  */ 
  insert into TMP_PrevVersionGuid (GUIDLevel, OBJECT_ID, NAME_ID, SHORT_NAME_ID, PROPERTY_TYPE_ID, MATCHING_ID)
  select GUIDLevel, io.OBJECT_ID, ipn.PROPERTY_CHAR,  null, IdPropGuid, NULL
    from IN_OBJECTS io join IN_CHAR_PROPERTIES ipn 
                         on (    ipn.OBJECT_ID = io.OBJECT_ID
                             and ipn.SESSION_ID = io.SESSION_ID)
                       join TMP_PropMigGuidList p on (ipn.PROPERTY_TYPE_ID = p.IdPropGuid)
   where 
         io.SESSION_ID = I_IDSESSION
     and ipn.SESSION_ID = I_IDSESSION
     and ipn.CHAR_BLOCK = L_CharBlock;
   
    get diagnostics L_RowCount := ROW_COUNT;
    if ( L_RowCount = 0 ) then
        return 0;
    end if;
     
  L_RowCount :=1 ;
  L_CharBlock := L_CharBlock + 1;
  
  while (L_RowCount != 0) loop
    update TMP_PrevVersionGuid 
       set NAME_ID = NAME_ID || ipn.PROPERTY_CHAR
      from IN_CHAR_PROPERTIES ipn 
     where ipn.OBJECT_ID = TMP_PrevVersionGuid.OBJECT_ID
       and ipn.SESSION_ID = I_IDSESSION
       and ipn.CHAR_BLOCK = L_CharBlock
       and ipn.PROPERTY_TYPE_ID = TMP_PrevVersionGuid.PROPERTY_TYPE_ID;
       
    get diagnostics L_RowCount := ROW_COUNT;
    L_CharBlock := L_CharBlock + 1;         
  end loop;

  select max(GUIDLevel) into L_FindGUIDLevel from TMP_PrevVersionGuid;
  L_RowCount:= 1;
  L_LoopGUIDLevel := L_FindGUIDLevel;
  
  while (L_LoopGUIDLevel >= 0) loop
    update TMP_PrevVersionGuid
       set MATCHING_ID = o.IdKey
      from Objects o 
     where o.IdNam = TMP_PrevVersionGuid.NAME_ID
       and MATCHING_ID is null
       and GUIDLevel = L_LoopGUIDLevel;
       
    delete from TMP_PrevVersionGuid
          using TMP_PrevVersionGuid t
          where t.MATCHING_ID is not null
            and TMP_PrevVersionGuid.OBJECT_ID = t.OBJECT_ID
            and TMP_PrevVersionGuid.GUIDLevel < t.GUIDLevel;
        
    L_LoopGUIDLevel:=L_LoopGUIDLevel-1;
  end loop;
  
  update Objects 
     set IdNam = io.NAME_ID,
         IdShortNam = io.SHORT_NAME_ID
    from TMP_PrevVersionGuid t join IN_OBJECTS io on (t.OBJECT_ID = io.OBJECT_ID)
   where t.MATCHING_ID = Objects.IdKey
     and t.MATCHING_ID is not null
     and io.SESSION_ID = I_IDSESSION;
  
  get diagnostics L_ROWCOUNT := ROW_COUNT;
  perform UpdateStats ('TMP_WK_DELFUSACC', L_ROWCOUNT);


  update JobAnalysisProperties
     set GuidLevel = L_MaxGUIDTechnicalLevel
   where IdJob = I_IDSESSION;
   
  /* cleanup properties used for migration */
  
  create index idx_inchprotyp on IN_CHAR_PROPERTIES (PROPERTY_TYPE_ID);
  delete from IN_CHAR_PROPERTIES 
   using TMP_PropMigGuidList t
   where IN_CHAR_PROPERTIES.PROPERTY_TYPE_ID = t.IdPropGuid;
   
  get diagnostics L_ROWCOUNT := ROW_COUNT;
  drop index idx_inchprotyp ;
  perform UpdateStats ('IN_CHAR_PROPERTIES', L_ROWCOUNT);



  select droptemporarytable('TMP_PropMigGuidList') into L_ErrorCode;  
  select droptemporarytable('TMP_PrevVersionGuid') into L_ErrorCode;  
   
  return L_ERRORCODE;
END;
$body$
LANGUAGE 'plpgsql'
/