Generates AFE (Authorization for Expenditure) summary showing budget versus actual spend and variance. Queries gold_dim_afe and gold_fct_afe_budget from GOLD_FINANCIAL with filtering by company, AFE number, and status. Supports status filtering (open, closed, all) and highlights over-budget items.
Budget versus actual cost tracking for Authorizations for Expenditure (AFEs). Shows completion status, budget variance, and variance percentage. Highlights over-budget AFEs for cost control visibility.
SELECT
a.afe_number,
a.afe_description,
a.well_code,
a.well_name,
a.afe_status,
b.budget_amount,
COALESCE(b.actual_amount, 0) AS actual_amount,
(b.budget_amount - COALESCE(b.actual_amount, 0)) AS variance,
ROUND(100.0 * COALESCE(b.actual_amount, 0) / NULLIF(b.budget_amount, 0), 1) AS pct_complete,
CASE
WHEN COALESCE(b.actual_amount, 0) > b.budget_amount THEN 'OVER'
WHEN COALESCE(b.actual_amount, 0) >= (b.budget_amount * 0.9) THEN 'CAUTION'
ELSE 'OK'
END AS status_flag
FROM GOLD_FINANCIAL.gold_dim_afe a
LEFT JOIN GOLD_FINANCIAL.gold_fct_afe_budget b
ON a.afe_id = b.afe_id
WHERE a.company_code = ?
{AND a.afe_number = ? IF afe_number provided}
{AND a.afe_status = ? IF status provided AND status != 'all'}
ORDER BY a.afe_status, a.afe_number
✅ Gold Models Exist:
gold_dim_afe
afe_id (PK)
afe_number (unique)
afe_description
well_id (FK to gold_dim_wells)
well_code
well_name
afe_status (open | closed | superseded)
company_code
created_date
approved_date
effective_date
gold_fct_afe_budget
afe_id (FK to gold_dim_afe)
period
budget_amount
actual_amount
committed_amount
variance_amount
updated_date