If you load _XM (1:M MVG records) using Siebel EIM without using ONLY BASE COLUMNS in the ifb file, the implicit primary foreign key updates can be incorrect for all primary foreign keys on parent table related to the _XM table. Background You have S_SRV_REQ (Service Request) and you have 1:M MVG child records of Service Request in S_SRV_REQ_XM. S_SRV_REQ.ROW_ID = S_SRV_REQ_XM.PAR_ROW_ID is the join to relate these items. The user key that uniquely identifies S_SRV_REQ_XM records is PAR_ROW_ID, NAME, TYPE so therefore you can have many different MVG records of different TYPE. So using this logic you can create many different MVG business components associated to Service Request by using a different value of TYPE and then the MVG BusComp would have a Search Specification like: [Type] = "Service Request LPA" and another MVG BusComp could have Search Specification like: [Type] = "Service Request LGA". If you have configured a primary foreign key for both of these MVGs then you would have two different columns on S_SRV_REQ to store the primary foreign key value of S_SRV_REQ_XM.ROW_ID. Lets say these columns are called X_PRIMARY_LPA_ID and X_PRIMARY_LGA_ID. What is important to note is that when you create the primary foreign key column on the parent Service Request table (In Siebel Tools object explorer > Table > Column) the Foriegn Key Table property will be populated with S_SRV_REQ_XM to identify that column is a foreign key to S_SRV_REQ_XM. Also Primary Child Table = S_SRV_REQ_XM, Primary Child Column = ROW_ID, Primary Child Join Column = PAR_ROW_ID, Primary Join Column = ROW_ID (Configuring these properties for a primary foreign key column is normal configuration practice). Problem Now lets say that you have a service request that already has MVG records populated of TYPE = "Service Request LPA" but you dont have any of TYPE = "Service Request LGA". You want to load some MVG records into S_SRV_REQ_XM for TYPE = "Service Request LGA", you want to ensure that the primary foreign key X_PRIMARY_LGA_ID is populated with any of these new "Service Request LGA" records but you dont care which one. Therefore you would create an EIM job to load the MVG records into S_SRV_REQ_XM and allow EIM to implicitly update the primary foreign key column X_PRIMARY_LGA_ID (If you dont specify an Explicit Primary Mapping when doing an EIM job the primary column will be automatically populated implicitly). You would load the following into EIM_SRV_REQ table to load into S_SRV_REQ_XM and populated the primary foreign key X_PRIMARY_LGA_ID on S_SRV_REQ: EIM_SRV_REQ: ROW_ID = 1 IF_ROW_BATCH_NUM = 7005 IF_ROW_STAT = FOR_IMPORT SR_NUM = SR_1 SR_BU = Default Organization XM_NAME = Service Request LGA Name 1 XM_TYPE = Service Request LGA Notice that I have not loaded an explicit primary mapping column. The explicit primary mapping column in EIM_SRV_REQ for S_SRV_REQ.X_PRIMARY_LGA_ID is EIM_SRV_REQ.X_PRIMARY_LGA. We execute the EIM job using the following IFB file: [Siebel Interface Manager] [LOAD_LGA] TYPE = IMPORT BATCH = 7005 TABLE = EIM_SRV_REQ After the EIM job has finished the result is that the primary foreign key column X_PRIMARY_LGA_ID has been populated to the value of S_SRV_REQ_XM.ROW_ID of an incorrect S_SRV_REQ_XM record, it is pointing to the S_SRV_REQ_XM record that already existed in prior to the load with a different TYPE (TYPE = Service Request LPA). Therefore if you go to the Siebel UI, go to the Service Request view and if both MVFs (Service Request LGA and Service Request LPA) are exposed on the applet they will both show the value of the original Service Request LPA MVG record. If you click on the Service Request LGA MVG glyph and open the MVG it will show the correct record and the primary foreign key column X_PRIMARY_LGA_ID will be updated at that point by Siebel automatically fixing your problem. But the problem is that if you have done this for many records then a massive performance hit will occur when users start to use the application and open up these MVGs simultaneously. Further to this problem the problem becomes much worse if you have say 10 different MVGs based on S_SRV_REQ_XM with different types. In the above process it is possible that many of these MVG primary foreign key columns will be updated with the same value hence in the UI all the MVFs will show the same value until you open the MVG and allow Siebel to automatically correct this. This is a problem with EIM when it does implicit primary foreign key updates. Here is the SQL that EIM runs to do the implicit primary updates: UPDATE SIEBEL.S_SRV_REQ BT SET X_PRIMARY_LGA_ID = (SELECT NVL(MIN(ROW_ID), 'No Match Row Id') FROM SIEBEL.S_SRV_REQ_XM CT WHERE (CT.PAR_ROW_ID = BT.ROW_ID)), DB_LAST_UPD = ?, DB_LAST_UPD_SRC = ?, LAST_UPD = ?, LAST_UPD_BY = ?, MODIFICATION_NUM = MODIFICATION_NUM + 1 WHERE (ROW_ID IN ( SELECT T_SRVREQXMPARROWID C1 FROM SIEBEL.EIM_SRV_REQ WHERE( T_SRVREQXMPARROWID IS NOT NULL AND IF_ROW_BATCH_NUM = ? AND T_SRV_REQ_XM__STA = 0 AND T_SRV_REQ_XM__EXS = 'N' AND T_SRV_REQ_XM__UNQ = 'Y')) AND (X_PRIMARY_LGA_ID IS NULL OR X_PRIMARY_LGA_ID = 'No Match Row Id')) SQLParseAndExecute Bind Vars 4 000001a54aa568fb:0 2009-09-09 13:51:11 01:{ts '2009-09-09 03:51:08'} 02:EIM 03:{ts '2009-09-09 03:51:08'} 04:0-1 05:7005 EIM is updating the X_PRIMARY_LGA_ID based on: SELECT NVL(MIN(ROW_ID), 'No Match Row Id') FROM SIEBEL.S_SRV_REQ_XM CT WHERE (CT.PAR_ROW_ID = BT.ROW_ID) Therefore it will update X_PRIMARY_LGA_ID with the MIN(ROW_ID) of all S_SRV_REQ_XM records where S_SRV_REQ_XM.PAR_ROW_ID = S_SRV_REQ.ROW_ID. Therefore the S_SRV_REQ_XM record that already exists with a different TYPE, if it has a lower in value ROW_ID than the newly created S_SRV_REQ_XM record then this will ROW_ID will be populated. The EIM implicit primary update does not care about the User Key of S_SRV_REQ_XM being TYPE, NAME and PAR_ROW_ID, it just uniquely identifies the S_SRV_REQ_XM record based on PAR_ROW_ID alone. For every primary foreign key column on S_SRV_REQ_XM that maps to S_SRV_REQ will have a simlar SQL update statement to the above, hence they will all get the same value. The reason for this problem is because the Table > Column property Primary Child Join Column (for X_PRIMARY_LGA_ID has value: PAR_ROW_ID) is used for the implicit primary update. EIM has no facility to identify the S_SRV_REQ_XM record based on its user key TYPE, NAME, PAR_ROW_ID. Resolution The workaround to prevent this problem from occurring is to always use explicit primary mapping in combination with "ONLY BASE COLUMNS" whenever loading any MVG *_XM records. This is the ONLY way around this problem. The above EIM job would be changed as such: Load the following into EIM_SRV_REQ: EIM_SRV_REQ: ROW_ID = 1 IF_ROW_BATCH_NUM = 7005 IF_ROW_STAT = FOR_IMPORT SR_NUM = SR_1 SR_BU = Default Organization XM_NAME = Service Request LGA Name 1 XM_TYPE = Service Request LGA X_PRIMARY_LGA = Y (X_PRIMARY_LGA is the explicit primary mapping column for X_PRIMARY_LGA_ID). We execute the EIM job using the following IFB file: [Siebel Interface Manager] [LOAD_LGA] TYPE = IMPORT BATCH = 7005 TABLE = EIM_SRV_REQ ONLY BASE COLUMNS = S_SRV_REQ.SR_NUM, S_SRV_REQ.BU_ID,\ S_SRV_REQ.X_LGA_PRIMARY_ID, S_SRV_REQ_XM.TYPE,\ S_SRV_REQ_XM.NAME, S_SRV_REQ_XM.PAR_ROW_ID This will ensure that explicit primary update will occur and no implicit primary update will occur. Also the ONLY BASE COLUMNS in the ifb ensures that no other primary foreign key columns will be updated through implicit primary update, only the primary foreign key column that we want to be updated. There is a complexity to the above EIM method if you are loading multiple MVG records of the same type, because you only want one of the MVG records to be the primary you only want to set one of the EIM records X_PRIMARY_LGA = Y for each SR_NUM and XM_TYPE combination. If you dont care which MVG record is made primary then you should load all your data into EIM_SRV_REQ first without setting the X_PRIMARY_LGA column for any of the data. Then execute the following SQL Update, this SQL Update will set at least one of the X_PRIMARY_LGA = Y for each combination of SR_NUM, XM_TYPE to ensure that every MVG for a Service Request will have a primary explicitly set (this SQL is for Oracle): UPDATE EIM_SRV_REQ e SET e.X_PRIMARY_LGA = 'Y' WHERE e.ROW_ID || e.IF_ROW_BATCH_NUM IN ( SELECT r.ROW_ID || r.IF_ROW_BATCH_NUM FROM ( SELECT eim.ROW_ID, eim.IF_ROW_BATCH_NUM, ROW_NUMBER() OVER (PARTITION BY eim.SR_NUM, eim.XM_TYPE ORDER BY NULL) RID FROM EIM_SRV_REQ eim WHERE eim.IF_ROW_BATCH_NUM = 7005 ) r WHERE r.RID = 1 ); COMMIT;

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.