Change Debit AR Account Based on Part Number

Epicor ERP 10
1

Hi all,

For some background our finance department creates invoices for lease terms. The debit amount should be moved to a separate AR account, however the amount is currently going to a default AR account instead. These lease terms always have the same part number, so I was hoping there is a way to decide which account to use based on part number.

Now I have done a little bit of research and everywhere I look people are saying to use product groups in GL control. However, based on what I am seeing this won’t work on the AR side of things. So then would modifying posting rules be the way to go with solving this? If so, what would that posting rule look like? I am new to the financial side of Epicor so I really could use a bit of guidance here.

Also side question: I have the posting rule tech reference, but is there any sort of basic examples or walkthroughs dealing with posting rules?

2

What Aspect of “AR” are you referring to?

  • The transactions that happen prior to invoicing (like a customer shipment)
  • The transactions that happen when an invoice is posted?
  • The transactions that happen during cash receipts?

Depending how your company settings are configured, the most basic GL flow is:

  1. PO Placed (no GL transaction)
  2. PO Received to Inventory
    (PUR-STK part tran creates GL trans: DB:Inventory, CR: ACCRUED RECEIVINGS)
  3. Cust Shipment from Inv
    (STK-CUS part tran creates GL trans: DB: A/R Clearing, CR:Inventory)
  4. Invoicing creates GL trans:
    4.1 DB: COS Account, CR: A/R Clearing
    4.2 DB: Sales/Revenue Acct, CR AR Account (not to be confused with A/R Clearing)

Which of those would you want to customize?

3

I honestly have no idea as a lot of this financial jargon is a bit over my head. I asked someone in Finance/Accounting and they said it would be when the invoice is posted & that happen during cash receipts. They also specified the following when I asked.

" AR is debited at invoicing not a credited….and sales is credited not debited.
When we do a cash receipt, we’d want the proper AR account to be credited as well.
The same customer might have invoices that need to go to different Accounts receivable accounts so we cannot make it customer specific."

Hopefully that makes some sense.

4

To understand how AR Invoices are posted you can open GL Transaction Type and load “AR Invoice”.

From there you can expand Posting Rules and see how invoice details are processed and how accounts are selected, the one you are referring to is below

image
image779Ă—160 6.11 KB

As you can see the account is taken from the bill to customer GL Control, if that is not assigned it will go and check the one in Company Configuration.

You can customize the rule based on any criteria, you can identify your special invoices using a UD field or something else and in the rule you can make sure that for those invoices it goes to the account you want.

1 Like
5

I probably got those two backwards - As I’m not an accountant. I have a 50/50 chance of getting DB’s and CR’s right but seem to fall well under 50% of the time.

And it looks like they’re talking about my point numbered 4.2, and the actual cash receipts part (#5, had I included it).

6

I would not modify the posting rules.
What I would do is separate the customer into 2 customers, the retail and the lease. This will allow you to keep the leasing as a separate AR account based on the customer G/L Control.
There is no other way to keep the AR accounts separate using the “vanilla” version.
Once you start modifying posting rules, you will have to review them with each update and users will constantly want changes, since they know it’s possible.
Charlie

1 Like
7

I just looked at the document “Epicor ERP Accounts Receivable Transaction Hierarchy”.

No mater what type of invoice (Adv Billing, Shipment, Deferred Revenue, etc…), the hierarchy for the AR Acct is always:

image

And if we’re taking a poll, I’d go with using the Customer context GL Control too.

8

@rbucek is this close to what you guys just did?

9

Your company may be set up to use Product Groups. If so, check the GL Accounts associated with them. That solved our similar issue.

10

Brenda, Product Groups will help with the Sales and COS but not the AR Account. Customer G/L Control is the only way to separate AR accounts.
Generally as a rule, you would use a Trade account and an Intercompany account. Intercompany customers would have a GL control pointing to the intercompany AR account and the others would have no control and use the company default AR G/L Control.

11

I appreciate the responses, however I really do think that making a custom posting rule is what our finance department is looking for. I navigate to GL transaction type maintenance, and then to the Post Invoice Amount to Accounts Receivable rule. How would I create a conditional in here to specify what account to use for each part number?

image
image1322Ă—970 50.3 KB

There is a long list of items under variables here, but none of them are part number.

12

Yes, the Finance department is often inefficient with their reporting. “Put everything in the Journal!”

This is a mistake and really makes the GL unmanageable. If the purpose is to get a report of receivables by part, it would be far easier to do so by altering the Open AR report.

I understand, it’s the only tool they use but not everything is a nail. They should justify the cost of not just the modification but the testing and checking at every update.

13

And all the time “fixing” it when it does what they asked for, but not what they really want.

1 Like
14

Hi Scott Jenkins,
You can map the GL control code in the customer maintenance with the required receivables account
image

For the lease customer AR account may be “10001”
For other customer AR account may be “10002”.

15

Which customer GL Control is used when the Sold to is the Main Customer Acct (without a GL Control), and the Bill To is the secondary customer acct (with the special GL Control)?

16

Hi guys,

Thanks for the ideas (I work with @SJenkins). Here’s the back story after speaking with our cost accountant & director of finance: Our business is complex (aren’t they all?). We do in-house financing with leases and promissory notes, and they are treated differently than “normal AR” for income recognition and also our financial auditors.

Our current process is to sell a building, invoice internally (but not send to customers), write off adjustment against the original invoice choosing the Lease GL account, then invoice customers monthly based on an amortization schedule.

The monthly misc invoice can have lines for Operating Leases, Capital Leases, or Interest (PartNum’s OL, CL, and INT), which the customer pays. Since Epicor has everything goes into the one AR account bucket, we need to split it out.

Our current process uses reports that identify these transactions, and then manual journal entries are made to get the GL to match the reports, which is a tedious monthly task.

What the finance department wants is to have the invoice lines for PartNum OL to go to AR account 10606-##-### and CL and INT to go to 10603-##-### automatically.

Preferred option would be to have those lines post to the correct account automatically, which seems like it’s posting rules. Since the GL Transaction Type Maintenance doesn’t list PartNum as a rule variable under AR Account, would I need to create a UD field that syncs with the PartNum?

image
image1074Ă—827 67.5 KB

Less preferred option would be to use multiple customer accounts. Someone would have extra work to create each additional customer, and then change it on the order. Many are global customers too, add linking them. Our customers change their mind about financing on make direct jobs up until the building ships. We’d need to change customers easily w/o a lot of extra work (should be as simple as changing the CustID on an open SO’s, no?). We already do that for internal invoicing (R&D, Maintenance).

Non-starter: Product Group have sales and COS, but don’t have the AR GL (from @CTCharlie)

I’m not a finance guy, but I’m learning…

17

Some more questions as I digest this… Can we just add the AR Context to Part, and have it available for setting the GL for AR transactions?

I also just learned about the finance lookup tables. Our cost accountant took the Advanced Finance Tips and Tricks 2 - Lookup Tables in GL by Patricia Aparicio at Insights last year.

Can I use that to transform the GL?

Help has a good section too:

Example(s)

A user assigns a user-defined property color to parts and needs to account for parts differently depending on their color. A lookup table can associate color codes with accounting segments or accounts. A GL transaction type revision (and an underlying posting rule) receives a color code as a posting code and uses it to retrieve a corresponding accounting segment directly from the lookup table. This saves the work of defining the association in the posting rules.

Lookup Tables

Lookup tables map database field values to both segment and account values. You can then modify or create posting rules which use lookup tables.

The tables define segment and account values for journal details based on values which generate in a business transaction, which provides the transformation of the source business transaction data into target GL transaction data.

18

@askulte I believe it’s hard to post to different AR account based on the part number even with the posting rule change. AR account (one-line entry in GL), which is a summary of sales + tax + misc. charge. Basically, with the posting rule, we can manipulate the GL accounts/segments but not the amount. In your scenario, you are trying to split the amount

19

The lookup tables sound like a way to reduce the number of GL Controls one would have to define.

For example, if you use Part Class GLC’s for Cost of Sales. The GLC could only be shared among PC’s if every context’s account was the same. Any difference between GLC context accounts requires a new GLC. If the COS context was the only acct that differed, you could make just one GLC, and then use the Lookup table to replace the acct for the GLC’s COS context.

Are they something specified in posting rules (i.e. a custom rule would be required just to use them)? Or do the posting rules already use them, but since none are defined, they do nothing?

20

@askulte. You can try the below option:

  1. Add new AR Account Context in Product Group Control Code and attach required AR Account
  2. Copy and Paste posting rule Extended Price Amount Regular Invoice, Part and Credit Memo, Part twice (for debit and credit accounts)
    image
  3. Leave the standard one as it is and it should post to Sales Account
  4. In the second posting rule, get the AR Account context from Product Group and posted to Debit Account
  5. In the third posting rule, get the AR Account from SoldTo Customer (its already available at a line level) and post to the credit account. As you have mentioned that it’s for internal company, I hope Bill To and Sold To will be the same.
    This is kind of mirror accounting where posting to one account splits to debit and credit.
    The final result will be :
  6. Default AR Account - Debit $1000
  7. Sales - Credit $1000
  8. Part specific AR Account Debit $1000
  9. Default AR Account - Credit $1000

Execute second and third sales posting rule only if AR Account Context is available in the Product Group