I’m working with the Scheduled Shipments SSRS report, in Epicor 10 cloud. It using the object OMR50 which I know isn’t a real table in the database but joins together many tables. Is it possible to find what table/fields this is pulling data from?
I need to pull data from JobHead, OrderHed and OrderDtl which I believe OMR50 also pulls data from but doesn’t include all the fields. The query gets messy with some duplicate records when joining the same table twice.
Thanks!
Your best bet is to open a SQL Profile session and see what SQL queries are being executed by the report.
If you want to see the structure of OMR50 temp table, use below query
select * from Ice.ZDataField where DataTableID = ‘OMR50’
If you are Epicor Cloud - Public Direct - you wont have access to the SSMS.
Your best bet is to “pretend” to add a join to one of the relationships. then look at the dropdown list of fields from the source table (OMR50).
DaveO
You are right, I missed the “Cloud” part, not sure how you can analyze much of the report besides looking at the RDL, below is OMR50 table fields
| SystemCode | DataTableID | FieldName |
|---|---|---|
| ERP | OMR50 | CustName |
| ERP | OMR50 | OrderRelNum |
| ERP | OMR50 | JobNum |
| ERP | OMR50 | Qty |
| ERP | OMR50 | PartDesc |
| ERP | OMR50 | ShipToName |
| ERP | OMR50 | WhseCode |
| ERP | OMR50 | IUM |
| ERP | OMR50 | ReqDate |
| ERP | OMR50 | OrderNum |
| ERP | OMR50 | OrderHeld |
| ERP | OMR50 | Company |
| ERP | OMR50 | FirmRelease |
| ERP | OMR50 | ShipVia |
| ERP | OMR50 | ShipToNum |
| ERP | OMR50 | PartNum |
| ERP | OMR50 | CustID |
| ERP | OMR50 | OrderLine |
| ERP | OMR50 | StagedQty |
Thanks for the response!
Is it possible to relate these fields to the actual database tables? Like I’d assume CustName is coming from the Customer table…
OrderRelNum from OrderRel
JobNum from JobHead
Qty from OrderDtl
Sometimes a field name will be preceded by the source table, like: OderRel_ShipTo. Especially if the source tables have fields with the same name (ex: on an order, ShipTo might be from OrderHed, OrderRel, or even Customer)
Actually JobNum seems to be the only field that is not used.
Most fields come from OrderRel and Customer, quantities seem to be calculated using internal libraries so I can’t say how exactly are calculated.
And the ShipTo is either the One Time ShipTo or the one assigned to the OrderRel.