A/R problem phantom transactions

Don Bush don at caffco.com
Wed May 27 06:40:20 PDT 2009


we are running 5.0.09R4 on SCO Unix openserver 6.0.
I have determined the problem to be with the manual entry processes.
here is a copy of the menu script that processes the deposit and 
other transactions .
-
ddir ccdeposit -l
ddir ccacrtran -l
dreport ccdeposit -f cccacr -y dummy -a
dreport ccacrtran -f cccacr -y dummy -a
ddir ccacrtran -k


code from ccdeposit

:'File description        This process will move the 
data:'                         ccdeposit to ccacrtran:
:'            Menu        CCTRAN  Option F:'      Written by        unknown:
:'    Date Written        unknown:'         FilePro        5.0:
:'**********************  UPDATE  ************************:'DATE 
  BY       DESCRIPTION  (newest on top):
:'11/26/08   PMR     added check for file on the lookup free 
is:'catch possible errors during processing, also field to field copy:
:'07/31/98    DH      modified the date format to 4 
position:'************************************************************:
:'begin date   end date:bd(8,mdyy,G); ed(8,mdyy,G):
getbd:bd eq "":input ("23","5") bd "Enter Beginning date <mmddyyyy> ":
:bd eq "":goto getbd:
:ed eq "":input ("23","35") ed "Enter Ending date <mmddyyyy> ":
:ed eq "":ed = bd:
:2 lt bd or 2 gt ed:goto end:
::lookup trn = ccacrtran  r=free  -n:
:not trn:show "@ERROR writing to CCACRTRAN from CCDEPOSIT-CCACR"; exit:
:'cus #    date       div      ref #      Tran code:trn(1)=1; 
trn(2)=2; trn(3)=3; trn(4)=4; trn(5)=5:
:'tran amt  laidin   item amt    adv chrg:trn(6)=6; trn(7)=7; 
trn(8)=8; trn(9)=9; write trn; close trn:
::delete:
END::end:

Code from ccacrtran
:'File description            This process will post those 
transaction:'                             manually entered to the cccacrpost:
:'                             ccrmcus, and 
cccacrhist:'            Menu            CCTRAN  Option F:
:'      Written By:'    Date Written:
:'         FilePro            5.0:'************************  UPDATE 
***************************:
:'DATE          BY          DESCRIPTION  (newest on top):':
:'12/23/08   PMR        modified the process to post the 
time:'                      and the user to ccacrhist:
:'12/01/08   DIB     added additional check for correct record 
on:'                   customer file lookup:
:'11/26/08     PMR         modified the process to do an 
error:'                       check when writing a new record ccacrhist:
:'11/04/08      PMR        added a new field to ccacrhist 
called:'                         posted date:
:':'**************************************************************:
:'              ACCOUNTS RECEIVABLE UPDATING::
:' this process updates all files pretaining to accounts receivable:' 
as a batch process:
:   'switch, 2nd switch:sw(1,.0,G); or(1,yesno):
:        'store amount into how much:hm = 6:
:                        ' read customer file indexed on account 
#:lookup cusn = ccrmcus  k=1    i=a -nx:
:not cusn            ' if account number not found goto field 
one:Beep; show "@\r Invalid Customer Number Input \r"; goto end:
:cusn(1) ne 1        ' if account number not found goto field 
one:Beep; show "@\r Invalid Customer Number Input \r"; goto end:
:5 gt "4" and 5 ne "9" and 5 ne "11"   'correct & trans = credit:gosub Credit:
:5 lt "5" or 5 eq "9" or 5 eq "11"  'correct & trans = 0-4 or 9 or 
11:gosub Debit:
END::end:
Credit:cusn(24) le hm                  ' 120 day balance <= 
payment:hm = hm - cusn(24) ; cusn(24) = "0":
:cusn(24) gt hm                  ' 120 day balance > payment:cusn(24) 
=  cusn(24) - hm; hm= "0":
:cusn(23) le hm                  ' 90 day balance <= payment:hm= hm- 
cusn(23) ; cusn(23) = "0":
:cusn(23) gt hm                  ' 90 day balance > payment:cusn(23) 
= cusn(23) - hm; hm= "0":
:cusn(22) le hm                  ' 60 day balance <= payment:hm= hm- 
cusn(22) ; cusn(22) = "0":
:cusn(22) gt hm                  ' 60 day balance > payment:cusn(22) 
= cusn(22) - hm; hm= "0":
:cusn(21) le hm                  ' 30 day balance <= payment:hm= hm- 
cusn(21) ; cusn(21) = "0":
:cusn(21) gt hm                  ' 30 day balance > payment:cusn(21) 
= cusn(21) - hm; hm= "0":
:cusn(20) le hm                  'age prior month <= payment:hm= hm- 
cusn(20); cusn(20)="0":
:cusn(20) gt hm                  'age prior month > payment:cusn(20) 
= cusn(20) -hm; hm="0":
:cusn(25) le hm                  ' current month charge <= payment:hm 
= hm - cusn(25); cusn(25) = "0":
:cusn(25) gt hm                  'current month charges > 
payment:cusn(25) = cusn(25) - hm; hm = "0":
:cusn(46) le hm                  'xmas dated balance <= payment:hm= 
hm- cusn(46); cusn(46) = "0":
:cusn(46) gt hm                  'xmas dated balance > 
payment:cusn(46) = cusn(46) -hm;hm = "0":
:cusn(26) le hm                 'spring dated balance <= payment:hm= 
hm -cusn(26); cusn(26) = "0":
:cusn(26) gt hm                 'spring dated balance > 
payment:cusn(26) = cusn(26) -hm; hm = "0":
:                        ' subtract amount from current 
balance:cusn(18) = cusn(18) - 6:
:'check if trans date > last trans date and tran-code is 
payment:'change last tran date and add amount to paid YTD:
:2 ge cusn(29) and 5 eq "5":cusn(29) = 2; cusn(28) = cusn(28) + 6:
:5 gt "5" and 5 ne "9" and 5 ne "11"      'credit 
transaction:cusn(30) = cusn(30) - 6:
::write cusn; gosub addpost; return:
Debit:5 eq "0" OR 5 eq "3" OR 5 eq "4" OR 5 eq "9":cusn(30) = 
cusn(30) + 6             'if shipment add to ship YTD:
:(5 eq "0" OR 5 eq "3" OR 5 eq "4" OR 5 eq "9") AND 2 ge 
cusn(31):cusn(31) = 2                  'if shipment update last ship date:
:5 eq "4"                       'xmas shipment add to xmas 
balance:cusn(46) = cusn(46) + 6:
:5 eq "9"                         'sprng ship add to sprng 
balance:cusn(26) = cusn(26) + 6:
:(5 lt "4" or 5 eq "11") and cusn(18) gt "0"    'if not xmas or 
sprint & balance >:cusn(25) = cusn(25) + 6             '0 add to this 
months charges:
:cusn(18) le "0" and (5 lt "4" or 5 eq "11")  'if not xmas or sprint 
& balance <:cusn(25) = cusn(18) + 6           'set current month charges:
:cusn(25) le "0":cusn(25) = "0":
:                        ' add amount to current balance:cusn(18) = 
cusn(18) + 6:
::write cusn; gosub addpost; return:
ADDPOST:                'open post file for updating:lookup post = 
ccacrpost  r=free  -n:
:not post:show "@ERROR writing to CCACRPOST from CCACRTRAN-CCCACR"; exit:
:post                    ' load 
fields:post(1)=1;post(2)=2;post(3)=3;post(4)=4;post(5)=5;post(6)=6:
:post                    ' get comment  &  load fields:gosub COMMENT; 
post(7)=cm;post(8)="";post(9)="";post(10)=cusn(2):
:post                    ' load 
fields:post(11)=cusn(3);post(12)=cusn(4) < cusn(5);post(13)=cusn(6):
:post                    ' load 
fields:post(14)=cusn(7);post(15)=cusn(41);post(16)=cusn(27):
:post                    ' load 
fields:post(17)=cusn(18);post(18)=cusn(21);post(19)=cusn(22):
:post                    ' load 
fields:post(20)=cusn(23)+cusn(24);post(21)=cusn(33);post(22)="1.75":
:cusn(32) = "9":post(22) = "0"                                'no 
finance charges:
:cusn(32) = "8":post(22) = ".83"                            'Ark. 
finance charges:
:post                    ' write post file:write post:
::gosub posthis; return:
COMMENT:' CHECK TRANSACTION CODES AND APPLY APPROPRIATE LABELS::
:5="0":cm = "SHIPMENT":
:5 eq "0" and 3 eq "LS":cm = "FINAL BILL":
:5 = "1":cm = "CREDIT ERR":
:5 = "2":cm = "DEBIT MEMO":
:5 = "3":cm = "SHIP COD":
:5 eq "3" and 3 eq "LS":cm = "PARTL BILL":
:5 = "4":cm = "XMAS SHIP":
:5 = "5":cm = "PAYMENT":
:5 = "6":cm = "DEBIT ERR":
:5 = "7":cm = "CREDIT MEM":
:5 = "8":cm = "VOID":
:5 = "9":cm = "SPRNG SHIP":
:5 = "10":cm = "HAND FEE":
:5 = "11":cm = "FINANCE CH":
:5 eq "12":cm = "DAMAGE RET":
:5 eq "13":cm = "SHORTAGE":
:5 eq "14":cm = "PRICE ERR":
:5 eq "15":cm = "GOODS RETN":
:5 eq "18":cm = "ROUTING ER":
:5 eq "19":cm = "PROMOTIONAL":
:5 eq "20":cm = "PROMO ALLW":
:5 eq "21":cm = "EDI INVOIC":
:5 eq "22":cm = "EDI ANS":
:5 eq "23":cm = "PACK ERROR":
:5 eq "24":cm = "CRED CARD":
:5 eq "25":cm = "UCC CHARGE":
::RETURN:
posthis::lookup his = ccacrhist  r=free  -n:
:not his:show "@ERROR writing to CCACRHIST from CCACRTRAN-CCCACR"; exit:
:his:his(1)=1; his(2)=2; his(3)=3; his(4)=4; his(5)=5; his(6)=6:
:his               ' posted date:his(7)=7; his(8)=8; his(9)=@td; 
his(10)=@tm; his(11)=@id:
:his:write his:
::return:

Don
>
>
>
>-----Original Message-----
>From: filepro-list-bounces+mtcomins=aperion.com at lists.celestial.com
>[mailto:filepro-list-bounces+mtcomins=aperion.com at lists.celestial.com] On
>Behalf Of Don Bush
>Sent: Tuesday, May 26, 2009 6:46 PM
>To: filePro List
>Subject: A/R problem phantom transactions
>
>
>I need a little help. So I thought I would start here.  Our A/R is
>written in filePro as is most everything except A/P and G/L.
>My problem is this;
>we have found transactions (manually keyed in) that are on the daily
>transaction reports but didn't show up on the customer
>statement. Then sometimes the transaction would show up a month later
>and on a couple ocassions two months later.
>Now here is the weird part.  There is no account that is out of
>balance and the A/R matches the G/L to the penny.
>Most all are payments and we have four  from Sept. 2008  that have
>never shown up on a statement and it is still in balance with
>the G/L . the following files are involved:
>                 ccrmcus -   the customer file
>                 ccacrpost -  this statement post file
>                 ccacrtran -   the common transaction file for records.
>                 ccdeposit -   the daily transaction file for entering
>customer payments
>                 ccacrhist -   the history file of all A/R transactions.
>                 buracr     -   the daily transaction file for invoices and
>credits
>
>here is how they work.
>1.someone in AR enters payments into ccdeposit and run a print of all
>the deposits.
>2. that person then processes the payments which are copied to
>ccacrtran which then updates the customer file and writes a record of
>the transaction
>      to ccacrpost for statements at the end of month as well as
>writting a record to ccacrhist for audit trail history. it then
>deletes the record from ccdeposit
>3.  after the updates the ccacrtran records are deleted for the next
>round of transactions.
>4. At the end of the day the buracr file is processed into ccacrtran
>to do the same updates to the other files. once the records are
>written to ccacrtran and processed
>      they are copied to a save file then deleted.
>5.  once the updates for the buracr records are done the ccacrtran
>records are again deleted.
>
>I have spent MONTHS looking over every single transaction deposit and
>transaction report and have found records that appear to have
>processed and some not.
>All of which I have dumped to multiple spreadsheets.
>
>Without rambling anymore the question is : How can a record process
>or not and still keep in balance with the G/L .  Figures for the G/L
>are all gathered
>independently of  the filePro system.
>
>Any help or ideas would be appreciated. Right now I am at a loss and
>am going home for the day.
>
>Don
>
>
>
>
>
>Hi Don,
>
>Have you examined the system maintained fields (Unix Only) of these records?
>There are fields that tell you the date the record was created and who
>created them.  That might give you some insight as to a pattern.  To see
>those fields add the following to a screen
>
>Create Date !@cd
>Created By: !@cb
>Update Date: !@ud
>Updated By:  !@ub
>
>
>I would then look at the processing tables and selection tables to see how
>the transactions are processed.  If these are missed, there has to be a
>reason.  Do you use an index to select records, a -v processing table, a
>selection table, etc?  Maybe the process is crashing?
>
>I have created log databases to track when users are running menu options.
>It logs the option, date, time, who did it and the order it was done in.
>That has proved valuable in tracking if the users are following directions.
>Example - run statements, process transactions - on most system your
>statement would not have the most recent data in the statement.
>
>
>What version of filePro?  On what OS?  Have you tried rebuilding freechains
>and indexes? Certain versions of filePro had problems with indexes.  Older
>versions of Windows filePro will not run well on new operating systems.
>
>Finally, I like to track changes to problem databases.  I use an array to
>capture the beginning values of the record and compare to an end array when
>esc is hit to generate a change database.  This will tell me the old value
>and the new value of any field changed in a record.
>
>Mark
>mtcomins at aperion.com
>
>
>
>
>
>
>
>_______________________________________________
>Filepro-list mailing list
>Filepro-list at lists.celestial.com
>http://mailman.celestial.com/mailman/listinfo/filepro-list



More information about the Filepro-list mailing list