Item Transaction Report

[Former Member]

Hi All,

I want a report similar to Inventory Audit report, but instead of Document, I want the report to display the Document details viz, doc no, doc date, vendor/customer. I tried to use PDN1 & DLN1 but system shows error, saying it is not contained in group by function.

Please help.

[kothandaraman_nagarajan]

Hi,

If you have query, please post here to check.

Thanks & Regards,

Nagarajan

[Former Member]

Declare @FromDate Datetime

Declare @ToDate Datetime

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where ((S0.Docdate = '[%0]' or ('[%0]' = '')))

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where ((S1.Docdate = '[%1]') or ('[%1]' = ''))

SELECT Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, Cases, Sheets_per_Case, SqM

From

(

SELECT T0.[DocNum] as "Doc_No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name", T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance", T1.[U_EA_Case] as "Cases", T1.[U_EA_Packsheet] as "Sheets_per_Case", T1.[Quantity] as "SqM"

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]

Where T0.[DocDate] between @fromdate and @todate

Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[U_EA_Case], T1.[U_EA_Packsheet], T1.[Quantity]

UNION ALL

SELECT T0.[DocNum] as "Doc No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name",T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length",  (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance", T1.[U_EA_Case] as "Cases", T1.[U_Ea_Sheet] as "Sheets_per_Case",T1.[Quantity] as "SqM"

FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]

Where T0.[DocDate] between @fromdate and @todate

Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[U_EA_Case], T1.[U_EA_Packsheet], T1.[Quantity])a

Group By Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, Cases, Sheets_per_Case, SqM

[kothandaraman_nagarajan]

Hi,

Try below query without UDF fields:

Declare @FromDate Datetime

Declare @ToDate Datetime

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where ((S0.Docdate = '[%0]' or ('[%0]' = '')))

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where ((S1.Docdate = '[%1]') or ('[%1]' = ''))

SELECT Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance,  SqM

From

(

SELECT T0.[DocNum] as "Doc_No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name", T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length", (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance",  T1.[Quantity] as "SqM"

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]

Where T0.[DocDate] between @fromdate and @todate

Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1], T1.[Quantity]

UNION ALL

SELECT T0.[DocNum] as "Doc No", T0.[DocDate] as "Doc_Date", T0.[NumAtCard] as "LPO_Ref", T0.[CardName] as "BP_Name",T1.[ItemCode] as "Item_No", T1.[Dscription] as "Item_Name",T1.[Height1] as "height", T1.[Width1] as "Width", T1.[Length1] as "Length",  (sum(T3.inqty)-sum(T3.outqty)) as "OpeningBalance",T1.[Quantity] as "SqM"

FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OINM T3 ON T2.[ItemCode] = T3.[ItemCode]

Where T0.[DocDate] between @fromdate and @todate

Group by T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Height1], T1.[Width1], T1.[Length1],T1.[Quantity])a

Group By Doc_No, Doc_Date, LPO_Ref, BP_Name, Item_No, Item_Name, Height, Width, Length, OpeningBalance, SqM

[Former Member]

Hi,

To link to OINM, you need to add warehouse column. Only ItemCode is not good enough.

Thanks,

GOrdon