CREATE OR REPLACE procedure AVLMFG.proc_wip_v2009_matl
( t_fromdt in varchar2,
t_todate in varchar2 ) as
cursor c0 is
select a.*
from sap_wip055_data a
for update
;
cursor c1 is
select
'MMMMMM' month,
get_spart(b.matnr) divn,
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness,
count(*) nos
from sap_physstock_20100401 b
, sapr3.ympct_tub_matl@l_mfg_sap a
where a.mandt = '600'
and b.matnr = a.matnr
and b.invtype = 'WIP'
and b.werks = '055'
group by
get_spart(b.matnr),
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness
union all
select
'MMMMMM' month,
get_spart(a.matnr) divn,
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness,
count(*) nos
from sap_mseg_20062007 a
where a.cpudt >= '20100401'
and a.budat >= '20100401'
and werks = '055'
and lgort in ('STHI', 'STCI', 'STH3', 'STGW', 'STGL', 'STGC', 'STB1', 'STBS', 'STBC', 'STBH')
and bwart in ('101', '102')
group by
substr(a.budat, 1, 6),
get_spart(a.matnr),
a.geometry,
a.draw_type,
a.spec,
a.out_dia,
a.in_dia,
a.class,
a.thickness
;
cursor c2 is
select divn
, geometry
, draw_type
, spec
, out_dia
, in_dia
, class
, thickness
from sap_wip055_data
where month = 'MMMMMM'
;
t_divn varchar2(2);
t_geometry varchar2(1);
t_draw_type varchar2(1);
t_spec varchar2(3);
t_out_dia number default 0;
t_in_dia number default 0;
t_class varchar2(1);
t_thickness number default 0;
cursor c3 is
select a.sur_finish
, sum(b.clabs+b.cinsm) kgs
from sap_physstock_20100401 b
, sapr3.ympct_tub_matl@l_mfg_sap a
where b.werks = '055'
and b.invtype = 'WIP'
and a.mandt = '600'
and b.matnr = a.matnr
and t_divn = get_spart(b.matnr)
and t_geometry = a.geometry
and t_draw_type = a.draw_type
and t_spec = a.spec
and t_out_dia = a.out_dia
and t_in_dia = a.in_dia
and t_class = a.class
and t_thickness = a.thickness
group by a.sur_finish
;
cursor c4 is
select a.lgort
, 'MMMMMM' month
, a.sur_finish
, sum(decode(a.shkzg, 'S', a.menge, -1*a.menge)) kgs
from sap_mseg_20062007 a
where a.werks = '055'
and cpudt >= t_fromdt
and cpudt <= t_todate
and bwart in ('101', '102')
and t_divn = get_spart(a.matnr)
and t_geometry = a.geometry
and t_draw_type = a.draw_type
and t_spec = a.spec
and t_out_dia = a.out_dia
and t_in_dia = a.in_dia
and t_class = a.class
and t_thickness = a.thickness
group by a.lgort
, substr(a.budat, 1, 6)
, a.sur_finish
;
begin
for c0rec in c0 loop
exit when c0%notfound;
begin
delete sap_wip055_data
where current of c0;
end;
end loop;
commit;
for c1rec in c1 loop
exit when c1%notfound;
begin
insert into sap_wip055_data (
DIVN,
GEOMETRY,
DRAW_TYPE,
SPEC,
OUT_DIA,
IN_DIA,
CLASS,
THICKNESS,
MONTH,
BLACK_OPNG,
BLACK_PRDN,
BLACK_TRFR_GALV,
BLACK_TRFR_PACK,
GALV_SCRAP_GALV,
BLACK_CLSG,
BLACK_SCRAP_FIN,
GALV_OPNG,
GALV_PRDN,
GALV_TRFR,
GALV_SCRAP_FIN,
GALV_CLSG,
CPUDT,
CPUTM ) values (
c1rec.divn,
c1rec.geometry,
c1rec.draw_type,
c1rec.spec,
c1rec.out_dia,
c1rec.in_dia,
c1rec.class,
c1rec.thickness,
c1rec.month,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
to_char(sysdate, 'yyyymmdd'),
to_char(sysdate, 'hh24miss') );
exception when dup_val_on_index then
null;
end;
end loop;
commit;
for c2rec in c2 loop
exit when c2%notfound;
t_divn := c2rec.divn;
t_geometry := c2rec.geometry;
t_draw_type := c2rec.draw_type;
t_spec := c2rec.spec;
t_out_dia := c2rec.out_dia;
t_in_dia := c2rec.in_dia;
t_class := c2rec.class;
t_thickness := c2rec.thickness;
for c3rec in c3 loop
exit when c3%notfound;
if c3rec.sur_finish = 'GL' then
begin
update sap_wip055_data
set galv_opng = galv_opng + c3rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia = t_in_dia
and class = t_class
and thickness = t_thickness
and month = 'MMMMMM'
;
end;
else
begin
update sap_wip055_data
set black_opng = black_opng + c3rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia = t_in_dia
and class = t_class
and thickness = t_thickness
and month = 'MMMMMM'
;
end;
end if;
end loop;
commit;
for c4rec in c4 loop
exit when c4%notfound;
if c4rec.sur_finish = 'GL' then
if c4rec.lgort in ('STGL', 'STGC') then
begin
update sap_wip055_data
set black_trfr_galv = black_trfr_galv + c4rec.kgs
, galv_prdn = galv_prdn + c4rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia= t_in_dia
and class = t_class
and thickness = t_thickness
and month = c4rec.month
;
end;
elsif c4rec.lgort in ('STB1', 'STBH', 'STBS', 'STBC') then
begin
update sap_wip055_data
set galv_trfr = galv_trfr + c4rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia= t_in_dia
and class = t_class
and thickness = t_thickness
and month = c4rec.month
;
end;
else
null;
end if;
else -- black
if c4rec.lgort in ('STHI', 'STCI', 'STH3', 'STGW') then
begin
update sap_wip055_data
set black_prdn = black_prdn + c4rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia= t_in_dia
and class = t_class
and thickness = t_thickness
and month = c4rec.month
;
end;
elsif c4rec.lgort in ('STB1', 'STBH', 'STBC', 'STBS;') then
begin
update sap_wip055_data
set black_trfr_pack = black_trfr_pack + c4rec.kgs
where divn = t_divn
and geometry = t_geometry
and draw_type = t_draw_type
and spec = t_spec
and out_dia = t_out_dia
and in_dia= t_in_dia
and class = t_class
and thickness = t_thickness
and month = c4rec.month
;
end;
else
null;
end if;
end if;
end loop;
commit;
end loop;
end;
/
What SQL dialect is this? What DBMS is it? It doesn't look very T-SQL/SQL Server to me.
@Magnus - it's oracle - I've just retagged