PO Report out of Macola

[J G PM]

I have been asked to come up with a report around Purchase Orders that shows varied information from Macola. I have created reports in the past using MS Visual Studio. Typically, I get the SQL I want from one or two existing reports, I put the SQL together, run it in our SQL Server environment until I am happy with what I am seeing, then I run it through Report Wizard on the MS Visual Studio and create my report. All good..

My issue is for this task is finding the right SQL, I have a couple reports, but they are both using the same tables, and neither are offering me the GL Account code that my Finance TM is asking for. Is it possible to get the combined information in one report and save the Finance Team from having to run the PO Number and drill down, to get varied information?

Here is the SQL I have plagiarized thus far:
SELECT
SUBSTRING(ph.ord_no,1,6) AS Ord_No,
SUBSTRING(ph.ord_no,7,2) AS Release,
ph.vend_no,
pl.item_no,
pl.item_desc_1,
pl.item_desc_2,
pl.uom,
pl.qty_ordered,
pl.request_dt,
pl.byr_plnr,
ap.vend_name,
pl.line_no,
pl.qty_remaining,
ph.ord_status AS HdrStatus
FROM (poordhdr_sql ph
LEFT OUTER JOIN poordlin_sql pl ON (ph.ord_no = pl.ord_no)
AND (ph.vend_no = pl.vend_no))
LEFT OUTER JOIN apvenfil_sql ap ON ph.vend_no = ap.vend_no

WHERE NOT (ph.ord_status = 'C' OR ph.ord_status = 'H')
and ord_dt >= '10/20/2020' and ord_dt <= '10/22/2020'
---and ph.ord_no = '25449'

The selected fields are flexible obviously, to start I had done Select * from the tables and did not see the GL Account Code, and when I check in Macola to see where that field exists I only see the prglfil_sql table but when I query that table I get nothing.
SQL I use to get tables: (also plagiarized)
select sys.columns.name as ColumnName, tables.name as TableName
from sys.columns join sys.tables on sys.columns.object_id = tables.object_id
where sys.columns.name like 'gl%'
order by columnName asc

This gives me a bunch of tables but none seem to be regular tables that might have the data I want?

J Green
DeltaTRAK

http://www.deltatrak.com

 

[dgillz]

Jim,

Mn_no, sb_no and dp_no are are the main account, cost unit and cost center, respectively. These are all in the poordlin_sql table.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[J G PM]

Don, thanks for the insight and help. As always you are spot on.
Thanks,


J Green
DeltaTRAK

http://www.deltatrak.com

 

[J G PM]

In my original post I show a GL Distribution account number, for recieving, where are the receiving transactions stored or where can I get the GL Distribution Account number related to any of the respective PO's? Its not in my original query so it must be another table. Is there a transaction table specific to Purchase Orders?
Thanks,


J Green
DeltaTRAK

http://www.deltatrak.com

 

[dgillz]

The IMRECHST_SQL will show you the PO receipts. Th GL account number is stored in the POORDLIN_SQL as well.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[NEmacGuy]

As Dgillz has mentioned your starting point should be the IMRECHST_SQL. If you want the distribution of the PO Receipts then you really want the GBKMUT table.

 

[J G PM]

To those of you who have chimed in and offered help I do appreciate it.. I am getting close. Recently I was asked where are the $$'ars on my report. the data I am pulling does not have money in the report. I was reluctant to bring that into a report available for everyone. Now I am being asked so I want to try. The issue is, I do not see the values or dollars in the main report and it appears to be inside IMRECHST_SQL but now I am challenged with joining that table with the other two tables I am already using. Is there any help out there?
Thanks


This is my current table lay out.
FROM poordhdr_sql po INNER JOIN poordlin_sql pl ON po.ord_no = pl.ord_no