Sunday, August 14, 2011

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect