Symptoms

When attempting to delete a resource from Operations Automation (OA), one of the errors below occurs:

1 resource types can not be deleted, 0 resource types were deleted successfully. Reason: PBA reported error while trying to remove resource type. Error: -1, 'Can not remove Resource #ID. The Resource is used.'..

1 resource types can not be deleted, 0 resource types were deleted successfully. Reason: java.lang.IllegalStateException: observer call failed.

Cause

In the Business Automation (BA) database, there is a reference to this resource. To find the exact error, it is necessary to tail /usr/local/bm/log/BM.log (in BA 5.5), /usr/local/bm/log/generic_worker.log (in BA 6.0), /var/log/pa/billing.log (BA 7.x) on the BA application server while reproducing the error.

Examples

  1. There is a reference to the resource in the financial document:

    [14-06-11 12:28:44.298 RDBMS       RQ198567 NTE] Code: 1002. ODBC code #23503: ERROR: update or delete on table "BMResource" violates foreign key constraint "DocDet_resourceID" on table "DocDet"
Key (resourceID)=(ID) is still referenced from table "DocDet".;

  2. There is a reference in the order:

    [14-06-11 12:37:22.153 RDBMS       RQ198715 NTE] Code: 1002. ODBC code #23503: ERROR: update or delete on table "BMResource" violates foreign key constraint "OItemUpgrade_resourceID" on table "OItemUpgrade"
Key (resourceID)=(ID) is still referenced from table "OItemUpgrade".;

    or 

    [14-06-11 12:47:51.114 RDBMS       RQ198885 NTE] Code: 1002. ODBC code #23503: ERROR: update or delete on table "BMResource" violates foreign key constraint "OrdDet_resourceID" on table "OrdDet"
Key (resourceID)=(ID) is still referenced from table "OrdDet".;

  3. The resource is present in the subscription(s):

    [14-06-11 12:54:14.772 RDBMS       RQ198996 NTE] Code: 1002. ODBC code #23503: ERROR: update or delete on table "BMResource" violates foreign key constraint "SubscrParam_resourceID" on table "SubscrParam"
Key (resourceID)=(ID) is still referenced from table "SubscrParam".;

  4. Resource #xxxxx can not be removed. It is used in service plan(s) (Some plans may belong to other accounts and may not be visible to you). #xxxx

    check resource rates:

    pba=> select * from "PlanRate" where "resourceID" IN (select "resourceID" from "IncludedResources" where "resourceID" = xxxx)
pba=> select * from "PlanRate" where "resourceID" = xxxx

  5. The record is left in the tariffs table:

    ERROR:  update or delete on table "BMResource" violates foreign key constraint "TrfResPrice_resourceID" on table "TrfResPrice"
DETAIL:  Key (resourceID)=(ID) is still referenced from table "TrfResPrice".

  6. Record is present in the table ResItem

    ERROR:  update or delete on table "BMResource" violates foreign key constraint "ResItem_resourceID" on table "ResItem"
DETAIL:  Key (resourceID)=(ID) is still referenced from table "ResItem".

Resolution

The appropriate resolution depends on the exact error. Find the appropriate error in the list above, then check the corresponding resolution below:

  1. There is no way to remove or modify an invoice. If this invoice belongs to a real customer, it is recommended that you leave the resource in the system. BA, as a billing system, needs to track all financial operations. If this is a test invoice, this line can be deleted from the BA database (no recalculations will be made, but the record preventing the resource removal will be deleted). If this operation in the BA database is required, please contact the Odin Support Team for assistance.

  2. Find the affected order(s) in the BA database:

    pba=> select * from "OItemUpgrade" where "resourceID" = <id>;
pba=> select "OrderID" from "OrdDet" where "resourceID" = <id>;

    Archive them and try to remove the resource from OA again.

  3. Find the list of subscriptions and accounts they belong to:

    pba=> select a."subscriptionID", b."AccountID", b."Status", b."ServStatus", b."PlanID" from "SubscrParam" as a join "Subscription" as b on a."subscriptionID"=b."subscriptionID" where a."resourceID" = <ID>

    Status - subscription status (10 - Ordered, 15 - Trial, 30 - Active, 40 - Graced, 50 - Expired, 60 - terminated, 70 - Canceled, 80 - Suspended);

    ServStatus - subscription service status (10 - Not Provisioned, 20 - Provisioning, 30 - Stopped, 40 - Starting, 50 - Running, 60 - Stopping, 70 - Removing, 80 - Changing Plan, 90 - Removed);

    If there are only subscriptions with Terminated/Stopped, Terminated/Removed, Ordered/Not Provisioned statuses, try to remove the resource from Home > Operations > Subscriptions > <subscription ID> > Resources. If this fails with an error Resource #ID Name can not be removed: there is some additional amount of it. Please, downgrade the Resource to included value and remove it then., it is bug PBA-75900 and the only way to proceed is database modification. Please contact Odin Technical Support for assistance.

    If there are any active subscriptions that are not going to be canceled, but you need to delete the resource from the Parallels Automation system completely, please contact Odin Technical Support.

  4. Remove corresponding resource rates from plan/plans.
  5. Contact Odin Technical support team.

  6. Find out the Order Ids:

    select OI."OrderDocOrderID" from "OItem" as OI join "ResItem" as RI using("OIID") where RI."resourceID" = <ID>

    Archive these orders.

    If the affected orders are resellers billing orders, it is not possible to archive them due to bug #PBA-36833 ("Can't archive reseller billing order."), an exception will occur on attempt: ODBC error #23503: ERROR: update or delete on "OrdDet" violates foreign key constraint "ResellerTrans_ReselOrdDetID" on "ResellerTrans" Key (DetID)=(6) is still referenced from table "ResellerTrans". No workaround can be suggested for the bug, it is recommended to leave the resource in the system.

Internal content

Link on internal Article