incoming payment against a single order -Validation

[pradyuman_singh]

Hi ,

Validation / check required: We should not be able to add an incoming payment of more than 1,99,500 in CASH against one sales order - whether this has been taken across multiple days or multiple times in the same day. The sum total of incoming cash type incoming payment against a single order must not exceed 1,99,500.

I tried to right this query but could not get the expected result 

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

    IF @object_type = N'24' AND ( @transaction_type  IN ('A','U'))                                                              

BEGIN                                                                              

       if exists (SELECT t3.DocEntry   

        From ORCT t3

Inner Join RCT2 on RCT2.DocNum = t3.DocEntry

inner join ODPI T4 on RCT2.DocEntry = T4.DocEntry and T4.ObjType = RCT2.InvType

inner join DPI1 t5 on t5.DocEntry=t4.DocEntry

inner join RDR1 t7 on t7.DocEntry=t5.BaseEntry and t7.ObjType=t5.BaseType-- and t7.LineNum=t5.BaseLine

inner join ORDR t8 on t8.DocEntry=t7.DocEntry

   where t3.DocEntry =@list_of_cols_val_tab_del  and t3.CashSum>=199500  )                                    

      BEGIN                                                            

    SELECT @Error = 122                                      

     SELECT @error_message = 'cash cant receive more then 199500'                                                            

      END                                                              

END  

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