ticket numbers

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.

 Example Trade:

 Part A - Primary Ticket

Ticket Number: 100000-000

Product: IBM

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:

SELECT tckmast.primary_ticket_number, tckmast.revision, tckdtl.desk, acct_code, tckdtl.transaction_type, tckdtl.product_id, quantity, price, tckdtl.other_party_code, stage, tckdtl.trade_date, tckdtl.settlement_date, tckmast.is_cancelled, 
FROM tckmast, tckdtl
WHERE tckmast.trade_date >='7/6/2000'
AND tckmast.trade_date <='7/6/2000'
AND (tckdtl.primary_ticket_number = tckmast.primary_ticket_number)
and (tckdtl.revision <= tckmast.revision)
and ((tckmast.is_cancelled = 0) and 
     (tckdtl.is_cancelled = 0))

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

(((tckmast.is_deleted = 1) and 
  (tckdtl.cancelled_revision = tckmast.revision)) 
or ((tckmast.is_cancelled = 0) and 
    (tckdtl.is_cancelled = 0)))
and (tckdtl.primary_ticket_number = tckmast.primary_ticket_number) 
and (tckdtl.revision <= tckmast.revision)

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.

SELECT tckmast.primary_ticket_number, tckmast.revision, tckdtl.desk, acct_code, tckdtl.transaction_type , tckdtl.product_id , quantity, price, tckdtl.other_party_code, stage, tckdtl.trade_date, tckdtl.settlement_date , tckmast.is_cancelled, 
FROM tckmast, tckdtl
WHERE tckmast.trade_date >='7/6/2000'
AND tckmast.trade_date <='7/6/2000'
AND (tckdtl.primary_ticket_number = tckmast.primary_ticket_number)
and (tckdtl.revision <= tckmast.revision)
and
(
((tckmast.is_cancelled = 0) and (tckdtl.is_cancelled = 0))
or 
((tckmast.is_cancelled = 1) and ((cancelled_revision >= tckmast.revision) or (cancelled_revision =""))) 
)

Last updated: 04/28/05