Duplicate PO's in Customer Orders

Our Order Entry has the habit of blowing through the CSI notifications when they enter an order with a duplicate Purchase Order number. We want to extend this somehow because we are getting way too many duplicate orders entered. Some thoughts have been to add a checkbox when order entry acknowledges the duplicate PO warning or have a report that is ran at the end of each day that shows the duplicate PO's for that day. Has anyone else done anything like this? We are multi-tenant cloud.

Find more posts tagged with

Syteline Development

Accepted answers

Legacy Contributor

Good morning, give this approach a shot:

Extend and replace the SLCos IDO
Add a Derived property to the IDO extension called DerDupCustPOExists
1. Defined as (SELECT TOP 1 1 FROM CO x where x.co_num <> CoNum and x.cust_po = CustPO)
  1. Where CoNum and CustPO are the existing IDO properties (case sensitive), not sure if what I have in my example is exact
Since you are in SAAS, there is not much you can do before saving since you do not have an existing object to compare to, what we can do is allow the user to save a new customer order with an existing PO of another CO, and then (once saved), we can added logic on an event called StdObjectSelectCurrentCompleted to throw some formscript to do some things such as:
1. Compare this new DerDupCustPOExists flag with an UET called Uf_CustServDupCustPOAcknowledged and if a duplicate cust po if found and the customer service rep has not acknowledged this to override, we can throw an error to the screen and/or prevent future saves by calling the ThisForm.PrimaryIDOCollection.SaveEnabled = False or any other other logic we want to use to handle this issue

All comments

Legacy Contributor

This is the Stored Procedure. I am wondering if I could capture the Return Value and have it set with a different integer if a duplicate is found vs not? Has anyone done anything like this?

-- Converted from as/rules/co.p (Procedure:validate-cust-po)
-- This routine checks to see if a cust po exists on
-- another customer order for the customer specified.
-- If so, it returns a warning message.

/* $Header: /ApplicationDB/Stored Procedures/CustPoExistsWarningSp.sp 13    12/23/15 5:04a mguan $ */
/*
***************************************************************
*                                                             *
*                           NOTICE                            *
*                                                             *
*   THIS SOFTWARE IS THE PROPERTY OF AND CONTAINS             *
*   CONFIDENTIAL INFORMATION OF INFOR AND/OR ITS AFFILIATES   *
*   OR SUBSIDIARIES AND SHALL NOT BE DISCLOSED WITHOUT PRIOR *
*   WRITTEN PERMISSION. LICENSED CUSTOMERS MAY COPY AND       *
*   ADAPT THIS SOFTWARE FOR THEIR OWN USE IN ACCORDANCE WITH *
*   THE TERMS OF THEIR SOFTWARE LICENSE AGREEMENT.            *
*   ALL OTHER RIGHTS RESERVED.                                *
*                                                             *
*   (c) COPYRIGHT 2010 INFOR. ALL RIGHTS RESERVED.           *
*   THE WORD AND DESIGN MARKS SET FORTH HEREIN ARE            *
*   TRADEMARKS AND/OR REGISTERED TRADEMARKS OF INFOR          *
*   AND/OR ITS AFFILIATES AND SUBSIDIARIES. ALL RIGHTS        *
*   RESERVED. ALL OTHER TRADEMARKS LISTED HEREIN ARE         *
*   THE PROPERTY OF THEIR RESPECTIVE OWNERS.                  *
*                                                             *
***************************************************************
*/
/* $Archive: /ApplicationDB/Stored Procedures/CustPoExistsWarningSp.sp $
*
* SL9.01 13 RS6298 mguan Wed Dec 23 05:04:47 2015
* RS6298
*
* SL8.02 12 rs4588 Dahn Thu Mar 04 10:30:44 2010
* RS4588 Copyright header changes
*
* SL8.02 11 rs4588 Dahn Thu Mar 04 09:41:22 2010
* RS4588 Copyright header changes
*
* SL8.01 10 rs3953 Vlitmano Tue Aug 26 16:44:24 2008
* RS3953 - Changed a Copyright header?
*
* SL8.01 9 rs3953 Vlitmano Mon Aug 18 15:09:24 2008
* Changed a Copyright header information(RS3959)
*
* SL8.00 8 rs2968 nkaleel Fri Feb 23 01:25:56 2007
* changing copyright information
*
* SL8.00 7 RS2968 prahaladarao.hs Thu Jul 13 02:50:17 2006
* RS 2968, Name change CopyRight Update.
*
* SL8.00 6 RS2968 prahaladarao.hs Tue Jul 11 05:50:11 2006
* RS 2968
* Name change CopyRight Update.
*
* SL7.05 5 91818 NThurn Fri Jan 06 14:42:21 2006
* Inserted standard External Touch Point call. (RS3177)
*
* SL7.04 4 91110 hcl-singind Mon Dec 26 04:43:08 2005
* Issue #: 91110.
* Added "WITH (READUNCOMMITTED)" to co Select Statement.
*
* $NoKeywords: $
*/
CREATE PROCEDURE dbo.extgen_CustPoExistsWarningSp (
   @CoNum CoNumType
, @CustPo CustPoType
, @CustNum CustNumType
, @Infobar InfobarType OUTPUT
)
AS
-- End of CallALTETPs.exe generated code.

DECLARE
@Severity INT

set @Infobar = NULL

IF EXISTS (SELECT 1
   FROM co WITH (READUNCOMMITTED)
   where co.cust_po = @CustPo
   AND   co.cust_num = @CustNum
   AND   co.co_num <> ISNULL(@CoNum, NCHAR(1)) )
BEGIN
   EXEC MsgAppSp @Infobar OUTPUT, 'I=ExistFor='
      , '@co.co_num'
      , '@co.cust_po'
      , @CustPo
END
return 0

Legacy Contributor

Still hoping for some help here. I think I am going to need to move the Stored Procedure to Form Script since I cannot get a return if it finds a duplicate PO. Has anyone done this and can offer some direction? Here is the SP:

DECLARE
@Severity INT

set @Infobar = NULL

Cloudsuite PLM for Process Monthly User Group Meeting_January 2024.zip

Legacy Contributor

Good morning, give this approach a shot:

Extend and replace the SLCos IDO
Add a Derived property to the IDO extension called DerDupCustPOExists
1. Defined as (SELECT TOP 1 1 FROM CO x where x.co_num <> CoNum and x.cust_po = CustPO)
  1. Where CoNum and CustPO are the existing IDO properties (case sensitive), not sure if what I have in my example is exact
Since you are in SAAS, there is not much you can do before saving since you do not have an existing object to compare to, what we can do is allow the user to save a new customer order with an existing PO of another CO, and then (once saved), we can added logic on an event called StdObjectSelectCurrentCompleted to throw some formscript to do some things such as:
1. Compare this new DerDupCustPOExists flag with an UET called Uf_CustServDupCustPOAcknowledged and if a duplicate cust po if found and the customer service rep has not acknowledged this to override, we can throw an error to the screen and/or prevent future saves by calling the ThisForm.PrimaryIDOCollection.SaveEnabled = False or any other other logic we want to use to handle this issue

Legacy Contributor

I've never done this, but I think you could simply return a variable depending on the conditional test. Something like:

DECLARE
@Severity INT

DECLARE @return_status int;

set @Infobar = NULL

set @return_status = 2

END

ELSE

BEGIN

set @return_status = 1

END
return @return_status

Legacy Contributor

The existing variable @Severity is used for return status, they have various meanings, but generally, anything other than 0 is treated as a failure or warning. I believe the MsgAppSp can be changed as well to immediately set @Severity to the error code sent back by:

EXEC @Severity = @Infobar OUTPUT, 'I=ExistFor='

Legacy Contributor

Edd,

Thanks for your suggestion - I was making this harder than it should have been. I made a few changes that I wanted to share in case anyone else needs to do this:

1. I added a Derived property to my extended SLCos IDO and set it as a Property Class of ListYesNo.

2. The definition of this property is:
CASE WHEN EXISTS (SELECT TOP 1 1 FROM co x where x.co_num <> CoNum and x.cust_num = CustNum and x.cust_po = CustPo) THEN 1 ELSE 0 END

3. I then added a checkbox field called Duplicate PO on the Customer Orders form and made the Derived property its data source

-J

Copyright © 2025 Infor. All rights reserved.