Stored Procedure for AP Invoice (Service) Based on PO

[RajuParmar]

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

[Former Member]

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

[isaac_kalii]

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.