Skip to Content
0

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

Feb 23 at 09:19 AM

85

avatar image
Former Member

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




-----
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

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

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Shankar,

Thanks but unfortunately it does not work. I don't know where is wrong with the code. You can even try yourself to run it on a test environment. You will see it does not respond. It will always provide the error even if the value for T1 IS NOT higher than T3 which is shouldn't be the case. It seems as if the code is getting its T0.DocEntry from a different invoice and not the invoice being added. I have exhausted all options now. I don't have any other way to tweak the code, I have done everything I know.

- Macdonald

0
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

0
Nagarajan K Feb 24 at 09:29 AM
0

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



Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Nagarajan,

Thanks. The logic seems obvious and fine to me as well but it simply does not work. I have tried changing the code multiple times but still the error executes even when the values from both tables match. This doesn't make any sense. Either I'm not joining the tables properly or I'm missing something.

I have run the code and output the results on a given invoice number and it responds without an issue. But putting the code in Transaction notification, the error executes when its not supposed.

Something is wrong somewhere but can't figure out where.

- Macdonald

0
Former Member
hi can you show the images of your process as well as the change you made in the transaction
0
Mathias Rikus Feb 28 at 02:21 PM
0

Hi,

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

Bests,

Mathias

Share
10 |10000 characters needed characters left characters exceeded