Q: How are ticket numbers assigned, and how can I get all the tickets associated with a particular trade?
A: The primary (or master) part of a trade is linked to the allocations (or details) by a primary ticket number (primary_ticket_number) assigned when the trade is booked. Primary ticket numbers typically start at 100000.
Along with the primary ticket number is a revision number. Revision numbers are incremented after a trade ticket has been edited in order to maintain a proper audit trail of changes to a trade. A new trade ticket will have a primary ticket number of 100000 and a revision number of 000. Together, these two fields comprise a unique primary ticket identifier, i.e. 100000-000.
Trade ticket allocations/details are stored internally using a three part key consisting of the primary ticket number, the detail ticket number, and a revision number. A detail ticket identifier would look like 100000-001-000. The revision number of a detail ticket corresponds to the primary’s revision number when the detail trade was inserted or updated. It is important to note that a detail ticket will not always have the same revision as its corresponding master.
Part A - Primary Ticket
Ticket Number: 100000-000
Total Quantity: 1,100
Part B - Allocations
Tickets are stored in two tables, the master table, tckmast, and the detail table, tckdtl. For a particular primary_ticket_number, if a trade has not been canceled/deleted by a user, you can retrieve the master and ticket records using the primary_ticket_number and checking for is_cancelled (yes, that is how it is spelled) = 0. The following query is an example:
To see the latest state of a set of tickets, i.e. active tickets and tickets that were explicitly canceled (as opposed to those that were canceled because of an edit), you can use the is_deleted flag on the ticket master table. This flag indicates trades that were explicitly deleted/canceled (i.e. not canceled as part of an edit/save operation). The following can be used in a WHERE clause to isolate these trades
To see all revisions, i.e. an audit trail, of a set of tickets, you need to use a combination if is_cancelled and cancelled_revision. The cancelled_revision indicates the revision of the master when the detail ticket was canceled.
Last updated: 04/28/05