July 22, 2010 at 3:16 am
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
July 22, 2010 at 10:30 am
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/
July 23, 2010 at 12:49 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply