采购订单跟踪报表(最后)

代码示例

SELECT T0.[CardCode], T0.[CardName], T0.[DoCENTRY] AS '采购订单号', T0.[TaxDate], T0.[DocDueDate] AS '交货日期'
	, T0.[DocTotal] AS '单据总金额'
	, T0.[DocTotal] - ISNULL(T12.FKZJ, 0) - ISNULL(T13.YFKZJ, 0) AS '待付款金额'
	, T0.[DocTotal] - ISNULL(T1.SH, 0) AS '待交货金额'
	, T0.[DocTotal] - ISNULL(T4.FP, 0) AS '未做应付发票金额'
	, ISNULL(T12.FKZJ, 0) + ISNULL(T13.YFKZJ, 0) AS '付款总计'
	, T9.FKnum AS '最后付款单号', T8.FKDATE AS '最后付款日期', T10.YFKDATE AS '最后预付款付款日期', T11.YFKnum AS '最后预付款付款单号', T3.SHnum AS '最后收货单号'
	, T2.SHDATE AS '最后收货日期', T1.SH AS '收货总计', T6.FPnum AS '最后发票单号', T5.FPDATE AS '最后发票日期', T4.FP AS '发票总计'
	, datediff(d, T10.YFKDATE, T5.FPDATE) AS '回票周期'
FROM OPOR T0
	LEFT JOIN (
		SELECT T1.[BaseEntry], SUM(T1.[GTotal]) AS 'SH'
		FROM OPDN T0
			INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T1.[BaseEntry]
	) T1
	ON T1.BaseEntry = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T0.TaxDate) AS 'SHDATE', T1.[BaseEntry]
		FROM OPDN T0
			INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T1.[BaseEntry]
	) T2
	ON T2.[BaseEntry] = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T0.DocNum) AS 'SHnum', T1.[BaseEntry]
		FROM OPDN T0
			INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T1.[BaseEntry]
	) T3
	ON T3.[BaseEntry] = T0.DocEntry
	LEFT JOIN (
		SELECT T2.[BaseEntry], SUM(T1.[GTotal]) AS 'FP'
		FROM OPCH T0
			INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN PDN1 T2
			ON T2.[DocEntry] = T1.[BaseEntry]
				AND T1.[BaseLine] = T2.[LineNum]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T2.[BaseEntry]
	) T4
	ON T4.[BaseEntry] = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T0.TaxDate) AS 'FPDATE', T2.[BaseEntry]
		FROM OPCH T0
			INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN PDN1 T2
			ON T2.[DocEntry] = T1.[BaseEntry]
				AND T1.[BaseLine] = T2.[LineNum]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T2.[BaseEntry]
	) T5
	ON T5.[BaseEntry] = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T0.DocNum) AS 'FPnum', T2.[BaseEntry]
		FROM OPCH T0
			INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN PDN1 T2
			ON T2.[DocEntry] = T1.[BaseEntry]
				AND T1.[BaseLine] = T2.[LineNum]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T2.[BaseEntry]
	) T6
	ON T6.[BaseEntry] = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T4.TaxDate) AS 'FKDATE', T2.[BaseEntry]
		FROM OPCH T0
			INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN PDN1 T2
			ON T2.[DocEntry] = T1.[BaseEntry]
				AND T1.[BaseLine] = T2.[LineNum]
			INNER JOIN VPM2 T3 ON T3.[baseAbs] = T0.[DocEntry]
			INNER JOIN OVPM T4 ON T4.[DocEntry] = T3.[DocNum]
		WHERE T3.InvType = '18'
			AND T0.[CANCELED] = 'N'
			AND T1.BaseType = '20'
			AND T2.BaseType = '22'
		GROUP BY T2.[BaseEntry]
	) T8
	ON T8.BaseEntry = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T4.DocNum) AS 'FKnum', T2.[BaseEntry]
		FROM OPCH T0
			INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN PDN1 T2
			ON T2.[DocEntry] = T1.[BaseEntry]
				AND T1.[BaseLine] = T2.[LineNum]
			INNER JOIN VPM2 T3 ON T3.[baseAbs] = T0.[DocEntry]
			INNER JOIN OVPM T4 ON T4.[DocEntry] = T3.[DocNum]
		WHERE T3.InvType = '18'
			AND T0.[CANCELED] = 'N'
			AND T1.BaseType = '20'
			AND T2.BaseType = '22'
		GROUP BY T2.[BaseEntry]
	) T9
	ON T9.BaseEntry = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T4.[TaxDate]) AS 'YFKDATE', T0.[BaseEntry]
		FROM DPO1 T0
			INNER JOIN VPM2 T3 ON T3.[baseAbs] = T0.[DocEntry]
			INNER JOIN OVPM T4 ON T4.[DocEntry] = T3.[DocNum]
		WHERE T0.BaseType = '22'
			AND T3.InvType = '204'
			AND T4.[CANCELED] = 'N'
		GROUP BY T0.[BaseEntry]
	) T10
	ON T10.BaseEntry = T0.DocEntry
	LEFT JOIN (
		SELECT MAX(T4.[DocNum]) AS 'YFKnum', T0.[BaseEntry]
		FROM DPO1 T0
			INNER JOIN VPM2 T3 ON T3.[baseAbs] = T0.[DocEntry]
			INNER JOIN OVPM T4 ON T4.[DocEntry] = T3.[DocNum]
		WHERE T0.BaseType = '22'
			AND T3.InvType = '204'
			AND T4.[CANCELED] = 'N'
		GROUP BY T0.[BaseEntry]
	) T11
	ON T11.BaseEntry = T0.DocEntry
	LEFT JOIN (
		SELECT SUM(T0.[PaidToDate]) AS 'FKZJ', T2.[BaseEntry]
		FROM OPCH T0
			INNER JOIN (
				SELECT T0.[BaseEntry], T0.[DocEntry]
				FROM PCH1 T0
				WHERE T0.BaseType = '20'
				GROUP BY T0.[BaseEntry], T0.[DocEntry]
			) T1
			ON T1.[DocEntry] = T0.[DocEntry]
			INNER JOIN (
				SELECT T0.[BaseEntry], T0.[DocEntry]
				FROM PDN1 T0
				GROUP BY T0.[BaseEntry], T0.[DocEntry]
			) T2
			ON T2.[DocEntry] = T1.[BaseEntry]
		WHERE T2.[BaseEntry] IS NOT NULL
			AND T0.[CANCELED] = 'N'
		GROUP BY T2.[BaseEntry]
	) T12
	ON T12.BaseEntry = T0.Docentry
	LEFT JOIN (
		SELECT SUM(T0.[PaidToDate]) AS 'YFKZJ', T1.[BaseEntry]
		FROM ODPO T0
			INNER JOIN (
				SELECT T0.[BaseEntry], T0.[DocEntry]
				FROM DPO1 T0
				WHERE T0.BaseType = '22'
				GROUP BY T0.[BaseEntry], T0.[DocEntry]
			) T1
			ON T1.[DocEntry] = T0.[DocEntry]
		WHERE T0.[CANCELED] = 'N'
		GROUP BY T1.[BaseEntry]
	) T13
	ON T13.BaseEntry = T0.DocEntry
WHERE T0.[TaxDate] >= '[%0]'
	AND T0.[TaxDate] <= '[%1]'
ORDER BY T0.[DoCENTRY] DESC