每人,每天,做好每件事
Everyone,Everyday,Everything

【泛微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:归档

相关数据字典

 uf_nscgddxzNS 数据字典
字段显示名数据库字段名称字段位置表现形式字段类型显示顺序
合同是否归档htsfgd主表选择框-下拉框是 否1
供应商代码gysdm主表单行文本框文本2
交期说明jqsm主表多行文本框多行文本框3
账期说明zqsm主表多行文本框多行文本框4
供应商账户信息gyszhxx主表多行文本框多行文本框5
其他约定条款说明qtydtksm主表多行文本框多行文本框6
请求类型reqType主表单行文本框文本7
采购订单idinternalID主表单行文本框文本8
采购订单编码tranid主表单行文本框文本9
日期trandate主表浏览按钮日期10
备注memo主表单行文本框文本11
状态approvalstatus主表选择框-下拉框待提交 待审核 已审核 已拒绝12
所属公司subsidiary主表浏览按钮自定义单选13
供应商编码vendorid主表浏览按钮自定义单选14
采购单申请人entity主表浏览按钮人力资源15
部门departmrnt主表浏览按钮部门16
币种currency主表浏览按钮自定义单选17
付款方式payterms主表浏览按钮自定义单选18
含税单价hsdj明细表1单行文本框浮点数1
合同交期htjq明细表1浏览按钮日期2
可装柜日期kzgrq明细表1浏览按钮日期3
含税总额grossamount明细表1单行文本框浮点数4
数量quantity明细表1单行文本框整数5
单价rate明细表1单行文本框浮点数6
税率taxcode明细表1单行文本框浮点数7
税码taxrate明细表1浏览按钮自定义单选8
税额taxamount明细表1单行文本框浮点数9
金额amount明细表1单行文本框浮点数10
店铺custcol_dps_account明细表1浏览按钮自定义单选11
预计接收日期expectedreceiptdate明细表1单行文本框文本12
是否关闭isclosed明细表1单行文本框文本13
行号linenumber明细表1单行文本框文本14
请购单idlinkedorder明细表1单行文本框文本15
备注memomx明细表1单行文本框文本16
需求日期custcol_dps_demand_date明细表1浏览按钮日期17
发货方式custitem_sku_shipment_method明细表1浏览按钮自定义单选18
货品item明细表1浏览按钮自定义单选19
库位号(店铺对应的库位)binnumber明细表1浏览按钮自定义单选20
地点location明细表1浏览按钮自定义单选21
报关名称custrecord_dps_invoice_chinese明细表1单行文本框文本22
国家custcol_dps_prline_country明细表1浏览按钮自定义单选23
店铺运营custcol_dps_operator明细表1浏览按钮人力资源24
付款方式备注fkfsbz明细表1单行文本框文本25
formtable_main_335
字段显示名数据库字段名称字段位置表现形式字段类型显示顺序
本位币汇率bwbhl主表单行文本框浮点数1
成本公司费用申请币种cbgsfysqbz主表浏览按钮币种2
对应成本公司dycbgs主表浏览按钮自定义单选3
供应商gys主表浏览按钮自定义单选4
关联采购合同/订单glcghtdd主表浏览按钮自定义单选5
辅助计算汇率年月fzjshlny主表单行文本框文本6
折算人民币总额zsrmbze主表单行文本框浮点数7
流程编码lcbm主表单行文本框文本8
NS返回信息nsfhxx主表单行文本框文本9
付款条件描述fktjms主表多行文本框多行文本框10
申请说明sqsm主表多行文本框多行文本框11
账户信息zhxx主表多行文本框多行文本框12
申请付款日期sqfkriqi主表浏览按钮日期13
作废ljfkbl主表单行文本框浮点数14
付款阶段fkjd主表选择框-下拉框首款 非首款15
返回idfhid主表单行文本框文本16
请求类型reqType主表单行文本框文本17
制单人zdr主表浏览按钮人力资源18
制单人(员工编号)createuser主表单行文本框文本19
对应部门bm主表浏览按钮部门20
部门编号department主表单行文本框文本21
预算科目km主表浏览按钮报销费用类型22
科目编码prepaymentaccount主表单行文本框文本23
对应公司subsidiary主表浏览按钮自定义单选24
过账期间postingperiod主表浏览按钮日期25
OA审核日期trandate主表浏览按钮日期26
货币类型currency主表浏览按钮自定义单选27
货币汇率exchangerate主表单行文本框浮点数28
银行科目account主表浏览按钮自定义单选29
制单日期zdrq主表浏览按钮日期30
承担主体类型cdztlx主表选择框-下拉框部门31
承担主体cdzt主表浏览按钮部门32
预算信息ysxx主表单行文本框文本33
帐户名称zhmc主表单行文本框文本34
开户银行khyx主表单行文本框文本35
银行账户yxzh主表单行文本框文本36
实际支付日期sjzfrq主表浏览按钮日期37
是否传NSsfcns主表选择框-下拉框是 否39
NS是否直接过账nssfzjgz主表选择框-下拉框是 否40
供应商NSIDentity主表单行文本框文本41
付款方式备注fkfsbz明细表1单行文本框文本1
累计付款比例(已支付+支付中)ljfkbl明细表1单行文本框浮点数2
本次申请付款比例bcsqfkbl明细表1单行文本框浮点数3
选择订单明细xzddmx明细表1浏览按钮自定义单选4
采购订单内部IDinternalid明细表1单行文本框文本5
含税总额hsze明细表1单行文本框浮点数6
已支付yzf明细表1单行文本框浮点数7
支付中zfz明细表1单行文本框浮点数8
可支付kzf明细表1单行文本框浮点数9
支付金额amount明细表1单行文本框浮点数10
备注memomx明细表1单行文本框文本11
采购订单号purchaseorder明细表1单行文本框文本12
货品hp明细表1浏览按钮自定义单选13
货币汇率exchangerate明细表1单行文本框浮点数14
付款方式fkfs明细表1浏览按钮自定义单选15
折算人民币金额zsrmbje明细表1单行文本框浮点数16
本位币汇率bwbhl明细表1单行文本框浮点数17

质检信息

----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)
赞(0) 打赏
未经允许不得转载:辣椒小鱼のBlog » 【泛微E9】采购订单付款信息、质检信息sql
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

×
订阅图标按钮