Goldengate: How to handle soft deletes in OBIEE

Concept: 

In the source table record got deleted , in the replicated target update the record attribute delete_flag from N to Y, if record got reinserted in the source, in the target modify delete_flag from ‘Y’ to ‘N’. If new record got inserted then delete_flag will be N and if source updates a record in the target also record should get updated but there is no change in the delete_flag value i.e. it will be N only.

Implementation details - for example let us have source target tables as follow

CREATE TABLE source.SOFT_DETELE_IMPLEMENTATION
( ROW_ID VARCHAR2(15 CHAR) ,
   LOGIN VARCHAR2(50 CHAR));
  
CREATE TABLE target.SOFT_DETELE_IMPLEMENTATION
(   ROW_ID VARCHAR2(15 CHAR) ,
   LOGIN VARCHAR2(50 CHAR) ,
   DELETE_FLG CHAR(1 CHAR) );

In extract parameter file add the table which you are interested in
example: edit /app/ggate/dirprm/eextract.prm

EXTRACT EEXTRACT

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID dbuser_name@DB_Instance, PASSWORD password

EXTTRAIL /app/gg/trail/  - etc

TABLE source.SOFT_DETELE_IMPLEMENTATION, &
      COLS (ROW_ID, LOGIN);

-Modify the Replicat parameter file and add following syntax for corresponding table then perform all necessary task need for extract / replication to work.

edit /app/gg/dirprm/rextract.prm

-- TARGET Table mapping -- 

- following tag will allow multiple maps for single source
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
- IGNOREDELETES will ignores deleted records
IGNOREDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "N" ),HANDLECOLLISIONS;
IGNOREINSERTS
IGNOREUPDATES
GETDELETES

- UPDATEDELETES will convert delete operations into update operations.

UPDATEDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "Y" ),HANDLECOLLISIONS;

Unit testing:
Inserted a record into source  ( insert into SOFT_DETELE_IMPLEMENTATION values ('400','aaa');)
Target Table Values ---- 400  aaa  N     

Updated a record in the source (update SOFT_DETELE_IMPLEMENTATION set login ='upd'  where row_id ='400';)
Target Table Values ---- 400     upd        N 

deleted a record from source (delete SOFT_DETELE_IMPLEMENTATION where row_id ='400';)
Target Table Values ---- 400     upd        Y   

Inserted same record 2nd time in the source
Target Table Values ----400     aaa         N    

Source record got updated
Target Table Values ----400     upd        N  

Source record got deleted second time
Target Table Values ----400     upd        Y