单据登记后台函数
存货总线设计方案中，最重要的是单据登记。每种单据中的数据，只有在登记后，才意味着数据被正式确认了。单据登记时，要将每种单据的不同总线属性反映出来，这样在总线数据查询中，就可以一目了然地看到每一具体存货或分类存货乃至所有存货总量的平衡状态，易于计算出最及时的物料需求。
单据登记函数代码
一、单据登记总函数
CREATE OR REPLACE FUNCTION public.x9_djdj( ssdqyh character varying, ssbjmc character varying, ssdjmc character varying, ssdjoid character varying) RETURNS character varying LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE sssfdj bool; ssdjjg varchar; -- 登记结果 rsxx varchar; ssglbj varchar(30); BEGIN -- 一、非账项登记类单据，直接标记登记并返回当前用户，也可反登记 -- 二 、登记前验证 可以多次验证 只验证当前单据涉及物料 IF $3 = '销售订单' OR $3 = '需求计算单' OR $3 = '采购订单' OR $3 = '委外订单' OR $3 = '生产计划单' OR $3 = '完工入库单' OR $3 = '委外入库单' OR $3 = '委外订单' OR $3 = '销售出库单' OR $3 = '领用出库单' OR $3 = '委外出库单' THEN IF exists(SELECT chxh FROM mlchxm WHERE (chxh IN (SELECT chxh FROM djchm WHERE oid = $4)) AND (sxyzbj <> '' OR clyzbj <> '' OR jgyzbj <> '')) THEN rsxx:='各类存货单据登记之前，请确定：' || chr(13) || chr(10) || chr(13) || chr(10) || '正确设置物料的仓库名称、供货单位、生产单位；' || chr(13) || chr(10) || '正确设置物料的标准存量、计划批量、具有包装单位物料的每件数量；' || chr(13) || chr(10) || '正确设置物料的计划进价、计划售价、加工单价；' || chr(13) || chr(10) || '单据名称:' || $3 || ',单据号:' || $4; RETURN rsxx; END IF; END IF; IF $3 = '盘点入库单' THEN IF exists(SELECT chxh FROM djchm WHERE oid = $4 AND (rkdj = 0 OR rkje = 0)) THEN rsxx:= $3 || $4 || ',单据要求输入入库单价和金额！' ; RETURN rsxx; END IF; END IF; IF $3 = '采购入库单' THEN IF exists(SELECT chxh FROM djchm WHERE oid = $4 AND (cgdj = 0 OR cgje = 0)) THEN rsxx:=$3 || $4 || ',单据要求输入入库单价和金额！' ; RETURN rsxx; END IF; END IF; IF $3 = '需求计算单' THEN SELECT glbj INTO ssglbj FROM djcho WHERE oid = $4; IF exists(SELECT oid FROM djcho WHERE djmc = '销售订单' AND oid = ssglbj AND glbj <> '') THEN rsxx:='销售订单' || ssglbj || ',已经登记过关联的需求计算单，本单不能重复登记！'; RETURN rsxx; END IF; END IF; -- 三、登记 -- 存货单据 需求、计划、完成、交付 IF $3 = '销售订单' OR $3 = '需求计算单' OR $3 = '采购订单' OR $3 = '委外订单' OR $3 = '生产计划单' OR $3 = '盘点入库单' OR $3 = '采购入库单' OR $3 = '完工入库单' OR $3 = '委外入库单' OR $3 = '销售出库单' OR $3 = '领用出库单' OR $3 = '委外出库单' THEN SELECT CASE WHEN djr <> '' OR shr = '' THEN true ELSE false END INTO sssfdj FROM djcho WHERE oid = $4; IF sssfdj = true THEN rsxx:=$3 || $4 || '已经登记或未经审核，本单据不支持反登记！'; ELSE IF $3 = '销售订单' OR $3 = '需求计算单' THEN ssdjjg:=x9_djdj_01xq($1,$3,$4); END IF; IF $3 = '采购订单' OR $3 = '委外订单' OR $3 = '生产计划单' THEN ssdjjg:=x9_djdj_02jh($1,$3,$4); END IF; IF $3 = '采购入库单' OR $3 = '完工入库单' OR $3 = '委外入库单' OR $3 = '盘点入库单' THEN ssdjjg:=x9_djdj_03wc($1,$3,$4); END IF; IF $3 = '销售出库单' OR $3 = '领用出库单' OR $3 = '委外出库单' THEN ssdjjg:=x9_djdj_04jf($1,$3,$4); END IF; IF ssdjjg = '登记完成' THEN UPDATE djcho SET djrq = CURRENT_DATE WHERE oid = $4; rsxx:=$1; ELSE rsxx:= $3 || $4 || '登记未能完成'|| chr(10)||ssdjjg ; END IF; END IF; RETURN rsxx; END IF; END; $BODY$;
二、需求类单据登记函数
CREATE OR REPLACE FUNCTION public.x9_djdj_01xq( ssdqyh character varying, ssdjmc character varying, ssdjoid character varying) RETURNS character varying LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ -- Description: 单据登记 需求：销售订单、需求计算单 DECLARE ssmsg text;ssdet text;sstname text;sshint text;ssctt text; --错误处理 rd record; ssny char(6);ssjhjj decimal(16,2);ssjcdj decimal(16,2);ssglbj varchar(30); cs_xq CURSOR FOR SELECT dj.mid,dj.chxh,dj.bzjs,dj.xqsl,dj.xqje,xm.ckmc FROM djchm dj INNER JOIN mlchxm xm ON dj.chxh = xm.chxh WHERE dj.oid = $3; BEGIN -- 单据头数据 单据头不输入仓库名称，直接取自MLCHXM -- 标记登记\更新单据体日期 ssny:=x9_dqny(); IF $2 = '销售订单' THEN -- select * from djchm -- 优先使用结存单价，其次使用计划进价 UPDATE djchm SET xqsl = xssl,sfdj = true,djrq = CURRENT_DATE WHERE oid = $3; UPDATE djchm dd SET xqdj = zz.jcdj FROM utzzch zz where zz.chxh = dd.chxh AND zz.jcdj > 0 AND dd.oid = $3 AND zz.ny = ssny; UPDATE djchm dd SET xqdj = xm.jhjj FROM mlchxm xm where xm.chxh = dd.chxh AND dd.oid = $3 AND dd.xqdj = 0; UPDATE djchm SET xqje = xqsl * xqdj WHERE oid = $3; END IF; IF $2 = '需求计算单' THEN SELECT glbj into ssglbj FROM djcho WHERE oid = $3 ; UPDATE djcho SET glbj = $3 WHERE oid = ssglbj; -- 销售订单中标记需求计算单号 UPDATE djchm SET sfdj = true,djrq = CURRENT_DATE WHERE oid = $3; UPDATE djchm dd SET xqdj = zz.jcdj FROM utzzch zz where zz.chxh = dd.chxh AND zz.jcdj > 0 AND dd.oid = $3 AND zz.ny = ssny; UPDATE djchm dd SET xqdj = xm.jhjj FROM mlchxm xm WHERE xm.chxh = dd.chxh AND dd.oid = $3 AND dd.xqdj = 0; UPDATE djchm SET xqje = xqsl * xqdj WHERE oid = $3; WITH sjxm (chxh,sydw) AS (SELECT dj.chxh,CASE mllb WHEN '自制' THEN scdw WHEN '外协' THEN ghdw ELSE '' END AS sydw FROM mlchxm xm INNER JOIN djchm dj ON dj.clyt = xm.chxh WHERE dj.oid = $3 AND dj.clyt <> '') UPDATE mlchxm xm SET sydw = sj.sydw FROM sjxm sj where sj.chxh = xm.chxh AND sj.sydw <> ''; END IF; -- 标记登记 UPDATE djcho SET djr = $1 WHERE oid = $3; -- 声明游标CS_XQ OPEN cs_xq; LOOP FETCH cs_xq INTO rd; EXIT WHEN NOT FOUND; -- 增加新账 IF NOT exists(SELECT * FROM utzzch WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh ) THEN INSERT INTO utzzch(ny,ckmc,chxh) VALUES (ssny,rd.ckmc,rd.chxh); END IF; -- 登记总账 UPDATE utzzch SET xqjs = xqjs + rd.bzjs,xqsl = xqsl + rd.xqsl,xqje = xqje + rd.xqje WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; END LOOP; CLOSE cs_xq; RETURN '登记完成'; EXCEPTION WHEN others THEN GET stacked DIAGNOSTICS ssmsg = MESSAGE_TEXT,ssdet = pg_exception_detail, sstname = TABLE_NAME, sshint = pg_exception_hint, ssctt = pg_exception_context; CALL x9_jlproc(ssmsg, ssdet, sstname, sshint, ssctt); RETURN ssmsg; END; $BODY$;
三、计划类单据登记函数
CREATE OR REPLACE FUNCTION public.x9_djdj_02jh( ssdqyh character varying, ssdjmc character varying, ssdjoid character varying) RETURNS character varying LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ -- Description: 单据登记 计划： 采购订单、委外订单、生产计划单 DECLARE ssmsg text;ssdet text;sstname text;sshint text;ssctt text; --错误处理 rd record; ssny char(6); ssjhjj decimal(16,2); ssjcdj decimal(16,2); cs_jh CURSOR FOR SELECT dj.mid,dj.chxh,dj.bzjs,dj.jhsl,dj.jhje,dj.jgsl,dj.jgje,dj.cgsl,dj.cgje,dj.zzse,xm.ckmc FROM djchm dj INNER JOIN mlchxm xm ON dj.chxh = xm.chxh WHERE dj.oid = $3; BEGIN -- 单据头数据 不在单据头中输入仓库名称，直接取自MLCHXM -- 标记登记\更新单据体日期 UPDATE djchm SET sfdj = true,djrq = CURRENT_DATE WHERE oid = $3; -- 标记登记 UPDATE djcho SET djr = $1 WHERE oid = $3; IF $2 = '采购订单' THEN UPDATE djchm SET jhsl = cgsl,jhdj = (cgje - zzse) / cgsl,jhje = cgje - zzse WHERE oid = $3; END IF; ssny:=x9_dqny(); OPEN cs_jh; LOOP FETCH cs_jh INTO rd; EXIT WHEN NOT FOUND; -- 增加新账 IF NOT exists(SELECT * FROM utzzch WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh ) THEN INSERT INTO utzzch(ny,ckmc,chxh) VALUES (ssny,rd.ckmc,rd.chxh); END IF; -- 登记总账 IF $2 = '采购订单' THEN UPDATE utzzch SET jhjs = jhjs + rd.bzjs,jhsl = jhsl + rd.jhsl,jhje = jhje + rd.jhje WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; END IF; IF $2 = '委外订单' THEN -- 委外订单，输入JGJS,JGSL,JGDJ,JGJE,加工数在总账中不体现，只登记计划数：JHJE,JHSL,JHDJ,JHJE. -- 计划进价为全价，含加工费。先取结存单价，没有取计划进价，所以计划数不涉及税额 SELECT jhjj INTO ssjhjj FROM mlchxm WHERE chxh = rd.chxh; SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; rd.jhje:=x9_iif(ssjcdj > 0,ssjcdj,ssjhjj) * rd.jgsl; UPDATE utzzch SET jhjs = jhjs + rd.bzjs,jhsl = jhsl + rd.jgsl,jhje = jhje + rd.jhje WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; UPDATE djchm SET jhsl = jgsl,jhdj = CASE WHEN ssjcdj > 0 THEN ssjcdj ELSE ssjhjj END, jhje = rd.jhje WHERE mid = rd.mid; END IF; IF $2 = '生产计划单' THEN -- 计划进价为全价，含加工费。先取结存单价，没有取计划进价 SELECT jhjj INTO ssjhjj FROM mlchxm WHERE chxh = rd.chxh; SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; rd.jhje:=x9_iif(ssjcdj > 0,ssjcdj,ssjhjj) * rd.jhsl; UPDATE utzzch SET jhjs = jhjs + rd.bzjs,jhsl = jhsl + rd.jhsl,jhje = jhje + rd.jhje WHERE ny = ssny AND ckmc = rd.ckmc AND chxh = rd.chxh; UPDATE djchm SET jhdj = CASE WHEN ssjcdj > 0 THEN ssjcdj ELSE ssjhjj END, jhje = rd.jhje WHERE mid = rd.mid; END IF; END LOOP; CLOSE cs_jh; IF $2 = '生产计划单' THEN --生成车间作业计划 INSERT INTO utcjzy (mid,oid,ny,rq,chxh,chdh,chmc,xh1,xh2,xh3,gg1,ckmc,scbm,jfrq,jldw, jhsl,pcsl,pcrq,pcyq,sfsh,shr,mhh) SELECT m.mid,m.oid,m.ny,o.rq,m.chxh,m.chdh,m.chmc,m.xh1,m.xh2,m.xh3,m.gg1, o.ckmc,o.scbm,o.jfrq,m.jldw,m.jhsl,m.jhsl AS pcsl,o.rq AS pcrq,'' AS pcyq,sfsh,shr,mid AS mhh FROM djcho o INNER JOIN djchm m ON o.oid = m.oid WHERE o.oid = $3; END IF; RETURN '登记完成'; EXCEPTION WHEN others THEN GET stacked DIAGNOSTICS ssmsg = MESSAGE_TEXT,ssdet = pg_exception_detail, sstname = TABLE_NAME, sshint = pg_exception_hint, ssctt = pg_exception_context; CALL x9_jlproc(ssmsg, ssdet, sstname, sshint, ssctt); RETURN ssmsg; END; $BODY$;
四、完成类单据登记函数
CREATE OR REPLACE FUNCTION public.x9_djdj_03wc( ssdqyh character varying, ssdjmc character varying, ssdjoid character varying) RETURNS character varying LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ -- Description: 单据登记 完成：盘点入库、采购入库、完工入库、委外入库 DECLARE ssmsg text;ssdet text;sstname text;sshint text;ssctt text; --错误处理 rd record; ssny char(6);ssjhjj decimal(16,2);ssjcdj decimal(16,2);ssrkdj decimal(16,2); ssjswb varchar(20);ssckmc varchar(50);sswldw varchar(150); cs_wc CURSOR FOR SELECT mid,chxh,bzjs,rksl,rkje,jgje,cgje,wbje,zzse FROM djchm WHERE oid = $3; BEGIN -- 单据头数据 SELECT jswb,ckmc,wldw INTO ssjswb,ssckmc,sswldw FROM djcho WHERE oid = $3 ; -- 标记登记\更新单据体日期 UPDATE djchm SET sfdj = true,djrq = CURRENT_DATE WHERE oid = $3; IF $2 = '委外入库单' THEN UPDATE djchm SET rksl = jgsl WHERE oid = $3; END IF; IF $2 = '采购入库单' THEN UPDATE djchm SET rksl = cgsl,rkdj = (cgje - zzse) / cgsl,rkje = cgje - zzse WHERE oid = $3; END IF; -- 标记登记 UPDATE djcho SET djr = $1 WHERE oid = $3; -- 声明变量 单据体数据 ssny:=x9_dqny(); OPEN cs_wc; LOOP FETCH cs_wc INTO rd; EXIT WHEN NOT FOUND; -- 增加新账 IF NOT exists(SELECT * FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh ) THEN INSERT INTO utzzch(ny,ckmc,chxh) VALUES (ssny,ssckmc,rd.chxh); END IF; IF sswldw <> '' AND (NOT exists(SELECT * FROM utzzwl WHERE ny = ssny AND wldw = sswldw)) THEN INSERT INTO utzzwl(ny,wldw,wbmc) VALUES (ssny,sswldw,ssjswb); UPDATE mlwl SET sfqy = true WHERE sfqy = false AND sswldw LIKE wldh || '%'; -- 启用目录 END IF; -- 登记总账 IF $2 = '盘点入库单' THEN UPDATE utzzch SET srjs = srjs + rd.bzjs,jcjs = jcjs + rd.bzjs,srsl = srsl + rd.rksl,jcsl = jcsl + rd.rksl, srje = srje + rd.rkje,jcje = jcje + rd.rkje WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; END IF; IF $2 = '采购入库单' THEN -- 金额含税 存货账不含税，往来账含税，外币金额为含税 UPDATE utzzch SET srjs = srjs + rd.bzjs,jcjs = jcjs + rd.bzjs,srsl = srsl + rd.rksl, jcsl = jcsl + rd.rksl,srje = srje + rd.rkje,jcje = jcje + rd.rkje WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE utzzwl SET dffs = dffs + rd.cgje,ymye = ymye - rd.cgje, wbdf = wbdf + rd.wbje,wbym = wbym - rd.wbje WHERE ny = ssny AND wldw = sswldw; END IF; IF $2 = '完工入库单' THEN -- 只输入数量。 -- 计划进价为全价，含加工费。先取结存单价，没有再取计划进价 SELECT jhjj INTO ssjhjj FROM mlchxm WHERE chxh = rd.chxh; SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; ssrkdj:=x9_iif(ssjcdj > 0,ssjcdj,ssjhjj); rd.rkje:=ssrkdj * rd.rksl; UPDATE utzzch SET srjs = srjs + rd.bzjs,jcjs = jcjs + rd.bzjs,srsl = srsl + rd.rksl,jcsl = jcsl + rd.rksl, srje = srje + rd.rkje,jcje = jcje + rd.rkje WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE djchm SET rkdj = ssrkdj,rkje = rd.rkje WHERE mid = rd.mid; END IF; IF $2 = '委外入库单' THEN -- 计划进价为成本价，不含税，含加工费。先取结存单价，没有取计划进价 -- 加工金额为含税，委外单位无外币结算 SELECT jhjj INTO ssjhjj FROM mlchxm WHERE chxh = rd.chxh; SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; ssrkdj:=x9_iif(ssjcdj > 0,ssjcdj,ssjhjj); rd.rkje:=ssrkdj * rd.rksl; UPDATE utzzch SET srjs = srjs + rd.bzjs,jcjs = jcjs + rd.bzjs,srsl = srsl + rd.rksl,jcsl = jcsl + rd.rksl, srje = srje + rd.rkje,jcje = jcje + rd.rkje WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE djchm SET rkdj = ssrkdj,rkje = rd.rkje WHERE mid = rd.mid; UPDATE utzzwl SET dffs = dffs + rd.jgje,ymye = ymye - (rd.jgje) WHERE ny = ssny AND wldw = sswldw; END IF; UPDATE utzzch SET jcdj = jcje / jcsl WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh AND jcsl <> 0; UPDATE mlck SET sfqy = true WHERE sfqy = false AND ssckmc LIKE ckmc || '%'; -- 启用仓库目录 END LOOP; CLOSE cs_wc; RETURN '登记完成'; EXCEPTION WHEN others THEN GET stacked DIAGNOSTICS ssmsg = MESSAGE_TEXT,ssdet = pg_exception_detail, sstname = TABLE_NAME, sshint = pg_exception_hint, ssctt = pg_exception_context; CALL x9_jlproc(ssmsg, ssdet, sstname, sshint, ssctt); RETURN ssmsg; END; $BODY$;
五、交付类单据登记函数
CREATE OR REPLACE FUNCTION public.x9_djdj_04jf( ssdqyh character varying, ssdjmc character varying, ssdjoid character varying) RETURNS character varying LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ -- Description: 单据登记。交付。委外出库单、领用出库单、销售出库单。 DECLARE ssmsg text;ssdet text;sstname text;sshint text;ssctt text; --错误处理 rd record; ssny char(6);ssjcdj decimal(16,2); ssjswb varchar(20);ssckmc varchar(50);sswldw varchar(150); cs_jf CURSOR FOR SELECT mid,chxh,bzjs,cksl,ckje,xssl,xsje,wbje,zzse,wbse FROM djchm WHERE oid = $3; BEGIN -- 单据头数据 SELECT jswb,ckmc,wldw INTO ssjswb,ssckmc,sswldw FROM djcho WHERE oid = $3 ; -- 标记登记\更新单据体日期 UPDATE djchm SET sfdj = true,djrq = CURRENT_DATE WHERE oid = $3; -- 标记登记 UPDATE djcho SET djr = $1 WHERE oid = $3; -- 声明变量 单据体数据 ssny:=x9_dqny(); OPEN cs_jf; LOOP FETCH cs_jf INTO rd; EXIT WHEN NOT FOUND; -- 增加新账 IF NOT exists(SELECT * FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh ) THEN SELECT jhjj INTO ssjcdj FROM mlchxm WHERE chxh = rd.chxh ; INSERT INTO utzzch(ny,ckmc,chxh,jcdj) VALUES (ssny,ssckmc,rd.chxh,ssjcdj); END IF; IF sswldw <> '' AND (NOT exists(SELECT * FROM utzzwl WHERE ny = ssny AND wldw = sswldw)) THEN INSERT INTO utzzwl(ny,wldw,wbmc) VALUES (ssny,sswldw,ssjswb); UPDATE mlwl SET sfqy = true WHERE sfqy = false AND sswldw LIKE wldh || '%'; -- 启用目录 END IF; -- 登记总账和单据中的出库单价、金额 IF $2 = '领用出库单' THEN SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE utzzch SET fcjs = fcjs + rd.bzjs,jcjs = jcjs - rd.bzjs,fcsl = fcsl + rd.cksl, jcsl = jcsl - rd.cksl,fcje = fcje + rd.cksl * ssjcdj,jcje = jcje - rd.cksl * ssjcdj WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE djchm SET ckdj = ssjcdj,ckje = rd.cksl * ssjcdj WHERE mid = rd.mid; END IF; IF $2 = '委外出库单' THEN SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE utzzch SET fcjs = fcjs + rd.bzjs,jcjs = jcjs - rd.bzjs,fcsl = fcsl + rd.cksl, jcsl = jcsl - rd.cksl,fcje = fcje + ssjcdj * rd.cksl,jcje = jcje - rd.cksl * ssjcdj WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE djchm SET ckdj = ssjcdj,ckje = rd.cksl * ssjcdj WHERE mid = rd.mid; END IF; IF $2 = '销售出库单' THEN IF NOT exists(SELECT * FROM utzzxs WHERE ny = ssny AND wldw = sswldw AND jswb = ssjswb AND chxh = rd.chxh ) THEN -- 增加新账，销售总账 INSERT INTO utzzxs(ny,wldw,jswb,chxh) VALUES (ssny,sswldw,ssjswb,rd.chxh); END IF; -- 输入的是XSJS,XSSL,XSJE,需求登记CKJS,CKSL,CKJE和销售账项 rd.cksl = rd.xssl; SELECT jcdj INTO ssjcdj FROM utzzch WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE utzzch SET fcjs = fcjs + rd.bzjs,jcjs = jcjs - rd.bzjs,fcsl = fcsl + rd.cksl, jcsl = jcsl - rd.cksl,fcje = fcje + ssjcdj * rd.cksl,jcje = jcje - rd.cksl * ssjcdj WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh; UPDATE utzzxs SET xsjs = xsjs + rd.bzjs,xssl = xssl + rd.cksl,xsje = xsje + rd.xsje - rd.zzse, xsse = xsse + rd.zzse,wbje = wbje + rd.wbje,wbse = wbse + rd.wbse WHERE ny = ssny AND wldw = sswldw AND chxh = rd.chxh; UPDATE djchm SET cksl = rd.cksl,ckdj = ssjcdj,ckje = rd.cksl * ssjcdj WHERE mid = rd.mid; UPDATE utzzwl SET jffs = jffs + rd.xsje,ymye = ymye + rd.xsje, wbjf = wbjf + rd.wbje,wbym = wbym + rd.wbje WHERE ny = ssny AND wldw = sswldw; END IF; UPDATE utzzch SET jcdj = jcje / jcsl WHERE ny = ssny AND ckmc = ssckmc AND chxh = rd.chxh AND jcsl <> 0; UPDATE utzzxs SET xsdj = xsje / xssl WHERE ny = ssny AND wldw = sswldw AND chxh = rd.chxh AND xssl <> 0; UPDATE utzzxs xs SET xscbdj = ch.fcje / ch.fcsl, xscbje = CASE WHEN xs.xssl = ch.fcsl THEN ch.fcje ELSE xs.xssl * (ch.fcje / ch.fcsl) END FROM utzzch ch WHERE xs.ny = ch.ny AND xs.chxh = ch.chxh AND xs.ny = ssny AND xs.wldw = sswldw AND xs.chxh = rd.chxh AND ch.fcsl <> 0; UPDATE mlck SET sfqy = true WHERE sfqy = false AND ssckmc LIKE ckmc || '%'; -- 启用仓库目录 END LOOP; CLOSE cs_jf; RETURN '登记完成'; EXCEPTION WHEN others THEN GET stacked DIAGNOSTICS ssmsg = MESSAGE_TEXT,ssdet = pg_exception_detail, sstname = TABLE_NAME, sshint = pg_exception_hint, ssctt = pg_exception_context; CALL x9_jlproc(ssmsg, ssdet, sstname, sshint, ssctt); RETURN ssmsg; END; $BODY$; ALTER FUNCTION public.x9_djdj_04jf(character varying, character varying, character varying) OWNER TO postgres;
关于存货总线设计的内容请参考本专栏相关文章。
本段代码涉及的数据字典已经在上一篇文章中给出，不再重复。
本段代码已经在postgresql15.1中运行通过。
