ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EF683869E053B46B63588994 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 Y CUSTOM IDC_BA_SIGN US CUSTOM IDC Authorized Bank Account Signers Application: Custom Development Source: IDC Authorized Bank Account Signers Short Name: IDC_BA_SIGN DB package: BI Publisher :param1 :param2 1 10 :param2 LOV Oracle IDC_Bank_Branches Y select branch_party_id id, bank_name value, bank_branch_name description from ce_bank_branches_v order by value,description US Bank Name 2 20 :param1 LOV Oracle IDC_Bank_Account_Signers Y select person_id id, full_name value, null description from per_all_people_f where person_id in (select person_id from ce_ba_signatories) and sysdate between effective_start_date and effective_end_date order by value,description US Signer Name