Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Sunday, December 27, 2015

Purchase Requisition to Purchase Order - Incorrect Line Item Description on Purchase Order


When a purchase requisition is fully approved, it shall be purchased to be migrated to a purchase order. This post describes a specific scenario in which the line item description is being incorrectly migrated to PO line items. 

Scenario :
  • Create a purchase requisition and enter several lines with the same item number (either inventory or non-inventory)
  • Each line item consists of various details (Quantity and Unit Cost ..etc) 
  • In case there is an active requisition workflow, this document will be submitted for approvals. Otherwise, move to the next step immediately

Requisition Line Items

  • Click on Actions > Purchase in order to open the "Purchase Order Preview" window. Flipping through the line items, you can see that all line items have the item description of the "first line" specifically, which is incorrect 

Purchase Order Preview – Line Item One
Purchase Order Preview – Line Item Two
Purchase Order Preview – Line Item Three
Purchase Order Preview – Line Item Four

  • Now, click on "Generate" button in order to generate a purchase order. A report will be printed which is "Purchase Order Generation Registry". It will show the item description for the four lines.
 
Purchase Order Generation Registry Report


  • Moving to the Purchase Order window, the PO lines description is inherited from the first line as shown in the screen shot below. 
Purchase Order Lines - Descriptions

Solution:
The script can be downloaded from this link >>> Download Link 


/*--------------------------------------------------------------------------------
Creation Date: 27 December, 2015
Modified Date: 24 January, 2016
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to correct line item description in POP10110 which 
are migrated from a requisition.

Revision History:
Revision No.            Revision Date     Description
1                       27/12/2015        Original Version
2                       24/01/2016        Modified Version
-------------------------------------------------------------------------------*/

CREATE VIEW POPReq_GPEssentials_LineDesc
AS
    SELECT  A.SOPNUMBE AS RequisitionNumber ,
            A.PONUMBER ,
            A.LNITMSEQ ,
            B.ORD Req_Ord ,
            B.LineNumber ,
            C.ORD PO_Ord ,
            B.QTYORDER ,
            RTRIM(LTRIM(B.ITEMDESC)) Original_Item_Description ,
            LTRIM(LTRIM(C.ITEMDESC)) Migrated_Item_Description ,
            C.DEX_ROW_ID
    FROM    [SOP60100] AS A
            LEFT OUTER JOIN POP30210 AS B
ON A.SOPNUMBE = B.POPRequisitionNumber
             AND B.ORD = A.LNITMSEQ
            LEFT OUTER JOIN dbo.POP10110 AS C
ON C.PONUMBER = A.PONUMBER
             AND C.ORD = A.ORD
    WHERE   RTRIM(LTRIM(B.ITEMDESC)) <> RTRIM(LTRIM(C.ITEMDESC));
              GO

             
IF EXISTS ( SELECT  *
            FROM    POPReq_GPEssentials_LineDesc )
    BEGIN
        DECLARE @RequisitionNumber CHAR(17) ,
            @PONumber CHAR(17) ,
            @LineSequence INT ,
            @Req_Order INT ,
            @LineNumber INT ,
            @PO_Order INT ,
            @OriginalItemDesc CHAR(101) ,
            @MigratedItemDesc CHAR(101) ,
            @Dex_Row_ID INT;


        DECLARE POPReq_CurSOR CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
        FOR
            SELECT  A.SOPNUMBE AS RequisitionNumber ,
                    A.PONUMBER ,
                    A.LNITMSEQ ,
                    B.ORD Req_Ord ,
                    B.LineNumber ,
                    C.ORD PO_Ord ,
                    RTRIM(LTRIM(B.ITEMDESC)) Original_Item_Description ,
                    LTRIM(LTRIM(C.ITEMDESC)) Migrated_Item_Description ,
                    C.DEX_ROW_ID
            FROM    [SOP60100] AS A
                    LEFT OUTER JOIN POP30210 AS B
ON A.SOPNUMBE = B.POPRequisitionNumber
      AND B.ORD = A.LNITMSEQ
                    LEFT OUTER JOIN dbo.POP10110 AS C
ON C.PONUMBER = A.PONUMBER
                    AND C.ORD = A.ORD
            WHERE   RTRIM(LTRIM(B.ITEMDESC)) <> RTRIM(LTRIM(C.ITEMDESC));



        OPEN POPReq_CurSOR;

        FETCH NEXT FROM POPReq_CurSOR
       INTO @RequisitionNumber, @PONumber, @LineSequence, @Req_Order,
            @LineNumber, @PO_Order, @OriginalItemDesc, @MigratedItemDesc,
            @Dex_Row_ID;

        WHILE @@FETCH_STATUS = 0
            BEGIN
            
                UPDATE  dbo.POP10110
                SET     ITEMDESC = @OriginalItemDesc
                WHERE   PONUMBER = @PONumber
                        AND DEX_ROW_ID = @Dex_Row_ID;
                         

                FETCH NEXT FROM POPReq_CurSOR
       INTO @RequisitionNumber, @PONumber, @LineSequence, @Req_Order,
                    @LineNumber, @PO_Order, @OriginalItemDesc,
                    @MigratedItemDesc, @Dex_Row_ID;

            END;

        CLOSE POPReq_CurSOR;
        DEALLOCATE POPReq_CurSOR;


    END;


Important Note:

If There are certain cases in which the two lines might have the same item number, same unit cost but 
different item description ... In such cases, these two lines are merged into one line and the description of one of the items will be overwritten. This is the default behavior of the system. 

Best Regards,
Mahmoud M. AlSaadi

8 comments:

  1. Has this work for anyone? I tried running this and I still keep facing the same issue where GP is merging all line descriptions into one. Please help.

    ReplyDelete
    Replies
    1. Hello Julio
      In fact, there are multiple scenarios in which the line items of a requisition are migrated into a Purchase Order.

      There are certain cases that the line items are merged, is this your case ? Please elaborate in order for me to provide a suitable answer.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Hi Mahmood,

    The issue I am having is when the requisition has the same Item, Vendor ID and unit cost. The only thing that changes is the description for each line.

    I am looking to keep all lines descriptions in the same PO. However the only option I have available in GP is to create multiple POs in order to separate the lines, and even in this case only the first description is kept.

    I will really appreciate any help or suggesting you can provide.

    Best Regards,
    Julio Fernandez

    ReplyDelete
    Replies
    1. Hello Julio
      The specific case you are describing is the standard process of the POP module, when there are multiple lines with the same: vendor id, item number and unit cost, the migrate to PO window will automatically merge them into one line.

      The script I am providing is when you have got multiple lines with the same item number and different unit cost (which will not be merged by the Migrate to PO process), although, the item description will be overridden by the first line item description.

      Please never hesitate to share any further inquiries,
      Best Regards,

      Delete
  3. Thanks Mahmood,

    I appreciate the feedback.

    Regards,

    ReplyDelete
  4. Create separate item cards, that is the only solution that worked for with me.

    Best Regards,
    Mahmoud M. AlSaadi

    ReplyDelete
  5. Hi Mahmoud,

    how are you doing. how to automate the above script so each PO is created from requisition will have correct description from the requisition.

    ReplyDelete
  6. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete