If a customer confirms that an orphaned transaction was approved, but Sedona did not update after 7 days, use the script below to create a payment and update orphaned transactions and invoices.
NOTE: This should no longer be necessary after update 6.0.17
begin tran
insert into AR_Deposit_Check(transaction_date,deposit_batch_id,payment_method_id,type_cveo,customer_id, vendor_id, employee_id, other, check_number, check_date, amount, memo, register_id, branch_id, Other_Branch_Amt, has_image, Ach_id)
select distinct Submit_Date, 1, Payment_Method_Id, 'C', i.Customer_Id, 1, 1, '', 'ACH Processing', Submit_Date, a.Amount, Memo, 1, Branch_id, 0.00,'',a.ACH_Id
from AR_ACH a
join ar_invoice i on a.Invoice_Id = i.Invoice_Id and a.Amount = i.Net_Due
where Trans_Status like '%orphan%' and a.Invoice_Id <> 1 and a.Deposit_Check_Id = 1
insert into AR_Deposit_Check_Detail (Deposit_Batch_Id, Deposit_Check_Id, Type_UAIM, Unapplied_Cash_Id, Advance_Deposit_Id, Invoice_Id, Misc_Inc_Account_Id, Amount, Register_Id, Debit_Register_Id, Branch_Id, PrePay_Register_Id, Category_Id)
select distinct 1, c.Deposit_Check_Id, 'I', 1, 1, i.Invoice_Id, 1, a.Amount, 1, 1, i.Branch_id, 1, a.Category_Id
from AR_Deposit_Check c
join AR_ACH a on c.ACH_Id = a.ACH_Id
join AR_Invoice I on I.Invoice_Id = A.Invoice_Id and A.Amount = I.Net_Due
where Trans_Status like '%orphan%' and a.Invoice_Id <> 1 and a.Deposit_Check_Id = 1
update A
set Deposit_Check_Id = C.Deposit_Check_Id,
Trans_status = 'APPROVED',
Trans_Message = 'APPROVED',
SettledDate = GETUTCDATE()
--select A.*
from AR_Deposit_Check c
join AR_ACH a on c.ACH_Id = a.ACH_Id
join AR_Invoice I on I.Invoice_Id = A.Invoice_Id and A.Amount = I.Net_Due
where Trans_Status like '%orphan%' and a.Invoice_Id <> 1 and a.Deposit_Check_Id = 1
update I
set Net_Due = 0,
Payment = Payment + a.Amount,
Has_Pending_EFT = 'N'
--select I.*
from AR_ACH a
join ar_invoice i on a.Invoice_Id = i.Invoice_Id and a.Amount = i.Net_Due
where Trans_Status like '%approv%' and a.Invoice_Id <> 1 --and a.Deposit_Check_Id = 1
Commit
