Reporting actual costs by component.

I prepare a weekly quoted vs. actual cost analysis by customer order line. The query pulls total actual costs (Material, Labor, Burden & Service) from the [INVENTORY_TRANS] file so there is just one row per order/line. Now I'm trying to create a breakdown of costs by item component so that we can compare the actuals to our quote to see where we have misses. I've seen under Eng/Mfg > WorkOrder/Master Cost Report where it gives the top-level breakdown of costs and they tie back to the total cost by category from the [INVENTORY_TRANS] file so I have to think that information is in some database.

My questions are:

1. Where does that actual cost by component information reside?

2. Can we break down even further than top-level components if a top level component is itself an assembly?

I don't know if anyone has had experience with this before but I thought I'd check

Thanks,

Joshua

0911060553270289.pdf

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

Legacy Contributor

Thinking in tables - this might be the way to go.

WORK_ORDER ties to REQUIREMENT (tables)

The requirement table has the individual part ids.

If you have an OPERATION (or a series of them, depending on the WO) that represents a built/fabricated/assembled part, then it sounds like you are asking for REQUIREMENT to OPERATION and then REQUIREMENT to INVENTORY_TRANS and then DEMAND_SUPPLY_LINK.

Legacy Contributor

Thanks Michael! I'm checking that out now!

Joshua

Legacy Contributor

Here is what I have without costs that shows what parts go into a WO for 'PART_XYZ1234'

/*AUTH:MMKING

DATE:20160922

STATUS:KNOWN GOOD, BEING IMPROVED.

PURPOSE:Given a WO#, SHOW ME THE PARTS AND THE LEGS.

NOTES:

*/

select a.workorder_base_id AS "WORK_ORDER",

a.workorder_lot_id AS "LOT",

a.workorder_split_id AS "SPLIT",

a.workorder_sub_id AS "SUB",

a.operation_seq_no AS "SEQ" ,

a.part_id AS "PART",

b.stock_um AS "UOM",

a.qty_per AS "QTY_PER",

b.description AS "PART_DESCRIPTION",

c.resource_id AS "RESOURCE",

cast(substring(coalesce(d.bits,' '),1,7) as char) AS "PACKET OR STEP",

coalesce(d.bits,' ') AS "PACKET OR STEP DETAIL"

from requirement a,

part b,

operation c

left outer join operation_binary d

on d.workorder_base_id = c.workorder_base_id

and d.workorder_lot_id = c.workorder_lot_id

and d.workorder_split_id = c.workorder_split_id

and d.workorder_sub_id = c.workorder_sub_id

and d.sequence_no = c.sequence_no

where a.part_id = b.id

and a.workorder_base_id = c.workorder_base_id

and a.workorder_lot_id = c.workorder_lot_id

and a.workorder_split_id = c.workorder_split_id

and a.workorder_sub_id = c.workorder_sub_id

and a.operation_seq_no = c.sequence_no

and a.workorder_base_id = 'PART_XYZ1234'

Legacy Contributor

Michael,

I really appreciate you posting the query definition. Was wondering, does that code return all parts, even for sub-assemblies to you? I'm now getting what the "Work Order/Master Cost Report" shows for a work order (which is great!) but was wondering if I could get it to break down any of those items which might be assemblies into their components? It sounded like your query above does that but when I recreated it in Access I wasn't getting anything beyond the top level. Thanks again!

Joshua

Copyright © 2025 Infor. All rights reserved.