Skip to Content
0

SAP B1 9.0 - Query - Add Freight Cost

May 12, 2017 at 09:58 PM

107

avatar image

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.
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Nagarajan K May 14, 2017 at 11:25 AM
1

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]')

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Nagarajan!! You are awesome!!

Thank you very much!!

Cheers!!

Larry T.

0

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

0