Hi,
1. Do not query OJDT and JDT1 without adding any condtions in where clause. If too many records, your system may be hanged.
Try this:
SELECT
T2.[FormatCode],
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) 'Document Type', (Case when t2.GroupMask = '1' then 'Asset'
when t2.GroupMask = '2' then 'Liabilites' when t2.GroupMask = '3' then 'Equity'when t2.GroupMask = '4' then 'Revenue' when t2.GroupMask = '5' then 'Expenditure' end)'Drawer',
T0.Number,
T1.[TransId] 'Trans No',
T1.[ProfitCode],
T1.Debit,
T1.Credit,
T0.[Memo] 'Remarks',
T1.[ContraAct],
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 where T0.[RefDate] between [%0] and [%1]
Thanks & Regards,
Nagarajan
Add comment