Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, July 22, 2010 10:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:25 AM
Points: 1,221, Visits: 1,294
Could it be as simple as this?

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
Posted Friday, July 23, 2010 12:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse