New challenge – Extract
Excel spreadsheet of yesterday’s bank settled credit card transactions from an E-mailed zip file attachment, send spreadsheet to the IBMi, appending data to SQL table and launch balancing program to match the transactions with the backend ERP deposits
table.
Background information -
Our previous credit card software, Payware Transact was discontinued and no longer supported. We decided to stay with Payware and upgrade to Payware Connect. Several
years ago, I created an automated process that matched ERP deposits with
Transact Request file and send me an Email with totals for each store and
subject line telling me if in balance or not. Nothing worse than accountant
coming to me two months later with a failed transaction and wanting to know why
and to fix it.
The process has one flaw, it does not capture the Internet transactions
processed through Authorize.net. At the time, I was working on store close out
balancing and I was able to complete project since Internet was not the focus. Internet has their own outsourced IT resources not managed by me.
The process of upgrading the ERP software was brutal. I was
at mercy of ERP vendor and not able to figure out what the resulting
process is going to be. During the 18-month sales cycle, I was told we would have
same capability as we currently have. So, I held my breath and took my lumps as
they occurred.
As it turned out we did not get what we had and I had to
change processes to fit what the ERP vendor wanted to code for. Settlement was by push and now set as a timer on Payware portal. I also discovered that the new programming marks the deposits records settled just because the transactions were sent. The transactions are not actually settled until PNC accepts and processes the transactions from Payware starting at 11:40pm. I brought this to the ERP vendor's attention and was told that "we have not seen any failure with this so we are not going to enhance programming". The API's exist to do this correctly but unfortunately I have limited Java experience and smart enough to know I do not want my fingerprints on Credit Card transaction processing code. There is more to the nightmare
but of no value to mention here. Bottom line
is new software is running and processing credit card transactions.
Since Payware Connect does not have a Request file holding status of processed credit card transactions I must change the process I created to balance with the ERP deposits table.
*Note - To
complete the following process, I had to first set up Client Solutions to run from the
IFS. See instructions later in this document.
New process
outline –
1) When email is received, automatically extract
attachment to folder on my desktop.
2) Unzip .XLSX from extracted attachment to IBMi
IFS shared folder IACOUT.
3) Delete PNC upload.xlsx from IFS shared folder IACOUT.
4) Rename *.xlsx file in IFS shared folder IACOUT
to PNC upload.xlsx.
5) Run Client Solutions extract to append PNC
upload.xlsx to PNCTRAN table on IBMi.
6) Run program to generate spreadsheet and email.
New process
details –
When email is received automatically, extract attachment to
folder on my desktop:
I searched Google looking for email extractor programs. If I
was handy with VB there are examples that I could have worked with.
After looking
over a few programs available I settled on Outlook
Email Extractor. It will work with Exchange Server, enables extraction to
any folder, has subsequent processing option and non-recurring low price.
The software was easy to install and setup. I have been
running trial version for a couple of weeks and works flawlessly.
Outlook Email Extractor –
Selecting existing profile and Edit displays profile setting
dialog. Obviously if one does not exist you would select New.
General tab you give the Profile a name. E-mail Folder is
where you specify what folder you want to watch.
Lots of filters to choose from, I am monitoring for sender
name and subject.
Storage location, this is where it starts to get a little tricky.
I am directing the output to a folder on my desktop and renaming to a generic
name to so I can hardcode the batch with name of .zip to extract from. Files is
received as Transaction Listing (9999999999).zip and I output as PNC daily
upload.zip.
Example of email received –
Subsequent – this is where I tell the extractor
to run batch file PNC IBMi upload.bat located in my Google Drive folder.
The last tab, Catch Up, is awesome. Allows me to run as many
times as I need to test the process.
Batch file – PNC IBMi
upload.bat
This batch program unzips the emailed zip file and launches IBMi
Client Solutions command line processing to call a CLLE program on the IBMi.
The CLLE program called is a wrapper that submits the actual program that
transfers file from IFS and appends to SQL table on IBMi.
I attempted to adjust my PC path environment variable but
was unsuccessful and decided to not get bogged down figuring out why and hard
coded the path to executables as needed.
1) Change directory to 7-Zip.Extract PNC daily upload.zip (renamed from email
earlier in the process)
2) Change directory to Client Solutions.To get around the password requirement you use
PLUGIN=cfg
3) PLUGIN=logon, to logon with my credentials.
4) PLUGIN=rmtcmd, calls my submit program on IBMi.
5) PLUGIN=cfg /del, this will remove the configuration
set in step four. This is for security purposes.
6)Pause, I put a pause in so the command window
will hang telling me all has completed. I will remove this after a few weeks
and when I am satisfied with the process.
PNC Transfer and Call
Balancing program –
Submit program call by PLUGIN=rmtcmd.
Why JOBQ QS36EVOKE? The queue is multithreaded and not a
production job queue. If something gets stuck I am notified and I can address. MSGW
will not hold up production jobs if failure occurs. This type of job is ok to
leave here for now.
Submit Client Solutions transfer and balancing program –
1) Delete existing object PNC upload.xlsx
2) Rename transferred spreadsheet to PNC
upload.xlsx
3) Qshell java command to run command line
transfer. (See set up Client Solutions in
IFS later in this document.)
4)Run program to balance data to deposits and
generate E-mail.
Balancing and email program –
This is a jewel and unfortunately, I do not have the
time to go through it step by step. If you have any questions about any to the SQL
code let me know.
I have since replaced CEELOCT with my own date service
program. I am not converting this program to use the service program as it is a
good example of CEELOCT API.
I do push the limits of CLLE. New RUNSQL command is awesome.
/*-------------------------------------------------------------------*/
/* Create and send via Email
yesterday First Data Settlements
*/
/*-------------------------------------------------------------------*/
PGM
DCL VAR(&QTIME) TYPE(*CHAR) LEN(6)
DCL VAR(&RPTDT)
TYPE(*CHAR) LEN(6)
DCL VAR(&SQL)
TYPE(*CHAR) LEN(500)
DCL VAR(&IFS) TYPE(*CHAR) LEN(100) +
VALUE('\\qcapibmi\excel\')
DCL VAR(&USER) TYPE(*CHAR) LEN(10)
DCL VAR(&VIEWAS) TYPE(*CHAR) LEN(02) VALUE('01') /* +
Excel */
DCL VAR(&SPLF) TYPE(*CHAR) LEN(10) VALUE(SETTLED) /* +
Program name */
DCL VAR(&FILE) TYPE(*CHAR) LEN(60) VALUE('Yesterday +
settled amounts')
DCL VAR(&FILEF) TYPE(*CHAR) LEN(60) VALUE('Yesterday +
settled amounts')
DCL VAR(&FILENAME) TYPE(*CHAR) LEN(100)
DCL VAR(&PATHEXL) TYPE(*CHAR) LEN(100) VALUE('/excel/')
DCL VAR(&EXLMSG) TYPE(*CHAR) LEN(100)
DCL VAR(&EXLH_DT) TYPE(*CHAR) LEN(10)
DCL VAR(&SETL_DT) TYPE(*CHAR) LEN(10)
DCL VAR(&SETL) TYPE(*CHAR) LEN(9) VALUE('SETTLED:%')
DCL VAR(&MSG_STAT) TYPE(*CHAR) LEN(100)
/* Date routine variables
----------------------------------------------*/
DCL VAR(&YESTERDAY) TYPE(*DEC) LEN(8 0)
DCL VAR(&LILIAN) TYPE(*CHAR) LEN(4)
DCL VAR(&JUNK1) TYPE(*CHAR) LEN(8)
DCL VAR(&JUNK2) TYPE(*CHAR) LEN(23)
DCL VAR(&WDATE) TYPE(*CHAR) LEN(8)
DCLF FILE(SETTBALA) OPNID(SETTBAL)
/* Get local time from system:
When this call is */
/* complete, &LILIAN will contain the number of */
/* days between today and Oct 14,
1582. */
CALLPRC PRC(CEELOCT) PARM(&LILIAN &JUNK1 &JUNK2 *OMIT)
/* Subtracting 1 from &LILIAN
will produce yesterday's date */
CHGVAR VAR(%BIN(&LILIAN)) VALUE(%BIN(&LILIAN) - 1)
/* Convert Lillian to yyymmdd date
*/
CALLPRC PRC(CEEDATE) PARM(&LILIAN 'YYYYMMDD' &WDATE *OMIT)
CHGVAR VAR(&YESTERDAY) VALUE(&WDATE)
/* End date routine
--------------------------------------------------*/
CHGVAR VAR(&SETL_DT) VALUE(%SST(&WDATE 1 4) *CAT '-' *CAT +
%SST(&WDATE 5 2) *CAT '-' *CAT %SST(&WDATE 7 2))
CHGVAR VAR(&EXLH_DT) VALUE(%SST(&WDATE 5 2) *CAT '/' *CAT +
%SST(&WDATE 7 2) *CAT '/' *CAT %SST(&WDATE 1 4))
CHGVAR VAR(&EXLMSG) VALUE(&EXLH_DT *BCAT 'First Data +
Settlements')
RTVJOBA USER(&USER) DATE(&RPTDT)
/* ERP program to retrieve file
name for this run */
CALL PGM(XAFILENAME) PARM(&VIEWAS &SPLF &FILE &FILEF)
CHGVAR VAR(&FILENAME) VALUE(&PATHEXL *TCAT &USER *TCAT +
'/' *TCAT %SST(&FILE 1 29))
/* Create table to hold summarized
transaction dollars from uploaded data */
RUNSQL SQL('create table r50modsdta.pncssum as
(select f2 +
as rzclid, f5 as
settle_date, +
cast(ifnull(sum(case
when f1 = ''Refund'' then +
f12 end),0) as
decimal(11,2)) as crdt_crdt_amt, +
cast(ifnull(sum(case when
f1 = ''Sale'' then f12 +
end),0) as
decimal(11,2)) as crdt_sale_amt from +
r50modsdta.pnctrans
where f5 = ''' *CAT &SETL_DT +
*CAT ''' group by
f2,f5 order by f2) with DATA') +
COMMIT(*NONE) NAMING(*SQL)
/* Create table to output in email
based on summarized transaction dollars */
RUNSQL SQL('create table r50modsdta.sett_ord as
(select +
owstr, owname,
rzclid, settle_date, +
ifnull(dec(crdt_sale_amt,11,2),0) as +
debit_sale,ifnull(dec(crdt_crdt_amt,11,2),0) as +
credit_sale,
ifnull(dec(crdt_sale_amt,11,2),0) - +
ifnull(abs(dec(crdt_crdt_amt,11,2)),0) as +
settle_net from
r50files.vcostore inner join +
r50modsdta.rtccxref
on cxvaiid = owmrch left +
outer join
r50modsdta.pncssum on rzclid = +
cxpncid and
settle_date = ''' *CAT &SETL_DT *CAT +
''' where owdel = ''A'') WITH DATA') +
COMMIT(*NONE) NAMING(*SQL)
/* Add ERP deposits net column to
output table */
RUNSQL SQL('alter table r50modsdta.sett_ord add
column +
deposit_net dec(11,2)
default 0 not null') +
COMMIT(*NONE) NAMING(*SQL)
/* Add ERP deposits debit column
to output table */
RUNSQL SQL('alter table r50modsdta.sett_ord add
column +
deposit_debit
dec(11,2) default 0 not null') +
COMMIT(*NONE) NAMING(*SQL)
/* Add ERP deposits credit column
to output table */
RUNSQL SQL('alter table r50modsdta.sett_ord add
column +
deposit_credit
dec(11,2) default 0 not null') +
COMMIT(*NONE) NAMING(*SQL)
/* Update output table with ERP
summarized debits */
RUNSQL SQL('update r50modsdta.sett_ord a set +
a.deposit_debit =
ifnull((select cast(sum(tdamt) +
as dec(11,2)) as
debit from r50files.vardeps b +
inner join
r50files.vcohead on oaord = b.tdord +
and oabocd = b.tdbocd
where a.owstr = oastr and +
b.tddate = ''' *CAT &WDATE *CAT ''' and +
(b.tdpdby = ''A'' or
b.tdpdby = ''D'' or +
b.tdpdby = ''M'' or
b.tdpdby = ''V'' or tdpdby = +
''WA'' or tdpdby =
''WD'' or tdpdby = ''WM'' or +
tdpdby = ''WV'') and
b.tdaut# <> ''B/O***'' and +
b.tddc = ''D'' group
by oastr),0)') +
COMMIT(*NONE) NAMING(*SQL)
/* Update output table with ERP
summarized credits */
RUNSQL SQL('update r50modsdta.sett_ord a set +
a.deposit_credit =
ifnull((select +
cast(sum(tdamt) as
dec(11,2)) as debit from +
r50files.vardeps b
inner join r50files.vcohead +
on oaord = b.tdord
and oabocd = b.tdbocd where +
a.owstr = oastr and
b.tddate = ''' *CAT &WDATE +
*CAT ''' and
(b.tdpdby = ''A'' or b.tdpdby = +
''D'' or b.tdpdby =
''M'' or b.tdpdby = ''V'' or +
tdpdby = ''WA'' or
tdpdby = ''WD'' or tdpdby = +
''WM'' or tdpdby =
''WV'') and b.tdaut# <> +
''B/O***'' and b.tddc
= ''C'' group by +
oastr),0)') COMMIT(*NONE) NAMING(*SQL)
/* Update output table with ERP
summarized net of credits and debits */
RUNSQL SQL('update r50modsdta.sett_ord a set +
a.deposit_net =
ifnull((select cast(sum(tdamt) +
as dec(11,2)) as
debit from r50files.vardeps b +
inner join
r50files.vcohead on oaord = b.tdord +
and oabocd = b.tdbocd
where a.owstr = oastr and +
b.tddate = ''' *CAT &WDATE *CAT ''' and +
(b.tdpdby = ''A'' or
b.tdpdby = ''D'' or +
b.tdpdby = ''M'' or
b.tdpdby = ''V'' or tdpdby = +
''WA'' or tdpdby =
''WD'' or tdpdby = ''WM'' or +
tdpdby = ''WV'') and
b.tdaut# <> ''B/O***'' and +
b.tddc = ''D'' group
by oastr),0) - +
ifnull((select
cast(sum(tdamt) as dec(11,2)) as +
debit from
r50files.vardeps c inner join +
r50files.vcohead on
oaord = c.tdord and oabocd = +
c.tdbocd where
a.owstr = oastr and c.tddate = +
''' *CAT &WDATE *CAT ''' and
(c.tdpdby = ''A'' +
or c.tdpdby = ''D''
or c.tdpdby = ''M'' or +
c.tdpdby = ''V'' or
tdpdby = ''WA'' or tdpdby = +
''WD'' or tdpdby =
''WM'' or tdpdby = ''WV'') +
and c.tdaut# <>
''B/O***'' and c.tddc = ''C'' +
group by oastr),0)') COMMIT(*NONE) NAMING(*SQL)
/* Create temp table used to check
if in balance */
RUNSQL SQL('Drop Table qtemp.settbala') COMMIT(*NONE)
MONMSG MSGID(SQL9010)
RUNSQL SQL('create table qtemp.settbala as (select +
dec(sum(settle_net),11,2) - +
dec(sum(deposit_net),11,2) as balance from +
r50modsdta.sett_ord)
with data') COMMIT(*NONE) +
NAMING(*SQL)
/* Linoma Suveyor export table to
IFS as Excel workbook */
SURVEYOR/EXPDTA USEQRY(*NO) INFILE(R50MODSDTA/SETT_ORD) +
TYPEOFEXP(*EXCEL) XLSTITLEYN(*YES) +
XLSTITLE(&EXLMSG) XLSTBOLD(*YES) +
HEADTYPE(*HEADINGS) XLSHSTROW(4) XLSBOLD(*NO) +
XLSHUNDERL(*YES) XLSHBCOLOR('*LIGHT_GRAY') +
XLSAUTOSIZ(*YES) XLSSHEET(RTSETTDCL) +
IFSDEST(&FILENAME) OUTPUT(*NONE)
/* Set up subject line - .10 threshold */
RCVF OPNID(SETTBAL)
IF COND((&SETTBAL_BALANCE *LE .10) *AND +
(&SETTBAL_BALANCE *GE -.10)) THEN(DO)
CHGVAR VAR(&MSG_STAT) VALUE('Yesterday PNC
Settlement +
is in balance!
Have a great day!')
CHGVAR VAR(&MSG_STAT) VALUE(%trim(&MSG_STAT))
SNDMAIL RECIPIENT((RICHARD xxxxxxx@xxxxxx.com) (JASON +
xxxxxxxxx@xxxxx.COM)) SENDER(QCAPIBMI +
xxxxxx@xxx.com) SUBJECT(&MSG_STAT) +
MESSAGE(&EXLMSG) ATTACHMENT(&FILENAME)
ENDDO
ELSE DO
CHGVAR VAR(&MSG_STAT) VALUE('Yesterday PNC
Settlement, +
one or more stores
are OUT OF BALANCE!')
CHGVAR VAR(&MSG_STAT) VALUE(%trim(&MSG_STAT))
SNDMAIL RECIPIENT((RICHARD xxxx@xxxx.com) (JASON +
xxxxxxxxx@xxxxx.COM) (HELPDESK +
HELPDESK@1800LIGHTING.COM)) SENDER(QCAPIBMI +
xxxxxx@xxx.com) SUBJECT(&MSG_STAT) +
MESSAGE(&EXLMSG) ATTACHMENT(&FILENAME)
ENDDO
/* Delete the temp tables */
DLTF FILE(r50modsdta/SETT_ORD)
DLTF FILE(r50modsdta/PNCSSUM)
END:
Running
IBMi Client solutions from IFS.
While searching with Google I found this PDF
by Craig Pelkie. He does an awesome job of showing us how to set up Client
Solutions to run from IFS.
Complete Instructions –
The email I receive contains the excel
spreadsheet created during the balancing process. I removed the data for this
example. Basically, if out of balance subject is received I look and Column G
and H looking for the mismatch. Then the real fun begins.
Ran automatically this morning without issue.
Have your best day!
~Richard
