You’re offline. This is a read only version of the page.
Announcements
I'm looking for tips on how to add the new user-defined fields that were created in the Dynamics GP 18.3 update in the General Journal form into our "Account Transactions" smartlist. I think my problem so far has been that if I add the GL10000 table, my smartlist only works for unposted transactions since I am looking in the Transactions work table, but then of course if I instead add the GL30000 table, my smartlist only works for historical posted transactions. I want this smartlist to pull all transactions, posted and unposted. Thank you to anyone that can help!
Hi,
If you are using smartlist builder you can use the existing "Account Transactions" SQL view and add in those missing columns. (the existing "Account Transactions" SQL views UNION's all the GL tables together so you get everything)
i.e. I've added the two columns here
The full query is below that you can use in Smartlist Builder (again this is just the "Account Transactions" SQL view with the addtions of the user defined fields)
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',
['Transaction Amounts Work'].[debitamt]
AS 'Debit Amount',
['Transaction Amounts Work'].[crdtamnt]
AS 'Credit Amount',
'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=GP2018&Srv=CCCD-85071&Cmp=TWO&Prod=0'
+ dbo.Dgppaccountindex(1, ['Transaction Amounts Work'].[actindx] ),
'Journal Entry For Drillback' =
'dgpp://DGPB/?Db=GP2018&Srv=CCCD-85071&Cmp=TWO&Prod=0'
+ dbo.Dgppjournalentry(1, ['Transaction Amounts Work'].[jrnentry], ['Transaction Work'].[rctrxseq], 1, 1 ),
/* **** Addition for User Defined Fields **** */
User_Defined_Text01 as [Journal User Defined Field 1],
User_Defined_Text02 as [Journal User Defined Field 2]
/* **** Addition for User Defined Fields **** */
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]
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',
['Year-to-Date Transaction Open'].[debitamt]
AS 'Debit Amount',
['Year-to-Date Transaction Open'].[crdtamnt]
AS 'Credit Amount',
'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=GP2018&Srv=CCCD-85071&Cmp=TWO&Prod=0'
+ dbo.Dgppaccountindex(1, ['Year-to-Date Transaction Open'].[actindx] ),
'Journal Entry For Drillback' =
'dgpp://DGPB/?Db=GP2018&Srv=CCCD-85071&Cmp=TWO&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] ),
/* **** Addition for User Defined Fields **** */
User_Defined_Text01 as [Journal User Defined Field 1],
User_Defined_Text02 as [Journal User Defined Field 2]
/* **** Addition for User Defined Fields **** */
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]
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',
['Account Transaction History'].[debitamt]
AS 'Debit Amount',
['Account Transaction History'].[crdtamnt]
AS 'Credit Amount',
'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=GP2018&Srv=CCCD-85071&Cmp=TWO&Prod=0'
+ dbo.Dgppaccountindex(1, ['Account Transaction History'].[actindx] ),
'Journal Entry For Drillback' =
'dgpp://DGPB/?Db=GP2018&Srv=CCCD-85071&Cmp=TWO&Prod=0'
+ dbo.Dgppjournalinquiry(1, ['Account Transaction History'].[jrnentry], ['Account Transaction History'].[rctrxseq], ['Account Transaction History'].[hstyear], ['Account Transaction History'].[trxdate] ),
/* **** Addition for User Defined Fields **** */
User_Defined_Text01 as [Journal User Defined Field 1],
User_Defined_Text02 as [Journal User Defined Field 2]
/* **** Addition for User Defined Fields **** */
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]
Hope this helps
Thanks
Gavin
#1
André Arnaud de Cal...
291,359
Super User 2024 Season 2
#2
Martin Dráb
230,370
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
