Information Systems:Bank EDI
Bank EDI
Bank EDI is a custom extension for ASW that allows the system to automate the processing of files received from the banks. Do not confuse this with regular EDI (which is how we transmit and receive POs and invoices with our vendors) as this is a completely different and unrelated process. Bank files are not received automatically; The finance department uses secure services to manually get them from the banks.
There are two types of files; debit and credit card transactions from stores, and detail of the cheques clearing uniPHARM's accounts.
The Bank EDI extension is accessed from Mochasoft in the (1)UWD Extensions menu and then the (70)Bank EDI menu.
12/03/14 11:48:32 uniPHARM -- PRODUCTION BERIQ030/CTL
Bank EDI Status
FI=Files imported FW=Files waiting
---------- Files ---------- Last Last
Interface Waiting Imported In error Import Post
__ TD 2254 2014-12-03 2014-12-03
__ Global Payments 577 2007-07-19 2007-07-19
__ CIBC 2131 2014-12-03 2014-12-03
__ Chase Paymentech
F3=Exit F5=Refresh F7=Merchants F8=Configuration F12=Previous
- note that Global Payments is no longer in use *
The interface name is passed as a parameter to all called programs, and controls the kind of processing done. 'CIBC' is cheque clearing; the others are debit and credit card processing.
'Files waiting' have been received from the bank and placed onto the IFS, but have not yet been imported. 'Files in error' have been imported, but something prevents them from being posted.
For technical details on this click on the following document:
\\Superserver\Systemi Documentation\Extension Manuals\Bank EDI Technical Manual V1.0.doc
Or you can go to:
\\Superserver\Unity\S000- UNITY Project\P0300 - Phase III Implementation\P0350 - UNITY Subprojects\UP016 -- Bank EDI Interfaces\...
...to see all of the files associated with this extension project.
Credit and Debit Card Transactions
Up to November 2016, we have been using the TD Bank for all of the debit card and credit card processing. After that, we will be switching to Chase Paymentech. The files are downloaded from the bank website by the finance department, then put onto the IFS. Bank EDI is used to upload it an post it to ASW.
At a high level the way this works is that our shareholders get debit and credit card terminals for processing payments at their store through uniPHARM's account with the bank. Because of the combined volume, uniPHARM is able to get better rates than a store could get on their own. The terminals are set up to deposit all of the funds collected into uniPHARM bank accounts. uniPHARM then uses the bank EDI extension process to move the funds back from the uniPHARM bank accounts onto the individual store accounts to offset their purchases.
Transactions are summarized by card type, and put them into accounts receivable for the shareholder. They will show on the next statement. If they make their account go into credit, we put the amount into their bank account via EFT.
Because this application was originally written for TD, Chase transactions must be translated to use the same transaction types and card types. If Chase sends a record with a new, unmapped type, it will have to be added to tables UX BEDICPCRDT or UX BEDICPTRNT.
As well as these A/R transactions, entries to the G/L are also made; ie. credit A/R and debit cash.
Store Payments to uniPHARM
The finance department has a card reader which they use to process accounts receivable payments from stores, using their credit cards. These records can be identified by the fact that the merchant ID on them matches one of the uniPHARM merchant numbers in the configuration file. The credit card account number, and card type is used to read the merchant file, and get the ASW customer number. Then a payment to that account, and the underlying G/L entries, are created.
Cheque Clearing
These files are used to reconcile cheques, and update the cash book. They used to be are picked up by ‘TumbleWeed’, but are now done manually by the finance department.
Configuration
Press F8 for configuration. Note that this is DFU, which is an uncontrolled update program. Be very careful what you do. Press page down to see the record. Not all of the fields fit on one screen, so press enter to see the rest.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : BECONFR File . . . . : BECONFP
*RECNBR: 1 uniPHARM TD Merchant: 22378885
uniPHARM GP Merchant: 22378885
uniPHARM CP Merchant: 6077431
TD Folder: BankEDI2/TD
TD Archive Folder: BankEDI2/TD/Archive
Chase Payments Folder: BankEDI2/Chase
Chase Archive Folder: BankEDI2/Chase/Archive
CIBC Folder: BankEDI2/CIBC
CIBC Archive Folder: BankEDI2/CIBC/Archive
Printer: LP05
Copies: 1
Hold?: N
Save?: N
uniPHARM TD-M Merchant: 23035553
uniPHARM CP-M Merchant: 6077431
uniPHARM GP Merchant: 999999999999
Global Payments Folder: BankEDI2/Global
Global Archive Folder: BankEDI2/Global/Archive
uniPHARM GP-M Merchant: 999999999999
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
The 'folders' are the locations in the IFS where files are imported from, then archived to. 'uniPHARM Merchant' numbers are used to identify transactions where shareholders are making payments to uniPHARM via their credit cards.
Issues
Masked Credit Card Numbers
Credit card account numbers are now being sent masked; ie part of the number is replace with asterisks. That makes it easier for us, as we don't have worry about having to keep them encrypted whenever we store them. Records on the merchant file contain the masked number so that transactions can be matched. The unmasked portion of the number seem to be enough to make them unique. If it happens that they aren't, the user will be prompted by a list, and asked to select one.
Bank EDI Does Not Post
Only one A/R batch payment process can be run at a time. Base ASW makes sure this doesn't happen. Several extensions process payments without checking first to make sure another isn't currently running. Originally this was okay, because QBATCH only ran one job at a time. We changed it to allow two.
When more than one card type is in a batch, this process is submitted to QBATCH for each type. As each may take a moment to run, the next must wait to be submitted. The user will see this screen -
*PL/480B* A/R batch payment update 11/30/16 11:53:49 FKR05101
-------------------------------------------------------------------------------
Accounting period... 17 10
Voucher type........ 75
Document type....... MC
Voucher date........ 113016
Identity............ MC
Remarks.............
Printer queue....... PRT01
Number of copies.... 1
Hold on spool file.. N
You cannot use this function now
They must wait a moment, then press enter to try again to submit the process.
Generate EFT from statements, and Bank EDI have been changed to check for other A/R payment batched running, but it apparently doesn't always work properly (or the A/R payment process is called from another extension).
BERUP060 – writes transactions to SROKBP
BERUP070 – updates payment batches from SROKBP (which posts 120000 and 730200), then writes transfers from 730200 to correct bank account into SROIBT Sometimes these batches do not completely post to ASW, but instead sit in SROKBP. (The function to update payment batch is locked by ASGR007. See job name FKR051 in file SROJBN. If status is ‘A’, the job is currently running, and cannot run again now.)
If there are records in SROKBP for the current batch with identity = ‘FI’, they will have to be changed to the correct identity. For example –
update srokbp set ptiden = 'VI' where ptiden = 'FI' and ptpadt = 20121207 and ptjono = 10006
So far, when I have had to do this, the records with an identity of FI have all had to be changed to VI.
To Post Manually
Select option 7 – Update A/R batch payments on menu ARPAY A/R Payment Tasks.
Voucher type is 75.
Document type / identity is AMX / AX, DBT / DB, DS / DS, MC / MC, VIS/ VI. This will post to A/R and 730200. Entries to move from 730200 to the correct bank accounts will not post, but will still be in SROIBT. The next time bank EDI is posted, they will be included. However one entry will be missing, so the journal will not be balanced. See ‘Problems / Finance / Journal in Error / Out of Balance – Debit/Credit Card Transactions’ for an explanation and how to fix it.
Duplicate File Name
If you try to import a file with the same name as one that has already been imported, you will get a message. You should cancel the process, and investigate it. Using the 'RD' (raw data) option, look at the file that has already been uploaded. A file with no transactions will have two records; a batch header and batch trailer (record types 10 and 99).
Columns . . . : 1 121 Browse UWDASWPRDD/BECPRDP
SEU==> C170118018
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9
*************** Beginning of data *********************************************************
0001.00 1001191711440310946585 TRN
0002.00 990000000020000000000000000000000000000000000000000000000000000TRN
0003.00
****************** End of data ************************************************************
If the new file to import has the same records as the original file, it can go straight to the archive (to keep a record of the fact it was received twice). Change the name by, for example, adding 'dup' to end to make it clear it was a duplicate file.
If the two files are different, I would question the bank - there is only supposed to be one file per day, and the date is part of the file name. If the second file should also be uploaded, the file name should be changed; but using a different method than above. The files are imported with the name 'C', six digit date (from the file name), then the file suffix; which is the Julian day. To import a second file for the same date, change the file suffix to make the name unique.
Even though the import process will allow you to import a file with a duplicate name, it is best not to do it.
EFT file not generated
When the "EC" menu option/function is run during the AR payments process, a text file is generated for upload to CIBC and automatically placed in the IFS. This file represents payments (AR credits) to customers. There have been several instances where the file does not get generated i.e. Accounting will report it missing from the EFT/ folder on the IFS. Because file generation is only one step in the EFT process, it is not feasible to re-run EC (in fact, it is not even possible, because the job status will have changed to prevent a duplicate run). Therefore the system command used to create the file (CPYTOSTMF) must be executed manually:
- First verify that the rest of the process (EC/E1) completed sucessfully:
- Check the status of the statement run. It should be at C1.
- Check that a printout of the G/L postings printed. Spot check a transaction listed on the printout in G/L 120000 (A/R).
- Obtain the file member name that needs to output to a file:
- WRKMBRPDM for file UWDASWPRDD/EFEFTSP.
- Find the desired member, and view it to verify that it is the correct member for which you would like to generate a file.
- Exit WRKMBRPDM
- On a command line, type CPYTOIMPF and F4-prompt.
- In the "From file" field, enter
/QSYS.LIB/UWDASWPRDD.LIB/EFEFTSP.FILE/XYZ, where XYZ is the file member determined in the previous step. - In the "To file" field, enter a directory on the IFS. Usually, I output this to my home directory i.e.
/home/NORWINU>. You could also try the actual folder it's supposed to go to (EFT/), but I'm more cautious. - Change Stream file option to *REPLACE and Stream file code page to *PCASCII.
- Hit Enter.
- If outputted to a different folder, retrieve the file and place it in the EFT/ folder. Accounting will now be able to upload it.