Epicor provides a robust REST interface.
2 Likes
thanks, i know i can call the rest api.
now, since you raise the rest api topic, my root of the problem is how to query the cloud epicor database properly.
give you an example, i have a stored procedure. it is list for each part, how many on hand qty, how many open quotes, how many open orders, how many qty shipped, how many qty in work order and how many qty in purchase not received yet.
now, I need convert this stored procedure using cloud database. I don’t know how to do it.
also, in my stored procedure, I have running total calculation because user choose the date, on that date, what is the on hand qty.
so, no idea how to do that. that’s why I ask if I can use linked server to do that. now, Epicor force everyone move to cloud. it cause some headache to grab the data easily and fast.
regards,
Eddy
1 Like
Sounds like you could create a BAQ and get its output via REST
3 Likes
So many options.
You can query the business objects via odata, or via custom methods.
For more advanced stuff (simple stuff too), you could design a BAQ.
If you want fine grained control on the “shape” of your data, use an Epicor Function.
2 Likes
Look at us tackling the move to cloud one post at a time! Yes, OP, @eqiu , sounds like a BAQ is in order for you.
And since I just referenced this today, this might be of interest to you.
ODBC ODATA:
3 Likes
Hello,
thanks. that’s the topic I saw too. CDATA ODBC driver. i think it is the solution i can try it.
thanks,
Eddy
1 Like
Man you had to dig for that one, you joined in 2021…
Or did I ? 
1 Like
That’s why I am trying to understand how you are so plugged in and I haven’t connected with you before. Am I just losing my memory or my mind?
I apologize if we have connected, and I just don’t remember. But I read a ton of your posts and learn a ton, I feel like I would have connected with you a lot sooner.
No, I really did join in 2021 
I just had a real trial by fire here at work to get 10-11 working the way we needed it, so
I read. A lot.
I also love this stuff and have a nice case of ADHD.
5 Likes
Pretty impressive haha
1 Like
Hi Eddy,
I have the same problems like you. I have stored procedures on my sql server and now that we transition to SaaS I am scratching my head how to convert the reports. Did you finaly try CDATA ODBC? Did it work?
Vassilis
What’s special about these stored procedures and what are you using to report?
We can probably get you guided in the right direction if you can provide some
more info.
2 Likes
To give you an example, we have multiple levels of subassemblies in methods master and we use crystal reports to explode the BOM down to the leaves with raw material part numbers and check availability for each part what’s in stock, open POs, demand for other jobs and so on. Although crystal reports is currently the front end of the report, on the back end it is stored procedures using Transact SQL to loop through the BOM levels and prepare the data for the report. Kind of what you see below:
while (exists (select * from #bomtemp1 with (nolock) where lvl=@levelnum))
begin
insert into #bomtemp1
select partmtl.company, partmtl.partnum, partmtl.revisionnum, partmtl.pullasasm, partmtl.viewasasm, partmtl.mtlpartnum,
@levelnum + 1, cast(partmtl.mtlseq as varchar(8)), partmtl.qtyper, #bomtemp1.nodes + ‘-’ + partmtl.mtlpartnum
from partmtl with (nolock)
inner join #tlastrev with (nolock)
on partmtl.company = #tlastrev.company and partmtl.partnum = #tlastrev.partnum and partmtl.revisionnum = #tlastrev.revisionnum
inner join #bomtemp1 with (nolock)
on partmtl.partnum = #bomtemp1.child and partmtl.company = #bomtemp1.company
where #bomtemp1.lvl = @levelnum and #bomtemp1.viewasasm = 1 and
partmtl.partnum in (select partnum from part with (nolock) where typecode=‘M’)
set @levelnum=@levelnum+1
end
I have no idea how to migrate this kind of reports in SaaS Kinetic.
- Move from Crystal Reports to SSRS
- Create a Recursive CTE in BAQ Designer
- Report Data Definition in Epicor lets you create BAQ Reports and link multiple tables, BAQ is powerful enough to recurse 100 levels if done right in less than 20 seconds.
Overall the Report Data Definition Designer, still isnt crazy powerful for mixing out-of-the-box RDD and BAQs… But what you are asking has been done and can be done with the tools available.
In 10 years, I have NEVER done a SQL View or Stored Procedure, It is much easier to do so, but working within the toolchain always worked.
Worst case you write out the data to CSV from BAQs and have Powershell do some reporting, but I havent hit that complexity yet either.
https://www.epiusers.help/search?q=recursive%20%40hkeric.wci
Note: Hierarchy Column
3 Likes
On another note I was told by Epicor CAM that they now offer a Readonly Database with SQL Access for on-prem to read their data. I never asked for additional info.
1 Like
They do, and it is real time. You can get OBDC access. I don’t know how expensive it is.
I wouldn’t go that route unless I absolutely had to though, and even then I’d be migrating.
I think @JeffLeBert at one point had a vision that every RDD would just become a BAQ in a few years and eliminate the old RDD ways… but they do run alot of Server-Side Internal .dll logic, that is not always easy to replicate in BAQ.
1 Like
A lot of that can be done with custom code on the GetList in a UBAQ.
(LINQ etc)
I know you know that, but just noting it here for others.