Cash Management - How to get unrecociled GL_JE_LINES
Posted: 01 October 2009 03:30 PM
Newbie
Rank
Total Posts:  1
Joined  2009-10-01

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

Profile