jobs_v1
Job refers to a specific task, assignment, or work order that needs to be completed by a field service technician or a resource.
| Column | Data type | Description |
|---|---|---|
| id | NUMBER(38,0) | The identifier of the job. |
| reference | VARCHAR(88) | The reference of the job that is inputted as free text. |
| category_name | VARCHAR(100) | The job's category name. |
| type_name | VARCHAR(200) | The type of the job. |
| description | VARCHAR(2000) | The job description that is inputted as free text. |
| group_reference | VARCHAR(80) | The reference of the job group that is inputted as free text. |
| flag | VARCHAR(100) | The current flag applied to the job. The flag is a marker or label used to categorise, prioritise, or highlight specific jobs or tasks. |
| flag_colour | VARCHAR(40) | The textual representation of the current flag colour set in the flag definition settings. |
| flag_comment | VARCHAR(2000) | Additional comments or notes associated with the current flag. |
| flag_date | TIMESTAMP_TZ(9) | The timestamp with the timezone offset of when the current flag was set to take effect. |
| flag_owner | VARCHAR(100) | The person responsible for setting or managing a flag. It is used to assign ownership and accountability for flagged items. |
| purchase_order_number | VARCHAR(80) | The purchase order number (or order number) supplied by the contact to complete the job, inputted as free text. |
| resource_group_name | VARCHAR(100) | The name of the resource's group. |
| secondary_persons | VARCHAR(1000) | Represents the names of secondary persons associated with a job. These individuals may be additional contacts or stakeholders related to the job. |
| secondary_persons_at_parent | VARCHAR(1000) | Represents the names of secondary persons associated with the parent contact on the job. It indicates any additional contacts or stakeholders related to the parent entity. |
| result_resource_notes | VARCHAR(4000) | Relevant notes or comments provided by the resource on the job result, inputted as free text. |
| result_customer_name | VARCHAR(100) | The name of the customer associated with the job's result. It indicates the customer who received the outcome or deliverable of the job. |
| result_customer_notes | VARCHAR(4000) | Specific notes or comments related to the customer's feedback on the job's result, inputted as free text. It may include additional instructions or feedback provided by the customer. |
| result_backoffice_notes | VARCHAR(1000) | Relevant notes or comments provided by the backoffice staff on the job result, inputted as free text. |
| is_result_actioned | VARCHAR(3) | Indicates if the job result was actioned or not. Users can mark a job as "Actioned" once they have performed the necessary tasks or checks associated with the job. This field can be used for various purposes, such as filtering jobs or triggering actions related to checking jobs, financial data or invoicing. Possible values: 'Yes', 'No'. |
| result_actioned_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of when the job result was actioned. |
| result_actioned_by | VARCHAR(100) | The name of the web user who actioned the job result. |
| status | VARCHAR(50) | The status of the job. Possible values: 'New', 'Scheduled', 'Unscheduled', 'Sent', 'Read', 'Accepted', 'Refused', 'On the way', 'Completed', 'Started', 'Suspended', 'Completed', 'Completed with issues', 'Cancelled', 'Rescheduled', 'Late start', 'Late finish'. |
| status_comment | VARCHAR(500) | The comment on the job status that is inputted as free text. |
| is_confirmed | VARCHAR(3) | Determines if the job is confirmed. |
| is_job_contracted_out | VARCHAR(3) | Indicates if the job is contracted out to another BigChange user (i.e, a Contractor) set up through the collaboration network. |
| is_job_contracted_in | VARCHAR(3) | Indicates if the job has been sent from another BigChange user set up through the collaboration network. |
| is_invoiced | VARCHAR(3) | Indicates if the job is already invoiced. |
| status_accepted_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of job status change to Accepted. This occurs when the resource accepts the job on their device. |
| status_on_the_way_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of job status change to On the way. This occurs when the resource indicates 'start driving' on their device. |
| status_started_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of job status change to Started. This occurs when the resource starts the job on their device. |
| status_completed_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of job status change to Completed. This occurs when the resource completes the job on their device. |
| status_completed_with_issues_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of job status change to Completed with issues. This occurs when the resource completes the job with issues on their device. |
| created_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job creation. |
| created_at_date | DATE | The date of the job creation. YYYY-MM-DD format. |
| scheduled_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job scheduling. |
| planned_start_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job planned start. |
| actual_start_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job actual start on device. |
| planned_end_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job planned end. |
| due_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset when the job is due. |
| actual_end_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job actual end on device. |
| actual_end_at_date | DATE | The date of the job end. YYYY-MM-DD format. |
| is_late_start | VARCHAR(3) | Indicates if the job was started with delay. |
| created_to_scheduled_in_minutes | NUMBER(18,0) | The time interval in minutes between the job creation and scheduling. |
| created_to_scheduled_in_hours | NUMBER(38,2) | The time interval in hours between the job creation and scheduling. |
| scheduled_to_planned_in_minutes | NUMBER(18,0) | The time interval in minutes between the job scheduling and planned start. |
| scheduled_to_planned_in_hours | NUMBER(38,2) | The time interval in hours between the job scheduling and planned start. |
| planned_to_actual_start_in_minutes | NUMBER(18,0) | The time interval in minutes between the job planned start and actual start. |
| planned_to_actual_start_in_hours | NUMBER(38,2) | The time interval in hours between the job planned start and actual start. |
| actual_start_to_actual_end_in_minutes | NUMBER(18,0) | The time interval in minutes between the job actual start and end. |
| actual_start_to_actual_end_in_hours | NUMBER(38,2) | The time interval in hours between the job actual start and end. |
| response_time_in_hours | NUMBER(38,4) | The time interval in hours between the job creation and actual start. |
| planned_duration_in_minutes | NUMBER(38,0) | The job planned duration in minutes. |
| planned_duration_in_hours | NUMBER(38,2) | The job planned duration in hours. |
| planned_driving_duration_in_minutes | NUMBER(38,0) | The planned driving duration in minutes. |
| planned_driving_duration_in_hours | NUMBER(38,2) | The planned driving duration in hours. |
| total_planned_duration_in_hours | NUMBER(38,2) | The sum of the job planned duration and the planned driving duration in hours. |
| actual_duration_in_seconds | NUMBER(38,0) | The actual time taken to complete the job from beginning to end, in seconds, not including any periods when the job was paused. |
| actual_duration_in_minutes | NUMBER(38,2) | The actual time taken to complete the job from beginning to end, in minutes, not including any periods when the job was paused. |
| actual_duration_in_hours | NUMBER(38,2) | The actual time taken to complete the job from beginning to end, in hours, not including any periods when the job was paused. |
| actual_driving_duration_in_seconds | NUMBER(38,0) | The length of time, in seconds, from when the resource initiated 'Start driving' to 'Start job' on their device. |
| actual_driving_duration_in_minutes | NUMBER(38,2) | The length of time, in minutes, from when the resource initiated 'Start driving' to 'Start job' on their device. |
| actual_driving_duration_in_hours | NUMBER(38,2) | The length of time, in hours, from when the resource initiated 'Start driving' to 'Start job' on their device. |
| total_actual_duration_in_hours | NUMBER(38,2) | The sum of the actual job duration and the actual driving duration in hours. |
| service_level_name | VARCHAR(80) | The service level agreement name inputted by the user against contact. |
| job_duration_in_minutes | NUMBER(18,0) | The time interval between job's actual start and job's actual end in minutes. |
| job_age_in_days | NUMBER(9,0) | The time interval between current date and the job's creation date in days. |
| time_to_complete_in_minutes | NUMBER(18,0) | The job's duration in minutes. The difference between the job's actual end and actual start. |
| time_to_complete_in_hours | NUMBER(38,2) | The job's duration in hours. The difference between the job's actual end and actual start. |
| time_to_complete_in_days | NUMBER(38,2) | The job's duration in days. Difference between the job's actual end and actual start. |
| time_outside_sla_in_minutes | NUMBER(18,0) | The time the job exceeds agreed service level in minutes. |
| time_outside_sla_in_hours | NUMBER(38,2) | The time the job exceeds agreed service level in hours. |
| time_outside_sla_in_days | NUMBER(38,2) | The time the job exceeds agreed service level in days. |
| driving_distance_km | NUMBER(6,2) | The distance, in kilometers, recorded from the moment the resource initiated 'Start driving' to 'Start job' on their device. |
| driving_distance_mi | NUMBER(38,2) | The distance, in miles, recorded from the moment the resource initiated 'Start driving' to 'Start job' on their device. |
| odometer_unit | VARCHAR(10) | The odometer's unit type. |
| contact_name | VARCHAR(256) | The name of the contact associated with the job. |
| parent_contact_name | VARCHAR(256) | The parent contact of the contact associated with the job. |
| primary_person | VARCHAR(256) | The primary person of the contact. |
| job_site | VARCHAR(423) | The job site of the contact. |
| contact_reference | VARCHAR(80) | The reference of the contact that is auto populated or inputted as free text. |
| contact_group | VARCHAR(100) | The contact group associated with the job. |
| resource_name | VARCHAR(100) | The resource name of the resource assigned for the job. |
| vehicle_registration | VARCHAR(100) | The registration number of the vehicle. |
| vehicle_group | VARCHAR(100) | The vehicle group of the vehicle. |
| is_recurring | VARCHAR(3) | Indicates if the job is recurring. |
| is_financially_complete | VARCHAR(3) | Indicates if the job is marked as financially complete. |
| financially_completed_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of the job financial completion. |
| financially_completed_at_date | DATE | The date of the job financial completion. |
| total_net_selling_price_from_credit_notes_GBP | NUMBER(38,2) | The total net selling price from the credit note associated with the job, converted into GBP based on exchange rates on the days that the source documents are created. |
| total_net_selling_price_source | VARCHAR(8) | Indicates if the source of 'total net selling price from invoices' is derived from an Invoice or a Proforma invoice. |
| total_net_selling_price_from_invoices_GBP | NUMBER(38,2) | The total net selling price from invoices converted into GBP based on exchange rates on the days that the source documents are created. |
| actual_revenue_GBP | NUMBER(38,2) | The invoiced amount converted into GBP based on exchange rates on the days that the source documents are created. |
| revenue_left_to_invoice_GBP | NUMBER(38,2) | The value of job that is not invoiced yet, converted into GBP based on exchange rates on the days that the source documents are created. |
| total_net_selling_price_from_job_lines_GBP | NUMBER(38,2) | The total net selling price from job lines converted into GBP based on exchange rates on the days that the source documents are created. |
| total_net_cost_price_GBP | NUMBER(38,2) | The total net cost price converted into GBP based on exchange rates on the days that the source documents are created. |
| profit_from_job_lines_GBP | NUMBER(38,2) | The profit from job lines converted into GBP based on exchange rates on the days that the source documents are created. This value represents profit calculated using job financial lines only and not invoices. |
| margin_from_job_lines_GBP | NUMBER(38,2) | The margin from job lines converted into GBP based on exchange rates on the days that the source documents are created. This value represents margin calculated using job financial lines only and not invoices. |
| actual_profit_GBP | NUMBER(38,2) | The actual profit converted into GBP based on exchange rates on the days that the source documents are created. This value represents profit calculated using invoices and also excludes any credit notes. |
| actual_margin_GBP | NUMBER(38,2) | The actual margin converted into GBP based on exchange rates on the days that the source documents are created. This value represents margin calculated using invoices and also excludes any credit notes. |
| cost_from_purchase_orders_GBP | NUMBER(38,2) | The cost from purchase orders converted into GBP based on exchange rates on the days that the source documents are created. |
| geolocation_latitude | FLOAT | The geolocation latitude of the contact. This is the latitude of the centroid of the shape describing contact location on the map. |
| geolocation_longitude | FLOAT | The geolocation longitude of the contact. This is the longitude of the centroid of the shape describing contact location on the map. |
| geolocation | VARCHAR(64) | The concatenated geolocation latitude and longitude of the contact associated with the job. |
| _job_group_id | NUMBER(38,0) | The identifier of the job group the job belongs to. |
| _sales_opportunity_id | NUMBER(38,0) | The identifier of the sales opportunity associated with the job. |
| _resource_id | NUMBER(38,0) | The identifier of the resource that has been assigned to the job. |
| _contact_id | NUMBER(38,0) | The identifier of the contact associated with the job. |
| _contract_id | NUMBER(38,0) | The identifier of the contract associated with the job. |
| _vehicle_id | NUMBER(38,0) | The identifier of the vehicle that has been assigned to the job. |
| _last_sync_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of when the job record was last refreshed. |