REM globtemp.sql
REM (c) GFC 2007

spool globtemp
CLEAR SCREEN
SET ECHO ON
ROLLBACK
/


DECLARE
 l_projname VARCHAR2(10) := 'GLOBTEMP';
BEGIN
 UPDATE psversion
 SET version = version + 1
 WHERE objecttypename IN('SYS','RDM','PJM');

 UPDATE pslock
 SET version = version + 1
 WHERE objecttypename IN('SYS','RDM','PJM');

 FOR r IN (
  SELECT r.recname
  FROM   psrecdefn r
  WHERE  r.rectype = 7
  AND NOT EXISTS( --omit tables where restart not disabled on referencing AE
   SELECT 'x' 
   FROM	  psaeappltemptbl t
   ,	  psaeappldefn a
   WHERE  t.recname = r.recname
   AND    a.ae_applid = t.ae_applid
   AND    a.ae_disable_restart = 'N' --restart not disabled
   AND    ROWNUM = 1)
 ) LOOP

  --insert record DDL overrides
  INSERT INTO psrecddlparm
  (recname, platformid, sizingset, parmname, parmvalue)
  SELECT r.recname, 2, 0, d.parmname, d.parmvalue
  FROM   psddldefparms d
  WHERE  d.statement_type = 1 /*create table*/
  AND    d.platformid = 2
  AND    d.parmname IN('GTSPCOM1','GTSPCOM2','GLOBTEMP')
  AND NOT EXISTS(
   SELECT 'x'
   FROM   psrecddlparm p
   WHERE  p.recname = r.recname
   AND    p.parmname = d.parmname
   AND    p.platformid = 2);

  --insert record into project
  INSERT INTO psprojectitem 
  (PROJECTNAME, OBJECTTYPE
  ,OBJECTID1,OBJECTVALUE1,OBJECTID2,OBJECTVALUE2
  ,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
  ,NODETYPE,SOURCESTATUS,TARGETSTATUS,UPGRADEACTION,TAKEACTION,COPYDONE)
  SELECT l_projname, 0
  ,      1, r.recname, 0, ' ', 0, ' ', 0, ' '
  ,      0, 0, 0, 0, 1, 0
  FROM dual
  WHERE NOT EXISTS(
   SELECT 'x'
   FROM   psprojectitem
   WHERE  projectname = l_projname
   AND    objecttype = 0 /*table*/
   AND    objectid1 = 1
   AND    objectvalue1 = r.recname);

  --
  UPDATE psrecddlparm
  SET    parmvalue = 'GLOBAL TEMPORARY'
  WHERE  parmvalue != 'GLOBAL TEMPORARY'
  AND    recname = r.recname
  AND    parmname = 'GLOBTEMP';

  UPDATE psrecddlparm
  SET    parmvalue = 'ON COMMIT PRESERVE ROWS /*'
  WHERE  parmvalue != 'ON COMMIT PRESERVE ROWS /*'
  AND    recname = r.recname
  AND    parmname = 'GTSPCOM1';

  UPDATE psrecddlparm
  SET    parmvalue = '*/'
  WHERE  parmvalue != '*/'
  AND    recname = r.recname
  AND    parmname = 'GTSPCOM2';

  --insert index DDL overrides
  FOR i IN (
   SELECT '_' indexid
   FROM   psrecfielddb
   WHERE  recname = r.recname
   AND    (   BITAND(useedit,1) = 1 --unique key
           OR BITAND(useedit,2) = 2) --duplicate key
   AND    ROWNUM = 1
   UNION
   SELECT LTRIM(TO_CHAR(rownum-1,'0')) --alternate searches
   FROM   psrecfielddb
   WHERE  recname = r.recname
   AND    BITAND(useedit,16) = 16
   UNION
   SELECT indexid
   FROM   psindexdefn
   WHERE  recname = r.recname
   AND    platform_ora = 1
   ) LOOP

   --insert index overrides
   INSERT INTO psidxddlparm
   (recname, indexid, platformid, sizingset, parmname, parmvalue)
   SELECT r.recname, i.indexid, 2, 0, d.parmname, d.parmvalue
   FROM   psddldefparms d
   WHERE  d.statement_type = 2 /*create index*/
   AND    d.platformid = 2
   AND    d.parmname IN('GTSPCOM1','GTSPCOM2')
   AND NOT EXISTS(
    SELECT 'x'
    FROM   psidxddlparm p
    WHERE  p.recname = r.recname
    AND    p.indexid = i.indexid
    AND    p.platformid = 2);
  END LOOP;

  --
  UPDATE psidxddlparm
  SET    parmvalue = '/*'
  WHERE  parmvalue != '/*'
  AND    recname = r.recname
  AND    parmname = 'GTSPCOM1';

  UPDATE psidxddlparm
  SET    parmvalue = '*/'
  WHERE  parmvalue != '*/'
  AND    recname = r.recname
  AND    parmname = 'GTSPCOM2';

  --insert index into project
  INSERT INTO psprojectitem 
  (PROJECTNAME, OBJECTTYPE
  ,OBJECTID1,OBJECTVALUE1,OBJECTID2,OBJECTVALUE2
  ,OBJECTID3,OBJECTVALUE3,OBJECTID4,OBJECTVALUE4
  ,NODETYPE,SOURCESTATUS,TARGETSTATUS,UPGRADEACTION,TAKEACTION,COPYDONE)
  SELECT l_projname, 1
  ,      1, r.recname, 24, i.indexid, 0, ' ', 0, ' '
  ,      0, 0, 0, 0, 1, 0
  FROM psindexdefn i
  WHERE i.recname = r.recname
  AND   i.platform_ora = 1
  AND   i.indexid BETWEEN 'A' AND 'Z'
  AND NOT EXISTS(
   SELECT 'x'
   FROM   psprojectitem
   WHERE  projectname = l_projname
   AND    objecttype = 1 /*index*/
   AND    objectid1 = 1
   AND    objectvalue1 = r.recname
   AND    objectid2 = 24
   AND    objectvalue2 = i.indexid
   );

  --update record version numbers
  UPDATE psrecdefn
  SET    version = (SELECT version FROM psversion WHERE objecttypename = 'RDM')
  ,      ddlcount = (SELECT COUNT(DISTINCT platformid) FROM psrecddlparm WHERE recname = r.recname)
  ,      lastupddttm = SYSDATE
  ,      lastupdoprid = 'GFC'
  WHERE  recname = r.recname;

  UPDATE psindexdefn i
  SET    ddlcount = (SELECT COUNT(DISTINCT platformid) FROM psidxddlparm i2 
                     WHERE i2.recname = r.recname AND i2.indexid = i.indexid)
  WHERE  recname = r.recname;

 END LOOP;
 UPDATE psprojectdefn
 SET    version = (SELECT version FROM psversion WHERE objecttypename = 'RDM')
 ,      lastupddttm = SYSDATE
 ,      lastupdoprid = 'GFC'
 WHERE  projectname = l_projname;
END;
/

pause

column recname format a18
column parmname format a10
column parmvalue format a40
SELECT recname, parmname, parmvalue
FROM   psrecddlparm
WHERE  parmname IN('GTSPCOM1','GTSPCOM2','GLOBTEMP')
ORDER BY recname
;
SELECT recname, indexid, parmname, parmvalue
FROM   psidxddlparm
WHERE  parmname IN('GTSPCOM1','GTSPCOM2','GLOBTEMP')
ORDER BY recname, indexid
;
column projectname format a20
column objectvalue1 format a18
column objectvalue2 format a10
SELECT projectname, objecttype, objectvalue1, objectvalue2
FROM psprojectitem
WHERE projectname = 'GLOBTEMP'
ORDER BY objectvalue1, objectvalue2
;

spool off