cancel
Showing results for 
Search instead for 
Did you mean: 

Stored procedure updating PO

Former Member
0 Kudos

Hi Experts

I have created a stored procedure which updates a UDF on the PO when a certain criteria is met. The stored proc works fine, however, for some reason i am unable to add any items with this proc activated - any idea why? Proc below:

--Set QC parametre on PO

IF @transaction_type = 'A' or @transaction_type = 'U' AND @object_type = '22'

begin

declare @QC as nvarchar(20)

set @QC = (

select distinct 'Yes' from por1

inner join opor on OPor.DocEntry = POR1.DocEntry

inner join OITM on OITM.ItemCode = POR1.ItemCode

where

OITM.U_QC = 'Yes'

and

POR1.docentry=@list_of_cols_val_tab_del)

begin

update opor

set opor.u_QC = @QC where opor.docentry=@list_of_cols_val_tab_del

end

end

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member218051
Active Contributor
0 Kudos

Hi,

firing DML statements on database is not recommended.

you may loose SAP support.

Instead of updating it from back end , validate that field through SPTN and throw an error if validation fails.

In this way gradually user will develop the habit of updating that field.

Thanking you

Malhaar

Former Member
0 Kudos

Hey Malhaar

I have no idea how to do that or what do you mean...

Will you please explain to me how to do this?

Thank you

former_member541807
Active Contributor
0 Kudos

hi,

Malhaar is referring to SP_TransactionNoticcation Stored Procedure within the SBO Database. every SBO Database has this storedProcedure. the rule of this StoredProc is to validated inputed data by the user before committing actual update in the Database. you can search within the Forum how to use the SP_TransactionNotication storedProc.

regards,

Fidel

Former Member
0 Kudos

Hello Fidel

Thank you for your reply. I understand the SPTN and how the stored proc works but not by what Malhaar mean "validate that field through SPTN and throw an error if validation fails".

How do you validate it so that before you send it for approval that it will give you an error?

thanks

former_member218051
Active Contributor
0 Kudos

Hi,

You have to write this code in SQL SERVER in the following path

Expand your DATABASE NODE

Select your database.

Select Programability

Select Stored Procedures

select SP_TRANASACTIONNOTIFICATION

And paste your code in the ADD CODE HERE SECTION

IF @transaction_type = 'A' or @transaction_type = 'U' AND @object_type = '22'

declare @QC as nvarchar(20)

begin

if not exists (select distinct 'Yes' from por1

inner join opor on OPor.DocEntry = POR1.DocEntry

inner join OITM on OITM.ItemCode = POR1.ItemCode

where OITM.U_QC = 'Yes'

and POR1.docentry=@list_of_cols_val_tab_del)

select @error = -1,

@error_message = 'Set U_QC to YES''

end

execute this code.

SQL give you message executed successfully.

Now when user adds the document server will implicitly fire this code.

with the help of system variable @list_of_cols_val_tab_del it will capture the document entry and give you the result.

As per your requirement you want to U_QC field to be updated to YES.

now if the user has not updated it to YES before adding then SPTN will not commit the transaction thereby giving an error message. In this way user will develop the habit of updating the field.

If you want to conditionally update the field you can do so by devising additional logic.

hope this will help you

thanking you

malhaar

former_member541807
Active Contributor
0 Kudos

hi,

doing an update Directly to your Database using SQL Script or directly from the Database is Prohibited. it may cause of losing Support from SAP.. .

regards,

Fidel

Former Member
0 Kudos

Hi,

Update B1 table directly is not allowed. You are risking lost SAP support.

Thanks,

Gordon