contract_financial_lines_v1
Contract financial lines refer to specific lines associated with a contract that offer insights into the contract's present and projected financial status, taking into account the invoicing periods. These lines are derived from the contract itself, as well as the related jobs and financial documents providing a comprehensive overview of the contract's financial position.
| Column | Data type | Description |
|---|---|---|
| id | VARCHAR(32) | The unique identifier of the line. |
| type | VARCHAR(50) | Specifies the entity the line belongs to. This field helps distinguish and categorise the line based on the type of entity it is associated with, providing clarity and organisation within the financial records. Possible values: 'Contract', 'Job', 'Invoice', 'CreditNote', 'Quote', 'Proforma', 'AppPayment', 'PurchaseOrder', 'DeliveryNote'. |
| effective_at_date | DATE | The effective date serves as a reference point for understanding the timing of the line's creation. For job lines, this is the created date. For financial document lines, this is the document created date and for contract lines, this is the date of the invoicing period occurrence. |
| created_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of line creation. |
| contract_created_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of contract creation. |
| deleted_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of line deletion. |
| contract_time_zone | VARCHAR(200) | Refers to the specific time zone of the user who created the contract or time zone the contract was created in. |
| contract_reference | VARCHAR(80) | The reference of the contract that is inputted as free text. |
| status | VARCHAR(50) | Specifies the status of the entity the line belongs to. For purchase orders, this is 'Draft', 'Approved', 'Sent' and 'Received'. For other financial documents, this is 'Sent', 'Created' and 'Paid'. For jobs, this is 'Incomplete' and 'Complete'. For contracts, this is 'Forecasted'. |
| contract_contact_name | VARCHAR(256) | The name of the contact associated with the contract. |
| contact_name | VARCHAR(256) | The name of the contact associated with the line. |
| recurrence_pattern_name | VARCHAR(400) | The name of the recurrence pattern used for the contract invoicing period. The field helps to identify and categorise the specific pattern being used, such as custom-defined interval, allowing for easy reference and management of recurring activities within the contract. |
| recurrence_type | VARCHAR(40) | The type of the recurrence used for the contract invoicing period. This field helps to define and identify the frequency and regularity of recurring events or actions within the contract. Possible values: 'Weekly', 'Monthly', 'Monthly Day of Week', 'Daily'. |
| invoicing_period_number | NUMBER(38,0) | Refers to the sequential number or identifier assigned to a specific invoicing period within a contract. It represents the order of the invoicing period in relation to other periods within the contract. The invoicing period number helps to distinguish and track each individual period for invoicing purposes, allowing for accurate record-keeping and financial management. |
| recurrence_occurrence_number | NUMBER(38,0) | Refers to the order or position of a specific instance within a series of recurring events. It helps differentiate and track each occurrence within the recurring pattern, ensuring accurate management of recurring activities in the contract. |
| document_reference | VARCHAR(80) | Document reference associated with the line. |
| job_reference | VARCHAR(88) | Job reference associated with the line. |
| line_order | NUMBER(38,0) | Refers to the sequential position of a specific line within a contract, document or job. |
| description | VARCHAR(2000) | The description of an item on the line. |
| quantity | NUMBER(8,3) | The quantity of the described item on a line. |
| currency | VARCHAR(3) | The ISO 4217 currency code of the line currency. |
| exchange_rate_date | DATE | The date of the exchange rate for currency conversions. |
| unit_price | NUMBER(10,2) | Price per unit expressed in the currency defined in the 'currency' column. |
| unit_price_GBP | NUMBER(10,2) | The unit price converted into GBP based on the exchange rate on the line effective date. |
| net_price | NUMBER(10,2) | The final price a customer pays for a product or service, excluding any additional fees, taxes, or shipping costs; in the currency defined in the 'currency' column. |
| net_price_GBP | NUMBER(10,2) | The net price converted into GBP based on the exchange rate on the line effective date. |
| tax_amount | NUMBER(10,2) | The tax (VAT) amount in the currency defined in the 'currency' column. |
| tax_amount_GBP | NUMBER(10,2) | The tax (VAT) amount converted into GBP based on the exchange rate on the line effective date. |
| tax_percentage | NUMBER(4,2) | The tax (VAT) percentage. |
| unit_tax_amount | NUMBER(10,2) | The tax (VAT) amount per unit, in the currency defined in the 'currency' column. |
| unit_tax_amount_GBP | NUMBER(10,2) | Unit tax (VAT) amount converted into GBP based on the exchange rate on the line effective date. |
| cost_price | NUMBER(10,2) | Cost incurred to produce or acquire a product or service per unit, expressed in the currency defined in the 'currency' column. |
| cost_price_GBP | NUMBER(10,2) | The cost price converted into GBP based on the exchange rate on the line effective date. |
| cost | NUMBER(10,2) | Cost incurred to produce or acquire a product or service, expressed in the currency defined in the 'currency' column. |
| cost_GBP | NUMBER(10,2) | The cost converted into GBP based on the exchange rate on the line effective date. |
| gross_amount | NUMBER(10,2) | Revenue generated from a transaction before any deductions such as taxes, discounts, fees etc. expressed in the currency defined in the 'currency' column. |
| gross_amount_GBP | NUMBER(10,2) | The gross amount converted into GBP based on the exchange rate on the line effective date. |
| discounted_value | NUMBER(10,2) | The discounted value in the currency defined in the 'currency' column. |
| discounted_value_GBP | NUMBER(10,2) | The discounted value converted into GBP based on the exchange rate on the line effective date. |
| discounted_percentage | NUMBER(5,2) | The discounted percentage. |
| markup_value | NUMBER(10,2) | The markup value in the currency defined in the 'currency' column. |
| markup_value_GBP | NUMBER(10,2) | The markup value converted into GBP based on the exchange rate on the line effective date. |
| markup_percentage | NUMBER(5,2) | The markup percentage. |
| nominal_code | VARCHAR(100) | Nominal code assigned to the line. |
| nominal_code_description | VARCHAR(200) | The nominal code description. |
| department_code | VARCHAR(16) | Department code assigned to the line. |
| department_code_description | VARCHAR(200) | The department code description. |
| _document_id | NUMBER(38,0) | The identifier of the document containing the lines. |
| _job_line_id | NUMBER(38,0) | The identifier of the job line. |
| _job_id | NUMBER(38,0) | The identifier of the job associated with the line. |
| _job_group_id | NUMBER(38,0) | The identifier of the job group associated with the line. |
| _contract_id | NUMBER(38,0) | The identifier of the contract associated with the line. |
| _contract_contact_id | NUMBER(38,0) | The identifier of the contact associated with the contract. |
| _contact_id | NUMBER(38,0) | The identifier of the contact associated with the line. |
| _last_sync_at | TIMESTAMP_TZ(9) | The timestamp with time zone offset of when the line record was last refreshed. |