SQL query for Analytical Accounting Transaction Details in Dynamics GP

Please use the below query to get all open and historical year analytical accounting transaction details with journal entry number.
SELECT [‘aaGLHdr’].[year1]
AS
‘Year’,
[‘aaGLHdr’].[jrnentry]
AS ‘Journal Entry’,
Rtrim([‘aaGLHdr’].[aagltrxsource])
AS ‘Transaction Source’,
(SELECT Rtrim([actnumst])
FROM [gl00105] AS [‘Account Index Master’]
WHERE [‘Account Index Master’].[actindx] =
[‘Account Master’].[actindx]) AS
‘Account Number’,
[‘aaGLAssign’].[debitamt]
AS ‘Debit Amount’,
[‘aaGLAssign’].[crdtamnt]
AS ‘Credit Amount’,
Rtrim([‘aaTrxDim’].[aatrxdim])
AS ‘Trx Dimension’,
Rtrim([‘aaTrxDim’].[aatrxdimdescr])
AS ‘Trx Dimension Description’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcode])
AS ‘Trx Dimension Code’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcodedescr])
AS ‘Trx Dim Code Description’,
[‘aaGLHdr’].[glpostdt]
AS ‘GL Posting Date’,
[‘aaGLHdr’].[aaglhdrid]
AS ‘AA Header ID’,
[‘aaGLDist’].[aagldistid]
AS ‘AA Distribution ID’,
[‘aaGLAssign’].[aaglassignid]
AS ‘AA Assignment ID’,
[‘aaGLAssign’].[aaassignedpercent] / 100
AS ‘Assignment Percentage’,
Rtrim([‘aaGLHdr’].[aatrxsource])
AS ‘AA Transaction Source’,
[‘Account Master’].[actindx]
AS ‘Account Index’,
Rtrim([‘Account Master’].[actdescr])
AS ‘Account Description’,
Rtrim([dbo].[Dyn_func_account_type]([‘aaGLDist’].[accttype]))
AS ‘Account Type’,
Rtrim(Isnull([‘aaAliasMstr’].[aaalias], ”))
AS ‘Alias’,
Rtrim([‘aaGLDist’].[aaassetid])
AS ‘Asset ID’,
Rtrim([‘aaGLDist’].[aabookid])
AS ‘Book ID’,
Rtrim([‘aaGLDist’].[aacustid])
AS ‘Customer ID’,
Rtrim([‘aaGLDist’].[employid])
AS ‘Employee ID’,
Rtrim([‘aaGLDist’].[aaitemid])
AS ‘Item Number’,
Rtrim([‘aaGLDist’].[aasiteid])
AS ‘Site ID’,
Rtrim([‘aaGLDist’].[aavendid])
AS ‘Vendor ID’,
Rtrim([‘aaGLDist’].[curncyid])
AS ‘Currency ID’,
[‘aaGLDist’].[currnidx]
AS ‘Currency Index’,
[‘aaGLDist’].[denxrate]
AS ‘Denomination Exchange Rate’,
[‘aaGLDist’].[exchdate]
AS ‘Exchange Date’,
Rtrim([‘aaGLDist’].[exgtblid])
AS ‘Exchange Table ID’,
[‘aaGLDist’].[time1]
AS ‘Exchange Time’,
Rtrim([‘aaGLDist’].[interid])
AS ‘Intercompany ID’,
[‘aaGLDist’].[mctrxstt]
AS ‘MC Transaction State’,
[‘aaGLAssign’].[ordbtamt]
AS ‘Originating Debit Amount’,
[‘aaGLAssign’].[orcrdamt]
AS ‘Originating Credit Amount’,
Rtrim([dbo].[Dyn_func_rate_calculation_method]([‘aaGLDist’].[rtclcmtd]))
AS
‘Rate Calculation Method’,
Rtrim([‘aaGLDist’].[ratetpid])
AS ‘Rate Type ID’,
Rtrim([‘aaGLAssign’].[distref])
AS ‘Distribution Reference’,
Rtrim([dbo].[Dyn_func_gl_ledger_description]([‘aaGLHdr’].[ledger_id]))
AS
‘Ledger Description’,
Rtrim([dbo].[Dyn_func_gl_ledger_name]([‘aaGLHdr’].[ledger_id]))
AS ‘Ledger Name’,
[‘aaGLHdr’].[rctrxseq]
AS ‘Recurring TRX Sequence’,
[‘aaGLDist’].[seqnumbr]
AS ‘Sequence Number’,
[‘aaGLDist’].[aachangedate]
AS ‘Change Date’,
[‘aaGLDist’].[aachangetime]
AS ‘Change Time’
FROM [aag30003] AS [‘aaGLCode’] WITH (nolock)
LEFT OUTER JOIN [aag30001] AS [‘aaGLDist’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLDist’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] = [‘aaGLDist’].[aagldistid]
LEFT OUTER JOIN [aag30000] AS [‘aaGLHdr’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
AND [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
LEFT OUTER JOIN [gl00100] AS [‘Account Master’] WITH (nolock)
ON [‘aaGLDist’].[actindx] = [‘Account Master’].[actindx]
LEFT OUTER JOIN [aag00401] AS [‘aaTrxDimCodeSetp’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] =
[‘aaTrxDimCodeSetp’].[aatrxdimid]
AND [‘aaGLCode’].[aatrxcodeid] =
[‘aaTrxDimCodeSetp’].[aatrxdimcodeid]
LEFT OUTER JOIN [aag30002] AS [‘aaGLAssign’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLAssign’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] =
[‘aaGLAssign’].[aagldistid]
AND [‘aaGLCode’].[aaglassignid] =
[‘aaGLAssign’].[aaglassignid]
LEFT OUTER JOIN [aag00800] AS [‘aaAliasMstr’] WITH (nolock)
ON [‘aaGLAssign’].[aaaliasid] = [‘aaAliasMstr’].[aaaliasid]
LEFT OUTER JOIN [aag00400] AS [‘aaTrxDim’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] = [‘aaTrxDim’].[aatrxdimid]

UNION ALL
–Historical Data

SELECT [‘aaGLHdr’].[year1]
AS
‘Year’,
[‘aaGLHdr’].[jrnentry]
AS ‘Journal Entry’,
Rtrim([‘aaGLHdr’].[aagltrxsource])
AS ‘Transaction Source’,
(SELECT Rtrim([actnumst])
FROM [gl00105] AS [‘Account Index Master’]
WHERE [‘Account Index Master’].[actindx] =
[‘Account Master’].[actindx]) AS
‘Account Number’,
[‘aaGLAssign’].[debitamt]
AS ‘Debit Amount’,
[‘aaGLAssign’].[crdtamnt]
AS ‘Credit Amount’,
Rtrim([‘aaTrxDim’].[aatrxdim])
AS ‘Trx Dimension’,
Rtrim([‘aaTrxDim’].[aatrxdimdescr])
AS ‘Trx Dimension Description’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcode])
AS ‘Trx Dimension Code’,
Rtrim([‘aaTrxDimCodeSetp’].[aatrxdimcodedescr])
AS ‘Trx Dim Code Description’,
[‘aaGLHdr’].[glpostdt]
AS ‘GL Posting Date’,
[‘aaGLHdr’].[aaglhdrid]
AS ‘AA Header ID’,
[‘aaGLDist’].[aagldistid]
AS ‘AA Distribution ID’,
[‘aaGLAssign’].[aaglassignid]
AS ‘AA Assignment ID’,
[‘aaGLAssign’].[aaassignedpercent] / 100
AS ‘Assignment Percentage’,
Rtrim([‘aaGLHdr’].[aatrxsource])
AS ‘AA Transaction Source’,
[‘Account Master’].[actindx]
AS ‘Account Index’,
Rtrim([‘Account Master’].[actdescr])
AS ‘Account Description’,
Rtrim([dbo].[Dyn_func_account_type]([‘aaGLDist’].[accttype]))
AS ‘Account Type’,
” AS ‘Alias’,
Rtrim([‘aaGLDist’].[aaassetid])
AS ‘Asset ID’,
Rtrim([‘aaGLDist’].[aabookid])
AS ‘Book ID’,
Rtrim([‘aaGLDist’].[aacustid])
AS ‘Customer ID’,
Rtrim([‘aaGLDist’].[employid])
AS ‘Employee ID’,
Rtrim([‘aaGLDist’].[aaitemid])
AS ‘Item Number’,
Rtrim([‘aaGLDist’].[aasiteid])
AS ‘Site ID’,
Rtrim([‘aaGLDist’].[aavendid])
AS ‘Vendor ID’,
Rtrim([‘aaGLDist’].[curncyid])
AS ‘Currency ID’,
[‘aaGLDist’].[currnidx]
AS ‘Currency Index’,
[‘aaGLDist’].[denxrate]
AS ‘Denomination Exchange Rate’,
[‘aaGLDist’].[exchdate]
AS ‘Exchange Date’,
Rtrim([‘aaGLDist’].[exgtblid])
AS ‘Exchange Table ID’,
[‘aaGLDist’].[time1]
AS ‘Exchange Time’,
Rtrim([‘aaGLDist’].[interid])
AS ‘Intercompany ID’,
[‘aaGLDist’].[mctrxstt]
AS ‘MC Transaction State’,
[‘aaGLAssign’].[ordbtamt]
AS ‘Originating Debit Amount’,
[‘aaGLAssign’].[orcrdamt]
AS ‘Originating Credit Amount’,
Rtrim([dbo].[Dyn_func_rate_calculation_method]([‘aaGLDist’].[rtclcmtd]))
AS
‘Rate Calculation Method’,
Rtrim([‘aaGLDist’].[ratetpid])
AS ‘Rate Type ID’,
Rtrim([‘aaGLAssign’].[distref])
AS ‘Distribution Reference’,
Rtrim([dbo].[Dyn_func_gl_ledger_description]([‘aaGLHdr’].[ledger_id]))
AS
‘Ledger Description’,
Rtrim([dbo].[Dyn_func_gl_ledger_name]([‘aaGLHdr’].[ledger_id]))
AS ‘Ledger Name’,
[‘aaGLHdr’].[rctrxseq]
AS ‘Recurring TRX Sequence’,
[‘aaGLDist’].[seqnumbr]
AS ‘Sequence Number’,
[‘aaGLDist’].[aachangedate]
AS ‘Change Date’,
[‘aaGLDist’].[aachangetime]
AS ‘Change Time’
FROM [aag40003] AS [‘aaGLCode’] WITH (nolock)
LEFT OUTER JOIN [aag40001] AS [‘aaGLDist’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLDist’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] = [‘aaGLDist’].[aagldistid]
LEFT OUTER JOIN [aag40000] AS [‘aaGLHdr’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
AND [‘aaGLCode’].[aaglhdrid] = [‘aaGLHdr’].[aaglhdrid]
LEFT OUTER JOIN [gl00100] AS [‘Account Master’] WITH (nolock)
ON [‘aaGLDist’].[actindx] = [‘Account Master’].[actindx]
LEFT OUTER JOIN [aag00401] AS [‘aaTrxDimCodeSetp’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] =
[‘aaTrxDimCodeSetp’].[aatrxdimid]
AND [‘aaGLCode’].[aatrxcodeid] =
[‘aaTrxDimCodeSetp’].[aatrxdimcodeid]
LEFT OUTER JOIN [aag40002] AS [‘aaGLAssign’] WITH (nolock)
ON [‘aaGLCode’].[aaglhdrid] = [‘aaGLAssign’].[aaglhdrid]
AND [‘aaGLCode’].[aagldistid] =
[‘aaGLAssign’].[aagldistid]
AND [‘aaGLCode’].[aaglassignid] =
[‘aaGLAssign’].[aaglassignid]
LEFT OUTER JOIN [aag00400] AS [‘aaTrxDim’] WITH (nolock)
ON [‘aaGLCode’].[aatrxdimid] = [‘aaTrxDim’].[aatrxdimid]

Hope this helps!!!

Advertisements

2 comments

    1. Hi Mahmoud, Thanks for the update. I have mistakenly posted this script instead of my another script. Now I have posted the correct script which is referenced from GP view.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s