基于存货总线设计的物料需求计算后台函数（plpgsql）

mlqd字段列表：
qdid      int4         32 清单序号          textedit          
cpchxh    varchar      20 产品货号          textedit     00000
cpchdh    varchar      30 产品代号          textedit          
jgdh      varchar      30 结构代号          textedit          
jc        int4         32 级次            textedit          
chxh      varchar      20 货号            buttonedit          
chdh      varchar      30 存货代号          buttonedit          
chmc      varchar     100 存货名称          textread          
xh1       varchar     100 型号            buttonedit          
xh2       varchar     100 规格            textedit          
xh3       varchar     400 参数            textedit          
gg1       varchar     100 颜色            comboedit     edit
gg2       varchar     100 材质            textedit          
gg3       varchar     100 图号            textedit          
jldw      varchar     100 单位            textread          
cllb      varchar      20 材料类别          textread          
desl      numeric      16 定额数量          textedit  0.0000;-0,0000;#
desh      numeric      16 定额损耗          textedit  0.00;-0,00;#
wldw      varchar     200 往来单位          buttonedit          
scbm      varchar     100 生产部门          textedit          
ckmc      varchar     100 仓库名称          listedit          
jsjd      int4         32 计算精度          textedit          
hh        int4         32 行号            textedit          
rq        date          0 日期            dateedit  yyyy-MM-dd
sfsh      bool          0 是否审核          checkedit         
shry      varchar      40 审核人员          textedit        

CREATE OR REPLACE FUNCTION public.x9_xqjs(
  ssdqyh 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; --错误处理
   ssdqjc int;
  ssdqny char(6);
  ssrkerr varchar(200);
BEGIN
  -- 需求计算前要对关联物料清单进行验证
  IF NOT exists(SELECT shr FROM djcho WHERE djmc = '销售订单' AND oid = $2 AND shr <> '' AND djr <> '')  THEN
    ssrkerr:='单据' || $2 || '不存在或未登记！' ;
    CALL x9_jlproc(ssrkerr,'','djcho','登记销售订单','需求计算');
    RETURN ssrkerr;
  END IF;
  IF exists(SELECT oid FROM djchm dd INNER JOIN mlqd qd ON qd.cpchxh = dd.chxh WHERE dd.oid = $2 AND qd.sfsh = false) THEN
    ssrkerr:='单据' || $2 || '的物料清单未被审核,无法进行需求计算' ;
    CALL x9_jlproc(ssrkerr,'','mlqd','清单审核','需求计算');
    RETURN ssrkerr;
  END IF;
  --  步骤
  --  1.删除原有计算结果
  DELETE FROM utwlxq WHERE oid = $2;
  ssdqny:=x9_dqny();
  --  2.生成当前单据的一级物料需求行
  INSERT INTO utwlxq  (oid,ny,cpchxh,jgdh,sjchxh,clchxh,jc,cllb,xqsl)
    SELECT $2 AS oid,ssdqny AS ny,mlqd.cpchxh, mlqd.jgdh, '' AS sjchxh,mlqd.cpchxh, mlqd.jc,
      mlqd.cllb,  sum(round((djchm.xqsl * mlqd.desl) * (1 + mlqd.desh / 100), mlqd.jsjd)) AS xqsl
    FROM djchm INNER JOIN mlqd ON djchm.chxh = mlqd.cpchxh
    GROUP BY djchm.oid,mlqd.cpchxh,mlqd.jgdh,mlqd.chxh,mlqd.jc,mlqd.cllb
    HAVING (djchm.oid = $2) AND (mlqd.jc = 1);
  --  3.分级计算
  ssdqjc:=1;
  LOOP
    EXIT WHEN ssdqjc > 5;
    -- 生成下一级物料需求
    INSERT INTO utwlxq (oid,ny,cpchxh, jgdh, sjchxh,clchxh, jc, cllb,xqsl)
    SELECT $2 AS oid,ssdqny AS ny, mlqd.cpchxh, mlqd.jgdh,wlxq.clchxh AS sjchxh,mlqd.chxh, mlqd.jc,
      mlqd.cllb, sum(round((wlxq.xqsl * mlqd.desl) * (1 + mlqd.desh / 100), mlqd.jsjd)) AS xqsl
    FROM utwlxq AS wlxq INNER JOIN mlqd ON wlxq.cpchxh = mlqd.cpchxh AND mlqd.jgdh like wlxq.jgdh || '%'
    WHERE (wlxq.oid = $2) AND (wlxq.jc = ssdqjc) AND (wlxq.xqsl > 0) AND (mlqd.jc = ssdqjc + 1)
      GROUP BY mlqd.cpchxh, mlqd.jgdh,wlxq.clchxh,mlqd.chxh,mlqd.jc,mlqd.cllb;
    ssdqjc:=ssdqjc + 1;
  END LOOP;
   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 ssdjoid || '需求计算出现错误';
END;
$BODY$;