ADM - List Of Values

There are plenty of posts on support web discussing the issues with migrating LOVs, but for my own sanity, I thought I would summarize all of the relevant issues in one place.

First, we need to address the defects. These are documented on support in Document 731411.1 but I will summarize here:

(1) Go to BC 'List Of Values Child (UDA)'
(2) Add a new field 'Parent Type' based on Join 'Parent LOV' and Column 'TYPE' with Text Length '30'.
(3) Expand the pickmap for the 'Parent' field. Replace pickmap field 'Type' with 'Parent Type' and uncheck Constrain flg.
(4) Go to the integration object 'UDA List Of Values'
(5) Find the Integration component 'List Of Values Child (UDA)'
(6) Add a new field to the integration component with Name = 'Parent Type'. Data Type = 'DTYPE_TEXT', Length = '30', Type = 'Data', External Name = 'Parent Type', External Data Type = 'DTYPE_TEXT', External Length = '30', External Sequence = '38', XML Tag = 'ParentType'
(8) Compile changes.

The SR then goes into some more detail on why after all that it still does not quite work. To understand, we need to see that the LOV ADM Integration Object is Hierarchical in one dimension. That is, there is the LOV_TYPE record and then there are the value records. But LOVs are frequently Hierarchical in two dimensions, by virtue of the Parent value. What I mean is that a given LOV value record will always have one 'parent' record, it's type or technical parent, and may have a second parent record, it's functional parent, if you will.

ADM loads the first, technical parent in the standard way, through the relationships of the Integration Object. To load the functional parent though, ADM must run in two passes, the first to create all the parent and child records, and the second to relate them. This is necessary because we cannot guarantee the sequence with which LOV value records will be placed in the extract file. If these value records do not exist in the target already, and the parent is alphabetically (or however else we chose to sort the records) after the child, then it would error if ADM did not take this approach. So how ADM takes two passes is by virtue of the ADM Data Type explorer. You will notice that the explorer does not actually specify the foreign key fields of an object to link them to each other. Its only purpose is to run ADM in multiple passes. But the twist is that ADM will actually process dependent data types setup in the explorer in reverse order, importing the children before the parent. I personally find this confusing from a terminology perspective. Perhaps a better way of naming these Data Types is to use 'LOV-2ndPass' instead of 'LOV-HierParent' and 'LOV-1stPass' instead of 'LOV-HierChild'. This way when we set up the search specifications for an ADM Export session, it is clear what we are trying to do.

OK, one more wrinkle to throw into the mix (just when you thought it was all making sense). There is actually a third parent relationship involved. That is the records that populate the S_LOV_REL table. I will be honest; I do not use the LOV explorer view that often and I don't really know what the point of this table is. In theory it can make LOVs M:M but I just don't think this is practical. Nevertheless, there are some vanilla uses of LOVs where these records are in fact used that way. The one that comes to mind is in payments, where the PAYMENT_TYPE_CODE values are children of the PAYMENT_METHOD_CODE and there are S_LOV_REL records created to store the relationships. The same issue applies when migrating these relationships. The related value must exist prior to the relationship being built.

One final note. I think the whole not deleting LOVs is well intended but more likely to cause confusion than solve anything. Here is why. Users can and will just change the Name/Value of a value record to something else in which case any sense of history is lost anyway. There are no foreign key relationships to LOVs so business data using these values is unaffected regardless. But others may disagree so this step is completely optional. I remove the no delete properties from the 'List Of Values Child (UDA)' BC and Integration Component. (I also allow deletes from the GUI but that is a separate issue). So my migration methodology is to synchronize values between environments for an initial release. You would take a different approach on a point release where values are likely to have been added directly to production and therefore may not exist in your DEV and TEST environments.

Anyway, what are we trying to do. Quite simply, we are trying to create all the Value records in pass 1, then we need to relate them to each other in Pass 2. I have already discussed how to group LOVs together for a release. This is where I diverge from Siebel's example because I am trying to think of real life scenarios where I am deploying releases, not just one LOV_TYPE. When creating the ADM Project/Session, here are the session items I use:

Data Type	Child Delete	Deployment Filter
LOV-2ndPass	Y	[Release] = '1.1'
LOV-1stPass	N	[Release] = '1.1' AND [List Of Values Relationship.Name] IS NULL

What this means is that the first pass includes all LOV_TYPE records that have been marked for this release and all LOV value records related to them. The second part of the expression basically just insures that no relationship records are included in the first pass. When ADM attempts to set the parent value on a child, it may not be able to find it so it will log a warning and move on. In the second pass, ADM will load all the Relationship records and set the Parent values that it missed on the first pass. I have also set child delete to true on the second pass so that this job effectively synchronizes the value records for the type records marked.