Oracle applications (11i) daily credit card failure report – ipayment gateway
- Oracle Applications:Checking Credit Card Number validity in Internet Expenses, iPayments using Luhn algorithm (modulus 10)
- Auto shipment notification to companies using oracle applications – Concurrent Job shell script
- Get Credit Card Number for an invoice (oracle 11i)
- Get Credit Card Number for a customer number (oracle 11i)
- Oracle applications (11i) Invalid credit cards in bank records report
User needs the daily credit card failure report to follow-up with customers. This will attach the generated excel sheet and send the email to the list of users. This is registered as unix host concurrent Program in oracle applications. This can also be simply modified as a crontab report. This is a practical example of unix / linux with oracle applications producing excel sheet. The attachment is possible as UUENCODE.
Download oracle_ipayment_credit_card_failure_report.sh
#
# Author: Jay
#
# P_EMAIL_ADDRESS_TO is a parameter from the concurrent job
#
old_args=$@
FILENAME="Credit_Card_Failure_Report.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
spool $FILENAME
SELECT RPAD (SUBSTR (a, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (b, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (c, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (d, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (e, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (f, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (g, 1, 16), 16, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (h, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (i, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (j, 1, 15), 15, ‘ ‘)
” chr(9)
” RPAD (SUBSTR (to_char(k), 1, 15), 15, ‘ ‘)”chr(9)
FROM (
select a,b,c,d,e,f,g,h,max(i) i,j,k from
(
SELECT decode(rct.org_id,262,’China Operating Unit’,283,’Europe Operating Unit’,303,’India Operating Unit’,307,’UK Operating Unit’,311,’Australia Operating Unit’,315,’Japan Operating Unit’,319,’Hong Kong Operating Unit’,223,’US Operating Unit’) a
,ac.customer_number b
,ac.customer_name c
,rct.attribute2 d
,rct.trx_date e
,rct.trx_number f
,rt.NAME g
,LPAD (SUBSTR (aba.bank_account_num,LENGTH (aba.bank_account_num)-3,4),
LENGTH (aba.bank_account_num),’*’ ) h
,nvl(max(its.bepmessage),’Declined’) i
,acol.NAME j
,aps.amount_due_remaining k
FROM
RA_CUSTOMER_TRX_ALL RCT
,RA_TERMS RT
,AP_BANK_ACCOUNTS_ALL ABA
,AR_CUSTOMERS AC
,AR_CUSTOMER_PROFILES ACP
,AR_COLLECTORS ACOL
,AR_PAYMENT_SCHEDULES_ALL APS
,RA_BATCH_SOURCES_ALL RBS
,APPS.IBY_TRXN_SUMMARIES_ALL ITS
WHERE 1=1
AND RCT.CUSTOMER_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AND RCT.BILL_TO_CUSTOMER_ID = AC.CUSTOMER_ID
AND RCT.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND ACP.CUSTOMER_ID = AC.CUSTOMER_ID
AND ACP.COLLECTOR_ID = ACOL.COLLECTOR_ID
AND RCT.TERM_ID = RT.TERM_ID
AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND ACP.SITE_USE_ID IS NULL
AND APS.AMOUNT_DUE_REMAINING > 0
AND APS.ORG_ID > 202
AND ITS.INSTRNUMBER = APPS.IBY_INSTRREG_PUB.ENCODE (ABA.BANK_ACCOUNT_NUM)
AND ITS.STATUS <> 0
AND ITS.CREATION_DATE >= SYSDATE – 2*365
AND ITS.STATUS > 0
AND ITS.INSTRTYPE = ‘CREDITCARD’
AND RCT.CREATION_DATE >= SYSDATE – 2*365
AND RCT.ORG_ID > 202
AND ABA.ACCOUNT_TYPE=’EXTERNAL’
AND RCT.ORG_ID = APS.ORG_ID
GROUP BY decode(rct.org_id,262,’China Operating Unit’,283,’Europe Operating Unit’,303,’India Operating Unit’,307,’UK Operating Unit’,311,’Australia Operating Unit’,315,’Japan Operating Unit’,319,’Hong Kong Operating Unit’,223,’US Operating Unit’)
,ac.customer_number
,ac.customer_name
,rct.attribute2
,rct.trx_date
,rct.trx_number
,rt.NAME
,LPAD (SUBSTR (aba.bank_account_num,LENGTH (aba.bank_account_num)-3,4),
LENGTH (aba.bank_account_num),’*’ )
,acol.NAME
,aps.amount_due_remaining
union
SELECT decode(rct.org_id,262,’China Operating Unit’,283,’Europe Operating Unit’,303,’India Operating Unit’,307,’UK Operating Unit’,311,’Australia Operating Unit’,315,’Japan Operating Unit’,319,’Hong Kong Operating Unit’,223,’US Operating Unit’) a
,ac.customer_number b
,ac.customer_name c
,rct.attribute2 d
,rct.trx_date e
,rct.trx_number f
,rt.NAME g
,LPAD (SUBSTR (aba.bank_account_num,LENGTH (aba.bank_account_num)-3,4),
LENGTH (aba.bank_account_num),’*’ ) h
,nvl(max(its.bepmessage),’Invalid CC or expiry date’) i
,acol.NAME j
,aps.amount_due_remaining k
FROM
RA_CUSTOMER_TRX_ALL RCT
,RA_TERMS RT
,AP_BANK_ACCOUNTS_ALL ABA
,AR_CUSTOMERS AC
,AR_CUSTOMER_PROFILES ACP
,AR_COLLECTORS ACOL
,AR_PAYMENT_SCHEDULES_ALL APS
,RA_BATCH_SOURCES_ALL RBS
,APPS.IBY_TRXN_SUMMARIES_ALL ITS
WHERE 1=1
AND RCT.CUSTOMER_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AND RCT.BILL_TO_CUSTOMER_ID = AC.CUSTOMER_ID
AND RCT.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND ITS.INSTRNUMBER(+) = APPS.IBY_INSTRREG_PUB.ENCODE (ABA.BANK_ACCOUNT_NUM)
AND nvl(ITS.STATUS,1) <> 0
–AND ITS.CREATION_DATE >= SYSDATE – 2*365
–AND ITS.STATUS > 0
— AND ITS.INSTRTYPE = ‘CREDITCARD’
AND ACP.CUSTOMER_ID = AC.CUSTOMER_ID
AND ACP.COLLECTOR_ID = ACOL.COLLECTOR_ID
AND RCT.TERM_ID = RT.TERM_ID
AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND ACP.SITE_USE_ID IS NULL
AND APS.AMOUNT_DUE_REMAINING > 0
AND APS.ORG_ID > 202
AND RCT.CREATION_DATE >= SYSDATE – 2*365
AND RCT.ORG_ID > 202
AND ABA.ACCOUNT_TYPE=’EXTERNAL’
AND RCT.ORG_ID = APS.ORG_ID
AND
(
RCT.CC_ERROR_FLAG IS NOT NULL
)
AND RCT.CC_ERROR_CODE IS NULL
GROUP BY decode(rct.org_id,262,’China Operating Unit’,283,’Europe Operating Unit’,303,’India Operating Unit’,307,’UK Operating Unit’,311,’Australia Operating Unit’,315,’Japan Operating Unit’,319,’Hong Kong Operating Unit’,223,’US Operating Unit’)
,ac.customer_number
,ac.customer_name
,rct.attribute2
,rct.trx_date
,rct.trx_number
,rt.NAME
,LPAD (SUBSTR (aba.bank_account_num,LENGTH (aba.bank_account_num)-3,4),
LENGTH (aba.bank_account_num),’*’ )
,acol.NAME
,aps.amount_due_remaining
)
GROUP BY a,b,c,d,e,f,g,h,j,k
);
EOF
if [ -f $FILENAME ]
then
uuencode $FILENAME $FILENAME ‘ mail -s "$FILENAME status! Reported on `date` " "$P_EMAIL_ADDRESS_TO"
# uuencode $FILENAME $FILENAME ‘ mail -s "$FILENAME status! Reported on `date` " "jay@notesbit.com" # For test purposes only!
fi
If you want to see a reader’s feedback
, I rate this article for four from five. Decent info, but I just have to go to that damn google to find the missed parts. Thank you, anyway!
Great post! Just wanted to let you know you have a new subscriber- me!