How to implement this logic in ODI 12c mapping?

Hi Experts,

I'm new to ODI 12c, but not new to ETL and Data warehousing.

I'm trying to create a mapping to update a couple of new columns added to an existing Dimension (SCD Type 2).

LOGIC: Each record in account dimension, check the Expiry Date and look for the last record loaded in staging table prior to that expiry date. Simplified example recordset gave below.

DIM_ACCOUNT

==============

record_no      account_id     effective_date      expiry_date     new_flag (NEW)

1                    1234               08-AUG-10          17-APR-17     null

2                    1234               17-APR-17          01-MAY-18     null

STAGING TABLE

==============

record_load_date     account_id     flag

16-OCT-11              1234               Y

19-JUL-14               1234               N

19-JUL-14               1234               Y

27-MAR-17              1234              N

How can I implement the above logic in ODI 12c mapping? Any help would be really helpful.

I already created a mapping by joining above two tables with ACCOUNT_ID and EXPIRY_DATE>=RECORD_LOAD_DATE.

But how I can implement another condition like..... AND EXPIRY_DATE = (SELECT MAX(RECORD_LOAD_DATE) from STAGING_TABLE WHERE ACCOUNT_ID=ACCOUNT_ID and EXPIRY_DATE>=RECORD_LOAD_DATE)

Thanks in advance.