Report of JE

[Former Member]

Hello Experts,

I need a query that will show the posted JE of A/P Invoice and Outgoing Payment by taking the input of Business Partner Name. The report template is as follows..

Posting Date	Document Date	Doc Number(A/P Invoice/Outgoing Payment)	Remarks	Account Name	Debit	Credit	Cumulative Bal.
25/09/2012	10/08/2013	Invoice No. 11100	Supplier invoice	abc		100.00
25/09/2012	10/08/2013	Invoice No. 11100	Supplier invoice	VAT Receivable (Input Tax)	2.00
25/09/2012	10/08/2013	Invoice No. 11100	Supplier invoice	Raw Materials (Stock)	100.00
25/09/2012	10/08/2013	Invoice No. 11100	Supplier invoice	Interest on Mobilisation	-1.00
25/09/2012	10/08/2013	Invoice No. 11100	Supplier invoice	FSD	-1.00
25/09/2012	25/09/2013	Outgoing Pay. No. 2550	Supplier Payment	abc	100.00
25/09/2012	25/09/2013	Outgoing Pay. No. 2550	Supplier Payment	Cash A/C Head Office		100.00

Please help me in above mention report. Thanks in advance.....

Regards,

Umamahesh.

[Former Member · Accepted solution]

I got the solution for the above requirement. Thanks....

Regards,

Umamahesh.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Could you share solution?

Thanks & Regards,

Nagarajan

[Former Member]

Hi Umamahesh,

If you got solution, please close your thread.

Thanks,

Gordon

[former_member197621]

Hi Uma,

This you can take it in general ledger report.

Financials->Financial Reports->General Ledger

In this report you un-select the account code and select the required BP and run the report.

[kothandaraman_nagarajan]

Hi,

Try this query for all transaction.

SELECT
T0.[RefDate],

case
when T0.[TransType] = 13 then 'AR'

when
T0.[TransType] = 14 then 'ARCredit'

when
T0.[TransType] = 15 then 'Delivery'

when
T0.[TransType] = 16 then 'SalesReturn'

when
T0.[TransType] = 203 then 'ARDown'

when
T0.[TransType] = 20 then 'Goods Receipt'

when
T0.[TransType] = 21 then 'Goods Return'

when
T0.[TransType] = 204 then 'APDown'

when
T0.[TransType] = 18 then 'APInvoice'

when T0.[TransType] = 13 then 'AP Credit Memo'

when
T0.[TransType] = 69 then 'LandedCost'

when
T0.[TransType] = 24 then 'Incoming Payment'

when
T0.[TransType] = 25 then 'Deposit'

when
T0.[TransType] = 46 then 'VendorPayment'

when T0.[TransType] = 57 then 'Check for Payment'

when
T0.[TransType] = 76 then 'Postdated Check'

when
T0.[TransType] = 58 then 'Inventory list'

when
T0.[TransType] = 59 then 'Good Receipt'

when
T0.[TransType] = 60 then 'Good Issue'

when T0.[TransType] = 67 then 'Inventory Transfer'

when
T0.[TransType] = 68 then 'Work Instruction'

when
T0.[TransType] = 162 then 'Inventory Valuation'

when
T0.[TransType] = 202 then 'Production order'

when
T0.[TransType] = -2 then 'Opening Balance'

when
T0.[TransType] = -3 then 'Closing Balance'

when
T0.[TransType] = 30 then 'Journal Entry'

when
T0.[TransType] = 321 then 'Internal Reconilation'

when
T0.[TransType] = 10000046 then 'Data archive'

when
T0.[TransType] = 310000001 then 'Initial Qty'

when
T0.[TransType] = 10000071 then 'Inventory posting' end as DocType

,T0.[TransId],
T2.[U_NAME] as Creator, T3.[FormatCode] as 'G/L Acct/BP code',
T1.[ShortName] as 'G/L Acct/BP Name', T3.[AcctName], T1.[Debit] as
Debit, T1.[Credit] as Credit

FROM
OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId inner join
OUSR T2 on t0.usersign = t2.userid INNER JOIN OACT T3 ON T1.Account =
T3.AcctCode

WHERE
T0.[RefDate] between [%1] and [%2]

GROUP
BY T0.[RefDate],T0.[TransType],T0.[TransId], T2.[U_NAME],
T3.[AcctName], T1.[Debit], T1.[Credit], T1.[ShortName],
T3.[FormatCode] ORDER BY T0.[TransId]

Hope helpful.

Thanks & Regards,

Nagarajan

[Former Member]

Hello Nagarajan,

When i execute the query it's showing "No Data found ....." message.

Regards,

Umamahesh.