Business Partner Data Not Displaying Can some one assist

[former_member608426]

I am new to SAP I hope you can assist

Select Distinct B.RefDate AS 'Posting Date' ,B.TransId AS 'Trans No' ,B.TransType --AS 'Origin -PU' ,Case When B.TransType = '18' Then 'PU' Else 'Other' End AS 'Origin' ,B.BaseRef AS 'Origin No' ,A.ContraAct AS 'OffSet Account' , E.AcctName AS 'Business Partner' ,C.CardName AS 'Business Partner' ,A.LineMemo AS 'Details' ,B.LocTotal AS 'C/D (LC)' --A.BaseSum ,a.ProfitCode AS 'Distr Rule' ,D.PrcName AS 'Name' ,D.CCTypeCode AS 'CCSortCode' ,D.PrcCode AS 'Cost Center' ,D.U_DIVISION AS Division ,E.Segment_0+ ' - ' + E.Segment_1 AS 'GL Accounts' ,FORMAT(B.[RefDate], 'yyyy/MM/dd') AS [Date] ,FORMAT(B.[RefDate], 'yyyy') AS [Year] ,FORMAT(B.[RefDate], 'yyyyMM') AS [Month] --,DATENAME From JDT1 A Left Join OJDT B ON B.TransId = A.TransId Left Join OPRC D ON D.U_ACCTCode = a.Account LEFT Join OACT E ON E.AcctCode = D.U_ACCTCode LEFT JOIN OCRD C ON C.CardCode = a.ShortName Where DATEPART(YEAR, A.RefDate)=DATEPART(YEAR, GETDATE()) AND E.FatherNum IN ('7000','7200','7250','7300','7200','6000','6020','5250') AND B.TransType = (18) AND E.AcctName Not In ('Maintenance Costs Recovered to COS')

[former_member608426]

Hi Bala

The values is different, do you have a better idea to execute the the joints,

From JDT1 A Left Join OJDT B ON B.TransId = A.TransId Left Join OPRC D ON D.U_ACCTCode = a.Account Left Join OACT E ON D.U_ACCTCode = E.AcctCode --= D.U_ACCTCode Left Join OCRD C ON C.CardCode = a.ShortName

These table need to be joined
JDT1
OJDT
OPRC
OACT
OCRD

To Get the below output, Any suggestions

[former_member608426]

I notice some name predicates are the same in the OCRD C ON C.Card Code = a.shortname

Is there perhaps a work work around

[kvbalakumar]

Hi,

In order to understand whether the conditions are correct, try by selecting a.shortname from JDT1 and check the value!

Regards,

Bala

[former_member608426]

Good Day

I tried the Left Outer Join No Luck

B.RefDate AS 'Posting Date' ,B.TransId AS 'Trans No' ,B.TransType --AS 'Origin -PU' ,Case When B.TransType = '18' Then 'PU' Else 'Other' End AS 'Origin'

,B.BaseRef AS 'Origin No' ,A.ContraAct AS 'OffSet Account' , E.AcctName AS 'Business Partner' ,C.CardName AS 'Business Partner' ,A.LineMemo AS 'Details' ,B.LocTotal AS 'C/D (LC)' ,a.ProfitCode AS 'Distr Rule' ,D.PrcName AS 'Name' ,D.CCTypeCode AS 'CCSortCode' ,D.PrcCode AS 'Cost Center' ,D.U_DIVISION AS Division

,E.Segment_0+ ' - ' + E.Segment_1 AS 'GL Accounts' ,FORMAT(B.[RefDate], 'yyyy/MM/dd') AS [Date] ,FORMAT(B.[RefDate], 'yyyy') AS [Year] ,FORMAT(B.[RefDate], 'yyyyMM') AS [Month Number] ,DATENAME(Month, B.RefDate) AS 'Month'

From JDT1 A Left Outer Join OJDT B ON B.TransId = A.TransId Left Outer Join OPRC D ON D.U_ACCTCode = a.Account Left Outer Join OACT E ON E.AcctCode = D.U_ACCTCode Left Outer Join OCRD C ON C.CardCode = a.ShortName

Where DATEPART(YEAR, A.RefDate)=DATEPART(YEAR, GETDATE()) AND E.FatherNum IN ('7000','7200','7250','7300','7200','6000','6020','5250') AND B.TransType = (18) AND E.AcctName Not In ('Maintenance Costs Recovered to COS')

[ens_]

Dear cyberc,

Try to include the SQL in code blocks so we know what is commented out since the structure of the text as is does not help us understand the query. I would suggest to perform a LEFT OUTER JOIN on all joins and see how it behaves.

Sincerely,

Varnavas Eleftheriou

[former_member608426]

I did that did not work

I notice some name predicates are the same in the OCRD C ON C.Card Code = a.shortname

Is there perhaps a work work around