REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
REM RMINC_fnd_concurrent_requests.sql
REM
REM This program creates the table RMINC_fnd_concurrent_requests
REM Before running this program, TURN OFF THE CONCURRENT MANAGER
REM You'll want to create this table, then run
REM RMINC_fnd_concurrent_requests.trg to create the triggers that
REM will monitor fnd_concurrent_requests, and then turn the
REM concurrent manager back on.  
REM
REM connect as applsys
REM NOTE:  you may want to store this table in its own tablespace,
REM as it will get very large.  We use RMINCD for the table and RMINCX
REM for the index. This table is updated whenever a Purge Concurrent
REM Request program is run (generally nightly) and is queried
REM whenever someone runs the rptsump.sql program
REM
REM 
connect applsys/password

REM Create tablespaces
connect system/password
CREATE TABLESPACE RMINCD
    DATAFILE '/ora_apps/dtemp2/ORAUPI/RMINCD01.dbf' SIZE 25M AUTOEXTEND ON NEXT 25M MAXSIZE 250M,
             '/ora_apps/dtemp2/ORAUPI/RMINCD02.dbf' SIZE 4M AUTOEXTEND ON NEXT 25M MAXSIZE 250M
    LOGGING
    DEFAULT STORAGE(INITIAL 40K
                    NEXT 40K
                    MINEXTENTS 1
                    MAXEXTENTS 249
                    PCTINCREASE 0)
    ONLINE
    PERMANENT
/

CREATE TABLESPACE RMINCX
    DATAFILE '/ora_apps/dtemp1/ORAUPI/RMINCX01.dbf' SIZE 25M AUTOEXTEND ON NEXT 25M MAXSIZE 250M,
             '/ora_apps/dtemp1/ORAUPI/RMINCX02.dbf' SIZE 4M AUTOEXTEND ON NEXT 25M MAXSIZE 250M
    LOGGING
    DEFAULT STORAGE(INITIAL 40K
                    NEXT 40K
                    MINEXTENTS 1
                    MAXEXTENTS 249
                    PCTINCREASE 0)
    ONLINE
    PERMANENT
/
alter user applsys quota unlimited on RMINCD;
alter user applsys quota unlimited on RMINCX;
connect applsys/apps
REM Create the historical table
lock table apps.fnd_concurrent_requests in exclusive mode;
create table applsys.RMINC_fnd_concurrent_requests
pctfree 10 pctused 80 
tablespace RMINCD 
storage (initial 20M next 4096000 pctincrease 0) 
as select 
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_DATE,
REQUESTED_BY,
PHASE_CODE,
STATUS_CODE,
PRIORITY_REQUEST_ID,
PRIORITY,
REQUESTED_START_DATE,
HOLD_FLAG,
ENFORCE_SERIALITY_FLAG,
SINGLE_THREAD_FLAG,
HAS_SUB_REQUEST,
IS_SUB_REQUEST,
IMPLICIT_CODE,
UPDATE_PROTECTED,
QUEUE_METHOD_CODE,
ARGUMENT_INPUT_METHOD_CODE,
ORACLE_ID,
PROGRAM_APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
RESPONSIBILITY_APPLICATION_ID,
RESPONSIBILITY_ID,
NUMBER_OF_ARGUMENTS,
NUMBER_OF_COPIES,
SAVE_OUTPUT_FLAG,
PRINTER,
PRINT_STYLE,
PRINT_GROUP,
REQUEST_CLASS_APPLICATION_ID,
CONCURRENT_REQUEST_CLASS_ID,
PARENT_REQUEST_ID,
CONC_LOGIN_ID,
LANGUAGE_ID,
DESCRIPTION,
REQ_INFORMATION,
RESUBMIT_INTERVAL,
RESUBMIT_INTERVAL_UNIT_CODE,
RESUBMIT_INTERVAL_TYPE_CODE,
RESUBMIT_TIME,
RESUBMIT_END_DATE,
RESUBMITTED,
CONTROLLING_MANAGER,
ACTUAL_START_DATE,
ACTUAL_COMPLETION_DATE,
COMPLETION_TEXT,
OUTCOME_PRODUCT,
OUTCOME_CODE,
CPU_SECONDS,
LOGICAL_IOS,
PHYSICAL_IOS,
LOGFILE_NAME,
LOGFILE_NODE_NAME,
OUTFILE_NAME,
OUTFILE_NODE_NAME,
ARGUMENT_TEXT,
NLS_LANGUAGE,
NLS_TERRITORY
from apps.fnd_concurrent_requests
where rownum < 1;

drop index applsys.RMINC_fnd_concurrent_req_u1;
create unique index applsys.RMINC_fnd_concurrent_req_U1 on
RMINC_fnd_concurrent_requests(request_id)
tablespace RMINCX    
storage (initial 20M next 10M pctincrease 0)
pctfree 5;

drop index applsys.RMINC_fnd_concurrent_req_n1;
create index applsys.RMINC_fnd_concurrent_req_n1 on
applsys.RMINC_fnd_concurrent_requests(description)
tablespace RMINCX 
storage (initial 20M next 10M pctincrease 0)
pctfree 5;

REM This index makes RPTHIS.sql (Concurrent Request History) run faster.
drop index applsys.RMINC_fnd_concurrent_req_n2;
create index applsys.RMINC_fnd_concurrent_req_n2 on
RMINC_fnd_concurrent_requests(concurrent_program_id,program_application_id,
  requested_by,phase_code,actual_start_date) 
tablespace RMINCX 
storage (initial 20M next 10M pctincrease 0)
pctfree 5;

REM This index is for deleting records periodically from
REM RMINC_fnd_concurrent_requests.  I plan to save only the last two years data
REM (as if that weren't enough!)
drop index applsys.RMINC_fnd_concurrent_req_n3;
create index applsys.RMINC_fnd_concurrent_req_n3 on
RMINC_fnd_concurrent_requests(actual_completion_date)
tablespace RMINCX
storage (initial 20M next 10M pctincrease 0)
pctfree 5;