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

来源：互联网发布：linux mint vs deepin 编辑：程序博客网时间：2024/04/29 04:13

双&号括起来的变量要先替换后才能执行， 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,
      CONVERT(CHAR(10), i.billdate, 23) +':'+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,
case when left(k.usercode,8)='00100104' then '部门---' + k.UserCode
      + '---' + k.FullName + '||库位---02---成品仓' else '部门---' + LEFT(k.UserCode, 6)
      + '---' + LEFT(k.FullName, 5) + '||库位---' + RIGHT(LTRIM(k.UserCode), 2)
      + '---' + RIGHT(LTRIM(k.FullName), 3) end 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 = 17) AND (i.RedWord = 0) and a.billdate='&上日&' and (left(k.usercode,3)='021' or left(k.usercode,3)='001')
ORDER BY a.BillDate, i.BillCode, k.UserCode