Error settlement of Payments after removing AMC banking ISV solution.

 Our client uses AMC banking ISV solution for past 3 year and now they thought to quite and use existing features in out of the box with few customizations in case of out going payments as well as incoming payments. 

For outgoing payments we have "MultiCash (PL)" but its text file but client required in "ISO 20022 credit transfer", hence we have derived standard format and built new. 

For incoming payments we have used  "ISO20022 camt.054" and for advance bank reconciliation we have used "ISO20022 camt.053" with few minor changes in format and model mapping.

Everything was fine but finally we had an issue with settlement of customer or vendor payments. all the open transactions marked as "In use", hence we are not able to settle any transactions.

and we don't see any pending customer / vendor payment journals in the system. 

As everyone aware we need to remove reference in SpecTrans table hence I have built following script, may be you can find it useful if you face similar issues.

Once we ran this script then we are able to settle all the transactions which are struck in use.

public void clicked()

{

            SpecTrans       specTrans;

            CustTransOpen   custTransOpen;

            VendTransOpen   vendTransOpen;

            CustTrans       custTrans;

            VendTrans       vendTrans;

            container       conCustInvoice, conVendInvoice;

            int             totalCustInvoices, totalVendInvoices;

            if(Box::yesNo('@JP:DeleteAllSpecTransForPoland', DialogButton::Yes) == DialogButton::Yes)

            {

                changecompany('<Legal Entity>')

                {

                    ttsbegin;

                    while select specTrans join custTransOpen

                        where CustTransOpen.DataAreaId == specTrans.RefCompany

                        && CustTransOpen.RecId == specTrans.RefRecId

                        && CustTransOpen.TableId == specTrans.RefTableId

                        join custTrans

                        where custTrans.RecId == custTransOpen.RefRecId

                        && custTrans.AccountNum == custTrans.AccountNum

                    {

                        if (!conFind(conCustInvoice, custTrans.Invoice))

                        {

                            conCustInvoice += custTrans.Invoice;

                            

                        }

                        totalCustInvoices++;

                    }

                    info(con2Str(conCustInvoice));

                    info(strFmt('@JP:TotalSpecTransRecordsDeletedForCustomer', totalCustInvoices));

                    specTrans.skipDeleteActions(true);

                    specTrans.skipDatabaseLog(true);

                    specTrans.skipEvents(true);

                    specTrans.skipDataMethods(true);

                    delete_from specTrans exists join custTransOpen

                       where CustTransOpen.DataAreaId == specTrans.RefCompany

                        && CustTransOpen.RecId == specTrans.RefRecId

                        && CustTransOpen.TableId == specTrans.RefTableId;

                    while select specTrans join vendTransOpen

                        where vendTransOpen.DataAreaId == specTrans.RefCompany

                        && vendTransOpen.RecId == specTrans.RefRecId

                        && vendTransOpen.TableId == specTrans.RefTableId

                        join VendTrans

                        where VendTrans.RecId == vendTransOpen.RefRecId

                        && VendTrans.AccountNum == vendTransOpen.AccountNum

                    {

                        if (!conFind(conVendInvoice, vendTrans.Invoice))

                        {

                            conVendInvoice += vendTrans.Invoice;

                            

                        }

                        totalVendInvoices++;

                    }

                    specTrans.skipDeleteActions(true);

                    specTrans.skipDatabaseLog(true);

                    specTrans.skipEvents(true);

                    specTrans.skipDataMethods(true);

                    delete_from specTrans exists join vendTransOpen

                        where vendTransOpen.DataAreaId == specTrans.RefCompany

                        && vendTransOpen.RecId == specTrans.RefRecId

                        && vendTransOpen.TableId == specTrans.RefTableId;

                    info(con2Str(conVendInvoice));

                    info(strFmt('@JP:TotalSpecTransDeletedForVendor', totalVendInvoices));

                    info("@JP:OperationCompleted");

                    ttscommit;

                }

            }

            else

                info("@JP:OperationCancelled");

}