SP TN Query - How to block owner field from changes?

[Former Member]

Hi all,

I need help, how to write the query to block "owner" field in document from being change other than default value ?

Thanks

[Former Member · Accepted solution]

Hi,

For which document you want to do this ?

Thanks,

Harshal

[Former Member · Accepted solution]

Hi,

Will apply to all the document with "owner" field.

Probably can test by starting from PO.

Thanks

[Former Member · Accepted solution]

Hi,

I have a work around for you.

First create 1 UDF in OPOR header level and apply below FMS to get Ownercode.

SELECT $[OPOR.OwnerCode.Number]

and you can set this field as auto refresh and make it inactive so that user cannot change this UDF value.

Now apply this below SP to achieve your requirement.

IF (@object_type = '22' AND @transaction_type IN ('A', 'U'))

BEGIN

	IF EXISTS ( SELECT distinct  T0.OwnerCode from OPOR T0  where T0.DocEntry = @list_of_cols_val_tab_del and T0.U_DocumentThrough <> T0.OwnerCode)
	BEGIN
	SELECT @error = -12,

    @error_message = 'Please do not change the owner'

END

END

Hope this helps.

Thanks,

Harshal

[Former Member · Accepted solution]

Hi Harshal,

Thank you for the prompt reply.

The workaround work, but I tried to simplified by not creating the UDF. So I add the FMS to query as below, however it will block even is the right owner.

IF (@object_type = '22' AND @transaction_type IN ('A', 'U'))

BEGIN

    IF EXISTS ( SELECT distinct  T0.OwnerCode from OPOR T0  where T0.DocEntry = @list_of_cols_val_tab_del and (SELECT $[OPOR.OwnerCode])<> T0.OwnerCode)

    BEGIN

    SELECT @error = -1299,

    @error_message = 'Please do not change the owner'

    END

END

[Former Member · Accepted solution]

Hi,

How about making it equal sign.

IF (@object_type = '22' AND @transaction_type IN ('A','U'))

BEGIN

    IF EXISTS ( SELECT distinct  T0.OwnerCode from OPOR T0 where T0.DocEntry = @list_of_cols_val_tab_del and (SELECT $[OPOR.OwnerCode])= T0.OwnerCode)

    BEGIN

    SELECT @error = -1299,

    @error_message = 'Please do not change the owner'

    END

END

Thanks,

Harshal

[Former Member · Accepted solution]

Hi Harshal,

Equeal sign doesn't work.

Any idea?

Thanks

[Former Member · Accepted solution]

Hi,

Lets wait for some experts to focus on it.

Thanks,

Harshal

[former_member227598 · Accepted solution]

Hi OLga Hu,

Please try the below SP .

IF (@object_type = '22' AND @transaction_type IN ('A','U'))

BEGIN

    IF EXISTS ( SELECT distinct  T1.[userId]

       from OPOR T0  INNER JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID]  where (T0.DocEntry = @list_of_cols_val_tab_del)

and (T0.[OwnerCode] = T1.[empID]) and (T0.[UserSign] <> T1.[userId] ))

    BEGIN

    SELECT @error = -1299,

    @error_message = 'Please do not change the owner'

    END

END

If you resolved the issue by using the above solution please mark as the correct  or Helpful answer.

Rgds,

Kamlesh Naware

[former_member227598 · Accepted solution]

Hi OLga ,

If your issue are resolved with using my solution so kindly marks closed the thread with mark the correct or helpful answer.

Thanks,

Kamlesh Naware

[Former Member · Accepted solution]

Hi Kamlesh,

Thank you, it worked so far.

Already marked as correct answer.

Thanks

[Former Member · Accepted solution]

Hi Kamlesh,

Maybe before this my testing was not complete.

Now having problem when user try to update the document that was not created by the user.

For eg:

Invoice 16001, created by user A, owner is user A

User B tried to update the invoice 16001 and change the owner to user B, SP TN will still block it.

Can this be solve?

Thanks

[kothandaraman_nagarajan]

Hi,

Please let me know how document owner is updated from current setup.

Below standard setup for document owner update in document.

• When a default sales employee is defined for a business partner (Business Partner Master Data -> General tab), the employee who is linked to that sales employee (Employee Master Data window) is displayed automatically.
• If the sales employee name selected in the document is not linked to an employee, the Owner field displays the employee linked to the user who is currently logged on to SAP Business One.

Thanks

[Former Member]

Hi Nagarajan,

Currently the condition is no 1. there is setup in employee master data.

Kamlesh query work for the condition.

Thanks

[former_member186712]

Hi Olga,

IF (@object_type = '22' AND @transaction_type IN ('U')) BEGIN     IF EXISTS ( SELECT top 1 T1.OwnerCode FROM OPOR T0 inner join ADOC T1 on T0.DocENtry = T1.DocENtry and T1.Objtype = '22' where T1.OwnerCode <> T0.OwnerCode and T0.DocEntry = @list_of_cols_val_tab_del ORDER BY T1.LogInstanc  desc)     BEGIN SELECT @ERROR=1,@ERROR_MESSAGE= 'Please you cant chage the owner'     END END

This query works in the Purchase Orders. I also have the same SP.

Regards,

Augusto

[Former Member]

Hi Augusto,

I assume this should work on update only, is that right?

I need to work on both add and update.

Anyway, Kamlesh query help to solve my solution, yours solution would giving help too.

Thanks

[Former Member]

Dear Olga

Why you don't use data ownership?

Regards

Edmund

[Former Member]

Hi Edmund,

Because I want the document to be visible to others as well.

Just want to block the "owner" field.

Current version  = 8.81 PL 11

Thanks

[Former Member]

Dear Olga

Can you write a query with approval procedure to block the user amend other users document?

Regards

Edmund

[Former Member]

Hi Edmund,

I don't think the approval procedure will behave like that.

If I am wrong, please correct me with details.

Thanks