[Help] - SAP B1 Create standard document query

[Former Member]

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

[edy_simon]

Hi Bryan,

No easy way to get the base and target document.

one way is to left join all tables and get the value using the COALESCE(INV1.DocEntry, RDN1.DocEntry, DLN1.DocEntry, RDR1.DocEntry) BaseDocEntry,

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 
          LEFT JOIN RDR1 ON b.BaseType = RDR1.ObjType AND b.BaseEntry = RDR1.DocEntry AND b.BaseLine = RDR1.LineNum
          LEFT JOIN DLN1 ON b.BaseType = DLN1.ObjType AND b.BaseEntry = DLN1.DocEntry AND b.BaseLine = DLN1.LineNum
          LEFT JOIN RDN1 ON.....
          LEFT JOIN INV1 ON..... etc... need to cover all the tables.
		   
		WHERE a.DocEntry =@DocKey