Skip to Content

Item Property in sales query

Hello Folks.

I have a query for sales and i need to add item group property from OITG table. but couldn't find any direct link. I am a novice so really have no idea how to add Item group property in Below query

Select T0.[DocNum], 
T0.[DocDate], 
Case when T0.[ObjType] = 13 then 'INVOICE' when T0.[ObjType] = 14 then 'CREDIT NOTE' end as 'Type',
T0.[CardCode], 
T0.[CardName], 
T1.[ItemCode], 
T1.[Dscription],
T1.[Quantity], 
T0.[DocCur], 
T1.[LineTotal] as 'Canadian, 
Case when T1.[Rate] = 0 then T1.[LineTotal] else (T1.[LineTotal]/T1.[Rate]) end as 'Equivalent Value',
Case when (T1.[Quantity] = 0 and (T1.[StockPrice]*T1.[Quantity])= 0 )  then T1.[LineTotal] else (T1.[INMPrice]*T1.[Quantity]) end as 'Sales - Net',
(T1.[StockPrice]*T1.[Quantity]) as 'Cost',
T1.[GrssProfit],
Case when (T1.[INMPrice]*T1.[Quantity]) =0 then 0 else (T1.[GrssProfit]/(T1.[INMPrice]*T1.[Quantity]))*100 end as 'GP %age'

FROM 

OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE 

T0.[CANCELED] = 'N' and
T0.[DocDate]   >= [%0] and
T0.[DocDate] <=[%1] and
T1.[LineTotal] <> '0'
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 29, 2017 at 12:32 PM
    Select T0.[DocNum], 
    T0.[DocDate], 
    Case when T0.[ObjType] = 13 then 'INVOICE' when T0.[ObjType] = 14 then 'CREDIT NOTE' end as 'Type',
    T0.[CardCode], 
    T0.[CardName], 
    T1.[ItemCode], 
    T1.[Dscription],
    T1.[Quantity], 
    T0.[DocCur], 
    T1.[LineTotal] as 'Canadian', 
    Case when T1.[Rate] = 0 then T1.[LineTotal] else (T1.[LineTotal]/T1.[Rate]) end as 'Equivalent Value',
    Case when (T1.[Quantity] = 0 and (T1.[StockPrice]*T1.[Quantity])= 0 )  then T1.[LineTotal] else (T1.[INMPrice]*T1.[Quantity]) end as 'Sales - Net',
    (T1.[StockPrice]*T1.[Quantity]) as 'Cost',
    T1.[GrssProfit],
    Case when (T1.[INMPrice]*T1.[Quantity]) =0 then 0 else (T1.[GrssProfit]/(T1.[INMPrice]*T1.[Quantity]))*100 end as 'GP %age'
    , T3.ItmsGrpNam
    FROM OINV T0 
    INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
    INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
    LEFT JOIN OITG T3 ON CASE WHEN ISNULL(T2.QryGroup1, '') = 'Y' THEN 1 WHEN ISNULL(T2.QryGroup2, '') = 'Y' THEN 2 END  = ItmsTypCod
    WHERE 
    T0.[CANCELED] = 'N' and
    T0.[DocDate]   >= [%0] and
    T0.[DocDate] <=[%1] and
    T1.[LineTotal] <> '0'

    you can join with OITG as per above given example

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 28, 2017 at 06:25 PM

    Or is there any way i can add this sub query to my above mentioned query ?

    select

    src.ItemCode,src.ItemName,src.ItmsGrpNam

    from

    (select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

    from OITM CROSS JOIN OITG

    where OITM.QryGroup1 = 'Y' and OITG.ItmsTypCod = 1

    Add comment
    10|10000 characters needed characters exceeded