Unsure how to model this data

ERP Epicor ERP 10
1

Hi everyone,

I’m wracking my brain trying to find a way to provide receipt data to a user. It seems straight forward but I’m struggling to thing about this/develop it dimensionally.

Business requirement: User wants to use Phocas to see the quantity of parts we received in by PO number. Additionally, she would like to be able to filter by receipt date.

The problem I’m running into: The only 1:1 relationship with receipt date is pack slip (as far as I know). And most of our large POs have multiple pack slips. Further complicating the issue is “received quantity” is by PO release.

Question: how do I model this data when it all seems to exist as different granularity?

Misc: I started with the EDA-supplied script f_Purchasing and modified it to include Receipt Date. This works except for the POs which have multiple pack slips and therefore multiple receipt dates. When there are multiple receipt dates for a PO, the received quantity is duplicated and double-counted.

The tables I’m primarily interested in are ERP.RcvDtl and ERP.PORel. Hopefully I’m missing some link between the two which will solve all my problems!

Thanks in advance,
Kellan

2

What is the user ultimately wanting to find patterns in and how will that impact a decision? We tackled a similar problem looking for expanding and contracting lead times. We used transactional data for order date and receive date for both closed and open PO’s. We then bucketed lead times by weighted average of spend in Excel.

image
image1740×593 105 KB

3

Good question.

We perform bulk receipts from suppliers and want to know how many of each part we received (and when we received them). Users typically review this information by PO number because that’s how they’re tracking receipts by particular suppliers.

4

I figured it out. I had to take a step back from the existing model supplied to me by EDA consultants (f_Purchasing).

Instead, I went back to the basics and identified what fields I needed, established the granularity and then built out a model using an ER diagram tool. Starting simple was the key.

image
image1170×467 61.5 KB