GL Journal Headers and Lines

SELECT gjh.je_header_id jnl_hdr_id

       , gjct.user_je_category_name category

       , gjst.user_je_source_name source

       , gjb.status post_status

       , gjb.name batch_name

       , gjh.period_name

       , gjh.name journal_name

       , gjh.external_reference REF

       , gjh.date_created

       , gjh.description

       , gjh.running_total_dr ttl

       , gjh.creation_date

       , fu.description created_by

       , '*** LINES ***'

       , gjl.je_line_num

       , gjl.description line_descr

       , gjl.reference_1 line_ref

       , gjl.reference_5 ap_inv_num

       , gjl.reference_6 line_src

       , gjl.reference_10 line_type

       , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 cgh_acct

       , gjl.accounted_dr dr

       , gjl.accounted_cr cr

--       , '*** 11i RECONCILIATION ***'

--       , gjl.jgzz_recon_status

--       , gjl.jgzz_recon_date

--       , gjl.jgzz_recon_id

--       , gjl.jgzz_recon_ref

--       , gjl.jgzz_recon_context

       , '*** R12 RECONCILIATION ***'

       , gjlr.jgzz_recon_status

       , gjlr.jgzz_recon_date

       , gjlr.jgzz_recon_id

       , gjlr.jgzz_recon_ref      

    FROM gl.gl_je_headers gjh

       , gl.gl_je_batches gjb

       , gl.gl_je_lines gjl

       , gl.gl_je_sources_tl gjst

       , gl.gl_je_categories_tl gjct

       , gl.gl_code_combinations gcc

       , gl.gl_je_lines_recon gjlr

       , applsys.fnd_user fu

   WHERE gjh.je_batch_id = gjb.je_batch_id(+)

     AND gjh.je_header_id = gjl.je_header_id

     AND gjh.je_source = gjst.je_source_name

     AND gjh.je_category = gjct.je_category_name

     AND gjh.created_by = fu.user_id

     AND gjl.code_combination_id = gcc.code_combination_id

     AND GJL.JE_HEADER_ID = GJLR.JE_HEADER_ID(+)

     AND GJL.JE_LINE_NUM = GJLR.JE_LINE_NUM(+)

     AND gjh.je_header_id IN (123456)

     AND gjl.je_line_num IN (2)

--     AND gjh.period_name = '08:MAR'

--     AND gcc.segment1 = 'ABC1234'

--     AND gcc.segment2 = '1234'

--     AND gjl.je_header_id = 12472485

--     AND gjh.creation_date BETWEEN '12-FEB-2013' AND '13-FEB-2013'

--     AND gjb.name = 'John Doe'

--     AND gjl.reference_1 = 'Cheese Machine'

--     AND gjst.user_je_source_name = 'AP Invoices'

--     AND gjb.name = 'Testing 123'

ORDER BY gjh.creation_date DESC

       , gjh.name

       , gjb.name

       , gjl.je_line_num;

 

/*##############################################################################

#        JOURNAL VOLUMES 1

/*############################################################################*/

 

  SELECT gjst.user_je_source_name source

       , gjct.user_je_category_name category

       , COUNT(*) ct

    FROM gl.gl_je_headers gjh

       , gl.gl_je_batches gjb

       , gl.gl_je_sources_tl gjst

       , gl.gl_je_categories_tl gjct

       , applsys.fnd_user fu

   WHERE gjh.je_batch_id = gjb.je_batch_id(+)

     AND gjh.je_source = gjst.je_source_name

     AND gjh.je_category = gjct.je_category_name

     AND gjh.created_by = fu.user_id

     AND gjh.creation_date > '10-JUN-2012'

GROUP BY gjst.user_je_source_name

       , gjct.user_je_category_name    

ORDER BY 1

       , 2;

 

/*##############################################################################

#        JOURNAL VOLUMES 2

/*############################################################################*/

 

  SELECT gjst.user_je_source_name

       , COUNT (*) ct

       , MAX (gjh.creation_date) latest

    FROM gl.gl_je_headers gjh

       , gl.gl_je_sources_tl gjst

   WHERE gjh.je_source = gjst.je_source_name

GROUP BY gjst.user_je_source_name;

 

/*##############################################################################

#      BALANCE PER PERIOD

/*############################################################################*/

 

  SELECT gjh.period_name

       , gcc.segment1

       , SUM (gjl.accounted_dr) - SUM (gjl.accounted_cr) balance

    FROM gl.gl_je_headers gjh

       , gl.gl_je_batches gjb

       , gl.gl_je_lines gjl

       , gl.gl_je_sources_tl gjst

       , gl.gl_je_categories_tl gjct

       , gl.gl_code_combinations gcc

       , gl.gl_je_lines_recon gjlr

       , applsys.fnd_user fu

   WHERE gjh.je_batch_id = gjb.je_batch_id(+)

     AND gjh.je_header_id = gjl.je_header_id

     AND gjh.je_source = gjst.je_source_name

     AND gjh.je_category = gjct.je_category_name

     AND gjh.created_by = fu.user_id

     AND gjl.code_combination_id = gcc.code_combination_id

     AND gjl.je_header_id = gjlr.je_header_id(+)

     AND gjl.je_line_num = gjlr.je_line_num(+)

     AND gjh.period_name LIKE '1213%'

GROUP BY gjh.period_name

       , gcc.segment1

ORDER BY gjh.period_name;

 

/*##############################################################################

#        JOURNAL HEADER AND LINE COUNT PER USER

/*############################################################################*/

 

  SELECT DISTINCT fu.user_name, fu.description

       , COUNT(DISTINCT gjh.je_header_id) journal_count

       , COUNT(GJL.JE_HEADER_ID) line_count

    FROM gl.gl_je_headers gjh

       , gl.gl_je_batches gjb

       , gl.gl_je_lines gjl

       , gl.gl_je_sources_tl gjst

       , gl.gl_je_categories_tl gjct

       , gl.gl_code_combinations gcc

       , gl.gl_je_lines_recon gjlr

       , applsys.fnd_user fu

   WHERE gjh.je_batch_id = gjb.je_batch_id(+)

     AND gjh.je_header_id = gjl.je_header_id

     AND gjh.je_source = gjst.je_source_name

     AND gjh.je_category = gjct.je_category_name

     AND gjh.created_by = fu.user_id

     AND gjl.code_combination_id = gcc.code_combination_id

     AND gjl.je_header_id = gjlr.je_header_id(+)

     AND gjl.je_line_num = gjlr.je_line_num(+)

     AND GJH.CREATION_DATE > '01-JUL-2012'

     AND GJH.CREATION_DATE < '02-JUL-2013'

     GROUP BY fu.user_name, fu.description

ORDER BY fu.user_name;