Entering Orders and Header/Detail Views

Sage ERP Accpac Business Logic objects are called Views. With these Views you can use various interfaces to input data, retrieve data, perform calculations or perform business processes. The interface to every View is the same and this allows us to offer many different interfaces to the Accpac Business Logic (see:  http://smist08.wordpress.com/2010/09/18/a-short-history-of-the-accpac-view-interface/).

Overview of the O/E Views

The Views in this posting are to do with entering and reading O/E Orders. Each View sits on a corresponding database table. Below are some of the Views and tables that make up an order.

Table                     View                      Description

OEORDH              OE0520                 Order Headers

OEORDD              OE0500                 Order Details

OECOINO            OE0180                 Order Comments

OETERMO           OE0740                 Order Pmt Schedules

OEORDQ              OE0526                 Order from Quotes

OEORDHO           OE0522                 Order Optional Fields

OEORDDO           OE0501                 Order Detail Optional Fields

OEORDDD           OE0502                 Order Kitting Details

OEORDDDS         OE0504                 Order Kitting Serial Numbers

OEORDDDL         OE0506                 Order Kitting Lot Numbers

OEORDDB            OE0503                 Order BOM Details

OEORDDS            OE0508                 Order Detail Serial Numbers

OEORDDL            OE0507                 Order Detail Lot Numbers

These Views then work together to manage Orders. These Views form header/detail relationships with each other and Accpac has standard protocols for manipulating header/detail Views. These are documented in the macros section of the System Manager User Guide. You don’t need to use all the Views all the time. If you aren’t using serial numbers or lots then you don’t need to worry about those Views. Mostly you use the Order Header and Order Details Views. To make the Views work together you need to compose them. You can get the list of Views that compose together from SDK tools, the Application Object Model on our Web site or you can get the code generated for you by recording a macro.

These Views manage the data in the database. They ensure data integrity is maintained. They control security. But additionally they will do calculations. For instance you can ask the order header calculate taxes or do a ship all.

Header/Detail

As an example of header/detail we have the Order Header and then its detail lines. The Order header has information that there is one of for the order. The details then contain data that there are many of. The Order Header contains information like the customer id and the ship-to address. Then each detail line contains an I/C Item number and quantity. So for an order you can order as many items as you like.

The Order Header also stores information like various totals. So as each detail line is added, updated or deleted these header totals have to be updated to keep them correct. Details are always associated with their header by having their primary key start with the header’s primary key, then just adding a uniquifier or counter. This is show in the diagram below:

To use header detail views you need to follow what we call View protocols. Generally they go along the following lines:

1. Open and compose all the Views
2. Either create or read the correct header record
3. Insert/update or delete which ever detail lines you are dealing with.
4. Update or Insert the header record.

Nothing is actually done to the database until you insert or update the header. Until this is done all changes are maintained in memory. Then when you update/insert the header, all changes are written to the database in a single database transaction. This ensures the database’s integrity is maintained where the details always match the header. This protocol also handles any multi-user situations that could arise.

Although Order Entry could potentially involve 12 Views, the basic protocols and interactions all follow this simple model. The Views themselves will also report errors if you do things incorrectly.

Order Entry Macro

Below is the VBA source code of an Accpac macro that enters Orders into Order Entry. The code to open and compose the Views was obtained by macro recording. The code to enter 1 order was also from macro recording but then modified into functions to be made re-usable. This code uses the Accpac COM API, but if you use the .Net interface or Java JNI, then the code will be very similar with just syntax differences for the calling language. Entering orders via SData would be quite different since you just provide the data in an SData XML payload, and the SData server handles all the View opening and composing automatically. The SData server then knows the View header/detail protocol and takes the data from the XML and feeds it into the Views via the algorithm indicated.

The macro below enters some optional fields. There are two optional field views. One is a detail of the header, meaning that you can have any number of optional fields for each order. The second one is a detail of the order detail View, meaning you can have any number of optional fields for each order detail line. Each of these is handled using the header detail protocol, but where the detail optional fields has the order detail View has its header.

This macro shows entering orders, but exactly the same mechanisms, logic and protocols would be used to enter any header/detail document in Accpac whether in G/L, A/R or P/O. So learning to do this once has great benefit towards your second program.

‘

‘ Macro to enter orders into O/E

‘

‘ Global variables for database links and views that are needed in most routines.

‘

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink

Dim OEORD1header As AccpacCOMAPI.AccpacView

Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail1 As AccpacCOMAPI.AccpacView

Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail2 As AccpacCOMAPI.AccpacView

Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail3 As AccpacCOMAPI.AccpacView

Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail4 As AccpacCOMAPI.AccpacView

Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail5 As AccpacCOMAPI.AccpacView

Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail6 As AccpacCOMAPI.AccpacView

Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail7 As AccpacCOMAPI.AccpacView

Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail8 As AccpacCOMAPI.AccpacView

Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail9 As AccpacCOMAPI.AccpacView

Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields

Dim OEORD1detail10 As AccpacCOMAPI.AccpacView

Dim OEORD1detail10Fields As AccpacCOMAPI.AccpacViewFields

 

Sub MainSub()

 

On Error GoTo ACCPACErrorHandler

 

    Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

    Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

   

    OpenAndComposeOrderViews

 

    ‘ In sample data we have set a price list of USA for customer 1200 location WAREHS so that prices are

    ‘ filled in automatically (A/R Shipto Locations).

 

    EnterOrder “1200″, “WAREHS”, Array(“A1-310/0″, “A1-400/0″, “A1-105/0″), Array(“5″, “4″, “6″), Array(“Red”, “White”, “Blue”)

 

    Cleanup

 

Exit Sub

ACCPACErrorHandler:

    MyErrorHandler

End Sub

 

‘

‘ Open and componse the various views used in this macro. Code is straight from macro recording.

‘

Sub OpenAndComposeOrderViews()

On Error GoTo OpenAndComposeOrderViewsErrorHandler

 

mDBLinkCmpRW.OpenView “OE0520″, OEORD1header

Set OEORD1headerFields = OEORD1header.Fields

mDBLinkCmpRW.OpenView “OE0500″, OEORD1detail1

Set OEORD1detail1Fields = OEORD1detail1.Fields

mDBLinkCmpRW.OpenView “OE0740″, OEORD1detail2

Set OEORD1detail2Fields = OEORD1detail2.Fields

mDBLinkCmpRW.OpenView “OE0180″, OEORD1detail3

Set OEORD1detail3Fields = OEORD1detail3.Fields

mDBLinkCmpRW.OpenView “OE0680″, OEORD1detail4

Set OEORD1detail4Fields = OEORD1detail4.Fields

mDBLinkCmpRW.OpenView “OE0526″, OEORD1detail5

Set OEORD1detail5Fields = OEORD1detail5.Fields

mDBLinkCmpRW.OpenView “OE0522″, OEORD1detail6

Set OEORD1detail6Fields = OEORD1detail6.Fields

mDBLinkCmpRW.OpenView “OE0501″, OEORD1detail7

Set OEORD1detail7Fields = OEORD1detail7.Fields

mDBLinkCmpRW.OpenView “OE0502″, OEORD1detail8

Set OEORD1detail8Fields = OEORD1detail8.Fields

mDBLinkCmpRW.OpenView “OE0504″, OEORD1detail9

Set OEORD1detail9Fields = OEORD1detail9.Fields

mDBLinkCmpRW.OpenView “OE0503″, OEORD1detail10

Set OEORD1detail10Fields = OEORD1detail10.Fields

 

OEORD1header.Compose Array(OEORD1detail1, OEORD1detail4, OEORD1detail3, OEORD1detail2, OEORD1detail5, OEORD1detail6)

OEORD1detail1.Compose Array(OEORD1header, OEORD1detail7, OEORD1detail10, OEORD1detail8)

OEORD1detail2.Compose Array(OEORD1header)

OEORD1detail3.Compose Array(OEORD1header, OEORD1detail1)

OEORD1detail4.Compose Array(OEORD1header, OEORD1detail1)

OEORD1detail5.Compose Array(OEORD1header)

OEORD1detail6.Compose Array(OEORD1header)

OEORD1detail7.Compose Array(OEORD1detail1)

OEORD1detail8.Compose Array(OEORD1detail1, OEORD1detail9)

OEORD1detail9.Compose Array(OEORD1detail8)

OEORD1detail10.Compose Array(OEORD1detail1)

 

Exit Sub

OpenAndComposeOrderViewsErrorHandler:  MyErrorHandler

   

End Sub

 

‘

‘ Enter an order based on a number of parameters.

‘

Sub EnterOrder(ByVal custno As String, ByVal shipto As String, ByVal items As Variant, ByVal quantities As Variant, ByVal colors As Variant)

    Dim lastLine As Long

On Error GoTo EnterOrderErrorHandler

   

    OEORD1header.Cancel

    OEORD1header.Init

 

    OEORD1headerFields(“CUSTOMER”).Value = custno                         ‘ Customer Number

    OEORD1headerFields(“SHIPTO”).Value = shipto                           ‘ Ship-To Location Code

   

    ‘ Insert any auto insert optional fields.

    OEORD1headerFields(“PROCESSCMD”).PutWithoutVerification (“1″)         ‘ Process Command

    OEORD1header.Process

           

        ‘ Set the CLERK optional field in the Order Header

        OEORD1detail6.RecordClear

        OEORD1detail6.RecordGenerate False

        OEORD1detail6Fields(“OPTFIELD”).Value = “CLERK”

        OEORD1detail6Fields(“VALIFTEXT”).Value = “Sammy ” + CStr(Date) + ” ” + CStr(Time)  ‘ Text Value

        OEORD1detail6.Insert

       

   

    For i = LBound(items) To UBound(items)

   

        lastLine = OEORD1detail1Fields(“LINENUM”).Value

        OEORD1detail1.RecordClear

        OEORD1detail1Fields(“LINENUM”).PutWithoutVerification (CStr(lastLine))          ‘ Line Number

        OEORD1detail1.RecordGenerate False

       

        ‘ Set the item code and quantity.

        ‘   Note that all the other fields are readonly until the ITEM is set.

        OEORD1detail1Fields(“ITEM”).Value = items(i)                                    ‘ Item

        OEORD1detail1Fields(“QTYORDERED”).Value = quantities(i)                         ’ Quantity Ordered

       

        ‘ Insert any auto insert optional fields

        OEORD1detail1Fields(“PROCESSCMD”).PutWithoutVerification (“1″)                  ‘ Process Command

        OEORD1detail1.Process

       

            ‘ Add the COLOR optional field to the detail.

           ‘ OEORD1detail7.RecordClear

           ‘ OEORD1detail7.RecordGenerate False

            OEORD1detail7Fields(“OPTFIELD”).Value = “COLOR”

            OEORD1detail7.Read ‘ Optional Field

            OEORD1detail7Fields(“VALIFTEXT”).Value = colors(i)                        ‘ Text Value

            OEORD1detail7.Update

       

        OEORD1detail1.Insert

 

    Next i

 

    OEORD1headerFields(“GOCHKCRDT”).Value = “1″                           ‘ Perform Credit Limit Check

    OEORD1header.Process

    OEORD1header.Insert

Exit Sub

 

EnterOrderErrorHandler:

 MyErrorHandler

   

End Sub

 

‘

‘ Cleanup routine destroys the COM objects in the reverse order that they were created.

‘

Sub Cleanup()

On Error Resume Next

 

Set OEORD1detail10Fields = Nothing

Set OEORD1detail10 = Nothing

Set OEORD1detail9Fields = Nothing

Set OEORD1detail9 = Nothing

Set OEORD1detail8Fields = Nothing

Set OEORD1detail8 = Nothing

Set OEORD1detail7Fields = Nothing

Set OEORD1detail7 = Nothing

Set OEORD1detail6Fields = Nothing

Set OEORD1detail6 = Nothing

Set OEORD1detail5Fields = Nothing

Set OEORD1detail5 = Nothing

Set OEORD1detail4Fields = Nothing

Set OEORD1detail4 = Nothing

Set OEORD1detail3Fields = Nothing

Set OEORD1detail3 = Nothing

Set OEORD1detail2Fields = Nothing

Set OEORD1detail2 = Nothing

Set OEORD1detail1Fields = Nothing

Set OEORD1detail1 = Nothing

Set OEORD1headerFields = Nothing

Set OEORD1header = Nothing

Set mDBLinkSysRW = Nothing

Set mDBLinkCmpRW = Nothing

 

End Sub

 

‘

‘ Simple generic error handler.

‘

Sub MyErrorHandler()

  Dim lCount As Long

  Dim lIndex As Long

 

  If Errors Is Nothing Then

       MsgBox Err.Description

     Else

      lCount = Errors.Count

 

      If lCount = 0 Then

          MsgBox Err.Description

      Else

          For lIndex = 0 To lCount – 1

              MsgBox Errors.Item(lIndex)

          Next

          Errors.Clear

      End If

  End If

 

End Sub

 

About these ads