I am trying to make a Journal Voucher form using CR2008. In SAP the data I need to fetch is from the table OBTD (Journal Voucher Header) and OBTF (Journal Voucher Line item) and OJDT (Journal Entry) and JDT1 (Journal Entry line). However these 4 tables were not related to one another.
OBTD and OBTF has a common key (Batchnum field)
OJDT and JDT1 has a common key (TransID)
My purpose is to have a print layout form for all Journal Voucher. In the layout, the header should have the details from OBTD & OBTF table: Fields like BatchNum, Memo (remarks), RefDate (posting date), Ref1 (reference no.) and detail or line part of the layout should display the journal entry (Account Code, Account Name, Debit Amt, Credit Amt).
So I decided to make a subreport. The Journal Voucher tables (OBTD & OBTF) will be in the main report and the Journal Entries (OJDT, JDT1) will be in the subreport. My parameter is the BatchNumber and also serves as the subreport link.
However, I cannot fetch the data if the status of the Journal Voucher is still OPEN (BtfStatus = 'O'). I am able to get the data of all posted JV transaction only. Accounting require that Journal Voucher can be printed even if its open or close status.
Also, In a batch number of a Journal Voucher there can be several line item of Journal Entry. I tried to consider second parameter that the user should input what transaction number in a batch to print. However, I couldn't get the right data. Instead all transactions are being fetched in a subreport. Sometimes data duplicates.
Please help. This is one of the mandatory requirement form of our Accounting dept.
Note: I am using SAP B1 2005 PL36 and Crystal Report Basic 2008 v.12