-----表名：采购订单跟踪报表
-----逻辑：以采购订单为核心，跟踪所有相关业务单据
-----作者：五六零 www.sap560.com
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声明：欢迎来到五六零学院。
