set feedback off
set termout off

col username new_val username noprint
select decode(upper(user), 'SYSTEM', 'PDM.',
                           'SYS',    'PDM.',
                                         '') username
from dual;

set verify off
set feedback on
set termout on
set trims on

PROMPT =====================================================
PROMPT Following procedure will fix inconsistency 
PROMPT between LOName and top generic PIName
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


DECLARE 
TYPE tn IS TABLE OF NUMBER   INDEX BY BINARY_INTEGER;
TYPE tc IS TABLE OF &&username.pdm_lo.loname%TYPE INDEX BY BINARY_INTEGER;
--
lonames tc;
loids   tn;
--
BEGIN
select /*+ ORDERED */ 
       pi.piname, lo.loid 
  bulk collect into 
       lonames, loids
  from (select lov.loid , lovc.pivid
          from &&username.pdm_lov lov, &&username.pdm_lovcontents lovc
         where (lov.loid,lov.lovver) in ( select lov1.loid,max(lov1.lovver) from &&username.pdm_lov lov1 group by lov1.loid)
           and lovc.lovid = lov.lovid
           and exists (select 1 from &&username.pdm_geninsrel gen 
                        where gen.genpivid = lovc.pivid
                          and exists (select 1 from &&username.pdm_lovcontents lovc1
                                       where lovc1.lovid = lovc.lovid 
                                         and lovc1.pivid = gen.instpivid)
                          and not exists (select 1 from &&username.pdm_geninsrel ins
                                           where ins.instpivid = gen.genpivid
                                             and exists ( select 1 from  &&username.pdm_lovcontents lovc1
                                                           where lovc1.pivid = ins.genpivid
                                                             and lovc1.lovid = lovc.lovid
                                                        )
                                         )
                      )
       ) a,
       &&username.pdm_lo lo, &&username.pdm_productitemversion piv, &&username.pdm_branch br, &&username.pdm_productitem pi
where lo.loid = a.loid
   and piv.pivid = a.pivid
   and br.brid = piv.brid
   and pi.piid = br.piid
   and lo.loname != pi.piname;
--
  IF loids.COUNT() > 0 THEN
    FORALL i IN loids.FIRST .. loids.LAST 
      UPDATE &&username.pdm_lo SET loname = lonames(i)
       WHERE loid = loids(i)
         AND NOT EXISTS(SELECT 1 FROM &&username.pdm_lo WHERE loname=lonames(i) 
                           AND loid != loids(i));
  END IF;

END;
/

set lines 144
set pages 60

col PIName for a35 hea 'Product Item Name'
col Difference for a17 hea 'Differences'
col ValOfdif for a90 hea 'Values of the differences'

TTitle skip-
left '=====================================================' skip-
left 'Differences in PI and LO       ' right 'Page:' sql.pno skip-
left '=====================================================' skip -
;

break on Difference skip 1 ;

select /*+ ORDERED */ 
        pi.piname,
	    DECODE(DECODE(pi.piname,lo.loname,0,1)+DECODE(pi.folid,lo.folid,0,2),
	       1,'In names',2,'In folders',3,'Names and Folders') Difference,
        DECODE(pi.piname,lo.loname,'','LOName: '||lo.loname)||
        DECODE(pi.folid,lo.folid,'',DECODE(pi.piname,lo.loname,'',CHR(10))||'PI folder :'||fpi.folpath||CHR(10)||'LO folder :'||flo.folpath) ValOfDif
  from (select lov.loid , lovc.pivid
          from &&username.pdm_lov lov, &&username.pdm_lovcontents lovc
         where (lov.loid,lov.lovver) in ( select lov1.loid,max(lov1.lovver) from &&username.pdm_lov lov1 group by lov1.loid)
           and lovc.lovid = lov.lovid
           and exists (select 1 from &&username.pdm_geninsrel gen 
                        where gen.genpivid = lovc.pivid
                          and exists (select 1 from &&username.pdm_lovcontents lovc1
                                       where lovc1.lovid = lovc.lovid 
                                         and lovc1.pivid = gen.instpivid)
                          and not exists (select 1 from &&username.pdm_geninsrel ins
                                           where ins.instpivid = gen.genpivid
                                             and exists ( select 1 from  &&username.pdm_lovcontents lovc1
                                                           where lovc1.pivid = ins.genpivid
                                                             and lovc1.lovid = lovc.lovid
                                                        )
                                         )
                      )
       ) a,
       &&username.pdm_lo lo, &&username.pdm_productitemversion piv, &&username.pdm_branch br, &&username.pdm_productitem pi,
       &&username.pdm_folder flo, &&username.pdm_folder fpi
where lo.loid = a.loid
   and piv.pivid = a.pivid
   and br.brid = piv.brid
   and pi.piid = br.piid
   and (lo.loname != pi.piname or lo.folid != pi.folid)
   and lo.folid = flo.folid
   and pi.folid = fpi.folid
order by 2,1;

PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT If report shows inconsistency <In names> 
PROMPT that means procedure could not rename LOName as per PIName
PROMPT because there exists different LO with the name (PIName)
PROMPT =====================================================

exit
