Here are its design and scripts for it.  I'm trying to avoid giving clear sketches and code for Enterprises' incoming psX12, and offer these for the Ledger Engine as an example instead.  The Ledger Engine provides syntax & logic for handling inbound psX12-formatted email, editing it for validity, and responding to it.  

The discussion below highlights the important syntax for dealing with MAPI and SQL.  Full details are included in the PB online help.  The logic is structured and I'll try to make it as clear as I can in comments and this text.  Please sit with the scripts and this page for a while and you will probably apprehend what's going on.  Ask any questions you have in class or on the discussion page.  

Identify the main functions, down the 'left hand side' of the diagram -- or in the first lines of your pseudocoded paragraphs.  Then refer to these scripts and the PB documentation to fill in the details.  As you work you may find that your Details, Order, or Goods & Services tables need some more columns to handle these new functions.

When you get to the point of testing your MAPI processes, start out by sending the resulting email back to yourself so you can see what results.  Then get with your classmates and give this microcosmic ecommerce a try.

w_getpsx12 is the window that is opened, and where all the scripts are kept:

Since the interface to the Ledger Engine is mostly through email, there's not much to the user interface except a stop button.  The open event for w_getpsx12 doesn't do much except TriggerEvent(Timer!) once so that there's an immediate pass through the script for the Timer event.  Then it sets it's window's timer(180) so that w_getpsx12's Timer event's script is started, fresh, each 180 seconds.

The Timer event script creates MS as a mailsession object.  MS then has all the properties, events, and methods (PB calls them functions) needed to describe and handle interaction with a Windows, or Mac, computer's default MAPI client.  Msg is declared as a Message type Object, and Recipient as a MailRecipient.

The instance variable Attempts is incremented at each pass through the Timer event's script so the number of attempts can be displayed on w_getpsx12.

MailLogon, MailDownLoad!,  mailReturnSuccess!, MailGetMessages, and the array MessageID[] are used to logon to the MAPI session and get InBox messages' ids into the array MessageID[].  The UpperBound function applied to MessageID[] gives the count of items in the InBox and loads InBoxCount after the Download request.

Of these InBoxCount messages, some may be formatted as psX12 transactions with a leading *!*! in the subject line.  A for/next loop goes through the InBox InBoxCount times looking for *!*!, increments psX12Count when it finds one, and drops the InBoxLoc-ation of each potential psX12 transaction  into an array at psX12Loc[psX12Count].

If psX12Count is zero after this check the script does a goto (not of the spaghetti variety) to MSLogoff, which tidies up after the session and returns control back to PB, which keeps the Timer running.

If psX12Count isn't zero the script continues to CONNECT to the database and write any of the psX12 transactions that have been pigeon-holed into a file for easy handling later in the script.  

A for/next loop checks MS.mailReadMessage for each of the locations 1 through psX12Count  & loads the MS object with each email message.  The psX12FileName is constructed using the current date, time, and position in the batch of email received.  Each email message has its Subject concatenated with CrLf and its NoteText as FileWrite streams it to a file on disk.  FileClose closes each file.  As each file is written, it's psX12FileName is put into psX12FileNames[psX12Count] for easy reference in the next part of the script.  MailReadMessage called with a FALSE in the last parameter leaves the message marked unread, and a TRUE there marks it read.

The next for/next loop gets each of the psX12FileNames, checks for a valid file name (not "") and starts the edit of the email item by doing a FileOpen and FileRead of the item into psX12Line.  Note that while the file was written in stream mode it is read one line at a time using the CrLf as a record delimiter.  

The mid string function gets a potential EnterpriseId from the 5th postion in the string SubjectLine for a length of 4 into EmailEnterpriseId.  An SQL SELECT checks the table enterprises for a valid EnterpriseId and returns the current value for EnterpriseName and EmailAddress.

If there is an email address the edit continues.  If there is no email address there is no reason to edit anymore and the only thing that happens is that the list box lb_psx12inbound.AddItem function displays it in the window.

The instance variable EditErrors is used to accumulate the count of errors that are detected through the script.  Another instance variable, EditMessage, is used to echo each line of the transaction as it is encountered and any edit messages that may apply.  At several points in the script EditErrors being greater than zero will terminate processing.  If the transaction is completely parsed with no EditErrors the database is updated, or the REQUEST is fulfilled.

After the script leaves the subject line, where the position in a string is all that is used to find the EnterpriseId, the other lines in the psX12 transaction all have their fields delimited with an asterisk so the data needs to be 'parsed' out of the string and put into variables.  The user function ParseLine is called for each subsequent psX12Line and parses out each field it finds into an array, defined as an instance variable Fields[].  It returns an integer into FieldCount so that the first edit consideration, number of fields, is an easy one to find.

If there are no edit errors from SubjectLine and the expected FieldCount is found, choose case Fields[1] calls the function appropriate for a case of JV, CAT, or REQUEST*CAT in Fields[1].  JV calls handleJV, CAT calls handleCAT, and REQUEST*CAT gets handleREQUEST.  Anything Else gets an EditMessage complaining about the data there.  Each function call passes psX12File (the integer with the file's 'handle' from the FileOpen) with it so that the function can ReadFile(psX12File) and get each subsequent line.

As each of the functions returns control to the Timer event's script EditErrors is checked.  If it is not zero a message is concatenated to the end of EditMessage advising that the transaction wasn't processed.

Email is sent in each case, EditErrors or not.  The property Recipient.Name is set to the string EmailAddress, Msg.Recipient[1] is set to Recipient, and Msg.Subject & Msg.NoteText get "!*!*Reponse from the Ledger Engine" and EditMessage, respectively.  MS.mailSend engages the mailsession function that interacts with the MAPI client to send the message.

Parseline is a 'user defined' function inserted into the PB scripts by clicking Insert, Function and filling out the prototype area at the top of the newly inserted function.  Here is a screenshot of a PB function.  If the 'prototype area' isn't displayed on your monitor when you insert your function open it up by clicking on the show/hide prototype button.

Functions are called from other scripts as needed in a format like TransactionOK = handlecat(psX12File).  handlecat is nominated as the Function Name.  When the function returns its script can return a value for the variable on the left side of the equal sign.  Here TransactionOK is a boolean and this is noted in the Return Type attribute in the prototype area.  psX12File is an integer type Argument that is passed into the function where it is referred to using the Argument Name filehandle.  I chose Access as public and Pass By as value.  Read up on possible uses of these in PB's online help.

Parseline starts by setting the instance array variable Fields equal to an equivalent, but otherwise unused, array called CleanFields.  This is easier than writing a loop to visit each array element and reset it to a null string before its use.  F is used as a subscript for Fields[] and is initialized to 1.  P is a pointer to the Position in the string being parsed (argument named aline) and L is set to len(aline) to control the for/next loop which parses the string.  The mid function is used to load each character into AChar (this step could just as easily be omitted) for comparison with Delimiter, which is an instance variable initialized as asterisk.  If AChar is delimiter the subscript F is incremented, else AChar is concatenated to Fields[F].  The return (F) returns the field count for the calling script.

Handlecat uses some new syntax and it is discussed here. To avoid a longer discussion than necessary, I ask you to please look at the data for a valid CAT transaction as you look at the script and you'll see that the logic reflects the data.  

This function allows email with valid CATalog entries to place and update entries in the catalog table in the Ledger Engine's database.

Dated and Timed are date and time data types that are loaded using the date and time functions from the pieces that are extracted from the date/time stamp in the subject line.  

SELECT "descr" WHERE "enterprise id" = :EnterpriseId AND "gsid" = :GSId is used to query the database for an existing entry.  

If Fields[3] = "DELETE" then an existing entry, verified by a SELECT, will be deleted.

If there wasn't a DELETE the edit continues.  If an entry for the EnterpriseId and GSId is already in the table SQLCA.SQLCode will be zero and an SQL UPDATE updates it with the data in the ENT line, else  INSERT is used to insert new entries.

When a line is encountered with Fields[1] <> "ITEM" this line is edited for the expected "ECAT".  If an ECAT line is encountered its line count in Fields[2] is compared to the LineCount that has been counted and its Fields[3] is compared to the IdHash that has been accumulated.  A difference causes EditErrors to be incremented.

Zero EditErrors gets a COMMIT so that all the INSERTs, DELETEs, and UPDATEs above are applied to the database.  Otherwise, ROLLBACK keeps the data out of the database.

HandleJV introduces very little new syntax, but the logic is somewhat different from handleCAT.  Instead of doing an insert or update for each ENT line as it is read, the ledger account and debit/credit amounts are placed in parallel arrays.  This lets the script assemble an entire journal in the arrays LDetIds[] and Amounts[] and make sure that the journal nets zero before proceeding to update the database.

Since a JV with the same date as an earlier one needs to be 'backed out' of the ledger a CURSOR, ExistingJournal, is opened using the Accounting Date on the JV.  If it's first FETCH gets an SQLCode of 0 the script sets out to SELECT the entries from the ledger and back out the amount in the journal entry in the CURSOR (LedgerNet -= Net).

Next a for/next loop for LDetP = 1 to LDetCount goes through the arrays LDetIds and Amounts, INSERTS each into the journal, and either UPDATEs existing ledger entries with a new LedgerNet or INSERTs previously unreferenced ledger accounts.  To avoid the Ledger Engineer having to make manual entries of ledger text that are not already defined in the simple chart of accounts new ledger accounts must be accompanied with new text as provided for in the psX12 standards.

If there are zero EditErrors a Trial Balance report is concatenated to EditMessage that should net zero.  This section of Script uses the len & fill functions to aligns columns as it formats them out of the CURSOR into EditMessage by padding with spaces.

Handlerequest is very simple compared to the above and introduces no new syntax.

It does introduce control break logic. The ORDER BY clause of the SELECT fills the CURSOR's CatEntries in order by Enterprise Names and Catalog Descriptions.  After the initial FETCH CurrentEnterpriseId is set to the first CatEnterpriseId in the CURSOR. The first line in EditMessage gets EID* + the CurrentEnterpriseId. 

When a 'break' is detected by CatEnterpriseId <> CurrentEnterpriseId the CurrentEnterpriseId gets swapped and a new EID* line is inserted for the next Enterprise in the listing before continuing to list the catalog ITEMs.

The best I can tell, ANSI X.12 transactions similar to these drive most of the 'B to B' activity of EDI and ecommerce today.  X.12 is a 'closed' standard, beautiful for an Analyst to behold, that describes every conceivable document involved in buying, selling, and transporting goods & services.  The X.12 documents cost $400+, although extracts of them are available among EDI trading partners.  

Our Enterprises, Ledger Engine, & psX12 standards resemble Real Enterprise, Accounting Systems, & ANSI X.12 the way a comic book of The Christmas Carol resembles Dickens' original work.  I estimate that it is maybe 1/20th to 1/10th as complex as a 'real EDI dance' among EDI trading partners and organizations using ERP.  But, the programs involved in the real thing are shaped like these & I hope the experience in this course helps when you encounter the real thing.

Several months back I was apprehensive when asked to work on a project for a book seller interested in using a large book distributor's 'web based' system for fulfilling orders instead of keeping inventory on hand.  My client put in me in touch with my counterpart in the distributor's system who said they were using a BISAC transaction that he would fax me and that we could 'just send it to them using ftp.'  I was delighted to find out that the BISAC standard was just a subset of ANSI X.12 that had been taken out of the complex EDI transport protocol and 'value added networks' that had provided such a challenge in the past.