如何在SAP Business One SQL版本中获取采购订单对应的全部应付预留发票？

要获取某一采购订单对应的所有应付预留发票，需要关联SAP Business One中的采购订单表（OPOR表头、POR1表体）和应付预留发票表（OAPR表头、APR1表体），核心关联逻辑是通过预留发票表体记录的源单据信息匹配采购订单行。

以下是基于你现有查询语句扩展后的完整SQL：

SELECT
    -- 采购订单表头字段
    OPOR.CardCode,
    OPOR.CardName,
    OPOR.U_ReferenceNo,
    OPOR.NumAtCard AS PO_NumAtCard,
    OPOR.DocNum AS PO_DocNum,
    OPOR.U_ShipmentTime,
    OPOR.U_DealType,
    OPOR.DocDate AS PO_DocDate,
    OPOR.U_Origin,
    OPOR.U_DealQuantity,
    OPOR.U_ContainerNo,
    -- 采购订单表体字段
    POR1.ItemCode,
    POR1.Dscription,
    POR1.Price AS PO_Price,
    POR1.U_CopperRate,
    POR1.U_AluminiumRate,
    POR1.U_IronRate,
    POR1.U_CopperPer,
    POR1.U_AluminiumPer,
    POR1.U_LeadPer,
    POR1.U_IronPer,
    POR1.U_PlasticPer,
    -- 应付预留发票表头字段
    OAPR.DocNum AS ReserveInv_DocNum,
    OAPR.DocDate AS ReserveInv_DocDate,
    OAPR.NumAtCard AS ReserveInv_NumAtCard,
    OAPR.DocStatus AS ReserveInv_Status,
    -- 应付预留发票表体字段
    APR1.Quantity AS ReserveInv_Qty,
    APR1.Price AS ReserveInv_Price,
    APR1.LineTotal AS ReserveInv_LineTotal
FROM
    OPOR
INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry
-- 关联应付预留发票表体，BaseType=22代表源单据是采购订单
LEFT JOIN APR1 ON POR1.DocEntry = APR1.BaseEntry 
               AND POR1.LineNum = APR1.BaseLine 
               AND APR1.BaseType = 22
-- 关联应付预留发票表头
LEFT JOIN OAPR ON APR1.DocEntry = OAPR.DocEntry
WHERE
    OPOR.DocStatus = 'O'
    -- 筛选指定采购订单，替换为实际订单号或DocEntry即可
    AND OPOR.DocNum = '12345'

关键说明： #

1. 表关联逻辑：
   • APR1.BaseType = 22：SAP Business One中采购订单的对象类型编码为22，用于标识预留发票的源单据类型。
   • APR1.BaseEntry = POR1.DocEntry + APR1.BaseLine = POR1.LineNum：通过这两个条件精准匹配采购订单的每一行对应的预留发票行。
2. LEFT JOIN的使用：如果采购订单某一行没有对应的预留发票，使用LEFT JOIN可以保留采购订单的原始记录，避免数据丢失。
3. 筛选指定订单：在WHERE条件中添加OPOR.DocNum = 'XXX'或OPOR.DocEntry = XXX即可指定查询某一个采购订单的对应预留发票。

内容的提问来源于stack exchange，提问作者irtaza malik