Query help for Journal - Addtional fileds required

[Former Member]

Dear All,

Due to Audit requirement I need a report in which all journal entries which are based into SAP Business One should come and for that I am using the following query :

SELECT T0.[Number], T0.[Memo],T1.[Line_ID], T1.[LineMemo],T1.[ShortName], T2.[ActType], T2.[AcctName],T2.[Levels], T2.[GroupMask], T1.[FCDebit], T1.[FCCredit], T1.[Debit], T1.[Credit], T1.[RefDate], T1.[CreatedBy], T0.[BaseRef] 'Base Reference',T0.[FinncPriod] FROM [dbo].[OJDT]  T0 INNER JOIN [dbo].[JDT1]  T1 ON T0.TransId = T1.TransId INNER JOIN [dbo].[OACT]  T2 ON T1.Account = T2.AcctCode

but the problem is that I am not able to bring some fields such as creator, Type ( which is the origin in the journal screen ) and the series by which it is passed. I know these fields are available in the Transaction Journal Report but when I run this report I am seeing that not all journal entries are shown here. Some journal entries are missing as compared to my report which is generated from the above query. I think in the transaction by Journal report which is available in base product is not showing the Manual Journal entries that are passed by the user.

Please help and advise.

regards,

Depika

[Former Member · Accepted solution]

Report with User and Series...

SELECT T0.[Number], 
T0.[Memo],
T1.[Line_ID], 
T1.[LineMemo],
T1.[ShortName], 
T2.[ActType], 
T2.[AcctName],
T2.[Levels], 
T2.[GroupMask], 
T1.[FCDebit], 
T1.[FCCredit], 
T1.[Debit], 
T1.[Credit], 
T1.[RefDate], 
T1.[CreatedBy], 
T0.[BaseRef] 'Base Reference',
T0.[FinncPriod],
T3.u_name [User Name],
T5.seriesname
FROM [OJDT]  T0 
INNER JOIN [JDT1]  T1 ON T0.TransId = T1.TransId 
INNER JOIN [OACT]  T2 ON T1.Account = T2.AcctCode
inner join [OUSR] T3 on T0.usersign = T3.userid
inner join NNM1 T5 on T5.series=T0.series and T5.objectcode=30

[Former Member · Accepted solution]

Dear Andrey,

I tried to generate your query but its giving error when its executed as :

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'APOG_UDO' to data type int.

Please advise.

Regards,

Depika

[former_member204969 · Accepted solution]

Try to change the T5.objectcode=30 to T5.objectcode='30'

[Former Member · Accepted solution]

Dear Depika,

The error message you got is not linking to this query. Have you copied all codes into the query manager? I have tried in both 2005A and 2007A version, it works fine.

If you keep getting error message, try to limit the output size by a condition such as:

Where T1.RefDate > '10/01/2009'

Thanks,

Gordon

[Former Member · Accepted solution]

Dear All,

The problem seems to have been resolved with all your guidance and inputs.

Thanks,

Depika

[former_member325312 · Accepted solution]

hello deepika dev find the query below

SELECT distinct (T0.[Number]),

T0.[Memo],

T1.[Line_ID],

T1.[LineMemo],

T1.[ShortName],

T2.[ActType],

T2.[AcctName],

T2.[Levels],

T2.[GroupMask],

T1.[FCDebit],

T1.[FCCredit],

T1.[Debit],

T1.[Credit],

T1.[RefDate],

T1.[CreatedBy],

T0.[BaseRef] 'Base Reference',

T0.[FinncPriod],

T3.u_name [User Name],

T4.seriesname

FROM [OJDT] T0

INNER JOIN [JDT1] T1 ON T0.TransId = T1.TransId

INNER JOIN [OACT] T2 ON T1.Account = T2.AcctCode

inner join [OUSR] T3 on T0.usersign = T3.userid

inner join NNM1 T4 on T4.series = T0.series and T4.objectcode='30'

now this will solve ur query i think so if s pls do close the thread

[Former Member]

Hi DIpika........

Alongwith OJDT table give inner join with OINM where you will get link of all the marketing documents responsible for th Auto JE with series, Type etc.......

Hope it will sort out you........

Regards,