Bill Cycle Progress

This script is used to check the progress of the bill cycle run.

It provides information on the number of accounts that have been billed. The script also provides information on the number of accounts that have failed to be billed, categorized by reason code.

Additionally, the script calculates basic metrics on the performance of the bill cycle run, such as the average time taken to bill an account and the total time taken for the bill cycle run.

The script is essential for monitoring the efficiency and effectiveness of the billing process, helping to identify any issues or bottlenecks that may arise.

Prerequisites

Billing for a particular Bill Cycle Run must be In Progress.

Step-by-Step Instructions

Preparation

Log in to the system as a user with privileges to OCS Admin objects.
Run the following command to list the bill cycle runs currently in progress:

-- Bill cycle runs currently in progress
SELECT bc.name, bc.internal_code, bc.bill_cycle_type, to_fk(bcr.id, bcr.ref_id) AS "bcr_fk", bcr.bill_cycle_type, bcr.bill_cycle_status, bcr.billing_period_start, bcr.billing_period_end
FROM bill_cycle bc, bill_cycle_run bcr
WHERE bcr.bill_cycle_id = to_fk(bc.id, bc.ref_id)
AND bcr.bill_cycle_status = 'IN_PROGRESS'
ORDER BY bc.internal_code, bcr.bill_cycle_type, bcr.billing_period_start;

-- -> pass bcr_fk to :bcRunFk

Pass the bcr_fk from the output of the above query to the variable bcRunFk.

Summary of the Bill Cycle Run

To check a brief summary over account semaphore, run the following query:

-- Group by account_semaphore
SELECT account_semaphore, COUNT(*)
FROM account_bill_cycle_run
WHERE bill_cycle_run_id = :bcRunFk
GROUP BY account_semaphore;

The output of the above query will show the number of accounts in each state.

Summary by Reason

To check the summary over reason, run the following query:

-- ABCR by reason
SELECT COALESCE(reason, CASE account_semaphore WHEN 'INVOICING_FINISHED' THEN 'INVOICED' WHEN 'WAITING' THEN 'WAITING' ELSE 'IN-PROGRESS' END) AS "reason", COUNT(*)
FROM account_bill_cycle_run abcr
WHERE bill_cycle_run_id = :bcRunFk
GROUP BY account_semaphore, reason;

The output of the above query will show the number of accounts that have been billed with errors categorized by reason code, together with the number of accounts billed successfully, and the number of accounts in progress.

Performance Measures

To check the basic performance measures of the bill cycle run, run the following query:

-- Performance - min, max, avg, total duration
SELECT MIN(invoicing_duration), MAX(invoicing_duration), AVG(invoicing_duration), MAX(invoicing_start) - MIN(invoicing_start) AS "duration", COUNT(*), SUM(CASE account_semaphore WHEN 'INVOICE_CALCULATED' THEN 1 WHEN 'INVOICE_NOT_GENERATED' THEN 1 ELSE 0 END) AS "done", SUM(CASE account_semaphore WHEN 'INVOICE_CALCULATED' THEN 0 WHEN 'INVOICE_NOT_GENERATED' THEN 0 ELSE 1 END) AS "in progress"
FROM account_bill_cycle_run
WHERE bill_cycle_run_id = :bcRunFk;

The output of the above query will show the minimum, maximum, and average time taken to bill an account, the total time taken for the bill cycle run, the number of accounts billed, and the number of accounts to be billed.

Median Performance Measures

To check the median performance measure of the bill cycle run, run the following command:

-- Performance - median (percentile 50%)
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY invoicing_duration)
FROM account_bill_cycle_run abcr
WHERE bill_cycle_run_id = :bcRunFk
AND (reason IS NULL OR reason IN ('RM-DOCUMENT-DISCARDED', 'INV-CFG'));

The output of the above query will show the median (percentile 50%) time taken to bill an account.

Erroneous Accounts

To check the accounts that have failed to be billed, run the following query:

-- Erroneous accounts
SELECT a.external_id, abcr.reason, abcr.result
FROM account_bill_cycle_run abcr, account a
WHERE abcr.bill_cycle_run_id = :bcRunFk
AND abcr.account_semaphore = 'INVOICE_ERROR'
AND a.id = from_fk(abcr.account_id);

The output of the above query will show the external ID of the accounts that have failed to be billed, together with the reason code and textual description of the error in the result.