---
--- 11-Nov-98  I-01-14   Olga      $$1  Created: clean up corrupted data.
--- 14-Dec-98  I-01-14   APB       $$2  Moved from dir. scripts to dir. hl_scripts
--- 20-Jan-99  I-01-14   Anil      $$3  bug fix
--- 28-Jan-99  I-01-14   Day       $$4  Changed update scripts for better
---                                     compatibility with corrupted
---                                     LO and LOV information
--- 28-May-99  I-01-14   Day       $$5  Fixed to handle more than one
---                                     corrupted LO per PINAME
--  18-Mar-08		 Marina B. $$6  Fixed the bug for SPR 1461406 (modified schema_name initialization)	
set echo off verify off showmode off feedback off linesize 100;
whenever sqlerror exit sql.sqlcode
set pause off
set pages 10000
set lines 500
set trims on
ACCEPT schema_name CHAR PROMPT 'Specify the schema name for Pro/Intralink (default - PDM):'  default 'PDM'

prompt === Please wait while script is running ...

set serveroutput on size 50000

declare
  new_name &&schema_name..pdm_lo.loname%TYPE;
  i number := 0;
  j number := 0;
  k number := 0;
  --
  cursor c1 is
    select lo.loid        loid,
           max(pi.piname) piname
    from &&schema_name..pdm_productitem        pi,
         &&schema_name..pdm_branch             br,
         &&schema_name..pdm_productitemversion piv,
         &&schema_name..pdm_geninsrel          gir,
         &&schema_name..pdm_lovcontents        lovc,
         &&schema_name..pdm_lov                lov,
         &&schema_name..pdm_lo                 lo
    where pi.piid      = br.piid
      and br.brid      = piv.brid
      and piv.pivid    = gir.genpivid
      and gir.genpivid = lovc.pivid
      and lovc.lovid   = lov.lovid
      and lov.loid     = lo.loid
    group by lo.loid, lo.loname
    having count(distinct pi.piid) = 1
       and lo.loname != max(pi.piname);
  --
  cursor c2 is
    select lov.lovid               lovid,
           lov.folid               lov_folid,
           count(distinct pi.piid) piid_count,
           max(pi.folid)           folid
    from &&schema_name..pdm_productitem        pi,
         &&schema_name..pdm_branch             br,
         &&schema_name..pdm_productitemversion piv,
         &&schema_name..pdm_geninsrel          gir,
         &&schema_name..pdm_lovcontents        lovc,
         &&schema_name..pdm_lov                lov
    where pi.piid      = br.piid
      and br.brid      = piv.brid
      and piv.pivid    = gir.genpivid
      and gir.genpivid = lovc.pivid
      and lovc.lovid   = lov.lovid
    group by lov.lovid, lov.folid;
  --
  cursor c3 (lovid_ number) is
    select distinct pi.folid folid
    from &&schema_name..pdm_productitem        pi,
         &&schema_name..pdm_branch             br,
         &&schema_name..pdm_productitemversion piv,
         &&schema_name..pdm_geninsrel          gir,
         &&schema_name..pdm_lovcontents        lovc,
         &&schema_name..pdm_lov                lov,
         &&schema_name..pdm_lo                 lo
    where pi.piid      = br.piid
      and br.brid      = piv.brid
      and piv.pivid    = gir.genpivid
      and gir.genpivid = lovc.pivid
      and lovc.lovid   = lov.lovid
      and lov.loid     = lo.loid
      and lo.loname    = pi.piname
      and lov.lovid    = lovid_;
  --
  cursor c4 (loname_ varchar2) is
    select count(*) from &&schema_name..pdm_lo
    where loname = loname_;
begin
  for rec in c1 loop
    j := 1;  k := 1;
    while (k != 0) loop
      new_name := substr('$$BAD____', 0, 10-length(to_char(j)))||j||'$$_'||
                  rec.piname;
      open c4(new_name); fetch c4 into k; close c4;
      j := j + 1;
    end loop;
    --
    update &&schema_name..pdm_lo set loname = new_name
    where loid = rec.loid;
    --
    i := i + 1;
  end loop;
  --
  dbms_output.put_line('=== Found '||i||' LOs with bad name.');
  i := 0;
  --
  for rec in (select loid, loname
              from &&schema_name..pdm_lo
              where loname like '$$BAD_____$$_%'
              order by nvl(modifiedon, createdon) desc) loop
    open c4(substr(rec.loname, 14)); fetch c4 into k; close c4;
    --
    if k = 0 then
      update &&schema_name..pdm_lo lo
      set loname = substr(loname, 14)
      where loid = rec.loid;
      --
      i := i + 1;
    end if;
  end loop;
  --
  dbms_output.put_line('.   Recovered '||i||' of them.');
  j := 0;
  --
  for rec in c2 loop
    if rec.piid_count = 1 then
      if rec.lov_folid != rec.folid then
        update &&schema_name..pdm_lov set folid = rec.folid
        where lovid = rec.lovid;
        --
        j := j + 1;
      end if;
    else
      i := 0;
      --                                      -- This LOV has at least
      for fol in c3(rec.lovid) loop           -- 2 PIVs that are Generics.
        update &&schema_name..pdm_lov set folid = fol.folid  -- We have to find that one
        where lovid = rec.lovid;              -- which has the same name
        --                                    -- like LO.
        i := i + 1;
        j := j + 1;
      end loop;
      --
      if i = 0 then
        dbms_output.put_line('=== Could''n find one PI with the same name like LO for LOVID = '||rec.lovid);
      elsif i > 1 then
        dbms_output.put_line('=== Find more than one PI with the same name like LO for LOVID = '||rec.lovid);
      end if;
    end if;
  end loop;
  --
  if j != 0 then
    dbms_output.put_line('=== Fixed '||j||' LOVs with bad folder.');
  end if;
end;
/

update &&schema_name..pdm_lo lo
  set folid = (select lov.folid
               from &&schema_name..pdm_lov lov
               where lov.loid = lo.loid
                 and lovver = (select max(lov1.lovver)
                               from &&schema_name..pdm_lov lov1
                               where lov1.loid = lo.loid))
where not exists (select 1 from &&schema_name..pdm_lov lov
                  where lov.loid  = lo.loid
                    and lov.folid = lo.folid
                    and lovver = (select max(lov1.lovver)
                                  from &&schema_name..pdm_lov lov1
                                  where lov1.loid = lo.loid));

update &&schema_name..pdm_lov lov
  set folid = (select lo.folid
               from &&schema_name..pdm_lo lo
               where lo.loid = lov.loid)
where not exists (select 1 from &&schema_name..pdm_lo lo1
                  where lo1.loid  = lov.loid
                    and lo1.folid = lov.folid);

exit;