业务伙伴科目余额

示例代码

SELECT *
FROM (
	SELECT T3.[CardCode] AS 业务伙伴代码, T3.[CardName] AS 业务伙伴名称, T1.[Account] AS 科目代码, T2.[AcctName] AS 科目名称
		, SUM(T1.[SYSDeb]) - SUM(T1.[SYSCred]) AS 本币余额
		, 'RMB' AS 本币币种, SUM(T1.[FCDebit]) - SUM(T1.[FCCredit]) AS 外币余额
		, T1.[FCCurrency] AS 外币币种
	FROM OJDT T0
		INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
		INNER JOIN OACT T2 ON T1.Account = T2.AcctCode
		INNER JOIN OCRD T3 ON T3.CardCode = T1.ShortName
	WHERE T3.[CardName] <> ''
	GROUP BY T3.[CardCode], T3.[CardName], T1.[Account], T2.[AcctName], T1.[FCCurrency]
	UNION ALL
	SELECT T0.[CardCode] AS 客户供应商代码, T0.[CardName] AS 客户供应商名称, '999999' AS KMDM, '应收账款-交货未开票' AS KMDM
		, SUM(T1.[OpenSum] * (1 + T1.[VatPrcnt] / 100)) AS 未清本币
		, 'RMB' AS 本币币种
		, SUM(T1.[OpenSumFC] * (1 + T1.[VatPrcnt] / 100)) AS 未清外币
		, T1.[Currency] AS 外币币种
	FROM ODLN T0
		INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
	WHERE T0.[CANCELED] = 'N'
		AND T0.[DocStatus] <> 'C'
	GROUP BY T0.[CardCode], T0.[CardName], T1.[Currency]
	UNION ALL
	SELECT T0.[CardCode] AS 客户供应商代码, T0.[CardName] AS 客户供应商名称, '666666' AS KMDM, '应付账款-收货未开票' AS KMDM
		, -1 * SUM(T1.[OpenSum] * (1 + T1.[VatPrcnt] / 100)) AS 未清本币
		, 'RMB' AS 本币币种
		, -1 * SUM(T1.[OpenSumFC] * (1 + T1.[VatPrcnt] / 100)) AS 未清外币
		, T1.[Currency] AS 外币币种
	FROM OPDN T0
		INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
	WHERE T0.[CANCELED] = 'N'
		AND T0.[DocStatus] <> 'C'
	GROUP BY T0.[CardCode], T0.[CardName], T1.[Currency]
) M
WHERE 本币余额 <> '0'
ORDER BY 业务伙伴代码