CUSTOM IDC Authorized Bank Account Signers

         SELECT
	  BANKPARTY.PARTY_NAME BANK_NAME, 
	  BRANCHPARTY.PARTY_NAME BRANCH_NAME,
	  CBA.BANK_ACCOUNT_NUM ACCOUNT_NUMBER,
	  PPF.person_id,
	  PPF.FULL_NAME PERSON_NAME,
	  BASIG.SINGLE_LIMIT_AMOUNT,
	  BASIG.JOINT_LIMIT_AMOUNT,
	  DECODE(PAAF.ORGANIZATION_ID,NULL,NULL, HR_GENERAL.DECODE_ORGANIZATION(PAAF.ORGANIZATION_ID)) HR_ORG, 
	  SUBSTR(DECODE(PAAF.JOB_ID,NULL,NULL,HR_GENERAL.DECODE_JOB (PAAF.JOB_ID)),INSTR(DECODE(PAAF.JOB_ID,NULL,NULL,HR_GENERAL.DECODE_JOB (PAAF.JOB_ID)),'.')+1) JOB,
	  INITCAP(BASIG.SIGNER_GROUP) SIGNER_GROUP,
	  BASIG.OTHER_LIMITS
	 FROM 
	 HZ_PARTIES BANKPARTY, 
	 HZ_PARTIES BRANCHPARTY, 
	 HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE, 
	 AR_LOOKUPS ARLOOKUP, 
	 HZ_RELATIONSHIPS BRREL, 
	 HZ_CODE_ASSIGNMENTS BRANCHCA, 
	 HZ_CODE_ASSIGNMENTS BANKCA,
	 CE_BA_SIGNATORIES BASIG  	,
	 PER_PEOPLE_F PPF  	,
	 PER_ALL_ASSIGNMENTS_F PAAF  	,
	 CE_BANK_ACCOUNTS CBA  
	 WHERE 
	 BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION' 
	 AND BRANCHPARTY.STATUS = 'A' 
	 AND BRANCHPARTY.PARTY_ID = BRANCHORGPROFILE.PARTY_ID 
	 AND  SYSDATE BETWEEN TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE) AND NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE), SYSDATE+1) 
	 AND BRANCHCA.CLASS_CATEGORY = ARLOOKUP.LOOKUP_TYPE AND BRANCHCA.CLASS_CODE = ARLOOKUP.LOOKUP_CODE 
	 AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES' AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID 
	 AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' AND BRANCHCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH') 
	 AND NVL(BRANCHCA.STATUS, 'A') = 'A' AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID 
	 AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH' AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF' AND BRREL.STATUS = 'A' 
	 AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND BRREL.SUBJECT_TYPE = 'ORGANIZATION' AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES' 
	 AND BRREL.OBJECT_TYPE = 'ORGANIZATION' AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES' AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID 
	 AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' 
	 AND BASIG.PERSON_ID = PPF.PERSON_ID  	
	 AND BASIG.DELETED_FLAG = 'N'  	
	 AND BASIG.END_DATE IS NULL  	
	 AND PPF.PERSON_ID = PAAF.PERSON_ID(+)  	
	 AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE  	
	 AND PPF.EFFECTIVE_END_DATE  	
	 AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE(+)  	
	 AND PAAF.EFFECTIVE_END_DATE(+)  	
	 AND PAAF.ASSIGNMENT_TYPE (+) <> 'B'  	
	 AND PAAF.PRIMARY_FLAG (+) = 'Y'  	
	 AND BASIG.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID  	
	 AND CBA.BANK_ID=BANKPARTY.PARTY_ID   
	 AND CBA.BANK_BRANCH_ID=BRANCHPARTY.PARTY_ID 
	 AND PPF.PERSON_ID=NVL(:PARAM1,PPF.PERSON_ID)
	 --AND BANKPARTY.PARTY_ID=NVL(:PARAM2,BANKPARTY.PARTY_ID)
	 AND BRANCHPARTY.PARTY_ID=NVL(:PARAM2,BRANCHPARTY.PARTY_ID)
         ORDER BY PPF.FULL_NAME,BANKPARTY.PARTY_ID

SELECT BANKPARTY.PARTY_NAME BANK_NAME, BRANCHPARTY.PARTY_NAME BRANCH_NAME, CBA.BANK_ACCOUNT_NUM ACCOUNT_NUMBER, PPF.person_id, PPF.FULL_NAME PERSON_NAME, BASIG.SINGLE_LIMIT_AMOUNT, BASIG.JOINT_LIMIT_AMOUNT, DECODE(PAAF.ORGANIZATION_ID,NULL,NULL, HR_GENERAL.DECODE_ORGANIZATION(PAAF.ORGANIZATION_ID)) HR_ORG, SUBSTR(DECODE(PAAF.JOB_ID,NULL,NULL,HR_GENERAL.DECODE_JOB (PAAF.JOB_ID)),INSTR(DECODE(PAAF.JOB_ID,NULL,NULL,HR_GENERAL.DECODE_JOB (PAAF.JOB_ID)),'.')+1) JOB, INITCAP(BASIG.SIGNER_GROUP) SIGNER_GROUP, BASIG.OTHER_LIMITS FROM HZ_PARTIES BANKPARTY, HZ_PARTIES BRANCHPARTY, HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE, AR_LOOKUPS ARLOOKUP, HZ_RELATIONSHIPS BRREL, HZ_CODE_ASSIGNMENTS BRANCHCA, HZ_CODE_ASSIGNMENTS BANKCA, CE_BA_SIGNATORIES BASIG , PER_PEOPLE_F PPF , PER_ALL_ASSIGNMENTS_F PAAF , CE_BANK_ACCOUNTS CBA WHERE BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION' AND BRANCHPARTY.STATUS = 'A' AND BRANCHPARTY.PARTY_ID = BRANCHORGPROFILE.PARTY_ID AND SYSDATE BETWEEN TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE) AND NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE), SYSDATE+1) AND BRANCHCA.CLASS_CATEGORY = ARLOOKUP.LOOKUP_TYPE AND BRANCHCA.CLASS_CODE = ARLOOKUP.LOOKUP_CODE AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES' AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' AND BRANCHCA.CLASS_CODE IN ('BANK_BRANCH', 'CLEARINGHOUSE_BRANCH') AND NVL(BRANCHCA.STATUS, 'A') = 'A' AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH' AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF' AND BRREL.STATUS = 'A' AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND BRREL.SUBJECT_TYPE = 'ORGANIZATION' AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND BRREL.OBJECT_TYPE = 'ORGANIZATION' AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES' AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' AND BASIG.PERSON_ID = PPF.PERSON_ID AND BASIG.DELETED_FLAG = 'N' AND BASIG.END_DATE IS NULL AND PPF.PERSON_ID = PAAF.PERSON_ID(+) AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE(+) AND PAAF.EFFECTIVE_END_DATE(+) AND PAAF.ASSIGNMENT_TYPE (+) <> 'B' AND PAAF.PRIMARY_FLAG (+) = 'Y' AND BASIG.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID AND CBA.BANK_ID=BANKPARTY.PARTY_ID AND CBA.BANK_BRANCH_ID=BRANCHPARTY.PARTY_ID AND PPF.PERSON_ID=NVL(:PARAM1,PPF.PERSON_ID) --AND BANKPARTY.PARTY_ID=NVL(:PARAM2,BANKPARTY.PARTY_ID) AND BRANCHPARTY.PARTY_ID=NVL(:PARAM2,BRANCHPARTY.PARTY_ID) ORDER BY PPF.FULL_NAME,BANKPARTY.PARTY_ID