|
select distinct
a.business_unit,
a.production_id,
a.op_sequence,
m.work_center_code,
a.crew_code,
b.machine_code,
m.crewsize,
m.mach_resources
from ps_sf_op_resrc a,
ps_sf_op_resrc b,
PS_SF_OP_LIST m
where
a.op_sequence = b.op_sequence and
a.business_unit = b.business_unit and
a.production_id = b.production_id and
(a.sf_resrc_type <> '50' and
b.sf_resrc_type <> '50') and
( ( (a.sf_resrc_type <> b.sf_resrc_type) and
(a.crew_code <> ' ' and b.machine_code <> ' ') )
or
( (a.sf_resrc_type = b.sf_resrc_type)
and
not exists (
select 'x'
from ps_sf_op_resrc c,
ps_sf_op_resrc d
where
(b.business_unit = d.business_unit and
a.business_unit = c.business_unit) and
(a.production_id = c.production_id and
b.production_id = d.production_id) and
( a.op_sequence = c.op_sequence and
b.op_sequence = d.op_sequence) and
(c.sf_resrc_type <> '50' and
d.sf_resrc_type <> '50') and (c.sf_resrc_type <> d.sf_resrc_type and
(c.crew_code <> ' ' and d.machine_code <> ' ')))) )
and m.business_unit=a.business_unit
and m.production_id = a.production_id
and m.op_sequence = a.op_sequence
|
BUSINESS_UNIT