Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Unanswered

Adding General Ledger User-Defined Fields to Smartlist

Posted on by

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!

Categories:
  • Gavin Profile Picture
    Gavin 2,337 on at
    RE: Adding General Ledger User-Defined Fields to Smartlist

    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

    pastedimage1620813638241v1.png

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans