Oracle applications (11i) Invalid credit cards in bank records report
- Oracle applications (11i) daily credit card failure report – ipayment gateway
- Bash script – Passing arguments to the shell script Unix/Linux.
- Oracle Applications:Checking Credit Card Number validity in Internet Expenses, iPayments using Luhn algorithm (modulus 10)
- Paymentech file comparison in Linux
- How To Extract,Using Bash/Unix/Linux Shell Scripting, The Oracle Data Like Excel Report And Send As Email Attachment?
Credit cards are loaded through interfaces. Oracle application, by default, accepts any value as this varchar2 format. However, the credit cards should be listed as a continuous number without any space or dashes or any special chars.
We can try to eliminate these at source using filter in loader control file. Still some of them elusive like termination char “\0″. This is common when the source of the data comes from mysql server or sybase.
These are invisible naked eye. You can review this with dump command. This program identifies all the invisible or unwanted or special chars.
If there is any hit, then only it will send an email. Otherwise no emails sent. This is handled at “if [ $FILESIZE -gt 444 ]“. Without a hit it produces 444 bytes file.
Download invalid_credit_cards_at_bank_records.sh
/*
* Author: Jay@notesbit.com
*/
old_args=$@
#
FILENAME="Invalid_credit_cards_in_bank_records.xls"
P_EMAIL_ADDRESS_TO=`echo $old_args ‘cut -f9 -d’ ‘ ‘ cut -f2 -d‘=’ ‘ sed ‘s/"//g’`
#
#source /usr/local/bin/PRODUCTION.env # For test purposes
#
if [ -f $FILENAME ]
then
rm -rf $FILENAME
echo "Old Data File ($FILENAME) deleted…"
fi
#
#echo $FILENAME
#echo "$P_EMAIL_ADDRESS_TO"
#
sqlplus -s $FCP_LOGIN<<EOF
SET echo OFF
SET feedback OFF
SET heading off
SET linesize 221
SET pagesize 0
SET newpage 0
–alter session set current_schema=apps; — For test purposes
spool $FILENAME
select ‘Invalid_credit_cards_in_bank_records:To be corrected at source’
from dual;
select ‘customer_name’ ” chr(9) ”
’Invoice_number’ ” chr(9) ”
’Dump CC Number”’ chr(9) ”
’Credit Card Name’ ” chr(9) ”
’Credit Card Number”’ chr(9) ”
’ Credit Card Validity’ ” chr(9) ”
’Invoice Amount’
from dual;
select rc.customer_name ” chr(9) ”
rct.trx_number ” chr(9) ”
to_char(dump(apa.bank_account_num))” chr(9) ”
apa.bank_account_name ” chr(9) ”
’************”’substr(apa.bank_account_num,-4) ” chr(9) ”
apa.inactive_date ” chr(9) ”
aps.AMOUNT_DUE_REMAINING
from ap_bank_accounts_all apa,
ra_customer_trx_all rct,
ra_customers rc,
ar_payment_schedules_all aps,
RA_BATCH_SOURCES_ALL RBS
where 1=1
AND RBS.NAME =’InvoiceSourceName’ — change to actual value
AND RCT.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
and rct.customer_trx_id = aps.customer_trx_id
and aps.status = ‘OP’
and apa.bank_account_id = rct.customer_bank_account_id
and rc.customer_id = rct.bill_to_customer_id
and replace(replace(replace(replace(apa.bank_account_num,chr(0),null),chr(32),null),chr(45),null),chr(95),null)<>apa.bank_account_num
AND aPS.gl_date_closed = TO_DATE(’4712/12/31′, ‘YYYY/MM/DD’)
AND aps.selected_for_receipt_batch_id IS NULL
and rct.creation_date >= to_date (’01-FEB-2005′,’DD-MON-YYYY’) — For better performance, this can be removed
and rct.attribute9=’CREDIT CARD’
AND aps.reserved_type IS NULL
AND aps.reserved_value IS NULL
and length(replace(replace(replace(replace(apa.bank_account_num,chr(0),null),chr(32),null),chr(45),null),chr(95),null)) in ( 15, 16 )
;
EOF
if [ -f $FILENAME ]
then
FILESIZE=$(stat -c%s "
if [ $FILESIZE -gt 444 ]
then
uuencode $FILENAME $FILENAME ‘ mail -s "$FILENAME. Reported on `date` " "$P_EMAIL_ADDRESS_TO"
# uuencode $FILENAME $FILENAME ‘ mail -s "$FILENAME. Reported on `date` " "jay@notesbit.com"
fi
fi
Following Google Searches Lead To This Post:
gl_date_closed “4712/12/31″
Comments
No comments yet.