Document : InsBridge Query-Report Specification

Updated : 8/15/2002

Author : Dave Goodall

User : Susan Neal

Contents

1. Report Description

2. User Operation

2.1 Carrier-State Code entry

2.2 Query Program Operation

3. Implementation

3.1 'Insbridge Carrier-State List' field

3.2 C# query program

4. Installation

1. Report Description :

The report lists issues of these types :

Insweb Bug, Project Product Change, Project Other, Documentation Change.

Issues are listed in in order of : Release(MPL#), Carrier, State

For each issue these these fields are displayed:

Release(MPL#) Carrier State IssueID IssueType Description AssignedTo

2. User Operation

2.1 Carrier-State Code Entry:

Carrier-State codes must be entered into the ‘InsBridge Carrier-State List’ field on the Project Info or Bug Info tabs of issues that are to be reported. For example :

The rules for entering State-Carrier combinations are :

- You can use upper case or lower case or a mixture. The program won't care but ALL-CAPS seems natural and is recommended. It is desirable to pick a standard and stick to it - consistently formatted stuff is easier to read.

- A CARRIER-STATE entry must be a contiguous string ie. should not contain a space or spaces

- The separator between CARRIER and STATE can be a dash (-) or a forward slash (/).

The program won't care but a dash is suggested, and that, whichever convention you pick you stick with it.

- Multiple CARRIER-STATE entries must be separated by a space ( or spaces if you want to for easier

readability) or comma(s) or semi-colon(s).

- For CARRIER acronyms i suggest you use the PMAC short-forms except for the carriers where i have

shown a 'Preferred acronym' . The program will accept both the PMAC and preferred acronym(s), and will accept this in the forma of an external table which can easily be changed.

The carrier acronyms in use as of the date of this document are listed below:

Carrier PMAC Preferred

Acronym Acronym

------

21st Century 20Cent

Auto Club Insurance Association AAAMiWi

AAA MO AAAMO

Auto Club South Insurance Company AAASO

AIG AIG

AIG Specialty AIGSpecial AIGS

Allstate Insurance Company ALLSTATE

AMEX Assurance Company AMEX

American Family AMFAM

Amica AMICA

Atlanta Casualty Companies AtlCas

Avomark AV

Country Companies CC

CNA CNA

Commerce Group Inc. COMM

CSE Insurance Group CSE

Deerbrook Insurance Company Deerbrk DB

Electric Insurance Company ELEC

Explorer Insurance Company EXPL

Farmers Farmers

Financial Indemnity Company FIC

GE Auto Insurance Program GEFA

GMAC Insurance GMAC

Great American GREATAMER GA

Hartford Affinity HFAff HFAF

Hartford APL HFAgency HFAG

The Hartford HPLIC

Infinity Infinity

Kemper KEMP

Leader Leader LDR

Liberty Mutual LM

Mercury MERC

Mercury MercGr

MetLife P and C MetLifePC

Meridian Insurance MRDIAN

National Merit NatlMerit

Nationwide Insurance (Obsolete) NW

Nationwide Insurance (Obsolete) NWE

New York Central Mutual NYCM

OrionAuto ORION

Permanent General PG

Progressive Insurance PROG

KemperDirect RD

Republic Indemnity Republic

Reliance National RLNT

Reliance Personal Insurance RPI

State Farm SF

TIG/ABD TIGABD

Travelers Property Casualty TrvlrsPC TRAV

Tri-State Consumer Insurance Co. TS

AIG Union Privilege UnionPriv

Unitrin Direct Unitrin

Windsor Group Windsor

2.2 Query program operation.

2.2.1 Logon

Click on the desktop icon of the query program.

On the Oracle Tab specify ‘Chainlink’ as the data source server, and enter your logon name and password.

The ‘Microsoft’ driver should be selected.

Click ‘Connect’ to logon to the Chainlink database

2.2.2 Query

When the main program dialog appears, use

Use File | Open to select ‘Insbridge.sql’ , then Query | Execute to run the query.

2.2.3Output

Use File | Save Query Results to save the query results in .csv format.

2.2.4 Import to Excel

The .CSV file may be imported into Excel:

3. Implementation

3.1 The 'Insbridge Carrier-State List' field is now present on these issue types:

- Insweb issue (Bug)

- Project Other

- Project Product Change

- Document Change

The field is defined to use the PARAMETER20 field of the CLIS_ISSUE_DETAILS table

which is reserved for this purpose.

3.2 A C# program installed on the user’s client computer issues this catalogued PL/SQL query:

select lu.meaning, id.parameter20, i.issue_number, it.issue_type_name, i.description, u.username

from cl.clri_issue_details id, cl.clri_issues i, cl.clri_issue_types it, cl.clis_users u, cl.clis_lookups lu

where id.parameter20 is not null and

i.issue_id = id.issue_id and

i.issue_type_id = it.issue_type_id and

i.assigned_to_user_id = u.user_id and

( i.module_code = lu.lookup_code and lu.lookup_type = 'APPLICATION' )

order by lu.meaning

The initial result set returned is, for example :

3.3 The program parses the PARAMETER20 string and generates an expanded secondary result set with one row for each carrier-state combination.

This is then sorted to RELEASE : CARRIER : STATE order.

The final result set presented to the user is :

RELEASE CARRIER_STATE ISSUE_NUMBER ISSUE_TYPE_NAME DESCRIPTION USERNAME

------

AU7.0 AIGS-VA 68507 InsWeb Project (Product Change) AMS - code discounts/surcharges for AIGS-VA, FIC-IL & FIC-OH ctaggart

AU7.0 FIC-IL 68507 InsWeb Project (Product Change) AMS - code discounts/surcharges for AIGS-VA, FIC-IL & FIC-OH ctaggart

AU7.0 FIC-OH 68507 InsWeb Project (Product Change) AMS - code discounts/surcharges for AIGS-VA, FIC-IL & FIC-OH ctaggart

AU7.7 DB-OR 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 DB-PA 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 DB-TX 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 DB-VA 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 FIC-CA 71685 InsWeb Project (Product Change) FIC CA and FIC OH Merge FIC Vehicle surcharge date to Insweb sneal

AU7.7 FIC-CA 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 FIC-OH 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 FIC-OH 71685 InsWeb Project (Product Change) FIC CA and FIC OH Merge FIC Vehicle surcharge date to Insweb sneal

AU7.7 GMAC-TX 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 GMAC-TX 71427 InsWeb Bug Tracking GMAC TX-fix rounding sneal

AU7.7 HFAG-AZ 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 HFAG-VA 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 HFAG-WA 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 TRAV-IN 71590 InsWeb Project (Other) Travelers TX & IN: InsBridge to DNQ if no base rates found sneal

AU7.7 TRAV-TX 71741 InsWeb Project (Product Change) InsBridge- Remove Rate Effective Date in IB Rate Set/Package sneal

AU7.7 TRAV-TX 71590 InsWeb Project (Other) Travelers TX & IN: InsBridge to DNQ if no base rates found sneal

AU7.8 EIC-OH 71345 InsWeb Project (Other) EIC-OH coverage limit revisions lruss

AU7.8 HFAG-IL 71761 InsWeb Project (Product Change) HFAgency IL - conversion to InsBridge - IB work ctaggart

AU7.8 XML-OH 71344 InsWeb Project (Other) XML-OH coverage limit revisions rabdul

4. Installation

The client computer to which the program is deployed must have pre-installed:

-The Common Language Run-Time (CLR) – to support the C# program.

-Oracle 7.3.3. or higher Client Software – to support Oracle Database access.

[End Document]