Cacher is the code snippet organizer for pro developers

SQL 
        SELECT
            U0_GL_JE_LINES.JE_HEADER_ID,
            U0_GL_JE_LINES.JE_LINE_NUM,
            U0_GL_JE_LINES.CODE_COMBINATION_ID,
            U0_GL_JE_LINES.EFFECTIVE_DATE,
            U0_GL_JE_LINES.ENTERED_DR,
            U0_GL_JE_LINES.ENTERED_CR,
            U0_GL_JE_LINES.ACCOUNTED_DR,
            U0_GL_JE_LINES.ACCOUNTED_CR,
            U0_GL_JE_LINES.DESCRIPTION,
            U0_GL_JE_LINES.ROWID,
            U2_GL_CODE_COMBINATIONS.CHART_OF_ACCOUNTS_ID,
            U2_GL_CODE_COMBINATIONS.SEGMENT1,
            U2_GL_CODE_COMBINATIONS.ROWID,
            U5_GL_JE_HEADERS.JE_SOURCE,
            U5_GL_JE_HEADERS.CURRENCY_CODE,
            U5_GL_JE_HEADERS.ROWID
        FROM GL_JE_LINES U0_GL_JE_LINES
            LEFT OUTER JOIN GL_CODE_COMBINATIONS U2_GL_CODE_COMBINATIONS
            ON U2_GL_CODE_COMBINATIONS.CODE_COMBINATION_ID = U0_GL_JE_LINES.CODE_COMBINATION_ID
            LEFT OUTER JOIN GL_JE_HEADERS U5_GL_JE_HEADERS
            ON U5_GL_JE_HEADERS.JE_HEADER_ID = U0_GL_JE_LINES.JE_HEADER_ID
        WHERE
            ( U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??000 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??011 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??010 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??009 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??008 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??007 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??006 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??005 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??004 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??003 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??002 OR
            U2_GL_CODE_COMBINATIONS.SEGMENT1 = ??001 OR
            ??000 = 'ALL' ) AND
            (U0_GL_JE_LINES.CODE_COMBINATION_ID IN (221469, 221435, 220252, 217244, 217240, 194443, 194438, 194436, 194267, 
                194265, 194263, 194261, 194259, 194258, 194256, 194199, 193210, 193203, 192358)) AND
            TRUNC( U0_GL_JE_LINES.EFFECTIVE_DATE ) >= TO_DATE( '01.05.2015', 'DD.MM.YYYY' ) AND
            TRUNC( U0_GL_JE_LINES.EFFECTIVE_DATE ) < TO_DATE( '01.07.2015', 'DD.MM.YYYY' )
;

define

-- Join for GL code
  file GJL_to_GCC =
   access gl_code_combinations, 
    set code_combination_id = gl_je_lines:code_combination_id, 
    many to one, 
    null fill on failure;

 -- Join for JE Source description
  file GJL_to_GJST =
   access gl_je_sources_tl,
    set gl_je_sources_tl:je_source_name = gl_je_headers:je_source,
        gl_je_sources_tl:language = 'US',
    many to one,
    null fill on failure;

 -- File joins for Natural Account description
  file GJL_to_FFV =
   access fnd_flex_values,
    set fnd_flex_values:flex_value = GJL_to_GCC:segment4,
    many to one,
    null fill on failure;

  file GJL_to_FFVT =
   access fnd_flex_values_tl,
    set fnd_flex_values_tl:flex_value_id = GJL_to_FFV:flex_value_id,
    many to one,
    null fill on failure;

  string ST_GL_Account =
    GJL_to_GCC:segment1 + "." + GJL_to_GCC:segment2 + "." + GJL_to_GCC:segment3 + "." + GJL_to_GCC:segment4 + "." + 
    GJL_to_GCC:segment5 + "." + GJL_to_GCC:segment6 + "." + GJL_to_GCC:segment7;

  number NUM_Debit =
   if gl_je_lines:accounted_dr = NULL
    then 0
    else gl_je_lines:accounted_dr;

  number NUM_Credit =
   if gl_je_lines:accounted_cr = NULL
    then 0
    else (gl_je_lines:accounted_cr * -1);

  number NUM_Net =
   NUM_Debit + NUM_Credit;

where
gl_je_lines:code_combination_id one of 192358,193203,193210,194199,194256,194258,194259,194261,194263,194265,194267,194436,194438,194443,217240,217244,220252,221435,221469
and date(gl_je_lines:effective_date) >= 5.1.2015 and date(gl_je_lines:effective_date) < 7.1.2015

list/domain="gl_je_lines"/title="GENERAL LEDGER MONTHLY EXTRACT"/duplicates
  gl_je_lines:effective_date/heading='Effective Date'
  gl_je_headers:je_source/heading="JE SOURCE"
  gl_je_lines:je_line_num/heading="JE LINE #"/nocommas
  gl_je_lines:description/heading="JE DESCRIPTION"
  gl_je_headers:currency_code/heading="Currency"
  gl_je_lines:accounted_dr/heading="ACCOUNTED DEBIT"/mask="ZZZ,ZZZ,ZZZ,ZZ9.99"
  gl_je_lines:accounted_cr/heading="ACCOUNTED CREDIT"/mask="ZZZ,ZZZ,ZZZ,ZZ9.99"
  gl_je_lines:entered_dr/heading="Entered DEBIT"/mask="ZZZ,ZZZ,ZZZ,ZZ9.99"
  gl_je_lines:entered_cr/heading="Entered CREDIT"/mask="ZZZ,ZZZ,ZZZ,ZZ9.99"

  --#gl_je_lines:je_header_id/heading="JE ID"/nocommas
  --#gl_je_lines:status/heading="JE STATUS"
  --#GJL_to_GJST:description/heading="JE SOURCE DESCRIPTION"
  --#gl_je_headers:je_category/heading="JE CATEGORY"
  --#gl_je_headers:name/heading="JE NAME"
  --#ST_GL_Account/heading="GENERAL LEDGER ACCOUNT"
  --#GJL_to_FFVT:description/heading="NATURAL ACCOUNT DESCRIPTION"
  --#date(gl_je_lines:last_update_date)/heading="LAST UPDATE DATE"
  --#gl_je_lines:period_name/heading="PERIOD NAME"
  --#NUM_Net/heading="NET"/mask="ZZZ,ZZZ,ZZZ,ZZ9.99"

sorted by gl_je_lines:effective_date