Skip to Content

Stored Procedure for AP Invoice (Service) Based on PO

Dear All,

I have below Store Procedure for Blocking Purchase Invoice not based on Purchase Order.

IF @transaction_type IN ('A','U') AND @object_type = '18'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocNum FROM OPCH T0 INNER JOIN PCH1 T1 on T1.DocEntry=T0.DocEntry WHERE T1.[BaseType] <> '22'

AND T0.DOCENTRY = @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 15, @error_message = 'Cannot Add Document without Purchase Order'

END

End

I would like to add additional criteria for blocking if the AP Invoice Amount Exceed the Open Amount in the PO.

Kind Regards

Raju Parmar

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    May 29, 2015 at 06:43 AM

    Hi

    Please try this:

    IF @transaction_type IN ('A','U') AND @Object_type = '18'

    Begin

    IF EXISTS(SELECT T1.AcctCode AS 'AcctCode' FROM [dbo].[POR1] T0 , [dbo].[PCH1] T1, dbo.[OPCH] T2 , dbo.[OUSR] T3,

    dbo.[Ocrd] T4, [dbo].[OPOR] T5  WHERE T5.DocEntry = T0.DocEntry AND  T1.DocEntry = T2.DocEntry and T2.cardcode = T4.cardcode

    AND T2.UserSign2 = T3.Internal_K AND T1.BaseEntry = T0.DocEntry AND T1.BaseLine = T0.LineNum AND  (T1.BaseType <> '22'

    or T2.DocTotal > T5.DocTotal ) AND T1.DocEntry = @list_of_cols_val_tab_del )

    BEGIN

    SELECT @Error = 1, @error_message = 'Cannot Add Document without Purchase Order'

    END

    END

    Regards,

    Isaac.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 29, 2015 at 10:29 AM

    Dear Rajindra Parmar,

    Please try this

    ----------------------------------For Validation of A/P Invoice Base Documnent--------------------------------------------------------------

    IF @object_type='18' and (@transaction_type='U' or @transaction_type='A')

    BEGIN

        Declare @Itemcode18 as varchar(255)

        Declare @DocType18 as varchar(5)

      Declare @minline18 int

      Declare @maxline18 int

      Declare @Base18 nvarchar(255)

      SET @minline18 = (select min(T0.linenum) from PCH1 T0 where T0.docentry=@list_of_cols_val_tab_del)

      SET @maxline18 = (select max(T0.linenum) from PCH1 T0 where T0.docentry=@list_of_cols_val_tab_del)

      SET @DocType18 = (select DocType from OPCH T0 where T0.docentry=@list_of_cols_val_tab_del)

    while @minline18 <= @maxline18

    BEGIN

        select @Itemcode18 =ItemCode from PCH1 where docentry=@list_of_cols_val_tab_del And LineNum=@minline18

      select @Base18 =BaseType from PCH1 where docentry=@list_of_cols_val_tab_del And LineNum=@minline18

        IF  (@Base18 = '-1') And @DocType18 = 'S'

      BEGIN

      SET @error='-1'

      SET @error_message='Please Generate Base Document First For Item ' + @Itemcode18

      END

    SET @minline18 = @minline18 + 1

    END

    END

    -----------------------------------------------------------------------------------------------------

    Regards,

    Chetan Vora

    Add comment
    10|10000 characters needed characters exceeded