SQL for Active Banks and Branches in EBS R12

--SQL for Active Banks

SELECT BankOrgProfile.*

FROM HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_CODE_ASSIGNMENTS BankCA

WHERE 1 = 1

AND SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)

AND NVL (TRUNC (BankOrgProfile.effective_end_date),

SYSDATE + 1)

AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'

AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')

AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND (BankCA.STATUS = 'A' OR BankCA.STATUS IS NULL)

AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID

--SQL for Active Bank Branches

select count(1) from CE_BANK_BRANCHES_V where nvl(END_DATE,sysdate)>=sysdate