Wednesday, November 30, 2016

Extract emailed attachment and upload to IBMi


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

No comments:

Post a Comment