Checking Credit Card Number validity in Internet Expenses, iPayments

SELECT

rct.org_id Operating_unit,

rc.customer_name customer,

rct.trx_number invoice_number,

aps.AMOUNT_DUE_REMAINING,

apa.bank_account_name bank_name,

apa.bank_account_num credit_card_number,

apa.inactive_date credit_validaity_date,

acol.NAME collector_name

FROM

AP_BANK_ACCOUNTS_ALL apa,

ra_customer_trx_all rct,

ra_customers rc,

ar_payment_schedules_all aps,

ra_batch_sources_all rbs,

AR_CUSTOMER_PROFILES ACP,

AR_COLLECTORS ACOL

WHERE

1 =1

AND ACP.CUSTOMER_ID = rc.CUSTOMER_ID

AND ACP.COLLECTOR_ID = ACOL.COLLECTOR_ID

AND ACP.SITE_USE_ID IS NULL

AND rct.batch_source_id = rbs.batch_source_id

AND rct.customer_trx_id = aps.customer_trx_id

AND rct.org_id = aps.org_id

AND apa.bank_account_id = rct.customer_bank_account_id

AND rc.customer_id = rct.bill_to_customer_id

AND aPS.gl_date_closed = TO_DATE(‘4712/12/31′, ‘YYYY/MM/DD’)

AND aps.selected_for_receipt_batch_id IS NULL

AND aps.status = ‘OP’

AND rct.attribute9 =‘CREDIT CARD’

AND RCT.ORG_ID > 202

AND aps.reserved_type IS NULL

AND aps.reserved_value IS NULL

AND aps.AMOUNT_DUE_REMAINING > 0

ORDER BY

aps.AMOUNT_DUE_REMAINING DESC;