SQL View to show JE's with or without AA data

(0) Share

Report

Posted on by Community Member

I wonder if this is do-able:

My client would like to see a Smartlist (created by a SQL view) that will show all General Ledger transactions, and if they have Analytical Accounting information attached to the transaction, to show that as well. Here is a script for AA that I found (see link below), but it will only show the transaction if it has AA Data. They want one that has any JE, regardless of AA or not. Does anyone have such a view?

http://mahmoudsaadi.blogspot.com/2014/03/general-ledger-journals-with-analytical.html

Thank you!

*This post is locked for comments

All responses (8)

Answers (0)

Tom Cruse 1,477 on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

Thanks for the kind words, just doing what I can to help the community out :) .
Community Member on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

You have been so incredibly helpful! It seems to work in SQL but when I create a view and put in Smartlist Designer it only picks up AA Journal Entries. I'll look at it again next week. We need a developer like you - do you work on a consulting basis????? Thank you!!!!
Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

Ok give this a whirl, I took the canned view you mentioned and added my CTE as a FULL OUTER JOIN to grab everything and then added the CTE columns into each query. Since we don't have AA I can't validate this completely so please review it with a fine toothed comb because it'll pull everything since there are no parameters set. :)

select

['Transaction Amounts Work'].[JRNENTRY] as 'Journal Entry',

'Series' = dbo.DYN_FUNC_Series_GL_Trx(['Transaction Work'].[SERIES]),

['Transaction Work'].[TRXDATE] as 'TRX Date',

(

select rtrim([ACTNUMST])

from [GL00105] as ['Account Index Master']

where ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

) as 'Account Number',

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description',

aa.aaTRXType,

aa.aaGLTRXSource,

aa.aaTRXSource,

aa.TotalDebit,

aa.TotalCredit,

aa.aaDebit,

aa.aaCredit,

['Transaction Amounts Work'].[CRDTAMNT] as 'Credit Amount',

['Transaction Amounts Work'].[DEBITAMT] as 'Debit Amount',

aa.aaTrxDim,

aa.aatrxdimdescr,

aa.aatrxdimdescr2,

aa.aatrxdimcode,

aa.aatrxdimcodedescr,

aa.aatrxdimcodedescr2,

'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Master'].[ACCATNUM]),

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description from Account Master',

['Transaction Amounts Work'].[ACTINDX] as 'Account Index',

'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Account Type from Account Master' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE]),

'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL]),

NULL as 'Back Out JE',

'Balance For Calculation' = dbo.DYN_FUNC_Balance_For_Calculation(['Transaction Amounts Work'].[BALFRCLC]),

'Balance For Calculation from Account Master' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC]),

rtrim(['Transaction Amounts Work'].[BACHNUMB]) as 'Batch Number',

rtrim(['Transaction Work'].[BCHSOURC]) as 'Batch Source',

'Closed Year' = dbo.DYN_FUNC_Closed_Year(['Transaction Work'].[CLOSEDYR]),

'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD]),

NULL as 'Correcting JE',

rtrim(['Transaction Amounts Work'].[CorrespondingUnit]) as 'CorrespondingUnit',

['Account Master'].[CREATDDT] as 'Created Date',

rtrim(['Currency Setup'].[CURNCYID]) as 'Currency ID',

['Transaction Amounts Work'].[CURRNIDX] as 'Currency Index',

rtrim(['Transaction Work'].[DTAControlNum]) as 'DTA Control Number',

['Transaction Amounts Work'].[DTA_GL_Status] as 'DTA GL Status',

['Transaction Work'].[DTA_Index] as 'DTA Index',

['Transaction Work'].[DTATRXType] as 'DTA TRX Type',

'Decimal Places' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Transaction Amounts Work'].[DECPLACS]),

'Decimal Places from Account Master' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS]),

['Transaction Work'].[DENXRATE] as 'Denomination Exchange Rate',

rtrim(['Transaction Amounts Work'].[DSCRIPTN]) as 'Description',

['Transaction Work'].[DOCDATE] as 'Document Date',

'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Trx(1),

['Transaction Work'].[ERRSTATE] as 'Error State',

['Transaction Amounts Work'].[EXCHDATE] as 'Exchange Date',

['Transaction Amounts Work'].[XCHGRATE] as 'Exchange Rate',

rtrim(['Transaction Amounts Work'].[EXGTBLID]) as 'Exchange Table ID',

'Fixed Or Variable' = dbo.DYN_FUNC_Fixed_Or_Variable(['Transaction Amounts Work'].[FXDORVAR]),

'Fixed Or Variable from Account Master' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR]),

['Account Master'].[HSTRCLRT] as 'Historical Rate',

'History TRX' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[HISTRX]),

NULL as 'History Year',

'ICDists' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[ICDISTS]),

'IC TRX' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[ICTRX]),

['Account Master'].[INFLAEQU] as 'Inflation Equity Account Index',

['Account Master'].[INFLAREV] as 'Inflation Revenue Account Index',

rtrim(['Transaction Amounts Work'].[INTERID]) as 'Intercompany ID',

['Transaction Work'].[LSTDTEDT] as 'Last Date Edited',

rtrim(['Transaction Work'].[LASTUSER]) as 'Last User',

'Line Status' = dbo.DYN_FUNC_Line_Status(['Transaction Amounts Work'].[LNESTAT]),

'MC Transaction State' = dbo.DYN_FUNC_MC_Transaction_State(['Transaction Amounts Work'].[MCTRXSTT]),

rtrim(['Account Master'].[MNACSGMT]) as 'Main Account Segment',

['Account Master'].[MODIFDT] as 'Modified Date',

['Transaction Work'].[NOTEINDX] as 'Note Index',

['Account Master'].[NOTEINDX] as 'Note Index from Account Master',

'Open Year' = dbo.DYN_FUNC_Open_Year(['Transaction Work'].[OPENYEAR]),

['Transaction Work'].[Original_JE] as 'Original JE',

rtrim(['Transaction Work'].[ORCOMID]) as 'Originating Company ID',

rtrim(['Transaction Amounts Work'].[ORCTRNUM]) as 'Originating Control Number',

['Transaction Amounts Work'].[ORCRDAMT] as 'Originating Credit Amount',

'Originating DTA Series' = dbo.DYN_FUNC_Originating_DTA_Series(['Transaction Work'].[OrigDTASeries]),

['Transaction Amounts Work'].[ORDBTAMT] as 'Originating Debit Amount',

rtrim(['Transaction Amounts Work'].[ORDOCNUM]) as 'Originating Document Number',

['Transaction Work'].[ORIGINJE] as 'Originating Journal Entry',

rtrim(['Transaction Amounts Work'].[ORMSTRID]) as 'Originating Master ID',

rtrim(['Transaction Amounts Work'].[ORMSTRNM]) as 'Originating Master Name',

['Transaction Work'].[ORPSTDDT] as 'Originating Posted Date',

['Transaction Amounts Work'].[OrigSeqNum] as 'Originating Sequence Number',

NULL as 'Originating Source',

rtrim(['Transaction Work'].[ORTRXSRC]) as 'Originating TRX Source',

'Originating TRX Type' = dbo.DYN_FUNC_Originating_TRX_Type(['Transaction Amounts Work'].[ORTRXTYP]),

NULL as 'Originating Type',

['Transaction Work'].[PERIODID] as 'Period ID',

NULL as 'Period Posting Number',

'' as 'Approval User ID',

NULL as 'Approval Date',

NULL as 'Polled Transaction',

'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn]),

'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn]),

'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn]),

'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn]),

NULL as 'Posting Number',

'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

'Posting Type from Account Master' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

'Printing Status' = dbo.DYN_FUNC_Printing_Status(['Transaction Work'].[PRNTSTUS]),

NULL as 'Quick Offset',

'Rate Calculation Method' = dbo.DYN_FUNC_Rate_Calculation_Method(['Transaction Work'].[RTCLCMTD]),

rtrim(['Transaction Work'].[RATETPID]) as 'Rate Type ID',

'Recurring TRX' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[RCRNGTRX]),

['Transaction Work'].[RCTRXSEQ] as 'Recurring TRX Sequence',

rtrim(['Transaction Work'].[REFRENCE]) as 'Reference',

'Reversing Closed Year' = dbo.DYN_FUNC_Reversing_Closed_Year(['Transaction Work'].[REVCLYR]),

['Transaction Work'].[RVRSNGDT] as 'Reversing Date',

'Reversing History TRX' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[REVHIST]),

['Transaction Work'].[REVPRDID] as 'Reversing Period ID',

rtrim(['Transaction Work'].[RVTRXSRC]) as 'Reversing TRX Source',

'Reversing Year' = dbo.DYN_FUNC_Reversing_Year(['Transaction Work'].[REVYEAR]),

['Account Master'].[ACTNUMBR_1] as 'Segment1',

['Account Master'].[ACTNUMBR_2] as 'Segment2',

['Account Master'].[ACTNUMBR_3] as 'Segment3',

['Transaction Amounts Work'].[SQNCLINE] as 'Sequence Number',

rtrim(['Transaction Work'].[SOURCDOC]) as 'Source Document',

rtrim(['Transaction Work'].[TRXSORCE]) as 'TRX Source',

['Transaction Work'].[Tax_Date] as 'Tax Date',

['Transaction Amounts Work'].[TIME1] as 'Time',

'Transaction Type' = dbo.DYN_FUNC_Transaction_Type(['Transaction Work'].[TRXTYPE]),

'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC]),

rtrim(['Account Master'].[USERDEF1]) as 'User Defined 1',

rtrim(['Account Master'].[USERDEF2]) as 'User Defined 2',

rtrim(['Transaction Work'].[USWHPSTD]) as 'User Who Posted',

'Voided' = dbo.DYN_FUNC_Boolean_All(['Transaction Work'].[VOIDED]),

'3' as 'Segments',

'Workflow Approval Status' = dbo.DYN_FUNC_Workflow_Approval_Status(['Posting Definitions Master'].[Workflow_Approval_Status]),

'Workflow Priority' = dbo.DYN_FUNC_Workflow_Priority(['Posting Definitions Master'].[Workflow_Priority]),

'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Transaction Work'].[Ledger_ID]),

'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Transaction Work'].[Ledger_ID]),

'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppAccountIndex(1,['Transaction Amounts Work'].[ACTINDX] ),

'Journal Entry For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppJournalEntry(1,['Transaction Amounts Work'].[JRNENTRY],['Transaction Work'].[RCTRXSEQ],1,1 )

from [GL10001] as ['Transaction Amounts Work'] with (NOLOCK)

left outer join [GL00100] as ['Account Master'] with (NOLOCK) on ['Transaction Amounts Work'].[ACTINDX] = ['Account Master'].[ACTINDX]

left outer join [GL10000] as ['Transaction Work'] with (NOLOCK) on ['Transaction Amounts Work'].[JRNENTRY] = ['Transaction Work'].[JRNENTRY]

left outer join [DYNAMICS].[dbo].[MC40200] as ['Currency Setup'] with (NOLOCK) on ['Transaction Amounts Work'].[CURRNIDX] = ['Currency Setup'].[CURRNIDX]

left outer join [SY00500] as ['Posting Definitions Master'] with (NOLOCK) on ['Transaction Work'].[BCHSOURC] = ['Posting Definitions Master'].[BCHSOURC] and ['Transaction Work'].[BACHNUMB] = ['Posting Definitions Master'].[BACHNUMB]

FULL OUTER JOIN (SELECT

YEAR1 as Year,

JRNENTRY,

B.ACTINDX,

H.ACTNUMST,

G.ACTDESCR,

A.aaTRXType,

a.aaGLTRXSource,

aaTRXSource,

GLPOSTDT ,

CAST(B.DEBITAMT AS VARCHAR(255)) AS TotalDebit ,

CAST(B.CRDTAMNT AS VARCHAR(255)) AS TotalCredit ,

CAST(C.DEBITAMT AS VARCHAR(255)) AS aaDebit ,

CAST(C.CRDTAMNT AS VARCHAR(255)) AS aaCredit ,

aaTrxDim ,

aaTrxDimDescr ,

aaTrxDimDescr2 ,

aaTrxDimCode ,

aaTrxDimCodeDescr ,

aaTrxDimCodeDescr2

FROM AAG30000 AS A

LEFT OUTER JOIN AAG30001 AS B ON A.aaGLHdrID = B.aaGLHdrID

LEFT OUTER JOIN AAG30002 AS C ON B.aaGLHdrID = C.aaGLHdrID AND

B.aaGLDistID = C.aaGLDistID

INNER JOIN AAG30003 AS D ON C.aaGLAssignID = D.aaGLAssignID AND

C.aaGLDistID = D.aaGLDistID AND

C.aaGLHdrID = D.aaGLHdrID

INNER JOIN AAG00401 AS E ON D.aaTrxDimID = E.aaTrxDimID AND

D.aaTrxCodeID = E.aaTrxDimCodeID

INNER JOIN AAG00400 AS F ON E.aaTrxDimID = F.aaTrxDimID

LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

) AA on AA.JRNENTRY = ['Transaction Work'].JRNENTRY

UNION ALL

select

['Year-to-Date Transaction Open'].[JRNENTRY] as 'Journal Entry',

'Series' = dbo.DYN_FUNC_Series_GL_Trx(['Year-to-Date Transaction Open'].[SERIES]),

['Year-to-Date Transaction Open'].[TRXDATE] as 'TRX Date',

(

select rtrim([ACTNUMST]) from [GL00105] as ['Account Index Master'] where ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

) as 'Account Number',

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description',

aa.aaTRXType,

aa.aaGLTRXSource,

aa.aaTRXSource,

aa.TotalDebit,

aa.TotalCredit,

aa.aaDebit,

aa.aaCredit,

['Year-to-Date Transaction Open'].[CRDTAMNT] as 'Credit Amount',

['Year-to-Date Transaction Open'].[DEBITAMT] as 'Debit Amount',

aa.aaTrxDim,

aa.aatrxdimdescr,

aa.aatrxdimdescr2,

aa.aatrxdimcode,

aa.aatrxdimcodedescr,

aa.aatrxdimcodedescr2,

'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Master'].[ACCATNUM]),

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description from Account Master',

['Year-to-Date Transaction Open'].[ACTINDX] as 'Account Index',

'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Account Type from Account Master' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE]),

'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL]),

['Year-to-Date Transaction Open'].[Back_Out_JE] as 'Back Out JE',

NULL as 'Balance For Calculation',

'Balance For Calculation from Account Master' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC]),

NULL as 'Batch Number',

NULL as 'Batch Source',

NULL as 'Closed Year', '

Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD]),

['Year-to-Date Transaction Open'].[Correcting_JE] as 'Correcting JE',

rtrim(['Year-to-Date Transaction Open'].[CorrespondingUnit]) as 'CorrespondingUnit',

['Account Master'].[CREATDDT] as 'Created Date',

rtrim(['Currency Setup'].[CURNCYID]) as 'Currency ID',

['Year-to-Date Transaction Open'].[CURRNIDX] as 'Currency Index',

NULL as 'DTA Control Number',

['Year-to-Date Transaction Open'].[DTA_GL_Status] as 'DTA GL Status',

['Year-to-Date Transaction Open'].[DTA_Index] as 'DTA Index',

NULL as 'DTA TRX Type',

NULL as 'Decimal Places',

'Decimal Places from Account Master' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS]),

['Year-to-Date Transaction Open'].[DENXRATE] as 'Denomination Exchange Rate',

rtrim(['Year-to-Date Transaction Open'].[DSCRIPTN]) as 'Description',

['Year-to-Date Transaction Open'].[DOCDATE] as 'Document Date',

'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Trx(2),

NULL as 'Error State',

['Year-to-Date Transaction Open'].[EXCHDATE] as 'Exchange Date',

['Year-to-Date Transaction Open'].[XCHGRATE] as 'Exchange Rate',

rtrim(['Year-to-Date Transaction Open'].[EXGTBLID]) as 'Exchange Table ID',

NULL as 'Fixed Or Variable',

'Fixed Or Variable from Account Master' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR]),

['Account Master'].[HSTRCLRT] as 'Historical Rate',

'History TRX' = dbo.DYN_FUNC_Boolean_All(0), NULL as 'History Year',

NULL as 'ICDists',

'IC TRX' = dbo.DYN_FUNC_Boolean_All(['Year-to-Date Transaction Open'].[ICTRX]),

['Account Master'].[INFLAEQU] as 'Inflation Equity Account Index',

['Account Master'].[INFLAREV] as 'Inflation Revenue Account Index',

NULL as 'Intercompany ID',

['Year-to-Date Transaction Open'].[LSTDTEDT] as 'Last Date Edited',

rtrim(['Year-to-Date Transaction Open'].[LASTUSER]) as 'Last User',

NULL as 'Line Status',

'MC Transaction State' = dbo.DYN_FUNC_MC_Transaction_State(['Year-to-Date Transaction Open'].[MCTRXSTT]),

rtrim(['Account Master'].[MNACSGMT]) as 'Main Account Segment',

['Account Master'].[MODIFDT] as 'Modified Date',

['Year-to-Date Transaction Open'].[NOTEINDX] as 'Note Index',

['Account Master'].[NOTEINDX] as 'Note Index from Account Master',

'Open Year' = dbo.DYN_FUNC_Open_Year(['Year-to-Date Transaction Open'].[OPENYEAR]),

['Year-to-Date Transaction Open'].[Original_JE] as 'Original JE',

rtrim(['Year-to-Date Transaction Open'].[ORCOMID]) as 'Originating Company ID',

rtrim(['Year-to-Date Transaction Open'].[ORCTRNUM]) as 'Originating Control Number',

['Year-to-Date Transaction Open'].[ORCRDAMT] as 'Originating Credit Amount',

'Originating DTA Series' = dbo.DYN_FUNC_Originating_DTA_Series(['Year-to-Date Transaction Open'].[OrigDTASeries]),

['Year-to-Date Transaction Open'].[ORDBTAMT] as 'Originating Debit Amount',

rtrim(['Year-to-Date Transaction Open'].[ORDOCNUM]) as 'Originating Document Number',

['Year-to-Date Transaction Open'].[ORIGINJE] as 'Originating Journal Entry',

rtrim(['Year-to-Date Transaction Open'].[ORMSTRID]) as 'Originating Master ID',

rtrim(['Year-to-Date Transaction Open'].[ORMSTRNM]) as 'Originating Master Name',

['Year-to-Date Transaction Open'].[ORPSTDDT] as 'Originating Posted Date',

['Year-to-Date Transaction Open'].[OrigSeqNum] as 'Originating Sequence Number',

rtrim(['Year-to-Date Transaction Open'].[ORGNTSRC]) as 'Originating Source',

rtrim(['Year-to-Date Transaction Open'].[ORTRXSRC]) as 'Originating TRX Source',

'Originating TRX Type' = dbo.DYN_FUNC_Originating_TRX_Type(['Year-to-Date Transaction Open'].[ORTRXTYP]),

'Originating Type' = dbo.DYN_FUNC_Originating_Type(['Year-to-Date Transaction Open'].[ORGNATYP]),

['Year-to-Date Transaction Open'].[PERIODID] as 'Period ID',

['Year-to-Date Transaction Open'].[PPSGNMBR] as 'Period Posting Number',

['Year-to-Date Transaction Open'].[APRVLUSERID] as 'Approval User ID',

['Year-to-Date Transaction Open'].[APPRVLDT] as 'Approval Date',

'Polled Transaction' = dbo.DYN_FUNC_Boolean_All(['Year-to-Date Transaction Open'].[POLLDTRX]),

'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn]),

'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn]),

'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn]),

'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn]),

['Year-to-Date Transaction Open'].[PSTGNMBR] as 'Posting Number',

'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

'Posting Type from Account Master' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

NULL as 'Printing Status',

['Year-to-Date Transaction Open'].[QKOFSET] as 'Quick Offset',

'Rate Calculation Method' = dbo.DYN_FUNC_Rate_Calculation_Method(['Year-to-Date Transaction Open'].[RTCLCMTD]),

rtrim(['Year-to-Date Transaction Open'].[RATETPID]) as 'Rate Type ID',

NULL as 'Recurring TRX',

['Year-to-Date Transaction Open'].[RCTRXSEQ] as 'Recurring TRX Sequence',

rtrim(['Year-to-Date Transaction Open'].[REFRENCE]) as 'Reference',

NULL as 'Reversing Closed Year',

NULL as 'Reversing Date',

NULL as 'Reversing History TRX',

NULL as 'Reversing Period ID',

NULL as 'Reversing TRX Source',

NULL as 'Reversing Year',

['Account Master'].[ACTNUMBR_1] as 'Segment1',

['Account Master'].[ACTNUMBR_2] as 'Segment2',

['Account Master'].[ACTNUMBR_3] as 'Segment3',

['Year-to-Date Transaction Open'].[SEQNUMBR] as 'Sequence Number',

rtrim(['Year-to-Date Transaction Open'].[SOURCDOC]) as 'Source Document',

rtrim(['Year-to-Date Transaction Open'].[TRXSORCE]) as 'TRX Source',

NULL as 'Tax Date',

['Year-to-Date Transaction Open'].[TIME1] as 'Time',

NULL as 'Transaction Type',

'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC]),

rtrim(['Account Master'].[USERDEF1]) as 'User Defined 1',

rtrim(['Account Master'].[USERDEF2]) as 'User Defined 2',

rtrim(['Year-to-Date Transaction Open'].[USWHPSTD]) as 'User Who Posted',

'Voided' = dbo.DYN_FUNC_Boolean_All(['Year-to-Date Transaction Open'].[VOIDED]),

'3' as 'Segments',

NULL as 'Workflow Approval Status',

NULL as 'Workflow Priority',

'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Year-to-Date Transaction Open'].[Ledger_ID]),

'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Year-to-Date Transaction Open'].[Ledger_ID]),

'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppAccountIndex(1,['Year-to-Date Transaction Open'].[ACTINDX] ),

'Journal Entry For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppJournalInquiry(1,['Year-to-Date Transaction Open'].[JRNENTRY],['Year-to-Date Transaction Open'].[RCTRXSEQ],['Year-to-Date Transaction Open'].[OPENYEAR],['Year-to-Date Transaction Open'].[TRXDATE] )

from [GL20000] as ['Year-to-Date Transaction Open'] with (NOLOCK)

left outer join [GL00100] as ['Account Master'] with (NOLOCK) on ['Year-to-Date Transaction Open'].[ACTINDX] = ['Account Master'].[ACTINDX]

left outer join [DYNAMICS].[dbo].[MC40200] as ['Currency Setup'] with (NOLOCK) on ['Year-to-Date Transaction Open'].[CURRNIDX] = ['Currency Setup'].[CURRNIDX]

FULL OUTER JOIN (SELECT

YEAR1 as Year,

JRNENTRY,

B.ACTINDX,

H.ACTNUMST,

G.ACTDESCR,

A.aaTRXType,

a.aaGLTRXSource,

aaTRXSource,

GLPOSTDT ,

CAST(B.DEBITAMT AS VARCHAR(255)) AS TotalDebit ,

CAST(B.CRDTAMNT AS VARCHAR(255)) AS TotalCredit ,

CAST(C.DEBITAMT AS VARCHAR(255)) AS aaDebit ,

CAST(C.CRDTAMNT AS VARCHAR(255)) AS aaCredit ,

aaTrxDim ,

aaTrxDimDescr ,

aaTrxDimDescr2 ,

aaTrxDimCode ,

aaTrxDimCodeDescr ,

aaTrxDimCodeDescr2

FROM AAG30000 AS A

LEFT OUTER JOIN AAG30001 AS B ON A.aaGLHdrID = B.aaGLHdrID

LEFT OUTER JOIN AAG30002 AS C ON B.aaGLHdrID = C.aaGLHdrID AND

B.aaGLDistID = C.aaGLDistID

INNER JOIN AAG30003 AS D ON C.aaGLAssignID = D.aaGLAssignID AND

C.aaGLDistID = D.aaGLDistID AND

C.aaGLHdrID = D.aaGLHdrID

INNER JOIN AAG00401 AS E ON D.aaTrxDimID = E.aaTrxDimID AND

D.aaTrxCodeID = E.aaTrxDimCodeID

INNER JOIN AAG00400 AS F ON E.aaTrxDimID = F.aaTrxDimID

LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

) AA on AA.JRNENTRY = ['Year-to-Date Transaction Open'].JRNENTRY

union all

select

['Account Transaction History'].[JRNENTRY] as 'Journal Entry',

'Series' = dbo.DYN_FUNC_Series_GL_Trx(['Account Transaction History'].[SERIES]),

['Account Transaction History'].[TRXDATE] as 'TRX Date',

(

select rtrim([ACTNUMST]) from [GL00105] as ['Account Index Master'] where ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]

) as 'Account Number',

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description',

aa.aaTRXType,

aa.aaGLTRXSource,

aa.aaTRXSource,

aa.TotalDebit,

aa.TotalCredit,

aa.aaDebit,

aa.aaCredit,

['Account Transaction History'].[CRDTAMNT] as 'Credit Amount',

['Account Transaction History'].[DEBITAMT] as 'Debit Amount',

aa.aaTrxDim,

aa.aatrxdimdescr,

aa.aatrxdimdescr2,

aa.aatrxdimcode,

aa.aatrxdimcodedescr,

aa.aatrxdimcodedescr2,

'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Master'].[ACCATNUM]),

rtrim(['Account Master'].[ACTDESCR]) as 'Account Description from Account Master',

['Account Transaction History'].[ACTINDX] as 'Account Index',

'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Account Type from Account Master' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE]),

'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE]),

'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL]),

NULL as 'Back Out JE',

NULL as 'Balance For Calculation',

'Balance For Calculation from Account Master' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC]),

NULL as 'Batch Number',

NULL as 'Batch Source',

NULL as 'Closed Year',

'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD]),

['Account Transaction History'].[Correcting_JE] as 'Correcting JE',

rtrim(['Account Transaction History'].[CorrespondingUnit]) as 'CorrespondingUnit',

['Account Master'].[CREATDDT] as 'Created Date',

rtrim(['Currency Setup'].[CURNCYID]) as 'Currency ID',

['Account Transaction History'].[CURRNIDX] as 'Currency Index',

NULL as 'DTA Control Number',

['Account Transaction History'].[DTA_GL_Status] as 'DTA GL Status',

['Account Transaction History'].[DTA_Index] as 'DTA Index',

NULL as 'DTA TRX Type',

NULL as 'Decimal Places',

'Decimal Places from Account Master' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS]),

['Account Transaction History'].[DENXRATE] as 'Denomination Exchange Rate',

rtrim(['Account Transaction History'].[DSCRIPTN]) as 'Description',

['Account Transaction History'].[DOCDATE] as 'Document Date',

'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Trx(3),

NULL as 'Error State',

['Account Transaction History'].[EXCHDATE] as 'Exchange Date',

['Account Transaction History'].[XCHGRATE] as 'Exchange Rate',

rtrim(['Account Transaction History'].[EXGTBLID]) as 'Exchange Table ID',

NULL as 'Fixed Or Variable',

'Fixed Or Variable from Account Master' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR]),

['Account Master'].[HSTRCLRT] as 'Historical Rate',

'History TRX' = dbo.DYN_FUNC_Boolean_All(1),

'History Year' = ['Account Transaction History'].[HSTYEAR],

NULL as 'ICDists',

'IC TRX' = dbo.DYN_FUNC_Boolean_All(['Account Transaction History'].[ICTRX]),

['Account Master'].[INFLAEQU] as 'Inflation Equity Account Index',

['Account Master'].[INFLAREV] as 'Inflation Revenue Account Index',

NULL as 'Intercompany ID',

['Account Transaction History'].[LSTDTEDT] as 'Last Date Edited',

rtrim(['Account Transaction History'].[LASTUSER]) as 'Last User',

NULL as 'Line Status',

'MC Transaction State' = dbo.DYN_FUNC_MC_Transaction_State(['Account Transaction History'].[MCTRXSTT]),

rtrim(['Account Master'].[MNACSGMT]) as 'Main Account Segment',

['Account Master'].[MODIFDT] as 'Modified Date',

['Account Transaction History'].[NOTEINDX] as 'Note Index',

['Account Master'].[NOTEINDX] as 'Note Index from Account Master',

NULL as 'Open Year',

['Account Transaction History'].[Original_JE] as 'Original JE',

rtrim(['Account Transaction History'].[ORCOMID]) as 'Originating Company ID',

rtrim(['Account Transaction History'].[ORCTRNUM]) as 'Originating Control Number',

['Account Transaction History'].[ORCRDAMT] as 'Originating Credit Amount',

'Originating DTA Series' = dbo.DYN_FUNC_Originating_DTA_Series(['Account Transaction History'].[OrigDTASeries]),

['Account Transaction History'].[ORDBTAMT] as 'Originating Debit Amount',

rtrim(['Account Transaction History'].[ORDOCNUM]) as 'Originating Document Number',

['Account Transaction History'].[ORIGINJE] as 'Originating Journal Entry',

rtrim(['Account Transaction History'].[ORMSTRID]) as 'Originating Master ID',

rtrim(['Account Transaction History'].[ORMSTRNM]) as 'Originating Master Name',

['Account Transaction History'].[ORPSTDDT] as 'Originating Posted Date',

['Account Transaction History'].[OrigSeqNum] as 'Originating Sequence Number',

NULL as 'Originating Source',

rtrim(['Account Transaction History'].[ORTRXSRC]) as 'Originating TRX Source',

'Originating TRX Type' = dbo.DYN_FUNC_Originating_TRX_Type(['Account Transaction History'].[ORTRXTYP]),

'Originating Type' = dbo.DYN_FUNC_Originating_Type(['Account Transaction History'].[ORGNATYP]),

['Account Transaction History'].[PERIODID] as 'Period ID',

['Account Transaction History'].[PPSGNMBR] as 'Period Posting Number',

['Account Transaction History'].[APRVLUSERID] as 'Approval User ID',

['Account Transaction History'].[APPRVLDT] as 'Approval Date',

'Polled Transaction' = dbo.DYN_FUNC_Boolean_All(['Account Transaction History'].[POLLDTRX]),

'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn]),

'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn]),

'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn]),

'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn]),

['Account Transaction History'].[PSTGNMBR] as 'Posting Number',

'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

'Posting Type from Account Master' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP]),

NULL as 'Printing Status',

['Account Transaction History'].[QKOFSET] as 'Quick Offset',

'Rate Calculation Method' = dbo.DYN_FUNC_Rate_Calculation_Method(['Account Transaction History'].[RTCLCMTD]),

rtrim(['Account Transaction History'].[RATETPID]) as 'Rate Type ID',

NULL as 'Recurring TRX',

['Account Transaction History'].[RCTRXSEQ] as 'Recurring TRX Sequence',

rtrim(['Account Transaction History'].[REFRENCE]) as 'Reference',

NULL as 'Reversing Closed Year',

NULL as 'Reversing Date',

NULL as 'Reversing History TRX',

NULL as 'Reversing Period ID',

NULL as 'Reversing TRX Source',

NULL as 'Reversing Year',

['Account Master'].[ACTNUMBR_1] as 'Segment1',

['Account Master'].[ACTNUMBR_2] as 'Segment2',

['Account Master'].[ACTNUMBR_3] as 'Segment3',

['Account Transaction History'].[SEQNUMBR] as 'Sequence Number',

rtrim(['Account Transaction History'].[SOURCDOC]) as 'Source Document',

rtrim(['Account Transaction History'].[TRXSORCE]) as 'TRX Source',

NULL as 'Tax Date',

['Account Transaction History'].[TIME1] as 'Time',

NULL as 'Transaction Type',

'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC]),

rtrim(['Account Master'].[USERDEF1]) as 'User Defined 1',

rtrim(['Account Master'].[USERDEF2]) as 'User Defined 2',

rtrim(['Account Transaction History'].[USWHPSTD]) as 'User Who Posted',

'Voided' = dbo.DYN_FUNC_Boolean_All(['Account Transaction History'].[VOIDED]),

'3' as 'Segments',

NULL as 'Workflow Approval Status',

NULL as 'Workflow Priority',

'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Account Transaction History'].[Ledger_ID]),

'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Account Transaction History'].[Ledger_ID]),

'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppAccountIndex(1,['Account Transaction History'].[ACTINDX] ),

'Journal Entry For Drillback' = 'dgpp://DGPB/?Db=&Srv=SJRAGPSQL01&Cmp=SJRA&Prod=0' +dbo.dgppJournalInquiry(1,['Account Transaction History'].[JRNENTRY],['Account Transaction History'].[RCTRXSEQ],['Account Transaction History'].[HSTYEAR],['Account Transaction History'].[TRXDATE] )

from [GL30000] as ['Account Transaction History'] with (NOLOCK)

left outer join [GL00100] as ['Account Master'] with (NOLOCK) on ['Account Transaction History'].[ACTINDX] = ['Account Master'].[ACTINDX]

left outer join [DYNAMICS].[dbo].[MC40200] as ['Currency Setup'] with (NOLOCK) on ['Account Transaction History'].[CURRNIDX] = ['Currency Setup'].[CURRNIDX]

FULL OUTER JOIN (SELECT

YEAR1 as Year,

JRNENTRY,

B.ACTINDX,

H.ACTNUMST,

G.ACTDESCR,

A.aaTRXType,

a.aaGLTRXSource,

aaTRXSource,

GLPOSTDT ,

CAST(B.DEBITAMT AS VARCHAR(255)) AS TotalDebit ,

CAST(B.CRDTAMNT AS VARCHAR(255)) AS TotalCredit ,

CAST(C.DEBITAMT AS VARCHAR(255)) AS aaDebit ,

CAST(C.CRDTAMNT AS VARCHAR(255)) AS aaCredit ,

aaTrxDim ,

aaTrxDimDescr ,

aaTrxDimDescr2 ,

aaTrxDimCode ,

aaTrxDimCodeDescr ,

aaTrxDimCodeDescr2

FROM AAG30000 AS A

LEFT OUTER JOIN AAG30001 AS B ON A.aaGLHdrID = B.aaGLHdrID

LEFT OUTER JOIN AAG30002 AS C ON B.aaGLHdrID = C.aaGLHdrID AND

B.aaGLDistID = C.aaGLDistID

INNER JOIN AAG30003 AS D ON C.aaGLAssignID = D.aaGLAssignID AND

C.aaGLDistID = D.aaGLDistID AND

C.aaGLHdrID = D.aaGLHdrID

INNER JOIN AAG00401 AS E ON D.aaTrxDimID = E.aaTrxDimID AND

D.aaTrxCodeID = E.aaTrxDimCodeID

INNER JOIN AAG00400 AS F ON E.aaTrxDimID = F.aaTrxDimID

LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

) AA on AA.JRNENTRY = ['Account Transaction History'].JRNENTRY
Community Member on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

This is great! Thanks! They are actually looking for all fields on the canned Account Transaction Smartlist (listed in SQL Views as dbo.AccountTransactions) in addition to the AA Transaction Dimension and Codes- so is there a way to combine the two into one view?
Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

I went ahead and threw in the TOP to each, just in case you miss one, this one with work and it includes the order by.

create view 'YOUR VIEW NAME' as

WITH AActe AS

(

SELECT

YEAR1 as Year,

JRNENTRY,

B.ACTINDX,

H.ACTNUMST,

G.ACTDESCR,

A.aaTRXType,

a.aaGLTRXSource,

aaTRXSource,

GLPOSTDT ,

CAST(B.DEBITAMT AS VARCHAR(255)) AS TotalDebit ,

CAST(B.CRDTAMNT AS VARCHAR(255)) AS TotalCredit ,

CAST(C.DEBITAMT AS VARCHAR(255)) AS aaDebit ,

CAST(C.CRDTAMNT AS VARCHAR(255)) AS aaCredit ,

aaTrxDim ,

aaTrxDimDescr ,

aaTrxDimDescr2 ,

aaTrxDimCode ,

aaTrxDimCodeDescr ,

aaTrxDimCodeDescr2

FROM AAG30000 AS A

LEFT OUTER JOIN AAG30001 AS B ON A.aaGLHdrID = B.aaGLHdrID

LEFT OUTER JOIN AAG30002 AS C ON B.aaGLHdrID = C.aaGLHdrID AND

B.aaGLDistID = C.aaGLDistID

INNER JOIN AAG30003 AS D ON C.aaGLAssignID = D.aaGLAssignID AND

C.aaGLDistID = D.aaGLDistID AND

C.aaGLHdrID = D.aaGLHdrID

INNER JOIN AAG00401 AS E ON D.aaTrxDimID = E.aaTrxDimID AND

D.aaTrxCodeID = E.aaTrxDimCodeID

INNER JOIN AAG00400 AS F ON E.aaTrxDimID = F.aaTrxDimID

LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

)

SELECT TOP 10000000 * FROM AActe

UNION

SELECT TOP 10000000

g.OPENYEAR as Year,

g.JRNENTRY,

g.ACTINDX,

g1.ACTNUMST,

g2.ACTDESCR,

'',

'',

'',

g.ORPSTDDT as GLPOSTDT,

g.DEBITAMT as TotalDebit,

g.CRDTAMNT as TotalCredit,

'',

'',

'',

'',

'',

'',

'',

''

FROM GL20000 g

LEFT JOIN gl00105 g1 ON g.actindx = g1.ACTINDX

LEFT JOIN GL00100 g2 ON g2.ACTINDX = g.ACTINDX

WHERE JRNENTRY NOT IN

(

SELECT JRNENTRY

FROM AActe

)

　

UNION

SELECT TOP 10000000

g.HSTYEAR as YEAR,

g.JRNENTRY,

g.ACTINDX,

g1.ACTNUMST,

g2.ACTDESCR,

'',

'',

'',

g.ORPSTDDT as GLPOSTDT,

g.DEBITAMT as TotalDebit,

g.CRDTAMNT as TotalCredit,

'',

'',

'',

'',

'',

'',

'',

''

FROM GL30000 g

LEFT JOIN gl00105 g1 ON g.actindx = g1.ACTINDX

LEFT JOIN GL00100 g2 ON g2.ACTINDX = g.ACTINDX

WHERE JRNENTRY NOT IN

(

SELECT JRNENTRY

FROM AActe

)

ORDER BY JRNENTRY DESC
Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

oh yea, views don't like ORDER BY unless you specify TOP in the SELECTS.

You have two options to choose from to make it work:

1. remove the ORDER BY at the bottom

2. Add TOP 1000000000 to each SELECT so it would be SELECT TOP 100000000 instead of just SELECT. Place a huge number in there so you'll be sure to grab each row, you don't want to use TOP 1000 when you have over a million rows.

Removing the ORDER BY is obviously the quickest way but the user will probably want to sort the list after it displays in the SmartList.
Community Member on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

Hi - thank you so much! This works as a SQL query but when I try to "create the view" I get all sorts of errors. Would you be able to modify the above that would be an example of creating the SQL View? I appreciate your help!
Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

RE: SQL View to show JE's with or without AA data

Try this one out, I just modified Mahmoud's by wrapping his AA query into a CTE and then select everything from the open year GL tables and historical GL tables where the journal wasn't in the AA CTE then selected everything from all three with a union to include the AA CTE, OPEN Year GL and Hist YEAR GL. It should work, we don't use AA so it's a little difficult to validate with 100% certainty. Give it a whirl though.

Props to Mahmoud for his original query, very nice.

WITH AActe AS

(

SELECT

YEAR1 as Year,

JRNENTRY,

B.ACTINDX,

H.ACTNUMST,

G.ACTDESCR,

A.aaTRXType,

a.aaGLTRXSource,

aaTRXSource,

GLPOSTDT ,

CAST(B.DEBITAMT AS VARCHAR(255)) AS TotalDebit ,

CAST(B.CRDTAMNT AS VARCHAR(255)) AS TotalCredit ,

CAST(C.DEBITAMT AS VARCHAR(255)) AS aaDebit ,

CAST(C.CRDTAMNT AS VARCHAR(255)) AS aaCredit ,

aaTrxDim ,

aaTrxDimDescr ,

aaTrxDimDescr2 ,

aaTrxDimCode ,

aaTrxDimCodeDescr ,

aaTrxDimCodeDescr2

FROM AAG30000 AS A

LEFT OUTER JOIN AAG30001 AS B ON A.aaGLHdrID = B.aaGLHdrID

LEFT OUTER JOIN AAG30002 AS C ON B.aaGLHdrID = C.aaGLHdrID AND

B.aaGLDistID = C.aaGLDistID

INNER JOIN AAG30003 AS D ON C.aaGLAssignID = D.aaGLAssignID AND

C.aaGLDistID = D.aaGLDistID AND

C.aaGLHdrID = D.aaGLHdrID

INNER JOIN AAG00401 AS E ON D.aaTrxDimID = E.aaTrxDimID AND

D.aaTrxCodeID = E.aaTrxDimCodeID

INNER JOIN AAG00400 AS F ON E.aaTrxDimID = F.aaTrxDimID

LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

)

SELECT * FROM AActe

UNION

SELECT

g.OPENYEAR as Year,

g.JRNENTRY,

g.ACTINDX,

g1.ACTNUMST,

g2.ACTDESCR,

'',

'',

'',

g.ORPSTDDT as GLPOSTDT,

g.DEBITAMT as TotalDebit,

g.CRDTAMNT as TotalCredit,

'',

'',

'',

'',

'',

'',

'',

''

FROM GL20000 g

LEFT JOIN gl00105 g1 ON g.actindx = g1.ACTINDX

LEFT JOIN GL00100 g2 ON g2.ACTINDX = g.ACTINDX

WHERE JRNENTRY NOT IN

(

SELECT JRNENTRY

FROM AActe

)

　

UNION

SELECT

g.HSTYEAR as YEAR,

g.JRNENTRY,

g.ACTINDX,

g1.ACTNUMST,

g2.ACTDESCR,

'',

'',

'',

g.ORPSTDDT as GLPOSTDT,

g.DEBITAMT as TotalDebit,

g.CRDTAMNT as TotalCredit,

'',

'',

'',

'',

'',

'',

'',

''

FROM GL30000 g

LEFT JOIN gl00105 g1 ON g.actindx = g1.ACTINDX

LEFT JOIN GL00100 g2 ON g2.ACTINDX = g.ACTINDX

WHERE JRNENTRY NOT IN

(

SELECT JRNENTRY

FROM AActe

)

ORDER BY JRNENTRY DESC