备案控制台
开发者社区 云计算 文章 正文

基于存货总线设计的单据登记后台函数（plpgsql）

2024-03-01 60
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 基于存货总线设计的单据登记后台函数（plpgsql）

单据登记后台函数

存货总线设计方案中，最重要的是单据登记。每种单据中的数据，只有在登记后，才意味着数据被正式确认了。单据登记时，要将每种单据的不同总线属性反映出来，这样在总线数据查询中，就可以一目了然地看到每一具体存货或分类存货乃至所有存货总量的平衡状态，易于计算出最及时的物料需求。

单据登记函数代码

一、单据登记总函数

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中运行通过。

文章标签：
云数据库 ClickHouse
明月看潮生
目录
相关文章
明月看潮生
|
1月前
存货总线数量明细总账设计（plpgsql）
存货总线数量明细总账设计（plpgsql）
明月看潮生
23 1
明月看潮生
|
1月前
|
数据可视化
基于存货总线设计的物料需求计算后台函数（plpgsql）
基于存货总线设计的物料需求计算后台函数（plpgsql）
明月看潮生
16 1
明月看潮生
|
1月前
|
前端开发 数据库
基于存货总线设计的单据审核后台函数(plpgsql)
基于存货总线设计的单据审核后台函数(plpgsql)
明月看潮生
44 0
科技小先锋
解析SBO单据对象
科技小先锋
1400 0
玩SAP的苏州BOY
SAP RETAIL 通过自动补货功能触发的采购申请有些啥特殊的地方？
SAP RETAIL 通过自动补货功能触发的采购申请有些啥特殊的地方？
玩SAP的苏州BOY
95 0
SAP RETAIL 通过自动补货功能触发的采购申请有些啥特殊的地方？
玩SAP的苏州BOY
SAP 启用了HUM和QM的前提下，无法对采购订单的收货在质量放行前执行部分退货!
SAP 启用了HUM和QM的前提下，无法对采购订单的收货在质量放行前执行部分退货!
玩SAP的苏州BOY
197 0
SAP 启用了HUM和QM的前提下，无法对采购订单的收货在质量放行前执行部分退货!
流星雨v5
SAP PM入门系列3 - 设备主数据里无‘检验类型‘栏位，那何处定义维护订单可以触发检验批？
SAP PM入门系列3 - 设备主数据里无‘检验类型‘栏位，那何处定义维护订单可以触发检验批？
流星雨v5
159 0
SAP PM入门系列3 - 设备主数据里无‘检验类型‘栏位，那何处定义维护订单可以触发检验批？
玩SAP的苏州BOY
SAP RETAIL 如何确定自动补货触发的单据类型 II
SAP RETAIL 如何确定自动补货触发的单据类型 II
玩SAP的苏州BOY
99 0
SAP RETAIL 如何确定自动补货触发的单据类型 II
玩SAP的苏州BOY
SAP RETAIL 如何确定自动补货触发的单据类型
SAP RETAIL 如何确定自动补货触发的单据类型
玩SAP的苏州BOY
119 0
SAP RETAIL 如何确定自动补货触发的单据类型
玩SAP的苏州BOY
SAP RETAIL 如何确定自动补货触发的单据类型 III
SAP RETAIL 如何确定自动补货触发的单据类型 III
玩SAP的苏州BOY
106 0
SAP RETAIL 如何确定自动补货触发的单据类型 III