|
1、ERP数据字典
链接：https://pan.baidu.com/s/1p1cs3G2MwrpSj0guHRacfg
提取码：9ae3
2、原料库存日单价SQL注释说明
create or replace procedure pro_aikcdj is
begin
declare
--jzbz varchar2(180); --上月是否结账标志
cbjs varchar2(20000); --财务未结账取成本计算单价
qcje varchar2(20000);
vqs1 varchar2(20000);
zvqsl varchar2(20000);
zerosl varchar2(20000); --库存为0的更新单价
rq varchar2(20);
csrq varchar2(20); --初始日期
sy varchar2(50);
endy varchar2(50);
rj varchar2(20); --上月期间
xtzh varchar2(20000);
xtck varchar2(20000);
qtrk varchar2(20000);
cgroup varchar2(80);
corg varchar2(80);
cjcsl varchar2(80);
cwlzj varchar2(80);
cwlbm varchar2(80);
cwlmc varchar2(80);
ccby varchar2(80);
csnabmny varchar2(80);
cnabmny varchar2(80);
czero varchar2(10);
cdr varchar2(10);
crkhj varchar2(80);
cckhj varchar2(80);
yearend varchar2(10);
yearstart varchar2(10);
zerojg varchar2(20000);
csprice varchar2(20000);
mprice varchar2(20000);
xzprice varchar2(20000);
dbrk varchar2(20000);
xsck varchar2(20000);
dbck varchar2(20000);
ccprk varchar2(20000);
yykj varchar2(20000);
cgth varchar2(20000);
jxcw varchar2(180);
cgcpdb varchar2(20000); --采购订单成品调拨
CURSOR cur_kc IS
select sum(ic_onhandnum.nonhandnum) jcsl,
ic_onhanddim.pk_group,
ic_onhanddim.pk_org,
cmaterialoid,
bd_material_v.code wlbm,
bd_material_v.name wlmc,
org_financeorg.pk_costregion pk_costregion
from (select *
from ic_onhanddim
where nvl(ic_onhanddim.dr, 0) = 0
and ic_onhanddim.pk_org in
(select distinct org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), 'yyyy-mm'))) ic_onhanddim
left join bd_material_v
on ic_onhanddim.cmaterialoid = bd_material_v.pk_source
left join ic_onhandnum
on ic_onhanddim.pk_onhanddim = ic_onhandnum.pk_onhanddim
left join org_stockorg
on ic_onhanddim.pk_org = org_stockorg.pk_stockorg
left join org_financeorg
on org_stockorg.pk_financeorg = org_financeorg.pk_financeorg
where 1 = 1
and bd_material_v.code like '1%'
or bd_material_v.code like 'S01%'
group by ic_onhanddim.pk_group,
ic_onhanddim.pk_org,
cmaterialoid,
bd_material_v.code,
bd_material_v.name,
org_financeorg.pk_costregion; --已结账算法
CURSOR cur_csh IS
select pk_group,
pk_org,
cinventoryid,
wlmc,
kcdj * nvl(kcsl, 0) nabmny,
wlbm,
pk_costregion
from (select *
from def_rcbdj
where 1 = 1
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), 'yyyy-mm')
and def_rcbdj.pk_org = org_costregion.pk_org)
and ddate = to_char(sysdate - 10, 'yyyy-mm-dd')
and nvl(def_rcbdj.dr, 0) = 0) def_rcbdj; --未结账算法 取10天内库存单价加权平均月初改单太多导致数据异常
begin
csrq := to_char(sysdate, 'dd');
rq := to_char(sysdate, 'yyyy-mm-dd');
rj := to_char(add_months(sysdate, -1), 'yyyy-mm');
sy := to_char(last_day(add_months(sysdate, -1)) + 1,
'yyyy-mm-dd'); --月初
endy := to_char(last_day(sysdate) + 1, 'yyyy-mm-dd'); --月底
czero := 0.0000;
cdr := 0;
yearend := to_char(sysdate, 'mm'); --二月底
yearstart := to_char(sysdate, 'yyyy') || '-01'; --年初
if csrq >= 01 then
open cur_kc;
loop
fetch cur_kc
into cjcsl, cgroup, corg, cwlzj, cwlbm, cwlmc, ccby;
exit when cur_kc%notfound;
select nvl(sum(ia_monthnab.nabmny), 0)
into cnabmny
from ia_monthnab
where ia_monthnab.caccountperiod = rj
AND ia_monthnab.cinventoryid = cwlzj
and ia_monthnab.pk_org = ccby
and ia_monthnab.nabmny <> 0
and nvl(ia_monthnab.dr, 0) = 0; --判断期初表里头没有的物料
select sum(xa.nassistnum * xc.vbdef10)
into crkhj
from ic_purchasein_b xa,
po_arriveorder_b xb,
po_order_b xc,
ic_purchasein_h xd
where xa.csourcebillbid = xb.pk_arriveorder_b
and xb.csourcebid = xc.pk_order_b
and xa.dr = 0
and xb.dr = 0
and xc.dr = 0
and xd.dr = 0
and xd.cgeneralhid = xa.cgeneralhid
and xd.freplenishflag <> 'Y' --采购退货参照采购订单金额冲减20190628
and dbizdate between
to_char(trunc(sysdate - 1, 'mm'), 'yyyy-mm-dd') and
to_char(sysdate + 1, 'yyyy-mm-dd')
and regexp_like(xc.vbdef10, '[[:digit:]]')
and xa.cmaterialvid = cwlzj
and xa.pk_org = corg;
--and xd.ccostdomainid = ccby;双成本域
select sum(nnum)
into cckhj
from ic_material_b a, ic_material_h b
where a.dr = 0
and b.dr = 0
and a.cgeneralhid = b.cgeneralhid
and a.cgeneralhid = b.cgeneralhid
and a.cmaterialvid = cwlzj
and a.pk_org = corg
--and b.ccostdomainoid = ccby--双成本域
and dbizdate between
to_char(trunc(sysdate - 1, 'mm'), 'yyyy-mm-dd') and
to_char(sysdate + 1, 'yyyy-mm-dd');
--if nvl(cnabmny,0)=0 and nvl(cjcsl,0)>0 then
insert into def_rcbdj
(id,
pk_group,
pk_org,
pk_costregion,
cinventoryid,
wlbm,
wlmc,
dbizdate,
kcdj,
nabmny,
kcsl,
rkhj,
cksl,
ddate,
dr,
jzzt)
values
(seq_ycl_rcbdj.nextval,
cgroup,
corg,
ccby,
cwlzj,
cwlbm,
cwlmc,
to_char(sysdate - 1, 'yyyy-mm-dd'),
czero,
czero,
cjcsl,
crkhj,
cckhj,
to_char(sysdate, 'yyyy-mm-dd'),
cdr,
'财务已结账');
end loop;
commit;
close cur_kc;
vqs1 := 'update def_rcbdj set kcsl=(select sum(ic_onhandnum.nonhandnum) xcl from ic_onhanddim
left join ic_onhandnum
on ic_onhanddim.pk_onhanddim=ic_onhandnum.pk_onhanddim
left join bd_material
on ic_onhanddim.cmaterialvid=bd_material.pk_material
left join bd_stordoc
on ic_onhanddim.cwarehouseid=bd_stordoc.pk_stordoc
where nvl(ic_onhandnum.dr,0)=0 and bd_material.code=def_rcbdj.wlbm and ic_onhanddim.pk_org=def_rcbdj.pk_org
and bd_stordoc.name not like ''%零成本%''
group by ic_onhanddim.pk_org,ic_onhanddim.cmaterialvid,bd_material.code) where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' ';
execute immediate vqs1; --更新库存数量20190910去掉零成本仓库的影响
commit;
if yearend = 02 then
qcje := ' update def_rcbdj set nabmny=(select
sum(nvl(ia_monthnab.nabmny, 0))
from ia_monthnab
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where
ia_monthnab.caccountperiod =''' || yearstart || '''
and nvl(ia_monthnab.dr, 0) = 0 AND bd_material_v.code=def_rcbdj.wlbm
and org_costregion.pk_org=def_rcbdj.pk_org
) where substr(def_rcbdj.TS,0,10)=''' || rq || ''' '; --更新期初
execute immediate qcje;
commit;
ccprk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl(( select sum(ia_monthnab.nabprice * ic_finprodin_b.nnum)
from ic_finprodin_b
inner join org_stockorg
on ic_finprodin_b.pk_org = org_stockorg.pk_stockorg
inner join org_financeorg
on org_stockorg.pk_financeorg = org_financeorg.pk_financeorg
inner join ia_monthnab
on ia_monthnab.pk_org = org_financeorg.pk_costregion
and ia_monthnab.cinventoryid = ic_finprodin_b.cmaterialvid
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
where ic_finprodin_b.dbizdate between
''' || sy || ''' and
''' || endy || '''
and ia_monthnab.caccountperiod=''' || yearstart || '''
and nvl(ic_finprodin_b.dr,0)=0
and ic_finprodin_b.pk_org=def_rcbdj.pk_org
and bd_material_v.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate ccprk; --增加产成品入库的金额每年二月份更新逻辑
commit;
else
qcje := ' update def_rcbdj set nabmny=(select
sum(nvl(ia_monthnab.nabmny, 0))
from ia_monthnab
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where
ia_monthnab.caccountperiod =''' || rj || '''
and nvl(ia_monthnab.dr, 0) = 0 AND bd_material_v.code=def_rcbdj.wlbm
and org_costregion.pk_org=def_rcbdj.pk_org
) where substr(def_rcbdj.TS,0,10)=''' || rq || ''' '; --更新期初
execute immediate qcje;
commit;
ccprk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl(( select sum(ia_monthnab.nabprice * ic_finprodin_b.nnum)
from ic_finprodin_b
inner join org_stockorg
on ic_finprodin_b.pk_org = org_stockorg.pk_stockorg
inner join org_financeorg
on org_stockorg.pk_financeorg = org_financeorg.pk_financeorg
inner join ia_monthnab
on ia_monthnab.pk_org = org_financeorg.pk_costregion
and ia_monthnab.cinventoryid = ic_finprodin_b.cmaterialvid
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
where ic_finprodin_b.dbizdate between
''' || sy || ''' and
''' || endy || '''
and ia_monthnab.caccountperiod=''' || rj || '''
and nvl(ic_finprodin_b.dr,0)=0
and ic_finprodin_b.pk_org=def_rcbdj.pk_org
and bd_material_v.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate ccprk; --增加产成品入库的金额
commit;
end if;
xtzh := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select sum(nvl(ncostmny,0))
from ic_generalin_b
left join bd_material
on ic_generalin_b.cmaterialvid= bd_material.pk_material
where cbodytranstypecode = ''4A-06''
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_generalin_b.dr = 0
and ic_generalin_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xtzh; --增加形态转换的金额
commit;
qtrk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select
sum(nvl(qtck.nnum * kcdj.nabprice, 0))
from (select avg(nabprice) nabprice ,pk_org,cinventoryid,KCRN
from (select cinventoryid,
org_costregion.pk_org,
nvl(nabprice, 0) nabprice,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_org order by ia_monthnab.caccountperiod desc) KCRN
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where nvl(ia_monthnab.dr, 0) = 0)
where KCRN = 1
group by pk_org,cinventoryid,KCRN
) kcdj
right join (select ic_generalin_b.pk_org,
ic_generalin_b.cmaterialvid,
bd_material.code wlbm,
nvl(sum(nnum), 0) nnum
from ic_generalin_b
left join bd_material
on ic_generalin_b.cmaterialvid = bd_material.pk_material
where ic_generalin_b.dr = 0
and cbodytranstypecode <> ''4A-06''
and cbodytranstypecode <> ''4A-02''
and dbizdate between to_char(last_day(add_months(sysdate,-1)) + 1,
''yyyy-mm-dd'') and
to_char(last_day(sysdate) + 1,''yyyy-mm-dd'')
group by ic_generalin_b.pk_org,
ic_generalin_b.cmaterialvid,
bd_material.code) qtck
on kcdj.cinventoryid = qtck.cmaterialvid
and kcdj.pk_org = qtck.pk_org
where 1=1
and qtck.pk_org=def_rcbdj.pk_org
and qtck.wlbm=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate qtrk; --增加其他入库的金额
commit;
xtck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl((select sum(nnum)
from ic_generalout_b
left join bd_material
on ic_generalout_b.cmaterialvid = bd_material.pk_material
where ic_generalout_b.dr =0
and cbodytranstypecode<>''4I-02''
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_generalout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xtck; --增加形态出库和其他出库的数量
commit;
xsck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl(( select sum(nnum)
from ic_saleout_b
left join bd_material
on ic_saleout_b.cmaterialvid = bd_material.pk_material
where ic_saleout_b.dr =0
and ic_saleout_b.dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_saleout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xsck; --增加销售出库的数量0709
commit;
cgcpdb := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select sum(ic_purchasein_b.nassistnum*po_order_b.vbdef10)
from ic_purchasein_b
left join po_order_b
on ic_purchasein_b.csourcebillbid=po_order_b.pk_order_b
left join bd_material
on ic_purchasein_b.cmaterialvid= bd_material.pk_material
where nvl(ic_purchasein_b.dr,0)=0
and nvl(po_order_b.dr,0)=0
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and regexp_like(po_order_b.vbdef10,''[[:digit:]]'')
and bd_material.code=def_rcbdj.wlbm
and ic_purchasein_b.pk_org =def_rcbdj.pk_org
and ic_purchasein_b.nassistnum>0
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate cgcpdb; --增加成品调拨的金额03-09新增逻辑
commit;
cgth := 'update def_rcbdj set cksl=nvl(cksl,0)-nvl((select sum(nnum)
from ic_purchasein_b
inner join ic_purchasein_h
on ic_purchasein_h.cgeneralhid = ic_purchasein_b.cgeneralhid
left join bd_material
on ic_purchasein_b.cmaterialvid = bd_material.pk_material
where nvl(ic_purchasein_b.dr,0)=0
and ic_purchasein_h.freplenishflag=''Y''
and nnum<0
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_purchasein_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate cgth; --增加采购退货的数量2019.02.25更新
commit; --去掉采购退货造成的多次数量冲减问题，李亚婷反馈2019-06-27
dbrk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select
sum(to_bill_b.norigtaxnetprice*ic_transin_b.nnum)
from to_bill_b
inner join ic_transin_b
on to_bill_b.cbill_bid = ic_transin_b.cfirstbillbid
left join bd_material
on ic_transin_b.cmaterialvid=bd_material.pk_material
where nvl(to_bill_b.dr,0)=0 and nvl(ic_transin_b.dr,0)=0
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_transin_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate dbrk; --增加调拨入库的金额
commit;
dbck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl((select
sum(ic_transout_b.nnum)
from ic_transout_b
left join bd_material
on ic_transout_b.cmaterialvid=bd_material.pk_material
where nvl(ic_transout_b.dr,0)=0
and dbizdate between
''' || sy || ''' and
''' || endy || '''
and ic_transout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate dbck; --增加调拨出库的数量
commit;
zvqsl := 'update def_rcbdj set kcdj=( (nvl(nabmny,0)+nvl(rkhj,0))-((nvl(nabmny,0)+nvl(rkhj,0))/(nvl(cksl,0)+kcsl))*nvl(cksl,0) )/kcsl where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and kcsl>0 and cksl+kcsl>0 ';
execute immediate zvqsl;
commit;
zerosl := 'update def_rcbdj set kcdj=0 where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and kcsl<=0 and wlbm not like ''119%'''; --没库存时库存单价设置为0,2019.01.19更新
execute immediate zerosl;
commit;
jxcw := 'update def_rcbdj set kcdj=0.535 where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and wlbm=''12000000000077'' and pk_org=''0001B110000000001IX1'''; --益肠宝赋值
execute immediate jxcw;
commit;
yykj := 'update def_rcbdj set kcdj=nvl( (select avg(nabprice) nabprice
from（select * from (select cinventoryid,
org_costregion.pk_org,
nvl(nabprice, 0) nabprice,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_book order by ia_monthnab.caccountperiod desc) KCRN
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where nvl(ia_monthnab.dr, 0) = 0 and nvl(org_costregion.dr,0)=0
) ia_monthnab
where KCRN =1） ia_monthnab
where ia_monthnab.pk_org=def_rcbdj.pk_org
and ia_monthnab.cinventoryid=def_rcbdj.cinventoryid
),0 )
where def_rcbdj.pk_org=''0001B110000000001IGO'' and ddate=''' || rq ||
''' ';
execute immediate yykj; --岳阳科技更新库存单价取月初的期初单价2019/02/20日更新
commit;
open cur_csh;
loop
fetch cur_csh
into cgroup, corg, cwlzj, cwlmc, csnabmny, cwlbm, ccby;
exit when cur_csh%notfound;
begin
select sum(xa.nassistnum * xc.vbdef10)
into crkhj
from ic_purchasein_b xa,
po_arriveorder_b xb,
po_order_b xc,
ic_purchasein_h xd
where xa.csourcebillbid = xb.pk_arriveorder_b
and xb.csourcebid = xc.pk_order_b
and xa.dr = 0
and xb.dr = 0
and xc.dr = 0
and xd.dr = 0
and xd.cgeneralhid = xa.cgeneralhid
and xd.freplenishflag <> 'Y' --采购退货参照采购订单金额冲减20190628
and xd.creationtime between
to_char(sysdate - 10, 'yyyy-mm-dd') and
to_char(sysdate + 1, 'yyyy-mm-dd')
and regexp_like(xc.vbdef10, '[[:digit:]]')
and xa.cmaterialvid = cwlzj
and xa.pk_org = corg;
--and xd.ccostdomainid = ccby;双成本域
select sum(nnum)
into cckhj
from ic_material_b a, ic_material_h b
where a.dr = 0
and b.dr = 0
and a.cgeneralhid = b.cgeneralhid
and a.cgeneralhid = b.cgeneralhid
and a.cmaterialvid = cwlzj
and a.pk_org = corg
--and b.ccostdomainoid = ccby--双成本域
and b.creationtime between to_char(sysdate - 10, 'yyyy-mm-dd') and
to_char(sysdate + 1, 'yyyy-mm-dd');
insert into def_rcbdj
(id,
pk_group,
pk_org,
pk_costregion,
cinventoryid,
wlbm,
wlmc,
dbizdate,
kcdj,
nabmny,
kcsl,
rkhj,
cksl,
ddate,
dr,
jzzt)
values
(seq_ycl_rcbdj.nextval,
cgroup,
corg,
ccby,
cwlzj,
cwlbm,
cwlmc,
to_char(sysdate - 1, 'yyyy-mm-dd'),
czero,
csnabmny,
czero,
crkhj,
cckhj,
to_char(sysdate, 'yyyy-mm-dd'),
cdr,
'财务未结账');
end;
end loop;
commit;
close cur_csh;
vqs1 := 'update def_rcbdj set kcsl=(select sum(ic_onhandnum.nonhandnum) xcl from ic_onhanddim
left join ic_onhandnum
on ic_onhanddim.pk_onhanddim=ic_onhandnum.pk_onhanddim
left join bd_material
on ic_onhanddim.cmaterialvid=bd_material.pk_material
left join bd_stordoc
on ic_onhanddim.cwarehouseid=bd_stordoc.pk_stordoc
where nvl(ic_onhandnum.dr,0)=0 and bd_material.code=def_rcbdj.wlbm and ic_onhanddim.pk_org=def_rcbdj.pk_org
and bd_stordoc.name not like ''%零成本%''
group by ic_onhanddim.pk_org,ic_onhanddim.cmaterialvid,bd_material.code) where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' ';
execute immediate vqs1; --更新库存数量20190910去掉零成本仓库带来的影响
commit;
xtzh := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select sum(nvl(ncostmny,0))
from ic_generalin_b
left join bd_material
on ic_generalin_b.cmaterialvid= bd_material.pk_material
where cbodytranstypecode = ''4A-06''
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and ic_generalin_b.dr = 0
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_generalin_b.pk_org= org_costregion.pk_org)
and ic_generalin_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xtzh; --增加形态转换的金额
commit;
qtrk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select
sum(nvl(qtck.nnum * kcdj.nabprice, 0))
from (select avg(nabprice) nabprice ,pk_org,cinventoryid,KCRN
from (select cinventoryid,
org_costregion.pk_org,
nvl(nabprice, 0) nabprice,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_org order by ia_monthnab.caccountperiod desc) KCRN
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where nvl(ia_monthnab.dr, 0) = 0)
where KCRN = 1 group by pk_org,cinventoryid,KCRN ) kcdj
right join (select ic_generalin_b.pk_org,
ic_generalin_b.cmaterialvid,
bd_material.code wlbm,
nvl(sum(nnum), 0) nnum
from ic_generalin_b
left join bd_material
on ic_generalin_b.cmaterialvid = bd_material.pk_material
where ic_generalin_b.dr = 0
and cbodytranstypecode <> ''4A-06''
and cbodytranstypecode <> ''4A-02''
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
group by ic_generalin_b.pk_org,
ic_generalin_b.cmaterialvid,
bd_material.code) qtck
on kcdj.cinventoryid = qtck.cmaterialvid
and kcdj.pk_org = qtck.pk_org
where 1=1
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and qtck.pk_org= org_costregion.pk_org)
and qtck.pk_org=def_rcbdj.pk_org
and qtck.wlbm=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate qtrk; --增加其他入库的金额
commit;
xtck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl((select sum(nnum)
from ic_generalout_b
left join bd_material
on ic_generalout_b.cmaterialvid = bd_material.pk_material
where ic_generalout_b.dr =0
and cbodytranstypecode<>''4I-02''
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_generalout_b.pk_org= org_costregion.pk_org)
and ic_generalout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xtck; --增加其他出库的数量
commit;
cgcpdb := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select sum(ic_purchasein_b.nassistnum*po_order_b.vbdef10)
from ic_purchasein_b
left join po_order_b
on ic_purchasein_b.csourcebillbid=po_order_b.pk_order_b
left join bd_material
on ic_purchasein_b.cmaterialvid= bd_material.pk_material
where nvl(ic_purchasein_b.dr,0)=0
and nvl(po_order_b.dr,0)=0
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and regexp_like(po_order_b.vbdef10,''[[:digit:]]'')
and bd_material.code=def_rcbdj.wlbm
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_purchasein_b.pk_org= org_costregion.pk_org)
and ic_purchasein_b.pk_org =def_rcbdj.pk_org
and ic_purchasein_b.nassistnum>0
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate cgcpdb; --增加成品调拨的金额03-09新增逻辑
commit;
cgth := 'update def_rcbdj set cksl=nvl(cksl,0)-nvl((select sum(nnum)
from ic_purchasein_b
inner join ic_purchasein_h
on ic_purchasein_h.cgeneralhid = ic_purchasein_b.cgeneralhid
left join bd_material
on ic_purchasein_b.cmaterialvid = bd_material.pk_material
where nvl(ic_purchasein_b.dr,0)=0
and ic_purchasein_h.freplenishflag=''Y''
and nnum<0
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_purchasein_b.pk_org= org_costregion.pk_org)
and ic_purchasein_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate cgth; ----增加采购退货的数量2019.02.25更新
commit; --去掉采购退货造成的多次数量冲减问题，李亚婷反馈2019-06-27
dbrk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl((select
sum(to_bill_b.norigtaxnetprice*ic_transin_b.nnum)
from to_bill_b
inner join ic_transin_b
on to_bill_b.cbill_bid = ic_transin_b.cfirstbillbid
left join bd_material
on ic_transin_b.cmaterialvid=bd_material.pk_material
where nvl(to_bill_b.dr,0)=0 and nvl(ic_transin_b.dr,0)=0
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_transin_b.pk_org= org_costregion.pk_org)
and ic_transin_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate dbrk; --增加调拨入库的金额
commit;
if yearend = 02 then
ccprk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl(( select sum(ia_monthnab.nabprice * ic_finprodin_b.nnum)
from ic_finprodin_b
inner join org_stockorg
on ic_finprodin_b.pk_org = org_stockorg.pk_stockorg
inner join org_financeorg
on org_stockorg.pk_financeorg = org_financeorg.pk_financeorg
inner join ia_monthnab
on ia_monthnab.pk_org = org_financeorg.pk_costregion
and ia_monthnab.cinventoryid = ic_finprodin_b.cmaterialvid
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
where ic_finprodin_b.dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and ia_monthnab.caccountperiod=''' || yearstart || '''
and nvl(ic_finprodin_b.dr,0)=0
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_finprodin_b.pk_org= org_costregion.pk_org)
and ic_finprodin_b.pk_org=def_rcbdj.pk_org
and bd_material_v.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate ccprk; --增加产成品入库的金额
commit;
else
ccprk := 'update def_rcbdj set rkhj=nvl(rkhj,0)+nvl(( select sum(ia_monthnab.nabprice * ic_finprodin_b.nnum)
from ic_finprodin_b
inner join org_stockorg
on ic_finprodin_b.pk_org = org_stockorg.pk_stockorg
inner join org_financeorg
on org_stockorg.pk_financeorg = org_financeorg.pk_financeorg
inner join ia_monthnab
on ia_monthnab.pk_org = org_financeorg.pk_costregion
and ia_monthnab.cinventoryid = ic_finprodin_b.cmaterialvid
left join bd_material_v
on ia_monthnab.cinventoryid = bd_material_v.pk_source
where ic_finprodin_b.dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and ia_monthnab.caccountperiod=''' || rj || '''
and nvl(ic_finprodin_b.dr,0)=0
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_finprodin_b.pk_org= org_costregion.pk_org)
and ic_finprodin_b.pk_org=def_rcbdj.pk_org
and bd_material_v.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate ccprk; --增加产成品入库的金额
commit;
end if;
dbck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl((select
sum(ic_transout_b.nnum)
from ic_transout_b
left join bd_material
on ic_transout_b.cmaterialvid=bd_material.pk_material
where nvl(ic_transout_b.dr,0)=0
and dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_transout_b.pk_org= org_costregion.pk_org)
and ic_transout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate dbck; --增加调拨出库的数量
commit;
xsck := 'update def_rcbdj set cksl=nvl(cksl,0)+nvl(( select sum(nnum)
from ic_saleout_b
left join bd_material
on ic_saleout_b.cmaterialvid = bd_material.pk_material
where ic_saleout_b.dr =0
and ic_saleout_b.dbizdate between
to_char(sysdate-10,''yyyy-mm-dd'')
and to_char(sysdate+1, ''yyyy-mm-dd'')
and NOT exists
(select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ic_saleout_b.pk_org= org_costregion.pk_org)
and ic_saleout_b.pk_org=def_rcbdj.pk_org
and bd_material.code=def_rcbdj.wlbm
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate xsck; --增加销售出库的数量0709
commit;
zvqsl := 'update def_rcbdj set kcdj=( (nvl(nabmny,0)+nvl(rkhj,0))-((nvl(nabmny,0)+nvl(rkhj,0))/(nvl(cksl,0)+kcsl))*nvl(cksl,0) )/kcsl where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and kcsl>0 and kcsl+cksl>0 ';
execute immediate zvqsl;
commit;
cbjs := 'update def_rcbdj set kcdj=nvl((select
(case when sum(nnum)=0 then 0 else sum(nnum*nprice)/sum(nnum) end) dj
from ia_detailledger
left join bd_material
on ia_detailledger.cinventoryvid = bd_material.pk_material
left join org_stockorg
on ia_detailledger.cstockorgid=org_stockorg.pk_stockorg
where caccountperiod =to_char(add_months(sysdate,-1),''yyyy-mm'')
and nvl(ia_detailledger.dr,0) = 0
and ia_detailledger.cbilltypecode not in (''IG'', ''IF'', ''IE'')
and (ia_detailledger.fintransitflag in (-1, 0))
and ia_detailledger.fdispatchflag = 1
and ia_detailledger.iauditsequence >= 0
and nvl(nprice, 0) > 0
and NOT exists (select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ia_detailledger.cstockorgid= org_costregion.pk_org
)
and ia_detailledger.cinventoryvid=def_rcbdj.cinventoryid
and ia_detailledger.cstockorgid=def_rcbdj.pk_org
),0),TS=to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'')
where substr(def_rcbdj.TS,0,10)=to_char(sysdate,''yyyy-mm-dd'')
and concat(def_rcbdj.cinventoryid,def_rcbdj.pk_org)
in (select concat(ia_detailledger.cinventoryvid,ia_detailledger.cstockorgid)
from ia_detailledger
left join bd_material
on ia_detailledger.cinventoryvid = bd_material.pk_material
left join org_stockorg
on ia_detailledger.cstockorgid=org_stockorg.pk_stockorg
where caccountperiod =to_char(add_months(sysdate,-1),''yyyy-mm'')
and nvl(ia_detailledger.dr,0) = 0
and ia_detailledger.cbilltypecode not in (''IG'', ''IF'', ''IE'')
and (ia_detailledger.fintransitflag in (-1, 0))
and ia_detailledger.fdispatchflag = 1
and ia_detailledger.iauditsequence >= 0
and nvl(nprice,0) > 0
and NOT exists (select org_costregion.pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
where caccountperiod =
to_char(add_months(sysdate, -1), ''yyyy-mm'')
and ia_detailledger.cstockorgid= org_costregion.pk_org)
)';
execute immediate cbjs;
commit; --2019.11.14增加未结账单价取财务成本计算的单价
zerosl := 'update def_rcbdj set kcdj=0 where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and kcsl<=0 and wlbm not like ''119%'''; --没库存时库存单价设置为0,2019.01.19更新
execute immediate zerosl;
commit;
jxcw := 'update def_rcbdj set kcdj=0.535 where substr(def_rcbdj.TS,0,10)=''' || rq ||
''' and wlbm=''12000000000077'' and pk_org=''0001B110000000001IX1'''; --益肠宝赋值
execute immediate jxcw;
commit;
yykj := 'update def_rcbdj set kcdj=nvl( (select avg(nabprice) nabprice
from（select * from (select cinventoryid,
org_costregion.pk_org,
nvl(nabprice, 0) nabprice,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_book order by ia_monthnab.caccountperiod desc) KCRN
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org = org_costregion.pk_costregion
where nvl(ia_monthnab.dr, 0) = 0 and nvl(org_costregion.dr,0)=0
) ia_monthnab
where KCRN =1） ia_monthnab
where ia_monthnab.pk_org=def_rcbdj.pk_org
and ia_monthnab.cinventoryid=def_rcbdj.cinventoryid
),0 )
where def_rcbdj.pk_org=''0001B110000000001IGO'' and ddate=''' || rq ||
''' ';
execute immediate yykj;
commit;
zerojg := 'update def_rcbdj set kcdj=nvl(( select avg(nvl(nabprice, 0))
from (select ia_monthnab.nabprice,
ia_monthnab.cinventoryid,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_book order by ia_monthnab.caccountperiod desc) KCRN,
org_costregion.pk_org pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
left join bd_material
on ia_monthnab.cinventoryid =
bd_material.pk_material
where nvl(ia_monthnab.dr, 0) = 0
and nvl(ia_monthnab.nabprice,0)>0
and bd_material.code like ''1%'') ia_monthnab
where KCRN = 1
and ia_monthnab.pk_org=def_rcbdj.pk_org
and ia_monthnab.cinventoryid=def_rcbdj.cinventoryid
),0)
where ddate=to_char(sysdate,''yyyy-mm-dd'')
and nvl(kcsl,0)=0
and nvl(dr,0)=0
and pk_org<>''0001A91000000000B6GB''
and pk_org<>''0001B110000000001IGO'' ';
/*zerojg := 'update def_rcbdj set kcdj=(
select nvl(sum(kcdj),0)
from (select pk_org,
cinventoryid,
row_number() over(partition by pk_org, cinventoryid order by ddate desc) rn,
kcdj,
ddate
from def_rcbdj
where 1 = 1
and nvl(dr, 0) = 0
and ddate > to_char(last_day(add_months(sysdate, -1)), ''yyyy-mm-dd'')
and kcdj<>0
and kcdj<>10000000
) zxdj
where rn = 1
and zxdj.pk_org=def_rcbdj.pk_org
and zxdj.cinventoryid=def_rcbdj.cinventoryid
)
where ddate=to_char(sysdate,''yyyy-mm-dd'')
and nvl(kcsl,0)=0
and nvl(dr,0)=0
and pk_org<>''0001A91000000000B6GB''
and pk_org<>''0001B110000000001IGO'' ';*/
execute immediate zerojg;
commit; --库存数量为0的价格修正逻辑2019.08.21
csprice := 'update def_rcbdj set cskcdj=kcdj where nvl(dr,0)=0 and ddate=to_char(sysdate,''yyyy-mm-dd'')';
execute immediate csprice;
commit; --将初始计算的价格保留2019.08.28
mprice := 'update def_rcbdj set monthprice=nvl(( select avg(nvl(nabprice, 0))
from (select ia_monthnab.nabprice,
ia_monthnab.cinventoryid,
row_number() over(partition by ia_monthnab.cinventoryid, ia_monthnab.pk_book order by ia_monthnab.caccountperiod desc) KCRN,
org_costregion.pk_org pk_org
from ia_monthnab
left join org_costregion
on ia_monthnab.pk_org =
org_costregion.pk_costregion
left join bd_material
on ia_monthnab.cinventoryid =
bd_material.pk_material
where nvl(ia_monthnab.dr, 0) = 0
and nvl(ia_monthnab.nabprice,0)>0
and bd_material.code like ''1%'') ia_monthnab
where KCRN = 1
and ia_monthnab.pk_org=def_rcbdj.pk_org
and ia_monthnab.cinventoryid=def_rcbdj.cinventoryid
),0)
where substr(def_rcbdj.TS,0,10)=''' || rq || ''' ';
execute immediate mprice;
commit; --记录最新月初单价
xzprice := ' update def_rcbdj set kcdj=monthprice
where
1=1
and ddate= to_char(sysdate,''yyyy-mm-dd'')
and
(case
when to_number(cskcdj, ''9999999999.9999'') > 0 and nvl(monthprice,0)<>0 then
(to_number(cskcdj, ''9999999999.9999'') -
to_number(monthprice, ''9999999999.9999'')) /
to_number(cskcdj, ''9999999999.9999'')
else
0
end)>0.2
or
(case
when to_number(cskcdj, ''9999999999.9999'') > 0 and nvl(monthprice,0)<>0 then
(to_number(cskcdj, ''9999999999.9999'') -
to_number(monthprice, ''9999999999.9999'')) /
to_number(cskcdj, ''9999999999.9999'')
else
0
end)<-0.2 ';
execute immediate xzprice; --当误差为上下浮动二十个百分点的时候修正单价
commit;
end if; --已经结账公司算法
end;
end pro_aikcdj;
|
本发明解决了ERP系统中原料库存单价无法精确到每日、不能根据行情变化实时调整的问题。通过每日自动计算库存单价，关联原料行情数据，实现了财务结账期间的准确性。此外，系统还能够根据市场行情动态修正单价，不受ERP系统结账影响，提高了效率和准确性。
扫一扫
3079
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
