采购订单跟踪报表

知识点

1. 付款只能对应发票整体，不能对应行，所以按订单取付款金额，不能合并收货 和 不能合并应付发票
2. 取付款金额字段，关联原始单据的，不能按行取值，因为付款是按整单发票或者预付款申请来的，则只能通过行表取来源单号，要去重复

代码示例

SELECT DISTINCT t0.CardCode,
                t0.CardName,
                t0.DocNum AS '采购订单号',
                t0.DocDate AS '订单日期',
                t0.DocTotal AS '订单总金额',
                t2.DocNum AS '采购收货号',
                t2.DocDate AS '收货日期',
                t2.DocTotal AS '收货总金额',
                t2.DocNum2 AS '采购发票号',
                t2.DocDate2 AS '发票日期',
                t2.DocTotal2 AS '发票总金额',
                t2.DocNum3 AS '付款号',
                t2.DocDate3 AS '付款日期',
                t2.DocTotal3 AS '付款总金额',
                t6.DocNum AS '预付款申请号',
                t6.DocDate AS '预付款申请日期',
                t6.DocTotal AS '预付款申请总金额',
                t6.DocNum2 AS '预付款付款号',
                t6.DocDate2 AS '预付款付款日期',
                t6.DocTotal2 AS '预付款付款总金额'
  FROM OPOR t0
       INNER JOIN POR1 t1 ON t1.DocEntry = t0.DocEntry
       LEFT JOIN                                                        --采购收货
                (SELECT a0.DocDate,
                        a0.DocNum,
                        a0.DocTotal,
                        a2.DocDate AS DocDate2,
                        a2.DocNum AS DocNum2,
                        a2.DocTotal AS DocTotal2,
                        a2.DocDate2 AS DocDate3,
                        a2.DocNum2 AS DocNum3,
                        a2.DocTotal2 AS DocTotal3,
                        a1.ItemCode,
                        a1.BaseLine,
                        a1.BaseEntry
                   FROM OPDN a0
                        INNER JOIN PDN1 a1 ON a1.DocEntry = a0.DocEntry
                        LEFT JOIN
                        (SELECT b0.DocDate,
                                b0.DocNum,
                                b0.DocTotal,
                                b2.DocDate AS DocDate2,
                                b2.DocNum AS DocNum2,
                                b2.DocTotal AS DocTotal2,
                                b1.ItemCode,
                                b1.BaseLine,
                                b1.BaseEntry
                           FROM OPCH b0
                                INNER JOIN PCH1 b1
                                   ON b1.DocEntry = b0.DocEntry
                                LEFT JOIN
                                (SELECT c0.DocDate,
                                        c0.DocNum,
                                        c1.baseAbs,
                                        c1.SumApplied AS DocTotal
                                   FROM OVPM c0
                                        INNER JOIN VPM2 c1
                                           ON c1.DocNum = c0.DocEntry
                                  WHERE c1.InvType = '18') b2
                                   ON b2.baseAbs = b0.DocEntry
                          WHERE b1.BaseType = '20') a2
                           ON     a2.ItemCode = a1.ItemCode
                              AND a2.BaseEntry = a1.DocEntry
                              AND a2.BaseLine = a1.LineNum
                  WHERE a1.BaseType = '22') t2
          ON     t2.ItemCode = t1.ItemCode
             AND t2.BaseEntry = t1.DocEntry
             AND t2.BaseLine = t1.LineNum
       --采购预付款
       LEFT JOIN (SELECT a2.DocDate AS DocDate2,
                         a2.DocNum AS DocNum2,
                         a2.DocTotal AS DocTotal2,
                         a0.DocDate,
                         a0.DocNum,
                         a0.DocTotal,
                         a1.ItemCode,
                         a1.BaseLine,
                         a1.BaseEntry
                    FROM ODPO a0
                         INNER JOIN DPO1 a1 ON a1.DocEntry = a0.DocEntry
                         LEFT JOIN
                         (SELECT c0.DocDate,
                                 c0.DocNum,
                                 c1.baseAbs,
                                 c1.SumApplied AS DocTotal
                            FROM OVPM c0
                                 INNER JOIN VPM2 c1
                                    ON c1.DocNum = c0.DocEntry
                           WHERE c1.InvType = '204') a2
                            ON a2.baseAbs = a0.DocEntry
                   WHERE a1.BaseType = '22') t6
          ON     t6.ItemCode = t1.ItemCode
             AND t6.BaseEntry = t1.DocEntry
             AND t6.BaseLine = t1.LineNum
WHERE t0.DocDate >=[%0] AND  t0.DocDate<=[%1]
ORDER BY t0.DocNum
FOR BROWSE