How to transfer a positive balance into a prepaid credit

Here is explained a workaround how to transfer a positive balance into a prepaid credit, E.G. in case an overpayment didn't transfer to prepaid credit

Please note this can be done if prepaid credit is already configured (http://support.atomia.com/solution/articles/4000072895-how-to-configure-prepaid-credit) and you are dealing with the latest Atomia release


1. Making partial refund for overpaid amount on invoice

Please note that it is not possible to make partial refund from admin panel, so we need a workaround;

Execute the following query:

select p.invoice_reference_number, p.created_time, p.discriminator, p.amount, i.total, p.amount - i.total overpayment,

'INSERT INTO [AtomiaBilling].[dbo].[payment] ([id],[fk_payment_method_id],[fk_currency_id],[fk_invoice_id],[new_invoice_id],[fk_created_by_account_id],[invoice_reference_number],[description],[document_id],[created_time],[last_change_time],[status],[batch_id],[payment_transaction_id],[amount],[fk_reseller_id],[discriminator],[fk_original_payment_id],[payment_id]) VALUES (NEWID(),NULL,''' + convert(nvarchar(36), p.fk_currency_id) + ''',''' + convert(nvarchar(36), p.fk_invoice_id) + ''',null,''' + convert(nvarchar(36), p.fk_created_by_account_id) + ''',''' + p.invoice_reference_number + ''',null,null,getdate(),getdate(),1,null,null,' + convert(nvarchar(36), p.amount - i.total) + ',''' + convert(nvarchar(36), p.fk_reseller_id) + ''',''Refund'',''' + convert(nvarchar(36), p.id) + ''',null)' refund

from payment p

left join invoice i on p.fk_invoice_id = i.id

left join payment_method m on m.id = p.fk_payment_method_id where

i.reference_number in ('xxxxxx')

order by created_time

Note: put appropriate reference number instead of xxxxxx

This query will return the another query needs to be run in order to make a refund:

something like:

INSERT INTO [AtomiaBilling].[dbo].[payment] ([id],[fk_payment_method_id],[fk_currency_id],[fk_invoice_id],[new_invoice_id],[fk_created_by_account_id],[invoice_reference_number],[description],[document_id],[created_time],[last_change_time],[status],[batch_id],[payment_transaction_id],[amount],[fk_reseller_id],[discriminator],[fk_original_payment_id],[payment_id]) VALUES
...

2. Making an order for prepaid credit for overpaid amount

In Admin panel go to the Orders tab, chose existing customer and order a prepaid product with the quantity of refunded amount from the previous chapter. 
For the next example, the ordered prepaid credit is 1NOK





3. Making a payment for prepaid credit

In Admin panel go to the Payment tab and make a payment for the invoice from the previous chapter.