Currently, my sql statements causes a encounter_no to populate my variance report when there is a Insurance Payor payment on today. When a encounter no shows on the variance report, the Revenue Collections area start to research the Variance to determine the reason for underpayment. However, if an additional payment arrives for the same encounter no, for example - 7 days after the first payment is received, there might not be a variance anymore! Therefore, all of the effort spent researching the "Variance" is "wasted."Issue - It appears that the Variance Report should be on a time lag, if you will, for approximately 2 weeks.

Example:

Enc No. Pymt Amt Total Ins Pymts Underpay Amt Date on Var Rpt
378201 $1000 $1000 $12000 9/17/03
378201 $12000 $13000 $0 9/23/03


My initial thought is to modify the statement within my sql script from "and trunc(epd.date_updated) = trunc(sysdate)" to "and trunc(epd.date_updated) between trunc(sysdate)-14 and trunc(sysdate)."


The entire SQL script is as follows:

SELECT ep.contract_id CONT, pe.encounter_no ENC, sum(epd.payment_amount) PYMNT, pe.admit_date DDATE, pe.date_updated BDATE, pe.total_charges CHGS, pe.expected_reimbursement EXP, nvl(ep.total_payments,0) EPPMT, nvl(pe.total_payments,0) - nvl(ep.total_payments,0) OTHPMT, pe.total_payments TOTPMT, (nvl(pe.expected_reimbursement,0) - (nvl(ep.total_payments,0))) BAL FROM patient_encounter pe, encounter_payor ep, encounter_payment_detail epd WHERE pe.encounter_no = ep.encounter_No and ep.rank = 1
AND EP.CONTRACT_ID NOT IN ('NCT','GVN','GVA')
and epd.transaction_code in ('4700384','4700813','4700812','4700564', '4700301')
AND trunc(epd.date_updated) = trunc(sysdate) and pe.expected_reimbursement > 0 and pe.encounter_no = epd.encounter_No and epd.payment_amount > 0 group by ep.contract_id, pe.encounter_no, pe.admit_date, pe.date_updated, epd.payment_date, pe.total_charges, pe.expected_reimbursement, nvl(ep.total_payments,0),nvl(pe.total_payments,0)-nvl(ep.total_payments,0), pe.total_payments, (nvl(pe.expected_reimbursement,0) - (nvl(pe.total_payments,0))) ORDER BY CONT, BAL desc




Note, ep.total_payments is Insurer Payments received to date. pe.total_payments is the total of all payments received to date.
The transaction codes stated are transaction codes for Insurance Payments only.