Skip to Content
0

query-show the user who entered the transaction.

Nov 03, 2017 at 07:46 AM

36

avatar image

Please, can you add a new field on the far right-hand side of this query to show the user who entered the transaction.

SELECT (Case when t2.GroupMask = '1' then 'Asset' when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Capital and Reserves' when t2.GroupMask = '4' then 'Turnover' when t2.GroupMask = '5' then 'Cost Of Sales' when t2.GroupMask = '6' then 'Operating Cost' when t2.GroupMask = '7' then 'Non Operating Income and Expenditure' when t2.GroupMask = '8' then 'Tax and Extraordinary Items' end)'Drawer', T1.[Account], T2.[AcctName], T0.[RefDate], T0.[DueDate], T0.[TaxDate], T5.[Name] 'Period Name', (Case when T0.TransType = 15 then 'DN' + ' ' +T0.[BaseRef] when T0.TransType = 16 then 'RE' + ' ' +T0.[BaseRef] when t0.TransType = '203' then 'DT' + ' ' +T0.[BaseRef] when t0.TransType = '13' then 'IN' + ' ' +T0.[BaseRef] when t0.TransType = '14' then 'CN' + ' ' +T0.[BaseRef] when t0.TransType = '132' then 'Correction Invoice' when t0.TransType = '20' then 'PD' + ' ' +T0.[BaseRef] when t0.TransType = '21' then 'PR' + ' ' +T0.[BaseRef] when t0.TransType = '204' then 'DT' + ' ' +T0.[BaseRef] when t0.TransType = '18' then 'PU' + ' ' +T0.[BaseRef] when t0.TransType = '19' then 'PC' + ' ' +T0.[BaseRef] when t0.TransType = '69' then 'IF' + ' ' +T0.[BaseRef] when t0.TransType = '24' then 'RC' + ' ' +T0.[BaseRef] when t0.TransType = '25' then 'DP' + ' ' +T0.[BaseRef] when t0.TransType = '46' then 'PS' + ' ' +T0.[BaseRef] when t0.TransType = '57' then 'CP' + ' ' +T0.[BaseRef] when t0.TransType = '76' then 'DD' + ' ' +T0.[BaseRef] when t0.TransType = '182' then 'BT' + ' ' +T0.[BaseRef] when t0.TransType = '-2' then 'OB' + ' ' +T0.[BaseRef] when t0.TransType = '-3' then 'BC' + ' ' +T0.[BaseRef] when t0.TransType = '30' then 'JE' + ' ' +T0.[BaseRef] when t0.TransType = '58' then 'ST' + ' ' +T0.[BaseRef] when t0.TransType = '59' then 'SI' + ' ' +T0.[BaseRef] when t0.TransType = '60' then 'SO' + ' ' +T0.[BaseRef] when t0.TransType = '67' then 'IM' + ' ' +T0.[BaseRef] when t0.TransType = '162' then 'MR' + ' ' +T0.[BaseRef] when t0.TransType = '202' then 'PW' + ' ' +T0.[BaseRef] End) 'Doc No.', (Case when t0.TransType = '15' then 'DN Delivery' when t0.TransType = '16' then 'RE Returns' when t0.TransType = '203' then 'DT A/R Down Payment' when t0.TransType = '13' then 'IN A/R Invoice' when t0.TransType = '14' then 'CN A/R Credit Memo' when t0.TransType = '132' then 'Correction Invoice' when t0.TransType = '20' then 'PD Goods Receipt PO' when t0.TransType = '21' then 'PR Goods Return' when t0.TransType = '204' then 'DT A/P Down Payment Invoice' when t0.TransType = '18' then 'PU A/P Invoice' when t0.TransType = '163' then 'A/P Correction Invoice' when t0.TransType = '164' then 'A/P Correction Invoice Reversal' when t0.TransType = '19' then 'PC A/P Credit Memo' when t0.TransType = '69' then 'IF Landed Costs' when t0.TransType = '24' then 'RC Incoming Payment' when t0.TransType = '25' then 'DP Deposit' when t0.TransType = '46' then 'PS Vendor Payment' when t0.TransType = '57' then 'CP Checks for Payment' when t0.TransType = '76' then 'DD Postdated Deposit' when t0.TransType = '182' then 'BT BoE Transaction' when t0.TransType = '-2' then 'OB Opening Balance' when t0.TransType = '-3' then 'BC Closing Balance' when t0.TransType = '321' then 'Internal Reconciliation' when t0.TransType = '30' then 'JE Journal Entry' when t0.TransType = '58' then 'ST Stock List' when t0.TransType = '59' then 'SI Goods Receipt' when t0.TransType = '60' then 'SO Goods Issue' when t0.TransType = '67' then 'IM Inventory Transfers' when t0.TransType = '68' then 'Work Instructions' when t0.TransType = '162' then 'MR Inventory Valuation' when t0.TransType = '202' then 'PW Production Order' when t0.TransType = '-1' then 'All Transactions' end) 'Document Type', T1.BaseRef 'No', T1.[TransId] 'Trans No', T1.[ProfitCode], T1.[Project] 'Project Code', (T1.Debit -T1.Credit) 'Deb./Cred.(LC)', T0.[Memo] 'Remarks', T1.[ContraAct] 'Offset Acc', Case when T1.ContraAct=T3.AcctCode Then T3.AcctName Else Case when T1.ContraAct=T4.CardCode Then T4.CardName Else 'NULL' End End 'Offset Acc Name', T0.[Ref2], T0.[Ref3], T1.[Ref1],T0.[Ref1], T1.[Ref2], T1.[Ref3Line], T1.[U_OFFA] 'Office' FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode LEFT JOIN OCRD T4 ON T4.CardCode = T1.ContraAct LEFT JOIN OACT T3 ON T3.AcctCode =T1.ContraAct INNER JOIN OFPR T5 ON T0.[FinncPriod] = T5.[AbsEntry] WHERE T0.[RefDate] between [%0] and [%1]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers