I agree with Kristian, this would be a very useful report which I also was expecting. There is one customer who is demanding this. 

BR, Thushan.

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 Michael.

Thanks for your response. We have made initial attempts following the ITH approach. However, our conclusions are that this is not the right way as we have very deep structures in our manufacturing and many (up to eight) levels of shop orders which means that we will track our purchased batches all the way up through all these levels. 

The lot batch master holds almost all the information that we need, so I think this is the right place to start. 

My approach would be something like this:

1.  Based on the tables of the lot/batch master, identify all top parts containing the lot batch and the lot/batch quantity each of these top parts contains.  The top parts can be either Lot/batch controlled or serialized. 
2. Figure out where the top part or lot/batch quantity itself are located and add it to the right column.

If you succeeded with the ITH approach on mulitlevel structures I’d love to see how you got through with that!

BR Kristian

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

We are importing raw materials for production under Inward Processing rules. This means that we have to answer this basic question:

How much of the purchased lot/batch is still in our possession?  And subsequently: Where is it? That is, as rawmaterial in inventory, as sub-assembly or final product in inventory or as WIP issued to a shop order (as rawmaterial or included in sub-assembly).

Hence, a bottom-up analysis is needed. 

I would think that this would be a quite basic report many IFS customers would need and I’m puzzled we don’t find anything helping us in IFS standard.

Br Kristian

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

Hi TERKRTI,

There’s a newly introduced functionality in IFS Cloud 21R1 

• “Lot batch where used” : used to view in what lots the specific lot batch has been used. The tree navigator allows you to follow a structure to the top. The information displayed is inventory transactions.

• Part Lot Batch: Used to maintain and track information about the individual lots/batches that are part of the tracked structure.  It gives us additional visibility to the level of sub lots if they are split into sub lots

The lot-tracked part uses Order-Based lot tracking, and is set to Sub Lots Allowed. In this case, the lot/batch number for the master order is generated using the normal order-based lot tracking logic, and reserved to the order. All other orders in the batch balance group have their lot/batch information created based on the rules described in the batch balance group part cases below.

The lot-tracked part uses regular Lot Tracking, and is set to Sub Lots Allowed. The master lot/batch is reserved to the order. The other orders in the batch balance group have their lot/batch information created

Detailed History of lot batch numbers: Includes transaction date, transaction description, order types and issued/received order numbers.

Hope this information helps.