Skip to Content

SAP B1 9.0 - Query - Add Freight Cost

Hello SAP Gurus,

Can someone please help add freight cost to this query? I am trying to add a column that has freight costs (if any) keeping all existing data in query.

Here is the query:

SELECT 
  T3.SlpName
, T0.CardName
, 'Invoice' 
, T0.DocDate as 'Posting Date'
, T5.ReconDate as 'Pay / Credit Date'
, CASE WHEN T5.InitObjTyp = '24'
  THEN 'Payment'
  ELSE CASE WHEN T5.InitObjTyp = '14'
  THEN 'Credit'
  ELSE 'OTHER'
  END
  END [Recon Type]
, T0.DocNum as 'Doc Number'
, T0.JrnlMemo
, Case When T0.Canceled <>'C' then  (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum) else  (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum) end as 'Subtotal'
, Case When T0.Canceled <>'C' then (T0.GrosProfit) else (T0.GrosProfit) end as 'Gross Profit'
,T0.TrackNo


FROM OINV T0
 
 
LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '13'
LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum
 
 
INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode
 
 
WHERE (T3.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND (T5.ReconDate BETWEEN '[%1]' AND '[%2]')
 
 
UNION ALL


SELECT  distinct   
  T3.SlpName
, T0.CardName
, 'Credit Memo' , T0.DocDate as 'Posting Date'
, T5.ReconDate as 'Pay / Credit Date'
, CASE WHEN T5.InitObjTyp = '24'
  THEN 'Payment'
  ELSE CASE WHEN T5.InitObjTyp = '14'
  THEN 'Credit'
  ELSE 'OTHER'
  END
  END [Recon Type]
, T0.DocNum as 'Doc Number'
, T0.JrnlMemo
, Case When T0.Canceled <>'C' then  (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum)*-1 else  (-T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum)*-1 end as 'Subtotal'
, Case When T0.Canceled <>'C' then (T0.GrosProfit)*-1 else (-T0.GrosProfit)*-1 end as 'Gross Profit'
,T0.TrackNo


FROM ORIN T0
 
 
LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '14'
LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum
 
 
INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode
 
 
WHERE (T3.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND (T5.ReconDate BETWEEN '[%1]' AND '[%2]')

Thank you for any help you can provide.

Cheers!!
Larry T.
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 14, 2017 at 11:25 AM

    Hi,

    Try this query,

    SELECT T3.SlpName , T0.CardName , 'Invoice' , T0.DocDate as 'Posting Date' , T5.ReconDate as 'Pay / Credit Date' , CASE WHEN T5.InitObjTyp = '24' THEN 'Payment' ELSE CASE WHEN T5.InitObjTyp = '14' THEN 'Credit' ELSE 'OTHER' END END [Recon Type] , T0.DocNum as 'Doc Number' , T0.JrnlMemo , Case When T0.Canceled <>'C' then (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum) else (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum) end as 'Subtotal' , Case When T0.Canceled <>'C' then (T0.GrosProfit) else (T0.GrosProfit) end as 'Gross Profit' ,T0.TrackNo,TA.LineTotal as 'Frieigth Cost'

    FROM OINV T0 inner join INV3 TA on TA.Docentry =T0.Docentry LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '13' LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode WHERE (T3.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND (T5.ReconDate BETWEEN '[%1]' AND '[%2]')

    UNION ALL

    SELECT distinct T3.SlpName , T0.CardName , 'Credit Memo' , T0.DocDate as 'Posting Date' , T5.ReconDate as 'Pay / Credit Date' , CASE WHEN T5.InitObjTyp = '24' THEN 'Payment' ELSE CASE WHEN T5.InitObjTyp = '14' THEN 'Credit' ELSE 'OTHER' END END [Recon Type] , T0.DocNum as 'Doc Number' , T0.JrnlMemo , Case When T0.Canceled <>'C' then (T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum)*-1 else (-T0.DocTotal-T0.VatSum- T0.TotalExpns-T0.DiscSum)*-1 end as 'Subtotal' , Case When T0.Canceled <>'C' then (T0.GrosProfit)*-1 else (-T0.GrosProfit)*-1 end as 'Gross Profit' ,T0.TrackNo, TA.Linetotal as 'Freight Cost'

    FROM ORIN T0 inner join RIN3 TA on TA.Docentry = T0.Docentry LEFT OUTER JOIN ITR1 T4 ON T0.DocEntry = T4.SrcObjAbs AND T4.SrcObjTyp = '14' LEFT OUTER JOIN OITR T5 ON T4.ReconNum = T5.ReconNum INNER Join OSLP T3 ON T3.SlpCode= T0.SlpCode WHERE (T3.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ') AND (T5.ReconDate BETWEEN '[%1]' AND '[%2]')

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Larry,

      Please note that by using inner join for the RIN3 table, you are effectively filtering out invoices that do not have freight costs.

      Please use LEFT OUTER JOIN, if you wish to see all invoices.

      Regards,

      Johan