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
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".;
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".;
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".;
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
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".
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:
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.
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.
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 errorResource #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.
- Remove corresponding resource rates from plan/plans.
- Contact Odin Technical support team.
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.