text: CREATE ALGORITHM=UNDEFINED DEFINER=`neon-user`@`104.47.140.143` SQL SECURITY DEFINER VIEW `vwAccountInvoicePayments` AS select `a`.`AccountName` AS `AccountName`,ifnull((select sum(coalesce(`i`.`GrandTotal`,0)) from `RMBilling3`.`tblInvoice` `i` where ((`i`.`InvoiceType` = 2) and (`a`.`AccountID` = `i`.`AccountID`) and ((coalesce(`i`.`InvoiceStatus`,'') = '') or (`i`.`InvoiceStatus` not in ('cancel','draft','awaiting'))))),0) AS `InvoicesIn`,ifnull((select sum(coalesce(`i`.`GrandTotal`,0)) from `RMBilling3`.`tblInvoice` `i` where ((`i`.`InvoiceType` = 1) and (`a`.`AccountID` = `i`.`AccountID`) and ((coalesce(`i`.`InvoiceStatus`,'') = '') or (`i`.`InvoiceStatus` not in ('cancel','draft','awaiting'))))),0) AS `InvoicesOut`,ifnull((select sum(coalesce(`p`.`Amount`,0)) from `RMBilling3`.`tblPayment` `p` where ((`p`.`PaymentType` = 'Payment In') and (`a`.`AccountID` = `p`.`AccountID`) and (`p`.`Recall` = 0) and (`p`.`Status` = 'Approved'))),0) AS `PaymentsIn`,ifnull((select sum(coalesce(`p`.`Amount`,0)) from `RMBilling3`.`tblPayment` `p` where ((`p`.`PaymentType` = 'Payment Out') and (`a`.`AccountID` = `p`.`AccountID`) and (`p`.`Recall` = 0) and (`p`.`Status` = 'Approved'))),0) AS `PaymentsOut`,(((ifnull((select sum(coalesce(`i`.`GrandTotal`,0)) from `RMBilling3`.`tblInvoice` `i` where ((`i`.`InvoiceType` = 1) and (`a`.`AccountID` = `i`.`AccountID`) and ((coalesce(`i`.`InvoiceStatus`,'') = '') or (`i`.`InvoiceStatus` not in ('cancel','draft','awaiting'))))),0) - ifnull((select sum(coalesce(`p`.`Amount`,0)) from `RMBilling3`.`tblPayment` `p` where ((`p`.`PaymentType` = 'Payment In') and (`a`.`AccountID` = `p`.`AccountID`) and (`p`.`Recall` = 0) and (`p`.`Status` = 'Approved'))),0)) - ifnull((select sum(coalesce(`i`.`GrandTotal`,0)) from `RMBilling3`.`tblInvoice` `i` where ((`i`.`InvoiceType` = 2) and (`a`.`AccountID` = `i`.`AccountID`) and ((coalesce(`i`.`InvoiceStatus`,'') = '') or (`i`.`InvoiceStatus` not in ('cancel','draft','awaiting'))))),0)) + ifnull((select sum(coalesce(`p`.`Amount`,0)) from `RMBilling3`.`tblPayment` `p` where ((`p`.`PaymentType` = 'Payment Out') and (`a`.`AccountID` = `p`.`AccountID`) and (`p`.`Recall` = 0) and (`p`.`Status` = 'Approved'))),0)) AS `Balance` from `RateManagement4`.`tblAccount` `a` where ((`a`.`Status` = 1) and (`a`.`AccountType` = 1))