Skip to Content
avatar image
Former Member

Transaction Notification to verify Invoice value not more than Purchase Order value

Hi!

I have been working on a Transaction Notification to be able to block posting of invoice if value is more than the base document value. Even when posting by line items, the invoice value should not exceed the base document balance (difference between DocTotal and PaidToDate). This is for the purpose of controlling the financials as at the moment our approval is at the PO level. I have the following code I have wrote but works to some extent but it seems its missing something. It works for some invoices but does not work especially when posting invoices from PO line items. Even when invoice and PO value or balance matches, the procedure will still execute and give the error and prevent posting.

Please assist with this issue. I might be missing something. If any question, please ask. Thanks in advance.

- Macdonald

-----Block posting of A/P Invoice if doc value greater than base document value and or balance (Difference between DocTotal and PaidToDate)




IF @transaction_type IN ('A') AND @object_type = '18'
BEGIN
IF EXISTS (SELECT T0.DocEntry 
FROM OPCH T0 
INNER JOIN PCH1 T1 ON T1.DocEntry=T0.DocEntry
INNER JOIN OPOR T2 ON T2.DocEntry = T1.BaseEntry
GROUP BY T0.DocEntry, T2.DocTotal, T2.DocTotal, T2.PaidToDate 
HAVING ((SUM(T1.LineTotal)) > (T2.DocTotal - T2.PaidToDate)) AND T0.DocEntry = @list_of_cols_val_tab_del)
BEGIN
SELECT @Error = 18, @error_message = 'Invoice value greater than base document value and or balance'
END
END




-----
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Feb 23 at 11:51 AM

    Hi Macdonald,

    Try this query, hope it works.

    IF @transaction_type IN ('A') AND @object_type = '18' BEGIN IF EXISTS (SELECT T0.DocEntry FROM OPCH T0 INNER JOIN PCH1 T1 ON T1.DocEntry=T0.DocEntry INNER JOIN OPOR T2 ON T2.DocEntry = T1.BaseEntry INNER JOIN POR1 T3 ON T3.DocEntry = T2.DocEntry WHERE (T1.LineTotal) > (T3.LineTotal) OR (T1.Price) > (T3.Price) AND T0.DocEntry = @list_of_cols_val_tab_del) BEGIN SELECT @Error = 18, @error_message = 'Invoice value greater than base document value and or balance' END END

    With Krgds

    Shiva

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Macdonald,

      can you give me the steps involved in the testing. I'll tweak the code and send it again.

      With Krgds

      Shiva

  • Feb 24 at 09:29 AM

    Hi,

    Try this,

    IF@transaction_type IN('A')AND@object_type ='18'BEGINIFEXISTS(SELECT T0.DocEntry 
    FROM OPCH T0 
    INNERJOIN PCH1 T1 ON T1.DocEntry=T0.DocEntry
    INNERJOIN OPOR T2 ON T2.DocEntry = T1.BaseEntry
    
    Where T1.[BaseType] = 22
    GROUPBY T0.DocEntry, T2.DocTotal, T2.DocTotal, T2.PaidToDate 
    HAVING((SUM(T1.LineTotal))>(T2.DocTotal - T2.PaidToDate))AND T0.DocEntry =@list_of_cols_val_tab_del)
    
    BEGINSELECT@Error =18,
    @error_message ='Invoice value greater than base document value and or balance'
    END
    END
    
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 28 at 02:21 PM

    Hi,

    the condition 'T0.DocEntry = @list_of_cols_val_tab_del' should always be part of where-clause, not having.

    Bests,

    Mathias

    Add comment
    10|10000 characters needed characters exceeded