Skip to main content
Skip table of contents

Financials - Missing AP Payment in Trial Balance/Financial Inquiry

🤔 Problem

The client had a voucher displayed as 'Paid' in the AP Payment screen, but it did not appear in the Financial Inquiry screen.

🌱 Solution

There is a SQL script and a Rails command to reinsert the missing data.

  1. First, run the Select statements below to verify if results are present. If the script generates results, you can proceed with the Rails command.

    .

SQL Select Statement

Select Statement to Verify Results
CODE
-- AP Invoices Missing GLT
SELECT DISTINCT voucher_number
INTO #glt_vouchers_ap
FROM gl_transactions
WHERE object_type = 'ApInvoice';
 
SELECT DISTINCT api.accounting_date
    , api.voucher_number
    , api.STATUS
    , api.[source]
    , api.posted_date
    , count(gld.id) AS gld_count
    , sum(gld.amount) AS amount
FROM ap_invoices api
JOIN gl_distributions gld
    ON gld.object_id = api.id
        AND gld.object_type = 'ApInvoice'
LEFT JOIN #glt_vouchers_ap gv
    ON gv.voucher_number = api.voucher_number
WHERE gv.voucher_number IS NULL
    AND api.STATUS IN ('Open', 'Paid', 'Voided')
GROUP BY api.accounting_date
    , api.voucher_number
    , api.STATUS
    , api.posted_date
    , api.[source];
 
DROP TABLE #glt_vouchers_ap;
 
-- Journal Entries Missing GLT
SELECT DISTINCT voucher_number
INTO #glt_vouchers_je
FROM gl_transactions
WHERE object_type = 'JournalEntry';
 
SELECT DISTINCT je.accounting_date
    , je.voucher_number
    , je.STATUS
    , je.[source]
    , je.posted_date
    , count(gld.id) AS gld_count
    , sum(gld.amount) AS amount
FROM journal_entries je
JOIN gl_distributions gld
    ON gld.object_id = je.id
        AND gld.object_type = 'JournalEntry'
LEFT JOIN #glt_vouchers_je gv
    ON gv.voucher_number = je.voucher_number
WHERE gv.voucher_number IS NULL
    AND je.STATUS IN ('Posted')
GROUP BY je.accounting_date
    , je.voucher_number
    , je.STATUS
    , je.posted_date
    , je.[source];
 
DROP TABLE #glt_vouchers_je;
 
-- AR Invoices Missing GLT
SELECT DISTINCT voucher_number
INTO #glt_vouchers_ar
FROM gl_transactions
WHERE object_type = 'ArInvoice';
 
SELECT DISTINCT ari.accounting_date
    , ari.voucher_number
    , ari.STATUS
    , ari.[source]
    , ari.posted_date
    , count(gld.id) AS gld_count
    , sum(gld.amount) AS amount
FROM ar_invoices ari
JOIN gl_distributions gld
    ON gld.object_id = ari.id
        AND gld.object_type = 'ArInvoice'
LEFT JOIN #glt_vouchers_ar gv
    ON gv.voucher_number = ari.voucher_number
WHERE gv.voucher_number IS NULL
    AND ari.STATUS IN ('Open', 'Paid', 'Voided')
GROUP BY ari.accounting_date
    , ari.voucher_number
    , ari.STATUS
    , ari.posted_date
    , ari.[source];
 
DROP TABLE #glt_vouchers_ar;
 
-- AP Payments Missing GLT
SELECT DISTINCT app.accounting_date
    , app.voucher_number
    , app.STATUS
    , app.[source]
    , app.posted_date
    , count(api.id) AS invoice_count
    , app.payment_amount AS amount
FROM ap_payments app
JOIN ap_payment_invoices api
    ON api.ap_payment_id = app.id
LEFT JOIN gl_transactions glt
    ON glt.object_type = 'ApPayment'
        AND glt.voucher_number = app.voucher_number
WHERE glt.id IS NULL
    AND app.STATUS IN ('Paid', 'Voided')
GROUP BY app.accounting_date
    , app.voucher_number
    , app.STATUS
    , app.[source]
    , app.posted_date
    , app.payment_amount;
 
-- AR Receipts Missing GLT
SELECT DISTINCT arr.accounting_date
    , arr.voucher_number
    , arr.STATUS
    , arr.[source]
    , arr.posted_date
    , count(glt.id) AS glt_count
    , arr.payment_amount AS amount
FROM ar_receipts arr
LEFT JOIN gl_transactions glt
    ON glt.object_type = 'ArReceipt'
        AND glt.voucher_number = arr.voucher_number
WHERE glt.id IS NULL
    AND arr.STATUS IN ('Applied', 'Open', 'Voided', 'Posted')
GROUP BY arr.accounting_date
    , arr.voucher_number
    , arr.STATUS
    , arr.[source]
    , arr.posted_date
    , arr.payment_amount;
 
-- AR Receipts APPLIES Missing GLT
SELECT DISTINCT
    --	ara.accounting_date,
    ara.voucher_number
    , ara.STATUS
    , ara.[source]
    , ara.posted_date
    , count(glt.id) AS glt_count
--	ara.payment_amount AS amount
FROM ar_receipt_applies ara
LEFT JOIN gl_transactions glt
    ON glt.object_type = 'ArReceiptApply'
        AND glt.voucher_number = ara.voucher_number
WHERE glt.id IS NULL
    AND ara.STATUS IN ('Applied', 'Voided')
GROUP BY ara.voucher_number
    , ara.STATUS
    , ara.[source]
    , ara.posted_date;
  1. If the missing voucher is returned, run the following Rails command to correct the data:

Rails Command Needed to Fix Data
CODE
DRY_RUN = false
@all_output = []

def AccountingPeriod.roll_forward_accounting_date(accounting_date, period)
  accounting_date
end
def AccountingPeriod.roll_forward_accounting_period(accounting_date, lock_type = nil, ensure_open = nil)
  AccountingPeriod.originals.where("period_date >= ?", accounting_date).where.not(fiscal_period: 0).order(:period_date).first
end

def generate_transactions voucher_hash, klass, poster_class
  count = 0
  vouchers = voucher_hash.map {|a| a["voucher_number"]}.uniq
  vouchers.each_slice(1000) do |sliced_vouchers|
    records = klass.where(voucher_number: sliced_vouchers)
    records.each do |record|
      record = record.as_of(record.posted_date)
      poster = poster_class.new
      poster.generate_transactions(record) do |h|
        h[:voucher_number] ||= record.voucher_number
        poster.enrich_transaction(h)
        poster.append_financial_statement_line_id(h)
        poster.convert_transaction(h)
        poster.apply_currency_fx_conversion(h)
        count+=1
        unless DRY_RUN
          GlTransaction.create! h
        end
      end
    end
  end
  
  msg = "Created #{count} transactions for #{klass.to_s}"
  puts msg
  @all_output << msg
end

########## AP INVOICES ##########
ActiveRecord::Base.connection.execute "SELECT DISTINCT
  voucher_number INTO #glt_vouchers
FROM gl_transactions
WHERE object_type = 'ApInvoice'"

tmp = ActiveRecord::Base.connection.select_all "SELECT DISTINCT
  api.accounting_date,
  api.voucher_number,
  api.status,
  api. [source],
  count(gld.id) AS gld_count,
  sum(gld.amount) AS amount
FROM ap_invoices api
  JOIN gl_distributions gld ON gld.object_id = api.id
    AND gld.object_type = 'ApInvoice'
  LEFT JOIN #glt_vouchers gv on gv.voucher_number = api.voucher_number
WHERE
  gv.voucher_number IS NULL
  AND api.status in('Open', 'Paid', 'Voided')
GROUP BY
  api.accounting_date,
  api.voucher_number,
  api.status,
  api.[source]"

klass = ApInvoice
poster_class = ApInvoiceGlPoster
generate_transactions tmp, klass, poster_class

ActiveRecord::Base.connection.execute "DROP TABLE #glt_vouchers"


########## JOURNAL ENTRIES ##########
ActiveRecord::Base.connection.execute "SELECT DISTINCT
  voucher_number INTO #glt_vouchers
FROM gl_transactions
WHERE object_type = 'JournalEntry'"

tmp = ActiveRecord::Base.connection.select_all "SELECT DISTINCT
  je.accounting_date,
  je.voucher_number,
  je.status,
  je.[source],
  count(gld.id) AS gld_count,
  sum(gld.amount) AS amount
FROM journal_entries je
  JOIN gl_distributions gld ON gld.object_id = je.id
    AND gld.object_type = 'JournalEntry'
  LEFT JOIN #glt_vouchers gv on gv.voucher_number = je.voucher_number
WHERE
  gv.voucher_number IS NULL
  AND je.status in('Posted')
GROUP BY
  je.accounting_date,
  je.voucher_number,
  je.status,
  je.[source]"

klass = JournalEntry
poster_class = JournalEntryGlPoster
generate_transactions tmp, klass, poster_class

ActiveRecord::Base.connection.execute "DROP TABLE #glt_vouchers"


########## AR INVOICES ##########
ActiveRecord::Base.connection.execute "SELECT DISTINCT
  voucher_number INTO #glt_vouchers
FROM gl_transactions
WHERE object_type = 'ArInvoice'"

tmp = ActiveRecord::Base.connection.select_all "SELECT DISTINCT
  ari.accounting_date,
  ari.voucher_number,
  ari.status,
  ari. [source],
  count(gld.id) AS gld_count,
  sum(gld.amount) AS amount
FROM ar_invoices ari
JOIN gl_distributions gld ON gld.object_id = ari.id
  AND gld.object_type = 'ArInvoice'
LEFT JOIN #glt_vouchers gv on gv.voucher_number = ari.voucher_number
WHERE
  gv.voucher_number IS NULL
  AND ari.status in('Open', 'Paid', 'Voided')
GROUP BY
  ari.accounting_date,
  ari.voucher_number,
  ari.status,
  ari. [source]"

klass = ArInvoice
poster_class = ArInvoiceGlPoster
generate_transactions tmp, klass, poster_class

ActiveRecord::Base.connection.execute "DROP TABLE #glt_vouchers"


########## AR Receipts ##########
tmp = ActiveRecord::Base.connection.select_all " SELECT DISTINCT arr.accounting_date,
  arr.voucher_number,
  arr.STATUS,
  arr.[source],
  arr.posted_date,
  count(glt.id) AS glt_count,
  arr.payment_amount AS amount
FROM ar_receipts arr
LEFT JOIN gl_transactions glt
  ON glt.object_type = 'ArReceipt' AND glt.voucher_number = arr.voucher_number
WHERE glt.id IS NULL AND arr.STATUS IN ('Applied', 'Open', 'Voided', 'Posted')
GROUP BY arr.accounting_date,
  arr.voucher_number,
  arr.STATUS,
  arr.[source],
  arr.posted_date,
  arr.payment_amount"

klass = ArReceipt
poster_class = ArReceiptGlPoster
generate_transactions tmp, klass, poster_class




############ AP_PAYMENT ######
tmp = ActiveRecord::Base.connection.select_all " SELECT DISTINCT app.accounting_date
    , app.voucher_number
    , app.STATUS
    , app.[source]
    , app.posted_date
    , count(api.id) AS invoice_count
    , app.payment_amount AS amount
FROM ap_payments app 
JOIN ap_payment_invoices api 
    ON api.ap_payment_id = app.id
LEFT JOIN gl_transactions glt 
    ON glt.object_type = 'ApPayment'
        AND glt.voucher_number = app.voucher_number
WHERE glt.id IS NULL
    AND app.STATUS IN ('Paid', 'Voided')
GROUP BY app.accounting_date
    , app.voucher_number
    , app.STATUS
    , app.[source]
    , app.posted_date
    , app.payment_amount"


klass = ApPayment
poster_class = ApPaymentGlPoster
generate_transactions tmp, klass, poster_class




############ Ar_Receipt_Apply ######

tmp = ActiveRecord::Base.connection.select_all " SELECT DISTINCT
    null as accounting_date,
    ara.voucher_number
    , ara.STATUS
    , ara.[source]
    , ara.posted_date
    , count(glt.id) AS glt_count
	,null AS amount
FROM ar_receipt_applies ara (nolock)
LEFT JOIN gl_transactions glt (nolock)
    ON glt.object_type = 'ArReceiptApply'
        AND glt.voucher_number = ara.voucher_number
WHERE glt.id IS NULL
    AND ara.STATUS IN ('Applied', 'Voided')
GROUP BY ara.voucher_number
    , ara.STATUS
    , ara.[source]
    , ara.posted_date;"
klass = ArReceiptApply
poster_class = ArReceiptApplyGlPoster
generate_transactions tmp, klass, poster_class

puts "============\n============\n============"
puts @all_output
  1. After fixing the rails, re-run the select statement to confirm that no results appear and verify that the Voucher is in the Transaction screens.

📎 Related Tickets

WESS-12430

WESS-16481

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.