{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["notes","1. Oracle table","2. Extensible section","3. Extensible fields","lookups"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":2,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"notes","index":0,"n_rows":25,"n_cols":4,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["This is a tool that will hopefully make it easier to create extensible table scripts.",null,"2014-09-11","[ks] I didn’t test the scripts yet because I don’t know what object_control_id and label_control_id in table DYNAMIC_ATTRIBUTE are for or how to get the values. The former is a required field. Do you know?"]
[null,null,"2014-09-12","[dr] Both object_control_id and label_control_id are obsolete fields, but object_control_id is required. This can be changed in the future, but it should be left as is for the time being because I'm sure there will be multiple changes to the \"DYNAMIC_\" tables when an extensibility interface is implemented in B5.  For now, you can leave label_control_id null and  put a fake number in object_control_id. You do not have to add the fake number to the SYSTEM_CONTROL table (from whence the FK object_control_id is supposed to come) because there is no FK constraint. The highest real control_id number is in the 600000's, so I have used numbers starting at 700000 when adding new extensible tables for programs."]
["Instructions",null,"2014-09-12","[ks] Re. object_control_id, there’s no unique constraint on it, so I don’t see why it can’t always be 0 (or some other number).\n\nAnother model change I think is needed is that dynamic_object.dynamic_name should have a unique constraint. That would give us a way to identify a dynamic object without relying on ID, e.g. before the ID has been created, as I’ve done in extensible table too, fields tab, column C. I think it would be safest to make that change now, though I suppose that requires checking for duplicates in the MP dbs. And if we’re going to do that, would it really hurt to delete object_control_id and label_control_id now? What do you think?\n\nThere are actually 11 dynamic objects sharing 4 names in central db, so at a minimum I’d like to resolve that. I don’t think dynamic_name is actually used anywhere?\n\nselect * from dynamic_object \nwhere dynamic_name in \n(select dynamic_name from dynamic_object\ngroup by dynamic_name having count(*) > 1)\norder by dynamic_name\n\n"]
["Fill in the information in highlighted cells on the next 3 tabs. Scripts are automatically generated (red font).",null,"2015-05-29","[ks] correction to previous: dynamic_object.dynamic_name is actually what is displayed as the section header; tab_caption has no effect. Therefore it is unlikely we'll be able to make this field unique. But we could make dynamic_object.dynamic_name + dynamic_form_id unique."]
["Be sure to read the in-cell comments on the \"3. Extensible fields\" tab."]
["Paste the generated scripts into TOAD or SQL+. (Haven't tested with SQL+.) ",null,"2014-09-12","[ks] it won’t let me getnextseq for HDMS_TABLE"]
["Test and run the scripts.",null,"2014-11-12","[dr] I just found out the answer to the HDMS_TABLE getnextseq error yesterday while working on a support ticket.  For reasons I can't understand, someone programmed getnextseq to throw that error, but it's super easy to fix, and there is no reason I can think of for HDMS_TABLE_ID not to be incremented in the same way that other table IDs. So I just changed it and now the insert will work.  "]
[null,null,"2015-05-29","[ks] not working on B5 Test Central but I think for a different reason--seems to be returning wrong id."]
["The 3 tabs do the following:",null,null,"insert into HDMS_TABLE ("]
["1. Create the Oracle table and add it to HDMS_TABLE.",null,null,"hdms_table_id, table_name, table_type, audit_comment, next_seq_keyword, core"]
["2. Add the extensible section to a data page by adding a record to the DYNAMIC_OBJECT table.",null,null,") VALUES (getnextseq('HDMS_TABLE'), "]
["3. Add the extensible fields to the extensible section by adding records to the DYNAMIC_ATTRIBUTE table.",null,null,"'COMM_ELEMENT_SUBTYPE', 'B', Null, Null, 0"]
[null,null,null,")"]
[null,null,null,"Error at line 2"]
[null,null,null,"ORA-00001: unique constraint (HDMS.HDMS_TABLE_PK) violated"]
[]
[]
[]
[]
[]
[]
["History"]
["2014-09-12 created "]
["2015-05-27 added concat fx to file, added notes"]
["5/29/2015 added more automation"]

{"sheet":"1. Oracle table","index":1,"n_rows":76,"n_cols":8,"rows_kept":76,"cut":false,"hidden":false,"merged":[]}
[null,null,"Do not rely entirely on this tool to create your table script. Main point here is to avoid extra typing."]
[null,null,"1. Create table",null,null,null,null,"check table name length"]
[null,"/* Create Oracle table */","Table Name:","COMM_ELEMENT_SUBTYPE",null,null,null,{"$f":"LEN(D3)","v":20}]
[null,{"$f":"\"CREATE TABLE \" & $D$3","v":"CREATE TABLE COMM_ELEMENT_SUBTYPE"},"Fields:","This info is also used on tab #3. Copy rows above DISPLAY_ORDER if you have more fields; make sure the formulas copy. Remove DISPLAY_ORDER and last comma if the table is not a grid."]
[null,"("," ","column_name","column_data_type","column_size","Nulls OK?","check column_name length"]
[null,{"$f":"\"  \"&D6&\"   \"&E6&IF(OR(E6=\"VARCHAR2\",E6=\"CHAR\"), \"(\"&F6&\")\",\"\") & IF(G6=\"N\",\"   NOT NULL\",\"\") & IF(ISBLANK(D6),\"\",\", \")","v":"  COMM_ELEMENT_SUBTYPE_ID   NUMBER   NOT NULL, "}," ",{"$f":"$D$3&\"_ID\"","v":"COMM_ELEMENT_SUBTYPE_ID"},"NUMBER",27,"N",{"$f":"LEN(D6)","v":23}]
[null,{"$f":"\"  \"&D7&\"   \"&E7&IF(OR(E7=\"VARCHAR2\",E7=\"CHAR\"), \"(\"&F7&\")\",\"\") & IF(G7=\"N\",\"   NOT NULL\",\"\") & IF(ISBLANK(D7),\"\",\", \")","v":"  SUBTYPE_PARENT_ID   NUMBER   NOT NULL, "}," ","SUBTYPE_PARENT_ID","NUMBER",27,"N",{"$f":"LEN(D7)","v":17}]
[null,"  SUBTYPE_CODE   VARCHAR2(10), "," ","SUBTYPE_CODE","VARCHAR2",10,null,12]
[null,"  SUBTYPE_SCIENTIFIC_NAME   VARCHAR2(500), "," ","SUBTYPE_SCIENTIFIC_NAME","VARCHAR2",500,null,23]
[null,"  SUBTYPE_CONCEPT_SENTENCE   VARCHAR2(1000), "," ","SUBTYPE_CONCEPT_SENTENCE","VARCHAR2",1000,null,24]
[null,"  SUBTYPE_NOTE   VARCHAR2(2000), "," ","SUBTYPE_NOTE","VARCHAR2",2000,null,12]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,"     "]
[null,{"$f":"\"  \"&D25&\"   \"&E25&IF(OR(E25=\"VARCHAR2\",E25=\"CHAR\"), \"(\"&F25&\")\",\"\") & IF(G25=\"N\",\"   NOT NULL\",\"\")","v":"  DISPLAY_ORDER   NUMBER"}," ","DISPLAY_ORDER","NUMBER",27,null,13]
[null,")"," "]
[null,"TABLESPACE ELEMENTS_DATA;"," "]
[null,null," "]
[null,{"$f":"\"CREATE UNIQUE INDEX \"&$D$3&\"_PK ON \"&$D$3","v":"CREATE UNIQUE INDEX COMM_ELEMENT_SUBTYPE_PK ON COMM_ELEMENT_SUBTYPE"},"2. Add indexes"]
[null,{"$f":"\"(\"&$D$3&\"_ID)\"","v":"(COMM_ELEMENT_SUBTYPE_ID)"},"a. Primary key on ID field"]
[null,"TABLESPACE HDMS_INDEX;"," "]
[null,null," "]
[null,{"$f":"\"CREATE INDEX \"&$D$3&\"_IX1 ON \"&$D$3","v":"CREATE INDEX COMM_ELEMENT_SUBTYPE_IX1 ON COMM_ELEMENT_SUBTYPE"},"b. Other indexes","Edit script directly where highlighted."]
[null,"(SUBTYPE_CODE)"," ","Delete any indexes you don't need (or add more)."]
[null,"TABLESPACE HDMS_INDEX;"]
[]
[null,{"$f":"\"CREATE INDEX \"&$D$3&\"_IX2 ON \"&$D$3","v":"CREATE INDEX COMM_ELEMENT_SUBTYPE_IX2 ON COMM_ELEMENT_SUBTYPE"}," "]
[null,"(SUBTYPE_PARENT_ID)"," "]
[null,"TABLESPACE HDMS_INDEX;"," "]
[null,null," "]
[null,{"$f":"\"ALTER TABLE \"&$D$3&\" ADD (\"","v":"ALTER TABLE COMM_ELEMENT_SUBTYPE ADD ("},"3. Add constraints"]
[null,{"$f":"\"  CONSTRAINT \"&$D$3&\"_PK\"","v":"  CONSTRAINT COMM_ELEMENT_SUBTYPE_PK"},"a. Primary key on ID field"]
[null," PRIMARY KEY"," "]
[null,{"$f":"\" (\"&$D$3&\"_ID)\"","v":" (COMM_ELEMENT_SUBTYPE_ID)"}," "]
[null,"    USING INDEX "," "]
[null,"    TABLESPACE HDMS_INDEX"," "]
[null,"    ENABLE VALIDATE);"," "]
[null,null," "]
[null,"ALTER TABLE COMM_ELEMENT_SUBTYPE ADD (","b. Other constraints","Edit script directly where highlighted."]
[null,"  CONSTRAINT COMM_ELEMENT_SUBTYPE_AK"," ","Delete any constraints you don't need (or add more)."]
[null,"  UNIQUE"]
[null," (SUBTYPE_CODE)"]
[null,"    USING INDEX "]
[null,"    TABLESPACE HDMS_INDEX"]
[null,"    ENABLE VALIDATE);"]
[]
[null,{"$f":"\"ALTER TABLE \"&$D$3&\" ADD (\"","v":"ALTER TABLE COMM_ELEMENT_SUBTYPE ADD ("}," "]
[null,"  CONSTRAINT COMM_EL_SUBTYPE_EGT"," "]
[null,"  FOREIGN KEY (SUBTYPE_PARENT_ID) "," "]
[null,"  REFERENCES COMMUNITY_GLOBAL (ELEMENT_GLOBAL_ID)"," "]
[null,"  ON DELETE CASCADE"," "]
[null,"  ENABLE VALIDATE);"," "]
[null,null," "]
[null,{"$f":"\"GRANT DELETE, INSERT, SELECT, UPDATE ON \"&$D$3&\" TO BIOTICS_DLINK;\"","v":"GRANT DELETE, INSERT, SELECT, UPDATE ON COMM_ELEMENT_SUBTYPE TO BIOTICS_DLINK;"},"4. Add permissions"]
[null,{"$f":"\"GRANT SELECT ON \"&$D$3&\" TO BIOTICS_QUERY;\"","v":"GRANT SELECT ON COMM_ELEMENT_SUBTYPE TO BIOTICS_QUERY;"}," "]
[null,{"$f":"\"GRANT SELECT ON \"&$D$3&\" TO BIOTICS_REPORT;\"","v":"GRANT SELECT ON COMM_ELEMENT_SUBTYPE TO BIOTICS_REPORT;"}," "]
[null,null," "]
[null,null,"5. Add to HDMS_TABLE","field","value"]
[null,"insert into HDMS_TABLE ("," ","hdms_table_id"]
[null,{"$f":"[1]!concat(D69:D74)","v":{"$error":"#NAME?"}}," ","table_name",{"$f":"\"'\" & D3 & \"'\"","v":"'COMM_ELEMENT_SUBTYPE'"}]
[null,") VALUES (getnextseq('HDMS_TABLE'), "," ","table_type","'B'"]
[null,{"$f":"[1]!concat(E70:E74)","v":{"$error":"#NAME?"}}," ","audit_comment","Null"]
[null,");"," ","next_seq_keyword","Null"]
[null,null," ","core",0]
[null,null," "]
[null,"commit;","6. Commit"]

{"sheet":"2. Extensible section","index":2,"n_rows":12,"n_cols":7,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"EXTENSIBLE SECTION"]
[]
[null,"/* Add extensible section */",null,"fields","values (fill these in)","note","values with quotes"]
[null,"insert into DYNAMIC_OBJECT (dynamic_object_id, "," ","dynamic_form_id",15,"Get correct value from DYNAMIC_FORM table.",{"$f":"E4","v":15}]
[null,{"$f":"[1]!concat(D4:D12)","v":{"$error":"#NAME?"}}," ","dynamic_name","Subtypes","THIS IS WHAT IS DISPLAYED AS THE SECTION HEADER.",{"$f":"\"'\" & E5 & \"'\"","v":"'Subtypes'"}]
[null,") VALUES (getnextseq('DYNAMIC_OBJECT'), "," ","dynamic_desc","Ecology EGT element subtypes",{"$f":"\"(optional, max characters = 50, actual = \" & LEN(E6) & \")\"","v":"(optional, max characters = 50, actual = 28)"},{"$f":"IF(ISBLANK(E6),\"Null\",\"'\" & E6 & \"'\")","v":"'Ecology EGT element subtypes'"}]
[null,{"$f":"[1]!concat(G4:G12)","v":{"$error":"#NAME?"}}," ","table_name",{"$f":"'1. Oracle table'!$D$3","v":"COMM_ELEMENT_SUBTYPE"},null,{"$f":"\"'\" & E7 & \"'\"","v":"'COMM_ELEMENT_SUBTYPE'"}]
[null,");"," ","tab_caption","Subtypes","THIS FIELD APPEARS TO HAVE NO EFFECT ON THE EXTENSIBLE SECTION VIEW.",{"$f":"\"'\" & E8 & \"'\"","v":"'Subtypes'"}]
[null,null," ","grid_label","Subtypes","(only for grids)",{"$f":"IF(ISBLANK(E9),\"Null\",\"'\" & E9 & \"'\")","v":"'Subtypes'"}]
[null,null," ","pk_column_name",{"$f":"'1. Oracle table'!$D$3&\"_ID\"","v":"COMM_ELEMENT_SUBTYPE_ID"},null,{"$f":"\"'\" & E10 & \"'\"","v":"'COMM_ELEMENT_SUBTYPE_ID'"}]
[null,null," ","fk_column_name","SUBTYPE_PARENT_ID",null,{"$f":"\"'\" & E11 & \"'\"","v":"'SUBTYPE_PARENT_ID'"}]
[null,null," ","is_grid",1,"1 = yes, 0 = no",{"$f":"E12","v":1}]

{"sheet":"3. Extensible fields","index":3,"n_rows":25,"n_cols":31,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,null,"FIELDS IN EXTENSIBLE SECTION"]
[null,null,"Copy more rows above DISPLAY_ORDER if you did so on tab #1. Make sure formulas are copied."]
[]
[null,null,"These values are copied from previous tabs.",null,null,null,null,"These values are specific to the display.",null,null,null,null,null,null,null,null,"with quotes"]
[null,"/* Add extensible fields */","dynamic_name","dynamic_form_id","column_name","column_data_type","column_size","default_value","visible","display_label","system_control_type_code","display_order","domain_table_name","domain_display_column","domain_data_column","domain_display_width","dynamic_object_id","column_name","column_data_type","column_size","default_value","visible","display_label","system_control_type_code","display_order","domain_table_name","domain_display_column","domain_data_column","domain_display_width","object_control_id","label_control_id"]
[null,{"$f":"IF(COUNTBLANK(E6)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R6:AD6) & \" from dynamic_object where dynamic_name = \" & Q6 & \" and dynamic_form_id=\" & D6 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'COMM_ELEMENT_SUBTYPE_ID', 'NUMBER', 27, Null, 0, Null, 'TXB', 0, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D6>\"\",'1. Oracle table'!D6,\"\")","v":"COMM_ELEMENT_SUBTYPE_ID"},{"$f":"IF('1. Oracle table'!E6>\"\",'1. Oracle table'!E6,\"\")","v":"NUMBER"},{"$f":"IF('1. Oracle table'!F6>0,'1. Oracle table'!F6,\"\")","v":27},null,0,null,"TXB",0,null,null,null,null,{"$f":"\"'\"&C6&\"'\"","v":"'Subtypes'"},{"$f":"\"'\"&E6&\"'\"","v":"'COMM_ELEMENT_SUBTYPE_ID'"},{"$f":"IF(ISBLANK(F6),\"Null\",\"'\"&F6&\"'\")","v":"'NUMBER'"},{"$f":"G6","v":27},{"$f":"IF(ISBLANK(H6),\"Null\",\"'\"&H6&\"'\")","v":"Null"},{"$f":"I6","v":0},{"$f":"IF(ISBLANK(J6),\"Null\",\"'\"&J6&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(K6),\"Null\",\"'\"&K6&\"'\")","v":"'TXB'"},{"$f":"L6","v":0},{"$f":"IF(ISBLANK(M6),\"Null\",\"'\"&M6&\"'\")","v":"Null"},"Null","Null",{"$f":"IF(ISBLANK(P6),\"Null\",P6)","v":"Null"},0]
[null,{"$f":"IF(COUNTBLANK(E7)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R7:AD7) & \" from dynamic_object where dynamic_name = \" & Q7 & \" and dynamic_form_id=\" & D7 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'SUBTYPE_PARENT_ID', 'NUMBER', 27, Null, 0, Null, 'TXB', 0, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D7>\"\",'1. Oracle table'!D7,\"\")","v":"SUBTYPE_PARENT_ID"},{"$f":"IF('1. Oracle table'!E7>\"\",'1. Oracle table'!E7,\"\")","v":"NUMBER"},{"$f":"IF('1. Oracle table'!F7>0,'1. Oracle table'!F7,\"\")","v":27},null,0,null,"TXB",0,null,null,null,null,{"$f":"\"'\"&C7&\"'\"","v":"'Subtypes'"},{"$f":"\"'\"&E7&\"'\"","v":"'SUBTYPE_PARENT_ID'"},{"$f":"IF(ISBLANK(F7),\"Null\",\"'\"&F7&\"'\")","v":"'NUMBER'"},{"$f":"G7","v":27},{"$f":"IF(ISBLANK(H7),\"Null\",\"'\"&H7&\"'\")","v":"Null"},{"$f":"I7","v":0},{"$f":"IF(ISBLANK(J7),\"Null\",\"'\"&J7&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(K7),\"Null\",\"'\"&K7&\"'\")","v":"'TXB'"},{"$f":"L7","v":0},{"$f":"IF(ISBLANK(M7),\"Null\",\"'\"&M7&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(N7),\"Null\",\"'\"&N7&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(O7),\"Null\",\"'\"&O7&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(P7),\"Null\",P7)","v":"Null"},0]
[null,{"$f":"IF(COUNTBLANK(E8)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R8:AD8) & \" from dynamic_object where dynamic_name = \" & Q8 & \" and dynamic_form_id=\" & D8 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'SUBTYPE_CODE', 'VARCHAR2', 10, Null, 1, 'Code', 'TXB', 1, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D8>\"\",'1. Oracle table'!D8,\"\")","v":"SUBTYPE_CODE"},{"$f":"IF('1. Oracle table'!E8>\"\",'1. Oracle table'!E8,\"\")","v":"VARCHAR2"},{"$f":"IF('1. Oracle table'!F8>0,'1. Oracle table'!F8,\"\")","v":10},null,1,"Code","TXB",1,null,null,null,null,"'Subtypes'","'SUBTYPE_CODE'","'VARCHAR2'",10,"Null",1,"'Code'","'TXB'",1,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E9)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R9:AD9) & \" from dynamic_object where dynamic_name = \" & Q9 & \" and dynamic_form_id=\" & D9 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'SUBTYPE_SCIENTIFIC_NAME', 'VARCHAR2', 500, Null, 1, 'Formatted Scientific Name', 'RTE', 2, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D9>\"\",'1. Oracle table'!D9,\"\")","v":"SUBTYPE_SCIENTIFIC_NAME"},{"$f":"IF('1. Oracle table'!E9>\"\",'1. Oracle table'!E9,\"\")","v":"VARCHAR2"},{"$f":"IF('1. Oracle table'!F9>0,'1. Oracle table'!F9,\"\")","v":500},null,1,"Formatted Scientific Name","RTE",2,null,null,null,null,"'Subtypes'","'SUBTYPE_SCIENTIFIC_NAME'","'VARCHAR2'",500,"Null",1,"'Formatted Scientific Name'","'RTE'",2,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E10)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R10:AD10) & \" from dynamic_object where dynamic_name = \" & Q10 & \" and dynamic_form_id=\" & D10 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'SUBTYPE_CONCEPT_SENTENCE', 'VARCHAR2', 1000, Null, 1, 'Concept Sentence', 'RTE', 3, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D10>\"\",'1. Oracle table'!D10,\"\")","v":"SUBTYPE_CONCEPT_SENTENCE"},{"$f":"IF('1. Oracle table'!E10>\"\",'1. Oracle table'!E10,\"\")","v":"VARCHAR2"},{"$f":"IF('1. Oracle table'!F10>0,'1. Oracle table'!F10,\"\")","v":1000},null,1,"Concept Sentence","RTE",3,null,null,null,null,"'Subtypes'","'SUBTYPE_CONCEPT_SENTENCE'","'VARCHAR2'",1000,"Null",1,"'Concept Sentence'","'RTE'",3,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E11)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R11:AD11) & \" from dynamic_object where dynamic_name = \" & Q11 & \" and dynamic_form_id=\" & D11 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'SUBTYPE_NOTE', 'VARCHAR2', 2000, Null, 1, 'Note', 'RTE', 4, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D11>\"\",'1. Oracle table'!D11,\"\")","v":"SUBTYPE_NOTE"},{"$f":"IF('1. Oracle table'!E11>\"\",'1. Oracle table'!E11,\"\")","v":"VARCHAR2"},{"$f":"IF('1. Oracle table'!F11>0,'1. Oracle table'!F11,\"\")","v":2000},null,1,"Note","RTE",4,null,null,null,null,"'Subtypes'",{"$f":"\"'\"&E11&\"'\"","v":"'SUBTYPE_NOTE'"},{"$f":"IF(ISBLANK(F11),\"Null\",\"'\"&F11&\"'\")","v":"'VARCHAR2'"},{"$f":"G11","v":2000},{"$f":"IF(ISBLANK(H11),\"Null\",\"'\"&H11&\"'\")","v":"Null"},{"$f":"I11","v":1},{"$f":"IF(ISBLANK(J11),\"Null\",\"'\"&J11&\"'\")","v":"'Note'"},{"$f":"IF(ISBLANK(K11),\"Null\",\"'\"&K11&\"'\")","v":"'RTE'"},{"$f":"L11","v":4},{"$f":"IF(ISBLANK(M11),\"Null\",\"'\"&M11&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(N11),\"Null\",\"'\"&N11&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(O11),\"Null\",\"'\"&O11&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(P11),\"Null\",P11)","v":"Null"},0]
[null,{"$f":"IF(COUNTBLANK(E12)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R12:AD12) & \" from dynamic_object where dynamic_name = \" & Q12 & \" and dynamic_form_id=\" & D12 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D12>\"\",'1. Oracle table'!D12,\"\")","v":""},{"$f":"IF('1. Oracle table'!E12>\"\",'1. Oracle table'!E12,\"\")","v":""},{"$f":"IF('1. Oracle table'!F12>0,'1. Oracle table'!F12,\"\")","v":""},null,0,null,"TXB",null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","'TXB'",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E13)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R13:AD13) & \" from dynamic_object where dynamic_name = \" & Q13 & \" and dynamic_form_id=\" & D13 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D13>\"\",'1. Oracle table'!D13,\"\")","v":""},{"$f":"IF('1. Oracle table'!E13>\"\",'1. Oracle table'!E13,\"\")","v":""},{"$f":"IF('1. Oracle table'!F13>0,'1. Oracle table'!F13,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'",{"$f":"\"'\"&E13&\"'\"","v":"''"},{"$f":"IF(ISBLANK(F13),\"Null\",\"'\"&F13&\"'\")","v":"''"},{"$f":"G13","v":""},{"$f":"IF(ISBLANK(H13),\"Null\",\"'\"&H13&\"'\")","v":"Null"},{"$f":"I13","v":0},{"$f":"IF(ISBLANK(J13),\"Null\",\"'\"&J13&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(K13),\"Null\",\"'\"&K13&\"'\")","v":"Null"},{"$f":"L13","v":0},{"$f":"IF(ISBLANK(M13),\"Null\",\"'\"&M13&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(N13),\"Null\",\"'\"&N13&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(O13),\"Null\",\"'\"&O13&\"'\")","v":"Null"},{"$f":"IF(ISBLANK(P13),\"Null\",P13)","v":"Null"},0]
[null,{"$f":"IF(COUNTBLANK(E14)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R14:AD14) & \" from dynamic_object where dynamic_name = \" & Q14 & \" and dynamic_form_id=\" & D14 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D14>\"\",'1. Oracle table'!D14,\"\")","v":""},{"$f":"IF('1. Oracle table'!E14>\"\",'1. Oracle table'!E14,\"\")","v":""},{"$f":"IF('1. Oracle table'!F14>0,'1. Oracle table'!F14,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E15)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R15:AD15) & \" from dynamic_object where dynamic_name = \" & Q15 & \" and dynamic_form_id=\" & D15 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D15>\"\",'1. Oracle table'!D15,\"\")","v":""},{"$f":"IF('1. Oracle table'!E15>\"\",'1. Oracle table'!E15,\"\")","v":""},{"$f":"IF('1. Oracle table'!F15>0,'1. Oracle table'!F15,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E16)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R16:AD16) & \" from dynamic_object where dynamic_name = \" & Q16 & \" and dynamic_form_id=\" & D16 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D16>\"\",'1. Oracle table'!D16,\"\")","v":""},{"$f":"IF('1. Oracle table'!E16>\"\",'1. Oracle table'!E16,\"\")","v":""},{"$f":"IF('1. Oracle table'!F16>0,'1. Oracle table'!F16,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E17)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R17:AD17) & \" from dynamic_object where dynamic_name = \" & Q17 & \" and dynamic_form_id=\" & D17 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D17>\"\",'1. Oracle table'!D17,\"\")","v":""},{"$f":"IF('1. Oracle table'!E17>\"\",'1. Oracle table'!E17,\"\")","v":""},{"$f":"IF('1. Oracle table'!F17>0,'1. Oracle table'!F17,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E18)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R18:AD18) & \" from dynamic_object where dynamic_name = \" & Q18 & \" and dynamic_form_id=\" & D18 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D18>\"\",'1. Oracle table'!D18,\"\")","v":""},{"$f":"IF('1. Oracle table'!E18>\"\",'1. Oracle table'!E18,\"\")","v":""},{"$f":"IF('1. Oracle table'!F18>0,'1. Oracle table'!F18,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E19)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R19:AD19) & \" from dynamic_object where dynamic_name = \" & Q19 & \" and dynamic_form_id=\" & D19 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D19>\"\",'1. Oracle table'!D19,\"\")","v":""},{"$f":"IF('1. Oracle table'!E19>\"\",'1. Oracle table'!E19,\"\")","v":""},{"$f":"IF('1. Oracle table'!F19>0,'1. Oracle table'!F19,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E20)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R20:AD20) & \" from dynamic_object where dynamic_name = \" & Q20 & \" and dynamic_form_id=\" & D20 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D20>\"\",'1. Oracle table'!D20,\"\")","v":""},{"$f":"IF('1. Oracle table'!E20>\"\",'1. Oracle table'!E20,\"\")","v":""},{"$f":"IF('1. Oracle table'!F20>0,'1. Oracle table'!F20,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E21)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R21:AD21) & \" from dynamic_object where dynamic_name = \" & Q21 & \" and dynamic_form_id=\" & D21 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D21>\"\",'1. Oracle table'!D21,\"\")","v":""},{"$f":"IF('1. Oracle table'!E21>\"\",'1. Oracle table'!E21,\"\")","v":""},{"$f":"IF('1. Oracle table'!F21>0,'1. Oracle table'!F21,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E22)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R22:AD22) & \" from dynamic_object where dynamic_name = \" & Q22 & \" and dynamic_form_id=\" & D22 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D22>\"\",'1. Oracle table'!D22,\"\")","v":""},{"$f":"IF('1. Oracle table'!E22>\"\",'1. Oracle table'!E22,\"\")","v":""},{"$f":"IF('1. Oracle table'!F22>0,'1. Oracle table'!F22,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E23)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R23:AD23) & \" from dynamic_object where dynamic_name = \" & Q23 & \" and dynamic_form_id=\" & D23 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D23>\"\",'1. Oracle table'!D23,\"\")","v":""},{"$f":"IF('1. Oracle table'!E23>\"\",'1. Oracle table'!E23,\"\")","v":""},{"$f":"IF('1. Oracle table'!F23>0,'1. Oracle table'!F23,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E24)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R24:AD24) & \" from dynamic_object where dynamic_name = \" & Q24 & \" and dynamic_form_id=\" & D24 & \";\")","v":""},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D24>\"\",'1. Oracle table'!D24,\"\")","v":""},{"$f":"IF('1. Oracle table'!E24>\"\",'1. Oracle table'!E24,\"\")","v":""},{"$f":"IF('1. Oracle table'!F24>0,'1. Oracle table'!F24,\"\")","v":""},null,null,null,null,null,null,null,null,null,"'Subtypes'","''","''","","Null",0,"Null","Null",0,"Null","Null","Null","Null",0]
[null,{"$f":"IF(COUNTBLANK(E25)>0,\"\",\"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, \" & concat(R$5:AD$5) & \") select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, \" & concat(R25:AD25) & \" from dynamic_object where dynamic_name = \" & Q25 & \" and dynamic_form_id=\" & D25 & \";\")","v":"insert into DYNAMIC_ATTRIBUTE (dynamic_attribute_id, dynamic_object_id, column_name, column_data_type, column_size, default_value, visible, display_label, system_control_type_code, display_order, domain_table_name, domain_display_column, domain_data_column, domain_display_width, object_control_id) select getnextseq('DYNAMIC_ATTRIBUTE'), dynamic_object_id, 'DISPLAY_ORDER', 'NUMBER', 27, Null, 0, Null, Null, 0, Null, Null, Null, Null, 0 from dynamic_object where dynamic_name = 'Subtypes' and dynamic_form_id=15;"},{"$f":"'2. Extensible section'!$E$5","v":"Subtypes"},{"$f":"'2. Extensible section'!$E$4","v":15},{"$f":"IF('1. Oracle table'!D25>\"\",'1. Oracle table'!D25,\"\")","v":"DISPLAY_ORDER"},{"$f":"IF('1. Oracle table'!E25>\"\",'1. Oracle table'!E25,\"\")","v":"NUMBER"},{"$f":"IF('1. Oracle table'!F25>0,'1. Oracle table'!F25,\"\")","v":27},null,null,null,null,null,null,null,null,null,"'Subtypes'","'DISPLAY_ORDER'","'NUMBER'",27,"Null",0,"Null","Null",0,"Null","Null","Null","Null",0]

{"sheet":"lookups","index":4,"n_rows":8,"n_cols":4,"rows_kept":8,"cut":false,"hidden":true,"merged":[]}
["column_data_type",null,"system_control_type_code"]
["CHAR",null,"CHB","Checkbox "]
["CLOB",null,"DCB","Combo box (Dropdown)"]
["DATE",null,"DCT","Date Control"]
["NUMBER",null,"RTE","Rich Text Editor "]
["VARCHAR2",null,"TXB","Text Box "]
[null,null,"MSL","Multi-list"]
[null,null,"PIC"," Picture box"]