微信搜索公众号:SAP中文学习网交流平台      SAP技术千人群: SAP中文学习网技术群      SAP技术交流2群:SAP技术交流2群      SAP新人群:SAP中文学习网新人群     

SAP中文学习网

当前位置: 主页 > HANA专区 >

SAP-HANA中国第一个数据库应用程序

来源: 互联网TAG标签: sap-hana 作者:SAP爱好者 点击:
很荣幸成为第一位在SAP-HANA上进行Procedure开发的中国人,我们的POC已经完成了,圆满也行,问题也行,都给了SAP的德国人去做产品的修改,总之,新的东西,需要有一段时间去完善,希望德国人能不断有进度的反馈吧!

不管怎样,我们还是需要列存储的数据库的,特别是基于内存计算技术,所以,在6月份我们应该成为SAP第一个中国的HANA用户,我的理解,HANA的SQLSCRIPT和PLSQL区别有,但还是可以写的。给一个简单的运费处理程序,逻辑不去说明了,主要看语法。可以看到游标的处理有限制,赋值方式有不同,字符串变量的类型不同。毕竟宣称是SAP的数据库,将来要替代BW和R3的,不能什么都和ORACLE一样呀,理解!(农夫胡健)

create procedure znfspring.bw_storage_trprice_op2 as sql
yf_v number;
wg_v number;
fh_yf_v number;
fh_wg_v number;
fh_yfdj_v number;
sprice_v number;
count_v number;

zmaterial_v varchar(50);
ZSTOR_LOC_v varchar(50);
batch_v varchar(50);
zplant_v varchar(50);
z_type_v varchar(1);
zfh_store_v varchar(50);
zfh_plant_v varchar(50);
z_yf_v number;
z_ntgew_v number;


cursor cur_transport_price is
select t.zmaterial,t.ZSTOR_LOC,t.batch,t.zplant,t.z_type,zfh_store,zfh_plant,z_yf,z_ntgew
from znfspring.bw_storage_trprice_v t
where t.ACT_GI_DTE <=
to_char(last_day(add_months(sysdate, -1)), 'yyyymmdd')
and t.ACT_GI_DTE>=to_char(add_months(sysdate, -1),'yyyymm')||'01';
begin
--将销售组织进行初始化
delete from znfspring.price t where t.store_type = 'F';
commit;

open cur_transport_price;
if cur_transport_price%isopen then
loop
fetch cur_transport_price into zmaterial_v,ZSTOR_LOC_v,batch_v,zplant_v,z_type_v,zfh_store_v,zfh_plant_v,z_yf_v,z_ntgew_v;


exit when cur_transport_price%notfound;

--取目的库存地点的总运费、总重量
select count(*)
into count_v
from znfspring.price aaa
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :ZSTOR_LOC_v
and aaa.batch = :batch_v
and aaa.z_plant = :zplant_v
and aaa.STORE_TYPE = :z_type_v;

if :count_v > 0 then
select aaa.Z_YF, aaa.Z_NTGEW
into yf_v, wg_v
from znfspring.price aaa
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :ZSTOR_LOC_v
and aaa.batch = :batch_v
and aaa.z_plant = :zplant_v
and aaa.STORE_TYPE = :z_type_v;
else
insert into znfspring.price
(Z_STORE,
ZMATERIAL,
BATCH,
Z_NTGEW,


Z_YF,
Z_YFDJ,
MODIFY_DATE,
Z_PLANT,
STORE_TYPE)
values
(:ZSTOR_LOC_v,
:zmaterial_v,
:batch_v,
0,
0,
0,
sysdate,
:zplant_v,
:z_type_v);
yf_v := 0;
wg_v := 0;
end if;

--取源库存地点的总运费、总重量、加权运费单价
select count(*)
into count_v
from znfspring.price aaa
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :zfh_store_v
and aaa.batch = :batch_v
and aaa.z_plant = :zfh_plant_v
and aaa.STORE_TYPE = 'S';
if :count_v > 0 then

select aaa.Z_YF, aaa.Z_NTGEW, aaa.z_yfdj
into fh_yf_v, fh_wg_v, fh_yfdj_v
from znfspring.price aaa
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :zfh_store_v
and aaa.batch = :batch_v
and aaa.z_plant = :zfh_plant_v
and aaa.STORE_TYPE = 'S';
else
insert into znfspring.price
(Z_STORE,
ZMATERIAL,
BATCH,
Z_NTGEW,
Z_YF,
Z_YFDJ,
MODIFY_DATE,
Z_PLANT,
STORE_TYPE)
values
(:zfh_store_v,


:zmaterial_v,
:batch_v,
0,
0,
0,
sysdate,
:zfh_plant_v,
'S');
fh_yf_v := 0;
fh_wg_v := 0;
fh_yfdj_v := 0;
end if;

--计算目标库存地点的加权平均金额
yf_v := :yf_v + :z_yf_v + :fh_yfdj_v * :z_ntgew_v;
wg_v := :wg_v + :z_ntgew_v;

if :yf_v <= 0 then
yf_v := 0;
end if;

begin
sprice_v := round(:yf_v / :wg_v, 5);
exception
when others then
sprice_v := 0;
end;

--更改目标库存地点物料总量、单价、金额等
update znfspring.price aaa
set Z_NTGEW = :wg_v,

Z_YF = :yf_v,
Z_YFDJ = :sprice_v,
modify_date = sysdate
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :ZSTOR_LOC_v
and aaa.batch = :batch_v
and aaa.z_plant = :zplant_v
and aaa.STORE_TYPE = :z_type_v;

--更改源库存地点的物料总量、金额等
fh_wg_v := :fh_wg_v - :z_ntgew_v;

fh_yf_v := :fh_yf_v - :fh_yfdj_v * :z_ntgew_v;

if :fh_yf_v <= 0 then
fh_yf_v := 0;
end if;

update znfspring.price aaa
set Z_NTGEW = :fh_wg_v,
Z_YF = :fh_yf_v,
modify_date = sysdate
where aaa.zmaterial = :zmaterial_v
and aaa.z_store = :zfh_store_v
and aaa.batch = :batch_v
and aaa.z_plant = :zfh_plant_v
and aaa.STORE_TYPE = 'S';


end loop;
end if;
close cur_transport_price;

delete from znfspring.price t where t.z_ntgew = 0;
commit;
end;

(编辑:cindy liu )
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
您有可能感兴趣的文章