Omni GRS

Global Reservation System (GRS) is a proprietary application written in the "Informix 4GL" (I-4GL) programming language used by Omni employees in Omaha (call center) and at hotels across North America to create, modify, and cancel reservations in Omni's proprietary Informix CRS database. The application itself is 53K lines of source code and uses 11K more lines of "Omni common" I-4GL.

Interfaces

No automated systems "interface" with GRS. GRS is an application humans use to view and manipulate data in CRS.

• Omni CRS: GRS manipulates dozens of tables in Omni CRS.
• Credit cards live in a separate, encrypted database with a web service API. Since I-4GL isn't capable of SOA operations GRS executes a Perl program that handles all read and write SOA calls to that database to read and write credit cards.

Misc Statistics

Reservation volumes

I could spend a few days in here slicing questions and answers differently. Of what I came up with, these might be the most helpful.

Q: How many book/modify/cancel events occur in GRS each day for any reservation booked anywhere?

Let's pull last week, which is probably typical. To get just the GRS users we'll join to the user table.

select extend(last_mod_when, year to day) date, count(*)
from res_transact_ar r, user u
where r.transaction_type = 'R'
and r.last_mod_agent = u.user_id
and last_mod_when > '2007-05-14 00:00:00'
and last_mod_when < '2007-05-21 00:00:00'
group by 1
order by 1

2007-05-14            10041
2007-05-15             8471
2007-05-16             8386
2007-05-17             7760
2007-05-18             8796
2007-05-19             2112
2007-05-20             2430

Q: For that week what is the total number of reservation book/modify/cancel events per day, occurring anywhere, of reservations that were originally booked in GRS or PMS?

select extend(last_mod_when, year to day) date, count(*)
from res_transact_ar
where transaction_type = 'R'
and tran_source like '%-PH'
and last_mod_when > '2007-05-14 00:00:00'
and last_mod_when < '2007-05-21 00:00:00'
group by 1
order by 1

2007-05-14            38151
2007-05-15            33239
2007-05-16            34479
2007-05-17            33035
2007-05-18            37055
2007-05-19            20324
2007-05-20            21255

Q: Where were those reservations originally booked?

select tran_source, count(*)
from res_transact_ar
where transaction_type = 'R'
and tran_source like '%-PH'
and last_mod_when > '2007-05-14 00:00:00'
and last_mod_when < '2007-05-21 00:00:00'
group by 1
order by 1

• 33,393 came from "OMNIREZ-PH" (the OmniRez call center).
• The other 184,145 reservations came from "PROP-PH", where PROP is one of 36 different hotels that use GRS to book reservations.

Q: Of the GRS events, what was the reservation status after the action occurred?

select resv_status, count(*)
from res_transact_ar r, user u
where r.transaction_type = 'R'
and r.last_mod_agent = u.user_id
and last_mod_when > '2007-05-14 00:00:00'
and last_mod_when < '2007-05-21 00:00:00'
group by 1
order by 1

         1            21896          (new booking)
         2            23174          (adjusted)
         3             2864          (cancelled)
         6               58          (no show)
         7                4          (reinstated)