Custom Filters for Advanced Reports

This page lists the common SQL filters for TrakIT Advanced Reports:

    1. WORKORDERID - Refers to the Work Order ID for any work order.
    2. Usage Examples:
    3. WORKORDERID=56548
    4. WORKORDERID>45000
    5. PRIORITY - Refers to the priority of the Work Order. 1=Critical, 2=High, 3=Normal, 4=Low , 5=Very Low, 6=On Hold.
    6. Usage Examples:
    7. PRIORITY<4
    8. PRIORITY=1
    9. CONTCUSID - Refers to the Entity ID of the contractual customer.
    10. Usage Examples:
    11. CONTCUSID=3221
    12. CONTCUSID in (1212,2210,4342)
    13. CONTTYPE - Refers to the type of the container. 0=Dry, 1=Insulated, 2=High Cube, 3=Flat Rack, 4=Tank (Gas), 5=Tank (Liquid), 6=Open Top, 7=Reefer (Live), 8=Reefer (Dry), 9=Half-Height, 10=Platform, 11=Unknown, 255= Not Specified.
    14. Usage Example:
    15. CONTTYPE=7
    16. CONTSIZE - Refers to the size of the container. 20=20 feet, 40=40 feet, 0=Non-container.
    17. Usage Example:
    18. CONTSIZE=20
    19. WOSTATUS - Refers to the status of the work order. 0=Active, 1=Abandoned, 2=Cancelled, 3=Manual Closed, 4= Auto Closed.
    20. Usage Examples:
    21. WOSTATUS=0
    22. WOSTATUS<>2 (to pull out all work orders except the ones that are cancelled)
    23. WEIGHT - Refers to the weight of the container/load.
    24. Usage Example:
    25. WEIGHT>20000
    26. CUSTOMESCORT - Is set to 1 if work order requires a customs escort, otherwise it is set to 0.
    27. Usage Example:
    28. CUSTOMESCORT=1
    29. TOTALDAYS - Refers to the total number of free days set for the work order.
    30. Usage Example:
    31. TOTALDAYS<20
    32. CONTCUSCODE - Refers to the entity code of the contractual customer.
    33. Usage Example:
    34. CONTCUSCODE='PQR-002'
    35. CORTYPE - Refers to the type of the workflow. 0=Import, 1=Export, 2=Round Trip.
    36. Usage Examples:
    37. CORTYPE=0
    38. CORTYPE<2
    39. OPCODE - Refers to the carrier code.
    40. Usage Example:
    41. OPCODE='ML'
    42. ACTIVEALERTS - Refers to the number of active alerts set in a work order.
    43. Usage Example:
    44. ACTIVEALERTS>0
    45. OWNCONT - Is set to 1 if the container belongs to the shipper, otherwise it is set to 0.
    46. Usage Example:
    47. OWNCONT=1
    48. DOORDEL - Is set to 1 if the container/load is to be door-delivered to the customer, otherwise it is set to 0.
    49. Usage Example:
    50. DOORDEL=1
    51. HAZARD - Is set to 1 if the container/load contains hazardous goods, otherwise it is set to 0.
    52. Usage Example:
    53. Hazard=1
    54. RESP - Refers to the User ID of the user responsible for the work order.
    55. Usage Example:
    56. RESP='AWA105'
    57. CREATEDON - Refers to the date on which the work orders are created.
    58. Usage Examples:
    59. Year(CREATEDON)=2012
    60. Month(CREATEDON)=5
    61. VSLNAME - Refers to the name of the vessel engaged for a shipment.
    62. Usage Example:
    63. VSLNAME='MAVERICK'
    64. VOYNUM - Refers to the voyage number for a shipment.
    65. Usage Example:
    66. VOYNUM='MS1234'
    67. LASTEVENTDATE - Refers to the latest date populated for an event. Dates must be specified in YYYYMMDD format.
    68. Usage Example:
    69. LASTEVENTDATE='20121219'
    70. LESEQCODE - Refers to the code of the last event populated in a work order.
    71. Usage Example:
    72. LESEQCODE='DIS'

* The above filters can also be combined using AND or OR between multiple SQL Clauses. Clauses joined with OR must be enclosed in brackets when combining with other clauses using AND. Example:

VSLNAME='MAVERICK' AND LESEQCODE='DIS' AND (OWNCONT=1 OR DOORDEL=1)