Introduction/ Issue:
Our finance operations team had no easy way to see how AR invoices were being created across the day. When a customer complained about a delayed invoice, or when the nightly AutoInvoice/interface job seemed to be taking longer than usual, there was no quick report showing how many invoices (and how many invoice lines) each batch source generated, hour by hour, over recent days.
Why we need to do / Cause of the issue:
Invoices reach Receivables through several batch sources – some loaded by interface/AutoInvoice jobs that run on a fixed schedule, others created manually by users during business hours. Without an hourly, per-source breakdown it was difficult to tell whether a particular source was creating an unusual burst of invoices at a specific hour, whether invoices carried an abnormal number of lines (a sign of bad data or a mapping issue upstream), or whether a slowdown was isolated to one source or affected the whole creation process. This made root-causing delays and planning batch-job timing largely guesswork.
How do we solve:
We built a single SQL report on top of ra_customer_trx_all, ra_customer_trx_lines_all and ra_batch_sources_all that gives a per-source, per-day, hour-by-hour view of invoice creation, along with line-count quality metrics. The query is structured in three steps:
1) inv_lines – counts the number of invoice lines per transaction from ra_customer_trx_lines_all. 2) base_data – joins invoice headers (trx_class = ‘INV’) from the last 60 days to their batch source name and brings in the line count, defaulting to zero where a transaction has no lines. 3) prepared – derives the calendar day and the creation hour (00-23) from the creation date so they can be used for grouping. The final SELECT pivots the hour into 24 columns (h00 … h23), each counting the distinct invoices created in that hour for that source and day, using COUNT(DISTINCT CASE WHEN hour_val = ‘xx’ THEN customer_trx_id END). Alongside the hourly breakdown, it rolls up the total invoices for the day (per_day), the total invoice lines created (total_line_count), the average lines per invoice, and the peak lines on any single invoice that day – all grouped by source and day, most recent day first.
The report query:
WITH inv_lines AS (
SELECT
customer_trx_id,
COUNT(*) AS line_count
FROM ra_customer_trx_lines_all
GROUP BY customer_trx_id
),
base_data AS (
SELECT
rbs.name AS source,
rcta.creation_date creation_pst,
rcta.customer_trx_id,
NVL(il.line_count, 0) AS line_count
FROM ra_customer_trx_all rcta
JOIN ra_batch_sources_all rbs
ON rcta.batch_source_seq_id = rbs.batch_source_seq_id
LEFT JOIN inv_lines il
ON il.customer_trx_id = rcta.customer_trx_id
WHERE rcta.creation_date >= TRUNC(SYSDATE) – 60
AND rcta.trx_class = ‘INV’ — only invoices
),
prepared AS (
SELECT
source,
TRUNC(CAST(creation_pst AS DATE)) AS day_bucket,
TO_CHAR(creation_pst, ‘HH24’) AS hour_val,
customer_trx_id,
line_count
FROM base_data
)
SELECT
source,
day_bucket,
TO_CHAR(day_bucket, ‘Dy’) AS day,
COUNT(DISTINCT CASE WHEN hour_val = ’00’ THEN customer_trx_id END) h00,
COUNT(DISTINCT CASE WHEN hour_val = ’01’ THEN customer_trx_id END) h01,
COUNT(DISTINCT CASE WHEN hour_val = ’02’ THEN customer_trx_id END) h02,
COUNT(DISTINCT CASE WHEN hour_val = ’03’ THEN customer_trx_id END) h03,
COUNT(DISTINCT CASE WHEN hour_val = ’04’ THEN customer_trx_id END) h04,
COUNT(DISTINCT CASE WHEN hour_val = ’05’ THEN customer_trx_id END) h05,
COUNT(DISTINCT CASE WHEN hour_val = ’06’ THEN customer_trx_id END) h06,
COUNT(DISTINCT CASE WHEN hour_val = ’07’ THEN customer_trx_id END) h07,
COUNT(DISTINCT CASE WHEN hour_val = ’08’ THEN customer_trx_id END) h08,
COUNT(DISTINCT CASE WHEN hour_val = ’09’ THEN customer_trx_id END) h09,
COUNT(DISTINCT CASE WHEN hour_val = ’10’ THEN customer_trx_id END) h10,
COUNT(DISTINCT CASE WHEN hour_val = ’11’ THEN customer_trx_id END) h11,
COUNT(DISTINCT CASE WHEN hour_val = ’12’ THEN customer_trx_id END) h12,
COUNT(DISTINCT CASE WHEN hour_val = ’13’ THEN customer_trx_id END) h13,
COUNT(DISTINCT CASE WHEN hour_val = ’14’ THEN customer_trx_id END) h14,
COUNT(DISTINCT CASE WHEN hour_val = ’15’ THEN customer_trx_id END) h15,
COUNT(DISTINCT CASE WHEN hour_val = ’16’ THEN customer_trx_id END) h16,
COUNT(DISTINCT CASE WHEN hour_val = ’17’ THEN customer_trx_id END) h17,
COUNT(DISTINCT CASE WHEN hour_val = ’18’ THEN customer_trx_id END) h18,
COUNT(DISTINCT CASE WHEN hour_val = ’19’ THEN customer_trx_id END) h19,
COUNT(DISTINCT CASE WHEN hour_val = ’20’ THEN customer_trx_id END) h20,
COUNT(DISTINCT CASE WHEN hour_val = ’21’ THEN customer_trx_id END) h21,
COUNT(DISTINCT CASE WHEN hour_val = ’22’ THEN customer_trx_id END) h22,
COUNT(DISTINCT CASE WHEN hour_val = ’23’ THEN customer_trx_id END) h23,
COUNT(DISTINCT customer_trx_id) AS per_day,
SUM(line_count) AS total_line_count,
ROUND(SUM(line_count) / NULLIF(COUNT(DISTINCT customer_trx_id), 0), 2) AS avg_lines_per_invoice,
MAX(line_count) AS peak_lines_per_invoice
FROM prepared
GROUP BY
source,
day_bucket
ORDER BY
source,
day_bucket DESC
Conclusion:
This report turns a manual, table-by-table investigation into a single query that gives a clear, hour-level heatmap of invoice creation per batch source for the last 60 days, together with average and peak line counts per invoice. It lets the team quickly spot unusual creation spikes, confirm whether a batch job ran on schedule, and flag invoices with abnormally high line counts – cutting down significantly on the time spent tracing invoice-creation issues back to their source.
