Document Series Stored Procedure Check

[Former Member]

Hi Experts,

I have 4 document series for Purchase Order, Good Reciept PO, AP Invoice as under :

For Purchase Order

Retail 1000-9999

Wholesale 2000-2999

Manufacturing 3000-3999

Trading 4000-4999

For Good Reciept PO

Retail 1000-9999

Wholesale 2000-2999

Manufacturing 3000-3999

Trading 4000-4999

AP Invoice

Retail 1000-9999

Wholesale 2000-2999

Manufacturing 3000-3999

Trading 4000-4999

Now when any marketing document e.g Puchase Order is created in Retail and based on Purchase Order Good reciept Note is created, I want that what document series was selected in the based document it should automatically come in the target document e.g PO in retail and doing GRPO based on PO It should catch retail Document series by default. How this can be achived in SAP Business One.

Can we do it with Stored Procedure. Is so then please let me know how it can be done. or else can it be also done that whatever base document series is selected the target document should also have the same series name otherwise it would give some type of error.

Kindly help in this regard.

Regards,

Kamlesh

[Former Member]

You can use != instead.

Thanks,

Gordon

[Former Member]

Someone has present the same case a while ago. The conclusion is: there is no easy way to achieve your goal. Go for SDK or an add-on.

[Former Member · Accepted solution]

It can do using stored procedure...

For example, for PO and good receipt.

insert this code into the stored procedure SBO_sp_transactionnotification:

-

---

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

SELECT @series_OPDN = T0.Series FROM OPDN T0 where T0.DocEntry=@list_of_cols_val_tab_del

declare @series_OPOR nvarchar (20)

select @series_OPOR = (SELECT distinct T1.series FROM opor T1 inner join pdn1 T0 on T0.basetype=T1.objtype and T0.baseref=T1.docnum where T0.DocEntry=@list_of_cols_val_tab_del)

if @series_OPDN<>@series_OPOR

begin

declare @seriesname nvarchar (20)

set @seriesname = isnull((select seriesname from NNM1 where objectcode=20 and series=@series_OPOR),'Another')

set @error= 1

set @error_message=N'You must select series: '+ @seriesname

end

end

end

[Former Member · Accepted solution]

Hi Andrey

Thanks for the reply. I have tried your SP but after putting in SQL SP and executing, its giving error as -

Msg 137, Level 15, State 2, Procedure SBO_SP_TransactionNotification, Line 178

Must declare the scalar variable "@series_OPDN@series_OPOR".

I used the same SP which you gave as under :

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

SELECT @series_OPDN = T0.Series FROM OPDN T0 where T0.DocEntry=@list_of_cols_val_tab_del

declare @series_OPOR nvarchar (20)

select @series_OPOR = (SELECT distinct T1.series FROM opor T1 inner join pdn1 T0 on T0.basetype=T1.objtype and T0.baseref=T1.docnum where T0.DocEntry=@list_of_cols_val_tab_del)

if @series_OPDN@series_OPOR

begin

declare @seriesname nvarchar (20)

set @seriesname = isnull((select seriesname from NNM1 where objectcode=20 and series=@series_OPOR),'Another')

set @error= 1

set @error_message=N'You must select series: '+ @seriesname

end

end

end

Kindly help and let me know what might be wrong.

Looking forward for an positive and promt reply.

Thanks once again,

Regards,

Kamlesh

[Former Member]

Hi,

Can anyone help if in this SP.

Regards,

Kamlesh

[Former Member]

i have found an error:

change "if @series_OPDN@series_OPOR"

for "if @series_OPDN<>@series_OPOR"

[Former Member]

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

SELECT @series_OPDN = T0.Series FROM OPDN T0 where T0.DocEntry=@list_of_cols_val_tab_del

declare @series_OPOR nvarchar (20)

select @series_OPOR = (SELECT distinct T1.series FROM opor T1 inner join pdn1 T0 on T0.basetype=T1.objtype and T0.baseref=T1.docnum where T0.DocEntry=@list_of_cols_val_tab_del)

if @series_OPDN != @series_OPOR

begin

declare @seriesname nvarchar (20)

set @seriesname = isnull((select seriesname from NNM1 where objectcode=20 and series=@series_OPOR),'Another')

set @error= 1

set @error_message=N'You must select series: '+ @seriesname

end

end

end

Edited by: Andrey Vologzhanin on Mar 11, 2009 4:08 PM

[Former Member]

Dear Andrey,

I have been able to sucessfully execute the SP in SQL Stored Procedure. But now the problem is coming that I am adding a PO like say in Series Name RETAIL and adding it to the system. Now based on this PO I am doing a GRN and trying to add it with some diffrent series say TRADING then its giving me an Error as - [ADOC], 'You must select series - Another' which is PERFECT and this is my requirement but now when I am trying to change the series which was there in the PO as Retail in GRPO as RETAIL then also GRPO is not getting added to the system. I am getting the same error message as - [ADOC], 'You must select series - Another'.

It should give me an error only if the based document series is not selected in the target document but here I am not able to add GRPO if also I am selecting the based document series name in the target document.

Please advise and help as to what might be the problem.

Awaiting for your solution and guidence.

Regards,

Kamlesh

[Former Member]

Try it:

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

set @series_OPDN = isnull((select T1.Seriesname FROM OPDN T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')

declare @series_OPOR nvarchar (20)

set @series_OPOR =

(SELECT distinct T2.seriesname

FROM opor T1

inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)

inner join NNM1 T2 on (T1.series=T1.series and T1.objtype=T2.objectcode)

where T0.DocEntry=@list_of_cols_val_tab_del)

if @series_OPDN != @series_OPOR

begin

set @error= 1

set @error_message=N'You must select series: '+ @series_OPOR

end

end

end

[Former Member]

Dear Andrey,

Now after putting your latest above SP its allowing me to put PO in any series but not auto checking in the GRPO the based document series which is selected wrong in GRPO.

I am adding a PO like say in Series Name RETAIL and adding it to the system. Now based on this PO I am doing a GRN and trying to add it with some diffrent series say TRADING then also its getting added to the system without any warning.

It should give me an error only if the based document series is not selected in the target document.

Hence this SP is not working as per the requirement.

Kindly help and advise.

regards,

kamlesh

[Former Member]

I found an error: T1.series=T1.series

But i dont know how it could affect that the document GRPO was added. Try again...

I check it. I have PO with series "First": and i try to add GRPO (based on PO with series "First") with new series "New1"... and a see alert message...

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

set @series_OPDN = isnull((select T1.Seriesname FROM OPDN T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')

declare @series_OPOR nvarchar (20)

set @series_OPOR =

isnull((SELECT distinct T2.seriesname

FROM opor T1

inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)

inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)

where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPDN != @series_OPOR

begin

set @error= 1

set @error_message=N'You must select series: '+ @series_OPOR

end

end

end

[Former Member]

Dear Andrey,

First of all let me say that YOU ARE SIMPLY GREAT. Now the matter is resolved and it giving an error to select the base document series onec I do the GRPO.

Now my friend can be please tell me that how can I use this SP for all marketing document. Means if I I do PO in one series and then make GRPO by this SP its doing the job. Now I want to use the same check for AP invoice so that it should check the base document series from AP is done from PO or from GRPO and AP to AP Credit memo check

Same check I require for for Sales Order / Deleivery / AR Invoice / AR Credit memo

Please tell me should I need to put seperate SP for each marketing document or in the existing I can change as per the requirement.

Thanks once again for the much awaited solution.

Regards,

kamlesh

[Former Member]

Dear All,

Can you please help me for this SP of how to make it applicable in all marketing document.

Regards,

Kamlesh

[Former Member]

Dear All,

I am using this Stored Procedure for Document Series Checkand its working frine for GRN to PO.

If @object_type = N'20'

begin

If (@transaction_type = N'U')or(@transaction_type = N'A')

begin

declare @series_OPDN nvarchar(20)

set @series_OPDN = isnull((select T1.Seriesname FROM OPDN T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')

declare @series_OPOR nvarchar (20)

set @series_OPOR =

isnull((SELECT distinct T2.seriesname

FROM opor T1

inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)

inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)

where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPDN != @series_OPOR

begin

set @error= 1

set @error_message=N'You must select series: '+ @series_OPOR

end

end

end

Now how can I use this SP for all marketing document. Means if I I do PO in one series and then make GRPO in another series by this SP its doing the job of not allowingf to add the document. Now I want to use the same check for AP invoice so that it should check the base document series from AP is done from PO or from GRPO and AP to AP Credit memo check

Same check I require for for Sales Order / Deleivery / AR Invoice / AR Credit memo

Please tell me should I need to put seperate SP for each marketing document or in the existing I can change as per the requirement.

Regards,

Kamlesh

[zal_parchem2]

Here are some other codes you might consider:

OPOR = Purchase Order = N'22'

OPDN = Goods Receipt PO = N'20'

ORPD = Goods Return = N'21'

OPCH = AP Invoice = N'18'

ORPC = AP Credit Memo = N'19'

OVPM = Outgoing Payment = N'46'

OQUT = Sales Quote = N'23'

ORDR = Sales Order = N'17'

ODLN = Delivery = N'15'

OINV = AR Invoice = N'13'

ORPC = AR Credit Memo = N'19'

OCRT = Incoming Payment = N'24'

[Former Member]

Dear Zal,

Please note that I know the marketing documents object number but while putting a SP for checking AP base document numbring series of GRN I am gettting a error while exeucting the SP as under :

Msg 134, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 204

The variable name '@series_OPDN' has already been declared. Variable names must be unique within a query batch or stored procedure.

I have the followings in my SP :

--GRN to PO series check

If @object_type = N'20'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPDN nvarchar(20)
set @series_OPDN = isnull((select T1.Seriesname FROM OPDN T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
declare @series_OPOR nvarchar (20)
set @series_OPOR =

isnull((SELECT distinct T2.seriesname
FROM opor T1
inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPDN != @series_OPOR
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPOR
end
end
end

-----AP TO GRN CHECK

If @object_type = N'18'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPCH nvarchar(20)
set @series_OPCH = isnull((select T1.Seriesname FROM OPCH T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
declare @series_OPDN nvarchar (20)
set @series_OPDN =

isnull((SELECT distinct T2.seriesname
FROM OPDN T1
inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPCH != @series_OPDN
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPDN
end
end
end

Please let me know is it possible to use one SP for all marketing document or do I need to make SP for each and individual marketing document to check the base document series.

I am not able to find the solution ifor a long time and it would be very nice if you can kindly help out.

regards,

kamlesh

[zal_parchem2]

--GRN to PO series check

FROM opor T1

inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)

inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)

where T0.DocEntry=@list_of_cols_val_tab_del),'')

-

---

AP TO GRN CHECK

FROM OPDN T1

inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)

inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)

where T0.DocEntry=@list_of_cols_val_tab_del),'')

Thanks for the info...maybe compare these two sections???

[zal_parchem2]

hello Kamlesh - we created a stored procedure for each and every document involved in the AR and AP modules of SAP B1....

Good luck - Zal

[Former Member]

Dear Zal

I could not understand what you intend to say. Please elaborate.

Regards,

Kamlesh

[zal_parchem2]

Kamlesh - sometimes I too overlook an item that others see, especially after working so long with a particular procedure like you have...cutting it down a bit more for you...

You say the GRN to PO works fine and have...

FROM OPOR T1 (Purchase Order Header)

INNER JOIN PDN1 T0 (Goods Receipt Detail) on ....

You say the AP to GRN is not working fine, but look...

FROM OPDN T1 (Goods Receipt Header)

INNER JOIN PDN1 T0 (Goods Receipt Detail) on ...

Should AP to GRN not say PCH1 (AP Invoice Detail) and not PDN1 (Goods Receipt Detail)???

Hope that works for you...Zal

[Former Member]

Dear Zal,

After recitifying the SP as per your guidence I am still getting the error which executing the Stored Procedure as under :

Msg 134, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 204

The variable name '@series_OPDN' has already been declared. Variable names must be unique within a query batch or stored procedure.

-----AP TO GRN CHECK
If @object_type = N'18'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPCH nvarchar(20)
set @series_OPCH = isnull((select T1.Seriesname FROM OPCH T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
declare @series_OPDN nvarchar (20)
set @series_OPDN =

isnull((SELECT distinct T2.seriesname
FROM OPDN T1
inner join PCH1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPCH != @series_OPDN
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPDN
end
end
end

I am extremly thankful that you are trying to guide me as best as possible but still I am not able to crack it. Would be thankful if you help on it.

Regards,

Kamlesh

[Former Member]

Hi All,

Can anyone please help me to solve the above problem. Awating for a response. Please reply.

Regards,

Kamlesh

[former_member204969]

Probably in the stored procedure the variable @series_OPDN is already declared in the code related to object type 20.

Then you should omit the line here containing the declaration.

If this does not solve your problem, you may list here the full procedure making possible to see us the error.

[Former Member]

Dear Istvan,

I have one SP as under :

--GRN to PO series name check

If @object_type = N'20'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPDN nvarchar(20)
set @series_OPDN = isnull((select T1.Seriesname FROM OPDN T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
declare @series_OPOR nvarchar (20)
set @series_OPOR =

isnull((SELECT distinct T2.seriesname
FROM opor T1
inner join pdn1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')

if @series_OPDN != @series_OPOR
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPOR
end
end
end

This SP is working fine.

Now when I put the below SP and trying to execute it its giving me an error of

Msg 134, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 205

The variable name '@series_OPDN' has already been declared. Variable names must be unique within a query batch or stored procedure.

-----AP TO GRN Series CHECK
If @object_type = N'18'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPCH nvarchar(20)
set @series_OPCH = isnull((select T1.Seriesname FROM OPCH T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
declare @series_OPDN nvarchar (20)
set @series_OPDN =
 
isnull((SELECT distinct T2.seriesname
FROM OPDN T1
inner join PCH1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')
 
if @series_OPCH != @series_OPDN
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPDN
end
end
end

I am really not able to understand what is the problem. What do you mean by omit the line here containing the declaration. Can you please help me out in this.

Regards,

Kamlesh

[former_member204969]

In the stored procedure there are together the two code segment you listed. In the first one the variable @series_OPDN is declared. When you insert the second code segment it will find a second declaration in it. So try the second segment without this declaration. (It can use the variable declared in the first segment.)

-----AP TO GRN Series CHECK
If @object_type = N'18'
begin
If (@transaction_type = N'U')or(@transaction_type = N'A')
begin
declare @series_OPCH nvarchar(20)
set @series_OPCH = isnull((select T1.Seriesname FROM OPCH T0 inner join NNM1 T1 on (T0.series=T1.series and T0.objtype=T1.objectcode)where T0.DocEntry=@list_of_cols_val_tab_del),'')
set @series_OPDN =
 
isnull((SELECT distinct T2.seriesname
FROM OPDN T1
inner join PCH1 T0 on (T0.basetype=T1.objtype and T0.baseentry=T1.docentry)
inner join NNM1 T2 on (T1.series=T2.series and T1.objtype=T2.objectcode)
where T0.DocEntry=@list_of_cols_val_tab_del),'')
 
if @series_OPCH != @series_OPDN
begin
set @error= 1
set @error_message=N'You must select series: '+ @series_OPDN
end
end
end

[Former Member]

Dear Istvan,

Yes after soo much of patience you cracked the issue. Thanks a lot for the help and instant solution.

I really appreciate it.

Thanks once again for it.

Regards,

[Former Member]

I would assume your retail series may have typo because 1000-9999 will overlap with others. They must be 1000-1999.

Are you sure that is good enough for you? Do you need one more digit?

As for the auto selection, although SP may be help, it has to update B1 table directly. I don't think that is a viable option.

Thanks,

Gordon

[Former Member]

Dear Gordon.

Sorry its a typing mistake. It's as under :

For Purchase Order

Retail 10000-19999

Wholesale 20000-29999

Manufacturing 30000-39999

Trading 40000-49999

For Good Reciept PO

Retail 10000-19999

Wholesale 20000-29999

Manufacturing 30000-39999

Trading 40000-49999

AP Invoice

Retail 10000-19999

Wholesale 20000-29999

Manufacturing 30000-39999

Trading 40000-49999

Please let me know how this problem can be addressed and resolved in SAP Business One.

regards,

Kamlesh

Edited by: Kamlesh Gupta on Mar 10, 2009 8:19 PM