日记账分录：发出商品明细

发出商品明细
取值日记账分录OJDT，单据范围仅限：交货单、退货单、应收发票

SELECT T0.[RefDate]
	, CASE 
		WHEN T0.[TransType] = '15' THEN 'DN' + N' ' + CAST(T0.[BaseRef] AS NVARCHAR(50))
		WHEN T0.[TransType] = '16' THEN 'RE' + N' ' + CAST(T0.[BaseRef] AS NVARCHAR(50))
		WHEN T0.[TransType] = '13' THEN 'IN' + N' ' + CAST(T0.[BaseRef] AS NVARCHAR(50))
	END AS '单据编号', T0.[TransId], T1.[Debit], T1.[Credit], T1.[ContraAct]
	, T2.[AcctName] AS '抵消科目名称', T0.[Memo]
	, CASE 
		WHEN T0.[TransType] = '15' THEN TA.[BaseType] + N' ' + CAST(TA.[BaseEntry] AS NVARCHAR(50))
		WHEN T0.[TransType] = '16' THEN TB.[BaseType] + N' ' + CAST(TB.[BaseEntry] AS NVARCHAR(50))
		WHEN T0.[TransType] = '13' THEN TC.[BaseType] + N' ' + CAST(TC.[BaseEntry] AS NVARCHAR(50))
	END AS '对应的单据来源'
	, CASE 
		WHEN T0.[TransType] = '15' THEN TA.[ItemCode]
		WHEN T0.[TransType] = '16' THEN TB.[ItemCode]
		WHEN T0.[TransType] = '13' THEN TC.[ItemCode]
	END AS '物料编号'
	, CASE 
		WHEN T0.[TransType] = '15' THEN TA.[Dscription]
		WHEN T0.[TransType] = '16' THEN TB.[Dscription]
		WHEN T0.[TransType] = '13' THEN TC.[Dscription]
	END AS '物料名称'
	, CASE 
		WHEN T0.[TransType] = '15' THEN TA.[Quantity]
		WHEN T0.[TransType] = '16' THEN TB.[Quantity]
		WHEN T0.[TransType] = '13' THEN TC.[Quantity]
	END AS '数量'
	, CASE 
		WHEN T0.[TransType] = '15' THEN TA.[StockValue]
		WHEN T0.[TransType] = '16' THEN TB.[StockValue]
		WHEN T0.[TransType] = '13' THEN TC.[StockValue]
	END AS '成本', 
CASE 
		WHEN T0.[TransType] = '15' THEN TA.[LineTotal]
		WHEN T0.[TransType] = '16' THEN TB.[LineTotal]
		WHEN T0.[TransType] = '13' THEN TC.[LineTotal]
	END AS '收入'
FROM OJDT T0
	INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
	LEFT JOIN OACT T2 ON T1.[ContraAct] = T2.[AcctCode]
	LEFT JOIN (
		SELECT T0.[DocEntry]
			, CASE 
				WHEN T0.[BaseType] = '15' THEN 'DN'
				WHEN T0.[BaseType] = '16' THEN 'RE'
				WHEN T0.[BaseType] = '13' THEN 'IN'
				WHEN T0.[BaseType] = '14' THEN 'CN'
				WHEN T0.[BaseType] = '17' THEN 'SO'
			END AS 'BaseType', T0.[BaseEntry], T0.[ItemCode], T0.[Dscription], T0.[Quantity]
			, T0.[StockValue],T0.[LineTotal]
		FROM DLN1 T0
	) TA
	ON TA.[DocEntry] = T0.[BaseRef]
		AND T0.[TransType] = '15'
	LEFT JOIN (
		SELECT T0.[DocEntry]
			, CASE 
				WHEN T0.[BaseType] = '15' THEN 'DN'
				WHEN T0.[BaseType] = '16' THEN 'RE'
				WHEN T0.[BaseType] = '13' THEN 'IN'
				WHEN T0.[BaseType] = '14' THEN 'CN'
				WHEN T0.[BaseType] = '17' THEN 'SO'
			END AS 'BaseType', T0.[BaseEntry], T0.[ItemCode], T0.[Dscription], T0.[Quantity]
			, T0.[StockValue],T0.[LineTotal]
		FROM RDN1 T0
	) TB
	ON TB.[DocEntry] = T0.[BaseRef]
		AND T0.[TransType] = '16'
	LEFT JOIN (
		SELECT T0.[DocEntry]
			, CASE 
				WHEN T0.[BaseType] = '15' THEN 'DN'
				WHEN T0.[BaseType] = '16' THEN 'RE'
				WHEN T0.[BaseType] = '13' THEN 'IN'
				WHEN T0.[BaseType] = '14' THEN 'CN'
				WHEN T0.[BaseType] = '17' THEN 'SO'
			END AS 'BaseType', T0.[BaseEntry], T0.[ItemCode], T0.[Dscription], T0.[Quantity]
			, T0.[LineTotal],  T0.[StockValue] 
		FROM INV1 T0
	) TC
	ON TC.[DocEntry] = T0.[BaseRef]
		AND T0.[TransType] = '13'
WHERE T0.[TransType] IN ('15', '16', '13','30')  AND T1.[Account]='140701'
	AND T0.[RefDate] >= [%0]
	AND T0.[RefDate] <= [%1]
ORDER BY T0.[TransType], T0.[BaseRef]