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.
Bill of Jobs Table
-
- Posts: 2
- Joined: Tue Apr 18, 2017 2:08 pm
Re: Bill of Jobs Table
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:
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:
And then in an application that polls the db and sends out the email via link is:
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.
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
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
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’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 /> 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)
Re: Bill of Jobs Table
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.
Who is online
Users browsing this forum: No registered users and 2 guests