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

采购申请单最低价

--采购申请单最低价
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23