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:
TomV
Past 24 Hours:
1
Prev. 24 Hours:
2
Overall:
5186
People Online:
Visitors:
357
Members:
0
Total:
357
Online Now:
New Topics
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Lawson Business Intelligence/Reporting/Crystal
Domain Name Change
3/5/2024 7:45 PM
Our domain name needs to change and was hoping I c
S3 Customization/Development
Cobol calling Shell Script
2/29/2024 1:27 PM
Has anyone created or modified a Lawson Cobol prog
Infor ION
ION vs IPA
2/29/2024 1:24 AM
We had a person new to Lawson and Infor go to Info
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1348
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson S3 Financials
Extracting Received Not Invoiced (PO135) with SQL
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
mikekask
Basic Member
Posts: 10
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
New Member
Posts: 2
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
Basic Member
Posts: 10
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
New Member
Posts: 1
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
Veteran Member
Posts: 1020
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
Veteran Member
Posts: 1162
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.
Attachments
RNI SQL.txt
Received Not Invoiced (Data Only).txt
You are not authorized to post a reply.