Stored Procedure

  • 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

  • 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/

  • 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