Creating prepayments for O/E transactions in Sage300
This blog uncovers the mystery of how to create A/R prepayments that are associated to their O/E documents and can be accessed via the drill down button on the A/R receipt entry screen. The logic described and presented in the blog applies equally to O/E orders, shipments and invoices.
Creating a prepayment with an O/E document is difficult for a few reasons:
- Macro recording does not work; you need to use RVSPY to understand view calls.
- Creating a prepayment requires manipulation of three separate, un-composed views and due to the lack of composition each view requires each field to be set individually.
There are three rough steps to creating a prepayment:
- Create an A/R receipt batch
- Create an O/E prepayment record.
- Update the O/E transaction header view.
Full source code is available for download at the bottom of this post.
A/R Receipt Batch
Firstly, it is necessary to either create a new A/R receipt batch or use a currently open batch. Creating the batch is relatively straight forward and really only requires you to set the bank account. Note that you do not need to compose each of the receipt views since you are not creating any receipt entries directly.
'Open the A/R Receipt Batch View
Dim ARReceiptBatch As AccpacView
mDBLinkCmpRW.OpenView "AR0041", ARReceiptBatch
'Create a new batch
'Set the bank to which the receipt will be entered.
'Set the bank currency...if you weren't setting this and were using the default
'bank account currency you can obtain it from the view.
If sBankCur <> vbNullString Then
ARReceiptBatch.Fields("CODECURN").Value = sBankCur
sBankCur = ARReceiptBatch.Fields("CODECURN").Value
'Set a batch description
ARReceiptBatch.Fields("BATCHDESC").Value = "Prepayment Batch from O/E"
'Update the batch
Update the O/E Prepayment View
The second step is to create the O/E prepayment record. The prepayment record is responsible for creating A/R receipt entry and it also associates the receipt to the O/E order so that there is a drill down against the prepayment.
Each O/E transaction (Order, Shipment & Invoice) has a separate prepayment view where each is identical with the exception of the view id. Therefore the logic used here is applicable to each.
If you are familiar with Sage300 development, most of the views are nicely programmed where a lot of hard work is performed behind the scenes by the view. Unfortunately, the prepayment views are the complete opposite! They do not have any default values either from the customer or order, so it is necessary to explicitly set each field.
The code sample below illustrates this, where it’s necessary to set the Customer, Customer Name & Customer Currency all individually despite setting the Customer Id.
Dim OrdPrepayments As AccpacView
mDBLinkCmpRW.OpenView "OE0530", OrdPrepayments
'Associate the prepayment with order
OrdPrepayments.Fields("ORDUNIQ") = OEOrdHeaderFields("ORDUNIQ").Value
OrdPrepayments.Fields("CUSTOMER").Value = OEOrdHeaderFields("CUSTOMER").Value
OrdPrepayments.Fields("CUSTDESC").Value = OEOrdHeaderFields("BILNAME").Value
OrdPrepayments.Fields("CUSTCURN").Value = OEOrdHeaderFields("ORSOURCURR").Value
Update O/E Header View
The final step is to update the prepayment fields on the O/E transaction header view, which update the Total Prepayments field on the order’s Totals Tab.
The attached code is a full working sample illustrating how to create and attach a prepayment and should work for Sage300 versions 5.6 and above.Download Sage 300 OE Prepayment source code