Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

PO Report out of Macola

Status
Not open for further replies.

J G PM

Technical User
Jun 18, 2020
24
US
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?


PO_Example_rtfjui.jpg



J Green
DeltaTRAK
 
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:

 
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
 
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:

 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top