[SOLVED] Update through Data Query

[pesr0412]

Hi,

I am trying to achieve the following:

I want to make an update to some orders that meets certain conditions. I also want this process to be scheduled to run at some time in a regular basis.

I was trying to use Data Query within Business Process Automation/Process Management option becuase this allows for scheduling and recurrence. So I defined a saved query with the update sql query. The problem is that when I try to run the data query it does not allow the saved query to run as it is not a select kind of query.

I know that this could be easily be done in the actual DB. But the idea behind this is to give an OTM user the ability to control when do the update can take place.

Any ideas of how can this be achieved? Are there any other way to accomplish the same?

Many thanks in advance

Sincerely,
Ricardo

[acuartero]

Re: Update through Data Query

Ricardo,

I've done something similar to this. You can achieve the same effect as an UPDATE by calling an Autonomous PL/SQL function from a SELECT statement that passes the GIDs for the objects that you want to update to the function. Make sure you define the function with PRAGMA AUTONOMOUS_TRANSACTION otherwise it won't be able to modify the data that is returned from the calling SELECT, in the function body put your update statement and as the parameter pass the GID of the object that you want to update. You should also make sure you have proper exception handling and it might also be worthwhile to have the function return a numeric value indicating success or failure.

Thanks,
Alan

[LourensGlog]

Re: Update through Data Query

The 'OTM' way of achieving this is to set up an order data query triggering a custom event, have an agent listen to this event, and update your orders using a DSU in that agent.
Let me know if you need help to set this up.

[pesr0412]

Re: Update through Data Query

Just perfect!

I made the PRAGMA AUTONOMOUS... thing and tested it, and it worked smoothly...

Thank you Alan!

Ricardo,

I've done something similar to this. You can achieve the same effect as an UPDATE by calling an Autonomous PL/SQL function from a SELECT statement that passes the GIDs for the objects that you want to update to the function. Make sure you define the function with PRAGMA AUTONOMOUS_TRANSACTION otherwise it won't be able to modify the data that is returned from the calling SELECT, in the function body put your update statement and as the parameter pass the GID of the object that you want to update. You should also make sure you have proper exception handling and it might also be worthwhile to have the function return a numeric value indicating success or failure.

Thanks,
Alan

[pesr0412]

Re: Update through Data Query

Lourens,

Although Alan's method works nice, I would definitively want to know how to setup using the OTM way you mention, please by all means help me. It is better to have many options available.

The 'OTM' way of achieving this is to set up an order data query triggering a custom event, have an agent listen to this event, and update your orders using a DSU in that agent.
Let me know if you need help to set this up.

[LourensGlog]

Re: Update through Data Query

Ricardo,

Assuming you have created a saved query called ORDER RELEASE - EXAMPLE SAVED QUERY, if you create the objects as described below, OTM will run your query every night at 4 am (system time), and run an agent for each individual order in that query. Take care that these agents will run in parallel, so take this into account when writing your DSUs.

1. Create a Custom Agent Event
Business Process Automation > Power Data > Event Management > Agent Events > New
Agent Event ID: ORDER RELEASE - CUSTOM EVENT (example)
Data Query Type ID: ORDER RELEASE

2. Set up Recurring Process to trigger the Manual Agent Event
Business Process Automation > Process Management > Business Process Automation > Data Query
Data Query Type: ORDER RELEASE
Based on: ORDER RELEASE - EXAMPLE SAVED QUERY
Raise Event: ORDER RELEASE - CUSTOM EVENT (previously defined)
Schedule: At <Next Day 04:00:00>, Every 1 D
Click Submit

3. Create Agent that listens to Manual Agent Event
Business Process Automation > Agents and Milestones > Automation Agent > New
Agent ID: ORDER RELEASE - EXAMPLE AGENT (example)
Agent Type: ORDER RELEASE
Active: ticked
Agent Events:
Event: ORDER RELEASE - CUSTOM EVENT (previously defined)
Restrictions: (none)
Actions: (enter your actions here)

Good luck,

Lourens

[pesr0412]

Re: Update through Data Query

I never said thanks to this, I know it took me couple of years but did not want to miss the chance...

Thanks Lourens...