Hi,

I have written a query to get unreconciled GL entries from GL for a particular bank account. But I get even the reconciled GL_je_lines records to this query.

SELECT gjl.code_combination_id AS ccid, gjl.je_header_id, gjl.je_line_num,gjl.accounted_cr,gjl.accounted_dr, gjl.description, gjl.effective_date
--Nvl2(gjl.accounted_cr,gjl.accounted_cr,gjl.accounted_dr) AS je_amount
FROM apps.gl_je_headers gjh, apps.gl_je_lines gjl
WHERE 1=1
--AND TRUNC(gjl.effective_date) BETWEEN ‘01-JUN-2009’ AND ‘30-JUN-2009’
AND gjh.je_header_id=gjl.je_header_id
AND gjl.status=’P’
AND EXISTS
(
SELECT 1 FROM apps.ce_bank_accounts
WHERE asset_code_combination_id=gjl.code_combination_id
AND bank_account_id=:P_BANK_ACC_ID

)
AND NOT EXISTS
(
SELECT sr.je_header_id, sr.reference_id, Sum(sr.amount) AS recon_amount
FROM
apps.ce_statement_reconcils_all sr
WHERE 1=1
AND sr.reference_type=’JE_LINE’
AND sr.status_flag=’M’
--AND sr.current_record_flag=’Y’
AND sr.je_header_id=gjl.je_header_id
AND sr.reference_id=gjl.je_line_num
GROUP BY sr.je_header_id, sr.reference_id
HAVING Sum(sr.amount)>=Abs(Nvl2(gjl.accounted_cr,gjl.accounted_cr,gjl.accounted_dr))
)
--AND Abs(Nvl2(gjl.accounted_cr,gjl.accounted_cr,gjl.accounted_dr))!=Abs(rec.recon_amount)
ORDER BY gjl.effective_date

Is there any issue with the query?

Thanks and regards,
rkodikara