Prepaid Delayed Downgrades, Item Removals, & Quantity Decreases

Conceptual Background

The application of mid-period downgrades, item removals, & quantity decreases on prepaid subscriptions result in the customer either over-paying for the period, or requiring a refund. To avoid this, we process these kind of updates at the end of the period. With the updates in place, BillingEvents can easily be generated for the next period, based on the new data.

For example, a customer has a gold-level subscription at $300 a month, & downgrades to a silver-level subscription at $150 a month. If the customer downgrades halfway through the month (assuming a 30-day month) & the change were applied immediately, then they’d either be overcharged by $75, or would be due a refund for $75. By delaying the downgrade until the end of the month, the customer maintains the higher level of access, & the new $150 per month price naturally take over in the next period.

Delayed Downgrade Walkthrough

The following subscription data shows a monthly-billed, prepaid subscription for the "Gold-Level Subscription" product that was purchased on Feb 25, 2025. Additionally, within the subscription’s first period, the customer requested a downgrade.

{
  "subscription_id": "01JQPTCC03FMS9Q1K4AJ6CJV45",
  "context_id": "01JQPTCC01AB2WR3KKJDZH4QES",
  "name": "Gold-Level Subscription",
  "root_item_ref_type": "BLC_PRODUCT",
  "root_item_ref": "01JATJQ8CQVB2EBJ4JCQVHG9JS",
  "payment_strategy": "PREPAID",
  "subscription_status": "ACTIVE",
  "user_ref_type": "BLC_ACCOUNT",
  "user_ref": "01JB03026VTXK0BA6043YQZ814",
  "alternate_user_ref_type": "BLC_ACCOUNT",
  "alternate_user_ref": "01JB03026VTXK0BA6043YQZ814",
  "subscription_src": "BLC_ORDER",
  "subscription_src_ref": "01JQPTCB1JECMQ0YN71NC9R56A",
  "secondary_source_type": "BLC_ORDER_ITEM",
  "secondary_source_ref": "01JQPTCBGCF8K0ZVPA8J7Q1CHZ",
  "period_type": "MONTHLY",
  "period_frequency": 1,
  "next_bill_date": "2025-03-25 00:00:00",
  "next_period": 2,
  "is_auto_renewal_enabled": "Y",
  "start_of_term_date": null,
  "end_of_term_date": null,
  "term_duration_length": null,
  "term_duration_type": null,
  "cancellation_policy_ref": "MY_CANCELLATION_POLICY",
  "trk_level": 100000,
  "periods": "[{\"period\": 1, \"billDate\": \"2025-02-25T00:00:00\", \"periodEndDate\": \"2025-03-24T23:59:59.999\", \"periodStartDate\": \"2025-02-25T00:00:00\"}]",
  "allow_auto_renew_modification": "Y",
  "fulfillment_workflow": "fulfillmentTypeA",
  "delayed_actions": "[{\"workflowName\":\"fulfillmentTypeADowngradeWorkflow\",\"actionFlow\":\"DOWNGRADE\",\"subscriptionId\":\"01JQPTCC01AB2WR3KKJDZH4QES\",\"orderId\":\"01JQPTES9BXTXFKE9CETFWKZD5\",\"orderFulfillmentId\":\"01JQPTESQN82HZJANWQ9N2GCNV\",\"actionType\":\"PREPAID_DOWNGRADE\",\"actionEffectiveType\":\"NEXT_BILL_DATE\",\"applicablePeriod\":2}]",
  ...
}

Note	The key details after the downgrade request: The subscription remains active & continues to reference the gold-level product We now have a DelayedSubscriptionAction (the `delayed_actions` entry) that describes the requested downgrade.

Further Dissecting the DelayedSubscriptionAction

[
  {
    "workflowName": "fulfillmentTypeADowngradeWorkflow",
    "actionFlow": "DOWNGRADE",
    "subscriptionId": "01JQPTCC01AB2WR3KKJDZH4QES",
    "orderId": "01JQPTES9BXTXFKE9CETFWKZD5",
    "orderFulfillmentId": "01JQPTESQN82HZJANWQ9N2GCNV",
    "actionType": "PREPAID_DOWNGRADE",
    "actionEffectiveType": "NEXT_BILL_DATE",
    "applicablePeriod": 2
  }
]

The DelayedSubscriptionAction was created by processing the 01JQPTES9BXTXFKE9CETFWKZD5 order & 01JQPTESQN82HZJANWQ9N2GCNV order fulfillment via the fulfillmentTypeADowngradeWorkflow workflow.
This action is scheduled to be applied at the next subscription bill date when the subscription’s 2nd period is being processed.

Processing the DelayedSubscriptionAction

From this data state, when the subscription billing job processes the subscription’s next bill date, it will recognize that the delayed downgrade is due & trigger a workflow to process the downgrade. More specifically, this is done via SubscriptionStatusBillingJobGenerator, which leverages SubscriptionStatusBillingJobGenerator#isDelayedActionDue to recognize the due downgrade, & triggers the workflow identified by DelayedSubscriptionAction#workflowName (fulfillmentTypeADowngradeWorkflow in this case), but now with the DELAYED_SUBSCRIPTION_ACTION subscription action flow.

Within the workflow, the DelayedActionSubscriptionModificationHandler is used to apply the DelayedSubscriptionAction to the subscription, & DefaultBillingEventGenerationService#generateBillingEventsForNextSubscriptionPeriod generates the next period’s BillingEvent based on the new items & prices.

Updated Subscription

{
  "subscription_id": "01JQPTCC03FMS9Q1K4AJ6CJV45",
  "context_id": "01JQPTCC01AB2WR3KKJDZH4QES",
  "name": "Silver-Level Product",
  "root_item_ref_type": "BLC_PRODUCT",
  "root_item_ref": "01JATJ8RM6Y5HNRA7P1AF4T5DF",
  "payment_strategy": "PREPAID",
  "subscription_status": "ACTIVE",
  "user_ref_type": "BLC_ACCOUNT",
  "user_ref": "01JB03026VTXK0BA6043YQZ814",
  "alternate_user_ref_type": "BLC_ACCOUNT",
  "alternate_user_ref": "01JB03026VTXK0BA6043YQZ814",
  "subscription_src": "BLC_ORDER",
  "subscription_src_ref": "01JQPV6V1JP6N6YK6ZM4TD3FVB",
  "secondary_source_type": "BLC_ORDER_ITEM",
  "secondary_source_ref": "01JQPV8JA94250RY18M0HBQE7P",
  "period_type": "MONTHLY",
  "period_frequency": 1,
  "next_bill_date": "2025-04-25 00:00:00",
  "next_period": 3,
  "is_auto_renewal_enabled": "Y",
  "start_of_term_date": null,
  "end_of_term_date": null,
  "term_duration_length": null,
  "term_duration_type": null,
  "cancellation_policy_ref": "MY_CANCELLATION_POLICY",
  "trk_level": 100000,
    "periods": "[{\"period\":1,\"billDate\":\"2025-02-25T00:00:00.00\",\"periodStartDate\":\"2025-02-25T00:00:00.00\",\"periodEndDate\":\"2025-03-24T23:59:59.999+00:00\"},{\"period\":2,\"billDate\":\"2025-03-25T00:00:00.00\",\"periodStartDate\":\"2025-03-25T00:00:00.00\",\"periodEndDate\":\"2025-04-24T23:59:59.999+00:00\"}]",
  "allow_auto_renew_modification": "Y",
  "fulfillment_workflow": "fulfillmentTypeA",
  "delayed_actions": "[{\"workflowName\":\"fulfillmentTypeADowngradeWorkflow\",\"actionFlow\":\"DOWNGRADE\",\"subscriptionId\":\"01JQPTCC01AB2WR3KKJDZH4QES\",\"orderId\":\"01JQPTES9BXTXFKE9CETFWKZD5\",\"orderFulfillmentId\":\"01JQPTESQN82HZJANWQ9N2GCNV\",\"actionType\":\"PREPAID_DOWNGRADE\",\"actionEffectiveType\":\"NEXT_BILL_DATE\",\"applicablePeriod\":2}]",
  ...
}

Note	Key Differences: `name` `root_item_ref` (updated to the silver-level product id) `subscription_src_ref` (the downgrade order id) `secondary_source_ref` (the downgrade order item id)

BillingEvents

source	subscription_period	bill_date	billing_cycle_start_date	billing_cycle_end_date	bill_total
SAMPLE-APP	1	2025-02-23 00:00:00	2025-02-23 00:00:00	2025-03-24 23:59:59.999	1348.00
SUBSCRIPTION_RENEWAL	2	2025-03-25 00:00:00	2025-03-25 00:00:00	2025-04-23 23:59:59.999	568.00

source

subscription_period

bill_date

billing_cycle_start_date

billing_cycle_end_date

bill_total

SAMPLE-APP

2025-02-23 00:00:00

2025-03-24 23:59:59.999

1348.00

SUBSCRIPTION_RENEWAL

2025-03-25 00:00:00

2025-04-23 23:59:59.999

568.00

BillingEventItems

source	subscription_period	item_name	item_unit_price	total_amount	quantity
SAMPLE-APP	1	Number of Users	100.00	100.00	1
SAMPLE-APP	1	Gold-Level Subscription	1248.00	1248.00	1
SUBSCRIPTION_RENEWAL	2	Number of Users	100.00	100.00	1
SUBSCRIPTION_RENEWAL	2	Silver-Level Subscription	468.00	468.00	1

Delayed Item Removal & Quantity Decrease Walkthrough

The following subscription data shows a monthly-billed, prepaid subscription for the gold-level product that was purchased on Feb 25, 2025. This subscription also includes Number of Users & Bonus Feature (optional) add-ons. Within the subscription’s first period, the customer requested to remove the Bonus Feature add-on & reduce the number of users from 4 to 2.

Subscription

{
  "subscription_id": "01JQPTCC03FMS9Q1K4AJ6CJV45",
  "context_id": "01JQPTCC01AB2WR3KKJDZH4QES",
  "name": "Gold-Level Subscription",
  "root_item_ref_type": "BLC_PRODUCT",
  "root_item_ref": "01JATJQ8CQVB2EBJ4JCQVHG9JS",
  "payment_strategy": "PREPAID",
  "subscription_status": "ACTIVE",
  "user_ref_type": "BLC_ACCOUNT",
  "user_ref": "01JB03026VTXK0BA6043YQZ814",
  "alternate_user_ref_type": "BLC_ACCOUNT",
  "alternate_user_ref": "01JB03026VTXK0BA6043YQZ814",
  "subscription_src": "BLC_ORDER",
  "subscription_src_ref": "01JQPTCB1JECMQ0YN71NC9R56A",
  "secondary_source_type": "BLC_ORDER_ITEM",
  "secondary_source_ref": "01JQPTCBGCF8K0ZVPA8J7Q1CHZ",
  "period_type": "MONTHLY",
  "period_frequency": 1,
  "next_bill_date": "2025-03-25 00:00:00",
  "next_period": 2,
  "is_auto_renewal_enabled": "Y",
  "start_of_term_date": null,
  "end_of_term_date": null,
  "term_duration_length": null,
  "term_duration_type": null,
  "cancellation_policy_ref": "MY_CANCELLATION_POLICY",
  "trk_level": 100000,
  "periods": "[{\"period\": 1, \"billDate\": \"2025-02-25T00:00:00\", \"periodEndDate\": \"2025-03-24T23:59:59.999\", \"periodStartDate\": \"2025-02-25T00:00:00\"}]",
  "allow_auto_renew_modification": "Y",
  "fulfillment_workflow": "fulfillmentTypeA",
  "delayed_actions": "[{\"workflowName\":\"fulfillmentTypeADowngradeWorkflow\",\"actionFlow\":\"DOWNGRADE\",\"subscriptionId\":\"01JQPTCC01AB2WR3KKJDZH4QES\",\"orderId\":\"01JQPTES9BXTXFKE9CETFWKZD5\",\"orderFulfillmentId\":\"01JQPTESQN82HZJANWQ9N2GCNV\",\"actionType\":\"PREPAID_DOWNGRADE\",\"actionEffectiveType\":\"NEXT_BILL_DATE\",\"applicablePeriod\":2}]",
  ...
}

Subscription Items

subscription_id	item_ref	item_name	item_unit_price	quantity
01JQPXW0M2T02HTZ6YDKSW5P39	01JATJQ8CQVB2EBJ4JCQVHG9JS	Gold-Level Subscription	1248.00	1
01JQPXW0M2T02HTZ6YDKSW5P39	01JATJ44AAVQ27P26375J8RGBA	Number of Users	100.00	4
01JQPXW0M2T02HTZ6YDKSW5P39	01JATJ44AAVQ27P26375J8RGBC	Bonus Feature	132.00	1

Further Dissecting the DelayedSubscriptionAction

[
  {
    "workflowName": "fulfillmentTypeAEditWorkflow",
    "actionFlow": "EDIT",
    "subscriptionId": "01JQPXW0M2T02HTZ6YDKSW5P39",
    "orderId": "01JQPXX81CBMQ0N4N6PRJ5YCK1",
    "orderFulfillmentId": "01JQPXX8CQVAE73SHTA41MBTTX",
    "actionType": "PREPAID_ITEM_REMOVAL",
    "actionEffectiveType": "NEXT_BILL_DATE",
    "applicablePeriod": 2,
    "attributes": {
      "subscriptionItemsToRemove": [
        "01JQPXW0MCE2GCR40S3ZKQSFWA"
      ]
    }
  },
  {
    "workflowName": "fulfillmentTypeAEditWorkflow",
    "actionFlow": "EDIT",
    "subscriptionId": "01JQPXW0M2T02HTZ6YDKSW5P39",
    "orderId": "01JQPXX81CBMQ0N4N6PRJ5YCK1",
    "orderFulfillmentId": "01JQPXX8CQVAE73SHTA41MBTTX",
    "actionType": "PREPAID_ITEM_UPDATE",
    "actionEffectiveType": "NEXT_BILL_DATE",
    "applicablePeriod": 2,
    "attributes": {
      "orderItemsToApply": [
        "01JQPXX7ZZZZW41S70BENHAHMC"
      ]
    }
  }
]

The first DelayedSubscriptionAction represents the item removal, & includes a list of subscriptionItemsToRemove in its attribute map. In this case, the Bonus Feature add-on (id: 01JQPXW0MCE2GCR40S3ZKQSFWA) is scheduled to be removed.
The second DelayedSubscriptionAction represents the quantity decrease for the Number of Users. In this delayed action, we record the order item that is to be updated.

Processing the DelayedSubscriptionAction

Updated Subscription

{
  "subscription_id": "01JQPTCC03FMS9Q1K4AJ6CJV45",
  "context_id": "01JQPTCC01AB2WR3KKJDZH4QES",
  "name": "Silver-Level Product",
  "root_item_ref_type": "BLC_PRODUCT",
  "root_item_ref": "01JATJ8RM6Y5HNRA7P1AF4T5DF",
  "payment_strategy": "PREPAID",
  "subscription_status": "ACTIVE",
  "user_ref_type": "BLC_ACCOUNT",
  "user_ref": "01JB03026VTXK0BA6043YQZ814",
  "alternate_user_ref_type": "BLC_ACCOUNT",
  "alternate_user_ref": "01JB03026VTXK0BA6043YQZ814",
  "subscription_src": "BLC_ORDER",
  "subscription_src_ref": "01JQPV6V1JP6N6YK6ZM4TD3FVB",
  "secondary_source_type": "BLC_ORDER_ITEM",
  "secondary_source_ref": "01JQPV8JA94250RY18M0HBQE7P",
  "period_type": "MONTHLY",
  "period_frequency": 1,
  "next_bill_date": "2025-04-25 00:00:00",
  "next_period": 4,
  "is_auto_renewal_enabled": "Y",
  "start_of_term_date": null,
  "end_of_term_date": null,
  "term_duration_length": null,
  "term_duration_type": null,
  "cancellation_policy_ref": "MY_CANCELLATION_POLICY",
  "trk_level": 100000,
    "periods": "[{\"period\":1,\"billDate\":\"2025-02-25T00:00:00.00\",\"periodStartDate\":\"2025-02-25T00:00:00.00\",\"periodEndDate\":\"2025-03-24T23:59:59.999+00:00\"},{\"period\":2,\"billDate\":\"2025-03-25T00:00:00.00\",\"periodStartDate\":\"2025-03-25T00:00:00.00\",\"periodEndDate\":\"2025-04-24T23:59:59.999+00:00\"},{\"period\":3,\"billDate\":\"2025-04-25T00:00:00.00\",\"periodStartDate\":\"2025-04-25T00:00:00.00\",\"periodEndDate\":\"2025-05-24T23:59:59.999+00:00\"}]",
  "allow_auto_renew_modification": "Y",
  "fulfillment_workflow": "fulfillmentTypeA",
  "delayed_actions": "[{\"workflowName\":\"fulfillmentTypeADowngradeWorkflow\",\"actionFlow\":\"DOWNGRADE\",\"subscriptionId\":\"01JQPTCC01AB2WR3KKJDZH4QES\",\"orderId\":\"01JQPTES9BXTXFKE9CETFWKZD5\",\"orderFulfillmentId\":\"01JQPTESQN82HZJANWQ9N2GCNV\",\"actionType\":\"PREPAID_DOWNGRADE\",\"actionEffectiveType\":\"NEXT_BILL_DATE\",\"applicablePeriod\":2}]",
  ...
}

Note	Key Differences: `name` `root_item_ref` (updated to the silver-level product id) `subscription_src_ref` (the downgrade order id) `secondary_source_ref` (the downgrade order item id)

Updated Subscription Items

subscription_id	item_ref	item_name	item_unit_price	quantity
01JQPXW0M2T02HTZ6YDKSW5P39	01JATJQ8CQVB2EBJ4JCQVHG9JS	Gold-Level Subscription	1248.00	1
01JQPXW0M2T02HTZ6YDKSW5P39	01JATJ44AAVQ27P26375J8RGBA	Number of Users	100.00	2

BillingEvents

source	subscription_period	bill_date	billing_cycle_start_date	billing_cycle_end_date	bill_total
SAMPLE-APP	1	2025-02-23 00:00:00	2025-02-23 00:00:00	2025-03-24 23:59:59.999	1780.00
SUBSCRIPTION_RENEWAL	2	2025-03-25 00:00:00	2025-03-25 00:00:00	2025-04-23 23:59:59.999	1448.00

source

subscription_period

bill_date

billing_cycle_start_date

billing_cycle_end_date

bill_total

SAMPLE-APP

2025-02-23 00:00:00

2025-03-24 23:59:59.999

1780.00

SUBSCRIPTION_RENEWAL

2025-03-25 00:00:00

2025-04-23 23:59:59.999

1448.00

BillingEventItems

source	subscription_period	item_name	item_unit_price	total_amount	quantity
SAMPLE-APP	1	Bonus Feature	132.00	132.00	1
SAMPLE-APP	1	Gold-Level Subscription	1248.00	1248.00	1
SAMPLE-APP	1	Number of Users	100.00	400.00	4
SUBSCRIPTION_RENEWAL	2	Gold-Level Subscription	1248.00	1248.00	1
SUBSCRIPTION_RENEWAL	2	Number of Users	100.00	200.00	2

Managing Delayed Actions

When a subscription has a delayed action due at the end of the current period, the following is possible:

If the delayed action is a downgrade, then the customer…
- Can modify the delayed downgrade. This should re-enter a downgrade flow, ideally starting with the prior delayed downgrade selections.
- Can cancel the delayed downgrade - i.e. remove the DelayedSubscriptionAction representing the delayed downgrade.
- Can upgrade.
  - Note: behind the scenes, this will remove the delayed downgrade.
- Can NOT edit. The downgrade will reset the landscape of the subscription, so editing the current subscription really doesn’t make sense. If they really want to edit, they can cancel the delayed downgrade, & then edit.
If the delayed action is an item removal and/or quantity decrease, then the customer…
- Can downgrade or upgrade.
  - Note: behind the scenes, this will remove existing delayed item removal and/or quantity decrease
- Can edit.
  - This should re-enter an edit flow, ideally starting with the prior delayed edit selections.

Testing Notes

Before each of the scenarios described below, I start with updating the subscription products to prepaid.

Delayed Action at End of Typical Period

Sign up for Gold-Level Subscription using a monthly billing frequency
Request a downgrade to silver level (sets up delayed action to be processed with the creation of period 2 billing data)
Modify the dates on the subscription & billing data to be 1 month in the past (makes the delayed action due)
Run the BILL_DUE_SUBSCRIPTIONS job (using the Run Now action in the Broadleaf Admin)

Delayed Action at End of Terms (Auto Renewal)

Sign up for Gold-Level Subscription using a monthly billing frequency & 2-month terms
Modify the dates on the subscription & billing data to be 1 month in the past
Request a downgrade to silver level (sets up delayed action to be processed with the creation of period 3 billing data - i.e. with auto-renewal)
Modify the dates on the subscription & billing data to be 1 month in the past (makes the delayed action due)
Run the BILL_DUE_SUBSCRIPTIONS job (using the Run Now action in the Broadleaf Admin)

Tip	Once finished with a test scenario, I’d clear the subscription, billing, & workflow data to start with a clean slate for my next test.

Helpful Sql Commands

Rest Subscription & BillingEvent to X Days/Months In Past

Postgres Version

-- Set Subscription Bill Dates back by specified number of days
-- SET interval.days TO '30 DAY';
SET interval.days TO '1 month';

update billing.subscription
set next_status_change_date = next_status_change_date - current_setting('interval.days')::interval,
next_bill_date = next_bill_date - current_setting('interval.days')::interval,
prior_bill_date = prior_bill_date - current_setting('interval.days')::interval,
end_of_term_date = end_of_term_date - current_setting('interval.days')::interval,
start_of_term_date = start_of_term_date - current_setting('interval.days')::interval,
created_date = created_date - current_setting('interval.days')::interval;

-- put billing events back by specified number of days
update billing.billing_event
set bill_date = bill_date - current_setting('interval.days')::interval,
billing_cycle_start_date = billing_cycle_start_date - current_setting('interval.days')::interval,
billing_cycle_end_date = billing_cycle_end_date - current_setting('interval.days')::interval;

-- ************** WHEN THERE ARE TWO PERIODS **************
-- period 1 & period 2 bill dates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,billDate}',
     to_jsonb((periods::jsonb->0->>'billDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,billDate}',
   	to_jsonb((periods::jsonb->1->>'billDate')::timestamp - current_setting('interval.days')::interval)
);

-- period 1 & period 2 periodEndDates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,periodEndDate}',
     to_jsonb((periods::jsonb->0->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,periodEndDate}',
   	to_jsonb((periods::jsonb->1->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
);

-- period 1 & period 2 periodStartDates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,periodStartDate}',
     to_jsonb((periods::jsonb->0->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,periodStartDate}',
   	to_jsonb((periods::jsonb->1->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
);
-- ************** END **************

-- ************** WHEN THERE IS ONLY ONE PERIOD **************
-- period 1 ONLY bill dates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,billDate}',
     to_jsonb((periods::jsonb->0->>'billDate')::timestamp - current_setting('interval.days')::interval)
   );

-- period 1 ONLY periodEndDates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,periodEndDate}',
     to_jsonb((periods::jsonb->0->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
   );

-- period 1 ONLY periodStartDates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,periodStartDate}',
     to_jsonb((periods::jsonb->0->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
   );
-- ************** END **************

Oracle Version

DECLARE
    -- build an INTERVAL once, then reuse it
--     shift_intervals  INTERVAL DAY(3) TO SECOND := NUMTODSINTERVAL(4, 'DAY');
    shift_intervals    INTERVAL YEAR(2) TO MONTH       := NUMTOYMINTERVAL(1, 'MONTH');
BEGIN
    -- shift all the date/timestamps:
    UPDATE BILLINGSCHEMA.subscription
    SET
        next_status_change_date = next_status_change_date - shift_intervals,
        next_bill_date          = next_bill_date - shift_intervals,
        prior_bill_date         = prior_bill_date - shift_intervals,
        end_of_term_date        = end_of_term_date - shift_intervals,
        start_of_term_date      = start_of_term_date - shift_intervals,
        created_date            = created_date - shift_intervals;

    -- put billing events back by specified number of days
    UPDATE BILLINGSCHEMA.billing_event
    SET bill_date = bill_date - shift_intervals,
        billing_cycle_start_date = billing_cycle_start_date - shift_intervals,
        billing_cycle_end_date = billing_cycle_end_date - shift_intervals;

    UPDATE BILLINGSCHEMA.subscription s
    SET periods = (
        SELECT JSON_ARRAYAGG(
                       JSON_OBJECT(
                               KEY 'period'           VALUE jsontable.period,
                               KEY 'billDate'         VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.billDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'periodStartDate'  VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.periodStartDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'periodEndDate'    VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.periodEndDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'freeTrial'        VALUE jsontable.freeTrial
                           )
                       ORDER BY jsontable.period
                   )
        FROM JSON_TABLE(
                     s.periods,
                     '$[*]'
                     COLUMNS (
                         period            NUMBER        PATH '$.period',
                         billDate          VARCHAR2(30)  PATH '$.billDate',
                         periodStartDate   VARCHAR2(30)  PATH '$.periodStartDate',
                         periodEndDate     VARCHAR2(30)  PATH '$.periodEndDate',
                         freeTrial         VARCHAR2(5)   PATH '$.freeTrial'
                         )
                 ) jsontable
    );
END;

Clear All Subscription, BillingEvent, & Workflow Data

Postgres Version

DELETE FROM billing.subscription_status_audit;
DELETE FROM billing.subscription_lock;
DELETE FROM billing.subscription_item_adjustment;
DELETE FROM billing.subscription_item;
DELETE FROM billing.subscription_billing_audit;
DELETE FROM billing.subscription;

DELETE FROM billing.billing_event_item_adjustment;
DELETE FROM billing.billing_event_item_attr;
DELETE FROM billing.billing_event_item;
DELETE FROM billing.billing_event_payment;
DELETE FROM billing.billing_event;

DELETE FROM workflow.blc_side_effect;
DELETE FROM workflow.blc_context_value;
DELETE FROM workflow.blc_activity_log;
DELETE FROM workflow.blc_notification_state;
DELETE FROM workflow.blc_resource_lock;
DELETE FROM workflow.blc_workflow;

Oracle Version

-- DELETE ALL Subscriptions
DELETE FROM BILLINGSCHEMA.subscription_status_audit;
DELETE FROM BILLINGSCHEMA.subscription_lock;
DELETE FROM BILLINGSCHEMA.subscription_item_adjustment;
DELETE FROM BILLINGSCHEMA.subscription_item;
DELETE FROM BILLINGSCHEMA.subscription_billing_audit;
DELETE FROM BILLINGSCHEMA.subscription;

DELETE FROM BILLINGSCHEMA.billing_event_item_adjustment;
DELETE FROM BILLINGSCHEMA.billing_event_item_attr;
DELETE FROM BILLINGSCHEMA.billing_event_item;
DELETE FROM BILLINGSCHEMA.billing_event_payment;
DELETE FROM BILLINGSCHEMA.billing_event;

-- DELETE ALL Workflows
DELETE FROM WORKFLOWSCHEMA.blc_side_effect;
DELETE FROM WORKFLOWSCHEMA.blc_context_value;
DELETE FROM WORKFLOWSCHEMA.blc_activity_log;
DELETE FROM WORKFLOWSCHEMA.blc_notification_state;
DELETE FROM WORKFLOWSCHEMA.blc_resource_lock;
DELETE FROM WORKFLOWSCHEMA.blc_workflow;