Extracting Received Not Invoiced (PO135) with SQL

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

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

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.

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.

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.