RE: Todd's EDIFACT ENTREC message for General Ledger

[Todd Boyle <tboyle@rosehill.net>]

William J. Kammerer said April 01, 2001
> Dear Todd:
> 
> After all these months of harpooning the poor ENTREC and hours
> calculating the possible instances of ENTREC, I would've thought it
> possible to build a MIG (message implementation guide) of the message
> which would suit your purpose.
> [..]
> Tell you what: give us a couple examples of real ledgers, and we can
> examine the ENTREC (why not the LEDGER?) and see if we can build a MIG
> that accommodates your samples...

Thank you, and others, for your useful posts.  I have been recalibrating
my thinking.  I agree-- it will be useful to see an example.

OK here is a schema that reflects the range of functionality I think
might be the "sweet spot" in terms of GL.  One caveat: SAP, Peoplesoft,
and Oracle have ERP systems called General Ledgers that have hundreds
of thousands of fields, tables, functions etc. This is a matter of
semantics over which reasonable people differ, but those are not my
idea of a GL.  To me, the purpose and scope of a "General Ledger" is
narrower.  Most commonly includes financial reporting, tax reporting, 
and maintaining external balances (cash, payables, and receivables) 
(or control over the subledgers that maintain them).  

Is this sufficient to avail of your offer for an EDIFACT MIG? 
Many thanks,
Todd

arapxml070(1of2).gif

arapxml070(2of2).gif

Title: General Ledger schema draft .70

OK William here you go :-)  my favorite GL schema, draft version .70

This is a three tier GL structure composed of XML schema sequences, with no attributes, that can be parsed with ASCII string parsers. The minimum usage is (1) value for ledger, (2) values for Transaction, and (5) fields for each row of Entry.  

A goal is to support narrow, best of breed vertical apps and web-based services, which only need to transmit specific shapes of GL transactions, without loading them down with huge infrastructure costs. 

It would be simple as pie, to loop thru a bunch of transactions with any string parser in VB, C, perl, python, etc. and populate an array or database.  So, small developers are not forced to maintain any XML infrastructure whatsoever, to send and receive XML instances. See the sample instance.  

Note that a PO, Invoice or Remittance advice could be easily formatted into this schema including product codes, payment information, party details, etc.  And could be transmitted as a single row.  Show me an Invoice.  I will give you an XSLT transform to re-shape it into this format, as an account receivable or payable entry which can then be stacked into flat GL rows.

I think something like this could have sufficient richness to support many enterprise GL applications.  Note it has unconstrained doc types, multiple party codes etc. I am confident something this small could support most SME GL applications.  THIS is very quick and dirty , sorry for any errors here,

TB 1 apr 2001

 

Entry Rows (1 or more required; out-of-balance permitted to support REA and STRs.)

 

Schema

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" elementFormDefault="qualified">
  <xsd:complexType name="Entry">
    <xsd:sequence>
      <xsd:element name="transactionId" type="xsd:string" minOccurs="0"/>
      <xsd:element name="entryId" type="xsd:string"/>
      <xsd:element name="entryDateTime" type="GLdatetime"/>
      <xsd:element name="entryJournalType" type="xsd:string" minOccurs="0"/>
      <xsd:element name="accountCode" type="xsd:string"/>
      <xsd:element name="originalAmount" type="FbcCurrency" minOccurs="0"/>
      <xsd:element name="amount" type="FbcCurrency"/>
      <xsd:element name="debitOrCredit" type="xsd:string" minOccurs="0"/>
      <xsd:element name="entryDescription" type="xsd:string" minOccurs="0"/>
      <xsd:element name="entryDocRef" type="DocRef" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="user" type="xsd:string" minOccurs="0"/>
      <xsd:element name="party" type="Code" minOccurs="0"/>
      <xsd:element name="partyUserId" type="xsd:string" minOccurs="0"/>
      <xsd:element name="partysDocRef" type="DocRef" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="settlementMethod" type="xsd:uriReference" minOccurs="0"/>
      <xsd:element name="dueDate" type="GLdatetime" minOccurs="0"/>
      <xsd:element name="entryTerms" type="EntryTerm" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="entryNegotiationState" type="xsd:string" minOccurs="0"/>
      <xsd:element name="entryDocRef" type="DocRef" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="entryCode" type="Code" minOccurs="0" maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="FbcCurrency">
    <xsd:sequence>
      <xsd:element name="value" type="xsd:double"/>
      <xsd:element name="currencyMnemonic" type="xsd:string" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="GLdatetime">
    <xsd:sequence>
      <xsd:element name="Date" type="xsd:date"/>
      <xsd:element name="Time" type="xsd:time" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="DocRef">
    <xsd:sequence>
      <xsd:element name="docNumber" type="xsd:string"/>
      <xsd:element name="docType" type="xsd:string" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="EntryTerm">
    <xsd:sequence>
      <xsd:element name="fixedAmount" type="xsd:string" minOccurs="0"/>
      <xsd:element name="fixedAmountDateTime" type="xsd:string" minOccurs="0"/>
      <xsd:element name="rate" type="xsd:string" minOccurs="0"/>
      <xsd:element name="rateDateTime" type="xsd:string" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="Code">
    <xsd:sequence>
      <xsd:element name="codeValue" type="xsd:string"/>
      <xsd:element name="codeList" type="xsd:uriReference"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="Transaction">
    <xsd:sequence>
      <xsd:element name="transactionId" type="xsd:string"/>
      <xsd:element name="transactionDocRef" type="DocRef" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="transactionDateTime" type="GLdatetime"/>
      <xsd:element name="transactionPostDate" type="GLdatetime" minOccurs="0"/>
      <xsd:element name="period" type="xsd:string" minOccurs="0"/>
      <xsd:element name="transactionDescription" type="xsd:string" minOccurs="0"/>
      <xsd:element name="originalLedgerUri" type="xsd:uriReference" minOccurs="0"/>
      <xsd:element name="originalTransactionId" type="xsd:string" minOccurs="0"/>
      <xsd:element name="groupId" type="xsd:string" minOccurs="0"/>
      <xsd:element name="transactionNegotiationState" type="xsd:string" minOccurs="0"/>
      <xsd:element name="postedStatus" type="xsd:boolean" minOccurs="0"/>
      <xsd:element name="transactionFiscalType" type="xsd:string" minOccurs="0"/>
      <xsd:element name="transactionJournalType" type="xsd:string" minOccurs="0"/>
      <xsd:element name="entry" type="Entry" maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="ledger">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ledgerName" type="xsd:string"/>
        <xsd:element name="ledgerUri" type="xsd:uriReference" minOccurs="0"/>
        <xsd:element name="actionRequest" type="xsd:string" minOccurs="0"/>
        <xsd:element name="transaction" type="Transaction" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Minimal sample GL transaction

<ledger>
<ledgerName>125 Mainstreet</ledgerName>
<Transaction>
  <transactionId>2352</transactionId>
  <transactionDate><Date>2001-04-01</Date></transactionDate>
    <entry>
      <entryId>2352001</entryId>
      <accountCode>Cash</accountCode>
      <amount><value>234.50</value></amount>
    </entry>
    <entry>
      <entryId>2352002</entryId>
      <accountCode>Accts Receivable</accountCode>
      <amount><value>-234.50</value></amount>
      <party><codeValue>069874936</codeValue><codeList>DUNS</codeList></party>
    </entry>
  </Transaction>
</ledger>