PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 6/15/2022 2:29 PM by  JonA
Extracting Received Not Invoiced (PO135) with SQL
 5 Replies
Sort:
You are not authorized to post a reply.
Author Messages
mikekask
Private
Private
Basic Member
(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
    New Member
    (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
    Basic Member
    (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
    New Member
    (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
    Veteran Member
    (2914 points)
    Veteran Member
    Posts:996


    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
    Veteran Member
    (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
    You are not authorized to post a reply.