Archive for the ‘Oracle General Ledger’ Category

Get JV more than Specified Amount

Thursday, April 9th, 2009

Connect by Apps user name and just enter Amount when Prompted.We can use this query in Oracle Alerts to Notify GL Manager

SELECT gjh.NAME, gjh.running_total_cr, gjh.running_total_dr,
gjh.currency_code, gjlv.description, gsnv.NAME,
DECODE (NVL (gjlv.entered_dr, 1),
1, ‘CREDIT’,
gjlv.entered_dr, gjlv.entered_dr
) debit,
DECODE (NVL (gjlv.entered_cr, 1),
1, ‘DEBIT’,
gjlv.entered_cr, gjlv.entered_cr
) credit,
gjlv.period_name,
( gccv.segment1
|| ‘ ‘
|| gccv.segment2
|| ‘ ‘
|| gccv.segment3
|| ‘ ‘
|| gccv.segment4
|| ‘ ‘
|| gccv.segment5
|| ‘ ‘
|| gccv.segment6
|| ‘ ‘
|| gccv.segment7
|| ‘ ‘
|| gccv.segment8
) ACCOUNT,
gjlv.entered_cr, gjlv.entered_dr
FROM gl_je_headers gjh,
gl_je_lines_v gjlv,
gl_sob_names_v gsnv,
gl_code_combinations_v gccv
WHERE ( (gjlv.je_header_id = gjh.je_header_id)
AND (gsnv.set_of_books_id = gjlv.set_of_books_id)
AND (gjlv.code_combination_id = gccv.code_combination_id)
)
AND (gjh.running_total_dr > :amount OR gjh.running_total_cr > :amount)

Posted in Oracle General Ledger | 1 Comment »