×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PO Report out of Macola

PO Report out of Macola

PO Report out of Macola

(OP)
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
www.deltatrak.com

RE: PO Report out of Macola

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:

www.gainfocus.biz/exceladdin.html

RE: PO Report out of Macola

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

J Green
DeltaTRAK
www.deltatrak.com

RE: PO Report out of Macola

(OP)
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
www.deltatrak.com

RE: PO Report out of Macola

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:

www.gainfocus.biz/exceladdin.html

RE: PO Report out of Macola

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.

RE: PO Report out of Macola

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close