WIP Entities Summary

select /*+ parallel*/
count(*) count,
x.wo_exists,
x.wro_exists,
x.type,
x.job_type,
x.status,
x.wip_supply_type,
x.we_entity_type,
x.wdj_job_type,
x.wdj_status_type,
x.wdj_wip_supply_type
from
(
select
(select 'Y' from wip_operations wo where we.wip_entity_id=wo.wip_entity_id and we.organization_id=wo.organization_id and rownum=1) wo_exists,
(select 'Y' from wip_requirement_operations wro where we.wip_entity_id=wro.wip_entity_id and we.organization_id=wro.organization_id and rownum=1) wro_exists,
xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) type,
xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type,
xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) status,
xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type,
we.entity_type we_entity_type,
wdj.job_type wdj_job_type,
wdj.status_type wdj_status_type,
wdj.wip_supply_type wdj_wip_supply_type
from
mtl_parameters mp,
wip_entities we,
wip_discrete_jobs wdj
where
1=1 and
mp.organization_id=we.organization_id and
we.wip_entity_id=wdj.wip_entity_id(+) and
we.organization_id=wdj.organization_id(+)
) x
group by
x.wo_exists,
x.wro_exists,
x.type,
x.job_type,
x.status,
x.wip_supply_type,
x.we_entity_type,
x.wdj_job_type,
x.wdj_status_type,
x.wdj_wip_supply_type
order by
count(*) desc

select /*+ parallel*/ count(*) count, x.wo_exists, x.wro_exists, x.type, x.job_type, x.status, x.wip_supply_type, x.we_entity_type, x.wdj_job_type, x.wdj_status_type, x.wdj_wip_supply_type from ( select (select 'Y' from wip_operations wo where we.wip_entity_id=wo.wip_entity_id and we.organization_id=wo.organization_id and rownum=1) wo_exists, (select 'Y' from wip_requirement_operations wro where we.wip_entity_id=wro.wip_entity_id and we.organization_id=wro.organization_id and rownum=1) wro_exists, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) type, xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) status, xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type, we.entity_type we_entity_type, wdj.job_type wdj_job_type, wdj.status_type wdj_status_type, wdj.wip_supply_type wdj_wip_supply_type from mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj where 1=1 and mp.organization_id=we.organization_id and we.wip_entity_id=wdj.wip_entity_id(+) and we.organization_id=wdj.organization_id(+) ) x group by x.wo_exists, x.wro_exists, x.type, x.job_type, x.status, x.wip_supply_type, x.we_entity_type, x.wdj_job_type, x.wdj_status_type, x.wdj_wip_supply_type order by count(*) desc