SELECT PAPF.PERSON_NUMBER,
PPNF.FULL_NAME EMPLOYEE_NAME,
IBA.*
FROM PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
HZ_PARTIES HZP,
HZ_ORIG_SYS_REFERENCES HOSR,
IBY_EXT_BANK_ACCOUNTS IBA,
IBY_ACCOUNT_OWNERS IBO
WHERE PPNF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND HOSR.OWNER_TABLE_ID = HZP.PARTY_ID
AND HOSR.ORIG_SYSTEM_REFERENCE = TO_CHAR(PAPF.PERSON_ID)
AND HOSR.ORIG_SYSTEM = 'FUSION_HCM'
AND HOSR.OWNER_TABLE_NAME='HZ_PARTIES'
AND IBA.EXT_BANK_ACCOUNT_ID=IBO.EXT_BANK_ACCOUNT_ID
AND IBO.ACCOUNT_OWNER_PARTY_ID = HZP.PARTY_ID
