This page is specific to database objects created in the warehouse when prepared for a client.
This content is intended for members of the reporting team and support and implementation teams.
bp_currencies materialized view
Purpose
- This materialized view is created to reduce the joins with the ebilling_ro.currencies foreign table.
- The currencies data is cached in the warehouse schema using this materialized view.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
Foreign Table: ebilling_ro.currencies -> ebilling.currencies in the ebilling master database
|
Column Name |
Data Type |
Unique Key |
Description |
Source Field |
| uuid |
uuid |
Yes |
Id of the currency |
currencies.id |
| currency_code |
character varying |
Currency Code |
currencies.currency_code |
|
| currency_symbol |
character varying | Currency Symbol |
currencies.currency_symbol |
bp_accounts materialized view
Purpose
- This materialized view is created to reduce the joins with the ebilling.accounts view that internally references a foreign table.
- The warehouse's attributes for the client are cached in the warehouse schema using this materialized view. This view has only 1 row for the client.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name |
Data Type |
Unique Key | Description | Source Field |
id |
uuid? |
Yes |
Id of the client account | accounts.id |
suppress_diversity_info |
boolean |
|
Specifies whether to supress diversity information | accounts.suppress_diversity_info |
| currency_id | uuid |
|
Id of the client currency | currencies.id |
client_base_currency_codecurr |
character varying |
|
Client currency code | currencies.currency_code |
| client_base_currency_symbol | character varying | Client currency symbol | currencies.currency_symbol |
bp_users materialized view
Purpose
- This materialized view stores the IDs and names of all the users who made invoice line item adjustments.
- The details of the users who performed the adjustments are cached in the warehouse schema using this materialized view.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name |
Data Type |
Unique Key | Description | Source field |
id |
uuid |
Yes | Id of the user | users.id |
user_name |
character varying | Name of the user | users.name |
bp_staff_classifications materialized view
Purpose
- This materialized view stores the union of staff classifications from ebilling master and the client's private staff classifications.
- The staff classifications are cached in the warehouse schema using this materialized view.
- This materialized view is refreshed daily as part of the nightly refresh process.
Query Joins
Data Dictionary
bp_projects view
Purpose
- This view contains the fields used in the warehouse from ebilling.client_projects.
- This view is just a wrapper on the ebilling.client_projects table from the client's private database.
Query Joins
Data Dictionary
| Column Name |
Data Type |
Unique Key | Description | Source Field |
id |
uuid |
Yes |
psb_client_project id, ID of the record, primary key |
client_projects.id |
account_id |
character varying | id of the vendor in billingpoint | client_projects.account_id | |
client_id |
character varying | id of the client vendor association | client_projects.client_id | |
| client_account_id | uuid | id of the client account in billingpoint? | client_projects.client_account_id | |
| name | character varying |
Client project name | client_projects.name | |
| number | character varying |
|
client project number | client_projects.number |
| created_at | timestamp without time zone | date on which record got created in BillingPoint | client_projects.created_at | |
| updated_at | timestamp without time zone |
|
date on which record got updated last time in BillingPoint |
client_projects.updated_at |
| remote_key | character varying |
|
id of VATM record in AppBuilder |
client_projects.remote_key |
| type | character varying | type of client project | client.projects.type |
bp_billing_authorization_requests view
Purpose
- This view stores the fields used in the warehouse from the ebilling.billing_authorization_requests table.
- This view is just a wrapper on the ebilling.client_projects table from the client's private database.
Query Joins
Data Dictionary
bp_invoices view
Purpose
- This view stores the fields used in the warehouse from the ebilling.invoices table.
- This view is just a wrapper on the ebilling.invoices table from the client's private database.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| id | uuid | Yes | Invoice id, IF of the record, primary key | invoices.id |
| Client_id | uuid | Id of the client | invoices.client_id | |
| payment_terms_id | uuid | Id of the payment term | invoices.payments_terms_id | |
| Office_id | uuid | Id of office | invoices.office_id | |
| Account_id | uuid | Id of the vendor account | invoices.account_id | |
| Billing_authorization_id | uuid | Id of BAR | invoices.billing_authorization_id | |
| Currency_id | uuid | Id of the invoice currency | invoices.currency_id | |
| Client_account_id | uuid | Id of the client account | Invoices.client_account_id | |
| Project_id | uuid | Id of the client project | Invoices.client_project_id | |
| Client_supported_currency_id | uuid | Client supported currency id for the BAR | invoices.client_supported_currency_id | |
| Account_supported_currency_id | uuid | Vendor supported currency id for the BAR | invoices.account_supported_currency_id | |
| Client_project_bar_id | uuid | BAR id of the client project | Invoices.client_project_bar_id | |
| Invoice_number | character varying | Vendor invoice number | invoices.invoice_number | |
| Invoice_date | timestamp without time zone | Invoice date | Invoices.invoice_date | |
| Po_number | character varying | Client PO # from vendor BAR | Invoices.po_number | |
| Discount_percentage | Numeric (20,6) | Invoice discount in percentage | invoices.discount_percentage | |
| Terms | text | Terms of invoice | invoices.terms | |
| Notes | text | Invoices description in LEDES | invoices.notes | |
| Sub_total | Numeric (20,6) | Invoice sub total | invoices.sub_total | |
| discount | Numeric (20,6) | Invoice discount | invoices.discount | |
| Tax_amount | Numeric (20,6) | Invoice tax amount | invoices.tax_amount | |
| Invoice_total | Numeric (20,6) | Invoice total in submitted currency | invoices.invoice_total | |
| Archive_number | character varying | Archive number if this Record is archives | invoices.archive_number | |
| Archives_at | Timestamp w/o time zone | Date on which invoice was archived in BillingPoint | invoices.archived_at | |
| Deleted_at | Timestamp w/o time zone | Date on which invoice was marked for soft delete | Invoices.deleted_at | |
| created_at | Timestamp w/o time zone | Date on which invoice got created in BillingPoint | invoices.created_at | |
| Updated_at | Timestamp w/o timezone | Date on which the invoice got updated in BillingPoint | Invoices.updated_at | |
| Due_date | date | Due date of invoice | invoices.due_date | |
| Last_invoice_status | Character varying | Last status of invoice | invoices.last_invoice_status | |
| Discount_type | character varying | Type of invoice discount - Fee, Expense | invoices.discount_type | |
| Billing_start_date | Timestamp w/o time zone | Billing start date | Invoices.billing_start_date | |
| Billing_end_date | Timestamp w/o time zone | Billing end date | invoices.billing_end_date | |
| state | character varying | Invoice BP phase (Failed, Pending Approval, Approved, Disputed, Voided, Paid, Draft) | invoices.state | |
| Invoice_fees | numeric (20,6) | Fees total in submitted currency | Invoices.invoice_fees | |
| Invoice_expenses | Numeric (20,6) | Expenses in total submitted currency | invoices.invoice_expenses | |
| resubmitted | Boolean DEFAULT false | True if invoice was resubmitted | invoices.resubmitted | |
| remote_key | character varying | AB Invoice atom ID | Invoices.remote_key | |
| Invoice_orig_amount | Numeric (20,6) | Invoice total on first time submission in submitted currency | invoices.invoice_orig_amount | |
| Client_spot_rate | Numeric (20,) | Spot rate of client on invoice | Invoices.client_spot_rate | |
| Account_spot_rate | Numeric (20,6) | Spot rate of vendor on invoice | Invoices.client_spot_rate | |
| Received_date | Timestamp w/o time zone | Date invoice first went to phase = Pending Approval | Invoice.received_date | |
| Invoice_orig_fees | Numeric (20,6) | Invoice total fees on first time submission in submitted currency | invoices.invoice_orig_fees | |
| Invoice_orig_discount | Numeric (20,6) | Invoice total expenses on first time submission in submitted currency | Invoices.invoice_orig_expenses | |
| Invoice_orig_discount | Numeric (20,6) | Invoice total discount on first time submission in submitted currency | invoices.invoice_orig_discount | |
| Submission_type | Character varying | Is this LEDES 98b, 98bi, xml, via UI? {LEDES | Manual} Currently, its “LEDES” for all LEDES formats and “Manual” for manually created invoices | Invoices.submission_type | |
| Header_short_pay_adjust | Numeric (20,6) | Not in use | Invoices.header_short_pay_adjustment | |
| Line_item_short_pay_adjustment | Numeric (20,6) | Not in use | invoices.line_item_short_pay_adjustment | |
| Short_pay_adjustment_total | Numeric (20,6) | Not in use | invoices.short_pay_adjustment_total | |
| Pay_total | Numeric (20,6) | Its invoice_total - short_pay_total. Since short pay may not be in use, so it’s most likley qual to invoice_total | invoices.pay_total | |
| Approved_date | Timestampe w/o time zone | Date invoice approval is completed bu all client approvers in Onit and invoices is readuy for payment processing | invoices.approved_date | |
| Edited_since_discputed | Boolean DEFAULT false | Billing authorization requests edited since disputed | Invoices.edited_sinces_disputed | |
| ap_details | text | Legacy data. THis waas used by AppBuilder to populate Legal Entity info. Replaced by current Legal Entity process | Invoices.ap_details | |
| Orig_fee_discount | Numeric (20,6) | Fee discount in submitted currency | Invoices.fee_discount | |
| Fee_discount | Numeric (20,6) | Expense discount in submitted currency | invoices.expense_discount | |
| Header_dispute_adjustment | Numeric (20,6) | invoices.header_dispute_adjustment | ||
| fee_line_item_dispute_adjustment | Numeric (20,6) | Fee line item discpute adjustment in submitted currency | invoices.fee_line_item_dispute_adjustment | |
| Expense_line_item_dispute_adjustment | Numeric (20,6) | Expense line item dispute adjustment in submitted currency | invoices.expense_line_item_dispute_adjustment | |
| Dispute_adjustment_total | Numeric (20,6) | Total invoice adjustments in submitted currnecy | invoices.dispute_adjustment_total | |
| Adjustments_enabled | Boolean DEAULT true | True if adjustments are enabled, otherwise false | invoice.adjustment_enabled | |
| Fee_header_dispute_adjustment | Numeric (20,6) | Total header fee adjustments in submitted currency | Invoices.fee_header_dispute_adjustment | |
| Expense_header_dispute_adjustment | Numeric (20,6) | Total header expense adjustments in submitted currency | invoices.expense_header_dispute_adjustment | |
| Vat_processing | Boolean DEFAULT false | invoices.vat_processing |
bp_invoice_line_items view
Purpose
- This view stores the fields used in the warehouse from the ebilling.invoice_line_items table
- This view is just a wrapper on the ebilling.invoice_line_items table from the client's private database
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Id | uuid | Yes | Invoice line item id, ID of the record, primary key | Invoice_line_items.id |
| Invoice_id | uuid | Id of the invoice | Invoice_line_items.invoice_id | |
| Account_id | uuid | Id of the vendor | Invoice_line_items.account_id | |
| Project_id | uuid | Id of the client project | Invoice_line_items.project_id | |
| Time_keeper_id | uuid | Id of the timekeeper | Invoice_line_items.time_keeper_id | |
| Time_keeper_rate_id | uuid | Id of the time keeper rate | Invoice_line_items.time_keeper_rate_id | |
| Client_account_id | uuid | Id of the client account | Invoice_line_items.client_account_id | |
| parent_id | uuid | Id of the parent line item | Invoice_line_items.parent_id | |
| Adjuster_id | uuid | Id of the adjuster user | Invoice_line_items.adjuster_id | |
| Item_description | text | Description of the line item | Invoice_line_items.item_description | |
| Item_unit_cost | Numeric (20,6) | Unit cost of the line item | Invoice_line_items.item_unit_cost | |
| Itme_quantity | Numeric (20,6) | Unit quantity of the line item | Invoice_line_items.item_quantity | |
| archive_number | Character varying | Archive number of the line item | Invoice_line_items.archive_number | |
| Archived_at | Timestamp w/o time zone | Date on which line item got archived in BillingPoint | Invoice_line_items.archived_at | |
| Deleted_at | Timestamp w/o time zone | Date on which line item got deleted in BillingPoint | Invoice_line_items.deleted_at | |
| Created_at | Timestamp w/o time zone | Date on which line item got created in BillingPoint | Invoice_line_items.created_at | |
| Updated_at | Timestamp w/o time zone | Date on which line item got updated in BillingPoint | Invoice_line_items.updated_at | |
| Line_item_total | Numeric (20,6) | Line item total in vendor base currency | Invoice_line_items.line_item_total | |
| discount | Numeric (20,6) | Discount | Invoice_line_items.discount | |
| Timekeeper_rate | Numeric (20,6) | Timekeeper rate | Invoice_line_items.timekeeper_rate | |
| Activity_date | date | Line item activity date | Invoice_line_items.activity_date | |
| Invoice_line_item_type | Character varying | Type of the invoice line item | Invoice_line_items.line_item_type | |
| Parent_type | Character varying | Parent of the line item, it may be a line item or invoice | Invoice_line_items.parent_type | |
| Adjustment_unit | Numeric (20,6) | No longer used | Invoice_line_items.adjustment_unit | |
| Adjustment_cost | Numeric (20,6) | No longer used | Invoice_line_items.adjustment_cost | |
| Adjustment_total | Numeric (20,6) | No longer used | Invoice_line_items.adjustment_total | |
| System_Created | Boolean DEFAULT false | True if this is system created | Invoice_line_items.system_created | |
| Dispute_adjustment_type | Character varying | Type of adjustment e.g. set_amount_to, reduce_by_pct, reduce_by_amount, set_rate_to, set_net_to | Invoice_line_items.dispute_adjustment_type | |
| adjustment_value | Numeric (20,6) | Value by an line item is adjusted, it may be hours, rate, percentage, or net | Invoice_line_items.adjustment_value | |
| Inactive | Boolean DEFAULT false | True, if item is inactive | Invoice_line_items.inactive | |
| Task_code | Character varying | Task code | Invoice_line_items.task_code | |
| Task_description | character varying | Description for the task code | Task_codes.description | |
| Expense_code | Character varying | Expense code | Invoice_line_items.expense_code | |
| Expense_description | Character varying | Description for expense code | Expense_codes.description | |
| Activity_code | Character varying | Activity code | Invoice_line_items.activity_code | |
| Activity_description | Character varying | Description of activity code | Activity_codes.description | |
| Adjustment_code | Character varying | Adjustment code | Invoice_line_items.adjustment_code | |
| Adjustment_description | Character varying | Description for adjustment code | Adjustment_codes.description | |
| Tax_code | Character varying | Tax code | Invoice_line_items.tax_code | |
| Tax_description | Character varying | Description for the tax code | Tax_codes.description | |
| Discount_code | Character varying | Discount code | Invoice_line_items.discount_code | |
| Discount_description | Character varying | Description of discount code | Discount_codes.description | |
| Adjuster_name | Character varying | Name of user who made the adjustments | bp_users.user_name | |
| Optional_tax_rate | Numeric (20,6) | Invoice_line_items.optional_tax_rate | ||
| Staff_classification_id | uuid | Id of the staff classification | Invoice_line_items.staff_classification_id | |
| Staff_classification_description | Character varying | Description of the staff classification | Invoice_line_items.staff_classification_description |
bp_timekeepers materialized view
Purpose
- This materialized view stores the data related to the client's timekeepers
- The timekeepers are cached in the warehouse schema using this materialized view. This reduces the number of joins with foreign tables from cube-related views.
- The materialized view is refreshed daily as part of the nightly refresh process.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Client_time_keeper_id | uuid | Yes | Client timekeepr id, ID of the record, primary key | Client_timekeepers.id |
| Time_keeper_id | uuid | Id of the time keeper | Client_timekeepers.time_keeper_id | |
| Account_id | uuid | Id of the vendor | Client_timekeepers.account_id | |
| Client_account_id | uuid | Id of the client account | Client_timekeeprs.client_account_id | |
| Pending_rate | Numeric (20,6) | Rate pending for approval on this timekeeper | Client_timekeepers.pending_rate | |
| Approved_rate | Numeric (20,6) | Rate approved for this timekeeper | Client_timekeepers.approved_rate | |
| Currency_id | uuid | Currency Id of the time keeper rate | Client_timekeepers.currency_id | |
| Employee_id | uuid | Employee Id of the time keeper | Time_keepers.employee_id | |
| Full_name | Character varying | Full name of the time keeper | Time_keepers.full_name | |
| Initials | Character varying | Initials of the time keeper | Time_keepers.initials | |
| Character varying | Email id of the time keeper | Time_keepers.email | ||
| Lawyer | Boolean | True, if timekeeper is a lawyer, otherwise false | Time_keepers.lawyer | |
| Phone | Character varying | Phone number of the time keeper | Time_keepers.phone | |
| First_practiced | Bigint | Year time keeper first practices | Time_keepers.first_practiced | |
| Url | Character varying | URL of the time keeper profile | Time_keepers.url | |
| Gender | Chracter varying | Gender of the time keeper | Time_keepers.gender | |
| Ethnicity | Character varying | Ethnicity of the time keeper | Time_keepers.ethnicity | |
| Other_ethnicity | Character varying | Other ethnicity of the time keeper | Time_keepers.other_ethnicity | |
| Date_bar_passed | Bigint | Date of BAR passes | Time_keepers.date_bar_passed | |
| Default_rate_effective_date | Timestamp w/o time zone | Effective date for the default rate | Time_keepers.default_rate_effictive_date | |
| Default_rate | Numeric (20,6) | Default rate of the time keeper | Time_keepers.default_rate | |
| Office_id | uuid | Id of the time keeper office record | Time_keepers.office_id | |
| Office_name | Character varying | Office name of the time keeper | Offices.office_name | |
| Country | Character varying | Country of the time keeper | offices.country | |
| City | Character varying | City of the timekeeper | Offices.city | |
| Street_address_1 | Character varying | Address line 1 of the time keeper | offices.street_address_1 | |
| Street_address_2 | Character varying | Address line 2 of the time keeper | Offices.street_address_2 | |
| Province_or_state | Character varying | Province or state of time keeper | offices.province_or_state | |
| Postal_or_zipcode | Character varying | Postal or zip code of time keeper | Offices.postal_or_zipcode | |
| Currency_code | Character varying | Currency code for the time keeper rate currency | Bp_currencies.currency_code | |
| Currency_symbol | Character varying | Currency symbol for the time keeper rate currency | Bp_currencies.currency_symbol | |
| Staff_class_description | Character varying | Staff classification of the time keeper | bp_staff_classifications.description | |
| Staff_class_code | Character varying | Staff classificatino code of the time keeper | bp_staff_classifications.code | |
| Rate | Numeric (20,6) | Rate for the staff classification | bp_staff_classifications.rate | |
| bp_tk_derived_pending_rate | Numeric (20,6) | Last updated rate from client timekeeper rates from BillingPoint where state is pending or pending_approval or resubmit or unapprove | client_timekeeper_rates.rate. Derived using the function bp_get_tk_pending_rate since the existing pending_rate field is no more used in BillingPoint |
bp_timekeeper_rate view
Purpose
- This view stores contains the fields that are used in the warehouse from the ebilling.client_timekeeper_rates table.
- This view is just a wrapper on the ebilling.client_timekeepr_rates table from the client's private database
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| id | uuid | Yes | Client timekeeper id, Id of record, primary key | Client_timekeeper_rates.id |
| Staff_classification_id | uuidq | Id of the staff classification for the time keeper | Client_timekeeper_rates.staff_classification_id | |
| Client_timekeeper_id | Id of the client time keeper record | Client_timekeeper_rates.client_timekeeper_id | ||
| Account_id | uuid | Id of the vendor account | Client_timekeeper_rates.account_id | |
| Effective_date | Timestamp w/o time zone | Effective date for the time keeper rate | Client_timekeeper_rates.effective_date | |
| rate | Numeric (20,6) | Rate of the time keeper | Client_timekeeper_rates.rate | |
| State | Character varying | State of the client timekeeper rate - pending, pending_approval, resubmit, unapprove, draft, sending, disputed, voided, edited_since_disputed | Client_timekeeper_rates.state | |
| Rate_increase_reason | Text | Reason for the rate increase | Client_timekeeper_rates.rate_increase_reason | |
| Remote_key | Character varying | Id of the timekeeper in Onit AppBuilder | Client_timekeeper_rates.remote_key | |
| Currency_id | uuid | Id of the currency | Client_timekeeper_rates.currency_id | |
| Currency_code | Character varying | Currency code for the timekeeper rate | bp_currencies.currency_code | |
| Currency_symbol | Charater varying | Currency symbol for the timekeeper rate | Bp_currencies.currency_symbol | |
| Time_keeper_id | uuid | Id of the timekeeper record | Client_timekeeper_rates.time_keeper_id |
bp_timekeeper_rate_histories materialized view
Purpose
- This materialized view stores the timekeeper rate histories from the ebilling master.
- The timekeeper rate histories are cached in the warehouse schema using this materialized view. This reduces the number of joins with foreign tables from cube-related views.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Tkr_id | uuid | Yes | Client timekeepr rate history id, Id of the record, primary key | Timekeepr_rate_histories.id |
| Time_keeper_id | uuid | Time keeper id | Timekeepr_rate_histories.time_keeper_id | |
| Tkr_rate | Numeric (20,6) | Rate for the time keeper | Timekeepr_rate_histories.rate | |
| Tkr_effective_start_date | Timestamp w/o time zone | Effective start date for the rate | Timekeepr_rate_histories.effective_start_date | |
| Tkr_effective_end_date | Timestamp w/o time zone | Effective end date for the rate | Timekeepr_rate_histories.effective_end_date | |
| Tk_employee_id | Character varying | Employee id of the time keeper | Time_keepers.employee_id | |
| Tk_staff_classification_description | Character varying | Staff classification for the time keeper | Bp_staff_classifications.description | |
| Tkr_currency_rate | Character varying | Currency code for the rate | Bp_currencies.currency_code | |
| Tkr_currency_sybmol | Character varying | Currency symbol for the rate | Bp_currencies.currency_symbol |
bp_invoice_validations view
Purpose
- This view stores the fields used in the warehouse from the ebilling.invoice_validations table.
- This view is just a wrapper on the ebilling.invoice_validations table from the client's private database.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| id | uuid | Yes | Invoice validation, ID of the record, primary key | Invoice_validations.id |
| Invoice_id | uuid | Id of the invoice record | Invoice_validations.invoice_id | |
| Context_type | Character varying | Context type can be Fee, Expense, HeaderTax, FeeDiscount, ExpenseDiscount, LineItemTax | Invoice_validations.context_type | |
| Context_id | uuid | Line item Id for line item level validation messages Invoice id for invoice level validation messages |
Invoice_validations.context_id | |
| Message_severity | Character varying | Message severity can be ERROR, WARNING | Invoice_validations.message_severity | |
| Message | Text | Validation message | Invoice_validations.message | |
| Created_at | Timestamp w/o time zone | Date on which invoice got created in BillingPoint | Invoice_validations.created_at | |
| Updated_at | Timestamp w/o time zone | Date on which invoice got updated in BillingPoint | Invoice_validations.updated_at | |
| Rule_type | Character varying | Name of the rule | Invoice_validations.rule_type | |
| Invoice_number | Character varying | Vendor invoice number | Invoices.invoice_number |
manual_invoice_line_item_cube materialized view
Purpose
- This materialized view transforms the manual invoices into line items. One manual invoice will be transformed into one or more line item rows based on whether manual fields (fees, fees discount, expenses, expenses_discount, taxes)
- The transformed manual invoice line items are cached in the warehouse schema using this materialized view.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Client_project_number | uuid | Yes | Invoice validation id, ID of the record, primary key | invoices.matter_number |
| Account_id | uuid | Pub_vendor_id from the vendors app | Vendors.psb_vendor_id | |
| Vendor_end_date | Timestamp w/o time zone | Ended at date of the atom from the vendor app | vendors.ended_at | |
| Vendor_id | uuid | Id of the atom in the vendor app | vendors.id | |
| Vendor_start_date | Timestamp w/o time zone | Created date of the atom from the vendor app | Vendors.created_at | |
| Vendor_state | Character varying | State of the atom in the vendor app | vendors.state | |
| Vendor_currency_code | Character varying | One of the below fields dependeing on the type of manual invoice line item: Currency code of manual fees OR Currency code of manual fee discount OR Currency code of manual expenses OR Currency code of manual expense discount OR Currency code of manual taxes |
Invoices.manual_fees_c OR invoices.manual_fees_discount_c OR Invoices.manual_expenses_c OR Invoices.manual_expense_discount_c OR Invoices.manual_taxes_c |
|
| Inv_curr_state_name | Character varying | Phase of the atom from the invoice app | Invoices.curr_state_name | |
| Inv_invoice_fees_submitted | Numeric (20,6) | Manual_fees field value from invoices app | Invoices.manual_fees | |
| Inv_invoice_fees_base | Numeric (20,6) | Invoice_fees field value from invoices app | Invoices.invoice_fees | |
| Inv_invoice_orig_fees_submitted | Numeric (20,6) | Manual_fees field from invoice app | Invoices.manual_fees | |
| Inv_invoice_orig_fees_base | Numeric (20,6) | Invoice_fees field value from invoices app | Invoices.invoice_fees | |
| Inv_fee_discount_submitted | Numeric (20,6) | Manual_fees_discount field from invoices app | Invoices.manual_fees_discount | |
| Inv_fee_discount_base | Numeric (20,6) | (manual_fees_discount field value from invoices app) x (derived spot rate) | Invoices.manual_fees_discount x derived spot rate | |
| Inv_orig_fee_discount_submitted | Numeric (20,6) | (manual_fees_discount field value) + (manual_expenses_discount field value) from invoices app | Invoices.manual_fees_discount | |
| Inv_orig_fee_discount_base | Numeric (20,6) | Discount_amount field from invoices app | Invoices.manual_fees_discount x derived spot rate | |
| Inv_invoice_orig_discount_submitted | Numeric (20,6) | (Manual_fees_discount fieldvalue from invoices app) x (derived spot rate) | Invoices.manual_fees_discount + invoices.manual_expenses_discount | |
| Inv_invoice_orig_discount_base | Numeric (20,6) | Discount_amount field value from invoices app | Invoices.discount_amount | |
| Inv_invoice_expenses_submitted | Numeric (20,6) | Manual_expenses field from invoices app | Invoices.manual_expenses | |
| Inv_invoice_expenses_base | Numeric (20,6) | Invoice_expenses field value from invoices app | Invoices.invoice_expenses | |
| Inv_invoice_orig_expenses_submitted | Numeric (20,6) | Manual_expenses field value from invoices app | Invoices.manual_expenses | |
| Inv_invoices_orig_expenses_base | Numeric (20,6) | invoice_expenses field from invoice app | Invoices.invoice_expenses | |
| Inv_expense_discount_submitted | Numeric (20,6) | Manual_expenses field from invoices app | Invoices.manual_expense_discount | |
| Inv_expense_discount_base | Numeric (20,6) | (Manual_expenses_discount field from the invoices app) x (derived spot rate) | Invoices.manual_expenses_discount x derived spot rate | |
| Inv_orig_expense_discount_submitted | Numeric (20,6) | Manual_expenses_dsicount field from invoice app | Invoice.manual_expense_discount | |
| Inv_orig_expense_discount_base | Numeric (20,6) | (Manual_expenses_discount field value from the invoices app) x (derived spot rate) | Invoices.manual_expenses_discount x derived spot rate | |
| Inv_orig_manual_taxes_submitted | Numeric (20,6) | Manual_taxes field value from invoices app | Invoices.manual_taxes | |
| Inv_orig_manual_taxes_base | Numeric (20,6) | (Manual_taxes field value from the invoices app) x (derived spot rate) | Invoices.manual_taxes x derived spot rate | |
| Inv_invoice_orig_amount_submitted | Numeric (20,6) | (manual_fees field value) + (manual_fees_discount field value) + (manual_expenses field value) + (manual_expenses_discount field value) + (manual_taxes field value) from the Invoices app |
invoices.manual_fees + invoices.manual_fees_discount + invoices.manual_expenses + invoices.manual_expenses_discount + invoices.manual_taxes |
|
| Inv_invoice_orig_amount_base | Numeric (20,6) | Invoice_orig_amount field value from invoices app | Invoices.invoice_orig_amount | |
| Inv_invoice_total_submitted | Numeric (20,6) | (manual_fees field value) + (manual_fees_discount field value) + (manual_expenses field value) + (manual_expenses_discount field value) + (manual_taxes field value) from the Invoices app |
invoices.manual_fees + invoices.manual_fees_discount + invoices.manual_expenses + invoices.manual_expenses_discount + invoices.manual_taxes |
|
| Inv_invoice_total_base | Numeric (20,6) | Invoice total in base currency. Invoice_total field value from invoices app | Invoices.invoice_total | |
| Inv_pay_total_submitted | Numeric (20,6) | Its invoice_total - short_pay_total. Since short_pay may not be in use (need to confirm) so it's most likely equals to invoice_total | Invoices.payment_amount | |
| Inv_pay_total_base | Numeric (20,6) | Same as above but in base currency | Invoices.invoice_total | |
| Inv_billing_end_date | Timestamp w/o time zone | billing_end_date field value from invoice app | Invoices.billing_end_date | |
| Inv_billing_start_date | Timestamp w/o time zone | Billing_start_date field value from invoices app | Invoices.billing_start_date | |
| Inv_invoice_date | Timestamp w/o time zone | Invoice_date field value from invoices field | Invoices.invoice_date | |
| Inv_invoice_number | Character varying | Value of the name field from invoices app | invoices.name | |
| Inv_notes | Character varying | Value of notes field from invoices app | invoices.notes | |
| Inv_received_date | Timestamp w/o time zone | Value of the created_at field from invoices app | invoices.created_at | |
| Li_item_unit_cost | Numeric (20,6) | One of the below fields depending on the type of manual invoice line item manual_fees, when manual_fees is not equal 0 manual_fees_discount, when manual_fees_discount is not equal 0 manual_expenses, when manual_expenses is not equal 0 manual_expenses_discount, when manual_expenses_discount is not equal 0 manual_taxes, when manual_taxes is not equal 0 |
invoices.manual_fees, when manual_fees is not equal 0 invoices.manual_fees_discount, when manual_fees_discount is not equal 0 invoices.manual_expenses, when manual_expenses is not equal 0 invoices.manual_expenses_discount, when manual_expenses_discount is not equal 0 invoices.manual_taxes, when manual_taxes is not equal 0 |
|
| Li_line_item_total_submitted | Numeric (20,6) | One of the below fields depending on the type of manual invoice line item manual_fees, when manual_fees is not equal 0 manual_fees_discount, when manual_fees_discount is not equal 0 manual_expenses, when manual_expenses is not equal 0 manual_expenses_discount, when manual_expenses_discount is not equal 0 manual_taxes, when manual_taxes is not equal 0 |
invoices.manual_fees, when manual_fees is not equal 0 invoices.manual_fees_discount, when manual_fees_discount is not equal 0 invoices.manual_expenses, when manual_expenses is not equal 0 invoices.manual_expenses_discount, when manual_expenses_discount is not equal 0 invoices.manual_taxes, when manual_taxes is not equal 0 |
|
| Li_line_item_total_base | Numeric (20,6) | One of the below fields depending on the type of manual invoice line item manual_fees x (derived spot rate), when manual_fees is not equal 0 manual_fees_discount x (derived spot rate), when manual_fees_discount is not equal 0 manual_expenses x (derived spot rate), when manual_expenses is not equal 0 manual_expenses_discount x (derived spot rate), when manual_expenses_discount is not equal 0 manual_taxes x (derived spot rate), when manual_taxes is not equal 0 |
invoices.manual_fees x (derived spot rate), when manual_fees is not equal 0 invoices.manual_fees_discount x (derived spot rate), when manual_fees_discount is not equal 0 invoices.manual_expenses x (derived spot rate), when manual_expenses is not equal 0 invoices.manual_expenses_discount x (derived spot rate), when manual_expenses_discount is not equal 0 invoices.manual_taxes x (derived spot rate), when manual_taxes is not equal 0 |
|
| Li_id | Character varying | Time UUID created for each line item row | Md5(random()::text || clock_timestamp()::text) | |
| Inv_id | uuid | _id of the atom from the invoices app | Invoices._id | |
| Inv_remote_key | uuid | _id of the atom from the invoices app | invoices._id | |
| Li_activity_date | Timestamp w/o time zone | Invoice_date field from the invoices app | Invoices.invoice_date | |
| Line_item_type | Character varying | Type of the manual line item. This field is derived. 'Fee' when manual_fees in Invoices app is not equal to 0 'FeeDiscount' when manual_fees_discount in Invoices app is not equal to 0 'Expense' when manual_expenses in Invoices app is not equal to 0 'ExpenseDiscount' when manual_expenses_discount in Invoices app is not equal to 0 'HeaderTax' when manual_taxes in Invoices app is not equal to 0 |
Type of the manual line item. This field is derived. 'Fee' when invoices.manual_fees in Invoices app is not equal to 0 'FeeDiscount' when invoices.manual_fees_discount in Invoices app is not equal to 0 'Expense' when invoices.manual_expenses in Invoices app is not equal to 0 'ExpenseDiscount' when invoices.manual_expenses_discount in Invoices app is not equal to 0 'HeaderTax' when invoices.manual_taxes in Invoices app is not equal to 0 |
invoices_line_item_cube materialized view
Purpose
- This materialized view stores the union of line items from BillingPoint and the line items transformed for manual invoices from AppBuilder.
- The invoice line items are cached in the warehouse schema using this materialized view.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Client_project_number | Character varying | Yes | Invoice validation id, ID of the record, primary key | Bp_projects.number |
| Account_id | Character varying | Psb_vendor_id from the vendor app | bp_invoices.account_id | |
| Client_account_id | Character varying | Client account id of the invoice | Bp_invoices.client_account_id | |
| Vendor_end_date | Timestamp w/o time zone | BAR end date | Bp_billing_authorization_requests.end_date | |
| Vendor_id | Text | BAR id | bp_billing_authorization_requests.id | |
| Vendor_fee_arrangement_name | Character varying | Fee arrangement name on BAR | Bp_billing_authorization_requests.fee_arrangement_name | |
| Vendor_modification_reason | Text | Reason for which vendor asked modification on BAR | Bp_billing_authorization_requests.modification_reason | |
| Vendor_purchase_order_number | Character varying | Purchase order number from BAR record | Bp_billing_authorization_requests.purchase_order_number | |
| Vendor_start_date | Timestamp w/o time zone | Start date of the BAR | Bp_billing_authorization_requests.start_date | |
| Vendor_state | Character varying | State of BAR | Bp_billing_authorization_requests.state | |
| Vendor_budget_amount_submitted | Numeric (20,6) | Budget amount in submitted currency | Bp_billing_authorization_requests.budget_amount | |
| Vendors_budget_amount_base | Numeric (20,6) | Budget amount in client base currency | Bp_billing_authorization_requests.client_spot_rate x Bp_billing_authorization_requests.budget_amount | |
| Vendor_client_spot_rate | Numeric (20,6) | Spot rate of vendor on invoice | Bp_billing_authorization_requests.client_spot_rate | |
| Vendor_currency_code | Character varying | Currency code for the spot rate of vendor of invoice | Bp_billing_authorization_requests.currency_code | |
| Vendor_currency_symbol | Character varying | Currency symbol for the spot rate of vendor of invoice | Bp_billing_authorization_requests.currency_symbol | |
| Vendor_edited_since_disputed | Bigint | 1 if BAR is edited since disputed, 0 otherwise | Bp_billing_authorization_requests.edited_since_disputed | |
| Inv_id | Character varying | BP invoice ID | Bp_invoices.id | |
| Inv_adjustments_enabled | Bigint | 1 if adjustments are enabled, 0 otherwise | Bp_invoices.adjustments_enabled | |
| Inv_ap_details | Text | Legacy data. This was used by AppBuilder to populate Legal Entity info. The was replaced by current Legal Entity process | Bp_invoices.ap_details | |
| Inv_approved_date | Timestamp w/o time zone | Date invoice approval is completed by all client approvers in Onit and invoices is ready for payment processing. | Bp_invoices.approved_date | |
| Inv_billing_end_date | Timestamp w/o time zone | Billing end date | Bp_invoices.billing_end_date | |
| Inv_billing_start_date | Timestamp w/o time zone | Billing start date | Bp_invoices.billing_start_date | |
| Inv_client_spot_rate | Numeric (20,6) | Client spot rate sotred on invoice record | Bp_invoices.client_spot_rate | |
| Inv_invoice_date | Timestamp w/o time zone | Invoice date | Bp_invoices.invoice_date | |
| Inv_invoice_number | Character varying | Vendor invoice number | Bp_invoices.invoice_number | |
| Inv_notes | Text | Invoice_description in LEDES | Bp_invoices.notes | |
| Inv_po_number | Character varying | Clinet PO # from vendor BAR | Bp_invoices.po_number | |
| Inv_received_date | Timestamp w/o time zone | Date invoice first went to phase = Pending Approval | Bp_invoices.received_date | |
| Inv_resubmitted | Bigint | 1 if invoice resubmitted, 0 otherwise | Bp_invoices.resubmitted | |
| Inv_state | Character varying | Invoice BP phase (Failed, Pending Approval, Approved, Disputed, Voided, Paid, Draft) | Bp_invoices.state | |
| Inv_submission_type | Character varying | Submission_type e.g., Manual, LEDES | Bp_invoices.submission_type | |
| Inv_dispute_adjustment_total_submitted | Numeric (20,6) | Total invoice adjustments in vendor base currency | Bp_invoices.dispute_adjustment_total | |
| Inv_dispute_adjustment_total_base | Numeric (20,6) | Invoice adjustments total in submitted currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.expense_discount | |
| Inv_expense_discount_submitted | Numeric (20,6) | Total expense discount in submitted currency | Bp_invoices.expense_discount | |
| Inv_expense_discount_base | Numeric (20,6) | Total expense discount in base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.expense_discount | |
| Inv_expense_header_dispute_adjustment_submitted | Numeric (20,6) | Total header expense adjustments in submitted currency | Bp_invoices.expense_header_dispute_adjustment | |
| Inv_expense_header_dispute_adjustment_base | Numeric (20,6) | Total header expense adjustments in base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.expense_header_dispute_adjustment | |
| Inv_expense_line_item_dispute_adjustment_submitted | Numeric (20,6) | Total line item expense adjustments in submitted currency | Bp_invoices.expense_line_item_dispute_adjustment | |
| Inv_expense_line_item_dispute_adjustment_base | Numeric (20,6) | Total line item expense adjustments in base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.expense_line_item_dispute_adjustment | |
| Inv_fee_discount_submitted | Numeric (20,6) | Total fee discount (header and line item) in submitted currency | bp_invoices.fee_discount | |
| Inv_fee_discount_base | Numeric (20,6) | Total fee discount (header and line item) in base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.fee_discount | |
| Inv_fee_header_dispute_adjustment_submitted | Numeric (20,6) | Fee header adjustments total in submitted currency | Bp_invoices.fee_header_dispute_adjustment | |
| Inv_fee_header_dispute_adjustment_base | Numeric (20,6) | Fee header adjustments in total base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoice.fee_header_dispute_adjustment | |
| Inv_fee_line_item_dispute_adjustment_submitted | Numeric (20,6) | Fee line item adjustments total in submitted currency | Bp_invoices.fee_line_item_dispute_adjustment | |
| Inv_fee_line_item_dispute_adjustment_base | Numeric (20,6) | Fee line item adjustments total in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.fee_line_item_dispute_adjustment | |
| Inv_header_dispute_adjustment_submitted | Numeric (20,6) | Header adjustments total (fee header + expense header adjustments) in submitted currency | bp_invoices.header_dispute_adjustment_submitted | |
| Inv_header_dispute_adjustment_base | Numeric (20,6) | Header adjustments total (ffe header + expense header) in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.header_dispute_adjustment_base | |
| Inv_header_short_pay_adjustment_submitted | Numeric (20,6) | Not in use | bp_invoices.header_short_pay_adjustment | |
| Inv_header_short_pay_adjustment_base | Numeric (20,6) | Not in use | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.header_short_pay_adjustment | |
| Inv_invoice_fees_submitted | Numeric (20,6) | Fees total in submitted currency | Bp_invoices.invoice_fees | |
| Inv.invoice_fees_base | Numeric (20,6) | Fees total in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_fees | |
| Inv_invoice_expenses_submitted | Numeric (20,6) | Expenses total in submitted currency | Bp_invoices.invoice_expenses | |
| Inv_invoice_expenses_base | Numeric (20,6) | Expenses total in base currency | Bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_expenses | |
| Inv_invoice_orig_amount_submitted | Numeric (20,6) | Invoice total on first time submission in submitted currency | Bp_invoices.invoice_orig_amount | |
| Inv_invoice_orig_amount_base | Numeric (20,6) | Invoice total on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_orig_amount | |
| Inv_invoice_orig_discount_submitted | Numeric (20,6) | Invoice total discount on first time submission in submitted currency | bp_invoices.invoice_orig_discount | |
| Inv_invoice_orig_discount_base | Numeric (20,6) | Invoice total discount on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_orig_discount | |
| Inv_invoice_orig_expenses_submitted | Numeric (20,6) | Invoice total expenses on first time submission in submitted currency | bp_invoices.invoice_orig_expenses | |
| Inv_invoice_expenses_base | Numeric (20,6) | Invoice total expenses on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_orig_expenses | |
| Inv_invoice_orig_fees_submitted | Numeric (20,6) | Invoice total fees on first time submission in submitted currency | Bp_invoices.invoice_orig_fees_submitted | |
| Inv_invoice_orig_fees_base | Numeric (20,6) | Invoice total on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.invoice_orig_fees_base | |
| Inv_invoice_total_submitted | Numeric (20,6) | Invoice total in submitted currency | bp_invoices.invoice_total | |
| Inv_invoice_total_base | Numeric (20,6) | Invoice total in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoice_total | |
| Inv_line_item_short_pay_adjustment_submitted | Numeric (20,6) | Not in use | Bp_invoices.line_item_short_pay_adjustment | |
| Inv_line_item_short_pay_adjustment_base | Numeric (20,6) | Not in use | bp_billing_authorization_requests.client_spot_rate x bp_invoices.line_item_short_pay_adjustment | |
| Inv_orig_expense_discount_submitted | Numeric (20,6) | Total expense discount on first time submission in submitted currency | bp_invoices.orig_expense_discount | |
| Inv_orig_expense_discount_base | Numeric (20,6) | Total expense discount on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.orig_expense_discount | |
| Inv_orig_fee_discount_submitted | Numeric (20,6) | Total fee discount on first time submission in submitted currency | bp_invoices.orig_fee_discount | |
| Inv_orig_fee_discount_base | Numeric (20,6) | Total fee discount on first time submission in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.orig_fee_discount | |
| Inv_pay_total_submitted | Numeric (20,6) | Its invoice_total -short_pay_total. Since short_pay may not be in use (need to confirm) so its most likely equals to invoice_total | bp_invoices.pay_total | |
| Inv_pay_total_base | Numeric (20,6) | Same as above but in base currency | bp_billing_authorization_requests.client_spot_rate x bp_invoices.pay_total | |
| Inv_short_pay_adjustment_total_submitted | Numeric (20,6) | Not in use | bp_invoice.short_pay_adjustment_total | |
| Inv_short_pay_adjustment_total_base | Numeric (20,6) | Not in use | bp_billing_authorization_requests.client_spot_rate x bp_invoices.short_pay_adjustment_total | |
| Inv_remote_key | Character varying | AB invoice atom ID | Bp_invoices.remote_key | |
| Li_task_description | Character varying | Description of task code | Bp_invoice_line_items.task_description | |
| Li_tax_description | Character varying | Description of tax code | bp_invoice_line_items.tax_description | |
| Li_expense_description | Character varying | Description of the expense code | bp_invoice_line_items.expense_description | |
| Li_discount_description | Character varying | Description of the discount code | bp_invoice_line_items.discount_description | |
| Li_activity_description | Charcter varying | Description of activity code | bp_invoice_line_items.activity_description | |
| Li_adjustment_description | Character varying | Description of adjustment code | bp_invoice_line_items.adjustment_description | |
| Li_id | text | Id of the invoice line item | bp_invoice_line_items.id | |
| Li_activity_code | Character varying | Activity code | bp_invoice_line_items.activity_code | |
| Li_activity_date | Timestamp w/o time zone | Date for which timekeeper is billing on invoice, activity date of line item | bp_invoice_line_items.activity_date | |
| Li_adjuster_id | Text | Id of the adjuster user is BillingPoint | bp_invoice_line_items.adjuster_id | |
| Li_adjuster_name | Character varying | Name of the adjuster user in BillingPoint | bp_invoice_line_items.adjuster_name | |
| Li_adjustment_code | Character varying | Adjustment code | bp_invoice_line_items.adjustment_code | |
| Li_adjustment_value | Numeric (20,6) | Value by an line item is adjusted, it may be hours, rate, percentage or net | bp_invoice_line_items.adjustment_value | |
| Li_discount_code | Character varying | Discount code | bp_invoice_line_items.discount_code | |
| Li_dispute_adjustment_type | Character varying | Type of adjustment e.g., set_amount_to, reduce_by_pct, reduce_by_amount,set_rate_to,set_net_to | bp_invoice_line_items.dispute_adjustment_type | |
| Li_expense_code | Character varying | Expense code | bp_invoice_line_items.expense_code | |
| Li_inactive | Bigint | 1 if line item is inactive, 0 otherwise | bp_invoice_line_items.inactive | |
| Li_invoice_line_item_type | Character varying | Type of line item | bp_invoice_line_items.invoice_line_item_type | |
| Li_item_description | Text | Description of the line item | bp_invoice_line_items.item_description | |
| Li_parent_type | Character varying | Parent of this line item, it may be a line item or invoice | bp_invoice_line_items.parent_type | |
| Li_parent_id | Uuid | Parent Id. When FeeDispute adjustment is made, this field stores the line item id of the fee line item | bp_invoice_line_items.parent_id | |
| Li_system_created | Bigint | 1 if system created, 0 otherwise | bp_invoice_line_items.system_created | |
| Li_task_code | Character varying | Task code | bp_invoice_line_items.task_code | |
| Li_tax_code | Character varying | Tax code | bp_invoice_line_items.tax_code | |
| Li_adjustment_cost_submitted | Numeric (20,6) | No longer used | bp_invoice_line_items.adjustment_cost | |
| Li_adjustment_cost_base | Numeric (20,6) | No longer used | bp_billing_authorization_requests.client_spot_rate x bp_invoice_line_items.adjustment_cost | |
| Li_adjustment_total_submitted | Numeric (20,6) | No longer used | bp_invoice_line_items.adjustment_total | |
| Li_adjustment_total_base | Numeric (20,6) | No longer used | bp_billing_authorization_requests.client_spot_rate x bp_invoice_line_items.adjustment_total | |
| Li_adjustment_unit | Numeric (20,6) | No longer used | Bp_invoice_line_items.adjustment_unit | |
| Li_item_quantity | Numeric (20,6) | Line item number of units | bp_invoice_line_items.item_quantity | |
| Li_item_unit_cost | Numeric (20,6) | Line item unit cost | bp_invoice_line_items.item_unit_cost | |
| Li_line_item_total_submitted | Numeric (20,6) | Line item total in vendor base currency | bp_invoice_line_items.line_item_total | |
| Li_discount_submitted | Numeric (20,6) | Line item total in submitted currency | bp_invoice_line_items.discount | |
| Li_discount_base | Numeric (20,6) | Submitted discount in client’s base currency. Client’s spot rate x discount | bp_billing_authorization_requests.client_spot_rate x bp_invoice_line_items.discount | |
| Li_line_item_total_base | Numeric (20,6) | Line item total in vendor base. Client’s spot rate x line item total | bp_billing_authorization_requests.client_spot_rate x bp_invoice_line_items.line_item_total_base | |
| Li_timekeeper_rate_submitted | Numeric (20,6) | Submitted rate of the timekeeper | Bp_invoice_line_items.timekeeper_rate | |
| Li_timekeeper_rate_base | Numeric (20,6) | Client’s spot rate x Submitted rate for the time keeper | bp_billing_authorization_requests.client_spot_rate x bp_invoice_line_items.timekeeper_rate | |
| Tk_time_keeper_id | Uuid | Id of the time keeper | bp_timekeepers.time_keeper_id | |
| Tk_currency_code | Character varying | Currency code for the time keeper’s currency | bp_timekeepers.currency_code | |
| Tk_currency_symbol | Character varying | Currency symbol for the time keeper’s currency | bp_timekeepers.currency_symbol | |
| Tk_date_bar_passed | Bigint | Date of bar passed | bp_timekeepers.date_bar_passed | |
| Tk_default_rate_effective_date | Timestamp w/o time zone | Effective date for the default rate for the time keeper | bp_timekeepers.default_rate_effective_rate | |
| Tk_email | Character varying | Email id of the timekeeper | bp_timekeepers.email | |
| Tk_employee_id | Character varying | Employee id of the timekeeper | bp_timekeepers.employee_id | |
| Tk_ethnicity | Character varying | Ethnicity of the time keeper. This field will contain ‘Not Disclosed’ when supress diversity info it true. | bp_timekeepers.ethnicity | |
| Tk_first_practiced | Bigint | Year when time keeper first practiced | bp_timekeepers.first_practiced | |
| Tk_full_name | Character varying | Full name of the time keeper | bp_timekeepers.full_name | |
| Tk_gender | Character varying | Gender of the time keeper | bp_timekeepers.gender | |
| Tk_default_rate | Numeric (20,6) | Default rate of the time keeper | bp_timekeepers.default_rate | |
| Tk_default_rate_base | Numeric (20,6) | Client’s spot rate x Default rate of the time keeper | bp_timekeepers.default_base_rate | |
| Tk_initials | Character varying | Initials of the time keeper | bp_timekeepers.initials | |
| Tk_lawyer | Bigint | 1 when timekeepr is a lawyer, 0 otherwise | bp_timekeepers.lawyer | |
| Tk_other_ethnicity | Character varying | Other ethnicity of the time keeper. This field is blank when supress diversty is true | bp_timekeepers.other_ethnicity | |
| Tk_phone | Character varying | Phone of the timekeeper | bp_timekeepers.phone | |
| Tk_staff_class_code | Character varying | Code of the staff classification for the timekeeper | bp_staff_classifications.code if available on line item, else bp_timekeepers.staff_class_code | |
| Tk_staff_class_description | Character varying | Description of the staff classification for the timekeeper | bp_staff_classifications.description if available on line item, else bp_timekeepers.staff_class_description | |
| Tk_url | Character varying | URL of the timekeepr’s profile | bp_timekeeper.url | |
| Tk_approved_rate | Numeric (20,6) | Rate approved for this timekeeper | bp_timekeeper.approved_rate | |
| Tk_pending_rate | Numeric (20,6) | Rate pending for approval for this timekeeper | bp_timekeeper.pending_rate | |
| Tk_approved_rate_base | Numeric (20,6) | Client’s spot rate x Rate approved for this timekeeper | bp_billing_authorization_requests.client_spot_rate x bp_timekeeper.approved_rate | |
| Tk_pending_rate_base | Numeric (20,6) | Client’s spot rate x Rate pending for approval for this timekeeper | bp_billing_authorization_requests.client_spot_rate x bp_timekeeper.pending_rate | |
| Tk_client_time_keeper_id | Uuid | Id of the client timekeeper | bp_timekeepr.client_time_keeper_id | |
| Tk_office_id | Uuid | Id of the office of the timekeeper | Bp_timekeeper.office_id | |
| Tk_office_name | Character varying | Name of the office of the time keeper | bp_timekeeper.office_name | |
| Bp_tk_client_approved_rate | Numeric (20,6) | Last approved rate for the time keeper for the client | Bp_timekeeper_rates.rate | |
| Bp_tk_standard_rate | Numeric (20,6) | Standard rate for the timekeeper that is derieved from the time keeper rate histories based on the line item activity date between effective start date and effective end date | Bp_timekeeper_rates_histories.rate | |
| Client_base_currency_code | Character varying | Currency code for client’s abse currency | bp_account.client_base_currency_code | |
| Client_base_currency_symbol | Character varying | Currency symbol for client’s base currency | Bp_accounts.client_base_currency_symbol | |
| Tk_country | Character varying | Country of the time keeper | Bp_timekeeper.country | |
| Tk_time_keeper_id_text | Text | Text format of the timekeepr id | bp_timekeepers.time_keeper_id | |
| Inv_vat_processing | Boolean DEFAULT false | VAT flag on invoice | Bp_invoices.vat_processing | |
| Warehouse_refresh_date | Timestamp w/o time zone | System date time when this view was refreshed | Now() | |
| bp_tk_derived_pending_rate | Numeric (20,6) | Last updated rate from the client timekeeper rates from BillingPoint where state is pending or pending_approval or resubmit or unapprove | Bp_timekeepers.bp_tk_derived_pending_rate | |
| Bp_tk_derived_pending_rate_base | Numeric (20,6) | Client’s pot rate x Last updated rate from client’s timekeeper rates from BillingPoint where state is pending or pending_approval or resubmit or unapprove | bp_billing_authorization_requests.client_spot_rate xbp_timekeepers.bp_tk_derived_pending_rate |
bp_invoices_line_item_cube view
Purpose
- This is just a wrapper view for the invoices_line_item_cube materialized view. This is added since Tableau Desktop does not show the materialized views to the Report writers.
- All the columns in this view are the same as invocies_line_item_cube. Please refer to the invocies_cube for a data dictionary for this view.
Query Joins
Data Dictionary
Refer to the data dictionary for invoices_line_item_cube materialized view for details
view_timekeeper_rate_report view
Purpose
- This view extracts the data related to the ELM timekeeper rate report.
Query Joins
Data Dictionary
| Column name | Data Type | Unique Key | Description | Source Field |
|---|---|---|---|---|
| Li_invoice_line_item_type | Character varying | Type of the line item | invoices_line_item_cube.li_invoice_line_item_type | |
| Li_activity_date | Timestamp w/o time zone | Line item activity type | invoices_line_item_cube.li_activity_type | |
| Tk_currency_code | Character varying | Currency code for the time keeper’s currency | invoices_line_item_cube.tk_currency_code | |
| Tk_full_name | Character varying | Full name of the timekeeper | invoices_line_item_cube.tk_full_name | |
| Tk_staff_class_description | Character varying | Description of the staff classification for the timekeepr | invoices_line_item_cube.tk_staff_class_description | |
| Bp_tk_client_approved_rate | Numeric (20,6) | Line item number of units | invoices_line_item_cube.li_item_quantity | |
| Li_line_item_total_base | Numeric (20,6) | Line item total in vendor base. Client’s spot rate x Line item total | invoices_line_item_cube.li_line_item_total_base | |
| Tk_time_keeper_id | Uuid | Id of the time keeper | invoices_line_item_cube.tk_time_keeper_id | |
| Tk_first_practiced | Bigint | Year when the timekeeper first practiced | invoices_line_item_cube.tk_first_practiced | |
| Inv_remote_key | Character varying | _id of the atom from the invoice app | invoices_line_item_cube.inv_remote_key | |
| Tk_client_time_keeper_id | Uuid | Id of the client timekeeper | invoices_line_item_cube.client_timekeeper_id | |
| Invoice_id | Uuid | Id of the invoice | invoices._id AS invoice_id | |
| Invoices_manual_invoice | Character varying | “True” if manual invoice, else “false" | Invoices.manual_invoice AS invoices_manual_invoice | |
| Invoices_psb_vendor_name | Characte varying | Name of the vendor from BillingPoint | invoices.psb_vendor_name AS invoices_psb_vendor_name | |
| invoices_office | Character varying | Office | invoices.office AS invoices_office | |
| Offices_id | Uuid | Id of the office | Offices._id AS offices_id | |
| Office_city | Character varying | City of the office | Offices.city AS offices_city | |
| Office_country | Character varying | Country of the office | Offices.country AS office_country | |
| Tk_default_rate | Numeric (20,6) | Default rate of the time keeper | invoices_line_item_cube.tk_default_rate | |
| Tk_default_rate_base | Numeric (20,6) | Client’s spot rate x Default rate of the time keeper | invoices_line_item_cube.tk_default_rate_based | |
| Tk_approved_rate | Numeric (20,6) | Approved rate of the time keeper | invoices_line_item_cube.tk_approved_rate | |
| Tk_approved_rate_base | Numeric (20,6) | Client’s spot rate x Rate pending approval for this timekeeper | invoices_line_item_cube.tk_approved_rate | |
| Tk_pending_rate | Numeric (20,6) | Rate pending for approval for this timekeeper | invoices_line_item_cube.tk_pending_rate | |
| Tk_pending_rate_base | Numeric (20,6) | Client’s spot rate x Rate pending for approval for this timekeeper | invoices_line_item_cube.tk_pending_rate_base | |
| Bp_tk_dervied_pending_rate | Numeric (20,6) | last updated rate from client timekeepr rates from BillingPoint where stat is pending or pending_approval or resubmit or unapprove | invoices_line_item_cube.tk_derived_pending_rate | |
| Bp_tk_dervied_pending_rate_base | Numeric (20,6) | Client's spot rate x Last updated rate from client timekeeper rates from BillingPoint where state is pending or pending_approval or resubmit or unapprove | invoices_line_item_cube.bp_tk_derived_pending_rate_base |
bp_bar_line_items materialized view
Purpose:
- This materialized view contains the matter rate,matter_rate_submitted date, matter_rate_currency_code from ebilling bar line items.
- This materialized view is refreshed daily during the nightly refresh process.
Query Joins
Data Dictionary
| Column Name | Data Type | Unique Key | Description | Default |
|---|---|---|---|---|
| id | uuid | Yes | BAR id, ID of the record, primary key | bar_line_items.id |
| billing_authorization_request_id | uuid | Billing authorization request | bar_line_items.billing_authorization_request_id | |
| client_timekeeper | uuid | Client Timekeeper ID | bar_line_items.client_timekeeper_id | |
| client_timekeeper_rate_id | uuid | Client Timekeeper ID | bar_line_items.client_timekeeper_rate_id | |
| time_keeper_id | uuid | Time Keeper ID | bar_line_items.timekeeper_id | |
| account_id | uuid | Account ID | bar_line_items.account_id | |
| remote_id | uuid | ID of BAR record in Appbuilder | bar_line_items.account_id | |
| time_keeper_rate | numeric | Bar timekeeper rate | bar_line_items.time_keeper_rate | |
| updated_at | timestamp without time zone | BAR updated date | bar_line_items.updated_at | |
| created_at | timestampe without time zone | BAR creation date | bar_line_items.created_at | |
| deleted_at | timestamp without time zone | BAR deleted date | bar_line_items.deleted_at | |
| name | numeric | Type of fee arrangement | fee_arrangements.name | |
| currency_code | character varying | Currency code for client sup[ported currency | bp_currencies.currency_code |