This might be a start for you. I used this SQL to create a view to attach to the item revision file with the standard hours summed from the routing file.
select A.ITNBR, A.ITRV, A.STID,
sum(dec(case when sucsz = 0 then 0
when tbcod = 'M' then sulhr/60/sucsz else sulhr/sucsz
end,20,5)
+ dec(case when tbcod = 'H' then runmc else 0 end,10,5)
)
as StdSetupHours,
sum(dec(g.stdqt*(DLEN1*DCAP1+DLEN2*DCAP2+DLEN3*DCAP3)/(MAX(DCAP1,DCAP2,DCAP3)) ,20,5))
as StdQueueHours,
sum(dec(case
when runmc = 0 then 0
when tbcod = 'C' then 0
when tbcod = 'P' then
(1000)/runmc
when tbcod = 'M' then
(1000)*runmc/60
when tbcod = '' then
(1000)*runmc
when tbcod = '1' then
(1000)*runmc/10
when tbcod = '2' then
(1000)*runmc/100
when tbcod = '3' then
(1000)*runmc/1000
when tbcod = '4' then
(1000)*runmc/10000 else 0
end,20,5) )
as StdRunHoursPer1000Foot,
sum(dec(case when sucsz = 0 then 0
when tbcod = 'M' then sulhr/60/sucsz else sulhr/sucsz
end/(DLEN1*DCAP1+DLEN2*DCAP2+DLEN3*DCAP3)/(MAX(DCAP1,DCAP2,DCAP3)),20,5)
+ dec(case when tbcod = 'H' then runmc else 0 end/(DLEN1*DCAP1+DLEN2*DCAP2+DLEN3*DCAP3)/(MAX(DCAP1,DCAP2,DCAP3)),10,5)
)
as StdSetupDays,
sum(g.stdqt )
as StdQueueDays,
sum(dec(case
when runmc = 0 then 0
when tbcod = 'C' then 0
when tbcod = 'P' then
(1000)/runmc
when tbcod = 'M' then
(1000)*runmc/60
when tbcod = '' then
(1000)*runmc
when tbcod = '1' then
(1000)*runmc/10
when tbcod = '2' then
(1000)*runmc/100
when tbcod = '3' then
(1000)*runmc/1000
when tbcod = '4' then
(1000)*runmc/10000 else 0
end/(DLEN1*DCAP1+DLEN2*DCAP2+DLEN3*DCAP3)/(MAX(DCAP1,DCAP2,DCAP3)) ,20,5))
as StdRunDaysPer1000Foot
from amflib.itmrva A join amflib.itmrel H on A.stid = H.stid and A.itnbr = H.itnbr and A.itrv = H.itrv
join amflib.itmprc B on A.stid = B.stid and A.itnbr = B.itnbr and A.itrv = B.itrv
join amflib.rtghdr C on B.stid = C.stid and B.rtid = C.rtid and B.rtvr = C.rtvr
join amflib.rtgopr D on C.stid = D.stid and C.rtid = D.rtid and C.rtvr = D.rtvr
join amflib.facmst G on D.wkctr = G.wkctr
where a.itcls in ('100','110')
group by A.STID, A.ITNBR, A.ITRV, A.ITDSC
;
This could then be used in a recursive CTE to traverse the BOM and get top level and bottom level hours. If you want an example of this let me know. I can't seem to find it right now.
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: mapics-l-bounces@xxxxxxxxxxxx [mailto:mapics-l-bounces@xxxxxxxxxxxx] On Behalf Of Connie Webb
Sent: Wednesday, January 09, 2013 8:04 AM
To: mapics-l@xxxxxxxxxxxx
Subject: [MAPICS-L] Total labor hours per assembly
We need the labor hours that are associated with the top level and lower levels dollars that are broken out in ITMRVB (ITEMASB). Has anyone developed a program to do this?
Connie Webb | Database Administrator | 540.387.5600 x272 Graham-White Manufacturing Co.<
http://www.grahamwhite.com/> | Providing Reliable Transportation Solutions℠
This email and any files transmitted with it are confidential and intended solely for the use of the individual to whom they are addressed. If you are not the intended recipient, be aware that disclosing, copying, distributing, or taking any action in reliance on the contents of this information is strictly prohibited. Please notify the sender immediately by email if you have received this email in error and delete this email from your system.
--
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/mapics-l.
midrange.com
