Solved

Part Cost Bucket vs Part Cost Bucket History

  • 25 February 2021
  • 8 replies
  • 552 views
S
Rank
Badge +2

Could someone explain to me the difference in the values found in the various Part Cost Bucket tables? 

  • part_cost_bucket_tab
  • part_cost_bucket_one_tab
  • part_cost_bucket_history_tab

I am attempting to construct a Bill of Materials for a data warehouse, and these three tables seem to carry slightly different values.  For the purposes of demand calculations in the data warehouse, I need to be able to leverage a Bill of Materials as of any point in time, so I was hopeful that the history table would include everything I need -- but there are some examples of Part associations in the other two tables not available there.

I could perform a simple union of these tables in my SQL query in order to handle for any missing data -- but that feels like a weak solution that could generate more confusion than it alleviates.

Any guidance others could provide me on the nuances between these tables would be greatly appreciated.  Thanks!

icon

Best answer by ShawnBerk 25 February 2021, 05:40

View original
This topic has been closed for comments

8 replies

S
Rank
Userlevel 7
Badge +28

In the simplest explanation:

 

part_cost_bucket_tab  - contains one set of records for every cost set that exists per site, so for us, this one table will contain something like 14 cost sets times 21 sites worth of information - none of it is the current actual cost unless you’ve just changed it there.  These cost sets are all changeable at any time

part_cost_bucket_history_tab - contains historical records of previous revisions of the structure and cost but not the current revision structure

part_cost_bucket_one_tab - contains the set of records that is for Cost Set 1, which should be the current standard cost set of costs.  This should be viewed as actual and current, or at least up till the last time it was updated.

 

The flow is creation of cost information in Cost Sets 2 through whatever - these are changeable and updateable - these appear in part_cost_bucket_tab.  When one of those cost sets is copied to Cost Set 1, you get the version of it that is made live and affects inventory value, standard costs of items, etc - this is part_cost_bucket_one_tab.  The history version will only get updated when the current version or revision of something is moved down from current into history.

 

All things being equal, if you want real and current, you want to concentrate only on the _one_ version.  If you are looking for something else interim or preliminary, then possibly you want something out of just the bucket_tab version, but you need to know which Cost Set you are reviewing and why. 

Can’t think of a good reason to go into history for reporting unless you find some use for it to report comparisons of past to current.

 

You definitely don’t want a union of these three tables, nor do you really want to mix them, they mean different things in time typically.

S
Rank
Badge +2

@ShawnBerk: Fantastic explanation, and very prompt; thank you, fellow Shawn.  :)  A couple of follow-up questions:

  • I don’t care about costs for the purpose of this demand analysis, to be honest; I’m more concerned about quantities, in order to associate a top-level part to its constituent parts.  For that purpose, and based on your explanation, should I focus my extract on part_cost_bucket_one_tab?  Or is there a different table I might want to use instead?
  • I understand your point about not unioning all three of these tables.  However, based on your description, would I be able to safely union one_tab and history_tab in order to generate a full historical record?  Asked another way: is the history table a record of previous structures for Cost Set 1?  (The history table doesn’t carry a COST_SET column, which is why I ask.)

Thank you for your help!

S
Rank
Userlevel 7
Badge +28

@ShawnFactorial 

If you aren’t concerned about costs and are looking at quantity and structure, then yes, I think there are better tables to use.  I would start with the Manuf_Structure group of tables and concentrate there as it contains all levels and revisions of the structure, so if you are looking to see changes from one engineering revision to another, you should be able to pick that up better from that table or group of tables.  The Part Cost Bucket group of tables are really about recording and managing cost first and foremost, not so much the relation of the top-level parts to child items.  They just come along for the ride in those tables.

Yes, the only items you’ll find in the history_tab if you stick with the cost tables is information that was once in the one_tab.  That is why Cost_Set isn’t in that table, it has no relevance because you can’t go from Cost Set 4 lets say direct to history.  It would have had to be copied to Cost Set 1 first, then the old Cost Set 1 information would move down to history.

S
Rank
Badge +2

@ShawnBerk: This is all super helpful, thank you so much.

Last question, I promise.  :)  The appeal of part_cost_bucket_one_tab is that it has already “exploded” the Bill of Materials for us: a clean look of top-level, parent, and component parts, including the “level 0” of the part being used to make itself (if applicable), allowing a user to filter for a top-level part and see all descendent parts at a glance.  Is there a similar such table or view amongst the manuf_structure group of entities that does this for me?  I’m certainly capable of crafting the SQL necessary atop manuf_structure_tab to explode it myself -- but it’s not trivial, and I’d prefer to avoid reinventing the wheel if it’s already available for my purposes.

M
Rank
Userlevel 6
Badge +12

Very good eplanation from Shawn (as always)

Be careful about performance here. The tables are large. In fact PartCostBucketHistory is often one of the largest table in the entire IFS Applications for a manufacturing customer. If you can live without the historical comparison, but still have easy accees to all components of a top part, without using “Start With … Connect By” clause, I would go for part_cost_bucket_one. In fact, if you can access the view Part_Cost_Bucket_One3 you don't have to bother about all cost buckets.

Do not run your queries against this table/view during peak load time… Well perhpas I should say “avoid”

Cheers,

Mats

S
Rank
Userlevel 7
Badge +28

@ShawnFactorial 

Product Structure > Multi Level Structure is the view I use all the time for this function.  In fact, I would want someone to justify to me why this view doesn’t work for them before I would spend the time to build something that lives outside of IFS.  However, given your initial statement regarding building a data warehouse, I can understand if you have a larger need to join non-IFS information in the warehouse.  For us, we have a global PLM system that is interfaced directly to IFS, so we view Engineering information directly in the PLM system and move backwards and forwards using the PLM UI if we need to look at previous revisions.  Similar information is carried over to IFS and we can view the current or historical information in every fashion we need from the Product Structure view.

S
Rank
Badge +2

@ShawnBerk: I see multi_level_structure_tab in the back-end, but in my case, it’s blank; I have assumed that this entity, like many in IFS, is only populated when a user elects to view the data in question.

My use case is to calculate demand as of various moments in time, in order to analyze improvements in inventory management over time.  That requires being able to associate Customer Orders with a Bill of Materials as of that moment in time to see how much material one would need to fulfill that demand.  Thus, all these questions about historical data I’ve been throwing your way.  :)

In any case, you’ve been extraordinarily helpful.  I’m marking your initial reply as the best answer.  Thank you!

S
Rank
Userlevel 7
Badge +28

OK thanks, hope you can tie things in the way you need to.  Glad you found the info useful.

Terms & ConditionsCookie settings