Prevent past date entry on the delivery date field

[Former Member]

Hi Guys,

I was wondering whether it is possible within standard SAP Business one 8.8 to prevent users from entering past date in the delivery date field on the Sales order screen. I have people making mistakes by entering 10/10/2010 instead of 10/10/2011. This caused problems with reports etc..

Is there a formatted search query i can create to warn/alert users with a message stating incorrect date entered? Please advice best course of action?

Regards

nick

[Former Member · Accepted solution]

Hi Nic.......

You can do this by two ways........

You can either stop users to do previous year entry by Locking the Last Year Posting Period or you may create a SP Transaction Notification to restrict so that you can never ever do entries in previous Year.........

Regards,

Rahul

[Former Member · Accepted solution]

HI Raul,

How can i create a SO Transaction Notification to restrict so that you can never ever do entries in previous Year?

[Former Member · Accepted solution]

Hi,

Try:

IF @object_type = '17' and @transaction_type = 'A'

BEGIN

IF EXISTS (SELECT T0.DocEntry

FROM dbo.ORDR T0 INNER JOIN RDR1 T1 ON T1.DocEntry=T0.DocEntry

WHERE DateDiff(YY,T1.ShipDate, GetDate())>0 AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

select @error = 17,

@error_message = 'You are not allowed to post to previous year.'

END

END

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

I have tried to add this query to a Formatted search and i am receiving an error message:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@object_type".

2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@list_of_cols_val_tab_del".

3). [Microsoft][SQL Server

[Former Member · Accepted solution]

This is not a FMS query but the SQL code to add to SP TransactionNotification.

[Former Member · Accepted solution]

Hi

There is a procedure in SQL , paste the query Given by Gordon over there, it will automatically block the user if they select wrong date.

Regards

[former_member445201 · Accepted solution]

Hi,

I have added the query on to the Stored Precedures on the SQL management studio under Programmability> stored precedures. I have created a new query and saved it under this location. It still allows me to add the sales order with previous date under the delivery date field.

Is the above steps correct? I have done this on my test database as i was not too sure on what to do..

[former_member196081 · Accepted solution]

Hi,

code given by Gordon will apply on following path..

Go to SQL Server>>Select Database>>Programmibility>>Stored Procedure>>Select SBO_SP_TransactionNotification>>Right Click>>Select Modify>> Paste Given Code Here(Under Add code Here Row)

[former_member445201 · Accepted solution]

Deepak,

The following code is already in this section? Can i just add Gordon query below the last "END" statement?

-- ADD YOUR CODE HERE

declare @islocked int

select @islocked = 0

if(@object_type = '17' and @transaction_type <> 'A')

select @islocked = U_LockedForEdit from [@A1WMS_Staging] where U_objType = '17' and U_docEntry = @list_of_cols_val_tab_del

if(@object_type = '22' and @transaction_type <> 'A')

select @islocked = U_LockedForEdit from [@A1WMS_Staging] where U_objType = '22' and U_docEntry = @list_of_cols_val_tab_del

if(@object_type = '202' and @transaction_type <> 'A')

select @islocked = U_LockedForEdit from [@A1WMS_Staging] where U_objType = '202' and U_docEntry = @list_of_cols_val_tab_del

if(@islocked <> 0)

begin

select @error = 999

select @error_message = 'Order Cannot be Updated/Canceled/Deleted as it is Locked in WMS'

end

[former_member196081 · Accepted solution]

Hi,

Paste the code above following line....

-- Select the return values

select @error, @error_message

end

[former_member218051 · Accepted solution]

Hi Nick,

You have to add the SP_TN under

Programmability -

---

> Stored Procedure -

---

> SBO_SP_TransactionNotification -

---

>

Right Click -

---

> Modify

And paste the code given by Gordon below

"ADD YOUR CODE HERE"

Thanks

Malhaar