Skip to main content

How to Track Authorisation Method

Objective​

Track every authorisation of an enquiry along with the method (manual or auto) and the order in which the authorisations happened.

View​

  • View Name In BigQuery: authorisation_method_view

  • Dataset In BigQuery: bqDatalake

Columns​

Column NameDescription
EnquiryIdUnique ID of the enquiry.
EnquiryNumberEnquiry number.
AccountIdAccount ID.
ServiceUnitIdService unit ID.
LeaseIdLease company ID.
GroupNameSupplier group name.
SupplierNameSupplier name.
LeaseCompanyNameLease company name.
VRMVehicle registration mark (plate number).
Vehicle_MakeVehicle make (brand).
Vehicle_ModelVehicle model.
AuthorisedByFull name of the user or system who authorised the enquiry.
AuthorisationMethodHow the authorisation happened: 'AUTO' or 'MANUAL'.
AuthorisedOnTimestamp when the authorisation occurred.
AuthorisationSequenceSequence number of authorisation (1 = first/initial authorisation, 2nd, 3rd ...).

Logic Behind Authorisation Method​

  • If the authoriser is:
    • 'System Admin' and all the system rules are satisfied,
      ➔ then AuthorisationMethod = 'AUTO'.
    • Or if an user authorises it manually from V-customer,
      ➔ AuthorisationMethod = 'MANUAL'.

How Sequence Number (AuthorisationSequence) is Calculated​

  • For each EnquiryId,
  • All AUTHORISED events are ordered by their timestamp (earliest first).
  • Then row numbers are assigned starting from 1.

How to Fetch Data​

Example 1: Get all authorisations for a specific Enquiry​

SELECT *
FROM `bqDataLake.authorisation_method_view`
WHERE EnquiryId = 'abc123'
ORDER BY AuthorisationSequence ASC;

Example 2: Get only the latest (most recent) authorisation for an Enquiry​

SELECT *
FROM `bqDataLake.authorisation_method_view`
WHERE EnquiryId = 'abc123'
ORDER BY AuthorisationSequence DESC
LIMIT 1;

Example 3: Find all Enquiries that were only AUTO-authorised (no manual)​

SELECT *
FROM `bqDataLake.authorisation_method_view`
WHERE EnquiryId IN (
  SELECT EnquiryId
  FROM `bqDataLake.authorisation_method_view`
  GROUP BY EnquiryId
  HAVING (COUNT(*) = 1 AND MAX(AuthorisationMethod) = 'AUTO')
     OR COUNTIF(AuthorisationMethod = 'AUTO') = COUNT(*)
);

Example 4: Find all Enquiries where the first authorisations were AUTO​

SELECT * FROM `bqDataLake.authorisation_method_view`
where AuthorisationSequence = 1 and AuthorisationMethod = 'AUTO'

✨ Authorisation Process Flow​


Status: Accepted
Category: Protected
Authored By: Vignesh Kanthimathinathan on Apr 29, 2025