Lot/batch report

Hi Kristian,

very tricky question! But a good one!
First I like to mention that most of your requests are from totally different “datasources”
(ORACLE Tables and Views). Some of them, you can combine (i.g. make Joins in ORACLE or MS SQL DWH Databases), some of them are not “joinable”.
At least to my knowledge.
All your data has something in common. A LotBachNo.

I think your approach would be better if you split it into scenarios.
So one scenario is:
What kind of material/products/etc. are we buying? What part of these PurchaseParst are with or without a lotbatchno?
This is your purchase part.
From this on, you can go to the inventory part scenario and ask:
Give InventoryPart with some or one specific lotbatchno: Where are these parts?
By the way: we learned the hard way that using the data in INVENTORY_PART_UNIT_COST(_SUM) is not recommandable. You will find that mask under Warehouse Management - Financial Control - Inventory Part Unit Cost. On first glance it looks very “attractive” but it is not very “sustainable”. The moment your combination InventoryPart + LotBarch runs out of stock the data record vanish!!!

We used a special version of the ITH = INVENTORY_TRANSACTION_HISTORY instead.
These records stay “for ever”
The Join looks like:

LEFT OUTER JOIN
 sql_ifs_hlp_ITH_OESHIP                                        as HIST
ON
 COIL.CONTRACT                = HIST.CONTRACT
AND
 COIL.ORDER_NO                = HIST.SOURCE_REF1
AND
 CO.LINE_NO                    = HIST.SOURCE_REF2
AND
 COIL.CATALOG_NO            = HIST.PART_NO
AND
 CDIR.DELIV_NO                = HIST.SOURCE_REF5                -- 13.11.19

The hlp table is derived from INVENTORY_TRANSACTION_HIST2 where you can find the field LOT_BATCH_NO. The last join criteria DELIV_NO = REF5 took me half a day to figure that out.
One must know, that even when you create a CustomerOrder with a specific LotBachtNo you can always change it within “the last second” on your ramp (finding out, that your first Batch is damaged for example)

So when asking for “Where is my IP + LBNo?” go for the ITH! :-)

Now to your next part:
When you ask: Given a manufactured product, what kind of (raw)material, subproducts (with seperate InventoryPartNos) with his own LotBatchNo.
What parts were used in this LotBatch and what LotBatches do they have?

The problem here is: When asking this question, one can not know on which level your rawmaterial, subproduct, etc. is located.
For solwing this problem you must have a recursive “procedure” or a QuickReport (QR).
We had this kind of QR with an own procedure but the performance was poor because with more than 70.000 InventorParts the number of combinations gets really high.

To sum it up:
Split your scenario at least into 2 parts:
The purchase and inventory part.
When did I buy LB xyz and where is it now? When did I used the last of this batch for
a) sales b) manfacturing (what shop orders) c) scrapping / inventory counts (mysteriously gone :-)

The sales and the manufacturing part:
When did LB xyz left my company and to what CustomerOrder(Line) it is connected? (ITH!)
This COL is connected / taken from which warehouse (stock) or what ShopOrder? 

In the model we created 2019 one could even ask - e.g. selling 600 pcs of Part xyz, taking 400 from LB1 and 200 from LB2 - what were the costs producing LB1 and the costs producing LB2?

Kristian, I would love to have a good discussion between BI experts.
Just give me a short note. I prefere MS Teams but I’m good with Zoom, Google Meet or similar tools.

All the best to your project and looking forward speeking with you.

Michael
PS:
Send you a contact message via LinkedIn.

Hi Kristian,

sounds good!
I imagine, as long as you don’t need detailled cost information with the lot/batch, the lot/batch master should be sufficient.
In this structure you should be able to ask top down: Manufactured lot/batch includes which parts?
or button up: Where would or is my lot/batch be part of?

Can you provide me with the initial questions and business cases?
I’m in the BI business now since 1999 and “converting” more and more to a consultant helping people asking the right questions and align their goals e.g.
CEO goals e.g. for purchase department,
goals for the department leader (purchase),
goals for purchaser (backlog lists of open POLs, etc. I have to do today )
The last question is: What kind of report shows “are we on track or not?”

But if I get your first post right the report is not for internal use but official sites like goverment, etc.
Dangery goods, etc.
My last customer has contracts with his customers which includes: If you change one part of the recipe you must inform your customer. This led to this recursive search process.

Do you speak some german?
The document we are developing right know is for the purchase department.
It starts with top goals, asking “How would you know, when you reached your goal?”
 

If you like to read I would love to share.

BR

Michael

Hi Kristian,

the german customer just finished the first phase of his BI project and we estimated roundabout 60 to 90 days of work for the next 6 months. We started with purchase and - so we hope - will proceed with sales and finance. I’m quite shure we will come across your issue as well. So let us stay in contact.
The moment we have a script, a QR or something else, I will come back to you.

You wrote:
How much of the purchased lot/batch is still in our possession?  And subsequently: Where is it?
Who wants to know and why? :-)
Is it “Finance”, the “inventory guys”, production, or is it sales with the very indirect question:
Given the rawmaterial “on board” what are we able to produce the next months and when could I sell it?

We changed our approach more and more:
The last years we just created reports exactly they already looked like (based on the old ERP).
These days we start with “top level” questions and even ask some provocative questions like:
What will hapen, if you do not know? Will you sell less or slower, less quality? Do we have more costs? Do we have to pay fees? Etc.
You know, after two years with one customer, 250 reports later, round about 0,5 Mio Euro BI project costs, we asked “What if we had started that project different and said “no” to at least 50 to 80 reports?”
We estimated at least 150 k less of costs!

Let me ask an other question:
Do you use a DataWareHouse (DWH) and is it MS SQL?
Do you use MS Cubes? And what kind of frontend?

BR

Michael