CloudBlue Technical Support

Symptoms

Resource is not refunded in BBP (i.e. Before Billing Period) subscription in case the billing order was processed before resource upgrade order

Issue may afffect all CH orders created before 7.0.

Issue affects the installations upgraded from versions 6.0.x to 7.0, and won't appear in 7.0 or later.

Cause

In OA 6.0 for the upgraded order item, the field EndDate is set to subscription expiration date.

Resolution

To fix the issue for CH orders that were placed before upgrade to 7.0 and were not processed (or processed after upgrade), the following actions are required:

1. Connect to the Billing Database node.

2. Find subscriptions (BBP subscriptions only), change orders and order items that could have broken deliver lines:

   SELECT sub."subscriptionID", sub."Status", sub."NextBillDate", sub."NextBillDate"::abstime, so."OrderID", so."OrderStatusID", so."OrderDate", oi."OIID", oi."StartDate"::abstime, oi."EndDate"::abstime
   FROM "OItem" oi
       JOIN "SalesOrder" so on so."OrderID" = oi."OrderDocOrderID" 
       JOIN "Subscription" sub on sub."subscriptionID" = oi."subscriptionID" 
       CROSS JOIN (
           SELECT "InstallDate" FROM "BMVersion" WHERE "Version" LIKE '7.%' ORDER BY "InstallDate" ASC LIMIT 1
       ) AS ver
   WHERE so."OrderTypeID" IN ('CH', 'UG', 'DG') 
       AND so."OrderDate" < ver."InstallDate" 
       AND (so."OrderStatusID" NOT IN ('CP', 'CL') OR (so."OrderStatusID" = 'CP' AND so."StatusChangeTime" > ver."InstallDate")
       )
       AND sub."RecurringType" = 10
       AND oi."EndDate" > (sub."NextBillDate" + 86400)
   ;
   

3. If EndDate in OrderItem is significantly greater than subscription's NextBillDate, fix EndDate value for corresponding OItem(s) to the first date of Billing Order created after CH.

   • Find first billing order date:

     SELECT so."OrderID", so."OrderDate" 
     FROM "SalesOrder" so 
         JOIN "OItem" oi ON oi."OrderDocOrderID" = so."OrderID" 
     WHERE so."OrderTypeID" IN ('BO', 'ZB') 
         AND oi."subscriptionID" = <subscription ID> 
         AND so."OrderDate" > <date of CH order> 
     ORDER BY so."OrderDate" LIMIT 1;
     

   • Then update OItem.EndDate:

     UPDATE "OItem"  SET "EndDate" = <date of nearest BO>  WHERE "OrderDocOrderID" = <CH Order ID>;
     

     If no BO found, expression above should use subscription NextBillDate.

4. For subscriptions with completed orders, fix deliver lines:

   • a. Find DLs for customers:

     SELECT dl."DeliverLineID", dl."DetID", dl."DLStatus", dl."ExtendedPrice_Value",  dl."UnitPrice_Value", dl."ResourceAmount", dl."Duration", dl."DLStartDate"::abstime, dl."DLEndDate"::abstime 
     FROM "DeliverLine" dl
         JOIN "Subscription" sub ON sub."subscriptionID" = dl."subscriptionID"
     WHERE sub."subscriptionID" = <subscriptionID>
         AND dl."resourceID" IS NOT NULL 
         AND dl."AccountID" = sub."AccountID" 
         ORDER BY "DeliverLineID" DESC;
     

   • b. Determine and fix broken deliver lines:

     In the simpliest case, the whole deliver line for the period is deleted (DLStatus = 999), no new deliver lines for this period is created. In this case, a Deliver Line should be restored:

     UPDATE "DeliverLine" SET "DLStatus" = 1 where "DeliverLineID" = <Broken deliver line ID> ;
     

     More complex case: if CH order is completed, there, probably, can be other orders that generated new deliver lines over the broken line. In this case, heuristic analyse is needed: for some lines, DLStatus should be set to '1', and ResourceAmount for some lines should be changed depending on the situation.

   • c. Find DLs for vendors:

     SELECT dl."DeliverLineID", dl."DetID", dl."DetType", dl."DLStatus", dl."ExtendedPrice_Value",  dl."UnitPrice_Value", dl."ResourceAmount", dl."Duration", dl."DLStartDate"::abstime, dl."DLEndDate"::abstime 
     FROM "DeliverLine" dl
         JOIN "Subscription" sub ON sub."subscriptionID" = dl."subscriptionID"
     WHERE sub."subscriptionID" = <subscriptionID>
         AND dl."resourceID" IS NOT NULL 
         AND dl."AccountID" <> sub."AccountID" 
         ORDER BY "DeliverLineID" DESC;
     

   • d. Fix deliver lines for vendors as in b.
5. Check subscription cancellation with full refund, if possible: try to cancel a subscription but do not place an order, check that all expected resources are present in order details.

Internal content