Month End Processing in LAMPS
During this session we will review recommended month end practices in LAMPS. Last day of the Month Process – Cash Receipts /Journal Entry Batch
Credit Card Processing for End of the Month- Booking Journal Entries Aging Reports
Member by Dues Type Report to process payments to NAR, State and RPAC (RAPAC-TREPAC)
Accounting Period Close
Apply Unapplied Payments and Credit Memos Journal Entry Report by GL Code
Cash Receipts Report by GL
IMPORTANT: This documentation is for Month End Processing and will act as a final check at the end of the month that all is in balance. Cash Receipt
reconciliation and General Ledger Batch Exports and Imports should occur on a daily or weekly basis.
Month End Processing in LAMPS Run the Cash Receipts Report
On the last day of the month it is important to balance and close out payments through LAMPS. Use the Cash Receipts Report to balance the checks, cash and credit card payments going in to the bank.
To run the Cash Receipts Report:
1. Go to LAMPS Reports, select the Accounting module.
2. From the fly-out menu, select Cash Receipts. Enter the desired Start and End Effective Dates and click Get Report. The Cash Receipts Report is displayed. 3. Verify that the cash receipts balance. (The amount of the deposit should equal
the total amount of cash and checks on the Cash Receipts Report.) To retain a record of the report, you can either print it and save the printed version or save the report file to a folder on your computer.
Credit Card Statements and Bank Charge Fees
E-Commerce card fees are deducted from the amount that is deposited to the bank account per transaction. Associations must be sure to enter a journal entry for the credit card fees at the end of the month.
On the E-Commerce site, go to the main menu listed under Access E-Commerce, click Organization Reporting Menu. The Organization Reporting screen displays. Select from the Reports menu.
1. At E-Commerce, pull up the ACH Payment Detail Report for the same date range as the LAMPS Bank Rec. Details Report. Use the ACH Report rather than the Group Summary Report as the ACH Report shows all transactions by member names. This makes it easy to make a name-by-name comparison between the two reports. (See Additional Information for instructions on accessing E-Commerce.)
2. Compare the credit card transactions on the LAMPS Bank Rec. Details Report to those on the ACH Report. Transactions that appear on the Bank Rec. Details Report but not on the E-Commerce ACH Report are said to be “in transit” due to the time required for E-Commerce to deposit the funds into the association‟s bank account. Add the daily credit card transaction totals on the Bank Rec. Details Report then subtract the transactions in transit. Verify that the result equals the Gross Amount of Invoice total on
the ACH Report.
Note: if a credit card transaction appears on the ACH Report but not on the LAMPS Bank Rec. Details Report and is outside of the “in transit” window, please contact LAMPS Support for assistance.
E-Commerce and Bank Verification
1. From the bank credit card deposit statement, determine the sum of the credit card transactions (minus any refunds).
2. Compare the bank credit card deposit statement total to the Net Association Portion total on the ACH Report. Verify that the amounts match.
3. The association must create a journal entry for the Transaction Fee listed on the ACH Report.
NOTE: Through the LAMPS accounting process, the total Gross Amount of Invoice is posted to the General Ledger of the association‟s bank account. E-Commerce deposits the total Net Association Portion amount. To account for the difference, which equals the Transaction Fee Gross Amount, the association must make a manual journal entry in its third party accounting software. The bank account must be credited and the Credit Card Fee Expense/Bank Charges account must be debited for the Transaction Fee Gross Amount.
Pa Pay Credit Card Processing
At Pay Pal pull up the Reports and then click on “Settlement Report”. Select the date range(s) and then click “Submit”.
Compare the credit card transactions on the LAMPS Bank Rec. Details Report to those on the Pay Pal Settlement Report. Transactions that appear on the LAMPS Bank Rec. Details Report but not on the Verisign Report are said to be “in transit” due to the time required for Verisign to deposit the funds into the association‟s bank account. Add the daily credit card transaction totals on the Bank Rec. Details Report then subtract the transactions in transit. Verify that the result equals the Gross Amount of Invoice total on the Verisign Settlement Report.
Pay Pal and Bank Verification
From the bank credit card deposit statement, determine the sum of the credit card transactions (minus any returns). Compare the bank credit card deposit statement total to the Net Association Portion total on the Pay Pal Settlement Report. Verify that the amounts match.
Note: Since Pay Pal closes out their batches at close of each business day, it is recommended to run the Settlement Report daily and reconcile with the LAMPS Reports. Since Pay Pal is depositing the “gross” amount of the transaction to the association bank account, credit card fees must be entered as a manual journal entry to the third party accounting package. The total amount of credit card fees are listed on either the Pay Pal reports or your bank statement.
Create the Journal Entry Batch To create a batch:
1. In the LAMPS Administration site, select the Accounting module.
2. From the fly-out menu, select Batch Jobs.
3. The Accounting Jobs screen appears. From the drop down menu, select Create Batch. Note: A red warning message will appear if you have previously created a batch which has not yet been exported. If you do not export the previously created batch it will be replaced by the new batch.
4. Select All Un-exported Transactions.
5. Click Run Job. The system responds with a message to advise the user notification will be emailed once the batch has been created.
Run the Journal Entries Report
It is recommended to run the Journal Entries Report in both the Summary and Detail views to make sure the batch is in balance (Debits equal Credits). To run the Journal Entries Report in the Detail view:
1. In the LAMPS Administration site, select the Accounting module.
2. From the fly-out menu, select Journal Entries; the Journal Entries Report screen appears.
3. Select Batched/Not Yet Posted.
4. Click Get Report. The Journal Entries Report appears in the Detail View. Verify that the Debits and Credits balance.
5. To retain a record of the report, export it in PDF format and save it to a folder on your desktop.
To run the Journal Entries Report in the Summary view:
Follow the above outlined procedures, remembering to click Summary Only in the Journal Entries Report screen.
Export the Batch To export a batch:
1. In the LAMPS Administration site, select the Accounting module.
2. From the fly-out menu, select Batch Jobs. The Accounting Jobs screen appears.
3. From the drop down menu, select Create Batch.
4. Click Run Job. For QuickBooks, the data is compiled into a file with a .iff extension; for Peachtree the compiled file will have a .csv extension. You will
receive an email with this file as an attachment. 5. Retrieve the
attachment from your email; right click on the attachment and save it to a folder on your desktop. The file is now ready to be
imported into your accounting software. Import to Third Party Accounting Software To import the file into QuickBooks:
1. Ensure that you have set LAMPS up as a Customer in QuickBooks.
2. It is recommended that, prior to each import, you perform a back-up in QuickBooks.
3. Select File.
4. Select Import/Export.
5. Browse to find the .iif file on your desktop. (Note: some versions of QuickBooks do not require you to browse to find the file).
7. In QuickBooks -Go to Reports> Accountant and Taxes> Transaction by Detail Report to verify that the imported data matches the LAMPS Journal Entries Summary Report.
To import the file into Peachtree:
1. It is recommended that, prior to each import, you perform a back-up in Peachtree.
2. Select File.
3. Select General Ledger.
3. Select the LAMPS GL Import Template. 4. Select Import.
5. Select Options.
6. Browse to find the .csv file on your desktop.
7. Check Import Options – First Row Contains Headings. 8. Click OK.
9. Post the batch – and print General Journal to verify that the imported data matches the LAMPS Journal Entry Summary Report.
LAMPS Aging Reports
At the close of each month, the Association should balance their Accounts Receivable and have record of members and offices that still have a balance due.
1. In the LAMPS Reports site, select the Accounting module.
2. From the fly-out menu, select Invoice Aging Report. Enter the end of the month date (Effective Date) and select Get Report. The Invoice Aging Report is displayed. This report provides a listing of Member and Office Invoices, Credit Memo‟s and Unapplied Payments that are still outstanding.
3. From the fly-out menu, select Member Dues Aging Report. Enter the end of the month date (Effective Date), as well as the Dues year and select Get Report. The Member Dues Aging Report is displayed. This report provides a listing of Member and Office Invoices that are still outstanding.
NOTE: The Member Dues Aging Report must be run by Dues Year to get a balance for Members who have an outstanding balance for each dues year. For clarification and clean up, we recommend you run this for previous years to ensure there are no uncollected balances.
Making Payments to NAR, State Association and RPAC/TREPAC/RAPAC
LAMPS provides details in the Member Dues by Dues Type report that the Associations will need to process payments for dues that have been collected on behalf of National, State and RPAC contributions.
1. In the LAMPS Reports site, select the Dues module.
2. From the fly-out menu, select Member Dues by Dues Type Report. 3. Enter the criteria for the Dues Type, and select Get Report.
NOTE: This report displays payments actually paid to the association. If a payment for an invoice was prorated, the prorated amount that was remitted will be displayed here.
Unapplied Payments/Credit Memos
At the close of each month the Accounting staff should review Unapplied
Payments/Credit Memos and apply each when applicable. Administrative users may refund Unapplied Payments and Credit Memos for both Members and Offices by Credit Card, Check or Cash.
Unapplied Payments and Credit Memos may also be applied across all available open Generic Invoices for Members and Offices.
A Comment Box with a 100 character limit is available for both Unapplied Payments and Credit Memos to allow staff to make any desired notation on the transaction. Comments appear on both Invoices and Receipts beneath the Billing/Payment information sections and will display for Members.
Applying an Unapplied Payment/Credit Memo
The „Unapplied Payments/Credit Memos‟ link is found under the Accounting menu. Selecting the „Unapplied Payments/Credit Memos‟ menu item displays a table of Unapplied Payments and a table of Credit Memos for each Member or Office within LAMPS.
An unapplied payment can be refunded back to the Member or Office by clicking the „REFUND‟ button.
Note: The Refund from Bank GL drop down box will display BANK GL Codes. The original BANK GL code the Credit Card amount was deposited to should be
An unapplied payment can be applied to a specific invoice for the corresponding Member by clicking on the „APPLY‟ button next to the unapplied payment.
When the Apply button on an unapplied payment is selected the „Apply to Invoice‟ screen displays all open invoices related to the Member or Office selected.
The top of the screen will display the Member or Office the unapplied payment is for and the total balance of the unapplied payment that can be applied to the open invoices.
The unapplied payment can be distributed as the user sees fit among all of the open invoices.
The user may readjust the unapplied payment distribution by selecting the „RECALCULATE‟ button.
The user can apply the payment to the select invoices by selecting the „APPLY‟ button. This opens a preview page, allowing the user to review the payment details before submitting. (Outlined on the next page) Below the invoices displays the total outstanding
balance, the total unapplied payment balance applied to the invoices, and the difference between what has been paid vs. what was owed.
The user can cancel the operation and return to the previous screen by selecting the „CANCEL‟ button
Selecting the „APPLY‟ button from the Apply to Invoice screen opens a
confirmation screen. This screen will show the distribution of the payment across the various invoices.
Once the Submit button is selected the system applies the Unapplied Payment to the invoice(s) and the Summary – Applied Payments screen appears which displays the transaction details.
Selecting the „CANCEL‟ button returns the user to the previous screen where additional adjustments can be made to the payment distribution or the process can be completely cancelled.
Selecting „SUBMIT‟ on this screen applies the payment to the invoice(s).
Accounting Period Close
This feature within LAMPS allows Administration staff to close a specific Accounting Period. Once a period is closed, all transaction activity will be restricted from being applied to the specific date range. The Accounting Period Close table will display detailed information regarding who Opened or Closed a specific period. When an
accounting period is closed it will override all Effective Date rules configured in the system.
It is important for the Accounting Staff to close the previous periods as part of the month end closing procedures. This will allow transactions to remain in the appropriate period.
Select the link to Open or Close an Accounting Period.
Specific details will display regarding the date, time and user that Opened or Closed each Accounting Period.
Journal Entry Report by GL Code
A new filter has been added to the Journal Entry Reports which allows you to sort the Journal Entries by General Ledger Code.
1. In the LAMPS Reports site, select the Accounting module. 2. From the fly-out menu, select Journal Entries
Enter the criteria for the report, whether you want to run this report in detail or summary, for a particular month, or a specific Batch.
If there is a possible discrepancy or question regarding an import to the third party accounting package, this report should be used to check off the LAMPS entries to QuickBooks or Peachtree Accounting Journal Reports.
Quick Books Report – Accounting and Taxes-Journal Report
Peachtree Accounting Report –
Cash Receipts by General Ledger Code –
LAMPS Cash Receipts by General Ledger Code report is for Associations who prefer to enter the journal entries manually to their third party accounting software and record the income on a cash basis.
This report displays the actual income accounts related to the purchase of the invoice, class, meeting, or dues, as well as the Bank Account it was posted to.
Once you have entered the specified dates for the report it displays the cash receipts recorded in LAMPS for that bank account, and the accounts related to the transaction.
Total Page of Cash Receipt by General Ledger Code
Note: If a partial payment is made to an invoice, the GL code displays as XXX and so the user can determine how to apply this partial payment to the
E-Commerce Credit Card Errors
E-Commerce News – 6/2/2010 identified for the Association the NRDS errors received when there is an issue with a credit card transaction
.
CREDIT CARD RESULT CODES For those of you who use the external interface into the Realtor Ecommerce network (i.e. you are using your own front end to interface from, such as systems from vendors such as Rapattoni, LAMPS, MMSI, etc. or web functions such as registration that someone wrote for you, etc), when a transaction arrives at Ecommerce, it sends a coded result back that your vendor/programmer then presents “in English” as the answer. Because we often get questions from our EPOEs as to what the possible responses are and what they mean, we have published the list below. Please keep in mind that your vendor/programmer may be wording these differently, but the result code number is always the same:
Results on a Purchase or Refund
Result code 0 – Result Message - Your transaction has succeeded.
On a Purchase or a Refund, this means that the transaction was approved and an EC Control Number has been assigned and sent back to your system.
Result Code 2 – Result Message - Your organization is not setup to use the Electronic Commerce external interface. This means that your association has not been set up in the Ecommerce Network. You may be trying to send transactions through before NAR has completed your setups.
Result Code 5 – Result Message - You are not a member in Active, Pending, or Suspended Status.
Every transaction requires a NRDS ID. This means the NRDS ID in your transaction is either not in NRDS or is not Active, Pending, or Suspended Status.
Result Code 6 - This is sent whenever there is an error with your data. The specific data error explanation is sent to your system each time a Result 6 is produced. Some of those are: A Credit Card Expiration Date in the form MMYY as it appears on your credit card statement is required.
This means an Expiration Date was not sent, or, it was not in the MMYY format.
A Credit Card Number as it appears on your credit card statement is required. No dashes or spaces
This message comes from the credit card processor. It means that the number sent is not a valid card number. For example, a Visa card starts with a 4 and is 16 numbers in length. The member may have selected Visa and typed in a 16 digit long number starting with 4, but that does not necessarily mean it is a valid Visa number. The credit card processor has a formula they run to verify if the card number itself is valid.
A transaction amount greater than zero is required, must not contain commas.
The Amount you sent was either zero, or it contained commas. Your system should have been designed to not send commas in the amount field.
An ampersand may not be used in a parameter
When your vendor/programmer designed your system, the technical document stated that the & could not be used within a field.
Invalid Credit Card Type.
The member selected Visa and then typed in an AMEX card number. Or, you are sending up a credit card type (i.e. Discover) but you did not turn on that type in the Disbursement & Payment Settings screen on the Ecommerce menu.
Your Account Name as it appears on your credit card statement is required, no greater than 50 characters.
The Account Name (cardholder name) cannot be more than 50 characters in length. Your Credit Card has expired.
Result Code 7
Result Message - Settlement failed
On a Purchase or Refund, this means the transaction was Declined by the credit card company. Result Code 8
Result Message - Authorization failed
On an Authorization, this means the transaction was Declined by the credit card company. Result Code 9
Result Message - Your association profile does not have a bank account set up.
This means you are trying to send a transaction through before NAR has set up your bank profile. Result Code 10
Result Message - The EC Control does not belong to your association.
This message is returned on a Refund transaction. Every Refund must reference the EC Control number of the original purchase. This means your system is referencing an EC Control Number that belongs to another association‟s purchase transaction.
Result Code 11
Result Message - The AssociationRequestID has already been processed.
Every transaction that your vendor/programmer‟s system sends to Ecommerce must have a unique number called an AssociationRequestID that your system creates. Each one must be unique and not be used more than once. This message means that the AssociationRequestID you are sending with a transaction has already been used before.
Result Code 12
Result Message - The association has not setup any payment methods
This means you did not go to the Ecommerce menu option – Disbursements & Payment Settings and select at least one credit card type for your checkout counter.
Result Code 13
Result Message - The Ecommerce system is not currently available.
This means that the Realtor Ecommerce connection or the processor‟s connection is down. In this situation, it means the same as “no answer” and your transaction was not processed. Result Code 16
Result Message – The return amount must be less than or equal to the original purchase amount. This is also sent when you try to send the same refund more than once.
This will occur on a Refund. The Refund amount must not be greater than the original Purchase amount.
Result Code 17
Result Message – Invalid ECControl Number or else not found.
This message is returned on a Refund transaction. Every Refund must reference the EC Control number of the original purchase. This means your system is referencing an EC Control Number that does not.
Result Code 44
Result Message - The Credit Card Number must be in valid numeric format.
This message comes from the credit card issuer and means that the credit card number entered does not exist.
Result Code 46
Result Message - The NRDS database is currently unavailable and cannot check this ID. Every transaction requires a NRDS ID. Ecommerce must check to make sure the NRDS ID sent exists and is not inactive. This message is returned if NRDS is down and Ecommerce cannot validate the NRDS ID. It will reject the transaction.
Results from a Search or Query
The next set of results codes are not returned on purchases or refunds. These are used strictly when the EPOE is running a Transaction Verification Request or search on a previously sent transaction. This is a function that your vendor/programmer can write for you. You would use this function to query/search the Ecommerce network to find out what happened with a previous transaction. Because your system and Ecommerce use the Internet, there is always a chance that the first time the response is sent, your system did not ‟hear‟ it and you need to ask again. This function does not run the transaction through again; it simply asks the Ecommerce Network “what was the answer on this transaction we previously sent
Result Code 21
Result Message – The Association Request ID passed in does not currently match an existing External Interface Transaction.
Every transaction that your vendor/programmer‟s systems send to Ecommerce must have a unique number called an AssociationRequestID that your system creates. Each one must be unique and not be used more than once. This message means that the AssociationRequestID (transaction) you are asking about never arrived at Realtor Ecommerce and therefore was not processed.
Result Code 22
Result Message – The External Interface purchase request is currently in Started status. Every now and then, the card processor does not complete the process of a transaction and sends back a “Started” status. When you see this response to your Transaction Verification, it means that the transaction was not processed by the card processor and you can send it again. Result Code 24
Result Message – The External Interface purchase request is currently in Completed status. The transaction was Approved.
Result Code 25
Result Message – The External Interface purchase request is currently in Settlement Failed status.
The transaction was declined. Result Code 26
Result Message – The External Interface purchase request is currently in Settled status. The transaction was Approved and has settled.
Again, these are some of the more common Result Codes. Please be sure to contact the NRDS/Ecommerce helpline at 1-800-868-3225 anytime you need more information on your Ecommerce transactions and their results.
