Query Report of Tracking No.

[former_member593234]

dathulworkssap-community13-3-19.jpgHi All,

I want a query report of tracking No. Please find the attached image.dathulworkssap-community13-3-19-1.jpg

[kvbalakumar]

Hi Athul,

We can provide the complete query but it is always good to some initiative from you...

Please try to create a query and provide us the query you are working with the issue, so that we can amend the query instead of writing from scratch.

[former_member593234]

Hi, Balakumar Viswanathan

I am trying to create query, but getting some errors. Please help..

declare @From date
set @From= /* select min(T0.[F_refdate]) from [dbo].[OFPR] T0 where T0.[F_refdate]=*/ [%0]
declare @To Date
set @To= /*select max(T0.[T_refdate]) from [dbo].[OFPR] T0 where T0.[T_refdate]=*/ [%1]
declare @name VarcHar(150)
set @name =/* Select max(a.CardName) from OCRD a where a.CardName= */'[%2]'
SELECT t1.docnum , t0.doctype , t1.cardcode , t1.cardname ,
t0.U_part_no , t0.itemcode, t0.Dscription , t0.Quantity , t0.price as 'Purchase Price',t0.Documenttotal,t1.TrackingNo,
FROM ODLN T0
JOIN OINV T1 ON T1.DocENtry=T0.DocEntry

[kvbalakumar · Accepted solution]

Hi,

Try the below

declare @From date
set @From= /* select min(T0.[F_refdate]) from [dbo].[OFPR] T0 where T0.[F_refdate]=*/ [%0]
declare @To Date
set @To= /*select max(T0.[T_refdate]) from [dbo].[OFPR] T0 where T0.[T_refdate]=*/ [%1]
declare @name VarcHar(150)
set @name =/* Select max(a.CardName) from OCRD a where a.CardName= */'[%2]'
SELECT T0.docnum , 'Delivery' as 'DocType' , t0.cardcode , t0.cardname ,
t1.U_part_no , t1.itemcode, t1.Dscription , t1.Quantity , t1.price as 'Purchase Price',t0.Doctotal,t0.TrackNo
FROM ODLN T0
JOIN DLN1 T1 ON T1.DocENtry=T0.DocEntry
Where T0.DocDate >= @from and T0.DocDate <= @to and T0.CardName = @name

Union all

SELECT T0.docnum , 'Invoice' as 'DocType' , t0.cardcode , t0.cardname , t1.U_part_no , t1.itemcode, t1.Dscription , t1.Quantity , t1.price as 'Purchase Price',t0.Doctotal,t0.TrackNo FROM OINV T0 JOIN INV1 T1 ON T1.DocENtry=T0.DocEntry Where T0.DocDate >= @from and T0.DocDate <= @to and T0.CardName = @name

Regards,

Bala

[former_member593234 · Accepted solution]

Hi Balakumar Viswanathan,

Thanks for the query, faced some errors

dathulworkssap-community13-3-192.jpg - Image 2 - Mentioned date & BP name, buts cant find out

dathulworkssap-community13-3-191.jpg

[kvbalakumar · Accepted solution]

Query updated, please check

[former_member593234 · Accepted solution]

Thank you

Balakumar Viswanathan ,

One more issue, I can't open document no in this report.

[kvbalakumar · Accepted solution]

This is default behaviour of SAP B1, if you have more than one object then the Link arrow won't work!