/*
   upgrade script to Bug 5092719 fix.
*/
drop procedure insertValue;
drop function get_mdsignature;
drop procedure upgradematchmergefordebugger;

create procedure insertValue(loadingparent in number, owner in number, 
  name in varchar2, logicalname in varchar2, 
  metadatasignature in varchar2, value in varchar2) is
  typeId NUMBER(9);
begin
  select elementid into typeId from wbscalartype_v
    where name = 'String';
  insert into wbprimitiveproperty_v
  (
    loadingparent,
    deletionparent,
    aggregatedsignature,
    classname,
    completed,
    createdby,
    creationtimestamp,
    customerdeletable,
    customereditable,
    customerrenamable,
    description,
    disabled,
    editable,
    elementid,
    frozen,
    generated,
    imported,
    logicalname,
    metadatasignature,
    name,
    note,
    notm,
    persistent,
    seeded,
    uoid,
    updatedby,
    updatetimestamp,
    version,
    isloadingparent,
    owner,
    datatype,
    position,
    hasname,
    hasposition,
    value
  )
  values 
  (
    loadingparent,
    0,
    null,
    'WBPrimitiveProperty',
    0,
    null,
    sysdate,
    0,
    0,
    0,
    null,
    0,
    0,
    cwmseq.nextval,
    0,
    0,
    0,
    logicalname,
    metadatasignature,
    name,
    null,
    0,
    null,
    1,
    sys_guid(),
    null,
    sysdate,
    null,
    0,
    owner,
    typeId,
    0,
    1,
    0,
    value
  );
  
end insertValue;
/

create function get_mdsignature(sname in varchar2, slogicalname in varchar2) return varchar2 is
 v_metadatasignature VARCHAR2(255);
cursor md_cursor is
   select metadatasignature from wbprimitiveproperty_v where name = sname and logicalname = slogicalname;
begin
   open md_cursor;
   fetch md_cursor into v_metadatasignature;
   close md_cursor;
   return v_metadatasignature;
end get_mdsignature;
/

create procedure upgradematchmergefordebugger is 
 v_loadingparent NUMBER(9);
 v_owner NUMBER(9);
 v_metadatasignature VARCHAR2(255);
 v_value VARCHAR2(4000);
begin
   -- OPERATOR_DELEGATE_3
   select loadingparent into v_loadingparent from wbprimitiveproperty_v where name = 'PUBLIC_NAME' 
   and value = 'MATCHMERGE';
   select owner into v_owner from wbprimitiveproperty_v where name = 'PUBLIC_NAME' and value = 'MATCHMERGE';
   v_metadatasignature := get_mdsignature('OPERATOR_DELEGATE_3', 'OPERATOR_DELEGATE_3');
   v_value := '';
   -- use v_loadingparent, v_owner, NAME, LOGICALNAME, value v_metadatasignature
   insertValue(v_loadingparent, v_owner, 'OPERATOR_DELEGATE_3',  'OPERATOR_DELEGATE_3', v_metadatasignature, v_value); 
  
   -- TYPE
   -- use same v_loadingparent, same 'TYPE' for name and logicalname
   select elementid into v_owner from wbprimitiveproperty_v where name = 'OPERATOR_DELEGATE_3' 
   and loadingparent = v_loadingparent;
   v_metadatasignature := get_mdsignature('TYPE', 'TYPE');
   v_value := 'oracle.wh.service.sdk.mapping.debugger.PlSqlDebugDelegate';
   insertValue(v_loadingparent, v_owner, 'TYPE', 'TYPE', v_metadatasignature, v_value); 
  
   -- DELEGATE
   -- use same v_owner for TYPE
   -- use same v_loadingparent 
   -- use same name and logicalname which is 'DELEGATE'
   v_metadatasignature := get_mdsignature('DELEGATE', 'DELEGATE');
   v_value := 'oracle.wh.service.impl.mapping.component.matchmerge.MatchMergeDebugDelegate';
   insertValue(v_loadingparent, v_owner, 'DELEGATE', 'DELEGATE', v_metadatasignature, v_value);
 
end upgradematchmergefordebugger;
/

begin
  upgradematchmergefordebugger();
end;
/

drop procedure insertValue;
drop function get_mdsignature;
drop procedure upgradematchmergefordebugger;