ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Accounting >> Purchase journal without department detail?
http://www.istechforum.com/YaBB.pl?num=1645733966

Message started by Laura Oliver on 02/24/22 at 13:19:26

Title: Purchase journal without department detail?
Post by Laura Oliver on 02/24/22 at 13:19:26

I am looking for a report that will show how we spent our money in 2021. The Purchase journal kinda does this but it gives department detail. I do not mind each transaction listed, but I do not want subtotals by department.

Detail trial balance may do this, but I cant get it to total by the parent GL. Every time i do it get depatment detail.

i think DBA/EVO may do this because you do get to print Financial statements without GL department detail.
How can I see purchase journal without GL detail, but a summary of the gl account.

Thanks
Laura

Title: Re: Purchase journal without department detail?
Post by Laura Oliver on 02/24/22 at 13:50:18

I do want the grandtotal of each GL Account ---- just not the department detail.

Title: Re: Purchase journal without department detail?
Post by Lynn_Pantic on 02/24/22 at 16:45:26

GL-E Summary - Net Change and limit to Purchase Journal? Try that.

Title: Re: Purchase journal without department detail?
Post by Laura Oliver on 03/02/22 at 15:23:20

I tried it every which way and I still get department detail.

Title: Re: Purchase journal without department detail?
Post by Kelloggs on 03/03/22 at 08:17:29

How do you define a "Department"?

I would use the BKAPHPO table to get that information. You can use the following columns as Dpt

bkap_po_obycus = Job Number
bkap_po_entby = Buyer
bkap_po_loc = Location
bkap_po_gldpt = GL Account

This query will show you only totals, use it as an example to get the department details

Then use Excel to query the data (You must have a ODBC Connection on your computer)
It is quite easy.

Option Explicit
'==============
Public curr_wb As Workbook
Public main_sh As Worksheet
Public var_sql As String
'==========================================
Public Const odbc_erp As String = "DBA_ERP" 'Your ODBC Connection
Public erp_conn As New ADODB.Connection
Public erp_rs As New ADODB.Recordset
Public erp_cmd As New ADODB.Command
'==================================
Private Sub get_data()
Dim i As Integer
Set curr_wb = ThisWorkbook
Set main_sh = curr_wb.Sheets("Main")
main_sh.Range("A4:C2000").Value = Empty
i = 4

'Open Conn
erp_conn.CursorLocation = adUseClient
erp_conn.Open odbc_erp, "USER", "PASS" 'If you have one

var_sql = "select MONTH(bkap_po_orddte) AS THE_MONTH,LTRIM(RTRIM(bkap_po_obycus)) AS THE_DPT,ROUND(sum(bkap_po_subtot),2) AS THE_AMOUNT from BKAPHPO where bkap_po_orddte >= '2019-01-01' and bkap_po_orddte <= '2019-12-31' group by THE_MONTH,THE_DPT;"
erp_rs.Open var_sql, erp_conn, adOpenStatic
Do While Not erp_rs.EOF
main_sh.Range("A" & i).Value = erp_rs("THE_MONTH")
main_sh.Range("B" & i).Value = erp_rs("THE_DPT")
main_sh.Range("C" & i).Value = erp_rs("THE_AMOUNT")
i = i + 1
erp_rs.MoveNext
Loop

'Close Recordset
erp_rs.Close

'Close Connection
erp_conn.Close
End Sub

Kelloggs
:)

PD:
I just realized a flaw with this sql query. The Order Date could not be on the same month as of the Receiving
We can use the BKAPHPOL table, and the BKAP_POL_ARD column to get the month, if you want by month

:-[