Skip to Content
author's profile photo Former Member
Former Member

Query to generate all existing JE, including JE from JV

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Dec 04, 2014 at 03:21 AM

    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]

    Add a comment
    10|10000 characters needed characters exceeded

    • 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]

  • Posted on Dec 03, 2014 at 07:45 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 03, 2014 at 09:09 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.