on 12-31-2014 2:31 PM
Dear Experts,
I want to create GL Transaction report.
Can anybody please help me for this?
Thanks,
Hi,
Have you tried with standard report under Financial report?
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sir,
Thank you for information,
As per clients requirements this report given a output.
But still they asking me to create query for this and save like other queries,
For easy to use.
Can you please help me to add commutative balance in this query please!!
SELECT T1.[Account], T2.[AcctName], T0.[RefDate], T0.[DueDate], T0.[TaxDate], T1.BaseRef 'Doc No',
(Case when t0.TransType = '15' then 'Delivery'
when t0.TransType = '16' then 'Returns'
when t0.TransType = '203' then 'A/R Down Payment'
when t0.TransType = '13' then 'A/R Invoice'
when t0.TransType = '165' then 'A/R Correction Invoice'
when t0.TransType = '166' then 'A/R Correction Invoice Reversal'
when t0.TransType = '14' then 'A/R Credit Memo'
when t0.TransType = '132' then 'Correction Invoice'
when t0.TransType = '20' then 'Goods Receipt PO'
when t0.TransType = '21' then 'Goods Return'
when t0.TransType = '204' then 'A/P Down Payment'
when t0.TransType = '18' then '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 'A/P Credit Memo'
when t0.TransType = '69' then 'Landed Costs'
when t0.TransType = '24' then 'Incoming Payment'
when t0.TransType = '25' then 'Deposit'
when t0.TransType = '46' then 'Vendor Payment'
when t0.TransType = '57' then 'Checks for Payment'
when t0.TransType = '76' then 'Postdated Deposit'
when t0.TransType = '182' then 'BoE Transaction'
when t0.TransType = '-2' then 'Opening Balance'
when t0.TransType = '-3' then 'Closing Balance'
when t0.TransType = '321' then 'Internal Reconciliation'
when t0.TransType = '30' then 'Journal Entry'
when t0.TransType = '58' then 'Stock List'
when t0.TransType = '59' then 'Goods Receipt'
when t0.TransType = '60' then 'Goods Issue'
when t0.TransType = '67' then 'Inventory Transfers'
when t0.TransType = '68' then 'Work Instructions'
when t0.TransType = '162' then 'Inventory Valuation'
when t0.TransType = '202' then 'Production Order'
when t0.TransType = '-1' then 'All Transactions'
end) as 'Document Type',
T0.Number,
T1.[TransId] 'Trans No',
T1.[ProfitCode],
T1.[FCDebit],
T0.[Memo] 'Remarks',
T1.[ContraAct],
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line]
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
LEFT JOIN OACT T2 ON T1.Account = T2.AcctCode
LEFT JOIN OCRD T3 ON
T2.AcctCode = T3.DebPayAcct
WHERE
(T0.[RefDate] >= [%0] and T0.[RefDate] <= [%1]) and (T1.[Account] >= [%3] and T1.[Account] <= [%4])
Thanks,
Please give to us some more clarification.
For example you want all the credit/debit of the accounts for all the journal entries?
You need a Crystal Report or just a query?
With Regards,
Harris K.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Could you please be more specific. What do you need exactly ?
For example the query below gives you all the rows of the journal entries grouped by account and ordered by account too.
Select
account as 'Account Number',
debit as 'Debit amount',
credit as 'Credit amount'
from jdt1
group by account, debit, credit
order by account
If you need a query with all columns you have at the photos, please let as know.
With Regards,
HK
Hi Harris,
I tried this query as below but i want to add Doc No,Doc Type,Control Acc and Commutative Balance in this. Please assist,
SELECT T1.[Account], T2.[AcctName], T0.[RefDate], T0.[Number], T0.[DueDate], T0.[TaxDate], T1.[TransId], T1.[ProfitCode], T1.[FCDebit],T0.[Memo], T1.[ContraAct],Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ), T0.[Ref2], T0.[Ref3], T1.[Ref1], T1.[Ref2], T1.[Ref3Line] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode INNER JOIN OCRD T3 ON T2.AcctCode = T3.DebPayAcct
Thanks,
Try the below please:
-- Account = Control Account
-- month = the month (as i saw, you had in the excel)
-- Baseref = Document Number
-- case... is the document type
-- * I could not find the Commutative Balance... could you please help me with that ?
-- i think that you mean Cumulative Balance and there is no field for that. If you want that let me know !!
With Regards,
HK
SELECT
T1.[Account] as 'Control Account',
T2.[AcctName],
T0.[RefDate],
T0.[Number],
t0.BaseRef as 'Doc Number',
T0.[DueDate],
T0.[TaxDate],
month (t0.CreateDate) as 'Month',
T1.[TransId],
T1.[ProfitCode],
T1.[FCDebit],
T0.[Memo],
(Case when t0.TransType = '15' then 'Delivery'
when t0.TransType = '16' then 'Returns'
when t0.TransType = '203' then 'A/R Down Payment'
when t0.TransType = '13' then 'A/R Invoice'
when t0.TransType = '165' then 'A/R Correction Invoice'
when t0.TransType = '166' then 'A/R Correction Invoice Reversal'
when t0.TransType = '14' then 'A/R Credit Memo'
when t0.TransType = '132' then 'Correction Invoice'
when t0.TransType = '20' then 'Goods Receipt PO'
when t0.TransType = '21' then 'Goods Return'
when t0.TransType = '204' then 'A/P Down Payment'
when t0.TransType = '18' then '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 'A/P Credit Memo'
when t0.TransType = '69' then 'Landed Costs'
when t0.TransType = '24' then 'Incoming Payment'
when t0.TransType = '25' then 'Deposit'
when t0.TransType = '46' then 'Vendor Payment'
when t0.TransType = '57' then 'Checks for Payment'
when t0.TransType = '76' then 'Postdated Deposit'
when t0.TransType = '182' then 'BoE Transaction'
when t0.TransType = '-2' then 'Opening Balance'
when t0.TransType = '-3' then 'Closing Balance'
when t0.TransType = '321' then 'Internal Reconciliation'
when t0.TransType = '30' then 'Journal Entry'
when t0.TransType = '58' then 'Stock List'
when t0.TransType = '59' then 'Goods Receipt'
when t0.TransType = '60' then 'Goods Issue'
when t0.TransType = '67' then 'Inventory Transfers'
when t0.TransType = '68' then 'Work Instructions'
when t0.TransType = '162' then 'Inventory Valuation'
when t0.TransType = '202' then 'Production Order'
when t0.TransType = '-1' then 'All Transactions'
end) as 'Document Type',
T1.[ContraAct],
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T0.[Ref2],
T0.[Ref3],
T1.[Ref1],
T1.[Ref2],
T1.[Ref3Line]
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN OACT T2 ON T1.Account = T2.AcctCode
INNER JOIN OCRD T3 ON T2.AcctCode = T3.DebPayAcct
If you want to have the cumulative balance in a query it should be a different one. Because at the query you created you have all the lines of jdt1 table. You can't have a cumulative balance in line level.
The only solution for your problem if you want to have a sum of all credits and a sum of all debits at the end of the query is to insert two more fields at the existing query, debit and credit. When you run the query if you click "Ctrl + (the name of the field at header level)" you will have a sum at the end of the query.
User | Count |
---|---|
102 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.