Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

OIVL query (warehouse Journal)

Hi Everyone,

First query below is working because the parameter is static, but if i set a dynamic parameter then it will return an error.

SELECT 'CN TO PRIMARY WHs' as 'Type', A.CreatedBy, A.Transtype, A.DocDate, A.ItemCode, A.InQty, A.OutQty, A.Price, A.LocCode, A.SumStock, A.Usersign,

CASE  WHEN A.Transtype = 13 THEN 'AR INVOICE'

WHEN A.Transtype = 14 THEN 'ARCM'

WHEN A.Transtype = 16  THEN 'RETURNS'

WHEN A.Transtype = 60 THEN 'Goods Issue/Issue for Production'

WHEN A.Transtype = 59 THEN 'Goods Receipt/Receipt from Production'

WHEN A.Transtype = 20 then 'Goods Receipt PO'

WHEN A.Transtype = 18 then 'A/P Invoice'

WHEN A.Transtype = 10000071 then 'Inventory Posting'

WHEN A.Transtype = 15 THEN 'Delivery 'ELSE 'Others' END AS Document_Type ,A.Base_Ref as Docnum

FROM OIVL A

WHERE A.[DocDate] >= '20140901' and A.Transtype NOT IN ('15', '59', '67') and A.LocCode not in ('LLD', 'THD')

DECLARE @Date  = /* SELECT FROM dbo.OIVL A WHERE A.DocDate >= */  '[%0]'

SELECT 'CN TO PRIMARY WHs' as 'Type', A.CreatedBy, A.Transtype, A.DocDate, A.ItemCode, A.InQty, A.OutQty, A.Price, A.LocCode, A.SumStock, A.Usersign,CASE WHEN A.Transtype = 13 THEN 'AR INVOICE'

  WHEN A.Transtype = 14 THEN 'ARCM'

  WHEN A.Transtype = 16  THEN 'RETURNS'

  WHEN A.Transtype = 60 THEN 'Goods Issue/Issue for Production'

  WHEN A.Transtype = 59 THEN 'Goods Receipt/Receipt from Production'

  WHEN A.Transtype = 20 then 'Goods Receipt PO'

  WHEN A.Transtype = 18 then 'A/P Invoice'

  WHEN A.Transtype = 10000071 then 'Inventory Posting'

WHEN A.Transtype = 15 THEN 'Delivery 'ELSE 'Others' END AS Document_Type ,A.Base_Ref as Docnum

FROM OIVL A

WHERE A.Docdate >= @Date and A.Transtype NOT IN ('15', '59', '67') and A.LocCode not in ('LLD', 'THD')

PS I also tried running it on A.Docdate >= [0%] but its not working.

Any ideas?


Thanks!
Regards,
Darius Gragasin

Former Member
replied

Hi,

Try this:

DECLARE @Date as Datetime

Set @date = /* SELECT FROM dbo.OIVL A WHERE A.DocDate >= */  '[%0]'  

SELECT 'CN TO PRIMARY WHs' as 'Type', A.CreatedBy, A.Transtype, A.DocDate, A.ItemCode, A.InQty, A.OutQty, A.Price, A.LocCode, A.SumStock, A.Usersign,CASE WHEN A.Transtype = 13 THEN 'AR INVOICE'  

WHEN A.Transtype = 14 THEN 'ARCM'  

  WHEN A.Transtype = 16  THEN 'RETURNS' 

WHEN A.Transtype = 60 THEN 'Goods Issue/Issue for Production' 

  WHEN A.Transtype = 59 THEN 'Goods Receipt/Receipt from Production' 

  WHEN A.Transtype = 20 then 'Goods Receipt PO' 

WHEN A.Transtype = 18 then 'A/P Invoice' 

  WHEN A.Transtype = 10000071 then 'Inventory Posting' 

WHEN A.Transtype = 15 THEN 'Delivery 'ELSE 'Others' END AS Document_Type ,A.Base_Ref as Docnum 

FROM OIVL A 

WHERE A.Docdate >= @Date and A.Transtype NOT IN ('15', '59', '67') and A.LocCode not in ('LLD', 'THD')

Thanks & Regards,

Nagarajan

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question