Trying to create a BAQ that lists sales by salesperson. Problem is SalesRepList can hold multiple. Help!

Epicor ERP 10
1

Hello,

I am trying to create a BAQ to list sales by salesperson. It looks like the OrderHed hold the list of salespeople in 1 column separated by a tilde (~) called SalesRepList. How can I break that up into individual reps for the BAQ? Is there any easier/alternate way to tie sales to individual salespeople?

Any ideas/help is appreciated.

Thanks,

Shawn

2

I think @josecgomez just wrestled with this recently.

3

You can do it using a CTE and it will work . However the performance is dreadful.
You are basically joining the InvcHead table to itself 5 times
So if you have 1 million invoices… that’s 5 million records

But if you want to do it I can definitely walk you through it.
We ended up writing a BPM to split that stupid ~ string into individual UD0XX records and then join that back. The BPM created the UDXX records when the InvcHead is saved and updates it as needed.

1 Like
4

I think the UDXX way is what I need. Any details you can give would be a definite help!

Thanks @josecgomez!

Shawn

5

Here is a Basic BAQ which does this using a CTE (Give it a shot if you’d like)

RepListFromIvcHead.baq (30 KB) (>10.2.100)

with [SubQuery1] as
(select
    [InvcHead].[Company] as [InvcHead_Company],
    [InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
    [InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
    (2) as [Calculated_num],
    ([Ice].entry(1 ,InvcHead.SalesRepList, '~')) as [Calculated_SalesRe]
from Erp.InvcHead as InvcHead
union all
select
    [SubQuery1].[InvcHead_Company] as [InvcHead_Company],
    [SubQuery1].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
    [SubQuery1].[InvcHead_SalesRepList] as [InvcHead_SalesRepList],
    (SubQuery1.Calculated_num+1) as [Calculated_num2],
    ([Ice].entry(SubQuery1.Calculated_num ,SubQuery1.InvcHead_SalesRepList, '~')) as [Calculated_SalesRep]
from  SubQuery1  as SubQuery1
where SubQuery1.Calculated_num < 5  and SubQuery1.Calculated_SalesRe  '')
 
select
    [SalesRepbyInvoices].[InvcHead_Company] as [InvcHead_Company],
    [SalesRepbyInvoices].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
    [SalesRepbyInvoices].[Calculated_SalesRe] as [Calculated_SalesRe]
from  SubQuery1  as SalesRepbyInvoices
where (SalesRepbyInvoices.Calculated_SalesRe <> '')
1 Like
6

We store 1 record per SalesRep in the UD04 table such that
Key1 = InvoiceNum
Key2= SalesRepID
Key3 = Sequence (1-5)

We have Post Tran Data Directive which runs every time that the SalesRepList is modified from any to any or there is a RowMod of A. We lookup and delete all the related UD04 records for that Invoice and re-create them when this happens.

Then on BAQ’s its pretty easy to Join UD04 to InvceHead on Key1=InvoiceNum

7

I think I got it but it will be a bit before I can test. Just got pulled into a different project.

If I get stuck or need clarification when I am back on it, I will post a reply here.

Again, Thank you for the quick replies and help @josecgomez!

1 Like
8

Hey @josecgomez,

Could you post your BPM?

Thanks,

Shawn

9

It won’t do you much good it is very specific to our company. What are you looking for? I can probably get the pieces you need there’s a crap of other logic we are doing that won’t help you

10

Just the basics. How did you split it in the BPM?

11 
foreach(var x in ttInvcHead.Where(r=>r.Added() || r.Updated())
{
      foreach(var sr in x.SalesRepList.Split('~'))
      {
             //Add UD record Here
      }
}
1 Like
12

We have found that the following code has worked for us, using a BAQ Calculated field to return the first rep in the list. Changing the 1 to 2 would return rep 2.

[Ice].entry(1,OrderHed.SalesRepList,’~’)