Examples in the article have been built using Simple ETL module generator.

Metadata example:

<?xml version="1.0"?>
<MODULE Pattern="FR_DELETE" TargetOwner="A2" TargetTable="PARTY">
<EXPR Name="N2CH">to_char(:1)</EXPR> 
<SRCTAB Owner="A2" Name="CUSTOMER" Alias="CUST" Type="FROM" Link="" />
<COLMAP TargetColumn="PT_SID" MatchFlag="Y">'SR1'</COLMAP>
<COLMAP TargetColumn="PT_ID" SourceAlias="CUST" SourceColumn="ID" Expr="N2CH" MatchFlag="Y" />
<COLMAP TargetColumn="PT_DELETED_FLAG">'Y'</COLMAP>
<COLMAP TargetColumn="UPD_EFF_DATE" UpdateFlag="Y">p_effective_date</COLMAP>
<COLMAP TargetColumn="UPD_DT" UpdateFlag="Y">sysdate</COLMAP>
<COLMAP TargetColumn="UPD_PROCESS" UpdateFlag="Y">p_process_key</COLMAP>
</MODULE>

Generated source code example:

MERGE INTO A2.PARTY TRG$$
USING(
  SELECT
      PT_SID
     ,PT_ID
     ,PT_DELETED_FLAG
     ,UPD_EFF_DATE
     ,UPD_DT
     ,UPD_PROCESS
  FROM A2.PARTY
 MINUS
  SELECT
      'SR1' as PT_SID
     ,to_char(CUST.ID) as PT_ID
     ,'Y' as PT_DELETED_FLAG
     ,p_effective_date as UPD_EFF_DATE
     ,sysdate as UPD_DT
     ,p_process_key as UPD_PROCESS
  FROM A2.CUSTOMER CUST
) SRC$$
ON(
  TRG$$.PT_SID=SRC$$.PT_SID
  AND TRG$$.PT_ID=SRC$$.PT_ID
)
WHEN MATCHED THEN UPDATE SET
   TRG$$.PT_DELETED_FLAG=SRC$$.PT_DELETED_FLAG
  ,TRG$$.UPD_EFF_DATE=SRC$$.UPD_EFF_DATE
  ,TRG$$.UPD_DT=SRC$$.UPD_DT
  ,TRG$$.UPD_PROCESS=SRC$$.UPD_PROCESS
;

Following additional attributes of column mappings are important for FR_DELETE pattern:

• Match flag - for all the matching key.
• Update flag - for audit columns and delete flag. Such columns should be specified with constant-wise source. Source of such columns should not come from source query.