采购订单交货及开票明细

使用说明：查询采购订单关联收货数量金额及发票数量金额

代码示例

SELECT T0.[CardCode], T0.[CardName], T2.[SlpName], t0.docentry AS '采购订单号', T0.[NumAtCard] AS '客户订单号'
	, T1.[ItemCode], T1.[Dscription], T1.[SubCatNum], T0.[TaxDate] AS '订单日期', T1.[PriceAfVAT] AS '含税单价'
	, T1.[Quantity] AS '订单数量', T3.[OnHand] AS '库存数量', T1.[GTotal] AS '订单金额', T1.[ShipDate] AS '工厂交期'
	, TS.[DocDate] AS '到货日期', TS.[Quantity] AS '到货数量', TS.[GTotal] AS '到货总金额', TS.FD AS '开票日期', TS.FM AS '开票单价'
	, TS.FQ AS '开票数量', TS.FJ AS '开票总金额', TS.WS AS '未开票数量', TS.WS * TS.FM AS '未开票总金额'
FROM OPOR T0
	INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode]
	INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode]
	LEFT JOIN (
		SELECT T0.ITEMCODE, T0.[BaseEntry], T0.[BaseLine], T0.[DocDate], T0.[Quantity]
			, T0.[GTotal], TA.[DocDate] AS 'FD', TA.[Quantity] AS 'FQ', TA.[PriceAfVAT] AS 'FM', TA.[GTotal] AS 'FJ'
			, T0.[Quantity] - TB.KS AS 'WS'
		FROM PDN1 T0
			LEFT JOIN (
				SELECT T0.ITEMCODE, T0.[DocDate], T0.[GTotal], T0.[OpenQty], T0.[OpenSum]
					, T0.[BaseLine], T0.[BaseEntry], T0.[Quantity], T0.[PriceAfVAT]
				FROM PCH1 T0
			) TA
			ON TA.[BaseEntry] = t0.docentry
				AND TA.[BaseLine] = T0.[LineNum]
				AND TA.ITEMCODE = T0.ITEMCODE
			LEFT JOIN (
				SELECT T0.[BaseEntry], T0.[BaseLine], SUM(T0.[Quantity]) AS 'ks'
				FROM PCH1 T0
				GROUP BY T0.[BaseEntry], T0.[BaseLine]
			) TB
			ON TB.[BaseEntry] = t0.docentry
				AND TB.[BaseLine] = T0.[LineNum]
	) TS
	ON TS.[BaseEntry] = t1.docentry
		AND TS.[BaseLine] = T1.[LineNum]
		AND TS.ITEMCODE = T1.ITEMCODE
WHERE T0.[TaxDate] >= '[%0]'
	AND T0.[TaxDate] <= '[%1]'
ORDER BY t0.docentry DESC