How does an alter work

The part of the filter that performs the join is t1.mobileno = t2.mobile AND t2.mobile = t3.mobileno. For that to be sensible, it is likely that you need two of the following three statements to be true:

1. mobileno is unique in traffic
2. mobile is unique in details
3. mobileno is unique in registernumbers

Because the join is returning more rows than you expect, I guess this is not the case. For further help we'll need to know the DDL for the tables (in particular the primary keys)

---EDIT

on second thoughts, I think

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)

needs to have some more brackets:

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND ( latitude > 48 
           OR (latitude < 52 AND longitude > 2) 
           OR (latitude > 52 AND longitude < 2) )

although some of the conditions seems to be redundant

The function based index you have created uses same functions but in reverse order than your query, this prevent oracle from using this index.

Since WHERE clause of your query contains:

   AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) 
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z')

you must create the index in this way:

CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    column3,
    TRIM(UPPER(column4)),
    TRIM(UPPER(column5))
  )

This still doesn't guarantee that your query will use that index, that depends also on selectivity of the query, if the query is highly selective (returns only a few rows), then oracle will probably use the index, but when the query is not selective (returns thousands of rows), than the optimiser will probably prefer full table scan over using the index.