Bill of Jobs Table

[Oso Rojo]

This is the magical table that links all the component jobs into a tree structure. Thanks to engineering for some help on this one, I did not find it on my own!

These are the details of the fields as I know them. Please add any information I have left out!


Bill Of Jobs
Generic Description - The rows in this table represent component jobs and give the added information to define the job's location in the tree.

Parent_Job - This is the Job ID of this jobs parent.
Component_Job - This job's name. This is the field to link to the Job field in the Job table, ie; Job.Job = Bill_Of_Jobs.Component_Job.
Job_Operation - ???
Relationship_Type - ???
Relationship_Qty - ???
Manual_Link - ???
Last_Updated - Date and Time this record was lasted updated by JobBoss.
Root_Job - The top level Job in the tree. This appears to be a duplicate of the Job.Top_Lvl_Job field.
ObjectID - GUID for this Bill of Job.
Job_Operation_OID - ???
Root_Job_OID - ??? GUID of the Top Level Job?
Parent_Job_OID - GUID for the Parent Job to this Job.
Component_Job_OID - This job's GUID

If you know any of the fields I left with question marks, please fill in what you can.

[andrerembert]

I haven't used this one much and haven't seen it used in many other out of the box queries so I've been using the code below:

Code: Select all

Select
	Child.Job
	, Child.Order_Quantity
	, Child.Shipped_Quantity
	, Parent.Customer
	, Parent.Job [Parent Job], Parent.Part_Number AS 'Parent Part Number'
	, Parent.Order_Quantity AS 'Parent Ordered Quantity'
	, Parent.Shipped_Quantity AS 'Parent Shipped Quantity'
	, Parent.Status [Parent Status]
From EMT_Metals.dbo.Job Child
	Inner Join EMT_Metals.dbo.Job Parent ON Parent.Job = Child.Top_Lvl_Job
	

This is looks at only the Top_Lvl_Job field because I found when using some other fields, I wasn't getting consistent results. If the Bill_Of_Jobs allows for a better hierarchical view, that'd be super because this forces a lot of self referencing. It's pretty fast but, we also have a pretty small db.

The functionality we were originally looking for is, sales wanted to be notified when certain jobs shipped and the first operation had at least 5% started or the operation was complete. So, the db view is:

Code: Select all

ALTER View [dbo].[Tracked_Jobs_Status]
AS
With AllOpenJobs AS
(
Select
	Child.Job
	, Child.Order_Quantity
	, Child.Shipped_Quantity
	, Parent.Customer
	, Parent.Job [Parent Job], Parent.Part_Number AS 'Parent Part Number'
	, Parent.Order_Quantity AS 'Parent Ordered Quantity'
	, Parent.Shipped_Quantity AS 'Parent Shipped Quantity'
	, Parent.Status [Parent Status]
From EMT_Metals.dbo.Job Child
	Inner Join EMT_Metals.dbo.Job Parent ON Parent.Job = Child.Top_Lvl_Job
	Inner Join EMT_Metals.dbo.User_Values U ON U.User_Values = Parent.User_Values
	Where U.Decimal1 = 1
), ReadyJobsIncludingSubComponents AS
(Select 
AOJ.[Parent Job] AS Job, AOJ.[Parent Part Number] AS 'Part_Number'
, AOJ.Order_Quantity
, AOJ.[Parent Status] AS 'Status'
From AllOpenJobs AOJ
Inner Join EMT_Metals.dbo.Job_Operation JO ON JO.Job = AOJ.Job
WHERE JO.Sequence = 0 
		AND (((JO.Est_Total_Hrs * .05) <= (JO.Act_Run_Hrs + JO.Act_Setup_Hrs))
			OR
			(JO.Status = 'C'))
Group By AOJ.[Parent Job], AOJ.[Parent Part Number]
, AOJ.Order_Quantity
, AOJ.[Parent Status])

Select
		AOJ.Job, AOJ.Part_Number, AOJ.Order_Quantity
		, AOJ.Status
		, D.Promised_Date
		, D.Shipped_Date
		, D.Shipped_Quantity
	From 
	ReadyJobsIncludingSubComponents AOJ
	Inner Join EMT_Metals.dbo.Delivery D On D.Job = AOJ.Job

And then in an application that polls the db and sends out the email via link is:

Code: Select all

		Dim SearchResultsTable As New DSVendorSearchTableAdapters.Tracked_Jobs_StatusTableAdapter
		Dim ShippedPartNumbers = From itm In SearchResultsTable.GetData
								 Where itm.IsShipped_DateNull = False AndAlso DateDiff(DateInterval.Day, itm.Shipped_Date, Now) = 0
								 Group itm By itm.Part_Number Into Jb = Group


		If ShippedPartNumbers.Count = 0 Then Exit Sub

		Dim strShippedNotice As New StringBuilder

		For Each itm In ShippedPartNumbers

			Dim UnshippedPartNumbers = Aggregate itms In SearchResultsTable.GetData
									   Where itms.Part_Number = itm.Part_Number AndAlso itms.IsShipped_DateNull = True
									   Select itms.Job Distinct
									   Into Count

			Dim totalShippedQty = SearchResultsTable.GetData.Where(Function(b) b.Part_Number = itm.Part_Number).Sum(Function(b)
																														Return b.Shipped_Quantity
																													End Function)

			Dim unshippedQty = SearchResultsTable.GetData.Where(Function(b) b.Part_Number = itm.Part_Number).Sum(Function(b)
																													 Return b.Order_Quantity - b.Shipped_Quantity
																												 End Function)


			strShippedNotice.AppendFormat("Part Number <b>{0}</b> has shipped {2:N0} units today. There are currently {1} tracked jobs in EMT&rsquo;s inventory remaining to be shipped for this part number:", itm.Part_Number, UnshippedPartNumbers, itm.Jb.Sum(Function(l) l.Shipped_Quantity))

			SearchResultsTable.GetData.Where(Function(b)
												 Return b.Part_Number = itm.Part_Number AndAlso
								  b.IsShipped_DateNull = True
											 End Function
			).OrderBy(Function(b) b.Promised_Date).ToList().ForEach(Sub(b)
																		strShippedNotice.AppendFormat("<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Job #<b>{0}</b> is scheduled to ship {1:N0} units on {2:d}", b.Job, b.Order_Quantity, b.Promised_Date)
																	End Sub)

			strShippedNotice.AppendLine("<br /><br />")

		Next

		Dim ee As New EMTEmailer
		ee.AddToAddress("super awesome@emtmetals.com", "Andre Rembert")

		ee.SendOutboundEmail("Tracked Part # Shipment Notification", strShippedNotice.ToString)

It's not the cleanest approach in the world but I like using LINQ to make better sense of what's happening in the db vs all the SQL that'd go into determining the same data. Also, I use separate LINQ statements, not taking advantage of the deferred execution but that's because this is still in testing and this was needed in a few hours.

[Oso Rojo]

My only use of this table is to redraw the tree structure of component jobs. Basically pulling all the components of a top level jobs with the link data from this table. The results are used to fill a TreeView control on a web page. The application was so management could walk through different levels of a job and see labor and materials billed to those levels.