销售订单跟踪报表（含行明细）

效果图

销售

代码示例

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

SELECT DISTINCT t0.CardCode, t0.CardName, T22.SlpName, t0.DocNum AS '销售订单号', t0.DocDate AS '订单日期'
	, T1.[ItemCode] AS '物料编号', T1.[Dscription], T1.[Quantity] AS '数量', T1.[Price] AS '单价', T1.[PriceAfVAT] AS '毛价'
	, T1.[LineTotal] AS '行总计', T23.[WhsName], t0.DocTotal AS '订单总金额', T0.[PaidToDate] 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
	INNER JOIN OSLP T22 ON T0.[SlpCode] = T22.[SlpCode]
	INNER JOIN OWHS T23 ON T1.[WhsCode] = T23.[WhsCode]
	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