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