Nov 29, 2016 at 02:33 AM
[Help] - SAP B1 Create standard document query
Hi experts, I need your help regarding for creating standard query report per document. I have this query when they generate a report from any document in SAP Business One it can capture it. What I want now is to include the Base Object or Target Object table dynamically or JOIN them in my query. For example, they generate report in AR invoice. I can get the base object and the target object. Below is my SQL Query.
--ALTER PROC [dbo].[spAppMarketingDoc] @DocKey int, @ObjectId INT
--AS
DECLARE @DocKey int = 8
DECLARE @ObjectId int = 13
DECLARE @object NVARCHAR(4000), @BaseObject nvarchar(4000)
IF (@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) SET @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'
------------------------------------------
SELECT * INTO #DOC FROM ORIN WHERE 1=2
SELECT * INTO #DOC1 FROM RIN1 WHERE 1=2
SELECT * INTO #DOC3 FROM RIN3 WHERE 1=2
SELECT * INTO #DOC9 FROM RIN9 WHERE 1=2
SELECT * INTO #DOC10 FROM RIN10 WHERE 1=2
INSERT INTO #DOC EXEC ('SELECT * FROM O'+@object+' WHERE DocEntry = '+@DocKey)
INSERT INTO #DOC1 EXEC ('SELECT * FROM '+@object+'1 WHERE DocEntry = '+@DocKey)
INSERT INTO #DOC3 EXEC ('SELECT * FROM '+@object+'3 WHERE DocEntry = '+@DocKey)
INSERT INTO #DOC9 EXEC ('SELECT * FROM '+@object+'9 WHERE DocEntry = '+@DocKey)
INSERT INTO #DOC10 EXEC ('SELECT * FROM '+@object+'10 WHERE DocEntry = '+@DocKey)
------------------------------------------
DECLARE @LineSeq INT
DECLARE @_LineNum INT
DECLARE @_LineSeq INT
DECLARE @LineType NCHAR
DECLARE @TotalLevel INT
CREATE TABLE #TempTab
( DocEntry INT,
LineNum INT,
_LineNum INT,
_LineSeq INT,
LineType NCHAR(1),
TotalLevel INT,
SubTotalQty NUMERIC(19, 6),
SubTotal NUMERIC(19, 6),
TotalFrgn NUMERIC(19, 6),
TotalSumSy NUMERIC(19, 6),
GTotal 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
)
INSERT INTO #TempTab(DocEntry, LineNum, _LineNum, _LineSeq, LineType, LineText) SELECT DocEntry, VisOrder, VisOrder, -1, LineType, '' FROM #DOC1
INSERT INTO #TempTab(DocEntry, LineNum, _LineNum, _LineSeq, LineType, LineText) SELECT DocEntry, -1, aftLinenum, LineSeq, LineType, LineText FROM #DOC10
-- UPDATE #TempTab SET LineText = '' WHERE LineType = 'S'
-- Update Total Level
UPDATE #TempTab SET TotalLevel = -1
DECLARE curTemp CURSOR FOR SELECT LineType,_LineNum, _LineSeq FROM #TempTab ORDER BY DocEntry, _LineNum, _LineSeq
SET @TotalLevel = 0
OPEN curTemp
FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LineType = 'S')
BEGIN
UPDATE #TempTab SET TotalLevel = @TotalLevel WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq
SET @TotalLevel = @TotalLevel + 1
END
ELSE
IF (@LineType = 'R')
BEGIN
SET @TotalLevel = 0
END
FETCH curTemp INTO @LineType, @_LineNum, @_LineSeq
END
CLOSE curTemp
DEALLOCATE curTemp
-- Update Total
DECLARE @d1 NUMERIC(19, 6)
DECLARE @d2 NUMERIC(19, 6)
DECLARE @d3 NUMERIC(19, 6)
DECLARE @d4 NUMERIC(19, 6)
DECLARE @d5 NUMERIC(19, 6)
DECLARE @d6 NUMERIC(19, 6)
DECLARE @d7 NUMERIC(19, 6)
DECLARE @d8 NUMERIC(19, 6)
DECLARE @d9 NUMERIC(19, 6)
DECLARE @d10 NUMERIC(19, 6)
DECLARE @fromLineNum INT
DECLARE @level INT
SET @level = 0
WHILE @level < 10
BEGIN
SET @fromLineNum = 0
DECLARE cur CURSOR FOR SELECT LineType,_LineNum, _LineSeq FROM #TempTab WHERE TotalLevel = @level ORDER BY DocEntry, _LineNum, _LineSeq
OPEN cur
FETCH cur INTO @LineType, @_LineNum, @_LineSeq
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LineType = 'S')
BEGIN
SELECT
@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(Quantity)
FROM #DOC1
WHERE (@fromLineNum<=VisOrder) AND (VisOrder<=@_LineNum) AND (LineType='R')
UPDATE #TempTab
SET SubTotal = @d1,
TotalFrgn = @d2,
TotalSumSy =@d3,
GTotal = @d4,
GTotalFC = @d5,
GTotalSC = @d6,
VatSum = @d7,
VatSumFrgn =@d8,
VatSumSy = @d9,
SubTotalQty = @d10
WHERE _LineNum = @_LineNum AND _LineSeq = @_LineSeq
SET @fromLineNum = @_LineNum + 1
END
FETCH cur INTO @LineType, @_LineNum, @_LineSeq
END
CLOSE cur
DEALLOCATE cur
SET @level = @level + 1
END
SELECT
@ObjectId 'ObjectId'
, case when (@ObjectId=112) then 'N'
else 'Y' end 'Confirmed'
, b1.SubTotalQty
, b1.SubTotal
, 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.Department ,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
,a.WTSum
,a.WTSumFC
,a.Printed
,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
FROM #DOC a
INNER JOIN #DOC1 b on a.DocEntry = b.DocEntry
LEFT OUTER JOIN #TempTab b1 on b.docentry = b1.docentry and b.LineNum = b1._LineNum
WHERE a.DocEntry = @DocKey
--DROP TABLE #Test
DROP TABLE #TempTab
DROP TABLE #DOC
DROP TABLE #DOC1
DROP TABLE #DOC3
DROP TABLE #DOC9
DROP TABLE #DOC10
--DROP TABLE #BASEDOC
--DROP TABLE #DOC_1
--DROP TABLE #DOC_11
--SELECT * FROM INV1