create table sp01 as
select oo.ORGANIZATION_CODE,oo.NAME,
to_char(ol.actual_shipment_date,'yyyymm') ship_month,
count(*) total,
sum(decode(sign(ol.shipped_quantity-100),1,0,1)) qty1t100,
sum(decode(sign(ol.shipped_quantity-101),-1,0,
decode(sign(ol.shipped_quantity-500),1,0,1)
)
) qty101t500,
sum(decode(sign(ol.shipped_quantity-501),-1,0,
decode(sign(ol.shipped_quantity-1000),1,0,1)
)
) qty501t1000,
sum(decode(sign(ol.shipped_quantity-1001),-1,0,
decode(sign(ol.shipped_quantity-5000),1,0,1)
)
) qty1001t5000,
sum(decode(sign(ol.shipped_quantity-5000),1,1,0)) overwq
from oe_order_lines_all ol,
oe_ship_from_orgs_v oo
where oo.organization_id=ol.ship_from_org_id
and ol.org_id in (801,543)
and to_char(ol.ACTUAL_SHIPMENT_DATE,'yyyy')=2010 --:p_year
and ol.shipped_quantity>=1
group by oo.ORGANIZATION_CODE,oo.NAME ,
to_char(ol.actual_shipment_date,'yyyymm')
create table sp02 as
select oo.ORGANIZATION_CODE,to_char(a.SCHEDULED_START_DATE,'yyyymm') wip_month,
count(*) total,
sum(decode(sign(a.start_quantity-100),1,0,1)) qty1t100,
sum(decode(sign(a.start_quantity-101),-1,0,
decode(sign(a.start_quantity-500),1,0,1)
)
) qty101t500,
sum(decode(sign(a.start_quantity-501),-1,0,
decode(sign(a.start_quantity-1000),1,0,1)
)
) qty501t1000,
sum(decode(sign(a.start_quantity-1001),-1,0,
decode(sign(a.start_quantity-5000),1,0,1)
)
) qty1001t5000,
sum(decode(sign(a.start_quantity-5000),1,1,0)) overwq
from WIP_DISCRETE_JOBS_V a,
org_organization_definitions oo
where a.ORGANIZATION_ID=802
and oo.organization_id=a.organization_id
and oo.OPERATING_UNIT in (801,543)
and a.start_quantity>=1
and to_char(a.SCHEDULED_START_DATE,'yyyy')=2010
group by oo.ORGANIZATION_CODE,to_char(a.SCHEDULED_START_DATE,'yyyymm')
select a.organization_code,a.ship_month mon,'OM' source_type,
a.qty1t100,a.qty101t500,a.qty501t1000,a.qty1001t5000,a.overwq,a.TOTAL
--nvl(a.qty1t100,0)+nvl(qty101t500,0)+nvl(qty501t1000,0)+nvl(qty1001t5000,0)+nvl(overwq,0) total2
from sp01 a
union all
select a.organization_code,a.wip_month mon,'WIP' source_type,
a.qty1t100,a.qty101t500,a.qty501t1000,a.qty1001t5000,a.overwq,a.TOTAL
--nvl(a.qty1t100,0)+nvl(qty101t500,0)+nvl(qty501t1000,0)+nvl(qty1001t5000,0)+nvl(overwq,0) total2
from sp02 a
order by organization_code,mon, source_type