物料采购价格按默认供应商有效 已失效 非默认有效 非默认已失效顺序获取价格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.FSUPPLIERIDm.FSUPPLIERID_IDANDpe.FEXPIRYDATEGETDATE()ANDpe.FDISABLESTATUSBTHEN0WHENp.FSUPPLIERIDm.FSUPPLIERID_IDAND((pe.FEXPIRYDATEBETWEEN2025-01-01ANDGETDATE())OR(pe.FDISABLESTATUSA))THEN1WHENp.FSUPPLIERIDm.FSUPPLIERID_IDANDpe.FEXPIRYDATEGETDATE()THEN2ELSE3ENDPRICELEVELFROMT_PUR_PRICELIST p/*价目表*/JOINdbo.T_PUR_PRICELISTENTRY peONpe.FIDp.FIDJOIN(SELECTs.FSUPPLIERID,s.FNUMBER,sl.FNAME,sl.FSHORTNAMEFROMdbo.T_BD_SUPPLIER sJOINdbo.T_BD_SUPPLIER_L slONs.FSUPPLIERIDsl.FSUPPLIERIDANDsl.FLOCALEID2052)vs/*供应商*/ONvs.FSUPPLIERIDp.FSUPPLIERIDJOIN(SELECTt0.FNUMBER,t0_b.FMATERIALID,t0_p.FDEFAULTVENDORID FSUPPLIERID_IDFROMdbo.T_BD_MATERIAL t0JOINT_BD_MATERIALBASE t0_bONt0_b.FMATERIALIDt0.FMATERIALIDANDt0_b.FUSEORGIDt0.FUSEORGIDLEFTJOINdbo.T_BD_MATERIALPURCHASE t0_pONt0_p.FMATERIALIDt0_b.FMATERIALIDWHERE11ANDt0_b.FERPCLSID1)mONm.FMATERIALIDpe.FMATERIALIDJOINT_BD_CURRENCY cONc.FCURRENCYIDp.FCURRENCYIDLEFTJOINRateCTE rONr.FCYFORIDp.FCURRENCYID/*汇率*/WHERE11ANDp.FDOCUMENTSTATUSCANDp.FFORBIDSTATUSA--AND pe.FDISABLESTATUS B/*业务失效 A失效B正常*/)pSELECT*FROM#MatePriceListDROPTABLE#MatePriceList