Banner Replication SAR

• Banner replication to the Registry occurs external to Middleware's application container.
• No one in Middleware controls or maintains the current replication code, which is written in PL/SQL.
• Replication writes directly to the database, undercutting Middleware's application container and breaking our 3-tier environment.

Duplicate the current replication functionality in its entirety as a JBoss service.

1. Read payload from an Oracle Advanced Queue.
2. Verify message is valid per XSD.
3. Send XML message to the Banner Change Bean for processing.
4. Do not acknowlege payload from Queue until the Banner Change Bean has responded that the message has been processed.

1. Must be written as a clustered singleton JBoss service.

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           elementFormDefault="unqualified"
           attributeFormDefault="unqualified">
 
  <xs:element name="PERSONSET">
    <xs:complexType>
    <xs:all>
      <xs:element name="PERSON" minOccurs="1" maxOccurs="1">
        <xs:complexType>
        <xs:sequence>
          <xs:element name="BANNER_PIDM" type="xs:integer" minOccurs="1" maxOccurs="1"/>
          <xs:element name="VT_IDNUM" type="xs:string" minOccurs="1" maxOccurs="1"/>
          <xs:element name="NAMESET" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:sequence>
              <xs:element name="PREFNAME" type="namedata" minOccurs="0" maxOccurs="1"/>
              <xs:element name="NONPREFNAME" type="namedata" minOccurs="0" maxOccurs="1"/>
              <xs:element name="ALUMNAME" type="namedata" minOccurs="0" maxOccurs="1"/>
            </xs:sequence>
            </xs:complexType>
          </xs:element>
          <xs:element name="BIRTHDATE" type="date-or-empty" minOccurs="0" maxOccurs="1"/>
          <xs:element name="SSN" type="xs:string" minOccurs="0" maxOccurs="1"/>
          <xs:element name="CONFIDENTIAL_FLAG" type="xs:boolean" minOccurs="1" maxOccurs="1"/>
          <xs:element name="DECEASED_FLAG" type="xs:boolean" minOccurs="1" maxOccurs="1"/>
          <xs:element name="AFFILIATION_DATA" minOccurs="1" maxOccurs="1">
            <xs:complexType>
            <xs:sequence>
              <xs:element name="AFFILIATION" type="xs:string" minOccurs="1" maxOccurs="unbounded"/>
            </xs:sequence>
            </xs:complexType>
          </xs:element>
          <xs:element name="ADDRESS_SET" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:sequence>
              <xs:element name="ADDRESS" minOccurs="1" maxOccurs="unbounded">
                <xs:complexType>
                <xs:all>
                  <xs:element name="STREET1" type="xs:string"/>
                  <xs:element name="STREET2" type="xs:string"/>
                  <xs:element name="STREET3" type="xs:string"/>
                  <xs:element name="CITY" type="xs:string"/>
                  <xs:element name="STATE_CODE" type="xs:string"/>
                  <xs:element name="POSTALCODE" type="xs:string"/>
                  <xs:element name="COUNTRY" type="xs:string"/>
                  <xs:element name="UNLISTED_FLAG" type="xs:boolean"/>
                  <xs:element name="MAILSTOP" type="xs:string"/>
                  <xs:element name="PHONESET">
                    <xs:complexType>
                    <xs:sequence>
                      <xs:element name="PHONE" minOccurs="0" maxOccurs="unbounded">
                        <xs:complexType>
                        <xs:all>
                          <xs:element name="NUMBER" type="xs:string"/>
                          <xs:element name="UNLISTED_FLAG" type="xs:boolean"/>
                        </xs:all>
                        <xs:attribute name="TYPE" type="xs:string" use="required"/>
                        </xs:complexType>
                      </xs:element>
                    </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:all>
                <xs:attribute name="TYPE" type="xs:string" use="required"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            </xs:complexType>
          </xs:element>
          <xs:element name="EMPLOYEE_DATA" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:all>
              <xs:element name="EMPLOYEE_TYPE" type="xs:string"/>
              <xs:element name="JOB_TITLE" type="xs:string"/>
              <xs:element name="EMPLOYEE_STATUS" type="xs:string"/>
              <xs:element name="HIREDATE" type="date-or-empty"/>
              <xs:element name="TERMDATE" type="date-or-empty"/>
              <xs:element name="DEPT" type="xs:string"/>
              <xs:element name="CONFIDENTIAL" type="xs:boolean"/>
            </xs:all>
            </xs:complexType>
          </xs:element>
          <xs:element name="STUDENT_DATA" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:sequence>
              <xs:element name="CAMPUS" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="ACADEMIC_LEVEL" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="GEN_ACADEMIC_LEVEL" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="STATUS" type="empty-string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="LASTTERM" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="NEXTTERM" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="COLLEGE" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="MAJOR" type="xs:string" minOccurs="0" maxOccurs="1"/>
              <xs:element name="DEGREE_SET" minOccurs="0" maxOccurs="1">
                <xs:complexType>
                <xs:sequence>
                  <xs:element name="DEGREE" minOccurs="0" maxOccurs="unbounded">
                    <xs:complexType>
                    <xs:all>
                      <xs:element name="LEVEL" type="xs:string"/>
                      <xs:element name="CODE" type="xs:string"/>
                      <xs:element name="MAJOR" type="xs:string"/>
                    </xs:all>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="MAJOR_SET" minOccurs="0" maxOccurs="1">
                <xs:complexType>
                <xs:all>
                  <xs:element name="MAJOR" type="xs:string"/>
                </xs:all>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            </xs:complexType>
          </xs:element>
          <xs:element name="ALUMNI_DATA" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:all>
              <xs:element name="CLASSYEAR" type="xs:string"/>
              <xs:element name="EXCLUSION_FLAG" type="xs:boolean"/>
              <xs:element name="WEBDIRECTORY" type="xs:boolean"/>
            </xs:all>
            </xs:complexType>
          </xs:element>
          <xs:element name="NONVT_DATA" minOccurs="0" maxOccurs="1">
            <xs:complexType>
            <xs:sequence>
              <xs:element name="EMAIL" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                <xs:all>
                  <xs:element name="EMAIL_ADDRESS" type="xs:string"/>
                </xs:all>
                <xs:attribute name="TYPE" type="xs:string" use="required"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:all>
    </xs:complexType>
  </xs:element>
 
  <xs:complexType name="namedata">
    <xs:all>
      <xs:element name="LASTNAME" type="xs:string"/>
      <xs:element name="FIRSTNAME" type="xs:string"/>
      <xs:element name="MIDDLENAME" type="xs:string"/>
      <xs:element name="SUFFIX" type="xs:string"/>
      <xs:element name="LEGALNAME" type="xs:boolean"/>
    </xs:all>
  </xs:complexType>
 
  <xs:simpleType name="date-or-empty">
    <xs:union memberTypes="xs:date empty-string" />
  </xs:simpleType>
 
  <xs:simpleType name="empty-string">
    <xs:restriction base="xs:string">
      <xs:enumeration value=""/>
    </xs:restriction>
  </xs:simpleType>
 
</xs:schema>

Messages that are invalid per the XSD will be rolled back by banner-repl and will eventually go into the exception queue for ED_VTPEOPLE_QUEUE. The exception queue is named AQ$_ED_VTPEOPLE_QTABLE_E. To view the messages in the exception queue, one can issue the following query:

SELECT to_char(extract(user_data,'/PERSONSET').getClobVal())
FROM VTREGISTRY.ED_VTPEOPLE_QTABLE
WHERE q_name = 'AQ$_ED_VTPEOPLE_QTABLE_E'

Sometimes it is useful to look at messages after a certain point in time:

SELECT to_char(extract(user_data,'/PERSONSET').getClobVal())
FROM VTREGISTRY.ED_VTPEOPLE_QTABLE
WHERE q_name = 'AQ$_ED_VTPEOPLE_QTABLE_E' AND enq_time > to_date('15-MAR-07')

The following table shows the mapping between Banner and Registry address types:

Note: If both a DM and an MA address exist in the payload, the DM address will take precedence.

• Javadocs
• Download Personset XSD
• Replication Diagram

Here are the differences between the old PL/SQL code and the new Java code:

• XML payload must be valid per the XSD
• Leading and trailing whitespace is removed from data
• Extra whitespace such as double spaces become a single space (need to verify this)
• Special characters are properly encoded
• A change is only made in the database if the data is different
• Deceased people are properly shelved
• Certain default values in the PL/SQL are no longer set (e.g. employee names. Need to get a more complete list of these)