Query to get bank, bank branch and external bank account information in Fusion HCM

BANK :  

SELECT HOME_COUNTRY,
       COUNTRY_NAME,
   SUBSTR(START_DATE,1,10) AS START_DATE,
   SUBSTR(END_DATE,1,10) AS END_DATE,
   BANK_NAME,
   BANK_NUMBER
  FROM CE_BANKS_V
 ORDER BY BANK_NAME

BANK BRANCH:

SELECT BANK_HOME_COUNTRY,
       COUNTRY_NAME AS G2_COUNTRY_NAME,
   SUBSTR(START_DATE,1,10) AS G2_START_DATE,
   SUBSTR(END_DATE,1,10) AS G2_END_DATE,
   BANK_NAME AS G2_BANK_NAME,
   BANK_BRANCH_NAME AS G2_BANK_BRANCH_NAME,
   BRANCH_NUMBER AS G2_ROUTING_NUMBER
  FROM CE_BANK_BRANCHES_V
 ORDER BY BANK_NAME,BANK_BRANCH_NAME

EXTERNAL BANK ACCOUNT:

SELECT IEBA.BANK_NAME AS G3_BANK_NAME,

        IEBA.BANK_BRANCH_NAME AS G3_BANK_BRANCH_NAME,

IEBA.BANK_ACCOUNT_NUMBER AS G3_BANK_ACCOUNT_NUMBER,

'' AS G3_IBAN,

IEBA.BANK_ACCOUNT_TYPE AS G3_BANK_ACCOUNT_TYPE,

'' AS G3_SECONDARY_ACCOUNT,

IEBA.PRIMARY_ACCT_OWNER_NAME AS G3_PRIMARY_ACCT_OWNER_NAME,

PAP.PERSON_NUMBER AS G3_PERSON_NUMBER

  FROM PER_ALL_PEOPLE_F PAP,

    PAY_PERSON_PAY_METHODS_F PPP,

    PAY_PAYROLL_ASSIGNMENTS PPA,

    PER_ALL_ASSIGNMENTS_M PAA,

IBY_EXT_BANK_ACCOUNTS_INT_V IEBA

WHERE PPP.PAYROLL_RELATIONSHIP_ID = PPA.PAYROLL_RELATIONSHIP_ID

   AND PPA.HR_ASSIGNMENT_ID = PAA.ASSIGNMENT_ID

   AND PAA.PERSON_ID = PAP.PERSON_ID

   AND PAA.ASSIGNMENT_TYPE = 'E'

   AND PPP.BANK_ACCOUNT_ID = IEBA.BANK_ACCOUNT_ID

   AND PAA.ASSIGNMENT_SEQUENCE = (SELECT MAX(ASSIGNMENT_SEQUENCE)

                                    FROM PER_ALL_ASSIGNMENTS_M

   WHERE PERSON_ID = PAP.PERSON_ID

     AND ASSIGNMENT_TYPE = 'E'

AND EFFECTIVE_LATEST_CHANGE = 'Y'

AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)

   AND PAA.EFFECTIVE_LATEST_CHANGE = 'Y'

   AND TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

   AND TRUNC(SYSDATE) BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE

 ORDER BY PAP.PERSON_NUMBER