18:47:03 SQL> --drop sequence  seq_md_citation;
18:47:03 SQL> 
18:47:03 SQL> create sequence seq_md_citation
18:47:03   2  start with 1 increment by 1 nomaxvalue nocycle cache 10;

Sequence created.

18:47:03 SQL> 
18:47:03 SQL> /*******************************************************
18:47:03 DOC>Create MetaData Bibliographic Citation table
18:47:03 DOC>**********************************************************/
18:47:03 SQL> --drop table md_citation;
18:47:03 SQL> 
18:47:03 SQL> create table md_citation
18:47:03   2  ( pk_isn number(12,0),
18:47:03   3  	organization_id VARCHAR2(256 char) not null,
18:47:03   4  	citation_is_number number(12,0) not null,
18:47:03   5  	organization_is_number number(12,0),
18:47:03   6  	citation_id VARCHAR2(256 char),
18:47:03   7  	title VARCHAR2(1999 char) not null,
18:47:03   8  	author VARCHAR2(256 char) not null,
18:47:03   9  	pblshr_org_name VARCHAR2(256 char) not null,
18:47:03  10  	publishing_year VARCHAR2(256 char) not null,
18:47:03  11  	vol_and_page VARCHAR2(256 char) not null,
18:47:03  12  	reference_id VARCHAR2(256 char),
18:47:03  13  	comments VARCHAR2(256 char));

Table created.

18:47:03 SQL> 
18:47:03 SQL> alter table md_citation
18:47:03   2  add constraint pk_citation primary key (pk_isn)
18:47:03   3  using index
18:47:03   4  tablespace  STORETINDX
18:47:03   5  storage(initial 300k next 300k)
18:47:03   6  nologging;

Table altered.

18:47:03 SQL> 
18:47:03 SQL> create index icit_org_id on md_citation(organization_id)
18:47:03   2  tablespace STORETINDX
18:47:03   3  storage(initial 512k next 512k)
18:47:03   4  nologging;

Index created.

18:47:03 SQL> 
18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> 
18:47:03 SQL> /*  moved to bottom
18:47:03 DOC>alter table md_citation
18:47:03 DOC>add( fk_org number(12,0));
18:47:03 DOC>
18:47:03 DOC>update md_citation ci
18:47:03 DOC>set fk_org =(select pk_isn from di_org o
18:47:03 DOC>		   where o.organization_is_number=ci.organization_is_number)
18:47:03 DOC>where ci.organization_is_number is not null;
18:47:03 DOC>commit;
18:47:03 DOC>
18:47:03 DOC>create index icitfk on md_citation(fk_org)
18:47:03 DOC>tablespace STORETINDX
18:47:03 DOC>storage (initial 512k next 512k)
18:47:03 DOC>nologging;
18:47:03 DOC>
18:47:03 DOC>commit;
18:47:03 DOC>
18:47:03 DOC>
18:47:03 DOC>alter table md_citation
18:47:03 DOC>add constraint fk_md_cit foreign key (fk_org) references di_org(pk_isn);
18:47:03 DOC>
18:47:03 DOC>commit;
18:47:03 DOC>*/
18:47:03 SQL> 
18:47:03 SQL> /********************************************************
18:47:03 DOC>Populate MetaData Bibliographic Citation table with data.
18:47:03 DOC>*********************************************************/
18:47:03 SQL> 
18:47:03 SQL> set serveroutput on
18:47:03 SQL> declare
18:47:03   2  cursor c_citation is
18:47:03   3  select TSRCITN_ORG_ID,TSRCITN_IS_NUMBER,TSMORGAN_IS_NUMBER,IDENTIFICATION_CD,TITLE_NAME,AUTHOR_NAME,JRNL_OR_PBLSHR_NM,PUBLICATION_YEAR,VOL_AND_PG_NUM,ORG_REF_ID_NUM,COMMENT_TEXT
18:47:03   4  from storet1.tsrcitn;
18:47:03   5  
18:47:03   6  begin
18:47:03   7  	for c_var in c_citation loop
18:47:03   8  	  insert into md_citation
18:47:03   9  	  (pk_isn,organization_id,citation_is_number,organization_is_number,citation_id,title,author,pblshr_org_name,publishing_year,vol_and_page,reference_id,comments)
18:47:03  10  	  values
18:47:03  11  	  (seq_md_citation.nextval,c_var.tsrcitn_org_id,c_var.tsrcitn_is_number,c_var.tsmorgan_is_number,c_var.identification_cd,c_var.title_name,c_var.author_name,c_var.jrnl_or_pblshr_nm,c_var.publication_year,c_var.vol_and_pg_num,c_var.org_ref_id_num,c_var.comment_text);
18:47:03  12  	end loop;
18:47:03  13  end;
18:47:03  14  /

PL/SQL procedure successfully completed.

18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> alter table md_citation
18:47:03   2  add( fk_org number(12,0));

Table altered.

18:47:03 SQL> 
18:47:03 SQL> update md_citation ci
18:47:03   2  set fk_org =(select pk_isn from di_org o
18:47:03   3  		    where o.organization_is_number=ci.organization_is_number)
18:47:03   4  where ci.organization_is_number is not null;

1675 rows updated.

18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> 
18:47:03 SQL> create index icitfk on md_citation(fk_org)
18:47:03   2  tablespace STORETINDX
18:47:03   3  storage (initial 512k next 512k)
18:47:03   4  nologging;

Index created.

18:47:03 SQL> 
18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> -- moved the constraint down after creating the fk_org.  test defect fix
18:47:03 SQL> alter table md_citation
18:47:03   2  add constraint fk_md_cit foreign key (fk_org) references di_org(pk_isn);

Table altered.

18:47:03 SQL> 
18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> 
18:47:03 SQL> alter table md_citation add SOURCE_SYSTEM VARCHAR2(256 CHAR);

Table altered.

18:47:03 SQL> alter table md_citation add SOURCE_UID NUMBER(20);

Table altered.

18:47:03 SQL> commit;

Commit complete.

18:47:03 SQL> 
18:47:03 SQL> alter table md_citation modify citation_is_number number(12,0) null;

Table altered.

18:47:03 SQL> 
18:47:03 SQL> 
18:47:03 SQL> spool off;
