Syspro中AP发票信息涉及哪些表？AP发票付款明细报表生成求助

问题背景 #

我需要生成一份包含所有AP发票、贷项通知单及其付款明细的报表，目前使用APInvoice和APInvoicePay两张表，但不确定关联逻辑是否正确。顾问曾提供以下关联语句：

FROM APInvoice LEFT OUTER JOIN
APInvoicePay ON APInvoice.Invoice = APInvoicePay.Invoice AND APInvoice.EntryNumber = APInvoicePay.EntryNumber AND APInvoice.Supplier = APInvoicePay.Supplier

但实际使用时发现无法关联到所有付款明细，现附上我的SQL脚本，请求帮忙排查解决。

我的SQL脚本 #

SELECT    '830' AS Systemid, 'R' AS Companyid

, dbo.ApInvoice.InvoiceYear AS ERPDocumentYear
, dbo.ApInvoice.Supplier AS SupplierNumber
,  isnull(replace((left(dbo.ApInvoice.[InvoiceDate],10)),'-',''),'') as DocumentDate
,dbo.ApInvoice.[Invoice]  as DocumentNumber,
               
      ( case when OrigInvValue ='0.00'  or OrigInvValue ='0'
      then [PaymGrossValue] 
      when (OrigInvValue ='0.00' or OrigInvValue ='0') and  ([PaymGrossValue] ='0.00'or [PaymGrossValue] ='0')  then [MthInvBal1]
      else OrigInvValue end )as DocumentAmount
                       
                      , (case when (cast((case when OrigInvValue ='0.00' or  OrigInvValue ='0'
      then [PaymGrossValue] 
      when (OrigInvValue ='0.00'  or  OrigInvValue ='0' )and  ([PaymGrossValue] ='0.00' or [PaymGrossValue] ='0')  then [MthInvBal1]
      else OrigInvValue end) as decimal (18,2))) < 0.00 then '' else 'X' end) as InvoiceCredit
                       
                      , isnull(CASE WHEN (dbo.ApInvoice.PaymentNumber) <> '' THEN '3' ELSE '1' END,'') AS ERPDocumentStatus
                      , isnull(replace((left(dbo.ApInvoicePay.JournalDate,10)),'-',''),'')  AS PaidDate
                      ,  isnull(dbo.ApInvoice.PostCurrency,'') AS Currency
                      , isnull(dbo.ApInvoice.PaymentNumber,'') AS PaymentDocument
                      ,  isnull(replace((left(dbo.ApInvoice.DueDate,10)),'-',''),'')  AS PaymentDueDate
                      ,  isnull(replace((left(dbo.ApInvoice.JournalDate,10)),'-',''),'')  AS CreationDate
                      into ERP1
FROM         dbo.ApInvoice LEFT OUTER JOIN
                      dbo.ApInvoicePay ON dbo.ApInvoice.Supplier = dbo.ApInvoicePay.Supplier AND dbo.ApInvoice.Invoice = dbo.ApInvoicePay.Invoice AND dbo.ApInvoice.PaymentNumber = dbo.ApInvoicePay.PaymentNumber AND 
                     ApInvoice.PaymentNumber <> ''

问题排查与修正方案 #

核心问题 #

1. 关联条件错误：在LEFT JOIN条件中加入ApInvoice.PaymentNumber <> ''，直接过滤掉了所有未分配付款编号的发票，违背了LEFT JOIN保留左表所有记录的初衷，同时遗漏了付款记录未关联发票付款编号的明细。
2. 关联键误用：顾问给出的关联条件包含EntryNumber，但你改用了PaymentNumber。通常EntryNumber是发票和付款记录的唯一关联键，PaymentNumber可能对应多笔付款或未被正确赋值，这是无法关联所有付款明细的主要原因。
3. 数值处理不严谨：用字符串格式比较数值（如OrigInvValue ='0.00'）容易引发类型转换异常，且逻辑冗余重复。

修正后的脚本 #

SELECT 
    '830' AS Systemid, 
    'R' AS Companyid,
    dbo.ApInvoice.InvoiceYear AS ERPDocumentYear,
    dbo.ApInvoice.Supplier AS SupplierNumber,
    ISNULL(REPLACE(LEFT(dbo.ApInvoice.[InvoiceDate], 10), '-', ''), '') AS DocumentDate,
    dbo.ApInvoice.[Invoice] AS DocumentNumber,
    -- 简化金额计算逻辑，改用数值比较
    CASE 
        WHEN ISNULL(dbo.ApInvoice.OrigInvValue, 0) = 0 THEN 
            CASE WHEN ISNULL(dbo.ApInvoicePay.PaymGrossValue, 0) = 0 THEN dbo.ApInvoice.MthInvBal1 
                 ELSE dbo.ApInvoicePay.PaymGrossValue 
            END
        ELSE dbo.ApInvoice.OrigInvValue 
    END AS DocumentAmount,
    -- 简化贷项标识逻辑
    CASE WHEN CAST(
        CASE 
            WHEN ISNULL(dbo.ApInvoice.OrigInvValue, 0) = 0 THEN 
                CASE WHEN ISNULL(dbo.ApInvoicePay.PaymGrossValue, 0) = 0 THEN dbo.ApInvoice.MthInvBal1 
                     ELSE dbo.ApInvoicePay.PaymGrossValue 
                END
            ELSE dbo.ApInvoice.OrigInvValue 
        END AS DECIMAL(18,2)) < 0 THEN '' ELSE 'X' END AS InvoiceCredit,
    ISNULL(CASE WHEN dbo.ApInvoice.PaymentNumber <> '' THEN '3' ELSE '1' END, '') AS ERPDocumentStatus,
    ISNULL(REPLACE(LEFT(dbo.ApInvoicePay.JournalDate, 10), '-', ''), '') AS PaidDate,
    ISNULL(dbo.ApInvoice.PostCurrency, '') AS Currency,
    ISNULL(dbo.ApInvoice.PaymentNumber, '') AS PaymentDocument,
    ISNULL(REPLACE(LEFT(dbo.ApInvoice.DueDate, 10), '-', ''), '') AS PaymentDueDate,
    ISNULL(REPLACE(LEFT(dbo.ApInvoice.JournalDate, 10), '-', ''), '') AS CreationDate
INTO ERP1
FROM dbo.ApInvoice 
LEFT OUTER JOIN dbo.ApInvoicePay 
    ON dbo.ApInvoice.Supplier = dbo.ApInvoicePay.Supplier 
    AND dbo.ApInvoice.Invoice = dbo.ApInvoicePay.Invoice 
    AND dbo.ApInvoice.EntryNumber = dbo.ApInvoicePay.EntryNumber -- 恢复顾问建议的关联键
-- 移除PaymentNumber过滤条件，确保所有发票都能被保留

补充说明 #

• 保留LEFT JOIN可确保所有AP发票（包括未付款、贷项通知单）都出现在报表中，无对应付款明细的记录会显示NULL值。
• 若贷项通知单在表中有专属标识字段（如InvoiceType），可添加WHERE子句确保包含这类记录。
• 对数值字段使用ISNULL处理NULL值，避免类型转换错误。

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