点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)您好,专家们,关于为每个文档创建...
点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)您好,专家们,关于为每个文档创建...
加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)
您好,专家们,关于为每个文档创建标准查询报告,我需要您的帮助。 当他们可以从SAP Business One中的任何文档生成报告时,都会收到此查询。 我现在想要的是动态包含基础对象或目标对象表,或在查询中加入它们。 例如,他们在AR发票中生成报告。 我可以获得基础对象和目标对象。 下面是我的SQL查询。
-ALTER PROC [dbo]。[spAppMarketingDoc] @DocKey int,@ ObjectId INT - 如 宣告@DocKey int = 8 宣告@ObjectId int = 13 宣告@object NVARCHAR(4000),@ BaseObject nvarchar(4000) 如果(@ ObjectId = 13)SET @object ='INV' ELSE IF(@ ObjectId = 14)SET @object ='RIN' ELSE IF(@ ObjectId = 15)SET @object ='DLN' ELSE IF(@ ObjectId = 16)SET @object ='RDN' ELSE IF(@ ObjectId = 17)SET @object ='RDR' ELSE IF(@ ObjectId = 18)SET @object ='PCH' ELSE IF(@ ObjectId = 19)SET @object ='RPC' ELSE IF(@ ObjectId = 20)SET @object ='PDN' ELSE IF(@ ObjectId = 21)SET @object ='RPD' ELSE IF(@ ObjectId = 22)SET @object ='POR' ELSE IF(@ ObjectId = 23)SET @object ='QUT' ELSE IF(@ ObjectId = 24)SET @object ='RCT' ELSE IF(@ ObjectId = 25)SET @object ='DPS' ELSE IF(@ ObjectId = 46)SET @object ='VPM' ELSE IF(@ ObjectId = 58)SET @object ='INM' ELSE IF(@ ObjectId = 59)SET @object ='IGN' ELSE IF(@ ObjectId = 60)SET @object ='IGE' ELSE IF(@ ObjectId = 67)SET @object ='WTR' ELSE IF(@ ObjectId = 68)SET @object ='WKO' ELSE IF(@ ObjectId = 69)SET @object ='IPF' ELSE IF(@ ObjectId = 112)SET @object ='DRF' ELSE IF(@ ObjectId = 132)SET @object ='CIN' ELSE IF(@ ObjectId = 140)SET @object ='PDF' ELSE IF(@ ObjectId = 162)SET @object ='MRV' ELSE IF(@ ObjectId = 163)SET @object ='CPI' ELSE IF(@ ObjectId = 164)SET @object ='CPV' ELSE IF(@ ObjectId = 165)SET @object ='CSI' ELSE IF(@ ObjectId = 166)SET @object ='CSV' ELSE IF(@ ObjectId = 191)SET @object ='SCL' ELSE IF(@ ObjectId = 198)设置@object ='FCT' ELSE IF(@ ObjectId = 199)SET @object ='MSN' ELSE IF(@ ObjectId = 202)SET @object ='WOR' ELSE IF(@ ObjectId = 203)SET @object ='DPI' ELSE IF(@ ObjectId = 204)SET @object ='DPO' ELSE IF(@ ObjectId = 321)SET @object ='ITR' ELSE IF(@ ObjectId = 140000009)SET @object ='OEI' ELSE IF(@ ObjectId = 140000010)SET @object ='IEI' ELSE IF(@ ObjectId = 540000006)SET @object ='PQT' ELSE IF(@ ObjectId = 1470000113)SET @object ='PRQ' ------------------------------------------ 从ORIN WHERE 1 = 2中选择*进入#DOC SELECT * INRIN#1从RIN1 1 = 2 从RIN3的SELECT * INTO#DOC3到1 = 2 从RIN9的SELECT * INTO#DOC9到1 = 2 从RIN10的SELECT * INTO#DOC10到1 = 2 插入#DOC EXEC('SELECT * FROM O'+ @ object +'WHERE DocEntry ='+ @ DocKey) 插入#DOC1 EXEC('SELECT * FROM'+ @ object +'1 WHERE DocEntry ='+ @ DocKey) 插入#DOC3 EXEC('SELECT * FROM'+ @ object +'3 WHERE DocEntry ='+ @ DocKey) 插入#DOC9 EXEC('SELECT * FROM'+ @ object +'9 WHERE DocEntry ='+ @ DocKey) 插入#DOC10 EXEC('SELECT * FROM'+ @ object + '10 WHERE DocEntry ='+ @ DocKey) ------------------------------------------ 声明@LineSeq INT 十进制@_LineNum INT 十进制@_LineSeq INT 宣告@LineType NCHAR 声明@TotalLevel INT 创建表#TempTab (DocEntry INT, LineNum INT, _LineNum INT, _LineSeq INT, LineType NCHAR(1), TotalLevel INT, SubTotalQty NUMERIC(19,6), 小计NUMERIC(19,6), TotalFrgn NUMERIC(19,6), TotalSumSy NUMERIC(19,6), G总计NUMERIC(19,6), GTotalFC NUMERIC(19,6), GTotalSC NUMERIC(19,6), VatSum NUMERIC(19,6), VatSumFrgn NUMERIC(19,6), VatSumSy NUMERIC(19,6), LineText NTEXT ) 插入到#TempTab(DocEntry,LineNum,_LineNum,_LineSeq,LineType,LineText)中选择DocEntry,VisOrder,VisOrder,-1,LineType,''FROM#DOC1 插入#TempTab(DocEntry,LineNum,_LineNum,_LineSeq,LineType,LineText)选择DocEntry,-1,aftLinenum,LineSeq,LineType,LineText来自#DOC10 -更新#TempTab SET LineText =''WHERE LineType ='S' -更新总水平 更新#TempTab SET TotalLevel = -1 从#TempTab ORDER BY DocEntry,_LineNum,_LineSeq选择LINEType,_LineNum,_LineSeq的DECLARE curTemp光标 SET @TotalLevel = 0 打开当前 获取当前输入@LineType,@ _ LineNum,@ _ LineSeq @@ FETCH_STATUS = 0时 开始 IF(@LineType ='S') 开始 更新#TempTab SET TotalLevel = @TotalLevel WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq SET @TotalLevel = @TotalLevel + 1 结束 其他 IF(@LineType ='R') 开始 SET @TotalLevel = 0 结束 获取当前输入@LineType,@ _ LineNum,@ _ LineSeq 结束 关闭当前 取消当前 -更新总计 声明@ d1 NUMERIC(19,6) 声明@ d2 NUMERIC(19,6) 声明@ d3 NUMERIC(19,6) 声明@ d4 NUMERIC(19,6) 声明@ d5 NUMERIC(19,6) 声明@ d6 NUMERIC(19,6) 声明@ d7 NUMERIC(19,6) 声明@ d8 NUMERIC(19,6) 声明@ d9 NUMERIC(19,6) 声明@ d10 NUMERIC(19,6) 声明@fromLineNum INT 宣告@level INT 设置@level = 0 @level <10时 开始 设置@fromLineNum = 0 从#TempTab WHERE TotalLevel = @level按DocEntry排序,_LineNum,_LineSeq的LineType,_LineNum,_LineSeq的预声明光标 OPEN电流 正在抓取INTO @LineType,@ _ LineNum,@ _ LineSeq @@ FETCH_STATUS = 0时 开始 IF(@LineType ='S') 开始 选择 @ d1 = SUM(LineTotal + vatsum), @ d2 = SUM(TotalFrgn), @ d3 = SUM(TotalSumSy), @ d4 = SUM(GTotal), @ d5 = SUM(TotalFrgn + VatSumFrgn), @ d6 = SUM(GTotalSC), @ d7 = SUM(VatSum), @ d8 = SUM(VatSumFrgn), @ d9 = SUM(VatSumSy), @ d10 = SUM(数量) 来自#DOC1 在(@fromLineNum <= VisOrder)AND(VisOrder <= @ _ LineNum)AND(LineType ='R') 更新#TempTab SET小计= @ d1, TotalFrgn = @ d2, TotalSumSy = @ d3, GTotal = @ d4, GTotalFC = @ d5, GTotalSC = @ d6, VatSum = @ d7, VatSumFrgn = @ d8, VatSumSy = @ d9, SubTotalQty = @ d10 _LineNum = @_LineNum和_LineSeq = @_LineSeq SET @fromLineNum = @_LineNum +1 结束 正在抓取INTO @LineType,@ _ LineNum,@ _ LineSeq 结束 CLOSE cur 释放当前 设置@level = @level + 1 结束 选择 @ObjectId'ObjectId' ,如果(@ ObjectId = 112)则为'N' 否则" Y"端"已确认" ,b1.SubTotalQty ,b1。小计 ,b1.GTotalFC ,b1.LineText ,a.TransID ,a.Series,a.DocEntry,a.DocNum,a.DocDate,a.DocDueDate,a.TaxDate,a.Doccur ,a.ReqDate,a.DocStatus,a.ReqType,a.Requester,a.ReqName,a.Branch ,a。部门,a.Email,a.CardCode,a.CardName,a.DocType,a.CANCELED ,a.OwnerCode,a.Comments,a.DocTotal,a.DocTotalFC,a.VatSum,a.VatSumFC ,a.DiscPrcnt'Header DcstPrcnt',a.DiscSum,a.DiscSumFC,a.RoundDif,a.TotalExpns,a.TotalExpFC ,a.TaxOnExp,a.TaxOnExApF ,a.ObjType,a.CreateDate,a.DocTime,a.draftKey,a.NumAtCard,a.SlpCode,a.WddStatus WTSum WTSumFC ,a。印刷 ,b.LineNum,b.ItemCode,b.Dscription,b.Quantity,b.UomCode,b.unitMsr,b.NumPerMsr ,b.Price,b.Currency,b.Rate,b.DiscPrcnt,b.WhsCode,b.LineTotal,b.OpenQty,isnull(b.TotalFrgn,0)'LineTotalFC' ,b.GTotal,b.VatSum'Line Vat',b.VatSumFrgn,b.AcctCode,b.Project,isnull(b.OcrCode,'')'OcrCode' ,b.OcrCode2,b.OcrCode3,b.OcrCode4,b.OcrCode5 ,b.BaseType ,b.BaseEntry,b.BaseRef ,b.BaseLine,b.LineStatus,b.Text,b.LinManClsd,b.UomEntry,b.InvQty,b.FreeTxt ,b.LineVendor ,b.VisOrder 来自#DOC a.DocEntry上的INNER JOIN#DOC1 b = b.DocEntry 在b.docentry = b1.docentry和b.LineNum = b1._LineNum上的左外连接#TempTab b1 在哪里a.DocEntry = @DocKey --DROP TABLE#测试 删除表#TempTab 删除表#DOC 删除表#DOC1 删除表#DOC3 删除表#DOC9 拖放表#DOC10 --DROP TABLE #BASEDOC -删除表#DOC_1 -删除表#DOC_11 -选择*来自INV1
嗨,布莱恩,
没有简单的方法来获取基本文档和目标文档。
一种方法是保留所有表的联接并使用COALESCE(INV1.DocEntry ,RDN1.DocEntry,DLN1.DocEntry,RDR1.DocEntry)BaseDocEntry,
一周热门 更多>