GL Web ADI

-- job details

select bauj.job_id
     , fat.application_name app
     , bauj.integrator_code
     , bit.user_name
     , bauj.upload_state
     , bauj.translated_upload_state
     , bauj.job_creation_date
     , bauj.job_start_date
     , bauj.job_end_date
     , bauj.creation_date cr_date
     , fu.description cy_by
     , '########################'
     , bauj.*
  from bne.bne_async_upload_jobs bauj
     , bne.bne_integrators_tl bit
     , applsys.fnd_user fu
     , applsys.fnd_application_tl fat
 where bauj.integrator_code = bit.integrator_code
   and bauj.created_by = fu.user_id
   and bauj.integrator_app_id = fat.application_id
   and bauj.creation_date > '20-MAY-2015'
   AND fu.user_name = 'SYSADMIN';

-- Web ADI Integrators

SELECT *
  FROM bne_integrators_tl
 WHERE integrator_code = 'JOURNALS_120';

-- Web ADI Layouts

SELECT * FROM bne_layouts_b;

-- Both together

SELECT fa.application_short_name appl, bit.application_id
     , bit.integrator_code
     , bit.user_name
     , bit.creation_date
     , blb.layout_code
     , blt.user_name user_name_tl
  FROM bne.bne_integrators_tl bit
     , bne.bne_layouts_b blb
     , bne.bne_layouts_tl blt
     , applsys.fnd_application fa
 WHERE bit.integrator_code = blb.integrator_code
   AND blb.layout_code = blt.layout_code
   AND bit.application_id = fa.application_id
   AND bit.integrator_code = 'JOURNALS_120';

-- simple count
 
SELECT fa.application_short_name appl
     , bit.integrator_code
     , count(*) ct
  FROM bne.bne_integrators_tl bit
     , bne.bne_layouts_b blb
     , bne.bne_layouts_tl blt
     , applsys.fnd_application fa
 WHERE bit.integrator_code = blb.integrator_code
   AND blb.layout_code = blt.layout_code
   AND bit.application_id = fa.application_id
GROUP BY fa.application_short_name
     , bit.integrator_code
ORDER BY 2;

-- layouts attached to integrators

SELECT fa.application_short_name appl
     , bit.integrator_code
     , bit.user_name
     , bit.creation_date
     , blb.layout_code
     , blt.user_name user_name_tl
  FROM bne.bne_integrators_tl bit
     , bne.bne_layouts_b blb
     , bne.bne_layouts_tl blt
     , applsys.fnd_application fa
 WHERE bit.integrator_code = blb.integrator_code
   AND blb.layout_code = blt.layout_code
   AND bit.application_id = fa.application_id
   AND bit.integrator_code = 'JOURNALS_120';

GL Security Rules

Sections:

  1. Responsibility Plus GL Security Rules – Basic
  2. GL Security Rules Only
  3. Responsibility Plus GL Security Rules – Resp and Rule Only
  4. GL Security Rule Count Per Responsibility

Responsibility Plus GL Security Rules – Basic

-- ##############################################################################
--        RESP PLUS RULE BASICS
-- ##############################################################################

  SELECT rtl.responsibility_name
       , fvr.flex_value_rule_name rule
    FROM applsys.fnd_flex_value_rules fvr
       , applsys.fnd_flex_value_rules_tl fvrtl
       , applsys.fnd_flex_value_rule_usages fvru
       , applsys.fnd_responsibility_tl rtl
       , applsys.fnd_responsibility fr
       , applsys.fnd_application_tl fatl
   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id
     AND fvru.responsibility_id = rtl.responsibility_id
     AND fatl.application_id = rtl.application_id
     AND rtl.responsibility_id = fr.responsibility_id
--     AND rtl.responsibility_name LIKE 'G%J%'
--     AND fvr.flex_value_rule_name = 'Stock'
ORDER BY rtl.responsibility_name;

GL Security Rules Only

-- ##############################################################################
--        RULES ONLY
-- ##############################################################################

  SELECT fvr.flex_value_rule_name rule
       , fvrtl.error_message
       , DECODE(ffvrl.include_exclude_indicator,'E','Exclude','I','Include') inc_exc
       , ffvrl.flex_value_low
       , ffvrl.flex_value_high
       , ffvrl.creation_date
       , fu.description
    FROM applsys.fnd_flex_value_rules fvr
       , applsys.fnd_flex_value_rules_tl fvrtl
       , applsys.fnd_flex_value_rule_lines ffvrl
       , applsys.fnd_flex_value_sets ffvs
       , applsys.fnd_user fu
   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id
     AND ffvrl.last_updated_by = fu.user_id
--     AND ffvrl.creation_date > '23-NOV-2015'
     AND fvrtl.error_message LIKE '%Only%balance%'
--     AND fvr.flex_value_rule_name = 'Stock'
     AND 1 = 1;

Responsibility Plus GL Security Rules – Resp and Rule Only

-- ##############################################################################
--        RESP PLUS RULE NAME ONLY
-- ##############################################################################

  SELECT DISTINCT
         rtl.responsibility_name
       , fvr.flex_value_rule_name rule
    FROM applsys.fnd_flex_value_rules fvr
       , applsys.fnd_flex_value_rules_tl fvrtl
       , applsys.fnd_flex_value_rule_usages fvru
       , applsys.fnd_responsibility_tl rtl
       , applsys.fnd_responsibility fr
       , applsys.fnd_application_tl fatl
       , applsys.fnd_flex_value_rule_lines ffvrl
       , applsys.fnd_flex_value_sets ffvs
   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id
     AND fvru.responsibility_id = rtl.responsibility_id
     AND fatl.application_id = rtl.application_id
     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id
     AND rtl.responsibility_id = fr.responsibility_id
     AND rtl.responsibility_name LIKE 'PO Internet%'
--     AND fvr.flex_value_rule_name = 'Stock'
ORDER BY rtl.responsibility_name
       , fvr.flex_value_rule_name;

GL Security Rule Count Per Responsibility

-- ##############################################################################
--        COUNT PER RESPONSIBILITY
-- ##############################################################################

  SELECT DISTINCT 
         rtl.responsibility_name
       , COUNT (*) ct
    FROM applsys.fnd_flex_value_rules fvr
       , applsys.fnd_flex_value_rules_tl fvrtl
       , applsys.fnd_flex_value_rule_usages fvru
       , applsys.fnd_responsibility_tl rtl
       , applsys.fnd_responsibility fr
       , applsys.fnd_application_tl fatl
       , applsys.fnd_flex_value_rule_lines ffvrl
       , applsys.fnd_flex_value_sets ffvs
   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id
     AND fvru.responsibility_id = rtl.responsibility_id
     AND fatl.application_id = rtl.application_id
     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id
     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id
     AND rtl.responsibility_id = fr.responsibility_id
     AND rtl.responsibility_name LIKE 'GL%'
GROUP BY rtl.responsibility_name
ORDER BY rtl.responsibility_name;

GL Cross Validation Rules

-- ##############################################################################
--      CROSS VALIDATION RULES HEADERS
-- ##############################################################################

SELECT ffvrv.flex_validation_rule_name rule_name
     , ffvrv.enabled_flag enabled
     , ffvrv.creation_date created
     , cr_by.description created_by
     , cr_by.user_name
     , ffvrv.last_update_date updated
     , up_by.description updated_by
     , ffvrv.error_segment_column_nameerror_segment
     , ffvrv.error_message_text error_message
     , ffvrv.description
  FROM apps.fnd_flex_vdation_rules_vl ffvrv
     , applsys.fnd_user cr_by
     , applsys.fnd_user up_by
 WHERE ffvrv.created_by = cr_by.user_id
   AND ffvrv.last_updated_by = up_by.user_id
   AND cr_by.user_name NOT IN ('AUTOINSTALL', 'INITIAL SETUP', 'SYSADMIN')
   AND 1 = 1;

      SELECT DISTINCT
             fifsv.segment_num "NUMBER"
           , fifsv.segment_name name
           , fifsv.form_left_prompt prompt
           , fifsv.application_column_name 
           , fnd_set.flex_value_set_name
           , fifsv.display_flag displayed
           , fifsv.enabled_flag enabled
        FROM apps.fnd_id_flex_segments_vl fifsv
   LEFT JOIN applsys.fnd_flex_value_sets fnd_set ON fifsv.flex_value_set_id = fnd_set.flex_value_set_id
       WHERE 1 = 1
         AND fifsv.id_flex_code = 'GL#'
         AND fifsv.application_id = 101
         AND 1 = 1
    ORDER BY fifsv.segment_num;

-- ##############################################################################
--      CROSS VALIDATION RULES LINES
-- ##############################################################################

SELECT ffvrv.flex_validation_rule_name rule_name
     , '#### HEADER DETAILS ####' header_details
     , ffvrv.enabled_flag enabled
     , ffvrv.creation_date header_created
     , cr_by.description header_created_by
     , ffvrv.last_update_date header_updated
     , up_by.description header_updated_by
     , ffvrv.error_segment_column_name
     , ffvrv.error_message_text error_message
     , ffvrv.description
     , '#### LINE DETAILS ####' line_details
     , ffvrl.creation_date line_created
     , cr_line_by.description line_created_by
     , ffvrl.last_update_date line_updated
     , up_line_by.description line_updated_by
     , ffvrl.enabled_flag line_enabled
     , ffvrl.concatenated_segments_low from_
     , ffvrl.concatenated_segments_high to_
     , DECODE (ffvrl.include_exclude_indicator
             , 'E', 'Exclude'
             , 'I', 'Include')
          include_exclude
  FROM apps.fnd_flex_vdation_rules_vl ffvrv
     , apps.fnd_flex_validation_rule_lines ffvrl
     , applsys.fnd_user cr_by
     , applsys.fnd_user up_by
     , applsys.fnd_user cr_line_by
     , applsys.fnd_user up_line_by
 WHERE ffvrv.flex_validation_rule_name = ffvrl.flex_validation_rule_name
   AND ffvrv.created_by = cr_by.user_id
   AND ffvrv.last_updated_by = up_by.user_id
   AND ffvrl.created_by = cr_line_by.user_id
   AND ffvrl.last_updated_by = up_line_by.user_id
   AND cr_by.user_name NOT IN ('AUTOINSTALL', 'INITIAL SETUP', 'SYSADMIN')
--   AND ffvrl.flex_validation_rule_name = 'Rule001'
   AND ffvrl.creation_date > '01-APR-2013'
ORDER BY ffvrl.creation_date desc;

GL Chart of Accounts

-- ##############################################################################
--        GL CHART OF ACCOUNTS - HEADER
-- ##############################################################################

    SELECT gl.ledger_id
         , gl.name ledger_name
         , gl.short_name ledger_short_name
         , gl.description ledger_descripption
         , fifsv.id_flex_num
         , fifsv.id_flex_structure_code code
         , fifsv.id_flex_structure_name title
         , fifsv.description
         , fifsv.structure_view_name view_name
         , fifsv.concatenated_segment_delimiter segment_separator
         , fifsv.freeze_flex_definition_flag freeze_flexfield_definition
         , fifsv.cross_segment_validation_flag cross_validate_segments
         , fifsv.enabled_flag enabled
         , fifsv.freeze_structured_hier_flag freeze_rollup_groups
         , fifsv.last_update_date
      FROM apps.fnd_id_flex_structures_vl fifsv
 LEFT JOIN gl.gl_ledgers gl ON gl.chart_of_accounts_id = fifsv.id_flex_num
     WHERE id_flex_code = 'GL#';

-- ##############################################################################
--        GL CHART OF ACCOUNTS - SEGMENTS SUMMARY
-- ##############################################################################

      SELECT fifsv.segment_num "NUMBER"
           , fifsv.segment_name name
           , fifsv.form_left_prompt prompt
           , fifsv.application_column_name 
           , fnd_set.flex_value_set_name
           , fifsv.display_flag displayed
           , fifsv.enabled_flag enabled
        FROM apps.fnd_id_flex_segments_vl fifsv
   LEFT JOIN applsys.fnd_flex_value_sets fnd_set ON fifsv.flex_value_set_id = fnd_set.flex_value_set_id
       WHERE 1 = 1
--         AND fifsv.id_flex_num = 50268
         AND fifsv.id_flex_code = 'GL#'
         AND fifsv.application_id = 101
         AND 1 = 1
    ORDER BY fifsv.segment_num;

GL Periods

-- ##############################################################################
--      PERIODS - 11i -- uses Sets of Books, not Ledgers
-- ##############################################################################

-- 11i

  SELECT fa.application_short_name app
       , fat.application_name app_name
       , fat.application_id
       , gps.period_name
       , gps.period_year
--       , GLV.NAME ledger
       , DECODE(gps.closing_status, 'O','Open', 'C', 'Closed', 'F', 'Future', 'W', 'Pending Close', 'N', 'Never Opened', gps.closing_status) stat
       , gps.last_update_date up_dt
       , fu.user_name up_by
       , gps.creation_date
       , fu2.user_name
       , sob.name sob_name
    FROM gl.gl_period_statuses gps
    JOIN applsys.fnd_application_tl fat ON gps.application_id =  fat.application_id
    JOIN applsys.fnd_application fa     ON fa.application_id = fat.application_id 
    JOIN applsys.fnd_user fu            ON gps.last_updated_by = fu.user_id
    JOIN applsys.fnd_user fu2           ON gps.created_by = fu2.user_id
    JOIN gl_sets_of_books sob           ON sob.set_of_books_id = gps.set_of_books_id
   WHERE 1 = 1
--     AND gps.last_update_date > TRUNC(SYSDATE) - 10
--     and glv.name = 'BOB HOPE TRUST'
     AND gps.application_id = 101
     and fu2.user_name = 'BOBHOPE'
--     AND gps.closing_status = 'O'
--     AND fat.application_id = 8721
--     AND TRUNC(SYSDATE) BETWEEN gps.start_date AND gps.end_date
--     AND gps.period_name = '1516-05:DEC'
--     AND gps.period_year = '2016'
ORDER BY gps.creation_date DESC;

-- ##############################################################################
--      PERIODS - R12 -- users Ledgers, not Sets of Books
-- ##############################################################################

-- Release 12

  SELECT fa.application_short_name app
       , fat.application_name app_name
       , fat.application_id
       , gps.period_name
       , gps.period_year
       , GLV.NAME ledger
       , DECODE(gps.closing_status, 'O','Open', 'C', 'Closed', 'F', 'Future', 'W', 'Pending Close', 'N', 'Never Opened', gps.closing_status) stat
       , gps.last_update_date up_dt
       , fu.user_name up_by
    FROM gl.gl_period_statuses gps
    JOIN applsys.fnd_application_tl fat ON gps.application_id =  fat.application_id
    JOIN applsys.fnd_application fa     ON fa.application_id = fat.application_id 
    JOIN applsys.fnd_user fu            ON gps.last_updated_by = fu.user_id
    JOIN gl_ledgers_v glv               ON gps.ledger_id = glv.ledger_id
   WHERE 1 = 1
     AND gps.last_update_date > TRUNC(SYSDATE) - 10
     and glv.name = 'BOB HOPE TRUST'
--     AND gps.application_id = 222
--     AND gps.closing_status = 'O'
--     AND fat.application_id = 8721
--     AND TRUNC(SYSDATE) BETWEEN gps.start_date AND gps.end_date
--     AND gps.period_name = '1516-05:DEC'
--     AND gps.period_year = '2016'
ORDER BY gps.last_update_date DESC;

-- #############################################################################################

SELECT MAX (period_name)
   FROM gl.gl_periods
  WHERE start_date < SYSDATE
    AND end_date > SYSDATE
    AND period_set_name = 'MY_CALENDAR';
    
SELECT period_name, start_date, end_date
   FROM gl.gl_periods
  WHERE start_date < '29-NOV-2015'
    AND end_date > '29-NOV-2015'
    AND period_set_name = 'MY_CALENDAR';    
    
SELECT MAX(period_name)
   FROM gl.gl_periods
  WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date
    AND period_set_name = 'MY_CALENDAR';

GL Journal Volumes

-- ##############################################################################
--        GL JOURNAL VOLUMES 1
-- ##############################################################################

  SELECT gjst.user_je_source_name source
       , gjct.user_je_category_name category
--       , gjh.actual_flag
       , COUNT(*) ct
    FROM gl.gl_je_headers gjh
    JOIN gl.gl_je_batches gjb        ON gjh.je_batch_id = gjb.je_batch_id
    JOIN gl.gl_je_sources_tl gjst    ON gjh.je_source =   gjst.je_source_name
    JOIN gl.gl_je_categories_tl gjct ON gjh.je_category = gjct.je_category_name
    JOIN applsys.fnd_user fu         ON gjh.created_by =  fu.user_id
   WHERE 1 = 1
--     AND gjh.creation_date > '01-JAN-2013'
     AND gjst.user_je_source_name = 'Projects'
--     AND gjb.je_batch_id = 5849416
GROUP BY gjst.user_je_source_name
       , gjct.user_je_category_name
--       , gjh.actual_flag
ORDER BY 1,2;

-- ##############################################################################
--        GL JOURNAL VOLUMES 2
-- ##############################################################################

-- by category v1

  SELECT gjct.user_je_category_name category
       , COUNT(*) ct
    FROM gl.gl_je_headers gjh
    JOIN gl.gl_je_categories_tl gjct ON gjh.je_category = gjct.je_category_name
   WHERE 1 = 1
--     AND gjh.creation_date > '01-OCT-2015'
GROUP BY gjct.user_je_category_name
ORDER BY 1,2;

-- by category v2

   SELECT gjct.user_je_category_name category
        , COUNT(*) ct
     FROM gl.gl_je_categories_tl gjct
LEFT JOIN gl.gl_je_headers gjh ON gjct.je_category_name = gjh.je_category
    WHERE 1 = 1
 --     AND gjh.creation_date > '01-OCT-2015'
      AND gjct.user_je_category_name IN ('Discounts','AX Inventory','AX Payables','AX Receivables')
 GROUP BY gjct.user_je_category_name
 ORDER BY 1,2;

-- by category v3 - details

   SELECT gjh.*
     FROM gl.gl_je_categories_tl gjct
LEFT JOIN gl.gl_je_headers gjh ON gjct.je_category_name = gjh.je_category
    WHERE 1 = 1
      AND gjct.user_je_category_name IN ('Discounts','AX Inventory','AX Payables','AX Receivables');

-- by source

  SELECT gjst.user_je_source_name
       , COUNT (*) ct
       , MAX (gjh.creation_date) latest
    FROM gl.gl_je_headers gjh
    JOIN gl.gl_je_sources_tl gjst ON gjh.je_source = gjst.je_source_name
--   WHERE gjh.creation_date > '10-SEP-2013'
GROUP BY gjst.user_je_source_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
    JOIN gl.gl_je_batches gjb        ON gjh.je_batch_id =         gjb.je_batch_id
    JOIN gl.gl_je_lines gjl          ON gjh.je_header_id =        gjl.je_header_id
    JOIN gl.gl_code_combinations gcc ON gjl.code_combination_id = gcc.code_combination_id
    JOIN applsys.fnd_user fu         ON gjh.created_by =          fu.user_id
   WHERE 1 = 1
     AND gjh.creation_date > '01-JUL-2012'
     AND gjh.creation_date < '10-JUL-2012'
     GROUP BY fu.user_name, fu.description
ORDER BY fu.user_name;

-- ##############################################################################
--        SUMMARY OF JOURNAL VOLUMES
-- ##############################################################################

--BY DAY 
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'RRRR-MM-DD') the_date
    FROM gl.gl_je_headers gjh
GROUP BY TO_CHAR(gjh.creation_date, 'RRRR-MM-DD')
ORDER BY TO_CHAR(gjh.creation_date, 'RRRR-MM-DD') DESC;

--BY DAY 
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'RRRR-MM-DD') the_date
       , gjh.document_creation_method
    FROM gl.gl_je_headers gjh
   WHERE gjh.authorization_status = 'APPROVED'
     AND gjh.type_lookup_code = 'STANDARD'
     AND gjh.creation_date > '01-FEB-2013'
GROUP BY TO_CHAR(gjh.creation_date, 'RRRR-MM-DD')
       , gjh.document_creation_method
ORDER BY TO_CHAR(gjh.creation_date, 'RRRR-MM-DD') DESC;

--BY MONTH 
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'RRRR-MM') the_date
    FROM gl.gl_je_headers gjh
GROUP BY TO_CHAR(gjh.creation_date, 'RRRR-MM')
ORDER BY TO_CHAR(gjh.creation_date, 'RRRR-MM') DESC;

--BY MONTH ORDERED BY MONTH
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'MON-RRRR') the_date
    FROM gl.gl_je_headers gjh
GROUP BY TO_CHAR(gjh.creation_date, 'MON-RRRR')
ORDER BY TO_CHAR(gjh.creation_date, 'MON-RRRR') DESC;

--BY MONTH ORDERED BY TALLY 
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'MON-RRRR') the_date
    FROM gl.gl_je_headers gjh
GROUP BY TO_CHAR(gjh.creation_date, 'MON-RRRR')
ORDER BY 1 DESC;

--BY YEAR  
SELECT   COUNT(*) tally
       , TO_CHAR(gjh.creation_date, 'RRRR') the_date
    FROM gl.gl_je_headers gjh
GROUP BY TO_CHAR(gjh.creation_date, 'RRRR')
ORDER BY TO_CHAR(gjh.creation_date, 'RRRR') DESC;

--ALL JOURNALS
SELECT COUNT(*) tally
  FROM gl.gl_je_headers gjh;

GL Journal Lines

Sections:

  1. GL Journal Lines
  2. GL Journal Lines – joined to SLA tables

GL Journal Lines

-- ##############################################################################
--      GL Journal Lines
-- ##############################################################################

      SELECT gjst.user_je_source_name source
           , gjct.user_je_category_name category
           , DECODE(gjh.actual_flag,'A','Actual','E','Encumbrance','Other') jnl_type
           , DECODE(gjh.status,'U','Unposted','P','Posted','Other') status
--           , gbv.budget_name
           , gjh.period_name
--           , gety.encumbrance_type
           , gjb.request_id
           , gjb.name batch_name
           , gjh.doc_sequence_value doc
           , gjh.default_effective_date
           , gjl.je_line_num line       
           , gjh.date_created
    --       , gjh.running_total_dr ttl
           , gjh.je_header_id
           , fu.description created_by
    --       , '*** lines ***'
           , gjl.effective_date line_gl_date
           , (replace(replace(gjl.description,chr(10),''),chr(13),' ')) line_descr
           , gjl.creation_date line_cr_dt
           , gjl.accounted_dr dr
           , gjl.accounted_cr cr       
           , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 cgh_acct
           , gcc.enabled_flag
           , gcc.detail_posting_allowed_flag
           , gcc.detail_budgeting_allowed_flag
           , gcc.last_update_date
           , gjl.reference_1
           , SUBSTR(gjl.reference_1,0,7) empno
           , gjl.reference_2
           , gjl.reference_4
           , gcc.segment1
           , gcc.segment2
           , gcc.segment3
           , gcc.segment4
           , gcc.segment5
           , gcc.segment6
    --       , '##############'
    --       , gjh.*
        FROM gl.gl_je_headers gjh
        JOIN gl.gl_je_batches gjb         ON gjh.je_batch_id =         gjb.je_batch_id
        JOIN gl.gl_je_lines gjl           ON gjh.je_header_id =        gjl.je_header_id
        JOIN gl.gl_je_sources_tl gjst     ON gjh.je_source =           gjst.je_source_name
        JOIN gl.gl_je_categories_tl gjct  ON gjh.je_category =         gjct.je_category_name
        JOIN gl.gl_code_combinations gcc  ON gjl.code_combination_id = gcc.code_combination_id
--   LEFT JOIN gl.gl_budget_versions gbv    ON gjh.budget_version_id =   gbv.budget_version_id
--   LEFT JOIN gl.gl_encumbrance_types gety ON gjh.encumbrance_type_id = gety.encumbrance_type_id
        JOIN applsys.fnd_user fu          ON gjh.created_by =          fu.user_id
       WHERE 1 = 1
--         AND gjh.period_name IN  ('1516-01:AUG','1415-01:AUG')
--         AND gjct.user_je_category_name = 'Budget'
         AND gjh.creation_date > '22-MAR-2016'
--         AND gcc.segment1 = 'AA00431'
--         AND gjl.
--         AND gjl.je_header_id IN (12968564)
--         AND gcc.code_combination_id = 516094
--         AND gjh.doc_sequence_value IN (367445)
--         AND gjst.user_je_source_name = 'Projects'
--         AND gjct.user_je_category_name = 'Miscellaneous Transaction'
--         AND gjl.je_line_num = 7296
--         AND fu.user_name = 'ZZALSJR8'
    AND 1 = 1;

GL Journal Lines – joined to SLA tables

With Oracle Release 12 comes the concept of sub-ledger-architecture (SLA). Journals can tie up with the SLA tables via the following SQL.

-- ##############################################################################
--      GL Journal Lines - joined to SLA tables
-- ##############################################################################

-- joined to SLA tables
-- http://www.orafaq.com/node/2242
-- N.B no join to them for custom sources e.g. payroll

    SELECT gjh.je_header_id jnl_hdr_id
         , gjb.je_batch_id 
         , gjb.request_id
         , gjst.user_je_source_name source
         , gjct.user_je_category_name category
         , DECODE(gjh.actual_flag,'A','Actual','E','Encumbrance','Other') jnl_type
         , DECODE(gjh.status,'U','Unposted','P','Posted','Other') status   
         , gjb.request_id
         , gjb.name batch_name
         , gjh.period_name
         , gjh.name jnl_name
         , gety.encumbrance_type
         , gjh.doc_sequence_value doc
         , gjl.creation_date
         , gjl.effective_date
         , gjl.je_line_num line
         , (replace(replace(gjl.description,chr(10),''),chr(13),' ')) line_descr
         , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 cgh_acct
         , gjl.accounted_dr dr
         , gjl.accounted_cr cr
         , fu.description
         , xte.transaction_number
         , xte.source_id_int_1
         , xte.source_id_int_2
         , xah.event_type_code
         , xah.product_rule_code
         , xte.entity_code
         , xal.accounting_class_code
         , xe.event_id
      FROM gl.gl_je_headers gjh             
      JOIN gl.gl_je_batches gjb             ON gjh.je_batch_id =         gjb.je_batch_id
      JOIN gl.gl_je_lines gjl               ON gjh.je_header_id =        gjl.je_header_id
      JOIN gl.gl_code_combinations gcc      ON gjl.code_combination_id = gcc.code_combination_id
      JOIN gl.gl_je_sources_tl gjst         ON gjh.je_source =           gjst.je_source_name
      JOIN gl.gl_je_categories_tl gjct      ON gjh.je_category =         gjct.je_category_name
      JOIN applsys.fnd_user fu              ON gjh.created_by =          fu.user_id
 LEFT JOIN gl.gl_import_references gir      ON gjh.je_header_id =        gir.je_header_id          AND gir.je_line_num =     gjl.je_line_num
 LEFT JOIN gl.gl_encumbrance_types gety     ON gjh.encumbrance_type_id = gety.encumbrance_type_id
 LEFT JOIN xla.xla_ae_lines xal             ON gir.gl_sl_link_table =    xal.gl_sl_link_table      AND gir.gl_sl_link_id =   xal.gl_sl_link_id
 LEFT JOIN xla.xla_ae_headers xah           ON xal.application_id =      xah.application_id        AND xal.ae_header_id =    xah.ae_header_id
 LEFT JOIN xla.xla_events xe                ON xah.application_id =      xe.application_id         AND xah.event_id =        xe.event_id
 LEFT JOIN xla.xla_transaction_entities xte ON xe.application_id =       xte.application_id        AND xe.entity_id =        xte.entity_id
     WHERE 1 = 1
--       AND 
       AND gjh.creation_date > '05-SEP-2015'
       AND gjb.je_batch_id = 5849416
--       AND gjh.creation_date < '05-AUG-2015'
--       AND gjh.doc_sequence_value = 400734
--       AND fu.user_name = 'MTFSSNM3'
--       AND gjst.user_je_source_name = 'Projects'
--       AND gjct.user_je_category_name = 'Revenue'
--       AND gjl.creation_date < '01-FEB-2016'
--       AND gcc.segment1 = 'R117737'
--       AND xte.source_id_int_1 = 76090
--       AND xte.source_id_int_2 IS NOT NULL
--       AND xah.event_type_code = 'REVENUE'
--       AND xah.product_rule_code = 'PA_STANDARD_ACCOUNTING'
--       AND xte.entity_code = 'REVENUE'
--       AND xal.accounting_class_code = 'UNBILL'
--       AND gcc.code_combination_id = 713283 -- 713283 = 'WH00367*9809*00*UM*00*00000'
--       AND (gjl.accounted_cr = 25243.81 OR gjl.accounted_dr = 25243.81)
--       AND xte.source_id_int_1 IN (2855405)
--       AND xe.event_id IN (3443068,3442934,3443088,3442950)
--       AND gcc.segment1 IN ('WH00366', 'WH00369')
--       AND gcc.segment2 IN ('9340')
       AND 1 = 1;

GL Journal Headers

-- ##############################################################################
--       GL JOURNAL HEADERS
-- ############################################################################*/
	
      SELECT gjh.doc_sequence_value doc_num
           , gjh.je_header_id
           , gjst.user_je_source_name source
           , gjct.user_je_category_name category
           , gjh.period_name
           , gjh.creation_date
           , gety.encumbrance_type enc_type
           , DECODE(gjh.actual_flag,'A','Actual','E','Encumbrance','Other') jnl_type
           , gjh.status
           , gjh.name journal_name
           , gjh.running_total_dr ttl
           , (SELECT COUNT(*) FROM gl.gl_je_lines gjl WHERE gjh.je_header_id = gjl.je_header_id) lines
           , to_char (gjh.creation_date, 'Dy') cr_day
           , fu.description created_by
           , gjb.je_batch_id
           , gjb.request_id
           , '----- REVERSAL INFO ----'
           , gjh.accrual_rev_period_name rev_period
           , gps.closing_status rev_period_status
           , gjh.accrual_rev_status rev
           , gjhrev.je_header_id
           , gjhrev.doc_sequence_value rev_doc
           , gjhrev.creation_date rev_cr_dt
           , furev.description rev_cr_by
        FROM gl.gl_je_headers gjh
        JOIN gl.gl_je_batches gjb         ON gjh.je_batch_id = gjb.je_batch_id
        JOIN gl.gl_je_sources_tl gjst     ON gjh.je_source = gjst.je_source_name
        JOIN gl.gl_je_categories_tl gjct  ON gjh.je_category = gjct.je_category_name
        JOIN applsys.fnd_user fu          ON gjh.created_by = fu.user_id
   LEFT JOIN gl.gl_encumbrance_types gety ON gjh.encumbrance_type_id = gety.encumbrance_type_id
   LEFT JOIN gl.gl_je_headers gjhrev      ON gjh.accrual_rev_je_header_id = gjhrev.je_header_id
   LEFT JOIN applsys.fnd_user furev       ON gjhrev.created_by = furev.user_id
   LEFT JOIN gl.gl_period_statuses gps    ON gps.period_name = gjh.accrual_rev_period_name AND gps.application_id = 101
       WHERE 1 = 1
--         AND gjb.je_batch_id IN (12345678)
--         AND gjh.status = 'M'
--         AND gjh.je_header_id = 12345678
--         AND gjh.accrual_rev_period_name IS NOT NULL
--         AND gjh.accrual_rev_status IS NULL
--         AND gps.closing_status != 'C'
         AND gjh.creation_date > '06-MAY-2016'
--         AND gjh.last_update_date > '01-DEC-2015'
--         AND gjb.group_id = 12345678
--         AND gjh.creation_date BETWEEN '30-OCT-2015' AND '01-NOV-2015'
--         AND fu.user_name = 'TESTUSER'
--         AND gjh.doc_sequence_value IN (123456)
--         AND gjh.actual_flag = 'A'
--         AND gjst.user_je_source_name = 'Payables'
--         AND gjct.user_je_category_name = 'Purchases'
--         AND gjb.request_id IN (17601705, 17601706)
--         AND gjb.request_id = 17601705
    ORDER BY gjh.creation_date desc;

GL Journals – Categories

-- ##############################################################################
--       GL JOURNAL CATEGORIES
-- ############################################################################*/

  SELECT gjct.je_category_name
       , gjct.user_je_category_name
       , gjct.creation_date cr_dt
       , gjct.description
       , gjct.je_category_key
       , fu1.user_name cr_by
       , gjct.last_update_date up_dt
       , fu2.user_name up_by
    FROM gl.gl_je_categories_tl gjct 
    JOIN applsys.fnd_user fu1 ON gjct.created_by = fu1.user_id
    JOIN applsys.fnd_user fu2 ON gjct.last_updated_by = fu2.user_id
   WHERE fu2.user_name != 'AUTOINSTALL' 
ORDER BY gjct.creation_date DESC;

GL Journals – Approvals

-- ##############################################################################
--       GL JOURNAL APPROVALS
-- ############################################################################*/

            select fu.user_name
                 , fu.description
                 , papf.employee_number submitter_empno
                 , NVL(gal.authorization_limit, 0) gl_limited_submitter
                 , gjb.creation_date batch_created
--                 , gjb.name batch_name
                 , gjb.running_total_dr
                 , gjb.approver_employee_id
                 , gjb.status
                 , gjb.approval_status_code
                 , papf2.full_name approver_name
                 , papf2.employee_number approver_empno
                 , NVL(gal2.authorization_limit, 0) gl_limited_approver
                 , fu2.user_name approver_user
                 , fu2.end_date approver_end_date
              from gl_je_batches gjb
              join fnd_user fu on fu.user_id = gjb.created_by
              join per_all_people_f papf on papf.person_id = fu.employee_id and sysdate between papf.effective_start_date and papf.effective_end_date
         left join gl_authorization_limits gal on gal.employee_id = papf.person_id
         left join per_all_people_f papf2 on papf2.person_id = gjb.approver_employee_id and sysdate between papf.effective_start_date and papf.effective_end_date
         left join gl_authorization_limits gal2 on gal2.employee_id = papf2.person_id
         left join fnd_user fu2 on papf2.person_id = fu2.employee_id
             where gjb.creation_date > '01-NOV-2017'
          order by gjb.creation_date desc;