BPA Workflow Commit

Hi,

As  per the release notes in 25R2, it supports for pragma autonomous transaction with commit, rollback and save point. But have not tested yet what actually works and not.

Best Regards,
Wasana

thank you. 

the documentation indicate that IFS BPA Workflows now support invoking APIs with Pragma Autonomous blocks in PL/SQL procedures and functions via IFS API tasks.

I was wondering how the non autonomous block will react?

I think It may be the same as before with error.But not tested anything yet.

BR,

Wasana

Hi,

I’ve tested with the API ProjectProcurementHandling.ProjectMiscPartProcurementSet to update the SupplyOption but I’m having the same error about Savepoint, so, I think this is not working still or am I doing something wrong?

Application service update: 25.2.2

Framework service update: 25.2.2

Hi,

In your procedure, you have declared PRAGMA autonomous_transaction as shown below. Now it works because, since 25R2, it is allowed through workflows.

PROCEDURE Example_Procedure___
IS
   PRAGMA autonomous_transaction;
BEGIN
   -- insert sql
   @ApproveTransactionStatement();
   COMMIT;
END Example_Procedure___;

Here, I used a test projection and a test procedure declared with PRAGMA autonomous_transaction. When I debugged this, I found no issues.

However, if you use COMMIT in workflows without PRAGMA autonomous_transaction, like this:

PROCEDURE Example_Procedure___
IS
BEGIN
   -- insert sql
   @ApproveTransactionStatement();
   COMMIT;
END Example_Procedure___;

without PRAGMA autonomous_transaction, according to the Workflow transaction management policy, APIs that have implicit commits, rollbacks, or save points cannot be called through a workflow. You will receive the error:

ORA-00034: cannot COMMIT in current PL/SQL session.

​@pdollk OK. Got your point.

What if the PL/SQL function/procedure doesn’t have PRAGMA autonomous_transaction declared inside? And here I’m talking about core/standard PL/SQL packages - PART_COST_SIM_API being one referenced above.

Do we really need to go inside the core PL/SQL package and add PRAGMA autonomous_transaction? This is considered modification isn’t it?

How should we handle such scenarios?

​@Eleazar_AyaviriCarrillo 

Hi ​@Marcel.Ausan ,

I have no idea about the feasibility of going into the core PL/SQL packages and adding PRAGMA autonomous_transaction, or whether it is practical. However, I guess that in core/standard PL/SQL packages, the owners have included commits with or without PRAGMA autonomous_transaction based on the business logic or requirements. If it is possible to modify the packages, that is one method.

However, an IFS REST Task can be used to invoke the same API by providing the API as the endpoint. This will initiate a new transaction and therefore should only be used if the entire process does not need to be executed within the same transaction. While an IFS REST Task can serve as an alternative, it will result in the workflow no longer being atomic.

​@pdollk  for this specific PL/SQL function there is no PRAGMA defined.

Would have been nice if R&D would ensure that all packages that have commit/savepoint/rollback have PRAGMA defined.

PART_COST_SIM_API.Split_Cost_Into_Buckets

Not a PRAGMA transaction:

Hi ​@Marcel.Ausan 

As I mentioned earlier, this may depend on the business logic, and I believe it is the component owner’s decision 🙃

From the workflow perspective, PRAGMA AUTONOMOUS_TRANSACTION allows a procedure or function to be called through the IFS API task. Because it runs in its own independent transaction, it is completely separate from the main transaction.

However, the IFS REST task can be used to call logic that performs direct commits, which means the workflow is no longer atomic.