OIVL query (warehouse Journal)

[Former Member]

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

[kothandaraman_nagarajan · Accepted solution]

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

[Former Member · Accepted solution]

Yup, it's also working.Thank you Nagarajan.

[Former Member]

Got it!

/*SELECT FROM [dbo].[OIVL] A*/

Declare  @Date as datetime

/* WHERE */

set @Date = /* 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')