管家婆ERP V3Ⅱ生产拆装单生成金蝶财务软件K3 V10.3标准凭证引入的SQL select 查询语句

双&号括起来的变量要先替换后才能执行， left(k.usercode,3)='021'  是限制查询仓库的范围，因为多家母子公司使用一套ERP，所以需要限制只查询某一家公司的数据

SELECT a.BillDate AS FDate, YEAR(a.BillDate) AS FYear, MONTH(a.BillDate) AS FPeriod,
      '记' AS FGroupID, '1' AS FNumber, '1243.02' AS FAccountNum,
      '废纸类商品（项目核算）' AS FAccountName, 'RMB' AS FCurrencyNum,
      '人民币' AS FCurrencyName,
      CASE WHEN a.qty > 0 THEN a.Total ELSE a.total * - 1 END AS FAmountFor,
      CASE WHEN (a.qty) > 0 THEN a.total ELSE 0 END AS FDebit, CASE WHEN (a.qty)
      < 0 THEN a.total ELSE 0 END AS FCredit, '童美华' AS FPreparerID,
      'NONE' AS FCheckerID, 'NONE' AS FApproveID, 'NONE' AS FCashierID,
      '' AS FHandler, '*' AS FSettleTypeID, '' AS FSettleNo,
      i.BillCode + ',' + p.FullName + ':' + LTRIM(STR(a.Qty, 15, 3))
      + '吨;单价:' + LTRIM(STR(a.Price, 15, 2))
      + '元/吨,' + i.Comment + '；' + LTRIM(i.explain) AS FExplanation, 0 AS FQuantity,
      '*' AS FMeasureUnitID, 0 AS FUnitPrice, '' AS FReference, a.BillDate AS FTransDate,
      '' AS FTransNo, 0 AS FAttachments, 999 AS FSerialNum, '' AS FObjectName,
      '' AS FParameter, 1 AS FExchangeRate, 0 AS FEntryID, '部门---' + LEFT(k.UserCode, 6)
      + '---' + LEFT(k.FullName, 5) + '||库位---' + RIGHT(LTRIM(k.UserCode), 2)
      + '---' + RIGHT(LTRIM(k.FullName), 3) AS FItem, 0 AS FPosted, '' AS FInternalInd,
      '' AS FCashFlow, k.UserCode AS Expr1, k.FullName AS Expr2, i.BillCode
FROM InOutstocktable a INNER JOIN
      Stock k ON k.typeId = a.KtypeId INNER JOIN
      ptype p ON p.typeId = a.PtypeId INNER JOIN
      BillIndex i ON i.BillNumberId = a.BillNumberId
WHERE (a.BillType = 16) AND (a.BillDate = '&上日&') and left(k.usercode,3)='021' AND (i.RedWord = 0)
UNION ALL
SELECT a.BillDate AS FDate, MAX(YEAR(a.BillDate)) AS FYear, MAX(MONTH(a.BillDate))
      AS FPeriod, '记' AS FGroupID, '1' AS FNumber, '2121.02' AS FAccountNum,
      '废纸类应付款' AS FAccountName, 'RMB' AS FCurrencyNum,
      '人民币' AS FCurrencyName, SUM(CASE WHEN a.qty < 0 THEN (total)
      ELSE - total END) AS FAmountFor,
      CASE WHEN SUM(CASE WHEN a.qty < 0 THEN (total) ELSE - total END)
      > 0 THEN SUM(CASE WHEN a.qty < 0 THEN (total) ELSE - total END)
      ELSE 0 END AS FDebit, CASE WHEN SUM(CASE WHEN a.qty < 0 THEN (total)
      ELSE - total END) < 0 THEN - SUM(CASE WHEN a.qty < 0 THEN (total)
      ELSE - total END) ELSE 0 END AS FCredit, '童美华' AS FPreparerID,
      'NONE' AS FCheckerID, 'NONE' AS FApproveID, 'NONE' AS FCashierID,
      '' AS FHandler, '*' AS FSettleTypeID, '' AS FSettleNo,
      i.BillCode + '，分捡拆装价差' AS FExplanation, 0 AS FQuantity,
      '*' AS FMeasureUnitID, 0 AS FUnitPrice, '' AS FReference, MAX(a.BillDate)
      AS FTransDate, '' AS FTransNo, 0 AS FAttachments, 999 AS FSerialNum,
      '' AS FObjectName, '' AS FParameter, 1 AS FExchangeRate, 0 AS FEntryID,
      MAX('部门---' + LEFT(k.UserCode, 6) + '---' + LEFT(k.FullName, 5)
      + '||库位---04---分捡拆装价差') AS FItem, 0 AS FPosted, '' AS FInternalInd,
      '' AS FCashFlow,max(k.UserCode) AS Expr1, MAX(k.FullName) AS Expr2,
      i.BillCode
FROM InOutstocktable a INNER JOIN
      Stock k ON k.typeId = a.KtypeId INNER JOIN
      BillIndex i ON i.BillNumberId = a.BillNumberId
WHERE (a.BillType = 16) AND (a.BillDate = '&上日&') and left(k.usercode,3)='021' AND (i.RedWord = 0)
GROUP BY a.BillDate, i.BillCode
ORDER BY a.BillDate, k.UserCode, i.BillCode