金蝶云星空采购申请单计算最低价

最新推荐文章于 2024-06-06 15:47:53 发布

懒人咖

最新推荐文章于 2024-06-06 15:47:53 发布

阅读量442

点赞数 3

分类专栏：金蝶云星空 MSSQL 文章标签： sql

本文链接：https://blog.csdn.net/qq_33881408/article/details/136227773

版权

金蝶云星空同时被 2 个专栏收录

93 篇文章 3 订阅

订阅专栏

MSSQL

3 篇文章 0 订阅

订阅专栏

采购申请单最低价

--采购申请单最低价
SELECT a.FBILLNO
,b.FSEQ
,d.FNUMBER
,MIN(ISNULL(e.FTAXPRICE,0)) 最低价
FROM T_PUR_Requisition a JOIN T_PUR_ReqEntry b ON a.fid =b.FID
JOIN  T_PUR_REQENTRY_S c ON c.FENTRYID=b.FENTRYID
JOIN T_BD_MATERIAL d ON d.FMATERIALID=b.FMATERIALID
LEFT JOIN (SELECT a.FNUMBER FNUMBER1,b.FSEQ,  a.FCREATEORGID,c.FNUMBER,b.FTAXPRICE,b.FEFFECTIVEDATE,b.FEXPIRYDATE
,isnull(d.FISSUEORGID,0) FISSUEORGID			                                                        
FROM t_PUR_PriceList a JOIN dbo.T_PUR_PRICELISTENTRY b ON a.FID=b.FID 
			JOIN T_BD_MATERIAL c ON c.FMATERIALID=b.FMATERIALID
			LEFT JOIN t_PUR_PriceList_ISSUE d ON d.fid=a.fid
			WHERE a.FPRICETYPE=2 --类型=采购
			AND a.FDOCUMENTSTATUS='C' 
			AND a.FFORBIDSTATUS='A'
			AND b.FROWAUDITSTATUS='A' 
			AND b.FDISABLESTATUS='B'
) e ON e.FNUMBER=d.FNUMBER AND (c.FPURCHASEORGID=e.FCREATEORGID OR c.FPURCHASEORGID=e.FISSUEORGID)
AND a.FAPPLICATIONDATE BETWEEN e.FEFFECTIVEDATE AND e.FEXPIRYDATE
WHERE  a.FBILLNO='CGSQ24010004'--采购申请单号
GROUP BY  
a.FBILLNO,b.FSEQ,d.FNUMBER