Open Forum

Expand all | Collapse all

Retain Line Item Order from Requisition When Transferring to PO

  • 1.  Retain Line Item Order from Requisition When Transferring to PO

    SILVER CONTRIBUTOR
    Posted 10 days ago
    Hi all!

    Before I ask my question I want to give a shoutout to @Amber Bell for troubleshooting this with me last week in the Summit GPUG forums. Want to see if anyone else has thoughts or potential workarounds.

    PO requisition line items are in order of entry yet the resulting PO line items are in alphabetical order. Can the line item order from the requisition be retained in the PO? The order is important as there is logic when it comes to quotes, batching, etc.

    Thanks in advance!

    ------------------------------
    Kelly Marinoff
    Keel & Company, LLC
    Virginia Beach VA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Retain Line Item Order from Requisition When Transferring to PO

    TOP CONTRIBUTOR
    Posted 10 days ago
    Hi @Kelly Marinoff ,

    I have thoughts, but it would take some testing and tweaking to figure out the viability of it.

    Questions I have....
    1.) We're talking about the actual display order in GP and not just the print order on the PO form, correct?  I'll have a whole separate set of thoughts if we're talking report writer.
    2.) Do you have GP Power Tools?
    3.) Would you theoretically be OK if GP assigned the PO lines their place alphabetically and then some magic happened after the generation/save of the PO that resorted in the PO itself?
    4.) Do you always do requisitions, sometimes do requisitions, or are the requisitions exceptions as opposed to rules?
    5.) Is there any manufacturing or project accounting used?

    OK  - now for my thoughts.

    The primary tables I'm looking at/thinking of -full disclosure I could be missing some - are the POP10110 and the SOP60100 tables.  The POP10110 table is the purchase order line table, and the SOP60100 is the SOP-POP link...which also houses the requisition to PO link.

    The SOP60100 houses fields that show both how it's sorted on the PO (ORD in screenshot below) and how it's sorted on the requisition (LNITMSEQ in screenshot below).
    SOP60100

    POP10110

    The task would be to update both the POP10110 ORD field and the SOP60100 ORD field to match the value in SOP60100..LNITMSEQ.  My initial thought would be a trigger or code from GP Power Tools to make the updates.

    I've messed around with it a little bit directly in SQL in my test company (naturally), and I can make it work under very very limited circumstances using the code below. By no means am I a SQL or coding expert so there may be much more elegant ways to accomplish it.  You'd likely also need to build in additional criteria/restrictions for no requisitions, etc., but it could be a place to start.

    UPDATE POP10110
    SET POP10110.ORD=SOP60100.LNITMSEQ
    FROM SOP60100,POP10110
    WHERE POP10110.PONUMBER=SOP60100.PONUMBER AND POP10110.ORD=SOP60100.ORD AND POP10110.ITEMDESC=SOP60100.ITEMDESC AND SOPTYPE=9

    UPDATE SOP60100
    SET ORD=LNITMSEQ WHERE SOPTYPE=9

    Does that make sense?  Let me know if it doesn't - no offense will be taken. :)

    Thanks,

    Samantha

    ​​

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Retain Line Item Order from Requisition When Transferring to PO

    SILVER CONTRIBUTOR
    Posted 8 days ago
    @Samantha Higdon, thanks SO much for your detailed response! This has given me a lot to think about. We do have GP Power Tools so that could be a great option ​to utilize in this situation. We are moving to an all requisition process (no direct PO's), so I'm excited to see if we can get this sorting figured out. I'll keep you updated as to our progress.
    Thanks again, you're awesome!

    ------------------------------
    Kelly Marinoff
    Keel & Company, LLC
    Virginia Beach VA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Retain Line Item Order from Requisition When Transferring to PO

    TOP CONTRIBUTOR
    Posted 8 days ago
    You're most welcome!  It was fun for me to take even a topical pass at it because it's something I haven't dug into before.

    If there's anything else I can do, just let me know!

    Thanks,

    Samantha​

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role