6 Replies Latest reply on Dec 7, 2012 10:49 AM by Jiri.Machotka-Oracle

    Removing rows from tables via URM

      Using 11g, I have a situation where business applications store content via UCM and then URM controls when the content should be disposed. However, the missing link is that the tables used by the internal applications still contain references to content that URM has disposed of so we end up with data integrity issues. Is there a way to delete rows from a table that reference a content item when that content item is removed by URM? The only thing I can think of is setting up some kind of referential integrity between the URM tables and the application tables, but I'm wondering if there's a way to insert some kind of SQL hooks into the disposition actions or create custom disposition actions that could do the work.

      Also, if anyone knows of a way to create custom disposition actions, please point me to that documentation. I have been unable to find it.

        • 1. Re: Removing rows from tables via URM
          Custom Disposition Actions are described here: http://docs.oracle.com/cd/E23943_01/doc.1111/e10640/e03_customdisp.htm#CHDJFDDJ

          Thinking aloud, prior to the disposition, you could also setup a flag that an item "will be auto-disposed" and then run a batch job to delete references from the other applications. Alternatively, you could somehow mark up or identify items that have been disposed (if you use Delete Revisions (Keep Metadata) rather than Delete All Revisions (Destroy Metadata), it is a query) and delete the references consequently.

          Somehow, I don't like the idea that URM would actively delete links in other applications - especially, if the link "belongs to the application".
          • 2. Re: Removing rows from tables via URM
            Thank you very much for the reply. Regarding Custom Disposition actions, I should have been more clear. I know how to create a custom disposition action. I also assume that any service on UCM can be used in a custom disposition action. What I don't know how to do is get a service registered in the list of services available to select when creating a custom disposition action. In effect I want to create a service that will insure data integrity between UCM and some external application tables. If I can create that service and get it registered, URM can handle all of the disposition and insure data integrity at the same time.
            • 3. Re: Removing rows from tables via URM
              William Phelps
              jiri.machotka wrote:Somehow, I don't like the idea that URM would actively delete links in other applications - especially, if the link "belongs to the application".
              I tend to agree with this opinion. If the external application is controlling the inserts into URM, it's just a natural extension that the external application queries URM to see if the link is still valid (by verifying the content item is still available), and updating its own data accordingly.

              The querying mechanism really is nothing more complex than executing a search for the item from the application. If the returned result set count equals 0, delete the link in the application. Otherwise, if rows are returned, leave the link alone.

              The generic adapter framework could be used/leveraged to completely control the process end-to-end, but if URM didn't initiate the process from the beginning, I would agree with Jiri that URM has no role in cleaning up the external application.
              • 4. Re: Removing rows from tables via URM
                Thanks for the replies.

                For what it's worth, the external applications are workflow driven applications. So the workflow is created when a content item hits UCM. Then the workflow goes through its steps to completion and some information about the content item is captured in the workflow and stored in a database. The information stored is specifically about the content item that's in UCM. Then in theory when URM decides that the content item is no longer needed based on business rules, the thinking is that the information that was obtained via the workflow process specifically for that content item is no longer needed either. So the real driver of the entire process is a piece of content in the UCM system. So it's not a scenario like having a seven year invoice get disposed and then go out and try to update an ERP system but still maintain data integrity within all of the ERP tables.

                Regardless... I don't have much say in how the solution is ultimately built. Do either of you (or anyone else) know the trick to registering a service so it's available for selection when creating a custom disposition action?
                • 5. Re: Removing rows from tables via URM
                  William Phelps
                  You'd probably need to look at editing the table "DispositionActionServicesList" found in "<MW_HOME>\Oracle_ECM1\ucm\idc\components\RecordsManagement\resources\rma_dispositions.hda"

                  Good luck. It'll probably be easier from the application side... :-(
                  • 6. Re: Removing rows from tables via URM
                    I would not be probably be that unyielding as William. Yet, I'd like to offer an alternative because a) it will be easier to implement b) it feels a better design.

                    1) I'd not put all the responsibility to applications. The scenario reminds me a cascading delete in the database, so I'd assume URM should have an active role in it. On the other hand, I'd discourage you from doing the active deletion at the URM side. You mentioned that checking the content is also a result of a workflow, so I'd suggest URM to fire an event (most likely calling a web service) and have the applications, or some middleware tier do the job. Again, conceptually it is like comparing point-to-point integration with a service bus, especially, if you have more applications to integrate with, and not just one.

                    2) As for the alternative, I don't think the triggering event has to be the disposition itself. Note that dispositions work more like a batch process - and often it is not important if you process them right away, or one day earlier, or later. If you buy the idea that you don't have to remove the link exactly the moment the disposition process takes place, you'd not have to bother with some risky customizations. All you'd need is the list |(a report) of items just disposed/to be disposed today, which would serve as the input for the web service mentioned earlier. Even though there might be no such report OOTB, it'd be relatively easy customization to create it, if necessary.

                    Either way, it's you to decide.