REM This file updates the Meta Data tables for 6i 4.4

DECLARE

BEGIN



-- M89.002 superceded by M89.009



-- M89.008
delete from cdi_dflt_user_pref_usages
where P_SNAME = 'CGSF'
and P_CGVERSION = '5.0'
and PF_SEQ = 1
and DUP_SNAME in ( 'RPTDNM', 'RPTDTY', 'RPTPTH', 'RPTSUF', 'RPTSVR', 'RPTFMT' );

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTDNM', -- DUP_SNAME
'Report destination name', -- DESCRIPTION
'', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45703, -- DESCRIPTION_MSGID
'N', -- MANDATORY
'', -- CHR_DOMAIN
80,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTDTY', -- DUP_SNAME
'Report destination type', -- DESCRIPTION
'CACHE', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45702, -- DESCRIPTION_MSGID
'Y', -- MANDATORY
'RPTDTY_VALS', -- CHR_DOMAIN
40,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTPTH', -- DUP_SNAME
'File path of the report to run', -- DESCRIPTION
'', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45701, -- DESCRIPTION_MSGID
'N', -- MANDATORY
'', -- CHR_DOMAIN
240,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTSUF', -- DUP_SNAME
'Report file suffix', -- DESCRIPTION
'rdf', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45705, -- DESCRIPTION_MSGID
'Y', -- MANDATORY
'', -- CHR_DOMAIN
10,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTSVR', -- DUP_SNAME
'Name of Reports server to run against', -- DESCRIPTION
'', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45700, -- DESCRIPTION_MSGID
'N', -- MANDATORY
'', -- CHR_DOMAIN
80,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG

insert into cdi_dflt_user_pref_usages
(P_SNAME
,P_CGVERSION
,PF_SEQ
,DUP_SNAME
,DESCRIPTION
,VALUE
,DUP_INTERNAL
,DUP_TYPE
,DESCRIPTION_MSGID
,MANDATORY
,CHR_DOMAIN
,CHR_MAXLEN
,NUM_HIVAL
,NUM_LOVAL
,DOM
,TAB
,CON
,COL
,MOD
,DTU
,CONU
,IGRP
,DCU
,ACI
,SUB
,LOV
,NLS_LANG)
values
(
'CGSF', -- P_SNAME
'5.0', -- P_CGVERSION
1, -- PF_SEQ
'RPTFMT', -- DUP_SNAME
'Report destination format', -- DESCRIPTION
'HTML', -- VALUE
'N', -- DUP_INTERNAL
'WEB DEPLOYMENT', -- DUP_TYPE
45704, -- DESCRIPTION_MSGID
'Y', -- MANDATORY
'RPTFMT_VALS', -- CHR_DOMAIN
40,  -- CHR_MAXLEN
NULL,  -- NUM_HIVAL
NULL,  -- NUM_LOVAL
'N', -- DOM
'N', -- TAB
'N', -- CON
'N', -- COL
'Y', -- MOD
'N', -- DTU
'N', -- CONU
'N', -- IGRP
'N', -- DCU
'N', -- ACI
'N', -- SUB
'N', -- LOV
'US'); -- NLS_LANG



-- M89.009 supercedes M89.002
delete from sdd_global_name_domains
where irid in (2607070979528228813084943875303672371
              ,2607070980147198832727634012753234483
  	      ,2607070980766168852370324150202796595
);

insert into sdd_global_name_domains (irid, ivid, name, system_flag) values (
  2607070979528228813084943875303672371
, 2607070979837713822906288944028453427
, 'PROFILE'
, 'Y');

insert into sdd_global_name_domains (irid, ivid, name, system_flag) values (
  2607070980147198832727634012753234483
, 2607070980456683842548979081478015539
, 'ROLLBACK_SEGMENT'
, 'Y');

insert into sdd_global_name_domains (irid, ivid, name, system_flag) values (
  2607070980766168852370324150202796595
, 2607070981075653862191669218927577651
, 'INDEX_PARTITION'
, 'Y');

insert into sdd_nls_global_name_domains (irid, ivid, nls_name, nls_plural_name, nls_lang, gnd_irid)
values (
 2607070981385138872013014287652358707
,2607070981694623881834359356377139763
,'Profile'
,'Profiles'
,'US'
,2607070979528228813084943875303672371
);

insert into sdd_nls_global_name_domains (irid, ivid, nls_name, nls_plural_name, nls_lang, gnd_irid)
values (
 2607070982004108891655704425101920819
,2607070982313593901477049493826701875
,'Rollback Segment'
,'Rollback Segments'
,'US'
,2607070980147198832727634012753234483
);

insert into sdd_nls_global_name_domains (irid, ivid, nls_name, nls_plural_name, nls_lang, gnd_irid)
values (
 2607070982623078911298394562551482931
,2607070982932563921119739631276263987
,'Index Partition'
,'Index Partitions'
,'US'
,2607070980766168852370324150202796595
);

begin
  declare 
    fmp_irid number;
    fg_irid  number;

  begin
    -- rename user defined data if any exists
    update sdd_global_name_domains
    set name = name ||'_UPGRADE'
    where name in ('FORMS_GRAPHICS', 'FORMS_MODULE_PARAMETER')
    and system_flag = 'N';

    begin
      insert into sdd_global_name_domains (irid, ivid, name, system_flag) values (
        2607070984479988970226464974900169267
      , 2607070984789473980047810043624950323
      , 'FORMS_MODULE_PARAMETER'
      , 'Y');

      insert into sdd_nls_global_name_domains (irid, ivid, nls_name, nls_plural_name, nls_lang, gnd_irid)
      values (
       2607070985717929009511845249799293491
      ,2607070986027414019333190318524074547
      ,'Forms Module Parameter'
      ,'Forms Module Parameters'
      ,'US'
      ,2607070984479988970226464974900169267
      );

      -- select irid of users data
      select irid
      into   fmp_irid
      from   sdd_global_name_domains
      where  name = 'FORMS_MODULE_PARAMETER_UPGRADE';

      -- remap domain_irid for users data to system data
      update sdd_global_names
      set    domain_irid = 2607070984479988970226464974900169267
      where  domain_irid = fmp_irid;

      -- remap gnd_irid for users nls data to system data
      update sdd_nls_global_name_domains
      set    gnd_irid = 2607070984479988970226464974900169267
      where  gnd_irid = fmp_irid
      and    nls_lang <> 'US';

      -- delete users data from global names which will cascade nls data where nls_lang = 'US'
      delete sdd_global_name_domains
      where  irid = fmp_irid;

    exception
      when DUP_VAL_ON_INDEX then
        -- will occur if patch is being rerun so no further processing required
        NULL;
      when NO_DATA_FOUND then
        -- will occur if there is no user data defined for FORMS_MODULE_PARAMETER
        NULL;
    end;

    begin
      insert into sdd_global_name_domains (irid, ivid, name, system_flag) values (
        2607070985098958989869155112349731379
      , 2607070985408443999690500181074512435
      , 'FORMS_GRAPHICS'
      , 'Y');

      insert into sdd_nls_global_name_domains (irid, ivid, nls_name, nls_plural_name, nls_lang, gnd_irid)
      values (
       2607070986336899029154535387248855603
      ,2607070986646384038975880455973636659
      ,'Forms Graphics'
      ,'Forms Graphics'
      ,'US'
      ,2607070985098958989869155112349731379
      );

      select irid
      into   fg_irid
      from   sdd_global_name_domains
      where  name = 'FORMS_GRAPHICS_UPGRADE';

      -- remap domain_irid for users data to system data
      update sdd_global_names   gn
      set    domain_irid = 2607070985098958989869155112349731379
      where  domain_irid = fg_irid;

      -- remap gnd_irid for users nls data to system data
      update sdd_nls_global_name_domains
      set    gnd_irid = 2607070985098958989869155112349731379
      where  gnd_irid = fg_irid
      and    nls_lang <> 'US';

      -- delete users data from global names which will cascade nls data where nls_lang = 'US'
      delete sdd_global_name_domains
      where  irid = fg_irid;

    exception
      when DUP_VAL_ON_INDEX then
        -- will occur if patch is being rerun so no further processing required
        NULL;
      when NO_DATA_FOUND then
        NULL;
    end;
  end;
end;



  -- Register 4_4 Upgrade in the RM$PATCHES RM$PATCHES table
  rmpatch.install('CI04012.4_4_UPGRADE');

  rmpatch.installed_ok;



  commit;



EXCEPTION

  WHEN OTHERS THEN
    dbms_output.put_line('* Exception Raised  ');
    dbms_output.put_line('...ERROR: '||substrb(sqlerrm,1,240));
    rollback work;
    RAISE;

END;
/
