Stored Procedure Help

[former_member282786]

Hi

I have created Stored Procedures that looks at Projects and BP Ref. No.; (1) will be if the Project field on Line level has been left as Null the SP prompts you to select a Project on line level, (2) will be if the Project field on Header Level has been left as Null the SP prompts you to select a Project on Header level, (3) we have certain projects that need blocking and others need to be selected, the SP prompts you to select another project than the one selected, (4) the final SP checks to see if the BP Ref. No field has been left as NULL, if so the SP prompts you to fill this in.

Now I have an issue with consistency on when this SP is run meaning (I have 15 databases) , it runs the SP for JE & AR on one database but then only JE on the other, I am not to sure what may be the issue, I have taken the Blocked Project SP and put it into the Select Project SP's it seems to work but I would like to seperate them so the user gets an understanding of what went wrong as the seperate Stored Procedures work fine on my Local Laptop with SBO on it, the issue is on the server, SP below:

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Cust Inv. No.

IF EXISTS (SELECT T0.DocEntry FROM [dbo].[OINV] T0 WHERE T0.NumAtCard ='0' OR T0.NumAtCard IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please define the Customer Invoice Number within Cust. Inv. No. Field'

END

END

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Header

IF EXISTS (SELECT T0.DocEntry FROM [dbo].[OINV] T0 WHERE T0.Project ='0' OR T0.Project IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please select Project Code under Accounting Tab > BP Project'

END

END

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Lines

IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='' OR T0.Project IS NULL) AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 10

SET @error_message = 'AR Invoice - Please set Project Code on every line !'

End

END

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Header

IF EXISTS (SELECT T0.DocEntry FROM [dbo].[OINV] T0 WHERE T0.Project ='100000' OR T0.Project ='100001' OR T0.Project ='102001' OR T0.Project ='104000' AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice Header - This Project is blocked please select the correct one'

END

END

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Lines

IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='100000' OR T0.Project ='100001' OR T0.Project ='102001' OR T0.Project ='104000') AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 10

SET @error_message = 'AR Invoice Lines - This Project is blocked please select the correct one'

End

END

The above is for AR Invoices, I also have for 14 - AR Credit Notes, 18 AP Invoices, 19 - AP Credit Notes and 30 - Manual Journal Entries.

Let me know where I may be going wrong.

Yours Sincerely

Kurt Walters

[Former Member · Accepted solution]

My suggestion:

Make one SP includes all conditions without ELSE. i.e. there will only have one general error message. The error message may not be that important than the ultimate goal. If you have time later, you may refine the SP.

[former_member282786 · Accepted solution]

Hi Gordon

I did consider that but the users were not happy when they received x1 error message, they laid it out that they wanted to see an error message everytime they did something wrong and where there error was coming from.

I think I may have come to resolve my issue, I noticed that I didn't have enough brackets in my SQL statement for the Header Levels query, as there wasn't an issue with the line level with multiple Projects being blocked I noticed that there was a bracket before Select and after Where:

IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='' OR T0.Project IS NULL) AND T0.DocEntry = @list_of_cols_val_tab_del)

I have applied this to the Header Level query and so far it went through without any hitches agaisnt the AR Invoice (Object Type 13), I will do further testing to see if this might of been a hit of luck but will carry this out against all other Object Types.

Just want to say thanks for your help and giving me ideas on making sure the SP works, I will let you know of my results after testing.

Regards

Kurt Walters

[former_member282786 · Accepted solution]

my testing proved correct, multiple projects need there own brackets; thanks for the help Gordon.

Regards

Kurt Walters

[Former Member]

Try this merge first:

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Cust Inv. No.

IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.NumAtCard ='0' OR T0.NumAtCard IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please define the Customer Invoice Number within Cust. Inv. No. Field'

END

--AR Invoice Header

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.Project ='0' OR T0.Project IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please select Project Code under Accounting Tab > BP Project'

END

END

[former_member282786]

Hi Gordon

Thanks for the reply, ok I see what you are saying, so basically to merge into one I should merge the OINV SP into one and merge INV1 SP into one, therefore it will look at the SP on Header Level then look at the SP on Line Level.

I will try this tonight.

Thanks very much four your help, if I have any issues I will reply to you.

Regards

Kurt Walters

[former_member282786]

Hi Gordon

The merging helped to a certain extent, I merged the OINV as one then INV1 as one but this stopped on the AR Header - Project is Blocked section and kept on this even though the correct and unblocked Poroject was selected, then I merged OINV and INV1 under AR Invoice SP (13) but the same result, the x2 SP listed below:

1st Merge:

OINV

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Cust Inv. No.

IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.NumAtCard ='0' OR T0.NumAtCard IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please define the Customer Invoice Number within Cust. Inv. No. Field'

END

--AR Invoice Header u2013 Mandatory Project

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.Project ='0' OR T0.Project IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 11

SET @error_message = 'AR Invoice - Please select Project Code under Accounting Tab > BP Project'

END

--AR Invoice Header - Blocked Projects

ELSE IF EXISTS (SELECT T0.DocEntry FROM [dbo].[OINV] T0 WHERE T0.Project ='180002' OR T0.Project ='180006' OR T0.Project ='180008' OR T0.Project ='181000' OR T0.Project ='183000' AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 12

SET @error_message = 'AR Invoice Header - This Project is blocked please select the correct one'

END

END

INV1

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Lines - Mandatory Project

IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='' OR T0.Project IS NULL) AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 10

SET @error_message = 'AR Invoice - Please set Project Code on every line !'

End

--AR Invoice Lines - Blocked Project

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='180002' OR T0.Project ='180006' OR T0.Project ='180008' OR T0.Project ='181000' OR T0.Project

='183000') AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 10

SET @error_message = 'AR Invoice Lines - This Project is blocked please select the correct one'

End

END

2nd Merge:

OINV & INV1 Merge

IF @transaction_type='A' AND @Object_type = '13'

BEGIN

--AR Invoice Cust Inv. No.

IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.NumAtCard ='0' OR T0.NumAtCard IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 10

SET @error_message = 'AR Invoice - Please define the Customer Invoice Number within Cust. Inv. No. Field'

END

--AR Invoice Header u2013 Mandatory Project

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.OINV T0 WHERE T0.Project ='0' OR T0.Project IS NULL AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 11

SET @error_message = 'AR Invoice - Please select Project Code under Accounting Tab > BP Project'

END

--AR Invoice Header - Blocked Projects

ELSE IF EXISTS (SELECT T0.DocEntry FROM [dbo].[OINV] T0 WHERE T0.Project ='180002' OR T0.Project ='180006' OR T0.Project ='180008' OR T0.Project ='181000' OR T0.Project ='183000' AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SET @Error = 12

SET @error_message = 'AR Invoice Header - This Project is blocked please select the correct one'

END

--AR Invoice Lines - Mandatory Project

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='' OR T0.Project IS NULL) AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 13

SET @error_message = 'AR Invoice - Please set Project Code on every line !'

End

--AR Invoice Lines - Blocked Project

ELSE IF EXISTS (SELECT T0.DocEntry FROM dbo.INV1 T0

WHERE (T0.Project ='180002' OR T0.Project ='180006' OR T0.Project ='180008' OR T0.Project ='181000' OR T0.Project

='183000') AND T0.DocEntry = @list_of_cols_val_tab_del)

Begin

SET @Error = 14

SET @error_message = 'AR Invoice Lines - This Project is blocked please select the correct one'

End

END

Any ideas on how I can get this to work? under pressure to get it working

Regards

Kurt Walters

[Former Member]

Hi Kurt Walters,

You need to put all 5 SPs into one big SP in order to solve your problem.

Thanks,

Gordon

[former_member282786]

Hi Gordon

I have all 5 SP's under SBO_SP_TransactionNotification, how would I go about putting all 5 SP's into one SP. As per above query written for Stored Procedure this is all I know basically the basics to have a mandatory field pop-out in SBO.

Are you able to help / assist me on getting the Stored Procedure from x5 into x 1 so when a user doesn't select a Project it prompts him to, when he selects one that is blocked it tells him to select another and so on (as per below listing):

- Project field on Line level = if left as NULL sbo prompts you to select one (via the SP)

- Project field on Line Level = if a blocked Project is selected SBO prompts you to select another (via the SP)

- Project field on Header Level = if left as NULL sbo prompts you to select one (via the SP)

- Project field on Header Level = if a blocked Project is selected SBO prompts you to select another (via the SP)

- NumAtCard field on Header Level = if left as NULL sbo prompts you to define a BP Ref. No. (via the SP)

Thanks very much for your help as I am under pressure on getting this resolved.

Regards

Kurt Walters

Edited by: Kurt Walters on Jul 20, 2010 7:22 AM