create or replace function paris_5905503_parent(elemid IN NUMBER, refName OUT VARCHAR2, cname OUT VARCHAR2) RETURN NUMBER AS
    pid NUMBER(9);
BEGIN
    refName := null;
    select classname, name into cname, refName from CMPElement_v where elementid=elemid;
    if (cname = 'CMPIndex') then
        select owningrelation, name into pid, refName from CMPIndex_v where elementid = elemid;
    elsif (cname = 'CMPPartition') then
        select partitionkey, name into pid, refName from CMPPartition_v where elementid = elemid;
    elsif (cname = 'CMPPartitionKey') then
        select nvl(owningrelation, owningindex), name into pid, refName 
        from CMPPartitionKey_v where elementid = elemid;
    else 
        pid := null;
    end if;
    return pid;
--EXCEPTION 
--    when others then
--        return null;
END;
 
/

create or replace function paris_5905503_find_ref(fco IN NUMBER, refid IN NUMBER, phyid IN NUMBER) RETURN NUMBER AS
    type idTable is table of CMPElement_v.elementid%TYPE index by BINARY_INTEGER;
    j BINARY_INTEGER := 0;
    idList idTable;
    owner NUMBER(9);
BEGIN
    for c in (select elementid from CMPReferencePropertyValue_v 
              where firstClassObject=fco and referencedElement=refid) loop
        BEGIN 
            select propertyowner into owner from (select propertyowner from CMPPropertyValue_v 
            start with elementid=c.elementid connect by elementid = prior propertyowner) 
            where propertyowner=phyid;
            idList(j) := c.elementid;
            j := j+1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                null;
        END;
    end loop;
    forall idx in 1..j-1 
        delete CMPReferencePropertyValue_v where elementid = idList(idx);
    if (j>0) THEN
        return idList(0);
    else 
        return 0;
    end if;
END;

/

create or replace function paris_5905503_create_ref(cby IN VARCHAR2, uby IN VARCHAR2, 
                  fco IN NUMBER, refid IN NUMBER, phyid IN NUMBER, ctime IN DATE, utime IN DATE) 
          RETURN NUMBER AS
    mUOID VARCHAR2(255);
    elemid NUMBER(9);
    sig VARCHAR(255);
    prefid number(9);
    parentid NUMBER(9);
    refName VARCHAR2(255);
    propowner NUMBER(9);
    propid NUMBER(9);
    refcname VARCHAR2(255);
BEGIN
    parentid := paris_5905503_parent(refid, refName, refcname);
    if (parentid = fco) then
        propowner := phyid;
    else
        propowner := paris_5905503_create_ref(cby, uby, fco, parentid, phyid, ctime, utime);
    end if;
    if (propowner is null) then
        return null;
    end if;
    propid := paris_5905503_find_ref(fco, refid, phyid);
    if (propid <> 0) then
        update CMPReferencePropertyValue_v set propertyOwner = propowner 
            where elementid = propid;
        return propid;
    else 
            --delete CMPReferencePropertyValue_v where firstClassObject=fco and referencedElement=refid;
            if (propowner = phyid) then 
                refcname := 'DEFAULT';
            end if;
            mUOID := sys_guid();    
            sig := substr(mUOID, 1, 32);
            select cwmseq.nextval into elemid from dual;
            insert into CMPReferencePropertyValue_v (AggregatedSignature, ClassName, 
                     Completed, CreatedBy, CreationTimestamp, 
                     CustomerDeletable, CustomerEditable, CustomerRenamable, 
                     Description, Disabled, Editable, ElementId, Frozen, Generated,
                     Imported, LogicalName, MetadataSignature, Name, Note, Notm, Persistent,
                     Seeded, StrongTypeName, UOID, UpdatedBy, UpdateTimestamp, Position,
                     PropertyOwner, FirstClassObject, ReferencedElement)
            Values  (sysdate, 'CMPReferencePropertyValue', 
                     1, cby, ctime, 
                     1, 1, 0, 
                     'Config property owner for ' || refName, 0, 1, elemid , 0, 0, 
                     0, refcname, sig, refName, null, 0, 1,
                     0, null, mUOID, uby, utime, 0,
                     propowner, fco, refid);
            return elemid;
    end if;
--EXCEPTION
--    WHEN OTHERS THEN
--        return null;
END;

/

create or replace procedure paris_5905503_update_ref(elemid IN NUMBER, propowner IN NUMBER, 
                  propid IN NUMBER, fco IN NUMBER, ctime IN DATE, utime IN DATE) AS
    parentid NUMBER(9);
    prefid NUMBER(9);
    realowner NUMBER(9);
    pcname VARCHAR2(255);
    cby VARCHAR2(40);
    uby VARCHAR2(40);
    refName VARCHAR(255);
    keepId NUMBER(9);
    refcname VARCHAR2(255);
BEGIN
    parentid := paris_5905503_parent(elemid, refName, refcname);
    if (parentid is null or parentid = fco) then
        return;
    end if;
    select classname, createdby, updatedby into pcname, cby, uby 
    from CMPElement_v where elementid = propowner;
    if (pcname = 'CMPPhysicalObject') then
        keepId := paris_5905503_find_ref(fco, elemid, propowner);
        if (keepId<>propid) then
            return;
        end if;
        realowner := paris_5905503_create_ref(cby, uby, fco, parentid, propowner, ctime, utime);
        update CMPPropertyValue_v set propertyOwner = realOwner where elementid = propid;
    else  
        return;
    end if;
--EXCEPTION
--    WHEN OTHERS THEN
--        return;
END;

/

create or replace procedure paris_5905503_patch AS
BEGIN

    for c in (select r.referencedelement, r.createdby, r.updatedby, r.firstclassobject, 
                     r.creationtimestamp, r.updatetimestamp, r.elementid, p.partitionkey, 
                     r.propertyowner 
              from cmppartition_v p, cmpreferencepropertyvalue_v r
              where p.elementid=r.referencedelement) loop
        paris_5905503_update_ref(c.referencedelement, c.propertyowner, c.elementid, c.firstclassobject, c.creationtimestamp, c.updatetimestamp);
    end loop;

END;

/

BEGIN
    paris_5905503_patch();
END;

/