Generates "Revenue Suspense" report showing owners with revenue held in suspense pending resolution. Used to trigger "show me suspense" or "revenue held".
Generate the revenue suspense summary from FO_PRODUCTION_DB.GOLD_FINANCIAL.gold_fct_owner_revenue_detail.
Gather from the user:
as_of_date: As-of date for the report (YYYY-MM-DD, required)company_code: Optional, default "200" (Formentera Operations LLC)owner_code: Optional, filter to specific owner(s)level: Optional, report level (default: "owner", or "well" for well-level detail)SELECT
owner_code,
owner_name,
COUNT(DISTINCT well_code) AS well_count,
COUNT(DISTINCT production_date) AS production_month_count,
SUM(net_value) AS net_value,
SUM(total_tax) AS total_tax,
SUM(net_deductions) AS net_deductions,
SUM(amount_suspended) AS amount_suspended,
LISTAGG(DISTINCT suspense_reason, '; ') AS suspense_reasons
FROM FO_PRODUCTION_DB.GOLD_FINANCIAL.GOLD_FCT_OWNER_REVENUE_DETAIL
WHERE amount_suspended > 0
AND company_code = :company_code
AND production_date <= :as_of_date
GROUP BY owner_code, owner_name
ORDER BY amount_suspended DESC, owner_name
SELECT
owner_code,
owner_name,
well_code,
well_name,
production_date,
product,
net_volume,
net_value,
total_tax,
net_deductions,
item_suspense,
owner_suspense,
well_suspense,
amount_suspended,
suspense_reason
FROM FO_PRODUCTION_DB.GOLD_FINANCIAL.GOLD_FCT_OWNER_REVENUE_DETAIL
WHERE amount_suspended > 0
AND company_code = :company_code
AND production_date <= :as_of_date
ORDER BY owner_code, well_code, production_date
Standard filters: WHERE amount_suspended > 0 AND company_code = :company_code AND production_date <= :as_of_date
Add optional owner filter: AND owner_code IN (:owner_codes)
The model tracks three levels of suspension:
item_suspense: Line-item level hold (revenue to check, product validation)owner_suspense: Owner-level hold (accounting review, documentation)well_suspense: Well-level hold (operational issue, shut-in)All three aggregate into amount_suspended in the output.
Owner Level: Owner Code, Owner Name, Well Count, Production Months, Net Value, Total Tax, Net Deductions, Amount Suspended, Suspense Reason
Well Level: Owner Code, Owner Name, Well Code, Well Name, Production Date, Product, Net Volume, Item Suspense, Owner Suspense, Well Suspense, Net Value, Total Tax, Net Deductions, Amount Suspended, Suspense Reason
Revenue Suspense - {level} - {as_of_date}.xlsx