Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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;



Best Regards,
Mahmoud M. AlSaadi

7 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. So, is there any solution to Julio's problem? We have the same problem - same Item number, unit cost, vendor, but different description being merged into one line of a combined quantity, with the first line's description.

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

      Best Regards,
      Mahmoud M. AlSaadi

      Delete