How to calculate split burden for concurrent jobs

We’re using a custom webpage to end job activity and we do so with a REST call. One of the fields on the LaborDetail in the rest call is BurdenHrs.

Normally when a single job is being performed, we can do LaborHrs = BurdenHrs.

But when more than one job is logged into at the same time, how can we calculate the correct burden hours, especially when the jobs aren’t necessarily started and stopped at the exact same moments? How does Epicor calculate the BurdenHrs with multiple jobs?

Is there an endpoint or custom method we can use to calculate the correct burden hours for a job so we can pass that value in the REST payload to end the production activity?

Conceptually I think this needs to happen in pseudocode

Foreach minute of the job's labor {
     Run a query and see how many distinct job labor were open at that minute
     Divide 1 minute by the count calculated above 
     Add the result of that to an accumulator
}
At the end of the loop we should have burden hours

I wonder if there is slick way to do that in an SQL query.

1 Like

What are the Burden settings on your Resource Groups/Resources?

We’re using these settings.

image

However, we’re not using Epicor’s MES client. We’re using REST, so we’re dictating the fields on the End Labor Activity ourselves so we need to pass the field in our payload after we’ve determined what we want it to be.

Ok, are you using the UpdateExt method? If you are, you can put whatever you want in there as none of the other methods will fire. I would ask your Accounting department what they want to use for a Burden.

1 Like

I think his point is that they’re trying to manually calculate the Burden to match what Epicor MES automatically calculates when Split Burden is active.

@smason - Are you using the same methods that MES calls when labor is started and ended or are you creating a LaborDtl and updating it with the LaborSvc methods? I would try to use the same method calls MES uses so the burden hours are calculated automatically. For manually calculating the burden split, I think the most straightforward way to calculate this would be like you said, where you essentially iterate through each minute between the ClockInMinute and ClockOutMinute and determine how many “splits” that minute should have.

However, you will need to include active labor transactions in that as well or else the first labor details you clock out of will not split burden correctly.

I just tested using the REST LaborSvc.UpdateExt method (PATCH to /LaborDtls) that if you set EndActivity to true and RowMod to “U”, Epicor will automatically split the Burden and/or Labor Hours based on its MES logic.

2 Likes

I’m tried to use the UpdateExt but it’s erroring with the message "a valid job number is required". Which of course isn’t the real error message because the job number I’m providing is the same one in the return payload I just got with GetDetail method. So something else in the black box of the BO is having trouble with the payload.

The endpoint I’m able to get to work is the PATCH /api/v1/Erp.BO.LaborSvc/LaborDtls(‘COMPANY’,123456,098765) but for that one it doesn’t auto calcuate the burden across the jobs, but it will set burden to whatever we set it to in the payload. Btw, if I set EndActivity : true against that endpoint it just crashes and doesn’t do anything rather than auto calculate.

So I’m stuck trying to develop an SQL query to let me calculate the burden.

I’ve got a query that can do the calculation… I think. Since we want to be doing split burden for everything this would work in our situation.

The ? parameter variables at the top are something that I will provide to the query when I’m running it. At the very bottom it spits out the BurdenHrs.

If any of the SQL wizards on here have any ideas let me know. It was quite an academic endeavor to come up with this and I definitely levelled up my SQL.

DECLARE @Company AS NVARCHAR(8) = ?
    , @EmployeeNum AS NVARCHAR(8) = ?
    , @LaborHedSeq AS INT = ?
    , @LaborDtlSeq AS INT = ?
    , @EndDateTime AS DATETIME = ?
    , @Shift AS INT = ? 
    , @BurdenMinutes AS DECIMAL(10, 2) = 0
    , @JobsOpenThatMinute AS INT = 0
;

DECLARE MinutesJobsCursor CURSOR FOR

    -- List of all minutes in yyyy-mm-dd hh:mm:ss from the start of the job until @EndDateTime
    WITH cte_minute_series (MyMinute) AS (

            SELECT CONVERT(DATETIME, CONCAT(ld.ClockInDate, ' ', ld.DspClockInTime)) AS MyMinute

            FROM dbo.LaborDtl AS ld WITH (NOLOCK)

            WHERE ld.Company = @Company
                AND ld.LaborHedSeq = @LaborHedSeq
                AND ld.LaborDtlSeq = @LaborDtlSeq

        UNION ALL

            SELECT 	DATEADD(MINUTE, 1, MyMinute)

            FROM cte_minute_series

            WHERE MyMinute < @EndDateTime
    )

    -- Gets a result set where each row is a count of open jobs this employee was in at each minute of the time period defined by the cte above
    , cte_jobs (MyMinute, OpenJobCount) AS (
        SELECT ms.MyMinute
            , COUNT(ld.JobNum) AS OpenJobCount

        FROM cte_minute_series AS ms

            INNER JOIN dbo.LaborDtl AS ld WITH (NOLOCK)
            ON (ld.DspClockinTime <= FORMAT(ms.MyMinute, 'HH:mm') AND
                ld.DspClockOutTime >= FORMAT(ms.MyMinute, 'HH:mm') AND
                ld.ClockInDate = CONVERT(DATE, @EndDateTime) AND
                ld.Company = @Company AND
                ld.EmployeeNum = @EmployeeNum AND 
                ld.LaborType IN ('P', 'S'))

        WHERE 
			-- Don't Count Breaks or Lunch
			NOT EXISTS (SELECT *
						FROM erp.ShiftBrk AS breaks WITH (NOLOCK)
						WHERE breaks.Company = @Company 
							AND breaks.Shift = @Shift 
							AND FORMAT(ms.MyMinute, 'HHmm') >= breaks.DspBreakStart 
							AND	FORMAT(ms.MyMinute, 'HHmm') <= breaks.DspBreakEnd)

			AND NOT EXISTS (SELECT * 
							FROM erp.JCShift AS lunch WITH (NOLOCK)
							WHERE lunch.Company = @Company 
								AND lunch.Shift = @Shift 							
								AND FORMAT(ms.MyMinute, 'HHmm') >= lunch.DspLunchStart
								AND	FORMAT(ms.MyMinute, 'HHmm') <= lunch.DspLunchEnd) 

        GROUP By ms.MyMinute
    )

    -- The result set of cte_jobs is for the MinutesJobsCursor
    SELECT OpenJobCount
    FROM cte_jobs


-- Open the newly minted cursor and loop through it
OPEN MinutesJobsCursor;
FETCH NEXT FROM MinutesJobsCursor INTO @JobsOpenThatMinute;
WHILE @@FETCH_STATUS = 0

    BEGIN
        -- Accumulator for Burden minutes. Add the correct fraction of a minute based on how many jobs were open that minute
        SET @BurdenMinutes = @BurdenMinutes
                             + ( 1 / CONVERT(DECIMAL(10,2), COALESCE(NULLIF(@JobsOpenThatMinute,0), 1)) )
        FETCH NEXT FROM MinutesJobsCursor INTO @JobsOpenThatMinute
    END;

CLOSE MinutesJobsCursor;
DEALLOCATE MinutesJobsCursor;

-- Finally divide the minutes by 60 to get the BurdenHrs that will go onto the LaborDtl Record
SELECT CONVERT(DECIMAL(10, 2), (@BurdenMinutes / 60)) AS BurdenHrs

I wonder if someone wants to run this through the fancy SQL to BAQ tool to see what it does that might be fun.

I think it could be modified further to join in the resource group of the job and only count jobs that are split burden but that’s an exercise for another time / place.