Announcement

Collapse
No announcement yet.

Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

    Hi everyone,

    I need to create a custom agent event that listens to a change in the ORDER_RELEASE_STATUS table STATUS_VALUE_GID column, whenever the user changes the status value via stored procedure.

    This means that the agent event will be raised whenever this field is changed from external sources.

    Attached are my insert statements to create the custom event.

    exec vpd.set_user ('CHN/CTMG.ADMIN')

    INSERT INTO NOTIFY_SUBJECT ( NOTIFY_SUBJECT_GID, NOTIFY_SUBJECT_XID, NOTIFY_FUNCTION_GID,
    NOTIFY_SUBJECT_TEXT, SUPPORTS_INVOLVED_PARTIES, SUPPORTS_SERVICE_PROVIDER,
    DOMAIN_NAME ) VALUES (
    'CHN/CTMG.OR_STATUS - UNASSIGNED', 'OR_STATUS - UNASSIGNED', 'ORDER_EVENT', 'Cusotm OR_STATUS - Unassigned Status Changed', 'Y', 'Y', 'CHN/CTMG');

    INSERT INTO AGENT_EVENT (AGENT_EVENT_GID, AGENT_EVENT_XID,
    DATA_QUERY_TYPE_GID, NOTIFY_SUBJECT_GID, AGENT_EVENT_PARENT,
    EVENT_TOPIC_CLASS, IS_CUSTOM, DOMAIN_NAME, IS_LIFETIME_MOD) VALUES
    ('CHN/CTMG.OR_STATUS - UNASSIGNED', 'OR_STATUS - UNASSIGNED',
    'ORDER RELEASE', 'CHN/CTMG.OR_STATUS - UNASSIGNED', 'ORDER - MODIFIED',
    'glog.server.workflow.lifetime.order.CustomModOrde rReleaseEvent', 'Y', 'CHN/CTMG', 'Y');

    INSERT INTO AGENT_EVENT_TABLE_MAPPING (AGENT_EVENT_GID, TABLE_NAME,
    DOMAIN_NAME)
    VALUES ('CHN/CTMG.OR_STATUS - UNASSIGNED', 'ORDER_RELEASE_STATUS', 'CHN/CTMG');

    INSERT INTO AGENT_EVENT_COLUMN_MAPPING (AGENT_EVENT_GID, TABLE_NAME,
    COLUMN_NAME, DOMAIN_NAME)
    VALUES ('CHN/CTMG.OR_STATUS - UNASSIGNED', 'ORDER_RELEASE_STATUS', 'STATUS_VALUE_GID', 'CHN/CTMG');

    Can anyone help?

    Thanks!
    Last edited by ianlo; May 18, 2010, 11:41.

  • #2
    Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

    Hi Ian,
    Basically, the steps for creating custom event are correct. Refer to http://www.otmfaq.com/forums/f18/how...2700/#post8524

    However, Agent can only capture changes made by OTM, such as Integration, Internal, User. Therefore, I am afraid that the custom event does not work for your case.

    A quick solution I can think about is, adding logic in your stored procedure using UTL_HTTP to send GenericStatusUpdate XML to OTM.
    Last edited by josephliang; May 19, 2010, 23:56.
    --
    Joseph Liang
    MavenWire APAC
    http://www.mavenwire.com/

    Comment


    • #3
      Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

      You wrote "...whenever the user changes the status value via stored procedure."

      I assume this stored procedure is part of an agent, right? Couldn't you have this agent trigger an event using a FOR EACH condition?

      Comment


      • #4
        Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

        Originally posted by LourensGlog View Post
        You wrote "...whenever the user changes the status value via stored procedure."

        I assume this stored procedure is part of an agent, right? Couldn't you have this agent trigger an event using a FOR EACH condition?
        Hi Lourens,

        Thanks for the reply. That was exactly what I was thinking also (in the car while driving ) and probably will that that approach.

        Its good to have a confirmation from you as well

        Thanks!

        Comment


        • #5
          Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

          Originally posted by josephliang View Post
          Hi Ian,
          Basically, the steps for creating custom event are correct. Refer to http://www.otmfaq.com/forums/f18/how...2700/#post8524

          However, Agent can only capture changes made by OTM, such as Integration, Internal, User. Therefore, I am afraid that the custom event does not work for your case.

          A quick solution I can think about is, adding logic in your stored procedure using HTTP_UTIL to send GenericStatusUpdate XML to OTM.
          Hi Joseph,

          Thanks for the information. Yeah after several rounds of testing in the office - I concluded as much.

          However thanks for the suggestion for HTTP_UTIL. Its an interesting option - one that may come in handy the next time

          Cheers!

          Comment


          • #6
            Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

            Hi Ian,
            Not sure whether you still need this.

            Instead of using GenericStatusUpdate, you can use Topic XML (send via UTL_HTTP) to trigger ORDER - STATUS CHANGED event.

            Code:
            <Transmission>
             <TransmissionHeader>
              <UserName></UserName>
              <Password></Password>
             </TransmissionHeader>
             <TransmissionBody>
              <GLogXMLElement>
               <Topic>
                <TopicAliasName>glog.server.workflow.lifetime.order.OrderReleaseModStatusChange</TopicAliasName>
                <TopicArg>
                 <TopicArgName>release</TopicArgName>
                 <TopicArgValue>CHN/CTMG.123</TopicArgValue>
                </TopicArg>
                <TopicArg>
                 <TopicArgName>dataSource</TopicArgName>
                 <TopicArgValue>USER</TopicArgValue>
            <!-- can be INTEGRATION, INTERNAL, USER -->
                </TopicArg>
                <TopicArg>
                 <TopicArgName>statusValue</TopicArgName>
                 <TopicArgValue>OR_STATUS - UNASSIGNED</TopicArgValue>
            <!-- NOTE: must be STATUS_VALUE_XID -->
                </TopicArg>
               </Topic>
              </GLogXMLElement>
             </TransmissionBody>
            </Transmission>
            --
            Joseph Liang
            MavenWire APAC
            http://www.mavenwire.com/

            Comment


            • #7
              Re: Custom Agent Event Listening to changes in ORDER_RELEASE_STATUS table

              Hi Joseph,

              Thanks for this! This will come in useful!

              Ian

              Comment

              Working...
              X