I need to add a filter to this PER_EXT_SEC_ASSIGNMENT_UE for my HCM extract so that it includes Global transfers between subsidiary companies. So I am looking for the record for the global transfer from the old subsidiary comany and the new subsidiary company. Below is my filter. I removed the MAX(DATE_START) sub query but then it brings extra records from other situations. How can change this filter to Global Transfers from new and old subsidiary company?
( ASG.ASSIGNMENT_TYPE in ('E', 'C','P') AND ASG.PRIMARY_FLAG = 'Y' AND ASG.PRIMARY_ASSIGNMENT_FLAG='Y' AND ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED','INACTIVE')
AND ASG.PERIOD_OF_SERVICE_ID IN ( SELECT PERIOD_OF_SERVICE_ID FROM per_periods_of_service PPOS WHERE ASG.PERSON_ID = PPOS.PERSON_ID AND PPOS.PERIOD_TYPE in ('E', 'C','P') AND PPOS.PRIMARY_FLAG='Y' AND PPOS.DATE_START in ( SELECT MAX(DATE_START) FROM PER_PERIODS_OF_SERVICE WHERE PERSON_ID = PPOS.PERSON_ID AND PERIOD_TYPE in ('E', 'C','P') AND PRIMARY_FLAG='Y' )) AND pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE') BETWEEN TO_DATE(ASG.EFFECTIVE_START_DATE-7) AND ASG.EFFECTIVE_END_DATE )