Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Stored Procedure

Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

walterskw02

Posted Thursday, July 22, 2010 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 30, 2010 3:50 AM
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

Post #957020

Mike01

Posted Thursday, July 22, 2010 10:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 9:20 AM
Points: 1,191, Visits: 1,251

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/

Post #957341

walterskw02

Posted Friday, July 23, 2010 12:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 30, 2010 3:50 AM
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

Post #957790

« Prev Topic | Next Topic »

Permissions