-- The following program checks for the existence of the tables
-- uoidxxx and pctree_v and hierarchyXXX before it creates them
-- Check for existence of UOIDREFERENCETABLE and CREATE IT IF
-- IT DOES NOT EXIST. For other Data Structures , drop them and
-- recreate them so as to avoid version related problems and
-- Bug fixes (Bug # 3161472 Related Fix and Bug # 2969208)
BEGIN
DECLARE
  x number := 100;
BEGIN -- Begin for Declare Block
 BEGIN -- Begin for UOIDREFERENCETABLE Block
  SELECT COUNT(1)
  INTO x
  FROM USER_TABLES
  WHERE TABLE_NAME IN ('UOIDREFERENCETABLE');
 IF x = 0 THEN
    EXECUTE IMMEDIATE
     'create table UOIDReferenceTable (
     objtype varchar2(128),
     name varchar2(128),
     context varchar2(1024),
     uoid varchar2(255),
     mdlobjtype varchar2(128))';
 END IF;
 EXCEPTION WHEN NO_DATA_FOUND THEN
    null;
END; -- End For UOIDREFERENCETABLE Block
end;

--- Begin Dropping Tables
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE UOIDReferenceTargetTable';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE UOIDErrorTable';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE objectContextTree';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ObjectHierarchyTable';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE hierarchyRoots';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE hierarchyRefTable';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

--- End of Dropping Tables

--- Begin Dropping Indexes

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX refcontextindex';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX refnameindex';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX refobjtypeindex';
   EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX parentidindex';
      EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX childidindex';
      EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
EXECUTE IMMEDIATE 'DROP INDEX rootidindex';
      EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
EXECUTE IMMEDIATE 'DROP INDEX memberidindex';
      EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX rootsidindex';
      EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
 EXECUTE IMMEDIATE 'DROP INDEX REFROOTIDINDEX';
      EXCEPTION WHEN OTHERS THEN NULL;
END;
--- End of Dropping Indexes

-- Begin RECREATE ALL THE DROPPED TABLES AND INDEXES.

EXECUTE IMMEDIATE
     'create table UOIDReferenceTargetTable (
     objtype varchar2(128),
     name varchar2(128),
     context varchar2(1024),
     uoid varchar2(255),
     mdlobjtype varchar2(128),
     elementid number)';
   EXECUTE IMMEDIATE 'create index refcontextindex on uoidreferencetable(context)';
   EXECUTE IMMEDIATE 'create index refnameindex on uoidreferencetable(name)';
   EXECUTE IMMEDIATE 'create index refobjtypeindex on uoidreferencetable(objtype)';

   EXECUTE IMMEDIATE
     'create table UOIDErrorTable (
      objtype varchar2(128),
      name varchar2(128),
      context varchar2(1024),
      uoid varchar2(255),
      elementid number)';

   EXECUTE IMMEDIATE
     'create table  objectContextTree  (
      parentId number,
      childId number,
      parentRole varchar2(100),
      childRole varchar2(100))';

   EXECUTE IMMEDIATE 'create index parentidindex on objectContextTree (parentid)';
   EXECUTE IMMEDIATE 'create index childidindex on objectContextTree (childid)';

   EXECUTE IMMEDIATE 'create table ObjectHierarchyTable(rootid number,ordering number,memberid number)';

   EXECUTE IMMEDIATE 'create index rootidindex on ObjectHierarchyTable(rootid)';
   EXECUTE IMMEDIATE 'create index memberidindex on ObjectHierarchyTable(memberid)';

   EXECUTE IMMEDIATE 'create table hierarchyRoots(rootid number)';

   EXECUTE IMMEDIATE 'create index rootsidindex on hierarchyRoots(rootid)';

   EXECUTE IMMEDIATE
     'create table hierarchyRefTable(
      rootid number,
      classname varchar2(255),
      uoid varchar2(255),
      name varchar2(255),
      logicalname varchar2(255))';

   EXECUTE IMMEDIATE 'create index REFROOTIDINDEX on HIERARCHYREFTABLE(ROOTID)';

-- End RECREATE ALL THE DROPPED TABLES AND INDEXES.


END;
/

CREATE OR REPLACE PACKAGE OWBUOIDReconcile IS
   FUNCTION  getContextName(elemID IN NUMBER) return varchar2;
   PROCEDURE populatePCTree;
   PROCEDURE populateHierarchyTable;
   PROCEDURE populateHierarchyRoots;
   PROCEDURE populateHierarchyRefTable;
   PROCEDURE populateUOIDTargetTable;
   PROCEDURE reconcile;
   PROCEDURE reconcileUOIDs;
   PROCEDURE cleanup;
   FUNCTION getVersion RETURN VARCHAR2;
END OWBUOIDReconcile;
/

CREATE OR REPLACE PACKAGE OWM_VIEW_UTILITIES AS

 index_exists EXCEPTION;
 pragma EXCEPTION_INIT (index_exists, -00955);

 FUNCTION owb_comp_id(objID IN NUMBER)
    	RETURN NUMBER;
    	
 FUNCTION GET_CONTAINED_FCO_PATH(containedObjID IN NUMBER)
        RETURN VARCHAR2;

 FUNCTION owb_param_id(objID IN NUMBER)
    	RETURN NUMBER;

 FUNCTION PARAM_DIRECTION(direction IN NUMBER)
	RETURN VARCHAR2;

 FUNCTION CLASSIFIED_OBJ_TYPE(classname IN VARCHAR2)
	RETURN VARCHAR2;

 FUNCTION CLASSIFIED_OBJ_TYPE2(theEltId IN NUMBER, classname IN VARCHAR2)
	RETURN VARCHAR2;

 FUNCTION OBJ_TYPE_TO_SCRIPTING_TYPE(objtype IN VARCHAR2)
  RETURN VARCHAR2;

 FUNCTION OPERATOR_TYPE(operatortype IN VARCHAR2)
	RETURN VARCHAR2;
	
 FUNCTION CONTEXT_NAME(theEltId IN NUMBER/**, classname IN VARCHAR2, objname IN VARCHAR2**/)
	RETURN VARCHAR2;	

 FUNCTION GET_FCO_PATH(theEltId IN NUMBER/**, classname IN VARCHAR2, objname IN VARCHAR2**/)
	RETURN VARCHAR2;	
/**
 FUNCTION PARENT_OBJ_TYPE(theEltId IN NUMBER, classname IN VARCHAR2)
	RETURN VARCHAR2;	
	
 FUNCTION PARENT_OBJ_NAME(theEltId IN NUMBER, classname IN VARCHAR2)
	RETURN VARCHAR2;
**/	
 FUNCTION PARENT_OBJ_ID(theEltId IN NUMBER/**, classname IN VARCHAR2**/)
	RETURN NUMBER;

 FUNCTION PARENT_OBJ_ID2(theEltId IN NUMBER/**, classname IN VARCHAR2**/)
	RETURN NUMBER;

 FUNCTION FUNCTION_SCRIPT(theEltId IN NUMBER)
	RETURN VARCHAR2;
	
 PROCEDURE REFRESHMV(mv_name IN VARCHAR2);

 FUNCTION REFRESHOWB RETURN VARCHAR2;

/****
 FUNCTION checkfkusage( fk_id IN NUMBER, fkownerrelationusage IN NUMBER, ukownerrelationusage IN NUMBER, mapid IN NUMBER) RETURN CHAR parallel_enable;

 FUNCTION findSourceItemUsage(funcItemUsageSource IN NUMBER, copyOnly IN VARCHAR2 DEFAULT 'FALSE') RETURN NUMBER;

 FUNCTION getAggregationFunction(itemUsage IN NUMBER) RETURN VARCHAR2;
****/

 FUNCTION PARAMETER_SEQUENCE2(theCompId IN NUMBER, theSeq IN NUMBER) RETURN VARCHAR2;

 FUNCTION PARAMETER_EXPRESSION2(expression1 IN VARCHAR2, theMapCompId IN NUMBER) RETURN VARCHAR2;

 PROCEDURE preImport;

 PROCEDURE postImport (p_user in VARCHAR2 default USER);

 --Given elementid of installedModule, return concrete module type
 FUNCTION getModuleName(p_id in number) RETURN VARCHAR2;

 --pass in classname for better performance
 FUNCTION getModuleName2(p_id in number, l_classname in varchar2) RETURN VARCHAR2;

 FUNCTION getVersion RETURN VARCHAR2;

/****
 FUNCTION isRealSource(sourceUsageId NUMBER) RETURN VARCHAR2;

 FUNCTION isMappedToTarget(sourceUsageId NUMBER,MapId NUMBER ,TargetID NUMBER ) RETURN VARCHAR2;
****/

 PROCEDURE analyzeOWBTable;

END OWM_VIEW_UTILITIES;
/


-- Bug 4283054.  Must drop dependent objects first (ORA-2303),
--     and must ignore exception in case repository is new.
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_column_prop_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_column_query_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_uk_prop_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_uk_query_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_fk_prop_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_fk_query_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_fd_prop_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_fd_query_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_rr_prop_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE profile_rr_query_set';
   EXCEPTION WHEN OTHERS THEN NULL;
END;
/
-- created for Paris by Ben

CREATE OR REPLACE TYPE profile_column_prop_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       AVG_VALUE              VARCHAR2(40),
                                       MAX_VALUE              VARCHAR2(4000),
                                       MIN_VALUE              VARCHAR2(4000),
                                       MEDIAN_VALUE           VARCHAR2(4000),
                                       STDDEV_VALUE           VARCHAR2(40),
                                       NUM_NULLS              VARCHAR2(40),
                                       NUM_DISTINCT           VARCHAR2(40),
                                       CONSENSUS_DATATYPE     VARCHAR2(40),
                                       CONSENSUS_DATATYPE_CNT VARCHAR2(40),
                                       CONSENSUS_LENGTH       VARCHAR2(40),
                                       CONSENSUS_LENGTH_CNT   VARCHAR2(40),
                                       CONSENSUS_PRECISION    VARCHAR2(40),
                                       CONSENSUS_PRECISION_CNT VARCHAR2(40),
                                       CONSENSUS_SCALE        VARCHAR2(40),
                                       CONSENSUS_SCALE_CNT    VARCHAR2(40),
                                       COMMON_FORMAT          VARCHAR2(40),
                                       COMMON_FORMAT_CNT      VARCHAR2(40),
                                       DOMINANT_CHARACTER_PATTERN      VARCHAR2(4000),
                                       DOMINANT_CHARACTER_PATTERN_CNT  VARCHAR2(40),
                                       DOMINANT_WORD_PATTERN           VARCHAR2(4000),
                                       DOMINANT_WORD_PATTERN_CNT       VARCHAR2(40),
                                       MAX_LENGTH             VARCHAR2(40),
                                       MIN_LENGTH             VARCHAR2(40),
                                       MAX_PRECISION          VARCHAR2(40),
                                       MIN_PRECISION          VARCHAR2(40),
                                       MAX_SCALE              VARCHAR2(40),
                                       MIN_SCALE              VARCHAR2(40),

     CONSTRUCTOR FUNCTION profile_column_prop_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_column_prop_set AS TABLE OF profile_column_prop_type;
/
CREATE OR REPLACE TYPE profile_column_query_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       CFORMAT_COMPLIANT_QUERY              VARCHAR2(4000),
                                       CFORMAT_DRILLDOWN_QUERY              VARCHAR2(4000),
                                       CFORMAT_NONCOMPLIANT_QUERY           VARCHAR2(4000),

                                       DATATYPE_COMPLIANT_QUERY             VARCHAR2(4000),
                                       DATATYPE_DRILLDOWN_QUERY             VARCHAR2(4000),
                                       DATATYPE_NONCOMPLIANT_QUERY          VARCHAR2(4000),

                                       DOMAIN_COMPLIANT_QUERY               VARCHAR2(4000),
                                       DOMAIN_DRILLDOWN_QUERY               VARCHAR2(4000),
                                       DOMAIN_NONCOMPLIANT_QUERY            VARCHAR2(4000),

                                       NULL_COMPLIANT_QUERY                 VARCHAR2(4000),
                                       NULL_DRILLDOWN_QUERY                 VARCHAR2(4000),
                                       NULL_NONCOMPLIANT_QUERY              VARCHAR2(4000),

                                       CHAR_PATTERN_COMPLIANT_QUERY         CLOB,
                                       CHAR_PATTERN_DRILLDOWN_QUERY         CLOB,
                                       CHAR_PATTERN_NONCOM_QUERY            CLOB,

                                       WORD_PATTERN_COMPLIANT_QUERY         CLOB,
                                       WORD_PATTERN_DRILLDOWN_QUERY         CLOB,
                                       WORD_PATTERN_NONCOM_QUERY            CLOB,
                                       
     CONSTRUCTOR FUNCTION profile_column_query_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_column_query_set AS TABLE OF profile_column_query_type;
/
CREATE OR REPLACE TYPE profile_uk_prop_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       UK_ERROR           VARCHAR2(40),
                                       UK_PARTITION       VARCHAR2(40),

     CONSTRUCTOR FUNCTION profile_uk_prop_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_uk_prop_set AS TABLE OF profile_uk_prop_type;
/
CREATE OR REPLACE TYPE profile_uk_query_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       COMPLIANT_QUERY                   VARCHAR2(4000),
                                       COMPLIANT_CNT_QUERY               VARCHAR2(4000),
                                       NONCOMPLIANT_QUERY                VARCHAR2(4000),
                                       NONCOMPLIANT_CNT_QUERY            VARCHAR2(4000),
                                       DRILLDOWN_QUERY                   VARCHAR2(4000),

     CONSTRUCTOR FUNCTION profile_uk_query_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_uk_query_set AS TABLE OF profile_uk_query_type;
/
CREATE OR REPLACE TYPE profile_fk_prop_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       LOCAL_MAX_CARDINALITY            VARCHAR2(40),
                                       LOCAL_MIN_CARDINALITY            VARCHAR2(40),
                                       REMOTE_MAX_CARDINALITY           VARCHAR2(40),
                                       REMOTE_MIN_CARDINALITY           VARCHAR2(40),
                                       FK_ERROR                         VARCHAR2(40),

     CONSTRUCTOR FUNCTION profile_fk_prop_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_fk_prop_set AS TABLE OF profile_fk_prop_type;
/
CREATE OR REPLACE TYPE profile_fk_query_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       COMPLIANT_QUERY                   VARCHAR2(4000),
                                       COMPLIANT_CNT_QUERY               VARCHAR2(4000),
                                       NONCOMPLIANT_QUERY                VARCHAR2(4000),
                                       NONCOMPLIANT_CNT_QUERY            VARCHAR2(4000),
                                       DRILLDOWN_QUERY                   VARCHAR2(4000),
                                       CHILDLESS_QUERY                   VARCHAR2(4000),

     CONSTRUCTOR FUNCTION profile_fk_query_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_fk_query_set AS TABLE OF profile_fk_query_type;
/
CREATE OR REPLACE TYPE profile_fd_prop_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                                        FD_ERROR      NUMBER(9),

     CONSTRUCTOR FUNCTION profile_fd_prop_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_fd_prop_set AS TABLE OF profile_fd_prop_type;
/
CREATE OR REPLACE TYPE profile_fd_query_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       COMPLIANT_QUERY                   VARCHAR2(4000),
                                       COMPLIANT_CNT_QUERY               VARCHAR2(4000),
                                       NONCOMPLIANT_QUERY                VARCHAR2(4000),
                                       NONCOMPLIANT_CNT_QUERY            VARCHAR2(4000),
                                       DRILLDOWN_QUERY                   VARCHAR2(4000),

     CONSTRUCTOR FUNCTION profile_fd_query_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_fd_query_set AS TABLE OF profile_fd_query_type;
/
CREATE OR REPLACE TYPE profile_rr_prop_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       LOCAL_MAX_CARDINALITY            VARCHAR2(40),
                                       LOCAL_MIN_CARDINALITY            VARCHAR2(40),
                                       REMOTE_MAX_CARDINALITY           VARCHAR2(40),
                                       REMOTE_MIN_CARDINALITY           VARCHAR2(40),
                                       IND_ERROR                        VARCHAR2(40),

     CONSTRUCTOR FUNCTION profile_rr_prop_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_rr_prop_set AS TABLE OF profile_rr_prop_type;
/
CREATE OR REPLACE TYPE profile_rr_query_type AS OBJECT ( PROPERTYOWNER NUMBER(9),
                                       COMPLIANT_QUERY                   VARCHAR2(4000),
                                       COMPLIANT_CNT_QUERY               VARCHAR2(4000),
                                       NONCOMPLIANT_QUERY                VARCHAR2(4000),
                                       NONCOMPLIANT_CNT_QUERY            VARCHAR2(4000),
                                       DRILLDOWN_QUERY                   VARCHAR2(4000),

     CONSTRUCTOR FUNCTION profile_rr_query_type (id NUMBER) RETURN SELF AS RESULT

);
/
CREATE OR REPLACE TYPE profile_rr_query_set AS TABLE OF profile_rr_query_type;
/


CREATE OR REPLACE PACKAGE DATA_PROFILE_UTL AS

  FUNCTION GET_PROFILE_COLUMN_PROPERTIES RETURN profile_column_prop_set PIPELINED;
  FUNCTION GET_PROFILE_COLUMN_QUERIES    RETURN profile_column_query_set PIPELINED;

  FUNCTION GET_PROFILE_UK_PROPERTIES     RETURN profile_uk_prop_set PIPELINED;
  FUNCTION GET_PROFILE_UK_QUERIES        RETURN profile_uk_query_set PIPELINED;

  FUNCTION GET_PROFILE_FK_PROPERTIES     RETURN profile_fk_prop_set PIPELINED;
  FUNCTION GET_PROFILE_FK_QUERIES        RETURN profile_fk_query_set PIPELINED;

  FUNCTION GET_PROFILE_FD_PROPERTIES     RETURN profile_fd_prop_set PIPELINED;
  FUNCTION GET_PROFILE_FD_QUERIES        RETURN profile_fd_query_set PIPELINED;

  FUNCTION GET_PROFILE_RR_PROPERTIES     RETURN profile_rr_prop_set PIPELINED;
  FUNCTION GET_PROFILE_RR_QUERIES        RETURN profile_rr_query_set PIPELINED;
  
  FUNCTION FORMAT_PATTERN(pattern IN VARCHAR2) RETURN VARCHAR2;

END DATA_PROFILE_UTL;
/