Announcement

Collapse
No announcement yet.

Update through Data Query

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SOLVED] Update through Data Query

    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
    -=RpS=-

  • #2
    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
    Last edited by acuartero; July 17, 2009, 20:23.

    Comment


    • #3
      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.

      Comment


      • #4
        Re: Update through Data Query

        Just perfect!

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

        Thank you Alan!

        Originally posted by acuartero View Post
        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
        -=RpS=-

        Comment


        • #5
          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.

          Originally posted by LourensGlog View Post
          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.
          -=RpS=-

          Comment


          • #6
            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

            Comment


            • #7
              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...
              -=RpS=-

              Comment

              Working...
              X