GL Useful Querys

1)s.o.b name,descritpion,calendar name,currency,c.o.a name for all.

/* Formatted on 9/4/2014 2:02:48 PM (QP5 v5.115.810.9015) */
SELECT gsb.name "S.O.B NAME",
       gsb.description "DESC",
       gsb.period_set_name calendar_name,
       gsb.currency_code currency,
       fs.id_flex_structure_code
FROM gl_sets_of_books gsb, fnd_id_flex_structures fs
WHERE gsb.chart_of_accounts_id = fs.id_flex_num
AND gsb.name like '468%';

2)calendar name,desc,period name,begin of period,end of period,period type,period type desc for all.

/* Formatted on 9/4/2014 2:03:03 PM (QP5 v5.115.810.9015) */
SELECT gps.period_set_name cal_name,
       gps.description cal_desc,
       gp.period_name,
       gp.start_date,
       gp.end_date,
       gpt.period_type,
       gpt.description period_type_desc
FROM gl_period_sets gps, gl_periods gp, gl_period_types gpt
WHERE     gps.period_set_name = gp.period_set_name
      AND gp.period_type = gpt.period_type
      AND gps.period_set_name LIKE '468%';

3)s.o.b name,period name,status for all open periods for ur s.o.b

/* Formatted on 9/4/2014 2:03:13 PM (QP5 v5.115.810.9015) */
SELECT sob.name, pstat.period_name, pstat.closing_status, glk.meaning
FROM gl_period_statuses pstat, gl_sets_of_books sob, gl_lookups glk
WHERE     sob.set_of_books_id = pstat.set_of_books_id
      AND pstat.closing_status LIKE 'O'
      AND glk.lookup_code = pstat.closing_status
      AND glk.lookup_type = 'CLOSING_STATUS'
      AND sob.name LIKE '%464%';

4)c.o.a for accounting flex field
structure code,structure type,structure desc

/* Formatted on 9/4/2014 2:03:24 PM (QP5 v5.115.810.9015) */
SELECT fifs.id_flex_structure_code "COA Code",
       tl.id_flex_structure_name "COA Name",
       tl.description
FROM fnd_id_flexs fif,
     fnd_id_flex_structures fifs,
     fnd_id_flex_structures_tl tl
WHERE     fif.id_flex_code = fifs.id_flex_code
      AND fifs.application_id = fifs.application_id
      AND fifs.id_flex_code = tl.id_flex_code
      AND fifs.id_flex_num = tl.id_flex_num
      AND fif.id_flex_name LIKE 'Accounting Flexfield'
      AND id_flex_structure_code LIKE '%464%';


5)value set lo
segment name,value set and values

/* Formatted on 9/4/2014 2:03:37 PM (QP5 v5.115.810.9015) */
SELECT fifs.segment_name, ffvs.flex_value_set_name, ffv.flex_value
FROM fnd_id_flex_segments fifs,
     fnd_flex_value_sets ffvs,
     fnd_flex_values_vl ffv
WHERE     1 = 1
      AND fifs.flex_value_set_id = ffvs.flex_value_set_id
      AND id_flex_num = 52569;

6)journal name,desc,journal source,j.category,j.period,j.curr for different dates.

/* Formatted on 9/4/2014 2:03:48 PM (QP5 v5.115.810.9015) */
SELECT name, je_category, je_source, period_name, currency_code, description
FROM gl_je_headers
WHERE je_source = 'Payables';

7)display the jour name,sob id,j.batch_name,j.source,j.category,period name,status for all unposted journals.

/* Formatted on 9/4/2014 2:03:56 PM (QP5 v5.115.810.9015) */
SELECT gjh.name,
       gjh.set_of_books_id,
       gjb.name,
       gjh.je_source,
       gjh.je_category,
       gjh.period_name,
       gjh.status
FROM gl_je_headers gjh, gl_je_batches gjb
WHERE gjh.je_batch_id = gjb.je_batch_id AND UPPER (gjh.status) = 'U';

8)j.name,j.line account,amount debited,credited,j.line desc

/* Formatted on 9/4/2014 2:04:08 PM (QP5 v5.115.810.9015) */
SELECT gjh.name,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
          "Account Details",
       gjl.accounted_dr,
       gjl.accounted_cr,
       gjl.description
FROM gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcc
WHERE     1 = 1
      AND gjh.je_header_id = gjl.je_header_id
      AND gjl.code_combination_id = gcc.code_combination_id;