销售订单跟踪报表

-----表名：销售订单跟踪报表 -----逻辑：以销售订单为核心，跟踪所有相关业务单据

代码示例

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 ORDR t0
       INNER JOIN RDR1 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 ODLN a0
                        INNER JOIN DLN1 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 OINV b0
                                INNER JOIN INV1 b1
                                   ON b1.DocEntry = b0.DocEntry
                                LEFT JOIN
                                (SELECT c0.DocDate,
                                        c0.DocNum,
                                        c1.baseAbs,
                                        c1.SumApplied AS DocTotal
                                   FROM ORCT c0
                                        INNER JOIN RCT2 c1
                                           ON c1.DocNum = c0.DocEntry
                                  WHERE c1.InvType = '13') b2
                                   ON b2.baseAbs = b0.DocEntry
                          WHERE b1.BaseType = '15') a2
                           ON     a2.ItemCode = a1.ItemCode
                              AND a2.BaseEntry = a1.DocEntry
                              AND a2.BaseLine = a1.LineNum
                  WHERE a1.BaseType = '17') 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 ODPI a0
                         INNER JOIN DPI1 a1 ON a1.DocEntry = a0.DocEntry
                         LEFT JOIN
                         (SELECT c0.DocDate,
                                 c0.DocNum,
                                 c1.baseAbs,
                                 c1.SumApplied AS DocTotal
                            FROM ORCT c0
                                 INNER JOIN RCT2 c1
                                    ON c1.DocNum = c0.DocEntry
                           WHERE c1.InvType = '203') a2
                            ON a2.baseAbs = a0.DocEntry
                   WHERE a1.BaseType = '17') 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 DESC
FOR BROWSE