SELECT
HP.PARTY_NAME BANK_NAME,
CBA.BANK_ACCOUNT_NUM ACCOUNT_NUMBER,
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),
basig.other_limits
FROM
CE_BA_SIGNATORIES BASIG ,
PER_PEOPLE_F PPF ,
PER_ALL_ASSIGNMENTS_F PAAF ,
CE_BANK_ACCOUNTS CBA ,
HZ_PARTIES HP
WHERE
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=HP.PARTY_ID AND
HP.PARTY_ID=:PARAM1 AND
BASIG.BANK_ACCOUNT_ID=NVL(:PARAM2,BASIG.BANK_ACCOUNT_ID)
ORDER BY BASIG.BANK_ACCOUNT_ID,BASIG.END_DATE DESC |
