---
--- 11-MAR-99  I-01-14+  APB    $$1   Initial submission
---                                   Script for checking consistency of Family Tables
---                                   in Pro/INTRALINK database (received from dyermole@ptc.com)
--- 25-MAR-99  I-01-14+  APB    $$2   New version (dyermole@ptc.com)
--- 05-APR-99  I-01-32   APB    $$3   New version (dyermole@ptc.com)
--- 28-May-99  I-01-14   Day    $$4   New version (dyermole@ptc.com)
--- 16-Aug-06  IL-06-X   Lijun  $$5   Nested familytable fix
--- 22-Oct-07  IL-06-602 Fusyak $$6   LO-PI name/folder check
---
-- v2.2
-- Script for checking consistency of Family Tables
-- in Pro/INTRALINK database.
--
-- Script has three arguments :
--   obj_mask - mask for object name (i.e. '%.prt')
--   rev1 - first revision of Pro/INTRALINK (i.e. '288')
--   rev2 - last revision of Pro/INTRALINK (i.e. '1998288')
--
-- Errors will be shown only for objects created or modified
-- between revisions rev1 and rev2 of Pro/INTRALINK.
-- To see all errors rev1 and rev2 have to be passed as '%'(percent).

prompt === Object name mask : &&1
prompt === First revision   : &&2
prompt === Last revision    : &&3

set pause off
set pages 50000
set numw  6
set feedb 1
set verif off
set lines 500
set trims on
set recsep off
set arrays 1

col pinamesize   new_v pinamesize   noprint
col lonamesize   new_v lonamesize   noprint
col instnamesize new_v instnamesize noprint

set termout off
alter session set optimizer_goal=ALL_ROWS;
alter session set nls_date_format='J:SSSSS';
col username new_v username noprint
select decode(upper(user), 'SYSTEM', 'PDM.',
                           'SYS',    'PDM.',
              NULL) username
from dual;
select 'a'||nvl(max(length(piname)), 6)     pinamesize,
       'a'||(nvl(max(length(piname)), 2)+4) instnamesize
from &&username.pdm_productitem;
select 'a'||nvl(max(length(loname)), 6) lonamesize
from &&username.pdm_lo;
set termout on

col piname   for &pinamesize
col loname   for &lonamesize
col instname for &instnamesize heading "PINAME"
col lovfname for a30
col pivrev   for a6
col pivfname for a30
col lovver_lt for a10 heading "Latest Ver."

create or replace view &&username.geninsrel_lovcontents as
select
  lovc1.lovid   lovid,
  gir.genpivid  genpivid,
  gir.instpivid instpivid
from &&username.pdm_geninsrel gir,
     &&username.pdm_lovcontents lovc1,
     &&username.pdm_lovcontents lovc2
where lovc1.pivid = gir.genpivid
  and lovc2.pivid = gir.instpivid
  and lovc1.lovid = lovc2.lovid;

create or replace function &&username.maxver(brid_ number)
                                             RETURN varchar2 is
  -- This function will find latest version of this PI
  -- in current branch brid_
  retval varchar2(32767) := 'No Last Ver';
  CURSOR c1 IS
    select pivrev||'/'||pivver||'/'||pivbrver
    from &&username.pdm_branch br,
         &&username.pdm_productitemversion piv
    where br.brid   = brid_
      and piv.pivid = br.brlastverid;
begin
  open c1; fetch c1 into retval; close c1;
  RETURN retval;
end;
/

create or replace function &&username.maxlover(loid_ number)
					       RETURN varchar2 is
  -- This function will find latest version of this LO
  retval varchar2(32767);
begin
  select to_char(max(lovver)) into retval
  from &&username.pdm_lov
  where loid = loid_;
  --
  RETURN nvl(retval, 'No Ver');
end;
/

set termout off

col rev1 new_v rev1 noprint
col rev2 new_v rev2 noprint

select decode ('&&2', '%', to_date('1:0', 'J:SSSSS'),
               nvl(min(nvl(modifiedon, createdon)),
                   to_date('1:0', 'J:SSSSS'))) rev1
from &&username.pdm_revisions
where revexe like '%&&2%'
   or revexe like '%&&3%';

select decode ('&&3', '%', sysdate,
               nvl(max(nvl(modifiedon, createdon)),
                   sysdate)) rev2
from &&username.pdm_revisions
where revexe like '%&&2%'
   or revexe like '%&&3%';

set termout on

ttitle '=============================================================' skip -
       '===== PI with no PIV'                                          skip 2

select piname
from &&username.pdm_productitem pi
where piname like '&&1'
  and not exists (select 1
                  from &&username.pdm_branch br,
                       &&username.pdm_productitemversion piv
                  where br.piid  = pi.piid
                    and piv.brid = br.brid)
order by 1;

ttitle '=============================================================' skip -
       '===== LO with no LOV'                                          skip 2

select loname
from &&username.pdm_lo lo
where loname like '&&1'
  and nvl(lo.modifiedon, lo.createdon) between '&rev1' and '&rev2'
  and not exists (select 1 from &&username.pdm_lov lov
                  where lov.loid = lo.loid)
order by 1;

ttitle '=============================================================' skip -
       '===== LOV with no PDM_LOVCONTENTS'                             skip 2

select loname, lov.lovver,
       decode(lov.lovver,
	      &&username.maxlover(lo.loid), '<- Latest!',
	      &&username.maxlover(lo.loid)) lovver_lt,
       nvl(lovf.lovfname, '--ERROR: NO FILE--') lovfname
from &&username.pdm_lovfile lovf,
     &&username.pdm_lov lov,
     &&username.pdm_lo lo
where lovf.lovid(+) = lov.lovid
  and lo.loid       = lov.loid
  and not exists (select 1 from &&username.pdm_lovcontents lovc
                  where lovc.lovid = lov.lovid)
  and loname like '&&1'
  and nvl(lov.modifiedon, lov.createdon) between '&rev1' and '&rev2';

ttitle '=============================================================' skip -
       '===== LOV with no file'                                        skip 2

select loname, lov.lovver,
       decode(lov.lovver,
	      &&username.maxlover(lo.loid), '<- Latest!',
	      &&username.maxlover(lo.loid)) lovver_lt,
       lov.lovid
from &&username.pdm_lov lov,
     &&username.pdm_lo lo
where lo.loid       = lov.loid
  and not exists (select 1 from &&username.pdm_lovfile lovf
                  where lovf.lovid = lov.lovid)
  and loname like '&&1'
  and nvl(lov.modifiedon, lov.createdon) between '&rev1' and '&rev2';

ttitle '=============================================================' skip -
       '===== Records in PDM_PRODUCTITEMVERSION which have'            skip -
       '===== PIVCLASS column with not right value'                    skip 2

col lovfname for a20
col pivfname for a20
col pivrevver for a10 heading "Rev/Ver/BrVer"
col pivver_lt for a10 heading "Latest Ver."
col pivclass  for a25 heading "Wrong value : marked as"

select piname,
       pivrev||'/'||pivver||'/'||pivbrver pivrevver,
       decode(pivrev||'/'||pivver||'/'||pivbrver,
              &&username.maxver(piv.brid), '<- Latest!',
              &&username.maxver(piv.brid)) pivver_lt,
       decode(pivclass,
              2, 'Generic',
              1, 'Instance',
              0, 'not Gen, not Inst',
              '-- Undefined value --') pivclass
from &&username.pdm_productitem pi,
     &&username.pdm_branch br,
     &&username.pdm_productitemversion piv
where br.brid        = piv.brid
  and br.piid        = pi.piid
  and ((pivclass = 2 and
        (not exists (select 1 from &&username.pdm_geninsrel
                     where genpivid = piv.pivid)
       or
         not exists (select 1 from &&username.pdm_lovcontents lovc
                     where lovc.pivid = piv.pivid)))
       or
       (pivclass = 1 and
        (not exists (select 1 from &&username.pdm_geninsrel
                     where instpivid = piv.pivid)
       or
         not exists (select 1 from &&username.pdm_lovcontents lovc
                     where lovc.pivid = piv.pivid)))
       or
       (pivclass = 0 and
        (exists (select 1 from &&username.pdm_geninsrel
                where genpivid  = piv.pivid
                    or instpivid = piv.pivid)
         or
         exists (select 1 from &&username.pdm_lovcontents lovc
                 where lovc.pivid = piv.pivid))))
  and piname like '&&1'
  and nvl(piv.modifiedon, piv.createdon) between '&rev1' and '&rev2'
order by 1, 2, 3;

ttitle '=============================================================' skip -
       '===== Records in PDM_LOVCONTENTS which have no corresponding'  skip -
       '===== records in PDM_GENINSREL'                                skip 2

select loname, lov.lovver,
       decode(lov.lovver,
	      &&username.maxlover(lo.loid), '<- Latest!',
	      &&username.maxlover(lo.loid)) lovver_lt,
       nvl(lovf.lovfname, '--ERROR: NO FILE--') lovfname,
       piname,
       pivrev||'/'||pivver||'/'||pivbrver pivrevver,
       decode(pivrev||'/'||pivver||'/'||pivbrver,
              &&username.maxver(piv.brid), '<- Latest!',
              &&username.maxver(piv.brid)) pivver_lt
from &&username.pdm_productitem pi,
     &&username.pdm_branch br,
     &&username.pdm_productitemversion piv,
     &&username.pdm_lovfile lovf,
     &&username.pdm_lov lov,
     &&username.pdm_lo lo,
     &&username.pdm_lovcontents lovc
where br.brid       = piv.brid
  and br.piid       = pi.piid
  and lovc.pivid    = piv.pivid
  and lovf.lovid(+) = lov.lovid
  and lo.loid       = lov.loid
  and lovc.lovid    = lov.lovid
  and loname like '&&1'
  and nvl(lovc.modifiedon, lovc.createdon) between '&rev1' and '&rev2'
  and not exists (select 1 from &&username.geninsrel_lovcontents gir_lovc
                  where lovc.lovid = gir_lovc.lovid
                    and (lovc.pivid = gir_lovc.genpivid or
                         lovc.pivid = gir_lovc.instpivid));

ttitle '=============================================================' skip -
       '===== LOV which have more than ONE generic in PDM_GENINSREL'   skip 2

select loname, lov.lovver,
       decode(lov.lovver,
	      &&username.maxlover(lo.loid), '<- Latest!',
	      &&username.maxlover(lo.loid)) lovver_lt,
       nvl(lovf.lovfname, '--ERROR: NO FILE--') lovfname
from &&username.pdm_lovfile lovf,
     &&username.pdm_lov lov,
     &&username.pdm_lo lo,
     (select lovid, count(distinct genpivid) count_gen
      from &&username.pdm_geninsrel g, &&username.pdm_lovcontents l
      where l.pivid = g.genpivid
	  and not exists (select null from &&username.pdm_geninsrel g2
	  	where g2.instpivid = g.genpivid)
      group by lovid
      having count(distinct genpivid) > 1) gir_lovc
where lovf.lovid(+)  = lov.lovid
  and lo.loid        = lov.loid
  and gir_lovc.lovid = lov.lovid
  and loname like '&&1'
  and nvl(lov.modifiedon, lov.createdon) between '&rev1' and '&rev2';

ttitle '=============================================================' skip -
       '===== Records in PDM_GENINSREL which have no corresponding'    skip -
       '===== records in PDM_LOVCONTENTS'                              skip 2

col generic  for a20 heading "Generic"
col instance for a20 heading "Instance"

select --+ RULE
       piv.pivid, pi.piname  generic,
       piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver pivrevver,
       decode(piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver,
              &&username.maxver(piv.brid), '<- Latest!',
              &&username.maxver(piv.brid)) pivver_lt,
       piv1.pivid, pi1.piname instance,
       piv1.pivrev||'/'||piv1.pivver||'/'||piv1.pivbrver pivrevver,
       decode(piv1.pivrev||'/'||piv1.pivver||'/'||piv1.pivbrver,
              &&username.maxver(piv1.brid), '<- Latest!',
              &&username.maxver(piv1.brid)) pivver_lt
from &&username.pdm_productitem pi,
     &&username.pdm_branch br,
     &&username.pdm_productitemversion piv,
     &&username.pdm_productitem pi1,
     &&username.pdm_branch br1,
     &&username.pdm_productitemversion piv1,
     &&username.pdm_geninsrel gir
where br.brid      = piv.brid    and br1.brid      = piv1.brid
  and br.piid      = pi.piid     and br1.piid      = pi1.piid
  and gir.genpivid = piv.pivid   and gir.instpivid = piv1.pivid
  and not exists (select 1 from &&username.geninsrel_lovcontents gir_lovc
                  where gir_lovc.genpivid  = gir.genpivid
                    and gir_lovc.instpivid = gir.instpivid)
  and (pi.piname like '&&1' or pi1.piname like '&&1')
  and nvl(gir.modifiedon, gir.createdon) between '&rev1' and '&rev2';

ttitle '=============================================================' skip -
       '===== PIV which participates in more than ONE LO'              skip 2

select piname,
       pivrev||'/'||pivver||'/'||pivbrver pivrevver,
       decode(pivrev||'/'||pivver||'/'||pivbrver,
              &&username.maxver(piv.brid), '<- Latest!',
              &&username.maxver(piv.brid)) pivver_lt
from &&username.pdm_productitem pi,
     &&username.pdm_branch br,
     &&username.pdm_productitemversion piv,
     (select pivid, count(distinct loid) count_lo
      from &&username.pdm_lovcontents lovc, &&username.pdm_lov lov
      where lov.lovid = lovc.lovid
      group by pivid
      having count(distinct loid) > 1) l
where br.brid = piv.brid
  and br.piid = pi.piid
  and l.pivid = piv.pivid
  and piname like '&&1'
  and nvl(piv.modifiedon, piv.createdon) between '&rev1' and '&rev2'
order by 1, 2;

prompt =============================================================
prompt ===== PDM_LOVCONTENTS :

col loname new_v loname noprint

ttitle skip 1 '===== LOname      : ' loname skip 2

break on loname skip page -
      on lovid on lovver on lovver_lt -
      on lovfname on piname on pivid on genpivid
col genmark  for a15 heading "In GIR"

select distinct
  loname, lovc.lovid,
  lov.lovver,
  decode(lov.lovver,
	 &&username.maxlover(lo.loid), '<- Latest!',
	 &&username.maxlover(lo.loid)) lovver_lt,
  decode(lovc.pivid, gir_lovc2.instpivid, ' \_ ')||piname instname,
  piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver pivrevver,
       decode(piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver,
              &&username.maxver(piv.brid), '<- Latest!',
              &&username.maxver(piv.brid)) pivver_lt,
  lovc.pivid,
  decode(lovc.pivid,
         gir_lovc1.genpivid,  'Generic',
         gir_lovc2.instpivid, '   \__ Instance',
         nvl(to_char(gir_lovc1.genpivid),
             nvl(to_char(gir_lovc2.instpivid),
                 '--ERROR--'))) genmark,
  nvl(lovf.lovfname, '--ERROR: NO FILE--') lovfname
from &&username.pdm_productitem pi,
     &&username.pdm_branch br,
     &&username.pdm_productitemversion piv,
     &&username.pdm_lovcontents lovc,
     &&username.geninsrel_lovcontents gir_lovc1,
     &&username.geninsrel_lovcontents gir_lovc2,
     &&username.pdm_lovfile lovf,
     &&username.pdm_lov lov,
     &&username.pdm_lo lo
where pi.piid      = br.piid
  and br.brid      = piv.brid
  and piv.pivid    = lovc.pivid
  and lovc.lovid   = lov.lovid
  and lovf.lovid(+)= lov.lovid
  and lo.loid      = lov.loid
  and lovc.lovid   = gir_lovc1.lovid(+)
  and lovc.lovid   = gir_lovc2.lovid(+)
  and lovc.pivid   = gir_lovc1.genpivid(+)
  and lovc.pivid   = gir_lovc2.instpivid(+)
  and loname like '&&1'
  and '&&1'        != '%'   -- Full report only
  and nvl(lovc.modifiedon, lovc.createdon) between '&rev1' and '&rev2'
order by loname, lov.lovver, genmark desc, instname;

prompt =============================================================
prompt ===== PDM_GENINSREL :

col lovfname   for a20
col pivfname   for a20
col instpiname for a20
col genloname  for a20
col instloname for a20
col loid_by_name for a15   heading "LOId found|By Name" just left
col loid         for 99999 heading "LOID"               just left
col lovid        for a8    heading "LOVID in|LOVCONT"   just left
col pivrevver_inst for a10 heading "Rev/Ver/BrVer"
col loname   new_v loname   print
col lovfname new_v lovfname print
col piname   new_v piname   noprint
col pivfname new_v pivfname noprint

ttitle skip 1 '===== Generic PIname : ' piname skip 1

break on piname skip page -
      on lovid duplicate -
      on pivrevver on genpivid on pivverrev

select
  pi.piname,
  piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver pivrevver,
  decode(piv.pivrev||'/'||piv.pivver||'/'||piv.pivbrver,
         &&username.maxver(piv.brid), '<- Latest!',
         &&username.maxver(piv.brid)) pivver_lt,
  gir.genpivid,
  gir.instpivid,
  pi2.piname instpiname,
  piv2.pivrev||'/'||piv2.pivver||'/'||piv2.pivbrver pivrevver_inst,
  decode(piv2.pivrev||'/'||piv2.pivver||'/'||piv2.pivbrver,
         &&username.maxver(piv2.brid), '<- Latest!',
         &&username.maxver(piv2.brid)) pivver_lt,
  nvl(to_char(gir_lovc.lovid), 'ERROR') lovid,
  decode(lo_by_name.loid,
         nvl(lo.loid, -1), to_char(lo_by_name.loid),
         NULL,             '-- No One --',
         lo_by_name.loid||' <-- DIFF') loid_by_name
from &&username.pdm_lo lo_by_name,
     &&username.pdm_lo lo,
     &&username.pdm_lov lov,
     &&username.pdm_geninsrel gir,
     &&username.geninsrel_lovcontents gir_lovc,
     &&username.pdm_productitemversion piv2,
     &&username.pdm_branch br2,
     &&username.pdm_productitem pi2,
     &&username.pdm_productitemversion piv,
     &&username.pdm_branch br,
     &&username.pdm_productitem pi
where lo_by_name.loname(+) = pi.piname
  and lo.loid(+)      = lov.loid
  and lov.lovid(+)    = gir_lovc.lovid
  and gir.genpivid    = gir_lovc.genpivid(+)
  and gir.instpivid   = gir_lovc.instpivid(+)
  and gir.instpivid   = piv2.pivid
  and br2.brid        = piv2.brid
  and br2.piid        = pi2.piid
  and gir.genpivid    = piv.pivid
  and br.brid         = piv.brid
  and br.piid         = pi.piid
  and pi.piname like '&&1'
  and '&&1'           != '%'  -- Full report only
  and nvl(gir.modifiedon, gir.createdon) between '&rev1' and '&rev2'
order by piname, pivrevver, genpivid, lovid, instpiname, pivrevver_inst;

--
-- SPR 1438567 
-- need Name check before migration to Windchill
--
set lines 144
set pages 60

col PIName for a35 print 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 'Please run fix_folder.sql and hl_loname_with_piname.sql scripts to resolve the differences below (if any). ' 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;

drop view &&username.geninsrel_lovcontents;
drop function &&username.maxver;
drop function &&username.maxlover;

exit
