【泛微E9】采购订单付款信息、质检信息sql

说明：formtable_main_335为流程-供应商预付款，formtable_main_335_dt1为流程-供应商预付款明细；uf_nscgddxzNS为建模-采购订单主表 uf_nscgddxzNS_dt1为建模-采购订单明细表

SELECT   t1.id, t1.mainid, t1.quantity, t1.rate, t1.taxcode, t1.taxamount, t1.amount, t1.grossamount, t1.expectedreceiptdate,t1.isclosed, t1.linenumber, t1.linkedorder, t1.memomx, t1.taxrate, t1.binnumber, t1.custcol_dps_account,t1.custcol_dps_demand_date, t1.custitem_sku_shipment_method, t1.location, t1.item, t1.internalID, t1.tranid,t1.vendorid, t1.subsidiary, t1.ddmxh, ISNULL(t2.fcost, 0) AS fcost, ISNULL(t3.fsqz, 0) AS fsqz,t1.grossamount - ISNULL(t2.fcost, 0) - ISNULL(t3.fsqz, 0) AS fcan
FROM      
	(SELECT   
		a.id, a.mainid, a.quantity, a.rate, a.taxcode,a.fkfsbz,a.taxamount, a.amount, a.grossamount, a.expectedreceiptdate, a.isclosed, a.linenumber, a.linkedorder, a.memomx, a.taxrate, a.binnumber, a.custcol_dps_account, a.custcol_dps_demand_date, a.custitem_sku_shipment_method, a.location, a.item, b.internalID, b.tranid,b.vendorid, b.subsidiary,b.payterms, b.tranid + '_' + a.linenumber AS ddmxh
    FROM      
		dbo.uf_nscgddxz_dt1 AS a 
	INNER JOIN
		dbo.uf_nscgddxz AS b 
	ON a.mainid = b.id) AS t1 
	LEFT OUTER JOIN                    
		(SELECT   
			SUM(a.amount) AS fcost, a.xzddmx
		FROM      dbo.formtable_main_335_dt1 AS a 
		INNER JOIN
			dbo.formtable_main_335 AS b 
		ON a.mainid = b.id 
		INNER JOIN
			dbo.workflow_requestbase AS c 
		ON b.requestid = c.requestid
		WHERE   (c.currentnodetype = 3)
		GROUP BY a.xzddmx) AS t2 
	ON t1.id = t2.xzddmx 
	LEFT OUTER JOIN 
		(SELECT   SUM(a.amount) AS fsqz, a.xzddmx
		FROM      dbo.formtable_main_335_dt1 AS a 
		INNER JOIN
			dbo.formtable_main_335 AS b 
		ON a.mainid = b.id 
		INNER JOIN
			dbo.workflow_requestbase AS c 
		ON b.requestid = c.requestid
        WHERE   (c.currentnodetype <> 3)
		GROUP BY a.xzddmx) AS t3 
	ON t1.id = t3.xzddmx

补充说明

----获取formtable_main_335供应商预付款中的 支付中 的支付金额汇总
SELECT  SUM(a.amount) AS fsqz支付金额, a.xzddmx as 选择订单明细   FROM      dbo.formtable_main_335_dt1 AS a 
INNER JOIN  dbo.formtable_main_335 AS b ON a.mainid = b.id INNER JOIN
                                     dbo.workflow_requestbase AS c ON b.requestid = c.requestid
                     WHERE   (c.currentnodetype <> 3)
                     GROUP BY a.xzddmx

------获取formtable_main_335供应商预付款中的 已支付 的支付金额汇总
(SELECT   SUM(a.amount) AS fcost, a.xzddmx
                    FROM      dbo.formtable_main_335_dt1 AS a INNER JOIN
                                    dbo.formtable_main_335 AS b ON a.mainid = b.id INNER JOIN
                                    dbo.workflow_requestbase AS c ON b.requestid = c.requestid
                    WHERE   (c.currentnodetype = 3)
                    GROUP BY a.xzddmx)



----uf_nscgddxzNS采购订单
SELECT   a.id as 明细行id, a.mainid as 主表id, a.quantity as 数量 , a.rate as 单价,
		a.taxcode as 税率,a.fkfsbz as 付款方式备注 ,a.taxamount as 税额, a.amount as 金额,
		a.grossamount as 含税总额, a.expectedreceiptdate as 预计接收日期 , a.isclosed as 是否关闭, a.linenumber as 行号, a.linkedorder as 请购单id,
		a.memomx as 备注, a.taxrate as  税码, a.binnumber as 库位号既店铺对应的库位, a.custcol_dps_account as 店铺,a.custcol_dps_demand_date as 需求日期,
		a.custitem_sku_shipment_method as 发货方式, a.location as 地点, a.item as 货品, b.internalID as 采购订单, b.tranid as 采购订单编码, 
                                 b.vendorid as 供应商编码, b.subsidiary as 所属公司,b.payterms as 付款方式, b.tranid  + '_' + a.linenumber AS ddmxhas采购订单编码明细行号
                 FROM      dbo.uf_nscgddxz_dt1 AS a INNER JOIN
                                 dbo.uf_nscgddxz AS b ON a.mainid = b.id

workflow_requestbase 工作流请求基本信息表

• 主键requestid
• currentnodetype/当前节点类型/char1/1：批准，2：提交，3：归档

相关数据字典

质检信息

----sumqualifiedlyes 已质检数量累计，sumqualifiedlno审核中数量累计，sumqualifiedlok可申请的数量
SELECT   t1.tranid, t1.internalID, t1.linenumber, t1.custcol_dps_account, t1.item, t1.quantity, t1.vendorid,t1.subsidiary, t1.isclosed, t1.linkedorder,
ISNULL(t2.sumqualifiedlyes, 0) AS sumqualifiedlyes, ISNULL(t3.sumqualifiedlno, 0) AS sumqualifiedlno,t1.quantity - ISNULL(t2.sumqualifiedlyes, 0) - ISNULL(t3.sumqualifiedlno, 0) AS sumqualifiedlok


FROM      
	(
	--b.tranid 批次号, b.internalID 批次号内部标识,a.linenumber	NS批次明细行号, a.item 货品, a.quantity 合同数量,a.isclosed 是否关闭, a.linkedorder 请购单id, a.custcol_dps_account 店铺, b.vendorid 供应商编码, b.subsidiary 所属公司
SELECT   b.tranid, b.internalID,a.linenumber, a.custcol_dps_account,a.item, a.quantity,  b.vendorid, b.subsidiary, a.isclosed, a.linkedorder
    FROM      
		dbo.uf_nscgddxz_dt1 AS a 
	INNER JOIN
		dbo.uf_nscgddxz AS b 
	ON a.mainid = b.id
	) AS t1 
	LEFT OUTER JOIN         
	
		(
		--b.requestid 请求ID b.lcbh 流程编号, b.lotno 批次号 ,a.linenum 批次明细行号,b.internalid 批次号NS内部标识 ,a.quantity 订单数量,SUM(a.qualified) AS sumqualifiedl已质检数量累计
		SELECT   b.requestid,b.lcbh, b.lotno ,a.linenum,b.internalid,a.quantity,
					SUM(a.qualified) AS sumqualifiedlyes
				FROM      dbo.formtable_main_343_dt1 AS a 
				INNER JOIN
					dbo.formtable_main_343 AS b 
				ON a.mainid = b.id 
				INNER JOIN
					dbo.workflow_requestbase AS c 
				ON b.requestid = c.requestid
				WHERE   (c.currentnodetype = 3)
				GROUP BY b.lotno ,a.linenum,b.requestid,b.lcbh,b.internalid,a.quantity) AS t2 

	ON (t1.internalID = t2.internalID and t1.linenumber = t2.linenum)
	LEFT OUTER JOIN 
		(
		--b.requestid 请求ID b.lcbh 流程编号, b.lotno 批次号 ,a.linenum 批次明细行号,b.internalid 批次号NS内部标识 ,a.quantity 订单数量,SUM(a.qualified) AS sumqualifiedlno正在审批中质检数量累计
		SELECT   b.requestid,b.lcbh, b.lotno ,a.linenum,b.internalid,a.quantity,
					SUM(a.qualified) AS sumqualifiedlno
				FROM      dbo.formtable_main_343_dt1 AS a 
				INNER JOIN
					dbo.formtable_main_343 AS b 
				ON a.mainid = b.id 
				INNER JOIN
					dbo.workflow_requestbase AS c 
				ON b.requestid = c.requestid
				WHERE   (c.currentnodetype <> 3)
				GROUP BY b.lotno ,a.linenum,b.requestid,b.lcbh,b.internalid,a.quantity) AS t3 
	ON (t1.internalID = t3.internalID and t1.linenumber = t3.linenum)