Business Automation: Find subscriptions which are not Auto-renewed.

Tested on Version: Business Automation 6.x

In-order to find the subscription's with auto-renewal enabled and expiration date = today (or in past) and are not auto-renewed, you can run the following query in Odin Business Automation Database:

pba=> SELECT s."subscriptionID", s."PlanID", s."AccountID", a."VendorAccountID" AS "Reseller ID", s."Status", s."ServStatus", s."renewalOrderOrderID" AS "Renewal Order ID", so."OrderStatusID", s."ExpirationDate"::ABSTIME::DATE,s."ShutdownDate"::ABSTIME::DATE, s."TerminateDate"::ABSTIME::DATE FROM "Subscription" s INNER JOIN "Account" a ON (s."AccountID" = a."AccountID") LEFT JOIN "SalesOrder" so ON (s."renewalOrderOrderID" IS NOT NULL AND s."renewalOrderOrderID" = so."OrderID") WHERE s."Status" IN (30) AND s."IsAutoRenew" = 1 AND s."ExpirationDate"::ABSTIME <= now()::ABSTIME::DATE ORDER BY s."subscriptionID";


To count the number of subscriptions which are not auto-renewed:

pba=> SELECT count(*) FROM "Subscription" as s INNER JOIN "Account" a ON (s."AccountID" = a."AccountID") LEFT JOIN "SalesOrder" so ON (s."renewalOrderOrderID" IS NOT NULL AND s."renewalOrderOrderID" = so."OrderID") WHERE s."Status" IN (30) AND s."IsAutoRenew" = 1 AND s."ExpirationDate"::ABSTIME <= now()::ABSTIME::DATE;
 count
-------
     0
(1 row)

pba=>