[帮助]-SAP B1创建标准文档查询

2020-09-27 21:02发布

点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)您好,专家们,关于为每个文档创建...

         点击此处--->   EasySAP.com群内免费提供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
 
1条回答
小灯塔
2020-09-27 21:58

嗨,布莱恩,

没有简单的方法来获取基本文档和目标文档。

一种方法是保留所有表的联接并使用COALESCE(INV1.DocEntry ,RDN1.DocEntry,DLN1.DocEntry,RDR1.DocEntry)BaseDocEntry,

 FROM #DOC a
           a.DocEntry上的INNER JOIN#DOC1 b = b.DocEntry
           在b.docentry = b1.docentry和b.LineNum = b1._LineNum上的左外连接#TempTab b1
           左联接RDR1 ON b.BaseType = RDR1.ObjType AND b.BaseEntry = RDR1.DocEntry AND b.BaseLine = RDR1.LineNum
           左联接DLN1 ON b.BaseType = DLN1.ObjType AND b.BaseEntry = DLN1.DocEntry AND b.BaseLine = DLN1.LineNum
           左联接RDN1接通..... 
左联接INV1接通.....等...需要遮盖所有桌子。 在哪里a.DocEntry = @ DocKey

一周热门 更多>