Query to add User Name in Document Journal

[rajesh_khater]

Hi,

I need to create a new query which will give identical results as in "Document Journal" report with 1 new column for User Name who created the document.

Please suggest.

Thanks.

[KennedyT21 · Accepted solution]

Try This

SELECT T1.[TransType], T0.[TransId], T1.[RefDate], T1.[DueDate], T1.[TaxDate], T0.[CreateDate], T1.[Credit], T1.[FCCredit],

T1.[SYSCred], T1.[LineMemo], T1.[CreatedBy], T1.[Ref1], T1.[Line_ID], T1.[ShortName], T1.[Debit], T1.[SYSDeb], T1.[FCDebit], T1.[FCCurrency], T1.[Account],

T2.[Name], T3.[CardName], T4.[AcctName], T1.[Ref2], T1.[Project], T1.[DebCred], 

   T5.[Number],   T0.[Ref1], T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line], t6.USER_CODE

  FROM  [dbo].[OJDT] T0  INNER  JOIN [dbo].[JDT1] T1  ON  T1.[TransId] = T0.[TransId] 

   LEFT OUTER  JOIN [dbo].[OVTG] T2  ON  T2.[Code] = T1.[VatGroup]    LEFT OUTER  JOIN [dbo].[OCRD] T3  ON  T3.[CardCode] = T1.[ShortName]  

    LEFT OUTER  JOIN [dbo].[OACT] T4  ON  T4.[AcctCode] = T1.[Account]  

  LEFT OUTER  JOIN [dbo].[OOAT] T5  ON  T5.[AbsID] = T0.[AgrNo]  

  inner join ousr t6 on t6.USERID=t0.UserSign

rgds

[rajesh_khater · Accepted solution]

Ya Kennedy, this query will do.

Thanks!

I will mark your answer as Correct Answer ..

Can you also reply on the other approach using DTW for which I have posted reply above?

[rajesh_khater · Accepted solution]

Can you tell me in your query what is the table OOAT and OVTG for? and where can I see the data entry screens in SAP which update data in these tables?

[KennedyT21 · Accepted solution]

Its related to tax tables you can ignore those tables...

[former_member184146]

HI ,

try the below link

SELECT b.USER_CODE FROM dbo.OJDT a INNER JOIN ousr b ON a.UserSign2=b.USERID

--Manish

[rajesh_khater]

Ok. And how to get other columns as in Document Journal report?

[KennedyT21]

You can use the same report , in which you can add the UDF column using the form settings...

Rgds

[rajesh_khater]

I was asking with respect to a fresh query, which Manish posted.

I will like to explore the fresh query approach. DTW will be my 2nd choice as it may be a lot of work.

[former_member184146]

which other columns you want , do you have any query so far if you have made ???  you can post here if not then please suggest the required columns.

--Manish

[KennedyT21]

Workaround to bring the username in the same report...

Create a header level udf in the journal entry and create a fms to fetch the user name into the UDF..  you can use the document journal report it self....

Rgds

[rajesh_khater]

Already a lot of documents are created.

We need the user name for the existing documents also.

[KennedyT21]

for the existing document update the udf using the DTW...

[rajesh_khater]

In Order to use DTW, how do I first export the data? And do I need to fill only the header sheet for DTW, as the UDF is in header level?

[KennedyT21]

Yes, you required to fill the header.