Received-Not-Paid Accrual Writer

Bill Kossmann, CGA
David Thompson Regional Health Authority
PO Box 5030
Red Deer, Alberta
T4N 6R2
voice: (403) 343 4463
fax: (403) 343 4697

Table of Contents

Top of Form


What's this?

The code in this document is "ObjectPAL", the proprietary programming language native to Paradox for Windows (PW), which is a PC-based relational database program.

The purpose of this PW application is to create a batch journal entry file based upon the records in a report file.

Top of Form

Background information

Prior to this program's implementation, the Received-Not-Paid (RNP) report--usually 500-600 pages long--would have been split into four parts: one part for each of the Accounts Payable (A/P) clerks. Each of the clerks would review their section of the RNP report on a line-by-line basis and then record the accrued A/P entries on journal entry forms. The journal entry forms would then be assembled into journal vouchers and forwarded to the data entry clerk for keying. After keying, a report was run that listed all of the resulting unposted journal entries. This report was reviewed for keying accuracy on a spot-check basis.

As you can imagine, this process was extremely time consuming; it took the four A/P clerks almost two days each to complete the process. In addition, the process invited the introduction of errors at every step. To complicate matters even further, the A/P clerks needed to follow a set of business rules when creating the journal entries.

Using this routine, however, the clerks take less than half a day to review the exception reports and record any payables. The A/P entries that this application generates are written to an interface file, which is uploaded to the DTRHA's general ledger host and executed as a batch job.

Top of Form

Site-specific glossary

Because this program was developed in-house at the DTRHA, a description of some site-specific concepts is necessary:

or Accounting Control Key, represents our general ledger code structure. We have an eight-digit cost centre code and a seven-digit expense code (together they make up the ACK).
is one of the terminal emulator packages that allow our PCs to communicate with our Data General (DG) minicomputers.
is an acronym for our general ledger package "Comprehensive Financial Operations". Actually a Walker Interactive product, it was ported to a DG platform and resold by Data General as CFO.
is the David Thompson Regional Health Authority. The DTRHA is the administrative body responsible for health care in central Alberta, Canada.
is the host name of one of our DG minis, and the host for CFO.
is a month-end report generated by our inventory system. It lists all the items physically received by the DTRHA but not yet paid for. Since these items represent financial liabilities, they are recorded as accounts payable at the end of the month.

Top of Form

User procedures

After A/P cutoff, run the RNP report in vendor order as you normally would. Download the resulting data file like this:

  1. Sign onto FIS using CEO-Connection.
  2. Do a Ctrl Break to get to the CEO-Connection menu.
  3. Select #5, "Retrieve a file from host", and enter the following:
    On the second line, enter the following:
    Notice that we need to specify "txt" instead of "dat" in the second line!
  4. When the file has been downloaded, "Become a terminal on host" (option #3).

Now it's time to do the processing with Paradox. Minimize CEO-Connection, launch Paradox for Windows, and make m:\pdata\rnp your working directory. Open the form RNP.FSL and push the Begin button. You will be prompted as follows:

  1. Create monthend accruals from the RNP file now? Click on either the "Yes" or the "No" button.
  2. Accrual is for: What month are the accruals for? Click on a month from the displayed list.
  3. Generate entries for (whatever month)? Click on either the "Yes" or the "No" button. This is the final confirmation before the processing begins.

During the course of the processing, two reports will be printed: (1) ackDel.rsl, which lists all items deleted because their ACKs meet the specifications in table ackDel.db; and (2) dateDel.rsl, which lists all items deleted because the "received" month is either greater or lesser than the specified month. The latter report will have to be reviewed by the A/P clerks for additional payables. In any event, both reports will print on the Finance laser.

When the processing is finished, you will get a message telling you what the net amount of the entries is (i.e., without the offset). Add this number to the totals at the bottom of the other two reports, and you should wind up with the total amount of the EDS-generated RNP report. If you don't, then notify Bill Kossmann as soon as possible.

All that remains is to get the journal entries into CFO. To do this, perform the following steps:

  1. Exit Paradox and re-enter CEO-Connection.
  2. Do a Ctrl Break to get to the CEO-Connection menu.
  3. Select #4, "Send a file to host", and enter the following:
    On the second line, enter the following:
  4. When the file has been uploaded, "Become a terminal on host" (option #3).
  5. Inform the accountants in charge of the nightly processing that "the interface file :cfo3:user:interfaces:c030.cfo is ready to be run." One of them will either run the job over the noon hour or set it up as part of the evening's processing. In any event, you will receive the Unposted Report as soon as the entries have been interfaced.
  6. Once you've reviewed the Unposted Report and have been satisfied that the entries are correct, submit the group for posting.

Top of Form

The pushButton code

The program will be controlled from this pushButton method. As soon as the "Begin" button is clicked, the variables are declared and the user will be asked to confirm his or her intentions. If the user wishes to continue, a popUpMenu will be built and the user will be presented with a list of months to process. After the month is chosen, the monthend date is calculated and the the user will be asked to confirm the choice. From there, we will call the other methods that do the actual processing.

Let's build the pushButton code as the file "button.txt."; it is imported to PW's IDE using the "Edit | Paste From" menu item. Indeed, all of our code will be imported to PW in this manner (simply create a new method and paste the corresponding text file into it). Here's a work-saving tip: establish an alias ":litProg:" that points to the directory holding the text files. When you want to import them, simply click on the ":litProg:" alias and then select the files you need (it's simpler than typing the path name or using the browser).

<standard comment>
method pushButton (var eventInfo event)
   <declare pushButton variables>
   <confirm start of processing>
   <build and show a popUpMenu>
   <calculate the date>
   <confirm the month selection>
   <call the other methods>
   <tell the user we're done>

Top of Form

Standard comment and error

This is the "standard" inline comment which is placed at the beginning of each method. If this code needs to be modified, we want the programmer to be able to find the documentation for it. More importantly, we want the documentation to match the code!

<standard comment>= (U-> U-> U-> U-> U->)
;// RNP   Written by Bill Kossmann 96/12/22
;// Here's how to access the program documentation:
;//     1) use a Web browser to open file "m:\pdata\noweb\rnp.htm", or,     
;//     2) look in the red binder labelled "Paradox for Windows:  Source
;//        code documentation" (it's located in Bill Kossmann's office).
;// If you make changes to the code, the noweb source is located in file
;// "m:\pdata\noweb\rnp.nw".  Remember, bad things will happen to you if 
;// you change the code but you don't change the documentation!

While we're at it, we might as well define what we're going to do when we encounter an error. First, we'll beep at the user, display the error using PW's standard error dialog box, and clear the status bar. Then, we'll set lErrors to "True" so that subsequent methods cannot start (as you'll see in subsequent methods, we check the value of lErrors when a method starts). Finally, we'll hide the text object progressTextObject and return to the form.

<do error processing>= (U-> U-> U-> U-> U-> U-> U-> U-> U-> U-> U-> U-> U-> U-> U->)
lErrors = True
progressTextObject.visible = False

Top of Form

Local and Global pushButton variables

Okay, now we can continue with the pushButton code by declaring the local pushButton variables. Variable dt will be used when we test the current year to see if it's a leap year; pMenu is a place holder for our popUpMenu.

<declare pushButton variables>= (U->)
   dt                           dateTime
   pMenu                        popUpMenu

Defines dt, pMenu (links are to index).

Our global variables need to be declared as well. lErrors is a logical variable whose value is set to "False" when we begin processing. As long as we have no errors, its value won't change. However, if we encounter an error, we'll change its value to "True". Since all methods test the value of lErrors before they start processing, this provides us with a means to gracefully shut down the program.

The other string variables (with one exception) are used in date calculations. We'll use them to calculate the month the user wants to process, and we'll also need them when we start writing the journal entries. The string variable sTotal is a reconciling total that we calculate later.

   lErrors                      logical
   sLastDay                     string
   sMonth                       string
   sTotal                       string
   sYearMonth                   string

Defines lErrors, sLastDay, sMonth, sTotal, sYearMonth (links are to index).

Top of Form

Confirm start of processing

This code will appear right after the pushButton variables are declared, so we'll set lErrors to "False". Then we'll ask the users if they want to begin processing. If they do not click on the "Yes" button then display a "Cancelled by ..." message and return to the form.

<confirm start of processing>= (U->)
lErrors = False
if msgQuestion("Confirm", "Create monthend accruals from 
    the RNP file now?") <> "Yes"
   then msgInfo("Status", "Cancelled by user command.")

Top of Form

Build and show a popUpMenu

We'll need to ask the user what month we're running. By presenting a popUpMenu of choices and then calculating the date variables, we can avoid having the user key in an invalid date.

Note that we specifically empty the pMenu even before it's built. It's a good idea to do this, even if you can't find this suggestion in the ObjectPAL manuals. The reason is, if your process fails, then under some situations the menu remains in memory. When you press the button again without closing the form, you'll wind up with all the menu items displayed twice. The menu will still work, but it's as ugly as sin and really confuses the user.

After the menu is built, "show()" it to the user and write the user's choice to variable sMonth.

<build and show a popUpMenu>= (U->)

pMenu.addStaticText("Accrual is for:")

sMonth =

Top of Form

Calculate the date

Here we'll calculate the monthend date from the month selected by the user. The year will always be the current calendar year unless the user chooses "December". In that case, the year will be the prior calendar year. Note this process has to be run in the month immediately following the accounting month--the inventory system will overwrite our files when the next monthend is run (that's just the way the inventory system works).

We'll use a long switch structure to calculate the last day of the month as well as the year-month combination. All the months except February are easy: the last day is either 30 or 31. We get the year-month combination by calculating the string value of the current year of the PC's clock and prepending it to the month (the year and month are separated by a slash).

The last date in February is a bit different. We have to give PW a date string in dateTime format, so we'll arbitrarily choose 6:00am on the first day of the first month of the current year. We'll then use the isLeapYear function to determine if this is a leap year. If it is, the last day is the 29th; if it's not, then it must be the 28th.

If the user clicks anywhere except a month or if Esc is pressed, a null string is returned and we indicate to the user that it's an invalid choice. If that happens, return to the form.

<calculate the date>= (U->)
   case sMonth = "January"   : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/01"
   case sMonth = "February"  : 
      sYearMonth = strVal(year(today())) + "/02"
      dt = dateTime("06:00:00 am " + "01/01/" + subStr(sYearMonth,3,2))
      if dt.isLeapYear()
         then sLastDay = "29"
         else sLastDay = "28"
   case sMonth = "March"     : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/03"
   case sMonth = "April"     : 
      sLastDay = "30"
      sYearMonth = strVal(year(today())) + "/04"
   case sMonth = "May"       : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/05"
   case sMonth = "June"      : 
      sLastDay = "30"
      sYearMonth = strVal(year(today())) + "/06"
   case sMonth = "July"      : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/07"
   case sMonth = "August"    : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/08"
   case sMonth = "September" : 
      sLastDay = "30"
      sYearMonth = strVal(year(today())) + "/09"
   case sMonth = "October"   : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())) + "/10"
   case sMonth = "November"  : 
      sLastDay = "30"
      sYearMonth = strVal(year(today())) + "/11"
   case sMonth = "December"  : 
      sLastDay = "31"
      sYearMonth = strVal(year(today())-1) + "/12"
      msgStop("Invalid selection", "You must click on a month.")


Top of Form

Confirm the month selection

Show the user the calculated effective year/month combination. If the user doesn't press the "Yes" button, then show the status of the process and return to the form. Notice that we use substrings to present a string like "Generate entries for December 1996?".

<confirm the month selection>= (U->)
if msgQuestion("Confirm", "Generate entries for " + sMonth
      + " " + subStr(sYearMonth,1,4) + "?") <> "Yes"
   then msgInfo("Status", "Process aborted by user command.")

Top of Form

Call the other methods

Now that we have the period variable and we're sure the user wants to proceed, we can continue processing. We have four basic processes: (1) import the RNP report file to PW; (2) delete any cost centres whose codes match the parameters in a PW table; (3) delete any entries whose month does not match the user's selection; and (4) write the accrued A/P journal entries.

We'll discuss these methods in later sections. For now, let's just establish the flow of the program:

<call the other methods>= (U->)
if lErrors = False
   then importRnpFile()

if lErrors = False
   then deleteCostCentres()

if lErrors = False
   then deleteNonCurrent()

if lErrors = False
   then writeAccrualEntries()

Top of Form

Tell the user we're done

If we've made it to this point, then we've succeeded. Let the user know what the reconciling total is (variable sTotal), and what they should do with it (we'll deal with the reconciling total in a later section).

<tell the user we're done>= (U->)
if lErrors = False
   then progressTextObject.visible = False
        msgInfo("Status", "The process has finished successfully.  The
        net amount of the journal entries--without the offset--is $" +
        sTotal + ".  Add this amount to the other two reports, and you
        should arrive at the total of the printed EDS RNP report.")

Top of Form

Import the RNP file

This section of the program is responsible for importing the RNP file to Paradox. Because Paradox's fixed length import module has proven to be unreliable, we'll use the textStream method. Note that the following textStream technique was adapted from Bill Todd's article "Import/Export ObjectPAL" in the February 1995 issue of the Paradox Informant.

To build the method, we'll include our "standard" commment and declare our variables. Then we'll empty the table, read the RNP report, and then repopulate the RNP table. To get rid of the items that were returned upon receipt, we'll summarize the table. Here we go:

<standard comment>
method importRnpFile()
   <declare the importRnpFile() variables>
   <empty the RNP table>
   <access the RNP report file>
   <read the report>
   <summarize the table>

Top of Form

Declare the importRnpFile() variables

All the variables used in this method are declared here. Variables iCounter, sAmount, sArray, and sQuantity are used to strip commas from the amount fields (more about that later); liFirstChar and liLastChar are used to read the report; and qVarImport is used to summarize the RNP table. Variable sLine holds one report record (without the record terminators); tcImport opens the RNP detail table; and tsImport is used to access the report file itself.

After we declare the variable types, we'll initialize liFirstChar as the first character position in the string to search for, and initialize liLastChar as the last character position to search for. We'll set them to "1" because we're searching for the one character that tells we're at the end of a record: a carriage return character.

<declare the importRnpFile() variables>= (<-U)
   iCounter                     smallInt
   liFirstChar                  longInt
   liLastChar                   longInt
   qVarImport                   query
   sAmount                      string
   sArray       array[]         anyType
   sLine                        string
   sQuantity                    string
   tcImport                     tCursor
   tsImport                     textStream

liFirstChar = 1
liLastChar  = 1

Defines iCounter, liFirstChar, liLastChar, qVarImport, sAmount, sArray, sLine, sQuantity, tcImport, tsImport (links are to index).

Top of Form

Empty the RNP table

Now we need to empty the table that will hold the RNP report data. To do this, we'll try to open the tCursor tcImport. If we can't open it then we'll execute our error processing code. However, if we are successful in opening the tCursor, then tell the user what we're up to, go into edit mode and empty the table.

I've set up the object progressTextObject as an unbound field on the form; its purpose is to display our messages to the user. We could have used the status bar, but we can't set the properties of any text displayed there (messages appearing in our text object will be nicely formatted).

<empty the RNP table>= (<-U)
if not"rnp.db")
   then <do error processing>
   else progressTextObject.value = "Deleting old data ..."
        progressTextObject.visible = True

Top of Form

Access the RNP report file

Try to gain "read" access to the RNP report file. If we can't, then execute the standard "error" code that we defined in the pushButton section.

<access the RNP report file>= (<-U)
if not"rnp.txt", "r")
   then <do error processing>

Top of Form

Read the report

Now we can look at the report character by character (hey, I did say it was slower than PW's routine!). If we find a carriage return character (chr(13)), read the record, get the information in it, calculate the amount and quantity, and try to post the record. If it's not a carriage return, read the next character.

We'll change the value of the text object so that the user knows what we're up to. All we need to do is convert the current record number to a string and include it in the message. When we're finished, we'll close the tCursor and the textStream.

<read the report>= (<-U)
while true
   liFirstChar = liLastChar
   if tsImport.advMatch(liFirstChar,liLastChar,chr(13)) 
      then <read the record>
           <get vendor, ACK, and invoice information>
           <get rid of the comma in Amount>
           <is Amount a debit or credit>
           <convert quantity string to integer>
           <try to post the record>
      else quitloop
   progressTextObject.value = "Successfully imported record number " 
                      + strVal(tcImport.nRecords())

When we finally locate the carriage return character, we'll set the textStream position to the first character of that line and read the entire line into variable sLine. Since we know the record is 157 characters long (including the <CR> and <LF> characters), we'll just read the first 155 characters of the record.

<read the record>= (<-U)
      then tsImport.setPosition(liFirstChar - 155)

Top of Form

Get the vendor, ACK and invoice information

By reading sLine as a bunch of substrings, we can insert information into the table as required by inserting a new record after the current tCursor position. We'll write the vendor, ACK and invoice information to the blank record by using dot notation with the tCursor.

Oh, yes, I almost forgot: the secondary code in the Inventory system is only 5 characters long. We need to append two zeros to make it 7 characters long, otherwise CFO will crap out on us.

<get vendor, ACK, and invoice information>= (<-U)
           tcImport."Facility"  = strVal(subStr(sLine,77,1)) 
           tcImport."Primary"   = strVal(subStr(sLine,78,2))
                                + strVal(subStr(sLine,81,5))
           tcImport."Secondary" = strVal(subStr(sLine,87,5)) + "00"
           tcImport."Vendor"    = strVal(subStr(sLine,126,30))
           tcImport."PO"        = strVal(subStr(sLine,1,5))
           tcImport."Stock Nbr" = strVal(subStr(sLine,22,10))
           tcImport."Item Desc" = strVal(subStr(sLine,31,30))
           tcImport."Date Recd" = strVal(subStr(sLine,102,8))

Top of Form

Convert the amount string to currency type

Because the amount string may contain a comma, we cannot directly convert the amount to a currency field type. We'll cheat our way around it ... let's breakApart the string using the comma as a field delimiter. The result will be written to the array sArray, and we can then empty sAmount by setting it to null. Then, we'll iterate through sArray to reconstruct the amount without the comma. The final result will be rewritten to sAmount.

<get rid of the comma in Amount>= (<-U)
           sAmount = strVal(subStr(sLine,113,11))
           sAmount = ""
           for iCounter from 1 to sArray.size()
               sAmount = sAmount + strVal(sArray[iCounter])

Top of Form

Is the amount a debit or credit?

Now we have to check the last character of the "Amount" field to see if it's a positive (a blank) or a negative (a "-"). If there is a trailing negative character in position #124, then it's a credit. Convert the string to currency type, subtract it from zero, and place the result in the "Amount" field of the tCursor. Otherwise, just convert it to currency type and place the amount in the "Amount" field of the tCursor.

<is Amount a debit or credit>= (<-U)
           if strVal(subStr(sLine,124,1)) = "-"
              then tcImport."Amount" = 0 - currency(sAmount)
              else tcImport."Amount" = currency(sAmount)

Top of Form

Convert the quantity string to an integer

We need to do the same for "Qty Recd" as we did for "Amount". The only thing different is the location of the variable within the string, and the type of variable we want to write (we'll establish quantity as an integer).

<convert quantity string to integer>= (<-U)
           sQuantity  = strVal(subStr(sLine,93,5))
           sQuantity = ""
           for iCounter from 1 to sArray.size()
               sQuantity = sQuantity + strVal(sArray[iCounter])
           if strVal(subStr(sLine,96,2)) = " -"
              then tcImport."Qty Recd" = 0 
              else if strVal(subStr(sLine,97,1)) = "-"
                      then tcImport."Qty Recd" = 0 - 
                      else tcImport."Qty Recd" = smallInt(sQuantity)

Top of Form

Try to post the record

Now that we have all the information we need, let's try a postRecord.

<try to post the record>= (<-U)
           if not tcImport.postRecord()
              then <do error processing>

Top of Form

Summarize the table

One more thing: we may have some returned items (quantity received and amount fields are negative). Therefore, we need to net them out against one another with--you guessed it--a query.

Let's declare the query and try to execute it. If we fail, then execute our error processing steps. Note that the resulting answer table is rnp.db; that's because we want to overwrite the original table with the summarized table.

<summarize the table>= (<-U)
qVarImport = Query
answer: :work:rnp.db

 rnp.db | Facility | Primary | Secondary | Vendor | PO    | Stock Nbr |
        | Check    | Check   | Check     | Check  | Check | Check     |
 rnp.db | Item Desc | Date Recd | Amount             | Qty Recd           |
        | Check     | Check     | calc sum as Amount | calc sum as Amount |


if not qVarImport.executeQBE()
   then <do error processing>


Top of Form

Delete cost centres

In this section, we'll delete all records whose ACK segments correspond to entries in the Paradox table ackDel.db. We'll scan the table and execute a delete query, then run the report ackdel.rsl. Here's how it'll look:

<standard comment>
method deleteCostCentres()
   <declare deleteCostCentres() variables>
   <open the query parameter table>
   <build the query string>
   <execute the deletion query>
   <open the report ackDel.rsl>

Top of Form

Declare the deleteCostCentres() variables

This section has two purposes: (1) declare the variable types; and (2) initialize some of the variables that we'll use. The variables riDelCc and riDelCc are used to open the report and change the header. The sQueryDelCc* variables are used to build the query image, and tDelCc is used to read a PW table to get the query variables.

In order to report on the deleted records, we'll have to build a query string that deletes all of the records at once. Here we'll set up the query string header sQueryDelCc1, initialize the query body sQueryDelCc2 as a null string, and set up the query string footer sQueryDelCc3. When declaring a query in this manner, we'll need to use the "\ n" characters to terminate a query image record.

<declare deleteCostCentres() variables>= (<-U)
   rDelCc                       report
   riDelCc                      reportPrintInfo
   sQueryDelCc                  string
   sQueryDelCc1                 string
   sQueryDelCc2                 string
   sQueryDelCc3                 string
   tcDelCc                      tCursor

sQueryDelCc1 = "Query\n answer: :priv:deleted.db\n\n"
             + "rnp.db | Facility | Primary | Secondary |\n"
sQueryDelCc2 = ""
sQueryDelCc3 = "\nendQuery\n"

Defines rDelCc, riDelCc, sQueryDelCc, sQueryDelCc1, sQueryDelCc2, sQueryDelCc3, tcDelCc (links are to index).

Top of Form

Open the query parameter table

Try to open a tCursor to the query parameter table ackDel.db.

<open the query parameter table>= (<-U)
if not"ackDel.db")
   then <do error processing>

Top of Form

Build the query string

Tell the user what we're up to by writing to progressTextObject. Now let's build the body of the query string by scanning the tCursor. Add one record to sQueryDelCc2 for every record in ackDel.db; when we've finished scanning the table, close the tCursor. Then build the query string sQueryDelCc by concatenating sQueryDelCc1, sQueryDelCc2, and sQueryDelCc3.

<build the query string>= (<-U)
progressTextObject.value = "Deleting specified ACKs"
progressTextObject.visible = True

scan tcDelCc:
   sQueryDelCc2 = sQueryDelCc2 + "DELETE | " + strVal(tcDelCc."Facility")
                + " | " + strVal(tcDelCc."Primary")
                + " | " + strVal(tcDelCc."Secondary") + " |\n"

sQueryDelCc = sQueryDelCc1 + sQueryDelCc2 + sQueryDelCc3

Top of Form

Execute the deletion query

Execute the query. This'll delete all of the records based upon the ACK segment values in table ackDel.db. Remember, if the A/P clerks decide to change the deletion parameters, no problem! Simply edit the records in the parameter table. You don't have to touch this code!

<execute the deletion query>= (<-U)
if not executeQbeString(sQueryDelCc)
   then <do error processing>

Top of Form

Open the report ackDel.rsl

Now let's open the report. We'll let the user know what's going on by writing to the text object. Then, to give the appearance of snappy updates, we'll delay the screen update until the report starts to print. After that, we can go onto the next method.

<open the report ackDel.rsl>= (<-U U->)
progressTextObject.value = "Printing report ackDel.RSL"
if not"ackdel.rsl") 
   then <do error processing>
   else <run the report ackDel.rsl>

Top of Form

Run the report ackDel.rsl

Here's where we define what's going to happen with the report. The ri.. variables define the printing orientation and the report name. Once we've got the report open, we'll go into design mode and change the header to read something like "December 31, 1996". Then we can save, print, and close the report.

<run the report ackDel.rsl>= (<-U U->)
riDelCc.orient = printLandscape = "ackdel.rsl"
rDelCc.period.value = sMonth + " " + sLastDay + ", " + subStr(sYearMonth,1,4)


Top of Form

Delete non-current records

In this section, we'll delete all records whose dates fall outside the beginning and ending date range for the month in question. The flow of the program will be like this:

<standard comment>
method deleteNonCurrent()
   <declare deleteNonCurrent() variables>
   <calculate the period begin and end dates>
   <delete the non current records>
   <sort the deleted.db table>
   <open the report ackDel.rsl>

Top of Form

Declare deleteNonCurrent() variables

Nothing out of the ordinary here ... qVarDelDate is our query image; rDelDate and riDelDate are used to open the report; the s..Date variables are used to calculate the beginning and ending dates of the period, and tVarDelDate is used to sort a table.

<declare deleteNonCurrent() variables>= (<-U)
   qVarDelDate                  query
   rDelDate                     report
   riDelDate                    reportPrintInfo
   sBeginDate                   string
   sEndDate                     string
   tVarDelDate                  table

Defines qVarDelDate, rDelDate, riDelDate, sBeginDate, sEndDate, tVarDelDate (links are to index).

Top of Form

Calculate the period begin and end dates

Let's tell the user what we're up to by writing to the text object again. Then we can calculate the period beginning and ending dates (note that they weren't calculated in the pushButton method because that'd mean we'd have to declare two more global variables).

<calculate the period begin and end dates>= (<-U)
progressTextObject.value = "Deleting non-current records"
sBeginDate = sYearMonth + "/01"
sEndDate   = sYearMonth + "/" + sLastDay

Top of Form

Delete the non-current records

Now set up the query image and try to execute it. Any record whose date received is less than the beginning of the month or greater than the end of the month will be deleted.

<delete the non current records>= (<-U)
qVarDelDate = Query

 rnp.db | Date Recd                    |
 DELETE | < ~sBeginDate or > ~sEndDate |


if not qVarDelDate.executeQBE()
   then <do error processing>

Top of Form

Sort the deleted.db table

To make things easier for the A/P clerks, let's sort deleted.db by Vendor, Date Recd and PO. What'll happen is, the report will be split by A/P clerk. For example, Clerk #1 will get A--C, Clerk #2 will get D--H, etc. If the report is sorted by vendor, this will make report distribution a snap. The clerks would also like the entries sorted by Date Recd and PO. This makes it a lot easier for them to look down the paper trail.

<sort the deleted.db table>= (<-U)
progressTextObject.value = "Printing report DATEDEL.RSL"

if not tVarDelDate.attach(":priv:deleted.db")
   then <do error processing>
   else sort tVarDelDate
          on "Vendor", "Date Recd", "PO"
          to ":priv:deleted.db"

Top of Form

Open the report dateDel.rsl

As with ackDel.rsl, we want to print in landscape, and we want to delay the screen updates to make it seem that the program is faster than it actually is.

<open the report dateDel.rsl>=
riDelDate.orient = printLandscape = "datedel.rsl"


if not 
   then <do error processing>
   else <run the report ackDel.rsl>



Top of Form

Run the report dateDel.rsl

Again, no big change from ackDel.rsl. We go into design mode, change the report header and save the report (then it's printed and closed).

<run the report dateDel.rsl>=
rDelDate.period.value = sMonth + " " 
   + sLastDay + ", " + subStr(sYearMonth,1,4)


Top of Form

Write the accrual entries

This section is responsible for the creation of the interface file "C030.CFO". What we'll do is include our standard comment and variable declarations as in the previous sections. Then, we'll create the group header record, create the detail records, and create the offsetting entry to Accounts Payable.

<standard comment>
method writeAccrualEntries()
   <declare writeAccrualEntries() variables>
   <create the group header record>
   <create the detail records>
   <create the offset record>

Top of Form

Declare the writeAccrualEntries() variables

The variable nJeAmt is the dollar amount of the record in the RNP report file. nOffset is a running total of the net amounts of all the nJeAmt entries; by subtracting the amounts from the running total, we wind up with an offsetting amount that we can write to the trade accounts payable code. sDesc is a concatenation of the vendor's name, the PO number, and the stock number (if it's available). tcWrite gives us access to the detail table so we can scan it, and tsWrite allows us to write to the interface file. The remaining string variables are all used to convert the amount in the table (a currency type) to a string with an overpunch character.

<declare writeAccrualEntries() variables>= (<-U)
   nJeAmt                       number
   nOffset                      number
   sDesc                        string
   tcWrite                      tCursor
   tsWrite                      textStream
   sAmount                      string
   sSign                        string
   sVal                         string
   sValue                       string

nOffset = 0

Defines nJeAmt, nOffset, sAmount, sDesc, sSign, sVal, sValue, tcWrite, tsWrite (links are to index).

Top of Form

Create the group header record

In order for the journal entries to be written to CFO, they must all belong to a journal entry group. What we'll do is try to create a file "C030.CFO". If it already exists, then we want to overwrite it. Once we've created the file, we want to write the group header record and commit the data from the memory buffer. The reason is, if PW fails then we can take a look at the last record to be successfully written and deduce the location of the error.

<create the group header record>= (<-U)
if not tsWrite.create("C030.CFO")
   then <do error processing>
   else <write the group header record>
        try tsWrite.commit()
          onFail <do error processing>

Here's where we define the group header. If you really want to see how the record is laid out, look at the manual "CFO Transaction Manual", pages 350--352, 357, and 378 (the manual is in Bill Kossmann's office). I won't go into the gory details, but suffice it to say that each record must be 80 characters long, and the correct positioning of the information within the record is critical.

Note that some of this stuff is hard-wired, such as the signon record, the transaction and sequence number, and the group number. Our variables sYearMonth and sLastDay come from the pushButton method (they're global variables).

<write the group header record>= (<-U)
tsWrite.writeString("120  01 A   C030      ",subStr(sYearMonth,6,2),

Top of Form

Create the detail records

Open the table rnp.db, then iterate (scan) through the table and look for any record whose amount is not equal to zero. For each of these records, get the required JE information from the table and calculate an overpunch character. Format the amount variable, then write the journal entry.

When we're all finished with the table, close it--but not before getting the total amount of the "Amount" field and holding it in sTotal. This will be presented to the user when we're finished, in order for the user to reconcile this process to the figures in the Inventory system reports.

<create the detail records>= (<-U)
if not"rnp.db")
   then <do error processing>

scan tcWrite for tcWrite."Amount" <> 0 :
   <get journal entry information>
   if nJeAmt > 0
      then <calculate a debit overpunch>
      else <calculate a credit overpunch>
   <format the journal entry>
   <write the journal entry>

sTotal = strVal(currency(tcWrite.cSum("Amount")))

Here's where we define the detail record. The record layout is in the manual "CFO Transaction Manual", pages 350--352, 357, and 378 (the manual is in Bill Kossmann's office). Note that the variable sAmount is 16 characters long. It's a concatenation of the amount (with leading zeros) and the overpunch character.

Again, note that some of this stuff is hard-wired. That's okay because the batch record definition will never change. Our variables include the global ones we used in the header record, as well as some detail information such as ACK and description.

Because we don't want the user to get bored and reboot the machine, we need to display the number of the journal entry that we're working on by writing to progressTextObject.

<format the journal entry>= (<-U)
   sAmount = subStr(sValue,2,15) + sSign
   tsWrite.writeString("122  01 A   C030      ",subStr(sYearMonth,6,2),
      space(35),"Y ")
   tsWrite.writeString("122  02 1",space(19),sAmount,space(36))
   tsWrite.writeString("122  03",space(12),sDesc,space(10))
   progressTextObject.value = "Writing journal entry " 
                      + strVal(tcWrite.recNo()) + " of " 
                      + strVal(tcWrite.nRecords())

Try to commit every line so that if Paradox chokes, we can isolate the offending record by looking at what it did manage to write.

<write the journal entry>= (<-U)
   try tsWrite.commit()
     onFail <do error processing>

Here's where we begin calculating the amount with the overpunch character. If you really want to see how the record is laid out, look at the manual "CFO Transaction Manual", pages 350--352, 357, and 380 (the manual is in Bill Kossmann's office).

First, we'll assign the value of the Amount field to nJeAmt, and calculate the offset amount. Then, we'll create the description, which is a field 51 characters long, left-aligned (AL) and upper-case (CU), with "|" characters acting as separators.

Next we'll format nJeAMt sot that it's a 17-character variable with leading zeros. We want it in pennies (i.e., drop the decimal), so we'll multiply the rounded amount by 100. The last (17th) character of sValue will be used to calculate the overpunch character, so we'll save that information in sVal--it'll be used in a minute.

<get journal entry information>= (<-U)
nJeAmt  = tcWrite."Amount"
nOffset = nOffset - tcWrite."Amount"

sDesc   = format("W51,AL, CU",tcWrite."Vendor" + " | " 
   + tcWrite."PO" + " | " + tcWrite."Stock Nbr")

sValue  = format("W17,EZ,S+",(100*(round(nJeAmt,2))))
sVal    = subStr(sValue,17,1)

The overpunch character is a representation of both the polarity of the number (positive or negative), and the value of the last character. For example, 1234E represents 123.45 (debit), and 4567Q represents 456.78 (credit). We will calculate the overpunch characters with two switch/endSwitch structures (one for debits and one for credits).

Since we know from nJeAmt whether or not the amount is positive or negative, all we have to do is look at the value of the last character and map it to the appropriate overpunch character:

<calculate a debit overpunch>= (<-U U->)
   case  sVal="0" :  sSign="{"
   case  sVal="1" :  sSign="A"
   case  sVal="2" :  sSign="B"
   case  sVal="3" :  sSign="C"
   case  sVal="4" :  sSign="D"
   case  sVal="5" :  sSign="E"
   case  sVal="6" :  sSign="F"
   case  sVal="7" :  sSign="G"
   case  sVal="8" :  sSign="H"
   case  sVal="9" :  sSign="I"

<calculate a credit overpunch>= (<-U U->)
   case  sVal="0" :  sSign="}"
   case  sVal="1" :  sSign="J"
   case  sVal="2" :  sSign="K"
   case  sVal="3" :  sSign="L"
   case  sVal="4" :  sSign="M"
   case  sVal="5" :  sSign="N"
   case  sVal="6" :  sSign="O"
   case  sVal="7" :  sSign="P"
   case  sVal="8" :  sSign="Q"
   case  sVal="9" :  sSign="R"

Top of Form

Create the offset record

Finally, we have to create the offset record. It's the same basic routine as we did for the detail records, except that we don't have to read the detail table. Notice that we can use our previous code for calculating the overpunch characters, just like we recycle the error processing code.

<create the offset record>= (<-U)
<get the offset information>

if nOffset > 0
   then <calculate a debit overpunch>
   else <calculate a credit overpunch>

<format the offset information>
<write the offset information>

try tsWrite.commit()
  onFail <do error processing>


The journal entry description for the offset record will be, for example, "ACCRUAL FOR DECEMBER 1996". The amount of the journal entry is the running total nOffset, so we'll convert it to pennies and grab the last (17th) digit.

<get the offset information>= (<-U)
sDesc  = format("W30,AL, CU","ACCRUAL FOR " + sMonth + " " 
       + subStr(sYearMonth,1,4))
sValue = format("W17,EZ,S+",(100*(round(nOffset,2))))
sVal   = subStr(sValue,17,1)

Again, as with the detail records, we concatenate the 2nd to 15th characters of the amount--remember, the first was a plus sign or a minus sign--with the overpunch character. Then we write the journal entry line, but this time we hard-code the ACK as "141390000000000", which is the Trade Accounts Payable account code.

<format the offset information>= (<-U)
sAmount = subStr(sValue,2,15) + sSign

tsWrite.writeString("122  01 A   C030      ",subStr(sYearMonth,6,2),
   sLastDay,subStr(sYearMonth,3,2),"141390000000000",space(35),"Y ")
tsWrite.writeString("122  02 1",space(19),sAmount,space(36))
tsWrite.writeString("122  03",space(12),sDesc,space(31))

Write the entry to disk from the memory buffer. If it fails, then do the error processing.

<write the offset information>= (<-U)
try tsWrite.commit()
  onFail <do error processing>

Top of Form

Code chunk listing

This section lists all of the code chunks and provides links to the locations in which they are used. The link to a "U" refers to the location of a chunk's use in other chunks. The link to a "D" refers to the location at which it is defined.

Top of Form

Identifier listing

This section lists all of the variables and provides links to the locations in which they are used. The link to a "U" refers to the location of a variable's use. The link to a "D" refers to the location at which it is declared.

Top of Form