I need help in writing update statement which is doing payments against invoice, each invoice has lineitems in it.in this example i have invoice total $1200, and it has 4 line items in it.each line item is $300.Now making payment only $1100 dollars: i should make payment like $300 ,$300, $300, $200 for the last one i am left with $200 only.Declare @PaymentCursor CURSOR FOR Select Pay_id, pay_lineItem, Inv_lineitem_Amt from tab_payments where inv_id=@invidi got these 4 rows in cursor PaymentCursor they are:payid pay_lineItem Inv_lineitem_Amt----------------------------------------------11 pml124 30012 pml125 30013 pml126 30014 pml127 300@inv_Total = $1,200Now, i am making payment of only $1100, one hundred dollars short of the @inv_Total which is $1200OPEN @PaymentCursorFETCH FIRST FROM PaymentCursor INTO @PayID,@Inv_Lineitem_Amt since i have $1100 i should make payments for 4 Inv_lineitems_Amts like this: 300,300,300 200 for the last one i only have $200 left. for each line item i must check what is the @inv_lineItemAmt and how much do i have left from $1100 dollars payment towards invoice. Update tab_payments set Pay_LineItemAmt=300 where payid=@PayID Update tab_payments set Pay_LineItemAmt=300 where payid=@PayID Update tab_payments set Pay_LineItemAmt=300 where payid=@PayID Update tab_payments set Pay_LineItemAmt=200 where payid=@PayID FETCH NEXT FROM PaymentCursor INTO @PayID,@Inv_Lineitem_AmtENDCLOSE PaymentCursorDEALLOCATE PaymentCursor
Thank you very much for the helpful info.
Update statement for invoices and payments
Posted - 2010-05-14 : 22:00:32
