SELECT t.ta_system_code,
t.ta_account_no,
t.cust_name,
t.ta_cfm_serial_no,
t.transaction_cfm_date,
t.product_code,
t.transaction_type
FROM cr_transaction t
WHERE t.ta_return_code = ‘0000’
AND NOT EXISTS (
SELECT 1
FROM cr_cust_calc_load_balance l
WHERE l.ta_account_no = t.ta_account_no
)
AND NOT EXISTS (
SELECT 1
FROM cr_transaction t2
INNER JOIN cr_customer_account m ON t2.ta_account_no = m.ta_account_no
WHERE t2.ta_system_code = ‘ETFTA’
AND m.ta_account_no IS NULL
AND t2.ta_cfm_serial_no = t.ta_cfm_serial_no
)