Add Purchase Order Line Account Number to PO Workflow

My customer requested to setup the workflow step based on the account (expense or inventory) assigned to the line items, this account is loaded from the e-requisition module when generating the purchase order and it represents the cost center of the requested item.

They wanted to have this for their projects which is currently managed using an account segment, unfortunately the workflow does not integrates the line account number where I had to find another alternative solution.

One of the fields I found in the workflow was “Requested By” which was unused by most of users nor was used by e-requisition module, so I created a trigger to copy account number from the original field to the requested by field and created the workflow on the requested by field.

Below the trigger I used:

-- =============================================
-- Author:        Mohammad R. Daoud
-- Create date: 24-02-2011
-- Description:    Trigger to copy Account Number to
-- requested by field in PO Details
-- =============================================
CREATE TRIGGER POP10110RequestedBy
   ON  POP10110
   AFTER INSERT
AS
BEGIN
DECLARE @PONUMBER    VARCHAR(500)
DECLARE @ORD        BIGINT
DECLARE @INVINDX    BIGINT
DECLARE @REQSTDBY    VARCHAR(5000)

SELECT @PONUMBER = PONUMBER, @ORD = ORD, @INVINDX = INVINDX FROM INSERTED

SELECT @REQSTDBY = LEFT(LTRIM(RTRIM(ACTNUMST, 20))) FROM GL00105 WHERE ACTINDX = @INVINDX

UPDATE POP10110 SET REQSTDBY = @REQSTDBY WHERE PONUMBER = @PONUMBER AND ORD = @ORD

END
GO

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com