Manufacturing WIP Report

(0) Share

Report

Posted on by babubaskaran@outloo...

Hello There I am trying to build a report for the detailed WIP report of Manufacturing Orders. I wanted to list manufactured order, item number, item description, PPN (BOM), Employ ID, Start date, Start time, Finish Date, Finish Time, Sequence cost, Elapsed time, First name, qtyrecvd * item cost as material cost. I have created a stored procedure and list Manufacturing Order listed with each item cost. But the problem is I am getting duplicates on this can anyone help on this. Basically each MO will have multiple line broken by Material, Labor, Overhead & Outsourced and MO status can be anything from open to closed.

*This post is locked for comments

All responses (9)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Manufacturing WIP Report

Here is one you could tweak for Material...

SELECT GETDATE() [Report Date]

, RTRIM(MOP1000.MANUFACTUREORDER_I) [MO Number]

, RTRIM(WO010032.DSCRIPTN) [MO Description]

, CASE WO010032.MANUFACTUREORDERST_I

WHEN 1 THEN 'Quote/Estimate'

WHEN 2 THEN 'Open'

WHEN 3 THEN 'Released'

WHEN 4 THEN 'Hold'

WHEN 5 THEN 'Canceled'

WHEN 6 THEN 'Complete'

WHEN 7 THEN 'Partially Received'

WHEN 8 THEN 'Closed'

ELSE ''

END [MO Status]

, MOP1000.ITEMNMBR [Part Number]

, ISNULL(RTRIM(IV00101.ITEMDESC),'') [Part Description]

, MOP1000.QTYRECVD - MOP1000.QTYSOLD [Qty in WIP]

, MOP1000.ITEM_COSTS_ARRAY_I_1 [Unit Price]

, (MOP1000.QTYRECVD - MOP1000.QTYSOLD) * MOP1000.ITEM_COSTS_ARRAY_I_1 [Amount]

, RTRIM(MOP1000.FROM_SITE_I) [Issue Site]

FROM MOP1000

JOIN WO010032 ON MOP1000.MANUFACTUREORDER_I = WO010032.MANUFACTUREORDER_I

LEFT JOIN IV00101 ON MOP1000.ITEMNMBR = IV00101.ITEMNMBR

WHERE WIPQTYSOLD = 0
Community Member Microsoft Employee on at

Like (1)

Report

RE: Manufacturing WIP Report

Here is one that might work for labor...

SELECT RTRIM(A.MANUFACTUREORDER_I) [MO Number]

, RTRIM(D.DSCRIPTN) [MO Description]

, CASE D.MANUFACTUREORDERST_I

WHEN 1 THEN 'Quote/Estimate'

WHEN 2 THEN 'Open'

WHEN 3 THEN 'Released'

WHEN 4 THEN 'Hold'

WHEN 5 THEN 'Cancelled'

WHEN 6 THEN 'Complete'

WHEN 7 THEN 'Partially Received'

WHEN 8 THEN 'Closed'

ELSE '#Error'

END [MO Status]

, RTRIM(A.RTSEQNUM_I) [Sequence No]

, RTRIM(A.RTSEQDES_I) [Sequence Description]

, RTRIM(A.WCID_I) [Work Center]

, RTRIM(C.WCDESC_I) [Work Center Description]

, A.LABORTIME_I [Total Estimated Hours]

, ISNULL(B.HRS,0) [Total Actual Hours]

, A.LABORTIME_I - ISNULL(B.HRS,0) [Labor Variance]

FROM WR010130 A

LEFT JOIN (

SELECT MANUFACTUREORDER_I, RTSEQNUM_I, SUM(ELAPSEDTIME_I) HRS FROM SF010115

GROUP BY MANUFACTUREORDER_I, RTSEQNUM_I) B ON A.MANUFACTUREORDER_I = B.MANUFACTUREORDER_I AND A.RTSEQNUM_I = B.RTSEQNUM_I

JOIN WC010931 C ON A.WCID_I = C.WCID_I AND C.OUTSOURCED_I <> 2

JOIN WO010032 D ON A.MANUFACTUREORDER_I = D.MANUFACTUREORDER_I
DavidMO 1,030 on at

Like (0)

Report

RE: Manufacturing WIP Report

Did you ever come up with some good SQL for this or similar MO reports? I would be interested if you have them. I am being asked to similar reports now.

Thanks!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Manufacturing WIP Report

Hi Babu,

Labor is coded to the MO directly, not to the individual picklist components. So you will have a lot of duplicated lines driving this from the picklist table. You'll actually need two different reports. One for material detail and one for labor detail.

-Trevor
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Manufacturing WIP Report

Babu,

Joining a header and detail will always results with duplicates as the Manufacturing Order (the header) will be repeated by the times of the lines items associated with it.

Therefore, If you are planning to publish this query on smart list builder, it wouldn't be such a good idea. The best option; to me, would be using Crystal Report in order to get the result set of data grouped by "Manufacturing Order". This means, splitting the set of data again between header section and detail section.

As for the script, I believe it would be more logical to only include the Manufacturing Order, associated items and cost (Fixed and Overhead) without the employee details.
babubaskaran@outloo... on at

Like (0)

Report

RE: Manufacturing WIP Report

Hi Trevor, thanks for your reply. Please see below my sql for this report.

select

pk.MANUFACTUREORDER_I,

pk.ITEMNMBR,

iv.ITEMDESC,

pk.PPN_I as bomno,

sf.EMPLOYID,

sf.ACTUALSTARTDATE_I,

sf.ACTUALSTARTTIME_I,

sf.ACTUALFINISHDATE_I,

sf.ACTUALFINISHTIME_I,

sf.SEQUENCECOST_I,

sf.ELAPSEDTIME_I,

up.LASTNAME,

up.FRSTNAME,

(mp.QTYRECVD * mp.ITEM_COSTS_ARRAY_I_1) AS materialcost,

(sf.SEQUENCECOST_I) AS labor,

(sf.Variable_Overhead_Amount) AS overhead,

(os1.EXTDCOST) AS outsource,

CASE WHEN wo.MANUFACTUREORDERST_I = '1' THEN 'quote'

WHEN wo.MANUFACTUREORDERST_I = '2' THEN 'open'

WHEN wo.MANUFACTUREORDERST_I = '3' THEN 'released'

WHEN wo.MANUFACTUREORDERST_I = '4' THEN 'hold'

WHEN wo.MANUFACTUREORDERST_I = '5' THEN 'cancelled'

WHEN wo.MANUFACTUREORDERST_I = '6' THEN 'complete'

WHEN wo.MANUFACTUREORDERST_I = '7' THEN 'partially rec.'

WHEN wo.MANUFACTUREORDERST_I = '8' THEN 'closed'

END AS MOstatus,

ENDQTY_I AS plannedqty,

(mp.QTYRECVD) AS qtyrecvd

from

PK010033 pk

inner join MOP1000 mp on pk.MANUFACTUREORDER_I = mp.MANUFACTUREORDER_I AND pk.ITEMNMBR = mp.ITEMNMBR AND pk.seq_i = mp.PICKLISTSEQ

LEFT outer join SF010115 sf on sf.MANUFACTUREORDER_I = mp.MANUFACTUREORDER_I AND pk.RTSEQNUM_I = sf.RTSEQNUM_I

LEFT outer JOIN dbo.UPR00100 UP ON up.EMPLOYID = sf.EMPLOYID

LEFT OUTER JOIN OSRC1100 os1 on os1.MANUFACTUREORDER_I = mp.MANUFACTUREORDER_I AND os1.ITEMNMBR = mp.ITEMNMBR AND sf.RTSEQNUM_I = os1.RTSEQNUM_I

LEFT OUTER JOIN IV00101 iv on iv.ITEMNMBR = pk.PPN_I

LEFT OUTER JOIN dbo.WO010032 wo ON wo.MANUFACTUREORDER_I = pk.MANUFACTUREORDER_I --AND wo.ITEMNMBR = mp.ITEMNMBR

AND pk.ROUTINGNAME_I = wo.ROUTINGNAME_I

where

pk.QTY_ISSUED_I <> 0

Thanks,

babu
Community Member Microsoft Employee on at

Like (0)

Report

RE: Manufacturing WIP Report

Hi Babu,

You probably have a problem with your table joins or grouping. SF010014 and SF010115 are the main labor tables and you'll need WO010032 for the MO header. Can you post your T-SQL?

-Trevor
babubaskaran@outloo... on at

Like (0)

Report

RE: Manufacturing WIP Report

Hi Sanjay,

I am running this on SSRS.

thanks,

babu
Community Member Microsoft Employee on at

Like (0)

Report

RE: Manufacturing WIP Report

Which reporting tool you are using ?

Cheers!

Sanjay