Collections - SQL to create new from old

For Office of Research at Griffith University - Code to rollover an existing Collection to a new Collection keeping the existing one's Questions and Responses.

-- GUR_COLL_RES
insert into sysadm.ps_gur_coll_res
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, 'NEW'
, ' '
, 0
, ''
from sysadm.ps_gur_coll_res
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;


-- GUR_COLL_RESDTL
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG
FROM sysadm.PS_GUR_COLL_RESDTL
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_RESDTL (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.GUR_FLD_EVIDENCE,
PS_REC.GUR_FLD_RETAINED,
PS_REC.GUR_FLD_EVI_RET,
PS_REC.GUR_RESPONSE_LNG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;


-- GUR_COLL_FILE
/
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG
FROM sysadm.PS_GUR_COLL_FILE
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_FILE (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.SEQNO,
PS_REC.ATTACHSYSFILENAME,
PS_REC.ATTACHUSERFILE,
PS_REC.GUR_PRE_POST_VERS,
PS_REC.GUR_EMPLID,
PS_REC.DATETIME_STAMP,
PS_REC.GUR_EP_STATUS,
PS_REC.GUR_EP_URL,
PS_REC.GUR_ADD_EPRINTS,
PS_REC.GUR_DEL_EPRINTS,
PS_REC.GUR_ALLOW_PUBLSH,
PS_REC.GUR_CONF_EMAIL,
PS_REC.GUR_AUTHORISED,
PS_REC.GUR_VERIFIED,
PS_REC.GUR_DECLINED,
PS_REC.GUR_REFORMATTED,
PS_REC.GUR_ENTBY_EP_STAFF,
PS_REC.GUR_PROCESS_FLAG,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;
/


-- GUR_COLL_RFCD
insert into sysadm.ps_gur_coll_rfcd
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, GUR_SECTION_NR
, GUR_QUESTION_NBR
, GUR_PBLCTN_REF_NR
, EFFDT
, GUR_FLD_RSRCH
, GUR_PERCENT
from sysadm.ps_gur_coll_rfcd
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;

0 comments: