Blovking Sales Order

[former_member522789]

Hello

I would like to have some help

The issue is about approval procedure

My customer would like to block sales order when the total invoice fror a project is reached.

I have created a user defined table to list all project.... the table is @PRO

The limit is a user defined field called ZLIMITE

I have created a user defined field on marketing document where the user must select the project assigned to a sales order

The field is ZPROJET

Then I have the qyerry to be used through approuval procedure which calculate the sum of all invoices already made for the same project and the same customer

The query is not working could anyone tell me why

SELECT DISTINCT TRUE IF SUM(T0.DocTotal-T0.VATsum)>[@PRO].[U_ZLIMITE] FROM OINV T0 where T0.CardCode=$[$4.1.0] and [@PRO].[Code]=$[$U_ZPROJET.1.0]

[Former Member · Accepted solution]

There are multiple problems for your query. It will be very difficult for us to test. You can try my amendment to see if you have any error messages due to no test:

SELECT DISTINCT TRUE IF $[$4.0.0\] = T0.CardCode and SUM(T0.DocTotal-T0.VATsum)>@PRO.U_ZLIMITE FROM OINV T0 where $[ORDR.U_ZPROJET\]=@PRO.Code

Thanks,

Gordon

[former_member522789 · Accepted solution]

OK I think the FROM OINV T0 is not correct

I have an error mesage on that part

Edited by: Rui Pereira on Apr 28, 2009 1:35 PM

[Former Member · Accepted solution]

It is long time for this thread. If you still need solution please try this one:

SELECT DISTINCT 'TRUE' FROM OINV T0 where $[ORDR.U_ZPROJET\]=@PRO.Code T0.CardCode = $[$4.0.0\]

GROUP BY T0.CardCode Having SUM(T0.DocTotal-T0.VATsum)>@PRO.U_ZLIMITE