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