Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Financial Management
Lawson S3 Financials
Extracting Received Not Invoiced (PO135) with SQL
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Vikas
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5070
People Online:
Visitors:
439
Members:
0
Total:
439
Online Now:
New Topics
Top Forum Posters
Name
Points
Greg Moeller
4123
David Williams
3343
JonA
3249
Kat V
2908
Woozy
1973
Jimmy Chiu
1880
Kwane McNeal
1431
Roger French
1304
Ragu Raghavan
1294
mark.cook
1244
Forums
Search Forum
Search
Advanced Search
Topics
Posts
Unanswered
Active Topics
Prev
Go to previous topic
Next
Go to next topic
Last Post 6/15/2022 2:29 PM by
JonA
Extracting Received Not Invoiced (PO135) with SQL
5 Replies
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
mikekask
Private
Private
(18 points)
Basic Member
Posts:10
Send Message:
4/28/2010 11:46 AM
I am trying to automate the data gathering for an AP Scorecard report in Excel. Part of the info I need is the Received Date and the Current Amount for PO's that are received but not invoiced. This info prints on the PO135 report, but I would like to get the data directly with an SQL query through an Oracle Client. This can all be done with a VBA macro in Excel. I need to know what fields and tables to get the data from. I'm not sure if this type of thing is discussed in this forum or on this site.
Thanks.
Mike
Jbeard
Private
Private
(6 points)
New Member
Posts:2
Send Message:
4/28/2010 6:44 PM
Hey Mike, how are things at YNH? Tell Barb I said hello.
So if I understand your request you are looking for SQL to return the $ value of the PO that has not been received. This is the amount that is accrued for in the PO135.
There is an OPEN_PO_AMT field on the PURCHORDER table that should contain this information, the field also exists on POLINE and is specific to that line.
Receipts are in PORECEIVE and PORECLINE and these will have the received date for those goods that have been received.
thanks
Jason
mikekask
Private
Private
(18 points)
Basic Member
Posts:10
Send Message:
4/28/2010 7:47 PM
Hi Jason,
Thanks very much. From my initial testing this looks like it exactly what I need. You saved me a lot of research time. Things are going well here at YNH. I let Barb know you said Hello.
This application I'm working on has several more sections, so I'll probably be posting more questions soon.
Mike
K Pacheco
System Consultant
Private
(3 points)
New Member
Posts:1
Send Message:
5/19/2022 7:26 PM
Were you able to figure out a SQL query that works for this? I'm getting stumped as to what the program is doing with certain PO's and why they populate on the report.
Kat V
Sr Supply Chain System Analyst
South Broward Hospital District
(2908 points)
Veteran Member
Posts:994
Send Message:
6/7/2022 8:45 PM
PO135 reports both the IC130 PO Receipt postings and the non-stock and specials - **IF** the PO is released at the time you run the report. But both the IC130 and the PO135 in update will post to the GL based on the released status of the receipt. It also rolls up receipts per PO Line and displays the date of the last receipt with the sum qty of the open receipts.
SO - it's perfectly possible to get a gl posting that doesn't appear on the report and is the number one reason we yell at the buyers not to touch the PO after receipt.
As for SQL - we've never attempted it - but you are looking at lawson.porecline table. You want ent-rec-qty - matched-qty - arch-wo-qty and you want to pull this from any receipt where the header isn't closed.
JonA
Private
Private
(3449 points)
Veteran Member
Posts:1147
Send Message:
6/15/2022 2:29 PM
Attached is the Crystal report - Received Not Invoiced (Data Only) - I use to pull RNI data and the SQL. You'll have to change the extension on the report to .rpt. I had to change it to .txt to be able to attach the file.
Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
Attachments
RNI SQL.txt
Received Not Invoiced (Data Only).txt
You are not authorized to post a reply.
General
--Dealing with Lawson / Infor
--SOx Compliance
--Career Development
--Staffing
--Hosting / Cloud
Human Capital Management
--Lawson S3 HR/Payroll/Benefits
--Infor Global HR (GHR)
Financial Management
--Infor CloudSuite Financials
--Lawson S3 Financials
--Lawson M3 Financials
--Infor FMS
--Lawson Services Automation (PS...
--Lawson Budgeting & Planning
Expense Management
--Infor Expense Management
--Lawson Time & Expense
Enterprise Resource Planning & Manufacturing
--Infor ERP (Syteline)
--Lawson S3 Procurement
--Lawson M3 Manufacturing Operat...
Enterprise Asset Management
--Infor EAM
--M3 Enterprise Asset Management
Supply Chain Management
--Infor SCM
--Lawson S3 Supply Chain
--Lawson M3 Supply Chain
Performance Management
--Lawson Business Intelligence/R...
----Smart Notification
--Lawson M3 Business Performance...
--Lawson Opportunity Analyzer
Lawson Healthcare
--Cloverleaf
--Lawson MediSuite
--Analytics for Healthcare
Infor / Lawson Platforms
--Lawson Landmark
--S3 Systems Administration
----S3 Security
--M3 Application Foundation
--M3 Workplace Foundation
--Infor CloudSuite
Integration / Customization
--Infor ION
--S3 Interfaces/Conversion
--S3 Customization/Development
--S3 Application Upgrades
--Lawson ProcessFlow
User Experience
--Infor Workspace
--Infor Social Space
--Lawson Portal
----Lawson Design Studio
--Lawson Smart Office
----Mashup Designer
--Mobility
--Lawson Add-Ins for Microsoft O...
--Lawson Enterprise Search
Job Fair
--Positions Available
--People Available
LawsonGuru.com
--LawsonGuru.com Support
Forums
Financial Management
Lawson S3 Financials