/*
Description: For any customer number, this gives credit card or bank account number (oracle 11i)
Author:      Jay Gopal / Jiltin
Input:       &&Cust_number
*/

SELECT
RC.CUSTOMER_NAME,
RC.CUSTOMER_NUMBER,
BACCT.BANK_ACCOUNT_NAME BANK_NAME,
BACCT.BANK_ACCOUNT_NUM CREDIT_CARD_NUMBER,
TO_CHAR(bacct.inactive_date,'DD-MON-YYYY')  CREDIT_VALIDAITY_DATE, BANK_ACCOUNT_USES_id, BACCT.BANK_ACCOUNT_ID
FROM
AP_BANK_ACCOUNT_USES_ALL BAUSES,
AP_BANK_BRANCHES BBNCH,
AP_BANK_ACCOUNTS_ALL BACCT,
RA_CUSTOMERS  RC
WHERE 1=1
AND BAUSES.EXTERNAL_BANK_ACCOUNT_ID = BACCT.BANK_ACCOUNT_ID
AND BACCT.BANK_BRANCH_ID = BBNCH.BANK_BRANCH_ID
AND BAUSES.CUSTOMER_ID   = RC.CUSTOMER_ID
AND RC.CUSTOMER_NUMBER IN ( '&&Cust_number');