sql直接账表:领料汇总表(简单生产领料与生产领料同时使用,月底需要汇总)
最近总是遇到客户,既用生产领料,又有部分共耗是简单生产领料的,财务月底总是要两边一起统计,不愿意excel导出匹配一下,所以写了个sql直接账表,我非开发所以写的有不少问题,就当抛砖引玉了
前提:客户是单组织的,统计维度是期间段内,各生产车间从各仓库领用领用情况汇总,统计的单据有生产领料、生产退料、简单生产领料、简单生产退料,版本是cloud 标准版7.7
第一步:写sql,代码如下:
/* 01 我遇到的第一个问题就是讲4个报表关联起来,因为四个表各自的数据并不一一对应,车间、仓库、物料这几个变量 无论用联接还是union都解决不了,所以我想了个笨办法,上来先遍历组合一遍,这个特别占资源,好在这个表一个月 也就用一次,暂时就不考虑性能了,并且后面结果加一些where限定条件速度还行 */ select wlandck.FMATERIALID,wlandck.FSTOCKID,T_BD_DEPARTMENT.FDEPTID from t_bd_department cross join --这里是第二步,wlandck(物料和仓库表)和车间表进行交叉连接,并且筛选出对应字段,留下了 --物料id,仓库id,部门id (select T_BD_MATERIAL.FMATERIALID,T_BD_STOCK.FSTOCKID from T_BD_MATERIAL cross join T_BD_STOCK) as wlandck --这里是第一步,先把物料和仓库做一遍交叉连接,然后作为wlandck(物料和仓库表),再一次和车间表交叉连接 where T_BD_DEPARTMENT.FDEPTPROPERTY='4866f13a3a3940b9b2fe47895a6e7cbe' --这里是第三步,防止数据过大做一些限定条件,我这里限定了生产车间 /* 02 有了上面的表作为基表,接下去处理每一个领料、退料的表的汇总,以简单生产领料为例,这里没有用表别名, 看起来会累赘点,其他三表都是相同原理。 */ select T_SP_PICKMTRL.FID AS T_SP_PICKMTRLFID,T_SP_PICKMTRLDATA.FMATERIALID AS T_SP_PICKMTRLDATA物料ID,T_SP_PICKMTRLDATA.FSTOCKID AS T_SP_PICKMTRLDATA仓库ID,T_SP_PICKMTRL.FWORKSHOPID as T_SP_PICKMTRL车间ID,sum(T_SP_PICKMTRLDATA.FACTUALQTY) as T_SP_PICKMTRLDATA数量 from T_SP_PICKMTRL --这里是第一步将简单生产领料表作为主表,搜索对应字段,注意这里用了sum聚合领料数量 left join T_SP_PICKMTRLDATA ON T_SP_PICKMTRLDATA.FID=T_SP_PICKMTRL.FID --这里是第二步将简单生产领料表和简单生产领料明细表左联接 WHERE T_SP_PICKMTRL.FDATE>='#FDateS#' AND T_SP_PICKMTRL.FDATE<='#FDateE#' --这里是第三步就是限定了一下单据的时间范围,其中#FDateS#、#FDateE#是金蝶的关键字, --这里没用变量处理,变量会报错 group by T_SP_PICKMTRL.FID,T_SP_PICKMTRLDATA.FMATERIALID,T_SP_PICKMTRLDATA.FSTOCKID,T_SP_PICKMTRL.FWORKSHOPID --这里是第四步,按照车间、仓库、物料id聚合 /* 03 所有的基础表好了之后,以01的搜索结果作为表头,去做关联其他四个表,以车间、仓库、物料id唯一匹配,下附原表 */ select bt.FMATERIALID,m.FNUMBER as 物料编码,ml.fname as 物料名称,ml.FSPECIFICATION as 规格型号,d.FNUMBER as 车间编码,dl.FNAME as 车间名称,s.FNUMBER as 仓库编码,sl.FNAME as 仓库名称, isnull(jdll.T_SP_PICKMTRLDATA数量,0) as 简单生产领料,isnull((0-jdtl.简单生产退料合计数量),0) as 简单生产退料,isnull(scll.生产领料合计数,0) as 生产领料,isnull((0-sctl.生产退料合计数量),0) as 生产退料, (isnull(jdll.T_SP_PICKMTRLDATA数量,0)-isnull(jdtl.简单生产退料合计数量,0)+isnull(scll.生产领料合计数,0)-isnull(sctl.生产退料合计数量,0)) as 本期领用 from--搜索开始 --先罗列所有物料组合 ( select wlandck.FMATERIALID,wlandck.FSTOCKID,T_BD_DEPARTMENT.FDEPTID from t_bd_department cross join (select T_BD_MATERIAL.FMATERIALID,T_BD_STOCK.FSTOCKID from T_BD_MATERIAL cross join T_BD_STOCK) as wlandck where T_BD_DEPARTMENT.FDEPTPROPERTY='4866f13a3a3940b9b2fe47895a6e7cbe' ) as bt --简单生产领料left join left join ( select T_SP_PICKMTRL.FID AS T_SP_PICKMTRLFID,T_SP_PICKMTRLDATA.FMATERIALID AS T_SP_PICKMTRLDATA物料ID,T_SP_PICKMTRLDATA.FSTOCKID AS T_SP_PICKMTRLDATA仓库ID,T_SP_PICKMTRL.FWORKSHOPID as T_SP_PICKMTRL车间ID,sum(T_SP_PICKMTRLDATA.FACTUALQTY) as T_SP_PICKMTRLDATA数量 from T_SP_PICKMTRL left join T_SP_PICKMTRLDATA ON T_SP_PICKMTRLDATA.FID=T_SP_PICKMTRL.FID WHERE T_SP_PICKMTRL.FDATE>='#FDateS#' AND T_SP_PICKMTRL.FDATE<='#FDateE#' --and T_SP_PICKMTRLDATA.FMATERIALID=102131 --测试用 group by T_SP_PICKMTRL.FID,T_SP_PICKMTRLDATA.FMATERIALID,T_SP_PICKMTRLDATA.FSTOCKID,T_SP_PICKMTRL.FWORKSHOPID ) as jdll on jdll.T_SP_PICKMTRLDATA物料ID=bt.FMATERIALID and jdll.T_SP_PICKMTRLDATA仓库ID=bt.FSTOCKID and jdll.T_SP_PICKMTRL车间ID=bt.FDEPTID --简单生产退料left join left join ( select T_SP_RETURNMTRLENTRY.FMATERIALID as 物料id,T_SP_RETURNMTRL.FWORKSHOPID as 生产车间ID,T_SP_RETURNMTRLENTRY.FSTOCKID as 仓库id,sum(T_SP_RETURNMTRLENTRY.FQTY) as 简单生产退料合计数量 from T_SP_RETURNMTRLENTRY left join T_SP_RETURNMTRL on T_SP_RETURNMTRL.fid=T_SP_RETURNMTRLENTRY.fid where T_SP_RETURNMTRLENTRY.fid in (select fid from T_SP_RETURNMTRL WHERE T_SP_RETURNMTRL.FDATE>='#FDateS#' AND T_SP_RETURNMTRL.FDATE<='#FDateE#') group by T_SP_RETURNMTRLENTRY.FMATERIALID,T_SP_RETURNMTRLENTRY.FSTOCKID,T_SP_RETURNMTRL.FWORKSHOPID ) as jdtl on jdtl.物料id=bt.FMATERIALID and jdtl.仓库id=bt.FSTOCKID and jdtl.生产车间ID=bt.FDEPTID --生产领料left join left join ( select T_PRD_PICKMTRLDATA.FMATERIALID as 物料ID,T_PRD_PICKMTRLDATA.FWORKSHOPID as 生产车间id,T_PRD_PICKMTRLDATA.FSTOCKID as 仓库ID,sum(T_PRD_PICKMTRLDATA.FACTUALQTY) as 生产领料合计数 from T_PRD_PICKMTRLDATA where T_PRD_PICKMTRLDATA.fid in (select T_PRD_PICKMTRL.fid from T_PRD_PICKMTRL WHERE T_PRD_PICKMTRL.FDATE>='#FDateS#' AND T_PRD_PICKMTRL.FDATE<='#FDateE#') group by T_PRD_PICKMTRLDATA.FMATERIALID,T_PRD_PICKMTRLDATA.FWORKSHOPID,T_PRD_PICKMTRLDATA.FSTOCKID ) as scll on scll.仓库ID=bt.FSTOCKID and scll.生产车间id=bt.FDEPTID and scll.物料ID=bt.FMATERIALID --生产退料left join left join ( select T_PRD_RETURNMTRLENTRY.FMATERIALID as 物料ID, T_PRD_RETURNMTRLENTRY.FWORKSHOPID as 生产车间ID,T_PRD_RETURNMTRLENTRY.FSTOCKID as 仓库ID,sum(T_PRD_RETURNMTRLENTRY.FQTY) as 生产退料合计数量 from T_PRD_RETURNMTRLENTRY where T_PRD_RETURNMTRLENTRY.fid in (select fid from T_PRD_RETURNMTRL WHERE T_PRD_RETURNMTRL.FDATE>='#FDateS#' AND T_PRD_RETURNMTRL.FDATE<='#FDateE#') group by T_PRD_RETURNMTRLENTRY.FMATERIALID, T_PRD_RETURNMTRLENTRY.FWORKSHOPID,T_PRD_RETURNMTRLENTRY.FSTOCKID ) as sctl on sctl.仓库ID=bt.FSTOCKID and sctl.生产车间ID=bt.FDEPTID and sctl.物料ID=bt.FMATERIALID --把基础资料信息加载过来 left join T_BD_MATERIAL m on bt.FMATERIALID=m.FMATERIALID left join T_BD_MATERIAL_l ml on bt.FMATERIALID=ml.FMATERIALID left join T_BD_STOCK s on bt.FSTOCKID=s.FSTOCKID left join T_BD_STOCK_l sl on bt.FSTOCKID=sl.FSTOCKID left join T_BD_DEPARTMENT d on bt.FDEPTID=d.FDEPTID left join T_BD_DEPARTMENT_l dl on bt.FDEPTID=dl.FDEPTID --限定条件 where jdll.T_SP_PICKMTRLDATA数量>0 or jdtl.简单生产退料合计数量>0 or scll.生产领料合计数>0 or sctl.生产退料合计数量>0 --order by m.FNUMBER asc,d.FNUMBER asc,s.FNUMBER asc
当前该表存在的问题:
A基表数据过大;
B无法进行排序操作(发布到前台,order居然报错);
C当前日期其实一开始是声明变量的,但是发布到前台,金蝶也报错;
D该表不适用多组织,尤其库存组织与生产组织不一致的时候;
E该表当前不能显示期初期末
反正问题很多,凑合能用,有大佬可以教教我就太好了
另外附上sql直接账表发布的链接
https://wenku.my7c.com/article/197756589615498496
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#aaa') and type='U')
DROP TABLE #aaa;
go
SELECT bm2.FNUMBER bmbh,
bm2.FFULLNAME bmmc,
wl.FNUMBER wlbh,
wl.FNAME wlc,
wl.FSPECIFICATION guige,
dw.FNAME danwei,
SUM(b.FSTOCKACTUALQTY) shuliang,
SUM(b.FAMOUNT) / SUM(b.FSTOCKACTUALQTY) cbdj,
SUM(b.FAMOUNT) cbje
INTO #aaa
FROM T_SP_PICKMTRL a
LEFT JOIN T_SP_PICKMTRLDATA b
ON a.FID = b.FID
LEFT JOIN
(
SELECT a.FMATERIALID,
a.FNUMBER,
b.FNAME,
b.FSPECIFICATION
FROM T_BD_MATERIAL a
LEFT JOIN T_BD_MATERIAL_L b
ON a.FMATERIALID = b.FMATERIALID
) AS wl
ON b.FMATERIALID = wl.FMATERIALID
LEFT JOIN
(
SELECT bm.FDEPTID,
bm.FNUMBER,
bml.FFULLNAME
FROM T_BD_DEPARTMENT bm
LEFT JOIN T_BD_DEPARTMENT_L bml
ON bm.FDEPTID = bml.FDEPTID
) AS bm2
ON a.FWORKSHOPID = bm2.FDEPTID
LEFT JOIN T_BD_UNIT_L dw
ON b.FUNITID = dw.FUNITID
WHERE a.FDATE >= '2022-01-01'
AND a.FDATE <= '2022-01-31'
GROUP BY bm2.FNUMBER,
bm2.FFULLNAME,
wl.FNUMBER,
wl.FNAME,
wl.FSPECIFICATION,
dw.FNAME
ORDER BY bm2.FNUMBER,
bm2.FFULLNAME,
wl.FNUMBER,
wl.FNAME,
wl.FSPECIFICATION,
dw.FNAME;
GO
INSERT INTO #aaa(bmbh,bmmc,cbje)
SELECT bmbh,bmmc+'(小计)',SUM(cbje) cbje
FROM #aaa
GROUP BY bmbh,bmmc+'(小计)'
ORDER BY bmbh
GO
SELECT *
FROM #aaa ORDER BY bmbh,bmmc
按部门统计简单生产领料,部门合计也有了。
简单生产领料里错了,把FID,不然聚合的时候就错了
sql直接账表:领料汇总表(简单生产领料与生产领料同时使用,月底需要汇总)
本文2024-09-16 17:19:40发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-14987.html