Visual Results – how to batch recalculate all sub engineering masters included on other engineering masters

In Visual Results you can create engineering masters that you then use to include on other engineering masters. To do this you open the child master and click the ‘Add Leg/Detail’ button. In this window you choose the sub engineering master and poof, you now have an engineering master that includes a sub engineering master.

While a neat feature, these sub masters can be problematic unfortunately. The issue is if you have multiple sub masters that contain one parent and you want to change the parent how do you get the sub masters to reflect the change?

This is where it’s tricky, Visual does not store enough information to know what engineering master the sub master came from. It doesn’t ‘link’ the sub master, it actually imports all the operations / requirements from the sub master into the master. The only remnant of what master was imported is the black line that says the master’s base id NOTE it does not know the Lot ID so if you have multiple masters with the same base and different lot this becomes impossible to do with 100% accuracy.

With this in mind, I was asked to update all masters that linked sub masters as my clients needed to do a large update. Fortunately they didn’t have any masters with a lot id other than 0 so I was able to link what sub master(s) each link came from. In the end this wound up being more complicated as they did override the sub masters in a number of places and didn’t want the overrides deleted.

I chose to write two queries, one that would delete all items on a master that came from sub masters, and one that would insert all items from sub masters on to masters. This proved very effective, and fast enough I was happy with it. It would of been better to write update quieries, but that gets quite a bit more complicated as I would have to update all matching items, delete all extra items no longer on the sub master, insert all items on the sub master not on the base master. All of those queries would have to know how to exclude the other situations. Deleting / Inserting worked well, was reliable and did exactly what we needed it to so that’s what this solution uses. If you wind up extending this with update queries I’d love to see your results!

Here is the query that deletes all items from a master that came from a sub master:

delete requirement from 
(
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
--If you wanted to ignore some parts this is how you would write it, note that this can cause issues if the piece_no, if you want to do all then omit the where.
where requirement.part_id not like 'X7%'
and (requirement.part_id not like 'X1%' or requirement.part_id='X1B1' or requirement.part_id='X1R1')
and (requirement.part_id not like 'X2%' or requirement.part_id='X2B1' or requirement.part_id='X2R1' or requirement.part_id='X22')

Here is the query to create all items on a master from the sub masters it includes:

insert into requirement
(workorder_type, workorder_base_id, workorder_lot_id, workorder_split_id, workorder_sub_id, operation_seq_no, piece_no, subord_wo_sub_id,
part_id,reference,status,qty_per,qty_per_type,fixed_qty,scrap_percent,dimensions,dim_expression,usage_um,effective_date,discontinue_date,calc_qty,issued_qty,
required_date,close_date,unit_material_cost,unit_labor_cost,unit_burden_cost,unit_service_cost,burden_percent,burden_per_unit,fixed_cost,
drawing_id,drawing_rev_no,vendor_id,vendor_part_id,est_material_cost,est_labor_cost,est_burden_cost,est_service_cost,rem_material_cost,
rem_labor_cost,rem_burden_cost,rem_service_cost,act_material_cost,act_labor_cost,act_burden_cost,act_service_cost,mfg_name,mfg_part_id,
protect_cost,length,width,height,drawing_file,warehouse_id,allocated_qty,fulfilled_qty,planning_leadtime,required_for_setup,user_1,user_2,
user_3,user_4,user_5,user_6,user_8,user_9,user_10,udf_layout_id,calc_fixed_scrap,wbs_code,wbs_description,wbs_clin,wbs_customer_wbs,wbs_tdc,
inherit_warehouse,location_id,due_date,dispatched,orig_stage_revision_id)
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,--r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4),--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2),--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2), --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and --this code only works for lot/split/sub of 0/0/0
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--the rest of this query is only needed if you want to exclude certain parts from the update
and r.part_id not like 'X7%'
and (r.part_id not like 'X1%' or r.part_id='X1B1' or r.part_id='X1R1')
and (r.part_id not like 'X2%' or r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22')

If you opt to exclude parts from your update you will need this query, it will tell you if your insert will fail due to operations having conflicting numbers. You will need to match the operation number to the one used on the sub master (or one not used) before you can run the insert.
Query to find parts that conflict on operation number: (this query could be cleaner, but it works)

select 
allPartsRemaining.workorder_type,
allPartsRemaining.workorder_base_id,
allPartsRemaining.workorder_lot_id,
allPartsRemaining.workorder_split_id,
allPartsRemaining.workorder_sub_id,
allPartsRemaining.operation_seq_no,
allPartsRemaining.piece_no,
allPartsRemaining.part_id,
allPartsForInsert.part_id,
allPartsForInsert.workorder_type
from 
(
select requirement.* from (
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
where requirement.workorder_type is not null
) as allPartsRemaining 
join 
(
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id as r_subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4) as one,--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2) as two,--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2) as three, --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--Update this list of ignored parts with your rules: (in this case ignored parts need to be included), delete them if you're not excluding parts
and r.part_id like 'X7%'
and (r.part_id like 'X1%' and not (r.part_id='X1B1' or r.part_id='X1R1'))
and (r.part_id like 'X2%' and not (r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22'))
)as allPartsForInsert on 
allPartsRemaining.workorder_type=allPartsForInsert.workorder_type and
allPartsRemaining.workorder_base_id =allPartsForInsert.workorder_base_id and
allPartsRemaining.workorder_lot_id=allPartsForInsert.workorder_lot_id and
allPartsRemaining.workorder_split_id=allPartsForInsert.workorder_split_id and
allPartsRemaining.workorder_sub_id=allPartsForInsert.subord_wo_sub_id and
allPartsRemaining.operation_seq_no=allPartsForInsert.operation_seq_no and
allPartsRemaining.piece_no=allPartsForInsert.piece_no and
allPartsRemaining.part_id  allPartsForInsert.part_id --It doesn't matter if they share the same part_id, they are being ignored or updated based on part id so having the same part id is good.

Here’s one last query, not strictly needed, but you may find you want it:
Query to take the rows we’re deleting, the rows we’re inserting and determine what the difference is: (once everything is run this should return nothing)

select existingRows.*, newRows.* from 
(
select requirement.*
from (
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
--Not needed if you're not excluding parts:
where requirement.part_id not like 'X7%'
and (requirement.part_id not like 'X1%' or requirement.part_id='X1B1' or requirement.part_id='X1R1')
and (requirement.part_id not like 'X2%' or requirement.part_id='X2B1' or requirement.part_id='X2R1' or requirement.part_id='X22')
) as existingRows

left join (
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id as r_subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4) as one,--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2) as two,--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2) as three, --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--Not needed if you're not excluding parts:
and r.part_id not like 'X7%'
and (r.part_id not like 'X1%' or r.part_id='X1B1' or r.part_id='X1R')
and (r.part_id not like 'X2%' or r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22')
) as newRows on
	existingRows.workorder_type = newRows.workorder_type and
	existingRows.workorder_base_id = newRows.workorder_base_id and
	existingRows.workorder_lot_id = newRows.workorder_lot_id and
	existingRows.workorder_split_id = newRows.workorder_split_id and
	existingRows.workorder_sub_id = newRows.subord_wo_sub_id and
	existingRows.operation_seq_no = newRows.operation_seq_no and
	existingRows.piece_no = newRows.piece_no 
where existingRows.workorder_type is null or newRows.workorder_type is null
About these ads
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: