Extracting Received Not Invoiced (PO135) with SQL

Sort:
You are not authorized to post a reply.
Author
Messages
mikekask
Basic Member
Posts: 10
Basic Member
    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
    New Member
      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
      Basic Member
        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
        New Member
          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
          Veteran Member
            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
            Veteran Member
              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
              Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
              You are not authorized to post a reply.