金蝶云星空-按指定排序获取物料采购价格

物料采购价格按默认供应商有效， 已失效， 非默认有效 非默认已失效顺序获取价格，

WITHRateCTEAS(SELECT[FBEGDATE],[FENDDATE],[FCYFORID],[FRATETYPEID],[FEXCHANGERATE]FROM[dbo].[Vw_BD_Rate_ToRMB])SELECTROW_NUMBER()OVER(PARTITIONBYp.FMATERIALIDORDERBYp.PRICELEVELASC,p.FEFFECTIVEDATEDESC,p.FFROMQTYASC/*分量计价从数量升序*/)RowSeq,PRICELISTNO,FSUPPLIERID,FSUPPLIERNO,FSUPPLIERNAME,FMATERIALID,FCURRENCYID,FCYFORTYPE,FMATERIALNO,FTAXPRICE,FTAXRATEPRICE,FTAXRATE,FEFFECTIVEDATE,FEXPIRYDATE,FPRICE,FRATEPRICE,FEXCHANGERATE,FRATETYPEID,FTOQTY,FFROMQTY,FENTRYID,FID,FSEQ,FMATERIALTYPEID,FMMASTERID,FPROCESSORGID,FISINCLUDEDTAX,FDEFPRICELISTID,FDISABLESTATUS,PRICELEVELINTO#MatePriceListFROM(SELECTp.FNUMBER PRICELISTNO,p.FSUPPLIERID,vs.FNUMBER FSUPPLIERNO,vs.FSHORTNAME FSUPPLIERNAME,pe.FMATERIALID,p.FCURRENCYID,c.FNUMBERASFCYFORTYPE,m.FNUMBERASFMATERIALNO,pe.FTAXPRICE,pe.FTAXPRICE*CASEp.FCURRENCYIDWHEN1THEN1ELSEISNULL(r.FEXCHANGERATE,1)ENDASFTAXRATEPRICE,pe.FTAXRATE,pe.FEFFECTIVEDATE,pe.FEXPIRYDATE,pe.FPRICE,pe.FPRICE*CASEp.FCURRENCYIDWHEN1THEN1ELSEISNULL(r.FEXCHANGERATE,1)ENDASFRATEPRICE,CASEp.FCURRENCYIDWHEN1THEN1ELSEISNULL(r.FEXCHANGERATE,1)ENDASFEXCHANGERATE,CASEp.FCURRENCYIDWHEN1THEN0ELSEr.FRATETYPEIDENDASFRATETYPEID,pe.FTOQTY/*分量计价到数量*/,pe.FFROMQTY/*2025-08-28 添加分量计价从数量*/,pe.FENTRYID,pe.FID,pe.FSEQ,pe.FMATERIALTYPEID,pe.FMMASTERID,pe.FPROCESSORGID,p.FISINCLUDEDTAX/*是否含税*/,p.FDEFPRICELISTID/*是否默认价目表*/,pe.FDISABLESTATUS,CASEWHENp.FSUPPLIERID=m.FSUPPLIERID_IDANDpe.FEXPIRYDATE>GETDATE()ANDpe.FDISABLESTATUS='B'THEN0WHENp.FSUPPLIERID=m.FSUPPLIERID_IDAND((pe.FEXPIRYDATEBETWEEN'2025-01-01'ANDGETDATE())OR(pe.FDISABLESTATUS='A'))THEN1WHENp.FSUPPLIERID<>m.FSUPPLIERID_IDANDpe.FEXPIRYDATE>GETDATE()THEN2ELSE3ENDPRICELEVELFROMT_PUR_PRICELIST p/*（价目表）*/JOINdbo.T_PUR_PRICELISTENTRY peONpe.FID=p.FIDJOIN(SELECTs.FSUPPLIERID,s.FNUMBER,sl.FNAME,sl.FSHORTNAMEFROMdbo.T_BD_SUPPLIER sJOINdbo.T_BD_SUPPLIER_L slONs.FSUPPLIERID=sl.FSUPPLIERIDANDsl.FLOCALEID=2052)vs/*供应商*/ONvs.FSUPPLIERID=p.FSUPPLIERIDJOIN(SELECTt0.FNUMBER,t0_b.FMATERIALID,t0_p.FDEFAULTVENDORID FSUPPLIERID_IDFROMdbo.T_BD_MATERIAL t0JOINT_BD_MATERIALBASE t0_bONt0_b.FMATERIALID=t0.FMATERIALIDANDt0_b.FUSEORGID=t0.FUSEORGIDLEFTJOINdbo.T_BD_MATERIALPURCHASE t0_pONt0_p.FMATERIALID=t0_b.FMATERIALIDWHERE1=1ANDt0_b.FERPCLSID=1)mONm.FMATERIALID=pe.FMATERIALIDJOINT_BD_CURRENCY cONc.FCURRENCYID=p.FCURRENCYIDLEFTJOINRateCTE rONr.FCYFORID=p.FCURRENCYID/*汇率*/WHERE1=1ANDp.FDOCUMENTSTATUS='C'ANDp.FFORBIDSTATUS='A'--AND pe.FDISABLESTATUS = 'B'/*业务失效 A失效，B正常*/)pSELECT*FROM#MatePriceListDROPTABLE#MatePriceList