Query to generate all existing JE, including JE from JV

[Former Member]

Hi,

Currently I used two separate query to generate the existing JE.

The first query will generate JE that created from JV.

The second query will generate JE that automatically created by systems, however it will only include partial JE created from JV.

At the end, I have to combine the result of both query.

Is there a way to combine both?

The result should show all existing JE either created from JV or by systems.

First Query:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T7.[DateID] AS "JV CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

Second Query:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

Really appreciate the help.

Thanks

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] AS "JV CREATE DATE", case when T0.taxdate is null then '-' end as "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1] group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] ,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver],T0.taxdate

union all

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],  case when T0.taxdate is null then '-' end as "JV CREATE DATE",

T0.[TaxDate] AS "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.taxdate,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver]

[Former Member · Accepted solution]

Hi,

You are truly great !

For the records that created from JV, is it possible to make the JE create data same line as JV create date? Refer attach.

Is it also possible to show as "auto posting" in creator name & approver name instead of empty field. Refer attach

Really appreciate your time and help.

Thanks

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] AS "JV CREATE DATE",  T0.taxdate  as "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

case when T0.[Creator] is null then 'Auto Posting' end as Creator,

case when T0.[Approver] is null then 'Auto posting' end as Approver

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1] group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] ,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver],T0.taxdate

union all

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],   case when T0.taxdate is null then '-' end  as "JE CREATE DATE",

T0.[TaxDate] AS "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

case when T0.[Creator] is null then 'Auto Posting' end as 'Creator',

case when T0.[Approver] is null then 'Auto posting' end as 'Approver'

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.taxdate,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver]

[Former Member · Accepted solution]

Hi,

The query work, however the creator name and approver name of JV will be missing.  Can make it to show both in one query?

Since JE created date can be same line as JV created date. Is it possible to directly eliminate the extra row?

Thanks

[kothandaraman_nagarajan · Accepted solution]

1. Advice your DB localization. These fields does not appearing for all localization

2. It is not possible to remove extra JE date.

[Former Member · Accepted solution]

Hi,

My localization is P.R.China.

Will it display the field?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Yes. For more details, refer SAP help file;

http://help.sap.com/saphelp_sbo882/helpdata/en/45/114b0d29fc4805e10000000a1553f6/content.htm

[Former Member · Accepted solution]

Hi,

I think there is misunderstanding.

What I mean is before I run the last query, the query before will show value in the creator and approval name field and the field is empty for JE that were auto posting.

Later on I request a query to fill up the empty field as "auto posting" meanwhile remain the value in both field for JE that created from JV.

However, the last query will only show auto posting, no value for records created from JV in both fields.

The result is reverse from the second last query in term of creator and approval field.

Thanks

[kothandaraman_nagarajan · Accepted solution]

try:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] AS "JV CREATE DATE",  T0.taxdate  as "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

case when T0.[Creator] is null then 'Auto Posting' else T0.[Creator] end as Creator,

case when T0.[Approver] is null then 'Auto posting' else T0.[Approver] end as Approver

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1] group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T7.[DateID] ,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver],T0.taxdate

union all

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],   case when T0.taxdate is null then '-' end  as "JE CREATE DATE",

T0.[TaxDate] AS "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

case when T0.[Creator] is null then 'Auto Posting' else T0.[Creator] end as 'Creator',

case when T0.[Approver] is null then 'Auto posting' else T0.[Approver] end as 'Approver'

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

group by  T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.taxdate,

T0.[RefDate] ,

T6.[Name],

T3.u_name ,

T0.[Creator],

T0.[Approver]

[kothandaraman_nagarajan]

I don't find any difference between query 1 and 2. Based on your requirement just added two queries by  UNION ALL function.

Run both queries separately and let me know your query result.

[Former Member]

Hi,

The only different is query 1 included the OBDT T7 table.

The UNION ALL query that provided join query 1 + query 1.

Not query 1 + query 2, however it result error when I tried on this

Error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

Anyway, the result as below if run separately:

First query, Result: Only display JE created from JV

Second query, Result: Display auto posting JE + few JE from JV, not all.

Thanks

[kothandaraman_nagarajan]

Error message appeared due to unequal column between two queries. In second query, below field is missing T7.[DateID] AS "JV CREATE DATE".

[Former Member]

Hi,

I had try to union all, and replaced the unequal column in query two by T0.[TaxDate] AS "JE CREATE DATE", not T7.[DateID] AS "JV CREATE DATE". Because if I do so, it will generate the same result from query 1.

Below is the query that union between query one and two.

It will result in duplicate records of JE created from JV.

The difference was the date in T0.[TaxDate] AS "JE CREATE DATE".

My requirement is to have a query to generate all JE (auto posting and JV) with the selected field below.

Thanks

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T7.[DateID] AS "JV CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

Union all

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T0.[TaxDate] AS "JE CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

[kothandaraman_nagarajan]

Instead of union all just use UNION and then check.

[kothandaraman_nagarajan]

Hi,

Try:

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T7.[DateID] AS "JV CREATE DATE",

T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry

INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

union all

SELECT T1.[Account],

T2.[AcctName],

T0.[Number],

T0.[BaseRef],

T1.[LineMemo],

T1.[Debit],

T1.[Credit],

T1.[FCCurrency],

T1.[FCDebit],

T1.[FCCredit],

T0.[TaxDate] AS "JE CREATE DATE",

T7.[DateID] AS "JV CREATE DATE",
T0.[RefDate] ,

T6.[Name],

T3.u_name [User Name],

T0.[Creator],

T0.[Approver]

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'

INNER JOIN OFPR T6 ON T0.FinncPriod = T6.AbsEntry
INNER JOIN OBTD T7 ON T0.BatchNum = T7.BatchNum

WHERE T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

It will only show JE from JV.

Will not show auto posting JE, the JE that created by systems.

Thanks

[Rafaee_Mustafa]

Dear Olga,

Nagarajan uses UNION in between so this will execute on same time.

[Former Member]

Hi,

I had try for few times, the result really don't show auto posting JE.

Have you try?

Thanks