Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure


Stored Procedure

Author
Message
walterskw02
walterskw02
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Hi

I am busy working on a Project where the ERP system is SAP Business One, it runs off SQL Server 2005, there is a requirement for a Stored Procedure to do the following (these are based off SAP fields but are in the SQL Server tables and fields):

1) Project field on Line level = if field left as NULL SAP prompts you to select one (via the SP)
2) Project field on Line Level = if a blocked Project is selected SAP prompts you to select another (via the SP)
3) Project field on Header Level = if field left as NULL SAP prompts you to select one (via the SP)
4) Project field on Header Level = if a blocked Project is selected SAP prompts you to select another (via the SP)
5) NumAtCard field on Header Level = if Field left as NULL SAP prompts you to define a BP Ref. No. (via the SP)
6) I have Intercompany Loan Accounts 125100 - 125950 and a Project 105000 = Intercompany, I am needing a Stored Procedure that will block any other GL Acounts from using the Intercompany Project Code '105000' except for when I select the Intecompany Loan accounts.

I have written a Stored Procedure for a AR Invoice (Object Type 13) on points 1) to 5) (please see below code) but I am also needing code to do point 6), with there being so many steps to the Stored Procedure and as I only know a bit, is it possible to merge all, I have issues with the code when running it:

- it will go through each step 1) to 5) but will keep stuck on Point 4) even though the correct Project is selected.

Code:

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 – 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

Is my question possible in the SP of SQL Server 2005, I despreately need assitance and help.

Yours Sincerely

Kurt Walters
Mike01
Mike01
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1273 Visits: 1522
Could it be as simple as this? Unsure


if Project = 105000 and Intercompany Loan Accounts not between 125100 - 125950
begin
SET @Error = 15
SET @error_message = 'AR Invoice Lines - Can Only use this GL Accounts with Intercompany Loan Accounts'
end



For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
walterskw02
walterskw02
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Hi Mike

Thanks for the reply I will try this and add it to my SQL Query as I will need to query the SQL Table and field.

Thanks very much for the help

Regards

Kurt Walters
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search