Epicor - REST API - STORED PROCEDURE

ERP 10
1

Hello,
my client will move epicor premise to epicor cloud. the big problem is we have a lot stored procedure based on the epicor database. now, we have to call rest api. I don’t know how to convert stored procedure into an efficient way to use LINQ to join multiple lists. Some stored procedure is very complicated not just a simple join. it includes cursor, with CTE and etc.

any ideas will be very appreciated.

thanks,

Eddy

2

BAQs maybe?

You can invoke a BAQ via rest.

BAQS have CTE capabilities fully built into them.

3 Likes
3

We were questioning the same thing. We came to the same conclusion unfortunately.

We use SPs for a couple of enhanced production shortage reports and putting it through BAQ just takes way to long.

We’ll need a complete redesign to get it into BAQs.

4

Thanks,
but BAQ has performance issue. if the result is more than 1000 records, BAQ failed to bring.

BAQ is not the best solution. any other idea?

thanks,

Eddy

1 Like
5

The 1000 records limit is in the BAQ designer. That goes away when you call that in other places (like a dashboard)

2 Likes
6

Make a rest call to the BAQ and tell me what happens. What do you see in the returned JSON, only 1000 rows?

Or is it timing out? I sense it’s what Brandon said.

1 Like
7

that’s true. i will try to use BAQ.

thanks,

Eddy

8

Hello Utah,
for the baq, it give good number of records. now, i have one sql syntax need convert to baq
row_number() over (partition by table.field, table.field etc order by table.field asc)
it is to select top n record by group by. i don’t know how to do that in BAQ. also, I need calculate the running total. don’t know how to do it in BAQ either.

any ideas,

thanks,

Eddy

9

There are many posts on here that show you how to do row number and partition in a BAQ. I don’t have time to explain it right now otherwise I’d dhow you or find the posts.

2 Likes
10

Put the the over partition in a calculated field and then in subQuery Criteria change the table to calculated and your field

11

And that can be overridden there as well.

1 Like