Request:

Create an EO Rank Change extensible section to reflect the SRANK Changes grid in the Subnational Element Tracking record.

Limitations:

Unlike the SRANK Changes grid:

  • The EO Rank Changes extensible section cannot be auto-populated. The functionality which enables that for the SRANK Changes grid requires application development - it is not controlled by the Oracle database (via a trigger, or any other way) and hence is not feasible within an extensible section. (BX-3531)
  • The Reference search via the binoculars icon again requires application development and is not functionality available in an extensible section. Furthermore, given the number of references, this wouldn't be possible via a standard dropdown (domain) list as it would significantly impact performance. (BX-5480). Hence, the appropriate Reference Code will have to be manually entered.

Solution (with Limitations!):

Attached is the Extensible table generation tool (spreadsheet) completed to create the EO Rank Change extensible table. The extraneous tabs in the spreadsheet have been hidden so only the relevant ones are displayed.

  1. Download the attached spreadsheet.
  2. Log into your preferred third-party tool (SQL Developer, SQL Plus, Tora, Toad) as the biotics_dlink user in order to run the SQL statements to create the Oracle tables necessary for the extensible section.
    NOTE: If using SQL Plus, remove empty lines from the SQL statement in the 1. Oracle table - 1 to n tab (column B, rows 4 - 27) prior to running it. SQL Plus interprets empty lines as ending the statement (rather than the semi-colon) and will result in an error regarding unknown command. This can be avoided by using SQL Developer, Tora, or Toad.
  3. First, create the new domain table:
    1. In the 1. Oracle table - Domain Table tab, copy the first SQL statement to create the new Domain table (red text, column B, rows 4 - 14).
    2. Paste the statement into the third-party tool and run it. 
    3. Assuming no errors are received, in Biotics, Grant/Refresh Database Privileges.
    4. Copy the remaining SQL statements (red text, column B, rows 17 - 33) into the third-party tool and run them all.
    5. Assuming no errors are received, commit; the changes.
  4. Next, create the extensible table:
    1. In the 1. Oracle table - 1 to n tab, copy the first SQL statement to create the new extensible table (red text, column B, rows 4 - 27).
    2. Paste the statement into the third-party tool and run it.
      NOTE: If using SQL Plus, remove empty lines from the SQL statement in the 1. Oracle table - 1 to n tab (column B, rows 4 - 27) prior to running it. SQL Plus interprets empty lines as ending the statement (rather than the semi-colon) and will result in an error regarding unknown command. This can be avoided by using SQL Developer, Tora, or Toad.
    3. Assuming no errors are received, in Biotics, Grant/Refresh Database Privileges.
    4. Copy the remaining SQL statements (red text, column B, rows 30 - 54) into the third-party tool and run them all.
    5. Assuming no errors are received, commit; the changes.
  5. Next, create the extensible sectionin Biotics (NOTE: This requires Administrative privileges):
    1. In Biotics, from the Configuration menu, choose Extensible Data.
    2. Within the Administer Extensible Data window, from the Filter By: Record Type dropdown menu, select Element Occurrence.
    3. Click the New Record button found in the lower left corner. The Manage Extensible Section dialog opens with the Record Type
    4. Complete the Manage Extensible Section dialog, as shown below, and detailed within the Create Extensible Section help topic.And, an example of the Combo Box details:

    5. Test the extensible section:
      1. Open an EO record. Within the record's Table of Contents, navigate to the EO Rank Change extensible section, distinguished with italics type.
      2. Add data to each field within the newly created extensible section and Save the record to verify that no errors are received. This will determine if the extensible section has been successfully created. 
      3. Then save the record a second time, because some errors are only encountered on the second save!