销售交货明细查询

示例代码

SELECT CONVERT(VARCHAR, T0.DocNum, 1000) AS '单据编号'
	, T1.LineNum + 1 AS '行号', T0.DocDate, T0.CardCode
	, T0.CardName
	, CASE 
		WHEN T0.DocStatus = 'C'
		AND T0.CANCELED = 'N'
		AND T0.DocManClsd = 'N' THEN '已发货'
		WHEN T0.DocStatus = 'O' THEN '未请'
		WHEN T0.DocStatus = 'C'
		AND T0.CANCELED = 'Y' THEN '已取消'
		WHEN DocManClsd = 'Y'
		AND DocStatus = 'C' THEN '手工关闭'
	END AS '单据状态', T1.ItemCode AS '物料编号', T1.Dscription AS '物料/服务描述', T1.Quantity AS '数量', T1.OpenQty AS '剩余未清数量'
	, T1.unitMsr AS '单位', T1.PriceAfVAT AS '含税单价', T1.GTotal AS '价税合计'
	, T1.NumPerMsr * T1.Quantity AS '库存单位数量', t2.InvntryUom AS '库存计量单位', t3.WhsName AS '仓库名称'
	
FROM ODLN T0
	INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
	LEFT JOIN OITM t2 ON T1.ItemCode = t2.ItemCode
	LEFT JOIN OWHS t3 ON t3.WhsCode = T1.WhsCode
WHERE (T0.DocDate >= [%1]
		OR [%1] = '')
	AND (T0.DocDate <= [%2]
		OR [%2] = '')
UNION ALL
SELECT '退货' + CONVERT(VARCHAR, T0.DocNum, 1000)
	, T1.LineNum + 1 AS '行号', T0.DocDate, T0.CardCode
	, T0.CardName, '已结算' AS '单据状态'
	, T1.ItemCode AS '物料编号', T1.Dscription AS '物料/服务描述', -1 * Quantity AS '数量', -1 * T1.OpenQty AS '剩余未清数量'
	, T1.unitMsr AS '单位', -1 * T1.PriceAfVAT AS '含税单价', -1 * T1.GTotal AS '价税合计'
	
	, -1 * T1.NumPerMsr * T1.Quantity AS '库存单位数量', t2.InvntryUom AS '库存计量单位'
	, t3.WhsName AS '仓库名称'
FROM ORDN T0
	INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry
	LEFT JOIN OITM t2 ON T1.ItemCode = t2.ItemCode
	LEFT JOIN OWHS t3 ON t3.WhsCode = T1.WhsCode
WHERE (T0.DocDate >= [%1]
		OR [%1] = '')
	AND (T0.DocDate <= [%2]
		OR [%2] = '')
UNION ALL
SELECT '贷项' + CONVERT(VARCHAR, T0.DocNum, 1000)
	, T1.LineNum + 1 AS '行号', T0.DocDate, T0.CardCode
	, T0.CardName, '已结算' AS '单据状态'
	, T1.ItemCode AS '物料编号', T1.Dscription AS '物料/服务描述',-1 * Quantity AS '数量', -1 * T1.OpenQty AS '剩余未清数量'
	, T1.unitMsr AS '单位', -1 * T1.PriceAfVAT AS '含税单价', -1 * T1.GTotal AS '价税合计'
	, -1 * T1.NumPerMsr * T1.Quantity AS '库存单位数量', t2.InvntryUom AS '库存计量单位'
	, t3.WhsName AS '仓库名称'
FROM ORIN T0
	INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
	LEFT JOIN OITM t2 ON T1.ItemCode = t2.ItemCode
	LEFT JOIN OWHS t3 ON t3.WhsCode = T1.WhsCode
WHERE (T0.DocDate >= [%1]
		OR [%1] = '')
	AND (T0.DocDate <= [%2]
		OR [%2] = '')