Class: ViewSalesNetBase

Inherits:
ApplicationViewRecord show all
Defined in:
app/models/view_sales_net_base.rb

Overview

== Schema Information

Table name: view_sales_net_bases
Database name: primary

id(Synthetic row number primary key.) :bigint
channel(Sales channel derived from report_grouping (e.g. Direct - PRO, E-Commerce).) :text
cogs_value(Total cost of goods sold in USD (qty * unit_cogs * exchange_rate). Monetary.) :decimal(, )
due_date(Invoice payment due date.) :date
employee_name(Employee full name.) :string(255)
ferguson_ssr(Boolean — true if this is a Ferguson Sales Support Rep commission.) :boolean
gl_date(General ledger posting date. For Ferguson SSR orders, uses sales_support_commission_date.) :date
local_sales_rep(Boolean — true if this commission is attributed to a local sales rep.) :boolean
num_items(Number of qualifying commission items in the order (from tier_items). Currently NULL (tier_items disabled).) :integer
opportunity_value(Opportunity value or sales value (monetary, USD). Falls back to sales_value if no opportunity.) :decimal(, )
profit(Gross profit = sales_value - cogs_value (monetary, USD). NULL for non-SO resource types.) :decimal(, )
profit_pctg(Gross profit percentage = (profit / sales_value) * 100. Rounded to 2 decimals. NULL for non-SO.) :decimal(, )
resource_type('SO' for sales order invoices, or opportunity type for pipeline records.) :string
sales_support_rep(Boolean — true if this commission is attributed to a sales support rep.) :boolean
sales_value(Total line item sales value in USD (qty * discounted_price * exchange_rate). Monetary.) :decimal(, )
state(Invoice state (e.g. paid, posted). Used for commission eligibility.) :string(255)
techs_present(Count of technical support reps who completed qualifying activities on the opportunity.) :integer
tier1(True if profit_pctg ≤ max_tier1 threshold — lowest commission bracket. Only for non-Ferguson, non-SSR orders.) :boolean
tier2(Currently NULL (tier 2 commission bracket is disabled).) :boolean
tier3(True if profit_pctg > min_tier3 threshold — higher commission bracket. Only for non-Ferguson, non-SSR orders.) :boolean
tier4(Currently NULL (new business tier is disabled).) :boolean
tier5(True if this is a local sales rep commission — the rep was the local_sales_rep on the order.) :boolean
tier6(True if the employee is a technical support rep (job_title like '%Technical Support%').) :boolean
tier7(True if this is a Ferguson Sales Support Rep (SSR) commission — uses sales_support_commission_date as gl_date.) :boolean
tier8(True if profit_pctg ≥ min_tier8 and the rep is a sales_support_rep.) :boolean
total_sales_val(Total order value from invoices.total — the full order amount (monetary, USD).) :decimal(, )
unpaid_amount(Unpaid balance = total_sales_val - receipt_amount (monetary, USD). Negative means overpaid.) :decimal(, )
company_id(Company ID (1 = US, 2 = Canada).) :integer
customer_id(FK → parties.id for the customer.) :integer
emp_comp_id(FK → companies.id — the employee's company ID.) :integer
employee_id(FK → parties.id for the sales employee earning commission.) :integer
opportunity_id(FK → opportunities.id.) :integer
order_id(FK → orders.id.) :integer
profile_id(FK → profiles.id for the customer profile type.) :integer
resource_id(FK to invoices.id (when SO) or opportunities.id (when opportunity).) :integer

Indexes

index_view_sales_net_bases_on_channel (channel)
index_view_sales_net_bases_on_company_id (company_id)
index_view_sales_net_bases_on_customer_id (customer_id)
index_view_sales_net_bases_on_due_date (due_date)
index_view_sales_net_bases_on_emp_comp_id (emp_comp_id)
index_view_sales_net_bases_on_employee_id (employee_id)
index_view_sales_net_bases_on_ferguson_ssr (ferguson_ssr)
index_view_sales_net_bases_on_gl_date (gl_date)
index_view_sales_net_bases_on_id (id) UNIQUE
index_view_sales_net_bases_on_local_sales_rep (local_sales_rep)
index_view_sales_net_bases_on_opportunity_id (opportunity_id)
index_view_sales_net_bases_on_order_id (order_id)
index_view_sales_net_bases_on_profile_id (profile_id)
index_view_sales_net_bases_on_resource_id (resource_id)
index_view_sales_net_bases_on_resource_type (resource_type)
index_view_sales_net_bases_on_sales_support_rep (sales_support_rep)
index_view_sales_net_bases_on_state (state)
index_view_sales_net_bases_on_techs_present (techs_present)

Class Method Summary collapse

Methods inherited from ApplicationViewRecord

create, create!, #readonly?

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Models::EventPublishable

#publish_event

Class Method Details

.refresh ⇒ Object 



64
65
66
 
# File 'app/models/view_sales_net_base.rb', line 64

def self.refresh
  Scenic.database.refresh_materialized_view(table_name, concurrently: true, cascade: false)
end