```SQL
--根据采购订单编码中的物料和供应商查询当前价目表最新价格
SELECT * FROM (
SELECT POMATE.FNUMBER AS 物料编码,POSUP.FNUMBER AS 供应商编码,PR.FNUMBER AS 价目表编码,PREN.FTAXPRICE AS 价目表含税单价,ROW_NUMBER() OVER(ORDER BY PREN.FEFFECTIVEDATE DESC,PREN.FENTRYID ASC) AS RN
FROM T_PUR_POORDER PO--采购订单基本信息
INNER JOIN T_PUR_POORDERFIN POFIN ON PO.FID = POFIN.FID--采购订单表头财务
INNER JOIN T_PUR_POORDERENTRY POEN ON PO.FID = POEN.FID--采购订单明细信息
INNER JOIN T_PUR_POORDERENTRY_F POENF ON POEN.FENTRYID = POENF.FENTRYID--采购订单明细财务拆分表
INNER JOIN T_PUR_POORDERENTRY_D POEND ON POEN.FENTRYID = POEND.FENTRYID--采购订单明细交货拆分表
INNER JOIN T_BD_SUPPLIER POSUP ON POSUP.FSUPPLIERID = PO.FSUPPLIERID--采购订单的供应商基础资料表
INNER JOIN T_BD_MATERIAL POMATE ON POMATE.FMATERIALID = POEN.FMATERIALID--采购订单的物料基础资料表
INNER JOIN T_BD_MATERIAL PRMATE ON PRMATE.FMASTERID = POMATE.FMASTERID--价目表的物料基础资料表，通过FMASTERID转换
INNER JOIN T_PUR_PRICELISTENTRY PREN ON PRMATE.FMATERIALID = PREN.FMATERIALID--价目表分录明细
INNER JOIN T_PUR_PRICELIST PR ON PREN.FID = PR.FID--价目表基本信息
LEFT JOIN T_BD_SUPPLIER PRSUP ON PRSUP.FSUPPLIERID = PR.FSUPPLIERID--价目表的供应商基础资料表
LEFT JOIN T_PUR_PRICELIST_ISSUE PRISS ON PO.FID = PRISS.FID--价目表分发信息表
WHERE 1=1
AND PR.FPRICEOBJECT = 'A'--价目表对象“按物料”
AND PR.FCURRENCYID = POFIN.FSETTLECURRID--币别
AND PREN.FUNITID = POENF.FPRICEUNITID--计价单位
AND PREN.FFROMQTY < POENF.FPRICEUNITQTY--从数量
AND (PREN.FTOQTY >= POENF.FPRICEUNITQTY OR PREN.FTOQTY = 0)--至数量
AND PREN.FEFFECTIVEDATE <= PO.FDATE--生效日期
AND PREN.FEXPIRYDATE > PO.FDATE--失效日期
AND (PR.FCREATEORGID = PO.FPURCHASEORGID OR PRISS.FISSUEORGID = PO.FPURCHASEORGID)--采购组织
AND (PREN.FPROCESSORGID = POEND.FREQUIREORGID OR PREN.FPROCESSORGID = 0)--需求组织
AND (PRSUP.FMASTERID = POSUP.FMASTERID OR PR.FSUPPLIERID = 0)--供应商
AND (PR.FISPRICEEXCLUDETAX = POFIN.FISPRICEEXCLUDETAX)--价外税
AND PO.FBILLNO = 'test123'--采购订单编号
) TT
WHERE TT.RN = 1--过滤排序第一的数据
```
**说明**：
==1、以上脚本不支持辅助属性影响价格和自定义维度取价时使用。因为辅助属性没法简单用sql进行匹配，而自定义维度每个客户场景不同需要自行拓展。==
==2、价目表取价排序默认是①价目表供应商不为空优先；②再取非0至升序排序；③最后按生效日期降序，如果以上三个维度还有相同的数据，最后就是按数据库默认的排序规则，即价目表分录内码FENTRYID升序排序。以上示例的脚本仅按生效日期降序和FENTRYID升序排序，默认所有价目表都有供应商且至相等==
**其他更多文章入口：**[https://wenku.my7c.com/link/s/lbRPP](https://wenku.my7c.com/link/s/lbRPP)
