Delete contacts from siebel through EIM

How to delete contacts from siebel through EIM?

Deleting from siebel can be done by 3 different ways depending on the requirement:

1. Deleting from a target/non-target base table using the user keys: DELETE EXACT method
2. Deleting from a target base table using a filter condition: DELETE MATCHES method
3. Deleting all rows from target base table: DELETE ALL ROWS method

Identify the deletion method:

Here we will take example of deleting contacts by using DELETE EXACT method. The requirement is to delete duplicate contacts in siebel. Once the logic for identifying duplicate contacts is implemented (this may depend on specific business scenario), we can go ahead and delete the party records associated with the contacts.

Get all information needed to load in EIM table:

Remember: To delete a contact, you need to delete contact records and associations from atleast 4 tables: S_PARTY, S_CONTACT, S_CONTACT_BU AND S_POSTN_CON. This is same as when importing a contact. There may be other assoicated records that you may want to delete like activities, service requests, opportunities etc. For accomplishsing these you need to identify all the child tables and records where reference to these contacts are present, build the logic and delete the data.

The only thing need to be identified after implemeting duplicate logic is the PARTY_UID of S_PARTY table. This can be obtained by below query:

--Execute:

SELECT
  CON.ROW_ID,
  PAR.PARTY_UID,
  PAR.PARTY_TYPE_CD
FROM
  SIEBEL.S_CONTACT CON,
  SIEBEL.S_PARTY PAR
WHERE
  PAR.ROW_ID = CON.PAR_ROW_ID
  AND CON.ROW_ID IN(/*DUPLICATE LOGIC SUBQUERY RETURNING CONTACT ROW_ID*/);

Load EIM table:

The interesting thing is that for deleting a contact, only party details need to be entered in EIM_CONTACT. The party information (PARTY_UID, PARTY_TYPE_CD) are the only requirement to delete a contact from all the 4 base tables mentioned above.

--Script:
INSERT INTO SIEBEL.EIM_CONTACT
(
  ROW_ID,
  IF_ROW_STAT,
  IF_ROW_BATCH_NUM,
  PARTY_UID,
  PARTY_TYPE_CD
)
SELECT
  ROWNUM,
  'FOR_IMPORT',
  1,
  PAR.PARTY_UID,
  PAR.PARTY_TYPE_CD
FROM
  SIEBEL.S_CONTACT CON,
  SIEBEL.S_PARTY PAR
WHERE
  PAR.ROW_ID = CON.PAR_ROW_ID
  AND CON.ROW_ID IN(/*DUPLICATE LOGIC SUBQUERY RETURNING CONTACT ROW_ID*/);

Create IFB and Run EIM job:

[Siebel Interface Manager]

PROCESS = "DELETE_CONTACTS"

[DELETE_CONTACTS]
    TYPE = SHELL
    INCLUDE = DELETE_CONTACTS_RECORDS

[DELETE_CONTACTS_RECORDS]
    TYPE = DELETE
    BATCH = 1
    TABLE = EIM_CONTACT
    ONLY BASE TABLES = S_PARTY
    DELETE EXACT = TRUE

    COMMIT EACH PASS = FALSE
    COMMIT EACH TABLE = FALSE
    ROLLBACK ON ERROR = TRUE

Verify the status of the records:

select count(1), if_row_stat, if_row_batch_num
from siebel.eim_contact
where if_row_batch_num = 1
group by if_row_stat, if_row_batch_num;