begin
	begin
	execute immediate 'drop type FOUR_NUMBER_COLUMNS_t';
	exception when others then
	 null;
	end;
	begin
	execute immediate 'drop type FOUR_NUMBER_COLUMNS_r';
	exception when others then
	 null;
	end;
	begin
	execute immediate 'drop table TEMP_EPMLINKSALL';
	exception when others then
	 null;
	end;
  end;
/
create or replace TYPE FOUR_NUMBER_COLUMNS_r AS OBJECT(col1 NUMBER,   col2 NUMBER, col3 NUMBER,   col4 NUMBER);
/

create or replace TYPE FOUR_NUMBER_COLUMNS_t AS TABLE of FOUR_NUMBER_COLUMNS_r;
/

CREATE OR REPLACE TYPE MY_TABLE_OF_NUMBER AS
   table of number;
/
CREATE OR REPLACE TYPE TABLE_OF_VARCHAR2 AS
   table of varchar2(160);
/
CREATE TABLE TEMP_EPMLINKSALL
(
  EPMLINKID NUMBER,
  LINKTYPE NUMBER,
  DEPTYPE NUMBER,
  DOCMASTER NUMBER,
  DOCID NUMBER
)
/
CREATE OR REPLACE function NOEXT(name varchar2) return varchar2 is
begin
    return SUBSTR(name,1,INSTR(name,'.',-1,1)-1);
end;
/
CREATE OR REPLACE PACKAGE mig_preprocess_pkg AS

--  '$Header: mig_preprocess.sql@@\main\ilwcmig.x-10-mor_pun\10  2008-03-27 04:38:44 GMT  builder $';

  TYPE PDMArrayId          IS TABLE of number  INDEX BY BINARY_INTEGER;
  TYPE PDMArrayStringLong  IS TABLE of pdm_pitypedef.pitname%TYPE INDEX BY BINARY_INTEGER;
  Type RefCur              IS REF CURSOR;

  PROCEDURE mig_markbranchedfamilypivs(dbLinkName varchar2,
                                       status number);

  PROCEDURE mig_findbranchedfamilypivs(
    v_outpivid   OUT PDMArrayId,
    v_outpiid    OUT PDMArrayId,
    mig_sp_status   IN OUT NOCOPY INTEGER );

  PROCEDURE mig_markSecondaryContent(
    dbLinkName IN varchar2,
    IlinkDomainId VARCHAR2,
    primarytdnames IN TABLE_OF_VARCHAR2,
    hardcodedtdnames  IN TABLE_OF_VARCHAR2,
    hard_categories IN TABLE_OF_VARCHAR2,
    secondarytdnames IN TABLE_OF_VARCHAR2,
    sec_categories IN TABLE_OF_VARCHAR2 );

  PROCEDURE mig_markUnFinishedPIs(
    dbLinkName IN varchar2,
    IlinkDomainId VARCHAR2 );

  PROCEDURE insertStatuses( cannotdo_pis PDMArrayId,
                          cannotdo_pivs PDMArrayId,
                          dbLinkName varchar2 );

  function addPIVs( primPIV IN number,
                   secPIV IN number,
                   secPI IN number,
                   state IN number default 0) return boolean;

  procedure mig_processReplGtwObjects (
      dbLinkName IN varchar2,
      IlinkDomainId VARCHAR2);

  function domainExists( domIds IN PDMArrayId,
                         domId IN number) return boolean;

  procedure countILObjects( domainId IN number,
                          dbLinkName IN varchar2,
                          sessionId IN varchar2,
                          typeDefInCond IN varchar2,
                          epmDocTypeDefInCond IN varchar2,
                          wtDocTypeDefInCond IN varchar2,
                          typeDefSecondInCond IN varchar2,
                          anyMembType IN number
                          );

  procedure printCurrentTime ( message varchar2 );

  procedure checkEquality ( cleanUp IN number,
                            IlinkDomainId IN varchar2,
                            dbLinkName IN varchar2,
                            anyMembType IN number ,
                            notIncludedTypeIds in varchar2);

  procedure cleanUpEqualityTables (ilDomainId IN number,
                                   dbLinkName IN varchar2);

  procedure markRelatedObjects (ilDomainId IN number,
                                dbLinkName in varchar2);

  procedure checkAttrEquality ( objectType IN number,
                                ilDomainId IN number,
                                dbLinkName IN varchar2 );

  procedure checkSysAttrEquality ( objectType IN number,
                                   ilDomainId IN number,
                                   dbLinkName IN varchar2 );

 procedure createDepDuplicateTable( ilDomainId in number,
                                   dbLinkName in varchar2,
                                   anyMembType in number,
                                   notIncludedTypeIds in varchar2);

  procedure  checkFamTabEquality( ilDomainId in number,
                                  dbLinkName in varchar2,
                                  badObjFound in out boolean );

  procedure  checkDepPIEquality( ilDomainId in number,
                                 dbLinkName in varchar2,
                                 badObjFound in out boolean );

  procedure markUnequalPIVs ( badpivs IN PDMArrayId,
                              delFromDep IN boolean,
                              ilDomainId IN varchar2,
                              dbLinkName IN varchar2 );

  procedure markUnEqualPIs( pis in PDMArrayId,
                            errIds in PDMArrayId,
                            errId in number,
                            ilDomainId in number,
                            dbLinkName in varchar2 );

  procedure debug_on;

  procedure debug_off;

--  function checkFlag (status in number, flag in number) return number;

--  function setEqual (status in number) return number;

--  function setCheckForEquality (status in number) return number;

--  function setUnEqual (status in number) return number;

END mig_preprocess_pkg;

/

sho err package mig_preprocess_pkg;


create or replace
PACKAGE BODY mig_preprocess_pkg AS


  object_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(object_exists, -955);

  MECHANICA_MESH_ILA_DEP          CONSTANT number := 1; -- These are found in
  FEM_MESH_ILA_DEP                CONSTANT number := 1; -- ./ilawrap/inc/proeutils.h
  PROE_ILA_DEP_MISC              CONSTANT number := 4096; --
  PROE_ACCEL_ILA_DEP_J03              CONSTANT number := 0; --
  PROE_ACCEL_ILA_DEP_J01              CONSTANT number := 67108864; --
  USER_DEFINED                    CONSTANT varchar2(80) := 'USER_DEFINED';
  MIG_CANNOTDO                        CONSTANT number := 4; -- from ILObject.java
  MIG_SUCCESS                         CONSTANT number := 1; -- from ILObject.java
  MIG_REPLSUCCESS                     CONSTANT number := 10; -- from ILObject.java
  MIG_UNFINISHED                      CONSTANT number := 3; -- from ILObject.java
  MIG_UPDATING                        CONSTANT number := 5;
  MIG_HOLES_UPDATING                  CONSTANT number := 6;  -- from ILObject.java
  MIG_HOLES                           CONSTANT number := 7;  -- from ILObject.java
  MIG_PART_CREATED                    CONSTANT number := 9;  -- from ILObject.java
  MIG_UNDECIDED                       CONSTANT number := 11;  -- from ILObject.java
  MIG_WCMODIFIED                      CONSTANT number := 1000; -- temporary
  MIG_REPL_UPDATING                   CONSTANT number := 1001; -- temporary flag while migrating second replica (master not migrated yet).
  							       --Once processing is done, replace it with MIG_HOLES or MIG_REPLSUCCESS in mig_processReplGtwObjects

  EPMSECONDARY                    CONSTANT number := 2;  -- from ILObject.java
  EPMDOCTYPE                      CONSTANT number := 0;  -- from ILObject.java
  WTDOCTYPE                      CONSTANT number := 1;  -- from ILObject.java
  GATEWAY CONSTANT number := 1; REPLICATION CONSTANT number := 2;
  MEMBER CONSTANT number := 0; REFERENCE CONSTANT number := 1; -- from ILDep.java

  SEC_MAXPIV CONSTANT number := 10;
  SEC_MAXPI CONSTANT number := 9;
  FINISHED_MAXPIV CONSTANT number := 13;
  ILCOUNT_MAXPIV CONSTANT number := 14;
-- This is a list of error ids that should match the error resource file

  ERR_NO_PARENT_PIV                                            CONSTANT number := 121;
  ERR_MORE_THAN_ONE_PARENT                                     CONSTANT number := 122;
  ERR_REQD_DEP_NOT_FOUND                                       CONSTANT number := 123;
  ERR_HAS_DEPENDENTS                                           CONSTANT number := 124;
  ERR_MORE_THAN_1_GOOD_PARENT                                  CONSTANT number := 125;
  ERR_NO_ASSTORED_PARENT_PIV                                   CONSTANT number := 126;
  ERR_WRONG_DEPTYPE                                            CONSTANT number := 127;
  ERR_SECONDARY_PARENT                                         CONSTANT number := 128;
  ERR_REPLICA_PI                                               CONSTANT number := 129;
  ERR_NAMECONFL_PI                                             CONSTANT number := 130;
  ERR_FILE_NOT_EXIST                                           CONSTANT number := 131;
  ERR_PART_FROM_OTHER_IL                                       CONSTANT number := 132;

  PI CONSTANT number := 1;
  PIV CONSTANT number := 2;
  DEP CONSTANT number := 3;
  EMPTY CONSTANT number := -1;


-- duplicate status bits from MergeStatus.java
--  public final static int INIT               = 0x100; //insignificant bit
--  public final static int VALID              =  0x1;
--  public final static int USER_VERIFIED      =  0x2;
--  public final static int CHECK_FOR_EQUALITY =  0x4;
--  public final static int EQUAL              =  0x8;
--  public final static int MARKED_FOR_MERGE   = 0x10;
--  public final static int UNEQUAL            = 0x20;

  VALID_FLAG CONSTANT number := 1;
  CHECK_FOR_EQUALITY_FLAG CONSTANT number := 4;
-- the program is starting with 0x100 = 256. Just to be safe I created the NOT of check for equality flag based on 512
-- so NOT_CHECK_FOR_EQUALITY_FLAG = 111111011 = 507
  NOT_CHECK_FOR_EQUALITY_FLAG CONSTANT number := 507;
  EQUAL_FLAG CONSTANT number := 8;
-- similarly NOT_EQUAL_FLAG = 111110111 = 503
  NOT_EQUAL_FLAG CONSTANT number := 503;
  UNEQUAL_FLAG CONSTANT number := 32;
-- similarly NOT_UNEQUAL_FLAG = 111011111 = 479
  NOT_UNEQUAL_FLAG CONSTANT number := 479;

  MARKED_FOR_MERGE_FLAG CONSTANT number := 16;
  VALID_AND_NEED_EQUALITY_CHECK number := 261;

  EMPTY_ERRID CONSTANT number := -1;
  UNEQUAL_PI CONSTANT number := 101;
  NO_CORRESPONDING_DEPS CONSTANT number := 102;
  CHILD_PI_UNEQUAL CONSTANT number := 103;
  UNEQUAL_PI_ATTR CONSTANT number := 111;
  UNEQUAL_PIV_ATTR CONSTANT number := 112;
  UNEQUAL_DEP_ATTR CONSTANT number := 113;
  FT_NOT_EQUAL CONSTANT number := 121;
  FT_CPIV_NOT_IN_DUPTAB constant number := 122;
  FT_CPIV_NO_DOC_IN_DUPTAB constant number := 123;
  FT_CONTENTS_NOT_EQUAL constant number := 124;
  UDA CONSTANT number := 1;
  SYS constant number := 2;

  pairFound boolean;
  primarypivs PDMArrayId; secondarypivs PDMArrayId; secondaryState PDMArrayId; secondarypis PDMArrayId;
  secondaryCat PDMArrayStringLong;
  pairCount number := 0;

procedure debug_on IS

  id varchar2(30);
BEGIN
  id := dbms_debug.initialize('MIG_DEBUG');
  dbms_debug.debug_on;
END debug_on;

procedure debug_off IS
begin
  dbms_debug.debug_off;
end debug_off;

procedure addPI ( piid in number,
                 pi_arr in out PDMArrayId ) IS

BEGIN

  if pi_arr.exists(piid) then
    pi_arr(piid) := pi_arr(piid) + 1; -- increment the piv number for this piv by 1
  else
    pi_arr(piid) := 1; -- initialize it to 1
  end if;

END addPI;

procedure printCurrentTime( message varchar2 ) IS

  v_time varchar2(80);
begin
  select to_char(sysdate, 'hh:mi:ss') into v_time from dual;
--  putpipe (message||': '||v_time);

end printCurrentTime;

PROCEDURE mig_markbranchedfamilypivs (
                   dbLinkName varchar2,
                   status     number) IS

  noxferpivs PDMArrayId;
  noxferpis PDMArrayId;
  mig_sp_status number := 0;
  sqlstmt varchar2(256);
  domid number;
  i number;
  nopivs number; nopis number;
  pivct number;

BEGIN

  -- if the status is already marked for a piv, then we should not run this sp
  sqlstmt := 'select count(pivid) from pivtowcstatus@'||dbLinkName||' where status = '||status;
  execute immediate sqlstmt into pivct;
  if (pivct = 0) then
    noxferpivs.delete;
    noxferpis.delete;
   
    mig_findbranchedfamilypivs(noxferpivs, noxferpis, mig_sp_status);

    select domainid into domid from pdm_domain where domislocal = 0;
    sqlstmt := 'insert into pivtowcstatus@'||dbLinkName||' (pivid, domainid, status) ' ||
               'values(:apivid, '||domid||', '||status||')';
--    putpipe(sqlstmt);
    nopivs := noxferpivs.FIRST;
    while nopivs is not null loop
      begin
        execute immediate sqlstmt using nopivs;
      exception when DUP_VAL_ON_INDEX then
        null;
      end;
      nopivs := noxferpivs.NEXT(nopivs);
    end loop;
--    putpipe( 'finished insert');
    sqlstmt := 'insert into pitowcstatus@'||dbLinkName||' (piid, domainid, status) ' ||
               'values(:apiid, '||domid||', '||status||')';
--    putpipe(sqlstmt);

    nopis := noxferpis.FIRST;
    while nopis is not null loop
        execute immediate sqlstmt using nopis;    
         nopis := noxferpis.NEXT(nopis);
    end loop;
  end if;

END mig_markbranchedfamilypivs;

PROCEDURE mig_findbranchedfamilypivs(
    v_outpivid   OUT PDMArrayId,
    v_outpiid    OUT PDMArrayId,
    mig_sp_status   IN OUT NOCOPY INTEGER ) IS

-- enum from ilapi/proilbase/pdmconst.h
--enum PDME_PIClass {
--  PIC_Ordinary = 0,
--  PIC_IsInstance,
--  PIC_HasInstance,
--  PIC_HasIsInstance
--};

    -- topgeneric
    cursor gen_cur is
      select pivid from pdm_productitemversion where pivclass = 2; 
  
    -- cursor gets all level instances for this top generic
    cursor inst_cur(cv_genpiv number) is
      select instpivid from pdm_geninsrel
             start with genpivid = cv_genpiv
             connect by prior instpivid = genpivid;

    -- this cursor get all the top generics for this instance
    cursor topgen_cur(cv_instpiv number) is
    select genins.genpivid from (select gir.genpivid
                                   from pdm_geninsrel gir
                                   start with instpivid = cv_instpiv
                                   connect by prior gir.instpivid = gir.genpivid) genins,
                                pdm_productitemversion piv
                          where piv.pivid = genins.genpivid
                            and piv.pivclass = 2;

    cursor lov_cur( cv_genid number ) is
      select lovid from pdm_lovcontents where pivid = cv_genid;

    cursor con_cur (cv_lovid number ) is
      select pivid from pdm_lovcontents where lovid = cv_lovid;
      
    cursor getPI (cv_pivid number ) is
      select br.piid from pdm_branch br, pdm_productitemversion piv
       where piv.pivid = cv_pivid 
         and piv.brid = br.brid;

  v_brfampivcount number := 0;
  lov_migratable boolean := false;
  genpiv number; lov number; piv number; instpiv number; topgenpiv number;
  genpi number; instpi number;
  ctr number := 1; i number;
  instpiv_migratable boolean := false;
  brpivfound boolean := false;
  pivbr varchar2(2000);
  noxfergens PDMArrayId;
  pis PDMArrayId;
  pictr number := 1; pivcount number;
  genpiv_on_mainbranch  boolean := false;
 

BEGIN
  select count(pivid) into v_brfampivcount
    from pdm_productitemversion piv, pdm_branch br
   where piv.brid = br.brid
     and br.brpath not like 'main'
     and piv.pivclass in (1,2,3);

  if (v_brfampivcount <> 0) then
    -- There are branched family table objects in this database, so we need to process

--    putpipe('found branched family pivs');
    noxfergens.delete;
    pis.delete;

    -- First find all the generics that can not be transferred due to non-main branches

    for rec_genpiv in gen_cur loop
      genpiv := rec_genpiv.pivid;
      genpiv_on_mainbranch := false;
        select br.brpath into pivbr
        from pdm_productitemversion piv, pdm_branch br
         where piv.brid = br.brid
           and piv.pivid = genpiv;
        if pivbr = 'main' then
          genpiv_on_mainbranch := true;
        end if;

--      putpipe('genpivid is '||genpiv);
      for rec_lov in lov_cur(genpiv) loop
--        putpipe('lovid is '||rec_lov.lovid);

        lov_migratable := false;
        if genpiv_on_mainbranch then
          for rec_piv in con_cur(rec_lov.lovid) loop
--            putpipe('contentid is '||rec_piv.pivid);
            brpivfound := false;
            select br.brpath into pivbr
              from pdm_productitemversion piv, pdm_branch br
             where piv.brid = br.brid
               and piv.pivid = rec_piv.pivid;
--            putpipe('branch for this piv is '||pivbr);
            if (pivbr != 'main') then
              brpivfound := true;
              -- if a piv is found on a branch other than main, then this lov can not be migrated
              -- so no need to check rest of the pivs
--              putpipe('exiting from loop since branch is not main');
              exit;
            end if;
          end loop;
          if (not brpivfound) then
            -- if branchpiv not found then this lov can be migrated
            -- and if this lov can be migrated then this generic can be migrated so no need to check further lovs
            lov_migratable := true;
            exit;
          end if;
        end if;
      end loop;
      if (not lov_migratable) then
        -- could not find any migratable lov for this generic, so put this generic in noxferpivs if it is on main branch
--        putpipe('generic piv '||genpiv||' is not migratable');
        if ( genpiv_on_mainbranch) then
          if not v_outpivid.exists (genpiv) then
            v_outpivid(genpiv) := genpiv;
          -- This increases the count of bad PIV for this PI
          open getPI(genpiv); 
          fetch getPI into genpi;
          close getPI;
          addPI(genpi, pis);
          end if;
        end if;
        noxfergens(genpiv) := genpiv;
      end if;
    end loop;

    -- Now find all the instances that can not be transferred due to these generics
    -- we only need instances from these generics

    i := noxfergens.FIRST;
    while i is not null loop
      genpiv := noxfergens(i);
      for rec_instpiv in inst_cur(genpiv) loop
        instpiv_migratable := false;
        for rec_topgenpiv in topgen_cur(rec_instpiv.instpivid) loop
          if (not noxfergens.exists(rec_topgenpiv.genpivid)) then
            instpiv_migratable := true;
            exit;
          end if;
        end loop;
        if (not instpiv_migratable)  then
--          putpipe('instance piv '||rec_instpiv.instpivid||' is not migratable');
          select br.brpath into pivbr
            from pdm_productitemversion piv, pdm_branch br
           where piv.brid = br.brid
             and piv.pivid = rec_instpiv.instpivid;
          if pivbr = 'main' then
            if not v_outpivid.exists (rec_instpiv.instpivid) then
                v_outpivid(rec_instpiv.instpivid) := rec_instpiv.instpivid;
            open getPI(rec_instpiv.instpivid);
            fetch getPI into instpi;
            close getPI;
            addPI(instpi, pis);
            end if;
          end if;
        end if;
      end loop;
      i := noxfergens.NEXT(i);
    end loop;

    i := pis.FIRST;
    while i is not null loop
      -- pivcount is the total count of PIVs in this PI on main branch
      select count(pivid) into pivcount
        from pdm_branch br, pdm_productitemversion piv
       where br.piid = i -- here treat i as piid , but we put pivid into pis?
         and br.brpath = 'main'
         and br.brid = piv.brid;
      -- if badpivs count for this PI is same as total number of PIVs for this PI (both on main branch)
      if pis(i) = pivcount then -- this pi can not be migrated since all pivs on main branch are not being migrated
        v_outpiid(pictr) := i;
        pictr := pictr + 1;
      end if;
      i := pis.NEXT(i);
    end loop;


  end if;

  EXCEPTION WHEN OTHERS THEN
     mig_sp_status := SQLCODE;

END mig_findbranchedfamilypivs;
PROCEDURE mig_markSecondaryContent(
    dbLinkName IN varchar2,
    IlinkDomainId VARCHAR2,
    primarytdnames IN TABLE_OF_VARCHAR2,
    hardcodedtdnames  IN TABLE_OF_VARCHAR2,
    hard_categories IN TABLE_OF_VARCHAR2,
    secondarytdnames IN TABLE_OF_VARCHAR2,
    sec_categories IN TABLE_OF_VARCHAR2 ) IS

  cursor parent_piv (c_pi number) is
    select br.piid, piv.pivid, piv.primtxnid, dg.dgid, dg.dgdeptype, dgdepclass
      from pdm_dependencygraph dg, pdm_productitemversion piv, pdm_branch br
     where dg.piid = c_pi and dg.pivid = piv.pivid and piv.brid = br.brid and br.brpath = 'main';

  cursor mainbr_id (c_pi number) is
    select br.brid from pdm_branch br where br.piid = c_pi and br.brpath = 'main';

  cursor revs (c_br number) is
    select distinct piv.pivrev from pdm_productitemversion piv where piv.brid = c_br;

  cursor all_pivs (c_pi number) is
    select piv.pivid, piv.primtxnid from pdm_branch br, pdm_productitemversion piv
     where br.piid = c_pi and br.brpath = 'main'
       and br.brid = piv.brid;

  cursor all_children (c_piv number) is
    select dgid from pdm_dependencygraph where pivid = c_piv;

  cursor primtxn_for_secpiv (c_piv number) is
    select pivtxnid from pdm_pivsectxn where pivid = c_piv;

  cursor deps_for_diff_pis (this_pi number, excl_pi number) is
    select dgid from pdm_dependencygraph dg, pdm_productitemversion piv, pdm_branch br
     where dg.piid = this_pi and dg.pivid = piv.pivid and piv.brid = br.brid
       and br.piid != excl_pi and br.brpath = 'main';

  hardcodedtdids PDMArrayId; secondarytdids PDMArrayId; primarytdids PDMArrayId;
  parentpis PDMArrayId; parentpivs PDMArrayId;
  cannotdo_pis PDMArrayId; cannotdo_pivs PDMArrayId; children_pivs PDMArrayId;
  cannotdo_deps PDMArrayId;
  --addedParentPIVs PDMArrayId;
  categories PDMArrayStringLong; allowed_depclass PDMArrayStringLong; allowed_deptypes PDMArrayStringLong;
  typedef_cond varchar2(4000);
  sqlstmt varchar2(4000);
  v_clsql varchar2(400); v_tpsql varchar2(400);
  clOK number; tpOK number;
  tdname varchar2(160); v_dgdepclass varchar2(160); category varchar2(160);
  tdcount number; i number; tdid number;
  v_piid number; v_typeid number; v_parentpiid number; v_pivid number; v_dgid number;
  v_dgdeptype number; v_primtxnid number; good_parent_pi number; v_parpiid number;
  v_txnid number; v_secprimtxnid number; v_parpivid number;
  need_comma boolean; abortPI boolean; hardCoded boolean; added boolean;
  primaryTD boolean ; reqd_dep_found boolean; multiple_good_parents boolean;
  picur RefCur;

  ilDomainId NUMBER := to_number(IlinkDomainId);
  STALE CONSTANT number := 1;
  v_primbrid number; v_secbrid number; v_primpivid number; v_secpivid number; v_pivver number;
  v_pivrev varchar2(160);
  v_dbmaxpivid number; v_maxpivid number := 0;
  v_dbmaxpiid number;
  insertmaxpiv boolean := false;

--  localcount number := 1000; tpicount number := 1;

BEGIN

--   printCurrentTime('1');
-- if maxpivid is not different from the old one, dont do anything
  select nvl(max(pivid), 0) into v_dbmaxpivid from pdm_productitemversion;
--  putpipe('current max pivid is '||v_dbmaxpivid);

  begin
    sqlstmt := 'SELECT MAXDBId FROM ResumeProcessStatus@'||dbLinkName||
      ' WHERE DomainId = :ilDomainId AND Purpose = :SEC_MAXPIV';
    execute immediate sqlstmt into v_maxpivid using ilDomainId, SEC_MAXPIV;
--    putpipe('old max pivid is '||v_maxpivid);
  exception when NO_DATA_FOUND then
    -- no row exists
    insertmaxpiv := true;
  end;

  if not v_dbmaxpivid > v_maxpivid then
      -- nothing changed from last processing so ignore
    return;
  end if;

--  putpipe('started the stored procedure');
  cannotdo_pis.delete;
  cannotdo_pivs.delete;
  cannotdo_deps.delete;

  hardcodedtdids.delete;
  secondarytdids.delete;
  categories.delete;
  primarytdids.delete;

  -- convert the typedef names into typedef ids
  tdcount := primarytdnames.count;
  for i in 1..tdcount loop
--    putpipe('primary tdname = '||primarytdnames(i));
    begin
      select pitypeid into tdid from pdm_pitypedef where pitname = primarytdnames(i);
      primarytdids(tdid) := tdid;
    exception when NO_DATA_FOUND then
      null; -- ignore it
    end;
  end loop;

  tdcount := hardcodedtdnames.count;
  for i in 1..tdcount loop
    tdname := hardcodedtdnames(i);
--    putpipe('hardcoded tdname = '||hardcodedtdnames(i));
    begin
      select pitypeid into tdid from pdm_pitypedef where pitname = tdname;
      hardcodedtdids(tdid) := tdid;
      if not categories.exists(tdid) then
        categories(tdid) := hard_categories(i);
      end if;
      -- create allowed_deptypes array
      if (tdname = 'Mechanica Mesh Part' or tdname = 'Mechanica Mesh Assembly') then
        allowed_depclass(tdid) := '(''PRO_MECHANICA'')';
        allowed_deptypes(tdid) := '('||MECHANICA_MESH_ILA_DEP||')';
      elsif (tdname = 'FEM Mesh Part' or tdname = 'FEM Mesh Assembly') then
        allowed_depclass(tdid) := '(''PRO_FEM'')';
        allowed_deptypes(tdid) := '('||FEM_MESH_ILA_DEP||')';
      elsif (tdname = 'Inst_Part' or tdname = 'Inst_Assembly') then
        allowed_depclass(tdid) := '(''PRO_E'', ''PROE_ILA'')';
        allowed_deptypes(tdid) := '('||PROE_ACCEL_ILA_DEP_J03||', '||PROE_ILA_DEP_MISC||', '||PROE_ACCEL_ILA_DEP_J01||')';
      elsif (tdname = 'Mfg_Tool_Path') then
      	allowed_depclass(tdid) := '(''PRO_E'')';
        allowed_deptypes(tdid) := '('||PROE_ILA_DEP_MISC||')';
      end if;
    exception when NO_DATA_FOUND then
      null; -- ignore
    end;
  end loop;

  tdcount := secondarytdnames.count;
  for i in 1..tdcount loop
--    putpipe('secondary tdname = '||secondarytdnames(i));
    begin
      select pitypeid into tdid from pdm_pitypedef where pitname = secondarytdnames(i);
      secondarytdids(tdid) := tdid;
      if not categories.exists(tdid) then
        categories(tdid) := sec_categories(i);
      end if;
    exception when NO_DATA_FOUND then
      null; --ignore
    end;
  end loop;

  -- Create typedef in condition to find the PIs that can become secondary contents
  need_comma := false;
  typedef_cond := '( ';
  i := hardcodedtdids.FIRST;
  while i is not null loop
    if need_comma then
      typedef_cond := typedef_cond ||', ';
    end if;
    typedef_cond := typedef_cond ||i;
    need_comma := true;
    i := hardcodedtdids.next(i);
  end loop;

  i := secondarytdids.FIRST;
  while i is not null loop
    if need_comma then
      typedef_cond := typedef_cond ||', ';
    end if;
    typedef_cond := typedef_cond ||i;
    need_comma := true;
    i := secondarytdids.next(i);
  end loop;
  typedef_cond := typedef_cond ||' )';

  if v_maxpivid > 0 then
    sqlstmt := 'select distinct pi.piid, pi.pitypeid from pdm_productitemversion piv, pdm_branch br, '||
               'pdm_productitem pi where '||
               'piv.pivid > '||v_maxpivid||' and piv.brid = br.brid and br.piid = pi.piid and '||
               'pitypeid in '||typedef_cond;
  else
    sqlstmt := 'select piid, pitypeid from pdm_productitem where pitypeid in '||typedef_cond;
  end if;

--  putpipe(sqlstmt);

--  printCurrentTime('2');

  open picur for sqlstmt;
  loop
    fetch picur into v_piid, v_typeid;
    exit when picur%NOTFOUND;

    category := categories(v_typeid);
--    putpipe('pi is :'||v_piid||' and type is '||v_typeid||' and category is '||category);
    abortPI := false;
    hardCoded := false;
    primaryTD := false;
    hardCoded := hardcodedtdids.exists(v_typeid);
--    if hardCoded then
--      putpipe('this typedef is hardcoded');
--    else
--      putpipe('this typedef is not hardcoded');
--    end if;
    if not hardCoded then
      primaryTD := primarytdids.exists(v_typeid);
    end if;
    -- For this pi find the parent pi, parent dep and parent deptype
    open parent_piv(v_piid);
    parentpis.delete;
    good_parent_pi := -1;
    parentpivs.delete;
    reqd_dep_found := false;
--    if localcount > 999 then
--      printCurrentTime(tpicount||': piid is '||v_piid);
--      localcount := 0;
--    end if;
    loop
      fetch parent_piv into v_parpiid, v_parpivid, v_primtxnid, v_dgid, v_dgdeptype, v_dgdepclass;
      exit when parent_piv%NOTFOUND;
--      putpipe('parent piv info is '||v_parpivid||', '||v_parpiid||', '||v_primtxnid||', '||v_dgid||', '||v_dgdeptype||', '||v_dgdepclass);
--      printCurrentTime('3');
      parentpis(v_parpiid) := 1;
      -- check the deptype or depclass
--        putpipe('allowed depclass is '||allowed_depclass(v_typeid));
--        putpipe('allowed deptype is '||allowed_deptypes(v_typeid));
      if hardCoded then
        v_clsql := 'select nvl(max(1),0) from dual where '''||v_dgdepclass||''' in '||allowed_depclass(v_typeid);
        v_tpsql := 'select nvl(max(1),0) from dual where '||v_dgdeptype||' in '||allowed_deptypes(v_typeid);
        execute immediate v_clsql into clOK;
        execute immediate v_tpsql into tpOK;
        if (clOK = 1) and (tpOK = 1) then
          reqd_dep_found := true;
          -- since we use only one parent PI, primtxnid will not be same for two versions of the pi
          if (good_parent_pi = -1) then
            good_parent_pi := v_parpiid;
          else
            if good_parent_pi != v_parpiid then
              -- two different parent pis with valid dependency types found, this is error condition
              multiple_good_parents := true;
              exit;
            end if;
          end if;
        end if;
      else
        if v_dgdepclass = USER_DEFINED then
          reqd_dep_found := true;
        end if;
      end if;
--      putpipe ('piv '||v_parpivid||' added at txn '||v_primtxnid);
      parentpivs(v_primtxnid):= v_parpivid;
    end loop;
    close parent_piv;

--    printCurrentTime('4');
    -- if parent not found put this as cannot do
    if parentpis.count=0 then
      -- since hardcoded typedefs can not be primary, we do not need special processing for those
      if not primaryTD then
        cannotdo_pis(v_piid) := ERR_NO_PARENT_PIV;
--        putpipe('cannot do pi '||v_piid||' since no parent and not primary');
--      else
--        putpipe('ignored pi '||v_piid||' since no parent and can be primary');
      end if;
      abortPI := true;
    -- if more than one parent found
    elsif parentpis.count > 1 then
      -- for hardcoded typedefs make sure that other deps are marked as cannot do
      if hardCoded then
        if reqd_dep_found then
          if multiple_good_parents then
            cannotdo_pis(v_piid) := ERR_MORE_THAN_1_GOOD_PARENT;
            abortPI := true;
          else
            -- find the dependencies for which this is child pi and parent piv is not from good pi
            -- and mark those as cannot do
            open deps_for_diff_pis(v_piid, good_parent_pi);
            loop
              fetch deps_for_diff_pis into v_dgid;
              exit when deps_for_diff_pis%NOTFOUND;
              cannotdo_deps(v_dgid) := ERR_WRONG_DEPTYPE;
            end loop;
            close deps_for_diff_pis;
          end if;
        end if;
      else
        -- for non-hardcoded typedefs ignore if they are also primary, else cannot do
        if not primaryTD then
          cannotdo_pis(v_piid) := ERR_MORE_THAN_ONE_PARENT;
        end if;
        abortPI := true;
      end if;
    end if;

--    printCurrentTime('5');
      -- if required dependency not found mark can not do for the PI
    if (not abortPI) and (not reqd_dep_found) then
      if not primaryTD then
          cannotdo_pis(v_piid) := ERR_REQD_DEP_NOT_FOUND;
      end if;
      abortPI := true;
    end if;

    -- check the children of the pivs of this pi and decide which pis and deps should be cannotdo
    if (not abortPI) then
      children_pivs.delete;
      open all_pivs(v_piid);
      loop
        fetch all_pivs into v_pivid, v_primtxnid;
        exit when all_pivs%NOTFOUND;
--        putpipe('piv is '||v_pivid||' and primtxnid is '||v_primtxnid);
        children_pivs(v_pivid) := v_primtxnid;
        open all_children(v_pivid);
        loop
          fetch all_children into v_dgid;
          exit when all_children%NOTFOUND;
          if hardCoded then
            -- mark this dgid as cannot do
            cannotdo_deps(v_dgid) := ERR_SECONDARY_PARENT;
          else
            -- for non hardcoded, this is a no-no
            if not primaryTD then
              cannotdo_pis(v_piid) := ERR_HAS_DEPENDENTS;
            end if;
            abortPI := true;
            exit;
          end if;
        end loop;
        close all_children;
        if abortPI then
          exit;
        end if;
      end loop;
      close all_pivs;
    end if;
--    printCurrentTime('6');

    pairFound := false;
    if (not abortPI) then
--      putpipe ('no of pivs is '||children_pivs.count);
      i := children_pivs.FIRST;
--      putpipe('piv of this secondary pi is '||i);
      while i is not null loop
--        putpipe(' working with piv '||i);
        added := false;
        v_txnid := children_pivs(i);
--        putpipe ('txnid is '||v_txnid);
        if parentpivs.exists(v_txnid) then
          secondaryCat(i) := category;
--          putpipe('secondaryCat for '||i||' piv is '||category);
          v_parpivid := parentpivs(v_txnid);
          added := addPIVs(v_parpivid, i, v_piid);
        end if;
        open primtxn_for_secpiv (i);
        loop
          fetch primtxn_for_secpiv into v_secprimtxnid;
--          putpipe('v_secprimtxnid = '||v_secprimtxnid);
          exit when primtxn_for_secpiv%NOTFOUND;
          if parentpivs.exists(v_secprimtxnid) then
            secondaryCat(i) := category;
--            putpipe('secondaryCat for '||i||' piv is '||category);
            v_parpivid := parentpivs(v_secprimtxnid);
            added := addPIVs(v_parpivid, i, v_piid);
          end if;
        end loop;
        close primtxn_for_secpiv;
        if (not added) then
          cannotdo_pivs(i) := ERR_NO_ASSTORED_PARENT_PIV;
        end if;
        i := children_pivs.NEXT(i);
      end loop;
      if not pairFound then
--        putpipe ('pair not found for '||v_parpiid||' and '||v_piid);
        if hardCoded then
          -- find the latest version of the secondary and the primary for each revision
          open mainbr_id(v_parpiid);
          fetch mainbr_id into v_primbrid;
          close mainbr_id;
--          putpipe('pi '||v_parpiid||' and br '||v_primbrid);
          open mainbr_id(v_piid);
          fetch mainbr_id into v_secbrid;
          close mainbr_id;
--          putpipe('pi '||v_piid||' and br '||v_secbrid);
          open revs(v_primbrid);
          loop
            fetch revs into v_pivrev;
            exit when revs%NOTFOUND;
--            putpipe (v_pivrev);
            -- find the latest piv for both primary and secondary pi on this rev
            v_pivver := pdmsp_oqlfunc.pivlastver(v_primbrid,v_pivrev);
            select pivid into v_primpivid from pdm_productitemversion
             where brid = v_primbrid and pivrev = v_pivrev and pivver = v_pivver;
            v_pivver := pdmsp_oqlfunc.pivlastver(v_secbrid,v_pivrev);
            if (v_pivver <> 0) then
              select pivid into v_secpivid from pdm_productitemversion
               where brid = v_secbrid and pivrev = v_pivrev and pivver = v_pivver;
              secondaryCat(v_secpivid) := category;
              added := addPIVs(v_primpivid, v_secpivid, v_piid, STALE);
            end if;
          end loop;
          close revs;
        else
          if not primaryTD then
            cannotdo_pis(v_piid) := ERR_NO_ASSTORED_PARENT_PIV;
          else
            -- remove the pivs of this pi from the cannotdo_pivs array - SPR 1105333
            i := children_pivs.FIRST;
            while i is not null loop
              cannotdo_pivs.delete(i);
              i := children_pivs.NEXT(i);
            end loop;
          end if;
          abortPI := true;
        end if;
      end if;
    end if;
--    localcount := localcount + 1;
--    tpicount := tpicount + 1;
--    printCurrentTime('7');
  end loop;
  close picur;

  insertStatuses(cannotdo_pis, cannotdo_pivs, dbLinkName);

  -- insert/update into resumeprocessstatus
  select nvl(max(piid), 0) into v_dbmaxpiid from pdm_productitem;
  if insertmaxpiv then
    sqlstmt := 'insert into resumeprocessstatus@'||dbLinkName||
      ' (MAXDBId, DomainId, Purpose) values (:1,:2,:3)';

    execute immediate sqlstmt using v_dbmaxpivid, ilDomainId, SEC_MAXPIV;
    execute immediate sqlstmt using v_dbmaxpiid, ilDomainId, SEC_MAXPI;
  else
    sqlstmt := 'update resumeprocessstatus@'||dbLinkName||
       ' set MAXDBId = :1 where DomainId = :2 and Purpose = :3';

    execute immediate sqlstmt using v_dbmaxpivid, ilDomainId, SEC_MAXPIV;
    execute immediate sqlstmt using v_dbmaxpiid, ilDomainId, SEC_MAXPI;
  end if;


--  exception when others then
--    putpipe('errcode is '||SQLCODE);
--    putpipe('errmesg is '||SQLERRM);


END mig_markSecondaryContent;

function addPIVs( primPIV IN number,
                   secPIV IN number,
                   secPI IN number,
                   state IN number default 0) return boolean IS

BEGIN
  pairFound := true;
  pairCount := pairCount + 1;
--  putpipe('primpiv '||primPIV||', secpiv is'||secPIV);
  primarypivs(pairCount) := primPIV;
  secondarypivs(pairCount) := secPIV;
  secondaryState(pairCount) := state;
  secondarypis(secPI) := secPI;
  return true;
END addPIVs;

PROCEDURE insertStatuses( cannotdo_pis PDMArrayId,
                          cannotdo_pivs PDMArrayId,
                          dbLinkName varchar2 ) IS

  cursor fileinfo( cv_pivid number ) is
    select f.fid, pi.piname, f.fsize, fs.fsvhost, fs.fsvport, fv.poolpath, f.fmapname
      from pdm_productitemverfile pivf, pdm_file f, pdm_pool fv, pdm_fileserver fs,
           pdm_productitem pi, pdm_productitemversion piv, pdm_branch br
     where pivf.pivid = cv_pivid and pivf.fid = f.fid and f.poolid = fv.poolid
       and fv.poolhost = fs.fsvhost
       and pivf.pivid = piv.pivid and piv.brid = br.brid and br.piid = pi.piid;

  v_secpivs PDMArrayId;
  i number;
  v_pi number; v_piv number; v_secpiv number; v_state number(1);
  v_errid number;
  v_sqlstmt varchar2(4000);
  v_cat varchar2(160);
  v_domainid number;
  v_fid number; v_fsize number;
  v_fname varchar2(160); v_fshost varchar2(160); v_fsport varchar2(160); v_fmapname varchar2(160);
  v_poolpath varchar2(4000);
  v_status number;
  v_filestatus number;

BEGIN
--  putpipe('in insertStatues');
--  putpipe ('cannotdo pis :'||cannotdo_pis.count);
  select domainid into v_domainid from pdm_domain where domislocal = 0;
  i := cannotdo_pis.FIRST;
  while i is not null loop
    v_pi := i;
    v_errid := cannotdo_pis(i);
    v_sqlstmt := 'insert into pitowcstatus@'||dbLinkName||
                 ' (piid, domainid, status, errid) ' ||
                 ' values ( :1, :2, :3, :4 )';
--    putpipe (v_sqlstmt);
    begin
      execute immediate v_sqlstmt using i, v_domainid, MIG_CANNOTDO, v_errid;
    exception when DUP_VAL_ON_INDEX then
      null; -- ignore it
    end;
    i := cannotdo_pis.NEXT(i);
  end loop;
--  putpipe('done cannotdo pis');

--  putpipe ('cannotdo pivs :'||cannotdo_pivs.count);
  i := cannotdo_pivs.FIRST;
  while i is not null loop
    v_piv := i;
    v_errid := cannotdo_pivs(i);
    v_sqlstmt := 'insert into pivtowcstatus@'||dbLinkName||
                 ' (pivid, domainid, status, errid) ' ||
                 ' values ( :1, :2, :3, :4 )';
--   putpipe (v_sqlstmt);
    begin
      execute immediate v_sqlstmt using i, v_domainid, MIG_CANNOTDO, v_errid;
    exception when DUP_VAL_ON_INDEX then
--      putpipe('ignored piv '||i);
      null; -- ignore it
    end;
    i := cannotdo_pivs.NEXT(i);
  end loop;
--  putpipe('done cannotdo pivs');

  v_secpivs.delete;
--  putpipe ('good primary pivs :'||primarypivs.count);
  i := primarypivs.FIRST;
  while i is not null loop
    v_piv := primarypivs(i);
    v_secpiv := secondarypivs(i);
--    putpipe('pair is '||v_piv||' and '||v_secpiv);
    v_secpivs(v_secpiv) := 1;
    v_state := secondaryState(i);
    v_sqlstmt := 'insert into pivtowcstatus@'||dbLinkName||
                 ' (pivid, domainid, hasSecondary, status) ' ||
                 ' values ( :1, :2, :3, :4 )';
--   putpipe (v_sqlstmt);
    begin
      execute immediate v_sqlstmt using v_piv, v_domainid, 1, MIG_UNFINISHED;
    exception when DUP_VAL_ON_INDEX then
      null; -- ignore it
    end;
    v_sqlstmt := 'insert into secondaryPIVs@'||dbLinkName||
                 ' (PrimaryPIVId, domainid, SecondaryPIVId, OutOfDate) ' ||
                 ' values ( :1, :2, :3, :4 )';
--   putpipe (v_sqlstmt);
    begin
      execute immediate v_sqlstmt using v_piv, v_domainid, v_secpiv, v_state;
    exception when DUP_VAL_ON_INDEX then
      null; -- ignore it
    end;
    i := primarypivs.NEXT(i);
  end loop;
--  putpipe('done primary pivs');

--  putpipe ('good sec pivs :'||v_secpivs.count);
  i := v_secpivs.FIRST;
  while i is not null loop
    v_piv := i;
    open fileinfo(v_piv);
    fetch fileinfo into v_fid, v_fname, v_fsize, v_fshost, v_fsport, v_poolpath, v_fmapname;
--    putpipe ('piv is '||v_piv);
    v_cat := secondaryCat(v_piv);
--    putpipe ('and category is '||v_cat);
--    putpipe ('piv: '||v_piv||', filename:'||v_fname||', port:'||v_fsport||', mapname:'||v_fmapname);
    close fileinfo;
    v_sqlstmt := 'insert into pivtowcstatus@'||dbLinkName||
                 ' (pivid, domainid, documenttype, status, fileid, filename, filesize, fileserverhost, fileserverport, ' ||
                 ' filepoolpath, filemapname, category, filestatus, errid) ' ||
                 ' values ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)';
--   putpipe (v_sqlstmt);
    begin
      if v_fmapname is null then
        v_status := MIG_CANNOTDO;
        v_filestatus := MIG_CANNOTDO;
        v_errid := ERR_FILE_NOT_EXIST;
      else
        v_status := MIG_SUCCESS;
        v_filestatus := null;
        v_errid := null;
      end if;
      execute immediate v_sqlstmt using i, v_domainid, EPMSECONDARY, v_status, v_fid, v_fname, v_fsize, v_fshost, v_fsport, v_poolpath, v_fmapname, v_cat, v_filestatus, v_errid;
    exception when DUP_VAL_ON_INDEX then
      null; -- ignore it
    end;
    i := v_secpivs.NEXT(i);
  end loop;
--  putpipe('done secondary pivs');

--  putpipe ('good sec pis :'||secondarypis.count);
  i := secondarypis.FIRST;
  while i is not null loop
    v_pi := i;
    v_sqlstmt := 'insert into pitowcstatus@'||dbLinkName||
                 ' (piid, domainid, documenttype, status)' ||
                 ' values ( :1, :2, :3, :4 )';
--   putpipe (v_sqlstmt);
    begin
      execute immediate v_sqlstmt using i, v_domainid, EPMSECONDARY, MIG_SUCCESS;
    exception when DUP_VAL_ON_INDEX then
      null; -- ignore it
    end;
    i := secondarypis.NEXT(i);
  end loop;
--  putpipe('done secondary pis');

END insertStatuses;

procedure mig_processReplGtwObjects (dbLinkName IN varchar2,
                                     IlinkDomainId VARCHAR2 ) IS
  procedure dropTempTable(p_temptable in varchar2);
  
  function isCWPExists return number;
  --procedure addTiming(step number, level number); 
  cursor replDomains (cv_piname varchar2, cv_domainId number) is
    select REPLICDOMAINID from pdm_registerpi_view where name = cv_piname and MAINBRDOMAINID = cv_domainId;
    
  cursor depCur (cv_pivid number) is
    select FOUR_NUMBER_COLUMNS_R(dgid, piid, dgdeptype, 1) from pdm_dependencygraph 
     where pivid = cv_pivid;

  cursor c_Get_EpmLinkSall (cv_docid number) is
    select FOUR_NUMBER_COLUMNS_R(epmLinkId, linkType, depType, docMaster)  
    from temp_epmLinksall where docid = cv_docid;  

  cursor getRegPIId( cv_Name varchar2, cv_domain number) is
    select regpiid from pdm_registerpi_view where name = cv_Name and MAINBRDOMAINID = cv_domain;

  v_sqlstmt varchar2(4000); v_memrefinsstmt varchar2(400); 
  v_sqlstmt1 varchar2(400);v_memrefstmt varchar2(400); 
  v_gwsqlstmt varchar2(400); v_repsqlstmt varchar2(400);
  v_edrepsqlstmt varchar2(400); v_wdrepsqlstmt varchar2(400); v_loopsqlstmt varchar2(2000);
  v_lnkCountstmt varchar2(400); v_thirdpartysqlstmt varchar2(400);
  v_piid number; v_masterid number; v_stpiid number; v_masterPIV number; v_pidoctype number;v_pivdoctype number;
  v_migDomainId number; v_docid number; v_pivid number; v_domainid number; v_guid number;
  v_localPIVId number; v_origPIVId number; v_origDomainId number; v_masterDomainId number;
  v_origPIId number; v_masterPIVId number; v_attrstatus number;
  v_guidstr varchar2(200); v_disname varchar2(200) := 'Windchill'; v_compName varchar2(200);
  v_dgid number;  v_depType number; v_linkid number; v_linkType number; v_lnkCount number;
  pubPIVCount number; pivCount number; pivExists number;
  v_piname pdm_productitem.piname%TYPE;
  cannotDoNameConflPIs PDMArrayId; piDocTypes PDMArrayId; pivDocTypes PDMArrayId;
  isPublished boolean; gateWayObj boolean; replObj boolean;
  domainFound boolean; wcModified boolean; migratedFromHere boolean; isReplica boolean;
  ilGatewayed boolean := true; isMigrated boolean := false; isGateWayed boolean := false;
  ignorePIV boolean;
  v_regPIId number;

  i number;

  ilDomainId NUMBER := to_number(IlinkDomainId);
  wcDomainId NUMBER;

  successPIs PDMArrayId; masterPIVs PDMArrayId; masterPIs PDMArrayId;masterDeps PDMArrayId; dgids PDMArrayId; piStatus PDMArrayId; pivStatus PDMArrayId;
  depTypes PDMArrayId; updatePIVs PDMArrayId; wcModifiedPIs PDMArrayId; replDomainIds PDMArrayId;
  piType PDMArrayId; updatePIs PDMArrayId; 
  epmlinkids PDMArrayId; linktypes PDMArrayId;  docmids PDMArrayId; depTypes2 PDMArrayId;
   j number; arrct number; v_partMid number; v_partstatus number; v_errid number;v_pivscount number;
  v_partid number; v_replicaPIV number; v_masterDomain number; v_prevDocId number;
  tmPIs MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER(); tmDocMasters MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER(); tmPiStatus MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER();
  tmPIVs MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER(); tmDocs MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER();
  tmDeps MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER(); tmLinkIds MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER(); tmDeptypes MY_TABLE_OF_NUMBER := MY_TABLE_OF_NUMBER();

  piCur RefCur;pivCur RefCur; docCur RefCur; domCur RefCur; guidCur RefCur; partCur RefCur; migCur RefCur; gatewayCur RefCur;
  
  --TYPE RecList IS TABLE OF depCur%ROWTYPE;
  --deps RecList;
  
  v_epmlinks_t FOUR_NUMBER_COLUMNS_T;
  v_deps_t FOUR_NUMBER_COLUMNS_T;
  v_migrated number; v_gatewaycon number; v_pitype number; v_pistatus number;v_newpistatus number;v_pivstatus number;
  v_piidstmt varchar2(400);  
  v_delete_current VARCHAR2(400);
  v_batchsize number :=10000; v_countpis number; 
  v_finishflag boolean := false;
 -- ctime date;ntime date;totaltime number;x number;
  v_successpiflag boolean;v_temp number;
  ctime date; ntime date; totaltime number;
  v_target_guid varchar2(200); --for cwp objects 
  v_target_ownership varchar2(400); --for cwp objects
    procedure dropTempTable(p_temptable in varchar2) is
   begin
         execute immediate 'drop table '||p_temptable;
   exception when others then
          null; -- ignore
   end;
  function isCWPExists return number is
    
    x number;
    begin
      select 1 into x from all_tables where table_name='CWP_REGISTERPI' and rownum<=1;
      return x;
    exception when others then
      x :=0;
      return x;
  end isCWPExists;
    /* procedure addTiming(step number, level number) is
    begin    
    --level =1 start process ; 2 middle of process  or  end of process
     if level=1 then 
        select sysdate into ctime from dual; 
     else  
        select sysdate into ntime from dual;
        totaltime :=to_number(ntime-ctime)*24*60*60;--ss
        insert into record_time values (step, totaltime);
        select sysdate into ctime from dual;  
        commit;
      end if;          
   end;
   
   */
   
BEGIN
  
  -- initialize
  successPIs.delete; masterPIVs.delete; masterPIs.delete;masterDeps.delete; dgids.delete; piStatus.delete; piType.delete;
  depTypes.delete; wcModifiedPIs.delete; piType.delete; piDocTypes.delete;depTypes2.delete;
  cannotDoNameConflPIs.delete;
  
  -- find windchill domainid for gatewayed windchill
  -- putpipe('ildomainid is '||ilDomainId);
  v_sqlstmt := 'select guid from repository@'||dbLinkName||' where displayname = :disname';
  open guidCur for v_sqlstmt using v_disname;
  loop
    fetch guidCur into v_guidstr;
    exit when guidCur%NOTFOUND;
  end loop;
  close guidCur;

  begin
    select domainid into wcDomainId from pdm_domain d, pdm_protocol p
     where d.domname = v_guidstr
       and d.protocolid = p.protocolid
       and p.protocolname = 'protocol_intralink_wcgateway';

  exception when NO_DATA_FOUND then
    -- no gateway from this intralink
    ilGatewayed := false;
  end;
  
   begin
        execute immediate 'drop table temp_pitowcstatus';
    exception when others then 
        null;
    end;
    --addTiming(0, 1);
   execute immediate ' CREATE TABLE temp_pitowcstatus as '||
                    ' select piid, domainid, epmdocumentmasterid masterid, status '||
                    ' from  pitowcstatus @'|| dblinkname ;
   begin
     execute immediate 'CREATE INDEX IDX1_temp_pitowcstatus ON temp_pitowcstatus (masterid) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX2_temp_pitowcstatus ON temp_pitowcstatus (status) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX3_temp_pitowcstatus ON temp_pitowcstatus (piid, domainid) TABLESPACE PDM_INDEX ';
   exception when others then
     null;
   end;
   
   begin
        execute immediate 'drop table temp_pivtowcstatus';
    exception when others then 
        null;
    end;
   execute immediate ' CREATE TABLE temp_pivtowcstatus as '||
                    ' select pivid, domainid, epmdocumentid docid '||
                    ' from  pivtowcstatus@'|| dblinkname ;
   begin
     execute immediate 'CREATE INDEX IDX1_temp_pivtowcstatus ON temp_pivtowcstatus (docid) TABLESPACE PDM_INDEX ';
   exception when others then
     null;
   end;
   begin
        execute immediate 'drop table temp_remoteobjinfo';
  exception when others then 
        null;
  end;
  --For CWP objects, the remoteobjectid format as different way (1:piid  or 2:pivid), so we need have a different function to get pipivid
  if isCWPExists=1 then  --there is cwp data
    execute immediate ' CREATE TABLE temp_remoteobjinfo as '||
                      ' select * from ('|| --Gateway objects
                      ' select ro.classnamekeya3 classname, ro.ida3a3 mdid, to_number(bitand(rid.remoteobjectid/2,281474976710655)) pipivid, rep.guid, 0 cwpflag  '||
                      ' from  remoteobjectid@'||dbLinkName||' rid, remoteobjectinfo@'||dbLinkName||' ro, repository@'||dbLinkName||' rep '||
                      ' where rid.ida2a2=ro.remoteid and instr(rid.remoteobjectid,'':'')=0 and ro.birthid = rep.ida2a2(+) and ro.classnamekeya3 in (''wt.epm.EPMDocumentMaster'', ''wt.epm.EPMDocument'')'||
                      ' and  TRANSLATE(rid.remoteobjectid,''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ:@#$&^*(){[]}|\+~?/><'', ''0123456789'')=rid.remoteobjectid '||
                      ' union '|| --CWP objects exported from IL Source
                      ' select ro.classnamekeya3 classname, ro.ida3a3 mdid, to_number(substr(rid.remoteobjectid,3)) pipivid, rep.guid, 1 '||
                      ' from  remoteobjectid@'||dbLinkName||' rid, remoteobjectinfo@'||dbLinkName||' ro, repository@'||dbLinkName||' rep '||
                      ' where rid.ida2a2=ro.remoteid and instr(rid.remoteobjectid,'':'')=2 and ro.birthid = rep.ida2a2(+) and ro.classnamekeya3 in (''wt.epm.EPMDocumentMaster'', ''wt.epm.EPMDocument'')'||
                      ')';
                      
 else  --there is no cwp data
   execute immediate ' CREATE TABLE temp_remoteobjinfo as '||
                     ' select ro.classnamekeya3 classname, ro.ida3a3 mdid, to_number(bitand(rid.remoteobjectid/2,281474976710655)) pipivid, rep.guid '||
                     ' from  remoteobjectid@'||dbLinkName||' rid, remoteobjectinfo@'||dbLinkName||' ro, repository@'||dbLinkName||' rep '||
                     ' where rid.ida2a2=ro.remoteid and instr(rid.remoteobjectid,'':'')=0 and ro.birthid = rep.ida2a2(+) and ro.classnamekeya3 in (''wt.epm.EPMDocumentMaster'', ''wt.epm.EPMDocument'')'||
                     ' and  TRANSLATE(rid.remoteobjectid,''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ:@#$&^*(){[]}|\+~?/><'', ''0123456789'')=rid.remoteobjectid ';

 end if;           
 begin
     execute immediate 'CREATE INDEX IDX1_temp_remoteobjinfo ON temp_remoteobjinfo (mdid) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX2_temp_remoteobjinfo ON temp_remoteobjinfo (classname) TABLESPACE PDM_INDEX ';
 exception when others then
     null;
 end;
   v_sqlstmt :=
           ' select * from ('||
           'select pi.piid, pi.piname, master.ida2a2, '||EPMDOCTYPE||
           ' from pdm_productitem pi, epmdocumentmaster@'||dbLinkName||
           ' master where '||
           'pi.piname = master.cadname '||
           ' union '||
           'select pi.piid, pi.piname, ro.mdid, '||EPMDOCTYPE||
           ' from pdm_productitem pi, temp_remoteobjinfo ro'||
           ' where ro.guid ='''|| IlinkDomainId||''''||
           ' and pi.piid = ro.pipivid and ro.classname=''wt.epm.EPMDocumentMaster'' ' ||
           '  union '||
           ' select pi.piid, pi.piname, master.ida2a2, ' ||WTDOCTYPE||
           ' from pdm_productitem pi, wtdocumentmaster@'||dbLinkName||
           ' master where '||
           ' pi.piname = master.name '|| 
           ') m where '|| --' rownum<=20 and '||
          ' not exists ('||
           ' select 1 from temp_pitowcstatus  st '||
           'where  st.piid=m.piid and st.domainid = '|| ilDomainid ||')';  
   --delete from record_time;
   --should include CWP objects 
 
  --step 1
  -- this query will find all the epmdocument and wtdocument masters having same names as intralink PIs
  -- putpipe ('find all common pis');
   --addTiming(1, 2);
  open piCur for v_sqlstmt ;
   loop 
    fetch piCur into v_piid, v_piname, v_masterid, v_pidoctype;
    exit when piCur%NOTFOUND;
--    putpipe(v_piid||', '||v_piname||', '||v_masterid||', '||v_doctype);
    isGateWayed := false;
    isMigrated := false;
    isReplica := false;
    v_masterDomainId := null;
    piDocTypes(v_piid) := v_pidoctype;

    v_origPIId := null;
    begin
     execute immediate  'select piid, domainid from temp_pitowcstatus where masterid = :masterid and piid is not null and rownum<=1 '
                          into v_origPIId, v_origDomainId using v_masterid;
      isMigrated := true;
    exception when NO_DATA_FOUND then 
      null;
    end ;

    -- If windchill object is migrated we dont need to find out if it was gateway published also.
    -- however if the windchill object is not a migrated object, we need to find out if it is gatewayed
    -- Only EPMDOCTYPE objects can be gateway published.
    if v_pidocType = EPMDOCTYPE and not isMigrated then
      -- check if this windchill object is gatewayed
      -- gatewayed windchill object has a remoteobjectid (ufid) in remoteobjectinfo table
      v_origPIId := null;
      
      v_loopsqlstmt :=  'select pipivid piid, ro.guid from temp_remoteobjinfo ro '||
                   'where ro.mdid= :masterid and ro.classname= ''wt.epm.EPMDocumentMaster'' ';
      open gatewayCur for v_loopsqlstmt using v_masterid;
      fetch gatewayCur into v_origPIId, v_origDomainId;
      close gatewayCur;
      if v_origPIId is not null then
        isGateWayed := true;
      end if;
    end if;
    -- different possibilities
    -- ILObject is replica/Master and WCObject is neither migrated nor gatewayed -> nameconfl
    -- ILObject is replica/Master and WCObject is migrated -> find the migrating ildomain for this WCobject
    --          and if it is one of the ILObejcts domain neighborhood, mark replica as replica success
    --          and master as updating else nameconfl
    -- ILObject is replica/master and WCObject is gatewayed -> find the publishing domain for this WCObject
    --          and if it is one of the ILObejcts domain neighborhood, mark replica as replica success
    --          and master as updating else nameconfl

  -- Here isGateWayed means either true gateway objects or CWP objects , since for both objects we do the same thing , we will not distinguish them
  -- the only extra thing we need do for CWP objects is that 
  -- Migrate GlobalId-> create entries in remoteobjectinfo and owningrepositoryLocalObject 
  -- create entry on 
    if isGateWayed or isMigrated then
      -- first decide if this is a replica in this commonspace.
      -- if this pi is replica, it will have an entry in the pdm_registerpi of this domain
      isReplica := true;
      begin
        select mainbrdomainid into v_masterDomainId from pdm_registerpi_view where name = v_piname and REPLICDOMAINID = ilDomainId;
      exception when NO_DATA_FOUND then
        -- this object is not a replica, but was created in this intralink
--      putpipe('not a replica');
        isReplica := false;
        v_masterDomainId := ilDomainId;
      end;
      -- find the neigbhorhood domains for this PI
      -- neighborhood domains include the masterdomain and all the replica domains
      -- If the origiinating Domain of the WCObject is one of the neighborhood domains
       v_successpiflag:=true;
       if v_origDomainId!=v_masterDomainId then
           begin
               select 1 into v_temp
               from pdm_registerpi_view 
               where name = v_piname and MAINBRDOMAINID =v_masterDomainId
                    and REPLICDOMAINID= v_origDomainId and rownum<=1;
           exception when NO_DATA_FOUND then
              v_successpiflag:=false;
          end; 
       end if ;
       if  v_successpiflag then
            if isMigrated then
              piType(v_piid) := REPLICATION;
              successPIs(v_piid) := v_masterid;
            else
              if v_piid = v_origPIId
              then successPIs(v_piid) := v_masterid;
              piType(v_piid) := GATEWAY;
            end if;
            end if;
            if isReplica then
             -- if curr PIV is second replica, then mark pistatus as MIG_REPL_UPDATING
              if(v_masterDomainId = v_origDomainId) then
                piStatus(v_piid) := MIG_REPLSUCCESS;
              else
                piStatus(v_piid) := MIG_REPL_UPDATING;	  
              end if;
            else
--        putpipe (v_piid||' is updating');
          piStatus(v_piid) := MIG_UPDATING;
        end if;
      else
        -- this WCObject originated from an Intralink other than part of this object's neighborhood
        cannotDoNameConflPIs(v_piid) := v_masterid;
--      putpipe(v_piid||' is cannotdo confl1');
      end if;
      
    else
      -- this object was independently created in PDMLink
      --cannotDoNameConflPIs(v_piid) := v_masterid;
      null;
--      putpipe(v_piid||' is cannotdo confl2');
    end if;
    
    
  end loop;
   close piCur;
    begin
          execute immediate 'truncate table temp_processpis';
    exception when others then 
          execute immediate ' CREATE TABLE temp_processpis (MASTERID NUMBER, PIID NUMBER, DOCTYPE NUMBER, PITYPE NUMBER, PISTATUS NUMBER, processflag number default 0)' ;
    end;
   
    i := successPIs.FIRST;
   while i is not null loop
     execute immediate  'insert into temp_processpis (MASTERID, PIID , DOCTYPE, PITYPE , PISTATUS ) values '||
       '('|| successPIs(i)||', '||i||', '||piDocTypes(i)||', '||piType(i)||', '|| piStatus(i)||')';
    --   insert into temp_SUCCESSPIS (MASTERID, PIID , DOCTYPE, PITYPE , PISTATUS ) values 
     --  (successPIs(i),i,  piDocTypes(i), piType(i), piStatus(i));
      i:= successPIs.next(i);
  end loop;
  commit;
  if successPIs.count >0 then 
    v_countpis := successPIs.count ; 
  else 
    v_countpis := 0; 
  end  if;
   cannotDoNameConflPIs.delete;successPIs.delete; piDocTypes.delete; piType.delete; piStatus.delete;
  begin
    execute immediate 'CREATE INDEX IDX1_temp_processpis ON temp_processpis (PIID) TABLESPACE PDM_INDEX ';
    execute immediate 'CREATE INDEX IDX2_temp_processpis ON  temp_processpis (MASTERID) TABLESPACE PDM_INDEX ';
  exception when others then
   null;
  end;
  --get masterPivs
  --to avoid out of process memory, create temp table for remoteobjectinfo
  
   v_loopsqlstmt :='select masterid, piid, doctype, pitype, pistatus from temp_processpis' ;
   v_edrepsqlstmt := 'select doc.ida2a2, st.pivid, st.domainid from epmdocument@'||dbLinkName||
                 ' doc, temp_pivtowcstatus st where '||
                 'IDA3MASTERREFERENCE = :r and doc.ida2a2 = st.docid(+) order by doc.ida2a2';
   v_wdrepsqlstmt := 'select doc.ida2a2, st.pivid, st.domainid from wtdocument@'||dbLinkName||
                 ' doc, temp_pivtowcstatus st where '||
                 'IDA3MASTERREFERENCE = :r and doc.ida2a2 = st.docid(+) order by doc.ida2a2';
   v_gwsqlstmt := 'select doc.ida2a2, ro.pipivid pivid, ro.guid '||
                  'from epmdocument@'||dbLinkName||' doc,temp_remoteobjinfo ro '||
                  ' where IDA3MASTERREFERENCE = :r and doc.ida2a2 = ro.mdid(+) and   '||
                  ' ro.classname = ''wt.epm.EPMDocument'' ';
  --addTiming(2, 2); 
  --step 2 
  --to get all the pivs
   begin
          execute immediate 'truncate table temp_processpivs';
    exception when others then
         execute immediate ' CREATE TABLE temp_processpivs (piid NUMBER, docid NUMBER, pivid NUMBER, pivdoctype number, PIVSTATUS NUMBER)' ;
    end;
   open piCur for v_loopsqlstmt;
  loop 
 -- while i is not null loop
 --to do could retrieve the piv and pi info same time
   fetch piCur into v_masterid, v_piid, v_pidoctype, v_pitype, v_pistatus;
    exit when piCur%NOTFOUND;
--    putpipe('pi :'||i);
    --v_masterid := successPIs(i);
    updatePIVs.delete;
    updatePIs.delete;
    
    wcModified := false;
    if v_pitype = REPLICATION then
--      putpipe('replication PI');
      if v_pidoctype = EPMDOCTYPE then
        v_sqlstmt := v_edrepsqlstmt;
      else
        v_sqlstmt := v_wdrepsqlstmt;
      end if;
      -- find all documents and their pivstatus, if available, for this documentmaster
      open docCur for v_sqlstmt using v_masterid;
      v_prevDocId := -1;
      loop
        fetch docCur into v_docid, v_pivid, v_domainid;
        exit when docCur%NOTFOUND;
        -- this docid must be in the status table, otherwise this object was
        -- created in PDMLink and so this PI can not be migrated
--        putpipe(i||', '||v_masterid||', '||v_docid||', '||v_pivid||', '||v_domainid);
        ignorePIV := false;
        if v_docid <> v_prevDocId then
          v_prevDocId := v_docid;
          if v_pivid is null then
            wcModified := true;
          else
            if v_pistatus = MIG_UPDATING then
              -- ILObject is masterpiv and piv from docCur is replica piv
              -- find the masterpiv for the replica piv and put it in updatePIVs with the epmdocumentId
              begin
                select masterpivid into v_localPIVId from pdm_registerpiv_view
                 where replicpivid = v_pivid
                   and replicdomainid = v_domainid;
              exception when NO_DATA_FOUND then --need think about again
                 v_localPIVId :=null;
              end;
            else
              -- ILObject is replica piv and piv from docCur is either masterpiv or another replicapiv
              -- find its masterpiv assuming its replica
              begin
                select masterpivid, masterdomainid into v_masterPIVId, v_masterDomainId
                  from pdm_registerpiv_view
                 where replicpivid = v_pivid
                   and replicdomainid = v_domainid;

              exception when NO_DATA_FOUND then
                -- this piv must be masterpiv
                v_masterPIVId := v_pivid;
                v_masterDomainId := v_domainid;
              end;

              -- If data found then masterPIV/masterDomain is from other neighborhood intralink
              -- else (exception code) masterPIV/masterDomain is the migrated documents PIV

              ignorePIV := false;
              begin
                select replicpivid into v_localPIVId from pdm_registerpiv_view
                 where masterpivid = v_masterPIVId
                   and masterdomainid = v_masterDomainId
                   and replicdomainid = ilDomainId;
              exception when NO_DATA_FOUND then
                 -- this piv does not have a replica in this intralink so do nothing
                 ignorePIV := true;
              end;
            end if;

            if not ignorePIV then
              -- confirm that this pivid is in this intralink and belongs to this PI
              -- putpipe('masterpivid is '||v_masterPIV);
              pivExists := 0;
              select count(pivid) into pivExists from pdm_productitemversion piv, pdm_branch br
               where br.piid = v_piid
                 and br.brpath = 'main'
                 and br.brid = piv.brid
                 and piv.pivid = v_localPIVId;

              if pivExists = 1 then
  --            putpipe(v_localPIV||' with docid '||v_docid||' added to updatePIVs');
                updatePIs(v_localPIVId) := v_piid;
                updatePIVs(v_localPIVId) := v_docid;
                --We do not update piv for second replica (we'll wait for the master)
                if v_pistatus = MIG_REPL_UPDATING then
                  pivStatus(v_localPIVId) := MIG_REPLSUCCESS;
                else
                  pivStatus(v_localPIVId) :=v_pistatus;
                end if;
--              putpipe('pivid '||v_localPIVId||' has status '||pivStatus(v_localPIVId));
                pivDocTypes(v_localPIVId) := v_pidoctype;
              end if;
            end if;
          end if;
        end if;
      end loop;
      close docCur;
      
    else
      -- gateway logic will be here
      -- find all documents and their pivstatus, if available, for this gatewayed documentmaster using remoteobjectinfo
      open docCur for v_gwsqlstmt using v_masterid;
      loop
        fetch docCur into v_docid, v_pivid, v_origDomainId;
        exit when docCur%NOTFOUND;
        -- this docid must be in the remoteobjectinfo table, otherwise this object was
        -- created in PDMLink and so this PI can not be migrated
        ignorePIV := false;
        if v_pivid is null then
          wcModified := true;
        else
          -- confirm that this pivid is in this intralink and belongs to this PI
          if v_origDomainId = ilDomainId then
            -- this object was published from this Domain
            v_localPIVId := v_pivid;
          else
            -- this object was published from a domain that was in the network of this domain
            -- assuming that the originating gateway domain was a replica
            begin
              select masterPIVID, masterDomainid into v_masterPIVId, v_masterDomainId
                from pdm_registerpiv_view
               where replicpivid = v_pivid
                 and replicdomainid = v_origDomainId;
            exception when NO_DATA_FOUND then
              -- originating gateway domain was actually master
              v_masterPIVId := v_pivid;
              v_masterDomainId := v_origDomainId;
            end;
            if v_masterDomainId = ilDomainId then
              v_localPIVId := v_masterPIVId;
            else
              -- this ILObject is a replica of a masterPIVId in masterDomainId
              ignorePIV := false;
              begin
                select replicpivid into v_localPIVId from pdm_registerpiv_view
                 where masterpivid = v_masterPIVId
                   and masterdomainid = v_masterDomainId
                   and replicdomainid = ilDomainId;
              exception when NO_DATA_FOUND then
                -- this piv does not have a replica in this intralink
                ignorePIV := true;
              end;
            end if;
          end if;

          if not ignorePIV then
            pivExists := 0;
            select count(pivid) into pivExists from pdm_productitemversion piv, pdm_branch br
             where br.piid = v_piid
               and br.brpath = 'main'
               and br.brid = piv.brid
               and piv.pivid = v_localPIVId;

            if pivExists = 1 then
              updatePIs(v_localPIVId) := v_piid;
              updatePIVs(v_localPIVId) := v_docid;              
               --We do not update piv for second replica (we'll wait for the master)
                if v_pistatus = MIG_REPL_UPDATING then
                  pivStatus(v_localPIVId) := MIG_REPLSUCCESS;
                else
                  pivStatus(v_localPIVId) :=v_pistatus;
                end if;
--            putpipe('pivid '||v_localPIVId||' has status '||pivStatus(v_localPIVId));
              pivDocTypes(v_localPIVId) := v_pidoctype;
            end if;
          end if;
        end if;
      end loop;
      close docCur;
    end if;
    -- need record changed pisatus to array then update temp_processpis
    if wcModified then
      -- this PI can not be migrated since windchill has modified some of its iterations
      v_pistatus := MIG_WCMODIFIED;
      piStatus(v_piid):= v_pistatus;
    else
      -- if not modified by windchill check if there are more pivs on intralink side compared to windchill side if this pistatus is not replsuccess
     
      if v_pistatus != MIG_REPLSUCCESS then
        pubPIVCount := updatePIVs.COUNT;
        -- find the number of pivs for this PI that are on main branch
        select count(piv.pivid) into pivCount
          from pdm_productitemversion piv, pdm_branch br
         where br.piid = v_piid
           and br.brpath = 'main'
           and br.brid = piv.brid;
        if pivCount > pubPIVCount then
          --If piStatus is REPL_UPDATING, then we are only going to fill holes.
          --We DO NOT update pivs for REPL_UPDATING (we'll wait for the Master to come and update).
          if v_pistatus = MIG_REPL_UPDATING then
            v_pistatus := MIG_HOLES;
            piStatus(v_piid):= v_pistatus;
          else       
            v_pistatus := MIG_HOLES_UPDATING;
            piStatus(v_piid):= v_pistatus;
          end if;
        else
          --come here when there are NO holes to fill.
          --now if piStatus is REPL_UPDATING, then pivs are already marked as REPL_SUCCESS and
          --since no holes to fill this PI can be marked as REPL_SUCCESS
          if v_pistatus = MIG_REPL_UPDATING then
            v_pistatus := MIG_REPLSUCCESS;
             piStatus(v_piid):= v_pistatus;
          end if;
        end if;        
      end if;
    end if;

    j := updatePIVs.FIRST;
    while j is not null loop
      masterPIs(j) := updatePIs(j);
      masterPIVs(j) := updatePIVs(j);
      if wcModified then
        pivStatus(j) := MIG_WCMODIFIED;
      end if;
      j := updatePIVs.NEXT(j);
    end loop;
    --insert batch size pivs to temp table 
    if masterPIVs.COUNT>=v_batchsize then
       i := masterPIVs.FIRST;
     while i is not null loop       
       execute immediate  'insert into temp_processpivs (piid, docid, pivid, pivdoctype, pivstatus ) values '||
         '('|| masterPIs(i)||','|| masterPIVs(i)||', '||i||', '||pivDocTypes(i)||', '||pivStatus(i)||')';
        i:= masterPIVs.next(i);
    end loop;
    commit;
       masterPIs.delete;masterPIVs.delete;pivStatus.delete;pivDocTypes.delete;updatePIVs.delete;updatePIs.delete;
    end if;
  end loop;
  close piCur;
  i := piStatus.FIRST;
  while i is not null loop
       execute immediate 'update temp_processpis set pistatus ='||piStatus(i)||' where piid='||i;
        i:=piStatus.next(i);
  end loop;
  piStatus.delete;
   if masterPIVs.COUNT>0 then
       i := masterPIVs.FIRST;
     while i is not null loop       
       execute immediate  'insert into temp_processpivs (piid, docid, pivid, pivdoctype, pivstatus ) values '||
         '('|| masterPIs(i)||','|| masterPIVs(i)||', '||i||', '||pivDocTypes(i)||', '||pivStatus(i)||')';
        i:= masterPIVs.next(i);
    end loop;
    masterPIs.delete;masterPIVs.delete;pivStatus.delete;pivDocTypes.delete;updatePIVs.delete;updatePIs.delete;
    commit;
    end if;
  execute immediate   'select count(*) from temp_processpivs' into v_pivscount;
  if v_pivscount > 0 then
    begin
     execute immediate 'CREATE INDEX IDX1_temp_processpivs ON temp_processpivs (PIVID) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX2_temp_processpivs ON temp_processpivs (PIID) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX3_temp_processpivs ON temp_processpivs (docid) TABLESPACE PDM_INDEX ';
     execute immediate 'CREATE INDEX IDX4_temp_processpivs ON temp_processpivs (pivstatus) TABLESPACE PDM_INDEX ';
    exception when others then
     null;
    end;
       begin
            execute immediate 'drop table temp_wtpartmasterids';
      exception when others then
           null;
      end;
       execute immediate ' CREATE TABLE temp_wtpartmasterids as '||
                         ' select  EPMDocumentMasterId, wtpartmasterid  from pitowcstatus@'||dbLinkName||
                          ' where wtpartmasterid is not null';
     begin
     execute immediate 'CREATE INDEX IDX1_temp_wtpartmasterids ON temp_wtpartmasterids (EPMDocumentMasterId) TABLESPACE PDM_INDEX ';
     exception when others then
     null;
    end;
  
      begin
            execute immediate 'drop table temp_wtpartids';
      exception when others then
           null;
      end;
       execute immediate ' CREATE TABLE temp_wtpartids as '||
                         ' select  epmdocumentid, wtpartid  from pivtowcstatus@'||dbLinkName||
                          ' where wtpartid is not null';
    begin
     execute immediate 'CREATE INDEX IDX1_temp_wtpartids ON temp_wtpartids (epmdocumentid) TABLESPACE PDM_INDEX ';
    exception when others then
     null;
    end;
  
   --addTiming(3, 2); 
     v_piidstmt :='select masterid, piid, doctype, pitype, pistatus '||
                   'from  temp_processpis '||
                   ' where  rownum<='||v_batchsize||' and processflag=0';
                   
    --step 3
   -- process all successPIs to decide the pivs that should be updating and the PIs that have holes and
   -- the PIs and PIVs that should be repl success
   -- batch size pis 
   --and get all dep 
   --and insert them to pitowcstatus, pivtowcstatus, deptowcstatus and thirdpartydata
    while  v_countpis>0 loop
     begin
          execute immediate 'drop table temp_batch_processpis';
    exception when others then
          null;
    end;
    execute immediate ' CREATE TABLE temp_batch_processpis as '|| v_piidstmt ;
     begin
      execute immediate 'CREATE INDEX IDX1_temp_batch_processpis ON temp_batch_processpis (piid) TABLESPACE PDM_INDEX '; 
    exception when others then 
      null;
    end;
    v_sqlstmt :='select masterid, piid, doctype, pitype, pistatus from temp_batch_processpis' ;
  
   begin
      execute immediate 'drop table temp_epmLinksall';
    exception when others then
      null; -- ignore
    end;
   --temp_epmLinks will store link info for all docids
   
   execute immediate 'create table temp_epmLinksall as '||
               ' select  ida2a2 epmLinkId, 0 linkType, deptype, '||
                '      IDA3B5 docMaster,  ida3a5 docid'|| 
                '      from epmmemberlink@'||dbLinkName||           
                ' union all '||
                ' select ida2a2 epmLinkId, 1 linkType, deptype, '||
                '      IDA3B5 docMaster, ida3a5 docid '||
                '       from epmreferencelink@'||dbLinkName;         
    begin
      execute immediate 'CREATE INDEX IDX1_temp_epmLinksall ON temp_epmLinksall (docid) TABLESPACE PDM_INDEX '; 
      execute immediate 'CREATE INDEX IDX2_temp_epmLinksall ON temp_epmLinksall (docid,deptype) TABLESPACE PDM_INDEX ';
    exception when others then 
      null;
    end;  
    v_masterid:=null;
    v_loopsqlstmt :=' select piv.docid, piv.pivid '||
                  ' from temp_processpivs piv, temp_batch_processpis pi '||
                  ' where pi.piid=piv.piid and piv.pivstatus ='|| MIG_UPDATING;
  open pivCur for v_loopsqlstmt;
  loop      
    fetch pivCur into v_docid, v_pivid;
     exit when pivCur%NOTFOUND;
          open c_Get_EpmLinkSall(v_docid);
          fetch c_Get_EpmLinkSall 
          BULK COLLECT INTO v_epmlinks_t;
          close c_Get_EpmLinkSall;       

  --  execute immediate 'select epmLinkId, linkType, depType, docMaster from temp_epmLinksall  where docid = :docid ' 
  --  BULK COLLECT INTO epmlinkids, linktypes, deptypes2, docmids  using v_docid;  
    if v_epmlinks_t.count > 0 then        
       /*EXECUTE IMMEDIATE
       'BEGIN
          select FOUR_NUMBER_COLUMNS_R(dgid, piid, dgdeptype, 1) 
          BULK COLLECT INTO :deps_t
          from pdm_dependencygraph 
            where pivid ='||to_char(v_pivid)||' ;  
        END;'
     USING OUT v_deps_t;
     */
      open depCur(v_pivid);
        FETCH depCur BULK COLLECT INTO v_deps_t;
      close depCur;
       if v_deps_t.count>0 then
          FOR i IN v_deps_t.FIRST .. v_deps_t.LAST
          loop
              v_dgid := v_deps_t(i).col1; v_piid :=v_deps_t(i).col2;v_depType := v_deps_t(i).col3;         
                begin 
                     begin 
                          execute immediate 'select masterid  from temp_processpis where piid=:piid'
                                       into v_masterid using v_piid ;
                      exception when NO_DATA_FOUND then
                          execute immediate 'select masterid  from temp_pitowcstatus where piid=:piid and domainid=:ildomainid  and status <> '||MIG_CANNOTDO                     
                                    into v_masterid using v_piid, ilDomainid ;
                      end ;                 
                      if v_epmlinks_t.count>0 then
                         j := v_epmlinks_t.first;
                          while  j is not null loop--for j in epmlinkids.first..epmlinkids.last loop
                          if v_epmlinks_t(j).col4=v_masterid and v_epmlinks_t(j).col3=v_depType then
                            dgids(v_dgid) := v_dgid;
                            masterDeps(v_dgid) := v_epmlinks_t(j).col1;
                            depTypes(v_dgid) := v_epmlinks_t(j).col2;
                            v_epmlinks_t.delete(j); 
                            exit;
                          end if;
                           j := v_epmlinks_t.next(j);
                         end loop;
                      end if;
              exception when NO_DATA_FOUND then
                  null;
              end;
            end loop; 
         end if;
        v_epmlinks_t.delete; 
      end if;  
  end loop;
  close pivCur;
  v_sqlstmt := 'insert into temp_deptowcstatus '||
               ' (DGId, DomainId, EPMLinkId, LinkType, Status, attrstatus) '||
               'values(:1, :2, :3, :4, :5, :6)';
   begin
        execute immediate 'truncate table temp_deptowcstatus';
    exception when others then
        execute immediate ' CREATE TABLE temp_deptowcstatus as select * from  deptowcstatus@'||dbLinkName||' where rownum<1';
    end;
    i := masterDeps.FIRST;
    while i is not null loop
         execute immediate v_sqlstmt using dgids(i), ilDomainId, masterDeps(i), depTypes(i), MIG_UPDATING, MIG_UPDATING;     
        i := masterDeps.next(i);
   end loop;
   masterDeps.delete; depTypes.delete;dgids.delete;  
   commit;
  --addTiming(4, 2); 
  --step 4 
  --now insert batch size data to status tables
   execute immediate  'insert into thirdpartydata@'||dbLinkName||'(EPMDocumentMasterId, DomainId) '||
            ' select  masterid, '||ilDomainid||' domainid '||
            ' from (select masterid, piid, doctype, pitype, pistatus, '||
            ' decode(pistatus, '|| MIG_WCMODIFIED||', '||MIG_REPLSUCCESS||', pistatus) newpistatus,'||
            ' decode(pistatus, '|| MIG_REPLSUCCESS||', null, '|| MIG_WCMODIFIED||', null,'||MIG_UPDATING||') attrstatus,'||
            '(select  wtpartmasterid  from temp_wtpartmasterids where EPMDocumentMasterId = masterid and rownum<=1) partmid '||
            ' from temp_batch_processpis) '||
            ' where   decode(pitype,'||GATEWAY||', '||MIG_UNDECIDED||', '||
            '(decode(partMid, null, null, (decode(pistatus,'||MIG_REPLSUCCESS||','||MIG_REPLSUCCESS||','||MIG_UNDECIDED||'))))) ='||MIG_UNDECIDED;         

   execute immediate  'insert into pitowcstatus@'||dbLinkName||
            ' (PIId, DomainId, documenttype, EPMDocumentMasterId, Status, attrstatus, wtpartmasterid, partstatus) '||
            ' select  piid, '||ilDomainid||' domainid,  doctype,masterid,  newpistatus,attrstatus,partmid, '||
            ' decode(pitype,'||GATEWAY||', '||MIG_UNDECIDED||', '||
            '(decode(partMid, null, null, (decode(pistatus,'|| MIG_WCMODIFIED||', '||MIG_REPLSUCCESS||','||MIG_REPLSUCCESS||','||MIG_REPLSUCCESS||','||MIG_UNDECIDED||'))))) partstatus '||
            'from (select masterid, piid, doctype, pitype, pistatus, '||
            'decode(pistatus, '|| MIG_WCMODIFIED||', '||MIG_REPLSUCCESS||', pistatus) newpistatus,'||
            'decode(pistatus, '|| MIG_REPLSUCCESS||', null, '|| MIG_WCMODIFIED||', null,'||MIG_UPDATING||') attrstatus,'||
            '(select  wtpartmasterid  from temp_wtpartmasterids where EPMDocumentMasterId = masterid and rownum<=1) partmid '||
            ' from temp_batch_processpis) ';
            
    execute immediate 
            'insert  into pivtowcstatus@'||dbLinkName||
            ' (PIVId, DomainId, documenttype, EPMDocumentId, Status, attrstatus, wtpartid) '||
            'select   pivid, '''||ilDomainid||''' , pivdoctype, docid,'||
            'decode(pivstatus, '|| MIG_WCMODIFIED||', '||MIG_REPLSUCCESS||', pivstatus) pivstatus,'||
            'decode(pivstatus, '|| MIG_REPLSUCCESS||', null, '|| MIG_WCMODIFIED||', null,'||MIG_UPDATING||') attrstatus,'||
            '(select wtpartid  from temp_wtpartids where epmdocumentid = docid  and rownum<=1) partid'||
            ' from temp_processpivs piv, temp_batch_processpis pi '||
            ' where piv.piid=pi.piid ';
                  
    execute immediate 'insert into deptowcstatus@'||dbLinkName||
           ' (DGId, DomainId, EPMLinkId, LinkType, Status, attrstatus) '||
           ' select DGId, DomainId, EPMLinkId, LinkType, Status, attrstatus from temp_deptowcstatus';
   execute immediate  ' update  temp_processpis'||
          ' set processflag = 1 '||
          ' where (piid, masterid) in '||
          '( select piid, masterid  from temp_batch_processpis)';
   commit;
   execute immediate 'select count(*) from ('||v_piidstmt||')'
                      into v_countpis;
    --addTiming(5, 2); 
  end loop;
  end if;
  if isCWPExists=1 then 
  --CWP case 4 :
-- for objects CWP imported from target system shouldn't do anything ecept create entries in pitowcstats and pivtowcstatus with status=MIG_REPLICASUCCESS
 -- get target system ownership   

      execute immediate 'select guid, guid||''@''||lastknowndomain from repository@'||dbLinkName||' where local=1'
      into v_target_guid, v_target_ownership ;
      
       execute immediate 
      'insert into pitowcstatus@'||dbLinkName||' (PIID,DOMAINID,STATUS,EPMDOCUMENTMASTERID)'||
      ' select pi.localpiid, '||ilDomainid||' domainid,'||MIG_REPLSUCCESS||' status, ro.mdid '||
      ' from cwp_registerpi pi, temp_remoteobjinfo ro '||
      ' where  pi.OWNERSHIP='''||v_target_ownership||''' and ro.guid='''||v_target_guid||''' and ro.pipivid= pi.localpiid and '||
      ' ro.classname = ''wt.epm.EPMDocumentMaster'''; 
      
      execute immediate 
      'insert into pivtowcstatus@'||dbLinkName||'(PIVID,DOMAINID,STATUS,EPMDOCUMENTID)'||
      ' select pi.localpiid, '||ilDomainid||' domainid,'||MIG_REPLSUCCESS||' status, ro.mdid '||
      ' from cwp_registerpi pi, cwp_registerpiv piv, temp_remoteobjinfo ro '||
      ' where pi.OWNERSHIP='''||v_target_ownership||'''  and pi.globalid=piv.pi_globalid  and '||
      '  ro.guid='''||v_target_guid||''' and ro.pipivid = piv.localpivid  and ro.classname = ''wt.epm.EPMDocument''';
  end if;
   --drop temp table
  dropTempTable('temp_batch_processpis');
  dropTempTable('temp_processpivs');
  dropTempTable('temp_processpis');
  dropTempTable('temp_remoteobjinfo'); 
  dropTempTable('temp_pitowcstatus');
  dropTempTable('temp_pivtowcstatus');
  dropTempTable('temp_deptowcstatus');
  execute immediate 'truncate table temp_epmLinksall';
  dropTempTable('temp_wtpartmasterids');
  dropTempTable('temp_wtpartids');
  --addTiming(6, 2); 
END mig_processReplGtwObjects;


PROCEDURE mig_markUnFinishedPIs( dbLinkName IN varchar2,
                                 IlinkDomainId VARCHAR2 ) IS

  ilDomainId NUMBER := to_number(IlinkDomainId);
  v_dbmaxpivid number; v_maxpivid number := 0; v_piid number;
  sqlstmt varchar2(2000); pi_sqlstmt varchar2(2000); part_sqlstmt varchar2(2000);
  insertmaxpiv boolean := false;
  picur RefCur;
BEGIN
--  putpipe('starting SP');
-- if maxpivid is not different from the old one, dont do anything
  select nvl(max(pivid), 0) into v_dbmaxpivid from pdm_productitemversion;
--  putpipe('current max pivid is '||v_dbmaxpivid);

  begin
    sqlstmt := 'SELECT MAXDBId FROM ResumeProcessStatus@'||dbLinkName||
      ' WHERE DomainId = :ilDomainId AND Purpose = :FINISHED_MAXPIV';
    execute immediate sqlstmt into v_maxpivid using ilDomainId, FINISHED_MAXPIV;
--    putpipe('old max pivid is '||v_maxpivid);
  exception when NO_DATA_FOUND then
    -- no row exists
    insertmaxpiv := true;
--    putpipe('old max pivid is '||v_maxpivid);
    v_maxpivid := 0;
--    putpipe('no entry in resumeprocessstatus');
    sqlstmt := 'select nvl(max(pivid), 0) from pivtowcstatus@'||dbLinkName||
      ' WHERE DomainId = :ilDomainId';
    execute immediate sqlstmt into v_maxpivid using ilDomainId;
  end;

--  putpipe('old max pivid is '||v_maxpivid);
  if not v_dbmaxpivid > v_maxpivid then
      -- nothing changed from last processing so ignore
    goto InsertMaxPIVId;
  else
    sqlstmt := 'select distinct pi.piid from pdm_productitemversion piv, pdm_branch br, '||
               'pdm_productitem pi, pitowcstatus@'||dbLinkName||' st where '||
               'piv.pivid > '||v_maxpivid||' and piv.brid = br.brid and br.piid = pi.piid and '||
               'pi.piid = st.piid and st.domainid = '||ilDomainId||' and st.status = '||MIG_SUCCESS;
--    putpipe(sqlstmt);
    open picur for sqlstmt;
    loop
      fetch picur into v_piid;
      exit when picur%NOTFOUND;

      pi_sqlstmt := 'update pitowcstatus@'||dbLinkName||' set status = '||MIG_UNFINISHED||
                    ' where piid = :piid and domainid = :domainid';
      part_sqlstmt := 'update pitowcstatus@'||dbLinkName||' set partstatus = '||MIG_UNFINISHED||
                      ' where piid = :piid and domainid = :domainid and (partstatus = '||MIG_SUCCESS||' or partstatus = '||MIG_PART_CREATED||')';
--      putpipe(sqlstmt);
      execute immediate pi_sqlstmt using v_piid, ilDomainId;
      execute immediate part_sqlstmt using v_piid, ilDomainId;

    end loop;
    close picur;

  end if;

<<InsertMaxPIVId>>
  if insertmaxpiv then
    sqlstmt := 'insert into resumeprocessstatus@'||dbLinkName||
      ' (MAXDBId, DomainId, Purpose) values (:1,:2,:3)';

  else
    sqlstmt := 'update resumeprocessstatus@'||dbLinkName||
       ' set MAXDBId = :1 where DomainId = :2 and Purpose = :3';
  end if;

--  putpipe(sqlstmt);
  execute immediate sqlstmt using v_dbmaxpivid, ilDomainId, FINISHED_MAXPIV;

END mig_markUnFinishedPIs;

function domainExists( domIds IN PDMArrayId,
                       domId IN number) return boolean IS

  domFound boolean := false;
BEGIN
--  putpipe( 'comparing with '||domId);
   if domIds.count>0 then
    for i in domIds.FIRST..domIds.LAST loop
  --    putpipe('domainid is '||domIds(i));
      if domIds(i) = domId then
        domFound := true;
        exit;
      end if;
    end loop;
   end if;
  return domFound;
END domainExists;

procedure countILObjects( domainId IN number,
                          dbLinkName IN varchar2,
                          sessionId IN varchar2,
                          typeDefInCond IN varchar2,
                          epmDocTypeDefInCond IN varchar2,
                          wtDocTypeDefInCond IN varchar2,
                          typeDefSecondInCond IN varchar2,
                          anyMembType IN number
                          ) IS

  piStTable varchar2(40);
  pivStTable varchar2(40);
  depStTable varchar2(40);
  lovStTable varchar2(40);
  pivTDefsTable varchar2(40);
  sqlstmt varchar2(20000);
  epmDocPICount number := 0; epmDocPIVCount number := 0; wtDocPICount number := 0;
  wtDocPIVCount number := 0; membDepCount number := 0; refDepCount number := 0;
  fileCount number := 0; v_dbmaxpivid number := 0; replPIVCount number := 0;
  pivFileCount number := 0; lovFileCount number := 0; secFileCount number := 0;
  blCount number := 0; badBLCount number := 0; emptyBLCount number := 0; totalBLCount number := 0;

  v_time varchar2(100);
  l_cnst_impossibleType CONSTANT varchar2(20):= '(''$$MIG$$$'')';
  l_t    varchar2(4000);
  l_st    varchar2(4000);
  -- MIG_STAT vars:
  v_Package_NM varchar2(40):='MIG_PREPROCESS_PKG';
  v_Procedure_NM varchar2(40):='countILObjects';
  l_Log_Id number;
BEGIN
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, 'BEGIN!',l_Log_Id);

  piStTable := 'ilCountPIStatus_'||sessionId;
  pivStTable := 'ilCountPIVStatus_'||sessionId;
  depStTable := 'ilCountDepStatus_'||sessionId;
  lovStTable := 'ilCountLOVStatus_'||sessionId;
  pivTDefsTable := 'piv_tdefs_'||sessionId;
  sqlstmt := 'create table '||piStTable||' as '||
             'select piid, domainid, documenttype, status, attrstatus '||
             ' from pitowcstatus@'||dbLinkName||' where domainid = '||domainId||
             ' and (status in ('||MIG_CANNOTDO||', '||MIG_REPLSUCCESS||') or documenttype = '||EPMSECONDARY||') ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create index ind_'||piStTable||' on '||piStTable||' (piid ASC) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create table '||pivStTable||' as '||
             'select pivid, domainid, documenttype, status, attrstatus, fileid '||
             ' from pivtowcstatus@'||dbLinkName||' where domainid = '||domainId||
             ' and (status in ('||MIG_CANNOTDO||', '||MIG_REPLSUCCESS||') or documenttype = '||EPMSECONDARY||') ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create index ind_'||pivStTable||' on '||pivStTable||' (pivid ASC) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;

  sqlstmt := 'create table '||depStTable||' as '||
             'select dgid, domainid, linktype, status, attrstatus '||
             ' from deptowcstatus@'||dbLinkName||' where domainid = '||domainId|| 
             ' and status ='||MIG_CANNOTDO;
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create index ind_'||depStTable||' on '||depStTable||' (dgid ASC) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;

  sqlstmt := 'create table '||lovStTable||' as '||
             'select lovid, domainid, status '||
             ' from lovtowcstatus@'||dbLinkName||' where domainid = '||domainId||
             ' and status in ('||MIG_CANNOTDO||', '||MIG_REPLSUCCESS||') ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create index ind_'||lovStTable||' on '||lovStTable||' (lovid ASC) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;

  sqlstmt := 'create table '||pivTDefsTable||' as '||
       ' select piv.pivid, piv.primtxnid, pi.piid, pit.pitname from '||
       ' pdm_productitemversion piv, pdm_branch br, pdm_productitem pi, '||
       ' pdm_pitypedef pit '||
       ' where piv.brid = br.brid and br.brpath = ''main'' and br.piid = pi.piid '||
       ' and pi.pitypeid = pit.pitypeid';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'create index idx1_'||pivTDefsTable||' on '||pivTDefsTable||'(pivid)';
  execute immediate sqlstmt;
  sqlstmt := 'create index idx2_'||pivTDefsTable||' on '||pivTDefsTable||'(pitname)';
  execute immediate sqlstmt;
  sqlstmt := 'create index idx3_'||pivTDefsTable||' on '||pivTDefsTable||'(primtxnid)';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
if epmDocTypeDefInCond IS NOT NULL and epmDocTypeDefInCond !='()' then
 sqlstmt := 'select count(piid) from'|| 
             ' ('||
             ' select p.piid'|| 
             ' from pdm_productitem p'||
             '   , pdm_pitypedef pt'|| 
             ' where p.pitypeid = pt.pitypeid'|| 
             ' and pt.pitname in  ' || epmDocTypeDefInCond ||
             ' and p.piid  in '||
             '                   (select piid '||
             '                    from '||pivTDefsTable||'  )'||
             ' minus'||
             ' select piid from '||piStTable||
             ' ) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into epmDocPICount;
else
  epmDocPICount:=0;
end if;  

if wtDocTypeDefInCond IS NOT NULL and wtDocTypeDefInCond!='()' then
 sqlstmt := 'select count(piid) from'|| 
             ' ('||
             ' select p.piid'|| 
             ' from pdm_productitem p'||
             '   , pdm_pitypedef pt'|| 
             ' where p.pitypeid = pt.pitypeid'|| 
             ' and pt.pitname in  ' || wtDocTypeDefInCond ||
             ' and p.piid  in '||
             '                   (select piid '||
             '                    from '||pivTDefsTable||'  )'||
             ' minus'||
             ' select piid from '||piStTable||
             ' ) ';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into wtDocPICount;
else
  wtDocPICount:=0;
end if;  


if epmDocTypeDefInCond IS NOT NULL and epmDocTypeDefInCond !='()' then
  sqlstmt := 'select count(pivid) from '||pivTDefsTable||
             ' p where pitname in '||epmDocTypeDefInCond||' and '||
             'not exists (select pivid from '||pivStTable||
             ' st where st.pivid = p.pivid'||
             ' )';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into epmDocPIVCount;
else
  epmDocPIVCount:=0;
end if;  

if wtDocTypeDefInCond IS NOT NULL and wtDocTypeDefInCond!='()' then
  sqlstmt := 'select count(pivid) from '||pivTDefsTable||
             ' p where pitname in '||wtDocTypeDefInCond||' and '||
             'not exists (select pivid from '||pivStTable||
             ' st where st.pivid = p.pivid'||
             ' )';
--  putpipe('wtDocquery = '||sqlstmt);
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into wtDocPIVCount;
else
  wtDocPIVCount:=0;
end if;  

  sqlstmt := 'select count(pivid) from '||pivStTable||
             ' st where st.status =  '||MIG_REPLSUCCESS;
--  putpipe('replpivquery = '||sqlstmt);
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into replPIVCount;



if epmDocTypeDefInCond IS NOT NULL and epmDocTypeDefInCond !='()' then
  sqlstmt :=
        ' select sum(membDep) membDepCount'|| 
        '      , sum(refDep) refDepCount '||
        ' from ('|| 
        ' SELECT '||
        ' decode(dep.dgdepclass, ''USER_DEFINED'',0,decode(to_number(bitand(dep.dgdeptype,   '||anyMembType||')),0,0,decode(sign(dep.dgdeptype),1,1,0))) membDep'||
        ' , '||
        ' decode(dep.dgdepclass, ''USER_DEFINED'',1,decode(to_number(bitand(dep.dgdeptype,   '||anyMembType||')),0,1,decode(sign(dep.dgdeptype),1,0,1))) refDep'||
        ' FROM pdm_dependencygraph dep'||
        ' WHERE ( '||
        '       (  dep.dgdepclass in (''PRO_E'') and    dep.dgdeptype > 0 and to_number(bitand(dep.dgdeptype, '||anyMembType||')) > 0) '||
        '   OR  ( (dep.dgdepclass in (''PRO_E'') and  ( dep.dgdeptype <= 0 OR to_number(bitand(dep.dgdeptype, '||anyMembType||')) = 0)) OR dep.dgdepclass = ''USER_DEFINED'')'||
        '       )'||
        '     and dep.pivid in (select pivid '||
        '                       from '||pivTDefsTable||
        '                       where pitname in ' || epmDocTypeDefInCond ||
        '                       minus'||
        '                       select pivst.pivid '||
        '                       from '||pivStTable||' pivst '||
        '                       ) '||
        '     and dep.piid in (select piid from  '||pivTDefsTable||
        '                       where pitname in '|| epmDocTypeDefInCond ||
        '                       minus'||
        '                       select piid from '||piStTable||' pist where pist.documenttype = '||EPMSECONDARY||
        '                     )'|| 
        '  and  dgid not in (select dgid from '||depStTable||' st)'||
        ' ) ';            

--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into membDepCount,refDepCount;
else
  membDepCount:=0;
  refDepCount:=0;
end if;  

if typeDefInCond IS NOT NULL and typeDefInCond !='()' then
BEGIN
  sqlstmt := 'with get_PI as   (select pist.piid'|| 
             '                       from'|| 
             '                           '||piStTable||' pist'|| 
             '                       where'|| 
             '                           pist.status   IN ('||MIG_CANNOTDO||', '||MIG_REPLSUCCESS||') )'|| 
             '     ,get_PIV as (select st.pivid'|| 
             '                         from'|| 
             '                             '||pivStTable||' st )'||            
             ' select count (distinct fid)'|| 
             ' from  pdm_productitemverfile  f'||
             ' where'|| 
             '      f.pivid in ('||
             '                  select   p.pivid'|| 
             '                  from '||pivTDefsTable||'  p '||
             '                  where'|| 
             '                  p.pitname in '||typeDefInCond||
             '                  and p.PIID NOT IN ('||
             '                                      select piid '||
             '                                      from '||
             '                                          get_PI'||
             '                                      where get_PI.piid=p.PIID'||    
             '                                    )'|| 
             '                  minus'||                   
             '                  select pivid'||
             '                  from'|| 
             '                      Get_PIV'||
             '                )';                         
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into pivFileCount;
EXCEPTION
   when OTHERS then-- catch 8i version limitation and re-write the sql
sqlstmt := '      select count (distinct fid)'||
           '   from  pdm_productitemverfile  f'||
           '   where'||
           '        f.pivid in ('||
           '                    select   p.pivid'||
           '                    from '||pivTDefsTable||'  p'|| 
           '                    where'|| 
           '                    p.pitname in '||typeDefInCond||
           '                    and p.PIID NOT IN ('||
           '                                        select piid '||
           '                                        from '||
           '                                            (select pist.piid'||
           '                                            from'||
           '                                              '||piStTable||' pist'||
           '                                            where'||
           '                                              pist.status   IN ('||MIG_CANNOTDO||', '||MIG_REPLSUCCESS||') ) get_PI'||
           '                                      )'||
           '                    minus'||
           '                    select pivid'||
           '                    from'||
           '                        (select st.pivid '||
           '                           from'||
           '                               '||pivStTable||' st) Get_PIV'||
           '                  )';                         
  execute immediate sqlstmt into pivFileCount;
END;  
else
  pivFileCount:=0;
end if;  


 sqlstmt := 'select count (distinct fid) from pdm_lovfile lovf where lovid in '||
             '(select lov.lovid ' ||
          'from pdm_lov lov, pdm_lovcontents lovc ' ||
          'where lovc.lovid = lov.lovid '||
           ' and not exists (select lovc3.pivid from pdm_lovcontents lovc3 where lovc3.lovid = lov.lovid and '||
           '      not exists (select 1 from '|| pivTDefsTable||' p1 where p1.pivid = lovc3.pivid))'||
            'and     exists (select 1 ' ||
                            'from pdm_geninsrel gir1 ' ||
                            'where gir1.genpivid = lovc.pivid ' ||
                              'and exists (select 1 from pdm_lovcontents lovc1 '||
                                          'where lovc1.pivid = gir1.instpivid ' ||
                                            'and lovc1.lovid = lov.lovid )) ' ||
            'and not exists (select 1 ' ||
                            'from pdm_geninsrel gir2 ' ||
                            'where gir2.instpivid = lovc.pivid ' ||
                              'and exists (select 1 from pdm_lovcontents lovc2 ' ||
                                          'where lovc2.lovid = lov.lovid ' ||
                                            'and lovc2.pivid = gir2.genpivid)))' ||
     ' and not exists (select 1 from '||lovStTable||' lst where lst.lovid = '||
     ' lovf.lovid )';

--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into lovFileCount;

  sqlstmt := 'select count (distinct fileid) from '||pivStTable||
             ' p where p.documenttype = '||EPMSECONDARY;
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into secFileCount;
--  putpipe(pivFileCount||', '||lovFileCount||', '||secFileCount);

  fileCount := pivFileCount + lovFileCount + secFileCount;

  sqlstmt := 'select count (distinct blid) from pdm_baseline';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into totalBLCount;

  sqlstmt := 'select count(blid) from pdm_baseline '||
             'where not exists '||
             '(select 1 from pdm_baselineset bset where bset.blid=blid)';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into emptyBLCount;
  if typeDefInCond IS NOT NULL and typeDefInCond !='()' then
    l_t:=typeDefInCond;
  else
    l_t:=l_cnst_impossibleType;
  end if;
  if typeDefSecondInCond IS NOT NULL and typeDefSecondInCond !='()' then
    l_st:=typeDefSecondInCond;
  else
    l_st:=l_cnst_impossibleType;
  end if;  
  sqlstmt :=
      '  select count (*) from'||
      '  ('||
      '    select  bl.blid'||
      '    from pdm_baseline    bl'||
      '        ,pdm_baselineset bset'||
      '    where'||
      '        bset.blid       = bl.blid'||
      '    and bset.blsmembpiv in (select  distinct p.pivid'||
      '                            from '||piStTable||' st'||
      '                                ,'||pivTDefsTable||' p'||
      '                            where'||
      '                                  st.piid = p.piid'|| 
      '                              and (st.status = '||MIG_CANNOTDO||' )'||
      '                              and ((p.pitname in '||l_t||')'||
      '                              or   (p.pitname in '||l_st||'))'||
      '                            )'||
      '    UNION'||
      '    select  bl.blid'||
      '    from pdm_baseline    bl'||
      '        ,pdm_baselineset bset'||
      '    where'||
      '          bset.blid       = bl.blid'||
      '    and   bset.blsmembpiv in (select  distinct p.pivid'||
      '                              from '||pivStTable||' st'||
      '                                  ,'||pivTDefsTable||' p'||
      '                              where'||
      '                                  st.pivid = p.pivid '||
      '                              and (st.status = '||MIG_CANNOTDO||' )'||
      '                              and ((p.pitname in '||l_t||')'||
      '                              or   (p.pitname in '||l_st||'))'||
      '                            )'||
      '            UNION'||
      '            select  bl.blid'||
      '            from pdm_baseline    bl'||
      '                ,pdm_baselineset bset'||
      '            where'||
      '                  bset.blid       = bl.blid'||
      '            and   bset.blsmembpiv in (select  distinct p.pivid'||
      '                                      from '||pivTDefsTable||' p'||
      '                                      where'||
      '                                       p.pitname not in '||l_t||
      '                                      and  p.pitname not in '||l_st||
      '                                    )'||
      '  )';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt into badBLCount;

  blCount := totalBLCount - badBLCount - emptyBLCount;

  sqlstmt := 'delete from ILCount@'||dbLinkName||' where domainid = :1';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt using domainId;

  sqlstmt := ' insert into ILCount@'||dbLinkName||' values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt using domainId, epmDocPICount, epmDocPIVCount, wtDocPICount, wtDocPIVCount, replPIVCount, membDepCount, refDepCount, fileCount, blCount;

  sqlstmt := 'drop table '||piStTable;
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt;
  sqlstmt := 'drop table '||pivStTable;
  execute immediate sqlstmt;
  sqlstmt := 'drop table '||depStTable;
  execute immediate sqlstmt;
  sqlstmt := 'drop table '||lovStTable;
  execute immediate sqlstmt;
  sqlstmt := 'drop table '||pivTDefsTable;
  execute immediate sqlstmt;

  sqlstmt := 'delete from resumeprocessstatus@'||dbLinkName||
             ' where domainid = :1 and purpose = :2';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt using domainId, ILCOUNT_MAXPIV;

  select nvl(max(pivid), 0) into v_dbmaxpivid from pdm_productitemversion;
  sqlstmt := 'insert into resumeprocessstatus@'||dbLinkName||
             ' (MAXDBId, DomainId, Purpose) values (:1,:2,:3)';
--MIG_STAT.prc_Start( USER, v_Package_NM, v_Procedure_NM, substr(sqlstmt,1,4000),l_Log_Id);
  execute immediate sqlstmt using v_dbmaxpivid, domainId, ILCOUNT_MAXPIV;
--MIG_STAT.prc_End(l_Log_Id);
  EXCEPTION WHEN OTHERS THEN
--dbg.put_line('countILObjects INPUT typeDefInCond='||typeDefInCond);
--dbg.put_line('countILObjects INPUT epmDocTypeDefInCond='||epmDocTypeDefInCond);
--dbg.put_line('countILObjects INPUT wtDocTypeDefInCond='||wtDocTypeDefInCond);
--dbg.put_line('sqlstmt='||sqlstmt);
    begin
      sqlstmt := 'drop table '||piStTable;
      execute immediate sqlstmt;
    exception when others then
      null;
    end;
    begin
      sqlstmt := 'drop table '||pivStTable;
      execute immediate sqlstmt;
    exception when others then
      null;
    end;
    begin
      sqlstmt := 'drop table '||depStTable;
      execute immediate sqlstmt;
    exception when others then
      null;
    end;
    begin
      sqlstmt := 'drop table '||lovStTable;
      execute immediate sqlstmt;
    exception when others then
      null;
    end;
    begin
      sqlstmt := 'drop table '||pivTDefsTable;
      execute immediate sqlstmt;
    exception when others then
      null;
    end;

    raise;

END countILObjects;

procedure checkEquality ( cleanUp IN number,
                          IlinkDomainId IN varchar2,
                          dbLinkName IN varchar2,
                          anyMembType IN number ,
                          notIncludedTypeIds in varchar2) IS

INT_TABLE CONSTANT varchar2(20) := 'IntegerValue';
FL_TABLE CONSTANT varchar2(20) := 'FloatValue';
STR_TABLE CONSTANT varchar2(20) := 'StringValue';
BOOL_TABLE CONSTANT varchar2(20) := 'BooleanValue';
DATE_TABLE CONSTANT varchar2(20) := 'TimestampValue';
v_sqlstmt varchar2(4000);
ilDomainId NUMBER := to_number(IlinkDomainId);
newBadObjectsFound boolean := true;

READY CONSTANT number := 1;
-- from migration/resume/Resume.java
--  public static final int READY_TO_MERGE = 5;
READY_TO_MERGE CONSTANT number := 5;
timing varchar2(30);

begin
--rollback;
 --dbms_debug_jdwp.connect_tcp('yhui03d', '4001');
---------------------------------
-- initial checks
---------------------------------
  -- clean all tables if recalculation needed
   -- select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') into timing from dual;
  --insert into TEMP_TIME_FOR_CHECKEQUALITY values (' checkequality start', timing);

  if cleanUp = 1 then
    cleanUpEqualityTables (ilDomainId, dbLinkName);
 
  -- fill in attribute map with udas
    v_sqlstmt := 'insert into AttrEqualityMap@'||dbLinkName||' (claid, defvalue, ilTableName, ilColumnName, '||
                 'claname, objectType, wcTableName, wcid, attrType) '||
      '(select cla.claid, cla.cladefvalue, dbad.dbaattrtable, dbad.dbaattrcol, claname, '||
      ' decode (cl.clname, ''PDMc_PI'','||PI||', ''PDMc_PIV'', '||PIV||',  '||
      '''PDMc_Dependency'', '||DEP||', ''PDMc_LCOV'', '||PIV||', '||EMPTY||' ), '||
      'decode(map.CLASSNAMEKEYB5, ''wt.iba.definition.IntegerDefinition'', '''||INT_TABLE||''', '||
      '      ''wt.iba.definition.FloatDefinition'', '''||FL_TABLE||''', '||
      '      ''wt.iba.definition.StringDefinition'', '''||STR_TABLE||''', '||
      '      ''wt.iba.definition.BooleanDefinition'', '''||BOOL_TABLE||''', '||
      '      ''wt.iba.definition.TimestampDefinition'', '''||DATE_TABLE||''', ''NO_TABLE''),  '||
      'map.ida3b5, '||UDA||' from '||
      'pdm_classattr cla, migrationdatamapping@'||dbLinkName||' map, pdm_classdef cl, '||
      'pdm_dbattrdef dbad, migrationsite@'||dbLinkName||' ms '||
      ' where cla.claname = map.fromvalue '||
      'and map.id like ''EPM%IBA'' '||
      'and map.ida3a5 = ms.ida2a2 '||
      'and ms.domainid = '||ilDomainId||
      ' and cla.CLAAPPLCLID = cl.clid ' ||
      'and cla.claid = dbad.claid)';
--    putpipe(v_sqlstmt);
    execute immediate v_sqlstmt;

  -- fill in the attribute map with piv sys attributes
    v_sqlstmt := 'insert into AttrEqualityMap@'||dbLinkName||' (claname, ilTablename, '||
                 'ilColumnName, wcTableName, wcColumnName, objectType, attrType) '||
                 'values (''FamilyTableStatus'', ''PDM_PRODUCTITEMVERSION'', ''PIVCLASS'', '||
                 ' ''EPMDOCUMENT'', ''FAMILYTABLESTATUS'', '||PIV||', '||SYS||')';
--    putpipe (v_sqlstmt);
    execute immediate v_sqlstmt;

  -- todo fill in the attribute map with dep sys attributes;
--    attrNames(1):= 'ProEId'; ilCols(1) := 'U_PROEID'; wcCols(1) :=
--    attrNames(2):=
--   v_sqlstmt := 'insert into into AttrEqualityMap@'||dbLinkName||' (claname, ilTablename, '||
--                 'ilColumnName, wcTableName, wcColumnName, objectType, attrType) '||
--                 'values ('''||attrNames(j)||''', ''PDM_DEPENDENCYGRAPH_UDA1'', '''||ilCols(j)||''', '||
--                 ' ''EPMMEMBERLINK'', '''||wcCols(j)||''', '||DEP||', '||SYS||')';
--    for j in 1..3 loop
--      execute immediate v_sqlstmt;
--    end loop;
  else -- if no cleanup then we need to mark related objects
  
    markRelatedObjects(ilDomainId, dbLinkName);
      
  end if;

-----------------------------------
-- Now do all the equality checks
-----------------------------------

--  putpipe('checking attr equality for pi');
  -- check attr equality for PI
 
  checkAttrEquality(PI, ilDomainId, dbLinkName);


--  putpipe('checking sys attr equality for piv');
  -- check sys attrs for PIV
 
  checkSysAttrEquality(PIV, ilDomainId, dbLinkName);

--  putpipe('checking attr equality for piv');
  -- check attr equality for PIV

  checkAttrEquality(PIV, ilDomainId, dbLinkName);

--  putpipe('deptowc duplicate creation');
  -- create deptowcduplicate table
   createDepDuplicateTable(ilDomainId, dbLinkName, anyMembType,notIncludedTypeIds);
-- check for sys attrs for DEP
--  checkSysAttrEquality(DEP, ilDomainId, dbLinkName);

--  putpipe('checking attr equality for dep');
  -- check attr equality for DEP
  checkAttrEquality(DEP, ilDomainId, dbLinkName);
  --  The next two checks depend on other objects' equality, so
  -- those need to be done recursively
  -- check dependents for the DEP
  newBadObjectsFound := true;

  while newBadObjectsFound loop

--    putpipe('inside depPI, famtab loop');
    -- function will return newBadObjectsFound as true if some pivs marked as unequal
    -- in this case, depPIEquality should be rechecked. else newBadObjectsFound will be false
    checkDepPIEquality(ilDomainId, dbLinkName, newBadObjectsFound);
  -- family table equality
    -- so if newBadObjectsFound is true this function will not touch it since we need to rerun the whole loop anyway
    -- if newBadObjectsFound is false, this function will make it true if any pivs are marked as bad
    checkFamTabEquality(ilDomainId, dbLinkName, newBadObjectsFound);
  end loop;

-----------------------------------------
-- Now mark all objects and system status
-----------------------------------------
  -- all equality checks done.
  -- by default all valid objects were unequal/equal and need equality check
  -- unequal objects already marked unequal and not need equality check
  -- so mark all the objects that are valid and need equality check
  -- to be equal and not need equality check

  v_sqlstmt := 'update pitowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setEqual@'||dbLinkName||'(status) where ignore = 0 and domainid = '||ilDomainId||
               ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1'||
               'and migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||') = 1 '||
               'and migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0 ';
--  putpipe(v_sqlstmt);
  execute immediate v_sqlstmt;
  -- update pivs that are valid, have check_for_equality flag and have pis marked equal
  v_sqlstmt := 'update pivtowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setEqual@'||dbLinkName||'(status) where domainid = '||ilDomainId||
               ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1'||
               ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||') = 1 and '||
               ' migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
               ' piid in (select piid from pitowcduplicate@'||dbLinkName||
               ' pidup where migrationPK.checkFlag@'||dbLinkName||'(pidup.status,'||EQUAL_FLAG||') = 1 and '||
               ' migrationPK.checkFlag@'||dbLinkName||'(pidup.status,'||CHECK_FOR_EQUALITY_FLAG||') = 0 '||
               ' and migrationPK.checkFlag@'||dbLinkName||'(pidup.status,'||MARKED_FOR_MERGE_FLAG||') = 0)';
--  putpipe(v_sqlstmt);
  execute immediate v_sqlstmt;
  -- now set the processstatus for ready to merge
  v_sqlstmt := 'update onceprocessstatus@'||dbLinkName||
               ' set status = '||READY||' where ' ||
               ' onceoperationtype = '||READY_TO_MERGE||' and domainid = '|| ilDomainId;
--  putpipe(v_sqlstmt);
  execute immediate v_sqlstmt;

 exception when others then
  --putpipe('errcode is '||SQLCODE);
--    putpipe('errmesg is '||SQLERRM);
  dbms_output.put_line('error '||sqlerrm(sqlcode));
  raise;

end checkEquality;

procedure cleanUpEqualityTables (ilDomainId IN number,
                                 dbLinkName IN varchar2) is

--  from migration/resume/Resume.java
--  public static final int CLEANUP_EQUALITY_TABLE = 7;

  CLEANUP_EQUALITY_TABLE CONSTANT number := 7;
  NOT_NEEDED CONSTANT number := 0;

  v_sqlstmt varchar2(1000);

begin

--  putpipe('done cleanup');

  v_sqlstmt := 'delete from attrequalitymap@'||dbLinkName;
  execute immediate v_sqlstmt;

  v_sqlstmt := 'delete from unequalObjects@'||dbLinkName;
  execute immediate v_sqlstmt;

  v_sqlstmt := 'delete from deptowcduplicate@'||dbLinkName||' where domainid = '||ilDomainId ||
               ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0';
  execute immediate v_sqlstmt;

  v_sqlstmt := 'update onceprocessstatus@'||dbLinkName||
               ' set status = '||NOT_NEEDED||' where ' ||
               ' onceoperationtype = '||CLEANUP_EQUALITY_TABLE||' and domainid = '||
               ilDomainId;
  execute immediate v_sqlstmt;

--  exception when others then
--    putpipe('errcode is '||SQLCODE);
--    putpipe('errmesg is '||SQLERRM);

end cleanUpEqualityTables;

procedure createDepDuplicateTable( ilDomainId in number,
                                   dbLinkName in varchar2,
                                   anyMembType in number,
                                   notIncludedTypeIds in varchar2) IS

  pivCur RefCur; mlinkCur RefCur; rlinkCur RefCur; membDepCur RefCur; refDepCur RefCur ;
  v_pivid number; v_docid number; v_count number; v_piid number; j number;
  v_dgid number; v_deptype number; v_linkid number; v_linktype number;
  v_depPIName varchar2(160); v_linkPIName varchar2(200);
  v_sqlstmt varchar2(2000); v_sqlstmt2 varchar2(2000); v_insstmt varchar2(2000);
  v_mlinkstmt varchar2(2000); v_rlinkstmt varchar2(2000); v_delstmt varchar2(2000);
  badpivs PDMArrayId; badpis PDMArrayId; hashedpis PDMArrayId;
  delFromDep boolean := true;
  v_membDepCur varchar2(2000);
  v_refDepCur varchar2(2000);
 ignoreStr varchar2(120):=' dup.pivid is not null and dup.docid is not null and pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid ';

begin
   
     v_membDepCur:='select dgid, dgdeptype, pi.piid, pi.piname from pdm_productitem pi, pdm_dependencygraph dep '||
      'where dep.piid = pi.piid '||
      ' and pi.pitypeid not in ('||notIncludedTypeIds||') '||
        'and dep.dgdepclass in (''PRO_E'', ''USER_BOM'') '||
        'and dep.dgdeptype > 0 and to_number(bitand(dep.dgdeptype, :membType)) > 0 '||
        'and dep.pivid =:pivid '||
        'order by pi.piname, dep.dgdeptype';
        
     v_refDepCur :='select dgid, dgdeptype, pi.piid, pi.piname from pdm_productitem pi, pdm_dependencygraph dep '||
     ' where dep.piid = pi.piid '||
     'and pi.pitypeid not in ('||notIncludedTypeIds||') '||
      ' and not  '||
      '   (dep.dgdepclass in (''PRO_E'', ''USER_BOM'') and dep.dgdeptype > 0 and to_number(bitand(dep.dgdeptype, :membType)) > 0) '||
      ' and dep.pivid = :pivid  '||
      ' order by pi.piname, dep.dgdeptype';
  -- find all the pivs that need checking
--  putpipe('started creating depduplicate');

   v_sqlstmt := 'select dup.pivid, dup.docid from pivtowcduplicate@'||dbLinkName||' dup, pitowcduplicate@'||dbLinkName||' pi where '||
                 ignoreStr ||' and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||CHECK_FOR_EQUALITY_FLAG||')  = 1 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||VALID_FLAG||')  = 1';

  v_sqlstmt2 := 'select count(*) From deptowcduplicate@'||dbLinkName||' where '||
                 'pivid = :pivid and domainid = '||ilDomainId;

  v_mlinkstmt := 'select l.ida2a2, l.deptype, docm.cadname '||
                 'from epmmemberlink@'||dbLinkName||' l, epmdocumentmaster@'||dbLinkName||' docm where '||
                 'l.ida3a5 = :docid and '||
                 'l.ida3b5 = docm.ida2a2 '||
                 'order by docm.cadname, l.deptype';
--  putpipe(v_mlinkstmt);

  v_rlinkstmt := 'select l.ida2a2, l.deptype, docm.cadname '||
                 'from epmreferencelink@'||dbLinkName||' l, epmdocumentmaster@'||dbLinkName||' docm where '||
                 'l.ida3a5 = :docid and '||
                 'l.ida3b5 = docm.ida2a2 '||
                 'order by docm.cadname, l.deptype';
--  putpipe(v_rlinkstmt);

  v_insstmt := 'insert into deptowcduplicate@'||dbLinkName||
               ' (pivid, domainid, dgid, deptype, piid, linkid, LinkType, status )' ||
               ' values (:1, :2, :3, :4, :5, :6, :7, :8) ';
  badpivs.delete;

  open pivCur for v_sqlstmt;
--  putpipe(v_sqlstmt);
--  putpipe(v_sqlstmt2);
--  putpipe('anymembtype is '||anyMembType);
  while true loop
    fetch pivCur into v_pivid, v_docid;
--    putpipe(v_pivid||', '||v_docid);
    exit when pivCur%NOTFOUND;

    execute immediate v_sqlstmt2 into v_count using v_pivid;

    if v_count = 0 then -- this piv has not already been visited
      -- first do member deps
      open membDepCur for v_membDepCur using anyMembType, v_pivid;
      open mlinkCur for v_mlinkstmt using v_docid;
      while true loop
        fetch membDepCur into v_dgid, v_deptype, v_piid, v_depPIName;
        fetch mlinkCur into v_linkid, v_linktype, v_linkPIName;
--        putpipe(v_dgid||', '||v_deptype||', '||v_piid||', '||v_depPIName);
--        putpipe(v_linkid||', '||v_linktype||', '||v_linkPIName);
        if mlinkCur%NOTFOUND and membDepCur%NOTFOUND then
          exit;
        end if;
        if mlinkCur%NOTFOUND or membDepCur%NOTFOUND then
          -- some dependency not found in the other system so piv unequal
          badpivs(v_pivid) := NO_CORRESPONDING_DEPS;
--          putpipe('one of them is null');
          exit;
        end if;
        if v_depPIName <> v_linkPIName or v_depType <> v_linkType then
          badpivs(v_pivid) := NO_CORRESPONDING_DEPS;
--          putpipe('one of them is not equal');
          exit;
        else
          -- enter into equal dep with a status of VALID_AND_NEED_EQUALITY_CHECK
          execute immediate v_insstmt using v_pivid, ilDomainId, v_dgid, v_depType, v_piid, v_linkid, MEMBER, VALID_AND_NEED_EQUALITY_CHECK;
--          putpipe('membdep inserted into depduplicate');
        end if;
      end loop;
      close mlinkCur;
      close membDepCur;

      -- repeat for ref dep if this was not a bad piv
      if not badpivs.exists(v_pivid) then
        open refDepCur for v_refDepCur using  anyMembType, v_pivid;
        open rlinkCur for v_rlinkstmt using v_docid;
        while true loop
          fetch refDepCur into v_dgid, v_deptype, v_piid, v_depPIName;
          fetch rlinkCur into v_linkid, v_linktype, v_linkPIName;
--          putpipe('refdep '||v_dgid||', '||v_deptype||', '||v_piid||', '||v_depPIName);
--          putpipe(v_linkid||', '||v_linktype||', '||v_linkPIName);
          if rlinkCur%NOTFOUND and refDepCur%NOTFOUND then
            exit;
          end if;
          if rlinkCur%NOTFOUND or refDepCur%NOTFOUND then
            -- some dependency not found in the other system so piv unequal
            badpivs(v_pivid) := NO_CORRESPONDING_DEPS;
--            putpipe('one of them is null');
            exit;
          end if;
          if v_depPIName <> v_linkPIName or v_depType <> v_linkType then
            badpivs(v_pivid) := NO_CORRESPONDING_DEPS;
--            putpipe('one of them is not equal');
            exit;
          else
            -- enter into equal dep with a status of VALID_AND_NEED_EQUALITY_CHECK
            execute immediate v_insstmt using v_pivid, ilDomainId, v_dgid, v_depType, v_piid, v_linkid, REFERENCE, VALID_AND_NEED_EQUALITY_CHECK;
--            putpipe('refdep inserted into depduplicate');
          end if;
        end loop;
        close rlinkCur;
        close refDepCur;
      end if;
    end if; -- the piv has not been visited before, so process the piv
  end loop; -- loop for all pivs
  close pivCur;

  -- for all the bad pivs, delete those from deptowcduplicate table and mark the related pis unequal

  markUnequalPIVs( badpivs, delFromDep, ilDomainId, dbLinkName);

end createDepDuplicateTable;


procedure markUnequalPIVs ( badpivs IN PDMArrayId,
                            delFromDep IN boolean,
                            ilDomainId IN varchar2,
                            dbLinkName IN varchar2 ) IS

  badpis PDMArrayId; hashedpis PDMArrayId; errIds PDMArrayId;
  v_delstmt varchar2(1000);
  v_pivstmt varchar2(1000);
  j number; v_piid number;
begin

  v_pivstmt := 'update pivtowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setUnEqual@'||dbLinkName||'(status), errid = :errid where pivid = :pivid';

  badpis.delete;
  hashedpis.delete;
  errIds.delete;
  v_delstmt := 'delete from deptowcduplicate@'||dbLinkName||' where pivid = :pivid';
  j := badpivs.FIRST;
  while j is not null loop
    -- ignore delFromDep. may be we will remove if from code later
    --if delFromDep then
    --  execute immediate v_delstmt using j;
    --end if;
    select br.piid into v_piid from pdm_productitemversion piv, pdm_branch br
     where piv.pivid = j
       and piv.brid = br.brid;
--    putpipe('badpiv = '||j||' and its pi is '||v_piid);
    if not hashedpis.exists(v_piid) then
      badpis(badpis.count+1) := v_piid;
      errIds(errIds.count+1) := badpivs(j);
      hashedpis(v_piid) := badpivs(j);
    end if;
    execute immediate v_pivstmt using badpivs(j), j;
    j := badpivs.NEXT(j);
  end loop;

--  putpipe (badpis.count);

  if badpis.count > 0 then
    markUnEqualPIs(badpis, errIds, EMPTY_ERRID, ilDomainId, dbLinkName);
  end if;
end markUnequalPIVs;

procedure markUnequalDeps ( baddeps IN PDMArrayId,
                            delFromDep IN boolean,
                            ilDomainId IN varchar2,
                            dbLinkName IN varchar2 ) IS

  hashedpivs PDMArrayId; errIds PDMArrayId;
  v_depstmt varchar2(1000);
  j number; v_pivid number;
begin

  v_depstmt := 'update deptowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setUnEqual@'||dbLinkName||'(status), errid = :errid where dgid = :dgid';

  hashedpivs.delete;
  errIds.delete;
  j := baddeps.FIRST;
  while j is not null loop
    select dep.pivid into v_pivid from pdm_dependencygraph dep
     where dep.dgid = j;
--    putpipe('baddep = '||j||' and its piv is '||v_pivid);
    if not hashedpivs.exists(v_pivid) then
      hashedpivs(v_pivid) := baddeps(j);
    end if;
    execute immediate v_depstmt using baddeps(j), j;
    j := baddeps.NEXT(j);
  end loop;

--  putpipe (badpis.count);

  if hashedpivs.count > 0 then
    markUnequalPIVs(hashedpivs, delFromDep, ilDomainId, dbLinkName);
  end if;
end markUnequalDeps;



procedure checkAttrEquality ( objectType IN number,
                              ilDomainId IN number,
                              dbLinkName IN varchar2 ) IS

  attrName varchar2(160); ilTabName varchar2(160);
  wcTab varchar2(160);
  colName varchar2(160); wcTabName varchar2(14); wcAttrId number;
  defVal varchar2(4000); defValue varchar2(4000);
  v_dbid number; v_ilval varchar2(4000); v_wcval varchar2(4000);
  dbid_str varchar2(20); dupTabName varchar2(200); wcid_str varchar2(20);
  v_selstmt varchar2(4000); v_insstmt varchar2(4000); v_sqlstmt varchar2(4000);
  v_objIds PDMArrayId; errIds PDMArrayId; v_hashedObjs PDMArrayId;
  v_errId number;
  noDelFromDep boolean := false;
  upperFunc varchar2(20) := '';
  ignoreStr varchar2(120) :='';
  attrInfo RefCur; dataCur RefCur;
  
  piTable varchar2(100) :='';
  timing varchar2(30);
begin
  
--  putpipe('started checkAttrEquality for type '||objectType);
  if objectType = PI then
    dbid_str := 'piid';
    dupTabName := 'pitowcduplicate@'||dbLinkName;
    wcid_str := 'docmasterid';
    v_errId := UNEQUAL_PI_ATTR;
    ignoreStr :=' dup.ignore=0 ';
   
  elsif objectType = PIV then
    dbid_str := 'pivid';
    dupTabName := 'pivtowcduplicate@'||dbLinkName;
     piTable :=', pitowcduplicate@'||dbLinkName||' pi';
    wcid_str := 'docid';
    v_errId := UNEQUAL_PIV_ATTR;
    ignoreStr :=' dup.pivid is not null and dup.docid is not null and pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid  ';
  elsif objectType = DEP then
    dbid_str := 'dgid';
    dupTabName := 'deptowcduplicate@'||dbLinkName;
    piTable :=', pitowcduplicate@'||dbLinkName||' pi';
    ignoreStr :='  pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid   ';
    wcid_str := 'linkid';
    v_errId := UNEQUAL_DEP_ATTR;
  end if;
  --need modify here so if PIV, add table  pitowcduplicate@yhui_neptune pi in  v_selstmt and conditions ignore=0

  v_sqlstmt := 'select claname, defvalue, ilTableName, ilColumnName, wcTableName, wcid '||
                 'from AttrEqualityMap@'||dbLinkName||' where objectType = '||objectType||
                 ' and attrType = '||UDA;

  v_objIds.delete;
  errIds.delete;
  v_hashedObjs.delete;
  open attrInfo for v_sqlstmt;
  loop
    fetch attrInfo into attrName, defVal, ilTabName, colName, wcTabName, wcAttrId;
    exit when attrInfo%NOTFOUND;
    wcTab := wcTabName ||'@'||dbLinkName;
    upperFunc := '';
    if defVal is null then
      defValue := 'null';
    else
      if substr(defVal,1,1) = 'S' then  -- atribute value type is string
        defValue := ''''||substr(defVal,2)||'''';
        upperFunc := 'upper';
      elsif substr(defVal, 1, 1) = 'D' then  -- attribute value type is date and the format is 'J:SSSSS'
        defValue := 'to_date('''||substr(defVal,2)||''', ''J:SSSSS'')';
      else
        defValue := substr(defVal,2);
      end if;
    end if;
    
    if ilTabName = 'PDM_LCO_VUDA1' then --todo use like
      v_selstmt := 'select dup.'||dbid_str||
                 ', upper(nvl(u.'||colName||', '||defValue||')), valtab.value from '||
                 ilTabName||' u, pdm_lco lco, pdm_lcov lcov, '||dupTabName||' dup, '||
                 wcTab||' valtab '||piTable||' where MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||VALID_FLAG||') = 1 and '||
                 ignoreStr|| ' and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||CHECK_FOR_EQUALITY_FLAG||') = 1 and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'upper(nvl(u.'||colName||', '||defValue||
                 ')) <> upper(valtab.value) and lco.lcoid=lcov.lcoid and u.lcovid = lcov.lcovid '||
                 'and lcov.islatest = 1 and lco.'||dbid_str||' = dup.'||dbid_str||
                 ' and dup.'||wcid_str||' = valtab.ida3a4 and valtab.ida3a6 = '||wcAttrId;
    else
      v_selstmt := 'select u.'||dbid_str||
                 ', upper(nvl(u.'||colName||', '||defValue||')), valtab.value from '||
                 ilTabName||' u, '||dupTabName||' dup, '||
                 wcTab||' valtab '||piTable||' where MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||VALID_FLAG||') = 1 and '||
                 ignoreStr|| ' and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||CHECK_FOR_EQUALITY_FLAG||') = 1 and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'upper(nvl(u.'||colName||', '||defValue||
                 ')) <> upper(valtab.value) and u.'||dbid_str||' = dup.'||dbid_str||
                 ' and dup.'||wcid_str||' = valtab.ida3a4 and valtab.ida3a6 = '||wcAttrId;
    end if;

    v_insstmt := 'insert into unequalObjects@'||dbLinkName||' ('||dbid_str||', inequalityType, attrName, wcattrid, ilval, wcval) '||
                 'values (:1, :2, :3, :4, :5, :6)';
                 
  -- v_selstmt very slow need tune
--    putpipe (v_selstmt);
    open dataCur for v_selstmt;
    while true loop
      fetch dataCur into v_dbid, v_ilval, v_wcval;
      exit when dataCur%NOTFOUND;

      if not v_hashedObjs.exists(v_dbid) then
        v_objIds(v_objIds.count + 1) := v_dbid;
        v_hashedObjs(v_dbid) := v_errId;
      end if;
      execute immediate v_insstmt using v_dbid, objectType, attrName, wcAttrId, v_ilval, v_wcval;
    end loop;
    close dataCur;
--    putpipe (v_selstmt);
--    putpipe (v_insstmt);
--    execute immediate v_insstmt;
  end loop;
  close attrInfo;

  -- mark the objects in the dupTab;
  if objectType = PI then
    markUnEqualPIs(v_objIds, errIds, UNEQUAL_PI_ATTR, ilDomainId, dbLinkName);
  elsif objectType = PIV then
    markUnequalPIVs(v_hashedObjs, noDelFromDep, ilDomainId, dbLinkName);
  elsif objectType = DEP then
    markUnequalDeps(v_hashedObjs, noDelFromDep, ilDomainId, dbLinkName);
  end if;

--  exception when others then
--    putpipe('errcode is '||SQLCODE);
--    putpipe('errmesg is '||SQLERRM);

end checkAttrEquality;

procedure checkSysAttrEquality ( objectType IN number,
                                 ilDomainId IN number,
                                 dbLinkName IN varchar2 ) IS

  attrInfo RefCur; dataCur RefCur;
  v_selstmt varchar2(4000); v_insstmt varchar2(4000); v_sqlstmt varchar2(4000);
  attrName varchar2(160); ilTabName varchar2(160); ilColName varchar2(160);
  wcTabName varchar2(160); wcColName varchar2(160); wcTab varchar2(200);
  dbid_str varchar2(20); dupTabName varchar2(200); wcid_str varchar2(20);
  ignoreStr varchar2(120) :='';
  piTable varchar2(100) :='';
  v_objIds PDMArrayId; errIds PDMArrayId; v_hashedObjs PDMArrayId;
  v_errId number;
  v_dbid number; v_ilval varchar2(4000); v_wcval varchar2(4000);
  noDelFromDep boolean := false;
begin
  
--  putpipe('started checkAttrEquality for type '||objectType);
  if objectType = PI then
    dbid_str := 'piid';
    dupTabName := 'pitowcduplicate@'||dbLinkName;
    wcid_str := 'docmasterid';
    ignoreStr :=' dup.ignore=0  ';
  elsif objectType = PIV then
    dbid_str := 'pivid';
    dupTabName := 'pivtowcduplicate@'||dbLinkName;
     piTable :=', pitowcduplicate@'||dbLinkName||' pi';
    wcid_str := 'docid';
     ignoreStr :=' dup.pivid is not null and dup.docid is not null and pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid ';
  elsif objectType = DEP then
    dbid_str := 'dgid';
    dupTabName := 'deptowcduplicate@'||dbLinkName;
    piTable :=', pitowcduplicate@'||dbLinkName||' pi';
    ignoreStr :='  pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid ';
    wcid_str := 'linkid';
   end if;
   v_sqlstmt := 'select claname, ilTableName, ilColumnName, wcTableName, wcColumnName '||
                 'from AttrEqualityMap@'||dbLinkName||' where objectType = '||objectType||
                 ' and attrType = '||SYS;

  v_objIds.delete;
  errIds.delete;
  v_hashedObjs.delete;
  open attrInfo for v_sqlstmt;
  loop
    fetch attrInfo into attrName, ilTabName, ilcolName, wcTabName, wcColName;
    exit when attrInfo%NOTFOUND;
    wcTab := wcTabName ||'@'||dbLinkName;

    v_selstmt := 'select il.'||dbid_str||', '||
                 'il.'||ilcolName||', wc.'||wcColName||' from '||
                 ilTabName||' il, '||dupTabName||' dup, '||
                 wcTab||' wc '||piTable||' where MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||VALID_FLAG||') = 1 and '||
                  ignoreStr|| ' and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status,'||CHECK_FOR_EQUALITY_FLAG||') = 1 and '||
                 'MigrationPK.checkFlag@'||dbLinkName||'(dup.status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'il.'||ilcolName||' <> wc.'||wcColName||' and il.'||dbid_str||' = dup.'||dbid_str||
                 ' and dup.'||wcid_str||' = wc.ida2a2';

    v_insstmt := 'insert into unequalObjects@'||dbLinkName||' ('||dbid_str||', inequalityType, attrName, wcattrid, ilval, wcval) '||
                 'values (:1, :2, :3, :4, :5, :6)';
    v_errId := UNEQUAL_PIV_ATTR;
    open dataCur for v_selstmt;
    while true loop
      fetch dataCur into v_dbid, v_ilval, v_wcval;
      exit when dataCur%NOTFOUND;

      if not v_hashedObjs.exists(v_dbid) then
        v_objIds(v_objIds.count + 1) := v_dbid;
        v_hashedObjs(v_dbid) := v_errId;
      end if;
      execute immediate v_insstmt using v_dbid, objectType, attrName, 0, v_ilval, v_wcval;
    end loop;

  end loop;
  close attrInfo;

  markUnequalPIVs(v_hashedObjs, noDelFromDep, ilDomainId, dbLinkName);
end checkSysAttrEquality;

procedure markRelatedObjects( ilDomainId number,
                              dbLinkName varchar2 ) is

  cursor getParentPIVs(cv_piid number) is
    select pivid from pdm_dependencygraph where piid = cv_piid;

  needCalculation number := 0;
  v_sqlstmt varchar2(2000); v_piupdstmt varchar2(2000); v_pivupdstmt varchar2(2000);
  piCur RefCur;
  v_piid number := 0; j number := 0; v_pivid number; v_parentPI number;
  seedPIs PDMArrayId; hashed_seedPIs PDMArrayId;

begin

  -- if all objects that are not marked for merge and are valid are also marked for equality check, we dont need to do this
  v_sqlstmt := 'select count(piid) from pitowcduplicate@'||dbLinkName||' where ignore=0 and '||
              'migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1 and '||
              'migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
              'migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||')  = 0 ';
--  putpipe(v_sqlstmt);
  execute immediate v_sqlstmt into needCalculation;
--why we need run this similar query twice, 
  if needCalculation <> 0 then
    -- find all the pivs which need related object calculation
    v_sqlstmt := 'select piid from pitowcduplicate@'||dbLinkName||' where ignore=0 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||')  = 1 ';
    open piCur for v_sqlstmt;
    j := 0;
    while true loop
      fetch piCur into v_piid;
      exit when piCur%NOTFOUND;
      j := j + 1;
      seedPIs(j) := v_piid;
      hashed_seedPIs(v_piid) := v_piid;
    end loop;
    close piCur;

    v_piid := seedPIs.FIRST;
    while v_piid is not null loop
      open getParentPIVs(v_piid);
      while true loop
        fetch getParentPIVs into v_pivid;
        exit when getParentPIVs%NOTFOUND;

        select br.piid into v_parentPI from pdm_productitemversion piv, pdm_branch br
         where piv.brid = br.brid
           and piv.pivid = v_pivid;

        -- if we havent already encountered this PI add it to the todo list
        if not hashed_seedPIs.exists(v_parentPI) then
          j := j + 1;
          seedPIs(j) := v_parentPI;
          hashed_seedPIs(v_parentPI) := v_parentPI;
        end if;

      end loop;
      close getParentPIVs;
      v_piid := seedPIs.NEXT(v_piid);
    end loop;

    -- todo also need to do the same for family tables ??

    -- now find all the pis which are in the seedPIs and have the check equality flag to false
    -- set the checkequality flag to true for this PI and all the pivs for this PI

    v_sqlstmt := 'select piid from pitowcduplicate@'||dbLinkName||' where ignore=0 and '||
              'migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1 and '||
              'migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||')  = 0 '||
              'for update of status';

    v_piupdstmt := 'update pitowcduplicate@'||dbLinkName||' set status = '||
                 'migrationPK.setCheckForEquality@'||dbLinkName||'(status) '||
                 'where piid = :piid and ignore=0 ';

    v_pivupdstmt := 'update pivtowcduplicate@'||dbLinkName||' set status = '||
                 'migrationPK.setCheckForEquality@'||dbLinkName||'(status) '||
                 'where piid = :piid and pivid is not null and docid is not null ';

--    putpipe(v_sqlstmt);
    open piCur for v_sqlstmt;
    while true loop
      fetch piCur into v_piid;
      exit when piCur%NOTFOUND;

      if hashed_seedPIs.exists(v_piid) then
        execute immediate v_piupdstmt using v_piid;
        execute immediate v_pivupdstmt using v_piid;
      end if;

    end loop;
    close piCur;

  end if;

end markRelatedObjects;

procedure  checkDepPIEquality( ilDomainId in number,
                               dbLinkName in varchar2,
                               badObjFound in out boolean ) IS

  depCur RefCur;
  v_pivid number; v_piid number; v_prevpiid number := -1; v_count number; v_dgid number;
  baddeps PDMArrayId;
  badpi boolean := false; nodelFromDep boolean := false;
  v_sqlstmt varchar2(2000); v_sqlstmt2 varchar2(2000);

begin
  v_sqlstmt := 'select dgid, pivid, piid from deptowcduplicate@'||dbLinkName||
               ' where domainid = '||ilDomainId||
               ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||MARKED_FOR_MERGE_FLAG||') = 0 '||
               ' and errid is null order by piid';

  v_sqlstmt2 := 'select count(piid) From pitowcduplicate@'||dbLinkName||
                ' where piid = :piid and domainid = '||ilDomainId|| ' and ignore = 0 '||
                ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||') = 1 '||
                ' and not '||
                -- the two pis are unequal and equality is checked
                ' (migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||') = 0 '||
                ' and migrationPK.checkFlag@'||dbLinkName||'(status, '||EQUAL_FLAG||') = 0)';

  baddeps.delete;
--  putpipe(v_sqlstmt);
--  putpipe(v_sqlstmt2);
  open depCur for v_sqlstmt;
  while true loop
    fetch depCur into v_dgid, v_pivid, v_piid;
    exit when depCur%NOTFOUND;


    if v_piid <> v_prevpiid then -- piid changed
      v_prevpiid:=v_piid;
      -- check if the piid is equal in the pitowcduplicate table
      badpi := false;
--      putpipe('sql stmt using v_piid'||v_piid);
      execute immediate v_sqlstmt2 into v_count using v_piid;
      if v_count = 0 then -- pi is unequal or does not exist in the table or ignored pi
        badpi := true;
      end if;
--      putpipe ('looking for PI '||v_piid||' and equality = '||v_count);
    end if;

    if badpi then
      baddeps(v_dgid) := CHILD_PI_UNEQUAL;
    end if;

  end loop;
  close depCur;

  -- badObjFound has a value true, so dont need to change if there are bad pivs
  if baddeps.count = 0 then
    badObjFound := false;
--    putpipe ('badObjects found in depcheck');
  end if;

  markUnequalDeps(baddeps, noDelFromDep, ilDomainId, dbLinkName);

--  putpipe('finished checkDepPIEquality');

end checkDepPIEquality;

procedure  checkFamTabEquality( ilDomainId in number,
                                dbLinkName in varchar2,
                                badObjFound in out boolean ) IS

  cursor getLOId (cv_pivid number) is
    select lov.loid from pdm_lov lov, pdm_lovcontents cont
     where cont.pivid = cv_pivid
       and cont.lovid = lov.lovid;

  cursor getLOVPIVs (cv_loid number, cv_pivid number) is
    select cont.lovid, cont.pivid, br.piid, piv.pivclass
      from pdm_lovcontents cont, pdm_lov lov, pdm_productitemversion piv, pdm_branch br
     where lov.loid = cv_loid
       and cont.lovid = lov.lovid
       and cont.pivid = piv.pivid
       and piv.brid = br.brid
       and exists (select 1 from pdm_lovcontents cont2 where cont2.lovid = cont.lovid and cont2.pivid = cv_pivid)
       order by lov.lovid;


  pivCur RefCur; famTabCur RefCur; docCur RefCur;
  v_outersqlstmt varchar2(2000); v_sqlstmt1 varchar2(2000);
  v_innersqlstmt varchar2(2000); v_sqlstmt2 varchar2(2000);
  v_pivid number; v_pivclass number; v_loid number; v_pi number; topGenPIV number;
  v_lovid number; v_type number; v_lopiv number; v_prevlovid number; j number; topGenCtr number;
  lovCounter number := 0; topGenDoc number; v_famtabid number; v_ftdocid number;
  lovStart number; lovEnd number; docCount number; v_docid number; v_status number;
  fam_visited_pivs PDMArrayId; lo_pivs PDMArrayId; breaks PDMArrayId; topGenerics PDMArrayId;
  lopis PDMArrayId; v_pis PDMArrayId; v_pivs PDMArrayId; v_lovs PDMArrayId; v_types PDMArrayId;
  equalTopGens PDMArrayId; lovDocs PDMArrayId; errIds PDMArrayId; errpivs PDMArrayId;
  topGenFound boolean; loEqual boolean := true; lovEqual boolean := true;
  ignoreStr varchar2(120):=' dup.pivid is not null and dup.docid is not null and pi.ignore=0 and pi.piid=dup.piid and pi.domainid=dup.domainid ';

begin

--  putpipe('doing famtab equality check');
  -- find all the pivs that need checking
  v_outersqlstmt := 'select dup.pivid from pivtowcduplicate@'||dbLinkName||' dup, pitowcduplicate@'||dbLinkName||' pi  where '||
                 ignoreStr ||' and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||MARKED_FOR_MERGE_FLAG||') = 0 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||CHECK_FOR_EQUALITY_FLAG||')  = 1 and '||
                 'migrationPK.checkFlag@'||dbLinkName||'(dup.status, '||VALID_FLAG||')  = 1';

   errIds.delete;
   fam_visited_pivs.delete;
   open pivCur for v_outersqlstmt;
   while true loop
     fetch pivCur into v_pivid;
     exit when pivCur%NOTFOUND;

     -- fam_visited_pivs have already been checked
     if not fam_visited_pivs.exists(v_pivid) then
       -- check if this is a family table piv
--       putpipe(v_pivid);
       select pivclass into v_pivclass from pdm_productitemversion where pivid = v_pivid;
       -- pivclass = 0 means it is a standalone piv, otherwise part of a family table
       -- so check fam tab equality in that case
       if v_pivclass <> 0 then
         v_lovs.delete;
         v_pivs.delete;
         v_types.delete;
         v_pis.delete;
         breaks.delete;
         topGenerics.delete;
         equalTopGens.delete;
         lopis.delete;
         v_prevlovid := -1;
         v_lovid := 0;
         --  find the lo for this piv
         open getLOId(v_pivid);
         fetch getLOId into v_loid;
         close getLOId;

         -- get all pivs and lovs for this lo
--         putpipe('for piv :'||v_pivid||' status is :'||v_pivclass||' and lo is :'||v_loid);
         loEqual := true;
         open getLOVPIVs(v_loid, v_pivid);
         while true loop
           fetch getLOVPIVs into v_lovid, v_lopiv, v_pi, v_type;
           exit when getLOVPIVs%NOTFOUND;
           v_lovs(v_lovs.count+1) := v_lovid;
           v_pivs(v_pivs.count+1) := v_lopiv;
           if not lopis.exists(v_pi) then
             v_pis(v_pis.count+1) := v_pi;
             lopis(v_pi) := v_pi;
           end if;
           v_types(v_types.count+1) := v_type;
           -- todo what if no topgen found for lov
           if v_lovid <> v_prevlovid then
             breaks(breaks.count + 1) := v_lovs.count;
--             putpipe('lov changed to '||v_lovid||' at '||v_lovs.count);
             v_prevlovid := v_lovid;
             topGenFound := false;
           end if;
           if not topGenFound then
             if v_type = 2 then
               topGenerics(topGenerics.count + 1) := v_pivs.count;
--               putpipe('and topgen for this lov is '||v_lopiv);
               topGenFound := true;
             end if;
           end if;
         end loop;
         close getLOVPIVs;

         -- for each lov check if the contents match the contents in the pdmlink and are not marked unequal
         if v_lovs.count <> 0 then
           errpivs.delete;
           for lovCounter in 1..breaks.count loop
             topGenCtr := topGenerics(lovCounter);
             topGenPIV := v_pivs(topGenCtr);
             if not equalTopGens.exists(topGenPIV) then
               lovStart := breaks(lovCounter);
               if lovCounter = breaks.count then
                 lovEnd := v_lovs.count;
               else
                 lovEnd := breaks(lovCounter+1) - 1;
               end if;
               lovDocs.delete;
               -- first check that none of the pivs are marked unequal or invalid in duplicate table
               v_innersqlstmt := 'select docid, status from pivtowcduplicate@'||dbLinkName||
                            ' where pivid = :pivid and not '||
                       '(migrationPK.checkFlag@'||dbLinkName||'(status, '||CHECK_FOR_EQUALITY_FLAG||')  = 0 and '||
                       'migrationPK.checkFlag@'||dbLinkName||'(status, '||EQUAL_FLAG||')  = 0) and '||
                       'migrationPK.checkFlag@'||dbLinkName||'(status, '||VALID_FLAG||')  = 1';
--               putpipe (v_innersqlstmt);
--               putpipe('lovStart '||lovStart||' and lovEnd '||lovEnd||' and lov '||v_lovs(lovStart));
               for j in lovStart..lovEnd loop
                 begin
                   execute immediate v_innersqlstmt into v_docid, v_status using v_pivs(j);
                 exception when NO_DATA_FOUND then
                   -- mark all unequal and move on
                   loEqual := false;
--                   putpipe ('no data found for '||v_pivs(j));
                   -- content PIV is not in duplicate table
                   errpivs(v_pivs(j)) := FT_CPIV_NOT_IN_DUPTAB;
                   goto badLO;
                 end;
                 if v_docid is null then
                   -- mark unequal and move on
                   loEqual := false;
--                   putpipe('docid was null for '||v_pivs(j));
                   errpivs(v_pivs(j)) := FT_CPIV_NO_DOC_IN_DUPTAB;
                   goto badLO;
                 end if;
                 if v_pivs(j) = topGenPIV then
                   topGenDoc := v_docid;
                 end if;
                 lovDocs(v_docid):= v_docid;
               end loop;

               -- now check if the familytable in pdmlink has the same contents as this
               -- find sepfamilytables for the topGenDoc
               v_sqlstmt1 := 'select ida3b5 from epmcontainedin@'||dbLinkName||' where '||
                            'ida3a5 = :docId';
               open famTabCur for v_sqlstmt1 using topGenDoc;
               lovEqual := true;
               while true loop
                 fetch famTabCur into v_famtabid;
                 exit when famTabCur%NOTFOUND;

                 docCount := 0;
                 lovEqual := true;
                 v_sqlstmt2 := 'select ida3a5 from epmcontainedin@'||dbLinkName||' where '||
                               'ida3b5 = :famtabid';
                 open docCur for v_sqlstmt2 using v_famtabid;
                 while true loop
                   fetch docCur into v_ftdocid;
                   exit when docCur%NOTFOUND;
                   docCount := docCount + 1;
                   if not lovDocs.exists(v_ftdocid) then
--                     putpipe('lov not equal: docid '||v_ftdocid||', famtab = '||v_famtabid);
                     lovEqual := false;
                     exit;
                   end if;
                 end loop;
                 close docCur;

                 if lovEqual then
                   if docCount <> lovDocs.count then
--                     putpipe('lov not equal: wcCount '||docCount||', ilCount '||lovDocs.count);
                     lovEqual := false;
                   end if;
                 end if;
                 if lovEqual then
                   exit;
                 end if;
               end loop;
               close famTabCur;

               if not lovEqual then
                 loEqual := false;
                 errpivs(topGenPIV) := FT_CONTENTS_NOT_EQUAL;
                 goto badLO;
               else
                 equalTopGens(topGenPIV) := topGenPIV;
               end if;
             end if; -- if topGenPIV did not exist in equalTopGens
           end loop; -- loop for cheking all lovs of this lo

<<badLO>>
           if not loEqual then
             -- mark the problematic piv with apprpriate reason
             if errpivs.count > 0 then
               markUnequalPIVs(errpivs, false, ilDomainId, dbLinkName);
             end if;
             -- mark all PIs and their pivs as unequal because famtab is unequal
             markUnEqualPIs(v_pis, errIds, FT_NOT_EQUAL, ilDomainId, dbLinkName);
             -- this is the only place where we mark objects unequal in this procedure
             -- so lets make badObjFound true
             badObjFound := true;
--             putpipe ('badObjects found in famtabcheck');
           end if;
           -- this lo is done regardless of whether it is equal or not, so put all its pivs in fam_visited_pivs
           for j in 1..v_pivs.count loop
--             putpipe('visited pivid is :'||v_pivs(j));
             fam_visited_pivs(v_pivs(j)) := v_pivs(j);
           end loop;

         end if; -- if there is at least one lov
       end if; -- pivclass is not 0, so this is part of familytable
--     else
--       putpipe ('pivid :'||v_pivid||' is already visited');
     end if; -- this piv is not already visited for fam tab comparisons
   end loop; -- loop for all the valid and need equality check pivs
   close pivCur;

end checkFamTabEquality;

procedure markUnEqualPIs( pis in PDMArrayId,
                          errIds in PDMArrayId,
                          errId in number,
                          ilDomainId in number,
                          dbLinkName in varchar2 ) is

  j number;
  v_piid number; v_errId number;
  v_pistmt varchar2(2000); v_pivstmt varchar2(2000);

begin

  v_pistmt := 'update pitowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setUnEqual@'||dbLinkName||'(status), errid = :errid where piid = :piid and ignore=0 ';

  -- only update pivs which are not yet marked with errid
  v_pivstmt := 'update pivtowcduplicate@'||dbLinkName||' set status = '||
               'migrationPK.setUnEqual@'||dbLinkName||'(status), errid = :errid where piid = :piid '||
               'and errid is null';

  if pis.count > 0 then
    for j in 1..pis.count loop
      v_piid := pis(j);
      if errId = EMPTY_ERRID then
        v_errId := errIds(j);
      else
        v_errId := errId;
      end if;
--      putpipe('unequal pi: '||v_piid||', '||errId);
      execute immediate v_pistmt using v_errId, v_piid;
      execute immediate v_pivstmt using UNEQUAL_PI, v_piid;
    end loop;
  end if;

end markUnEqualPIs;


-- All the following functions are used from migrationPK package
-- so they are commented out here

--function setEqual (status in number) return number is
--
--  tstatus number := 0;
--  t2status number := 0;
--  fstatus number := 0;
--
--begin
--  -- first turn on equality bit
--  tstatus := status + EQUAL_FLAG - bitand(status, EQUAL_FLAG);
--
--  -- now turn off inequality bit
--  t2status :=  bitand(tstatus, NOT_UNEQUAL_FLAG);
--
--  -- now turn off the bit check_equality_needed
--  fstatus :=  bitand(t2status, NOT_CHECK_FOR_EQUALITY_FLAG);
--
--  putpipe('EQUAL status: ' ||status||', tstatus: '||tstatus||', t2status: '||t2status||', fstatus: '||fstatus);
--  return fstatus;
--end setEqual;

--function setCheckForEquality (status in number) return number is
--
--  fstatus number := 0;
--
--begin
--
--  -- turn on the bit check_equality_needed
--  -- bitand subtracts the flag value if the flag is already present, so that it is not added twice
--  fstatus :=  status + CHECK_FOR_EQUALITY_FLAG - bitand(status, CHECK_FOR_EQUALITY_FLAG);
--
--  return fstatus;
--end setCheckForEquality;

--function setUnEqual (status in number) return number is
--
--  tstatus number := 0;
--  t2status number := 0;
--  fstatus number := 0;
--
--begin
--  -- first turn off the equality bit
--  
--  tstatus := bitand(status, NOT_EQUAL_FLAG);
--
--  -- now turn off the bit check_equality_needed
--  t2status :=  bitand(tstatus, NOT_CHECK_FOR_EQUALITY_FLAG);
--
--  -- now turn on the inequality bit
--  fstatus := t2status + UNEQUAL_FLAG - bitand(t2status, UNEQUAL_FLAG);
--
--  putpipe('UNEQUAL status: ' ||status||', tstatus: '||tstatus||', t2status: '||t2status||', fstatus: '||fstatus);
--
--  return fstatus;
--end setUnEqual;

--function checkFlag (status in number, flag in number) return number is
--
--begin
--
--  if bitand(status, flag) = 0 then
--    return 0;
--  else
--    return 1;
--  end if;
--
--end checkFlag;

end mig_preprocess_pkg;

/

sho err package body mig_preprocess_pkg;