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.
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;
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
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.