entLink connector SQL template
The entLink connector in the MDEPerson_2 job requires an SQL select statement. Use this template for the SQL and update the <enttype> property to match the entity type that you have configured for your MDM implementation.
Note: This template is a only a sample and may
not be sufficient for all implementations.
Do not use the < > brackets in the SQL. For example, replace from mpi_entlink_<enttype> link with from mpi_entlink_mdmper link.
select distinct(link.curentrecno) as EnterPriseID,src.srccode as SourceSystemID,
head.memidnum as SourceSystemPrimaryKey,
pername.lastname as LastName,
VARCHAR_FORMAT(CURRENT TIMESTAMP,'YYYYMMDD hh:mm:ss') as ExportTimeStamp
from mpi_entlink_<enttype> link,
mpi_srchead src,
mpi_memhead head
left outer join mpi_pername pername on pername.memrecno = head.memrecno
where
link.memrecno = head.memrecno and
src.srcrecno = head.srcrecno and
link.curentrecno in(select distinct(entrecno) from
(
select xeia.supentrecno as entrecno
from mpi_srchead s, mpi_memhead m, mpi_entxeia_<enttype> xeia
where
xeia.recmtime > (select min_recmtime from temp_recmtime_watermark where enttype = '<enttype>') and
xeia.recmtime <= (select max_recmtime from temp_recmtime_watermark where enttype = '<enttype>') and
s.srcrecno=m.srcrecno and
m.memrecno=xeia.memrecno and
xeia.supentrecno in (select curentrecno from mpi_entlink_<enttype>)
) as entrecno
union
(
select xeia.preventrecno as entrecno
from mpi_srchead s, mpi_memhead m, mpi_entxeia_<enttype> xeia
where
xeia.recmtime > (select min_recmtime from temp_recmtime_watermark where enttype = '<enttype>') and
xeia.recmtime <= (select max_recmtime from temp_recmtime_watermark where enttype = '<enttype>') and
xeia.preventrecno > 0 and
s.srcrecno=m.srcrecno and
m.memrecno=xeia.memrecno and
xeia.preventrecno in (select curentrecno from mpi_entlink_<enttype>)
)
union
(
select link.curentrecno as entrecno
from mpi_srchead s, mpi_memhead m, mpi_entlink_<enttype> link
where link.memrecno in
(
select distinct (head.memrecno)
from mpi_memhead head
where
maudrecno > (select seqnum from mpi_seqgen where seqname = 'maxaudrecno_person') and
maudrecno <= (select seqnum from mpi_seqgen where seqname = 'minaudrecno_person')
) and
s.srcrecno=m.srcrecno and
m.memrecno=link.memrecno
))
order by EnterPriseID