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

Visual Reporting – Gupta Report Builder

For those of you that have used it you’ll know that Gupta Report Builder is a real pain to use. If you have to use it you should check out these documents to help you through:

Gupta Report Builder Starter Guide: http://support.unify.com/Docs/ReportBuilder/WhitePapers/ReportBuilderStarterGuide.pdf

Report Builder Guide to Creating Reports:
http://support.unify.com/Docs/ReportBuilder/WhitePapers/ReportBuilderGuideToCreatingReports.pdf

Report Builder Documentation:
http://support.unify.com/Docs/ReportBuilder/report.pdf

I can’t take credit for this, thanks for the links goes to visual nuggets. You can find the same links on their site here:
http://www.visualnuggets.com/visual_nuggets/2011/02/resources-for-gupta-report-builder.html

I’ve copied them because every so often links go dead, and I’d like to keep this as a backup reference for myself!

Infor ERP – Visual Enterprise

Infor ERP – Visual Enterprise is something of a niche product.  Try searching the internet for it and you’ll see what I mean.  There are very few hits and even fewer sites that offer help documentation on how to develop for it.

Well, finally I found a blog written by a fellow that seems to know quite a bit about Visual, check it out.

One of my favourite articles is where he details how to easily decode the BINARY fields in Visual tables.  He uses views for his solution so it requires you to write a query that joins the view to the table.

I extended his solution somewhat, though mine is not for the faint of heart.  There is a great feature of SQL server called User Defined Fields (UDFs).  To use UDFs to get decoded Binary fields in Visual without having to join to another table follow these steps:

1) Open the Visual database and then open the table you want to ‘fix’ in design mode. (that’s right, we’re changing the design of the Visual tables themselves.  I told you this wasn’t for the faint of heart.)
2) Add a new column to the END of the list.  I called mine ‘BITS_PLAIN’
3) Don’t give it a datatype, instead (using Microsoft SQL Server Management Studio) expand the ‘Computed Column Specification’ in the bottom and enter the following formula:
(convert(varchar(5000),convert(varbinary(5000),[BITS])))
This will convert the first 5000 bits to 5000 chars for reading.  If you are running on a newer version of SQL server you should be able to use this:
(convert(varchar(MAX),convert(varbinary(MAX),[BITS])))
Which won’t truncate the data arbitrarily.
4) Save your changes.

The new SQL won’t slow down queries that don’t include that column in the select statement, but it’s great for standardizing where to get the binary data in your applications.  Unfortunately this doesn’t solve the issue of writing the data back to the database as you can’t write to the UDF.  More on that in a future article.